排序
为了方便查看数据,可以对数据进行排序
--语法:
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
select * from students where gender='男' order by high desc;
--说明
- 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
- 默认按照列值从小到大排列(asc)
- asc从小到大排列,即升序
- desc从大到小排序,即降序
实例:
# order by 字段
# asc从小到大排列,即升序 默认
# desc从大到小排序,即降序
# 查询年龄在18到26岁之间的男同学,按照年龄从小到到排序
select * from students where (age between 18 and 26) and gender=1;
select * from students where (age between 18 and 26) and gender=1 order by age;
select * from students where (age between 18 and 26) and gender=1 order by age asc;
# 查询年龄在18到26岁之间的女同学,身高从高到矮排序
select * from students where (age between 18 and 26) and gender=2 order by id desc;
分组查询
### group by
1. group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
2. group by可用于单个字段分组,也可用于多个字段分组
### group by + group_concat()
1. group\_concat(字段名)可以作为一个输出字段来使用,
2. 表示分组之后,根据分组结果,使用group\_concat()来放置每一组的某字段的值的集合
### group by + 集合函数
通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个`值的集合`做一些操作
### group by + having
1. having 条件表达式:用来分组查询后指定一些条件来输出查询结果
2. having作用和where一样,但having只能用于group by
实例:
#group by
#select ... from students group by gender;
#真正能区分这个组的字段,这里的name无法做到,这是个错误示范
select name from students group by gender; -- 字段常用聚合函数
#按照性别分组,查询所有的性别
select gender from students group by gender;
#计算男生和女生中的人数
select count(*) from students group by gender;
select gender as 性别,count(*) from students group by gender;
select gender as 性别,count(*) from students where gender = 1;
select count(*) from students where gender = 2;
#男女同学最大年龄
select gender as 性别,max(age) from students group by gender;
#group_concat(...) 查看组内的信息
#查询同种性别中的姓名
select gender as 性别,group_concat(name) from students group by gender;
#查询组内年龄,姓名 'a' + b + 'c' b 变量
select gender as 性别,group_concat('姓名:',name,'-','年龄:',age) from students group by gender;
#分组之后的筛选 having
#查询男生女生总数大于2
select gender,count(*) from students group by gender having count(*)>2;
#查询男生女生总数大于2的姓名
select gender,count(*),group_concat(name) from students group by gender having count(*)>2;
#查询平均年龄超过18岁的性别,以及姓名 having avg(age) > 18
select gender,group_concat(name),avg(age) from students group by gender having avg(age) > 18;
# order by 多个字段
# 查询年龄在18到28岁之间的男性,年龄从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
# 排序的字段 相同了
# 先按照 年龄排序 如果年龄相同 在按照身高进行排序
select * from students where (age between 18 and 28) and gender=1 order by age desc,hiht asc;
获取部分行(分页查询)
-- 当数据量过大时,在一页中查看数据是一件非常麻烦的事情
# 语法
select * from 表名 limit start,count
# 说明:从start开始,获取count条数据
实例:
# 分页
# limit start, count 起始的位置(从0开始), 个数
# 限制查询出来的数据个数
select * from students limit 2;
# 查询前5个数据
select * from students limit 5;
# 查询id 6-10(包含)的数据
select * from students limit 5,5;
# 制作分页
# 每页显示2个,第1个页面
select * from students limit 0,2;
# 每页显示2个,第2个页面
select * from students limit 2,2;
# 每页显示2个,第3个页面
select * from students limit 4,2;
# 每页显示2个,第4个页面
select * from students limit 6,2;
-- limit 第三页的话 limit (第N页-1)*每页显示的个数,每页显示的个数
-- select * from students limit 2*(6-1),2; 错误的
-- select gender from students limit 0,2 group by gender ; 只能写到最后
-- 限制显示的条数
连接查询
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回mysql支持三种类型的连接查询,分别为:
- 左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
- 右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
# 语法
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
实例:
# 内连接查询 inner join ... on
# select ... from 表A inner join 表B;
select * from students inner join classes;
# 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;
# 按照要求显示姓名、班级
select students.*,classes.name from students inner join classes on students.cls_id = classes.id;
# 给数据表起名字
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;
# 查询 有能够对应班级的学生以及班级信息,显示学生的所有信息,只显示班级名称
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;
# 在以上的查询中,将班级姓名显示在第1列
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id;
# 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name;
# 当同一个班级的时候,按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name,s.id asc;
# 左连接查询 left join
# 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;
select * from classes left join students on students.cls_id = classes.id;
# 查询没有对应班级信息的学生
# select ... from xxx as s left join xxx as c on..... having .....
# select * from students as s left join classes as c on s.cls_id = c.id where c.id is null;
select * from students as s left join classes as c on s.cls_id = c.id having c.id is null;
# 又连接查询right join on
# 一般将数据表名字互换位置,用left join完成
子查询
# 解析:在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句
# 查询最高的男生信息
select * from students where higt = 170;
select max(higt) from students where gender = '男';
select * from students where higt = (select max(higt) from students where gender = '男');
# select * from students where higt = 170.4;
select * from students where higt = (select max(higt) from students) and gender='男';
# select * from students where higt = 175.4;
select * from students where higt = (select max(higt) from students where gender='男') and gender='男';
# 查询出高于平均身高的信息
select * from students where higt > (select avg(higt) from students);
# 列级子查询
# 查询学生的班级号能够对应的学生信息
select * from students where cls_id in (select id from classes);