277 views
Mysql

4.MySQL查询02

文章目录

排序

为了方便查看数据,可以对数据进行排序
--语法:
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);
影子专属博客 赣ICP备17013143号