【MYSQL中级篇】数据库数据查询学习

🍁博主简介

  🏅云计算领域优质创作者
  🏅华为云开发者社区专家博主
  🏅阿里云开发者社区专家博主
💊交流社区：运维交流社区 欢迎大家的加入！

相关文章

文章名	文章地址
【MYSQL初级篇】入门学习【增删改查-库表数据】	https://liucy.blog.csdn.net/article/details/128396592
【MYSQL中级篇】数据库数据查询学习	https://liucy.blog.csdn.net/article/details/128717294

前言

没有安装mysql的，大家可参考【 Centos7安装Mysql5.7（超详细版）】、【【云原生】Docker之创建并进入mysql容器】 两种方式任选其一来安装；

SQL语句

• DDL 数据定义语言 create drop alter
• DML 数据操纵语言 update delete insert
• DQL 数据查询语言 select
• DCL 数据控制语言 grant revoke

排序查询

• desc 降序
• asc 升序
• limit 1 取第一行

mysql order by排序默认为升序（从小到大）

#排序；默认为升序
select * from 表名 order by id;（根据id排序）#取行数
select * from 表名 limit 偏移量,行数;

#根据score排序，默认为升序
select * from student order by score;#根据id排序，升序（从小到大）
select * from student order by id asc;#根据score排序，降序（从大到小）
select * from student order by score desc;#偏移量为2，取5行；（取第3行到第7行）
select * from student limit 2,5;

分组查询

select * from 表名 group by 字段;

#按分数打包分组(打包相同的分数)
select * from student group by score;

如遇到以下报错，可了解 https://blog.csdn.net/weixin_44013783/article/details/119422353、https://blog.csdn.net/W_317/article/details/116723943

去重查询

select distinct 字段 from 表名;

#去除相同的分数
select distinct score from student;
select distinct score as '分数' from student;

聚合函数

• max()  最大值

select max(字段) from 表名;

#查找最高的分数，显示姓名和分数
select name as '姓名',max(score) as '分数' from student;+----+-------+
| 姓名 | 分数 |
+----+-------+
| 老六 | 125 |
+----+-------+

• min()  最小值

select min(字段) from 表名;

#查找最底的分数，显示姓名、性别、年龄、分数；
select name as '姓名',sex as '性别',age as '年龄',min(score) as '分数' from student;+-----+----+-----+-------+
| 姓名 | 性别 | 年龄 | 分数 |
+-----+----+-----+-------+
| 王五 |  男  | 15 |  100 |
+-----+----+-----+-------+

• sum()  和

select sum(字段) from 表名;

#查询班级的总分
select sum(score) as '班级总分' from student;+---------+
| 班级总分 |
+---------+
|  1546   |
+---------+

• count()  统计

select count(字段) from 表名;

#统计班级有多少个人
select count(*) as '班级总人数' from student;+----------+
| 班级总人数 |
+----------+
|    13    |
+----------+

• avg()  平均数

select avg(字段) from 表名;

#计算班级总分的平均分
select avg(score) as '班级平均分' from student;+----------+
| 班级平均分 |
+----------+
| 118.9231 |
+----------+

综合：

#统计班级的总人数、总分及平均分
select count(*) as '班级总人数',sum(score) as '班级总分',avg(score) as '班级平均分' from student;+---------+----------+----------+
| 班级总人数 | 班级总分 | 班级平均分 |
+---------+----------+----------+
|    13    |   1546  | 118.9231 |
+---------+----------+----------+

• with rollup  对聚合结果进行汇总

使用 with rollup，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。

#按分数分组，并计算出各组人数、总分、平均分，在做全部统计
select count(*),sum(score),avg(score) from student group by score with rollup;
select count(*) as '各组人数',sum(score) as '各组总分',avg(score) as '各组平均分' from student group by score with rollup;+---------+---------+----------+
| 各组人数 | 各组总分 | 各组平均分 |
+---------+---------+----------+
|       2 |     246 | 123.0000 |
|       3 |     360 | 120.0000 |
|       2 |     242 | 121.0000 |
|       2 |     200 | 100.0000 |
|       2 |     248 | 124.0000 |
|       2 |     250 | 125.0000 |
|      13 |    1546 | 118.9231 |
+---------+---------+----------+

• having    对于聚合后的结果进行过滤，如果逻辑允许，多用where

