MySQL基础

MarcyTheLibrarian

已于 2022-09-23 10:58:19 修改

阅读量982

点赞数

分类专栏： SQL 文章标签：数据库架构数据库开发数据库

于 2022-09-22 21:29:12 首次发布

本文链接：https://blog.csdn.net/twi_twi/article/details/126960164

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

第1章数据库概述

1.1 数据库的相关概念

DB	DBMS	SQL
数据库	数据库管理系统	结构化查询语言
Database	Database Management System	Structured Query Language
即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。	是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。	专门用来与数据库通信的语言。

DBMS、DB、Table的套娃关系：
数据库管理系统可以管理多个数据库；
一般开发人员会针对每一个应用创建一个数据库；
为保存应用中实体的数据，一般会在数据库创建多个表。

1.2 常见的数据库

Oracle：商用（收费）数据库软件
PostgreSQL：数据库科研中常用
MySQL：每个人在学习时都用过，从5.7版本直接跳跃发布了8.0版本，所以不要奇怪为什么不是5就是8

1.3 关系型数据库(RDBMS)

把复杂的数据结构归结为简单的二元关系;
以行列的形式存储数据。
SQL 就是关系型数据库的查询语言。
支持事务使得对于安全性能很高的数据访问要求得以实现。

1.3.1 表、记录、字段：

一个实体集对应于数据库中的一个表；
一个实体则对应于数据库表中的一行，也称为一条记录；
一个属性对应于数据库表中的一列，也称为一个字段.

实体集	表	实体	行	记录	属性	列	字段
class	table	instance	row	record	attribute	column	field

$class=table\ni instance=row=record$
$class=table\ni attribute=column=field$

1.3.2 表的关联关系

一对一关联（one-to-one）
不常用，因为能放一张表为啥拆两张
一对多关系（one-to-many）
在(多个)从表创建一个字段，字段作为外键指向主表的主键
多对多（many-to-many）
将两个表的主键都插入到第三个表中，将多对多关系划分为两个一对多关系。
既然是多对多，那至少得有第三者（×）
自我引用(Self reference)

1.4 非关系型数据库

基于键值对存储数据，不需要经过SQL层的解析，性能非常高

e.g. 只要好好学习，总有一天你会遇到下面这些妖魔鬼怪
键值型：Redis、MongoDB
文档型：MongoDB
搜索引擎：Solr、Elasticsearch、Splunk
列式：HBase
图形：Neo4J、InfoGrid

第2章 MySQL环境搭建

在Windows上安装MySQL的文章历历可数，但是在Linux（CentOS）上安装MySQL可以看一下这篇博客：
https://blog.csdn.net/twi_twi/article/details/126832272?spm=1001.2014.3001.5501

第3章启动MySQL命令

启动 MySQL 服务命令

net start MySQL服务名

停止 MySQL 服务命令

net stop MySQL服务名

登录MySQL
-p后跟密码：-p与密码之间不能有空格
-p后直接回车：密码会在下一行Enter password: 输入，保证安全
客户端和服务器在同一台机器上，直接：mysql -u root -p

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

查看MySQL版本

mysql --version

第4章 MySQL演示使用

4.1 MySQL的使用演示

查看所有的数据库

show databases;

创建数据库

#创建数据库时，该名称不能与已经存在的数据库重名。
create database 数据库名;

使用数据库

use 数据库名;

查看某个库的所有表格

show tables from 数据库名;

创建新的表格（具体格式说明和复杂用法是后话了）

create table 表名称(
    字段名  数据类型,
    字段名 数据类型
);

查看一个表的数据（*：学过通配符的大家看我眼不眼熟）

select * from 数据库表名称;

添加一条记录

insert into 表名称 values(值列表);

查看表的创建信息

show create table 表名称\G

查看数据库的创建信息

show create database 数据库名\G

删除表格

drop table 表名称;

删除数据库

drop database 数据库名;

4.2 MySQL的编码设置

步骤1：查看编码命令

show variables like 'character_%';
show variables like 'collation_%';

步骤2：修改mysql的数据目录下的my.ini配置文件

default-character-set=utf8  #默认字符集

[mysqld]  # 大概在76行左右，在其下添加
...
character-set-server=utf8
collation-server=utf8_general_ci

步骤3：重启服务

步骤4：查看编码命令

4.3 MySQL图形化管理工具

Navicat：强大的MySQL数据库服务器管理和开发工具
可以连接好多种数据库，用习惯了感觉非常合适，界面也好看

4.4 MySQL目录结构与源码

4.4.1 主要目录结构

MySQL的目录结构	说明
bin目录	所有MySQL的可执行文件。如：mysql.exe
MySQLInstanceConfig.exe	数据库的配置向导，在安装时出现的内容
data目录	系统数据库所在的目录
my.ini文件	MySQL的主要配置文件
c:\ProgramData\MySQL\MySQL Server 8.0\data\	用户创建的数据库所在的目录

4.4.2 MySQL 源代码获取

sql 子目录是 MySQL 核心代码；
libmysql 子目录是客户端程序 API；
mysql-test 子目录是测试工具；
mysys 子目录是操作系统相关函数和辅助函数；

问题1：root用户密码忘记，重置的操作
1: 通过任务管理器或者服务管理，关掉mysqld(服务进程)
2: 通过命令行+特殊参数开启mysqld mysqld --defaults-file=“D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini” --skip-grant-tables
3: 此时，mysqld服务进程已经打开。并且不需要权限检查
4: mysql -uroot 无密码登陆服务器。另启动一个客户端进行
5: 修改权限表

# 进入 mysql 库
use mysql; 
# 修改 user 表
update user set authentication_string=password('新密码') where user='root' and Host='localhost'; 
# 刷新配置
flush privileges;

6: 通过任务管理器，关掉mysqld服务进程。
7: 再次通过服务管理，打开mysql服务。
8: 即可用修改后的新密码登陆。

问题2：mysql命令报“不是内部或外部命令”
如果输入mysql命令报“不是内部或外部命令”，把mysql安装目录的bin目录配置到环境变量path中。

问题3：命令行客户端的字符集问题
服务器端认为你的客户端的字符集是utf-8，而实际上你的客户端的字符集是GBK。

问题4：修改数据库和表的字符编码
修改编码：
（1）先停止服务
（2）修改my.ini文件
（3）重新启动服务

第5章基本的SELECT语句

5.1 SQL概述

SQL（Structured Query Language，结构化查询语言）是使用关系模型的数据库应用语言

SQL语言3大类：

