MySQL入门篇

学习地址1

学习地址2

一、数据库概述

1.为什么使用数据库

• 持久化（persistence）：把数据保到可掉电式存储设备中以供之后使用，数据持久化意味着将内存中的数据保存到硬盘上加以“固化”，而持久化的实现过程大多通过各种关系数据库来完成。
• 持久化的主要作用是将内存中数据存储在关系型数据库中，当然也可以直接存储在磁盘文件，XML数据文件中。
  

2.数据库与数据库管理系统

2.1 数据库的相关概念

2.2 数据库与数据管理系统的关系

数据库管理系统（DBMS）可以管理多个数据库，一般开发人员会针对每一个应用去创建一个数据库。为保存应用中实体的数据，一般会在数据库中创建多个表，以保存程序中实体用户的数据。
数据库管理系统、数据库和表的关系如图表示：

3.MySQL介绍

3.1 概述

• MySQL是一个开放源代码的关系型数据库管理系统。
• MySQL6.X版本之后分为社区版和商业版。
• MySQL是可以定制的，采用了GPL(GNU General Public License)协议，你可以修改源码来开发自己的MySQL系统。
• MySQL支持大型数据库。可以处理拥有上千万条记录的大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件位8TB。

4.RDBMS与非RDBMS

从排名中我们能看出来，关系型数据库绝对是DBMS的主流，其中使用最多的DBMS分别是Oracle、MySQL和SQL Server。这些都是关系型数据库（RDBMS）。

4.1 关系型数据库（RDBMS）

4.1.1 实质

• 这种类型是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。
• 关系型数据库以行（row）和列（column）的形式存储数据，以便于用户理解。这一系列的行和列被称为表（table），一组表组成了一个库（database）。
• 表与表之间的数据记录有关系（relationship）。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系型数据库，就是建立在关系模型基础上的数据库。
• SQL就是关系型数据库的查询语言。

4.1.2 优势

• 复杂查询
  可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持
  使得对于安全性能很高的数据访问要求得以现实。

4.2 非关系型数据库（非RDBMS）

4.2.1 介绍

非关系型数据库，可看成传统关系型数据库功能的阉割版本，基于键值对存储数据，不需要经过SQL层的解析，性能非常高。同时，通过减少不常用的功能，进一步挺高性能。

4.2.2 有哪些非关系型数据库

相比于SQL，NoSQL泛指非关系型数据库，包括了键值对型数据库，文档型数据库，搜索引擎和列存储等，除此之外还包括了图形数据库。也只有用NoSQL一次才能将这些计数囊括出来。

5. 关系型数据库设计规则

• 关系型数据库的典型数据结构就是数据表，这些数据表的组成都是结构化的（Structured）。
• 将数据放到表中，表再放到库中。
• 一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。
• 表具有一些特性，这些特性定义了数据再表中如何存储，类似Java和Python中“类”的设计。

5.1 表、记录、字段

• E-R（entity-relationship，实体-联系）模型中有三个主要概念是：实体集、属性、联系集。
• 一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
  

ORM思想（Object Relational Mapping）体现：
数据库中的一个表 <—> Java中的一个类
表中的一条数据 <—> 类中的一个对象（或实体）
表中的一个列 <—> 类中的一个字段、属性（field）

5.2 表的关联联系

• 表与表之间的数据记录有关系（relationship）。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
• 四种：一对一关联、一对多关联、多对多关联、自我引用

5.2.1 一对一关联（one - to - one）

• 在实际开发中应用不多，因为一对一可以创建成一张表。

• 举例：设计学生表：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…

  • 拆为两个表：两个表的记录是一一对应关系。
  • 基础信息表（常用信息）：学号 、姓名、手机号码、班级、系别
  • 档案信息表（不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

• 两种建表原则：

  • 外键唯一：主表的主键和从表的外键(唯一)，形成主外键关系，外键唯一。
  • 外键是主键：主表的主键和从表的主键，形成主外键关系。
    

5.2.2 一对多关系（one-to-many）

• 常见实例场景：客户表和订单表，分类表和商品表，部门表和员工表。
• 举例：
  • 员工表：编号、姓名、…、所属部门
  • 部门表：编号、名称、简介
• 一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键
  
  

5.2.3 多对多关系（many-to-many）

要表示多对多关系，必须创建第三个表，该表通常称为联接表，它将多对多关系划分为两个一对多的关系。将这两个表的主键都插入到第三个表中。

举例1：学生-课堂

• 学生信息表：一行代表一个学生的信息（学号、姓名、手机号码、班级、系别…）
• 课程信息表：一行代表一个课程的信息（课程编号、授课老师、简介…）
• 选课信息表：一个学生可以选择多门课，一门课可以被多个学生选择

学号 课程编号
1 1001
2 1001
1 1002

举例2：产品-订单
“订单”表和“产品”表一种多对多的关系，这种关系是通过与“订单明细”表建立两个一对多的关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中。

• 产品表：“产品”表中的每条记录表示一个产品。
• 订单表：“订单”表中的每条记录表示一个订单。
• 订单明细表：每个产品可以与“订单”表中的多条记录对应，即出现在多个订单中。一个订单可以与“产品”表中的多条记录对应，即包含多个产品。
  

5.2.4 自我引用（Self reference）

6.MySQL的登录

6.1 服务的启动与停止

MySQL安装完毕之后，需要启动服务器进程，不然客户端无法连接数据库。
在前面的配置过程中，已经将MySQL安装为Windows服务，并且勾选当windows启动、停止时，MySQL也自动启动、停止

方式一：使用图形界面工具

步骤1：打卡windiws服务

方式一：单机计算机右键，管理点击服务和应用程序，点击服务
方式二：点击控制面板，系统和安全，管理工具，服务
方式三：任务栏，任务管理器，点击服务
方式四：单机【开始】菜单，在搜索框中输入“services.msc”，按Enter键确认

步骤2：找到MySQL点击鼠标右键，启动或停止

方式二：使用命令行模式（以管理员运行）

启动MySQL服务命令：

net start MySQL
1

停止MySQL服务命令:

net stop MySQL
1

说明：start和stop后面的服务名与之前配置时指定的服务名一致。

6.2 自带客户端的登录与退出

当MySQL服务启动完成后，便可通过客户端来登录MySQL数据库。注意：服务是开启的。

登录方式1：MySQL客户端

