增 insert

增加一条记录

insert tb set col_1='a',col_2=10;

增加多条记录

需完全对应

insert tb values(...,...);

无需完全对应

insert tb (col_1,col_2) values(...,...);

增加查询来的记录（蠕虫复制）

insert tb set [col_1] select ...;

向tb插入tb1的数据

insert tb select * from tb1 [where 条件];

向tb插入tb1的数据（可选择插入列）

insert tb(col_1,col_2) select col_1,col_2 from tb1 [where 条件];

增加编号+10的记录

insert tb_1 (select max(deptno)+10,"AAA","SSS" from tb_2);

增加无错误记录

忽略错误，即错误值不插入

insert ignore tb ...;

删 delete

删除单表记录

删除20部门工资最高的五人记录

delete from tb
where deptno=20
order by sal
limit 5;

删除多表记录

删除dept表中的sales部门和emp表中sales部门的所有记录

delete e,d
from emp e join dept d 
on e.deptno=d.deptno
where d.dname="sales";

删除列中的指定行

delete from tb where col_2='...';

删除全部记录

重新创建个空表

truncate table tb;

一条条删除

delete table tb;

删除重复数据

删除重复邮箱中Id小的数据

delete p1 from Person p1,Person p2
where p1.Email = p2.Email and p1.Id > p2.Id

连接子句：重复姓名的最大id的数据 删除逻辑：删除重复姓名中id比子句中id小的数据

delete a from tb a join (
  select name,count(*),max(id) as id from tb
  group by name having count(*)>1) b
on a.name=b.name
where a.id<b.id

改 update

更新单表记录

update tb set col_1=公式/值,col_2=公式/值 [where 条件];

更新多表记录

底薪低于平均的加100元

update t_emp e
join (select avg(sal) avg from t_emp) t
on e.sal<t.avg
set sal=sal+100;

查 select

子句查询执行顺序：from-where-group by-having-select-order by-limit

查看数据

select * from tb;

查看指定列数据

select col_1 c1,col_2/10 c2 from tb;

条件查询 where

where分组前过滤，having分组后过滤

select col_1 from tb where 条件 and/or 条件;

多列筛选

where (col_1,col_2) in (select col_1,col_2 from tb1)

薪水在4000到6000间

salary between 4000 and 6000

模糊查询：第二个字母是J开头（_：表示一个字符；%：表示任意个字符）

name like "_j%"

指定部门内

deptno in (10,20,30)

正则表达式（匹配中文名）

name regexp '^[\\u4e00-\\u9fa5]{2,4}$'

聚合 group by

整列聚合 count / sum / avg / max / min

select sum(col) from tb;

一共多少行 count(*)

select count(*) from tb;

仅含每组第一条数据（不常用）

select * from tb group by col_1,col_2 [asc/desc];

where分组前过滤，having分组后过滤（常用）

select sex , type , sum(col) from tb [where 条件] group by sex , type [having 条件];

聚合集合 group_concat

select col_1 group_concat(col_2) from...group by col_1;

给各聚合分类再汇总 rollup

select...from...group by...with rollup;

排序 order by

asc(默认升序)/desc(降序)

select * from tb order by col_1 desc,col_2 desc [having 条件];

分页 limit

起始位置，偏移量

select * from tb limit 0,20;

忽略 offset

select * from tb limit 1 offset 1;

去重 distinct

多字段的组合去重

select distinct col_1,col_2 from tb;

有重复的姓名

select name,count(*) from tb group by name having count(*)>1;

null转换为0

ifnull(col_1,0)

合并 union

字段需一一对应 union：合并结果去重；union all：合并结果不去重；

select a.name,a.age from tb1 a union [all] select b.name,b.age from tb2 b;

行转列

多行 转 多列

select sum(case when sex='male' then numbers end) as 'male',
sum(case when sex='female' then numbers end) as 'female' from tb;

连接

连接条件

e.deptno=d.deptno # 两表的相同字段
e.sal between s.low and s.high # 表1的字段在表2的两字段值之间

内连接 join

select tb1.name,tb2.salary from tb1 join tb2 on tb1.name=tb2.name [where 条件];

左/右外连接 left/right join

显示左表全部和右表符合连接的记录 / 显示右表全部和左表符合连接的记录

select * from tb1 left join tb2 on tb1.name=tb2.name;

外连接 outer join

select * from tb1 outer join tb2 on tb1.name=tb2.name;

交叉连接 cross join

也称笛卡尔连接。两表相乘（行数 = tb1行数 * tb2行数）

select * from tb1 cross join tb2;

子查询推荐在from子句中，不推荐在where子句中，因为会执行多次

自建表放在from子句中连接（大于平均薪水的员工）

select e.ename
from t_emp e join (select avg(sal) avg from t_emp) t 
on e.sal>t.avg;

子句和表连接的区别

子句（找出tb表中含有tb1表里的id的name，只要tb表内name不重复，就不会有重复）

select name from tb where id in (select id from tb1);

表连接（tb1表的id可能会重复，因此需去重）

select distinct name from tb a join tb1 b on a.id=b.id