增
insert into tb1(name,age) values('alex',12),('egg',18);
insert into tb2(name,age) select name,age from tb1;
删
delete from tb1 where id>2 and name='alex'
改
update tb2 set name='alex',age=19 where id>12 and name='xx'
查
select count(id) from tb1; # 所有项个数
select id,name from tb1;
select id,name from tb1 where id>12 or name='alex';
select id,name as cname from tb1 where id>12 or name='alex';
select name,age,11,(select count(1) from tb1) from tb2 # 11是常量,select count(1) from tb1是一个值
SELECT
student_id,
(select num from score as s2 where s2.student_id = s1.student_id and course_id = 1)
from score as s1; # 嵌套
select * from tb1 where id in (1,2,3);
select * from tb1 where id not in (1,2,3);
select * from tb1 where id between 1 and 5; # 闭区间
select * from tb1 where id in (select nid from 表); # 先执行括号语句
通配符：
select * from tb1 where name like "a%"; # 后续任意字符
select * from tb1 where name like "a_"; # 后续一个字符
分页：
select * from tb1 limit 10; # 前10项
select * from tb1 limit 0,10; #从0项开始前10项
select * from tb1 limit 10,10;
select * from tb1 limit 10 offset 20; # 从20开始前10项
排序：
select * from tb1 order by id asc;
select * from tb1 order by id desc;
select * from tb1 order by id desc,age asc; # 如果id重复按age排
取后10条数据
select * from tb1 order by id desc limit 10;
分组：
select count(id),max(id),part_id from userinfo group by part_id;
筛选条件：
in, not in, btween and, !=, and, or
聚合函数：
count,max,min,sum,avg
如果对聚合函数结果进行二次筛选，必须用having
select count(id),part_id from userinfo group by part_id having count(id)>1;
可以用where但是不能跟聚合函数
select count(id),part_id from userinfo where id>0 group by part_id having count(id)>1;
连表操作：
select * from userinfo,department where userinfo.part_id = de[artment.id;
select * from userinfo left join department on userinfo.part_id = department.id;
select * from userinfo right join department on userinfo.part_id = department.id;
select * from userinfo inner join department on userinfo.part_id = department.id; # 所有null的行隐藏
五连表
select
score.sid # 这里用*容易混乱，最好用 表.id
student.sid
from
score
left jion student on score.student_id = student.sid
left jion course on score.course_id = course.cid
left jion class on student.class_id = class.cid
left jion teacher on courese.teacher_id = teacher.tid
上下连表
union # 自动去重
union all # 不去重
条件：
select course_id,case when min(num)<10 THEN 0 ELSE min(num) END from score GROUP BY course_id;
