mysql_02CRUD_easy

select

select distinct id,gender from student;   -- id,gender都不相同

select * from student where id in(1, 3, 8);select * from student where id between 3 and 8 and gender = 1;

null 和 “” 是不同的，null不占内存，“”是空字符传，占内存select * from student where birthday is null;is not null

聚合函数

select count(*) from student where idDelete = 0;        -- 先where，再count(*)select max(id) ...select min(id) ...select sum(id) ...select avg(id) ...select * from student where id = (select max(id) from student where idDelete = 0);        -- 子查询

分组 — 也是为了聚合

select gender, count(*) from student group by gender;        -- group by 后面的字段必须是selectselect gender, count(*) as rs from student group by gender having rs > 2;        -- 根据性别分组，选出分组中记录数大于2的那(些)组

排序 order by

分页

select * from student limit start, count;    select * from student limit 1, 3; -- 展示2、3、4条记录

已知：每个显示m条数据，当前是第n页，n从1开始，(注意：start是从0开始的)

select * from student where isDelete = 0 limit (n-1)*m, m;

连接

select student.name, subject.title, scores.scorefrom scoresinner join student on score.stuid=student.idinner join subject on score.subid=subject.id

A inner join B -- A\B中完全匹配的才会出现A left join B -- 以A为准，A中数据完全出现，B中匹配不到的数据显示null