开始菜单 ->所有程序->MySQL->MySQL 8.0 Command Line Client
仅仅限于root账户

登录方式2：命令行模式

mysql -h 主机名 -P 端口号 -u 用户名 -p密码
1

举例：

mysql mysql  -P3306 -uroot -proot
1

注意：
（1） -p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有控制
（2）密码建议在下一行输入，保证安全

mysql -h mysql -P 3306 -u root -p
1

（3）客户端和服务器在同一台机器上，所以输入localhost或者IP地址127.0.0.1。同时，因为是连接本机： -hmysql就可以省略，如果端口号没有修改：-P3306也可以省略
简写成：

mysql -u root -p
Enter password:****
1
2

连接成功后通过select version();查看版本信息

exit或quit退出登录

二、基本的SELECT语句

1.SQL分类

SQL语言在功能上主要分为如下3大类：
DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

• 主要的语句关键字包括CREATE、DROP、ALTER等。

DML(Data Manipulation Languages、数据操作语言)，用于添加、删除、更新和查询数据库记录，并检查数据完整性。

• 主要的语句关键字包括INSERT、 DELETE、UPDATE 、SELECT等。
• SELECT是语言的基础，最为重要。

DCL(Data Control Languages、数据控制语言)，用于定义数据库、表、字段、用户的访问权限和安全级别。

• 主要的语句关键字包括GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。

因为查询语言使用比较频繁，所以很多人把查询语言单拎出来一类：DQL（数据查询语言）
还有单独将COMMIT、ROLLBACK取出来称为TCL（Transation Control Language，事务控制语言）

1.1 DDL-操作

• 库操作

查询所有数据库

SHOW DATABASE;
1

查询当前数据库

SELECT DATABASE();
1

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名[DEFAULT CHARSET 字符集][COLLATE排序规则];
1

删除数据库

DROP DATABASE[IF EXISTS]数据库名;
1

使用数据库

USE 数据库名;
1

• 表操作-查询

查询当前数据库所有表

SHOW TABLES;
1

查询表结构

DESC 表名;
1

查询指定表的建表语句

SHOW CREATE TABLE 表名;
1

• 表操作-创建

CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	字段3 字段3类型[COMMENT 字段3注释],
	...
	字段n 字段n类型[COMMENT 字段n注释],
)[COMMENT 表注释];
1
2
3
4
5
6
7

注意：[...]为可选参数，最后一个字段后面没有逗号

• 表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

• 表操作修改

添加字段

ALTER TABLE表名 ADD 字段名 类型（长度） [COMMENT注释] [约束];
1

修改数据类型

ALTER TABLE表名 MODIFY 字段名 新数据类型（长度）;
1

修改字段名和字段类型

ALTER TABLE表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT注释] [约束];
1

删除字段

ALTER TABLE表名 DROP 字段名;
1

修改表名

ALTER TABLE表名 RENAME TO 新表名;
1

删除表

DROP TABLE[IF EXISTS] 表名;
1

注意：在删除表时，表中的数据也会全部删除。

1.2 DML-操作

• 添加数据

1.给指定字段添加数据

INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2,...);
1

2.给全部字段添加数据

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

3.批量添加数据

INSERT INTO 表名 (字段名1,字段名2,...)  VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
1

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

注意：
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
- 字符串和日期型数据应该包含在引导中。
- 插入的数据大小，应该在字段的规定范围中。

• 修改数据

UPDATE 表名 SET 	字段名1=值1,字段名2=值2... [WHERE 条件];
1

注意：修改语句的条件也可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

• 删除数据

DELETE FROM 表名 [WHERE 条件]
1

注意:
-DELETE 语句的条件也可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
-DELETE语句不能删除某一个字段的值（可以使用UPDATE）。

1.3 DCL-操作

• 管理用户

1.查询用户

USE mysql
SELECT * FROM user;
1
2

2.创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
1

3.修改用户密码

CREATE USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password	BY '新密码';
1

4.删除用户

DROP USER '用户名'@'主机名';
1

注意:
主机名可以使用%
这类sql开发人员操作的比较少，主要是DBA使用。

• 权限控制
  常用的几种：
  
  1.查询权限

SHOW GRANTS FOR '用户名'@'主机名';
1

2.授予权限

GRANTS 权限列表 ON 数据库.表名 TO '用户名'@'主机名';
1

3.撤销权限

REMOVE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';
1

2.SQL语言的规则与规范

2.1 基本规则

• SQL可以写在一行或多行，为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以;或\g或\G结束
• 关键字不能被缩写也不能分行
• 关于标点符号
  • 必须保证所有的（）、单引号、双引号是成对结束的
  • 必须使用英文状态下的半角输入方式
  • 字符串型和日期时间类型的数据可以使用单引号（‘’）表示
  • 列的别名，尽量使用双引号（“”），而且不建议省略as

2.2 SQL大小写规范（建议遵守）

• MySQL在windows环境下是大小写不敏感的

• MySQL在Linux环境下是大小写敏感的

  • 数据库名、表名、表的别名、变量名是严格区分大小的
  • 关键字、函数名、列名（或字段名）、列的别名（字段的别名）是忽略大小写的。

• 推荐采用统一的书写规范：

  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL关键字、函数名、绑定变量等都大写

2.3 注释

可以使用如下格式的注释结构

单行注释：#注释文字（MySQL 特有的方式）
单行注释： -- 注释文字 -- （--后面必须包含一个空格）
多行注释： /* 注释文字 */
1
2
3

2.4 命名规则（暂时了解）

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含A-Z，a-z，0-9，_共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了。

2.5 导入现有的数据表、表的数据。

方式1：使用命令行 source文件的全路径名

例子：

source d:\student.sql;
1

方式2：基于具体的图形化界面的工具可以导入数据

3.基本的SELECT语句(DQL-操作 )

3.1 SELECT… FROM

• 语法

SELECT	标识选择哪列
FROM  	标识从哪个表中选择
1
2

• 选择全部列：

SELECT *
FROM	departments;
1
2

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符 “ * ”。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。
在生产环境下，不推荐使用SELECT *进行查询

• 选定特定的列

SELECT department_id,location_id
FROM departments;
1
2

MySQL中的语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开分人员习惯将关键字大写、数据列和表名小写。

3.2 列的别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
• AS可以省略
• 建议别名简短，简明之意
• 示例：

SELECT last_name AS name,commission_pct comm
FROM employees;
1
2