DDL	DML	DCL
Data Definition Languages	Data Manipulation Language	Data Control Language
数据定义语言	数据操作语言	数据控制语言
CREATE DROP ALTER	INSERT DELETE UPDATE SELECT	GRANT REVOKE COMMIT ROLLBACK SAVEPOINT

5.2 SQL语言的规则与规范

SQL 可以写在一行或者多行。
每条命令以;或\g或\G结束
关键字不能被缩写也不能分行

5.2.1 关于标点符号

必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式
字符串型和日期时间类型的数据可以使用单引号( ’ ’ )表示
列的别名，尽量使用双引号（" "），而且不建议省略 as

5.2.2 大小写规范

MySQL 在 Windows 环境下是大小写不敏感的
MySQL 在 Linux 环境下是大小写敏感的
- 严格区分大小写：数据库名、表名、表的别名、变量名
- 忽略大小写：关键字、函数名、列名(或字段名)、列的别名(字段的别名)
推荐采用统一的书写规范
- 都小写：数据库名、表名、表别名、字段名、字段别名等
- 都大写：SQL 关键字、函数名、绑定变量等

5.2.3 注释

单行注释：#注释文字(MySQL特有的方式)有没有想起Python
单行注释：-- 注释文字(请注意那个空格)
行注释：/* 注释文字 */

5.2.4 命名规则

数据库、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
不能同名者（顾名思义，理所应当）
- 同一个MySQL软件中，数据库不能同名
- 同一个库中，表不能重名
- 同一个表中，字段不能重名
保证字段没有和保留字、数据库系统或常用方法冲突
保持字段名和类型的一致性

5.3 SQL部分指令

5.3.1 数据导入指令

看看下面这句，有没有想起Linux的source命令

source d:\mysqldb.sql

5.3.2 列的别名

重命名一个列 请达到见名知意的效果
便于计算
可以在列名和别名之间加入关键字AS（也可以省略）
别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。

# last_name AS name 是加了AS的取别名
# commission_pct comm 是不加AS的取别名
SELECT last_name AS name, commission_pct comm
FROM   employees;

5.3.3 去除重复行

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

SELECT DISTINCT department_id
FROM   employees;

5.3.4 显示表结构

DESCRIBE tablename;
# 或
DESC tablename;

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int(6)      | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int(6)      | YES  | MUL | NULL    |       |
| department_id  | int(4)      | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

各个字段的含义分别解释如下：

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

第6章运算符

6.1 算术运算符

运算符	名称	作用	示例
+	加法	计算和	select A+B
-	减法	计算差	select A-B
*	乘法	计算乘积	select A*B
/或DIV	除法	计算商	select A/B
%或MOD	求模	计算余数	select A%B

特别的：

一个数乘以浮点数1和除以浮点数1后变成浮点数；
一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
一个数除以另一个数，除不尽时，结果为一个保留到小数点后4位的浮点数
在MySQL中，一个数除以0为NULL；

6.2 比较运算符

比较表达式左边的操作数和右边的操作数：
结果为真则返回1，为假则返回0，其他情况则返回NULL

6.2.1 符号类

运算符	名称	作用	示例
=	等于	是否相等	where A=B
<=>	安全等于	安全相等	where A<=>B
<>	不等于	是否不等	where A<>B
!=	不等于	是否不等	where A!=B
<	小于	是否小于	where A<B
<=	小于等于	是否小于等于	where A<=B
>	大于	是否大于	where A>B
>=	大于等于	是否大于等于	where A<=B

6.2.2 非符号类

运算符	名称	作用	示例
IS NOT NULL	不为空	判断是否不为空	where A is not null
LEAST	最小值	返回最小值	where C least(A,B)
GREATEST	最大值	返回最大值	where C greatset(A,B)
BETWEEN AND	两值之间	一值是否在两值之间	where C between A and B
ISNULL	为空	是否为空	where A isnull
IN	属于	是否属于	where C in (A,B)
NOT IN	不属于	是否不属于	where C not in (A,B)
LIKE	模糊匹配	是否符合规则	where A like B
REGEXP	正则表达式	是否符合规则	where A regexp B
RLIKE	正则表达式	是否符合规则	where A rlike B

特别的说明：

LIKE

“%”：匹配0个或多个字符；
“_”：只能匹配一个字符；
可使用 “\” 表示转义；
如果不使用 “\” 表示转义，则要加上escape
e.g. WHERE job_id LIKE ‘IT\_%‘ = WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘

SELECT job_id
FROM   jobs
WHERE  job_id LIKE ‘IT\_%‘;

# escape ‘$‘：表示这里的$表示转义符
SELECT job_id
FROM   jobs
WHERE  job_id LIKE ‘IT$_%‘ escape ‘$‘;

REGEXP

语法格式为：
SELECT * 
FROM fruits 
WHERE f_name REGEXP '正则表达式';

选项	说明	例子	-
^	匹配文本的开始字符	^m	marcy、marmar、mmm
$	匹配文本的结束字符	^a	anna、banana、sasha
.	匹配任何一个单字符	a.p	app，apple，amphibia
*	匹配前面的字符0次或多次	a*	amphibia，theowlhouse
+	匹配任何1个或多个字符	n+	anna，any，annnn
字符串	匹配包含指定字符串的文本	fa	fangirl，fafa
[字符串]	匹配字符集合中的任何一个字符	[ta]	amphibia，theowlhouse
[^字符]	匹配不在括号中的任何字符	[^zy]	amphibia，theowlhouse
字符串{n,}	匹配前面的字符串至少n次	n{2}	anna
字符串{n,m}	匹配前面的字符串n-m次	n{2,5}	anna，annnnna

6.2.3 逻辑运算符

运算符	作用	示例
NOT	逻辑非	select not A
!	逻辑非	select !A
AND	逻辑与	select A AND B
&&	逻辑与	select A && B
OR	逻辑或	select A or B
\|\|	逻辑或	select A \|\| B
XOR	逻辑异或	select A xor B

6.2.3.1 逻辑非运算符

NOT 1	NOT 0	NOT(1+1)	NOT !1	NOT NULL
0	1	0	1	NULL

6.2.3.2 逻辑与运算符

1 AND -1	0 AND 1	0 AND NULL	1 AND NULL
1	0	0	NULL

6.2.3.3 逻辑或运算符

1 OR -1	1 OR 0	1 OR NULL	0 \|\| NULL	NULL \|\| NULL
1	1	1	NULL	NULL

6.2.3.4 逻辑异或运算符

1 XOR -1	1 XOR 0	0 XOR 0	1 XOR NULL	1 XOR 1 XOR 1	0 XOR 0 XOR 0
0	1	0	NULL	1	0

6.2.4 位运算符

