MYSQL学习笔记（四）

港控/mmm° 2023-09-29 11:16 30阅读 0赞

**目录**

一：排序数据

二：多表连接

三：多表查询分类讲解

三：UNION的使用

四：7种SQL JOINS的实现

五：SQL99语法新特性

七：多表查询章节小结

--------------------

## 一：排序数据 ##

**1.1 排序规则：**

> 使用 ORDER BY子句排序
> 
> ASC（ascend）：升序
> 
> DESC（descend）：降序
> 
> ORDER BY子句在SELECT语句的结尾

>  *  **如果没有使用排序操作，默认查询返回数据是按照当初添加数据的顺序**
>  *  **如果在ORDER BY后没有显示指名排序的方式的话，则默认按照升序排列。**
>  *  **列的别名只能在ORDER BY中使用，不能在WHERE中使用。（可以理解为当运行到WHERE语句时，列的别名还没有初始化）**

#显示员工信息，按照employee_id的降序排序，salary的升序排列
        #（注意）：是在id的降序排序下对salary进行升序
    	SELECT employee_id,salary,department_id
    	From employees
    	ORDER BY department_id DESC,salary ASC;

>  *  可以使用不在SELECT列表中的列排序。
>  *  在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

**1.2 分页：**

**1.2.1：分页的背景：**

> 背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？  
> 背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？

**1.2.2 实现规则：**

分页原理：所谓分页显示，就是将数据库中的结果集，一段一段显示处理需要的条件。

MYSQL中使用LIMIT实现分页格式：

LIMIT [位置偏移量,] 行数

> 第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。

--前10条记录：
    SELECT * FROM 表名 LIMIT 0,10;
    或者
    SELECT * FROM 表名 LIMIT 10;
    --第11至20条记录：
    SELECT * FROM 表名 LIMIT 10,10;
    --第21至30条记录： 
    SELECT * FROM 表名 LIMIT 20,10;

> MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT4,3;”返回的结果相同。

**1.2.3 分页显示公式：（当前页数-1）\*每页条数，每页条数**

SELECT * FROM table 
    LIMIT(PageNo - 1)*PageSize,PageSize;

> **注意：LIMIT 子句必须放在整个SELECT语句的最后！**

**使用 LIMIT 的好处：**

> 约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

## 二：多表连接 ##

**2.1 多表连接的错误形式（也称笛卡尔积的错误）：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_11_color_FFFFFF_t_70_g_se_x_16][]

**2.2 笛卡尔积（或交叉连接）的理解：**

> 笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_12_color_FFFFFF_t_70_g_se_x_16][]

> SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 **CROSS JOIN**表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。

>  *  \#多表查询的正确方式：需要有连接条件
>  *  \#如果查询语句中出现了多个表中都存在的字段，则必须指名此字段所在的表
>  *  \#建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
>  *  \#可以给表起别名，在SELECT和WHERE中使用表的别名，如果给表起了别名，一旦在SELECT或WHRER使用表名的话，则必须使用表的别名，而不能使用表的原名。
>  *  \#结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

## 三：多表查询分类讲解 ##

**2.1 分类一：等值连接 vs 非等值连接**

**2.1.1 等值连接：**

#多表查询的正确方式：需要有连接条件
    SELECT employees_id,department_name
    FROM employees,departments
    #两个表的连接条件
    WHERE employees.'department_id'=departments.department_id;
    
    #如果查询语句中出现了多个表中都存在的字段，则必须指名此字段所在的表
    SELECT employees_id,department_name,employees.depatment_id
    FROM employees,departments
    WHERE employees.'department_id'=departments.department_id;
    
    #建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。
    
    
    #可以给表起别名，在SELECT和WHERE中使用表的别名，如果给表起了别名，一旦在SELECT或WHRER使用表名的话，则必须使用表的别名，而不能使用表的原名。
    SELECT emp.employees_id,dept.department_name,emp.depatment_id
    FROM employees emp,departments dept
    WHERE emp.'department_id'=dept.department_id;
    
    #结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件

> 1. 多个连接条件与AND操作符
> 
> 2. 区分重复的列名：（1）多个表中有相同列时，必须在列名之前加上表名前缀（2）在不同表中具有相同列名的列可以用表名加以区分。
> 
> 3. 表的别名：（1）使用别名可以简化查询（2）列名前使用表明前缀可以提高查询效率
> 
> **注意：如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。**
> 
> **（建议）：对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。**
> 
> 4. 连接多个表： 连接 n个表,至少需要n-1个连接条件

**2.1.2 非等值连接：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_15_color_FFFFFF_t_70_g_se_x_16][]

**2.2 分类二：自连接和非自连接**

> **当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。**

2.2.1 自连接的例子：

