MySQL查询语法

用到的数据

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