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 后面的字段必须是select


select 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.score
from scores
inner join student on score.stuid=student.id
inner join subject on score.subid=subject.id

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