MySQL笔记

1 数据库基础知识

1.1 先谈发音

MySQL 如何发音？在国内 MySQL 发音有很多种，Oracle 官方文档说他们念作 My sequal['si:kwəl]。

1.2 程序中数据库概念

1. 数据库是按照数据结构来组织、存储和管理数据的仓库。
2. 是一个长期存储在计算机内的、有组织的、可共享的、统一管理的、大量数据的集合文件。

   • 数据库的具体体现，就是磁盘的文件结构，方便数据读取和修改等。

     

   • 一种长期存储手段，不主动删除数据不应该消失。

     

   • 数据库中数据存储是有组织的结构，方便数据读取和修改等。

     

   • 数据库数据属于可共享的符合真正开发需求。

     

1.3 数据库基本概念

1. 数据
   数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。

2. 数据库
   数据库是数据管理的有效技术，是由一批数据构成的有序集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反映客观事物间的本质联系。

3. 数据库管理系统
   数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。

4. 数据库应用程序
   数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序。

5. 数据库管理员
   数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。

1.4 数据库分类

关系型数据库

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。可以采用结构化查询语言（SQL）对数据库进行操作。

优点

1. 易于维护：都是使用表结构，格式一致；
2. 使用方便：SQL 语言通用，可用于复杂查询；
3. 复杂操作：支持 SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点

1. 读写性能比较差，尤其是海量数据的高效率读写；
2. 固定的表结构，灵活度稍欠；
3. 高并发读写需求，传统关系型数据库来说，硬盘 I/O 是一个很大的瓶颈。

数据按照类别进行存储，每个类别存储到一个容器（表）中，表和表之间可以建立关系，可以进行关联操作，性能相对一般。

• 结构化数据模型：数据以表格形式存储，具有固定的结构。例如：学生和分数分别存储到不同的表。
• ACID 事务：通过强大的事务支持，保证数据的原子性、一致性、隔离性和持久性。例如：转账失败，钱不损失。
• 丰富查询语句：支持 SQL 语句，能够进行复杂的关联数据查询。例如：查询学生以及学生的分数。
• 数据一致性：数据的关系和约束确保数据的一致性和完整性。例如：存储学生数据，保证身份证号唯一且不为空。

非关系型数据库

非关系型数据库也称之为 NoSQL 数据库，是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点

1. 格式灵活：存储数据的格式可以是 (key,value) 形式、文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。
2. 速度快：nosql 可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；
3. 高扩展性；
4. 成本低：nosql 数据库部署简单，基本都是开源软件。

缺点

1. 不提供 sql 支持，学习和使用成本较高；
2. 无事务处理；
3. 数据结构相对复杂，复杂查询方面稍欠。

非关系型数据库并没有统一存储结构标准，现常见结构有键值、文档、JSON 类型等，对高性能需求设计。

• 灵活的数据模型：支持多种数据模型，例如键值对、文档型、列族型和图形数据库。
• 无固定的表结构：不需要预定义固定的表结构，适应非结构化或者半结构化数据。
• 高性能读写：少关系型，注重高性能读写能力，适用于大规模数据和高并发访问模型。

总结

• 关系型数据库适用于需要强一致性和复杂查询的场景。
• 非关系型数据库适用于需要高性能、灵活性和分布式处理的场景。

1.5 数据库选择

[!question] 思考
开发中使用哪种类型数据库多一些呢？

---

[!done] 答

1. 绝大部分应用采用混合模式，二者结合使用。
2. 程序主体数据一般存储在关系型数据库（主）。
3. 程序缓存数据和高并发数据存储到非关系型数据库（辅）。

1.6 关系型数据库存储设计规则 (E-R 模型)

• 遵循 ER 模型
  • E（Entity） 代表实体类别，关系型数据库中一类数据，对应数据库中的一张表存储。
  • R（Relationship）表和表可以维护某种关系，可以通过关系进行多表操做。
• 模型解释
  • 数据库中最大的存储单位为库。
  • 每类数据存储一张表中，表存储到库中。
  • 表具有一些特性列，这些特性列定义数据在表中如何存储。
  • 表中的数据是按照行存储，一行即为一条记录。
• 对比总结

单位名称	描述	对别
库	数据库概念中最大的存储单位，内部存储表
表	一类数据和实体的集合	Python 类
行	一行数据，一个实体，操作的最基本单位	Python 对象
列	最小存储单位，代表一个属性	对象属性

2 MySQL 基础知识

2.1 MySQL 简介

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

2.2 常见的关系型数据库

2.3 MySQL 特点

MySQL 是开源的。

MySQL 支持大型系统的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL 使用标准的 SQL 数据语言形式。

MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP 等。

MySQL 存储数据量较大，32 位系统表文件最大可支持 4GB ，64 位系统支持最大的表文件为 8TB。

MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

2.4 MySQL 分类

1. MySQL 分为社区版
   社区版是完全开源免费的，社区版也支持多种数据类型和标准的 SQL 查询语言，能够对数据进行各种查询、增加、删除、修改等操作，所以一般情况下社区版就可以满足开发需求了。
2. 企业版
   企业版是收费的。即使在开发中需要用到一些付费的附加功能，价格相对于昂贵的 Oracle、DB2 等也是有很大优势的。对数据库可靠性要求比较高的企业可以选择企业版。

3 MySQL 的下载

MySQL 官网地址

[!tip]
MySQL 8 has numerous improvements over MySQL 5.7,Such as better performance, security, and new features. In addition, per Oracle's Lifetime Support policy, as of October 25, 2023, MySQL 5.7 Extended Support is ended, and it is now covered under Oracle Sustaining Support only. Hence, all MySQL 5.7 users are encouraged to migrate to MySQL 8.0 to start benefiting again from Premier Support.

MySQL 8 相对于 5.7 带来了许多改进，包括更好的性能、安全性增强以及新功能的引入。Oracle 的支持政策也在 2023 年 10 月 25 日结束了 MySQL 5.7 的扩展支持，目前只提供 Sustaining Support(末期版本)。因此，强烈建议所有的 MySQL 5.7 用户尽快迁移到 MySQL 8.0，以重新享受到首选支持。

4 MySQL 的安装与卸载

4.1 MySQL 安装

4.2 MSQL 卸载

5 连接 MySQL

5.1 通过 MySQL 自带客户端工具

可通过 MySQL 自带的客户端工具链接 MySQL。

上面的方式直接明文显示密码，不安全，可以输入 mysql -u用户名 -p 后回车，然后再输入密码此时密码不会显示在屏幕上。

通过客户端工具可对 MySQL 进行操作。

通过 MySQL 客户端工具的快捷方式连接 MySQL

5.2 使用 cmd 进行连接

先打开cmd命令窗口。

连接 mysql 服务命令。确保 mysql 服务已经启动，并且电脑设置了 Path 变量。

mysql -u <username> -p<password> [-h <hostname>] [-P <port>] [<databasename>]

注意：参数不分先后顺序

参数	含义
-u <username>	用于指定你要连接的 MySQL 数据库的用户名。
-p<password>	表示密码，后面紧跟着密码，中间没有空格。如果你不希望在命令行中显示密码，可以不指定密码，直接 -p，然后在提示下手动输入密码。
-h <hostname>	用于指定 MySQL 服务器的主机名或者 IP 地址。如果 MySQL 在本地运行，可以用 localhost。
-P <port>	用于指定连接 MySQL 服务器的端口号，默认情况下是 3306。
<databasename>	是你要连接的数据库的名称。连接后会默认使用这个数据库。

其他基础SQL命令

SQL 注释：

单行注释：# 注释内容
单行注释：-- 注释内容    其中--后面的空格必须有
多行注释：/* 注释内容 */

版本和退出连接：

-- 查看版本，命令符号之间空格隔开，每条命令后使用 ; 结束，否则不执行。切记！！
mysql> select version();
-- 退出连接
mysql> exit;

5.3 可视化工具选择和安装

市面SQL可视化工具有很多，常用的有：

1. MySQL Workbench：它是MySQL官方提供的工具，具有数据建模、SQL开发、数据库管理等多种功能。主要是开源免费，但是全英界面！！
   
2. Navicat：支持多种数据库系统，包括MySQL、PostgreSQL、Oracle等。它提供了强大的图形界面和数据同步功能，界面稍显复杂，免费版功能阉割！
   
3. SQLyog：是一个用于MySQL数据库的图形化管理工具，提供了直观的用户界面和强大的数据库管理功能。界面相对简单，免费版功能阉割（没有代码提示和补全能力）！
   

Navicat 工具的使用

6 SQL 语言

6.1 SQL 语言简介

结构化查询语言（Structured Query Language）简称 SQL（发音：sequal['si:kwəl]），是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL 被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准，可以使用 SQL 命令进行关系型数据库操作。

[!tip] 提示
所有关系型数据库都支持标准SQL语句。

但是也会有特定的方言SQL语句。

本笔记，主要使用标准 SQL 语句和 MySQL 方言。

SQL 能做什么？

• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 面向数据库执行查询
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

6.2 SQL 标准

SQL 是 1986 年 10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了 SQL 正式国际标准。

1989 年 4 月，ISO 提出了具有完整性特征的 SQL89 标准，1992 年 11 月又公布了 SQL92 标准，在此标准中，把数据库分为三个级别：基本集、标准集和完全集。在 1999 年推出 99 版标准。最新版本为 SQL2016 版。

比较有代表性的几个版本：SQL86、SQL92、SQL99。

6.3 SQL 语言分类

SQL包括了所有对数据库的操作，主要是由数据定义、数据操纵、数据查询、数据控制、事务控制等SQL语言的使用规定组成。

1. 数据定义语言（DDL：Data Definition Language）定义数据库对象语言，其语句包括动词 CREATE 和 DROP 等。
   DDL 用于定义和管理数据库的结构，包括库、表、索引、视图等数据库对象的创建、修改和删除。
   DDL 不涉及对数据的操作，而是关注数据库的结构和元数据（容器）。
   • CREATE：用于创建数据库、表、索引、视图等。
   • ALTER：用于修改数据库对象的结构，如修改表结构、添加列、删除列等。
   • DROP：用于删除数据库对象，如删除表、删除索引等。
2. 数据操作语言（DML：Data Manipulation Language）其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行。
   • INSERT：添加数据
   • UPDATE：更新数据
   • DELETE：删除数据
3. 数据查询语言（DQL：Data Query Language）其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。关键字 SELECT 是 DQL（也是所有 SQL）用得最多的动词。
   • SELECT
   • FROM
   • WHERE
   • ORDER BY
   • HAVING
4. 数据控制语言（DCL：Data Control Language）它的语句通过 GRANT 或 REVOKE 获得许可，确定用户对数据库对象的访问。
   • GRANT：授予用户某种权限
   • REVOKE：回收授予的某种权限
5. 事务控制语言（TCL ：Transaction Control Language）它的语句能确保被 DML 语句影响的表的所有行及时得以更新。
   • COMMIT：提交事务
   • ROLLBACK：回滚事务
   • SAVEPOINT：设置回滚点

[!tip] 注意

数据操纵语言 DML（insert、update、delete）针对表中的数据。

而数据定义语言 DDL（create、alter、drop）针对数据库对象，比如数据库 database、表 table、索引 index、视图 view、存储过程 procedure、触发器 trigger。

6.4 SQL 语言语法

1. SQL 语句不区分大小写，关键字建议大写。
2. SQL 语句可以单行或多行书写。

6.5 SQL命名规定和规范

标识符命名规定

1. 数据库名、表名不得超过30个字符，变量名限制为29个。

2. 必须只能包含 A–Z，a–z，0–9，_ 共63个字符，而且不能数字开头。

3. 数据库名、表名、字段名等对象名中间不能包含空格。

4. 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名。

5. 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用 `（着重号）引起来。
   MySQL 关键字

可以起的数据库名：

1. mycompanydatabase

2. sales_data

3. Customer_Records_DB

4. ecommerce_DB

不能起的数据库名：

6. my company database

7. sales&data

8. SELECT_db

9. 123_database

10. inventory database v1.0

标识符命名规范（基于阿里巴巴规范手册）

阿里巴巴的 SQL 规范建议通常是为了确保数据库操作的效率、安全性和可维护性。

1. 注释应该清晰、简洁地解释 SQL 语句的意图、功能和影响。

2. 库、表、列名应该使用小写字母，并使用下划线(_)（推荐）或驼峰命名法（不推荐）。

3. 库、表、字段名应该简洁明了，具有描述性，反映其所存储数据的含义。

4. 库名应与对应的程序名一致 例如：程序名为 EcommercePlatform 数据库名命名为 ecommerce_platform。

5. 表命名最好是遵循业务名称_表的作用。例如：alipay_task、force_project、trade_config。

6. 列名应遵循表实体_属性的作用 例如：product_name 或 productName。

---

数据库名 (Database Names)：

1. customer_data_db

2. ecommerce_platform_db

3. inventory_management_db

表名 (Table Names)：

1. user_accounts

2. order_details

3. product_catalog

列名 (Column Names)：

1. customer_id

2. product_name

3. order_quantity

7 创建与删除数据库

7.1 创建数据库

使用 DDL 语句创建数据库

创建库，我们必须指定库名，可能指定字符集或者排序方式！

方式1：创建数据库，使用默认的字符集和排序方式。

CREATE DATABASE 数据库名;

方式2：判断并创建默认字符集库（推荐）。

Create DATABASE IF NOT EXISTS 数据库名;

方式3：创建指定字符集库或者排序方式。

CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE 数据库名 COLLATE 排序规则;

方式4：创建指定字符集和排序规则库。

CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则;

MySQL8默认值(不同版本可能会有不同)：

• 字符集：utf8mb4 是一种广泛支持各种语言字符的字符集。

• 排序规则：utf8mb4_0900_ai_ci 是一种不区分大小写的排序规则。

# 查看默认字符集和排序方式命令
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

常见字符集（Character Set）：

1. utf8：早期版本的字符集，最多3字节存储一个字符，3字节无法覆盖全部unicode编码，有显示乱码可能。

2. utf8mb4（8+默认）：解决utf8的存储限制，常用汉字使用3字节进行字符存储，不常用的用4字节进行存储，可以覆盖更广 Unicode 编码，包括表情符号等等。

常见排序规则（Collate）：

1. utf8mb4_0900_ai_ci：UTF-8 的不区分大小写的排序规则(mysql8+的默认排序规则)。

2. utf8mb4_0900_as_cs：UTF-8 的 Unicode 排序规则，区分大小写！

案例1：创建一个 test 的数据库，并查看该数据库，以及该数据库的编码。

创建数据库：

CREATE DATABASE test DEFAULT CHARACTER SET utf8;

查看数据库：

SHOW DATABASES;

查看数据库编码：

SELECT
	schema_name,
	default_character_set_name 
FROM
	information_schema.schemata 
WHERE
	schema_name = 'test';

案例2：查询数据库默认的字符集。

-- 查询数据库默认的字符集
SHOW VARIABLES LIKE 'character_set_database';

案例3：查询数据库默认的排序方式。

-- 查询数据库默认的排序方式
SHOW VARIABLES LIKE 'collation_database';

案例4：创建 ddl_d1 库，指定字符集为 utf8，且排序方式用大小写敏感的 utfmb4_0900_as_cs 模式。

-- 创建 ddl_d1 库，指定字符集为 utf8，且排序方式用大小写敏感的 utfmb4_0900_as_cs 模式
CREATE DATABASE IF NOT EXISTS ddl_d1 CHARACTER SET utf8 COLLATE utf8mb4_0900_as_cs;

运行语句报错，这是因为创建数据库时指定的字符集为 utf8，该字符集是1-3字节的可变字节编码，而排序规则则是用的 utf8mb4 字符集的排序规则，不匹配。

CREATE DATABASE IF NOT EXISTS ddl_d1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

使用 Navicat 创建数据库

示例：创建一个 test2 的数据库。

7.2 查看和使用库

使用和查看库，包括展示和切换库等命令。

方式1：查看当前所有库。

show databases;

方式2：查看当前使用库。

select database();

方式3：查看指定库下所有表。

show tables from 数据库名;

方式4：查看创建库的信息。

show create database 数据库名;

方式5：切换库/选中库。

USE 数据库名;

[!note] 注意
要操作表格和数据之前必须先说明是对哪个数据库进行操作，即要先use库。

案例：

-- 查看当前所有库
SHOW DATABASES;

-- 查看指定库下所有表
SHOW TABLES FROM bjsxt;

-- 查看创建库的信息
SHOW CREATE DATABASE bjsxt;

-- 切换库/选中库
USE bjsxt;

-- 查看当前使用库
SELECT DATABASE();

7.3 选择数据库

在创建表时，需要先选择数据库。

USE 数据库名;

示例：创建一个名称为 bjsxt 的数据库，编码为 utf8。

create database bjsxt default character set utf8;

选择该数据库。

USE bjsxt;

7.4 修改库

方式1：修改库编码字符集。

# 修改字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;

# 改排序方式
ALTER DATABASE 数据库名 COLLATE 排序方式;

# 修改字符集和排序方式
ALTER DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序方式;

[!note] 注意

DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

7.5 删除数据库

删除数据库前要三思，是不是有刁民要害朕，确认好再删除，否则真要提桶跑路！！

使用 DDL 语言删除数据库

方式1：直接删除库。

DROP DATABASE 数据库名称;

方式2：判断并删除库（推荐）。

DROP DATABASE IF EXISTS 数据库名;

示例：删除 test 数据库

drop database test;

使用 Navicat 删除数据库

示例：删除 test2 数据库

7.6 库管理实战练习

场景1：假设你正在为一个多语言的博客平台设计数据库。你需要创建一个名为 blog_platform 的数据库，支持存储多语言的文章和评论。由于博客平台可能包含来自不同语言的用户，你决定使用 utf8mb4 字符集，排序方式选择默认值，以支持广泛的 Unicode 字符。

CREATE DATABASE IF NOT EXISTS blog_platform CHARACTER SET utf8mb4;

场景2：查看数据库字符集和排序规则。

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

场景3：假设在后续的发展中，你决定将排序方式修改为 utf8mb4_0900_as_cs，以实现大小写敏感的比较。

ALTER DATABASE blog_platform COLLATE utf8mb4_0900_as_cs;

场景4：查看修改后数据库字符集和排序规则。

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

场景5：项目惨遭放弃，需要删除项目库，并且跑路。

DROP DATABASE IF EXISTS blog_platform;

8 MySQL 中的数据类型

8.1 整数类型

MySQL 数据类型	含义 (有符号)
tinyint(m)	1 个字节 范围（-128~127）
smalint(m)	2 个字节 范围（-32768~32767）
mediumint(m)	3 个字节 范围（-8388608~8388607）
int(m)	4 个字节 范围（-2147483648~2147483647）
bigint(m)	8 个字节 范围（$+-9.22*10^{18}$）

以分号结尾。

例如：m，如果实际值是 zerofill，如果列指定了 int(3)，查询结果就是 2，左边用 zerofill 来填充。

8.2 浮点类型

MySQL 数据类型	含义
float(m,d)	单精度浮点型 8 位精度（4 字节）m 总个数，d 小数位
double(m,d)	双精度浮点型 16 位精度（8 字节）m 总个数，d 小数位

8.3 字符类型

MySQL 数据类型	30 含义
char(n)	固定长度，最多 255 个字符
tinytext	可变长度，最多 255 个字符
varchar(n)	可变长度，最多 65535 个字符
text	可变长度，最多 65535 个字符
mediumtext	可变长度，最多 2 的 24 次方 -1 个字符
longtext	可变长度，最多 2 的 32 次方 -1 个字符

[!tip] char 和 varchar

1. char 长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。
2. varchar 可变长度，可以设置最大长度；适合用在长度可变的属性。
3. text 不设置长度，当不知道属性的最大长度时，适合用 text。

按照查询速度：char 最快，varchar 次之，text 最慢。

[!tip] 字符串型使用建议

1. 经常变化的字段用 varchar
2. 知道固定长度的用 char
3. 尽量用 varchar
4. 超过 255 字符的只能用 varchar 或者 text
5. 能用 varchar 的地方不用 text

8.4 日期类型

MySQL 数据类型	含义
date	日期 YYYY-MM-DD
time	时间 HH:MM:SS
datetime	日期时间 YYYY-MM-DD HH:MM:SS
timestamp	时间戳 YYYYMMDD HHMMSS

[!tip] 注意
datetime 存储的日期是考虑时区的，使用服务器的时区。

timestamp 不考虑时区，需要自己进行时区处理。

[!tip] 日期类型使用建议

1. 只存年月日用 date
2. 只存时分秒用 time
3. 既存年月日又存时分秒用 datetime
4. 如果项目是跨时区的，选 timestamp

8.5 二进制数据 (BLOB)

1. BLOB 和 TEXT 存储方式不同，TEXT 以文本方式存储，英文存储区分大小写，而 Blob 是以二进制方式存储，不分大小写。
2. BLOB 存储的数据只能整体读出。
3. TEXT 可以指定字符集，BLOB 不用指定字符集。

9 创建表与删除表

创建库VS创建表：

例子：创建了一个名为 posts 的博客文章表，包括一些常见的数据类型、编码设置以及列注释！

CREATE TABLE posts ( 
     post_id INT AUTO_INCREMENT PRIMARY KEY, 
     title VARCHAR(255) NOT NULL COMMENT 'The title of the blog post’, 
     content TEXT NOT NULL COMMENT 'The content of the blog post’, 
     author_id INT NOT NULL COMMENT 'The ID of the author of the post’, 
     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'The timestamp when the post was created’, 
    CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES authors(author_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'Table storing blog posts';

CREATE TABLE 表名 ( 
     列名 类型 [列可选约束],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
    [列可选约束]
) [表可选约束] [COMMENT '表可选注释'];

CREATE TABLE [IF NOT EXISTS] 表名 ( 
     列名 类型 [列可选约束],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],  # 列之间使用 , 分割
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
     列名 类型 [列可选约束] [COMMENT '列可选注释'],
    [列可选约束]
) [表可选约束] [COMMENT '表可选注释'];

9.1 创建表

使用 DDL 语句创建表

CREATE TABLE 表名(列名 类型, 列名 类型……);

示例：创建一个 employees 表包含雇员 ID，雇员名字，雇员薪水。

CREATE TABLE employees (
	employee_id INT,
	employee_name VARCHAR ( 10 ),
    employee_salary FLOAT ( 8, 2 ));

查看已创建的表。

show tables;

案例：

场景1：假设你正在设计一个简单的在线图书管理系统。需要创建一个名为 book_libs 的数据库，你决定使用 utf8mb4 字符集，排序方式选用大小写敏感的 utf8mb4_0900_as_cs。

CREATE DATABASE IF NOT EXISTS book_libs CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

场景2：创建一个图书表 books，判断不存在再创建，并且手动设置 books 表字符集为 utf8mb4，添加表注释内容。

同时图书表 books 中应该以下列：

• 图书名称 book_name 列，类型为varchar(20)，添加注释。

• 图书价格 book_price 列，类型为double(4,1)，添加注释。

• 图书数量 book_num 列，类型为int，添加注释。

按以上要求完成图书表的创建！

USE book_libs;

CREATE TABLE IF NOT EXISTS books(
	book_name VARCHAR(20) COMMENT '图书名称',
	book_peice DOUBLE(4, 1) COMMENT '图书价格',
	book_num INT COMMENT '图书数量'
) CHARSET = utf8mb4 COMMENT '图书表';

SHOW TABLES FROM book_libs;

使用 Navicat 创建表

示例：创建 employees2 表。

9.2 删除表

使用 DDL 语句删除表

DROP TABLE 表名;

示例：删除 employees 表。

drop table employees;

使用 Navicat 删除表

示例：删除 employees2 表

10 修改表

10.1 修改表名

使用 DDL 语句修改表

ALTER TABLE 旧表名 RENAME 新表名;

示例一：创建一个 employees 表包含雇员 ID，雇员名字，雇员薪水。

create table employees(employee_id int, employee_name varchar(10), salary float(8, 2));

示例二：将 employees 表名修改为 emp。

alter table employees rename emp;

使用 Navicat 修改表名

选择表按 F2。

10.2 修改列名

使用 DDL 语句修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 类型;

[!tip] 注意
示例是必须要有的，哪怕修改后和原来类型一样也得加上。

示例：将 emp 表中的 employee_name 修改为 name。

alter table emp change column employee_name name varchar(10);

使用 Navicat 修改列名

10.3 修改列类型

使用 DDL 语句修改列类型

ALTER TABLE 表名 MODIFY 列名 新类型;

[!tip] 注意
使用上一节的 002 也可以达到修改类型的目的。

示例：将 emp 表中的 name 的长度指定为 40。

alter table emp modify name varchar(40);

使用 Navicat 修改列类型

10.4 添加新列

使用 DDL 语句添加新列

ALTER TABLE 表名 ADD COLUMN 新列名 类型;

示例：在 emp 表中添加佣金列，列名为 commission_pct。

alter table emp add column commission_pct float(4, 2);

使用 Navicat 添加新列

10.5 删除指定列

使用 DDL 语句删除指定的列

ALTER TABLE 表名 DROP COLUMN 列名;

示例：删除 emp 表中的 commission_pct。

alter table emp drop column commission_pct;

使用 Navicat 删除指定的列

11 MySQL 中的约束

• 主键约束（Primary Key）

  1. 不允许为空
  2. 不允许重复
  3. 保证数据的唯一性

• 外键约束（Foreign Key）

  1. 允许有空值
  2. 允许有重复
  3. 值必须是参照表中的参照列中有的值
  4. 保证数据的参照完整性

• 唯一性约束（Unique）

  1. 相同值只能出现一次
  2. 允许为多个列添加唯一性约束
  3. 保证数据的唯一性

• 非空约束（Not Null）

  1. 列中不能有空值
  2. 允许重复值
  3. 允许为多个列添加非空约束
  4. 保证数据没有空值

• 检查约束（Check）

  1. 用户自己定义约束条件
  2. 保证数据满足自定义的条件约束
  3. MySQL 目前不支持检查约束

11.1 约束概述

数据库约束是对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

1. 主键约束（Primary Key）PK

   [!tip]
   主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如，学生信息表中的学号是唯一的。

2. 外键约束（Foreign Key）FK

   [!tip]
   外键约束经常和主键约束一起使用，用来确保数据的一致性。

3. 唯一性约束（Unique）

   [!tip]
   唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的。

4. 非空约束（Not Null）

   [!tip]
   非空约束用来约束表中的字段不能为空。

5. 检查约束（Check）

   [!tip]
   检查约束也叫用户自定义约束，是用来检查数据表中，字段值是否有效的一个手段，但目前 MySQL 数据库不支持检查约束。

11.2 添加主键约束 (Primary Key)

1. 单一主键
   使用一个列作为主键列，当该列的值有重复时，则违反唯一约束。

2. 联合主键
   使用多个列作为主键列，当多个列的值都相同时，则违反唯一约束。

修改表添加主键约束

使用 DDL 语句添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY (列名[, 列名...]);

示例：将 emp 表中的 employee_id 修改为主键。

ALTER TABLE emp ADD PRIMARY KEY ( employee_id );

主键自增长

MySQL 中的自动增长类型要求：

• 一个表中只能有一个列为自动增长。
• 自动增长的列的类型必须是整数类型。
• 自动增长只能添加到具备主键约束或唯一性约束的列上。
• 删除主键约束或唯一性约束，如果该列拥有自动增长能力，则需要先去掉自动增长然后在删除约束。

alter table 表名 modify 列名 类型 auto_increment;

示例：将 emp 表中的 employee_id 主键修改为自增。

alter table emp modify employee_id int auto_increment;

使用 Navicat 添加主键约束

删除主键

使用 DDL 语句删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

[!tip] 注意
删除主键时，如果主键列具备自动增长能力，需要先去掉自动增长，然后在删除主键。

示例：删除 emp 表中的 employee_id 主键约束。

去掉自动增长：

alter table emp modify employee_id int;

删除主键

alter table emp drop primary key;

使用 Navicat 删除主键

11.3 添加外键约束 (Foreign Key)

修改表添加外键约束

使用 DDL 语句添加外键约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(列名) REFERENCES 参照的表名(参照的列名);

[!tip] 注意
外键约束必须要有一个约束名，一般命名为 数值类型中的长度 0 是指显示长度，并不表示存储长度，且只有字段指定 change column 时有用 来表示其为外键。

示例一：创建 departments 表包含 department_id、department_name、location_id。

create table departments(department_id int, department_name varchar(30), location_id int);

示例二：修改 departments 表，向 department_id 列添加主键约束与自动递增。

alter table departments add primary key(department_id);

alter table departments modify department_id int auto_increment;

示例三：修改 emp 表，添加 dept_id 列。

alter table emp add column dept_id int;

示例四：向 emp 表中的 dept_id 列添加外键约束。

alter table emp add constraint emp_fk foreign key(dept_id) references departments(department_id);

使用 Navicat 添加外键约束

删除外键约束

使用 DDL 语句删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

示例：删除 dept_id 的外键约束。

alter table emp drop foreign key emp_fk;

使用 Navicat 删除外键约束

11.4 添加唯一性约束 (Unique)

修改表添加唯一性约束

使用 DDL 语句添加唯一性约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);

[!tip] 注意
唯一性约束必须要有一个约束名，一般命名为 类型参数 来表示其为唯一性约束。

示例：向 emp 表中的 name 添加唯一约束。

alter table emp add constraint emp_uk unique(name);

使用 Navicat 添加唯一性约束

删除唯一性约束

使用 DDL 语句删除唯一性约束

ALTER TABLE 表名 DROP KEY 约束名;

示例：删除 name 的唯一约束。

alter table emp drop key emp_uk;

使用 Navicat 删除唯一性约束

11.5 非空约束 (Not Null)

修改表添加非空约束

使用 DDL 语句添加非空约束

ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL;

示例：向 emp 表中的 salary 添加非空约束。

alter table emp modify salary float(8,2) not NULL;

使用 Navicat 添加非空约束

删除非空约束

使用 DDL 语句删除非空约束

ALTER TABLE 表名 MODIFY 列名 类型 [NULL];

示例：删除 emp 表中 salary 的非空约束。

alter table emp modify salary float(8, 2) NULL;

[!tip] 注意
不写 null 关键字也可以，mysql 的列默认就是 null

使用 Navicat 删除非空约束

11.6 创建表时添加约束

查询表中的约束信息：

SHOW KEYS FROM 表名;

示例：创建 depts 表包含 department_id 该列为主键且自动增长，department_name 列不允许重复，location_id 列不允含有空值。

create table depts(department_id int primary key auto_increment, department_name varchar(30) unique, location_id int not null);

12 MySQL 中 DML 操作

12.1 添加数据 (INSERT)

选择插入

INSERT INTO 表名 (列名1,列名2,列名3…) VALUES(值1,值2,值3…);

示例：向 departments 表中添加一条数据，部门名称为 market，工作地点 ID 为 1。

insert into departments(department_name, location_id) values("market", 1);

完全插入

INSERT INTO 表名 VALUES(值1, 值2, 值3…);

[!tip] 注意
如果当前表名 _fk，可以使用 default 或者 null 或者 0 占位。

示例一：向 departments 表中添加一条数据，部门名称为 development，工作地点 ID 为 2。当前表名 _uk。

insert into departments values(default, "development", 2);

示例二：向 departments 表中添加一条数据，部门名称为 human，工作地点 ID 为 3。主键是自动增长。

insert into departments values(null, "human", 3);

示例三：向 departments 表中添加一条数据，部门名称为 teaching，工作地点 ID 为 4。使用 default 占位。

insert into departments values(0, "teaching", 4);

12.2 默认值处理 (DEFAULT)

在 MySQL 中可以使用 DEFAULT 为列设定一个默认值。如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。

创建表时指定列的默认值

CREATE TABLE 表名 (列名 类型 default 默认值,…);

示例：创建 emp3 表，该表包含 emp_id 主键且自动增长，包含 name，包含 address 该列默认值为 Unknown。

CREATE TABLE emp3 ( 
	emp_id INT PRIMARY KEY auto_increment, 
	name VARCHAR ( 30 ), 
	address VARCHAR ( 50 ) DEFAULT "Unkonwn" 
);

修改表添加新列并指定默认值

ALTER TABLE 表名 ADD COLUMN 列名 类型 DEFAULT 默认值;

示例：修改 emp3 表，添加 job_id 该列默认值为 0。

alter table emp3 add column job_id int default 0;

插入数据时的默认值处理

如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是完全项插入需要使用 default 来占位。

示例：向 emp3 表中添加数据，要求 address 列与 job_id 列使用默认值作为该列的值。

insert into emp3(name) values("admin");
insert into emp3 values(default, "oldlu", default, default);

12.3 更新数据 (UPDATE)

UPDATE 表名 SET 列名 = 值, 列名 = 值 WHERE 条件;

[!tip] 注意
更新语句中使用 null 占位，否则表中的所有数据都会被更新。

示例：更新 emp3 表中的 id 为 1 的数据，添加 address 为 BeiJing。

update emp3 set address = "BeiJing" where emp_id = 1;

12.4 删除数据 (DELETE)

DELETE 删除数据

DELETE FROM 表名 WHERE 条件;

[!tip] 注意
在 DELETE 语句中，如果没有给定删除条件则会删除表中的所有数据。

示例：删除 emp3 表中 emp_id 为 1 的雇员信息。

delete from emp3 where emp_id = 1;

TRUNCATE 清空表

TRUNCATE TABLE 表名;

示例：删除 emp3 表中的所有数据。

truncate table emp3;

清空表时 DELETE 与 TRUNCATE 区别

• truncate 是整体删除 (速度较快)，delete 是逐条删除 (速度较慢)；
• truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete 高的原因；
• truncate 是会重置自增值，相当于自增列会被置为初始值，又重新从 1 开始记录，而不是接着原来的值。而 delete 删除以后，自增值仍然会继续累加。

13 MySQL 查询数据

13.1 SELECT 基本查询

SELECT 语句的功能

SELECT 语句从数据库中返回信息。使用一个 SELECT 语句，可以做下面的事：

• 使用 0 占位：能够使用 SELECT 语句的列选择功能选择表中的列，这些列是想要用查询返回的。当查询时，能够返回列中的数据。
• 一定要给定更新条件：能够使用 SELECT 语句的行选择功能选择表中的行，这些行是想要用查询返回的。能够使用不同的标准限制看见的行。
• 列选择：能够使用 SELECT 语句的连接功能来集合数据，这些数据被存储在不同的表中，在它们之间可以创建连接，查询出我们所关心的数据。

SELECT 基本语法

基本 SELECT 语句

在最简单的形式中，SELECT 语句必须包含下面的内容：

• 一个 SELECT 子句，指定被显示的列
• 一个 FROM 子句，指定表，该表包含 SELECT 子句中的字段列表

在语法中：

语句	含义
SELECT	是一个或多个字段的列表
*	选择所有的列
DISTINCT	禁止重复
column 丨 expression	选择指定的字段或表达式
alias	给所选择的列不同的标题
FROM table	指定包含列的表

添加测试数据

将 data.sql 文件通过 Navicat 导入到 MySQL 中 itbz 数据库中。该文件包含了课程中所使用的案例表。

13.2 查询中的列选择

选择所有列

用跟在 SELECT 关键字后面的星号（*），你能够显示表中数据的所有列。

示例：查询 departments 表中的所有数据。

select * from departments;

选择指定的列

能够用 SELECT 语句来显示表的指定列，指定行选择。

示例：查询 departments 表中所有部门名称。

select department_name from departments;

13.3 查询中的算术表达式

需要修改数据显示方式，如执行计算，或者作假定推测，这些都可能用到算术表达式。连接。

[!tip] 注意
算术表达式只会对查询的结果进行处理，并不会对表中的数据产生影响。

使用算术运算符

示例：查询雇员的年薪，并显示他们的雇员 ID，名字。

select employees_id, last_name, 12*salary from employees;

运算符的优先级

如果算术表达式包含有一个以上的运算，乘法和除法先计算。如果在一个表达式中的运算符优先级相同，计算从左到右进行。可以用圆括号强制其中的表达式先计算。

示例一：计算 employees 表中的员工全年薪水加 100 以后的薪水是多少，并显示他们的员工 ID 与名字。

select employees_id, last_name, 12*salary+100 from employees;

示例二：计算 employees 表中的员工薪水加 100 以后的全年薪水是多少，并显示他们的员工 ID 与名字。

select employees_id, last_name, 12* (salary+100) from employees;

13.4 MySQL 中定义空值

如果一行中的某个列缺少数据值，该值被置为 null，或者说包含一个空。

空是一个难以获得的、未分配的、未知的，或不适用的值。空和 0 或者空格不相同。0 是一个数字，而空格是一个字符。

算术表达式中的空值

示例：计算年薪包含佣金。

select 12*salary*commission_pct from employees;

13.5 MySQL 中的别名

使用列别名

SELECT 列名 AS 列别名 FROM 表名 WHERE 条件;
-- 或者
SELECT 列名 列别名 FROM 表名 WHERE 条件;

示例：查询 employees 表将雇员 last_name 列定义别名为 name。

select last_name as name from employees;
select last_name name from employees;

使用表别名

SELECT 表别名.列名 FROM 表名 as 表别名;
-- 或者
SELECT 表别名.列名 FROM 表名 表别名;

示例：查询 employees 表为表定义别名为 emp，将雇员 last_name 列定义别名为 name。

select last_name as name from employees as emp;
select emp.last_name name from employees emp;

13.6 MySQL 中去除重复

除去相同的行

SELECT DISTINCT 列名 FROM 表名;

示例：查询 employees 表，显示唯一的部门 ID。

select distinct department_id from employees;

[!tip] 注意
查询 employees 表，显示部门 ID 和 salary。

select distinct department_id, salary from employees;

从运行结果中我们发现部门 ID 又有重复的了，是 distinct 失效了吗？

不是，列名之间用逗号分隔。

13.7 查询中的行选择

用 WHERE 子句限制从查询返回的行。一个 WHERE 子句包含一个必须满足的条件，WHERE 子句紧跟着 FROM 子句。如果条件是 true，返回满足条件的行。

在语法中：

• WHERE 限制查询满足条件的行
• condition 由列名、表达式、常数和比较操作组成

SELECT * | 投影列 FROM 表名 WHERE 选择条件;

示例：查询 departments 表中部门 ID 为 90 的部门名称与工作地点 ID。

select department_name, location_id from departments where department_id = 90;

13.8 MySQL 中的比较条件

[!tip]
符号 列名 也能够表示 不等于条件。

示例一：查询 employees 表中员工薪水大于等于 3000 的员工的姓名与薪水。

select last_name, salary from employees where salary >= 3000;

示例二：查询 employees 表中员工薪水不等于 5000 的员工的姓名与薪水。

select last_name, salary from employees where salary<>5000;

13.9 其他比较条件

使用 BETWEEN 条件

可以用 BETWEEN 范围条件显示基于一个值范围的行。指定的范围包含一个下限和一个上限。

固定的数字值 包含边界范围，即 算术运算符 <==> !=

示例：查询 employees 表，薪水在 3000-8000 之间的雇员 ID、名字与薪水。

select employee_id, last_name, salary 
from employees 
where salary between 3000 and 8000;
-- 等价于
select employee_id, last_name, salary
from employees
where salary >= 3000 and salary <= 8000;

使用 IN 条件

示例：查询 employees 表，找出薪水是 5000，6000，8000 的雇员 ID、名字与薪水。

select employee_id, last_name, salary
from employees
where salary in (5000, 6000, 8000);

使用 LIKE 条件

示例：查询 employees 中雇员名字第二个字母是 e 的雇员名字。

select last_name from employees where last_name like '_e%';

使用 NULL 条件

NULL 条件，包括 IS NULL 条件和 IS NOT NULL 条件。

between and 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此，一个算术表达式可以包含 salary between 2500 and 3000、salary >= 2500 and salary <= 3000 和 IS NULL。

示例一：找出 emloyees 表中那些没有佣金的雇员雇员 ID、名字与佣金。

select employee_id, last_name, commission_pct
from employees
where commission_pct is null;

示例二：找出 employees 表中那些有佣金的雇员 ID、名字与佣金。

select employee_id, last_name, commission_pct
from employees
where commission_pct is not null;

13.10 逻辑条件

逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果，或者逆转一个单个条件的结果。当所有条件的结果为真时，返回行。

SQL 的三个逻辑运算符是：

• AND
• OR
• NOT

可以在 WHERE 子句中用 AND 和 OR 运算符使用多个条件。

示例一：查询 employees 表中雇员薪水是 8000 的并且名字中含有 e 的雇员名字与薪水。

select last_name, salary
from employees
where salary = 8000 and last_name like "%e%";

示例二：查询 employees 表中雇员薪水是 8000 的或者名字中含有 e 的雇员名字与薪水。

select last_name, salary from employees where salary = 8000 or last_name like '%e%';

示例三：查询 employees 表中雇员名字中不包含 u 的雇员的名字。

select last_name from employees where last_name not like '%u%';

13.11 优先规则

在图片的例子中，有两个条件

1. 第一个条件是 job_id 是 AD_PRES 并且薪水高于 15000。
2. 第二个条件是 job_id 是 SA_REP。

在图片中的例子有两个条件

1. 第一个条件是 job_id 是 AD_PRES 或者 SA_REP。
2. 第二个条件是薪水高于 15000。

13.12 使用 ORDER BY 排序

在一个不明确的查询结果中排序返回的行。ORDER BY 子句用于排序。如果使用了 ORDER BY 子句，它必须位于 SQL 语句的最后。

SELECT 语句的执行顺序

1. FROM 子句
2. WHERE 子句
3. SELECT 子句
4. ORDER BY 子句

示例一：查询 employees 表中的所有雇员，显示他们的 ID、名字与薪水，并按薪水升序排序。

select employee_id, last_name, salary
from employees
order by salary asc;
-- 默认就是升序排序
select employee_id, last_name, salary
from employees
order by salary;

示例二：查询 employees 表中的所有雇员，显示他们的 ID 与名字，并按雇员名字降序排序。

select employee_id, last_name
from employees
order by last_name desc;

使用别名排序

示例：显示雇员 ID，名字。计算雇员的年薪，年薪列别名为 annsal，并对该列进行升序排序。

select employee_id, last_name, salary * 12 annsal
from employees
order by annsal;

多列排序

示例：以升叙排序显示 DEPARTMENT_ID 列，同时以降序排序显示 SALARY 列。

select department_id, salary
from employees
order by department_id, salary desc;

示例：显示雇员姓名，以升叙排序 DEPARTMENT_ID 列，同时以降序排序 SALARY 列。

select last_name
from employees
order by department_id, salary desc;

13.13 练习

1. 创建一个查询，显示收入超过 12000 的雇员的名字和薪水。

   select LAST_NAME, SALARY
   from employees
   WHERE SALARY > 12000;
   

2. 创建一个查询，显示雇员号为 176 的雇员的名字和部门号。

   select last_name, department_id 
   from employees 
   where employee_id = 176;
   

3. 显示所有薪水不在 5000 和 12000 之间的雇员的名字和薪水。

   select last_name, salary
   from employees
   where salary not between 5000 and 12000;
   

4. 显示所有在部门 20 和 50 中的雇员的名字和部门号，并以名字按字母顺序排序。

   select last_name, department_id
   from employees
   where department_id between 20 and 50
   order by last_name;
   

5. 列出收入在 5000 和 12000 之间，并且在部门 20 或 50 工作的雇员的名字和薪水。将列标题分别显示为 Employee 和 Monthly Salary。

   SELECT
   	last_name Employee,
   	salary "Monthly Salary" 
   FROM
   	employees 
   WHERE
   	( salary BETWEEN 5000 AND 12000 ) 
   	AND (
   	department_id IN ( 20, 50 ));
   

6. 显示所有没有主管经理的雇员的名字和工作岗位。

   select last_name, job_id
   from employees
   where manager_id is null;
   

7. 显示所有有佣金的雇员的名字、薪水和佣金。以薪水和佣金的降序排序数据。

   select last_name, salary, commission_pct
   from employees
   where commission_pct is not null
   order by salary desc, commission_pct desc;
   

8. 显示所有名字中有一个 a 和一个 e 的雇员的名字。

   SELECT
   	last_name 
   FROM
   	employees 
   WHERE
   	last_name LIKE "%a%" 
   	AND last_name LIKE "%e%";
   

9. 显示所有工作岗位是销售代表（SA_REP）或者普通职员 (ST_CLERK)，并且薪水不等于 2500、3500 或 7000 的雇员的名字、工作岗位和薪水。

   SELECT
   	last_name,
   	job_id,
   	salary 
   FROM
   	employees 
   WHERE
   	job_id IN ( "SA_REP", "ST_CLERK" ) 
   	AND salary NOT IN ( 2500, 3500, 7000 );
   

14 SQL 函数

14.1 函数介绍

函数是 SQL 的一个非常强有力的特性，函数能够用于下面的目的：

• 执行数据计算
• 修改单个数据项
• 操纵输出进行分组
• 格式化显示的日期和数字
• 转换列数据类型

SQL 函数有输入参数，并且总有一个返回值。

14.2 函数分类

• 单行函数
  单行函数仅对单个行进行运算，并且每行返回一个结果。
  常见的函数类型：
  • 字符
  • 数字
  • 日期
  • 转换
• 多行函数
  多行函数能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。

14.3 单行函数

单行函数分类

14.4 字符函数

大小写处理函数

函数	描述	实例
LOWER(S) | LCASE(S)	将字符串 s 转换 为小写	将字符串 OLDLU 转换为小写：SELECT LOWER("OLDLU"); -- oldlu
UPPER(S) | UCASE(s)	将字符串 s 转换为 大写	将字符串 oldlu 转换为大写：SELECT UPPER("oldlu"); -- OLDLU

示例：显示雇员 Davies 的雇员号、姓名和部门号，将姓名转换为大写。

select employee_id, UPPER(last_name), department_id from employees where last_name = 'davies';

[!tip] 注意
mysql 默认是忽略大小写的，如果想要某一列数据严格区分大小写，可以在创建表时为该列加上 BINARY 关键字。

字符处理函数

函数	描述	112 实例
LENGTH(S)	返回字符串 s 的长度	返回字符串 oldlu 的字符数：SELECT LENGTH("oldlu"); --5;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串：SELECT CONCAT("sxt ", "teacher ", "oldlu"); --sxt teacher oldlu
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串×填充到 oldlu 字符串的开始处：SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu 开始处的空格：SELECT LTRIM(" oldlu");-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE('oldlu','o','O"); --Oldlu
REVERSE(S)	将字符串 s 的顺序反过来	将字符串 abc 的顺序反过来：SELECT REVERSE('abc'); -- cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx 填充到 oldlu 字符串的结尾处：SELECT RPAD('oldlu',8,'x'); -- oldluxxx
RTRIM(S)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格：SELECT RTRIM("oldlu "); -- oldlu
SUBSTR(S, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU 中的第 2 个位置截取 3 个 字符：SELECT SUBSTR("OLDLU", 2, 3); -- LDL
SUBSTRING(S, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU 中的第 2 个位置截取 3 个 字符：SELECT SUBSTRING("OLDLU", 2, 3); -- LDL
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 oldlu 的首尾空格：SELECT TRIM(' oldlu ');--oldlu

示例：显示所有工作岗位名称从第 4 个字符位置开始，包含字符串 REP 的雇员的 ID 信息，将雇员的姓和名连接显示在一起，还显示雇员名的的长度，以及名字中字母 a 的位置。

SELECT employee_id, CONCAT(last_name，first_name) name, 
job_id, LENGTH(last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';

14.5 数字函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值：SELECT ABS(-1); -- 返回 1
ACOS(x)	求×的反余弦值(参数是弧度)	SELECT ACOS(0.25);
ASIN(X)	求反正弦值(参数是弧度)	SELECT ASIN(0.25);
ATAN(x)	求反正切值(参数是弧度)	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值(参数是弧度)	SELECT ATAN2(-0.8, 2);
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中 Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(X)	返回大于或等于×的最小整数	SELECT CEIL(1.5) -- 返回 2
CEILING(X)	返回大于或等于×的最小整数	SELECT CEILING(1.5); -- 返回 2
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	SELECT COT(6);
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或 者* 号	返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(X)	将弧度转换为角度	SELECT DEGREES(3.1415926535898); -- 180
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5：SELECT 10 DIV 5; -- 2
EXP(x)	返回 e 的×次方	计算 e 的三次方：SELECT EXP(3);-- 20.085536923188
FLOOR(X)	返回小于或等于×的最大整数	小于或等于 1.5 的整数：SELECT FLOOR(1.5)-- 返回 1
GREATEST(expr1,expr2, expr3,…)	返回列表中的最大值	返回以下数字列表中的最大值：SELECT GREATEST(3,12,34,8, 25); -- 34 返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob
LEAST(expr1, expr2, expr3,…)	返回列表中的最小值	返回以下数字列表中的最小值：SELECT LEAST(3,12,34,8,25); -- 3 返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple
LN	返回数字的自然对数，以 e 为底。	返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453
LOG(x) LOG(base,x)	返回自然对数 (以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数	SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2
LOG10(x)	返回以 10 为底的对数	SELECT LOG10(100); -- 2
LOG2(X)	返回以 2 为底的对数	返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
MOD(x,y)	返回×除以 y 以后的余数 返回圆周率(3.141593)	5 除于 2 的余数：SELECT MOD(5,2); -- 1
PI() POW(x,y)	返回×的次方	SELECT PI0) -- 3.141593 2 的 3 次方：SELECT POW(2,3); -- 8
POWER(x,y)	返回×的次方	2 的 3 次方：SELECT POWER(2,3); -- 8
RADIANS(X)	将角度转换为弧度	180 度转换为弧度：SELECT RADIANS(180); -- 3.1415926535898
RAND()	返回 0 到 1 的随机数	SELECT RAND() -- 0.93099315644334
ROUND(X)	返回离×最近的整数	SELECT ROUND(1.23456); -- 1
SIGN(X)	返回×的符号，×是负数、 0、正数分别返回-1、0 和 1	SELECT SIGN(-10); -- (-1)
SIN(X)	求正弦值(参数是弧度)	SELECT SIN(RADIANS(3O)); -- 0.5
SQRT(X)	返回 x 的平方根	25 的平方根：SELECT SQRT(25); -- 5
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalltemsOrdered FROM OrderDetails;
TAN(X)	求正切值(参数是弧度)	SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y)	返回数值×保留到小数点后 y 位的值（与 ROUND 最大的区 别是不会进行四舍五入)	SELECT TRUNCATE(1.23456,3);-- 1.234

ROUND(column|expression, n) 函数

ROUND 函数四舍五入列、表达式或者 n 位小数的值。如果第二个参数是 0 或者缺少，值被四舍五入为整数。如果第二个参数是 2 值被四舍五入为两位小数。如果第二个参数是 –2，值被四舍五入到小数点左边两位。

SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923, -1);

TRUNCATE(column|expression, n) 函数

TRUNCATE 函数的作用类似于 ROUND 函数。如果第二个参数是 0 或者缺少，值被截断为整数。如果第二个参数是 2，值被截断为两位小数。如果第二个参数是 –2，值被截断到小数点左边两位。与 ROUND 最大的区别是不会进行四舍五入。

SELECT TRUNCATE(45.923, 2), TRUNCATE(45.932, 0), TRUNCATE(45.932, -1);

使用 MOD(m, n) 函数

MOD 函数找出 m 除以 n 的余数。

示例：所有 job_id 是 SA_REP 的雇员的名字，薪水以及薪水被 5000 除后的余数。

SELECT last_name, salary, MOD(salary, 5000) 
FROM employees
WHERE job_id = 'SA_REP';

14.6 日期函数

在 MySQL 中distinct 是对整个查询的结果集进行去重，而不是对某个列做去重。即只有两行记录的所有列都相同时才会被认为是重复数据，但是要求不能用 =，因为 null 不能等于或不等于任何值：YYYY-MM-DD HH:MI:SS 或者 YYYY/MM/DD HH:MI:SS。

函数名	描述	实例
CURDATE()	返回当前日期	SELECT CURDATE(); -> 2018-09-19
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE(); -> 2018-09-19
CURRENT_TIME()	返回当前时间	SELECT CURRENT_TIME(); -> 19:59:02
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF("2001-01-01','2001-02-02"); -> -> 32
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
DAYNAME(d)	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME(*2011-11-11 11:11:11); -> Friday
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH(2011-11-11 11:11:11); -> 11
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期 一，以此类推	SELECT DAYOFWEEK('2011-11-11 11:11:11'); -> 6
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11'); ->315
HOUR(t)	返回 t 中的小时值	SELECT HOUR('1:2:3'); -> 1
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY("2017-06-20"); -> 2017-06-30
MONTHNAME(d)	返回日期当中的月份名称，如 November	SELECT MONTHNAME('2011-11-11 11:11:11'); -> November
MONTH(d)	返回日期 d 中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11') -> 11
NOW()	返回当前日期和时间	SELECT NOW(); -> 2018-09-19 20:57:43
SECOND(t)	返回 t 中的秒钟值	SELECT SECOND('1:2:3'); -> 3
SYSDATE()	返回当前日期和时间	SELECT SYSDATE(); -> 2018-09-19 20:57:43
TIMEDIFF(time1, time2)	计算时间差值	SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01
TO_DAYS(d)	计算日期 d 距离 0000 年 1 月 1 日的天数	SELECT TO_DAYS('0001-01-01 01:01:01'); -> 366
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK("2011-11-11 11:11:11'); -> 45
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星 期二	SELECT WEEKDAY("2017-06-15"); -> 3
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR(2011-11-11 11:11:11'); -> 45
YEAR(d)	返回年份	SELECT YEAR("2017-06-15"); -> 2017

示例一：向 employees 表中添加一条数据，雇员 ID：300，名字：kevin，email：kevin@163.cn，入职时间：2049-5-1 8:30:30，工作部门：‘IT_PROG’。

insert into employees(employee_id, last_name, email, hire_date, job_id) 
values(300, "kevin", "kevin@163.cn", "2049-5-1 8:30:30", "IT_PROG");

示例二：显示所有在部门 90 中的雇员的名字和从业的周数。雇员的总工作时间以周计算，用当前日期 (SYSDATE) 减去雇员的受顾日期，再除以 7。

SELECT last_name, (SYSDATE()-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;

14.7 转换函数

隐式数据类型转换

隐式数据类型转换是指 MySQL 服务器能够自动地进行类型转换。

如：可以将标准格式的字串日期自动转换为日期类型。

MySQL 字符串日期格式为：YYYY-MM-DD HH:MI:SS 或 YYYY/MM/DD HH:MI:SS。

显示数据类型转换

显示数据类型转换是指需要依赖转换函数来完成相关类型的转换。

如：

• DATE_FORMAT(date, format) 将日期转换成字符串。
• STR_TO_DATE(str, format) 将字符串转换成日期。

示例一：向 employees 表中添加一条数据，雇员 ID：400，名字：oldlu，email：oldlu@sxt.cn，入职时间：2049 年 5 月 5 日，工作部门：‘IT_PROG’。

insert into employees(EMPLOYEE_ID, last_name, email, HIRE_DA TE, JOB_ID) values(400, 'oldlu', 'oldlu@sxt.cn', 
STR_TO_DATE('2049 年 5 月 5 日', '%Y 年 %m 月 %d 日'), 'IT_PROG');

示例二：查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM 月 dd 日。

select DATE_FORMAT(hire_date, '%Y 年 %m 月 %d 日') from employees where last_name = 'King';

14.8 通用函数

函数名	描述	实例
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,'正确','错误');->正确
IFNULL(V1,v2)	如果 v1 的值不为 NULL，则返回 V1，否则返回 v2。	SELECT IFNULL(null,'Hello Word'); -> Hello Word
ISNULL(expression)	判断表达式是否为 NULL	SELECT ISNULL(NULL); -> 1
NULLIF(expr1, expr2)	比较两个参数是否相同，如果参数 expr1 与 expr2 相等返回 NULL，否则返回 expr1	SELECT NULLIF(25, 25); -> NULL
COALESCE(expr1, expr2, …, expr_n)	返回参数中的第一个非空表达式（从左向右)	SELECT COALESCE(NULL, NULL, NULL, 'bjsxt.com', NULL, 'google.com'); -> bjsxt.com
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 … WHEN conditionN THEN resultN ELSE result END	CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1，如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。	SELECT CASE 'oldlu' WHEN 'oldlu' THEN 'OLDLU' WHEN 'admin' THEN 'ADMIN' ELSE 'kevin' END;

示例一：查询部门编号是 50 或者 80 的员工信息，包含他们的名字、薪水、佣金。在 income 列中，如果有佣金则显示 SAL+COMM ，无佣金则显示 SAL。

SELECT last_name, salary, commission_pct,         if(ISNULL(commission_pct), 'SAL','SAL+COMM') income 
FROM employees 
WHERE department_id IN (50, 80);

示例二：计算雇员的年报酬，你需要用 12 乘以月薪，再加上它的佣金 (等于年薪乘以佣金百分比)。

SELECT
	salary,
	ifnull( commission_pct, 0 ),
	salary * 12 + salary * 12 * ifnull( commission_pct, 0 ) AN_SAL
FROM
	employees;

示例三：查询员工表，显示他们的名字、名字的长度该列名为 expr1，姓氏、姓氏的长度该列名为 expr2。在 result 列中，如果名字与姓氏的长度相同则显示空，如果不相同则显示名字长度。

select 
	first_name, 
	length(first_name) "expr1", 
	last_name, 
	length(last_name) "expr2",
	nullif(length(last_name), length(first_name)) result
from 
	employees;

示例四：查询员工表，显示他们的名字，如果 COMMISSION_PCT 值是非空，显示它。如果 COMMISSION_PCT 值是空，则显示 SALARY。如果 COMMISSION_PCT 和 SALARY 值都是空，那么显示 10。在结果中对佣金列升序排序。

select
	last_name, 
	ifnull(commission_pct, ifnull(salary, 10)) comm
from
	employees
order by
	commission_pct;
-- 简洁方式
SELECT 
	last_name, 
	COALESCE(commission_pct, salary, 10) comm 
FROM 
	employees 
ORDER BY 
	commission_pct;

示例五：查询员工表，如果 JOB_ID 是 IT_PROG，薪水增加 $10%$；如果 JOB_ID 是 ST_CLERK，薪水增加 $15%$；如果 JOB_ID 是 SA_REP，薪水增加 $20%$。对于所有其他的工作角色，不增加薪水。

select
	job_id,
	case job_id
	when "IT_PROG"
	then salary * 1.1
	when "ST_CLERK"
	then salary * 1.15
	when "SA_REP"
	then salary * 1.2
	else salary
	end "REVISED_SALARY"
from
	employees;

14.9 练习

1.显示受雇日期在 1998 年 2 月 20 日 和 2005 年 5 月 1 日之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。

select
	last_name, job_id, hire_date
from 
	employees
where 
	hire_date between date("1998-2-20") and date("2005-5-1")
order by 
	hire_date;
-- 符合日期格式的字符串会自动转型
select
	last_name, job_id, hire_date
from 
	employees
where 
	hire_date between "1998-2-20" and "2005-5-1"
order by 
	hire_date;

2.显示每一个在 2002 年受雇的雇员的名字和受雇日期。

select last_name, hire_date
from employees
where year(hire_date) = "2002";

select LAST_NAME, HIRE_DATE
FROM employees
where HIRE_DATE like '2002%';

3.对每一个雇员，显示 employee_id、last_name、salary 和 salary 增加 $15%$，并且表示成整数，列标签显示为 New Salary。

select
	employee_id,
	last_name,
	salary,
	round(salary * 1.15, 0) "New Salary"
from
	employees;

4.写一个查询，显示名字的长度，对所有名字开始字母是 J、A 或 M 的雇员。用雇员的 last_name 排序结果。

SELECT LAST_NAME, LENGTH(LAST_NAME)
FROM employees
WHERE LAST_NAME LIKE 'J%' 
	OR LAST_NAME LIKE 'A%'
	OR LAST_NAME LIKE 'M%'
ORDER BY LAST_NAME;
-- 使用 substr 函数
select last_name, length(last_name)
from employees
where substr(last_name, 1, 1) in ("J", "A", "M");

5.创建一个查询显示所有雇员的 last name 和 salary。将薪水格式化为 15 个字符长度，用 $ 左填充。

select last_name, lpad(salary, 15, "$")
from employees;

6.创建一个查询显示雇员的 last names 和 commission (佣金) 比率。如果雇员没有佣金，显示 “No Commission”，列标签 COMM。

select
	last_name,
	ifnull(commission_pct, "No Commission") COMM
from
	employees;

7.写一个查询，按照下面的数据显示所有雇员的基于 JOB_ID 列值的级别。

工作	级别
AD_PRES	A
ST_MAN	B
IT_PROG	C
SA_REP	D
ST_CLERK	E
不在上面的	0

select
	last_name,
	job_id,
	case job_id
	when "AD_PRES"
	then "A"
	when "ST_MAN"
	then "B"
	when "IT_PROG"
	then "C"
	when "SA_REP"
	then "D"
	when "ST_CLERK"
	then "E"
	else 0
	end "level"
from
	employees;

15 多表查询

15.1 多表查询简介

笛卡尔乘积

笛卡尔乘积：

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积（Cartesian product），其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的行，其结果没有什么用。你允许直接使用字符串表示日期，除非你有特殊的需求，需要从所有表中组合所有的行。

多表查询分类

• sql92 标准：内连接（等值连接、非等值连接、自连接）。
• sql99 标准：内连接、外连接（左外、右外、全外（MySQL 不支持全外连接））、交叉连接。

15.2 SQL92 标准中的查询等值连接

等值连接

为了确定一个雇员的部门名，需要比较 EMPLOYEES 表中的 DEPARTMENT_ID 列与 DEPARTMENTS 表中的 DEPARTMENT_ID 列的值。在 EMPLOYEES 和 DEPARTMENTS 表之间的关系是一个相等（equijoin）关系，即两个表中 DEPARTMENT_ID 列的值必须相等。

等值连接特点

1. 多表等值连接的结果为多表的交集部分；
2. n 表连接，至少需要 n-1 个连接条件；
3. 多表不分主次，没有顺序要求；
4. 一般为表起别名，提高阅读性和性能；
5. 可以搭配排序、分组、筛选…等子句使用；

[!tip] 注意
等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins)。

等值连接的使用

• SELECT 子句指定要返回的列名：
  • employee last name、employee number 和 department number，这些是 EMPLOYEES 表中的列。
  • department number、department name 和 location ID，这些是 DEPARTMENTS 表中的列。
• FROM 子句指定数据库必须访问的两个表：
  • EMPLOYEES 表
  • DEPARTMENTS 表
• WHERE 子句指定表怎样被连接：
  EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID，因为 DEPARTMENT_ID 列是两个表的同名列，它必须用表名做前缀以避免混淆。

增加搜索条件

添加查询条件

除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。

限制不明确的列

字符串的日期格式必须为

• 需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID 列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。
• 如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为 MySQL 服务器可以根据表前缀找到对应的列。
• 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT 子句或 ORDERBY 子句。

使用表别名

应该在 WHERE 子句中始终包含一个有效的连接条件

• 表别名不易过长，短一些更好。
• 表别名应该是有意义的。
• 表别名只对当前的 SELECT 语句有效。

多表连接

示例一：查询雇员 King 所在的部门名称。

select d.department_name from employees e, departments d where e.dept_id = d.department_id and e.last_name = 'King';

示例二：显示每个雇员的 last name、departmentname 和 city。

SELECT e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id;

非等值连接

非等值连接

一个非等值连接是一种不同于等值操作的连接条件。EMPLOYEES 表 和 JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个表之间的关系是 EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的 LOWEST_SALARY 和 HIGHEST_SALARY 列之间的值。限制不明确的列名。

示例一：创建 job_grades 表，包含 lowest_sal ，highest_sal ，grade_level。

create table job_grades(lowest_sal int, highest_sal int, grade_level varchar(30));

示例二：插入数据

1000 2999 A
2000 4999 B
5000 7999 C
8000 12000 D

insert into job_grades values(1000, 2999, 'A');
insert into job_grades values(2000, 4999, 'B');
insert into job_grades values(5000, 7999, 'C');
insert into job_grades values(8000, 12000, 'D');

示例三：查询所有雇员的薪水级别。

select e.last_name, j.grade_level
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal;

自连接

自连接

有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接 EMPLOYEES 表到它自己，或执行一个自连接。

图片中的例子连接 EMPLOYEES 表到它自己。为了在 FROM 子句中模拟两个表，对于相同的表 EMPLOYEES，用两个别名，分别为 worker 和 manager。在该例中，WHERE 子句包含的连接意味着“一个工人的经理号匹配该经理的雇员号”。

示例一：查询每个雇员的经理的名字以及雇员的名字，雇员名字列别名为 W，经理列别名为 M。

SELECT
select worker.last_name W, manager.last_name M
from employees worker, employees manager
where worker.manager_id = manager.employee_id;

示例二：查询 Fox 的经理是谁？显示他的名字。

select worker.last_name, manager.last_name
from employees worker, employees manager
where worker.last_name = "Fox" and worker.manager_id = manager.employee_id;

15.3 SQL99 标准中的查询

MySQL5.7 支持部分的 SQL99 标准。

SQL99 中的交叉连接 (CROSS JOIN)

表别名定义原则。

示例：使用交叉连接查询 employees 表与 departments 表。

select *
from employees, departments;
-- 使用 cross join 关键字
select *
from employees cross join departments;

SQL99 中的自然连接 (NATURAL JOIN)

自然连接

连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

使用 * 不同于 * 等于（=）的操作符获得关系

在图片例子中，LOCATIONS 表被用 LOCATION_ID 列连接到 DEPARTMENT 表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为 and。

可以看到，虽然使用自然连接 NATURAL JOIN 代替更简洁，但其远不如 SQL92 中的等值连接那样灵活。因为自然连接 NATURAL JOIN 是一定会自动查询两张表中我们实际使用过程中都希望尽力避免笛卡尔乘积，故此连接方式几乎不用相同的字段，但很多时候我们仅需要一个相同字段作为连接条件即可，并不需要所有相同字段作为连接条件，因此自然连接 NATURAL JOIN 的应用范围是十分有限的。

自然连接也可以被写为等值连接：

SELECT d.department_id, d.department_name, d.location_id, l.city
FROM departments d, locations l
WHERE d.location_id = l.location_id;

示例：使用自然连接查询所有有部门的雇员的名字以及部门名称。（注意自然连接会将所有列名相同的列进行等值连接）

-- 使用自然连接 natural join
select e.last_name, d.department_name
from employees e natural join departments d; 
-- 使用等值连接
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id and e.manager_id = d.manager_id;

SQL99 中的内连接 (INNER JOIN)

语法

• SELECT 查询列表
• FROM 表 1 别名
• INNER JOIN 连接表 (INNER 关键字可省略)
• ON 连接条件

可以使用 inner join 接来实现 SQL92 中的自然连接查询、所有、等值连接。

等值连接实现：

-- SQL99 内连接实现等值连接
select e.employee_id, e.salary, d.department_name
from employees e
inner join departments d
on e.department_id = d.department_id
where e.last_name = "Fox";
-- SQL92 等值连接实现
select e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.last_name = "Fox" and e.department_id = d.department_id;

非等值连接

-- SQL99 内连接实现非等值连接
select e.last_name, j.grade_level
from employees e
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;
-- SQL92 非等值连接实现
select e.last_name, j.grade_level
from employees e, job_grades j
where e.salary between j.lowest_sal and j.highest_sal;

自连接

-- SQL99 内连接实现自连接
select worker.last_name, manager.last_name
from employees worker
join employees manager
on worker.manager_id = manager.employee_id
where worker.last_name = "Fox";
-- SQL92 实现自连接
select worker.last_name, manager.last_name
from employees worker, employees manager
where worker.last_name = "Fox" and worker.manager_id = manager.employee_id;

用 ON 子句指定连接条件

用 ON 子句指定更多的连接条件

示例：查询雇员名字为 Fox 的雇员 ID，薪水与部门名称。

-- SQL99 内连接实现
select e.employee_id, e.salary, d.department_name
from employees e
inner join departments d
on e.department_id = d.department_id
where e.last_name = "Fox";
-- SQL92 等值连接实现
select e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.last_name = "Fox" and e.department_id = d.department_id;

外连接查询 (OUTER JOIN)

孤儿数据 (Orphan Data)

孤儿数据是指被连接的列的值为空的数据。

左外连接 (LEFT OUTER JOIN)

左外连接

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行。

示例：查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

select e.last_name, d.department_name
from employees e
left outer join departments d
on e.department_id = d.department_id;

右外连接 (RIGTH OUTER JOIN)

右外连接

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询也会取回 DEPARTMENTS 表中所有的行。

示例：查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

select e.last_name, d.department_name
from employees e
right outer join departments d
on e.department_id = d.department_id;

全外连接 (FULL OUTER JOIN)

[!tip] 注意
MySQL 中不支持 FULL OUTER JOIN 连接。

可以使用 union 实现全完连接。

• UNION：可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。
• UNION ALL：只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

语法结构

(SELECT  投影列 FROM 表名 LEFT OUTER JOIN  表名 ON  连接条件) 
UNION 
(SELECT  投影列 FROM 表名 RIGHT OUTER JOIN  表名 ON  连接条件);

示例：查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。

(select e.last_name, d.department_name
from employees e
left join departments d
on e.department_id = d.department_id)
union
(select e1.last_name, d1.department_name
from employees e1
right join departments d1
on e1.department_id = d1.department_id);

15.4 练习

1.写一个查询显示所有雇员的 last name、department id 和 department name。

-- SQL99 内连接实现
select e.last_name, e.department_id, d.department_name
from employees e
inner join departments d
on e.department_id = d.department_id;
-- SQL92 等值连接实现
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

2.创建一个在部门 80 中的所有工作岗位的唯一列表，在输出中包括部门的地点。

SELECT DISTINCT
e.JOB_ID, d.LOCATION_ID
FROM employees e, departments d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND e.DEPARTMENT_ID = 80;

3.写一个查询显示所有有佣金的雇员的 last name、department name、location ID 和城市。

-- SQL99
select
	e.last_name,
	d.department_name,
	d.location_id,
	l.city
from
	employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where
	e.commission_pct is not null;
-- SQL92
select
	e.last_name,
	d.department_name,
	d.location_id,
	l.city
from
	employees e,
	departments d,
	locations l
where
	e.commission_pct is not null
	and
	e.department_id = d.department_id
	and
	d.location_id = l.location_id;

4.显示所有在其 last name 中有一个小写 a 的雇员的 last name 和 department name。

-- SQL99
select e.last_name, d.department_name
from employees e
join departments d
on e.department_id = d.department_id
where e.last_name like "%a%";
-- SQL92
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id 
and e.last_name like "%a%";

5.用 sql99 的内连接写一个查询显示那些工作在 Toronto 的所有雇员的 last name、job、department id 和 department name。

select e.last_name, e.job_id, e.department_id, d.department_name
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where l.city = "Toronto";

6.显示雇员的 last name 和 employee number 连同他们的经理的 last name 和 manager number。列标签分别为 Employee、Empid、Manager 和 Mgrid。

-- SQL99
select
	worker.last_name Employee,
	worker.employee_id Empid,
	manager.last_name Manager,
	manager.employee_id Mgrid
from 
	employees worker
join 
	employees manager
on 
	worker.manager_id = manager.employee_id;
-- SQL92
select
	worker.last_name Employee,
	worker.employee_id Empid,
	manager.last_name Manager,
	manager.employee_id Mgrid
from 
	employees worker, employees manager
where
	worker.manager_id = manager.employee_id;

16 聚合函数

16.1 聚合函数介绍

聚合函数

聚合函数也称之为多行函数，组函数或分组函数。聚合函数不象单行函数，聚合函数对行的分组进行操作，对每组给出一个结果。如果在查询中没有指定分组，那么聚合函数则将查询到的结果集视为一组。

聚合函数类型

聚合函数说明：

函数名	描述	实例
AVG(expression)	返回一个表达式的平均 值，expression 是一个字段	返回 Products 表中 Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
COUNT(expression)	返回查询的记录总数， expression 参数是一个字段或者* 号	返回 Products 表中 products 字段总共有多少条记 录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products;
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalltemsOrdered FROM OrderDetails;

聚合函数使用方式

使用聚合函数的原则

• DISTINCT 使得函数只考虑不重复的值；
• 所有聚合函数忽略空值。为了用一个值代替空值，用 IFNULL 或 COALESCE 函数。

16.2 AVG 和 SUM 函数

非等值连接

• 对分组数据做平均值运算。
• arg: 参数类型只能是数字类型。

内连接

• 对分组数据求和。
• arg: 参数类型只能是数字类型。

示例：计算员工表中工作编号含有 REP 的工作岗位的平均薪水与薪水总和。

select avg(e.salary), sum(e.salary)
from employees e
where e.job_id like "%REP%";

16.3 MIN 和 MAX 函数

AVG(arg) 函数

• 求分组中最小数据。
• arg: 参数类型可以是字符、数字、日期。

SUM(arg) 函数

• 求分组中最大数据。
• arg: 参数类型可以是字符、数字、日期。

示例：查询员工表中入职时间最短与最长的员工，并显示他们的入职时间。

select min(hire_date), max(hire_date)
from employees;

16.4 COUNT 函数

返回分组中的总行数。

COUNT 函数有三种格式：

• COUNT(*)：返回表中满足 SELECT 语句的所有列的行数，包括重复行，包括有空值列的行。
• COUNT(expr)：返回在列中的由 expr 指定的非空值的数。
• COUNT(DISTINCT expr)：返回在列中的由 expr 指定的唯一的非空值的数。

使用 DISTINCT 关键字

• COUNT(DISTINCT expr) 返回对于表达式 expr 非空并且值不相同的行数。
• 显示 EMPLOYEES 表中不同部门数的值。

示例一：显示员工表中部门编号是 80 中有佣金的雇员人数。

select count(commission_pct)
from employees
where department_id = 80;

示例二：显示员工表中的部门数。

select count(distinct job_id)
from employees;

组函数和 Null 值

在组函数中使用 IFNULL 函数。

SELECT AVG(IFNULL(commission_pct, 0)) FROM employees;

17 数据分组 (GROUP BY)

17.1 创建数据组

在没有进行数据分组之前，所有聚合函数是将结果集作为一个大的信息组进行处理。但是，有时，则需要将表的信息划分为较小的组，可以用 GROUP BY 子句实现。

GROUP BY 子句语法

MIN(arg) 函数

• 使用 WHERE 子句，可以在划分行成组以前过滤行。
• 如果有 WHERE 子句，那么 GROUP BY 子句必须在 WHERE 的子句后面。
• 如果有 ORDER BY 子句，那么 GROUP BY 子句必须在 ORDER BY 的子句前面。
• 在 GROUP BY 子句中必须包含列。

使用 GROUP BY 子句

GROUP BY 子句

下面是包含一个 GROUP BY 子句 SELECT 语句的求值过程：

• SELECT 子句指定要返回的列。
• 在 EMPLOYEES 表中的部门号
  • GROUP BY 子句中指定分组的所有薪水的平均值
  • FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
• WHERE 子句指定被返回的行。因为无 WHERE 子句默认情况下所有行被返回。
• GROUP BY 子句指定行怎样被分组。行用部门号分组，所以 AVG 函数被应用于薪水列，以计算每个部门的平均薪水。

示例：计算每个部门的员工总数。

select department_id, count(employee_id)
from employees
group by department_id;

17.2 在多列上使用分组

在组中分组

可以列出多个 GROUP BY 列返回组和子组的摘要结果。可以用 GROUP BY 子句中的列的顺序确定结果的默认排序顺序。下面是图片中的 SELECT 语句中包含一个 GROUP BY 子句时的求值过程：

• SELECT 子句指定被返回的列：
  • 部门号在 EMPLOYEES 表中
  • Job ID 在 EMPLOYEES 表中
  • 在 GROUP BY 子句中指定的组中所有薪水的合计
• FROM 子句指定数据库必须访问的表：EMPLOYEES 表。
• GROUP BY 子句指定你怎样分组行：
  • 首先，用部门号分组行。
  • 第二，在部门号的分组中再用 job ID 分组行。

如此 SUM 函数被用于每个部门号分组中的所有 job ID 的 salary 列。

示例：计算每个部门的不同工作岗位的员工总数。

select department_id, job_id, count(employee_id)
from employees
group by department_id, job_id;

17.3 约束分组结果 (HAVING)

HAVING 子句

HAVING 子句是对查询出结果集分组后的结果进行过滤。

约束分组结果

用 WHERE 子句约束选择的行，用 HAVING 子句约束组。为了找到每个部门中的最高薪水，而且只显示最高薪水大于 $10000 的那些部门，可以象下面这样做：

• 用部门号分组，在每个部门中找最大薪水。
• 返回那些有最高薪水大于 $10000 的雇员的部门。

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

HAVING 子句语法

示例：显示那些合计薪水超过 13000 的每个工作岗位的合计薪水。排除那些 JOB_ID 中含有 REP 的工作岗位，并且用合计月薪排序列表。

select job_id, sum(salary)
from employees
where job_id not like "%REP%"
group by job_id
having sum(salary) > 13000
order by sum(salary);

17.4 练习

1.显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Max、Min、Sum 和 Avg。四舍五入结果为最近的整数。

select 
	round(max(salary), 0) Max, 
	round(min(salary), 0) Min, 
	round(sum(salary), 0) Sum, 
	round(avg(salary), 0) Avg
from employees;

2.写一个查询显示每一工作岗位的人数。

select job_id, count(employee_id)
from employees
group by job_id;

3.确定经理人数，不需要列出他们，列标签是 Number of Managers。提示：用 MANAGER_ID 列决定经理号。

select count(distinct manager_id) "Number of Managers"
from employees;

4.写一个查询显示最高和最低薪水之间的差。

select max(salary) - min(salary)
from employees;

5.显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。

select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) > 6000
order by min(salary) desc;

6.写一个查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数。

select 
	d.department_name, 
	d.location_id, 
	count(e.employee_id), 
	round(avg(e.salary), 2)
from employees e
join departments d
on e.department_id = d.department_id
group by e.department_id;

18 子查询

18.1 子查询介绍

用子查询解决问题

假如要写一个查询来找出挣钱比 Abel 的薪水还多的人。为了解决这个问题，需要两个查询：一个找出 Abel 的收入，第二个查询找出收入高于 Abel 的人。可以用组合两个查询的方法解决这个问题。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

子查询语法

子查询

子查询是一个 SELECT 语句，它是嵌在另一个 SELECT 语句中的子句。使用子查询可以用简单的语句构建功能强大的语句。

可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句
• HAVING 子句
• FROM 子句

使用子查询

• 子查询放在圆括号中。
• 将子查询放在比较条件的右边。
• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

子查询类型

示例：查询与 Fox 同一部门的同事，并显示他们的名字与部门 ID。

SELECT
	e.last_name,
	e.department_id 
FROM
	employees e 
WHERE
	e.department_id = ( SELECT e1.department_id FROM employees e1 WHERE e1.last_name = "Fox" );

18.2 单行子查询

单行子查询

单行子查询是从内查询返回一行的查询。在该子查询类型中用一个单行操作符。

示例：查询 Fox 的同事，但是不包含他自己。

SELECT
	e.last_name,
	e.department_id 
FROM
	employees e 
WHERE
	e.department_id = ( SELECT e1.department_id FROM employees e1 WHERE e1.last_name = "Fox" )
and e.last_name <> "Fox";

18.3 多行子查询

多行子查询

子查询返回多行被称为多行子查询。对多行子查询要使用多行运算符而不是单行运算符。

使用 ANY 运算符

ANY 运算符

ANY 运算符比较一个值与一个子查询返回的每一个值。

• < ANY 意思是小于最大值。

• ANY 意思是大于最小值

• = ANY 等同于 IN。

使用 ALL 运算符

ALL 运算符比较一个值与子查询返回的每个值。

• < ALL 意思是小于最小值。

• ALL 意思是大于最大值。

NOT 运算符可以与 IN 运算符一起使用。

子查询中的空值

内查询返回的值含有空值，并因此整个查询无返回行，原因是用大于、小于或不等于比较 Null 值，都返回 null。所以，只要空值可能是子查询结果集的一部分，就不能用 NOT IN 运算符。NOT IN 运算符相当于 <> ALL。

[!tip] 注意
空值作为一个子查询结果集的一部分，如果使用 IN 操作符的话，不是一个问题。IN 操作符相当于 = ANY。

SELECT emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id 
FROM employees mgr);

示例：查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。

select e.last_name, e.salary, e.department_id
from employees e, (
	select min(e1.salary) min_sal, e1.department_id id
	from employees e1
	group by e1.department_id) res
where e.salary = res.min_sal 
	and ifnull(e.department_id, "null") = ifnull(res.id, "null");

18.4 练习

1.写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和 hire date，结果中不包括 Zlotkey。

SELECT
	e.last_name,
	e.hire_date 
FROM
	employees e 
WHERE
	e.last_name <> "Zlotkey" 
	AND e.department_id = 
	( SELECT e1.department_id 
		FROM employees e1 
		WHERE e1.last_name = "Zlotkey" );

2.创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。按薪水的升序排序。

SELECT
	e.employee_id,
	e.last_name 
FROM
	employees e 
WHERE
	e.salary > ( SELECT avg( e1.salary ) FROM employees e1 ) 
ORDER BY
	e.salary;

3.写一个查询显示所有工作在有任一雇员的名字中包含一个 u 的部门的雇员的雇员号和名字。

SELECT
	e.employee_id,
	e.last_name 
FROM
	employees e 
WHERE
	e.department_id IN 
	( SELECT distinct e1.department_id 
		FROM employees e1 
		WHERE e1.last_name LIKE "%u%" );

4.显示所有部门地点号 (department location ID ) 是 1700 的雇员的 last name、department number 和 job ID。

select
	e.last_name, 
	e.department_id, 
	e.job_id
from 
	employees e
where
	e.department_id in (
	select d.department_id
	from departments d
	where d.location_id = 1700
	);

5.显示每个向 King 报告的雇员的名字和薪水。

select e.last_name, e.salary
from employees e
where e.manager_id in (
	select e1.employee_id
	from employees e1
	where e1.last_name = "King"
	);

6.显示在 Executive 部门的每个雇员的 department number、lastname 和 job ID。

select e.department_id, e.last_name, e.job_id
from employees e
where e.department_id = (
	select d.department_id
	from departments d
	where d.department_name = "Executive"
);

19 MySQL 中的索引

19.1 索引介绍

索引是对数据库表中的一列或多列值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。索引是一种特殊的文件，它们包含着对数据表里所有记录的位置信息。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

索引的作用

索引相当于图书上的目录，可以根据目录上的页码快速找到所需的内容，提高性能（查询速度）。

索引优点

1. 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性；
2. 可以加快数据的检索速度；
3. 可以加速表与表之间的连接；
4. 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间；

索引缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
2. 索引需要占用物理空间，数据量越大，占用空间越大；
3. 会降低表的增删改的效率，因为每次增删改索引都需要进行动态维护；

什么时候需要创建索引

1. 频繁作为查询条件的字段应该创建索引；
2. 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）；
3. 查询中统计或者分组的字段；

什么时候不需要创建索引

1. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件；
2. where 条件里用不到的字段，不创建索引；
3. 表记录太少，不需要创建索引；
4. 经常增删改的表；
5. 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引；

MySQL 中的索引类型

• 普通索引：
  最基本的索引，它没有任何限制。

• 唯一索引：
  索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。

• 主键索引：
  特殊的索引，唯一的标识一条记录，不能为空，一般用 primarykey 来约束。

• 联合索引：
  在多个字段上建立索引，能够加速查询到速度。

19.2 普通索引

是最基本的索引，它没有任何限制。在创建索引时，可以指定索引长度。length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度，如果是 BLOB 和 TEXT 类型，必须指定 length。

[!tip] 创建索引时需要注意
如果指定单列索引长度，length 必须小于这个字段所允许的最大字符个数。

查询索引

SHOW INDEX FROM table_name;

直接创建索引

CREATE INDEX index_name ON table(column(length));

示例：为 emp3 表中的 name 创建一个索引，索引名为 emp3_name_index；

create index emp3_name_index ON emp3(name);

修改表添加索引

ALTER TABLE table_name ADD INDEX index_name (column(length));

示例：修改 emp3 表，为 addrees 列添加索引，索引名为 emp3_address_index；

alter table emp3 add index emp3_address_index(address);

创建表时指定索引列

CREATE TABLE `table` (
COLUMN TYPE,
PRIMARY KEY (`id`),
INDEX index_name (column(length)));

示例：创建 emp4 表，包含 emp_id，name，address 列，同时为 name 列创建索引，索引名为 emp4_name_index。

create table emp4(
	emp_id int primary key auto_increment, 
	name varchar(30), address varchar(50), 
	index emp4_name_index(name));

删除索引

DROP INDEX index_name ON table_name;

示例：删除 emp3 表中索引名为 emp3_address_index 的索引。

drop index emp3_address_index on emp3;

19.3 唯一索引

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length));

示例：为 emp 表中的 name 创建一个唯一索引，索引名为 emp_name_index。

create unique index emp_name_index on emp(name);

修改表添加唯一索引

ALTER TABLE table_name ADD UNIQUE indexName (column(length));

示例：修改 emp 表，为 salary 列添加唯一索引，索引名为 emp_salary_index。

alter table emp add unique emp_salary_index(salary);

创建表时指定唯一索引

CREATE TABLE `table` (
COLUMN TYPE,
PRIMARY KEY (`id`),
UNIQUE index_name (column(length)) );

示例：创建 emp5 表，包含 emp_id，name，address 列，同时为 name 列创建唯一索引。索引名为 emp5_name_index。

create table emp5(
	emp_id int primary key auto_increment,
	name varchar(30),
	address varchar(50),
	unique emp5_name_index(name)
);

19.4 主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

修改表添加主键索引

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

示例：修改 emp 表为 employee_id 添加主键索引。

alter table emp add primary key(employee_id);

创建表时指定主键索引

CREATE TABLE `table` ( COLUMN TYPE ,
PRIMARY KEY(column)
);

示例：创建 emp6 表，包含 emp_id，name，address 列，同时为 emp_id 列创建主键索引。

create table emp6(employee_id int primary key auto_increment, name varchar(20), address varchar(50));

19.5 组合索引

组合索引是指使用多个字段创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用（最左前缀原则）。

最左前缀原则

就是最左优先。

如：我们使用表中的 name，address，salary 创建组合索引，那么想要组合索引生效，我们只能使用如下组合：

• name/address/salary
• name/address
• name/

如果使用 addrees/salary 或者是 salary 则索引不会生效。

添加组合索引

ALTER TABLE table_name ADD INDEX index_name (column(length),column(length)[,...]);

示例：修改 emp6 表，为 name，address 列创建组合索引。

alter table emp6 add index emp6_index_n_a(name, address);

创建表时创建组合索引

CREATE TABLE `table` (
COLUMN TYPE ,
INDEX index_name (column(length),column(length)) );

示例：创建 emp7 表，包含 emp_id，name，address 列，同时为 name，address 列创建组合索引。

create table emp7(
	emp_id int primary key auto_increment,
	name varchar(30),
	address varchar(50),
	index emp7_index_n_a(name, address)
);

20 MySQL 事务

20.1 事务简介

事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

MAX(arg) 函数

• 事务是一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务 (例如银行账户转账业务，该业务就是一个最小的工作单元)。
• 一个完整的业务需要批量的 DML(insert、update、delete) 语句共同联合完成。
• 事务只和 DML 语句有关，或者说 DML 语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML 语句的个数不同。

原则

• 原子性 (ATOMICITY)
  事务中的操作要么都不做，要么就全做。

• 一致性 (CONSISTENCY)
  一个事务应该保护所有定义在数据上的不变的属性 (例如完整性约束)。在完成了一个成功的事务时，数据应处于一致的状态。

• 隔离性 (ISOLATION)
  一个事务的执行不能被其他事务干扰。

• 持久性 (DURABILITY)
  一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

事务定义 (Transaction)

• 显式事务
  需要我们手动的提交或回滚。DML 语言中的所有操作都是显示事务操作。

• 隐式事务
  数据库自动提交不需要我们做任何处理，同时也不具备回滚性。DDL、DCL 语言都是隐式事务操作。

20.2 使用事务

TCL 语句	描述
start transaction	事务开启
commit	事物提交
rollback	事物回滚

示例一：创建 account 账户表，包含 id、卡号、用户名、余额。

create table account(
id int primary key auto_increment, cardnum varchar(20) not null, username varchar(30) not null, balance double(10,2)
);

示例二：向 account 表中插入两条数据。

insert into account(cardnum, username, balance) VALUES('123456789', '张三', 2000);
insert into account(cardnum, username, balance) 
VALUES('987654321', '李四', 2000);

示例三：在一个事务中完成转账业务。

START TRANSACTION
update account set balance = balance-200 where cardnum = '123456789';
update account set balance = balance+200 where cardnum = '987654321';
select * from account;
-- 当我们关闭数据库重新打开后，张三和李四的账户余额并没发生任何变化。
-- 这是因为当我们使用“START TRANSACTION”开启一个事务后，该事务的提交方式不再是自动的。
-- 而是需要手动提交，而在这里，我们并没有使用事务提交语句COMMIT。
-- 所以对account表中数据的修改并没有永久的保存到数据库中，也就是说我们的转账事务并没有执行成功。

-- 提交转账事务
commit;

-- 事务的回滚让数据库恢复到了执行事务操作前的状
态。
-- 需要注意的是事务的回滚必须在事务提交之前，因为事务一旦提交就不能再进行回滚操作。
rollback;

20.3 事务的并发问题

脏读（读取未提交数据）

指一个事务读取了另外一个事务未提交的数据。

A 事务读取 B 事务尚未提交的数据，此时如果 B 事务发生错误并执行回滚操作，那么 A 事务读取到的数据就是脏数据。

不可重复读（前后多次读取，数据内容不一致）

在一个事务内读取表中的某一行数据，多次读取结果不同。

事务 A 在执行读取操作，由整个事务 A 比较大，前后读取同一条数据需要经历很长的时间 。而在事务 A 第一次读取数据，比如此时读取了小明的年龄为 20 岁，事务 B 执行更改操作，将小明的年龄更改为 30 岁，此时事务 A 第二次读取到小明的年龄时，发现其年龄是 30 岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读（前后多次读取，数据总量不一致）

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

事务 A 在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务 B 执行了新增数据的操作并提交后，这个时候事务 A 读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

20.4 事务的隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以 MySQL 中提供了四种隔离级别来解决上述问题。

事务的隔离级别从低到高依次为：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

隔离级别越低，越能支持高并发的数据库操作。

查看 MySQL 默认事务隔离级别

SELECT @@transaction_isolation;

设置事务隔离级别

对当前 session 有效。

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

21 MySQL 的用户管理

MySQL 是一个多用户的数据库系统，按权限，用户可以分为两种：root 用户，超级管理员，和由 root 用户创建的普通用户。

21.1 用户管理

创建用户

CREATE USER username IDENTIFIED BY password';

查看用户

SELECT USER, HOST FROM mysql.user;

示例：创建一个 u_sxt 的用户，并查看创建是否成功。

create user u_sxt IDENTIFIED by 'sxt';
select user, host from mysql.user;

21.2 权限管理

新用户创建完后是无法登陆的，需要分配权限。

GRANT 权限 ON 数据库.表 TO 用户名@登录主机 IDENTIFIED BY "密码";

事务四大特征 (ACID)：

字段	含义
%	匹配所有主机
localhost	localhost 不会被解析成 IP 地址，直接通过 UNIXsocket 连接
127.0.0.1	会通过 TCP/IP 协议连接，并且只能在本机访问
::1	::1 就是兼容支持 ipv6 的，表示同 ipv4 的 127.0.0.1

事务类型：

权限	作用范围	作用
all [privileges]	服务器	所有权限
select	表、列	选择行
insert	表、列	插入行
update	表、列	更新行
delete	表	删除行
create	数据库、表、索引	创建
drop	数据库、表、视图	删除
reload	服务器	允许使用 flush 语句
shutdown	服务器	关闭服务
process	服务器	查看线程信息
file	服务器	文件操作
grant option	数据库、表、 存储过程	授权
references	数据库、表	外键约束的父表
index	表	创建/删除索引
alter	表	修改表结构
show databases	服务器	查看数据库名称
super	服务器	超级权限
create temporary tables	表	创建临时表
lock tables	数据库	锁表
execute	存储过程	执行
replication client	服务器	允许查看主/从/二进制日志状态
replication slave	服务器	主从复制
create view	视图	创建视图
show view	视图	查看视图
create routine	存储过程	创建存储过程
alter routine	存储过程	修改/删除存储过程
create user	服务器	创建用户
event	数据库	创建/更改/删除/查看事件
trigger	表	触发器
create tablespace	服务器	创建/更改/删除表空间/日志文件
proxy	服务器	代理成为其它用户
usage	服务器	没有权限

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

示例：为 u_sxt 用户分配只能查询 bjsxt 库中的 emp 表，并且只能在本机登陆的权限。

grant select ON bjsxt.emp to 'u_sxt'@'localhost' IDENTIFIED by 'sxt';

刷新权限

每当调整权限后，通常需要执行以下语句刷新权限。

FLUSH PRIVILEGES;

删除用户

DROP USER username@localhost;

示例：删除 u_sxt 用户。

drop user 'u_sxt'@'localhost';

22 Navicat 工具

22.1 创建用户

分配权限

删除用户

22.2 数据的导入导出

导出 sql 脚本文件

导入 sql 脚本文件

23 MySQL 分页查询

登陆主机：

• 在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
• MySQL 分页中开始位置为 0。
• 分页子句在查询语句的最后侧。

23.1 LIMIT 子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY 排序列 LIMIT 开始位置，查询数量;

示例：查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。

select * from employees order by employees_id limit 0, 2;

23.2 LIMIT OFFSET 子句

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY 排序列 LIMIT 查询数量 OFFSET 开始位置;

示例：查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。

select * from employees order by employees_id limit 2 offset 4;