MySQL 基础篇

MySQL 基础篇

1. 数据库概述

• 关系型数据库典型的数据结构就是 数据表，这些数据表的组成都是结构化的（Structured）
• 将数据放到表中，表再放到库中；
• 一个数据库中可以有多个表，每个表都有一个唯一的标识名；

1.1. 表、记录、字段

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

ORM 思想（Object Relational Mapping）
表 <---> 类
一条记录 <---> 实例对象（实体）
列 <---> 类中的一个字段（field）

1.2. 表的关联关系

• 表与表之间的数据记录之间有四种关系（relationship）：一对一关联、一对多关联、多对多关联、自我引用；

1.2.1. 一对一关联（one-to-one）

不常用，一对一可以创建成一张表（当然有时候字段太多也可以拆分下）；

• 外键唯一：主表的主键与从表的外键（唯一），形成主外键关系，外键唯一；
• 外键是主键：主表的主键和从表的主键，形成主外键关系；

1.2.2. 一对多关联（ont-to-many）

常见实例场景：客户表和订单表、分类表和商品表、部门表和员工表

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键；

1.2.3. 多对多关联（mant-to-many）

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

• 举个栗子：学生-课程

  • 学生表：学号、姓名、班级
  • 课程表：课程号、授课老师、简介
  • 选课表：一个学生可以选多门课，一门课可以被多个学生选择

• 再举个栗子：产品-订单

  《订单表》和《产品表》有一种多对多的关系，这种关系是通过《订单明细表》建立两个一对多关系来定义的。一个订单可以有多个产品，每个产品可以出现在多个订单中；

  • 产品表
  • 订单表
  • 订单明细表

2. SQL 之 SELECT

2.1. 基本规则

• 为了可读性，SQL 各子句分行写，必要时使用缩进

• 每条命令以 ; 或 \g 或 \G 结束

• 列的别名，尽量使用双引号 ""，不建议省略 as

• 字符串型和日期类型的数据使用单引号 '' 表示

• MySQL 在 Windows 下大小写不敏感，而在 Linux 下大小写敏感；

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

• 大小写规范：

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

• 注释

  单行注释：# 注释文字
  单行注释：-- 注释文字  --（--后面必须有一个空格）
  多行注释：/* 注释文字 */
  