运算符	作用	示例
&	按位与	select A & B
\|	按位或	select A \| B
^	按位异或(XOR)	select A^B
~	按位取反	select ~A
>>	按位右移	select A>>2
<<	按位左移	select B<<2

作用	例子
按位与	0001&1010 => 0000
按位或	0001\|1010 => 1011
按位异或	0001^1010 => 1011
按位取反	~0001 => 1110
按位右移	001 >> 2 => 0 100 >> 2 => 1 `特别说明`：右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐。
按位左移	1 << 2 => 100 100 << 2 => 10000

6.2.5 运算符的优先级

优先级从大到小，自上而下排序：

=(用于赋值的)
||，OR，XOR
&&，AND
NOT
BETWEEN，CASE，WHEN，THEN，ELSE
=(用于比较的)，<=>，>=，>，<=，
<，<>，!=，IS，LIKE，REGEXP，IN
|
&
<<，>>
-(减号)，+
*，/，DIV，%，MOD
^
-(负号)，~
！
()

第7章排序与分页

7.1 排序数据

使用 ORDER BY子句排序（ORDER BY 子句在SELECT语句的结尾）

ASC（ascend）：升序
DESC（descend）：降序

SELECT last_name, department_id, salary
FROM   employees
ORDER BY department_id, salary DESC;

7.2 分页

LIMIT [位置偏移量,] 行数
直接看举例

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
--或者
SELECT * FROM 表名 LIMIT 10;

--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;

--第21至30条记录： 
SELECT * FROM 表名 LIMIT 20,10;

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

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

第8章多表查询

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

笛卡尔乘积是一个数学运算。

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

笛卡尔积的错误会在下面条件下产生：

省略多个表的连接条件（或关联条件）
连接条件（或关联条件）无效
所有表中的所有行互相连接
为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件

8.2 多表查询分类讲解

8.2.1 使用别名可以简化查询

列名前使用表名前缀可以提高查询效率。

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;

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。

8.2.2 连接多个表

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

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
- 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。
- 如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

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

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

就像 for 循环：

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

8.2.2.1 内连接(INNER JOIN)的实现

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

8.2.2.2 外连接(OUTER JOIN)的实现

左外连接(LEFT OUTER JOIN)

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

右外连接(RIGHT OUTER JOIN)

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

满外连接(FULL OUTER JOIN)

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

8.3 UNION的使用

合并查询结果

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

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

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

8.4 7种JOINS的实现

在这里插入图片描述

#中图：内连接 A∩B
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 LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

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

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

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

SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL  #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

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

#左中图:实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

#右中图:实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

#左下图:实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
#实现A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

8.5 新特性

8.5.1 自然连接

NATURAL JOIN 用来表示自然连接。

理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

# 以前的版本
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
==>
# SQL99的版本
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

8.5.2 USING连接

使用 USING 指定数据表里的同名字段进行等值连接

# 以前的版本
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;
==>
# SQL99的版本
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

8.5.3 表连接的约束条件

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

关键词	条件
WHERE	适用于所有关联查询
ON	只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写, 但分开写可读性更好。
USING	只能和JOIN一起使用. 而且要求两个关联字段在关联表中名称一致而且只能表示关联字段值相等

第9章单行函数

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

9.1 数值函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
PI()	返回圆周率的值
CEIL(x) CEILING(x)	返回大于或等于某个值的最小整数
FLOOR(x)	返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根。当X的值为负数时，返回NULL
RADIANS(x)	将角度转化为弧度，其中，参数x为角度值
DEGREES(x)	将弧度转化为角度，其中，参数x为弧度值
SIN(x)	返回x的正弦值，其中，参数x为弧度值
ASIN(x)	返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x)	返回x的余弦值，其中，参数x为弧度值
ACOS(x)	返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x)	返回x的正切值，其中，参数x为弧度值
ATAN(x)	返回x的反正切值，即返回正切值为x的值
ATAN2(m,n)	返回两个参数的反正切值
COT(x)	返回x的余切值，其中，X为弧度值
POW(x,y) POWER(X,Y)	返回x的y次方
EXP(X)	返回e的X次方，其中e是一个常数，2.718281828459045
LN(X) LOG(X)	返回以e为底的X的对数当X<=0时，返回的结果为NULL
LOG10(X)	返回以10为底的X的对数当X<=0时，返回的结果为NULL
LOG2(X)	返回以2为底的X的对数当X<=0时，返回NULL
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(x)	返回x的八进制编码
CONV(x,f1,f2)	返回f1进制数变成f2进制数

9.2 字符串函数

函数	用法
ASCII(S)	返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串
CONCAT_WS(x,s1,s2,…,sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT(str,idx,len,replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s)或UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s)或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str,len,pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str,len,pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str,n)	返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

9.3 日期和时间函数

9.3.1 获取日期、时间

函数	用法
CURDATE() CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() SYSDATE() CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

9.3.2 日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

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

函数	用法
YEAR(date) MONTH(date) DAY(date)	返回具体的日期值
HOUR(time) MINUTE(time) SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份：January，…
DAYNAME(date)	返回星期几：MONDAY，TUESDAY…SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(date) WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7

函数	用法
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

在这里插入图片描述

9.3.4 时间和秒钟转换的函数

函数	用法
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值。转化的公式为：小时3600+分钟60+秒
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间

9.3.5 计算日期和时间的函数

函数	用法
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期

在这里插入图片描述

函数	用法
ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔
FROM_DAYS(N)	返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)	返回日期date距离0000年1月1日的天数
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)	返回time加上n后的时间

9.3.6 日期的格式化与解析

函数	用法
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

GET_FORMAT函数中date_type和format_type参数取值如下：
在这里插入图片描述

fmt参数常用的格式符：

符号	说明	符号	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…）
%H	两位数字表示小数，24小时制（01,02…）	%h %I	两位数字表示小时，12小时制（01,02…）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4…）
%i	两位数字表示分钟（00,01,02）	%S %s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w	以数字表示周中的天数(0=Sunday,1=Monday…)
%j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

9.4 流程处理函数

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END	相当于Java的 if… else if… else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的 switch… case…

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
                  WHEN salary>=10000 THEN '潜力股'  
                  WHEN salary>=8000 THEN '屌丝' 
                  ELSE '草根' END  "描述"
FROM employees;

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
                   ELSE salary END "REVISED_SALARY"
FROM   employees;

9.5 加密与解密函数

函数	用法
PASSWORD(str)	返回字符串str的加密版本 `41位长的字符串` 加密结果不可逆，常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值。若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 `SHA加密算法比MD5更加安全`
ENCODE(value,password_seed)	返回使用password_seed 作为加密密码加value
DECODE(value,password_seed)	返回使用password_seed 作为加密密码解value

ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数

