23.MySQL--聚合函数、内连接、外连接、多表连接、子查询

MySQL_Day03

第零节：内容回顾

数据类型：数值类型 时间类型 字符串类型

数值类型：

• 整数类型
• 浮点类型–不精确的
• DECIMAL 确定数据类型

时间：

• DATE
• TIME
• DATETIME
• TIMESTAMP -时间戳

字符串：

• char
• varchar(长度)
• text

约束：

• 非空 not null

• 唯一 unique

• 主键 primary key

• 自增 auto_increment

• 默认值 default

• 注释comment

建表及对表的操作：

• ALERT TABLE 表名

• 重命名：RENAME AS

• 新增 ADD

• 重命名列：CHANGE

• 更改 MODIFY

• 删除 DROP

  1.删除数据库： DROP 数据库； 彻底删除数据库 ： TRUNCATE 数据库;

  2.删除表： DROP TABLE [IF EXISTS] 表名; 彻底删除表 ： TRUNCATE 表名;

  3.删除字段： 1.ALTER TABLE 表名 DROP 字段名； 2.DELETE FROM 表名 WHERE 条件;

表数据的操作：

1.新增 INSERT INTO

2.修改 UPDATE 表名 SET

3.删除 DELETE FROM 表名

4.TRUNCATE 表名

查询 SELECT 字段 FROM 表名：

1.条件 WHERE

2.分组 GROUP BY WHERE (满足条件就参与分组) HAVING （分完组在删选）

3.排序 ORDER BY 升序 ASC 降序DESC

4.聚合 COUNT SUM MAX MIN AVG

第一节：聚合函数及其问题

本节目标：

• 理解聚合函数对null的处理特点及解决方案
• 掌握DISTINCT的使用

聚合函数问题：

1.DISTINCT：用于去重

select 聚合函数(distinct(字段)) from  表名;
select count(distinct(name))  from  表名;

2、聚合函数在使用时，数据为null的时候会自动过滤。（显示null数据）

select 聚合函数(ifnull(字段名,0))  from  表名;
select avg(ifnull(age,0))  from  user;

第二节：limit关键字使用

本节目标

• 掌握limit的基本使用
• 理解分页查询的应用场景
• 掌握分页查询写法

我们可以通过limit限制查询的行数。

使用：

SELECT * FROM 表名 LIMIT (起始行-1，条数)//注意：行的计数从0开始

基本使用：

1、可以查指定某行到某行 起始行数-1 ，结束行-起始行-1

SELECT * FROM student_query LIMIT3,2

2、可以查询前几行 传需要查询的行数 默认就会从第0行还是查

SELECT * FROM student_query ORDER BY math ASC LIMIT 3

3、用于分页查询

什么是分页：我们的数据在前台展示的时候，一页显示不完了，就需要对数据进行分页

如：淘宝–很多商品–对数据进行分页–每一页显示合适的条数

思考：分页里面 每一页显示多少条数据–第二个参数 页数：我在第一页的时候 0-每一页条数-1 我在第二页的时候 每一页条数-每一页条数*2-1

递推：前台给我传第几页 pagenum 每一页需要展示的条数 pagesize

我们通过limit做分页查询 参数怎么算：(pagenum-1)*pagesize pagesize

SELECT * FROM 表名 LIMIT (pagenum-1)*pagesize ,pagesize

此处：pagenum 查询第几页

​ pagesize 每一页显示的条数

第三节：连接查询

本节目标

• 理解多表查询的应用场景
• 掌握多表查询的分类方式
• 理解笛卡尔积现象
• 理解三种内连接区别及应用场景
• 理解两种外连接特点及应用场景
• 掌握子查询使用
• 掌握union合并结果集

环境搭建：

1、通过

运行指定SQL文件导入外部SQL数据

分析表：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ehLDKhUf-1628330016754)(https://raw.githubusercontent.com/li573925122/image/master/img/20210805142845.png)]

3.1、连接查询的应用场景

需求：查询某个员工的名字及其所属部门的名称。

分析：1、需要哪些字段 员工名字 部门的名称

​ 2、从哪里拿 员工表 部门表

我们需要查询的字段来自于多个表，我们就需要将多个表连接起来进行查询，这种操作我们称为连接查询。

SELECT * FROM emp,dept;

上述这种现象（一个表中的一行数据分别和另一个表中的每一行数据都做了连接）我们笛卡尔积现象。（是由笛卡尔发现的，本质是一个数学现象）

3.2、内连接

SELECT * FROM 表名1 别名1 [INNER] JOIN 表名2 别名2 ON 连接条件

1、等值连接

查询每个员工所在部门名称，显示员工名和部门名？

SELECT 字段 表名1，表名2 WHERE 连接条件

SELECT * FROM emp,dept WHERE 
emp.deptno = dept.deptno
-- 获取制定字段
SELECT ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 上述方式弊端 每次取值的时候都要去从连接起来的表中 去筛选字段
-- 指定表名
SELECT emp.ename,dept.dname FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 在做连接查询的时候我们往往会对表取别名
SELECT e.ename,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno; -- SQL92语法  1992年制定语法 我们现在常用SQL99  99年制定语法

采用SQL99的语法：

SELECT e.ename,d.dname FROM emp e 
INNER JOIN 
dept d
ON
e.deptno = d.deptno;

2、非等值连接

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

1、需要什么：员工名 薪资 薪资等级

2、从哪拿 员工表 薪资等级表

3、条件 范围

SELECT * FROM emp e
JOIN 
salarylevel s
ON 
e.salary BETWEEN s.lowsal AND s.higsal

注意：ON后面跟条件，作用用于筛选前面做连接产生的笛卡尔积的表

3、自连接

查询员工的上级领导，要求显示员工名和对应的领导名？

1、查什么 员工的名字 领导名字

2、来自哪里 员工表 员工表

SELECT e