• 命名规则

  1. 数据库、表名在 30 个字符以内，变量名限制为 29 个
  2. 必须只能包含 A-Z、a-z、0-8、_ 共 63 个字符
  3. 同一个 MySQL 中，数据库不能同名；同一个数据库中，表不能同名；同一个表中，字段不能重名；
  4. 字段中不能和保留字、数据库系统或常用方法冲突，冲突则需要用 ` 着重号引起来
  5. 保持字段名和类型的一致性

• ⭐导入现有的数据表、表的数据：

  1. source 文件的全路径名：source d:\\book.sql
  2. 使用图形化界面工具导入

2.2. 基本语法

1. 伪表 DUAL： 一个不存在的表，它的字段和记录都是无意义的，用来方便操作；

   SELECT 1 + 1
   # 上面的 SQL 会被自动补为
   SELECT 1 + 1
   FROM DUAL
   

2. 列的别名 alias： 简称 as，当列的别名中有空格时，需要使用双引号 "" 引起来；

   SELECT employee_id emp_id, last_name AS lname, department_id "部门 id"
   FROM employees;
   

3. 去除重复行 DISTINCT

   SELECT DISTINCT department_id
   FROM employees
   

4. 空值 null： 空值不等同于 0，空值参与运算，结果也一定为空；

5. 查询常数： 在查询结果中添加一列或多列

   SELECT '尚硅谷', 123, employee_id, last_name
   FROM employees
   

6. 显示表结构 DESCRIBE

   DESCRIBE employees
   # 简写为 DESC
   DESC employees
   

7. 过滤数据 WHERE

   SELECT *
   FROM employees
   # 过滤条件
   WHERE department_id = 90
   

2.3. 运算符

只要 NULL 值参与运算，结果就为 NULL；有特殊情况 <=> 安全等于号，运算符主要需要注意的就是 NULL 值参与运算是结果是什么；

2.3.1. 算术运算符

算术运算符主要用于数学运算，可以连接运算符前后的两个数值或表达式，对数值或表达式进行加减乘除或取模运算；

运算符	名称	示例
+	加法运算符	SELECT A + B
-	减法运算符	SELECT A - B
*	乘法运算符	SELECT A * B
/ 或 DIV	除法运算符	SELECT A / B
% 或 MOD	求模（求余）运算符	SELECT A % B

SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 - 0, 100 + 35.5, 100 - 35.5
FROM DUAL;
# 100 100 100 150 150 135.5 64.5

SELECT 100 + '1' # 在 SQL 中，+ 没有连接的作用
FROM DUAL;
# 101

SELECT 100 + '1' # 此时将 'a' 看做 0 处理
FROM DUAL;
# 100

SELECT 100 + NULL # NULL 参与运算结果为 NULL
FROM DUAL;
# 100

SELECT 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2, 100 + 2 * 5 / 2, 100 / 3, 100 DIV 0
FROM DUAL;
# 100 100.0 100.0000 50.0000 105.0000 33.3333 NULL

SELECT 12 % 3, 12 % 5, 12 MOD -5, -12 % 5, -12 % -5
FROM DUAL
# 0 2 2 -2 -2

2.3.2. 比较运算符

运算符	名称	示例
=	等于
<=>	安全等于
<>(!=)	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于

1. 等于 =

   SELECT 1 = 2,1 != 2, 1 = '1', 1 = 'a', 0 = 'a', 'a' = 'a', 'ab' = 'ab', 'a' = 'b'
   FROM DUAL # 两边都是字符串则以 ASCII 码比较
   # 0 1 1 0 1 1 1 0
   

2. 安全等于 <=>：与 = 的 唯一区别 是： <=> 可以用来对 NULL 进行判断；

   SELECT 1 <=> NULL, NULL <=> NULL # 两边都是 NUll 则为 1，一边是 NULL则为 0
   FROM DUAL;
   # 0 1
   

比较运算符之一些关键字；

运算符	名称	示例
IS NULL	判断值、字符串或表达式是否为空	SELECT B FROM TABLE WHERE A IS NULL
IS NOT NULL	判断值、字符串或表达式是否不为空	SELECT B FROM TABLE WHERE A IS NOT NULL
LEAST	在多个值中返回最小值	SELECT D FROM TABLE WHERE C LEAST(A, B)
GREATEST	在多个值中返回最大值	SELECT D FROM TABLE WHERE C GREATEST(A, b)
BETWEEN AND	判断一个值是否在两个值之间，闭区间	SELECT D FROM TABLE WHERE C BETWEEN A AND B
ISNULL	判断一个值、字符串或表达式是否为空	SELECT B FROM TABLE WHERE ISNULL(A)
IN	判断一个值是否为列表中的任意一个值	SELECT D FROM TABLE WHERE C IN (A, B)
NOT IN	判断一个值是否不是一个列表中的任意一个值	SELECT D FROM TABLE WHERE C NOT IN (A, B)
LIKE	判断一个值是否符合模糊匹配规则	SELECT C FROM TABLE WHERE A LIKE B
REGEXP	判断一个值是否符合正则表达式的规则	SELECT C FROM TABLE WHERE A REGEXP B
RLIKE	判断一个值是否符合正则表达式的规则	SELECT C FROM TABLE WHERE A RLIKE B

# % 代表不确定个数的字符（0 个，1 个，多个）
# _ 代表一个不确定的字符
# \_ 代表 _
# \\ 代表 \
SELECT last_name
FROM employees
WHERE last_name LIKE "%a%";

# 把转义字符设置为 $
SELECT last_name
FROM employees
WHERE last_name LIKE "_$_a%" ESCAPE '$';

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

(1) '^' 匹配以紧跟着的字符开头的字符串
(2) '$' 匹配以紧跟着的字符结尾的字符串
(3) '.' 匹配任意一个单字符
(4) '[...]' 匹配方括号内任意一个字符，[0-9] 代表任意数字
(5) '*' 拓展零个或多个他前面的字符

SELECT 'xbai-hang' REGEXP '^x', 'xbai-hang' REGEXP 'g$', 'xbai-hang' REGEXP 'bai'
FROM DUAL;
# 1 1 1 

2.3.3. 逻辑运算符

逻辑运算主要用来判断表达式的真假，在 MySQL 中，逻辑运算符的返回结果为 1、0、NULL；

OR 和 AND 可以一起使用，AND 的优先级高于 OR；

运算符	作用	示例
NOT 或 !	逻辑非	SELECT NOT A
AND 或 &&	逻辑与	SELECT A AND B SELECT A && B
OR 或 `		`
XOR	逻辑异或	SELECT A AND B

2.3.4. 位运算符

将操作数转换成二进制，然后进行位运算，最后将结果转换为十进制；

运算符	作用	示例
&	按位与	SELECT A & B
`	`	按位或
^	按位异或	SELECT A ^ B
~	按位取反	SELECT ~A
>>	按位右移	SELECT A >> 2
<<	按位左移	SELECT A << 2

2.3.5. 运算符优先级

优先级	运算符
1	:==, =(赋值)
2	`
3	&&, AND
4	NOT
5	BETWEEN, CASE, WHEN, THEN, ELSE
6	=(比较运算符), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7	`
8	&
9	<<, >>
10	-, +
11	*, /, DIV, %, MOD
12	^
13	-(负号), ~(按位取反)
14	!
15	()

2.4. 排序

如果没有排序操作，默认查询返回的数据是以添加数据的顺序显示的；

2.4.1. 排序规则

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

2.4.2. 单列排序

# 默认升序，不写 ASC 也是升序
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC;

# WHERE 要在 FROM 之后、ORDER BY 之前
SELECT employee_id AS emp_id, salary
FROM employees
WHERE department_id IN (50, 60, 70)
-- WHERE emp_id IN (50, 60, 70) # 错误的，WHERE 子句中不能用别名
ORDER BY emp_id DESC;

2.4.3. 多列排序

当第一列列值相同时，对第二列进行排序；如下例中 部门 id 降序排序，相同部门时按 薪资高低升序排序；

# 先按照 department_id 降序排序，再按照 salary 升序排序
SELECT employee_id, department_id, salary
FROM employees
ORDER BY department_id DESC, salary ASC;

2.5. 分页

WHERE、ORDER BY、LIMIT 声明顺序（非执行顺序）：先 WHERE，后 ORDER BY，最后 LIMIT；

2.5.1. 使用规则

• LIMIT 位置偏移量, 条目数，偏移量为 0 时可以省略；
  • LIMIT 0, 条目数 等价于 LIMIT 条目数
• MySQL 8.0 新特性：LIMIT ... OFFSET ...
  • LIMIT 条目数 OFFSET 偏移量

SELECT employee_id, last_name
FROM employees
LIMIT 0, 20; -- 每页 20 条记录，显示第 1 页

SELECT employee_id, last_name
FROM employees
LIMIT 20, 20; -- 每页 20 条记录，显示第 2 页

# 需求：每页显示 pageSize 条记录，此时显示第 pageNo 页
# 公式：LIMIT (pageNo - 1) * pageSize, pageSize;
SELECT employee_id, last_name
FROM employees
LIMIT 20, 20;

• 分页显示公式： (当前页数 - 1) * 每页条数，每页条数

  SELECT * FROM table
  LIMIT (pageNo - 1) * pageSize, pageSize;
  

• LIMIT 子句必须放在整个 SELECT 语句的最后

• 使用 LIMIT的好处：

  约束返回结果的数量可以 减少数据表的网络传输量，也可以 提升查询效率；当直到返回结果只有 1 条，就可以使用 LIMIT 1 ；这样做的好处是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回；

2.6. ⭐多表查询

多表查询：也称为关联查询，指两个或更多个表一起完成查询操作；

前提条件：一起查询的表之间有关联关系（一对多，一对一），它们之间有关联字段，这个关联字段可能建立了外键，也可能没有建立外键；

比如：员工表和部门表，两个表依靠 部门编号 进行关联；

【Alibaba 开发手册 强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时， 保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

• 表中有相同列时，在列名之前加上表名前缀
• 给表起别名后，SELECT、WHERE 中使用表名则必须用表的别名
• 从 sql 优化的角度，建议多表查询时，每个字段前都指明其所在的表

等值连接与非等值连接

• 等值连接： 即 WHERE 语句的条件是等值判断；

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

• 非等值连接

  SELECT e.last_name, e.salary, j.grade_level
  FROM employees e, job_grades j
  WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
  

自连接与非自连接

涉及多表查询的都是非自连接；自连接仅涉及一张表（自我引用，逻辑层面视为多张表）

SELECT emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
FROM  employees AS emp, employees AS mgr
WHERE emp.manager_id = mgr.employee_id;

内连接与外连接

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

  SELECT 字段列表
  FROM A 表 [INNER] JOIN B 表
  ON 关联条件
  WHERE 等其子句
  
  SELECT e.last_name, d.department_name
  FROM employees e JOIN departments d
  ON e.department_id = d.department_id;
  

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

• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表

  SELECT 字段列表
  FROM A 表 LEFT | RIGHT [OUTER] JOIN B 表
  ON 关联条件
  WHERE 等其子句
  
  SELECT last_name, department_name
  FROM employees e LEFT JOIN  departments d
  ON e.department_id = d.department_id
  
  SELECT last_name, department_name
  FROM employees e RIGHT JOIN  departments d
  ON e.department_id = d.department_id
  

• 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表

2.7. UNION 的使用

UNION 用于合并查询结果，将多条 SELECT 语句的结果组合成单个结果集；合并时，两个表对应的列数和数据类型必须相同，并相互对应；

各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字隔离；

语法格式：

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

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

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

2.8. SQL99 新特性

自然（NATURAL）连接

自然连接 NATURAL JOIN 自动查询两张连接表中的 所有相同字段 ，然后进行 等值连接

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

上面的查询语句可以写为如下语句：

# 改写为自然连接
SELECT employee_id, last_name, department_name
FROM employees e NATURAL JOIN  departments d

USING 连接

USING 连接制定了具体的相同的字段名称，在 USING 的括号 () 中填入要指定的同名字段；

SELECT employee_id, last_name, department_name
FROM employees e JOIN  departments d
ON e.department_id = d.department_id

上面的查询语句可以写为如下语句：

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

3. 单行函数

在 MySQL 中，函数分为内置函数与自定义函数；内置函数从实现的功能角度可以分为： 数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取 MySQL 信息函数、聚合函数等；

3.1. 单行函数的理解

• 操作数据对象
• 接受参数返回一个结果