增 Insert

insert into t1(id, name) value(1, '张三');
insert into t1(name, age) value('张三', 17), ('李四', 18);
insert into t1(name, age) select name, age from t2;

删 Delete

delete from t1;
delete from t1 where id = 2;
delete from t1 where id < 6 and name = '张三';

改 Update

update t1 set age = 18 where age = 17;
update t1 set name = '张四', age = 19 where id = 1;

查 Select

select * from t1;
select id, name from t1;
select id, name as cname from t1;
select id, name, 11 from t1; -- 11为额外的列
select id, name, 11, (select num from t2) as number from t1; -- 子查询语句，必须单值
select id, name, 11, (select num from t1 as b where b.student_id = a.student_id) as number from t1 as a group by a.student_id; -- 子查询语句可以使用外层表
select case when min(num) < 10 then 0 else min(num) end from t1;
select if(min(num)<10, 0, min(num)) from t1; -- 同上
select distinct name from t1; -- 去重，但效率不高，可用group by去重

使用函数查询

if(,,)
isnull()

条件查询

where

select * from t1 where id != 1;
select * from t1 where id > 10 or name = '张三';
select * from t1 where id not in(1,5,12);
select * from t1 where id between 5 and 12; -- 包含 5, 12
select * from t1 where id in (select nid from t2);

like

select * from t1 where name like 'a%';
select * from t1 where name like 'a_';

limit

select * from t1 limit 5; -- 前5行
select * from t1 limit 5 offset 2 -- 从第3行开始的5行
select * from t1 limit 2, 5; -- 从第3行开始的5行

order by

select * from t1 order by id asc;
select * from t1 order by id desc;
select * from t1 order by id asc, name desc;

group by

select count(id), part_id from t1 group by part_id;
select max(id), part_id from t1 group by part_id;
select min(id), part_id from t1 group by part_id;

聚合函数 count、max、min、sum、avg

聚合函数二次筛选，必须使用 having

select count(id), part_id from t1 group by part_id having count(id) > 1;

连表查询

左右连表 left join, right join, inner join

• where连表

select * from a1, b1 where a1.part_id = b1.id;

• left join连表

  a1是左表, b1是右表，left join为左表数据全部显示

select * from a1 left join b1 on a1.part_id = b1.id;

• right join连表

  a1是左表，b1是右表，right join为右表数据全部显示

select * from a1 right join b1 on a1.part_id = b1.id;

• inner join连表

  a1是左表, b1是右表, inner join，把出现null的行隐藏

select * from a1 inner join b1 on a1.part_id = b1.id;

上下连表 union

• union连表，去重

select id, name from tb1
union
select num, sname from tb2;

• union all连表，不去重

select id, name from tb1
union all
select num, sname from tb2;

临时表查询

select * from (select * from score where num > 60) as B;