内容导读
排序与分组及分页
MySQL关联查询
MySQL子查询
MySQL的DML应用
一、排序与分组及分页
1.1 排序order by
将查询到的满足条件的记录按照指定的列的值升序/降序排列
语法：select * from 表名 where 条件 order by 列名 asc|desc;
order by 列名，表示将查询结果按照指定的列排序
asc 按照指定的列升序（默认）
desc 按照指定的列降序
# 单字段排序
select * from stus where stu_age>15 order by stu_gender desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210101 | omg | 男 | 21 | 13030303300 | NULL |
| 20210103 | Tom | 男 | 20 | 13030303302 | 777777 |
| 20210105 | Polly | 男 | 21 | 13030303304 | 666666 |
| 20210106 | Theo | 男 | 17 | 13232323322 | NULL |
| 20210102 | Peter | 女 | 18 | 13131313311 | 999999 |
| 20210104 | Lucy | 女 | 21 | 13131323334 | NULL |
+----------+-----------+------------+---------+-------------+--------+
# 多字段排序 ： 先满足第一个排序规则，当第一个排序的列的值相同时再按照第二个列的规则排序
select * from stus where stu_age>15 order by stu_gender asc,stu_age desc;
+----------+-----------+------------+---------+-------------+--------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq |
+----------+-----------+------------+---------+-------------+--------+
| 20210104 | Lucy | 女 | 21 | 13131323334 | NULL |
| 20210102 | Peter | 女 | 18 | 13131313311 | 999999 |
| 20210101 | omg | 男 | 21 | 13030303300 | NULL |
| 20210105 | Polly | 男 | 21 | 13030303304 | 666666 |
| 20210103 | Tom | 男 | 20 | 13030303302 | 777777 |
| 20210106 | Theo | 男 | 17 | 13232323322 | NULL |
+----------+-----------+------------+---------+-------------+--------+1.2 分组函数group by
分组：就是将数据表中的记录按指定的列进行分组
select 分组字段/聚合函数
from 表名
[where 条件]
group by 分组列名 [having 条件]
[order by 排序字段]-
select后通常显示分组字段和聚合函数(对分组后的数据进行统计、求和、平均值等) -
语句执行顺序
（1）先根据where条件从数据库查询记录
（2）group by对查询记录进行分组
（3）执行having对分组后的数据进行筛选
# 先对查询的学生信息按性别进行分组（分成了男、女两组），然后再分别统计每组学生的个数
select stu_gender,count(stu_num) from stus group by stu_gender;
+------------+----------------+
| stu_gender | count(stu_num) |
+------------+----------------+
| 女 | 4 |
| 男 | 5 |
+------------+----------------+
# 先对查询的学生信息按性别进行分组（分成了男、女两组），然后再计算每组的平均年龄
select stu_gender,avg(stu_age) from stus group by stu_gender;
+------------+--------------+
| stu_gender | avg(stu_age) |
+------------+--------------+
| 女 | 19.7500 |
| 男 | 18.2000 |
+------------+--------------+
# 先对学生按年龄进行分组（分了16、17、18、20、21、22六组），然后统计各组的学生数量，还可以对最终的结果排序
select stu_age,count(stu_num) from stus group by stu_age order by stu_age;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 17 | 1 |
| 18 | 1 |
| 20 | 3 |
| 21 | 1 |
| 22 | 1 |
+---------+----------------+
# 查询所有学生，按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数>1的组，再按年龄升序显示出来
select stu_age,count(stu_num)
from stus
group by stu_age
having count(stu_num)>1
order by stu_age;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 20 | 3 |
+---------+----------------+
# 查询性别为'男'的学生，按年龄进行分组，然后分别统计每组的人数，再筛选当前组人数>1的组，再按年龄升序显示出来
mysql> select stu_age,count(stu_num)
-> from stus
-> where stu_gender='男'
-> group by stu_age
-> having count(stu_num)>1
-> order by stu_age;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 16 | 2 |
| 20 | 2 |
+---------+----------------+1.3 分页查询limit
当数据表中的记录比较多的时候，如果一次性全部查询出来显示给用户，用户的可读性/体验性就不太好，因此我们可以将这些数据分页进行展示。
select ...
from ...
where ...
limit param1,param2;-
param1：表示获取查询语句的结果中的第一条数据的索引（索引从0开始）
-
param2：表示获取的查询记录的条数（如果剩下的数据条数<param2，则返回剩下的所有记录）
示例如下：
对数据表中的学生信息进行分页显示，总共有10条数据，我们每页显示3条
总记录数：
count 10每页显示：
pageSize 3总页数：
pageCount=count%pageSize==0?count/pageSize:count/pageSize+1
# 查询第一页：
select * from stus [where ...] limit 0,3; #(1-1)*3
# 查询第二页：
select * from stus [where ...] limit 3,3; #(2-1)*3
# 查询第三页：
select * from stus [where ...] limit 6,3; #(3-1)*3
# 查询第四页：
select * from stus [where ...] limit 9,3; #(4-1)*3
# 如果在一张数据表中：
# pageNum表示查询的页码
# pageSize表示每页显示的条数
# 通用分页语句如下：
select * from stus [where ...] limit (pageNum-1)*pageSize,pageSize;二、MySQL关联(连接)查询
1、关联查询的作用
MySQL是一个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系，即通过在数据表中添加字段建立外键约束。
2、数据与数据之间的关联关系分为四种
一对一关联：学生基本信息表与学籍卡表，直接通过主键进行关联
一对多关联：母亲表与孩子表，需要通过主键与外键进行关联，在多的表中创建一的表的主键字段，俗 称为外键
多对一关联：同一对多关系，多个学生与年班级的关系
多对多关联：学生基本信息表与课程表，添加中间表（成绩表）进行关联
3、关联查询的类型有两种
等值(关联)查询、连接(关联)查询
2.1 等值查询
数据准备：建库建表，并完成下面的等值(关联)查询。
1、创建一对一，一对多、多对多关系的表
（1）一对一关系表，两个表设置相同的主键及相同的主键值
院系表----系主任
# 查询化学系的位置 -- SELECT dep_name,address from department where dep_name='化学系'; # 1.查询人工智能与大数据学院的地址，院长姓名，手机，电话 # 通过两表的id相等：等值连接(关联)综合查询信息 # 多个表的关联查询,为了区分多个表中的字段,一律给表取个别名,再通过别名指定字段名 # 务必设置两表id相等条件 select de.dep_name,di.`name`,de.address,di.mobile,de.tel from department de,director di where de.id=di.id and de.dep_name='人工智能与大数据学院';（2）一对多关系表，分清一个表的主键与另一个表的外键的关系
职员表----银行卡表
### 2.一对多查询：查询工号为1002员工的工号、姓名、性别、帐号与银行类型 # 务必设置两表主外键相等条件 #2.1 查询所有员工的所有银行卡 select e.id,e.`name`,e.gender,b.account,b.type from employee e,bank_card b where e.id=b.emp_id; #2.2 查询工号为1002员工的工号、姓名、性别、帐号与银行类型 select e.id,e.`name`,e.gender,b.account,b.type from employee e,bank_card b where e.id=b.emp_id and e.id=1001;（3）多对多关系表，一律给两个表加个中间表
装备表(equip)----玩家表(player)
两张中间设置一个中间表：经验表exp(equip_id,player_id,skill)
# 查询化学系的位置 -- SELECT dep_name,address from department where dep_name='化学系'; ### 1.一对一查询：人工智能与大数据学院的地址，院长姓名，手机，电话 # 通过两表的id相等：等值连接(关联)综合查询信息 # 多个表的关联查询,为了区分多个表中的字段,一律给表取个别名,再通过别名指定字段名 # 务必设置两表id相等条件 -- select de.dep_name,di.`name`,de.address,di.mobile,de.tel from department de,director di where de.id=di.id and de.dep_name='人工智能与大数据学院'; ### 2.一对多查询：查询工号为1002员工的工号、姓名、性别、帐号与银行类型 # 务必设置两表主外键相等条件 #2.1 查询所有员工的所有银行卡 -- select e.id,e.`name`,e.gender,b.account,b.type from employee e,bank_card b where e.id=b.emp_id; #2.2 查询工号为1002员工的工号、姓名、性别、帐号与银行类型 -- select e.id,e.`name`,e.gender,b.account,b.type from employee e,bank_card b where e.id=b.emp_id and e.id=1001; ### 3.多对多查询 #3.1 查询所有玩家的：姓名,性别,装备,价值,经验值 # 务必设置三个表主外键相等条件 SELECT p.`name`,p.gender,eq.equ_name,eq.`value`,e.skill from player as p,exp e,equip eq where p.id=e.player_id and e.equip_id=eq.id ORDER BY p.`name`; #3.2查询玩家姓名为tom的：姓名,性别,装备,价值,经验值 # 务必设置三个表主外键相等条件 SELECT p.`name`,p.gender,eq.equ_name,eq.`value`,e.skill from player as p,exp e,equip eq where p.id=e.player_id and e.equip_id=eq.id and p.`name`='tom';
2.2 连接查询
1、通过对DQL的学习，我们可以很轻松的从一张数据表中查询出需要的数据
在企业的应用开发中，我们经常需要从多张表中查询数据
例如：我们查询学生信息的时候需要同时查询学生的班级信息，可以通过连接查询从多张数据表提取数据
2、在MySQL中可以使用join实现多表的联合查询——连接查询，join按照其功能不同分为三种连接查询
（1）inner join 内连接
（2）left join 左连接
（3）right join 右连接
2.2.1 数据准备
创建班级表和学生表
create table classes(
class_id int primary key auto_increment,
class_name varchar(40) not null unique,
class_remark varchar(200)
);
create table students(
stu_num char(8) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null,
cid int,
constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE
);添加班级数据
# Java2104 包含三个学生信息
insert into classes(class_name,class_remark) values('Java2104','...');
# Java2105 包含两个学生信息
insert into classes(class_name,class_remark) values('Java2105','...');
# 以下两个班级在学生表中没有对应的学生信息
insert into classes(class_name,class_remark) values('Java2106','...');
insert into classes(class_name,class_remark) values('Python2105','...');添加学生数据
# 以下三个学生信息 属于 class_id=1 的班级 （Java2104）
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210101','张三','男',20,1);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210102','李四','女',20,1);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210103','王五','男',20,1);
# 以下三个学生信息 属于 class_id=2 的班级 （Java2105）
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210104','赵柳','女',20,2);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210105','孙七','男',20,2);
# 小红和小明没有设置班级信息
insert into students(stu_num,stu_name,stu_gender,stu_age) values('20210106','小红','女',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('20210107','小明','男',20);2.2.2 内连接查询inner join
语法：
select ... from 表1 inner join 表2 on 匹配条件 [where 筛选条件];
2.2.2.1 笛卡尔积
笛卡尔积：使用A中的每个记录一次关联B中每个记录，笛卡尔积的总数=A总数*B总数。
如果直接执行
select ... from 表1 inner join 表2;会获取两种数据表中的数据集合的笛卡尔积（依次使用表1中的每一条记录去匹配表2的每条数据）。不建议使用。
2.2.2.2 内连接条件
（1）两张表时用inner join连接查询之后生产的笛卡尔积数据中很多数据都是无意义的，我们可以添加两张进行连接查询时的条件来消除无意义的数据
（2）使用
on设置两张表连接查询的匹配条件，查询结果只获取两种表中匹配条件成立的数据，任何一张表在另一种表如果没有找到对应匹配则不会出现在查询结果中
# 不建议使用，where设置过滤条件：先生成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from students INNER JOIN classes where students.cid = classes.class_id;
# 建议使用，ON设置连接查询条件：先判断连接条件是否成立，如果成立两张表的数据进行组合生成一条结果记录
select * from students INNER JOIN classes ON students.cid = classes.class_id;2.2.3 左连接left join
需求：请查询出所有的学生信息，如果学生有对应的班级信息，则将对应的班级信息也查询出来。
左连接：显示左表中的所有数据，如果在有右表中存在与左表记录满足匹配条件的数据，则进行匹配；如果右表中不存在匹配数据，则显示为null。
# 语法
select * from leftTabel LEFT JOIN rightTable ON 匹配条件 [where 条件];
# 左连接 : 显示左表中的所有记录，右表中的不匹配的显示null
select * from students LEFT JOIN classes ON students.cid = classes.class_id;2.3.4 右连接right join
右连接：显示右表中的所有数据，如果在有左表中存在与右表记录满足匹配条件的数据，则进行匹配；如果左表中不存在匹配数据，则显示为null。
# 右连接 ：显示右表中的所有记录，左表中的不匹配的显示null
select * from students RIGHT JOIN classes ON students.cid = classes.class_id;2.3.5 数据表别名
如果在连接查询的多张表中存在相同名字的字段，我们可以使用
表名.字段名来进行区分，如果表名太长则不便于SQL语句的编写，我们可以使用数据表别名
select s.*,c.class_name from students s
INNER JOIN classes c ON s.cid = c.class_id;三、MySQL子查询/嵌套查询
子查询的作用：
实现更复杂查询，先进行一次查询，第一次查询的结果作为第二次查询的条件(第二次查询是基于第一次的查询结果来进行的)
子查询返回单个值（单行单列）
比如：查询班级名称为'Java2305'班级中的学生信息
# 如果子查询返回的结果是一个值（单列单行），条件可以直接使用关系运算符（= != ....）
select * from students where cid = (select class_id from classes where class_name='Java2305');子查询返回多个值（多行单列）
比如：查询所有Java班级中的学生信息
# 如果子查询返回的结果是多个值（单列多行），条件使用IN / NOT IN
select * from students where cid IN (select class_id from classes where class_name LIKE 'Java%');子查询返回多个值（多行多列）
比如：查询cid=1的班级中性别为男的学生信息
# 先查询cid=1班级中的所有学生信息，将这些信息作为一个整体虚拟表(多行多列)，再基于这个虚拟表查询# 性别为男的学生信息（‘虚拟表’需要别名）
select * from (select * from students where cid=1) t where t.stu_gender='男';四、MySQL的DML应用
用于完成对数据表(一般为单表)中数据的插入、删除、修改操作
4.1 插入数据
语法：
insert into 表名(列名1, 列名2....) values(值1,值2....);
# 向数据表中指定的列添加数据（不允许为空的列必须提供数据）
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel)
values('20210101','张三','男',21,'13030303300');
# 数据表名后的字段名列表顺序可以不与表中一致，但是values中值的顺序必须与表名后字段名顺序对应
insert into stus(stu_num,stu_name,stu_age,stu_tel,stu_gender)
values('20210103','王五',20,'13030303302','女');
# 当要向表中的所有列添加数据时，数据表名后面的字段列表可以省略，但是values中的值的顺序要与数据表定义的字段保持一致;
insert into stus values('20210105','孙琦','男',21,'13030303304','666666');
# 不过在项目开发中，即使要向所有列添加数据,也建议将列名的列表显式写出来(增强SQL的稳定性)
insert into stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq)
values('20210105','孙琦','男',21,'13030303304','666666');4.2 删除数据
语法：
delete from 表名 [where 条件];
# 删除学号为20210102的学生信息
delete from stus where stu_num='20210102';
# 删除年龄大于20岁的学生信息(如果满足where子句的记录有多条，则删除多条记录)
delete from stus where stu_age>20;
# 如果删除语句没有where子句，则表示删除当前数据表中的所有记录(敏感操作)
delete from stus;4.3 修改数据
语法：
update 表名 set 列名1=值1，列名2=值2 [where 条件];
# 将学号为20210105的学生姓名修改为“孙七”（只修改一列）
update stus set stu_name='孙七' where stu_num='20210105';
# 将学号为20210103的学生 性别修改为“男”,同时将QQ修改为 777777（修改多列）
update stus set stu_gender='男',stu_qq='777777' where stu_num='20210103';
# 根据主键修改其他所有列
update stus set stu_name='韩梅梅',stu_gender='女',stu_age=18,stu_tel='13131313311' ,stu_qq='999999' where stu_num='20210102';
# 如果update语句没有where子句，则表示修改当前表中所有行（记录）
update stus set stu_name='Tom';随堂练习：
创建学生表，要求：
（1）向学生表中添加若干条学生记录，每条记录中包含学号、姓名、年龄、籍贯、手机号、
成绩
（2）删除不及格的学生
（3）将80分以上的男生成绩加10分
更多内容请关注本站！
扫一扫
3143
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