9.6 MySQL信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE() SCHEMA()	返回MySQL命令行当前所在的数据库
USER() CURRENT_USER() SYSTEM_USER() SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)	返回字符串value自变量的字符集
COLLATION(value)	返回字符串value的比较规则

9.7 其他函数

函数	用法
FORMAT(value,n)	返回对数字value进行格式化后的结果数据。 n表示四舍五入后保留到小数点后n位
CONV(value,from,to)	将value的值进行不同进制之间的转换
INET_ATON(ipvalue)	将以点分隔的IP地址转化为一个数字
INET_NTOA(value)	将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USINGchar_code)	将value所使用的字符编码修改为char_code

第10章聚合函数

10.1 基本介绍

顾名思义：

聚合函数类型	作用范围
AVG()	对数值型数据使用
SUM()	对数值型数据使用
MAX()	对任意数据类型的数据使用
MIN()	对任意数据类型的数据使用
COUNT()	返回表中记录总数，适用于任意数据类型

注意的点：

count()可以用count(*)，count(1)，count(列名)
问题：用count(*)，count(1)，count(列名)谁好呢?
答：对于MyISAM引擎的表是没有区别的。因为引擎内部有一计数器在维护着行数。

常常这样写代码…

# []中代表按需求可写项
SELECT [column,] group function(column),...
FROM tablename
[WHERE condition]
[GROUP BY column]
[HAVING group_condition]
[ORDER BY column];

10.2 基本使用

10.2.1 使用单个列分组

基本问题框架：
求出【tablename】表中
按照【columnX】划分的
【columnY】的
【AVG 或 SUM 或 MAX 或 MIN 或 COUNT】

SQL语句框架：

# group_function: AVG 或 SUM 或 MAX 或 MIN 或 COUNT
SELECT columnX, group_function(columnY)
FROM tablename
[WHERE  condition?]
GROUP BY  columnY
[ORDER BY columnY];

10.2.2 使用多个列分组

基本问题框架：
求出【tablename】表中
按照【columnX、columnY、…】划分的
【columnZ】的
【AVG 或 SUM 或 MAX 或 MIN 或 COUNT】

SQL语句框架：

# group_function: AVG 或 SUM 或 MAX 或 MIN 或 COUNT
SELECT columnX,columnY,group_function(columnZ)
FROM tablename
[WHERE  condition?]
GROUP BY  columnX,columnY
[ORDER BY column];

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

# WITH ROLLUP 加在这个位置
GROUP BY columnX WITH ROLLUP;

10.2.3 HAVING

过滤分组：HAVING子句

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

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

# 这是错的！！！！！！！！！！！！！！！
# group function is not allowed here
SELECT   department_id, AVG(salary)
FROM     employees
WHERE    AVG(salary) > 8000
GROUP BY department_id;

WHERE和HAVING的区别

区别1
HAVING 可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选
WHERE排除的记录不再包括在分组中

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

区别2
在关联查询中，WHERE 比 HAVING 更高效

环境	WHERE	HAVING
SQL中有JOIN	WHERE 是先筛选后连接	HAVING 是先连接后筛选

10.2.4 SELECT的执行过程`顺序很重要`

#方式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：分页

关键字的顺序是不能颠倒的：sfwghol
SELECT …
FROM …
WHERE …
GROUP BY …
HAVING …
ORDER BY …
LIMIT…
SELECT 语句的执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7

虚拟表
在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。

SQL 的执行原理

1. SELECT 先执行 FROM
在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

首先先通过 CROSS JOIN 求笛卡尔积，
相当于得到虚拟表 vt（virtual table）1-1；
通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，
得到虚拟表 vt1-2；
添加外部行。
如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，
也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。

2. 进入GROUP 和 HAVING 阶段
实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，
得到中间的虚拟表 vt3 和 vt4 。

3. 进入SELECT 和 DISTINCT阶段
首先在 SELECT 阶段会提取想要的字段，
然后在 DISTINCT 阶段过滤掉重复的行，
分别得到中间的虚拟表vt5-1 和 vt5-2 。

4. 进入 ORDER BY 阶段

5. 最后 LIMIT 阶段

第11章子查询

当
查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较
时使用。

11.1 基本使用

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用。
注意事项：

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询
多行操作符对应多行子查询

11.1.1 子查询的分类

分类方式1：

按内查询的结果返回一条还是多条记录，将子查询分为：
单行子查询
多行子查询

1. 单行子查询

单行比较操作符
=、>、>=、<、<=、<>

CASE中的子查询

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;

非法使用子查询

# 报错：因为子查询中使用了 GROUP BY，会返回不止一行的数据(就不是单行子查询)
# Subquery returns more than 1 row
SELECT employee_id, last_name
FROM   employees
WHERE  salary =
                (SELECT   MIN(salary)
                 FROM     employees
                 GROUP BY department_id);

2. 多行子查询

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询返回的`某一个值`比较
ALL	需要和单行比较操作符一起使用，和子查询返回的`所有值`比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

成对比较	不成对比较
where/having (columnX,columnY) IN (select …)	where/having columnX IN (select …)

例1：查询平均工资最低的部门id

# A <= all -> A最低
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
                SELECT AVG(salary) avg_sal
                FROM employees
                GROUP BY department_id
)

分类方式2：

按内查询是否被执行多次，将子查询划分为：
相关(或关联)子查询
不相关(或非关联)子查询

1. 相关子查询

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

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
在这里插入图片描述

SELECT column1,column2,...
FROM table1 outer
WEHRE column1 operator
		(SELECT column1,column2
		FROM table2
		WHERE expr1 = 
				outer.expr2);

EXISTS 与 NOT EXISTS关键字

EXISTS：用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行：
  条件返回 FALSE
  继续在子查询中查找
- 如果在子查询中存在满足条件的行：
  不在子查询中继续查找
  条件返回 TRUE
NOT EXISTS：表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

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);

相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据

UPDATE table1 alias1
SET column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column = alias2.column);

相关删除

 DELETE FROM table1 alias1
 WHERE column operator (SELECT expression
                        FROM   table2 alias2
                        WHERE  alias1.column = alias2.column);

第12章创建和管理表

12.1 基础知识

在 MySQL 中，
一个完整的数据存储过程总共有 4 步，
分别是：创建数据库、确认字段、创建数据表、插入数据。

12.1.1 标识符命名规则

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

12.1.2 MySQL中的数据类型

数据类型	关键字
整数类型	TINYINT、SMALLINT、MEDIUMINT、 INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、 TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