#查询员工id，员工姓名及其管理者的id和姓名
    SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
    FROM employees emp,employees mgr
    WHERE emp.manager_id =mgr.employ_id;

**2.3 分类三：内连接和外连接**

**2.3.1 内连接：**

> 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行（意思是说只显示匹配成功的结果）。

**2.3.2 外连接：**

> 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

> **外连接的分类：左外连接、右外连接、满外连接。**
> 
>  *  如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。（两个表再连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行）
>  *  如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。（两个表再连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行）

**2.3.3 SQL92实现内连接和外连接：**

**SQL92实现内连接的例子：**

SELECT employee_id,department_name
    FROM employees e,departments d
    WHERE e.department_id=d.department_id;

**SQL92实现外连接：（使用（+）创建连接）：**

>  *  在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
>  *  Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。
>  *  而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

#左外连接
    SELECT last_name,department_name
    FROM employees ,departments
    WHERE employees.department_id = departments.department_id(+);
    #右外连接
    SELECT last_name,department_name
    FROM employees ,departments
    WHERE employees.department_id(+) = departments.department_id;

**2.3.3 SQL99实现内连接和外连接：**

使用JOIN...ON子句创建连接的语法结构：

SELECT table1.column, table2.column,table3.column
    FROM table1
        JOIN table2 ON table1 和 table2 的连接条件
            JOIN table3 ON table2 和 table3 的连接条件

它的嵌套逻辑类似我们使用的 FOR 循环：

for t1 in table1:
        for t2 in table2:
           if condition1:
               for t3 in table3:
                  if condition2:
                     output t1 + t2 + t3

> SQL99 采用的这种嵌套结构非常清爽、层次性更强、可读性更强，即使再多的表进行连接也都清晰可见。如果你采用 SQL92，可读性就会大打折扣。

语法说明：

>  *  可以使用 ON 子句指定额外的连接条件。
>  *  这个连接条件是与其它条件分开的。
>  *  ON 子句使语句具有更高的易读性。
>  *  关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

**SQL99实现内连接：**

语法：(其中INNER可以省略)

SELECT 字段列表
    FROM A表 INNER JOIN B表
    ON 关联条件
    WHERE 等其他子句;

例子：

SELECT e.employee_id, e.last_name, e.department_id, 
           d.department_id, d.location_id
    FROM   employees e JOIN departments d
    ON     (e.department_id = d.department_id);

**SQL99实现外连接**：

**1. 左外连接：**

语法：

#实现查询结果是A
    SELECT 字段列表
    FROM A表 LEFT JOIN B表
    ON 关联条件
    WHERE 等其他子句;

例子：

SELECT e.last_name, e.department_id, d.department_name
    FROM   employees e
    LEFT OUTER JOIN departments d
    ON   (e.department_id = d.department_id) ;

**2. 右外连接：**

语法：

FROM A表 RIGHT JOIN B表
    ON 关联条件
    WHERE 等其他子句;

例子：

SELECT e.last_name, e.department_id, d.department_name
    FROM   employees e
    RIGHT OUTER JOIN departments d
    ON   (e.department_id = d.department_id) ;

> 需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

**3. 满外连接：**

>  *  满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
>  *  SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
>  *  **需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。**

## 三：UNION的使用 ##

**3.1 合并查询结果：**

> **合并查询结果** ：利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNIONALL关键字分隔。

语法格式：

SELECT column,... FROM table1
    UNION [ALL]
    SELECT column,... FROM table2

**3.2 UNION操作符：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_10_color_FFFFFF_t_70_g_se_x_16][]

> UNION 操作符返回两个查询的结果集的并集，去除重复记录。（**去重**）

**3.3 UNION ALL操作符：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_10_color_FFFFFF_t_70_g_se_x_16 1][]

> UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分**，不去重**。

> **注意**：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

举例：查询部门编号>90或邮箱包含a的员工信息
    
    #方式1
    SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
    #方式2
    SELECT * FROM employees  WHERE email LIKE '%a%'
    UNION
    SELECT * FROM employees  WHERE department_id>90;

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息
    
    SELECT id,cname FROM t_chinamale WHERE csex='男'
    UNION ALL
    SELECT id,tname FROM t_usmale WHERE tGender='male';

## 四：7种SQL JOINS的实现 ##

**4.1 7种SQL JOINS的示意图：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_20_color_FFFFFF_t_70_g_se_x_16][]  
 **4.2 代码实现：**

**4.2.1：中图：内连接**

#中图：内连接 A∩B
    SELECT employee_id,last_name,department_name
    FROM employees e JOIN departments d
    ON e.`department_id` = d.`department_id`;

**4.2.2：左上图 ：左外连接：**

