mysql 语句基础（二）

1、使用json

2、公用表表达式（CTE）

3、生成列（generated column）

4、窗口函数

1、使用json

json文档以二进制格式存储

对文档元素的快速读取访问。

当服务器再次读取json文档时，不需要重新解析文本获取该值。

通过键或数组直接查找子对象或嵌套值，而不需要读取文档中的所有值。

create table t_json(id int primary key ,details json);
insert into t_json values (1,'{"email":"abcA@qq.com","address":{"line":"abc","line2":"sysz"}}');
insert into t_json values (2,'{"email":"abcA@qq.com","address":{"line2":"sysz"}}');
commit;

查询json 

可以使用-> 和->>运算检索json列的字段

如果不用引号检索数据，可以使用->>运算

select id ,details->>'$.address.line' email from t_json;

json函数

json_pretty()函数

select id,json_pretty(details) from t_json \G

json_contains()如果找到了数据，则返回1，否则返回0；

select json_contains(details->>'$.address.line',"abc" ) from t_json;

如何查询一个key？ 假设要检查address.line1是否存在

select json_contains_path(details,'one',"$.address.line") from t_json;

one  表示至少应该存在一个键，

检查address.line 和address.line2是否同时存在，可以使用all，而不是one

select json_contains_path(details,'all',"$.address.line"."$.address.line2") from t_json;

修改

在mysql8.0 之前的版本我们还需要对整个列进行完整的更新

json_set():替换现有值并添加不存在的值、

update t_json set details=json_set(details,"$.address.line","mmkk","$.nick","kai") where id=1;
commit;

json_insert()插入值，但不替换现有的值

update t_json set details=json_insert(details,"$.address.line3","Wing") where id=2;
COMMIT;

json_replace():仅替换现有值

update t_json set details=json_replace(details,"$.email","4508@qq.com") where id=2;
COMMIT;

json_remove 能从json文档中删除数据

update t_json set details=json_remove(details ,"$.address.line3") where id=2;
commit;

json_key():获取json文档中的所有键。

json_length():给出json文档中的元素数。

2、公用表表达式（CTE）

公共表表达式运行使用命名的临时结果集，这是通过允许在select语句和某些语句前面使用with子句来实现的。

非递归cte公用表表达式（cte）与派生表类似，但它的声明会放在查询快之前，而不是from子句中。

派生表

select ... from (subquery) as derived, t1 ...

CTE

select ... wtih derived as (subquery) select ... from derivde ,t1 ...

派生查询不能引用其他派生查询

cte可以用于其他cte

with d1 as (select ...from ...) ,d2 as (select ...from d1...) select ...from d1,d2..

递归cte

递归cte是一种特殊的cte，其子查询会引用自己的名字。with子句必须以with recursive开头。

递归cte子查询包括两部分：seed 查询和recursive查询，有union[ALL] 或union distinct 分离。

seed select 被执行一次以创建初始数据子集；recursive select 被重复执行以返回数据的子集，

直到获得完整的结果集。当迭代不会生成任何新行时，递归会停止。这对挖掘层次结构（父、子或部分、子部分）非常有用。

with recursive cte as

(select ... from table_name *seed select */

union all

select ... from cte, table_name) *"recursive select */"

select ... from cte;

with recursive cte (n) as (select 1 union all select (n+1) from cte where n<5) select * from cte;

3、生成列（generated column）

 生成列（generated column）的值是根据列定义中包含的表达式计算得出的。生成列包含下面两种类型。

 virtual 生成列：当从表中读取记录时，将计算该列。

 stored 生成列：当向表中写入新记录时，将计算该列并将其作为常规列存储在表中。

 virtual生成列比stored生成列更有用，因为一个虚拟的列不占用任何存储空间。你可以使用触发器模拟stored生成列的行为。

4、窗口函数

对于查询中的每一行，可以使用窗口函数，利用与该行相关的行执行计算。这是通过使用over和window子句来完成的。

以下是可以执行计算的裂变

cume_dist():累积分布值。

dense_rank():分区内当前行的等级（无间隔）

first_value():窗口帧中第一行的参数值。

lag():落后于分区内当前行的那一行的参数值

last_value():窗口帧中的第n行的参数值

ntile():分区内当前行的桶的编号

percent_rank():百分比排名值

rank():分区中当前行的等级（有间隔）

row_number():分区内当前行的编号

-- 行号

select row_number() over(ORDER BY id) from t1

-- 分隔结果 可以在over子句中分隔结果

SELECT id,name,row_number() over(partition by name order by id) from t1;

-- 命令窗口

可以根据需要对一个窗口命名，并多次使用它，无须每次都重新定义。

select id ,name,rank() over w from t1 window w as (partition by name order by id) ;

--第一个、最后一个和第n个值

你可以选择窗口结果中的第一个、最后一个和第n个值。如果该行不存在，则返回null。

SELECT id,name ,rank() over w as 'rank' ,first_value(name) over w as 'first',
nth_value(name,3) over w as 'third',last_value(name) over w as 'last' from t1
window w as (partition by name order by id );
select * from t1
select * from t2
commit;

本公众号是个人学习工作笔记，希望大家发现问题能及时和我本人沟通，希望你与我共同成长。个人微信zgjt12306。

欢迎关注“自学Oracle”