#查询分数小于120的人
select * from student having score<120;

• where   用于聚合前，having用于聚合后。

#查询性别为女的人
select * from student where sex='女';

比较运算符查询

• 等于: =

#查询性别是男的学生
select * from student where sex='男';

• 大于: >

#查询年龄大于15的学生
select * from student where age>15;

• 大于等于: >=

#查询年龄大于等于15的学生
select * from student where age>=15;

• 小于: <

#查询分数小于122的学生
select * from student where score<122;

• 小于等于: <=

#查询分数小于等于122的学生
select * from student where score<=122;

• 不等于: != 或 <>

#查询学生性别不是男生的
select * from student where sex!='男';

#查询学生性别不是女生的
select * from student where sex<>'女';

逻辑运算符查询

• and 和，要同时符合这两个条件或多个条件；

#查询学生分数高于122的男同学
select * from student where score>122 and sex='男';

• or 或，满足其中一个条件即可，两者都有则都输出；

#查询年龄大于18或小于20的学生
select * from student where age>18 or age<20;

• not 不，不要指定条件的数据；

#查询分数不在120到123的学生
select * from student where not (score>=120 and score<=123);

多个条件判断想要作为一个整体的时候，可以使用()。

模糊查询

新增3条模糊查询要用到的数据

insert into student values (null,'张九','男',15,7.12,'北京市朝阳区某小区1号楼1单元103','[e-mail]',16839217282,122);
insert into student values (null,'张时嘉','女',17,12.12,'北京市朝阳区某小区1号楼1单元102','[e-mail]',13307189235,124.5),(null,'张时依','女',17,12.12,'北京市朝阳区某小区1号楼1单元102','[e-mail]',13307189236,125);

• like是模糊查询关键字

• %表示任意多个任意字符

• _表示一个任意字符

例1：查询姓张的学生都有谁

#查询姓张的学生都有谁
select name from student where name like "张%";

例2：查询姓张的两个字的学生都有谁

#查询姓张的两个字的学生都有谁
select * from student where name like "张_";

例3：查询三个字的学生都有谁

#查询三个字的学生都有谁
select * from student where name like "___";

例4：查询手机号以16开头的学生

#查询手机号以16开头的学生
select * from student where iphone like "16%";

例5：查询姓老的或是名字中带时的都有谁

#查询姓老的或是名字中带时的都有谁
select * from student where name like "老%" or name like "%时%";

范围查询

• between … and … 表示在一个连续的范围内查询
• in 表示在一个非连续的范围内查询

例1：查询生日6月01日到9月31日的学生

#查询生日6月01日到9月31日的学生
select * from student where birthday between "6.01"and"9.31";

例2：查询地址为1单元103到109之间的男同学

#查询地址为1单元103到109之间的男同学
select * from student where (address between "北京市朝阳区某小区1号楼1单元103"and"北京市朝阳区某小区1号楼1单元109") and sex='男';

例3：查询id为1和22的两个学生

#查询id为1和22的两个学生
select * from student where id in(1,22);

IN 列表项不仅支持数字，也支持字符甚至时间日期类型等，并且可以将这些不同类型的数据项混合排列而无须跟 column 的类型保持一致：
一个 IN 只能对一个字段进行范围比对，如果要指定更多字段，可以使用 AND 或 OR 逻辑运算符：
使用 AND 或 OR 逻辑运算符后，IN 还可以和其他如 LIKE、>=、= 等运算符一起使用。

空判断查询

• 判断为空使用: is null
• 判断非空使用: is not null

#查询邮件是空的学生
select * from student where email is null;

#查询邮件不是空的学生
select * from student where email is not null;

不能使用 where height = null 判断为空
不能使用 where height != null 判断非空
null 不等于 空字符串

多表联查

新增一个表

create table body (id int(3) ZEROFILL PRIMARY KEY AUTO_INCREMENT comment'id',name varchar(255) not null comment'姓名',height varchar(255) comment'身高',weight varchar(255) not null comment'体重',heartbeat int comment'心跳/每分钟')comment='学生身体表';

新增数据

insert into body values (null,'张三','177.2','140.4',99),(null,'李四','180','149.2',80),(null,'王五','167.9','141',80),(null,'老六','173','128',77),(null,'小七','159.5','90.8',89),(null,'老八','169.9','145.2',78),(null,'张九','169','120.5',82),(null,'张时嘉','168','83',79),(null,'张时依','168.3','83.4',82);