#左上图：左外连接
    SELECT employee_id,last_name,department_name
    FROM employees e LEFT JOIN departments d
    ON e.`department_id` = d.`department_id`;

**4.2.3 右上图 ：右外连接：**

#右上图：右外连接
    SELECT employee_id,last_name,department_name
    FROM employees e RIGHT JOIN departments d
    ON e.`department_id` = d.`department_id`;

**4.2.4 左中图 ：**

#左中图：A - A∩B
    SELECT employee_id,last_name,department_name
    FROM employees e LEFT JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE d.`department_id` IS NULL

**4.2.5 右中图：**

#右中图：B-A∩B
    SELECT employee_id,last_name,department_name
    FROM employees e RIGHT JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE e.`department_id` IS NULL

**4.2.6 左下图：满外连接：**

#左下图：满外连接
    # 左中图 + 右上图 A∪B
    SELECT employee_id,last_name,department_name
    FROM employees e LEFT JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE d.`department_id` IS NULL
    UNION ALL  #没有去重操作，效率高
    SELECT employee_id,last_name,department_name
    FROM employees e RIGHT JOIN departments d
    ON e.`department_id` = d.`department_id`;

**4.2.7 右下图：**

#右下图
    #左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
    SELECT employee_id,last_name,department_name
    FROM employees e LEFT JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE d.`department_id` IS NULL
    UNION ALL
    SELECT employee_id,last_name,department_name
    FROM employees e RIGHT JOIN departments d
    ON e.`department_id` = d.`department_id`
    WHERE e.`department_id` IS NULL

## 五：SQL99语法新特性 ##

**6.1 自然选择：**

> SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

在SQL92标准中：

SELECT employee_id,last_name,department_name
    FROM employees e JOIN departments d
    ON e.`department_id` = d.`department_id`
    AND e.`manager_id` = d.`manager_id`;

在 SQL99 中你可以写成：

SELECT employee_id,last_name,department_name
    FROM employees e NATURAL JOIN departments d;

**6.2 USING连接：**

> 当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。

SELECT employee_id,last_name,department_name
    FROM employees e JOIN departments d
    USING (department_id);

> 你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。它与下面的 SQL 查询结果是相同的：

SELECT employee_id,last_name,department_name
    FROM employees e ,departments d
    WHERE e.department_id = d.department_id;

## 七：多表查询章节小结 ##

> 表连接的约束条件可以有三种方式：WHERE, ON, USING  
> 1.**WHERE**：适用于所有关联查询  
> 2.**ON** ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。  
> 3.**USING**：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

#关联条件
    #把关联条件写在where后面
    SELECT last_name,department_name 
    FROM employees,departments 
    WHERE employees.department_id = departments.department_id;
    
    
    #把关联条件写在on后面，只能和JOIN一起使用
    SELECT last_name,department_name 
    FROM employees INNER JOIN departments 
    ON employees.department_id = departments.department_id;
    SELECT last_name,department_name 
    FROM employees CROSS JOIN departments 
    ON employees.department_id = departments.department_id;
    SELECT last_name,department_name  
    FROM employees JOIN departments 
    ON employees.department_id = departments.department_id;
    
    
    #把关联字段写在using()中，只能和JOIN一起使用
    #而且两个表中的关联字段必须名称相同，而且只能表示=
    #查询员工姓名与基本工资
    SELECT last_name,job_title
    FROM employees INNER JOIN jobs USING(job_id);
    
    
    #n张表关联，需要n-1个关联条件
    #查询员工姓名，基本工资，部门名称
    SELECT last_name,job_title,department_name FROM employees,departments,jobs 
    WHERE employees.department_id = departments.department_id 
    AND employees.job_id = jobs.job_id;
    SELECT last_name,job_title,department_name 
    FROM employees INNER JOIN departments INNER JOIN jobs 
    ON employees.department_id = departments.department_id 
    AND employees.job_id = jobs.job_id;

> **注意**：我们要 控制连接表的数量 。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。
> 
> 【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。  
> 说明：即使双表 join 也要注意表索引、SQL 性能。

--------------------

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_11_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/a11ad295a7cd4da68af11c1f0f1e4a4a.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ==,size_11,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_12_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/2063e9c2e89b4dce9101b5241115c89c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ==,size_12,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_15_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/2a045e0fb67e4f9f93b20672519ba19c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ==,size_15,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_10_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/94ba3c7c0c504fe4a5024f939d63b5a8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ==,size_10,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_10_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/da244490ca744c1881dcedec182dfde2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ==,size_10,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/9e585d478b7841e2987b8b794db3f1a0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBASmlhWGluZ05hc2hpc2h1YQ==,size_20,color_FFFFFF,t_70,g_se,x_16