SQL基础

create database xxx; --创建 xxx 数据库

create table yyy( --创建 yyy 表
a char(4) not null, -- 创建 a 列，类型 char(4),约束 not null(不为空)
b integer, -- 创建 b 列
primary key(a)); -- 设置主键为 a 列

drop table yyy;

alter table yyy add (
    c integer, -- 添加 c 列
    d integer  -- 添加 d 列
);

alter table yyy drop column b; -- 删除 b 列

insert into yyy values('a',1,2); -- 添加一行数据到表格 yyy

insert into yyy(a,c,d) values('b',2,3); -- 指定对应的列
insert into yyy values('c',3,3);

create table zzz(
    a char(4) not null,
    b integer default 0,
    c integer,
    primary key(a)
);

insert into zzz(a,c,d) 
select a,b,c from yyy; -- 插入其他表的数据

select a,c from yyy; -- 查询 yyy 表格，选取 a,c 两列返回

select * from yyy; -- 返回所有列

select 1 as "汉"; -- 选取常量 1，并重命名列名为"汉"，汉字需要用双引号包括。

select distinct c,d from yyy; -- 查询 yyy 表中的 c,d 两列，并删除 c,d 两列中存在的相同行，合并为一条

select * from yyy
where -- 添加查询条件
a like 'a%' 
and (c>=1 
or d - 1 > 1);

case 
    when /* 谓语 */ then /* 表达式 */
    when .. then ..
    else /* 表达式 */
end

select count(*) from yyy; -- 返回 yyy 表的总行数

select count(*) from yyy
group by -- 按指定聚合键分组
c;

select c,count(*) from yyy
group by c
having min(c) > 1; -- 分组条件

select * from yyy
order by a; -- 根据 a 列升序排序

drop table zzz; --删除表

delete from zzz; --删除表中的数据

delete from zzz
where -- 指定删除的行条件
a='a';

update yyy set c=2,d=3
where -- 指定更新的行条件
a='a';

begin transaction; --开始事务（SQL标准没有定义该语句的标准写法）
-- DML 语句段落
commit; -- 提交事务
rollback; -- 或者取消事务

-- 例子
begin transaction;
update yyy set c=100 where a='a';
select * from yyy; -- 预览更新效果
rollback; -- 取消事务
select * from yyy; -- 可以看到更新被取消

create view vvv(id,sum) as -- 创建视图
select a,c+d from yyy;

drop view vvv; -- 删除视图

select * from (
    select a,c+d from yyy
) as vy; -- 子查询临时命名为 vy

select * from yyy
where 
c > (select avg(c) from yyy); -- 子查询结果是标量，因此可以用在此处进行判断

select * from yyy as p1
where d > (select avg(d) --每一组的平均值
from yyy as p2 
where p2.c = p1.c -- 关联条件，即获取指定组的平均值
group by c -- 分组，得到一个非标量的查询结果（即多个组）
); -- 最终结果形成一个标量子查询

select * from yyy
union -- 并
select * from zzz;
-- 结果(1,2,3,4,5,6)

select * from yyy
union all-- 并(不去重)
select * from zzz;
-- 结果(1,2,3,4,3,4,5,6)

select * from yyy
intersect -- 交
select * from zzz;
-- 结果(3,4)

select * from yyy
except -- 交
select * from zzz;
-- 结果(1,2)

select z.zid,z.yid,z.age,y.class
from zzz as z
inner join -- 内联接
yyy as y
on z.yid = y.yid; -- 联接条件

select z.zid,y.yid,z.age,y.class
from yyy as y
left outer join -- 左外联接
zzz as z
on z.yid = y.yid; -- 联接条件

select * from yyy 
cross join  -- 交叉联接
zzz;

-- 对重复的 b 去重
select distinct b from ycx as p1 
where not exists ( -- 存在不满足的 b 项去除
    select a from y 
    except -- 满足全部 y 的差集为空集
    select a from ycx as p2 
    where p1.b = p2.b -- 对当前 b 进行
    );

select sum(a) over(order by a) as sum_a from yyy;

select sum(a) over(partition by a order by a) as sum_a from yyy;

select sum(a) over(order by a rows 1 preceding) as moving_sum from yyy;

select a,sum(a) from yyy
group by a -- 小计
union all -- 并集（不去除重复项）
select "合计" as a,sum(a) from yyy;--合计

select a,sum(a) from yyy
group by rollup(a);

select case when grouping(a) = 1 -- 是否为合计行
    then '合计'
    else cast(a as varchar(10)) -- 转换为字符串
    end as a,
    sum(a)
from yyy
group by rollup(a);