数据类型	描述
INT	从-2³¹到2³¹-1的整型数据。存储大小为 4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
FLOAT(M,D)	单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6
DOUBLE(M,D)	双精度，占用8个字节， D<=M<=255,0<=D<=30，默认M+D<=15
DECIMAL(M,D)	高精度小数，占用M+2个字节， D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。
DATE	日期型数据，格式’YYYY-MM-DD’
BLOB	二进制形式的长文本数据，最大可达4G
TEXT	长文本数据，最大可达4G

12.2 创建和管理数据库

创建数据库

# 方式1：创建数据库
CREATE DATABASE 数据库名; 

# 方式2：创建数据库并指定字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

# 方式3：判断数据库是否已经存在，不存在则创建数据库（推荐 ）
CREATE DATABASE IF NOT EXISTS 数据库名;

使用数据库

# 查看当前所有的数据库
SHOW DATABASES; #有一个S，代表多个数据库

# 查看当前正在使用的数据库
SELECT DATABASE();  #使用的一个 mysql 中的全局函数

# 查看指定库下所有的表
SHOW TABLES FROM 数据库名;

# 查看数据库的创建信息
SHOW CREATE DATABASE 数据库名;
# 或者：
SHOW CREATE DATABASE 数据库名\G

# 使用/切换数据库
USE 数据库名;

修改数据库

# 更改数据库字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：gbk、utf8等

# 方式1：删除指定的数据库
DROP DATABASE 数据库名;

# 方式2：删除指定的数据库（推荐 ）
DROP DATABASE IF EXISTS 数据库名;

12.3 创建表

12.3.1 创建方式1

必须具备：CREATE TABLE权限、存储空间

语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
    字段1, 数据类型 [约束条件] [默认值],
    字段2, 数据类型 [约束条件] [默认值],
    字段3, 数据类型 [约束条件] [默认值],
    ……
    [表约束条件]
);

必须指定：表名、列名(或字段名)，数据类型，长度
可选指定：约束条件、默认值

创建表举例1：

-- 创建表
CREATE TABLE emp (
  -- int类型，自增
  deptno INT(2) AUTO_INCREMENT,
  -- 最多保存20个中英文字符
  emp_name VARCHAR(20),
  -- 总位数不超过15位
  salary DOUBLE,
  -- 日期类型
  birthday DATE
  -- 主键
  PRIMARY KEY (deptno)
);

12.3.2 创建方式2

使用 AS subquery 选项，将创建表和插入数据结合起来

指定的列和子查询中的列要一一对应
通过列名和默认值定义列

CREATE TABLE dept80
AS 
SELECT  employee_id, last_name, salary*12 ANNSAL, hire_date
FROM    employees
WHERE   department_id = 80;

12.3.3 查看数据表结构

在MySQL中创建好数据表之后，可以查看数据表的结构。
MySQL支持使用DESCRIBE/DESC 语句查看数据表结构，也支持使用SHOW CREATE TABLE 语句查看数据表结构。

语法格式如下：

SHOW CREATE TABLE 表名\G

12.4 修改表

修改表指的是修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句可以实现：

向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列

追加一个列

ALTER TABLE 表名 
ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

修改一个列

ALTER TABLE 表名 
MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

重命名一个列

ALTER TABLE 表名 
CHANGE 【column】 列名 新列名 新数据类型;

**删除一个列 **

ALTER TABLE 表名 
DROP 【COLUMN】字段名

12.5 删除表、清空表

# 删除表
# 数据和结构都被删除
# 所有正在运行的相关事务被提交
# 所有相关索引被删除
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

# 清空表
# 删除表中所有的数据
# 释放表的存储空间
TRUNCATE TABLE detail_dept;

注意：TRUNCATE语句不能回滚，而使用 DELETE 语句删除数据，可以回滚

12.6 MySQL字段命名

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
- 正例：aliyun_admin，rdc_config，level3_name
- 反例：AliyunAdmin，rdcConfig，level_3_name
【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。
【强制】表必备三字段：id, gmt_create, gmt_modified。
- 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新
【推荐】表的命名最好是遵循 “业务名称_表的作用”。
- 正例：alipay_task 、 force_project、 trade_config
【推荐】库名与应用名称尽量一致。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

第13章数据处理之增删改

13.1 插入数据

# 一次向表中插入一条数据
INSERT INTO 表名(column1 [, column2, …, columnn]) 
VALUES (value1 [,value2, …, valuen]);

# 同时插入多条记录
INSERT INTO table_name(column1 [, column2, …, columnn]) 
VALUES 
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

13.2 更新数据

# 一次更新多条数据
# 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

13.3 删除数据

DELETE FROM table_name 
[WHERE <condition>];

table_name指定要执行删除操作的表；
“[WHERE ]”为可选参数，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中的所有记录。

13.4 计算列