两表联查内连接

两表联查必须加上where 表1 id=表2 id，否则查询出来会有很多重复的数据，你写了几个字段，就会查询到多少字段的重复数据；

内连接格式：A inner join B on 条件

例子：
select * from A inner join B on 条件；
select * from A inner join B on 条件 where 条件；
select * from A inner join B on 条件 where 条件 having 条件；
select * from A inner join B on 条件 order by 字段；
select * from A inner join B on 条件 where 条件 order by 字段；
select * from A inner join B on 条件 where 条件 having 条件 order by 字段；

例1：查询student表姓名、性别、年龄，body表身高、体重、心跳；

#两表联查：查询student表姓名、性别、年龄，body表身高、体重、心跳；
select student.name as '姓名',student.sex as '性别',student.age as '年龄',body.height as '身高',body.weight as '体重',body.heartbeat as '心跳/每分钟' from student,body where student.id=body.id;#别名两表联查：查询student表姓名、性别、年龄，body表身高、体重、心跳；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟' from student a,body b where a.id=b.id;

例2：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的学生；

#别名两表联查：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的学生；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a,body b where a.id=b.id having score>=122;

例3：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生；

#别名两表联查内连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a inner join body b on a.id=b.id where score>=122 having sex='女';

例4：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生且从大到小排序；

#别名两表联查内连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生且从大到小排序；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a inner join body b on a.id=b.id where score>=122 having sex='女' order by score desc;

两表联查左连接

两表联查必须加上where 表1 id=表2 id，否则查询出来会有很多重复的数据，你写了几个字段，就会查询到多少字段的重复数据；

内连接格式：A left join B on 条件

例子：
select * from A left join B on 条件；
select * from A left join B on 条件 where 条件；
select * from A left join B on 条件 where 条件 having 条件；
select * from A left join B on 条件 order by 字段；
select * from A left join B on 条件 where 条件 order by 字段；
select * from A left join B on 条件 where 条件 having 条件 order by 字段；

例1：查询student表姓名、性别、年龄，body表身高；

#别名两表联查左连接：查询student表姓名、性别、年龄，body表身高；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高' from student a left join body b on a.id=b.id;

例2：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的学生；

#别名两表联查左连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的学生；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a left join body b on a.id=b.id where score>=122;

例3：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生；

#别名两表联查左连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a left join body b on a.id=b.id where score>=122 having sex='女';

例4：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生且从大到小排序；

#别名两表联查左连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生且从大到小排序；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a left join body b on a.id=b.id where score>=122 having sex='女' order by score desc;

两表联查右连接

两表联查必须加上where 表1 id=表2 id，否则查询出来会有很多重复的数据，你写了几个字段，就会查询到多少字段的重复数据；

内连接格式：A right join B on 条件

例子：
select * from A right join B on 条件；
select * from A right join B on 条件 where 条件；
select * from A right join B on 条件 where 条件 having 条件；
select * from A right join B on 条件 order by 字段；
select * from A right join B on 条件 where 条件 order by 字段；
select * from A right join B on 条件 where 条件 having 条件 order by 字段；

例1：查询student表姓名、性别、年龄，body表身高；

#别名两表联查右连接：查询student表姓名、性别、年龄，body表身高；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高' from student a right join body b on a.id=b.id;

例2：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的学生；

#别名两表联查右连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的学生；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a right join body b on a.id=b.id where score>=122;

例3：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生；

#别名两表联查右连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a right join body b on a.id=b.id where score>=122 having sex='女';

例4：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生且从大到小排序；

#别名两表联查右连接：查询student表姓名、性别、年龄、分数，body表身高、体重、心跳，并只显示成绩大于等于122的女学生且从大到小排序；
select a.name as '姓名',a.sex as '性别',a.age as '年龄',b.height as '身高',b.weight as '体重',b.heartbeat as '心跳/每分钟',a.score as '分数' from student a right join body b on a.id=b.id where score>=122 having sex='女' order by score desc;

全连接: union或union all

注意：
1.两张表的数据数量必须相同
2.全连接内使用order by 没有效果，可以对连接后的结果进行排序；
3.union会合并相同的数据；

select * from 表1 union all select * from 表2;

select * from student1 union all select * from student2;