用到的数据
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default '',
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('男','女','中性','保密') default '保密',
cls_id int unsigned default 0,
is_delete bit default 0
);
-- classes表
create table classes (
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
-- 向students表中插入多条数据 用,分开
INSERT INTO students(name,age,height,gender,cls_id,is_delete)
VALUES
( '小明', 18, 180.00, 2, 1, 0 ),
( '小月月', 18, 180.00, 2, 2, 1 ),
( '彭于晏', 29, 185.00, 1, 1, 0 ),
( '刘德华', 59, 175.00, 1, 2, 1 ),
( '黄蓉', 38, 160.00, 2, 1, 0 ),
( '凤姐', 28, 150.00, 4, 2, 1 ),
( '王祖贤', 18, 172.00, 2, 1, 1 ),
( '周杰伦', 36, NULL, 1, 1, 0 ),
( '程坤', 27, 181.00, 1, 2, 0 ),
( '刘亦菲', 25, 166.00, 2, 2, 0 ),
( '金星', 33, 162.00, 3, 3, 1 ),
( '静香', 12, 180.00, 2, 4, 0 ),
( '郭靖', 12, 170.00, 1, 4, 0 ),
( '周杰', 34, 176.00, 2, 5, 0 );
-- 向classes表中插入多条数据
insert into classes values (0, "python_01"), (0, "python_02");
排序
-- 排序 默认升序asc 降序desc
select * from students order by id;
select * from students order by id desc;
-- 先按年龄从大到小排序,相同时 ,再按身高从高到矮 用,隔开
select * from students order by age desc,height desc;
聚合
-- null不参与分组里的聚合
select count(*) from students;
select max(age) from students;
select min(age) from students;
-- round 保留2位小数
select round(avg(age),2) from students;
select sum(age) from students;
分组
select gender from students group by gender;
-- group_concat对字符串进行聚合 中间用 , 隔开
select gender,GROUP_CONCAT(name) from students group by gender;
-- having用于分组后的聚合结果的过滤,这时用where会报错
select gender,count(*) as c from students group by gender having c > 2;
-- with rollup 在最后新增一行,记录当前列所有记录的总和 having 放在 with rollup 之后
select gender,count(*) from students where gender<=2 group by gender with rollup ;
获取部分行
-- 获取部分页的部分内容 limit start,length
select * from students limit 0,3;
-- ceiling 向上取整
select ceiling(count(*)/5) from students;
-- floor 向下取整
select floor(count(*)/5) from students;
窗口函数
select *,rank() over (partition by cls_id order by age desc) as rank1,
dense_rank() over (partition by cls_id order by age desc) as dese_rank,
row_number() over (partition by cls_id order by age desc) as row_num from students;
连接查询
-- 内连接查询
select * from students inner join classes on students.cls_id = classes.id;
-- 左连接查询(以左表为主) oj网站常用
select * from students left join classes on students.cls_id = classes.id;
-- 右连接查询(以右表为主)
select * from students right join classes on students.cls_id = classes.id;
-- 查询学生姓名和班级名称
select students.name,classes.name from students inner join classes on students.cls_id = classes.id;
自关联
-- 查询省份为 河北省的 城市
select * from areas as a inner join areas as p on a.pid = p.aid where p.attitle = "河北省";
-- 查询市的名称为“广州市”的所有区县
select dis.* from areas as dis
inner join areas as city on city.aid=dis.pid
where city.attitle='广州市';
子查询
-- 标量子查询
-- 查询班级哪些学生的身高大于平均身高
select * from students where height > (select avg(height) from students);
-- 列级子查询 in
-- 查询还有学生在班的所有班级名字
select name from classes where id in (select distinct cls_id from students);
-- 行子查询
select * from students where (height,age) = (select max(height),max(age) from students);
各个关键字的使用顺序
select distinct *
from 表名
where ...
group by ... having ...
order by ...
limit start,length