CREATE TABLE tb1(
	id INT,
	a INT,
	b INT,
	# 注意这里的 c = (a+b)
	c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

第14章 MySQL数据类型精讲

14.1 MySQL中的数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型：GEOMETRY、POINT、LINESTRING、POLYGON；集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

14.2 整数类型

整数类型	字节	有符号数取值范围	无符号数取值范围
TINYINT	1	-128~127	0~255
SMALLINT	2	-32768~32767	0~65535
MEDIUMINT	3	-8388608~8388607	0~16777215
INT、INTEGER	4	-2147483648~2147483647	0~4294967295
BIGINT	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

整数类型的可选属性有三个：

M : 表示显示宽度，M的取值范围是(0, 255)。
UNSIGNED : 无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。
ZEROFILL : 0填充,（如果某列是ZEROFILL，那么MySQL会自动为当前列添加UNSIGNED属性），如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

14.3 浮点类型

MySQL 存储浮点数的格式为：符号(S) 、尾数(M) 和阶码(E) 。
MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。
因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。

14.4 定点数类型

数据类型	字节数	含义
DECIMAL(M,D) DEC NUMERIC	M+2字节	有效范围由M和D决定

浮点数 vs 定点数

浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）
定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）

14.5 位类型：BIT

二进制字符串类型	长度	长度范围	占用空间
BIT(M)	M	1 <= M <= 64	约为 $\frac{M + 7}{8}$ 个字节

14.6 日期与时间类型

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

14.7 文本字符串类型

MySQL中，文本字符串总体上分为CHAR 、VARCHAR 、TINYTEXT 、TEXT 、MEDIUMTEXT 、LONGTEXT 、ENUM 、SET 等类型。
在这里插入图片描述
CHAR与VARCHAR类型

字符串(文本)类型	特点	长度	长度范围	占用的存储空间	空间上	时间上	适用场景
CHAR(M)	固定长度	M	0 <= M <= 255	M个字节	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	M	0 <= M <= 65535	(实际长度 + 1) 个字节	节省存储空间	效率低	非CHAR的情况

TEXT类型

文本字符串类型	特点	长度	长度范围	占用的存储空间
TINYTEXT	小文本、可变长度	L	0 <= L <= 255	L + 2 个字节
TEXT	文本、可变长度	L	0 <= L <= 65535	L + 2 个字节
MEDIUMTEXT	中等文本、可变长度	L 0 <= L <= 16777215	L + 3 个字节
LONGTEXT	大文本、可变长度	L	0 <= L<= 4294967295（相当于4GB）	L + 4 个字节

14.8 ENUM类型

文本字符串类型	长度	长度范围	占用的存储空间
ENUM	L	1 <= L <= 65535	1或2个字节

14.9 SET类型

成员个数范围（L表示实际成员个数）	占用的存储空间
1 <= L <= 8	1个字节
9 <= L <= 16	2个字节
17 <= L <= 24	3个字节
25 <= L <= 32	4个字节
33 <= L <= 64	8个字节

**BINARY与VARBINARY类型 **

二进制字符串类型	特点	值的长度	占用空间
BINARY(M)	固定长度	M （0 <= M <= 255）	M个字节
VARBINARY(M)	可变长度	M（0 <= M <= 65535）	M+1个字节

14.10 二进制字符串类型

二进制字符串类型	值的长度	长度范围	占用空间
TINYBLOB	L	0 <= L <= 255	L + 1 个字节
BLOB	L	0 <= L <= 65535（相当于64KB）	L + 2 个字节
MEDIUMBLOB	L	0 <= L <= 16777215 （相当于16MB）	L + 3 个字节
LONGBLOB	L	0 <= L <= 4294967295（相当于4GB）	L + 4 个字节

14.11 JSON 类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。
JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

14.12 空间类型

在这里插入图片描述

第15章约束

15.1 约束概述

-	位置	支持的约束类型	是否可以起约束名
列级约束：	列的后面	语法都支持，但外键没有效果	不可以
表级约束：	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

约束	作用
NOT NULL	非空约束，规定某个字段不能为空
UNIQUE	唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY	主键(非空且唯一)约束
FOREIGN KEY	外键约束
CHECK	检查约束
DEFAULT	默认值约束

15.2 主键约束

15.2.1 添加主键约束

15.2.1.1 建表时指定主键约束

create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    [constraint 约束名] primary key(字段名) #表级模式
);

列级约束

CREATE TABLE emp4(
	id INT PRIMARY KEY AUTO_INCREMENT ,
	NAME VARCHAR(20)
);

表级约束

CREATE TABLE emp5(
	id INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20),
	pwd VARCHAR(15),
	CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

15.2.2.2 建表后指定主键约束

# 字段列表可以是一个字段，也可以是多个字段，
# 如果是多个字段的话，是复合主键
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

15.2.2 复合主键

create table 表名称(
    字段名  数据类型,
    字段名  数据类型,  
    字段名  数据类型,
    #表示字段1和字段2的组合是唯一的，也可以有更多个字段
    primary key(字段名1,字段名2)  
);

15.2.2 删除主键约束

alter table 表名称 drop primary key;

15.3 自增列：AUTO_INCREMENT

一个表最多只能有一个自增长列
当需要产生唯一标识符或顺序值时，可设置自增长
自增长列约束的列必须是键列（主键列，唯一键列）
自增约束的列的数据类型必须是整数类型
如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

建表时设置自增

create table 表名称(
    字段名  数据类型  primary key auto_increment,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);

建表后设置自增

alter table 表名称 modify 字段名 数据类型 auto_increment;

**如何删除自增约束 **

#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束

alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

15.4 FOREIGN KEY 约束

主表（父表）：被引用的表，被参考的表
从表（子表）：引用别人的表，参考别人的表

约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

特点

从表的外键列，必须引用/参考主表的主键或唯一约束的列
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名。
创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
删表时，先删从表（或先删除外键约束），再删除主表
当主表的记录被从表参照时，主表的记录将不允许删除，
- 如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在“从表”中指定外键约束，并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引名。（根据外键查询效率很高）
删除外键约束后，必须手动删除对应的索引

15.4.1 添加外键约束

在建表时添加外键约束

create table 主表名称(
    字段1  数据类型  primary key,
    字段2  数据类型
);

create table 从表名称(
    字段1  数据类型  primary key,
    字段2  数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样

-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

说明：
（1）主表必须先创建成功，然后才能创建从表，指定外键成功。
（2）删除表时，先删除从表，再删除主表

在建表后添加外键约束

ALTER TABLE 从表名 
ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) 
REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

15.4.2 删除外键约束

(1)第一步先查看约束名和删除外键约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#查看某个表的约束名

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

（2）第二步查看索引名和删除索引。（注意，只能手动删除）

SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 索引名;

15.5 约束等级

Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
Restrict方式 ：同no action，都是立即检查外键约束
Set default方式 （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别

15.6 CHECK 约束

检查某个字段的值是否符号xx要求，一般指的是值的范围

CREATE TABLE temp(
	id INT AUTO_INCREMENT,
	NAME VARCHAR(20),
	# 检查 age 的值是否 > 20
	age INT CHECK(age > 20),
	PRIMARY KEY(id)
);

使用check

# 这样的
age tinyint check(age >20)
sex char(2) check(sex in(‘男’,’女’))
# 或这样的
CHECK(height>=0 AND height<3)

15.7 DEFAULT约束

给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

在建表时设置DEFAULT

create table 表名称(
    字段名  数据类型  primary key,
    字段名  数据类型  unique key not null,  
    字段名  数据类型  unique key,
    字段名  数据类型  not null default 默认值, 
);

在建表后设置DEFAULT

alter table 表名称 modify 字段名 数据类型 default 默认值;

删除默认值约束

#删除默认值约束，也不保留非空约束
alter table 表名称 modify 字段名 数据类型 ;

#删除默认值约束，保留非空约束
alter table 表名称 modify 字段名 数据类型  not null; 

#删除gender字段默认值约束，如果有非空约束，也一并删除
alter table employee modify gender char; 

#删除tel字段默认值约束，保留非空约束
alter table employee modify tel char(11)  not null;

第16章视图