SELECT last_name AS "Name",salary * 12  "Annaul salary"
FROM employees;
1
2

3.3 去除重复行

默认下，查询会返回全部行，包括重复行。（没有去重的情况）

SQL语句示例如下：

SELECT department_id
FROM employees;
1
2

在SELECT语句使用关键字DISTINCT去除重复行（去重）

SQL语句示例如下：

SELECT DISTINCT department_id
FROM employees;
1
2

对于：

# 没有报错，但是没有实际意义。
SELECT DISTINCT department_id,salary
FROM employees;
1
2
3

注意点： DISTINCT其实是对后面所有的列名的组合进行去重，得到的整体和其他的不重复，如果仅仅想要查看哪些不同的部门（department_id），只需要写DISTINCT department_id就行，后面不需要加其他列名。

3.4 空值参与运算

1. 空值：Null
2. null不等同于 0 ，‘’，‘null’

• 所有运算符或列值遇到Null值，运算结果都为Null

SELECT employee_id,salary "月工资",salary * (1 + commission_pct) * 12 "年工资"
FROM employees;
1
2

注意：在MySQL里面，空值不等于空字符串。一个字符串的长度是0，而一个空值的长度是空。而且，在MySQL里面，空值是占用空间的。

3.5 着重号（`）

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

• 错误的查询示例：

SELECT * FROM order
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1
> 时间: 0s
1
2
3

• 正确的查询示例：

SELECT * FROM `order`;
1

3.6 查询常数

SELECT 查询还可以对常数进行查询。就是在SELECT查询结果中增加一列固定的常数列，这列取值是我们指定的，而不是从数据表中动态取出的。

如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

举例：对employees数据表中的员工姓名进行查询，同时增加一列字段corporation，这个字段固定值为“尚硅谷”，代码如下：

SELECT '尚硅谷' as corporation,last_name 
FROM employees;
1
2

4.显示表的结构

使用DESCRIBE或DESC命令，表示表结构。

显示了表中字段的详细信息

SQL语句示例如下：

DESCRIBE employees;
1

DESC employees;
1

各个字段的含义如下：

• Field：表示字段名称
• Type：表示字段类型
• Null：表示该列是否可以存储Null值
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，那么值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

5.过滤数据

• 语法：

SELECT 字段1，字段2
FROM 表名
WHERE 过滤条件
1
2
3

使用WHERE子，将不满足条件的行过滤掉
WHERE子句紧随FROM子句

• SQL语句示例如下：

# 查询last_name为'king'的员工信息
SELECT * 
FROM employees
WHERE last_name = 'King';
1
2
3
4

三、运算符

1.算数运算符

算数运算符主要用于数学运算，其可以连接运算符前后的两个数值或表达式，对数值或表达式进行（+）、减（-）、乘（*）、除（/）和取模（%）运算。

1.1 加法与减法运算符

SQL语句示例如下：

SELECT 100, 100 + 0, 100 - 0,100 + 50, 100 + 50 * 30,100 + 35.5 ,100 - 35.5
FROM DUAL;
1
2

由运算结果得出结论：

一个整数类型的值对整数进行加法减法操作，结果还是一个整数；
一个整数类型的值对浮点数进行加法和减法操作，结果还是一个浮点数；
加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
在Java中， + 的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

1.2 乘法与除法运算符

SQL语句示例如下：

SELECT 100,100 * 1,100 * 1.0 ,100 / 1.0 ,100 / 2,100 + 2 * 5 / 2,100 / 3, 100  DIV 0	# 分母如果为0，则结果为null
FROM DUAL;
1
2

由运算结果得出结论：

一个数乘以整数1和除以整数1后仍得原数；
一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

1.3 取模（求余）运算符 （% ，mod）

SQL语句示例如下：

SELECT 12 % 3, 12 % 5 , 12 mod -5, -12 % 5 ,-12 % -5
FROM DUAL;
1
2

举例：查询员工id为偶数的员工信息
SQL语句示例如下：

# 查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;
1
2
3
4

2.比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回Null。

比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

2.1 等号运算符

• 等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。

• 在使用等号运算符时，遵循如下规则：

  • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
  • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
  • 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
  • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

• 对比：SQL中赋值符号使用 :=
  SQL语句示例如下：

SELECT 1 = 2, 1 != 2, 1 = '1',1 = 'a',0 = 'a'	#字符串存在隐士转换。如果转换不成功，则看作0
FROM DUAL;
1
2

SELECT 'a' = 'a','ab' = 'ab' ,'a' = 'b' # 两边都是字符串的话，则按照ANSI的比较规则进行比较。
FROM DUAL;
1
2

SELECT 1 = NULL,NULL = NULL	# 只要有Null参与判断，结果就为null
FROM DUAL;
1
2

SELECT last_name , salary,commission_pct
FROM employees
WHERE commission_pct = Null;
1
2
3

2.2 安全等于运算符

安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是<==>可以用来对Null进行判断。在两个操作数均为Null时，返回值为1，而不为Null，当一个操作数为Null时，其返回值为0，而不为Null。
SQL语句示例如下：

SELECT 1 <=> NULL , NULL <=> NULL
FROM DUAL;
1
2

# 查询表中commission_pct 为 null的数据有哪些
# 与上面等号查询结果对比
SELECT last_name , salary,commission_pct
FROM employees
WHERE commission_pct <=> Null;
1
2
3
4
5

2.3 不等于运算符

不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。

SQL语句示例如下：

 SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
1

3.非符号类型的运算符

非符号类型的运算符:

3.1 空运算符

（IS NULL或ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回0。

SQL语句示例如下：

 SELECT NULL IS NULL ,ISNULL(NULL),ISNULL('a'),1 IS NULL;
1

查询表中commission_pct为null的数据有哪些

 SELECT last_name,salary,commission_pct
 FROM employees
 WHERE commission_pct IS NULL;
1
2
3

 SELECT last_name,salary,commission_pct
 FROM employees
 WHERE commission_pct <=> NULL;
1
2
3

 SELECT last_name,salary,commission_pct
 FROM employees
 WHERE ISNULL(commission_pct);
1
2
3

3.1 非空运算符

查询表中commission_pct不为null的数据有哪些
SQL语句示例如下：

 SELECT last_name,salary,commission_pct
 FROM employees
 WHERE commission_pct IS NOT NULL;
1
2
3

 SELECT last_name,salary,commission_pct
 FROM employees
 WHERE NOT commission_pct <=> NULL;
1
2
3

3.3 最小值运算符

语法格式为：LEAST(值1，值2，…，值n)。其中，“值n”表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。
SQL语句示例如下：

SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
1

由结果可以看到，当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

 SELECT LEAST(first_name,last_name)
 FROM employees;
1
2

3.4 最大值运算符

语法格式为：GREATEST(值1，值2，…，值n)。其中，n表示参数列表中有n个值。当有两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

SQL语句示例如下：

 SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
1

由结果可以看到，当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

3.5 BETWEEN AND运算符

BETWEEN运算符使用的格式通常为SELECT D FROM TABLE WHERE C BETWEEN A（条件下界） AND B（条件上界），此时，当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

SQL语句示例如下：

SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c';
1

# 查询工资在6000到8000的员工信息（交换6000和8000之后查不到数据）
SELECT employee_id ,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
1
2
3
4

# 查询工资不在6000到8000的员工信息
SELECT employee_id ,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000; 
1
2
3
4

3.6 IN运算符

IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。

SQL语句示例如下：

SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
1

# 查询部门为10，20，30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN (10,20,30);
1
2
3
4

3.8 NOT IN运算符

NOT IN运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。

SQL语句示例如下：

SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
1

# 查询工资不是6000，7000，8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000);
1
2
3
4

3.8 LIKE运算符

LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0。如果给定的值或者匹配条件为NULL，则返回结果为NULL。
LIKE运算符通常使用如下通配符：

“%”：匹配0个或多个字符。 
“_”：只能匹配一个字符。
1
2

SQL语句示例如下：

SELECT NULL LIKE 'abc', 'abc' LIKE NULL;
1

# 查询last_name中包含字符'a'的员工信息（%代表不确定个数的字符）0个，1个，或多个
SELECT last_name 
FROM employees
WHERE last_name LIKE '%a%';
1
2
3
4

# 查询last_name中以字符'a'开头的员工信息
SELECT last_name 
FROM employees
WHERE last_name LIKE 'a%';
1
2
3
4

# 查询last_name中包含字符'a'且包含字符'e'的员工信息
# 写法1：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE 	'%e%';

# 写法2：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
1
2
3
4
5
6
7
8
9
10

# 查询第3个字符是'a'的员工信息(_代表一个不确定的字符)
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
1
2
3
4

# 查询第二个字符是_且第三个字符是'a'的员工信息
# 需要使用转义字符： \
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';


# 或者（了解）
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
1
2
3
4
5
6
7
8
9
10
11

ESCAPE

• 回避特殊符号的：使用转义符。例如：将[%]转为[ %]、[]转为[ ]，然后再加上[ESCAPE‘$’]即可。
• 如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。

3.9 REGEXP运算符

REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP 匹配条件 。如果expr满足匹配条件，返回1；如果不满足，则返回0。若expr或匹配条件任意一个为NULL，则结果为NULL。

REGEXP运算符在进行匹配时，常用的有下面的通配符：

（1）‘^‘匹配以该字符后面的字符开头的字符串。
（2）’$‘匹配以该字符前面的字符结尾的字符串。
（3）’.‘匹配任何一个单字符。
（4）"[…]“匹配在方括号内的任何字符。例如，”[abc]“匹配"a” “b"或"c”。为了命名字符的范围，使用一个’-’。"[a-z]“匹配任何字母，而”[0-9]"匹配任何数字。
（5）'*'匹配零个或多个在它前面的字符。例如，"x * “匹配任何数量的’x’字符，”[0-9] * “匹配任何数量的数字，而” * " ，匹配任何数量的任何字符。

SQL语句示例如下：

SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk';
1

4.逻辑运算符

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。 MySQL中支持4种逻辑运算符如下：

4.1 逻辑非（NOT或!）

（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。

SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;
1

4.2 逻辑或（OR或||）

逻辑或（OR或||）运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。

SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;
1

# 方式一：
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 OR department_id = 20;

# 方式二：
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 10 || department_id = 20;
1
2
3
4
5
6
7
8
9

#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary FROM employees WHERE NOT (salary >= 9000 AND salary <= 12000);
1
2

4.3 逻辑与（AND或&&）

逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL;
1

#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary 
FROM employees 
WHERE NOT (salary >= 9000 AND salary <= 12000);
1
2
3
4

SELECT employee_id, last_name, job_id, salary 
FROM employees 
WHERE salary >=10000 AND job_id LIKE '%MAN%';
1
2
3

注意：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先 对AND两边的操作数进行操作，再与OR中的操作数结合。

4.4 逻辑异或（XOR）

逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

# XOR:追求的“异”
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;
1
2
3
4

5.位算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。

MySQL支持的位运算符如下：

5.1 按位与运算符

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0。

SELECT 1 & 10, 20 & 30
FROM DUAL;
1
2

1的二进制数为0001，10的二进制数为1010，所以1 & 10的结果为0000，对应的十进制数为0。20的二进制数为10100，30的二进制数为11110，所以20 & 30的结果为10100，对应的十进制数为20。

5.2 按位或运算符

按位或（|）运算符将给定的值对应的二进制数逐位进行逻辑或运算。当给定值对应的二进制位的数值有一个或两个为1时，则该位返回1，否则返回0。

SELECT 1 | 10, 20 | 30
FROM DUAL;
1
2

1的二进制数为0001，10的二进制数为1010，所以1 | 10的结果为1011，对应的十进制数为11。20的二进制数为10100，30的二进制数为11110，所以20 | 30的结果为11110，对应的十进制数为30。

5.3 按位异或运算符

按位异或(^)运算符将给定的值对应的二进制数逐位进行逻辑异或运算。当给定值对应的二进制位的数值不同时，则该返回1，否则返回0。

SELECT 1 ^ 10, 20 ^ 30
FROM DUAL;
1
2

1的二进制数为0001，10的二进制数为1010，所以1 ^ 10的结果为1011，对应的十进制数为11。20的二进制数为10100，30的二进制数为11110，所以20 ^ 30的结果为01010，对应的十进制数为10。

5.4 按位取反运算符

按位取反（~）运算符将给定的值的二进制数逐位进行取反操作，即将1变为0，将0变 为1。

SELECT 10 & ~1
FROM DUAL;
1
2

5.5 按位右移运算符

按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的
位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。(每右移一位相当于除以2)

SELECT 1 >> 2, 4 >> 2
FROM DUAL;
1
2

5.6 按位左移运算符

按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的
位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐。（每左移一位相当于乘以2）

SELECT 1 << 2, 4 << 2;
FROM DUAL;
1
2

1的二进制数为0000 0001，左移两位为0000 0100，对应的十进制数为4。4的二进制数为0000 0100，左移两位为0001 0000，对应的十进制数为16。
able>

6. 运算符的优先级

数字的编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符的优先级最低，使用（）括起来的表达式的优先级最高。

四、排序与分页

1.排序数据

1.1 排序规则

• 使用ORDER BY子句排序
  • ASC(ascend)：升序 （默认）
  • DESC(descend)：降序
• ORDER BY子句在SELECT语句的结尾。

1.2 单列排序

# 按照salary从高到低的顺序显示员工信息
SELECT employee_id ,last_name,salary
FROM employees
ORDER BY salary DESC;
1
2
3
4

# 按照salary从低到高的顺序显示员工信息(如果在ORDER BY后没有显示指明排序的方式的话，则默认按照升序排列。)
SELECT employee_id ,last_name,salary
FROM employees
ORDER BY salary ASC;
1
2
3
4

# 使用列的别名，进行排序（列的别名只能在Ordey by中使用，不能在WHERE中使用）
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
1
2
3
4

1.3 多列排序

# 显示员工信息，按照department_id的降序排列，salary的升序排列
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;
1
2
3
4

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

2.分页

2.1 实现规则

• 分页原理所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
• MySQL中使用 LIMIT 实现分页
• 格式：

LIMIT [位置偏移量,] 行数
1

• 举例

# 使用limit实现数据的分页显示
# 需求1：每页显示20条记录，此时显示第1页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

# 需求1： 每页显示20条记录，此时显示第2页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;


# 需求3： 每页显示20条记录，此时显示第3页
SELECT employee_id,last_name
FROM employees
LIMIT 40,20;



# 只想显示第32、33条数据
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

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

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

# WHERE ... ORDER BY ... LIMIT	声明顺序如下：
# LIMIT的格式：严格的来说：LIMIT 	位置偏移量，条目数
# 结构"LIMIT 0,条目数"等价于"LIMIT 条目数"

SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
# Limit 0,10;
LIMIT 10;
1
2
3
4
5
6
7
8
9
10

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

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

# MySQL8.0新特性：LIMIT ... OFFSET... （和之前写法的两个参数位置对换）
# 表里有107条数据，只想要显示第32、33条数据
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;
1
2
3
4
5

五、多表查询

多表查询，也称为关联查询，指两个或多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

1.一个案例引发的多表查询

1.1 案例说明

# 案例：查询员工的姓名及其部门名称 （错误的实现方式：每个员工都与每个部门匹配了一遍。）
# 错误的原因：缺少了多表的连接条件
SELECT last_name, department_name FROM employees, departments;
1
2
3

分析错误情况：

SELECT COUNT(employee_id) From employees;
# 输出107行

SELECT COUNT(department_id) From departments;
# 输出27行

SELECT 107 * 27 FROM dual;
1
2
3
4
5
6
7

把上述多表查询中出现的问题称为：笛卡尔积的错误。

1.2 笛卡尔积（或交叉连接）的理解

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

• 笛卡尔积的错误会在下面条件下产生：
  • 省略多个表的连接条件（或关联条件）
  • 连接条件（或关联条件）无效
  • 所有表中的所有行互相连接
• 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
• 加入连接条件后，查询语法：

SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2; #连接条件
1
2
3

在 WHERE子句中写入连接条件。

• 正确写法：

#案例：查询员工的姓名及其部门名称 
SELECT last_name, department_name 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;
1
2
3
4

练习：

# 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
# 建议：从sql优化的角度，建议查表查询时，每个字段前都指明其所在的表。
SELECT employee_id,department_name,employees.department_id
FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;
1
2
3
4
5

# 如果给表起了别名，一旦在SELECT 或 WHERE 	中使用表名的话，则必须使用表的别名，而不能再使用表的原名。
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE	emp.department_id = dept.department_id;
1
2
3
4

• 在表中有相同列时，在列名之前加上表名前缀。

2. 多表查询分类

2.1 分类1：等值连接 vs 非等值连接

等值连接

SELECT emp.employee_id,emp.last_name,emp.department_id,dept.department_name,dept.location_id
FROM employees emp,departments dept
WHERE	emp.department_id = dept.department_id;
1
2
3

• 多个连接条件与 AND 操作符

• 区分重复的列名
  • 多个表中有相同列时，必须在列名之前加上表名前缀。
  • 在不同表中具有相同列名的列可以用 表名 加以区分。

SELECT employees.last_name, departments.department_name,employees.department_id 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;
1
2
3

• 表的别名
  • 使用别名可以简化查询。
  • 列名前使用表名前缀可以提高查询效率。

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id 
FROM employees e , departments d 
WHERE e.department_id = d.department_id;
1
2
3

需要注意的是，如果使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错
【 强制 】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
说明 ：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
正例 ：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id; 反例 ：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年
后，最近在 某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052 异常：Column ‘name’ in field list is ambiguous。

• 连接多个表

总结：连接 n个表,至少需要n-1个连接条件。 比如，连接三个表，至少需要两个连接条件。

# 如果有n个表实现多表查询，则需要n-1个连接条件
# 查询员工的employee_id,last_name,department_name,city
SELECT employee_id,last_name,department_name,city
FROM employees e,departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
1
2
3
4
5
6

非等值连接

SELECT last_name , salary , grade_level
FROM employees e,job_grades j
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;	
1
2
3

2.2 分类2：自连接 vs 非自连接

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

# 自连接
# 查询员工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.employee_id;
1
2
3
4
5

# 查询出last_name为 ‘Chen’ 的员工的 manager 的信息
SELECT w.employee_id,w.last_name,m.employee_id,m.last_name
FROM employees w,employees m
WHERE w.manager_id = m.employee_id AND w.last_name = 'Chen';
1
2
3
4

2.3 分类3：内连接 vs 外连接

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

# 查询所有的员工的last_name,department_name信息
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;	#只有106条记录
1
2
3
4

• 外连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行之外，还查询到了左表或右表中不匹配的行。
  • 外连接的分类：左外连接、右外连接、满外连接
  • 左外连接： 两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行 ，这种连接称为左外连接。
  • 右外连接： 两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行 ，这种连接称为右外连接。

2.3.1 SQL92：使用(+)创建连接

SQL92语法实现内连接(以上)

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

SQL92语法实现外连接：使用 +

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

#以下方式MySQL不支持
#左外连接 
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;
1
2
3
4
5
6
7
8
9
10

3. SQL99语法实现多表查询

• 使用JOIN…ON子句创建连接的语法结构：（这种方式也能解决外连接的问题）

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

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

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

3.1 内连接(INNER JOIN)的实现

• 语法：

# INNER可以省略
SELECT 字段列表 
FROM A表 INNER JOIN B表 
ON 关联条件 
WHERE 等其他子句;
1
2
3
4
5

• 举例1：

SELECT e.employee_id,e.last_name,d.department_id,d.location_id
FROM employees e 
	INNER JOIN departments d	ON e.department_id = d.department_id
1
2
3

• 举例2：

SELECT e.employee_id,e.last_name,d.department_id,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;
1
2
3
4

3.2 外连接(OUTER JOIN)的实现

3.2.1 左外连接(LEFT OUTER JOIN)

• 语法：

#实现查询结果是A （A表的数据量多）
#OUTER可以省略
SELECT 字段列表 
FROM A表 LEFT OUTER JOIN B表 
ON 关联条件 
WHERE 等其他子句;
1
2
3
4
5
6

• 举例：查询所有员工的last_name,department_name信息

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;
1
2
3
4

3.2.2 右外连接(RIGHT OUTER JOIN)

• 语法：

#实现查询结果是B （B表的数据量多）
#OUTER可以省略
SELECT 字段列表 
FROM A表 RIGHT OUTER JOIN B表 
ON 关联条件 
WHERE 等其他子句;
1
2
3
4
5
6

• 举例：

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;
1
2
3
4

3.3.3 满外连接(FULL OUTER JOIN)

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

4.UNION的使用

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

语法格式：

SELECT column,... FROM table1 
UNION [ALL] 
SELECT column,... FROM table2
1
2
3

UNION操作符

UNION 操作符返回两个查询的结果集的并集，去除重复记录。因为UNION操作要再UNION ALL的基础上去除一部分重复数据，所以导致效率低了，推荐使用UNION ALL效率更高

UNION ALL操作符

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

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

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1 
SELECT * FROM employees 
WHERE email LIKE '%a%' OR department_id>90;
1
2
3

#方式2 
SELECT * FROM employees WHERE email LIKE '%a%' 
UNION 
SELECT * FROM employees WHERE department_id>90;
1
2
3
4

举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息

SELECT id,cname FROM t_chinamale WHERE csex='男' 
UNION ALL 
SELECT id,tname FROM t_usmale WHERE tGender='male';
1
2
3

5.七种SQL JOINS的实现

5.1 第一种

内连接

• 举例：

SELECT employee_id,department_name
FROM employees e 
	JOIN departments d 
		ON e.department_id = d.department_id;
1
2
3
4

5.2 第二种

左外连接

• 举例：

SELECT employee_id,department_name
FROM employees e 
	LEFT JOIN departments d
		ON e.department_id = d.department_id;
1
2
3
4

5.2 第三种

右外连接
- 举例：

SELECT employee_id,department_name
FROM employees e 
	RIGHT JOIN departments d
		ON e.department_id = d.department_id;
1
2
3
4

5.2 第四种

• 举例：

SELECT employee_id,department_name
FROM employees e 
		LEFT JOIN departments d
			ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
1
2
3
4
5

5.2 第五种

• 举例：

SELECT employee_id,department_name
FROM employees e 
		RIGHT JOIN departments d
			ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
1
2
3
4
5

5.2 第六种

满外连接

• 举例方式一：第二种 + 第五种组合 （UNION ALL）

SELECT employee_id,department_name
FROM employees e 
		LEFT JOIN departments d
			ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e 
		RIGHT JOIN departments d
			ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
1
2
3
4
5
6
7
8
9
10

• 举例方式二：第三种 + 第四种组合 （UNION ALL）

SELECT employee_id,department_name
FROM employees e 
	RIGHT JOIN departments d
		ON e.department_id = d.department_id;
UNION ALL
SELECT employee_id,department_name
FROM employees e 
		LEFT JOIN departments d
			ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
1
2
3
4
5
6
7
8
9
10

5.2 第七种

• 举例：第四种+ 第五种（UNION ALL）

SELECT employee_id,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,department_name
FROM employees e 
		RIGHT JOIN departments d
			ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

1
2
3
4
5
6
7
8
9
10
11
12

6.SQL99语法新特性

6.1 自然连接

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

• 在SQL92标准中：

SELECT employee_id,last_name,department_name
FROM employees e , departments d
WHERE e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
1
2
3
4

• 在 SQL99 中你可以写成：

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

6.2 USING连接

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

• 在SQL92标准中：

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

• 在 SQL99 中你可以写成：

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

7.总结

多表连接的约束条件可以有三种方式：WHERE, ON, USING

• WHERE：适用于所有关联查询

• ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

• USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

注意：
我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
来源：阿里巴巴《Java开发手册》

六、单行函数

1.函数的理解

1.1 什么是函数

函数在计算机语言的使用中贯穿始终，函数的作用可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。

将函数分成内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的。

1.2 不同DBMS函数的差异

使用SQL语言时，不与这门语言直接打交道，而是通过使用不同的数据库软件，即DBMS。DBMS之间的差异很大，远大于同一个语言不同版本之间的差异。只有极少的函数是被DBMS同时支持的。比如，大多数DBMS使用（||）或（+）来做拼接符，而在MySQL中的字符串拼接函数为conact()。大部分DBMS会有自己特定的函数，这代表采用SQL函数的代码可移植性是很差的，所以在使用时要特别注意。

1.3 MySQL的内置函数及分类

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

2.数值函数

2.1 基本函数

举例：

SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
1
2
3

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
1
2

SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;
1
2

2.2 角度与弧度互换函数

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;
1
2

2.3 三角函数

2.4 指数与对数

2.5 进制间的转换

3. 字符串函数

注意：MySQL中，字符串的位置是从1开始的。

# ASCII 、CHAR_LENGTH、LENGTH
SELECT ASCII('abc'),CHAR_LENGTH("hello"),CHAR_LENGTH('我们'),
LENGTH('hello'),LENGTH('我们')
FROM DUAL;
1
2
3
4

# CONCAT
SELECT CONCAT(emp.last_name,'  worked for  ',mgr.last_name)	"details"
FROM employees emp JOIN employees mgr
WHERE emp.manager_id = mgr.employee_id;
1
2
3
4

#  CONCAT_WS
SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;
1
2
3

# INSERT 字符串的索引是从1开始的(从2开始数3个替换成aaaa)
# REPLACE
SELECT INSERT('helloworld',2,3,'aaaa'),REPLACE('hello','ll','mm')
FROM DUAL;
1
2
3
4

# UPPER  LOWER
SELECT UPPER('hello'),LOWER('HeLLO')
FROM DUAL;
1
2
3

# LEFT  RIGHT
SELECT LEFT('hello',2),RIGHT('hello',3)
1
2

# LPAD(占10位，不足的话在前面补*补够10位)实现右对齐的效果
# RPAD(实现左对齐的效果)
SELECT employee_id,last_name,LPAD(salary,10,'*')
FROM employees;
1
2
3
4

# LTRIM、RTRIM、TRIM
SELECT TRIM('     hel  lo        '),
TRIM('o' FROM 'ooheollo')
FROM DUAL;
1
2
3
4

# REPEAT、SPACE,STRCMP
SELECT REPEAT('hello',4),SPACE(5),STRCMP('abc','abd')
FROM DUAL;
1
2
3

# SUBSTR,LOCATE
SELECT SUBSTR('hello',2,2),LOCATE('l','hello')
FROM DUAL;
1
2
3

# ELT,FIELD,FIND_IN_SET
SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'),
FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg');
FROM DUAL;
1
2
3
4

# NULLIF
SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) 'comapre'
FROM employees;
1
2
3

4. 日期和时间函数

4.1 获取日期、时间

SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME()
1
2

4.2 日期与时间戳的转换

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-5-30 12:12:32'),
FROM_UNIXTIME(1653389986),FROM_UNIXTIME(1653883952)
FROM DUAL;
1
2
3

4.3 获取月份、星期、星期数、天数等函数

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
1
2
3

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE())
FROM DUAL;
1
2
3

SELECT  WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;
1
2
3

4.4 日期的操作函数

EXTRACT(type FROM date)函数中type的取值与含义：

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM NOW())
FROM DUAL;
1
2
3

4.5 时间和秒钟转换的函数

SELECT TIME_TO_SEC(CURRENT_TIME),
SEC_TO_TIME(27177)
FROM DUAL;
1
2
3

4.6 计算日期和时间的函数

第1组：

上述函数中type的取值：

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL; 
1
2
3
4

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
1
2
3
4
5
6

第2组：

4.7 日期的格式化与解析

上述非GET_FORMAT函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

5. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资')
FROM employees;
1
2

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0)
FROM employees;
1
2

SELECT	last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;
1
2

SELECT last_name,salary,CASE 
	WHEN salary >= 15000 THEN
		'白骨精'
	WHEN salary >= 10000 THEN
		'潜力股'
	WHEN salary >= 800 THEN
		'小屌丝'

	ELSE
		'农名'
END "details"
FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12

6. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

SELECT MD5('mysql'),SHA('mysql')
FROM DUAL;
1
2

7. MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。

8. 其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

七、聚合函数

聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

1.聚合函数介绍

• 什么是聚合函数
  聚合函数作用于一组数据，并对一组数据返回一个值。
  

• 聚合函数类型

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

• 聚合函数语法

SELECT [column,1]	gruop function(column),...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
1
2
3
4
5

• 聚合函数不能嵌套调用。比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

1.1 AVG和SUM函数

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary),SUM(salary),AVG(salary) * 107
FROM employees;
1
2

1.2 MIN和MAX函数

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;
1
2

1.3 COUNT函数

• COUNT(*)返回表中记录总数，适用于任意数据类型。

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;
1
2
3

• COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;
1
2
3

• 问题：用count(*)，count(1)，count(列名)谁好呢?

  其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

  Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

• 问题：能不能使用count(列名)替换count(*)?

  不要使用 count(列名)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

  说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2. GROUP BY

2.1 基本使用

可以使用GROUP BY子句将表中的数据分成若干组

SELECT 字段列表,函数 
FROM 表名
[WHERE 条件]
GROUP BY 分组字段名
[ORDER BY 字段列表];
1
2
3
4
5

明确：WHERE一定放在FROM后面

在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT   department_id, AVG(salary)
FROM     employees
GROUP BY department_id ;
1
2
3

包含在 GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT   AVG(salary)
FROM     employees
GROUP BY department_id ;
1
2
3

2.2 使用多个列分组

SELECT   department_id dept_id, job_id, SUM(salary)
FROM     employees
GROUP BY department_id, job_id ;
1
2
3

2.3 GROUP BY中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

注意：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

3. HAVING

3.1 基本使用

过滤分组：HAVING子句

1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

语法：

SELECT 字段列表,函数 
FROM 表名
[WHERE 条件]
GROUP BY 分组字段名
[HAVING 分组后过滤条件]
[ORDER BY 字段列表];
1
2
3
4
5
6

WHERE和HAVING的区别：

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而Having可以。

注意“

• 执行顺序：where>聚合函数>having。
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

SELECT   department_id, MAX(salary)
FROM     employees
GROUP BY department_id
HAVING   MAX(salary)>10000 ;
1
2
3
4

• 非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数如下：

SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;
1
2
3
4

3.2 WHERE和HAVING的对比

区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

小结如下：

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

4. SELECT的执行过程

4.1 查询的结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

4.2 SELECT执行顺序

你需要记住 SELECT 查询时的两个顺序：

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
1

2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
1

八、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

1.需求分析与问题解决

1.1 实际问题

现有解决方式：

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary
FROM employees
WHERE salary > 11000;
1
2
3
4
5
6
7
8

#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
1
2
3
4
5

#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = 'Abel'
);
1
2
3
4
5
6
7
8

1.2 子查询的基本使用

• 子查询的基本语法结构：

SELECT 字段列表
FROM  表名
WHERE 	exper operator 
	(
		SELECT 字段列表
		FROM  表名
) ;
1
2
3
4
5
6
7

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。
• 注意事项
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

1.3 子查询的分类

分类方式1：
我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。

• 单行子查询
  
• 多行子查询
  分类方式2：
  我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

2. 单行子查询

2.1 单行比较操作符

2.2 代码示例

题目：查询工资大于149号员工工资的员工信息

SELECT last_name
FROM employees
WHERE salary > 
	(
		SELECT salary
		FROM employees
		WHERE employee_id = 149
	);
1
2
3
4
5
6
7
8

题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =  
	(
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
	)
AND salary > 
	(SELECT salary
		FROM employees
		WHERE employee_id = 143
	);
1
2
3
4
5
6
7
8
9
10
11
12
13

2.3 HAVING 中的子查询

• 首先执行子查询。
• 向主查询中的HAVING 子句返回结果。
  题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);
1
2
3
4
5
6
7

2.4 CASE中的子查询

在CASE表达式中使用单列子查询：

题目：显式员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id, last_name,
       (CASE department_id
        WHEN
             (SELECT department_id FROM departments
	      WHERE location_id = 1800)           
        THEN 'Canada' ELSE 'USA' END) location
FROM   employees;
1
2
3
4
5
6
7

2.5 子查询中的空值问题

SELECT last_name, job_id
FROM   employees
WHERE  job_id =
                (SELECT job_id
                 FROM   employees
                 WHERE  last_name = 'Haas');
1
2
3
4
5
6

子查询不返回任何行

2.6 非法使用子查询

SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);
1
2
3
4
5
6

Subquery returns more than 1 row

3. 多行子查询

• 也称为集合比较子查询
• 内查询返回多行
• 使用多行比较操作符

3.1 多行比较操作符

3.2 代码示例

题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ANY
		(
			SELECT salary
			FROM employees
			WHERE job_id = 'IT_PROG'
		)
AND job_id <> 'IT_PROG';
1
2
3
4
5
6
7
8
9

题目：返回其它job_id中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < ALL
		(
			SELECT salary
			FROM employees
			WHERE job_id = 'IT_PROG'
		)
AND job_id <> 'IT_PROG';
1
2
3
4
5
6
7
8
9

题目：查询平均工资最低的部门id

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
);
1
2
3
4
5
6
7
8
9

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
				) dept_avg_sal
			)
1
2
3
4
5
6
7
8
9
10
11
12

3.3 空值问题

SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			);
1
2
3
4
5
6

子查询不返回任何行

4. 相关子查询

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

说明：子查询中使用主查询中的列

4.2 代码示例

题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

方式一：相关子查询

SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.department_id
	);
1
2
3
4
5
6
7

方式二：在 FROM 中使用子查询

SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;
1
2
3
4

from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名，把它当成一张“临时的虚拟的表”来使用。

在ORDER BY 中使用子查询：
题目：查询员工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (      
		SELECT department_name      
		FROM departments d      
		WHERE e.`department_id` = d.`department_id`    
);
1
2
3
4
5
6
7

4.3 EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  • 条件返回 FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  • 不在子查询中继续查找
  • 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

方式一：EXISTS

SELECT employee_id, last_name, job_id, department_id
FROM   employees e1
WHERE  EXISTS ( SELECT *
                 FROM   employees e2
                 WHERE  e2.manager_id = 
                        e1.employee_id);
1
2
3
4
5
6

方式二：自连接

SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM   employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
1
2
3

方式三：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
		     SELECT DISTINCT manager_id
		     FROM employees
		     
		     );
1
2
3
4
5
6
7

题目：查询departments表中，不存在于employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM   employees
                  WHERE  department_id = d.department_id);
1
2
3
4
5

九、约束

1.概述

1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2. 目的：保证数据库中数据的正确性、有效性和完整性。
3. 分类：
   
   注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

2.约束演示

CREATE TABLE user(
	`id` int PRIMARY KEY AUTO_INCREMENT	COMMENT 'ID唯一标识',
	`name` VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
	`age` int CHECK( age > 0 & age <= 120) COMMENT '年龄',
	`status` CHAR(1) DEFAULT(1)	COMMENT '状态',
	`gender` CHAR(1) COMMENT '性别'	 
)COMMENT '用户表';
1
2
3
4
5
6
7

3.外键约束

• 概念
  外键用来让两张表的数 之间建立连接，从而保证数据的一致性和完整性。
  

注意：上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

• 语法

-添加外键

CREATE TABLE 表名(
	字段名 数据类型
	...
	[CONSTRAINT][外键名称] FOREIGN KEY(外键字段名) REFERENCES主表（主表列名）
	);
1
2
3
4
5

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY（外键字段名） REFERENCES主表（主表列名）;
1

-删除外键

ALTER TABLE 表名 DROP FOREIGN KEY(外键字段名);
1

• 删除/更新行为
  

十、事务

1.事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

默认MySQL的事务是自动提交的，当执行一条DML语句，MySQL会立即隐式的提交事务。

2.事务操作

环境准备

CREATE TABLE account(
	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
	name VARCHAR(10) COMMENT '姓名',
	money INT COMMENT '余额'
) COMMENT '账户表';	

INSERT INTO account(id,name,money) VALUES(null,'张三',2000),(null,'李四',2000);
1
2
3
4
5
6
7

恢复数据

UPDATE account set money = 2000 WHERE name = '张三' or name = '李四'
1

– 转账操作（张三给李四转账1000）

– 1.查询张三账户余额

SELECT * FROM account WHERE name = '张三';
1

– 2.将张三账户余额 -1000

UPDATE account set money = money - 1000 WHERE name = '张三';			
1

–3.将李四账户余额 +1000

UPDATE account set money = money + 1000 WHERE name = '李四';
1

正常成功下：

程序抛出异常：

方式一：

• 查看/设置事务提交方式

SELECT @@autocommit; 	
SET @@autocommit=0;		# 设置为手动提交
1
2

• 提交事务

COMMIT;
1

• 回滚事务

ROLLBACK;
1

方式二：

• 开启事务

START TRANSACTION;
1

或

BEGIN;
1

• 提交事务

COMMIT;
1

• 回滚事务

ROLLBACK;
1

3.事务的四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  

• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
  

4.并发事务问题

5.事务的隔离级别

– 查看事务的隔离级别

SELECT @@TRANSACTION_ISOLATION;
1

– 设置事务隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE}
1

注意：事务隔离级别越高，数据越安全，但是性能越低。