基本SQL语句
// 查看数据表信息
select * from table
// 删除该表里面的数据
delete from table
// 修改数据库里的数据
UPDATEtable_name SETcolumn1=value1,column2=value2,... WHEREsome_column=some_value;
// 插入到表里面的数据
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
渐进的SQL语句
// 查询数据表里前20 的数据
select * from table offset 0 limit 20
// left 连接 以a表为主表, 以b 表为副表
select * from table1 as a left join table2 as b on a.id = b.id
// group by 分组
select a.key, b.key2
from table1 as a
left join table2 as b on a.id = b.id
group by a.key, b.key2
// having 分组后的条件
select a.key, count(*) as num
from table1 as a
left join table2 as b on a.id = b.id
group by a.key
having num > 20
// order by 排序
select * from table order by id desc
select * from table order by id asc
select * from table order by id asc , name desc
// distinct 去重
select distinct website_id from table
// where 条件 or , and , in , not in
select * from table where id = 12 and name = 'dayday'
select * from table where id = 12 or name = 'dayday'
select * from table where id in (1,2,3,4,5,6) and name not in ('dayday', 'mc' , 'cl')
// 数组查询, 查出来的数据以数组的形式展示
select array_agg(DISTINCT COALESCE(id,0 ) ) as student_id,
name，class
from table where name in （'dayday', 'cl' ,'mc'）
// string_agg 用逗号分隔， 并且排序 ，按照 deptno 分组
select deptno, string_agg(ename, ',' order by ename desc) from table group by deptno
// 排名 rank , 正常排名，名次1，1，1，1，5, 5, 7
select a,b,c,
rank() over ( order by a desc ) as rank
from table
// 排名 row_number , 序号 1,2,3,4,5,6,7
select a,b,c,
row_number () over ( order by a desc ) as rank
from table
// 将一个表里的数据插入到另一个表里
insert into table (a, b, c, d)
select a ,b ,c,d from table1
// 将一个表里的数据插入到另一个表里, 并且 返回 table 表里的数据
insert into table (a, b, c, d)
select a ,b ,c,d from table1
returning id
// 如果两个表里面的字段一致，
insert into table select * from table
// with as 的 用法
with a as (
select name from table where age > 20
) select * from table where name in ( select * from a )
// case when 的 用法
select name ，
case when score < 60 then '不合格'
when score < 70 then '合格'
when score < 80 then '中'
when score < 90 then '优秀'
else '完美' end
from table
// 查看3天前的信息
select (now - interval '3 day') from table
select (now - interval '1 min') from table
select (now - interval '3 month') from table
select (now - interval '3 year') from table