**常见的数据库对象 **

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束(CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程(PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数(FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器(TRIGGER)	相当于一个事件监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

16.1 创建视图

CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

16.1.1 创建单表视图

CREATE VIEW empvu80
AS 
SELECT  employee_id, last_name, salary
FROM    employees
WHERE   department_id = 80;

16.1.2 创建多表联合视图

CREATE VIEW empview 
AS 
SELECT employee_id emp_id,last_name NAME,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

16.2 查看视图

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。

语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

16.3 更新视图的数据

不支持更新操作的视图

在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作；
在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也
不支持UPDATE使用了数学表达式、子查询的字段值；
在定义视图的SELECT语句后的字段列表中使用DISTINCT 、聚合函数、GROUP BY 、HAVING 、UNION 等，视图将不支持INSERT、UPDATE、- DELETE；
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE；
视图定义基于一个不可更新视图；
常量视图。

16.4 修改、删除视图

16.4.1 修改视图

方式1：使用CREATE OR REPLACE VIEW 子句修改视图

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS 
SELECT  employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

方式2：ALTER VIEW
修改视图的语法是：

ALTER VIEW 视图名称 
AS
查询语句

16.4.2 删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

删除视图的语法是：

DROP VIEW IF EXISTS 视图名称;

第17章存储过程与函数

存储过程的参数类型可以是IN、OUT和INOUT
1、没有参数（无参数无返回）
2、仅仅带 IN 类型（有参数无返回）
3、仅仅带 OUT 类型（无参数有返回）
4、既带 IN 又带 OUT（有参数有返回）
5、带 INOUT（有参数有返回）

17.1 创建存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
    存储过程体
END

IN|OUT|INOUT

IN ：当前参数为输入参数，也就是表示入参；
存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN ，表示输入参数。
OUT ：当前参数为输出参数，也就是表示出参；
执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT ：当前参数既可以为输入参数，也可以为输出参数。

**characteristics **
表示创建存储过程时指定的对存储过程的约束条件

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制。
- CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
- NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
- READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
- MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
- 默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
- DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
- INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

17.2 调用存储过程

CALL 存储过程名(实参列表)

格式

# 1、调用in模式的参数：
CALL sp1('值');

# 2、调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;

# 3、调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;

17.3 存储函数的使用

17.3.1 创建存储函数

CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[characteristics ...]
BEGIN
    函数体   #函数体中肯定有 RETURN 语句

END

参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
RETURNS type 语句表示函数返回数据的类型；
RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函
数体必须包含一个RETURN value 语句。
characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

17.3.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

SELECT 函数名(实参列表)

存储函数和存储过程对比

关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个
存储函数	FUNCTION	SELECT 函数()	只能是一个

17.4 存储过程和函数的查看、修改、删除

17.4.1 查看

使用SHOW CREATE语句查看存储过程和函数的创建信息
基本语法结构如下：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

使用SHOW STATUS语句查看存储过程和函数的状态信息
基本语法结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

从information_schema.Routines表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

17.4.2 修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

characteristic指定存储过程或函数的特性

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

符号	功能
CONTAINS SQL	表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL	表示子程序中不包含SQL语句。
READS SQL DATA	表示子程序中包含读数据的语句。
MODIFIES SQL DATA	表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER	INVOKER }
DEFINER	表示只有定义者自己才能够执行。
INVOKER	表示调用者可以执行。
COMMENT ‘string’	表示注释信息。

17.4.3 删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

第18章变量、流程控制与游标

18.1 变量

在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据。

18.1.1 系统变量

查看所有或部分系统变量

# 查看所有全局变量
SHOW GLOBAL VARIABLES;

# 查看所有会话变量
SHOW SESSION VARIABLES;
# 或
SHOW VARIABLES;

# 查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%标识符%';

# 查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';

查看指定系统变量

# 查看指定的系统变量的值
SELECT @@global.变量名;

# 查看指定的会话变量的值
SELECT @@session.变量名;
# 或者
SELECT @@变量名;

修改系统变量的值
方式1：修改MySQL 配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
方式2：在MySQL服务运行期间，使用“set”命令重新设置系统变量的值

# 方式1：
SET @@global.变量名=变量值;
# 方式2：
SET GLOBAL 变量名=变量值;

# 为某个会话变量赋值
# 方式1：
SET @@session.变量名=变量值;
# 方式2：
SET SESSION 变量名=变量值;

18.1.2 用户变量

MySQL 中的用户变量以一个“@” 开头。
根据作用范围不同，又分为会话用户变量和局部变量。

会话用户变量：作用域和会话变量一样，只对当前连接会话有效。
局部变量：只在 BEGIN 和 END 语句块中有效。局部变量只能在存储过程和函数中使用。

18.1.2.1 会话用户变量

变量的定义

# 方式1：“=”或“:=”
SET @用户变量 = 值;
SET @用户变量 := 值;

# 方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句];
SELECT 表达式 INTO @用户变量  [FROM 等子句];

查看用户变量的值

SELECT @用户变量

e.g. 可以试运行下面语句进行体会

SET @a = 1;

SELECT @a;

SELECT @num := COUNT(*) FROM employees;

SELECT @num;

SELECT AVG(salary) INTO @avgsalary FROM employees;

SELECT @avgsalary;

SELECT @big;

18.1.2.2 局部变量

定义：可以使用DECLARE 语句定义一个局部变量
作用域：仅仅在定义它的 BEGIN … END 中有效
位置：只能放在 BEGIN … END 中，而且只能放在第一句

BEGIN
    #声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];

    #为局部变量赋值
    SET 变量名1 = 值;
    SELECT 值 INTO 变量名2 [FROM 子句];

    #查看局部变量的值
    SELECT 变量1,变量2,变量3;
END

18.2 定义条件与处理程序

定义条件 是事先定义程序执行过程中可能遇到的问题
处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。

18.2.1 定义条件

定义条件就是给MySQL中的错误码命名，这有助于存储的程序代码更清晰。
它将一个错误名字和指定的错误条件关联起来。
这个名字可以随后被用在定义处理程序的DECLARE HANDLER 语句中。

定义条件使用DECLARE语句

DECLARE 错误名称 CONDITION FOR 错误码（或错误条件）

e.g.

#使用MySQL_error_code
DECLARE Field_Not_Be_NULL CONDITION FOR 1048;

#使用sqlstate_value
DECLARE Field_Not_Be_NULL CONDITION FOR SQLSTATE '23000';

为SQL执行过程中发生的某种类型的错误定义特殊的处理程序

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式：处理方式有3个取值：CONTINUE、EXIT、UNDO。
- CONTINUE ：表示遇到错误不处理，继续执行。
- EXIT ：表示遇到错误马上退出。
- UNDO ：表示遇到错误后撤回之前的操作。MySQL中暂时不支持这样的操作。
错误类型（即条件）可以有如下取值：
- SQLSTATE ‘字符串错误码’ ：表示长度为5的sqlstate_value类型的错误代码；
- MySQL_error_code ：匹配数值类型错误代码；
- 错误名称：表示DECLARE … CONDITION定义的错误条件名称。
- SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
- NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
- SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；
处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。
语句可以是像“ SET 变量 = 值 ”这样的简单语句，也可以是使用BEGIN … END 编写的复合语句。

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

18.3 流程控制

流程的三种结构
顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

3 类流程控制语句
条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

18.3.1 分支结构之 IF

IF 语句的语法结构是：

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

特点：
① 不同的表达式对应不同的操作
② 使用在begin end中

类似Java：

if(表达式1){
	操作1
}else if(表达式2){
	操作2
}else if(...){
	...
}else{
	操作n
}

18.3.2 分支结构之 CASE

CASE 语句的语法结构1：

# 情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

类似Java：

 switch (表达式) {
    case 常量表达式或枚举常量:
        语句;
        break;
    case 常量表达式或枚举常量:
        语句;
        break;
    ......
    default: 
    	语句;
        break;
}

CASE 语句的语法结构2：

#情况二：类似于多重if
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

类似Java：

if(表达式1){
	操作1
}
if(表达式2){
	操作2
}
if(...){
	...
}
else{
	操作n
}

18.3.3 循环结构之LOOP

[loop_label:] LOOP
    循环执行的语句
END LOOP [loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略。

18.3.4 循环结构之WHILE

[while_label:] WHILE 循环条件  DO
    循环体
END WHILE [while_label];

18.3.5 循环结构之REPEAT

[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

18.3.6 跳转语句之LEAVE语句

LEAVE语句：
可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。
可以把 LEAVE 理解为 break。

LEAVE 【标记名】

其中，【标记名】是循环的标志。
LEAVE和BEGIN … END或循环一起被使用。

DELIMITER //

CREATE PROCEDURE leave_begin(IN num INT)

    begin_label: BEGIN
        IF num<=0 
            THEN LEAVE begin_label;
        END IF;
        
        SELECT COUNT(*) FROM employees;
    END //
DELIMITER ;

18.3.7 跳转语句之ITERATE语句

ITERATE语句：只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。
如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

ITERATE label

18.4 游标

游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

第一步，声明游标
在MySQL中，使用DECLARE关键字来声明游标，其语法的基本形式如下：

DECLARE cursor_name CURSOR FOR select_statement;

第二步，打开游标

OPEN cursor_name

第三步，使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

第四步，关闭游标

CLOSE cursor_name

第19章触发器

触发器是由事件来触发某个操作，包括：INSERT 、UPDATE 、DELETE 事件。所谓事件就是指用户的动作或者触发某项行为。
如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

19.1 触发器的创建

19.1.1 创建触发器语法

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名 
FOR EACH ROW 
触发器执行的语句块;

表名：表示触发器监控的对象。
BEFORE|AFTER ：表示触发的时间。
- BEFORE 表示在事件之前触发；
- AFTER 表示在事件之后触发。
INSERT|UPDATE|DELETE ：表示触发的事件。
- INSERT 表示插入记录时触发；
- UPDATE 表示更新记录时触发；
- DELETE 表示删除记录时触发。

19.2 查看、删除触发器

19.2.1 查看触发器

方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS\G

方式2：查看当前数据库中某个触发器的定义

SHOW CREATE TRIGGER 触发器名

方式3：从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

SELECT * FROM information_schema.TRIGGERS;

19.2.2 删除触发器

触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：

DROP TRIGGER  IF EXISTS 触发器名称;

MarcyTheLibrarian

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础

多图、多表、代码，多管齐下，带你掌握MySQL基本使用。
复制链接

扫一扫

专栏目录

MySQL基础

第1章 数据库概述

1.1 数据库的相关概念

1.2 常见的数据库

1.3 关系型数据库(RDBMS)

1.3.1 表、记录、字段：

1.3.2 表的关联关系

1.4 非关系型数据库

第2章 MySQL环境搭建

第3章 启动MySQL命令

第4章 MySQL演示使用

4.1 MySQL的使用演示

4.2 MySQL的编码设置

4.3 MySQL图形化管理工具

4.4 MySQL目录结构与源码

4.4.1 主要目录结构

4.4.2 MySQL 源代码获取

第5章 基本的SELECT语句

5.1 SQL概述

5.2 SQL语言的规则与规范

5.2.1 关于标点符号

5.2.2 大小写规范

5.2.3 注释

5.2.4 命名规则

5.3 SQL部分指令

5.3.1 数据导入指令

5.3.2 列的别名

5.3.3 去除重复行

5.3.4 显示表结构

第6章 运算符

6.1 算术运算符

6.2 比较运算符

6.2.1 符号类

6.2.2 非符号类

特别的说明：

LIKE

REGEXP

6.2.3 逻辑运算符

6.2.3.1 逻辑非运算符

6.2.3.2 逻辑与运算符

6.2.3.3 逻辑或运算符

6.2.3.4 逻辑异或运算符

6.2.4 位运算符

6.2.5 运算符的优先级

第7章 排序与分页

7.1 排序数据

7.2 分页

第8章 多表查询

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

8.2 多表查询分类讲解

8.2.1 使用别名可以简化查询

8.2.2 连接多个表

8.2.2.1 内连接(INNER JOIN)的实现

8.2.2.2 外连接(OUTER JOIN)的实现

8.3 UNION的使用

8.4 7种JOINS的实现

8.5 新特性

8.5.1 自然连接

8.5.2 USING连接

8.5.3 表连接的约束条件

第9章 单行函数

9.1 数值函数

9.2 字符串函数

9.3 日期和时间函数

9.3.1 获取日期、时间

9.3.2 日期与时间戳的转换

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

9.3.4 时间和秒钟转换的函数

9.3.5 计算日期和时间的函数

9.3.6 日期的格式化与解析

9.4 流程处理函数

9.5 加密与解密函数

9.6 MySQL信息函数

9.7 其他函数

第10章 聚合函数

10.1 基本介绍

10.2 基本使用

10.2.1 使用单个列分组

10.2.2 使用多个列分组

10.2.3 HAVING

第1章数据库概述

第3章启动MySQL命令

第5章基本的SELECT语句

第6章运算符

第7章排序与分页

第8章多表查询

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

第9章单行函数

第10章聚合函数

10.2.4 SELECT的执行过程`顺序很重要`

第11章子查询

第12章创建和管理表

第13章数据处理之增删改

第15章约束

第16章视图

第17章存储过程与函数