公用表达式
简称CTE,通过with语句实现,分为递归和非递归
非递归
select * from (select year(now())) as year;
使用CTE,sql语句更加容易理解,多个CTE语句中间有逗号隔开即可
with year as (select year(now())) select * from year;
递归
输出1到10
with recursive cte_num(num) as
(
select 1 union all
select num + 1 from cte_num where num < 10
)
select * from cte_num;
层级查询
with recursive area_depth(id,name,path) as
(
select id,name,CAST(id as char(300))
from t_area where pid is null
union all
select a.id,a,name, CONCAT(ad.path, ',', a.id)
from area_depth as ad
join t_area as a
on ad.id = a.pid
)
select * from area_depth order by path;
生成列
生成列是根据数据表中定义列时指定的表达式计算得到的,分为virtual和stored。virtual是数据查询得到的,stored是写入数据生成的。
create table t_genearted_column(
a double,
b double,
c double as (a*a + b * b)
);
insert into t_genearted_column (a,b) values (1,1);
如下图所示,没有插入c列的数据,其中也会有数据,不能像c列插入值,要插入只能插入default
创建表明确指明virsual生成列。
create table t_genearted_column1(
a double,
b double,
c double generated always as (a + b) virtual
);
添加生成列
alter table table_name add column c double generated always as (a + b) stored
修改生成列
alter table table_name modify column c double generated always as (a + b) stored
删除
alter table table_name drop column c