文章目录
- MySQL 基础篇
-
- 1. 数据库概述
- 2. SQL 之 SELECT
- 3. 单行函数
- 4. 聚合函数
- 5. 子查询
- 6. SQL 之 DDL、DML、DCL
- 7. MySQL 数据类型
- 8. 约束
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 下大小写敏感;
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列明(或字段名)、列的别名(字段的别名)时忽略大小写的
-
大小写规范:
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写
-
注释
单行注释:# 注释文字 单行注释:-- 注释文字 --(--后面必须有一个空格) 多行注释:/* 注释文字 */
-
命名规则
- 数据库、表名在 30 个字符以内,变量名限制为 29 个
- 必须只能包含 A-Z、a-z、0-8、_ 共 63 个字符
- 同一个 MySQL 中,数据库不能同名;同一个数据库中,表不能同名;同一个表中,字段不能重名;
- 字段中不能和保留字、数据库系统或常用方法冲突,冲突则需要用 ` 着重号引起来
- 保持字段名和类型的一致性
-
⭐导入现有的数据表、表的数据:
source
文件的全路径名:source d:\\book.sql
- 使用图形化界面工具导入
2.2. 基本语法
-
伪表 DUAL: 一个不存在的表,它的字段和记录都是无意义的,用来方便操作;
SELECT 1 + 1 # 上面的 SQL 会被自动补为 SELECT 1 + 1 FROM DUAL
-
列的别名 alias: 简称 as,当列的别名中有空格时,需要使用双引号
""
引起来;SELECT employee_id emp_id, last_name AS lname, department_id "部门 id" FROM employees;
-
去除重复行 DISTINCT
SELECT DISTINCT department_id FROM employees
-
空值 null: 空值不等同于 0,空值参与运算,结果也一定为空;
-
查询常数: 在查询结果中添加一列或多列
SELECT '尚硅谷', 123, employee_id, last_name FROM employees
-
显示表结构 DESCRIBE
DESCRIBE employees # 简写为 DESC DESC employees
-
过滤数据 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. 比较运算符
运算符 | 名称 | 示例 |
---|---|---|
= |
等于 | |
<=> |
安全等于 | |
<>(!=) |
不等于 | |
< |
小于 | |
<= |
小于等于 | |
> |
大于 | |
>= |
大于等于 |
-
等于
=
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
-
安全等于
<=>
:与=
的 唯一区别 是:<=>
可以用来对 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. 单行函数的理解
- 操作数据对象
- 接受参数返回一个结果