SQL笔记

Insert

insert into TABLE_NAME values(...,...,...)

insert into TABLE_NAME(col1,col2,col3) values(...,...,...)

insert into TABLE_NAME(col1,col2,col3) select col1,col2,col3 from TABLE_NAME2

Delete

delete from TABLE_NAME where col=...

子查询不能边删边查，要重新命名

Update

update TABLE_NAME set col1=..., col2=...,col3=... where col=...

触发器

create trigger NAME
#触发条件
after insert/update/delete
on TABLE_NAME
for each row
begin
#执行语句
insert into audit values(new.id,new.name);
end;

Replace

查询替换

select *,replace(gender,'M','male') as new from employees

将替换的值生成新的一列new

更新替换

update TABLE_NAME
set COL_NAME = replace(COL_NAME,OLD,NEW) 
where COL_NAME2 = XXX   #筛选条件

插入替换

replace into TABLE_NAME
values(...,....,....)

如果主键不存在，直接插入
如果主键存在，则替换

Alter

结尾处添加
FIRST：第一列
AFTER <列名>：在该列之后

添加字段

alter table TABLE_NAME add COL_NAME COL_TYPE

删除字段

alter table TABLE_NAME drop COL_NAME

修改字段名

alter table TABLE_NAME change OLD_COL NEW_COL NEW_TYPE

修改字段类型

alter table TABLE_NAME modify COL_NAME NEW_TYPE
alter table TABLE_NAME change COL_NAME COL_NAME NEW_TYPE

修改字段类型

直接用modify/change后加first/after …

修改字段默认值

alter table TABLE_NAME alter COL_NAME set default 默认值

修改表名

alter table TABLE_NAME rename NEW_NAME

外键约束

链接两个表的变量
A表的外键约束作用：让A表的某个字段依赖于B表中的某个字段

-- 创建表
create table TABLEA_NAME(
	.......
	-- 创建时添加表级外键约束
	constraint FK_NAME  foreign key (ACOL_NAME) references TABLEB_NAME (BCOL_NAME)
);

-- 已存在的表添加表级外键约束
alter table TABLEA_NAME add constraint FK_NAME 
foreign key TABLEA_NAME(ACOL_NAME) references TABLEB_NAME(BCOL_NAME);

RIGHT&LEFT

取字符串的右侧三个字符

right(STRING,3)

取字符串的左侧两个字符

left(STRING,2)

group_concat

select group_concat(COL_NAME1) from TABLE_NAME group by COL_NAME2

将同一个COL_NAME2下的COL_NAME1用逗号连接汇总

分页查询

page：查询的页数
cnt：每页的数据条数

select * from TABLE_NAME
limit page-1,cnt

in&exist

# in 实现
select * from  TABLEA where COL_NAME  in (select COL_NAME2 from TABLEB)

查询TABLEA，查询TABLEB，两个查询对比计算笛卡尔积

# exists 实现
select * from  TABLEA a where exists (select COL_NAME2 
from TABLEB b where a.COL_NAME = b.TABLEB )

查询TABLEA，逐条与TABLEB对比，返回TRUE/FALSE
其中子查询的select COL_NAME2 和 select *结果一样

CASE WHEN

case when CONDITIONA
then VALUEA
when CONDITIONB
then VALUEB
.......
else VALUEE
end

SUM OVER & ROW_NUMBER OVER

select COL1,COL2,COL3, sum(COL3) over (order by COL1) from TABLE_NAME

按照col1的顺序，计算该行col1列之前的所有col3的总和

select COL1,COL2,COL3, sum(COL3) over(partition by COL2 order by COL1 ) from TABLE_NAME

按col2 进行分组(partition )，每组以col1进行排序(order)，并进行连续加总（sum）

select COL1,COL2, row_number() over (order by COL1) from TABLE_NAME

根据col1的排序添加行数

HAVING

执行顺序：where ----- group by ----- having
where 对分组前的行进行操作
having 对分组后的数据进行操作

select COL1, count(COL2) cn from TABLE_NAME
group by COL1 having cn<2

RANK OVER

rank() over(order by … )：并列后间隔排名（1，2，2，4，5）
dense_rank() over(order by … )：并列后间隔排名（1，2，2，3，4）

select COL1, rank() over(order by COL1) r
from TABLE_NAME

聚合函数+case when筛选

user_id	date	rank_date
23	07-28	2
23	07-15	1
23	08-06	3

筛选用户第一次和第二次登陆的日期

select user_id,
min(case when rank_date = 1 then date end),
min(case when rank_date = 2 then date end)
from TABLENAME
group by user_id

其中聚合函数用min,max都可以，因为实际只有一组数据

count(*) over(partition by)

分组基数，还可以免于筛选时必须group by

语法记录

1. NULL不用加""
2. 建立新一列可以直接select … as
3. 连接字符concat(...,...,...)
4. 统计字符中某个字符的个数length(string)-length(replace(string,"x",""))
5. 最大max()，最小min()，平均值avg()
6. 默认升序，desc降序，asc升序
7. 可以不用Join直接链接筛选

select * from
TABLE_NAMEA AS A, TABLE_NAMEB AS B, TABLE_NAMEC AS C
WHERE A.COL_NAME = B.COL_NAME
AND B.COL_NAME = C.COL_NAME

8. select后，对字段命名时，有没有空格，有没有as都可以
9. 依次排序 order by COL1 desc, COL2 asc
10. 控制小数位数 format(COL,n)
11. 四舍五入round(COL,n)
12. 有条件count():count(COL="...." or null)
13. 日期max最近，min最远
14. 数组in:where (col1,col2) in (select col1,col2 from t)
15. 日期加减：date_add(DATE,interval 1 day)
16. ifnull(a,b):如果a是null,则返回b,若a不是null,则返回a
17. 提取日期年月日：year(DATE),month(DATE),day(DATE)
18. 修改日期格式：date_format(DATE,'%Y-%m-%d') / date_format(DATE,'%Y-%m').....
19. 中位数：正序逆序排序均>=总数/2
20. sum() group by有时候可以用 distinct ...,sum() over(partition by...) 替换
21. 获取当前时间current_date