Mysql入门到精通全集（SQL99）包含关系运算，软考数据库工程师复习首选

目录

数据（Date）：

1、结构化查询语句：SQL

三级模式、二级映像：

两类模型：概念模型 逻辑模型和物理模型

常用的数据模型

关系模型的数据结构

2、关系运算

关系

关系完整性

关系运算：

3、数据类型：

算数运算符

逻辑运算符

查询函数：

4、存储引擎

5、语句分类

6、常用命令

7、DQL语句

条件查询：where

运算查询：

常用函数：

排序查询：order by

分组查询：group by

连接查询：添加条件，满足的被筛选出来

子查询：

相关子查询：

关键字：

8、DDL语句

表的创建：

表的删除：

修改表结构：

约束：

9、DML语句

添加元素：

修改元素：

删除元素：

10、DCL

事务隔离级别：

11、DBA

12、索引

13、视图

14、数据库设计三范式

15、触发器

16、存储过程

17、变量

18、流程控制

19、游标

20、事件

21、预处理

22、函数

多行处理函数:

单行处理函数:

运算函数：

比较运算的函数

三角函数：

指数和对数：

进制转换函数：

类型转换函数：

字符串函数：

日期函数：

格式化与解析函数

流程控制函数：

加密函数：

系统信息函数：

JSON函数：

用户自定义函数

数据（Date）：

数据是数据库中存储的基本对象，描述事物的符号记录称为数据。数据的含义称为数据的语义，数据与其语义是不可分的

数据库：DB（Database）：

是长期储存在计算机内、有组织的、可共享的大量数据的集合

数据库管理系统：DBMS

DBMS是一个大型复杂的基础软件系统，位于用户与操作系统之间的一层数据管理软件。DBMS能够科学地组织和存储数据、高效地获取和维护数据。

DBMS具有数据定义（提供数据库定义语言DDL），数据组织、存储和管理（提供数据操作语言DML），数据库的事务管理和运行管理（安全性等），数据库的建立和维护等功能。

数据库系统（DMS）：

数据库系统由数据库、数据库管理系统（及其应用开发工具）、应用程序、数据库管理员四部分构成。

1、结构化查询语句：SQL

DBMS–执行–>SQL–操作–>DB

数据库最基本单元是表：table

行（row）：数据/记录
​
列（column）：字段（字段名、数据类型、约束）

命名规范：所有标识符全部小写，下划线分割

分号表示结束，不见分号不执行

数据库字符串统一用单引号

端口号3306

xxx.sql：SQL脚本文件（source执行）

“型” 和“值” 的概念

型（Type）

对某一类数据的结构和属性的说明
​
值（Value）

是型的一个具体赋值

三级模式、二级映像：

应用–>外模式–>模式–>内模式–>数据库

外模式/模式映像   内模式/模式映像

模式（也称逻辑模式）

数据库中全体数据的逻辑结构和特征的描述

所有用户的公共数据视图

一个数据库只有一个模式

模式的地位：是数据库系统模式结构的中间层

与数据的物理存储细节和硬件环境无关

与具体的应用程序、开发工具及高级程序设计语言无关

外模式（也称子模式或用户模式）

数据库用户（包括应用程序员和最终用户）使用的局部数据的逻辑结构和特征的描述

数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

外模式的地位：介于模式与应用之间

模式与外模式的关系：一对多

外模式通常是模式的子集

一个数据库可以有多个外模式。反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求

对模式中同一数据，在外模式中的结构、类型、长度、保密级别等都可以不同

外模式与应用的关系：一对多

同一外模式也可以为某一用户的多个应用系统所使用

但一个应用程序只能使用一个外模式

内模式（也称存储模式）

是数据物理结构和存储方式的描述

是数据在数据库内部的表示方式

记录的存储方式（例如，顺序存储，按照B树结构存储，按hash方法存储等）

索引的组织方式

数据是否压缩存储

数据是否加密

数据存储记录结构的规定

一个数据库只有一个内模式

两类模型：概念模型 逻辑模型和物理模型

概念模型： 也称信息模型，它是按用户的观点来对数据和信息建模，用于数据库设计

逻辑模型和物理模型 :

逻辑模型主要包括网状模型、层次模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等。按计算机系统的观点对数据建模，用于DBMS实现。

物理模型是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法。

概念模型的一种表示方法：实体E-联系R方法（表示实体属性关系）

（1）实体（Entity）

客观存在并可相互区别的事物称为实体。
​
可以是具体的人、事、物或抽象的概念。

（2）属性（Attribute）

实体所具有的某一特性称为属性。
​
一个实体可以由若干个属性来刻画。  

（3）码（Key）

唯一标识实体的属性集称为码。

（4）实体型（Entity Type）

用实体名及其属性名集合来抽象和刻画同类实体称为实体型

（5）实体集（Entity Set）

同一类型实体的集合称为实体集

实体联系方法：E-R:一对一（n：n），一对多（n：m），多对多（m：m） 线 树 图

实体：矩形

联系：菱形

属性：椭圆

工具：mysql workbench

常用的数据模型

层次模型（Hierarchical Model）：

用树形结构来表示各类实体以及实体间的联系

网状模型（Network Model）：

网状模型中子女结点与双亲结点的联系可以不唯一要为每个联系命名，并指出与该联系有关的双亲记录和子女记录

关系模型（Relational Model)）：

关系模型中数据的逻辑结构是一张二维表，它由行和列组成

面向对象数据模型（Object Oriented Data Model）

对象关系数据模型（Object Relational Data Model）

半结构化数据模型（Semistruture Data Model）

关系模型的数据结构

关系的完整性约束条件

实体完整性

参照完整性

用户定义的完整性

2、关系运算

域是一组具有相同数据类型的值的集合

关系

D1×D2×…×Dn的子集叫作在域D1，D2，…，Dn上的关系，表示为

R（D1，D2，…，Dn）

R：关系名

n：关系的目或度（Degree）

元组

关系中的每个元素是关系中的元组，通常用t表示。

单元关系与二元关系

当n=1时，称该关系为单元关系（Unary relation）或一元关系

当n=2时，称该关系为二元关系（Binary relation）

关系的表示

关系也是一个二维表，表的每行对应一个元组，表的每列对应一个域

属性

关系中不同列可以对应相同的域

为了加以区分，必须对每列起一个名字，称为属性（Attribute）

n目关系必有n个属性

码

候选码（Candidate key）

若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码

简单的情况：候选码只包含一个属性

全码（All-key）

最极端的情况：关系模式的所有属性组是这个关系模式的候选码，称为全码（All-key）

主码

若一个关系有多个候选码，则选定其中一个为主码（Primary key）

主属性

候选码的诸属性称为主属性（Prime attribute）

不包含在任何侯选码中的属性称为非主属性（Non-Prime attribute）或非码属性（Non-key attribute）

三类关系

基本关系（基本表或基表）

实际存在的表，是实际存储数据的逻辑表示

查询表

查询结果对应的表

视图表

由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据

基本关系的性质

① 列是同质的（Homogeneous）

② 不同的列可出自同一个域

其中的每一列称为一个属性

不同的属性要给予不同的属性名

③ 列的顺序无所谓,，列的次序可以任意交换

④ 任意两个元组的候选码不能相同

⑤ 行的顺序无所谓，行的次序可以任意交换

⑥ 分量必须取原子值

这是规范条件中最基本的一条

关系模式（Relation Schema）是型,关系是值,关系模式是对关系的描述

元组集合的结构

属性构成

属性来自的域

属性与域之间的映象关系

关系模式可以形式化地表示为：

R（U，D，DOM，F）

R 关系名

U 组成该关系的属性名集合

D U中属性所来自的域

DOM 属性向域的映象集合

F 属性间数据的依赖关系的集合

关系模式通常可以简记为

R (U) 或 R (A1，A2，…，An)

R: 关系名

A1，A2，…，An : 属性名

注：域名及属性向域的映象常常直接说明为属性的类型、长度

关系完整性

实体完整性和参照完整性

关系模型必须满足的完整性约束条件称为关系的两个不变性，应该由关系系统自动支持

用户定义的完整性

应用领域需要遵循的约束条件，体现了具体领域中的语义约束

实体完整性规则（Entity Integrity）

若属性A是基本关系R的主属性，则属性A不能取空值

（1）实体完整性规则是针对基本关系而言的。

一个基本表通常对应现实世界的一个实体集。

（2）现实世界中的实体是可区分的，即它们具有某种唯 一性标识。

（3）关系模型中以主码作为唯一性标识。

（4）主码中的属性即主属性不能取空值。

参照完整性

设F（课程号、学号）是基本关系R（选修表）的一个或一组属性，但不是关系R的码。如果F（课程号、学号）与基本关系S（课程表、学生表）的主码Ks相对应，则称F（课程号、学号）是R（选修表）的外码

基本关系R称为参照关系 (选修表)（Referencing Relation）

基本关系S称为被参照关系（学生表、课程表）（Referenced Relation）或目标关系（Target Relation）

参照完整性规则

若属性（或属性组）F是基本关系R的外码它与基本关系S的主码Ks相对应（基本关系R和S不一定是不同的关系）则对于R中每个元组在F上的值必须为：

关系运算：

并（U）：合并

差（-）：差异

交（n）：相同

笛卡尔积（x）：乘积

选择（where）：查找

投影（selcet）：找出

连接（join）：

θ连接：有条件的笛卡尔积

等值连接：θ为等于号

自然连接：特殊的等值连接，比较分量为相同的属性集并去除重复（没有重复属性就为笛卡尔积）

除：除去包含

专门的关系运算符

σ 选择

π 投影

⋈ 连接

÷ 除

3、数据类型：

算数运算符

逻辑运算符

查询函数：

4、存储引擎

存储引擎：mysql特有术语，指表存储数据的方式

在建表的时候在小括号后边指定

ENGINE:InnoDB（默认）：存储引擎

DEFAULT CHARSET=utf8：字符编码方式

show engines \\G:查看支持存储引擎

myisam：三个文件表示表（格式文件（mytable.frm），数据文件（.MYD），索引文件（.MYI））可被转换为压缩，只读表来节省空间

innodb：缺省引擎，支持事务，支持数据库崩溃后自动恢复机制（.frm），效率不高

memory：内存存储引擎，快，不能包含TEXT和BLOB字段，关机消失，非常不安全

5、语句分类

DQL：数据查询语言（带select）

DML：数据操作语言（对内容增删改查，insert增，delete删，update改）

DDL：数据定义语言（操作结构，create增，drop删、alter改）

DCL：事务控制语言（commit事务提交，rollback事务回滚）

DBA：数据控制语言（grant授权，revoke撤销权限）

6、常用命令

数据库命令

select version（）； 查看版本号

select databases（）； 查看所有数据库

select database（）； 查看当前数据库

结果集导出

select count(1) from table into outfile '/tmp/test.xls';

pager cat > /tmp/test.txt ; //自动写入

mysql -h 127.0.0.1 -u root -p XXXX -P 3306 -e "select * from table" > /tmp/test/txt

常用命令

启动命令
net start|stop MySQL 启动|关闭

/bin/mysql.exe 连接数据库

mysql -uroot -p123456 登录 -p 回车：隐藏输入密码

exit 退出

初始化命令
use 名称； 使用数据库

show databases； 查看全部数据库

show database 名称； 查看数据库

show create database 名称； 查看建库语句

show tables[匹配]； 查看数据库下有哪些表

source 路径（无中文） 导入SQL脚本

create database if not exists 名称； 创建数据库

default charset 字符集 collate 排序规则； 设置默认字符集与排序规则

charset（sharacter set） 字符集缩写

字符集_国家_ci（不分）|cs（区分大小写）|bin（按照二进制编码值比较）

alter database 名称 charset 字符集 collate 排序规则 修改数据库字符集与排序顺序

drop database 名称； 删除数据库

select * form 表明； 查看表数据

desc 表明； 查看表结构

show full columns form 表名； 查看详细表结构

7、DQL语句

执行顺序：select—-from—-where—–group by—–having——-ordre by——–

select 字段名 form 表名； 查询一个字段

select 字段名，字段二 form 表名； 查询多个字段

select 每个字段 form 表明； 查询所有字段

select 字段名 as 别名 form 表名； 起别名（显示），可以省略不加逗号，有空格加引号

条件查询：where

select 字段名 form 表名 where 条件；

# where not :查询与条件相反的结果

运算查询：

# 加减：

select id,id+1,id-1 from 表名；

默认无符号操作：可以强制类型转换

cast（……as signed）：

select cast（id as signed-1 from 表名；

#乘除：

浮点数精度为最大精度

查看精度默认值：

show variables like'div_precision_incremet'

除法运算浮点数结果为默认值与最大值求大

除数为0，结果为null（有null参与的运算输出都为null）

div：除法运算，只留整数

#求余：

mod和%功能相同

常用函数：

• ceil（x）：向上取整

• floor（x）：向下取整

• format（x，y）：保留x的y位小数（四舍五入）

• round（x）：四舍五入求整（可加y参数，与format相同）

• truncate（x，y）：保留x的y位小数（不进行四舍五入）

• abs（x）：绝对值

• avg（x）：平均值

• div（x，y）：除法（保留整数）

• mod（x，y）：求模

• pi（）：圆周率

• sqrt（x）：平方根

• pow（x，y）：幂运算

• rand（）：随机数

• floot（x+rand（）*（y-x））：得到一个最小x，最大y的随机整数

排序查询：order by

select 字段名 form 表名 order by 字段名； 默认升序

select 字段名 form 表名 order by 字段名 desc（asc）； 降序（升序）

select 字段名 form 表名 order by 字段名 desc（asc），字段二desc（asc）；多个字段排序

select 字段名 字段二 form 表名 order by 2； 按照查询第二个字段排序

convert强制转换gbk进行中文排序（null为最小值）

CONVERT(姓名 USING gbk)

分组查询：group by

有分组的话字段只能跟，参加分组的字段和分组函数

select 字段名 分组函数 form 表名 group by 字段名； 先分组后查询

select 字段名 分组函数 form 表名 group by 字段名 字段二； 两个字段联合为一个看 联合分组（连成一个）

#分组筛选：
select 字段名 分组函数 form 表名 group by 字段名 having 条件和函数； 再次过滤（优先选择where）

去除重复：distinct（最前，多个为联合去重）

• count（distinct···）：统计数量

• limit （起始下标）输出条数：限量输出

• with rollup：回溯统计（结果字段名为null，分组函数为和）与排序仅能出现一个

连接查询：添加条件，满足的被筛选出来

交叉连接：

返回笛卡尔积 cross join

内连接:join –on—

完全能匹配上的条件数据查询出来；inner关键词可省略

等值连接

select e.ename ，d.dname form emp e inner（可省略） join dept d on e.depton=d.depton where~；

select 表一地段，表二字段 form 表一 inner（表示内连接）join 表二 on 条件 where 附加条件；

非等值连接

select e.ename ，d.dname form emp e inner（可省略） join dept d on 字段一 between 字段二 and 字段三；

自连接：一张表看成两张表

select a.ename ‘员工’ ，b.ename ‘领导’ form emp a inner（可省略） join emp b on a.mgr =b.empno; ；

自然连接：natural join

自动查询两张表连接条件

select ……from……natural join ……

using：简化等值连接

select ……from……join ……using（连接字段）

union ：

合并查询结果集相当于 where的or，in；

可以减少匹配次数，效率较高；放在两条相同字段语句之间；

需要列数相同，数据类型一致（出条件外都相同）

自动去重

union all： 不去重

外连接:一张表看成两张表；产生主次关系；outer关键词可省略

• 左外连接（left）：左表为主表，右表没有的返回null

• 右外连接（right）：返回右表所有指定记录和满足连接条件的记录

# join后表格全匹配
select e.ename ，d.dname form emp e right outer join dept d on e.depton=d.depton where~；

多表连接：内外连接可混合使用

select
form
a join b
on a和b连接条件
join c
on a和c连接条件~

子查询：

嵌套，可以在select—-from—-where—-order by 四个关键词后面使用，先执行子查询

单行子查询：

操作符： = < > <> <= =>

# where子句中的子查询

select emane,sal from emp where sal>(select min(sal) from emp);

多行子查询（集合比较子查询）：

关键字子查询：在括号前面加（not）in：等于任意一个

和单行操作符一起使用：any（some）：匹配任意一个 all：匹配所有

from子句子查询：可以将子查询的查询结果当做一张临时表；注意字段和表都要起别名

select t.* ,s.geade from (select job,avg(sal) as avgsal from emp group by job) t

join salgrade s on t.avgsal between s.losal and s.hisal

select子句子查询：只能查出 一条记录

相关子查询：

内查询引用外查询的对象就叫相关子查询

select last_name,salary,department_id

from employees e1

where salary >(

​ select AVG(salary) from employees e2

​ where e1.department_id=e2.department_id

​ GROUP BY department_id

​ )

关键字：

（not）exists：查询在子查询中是否存在满足条件的行

limit：将查询结果一部分取出来，常用于分页查询;起始下标可省略（执行在order by 后）

select ename ,sal from emp oeder by sal desc limit 5(2：起始下标0代表1,5：长度);

通用分页：每页显示n条记录，第m页起始下标就为（m-1）*n；

limit（pageno-1）*pagesize，pagesize

8、DDL语句

表的创建：

Create table 表名（

字段一 数据类型（建议长度，不是字节） 约束，

字段二 数据类型（可不加使用默认） default‘默认值’，

字段三 数据类型（可以超过）comment '别名'

约束（字段一，字段二） //联合约束（联合起来唯一）

）engine=innoDB DEFAULT CHARSET=UTF8；

表名：建议以t_或者tbl_开始，可读性强，见明知意

字段名：见明知意；二者都属于标识符

创建临时表：

Create temporary table 数据库.表名； //如果数据库不存在

表的删除：

drop table if exists 表名1，2，···； 如果存在就删除，可省略

drop table 表名； 删除父表保留子表先接触约束

修改表结构：

#修改表名
alter table 旧表名 rename [to/as]新表明 rename table……to……（省略写法）
#修改字段数据类型
alter table 表名 modify 字符名 数据类型
#修改字段排列位置
alter table 表名 modify 字符一（要修改位置的字段） 数据类型 flrst（第一个） | after 字段二（一放二后面）
# 修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型
#修改字符编码
alter table 表名 modify 字段名 数据类型 charset utf8；

#添加字段
alter table 表名 add 新字段 数据类型 [约束条件] [flrst（设置为第一个字段） | after 已存在字段名（放在已存在字段名后面）]
#删除字段
alter table 表名 drop 字段名
#修改存储引擎
alter table 表名 engine=存储引擎

#添加主键约束
alter table 表名 add primary key（字段一，字段二）
#添加外键约束
alter table 表名 add constraint 外键约束名 foreign key（外键字段） references 关联表名（关联字段）
#唯一约束名
alter table 表名 add constraint 唯一约束名 unique（字段名）

#删除约束
alter table 表名 drop primary key
#外键约束名
alter table 表名 drop foreign key
#唯一约束名
alter table 表名 drop index

约束：

对插入数据库中的数据进行限定，保证表中数据完整有效

直接添加在列后，叫列级约束，添加在最后，叫表级约束

联合约束（多个字段联合起来添加某一个约束）

非空约束：not null

约束的数据不能为空

唯一性约束：unique

约束的数据不能重复，可以为null

联合唯一，写在最后面：unique（name，age）

主键约束（pk）：primary key

mysql中一个字段同时被唯一非空约束，自动变成主键字段

主键值：每一行记录的唯一标识，身份证明（不能为空，不能重复），建议int ，char定长，不建议varchar

可表级约束，叫复合主键，不建议使用

自然主键：自然数，和业务无关（使用较多，不需要有意义）

业务主键：和业务紧密关联（业务发生变动时，可能影响到主键值）

reimary key auto_increment:自增主键（以一开始）

外键约束（fk）：foreign key

引用父表的字段作为字表一个字段的约束（保证字段值有效）

foreign key（字段） references 父表（字段）

被子表引用父表中的字段，被引用的这个字段必须不一定主键但至少有唯一性约束，外键值可以为null

cascade 删除主表的某个字段的时候，子表含有这个字段的数据会被清空，这个还是属于相对危险的一个操作的

set null 删除主表的某个字段的时候，子表含有这个字段的这个哪一行的这个字段会用null来显示，

但是有个细节就是设置外键的这个字段字段类型就不能设置为not null类型的，否则会报错

no action 这个字段会比较有意思，也就是说当主表要删除某个行的时候

如果外键关联有含有这个主表的外键的字段数据的话，就不会删除成功，系统会直接报错

cascade,set null,no action意思是也是一样的，主表某个字段更新了，子表也会更新那个字段！！！

主表更新的某条数据，子表的使用的那个数据会变成空，和子表在使用的情况下，主表就不能够更新数据

检查约束：check（mysql不支持，Oracle支持）

默认值约束： default

自增长约束：anto_increment

无符号约束： unsigned

零填充约束： zerofill

指定字符集约束： character set 字符集名/charset 字符集

注释约束： comment '注释'

9、DML语句

添加元素：

表的值和字段名要一一对应（数量，数据类型），但凡执行成功必定多出一行，无值默认null，前面字段省略意味都写上，值也要跟够

insert into 表名（字段名1，字段名2…） values（值1，值2…）;

一次添加多条：values（值1，值2…），（值1，值2···），···

快速复制，同时建表：create table emp2 as select * from emp; ||create table emp2 like emp;

查询结果插入：insert into 表名 select * from depy；

主键冲突：on duplicate key update //如果冲突就更新为

replace into //替换

修改元素：

没有条件限制会导致所有数据全部更新

update 表名 set 字段名1=值1，字段名2=值2……where 条件；

删除元素：

没有条件表将全部删除

delete form 表名 where 条件； //逐条删除，比较慢、日志记录，可恢复、有有效返回值（DML）

快速删除：truncate table 表名 //清空内容，不可恢复，效率高，不可添加条件，自增长数据不保留（DDL）

10、DCL

事务：一个事务就是一个完整的业务逻辑

操作DML语言

做某件事需要多条DML语句，所以要添加事务（目的是使多条DML语句同时成功 或同时失败）

提交事务：清空事务性日志文件，将数据全部彻底持久化到数据库中（标志事务全部成功的结束）

回滚事务：清空事务性日志文件，将之前操作全部撤销（标志事务全部失败的结束）

select @@autocommit:查看手动提交是否开启

start transaction：开启事务（关闭自动提交机制）

commit：提交事务

rollbake:回滚事务（只能回滚到上一次提交点）

savepoint 保存点名称：创建保存点

release savepoint 保存点：删除保存点

rollback to savepoint 保存点：回滚到保存点

事务特性：

A(原子性）：事务是最小工作单元，不可再分

C（一致性）：要求必须同时成功，同时失败

I（隔离性）：ab事务具有一定隔离

D（持久性）：事务最终结束的一个保障

事务隔离级别：

查看全局事务隔离级别：select @@tx_ioslation

设置全局事务隔离级别：set global teansaction ioslation 隔离级别

读未提交（read uncommitted）：事务a可以读取到事务b未提交的数据（容易脏读数据，理论级别）

读已提交（read committed）：事务a只能读取到事务b提交的数据

（不可重复读取数据，比较真实的数据，读到的数据绝对真实，Oracle默认级别）

可重复读（repeatable read）：事务a开启后，不管多久，每一次事务a读取到的数据都是一致的

（可能出现幻影读，不够真实，mysql默认级别）

序列化/串行化：(serializable)：最高级别，表示事务排队，不能并发，效率最低，最真实

11、DBA

# 新建用户：

create user 用户名 identified by ‘密码’

# 数据的导入:

source D:\\lujing.sql

# 数据的导出：在dos窗口不登录

mysqldump 数据库>D:\\lujing.sql -uroot -p123456 导出数据库

mysqldump 数据库 表>D:\\lujing.sql -uroot -p123456 导出表

12、索引

索引（index）B-Tree结构（自平衡二叉树）：

在数据库表的字段上添加，一张表的一个字段可以添加一个索引，也可以多个字段联合添加，需要排序

任何数据库主键都会添加索引, mysql如果字段有unique约束，也会自动创建索引编号

#创建索引 create index 索引名 on 表名（字段）；

create index emp_ename_index on emp（ename）;

#删除索引 drop index 索引名 on 表名；

drop index emp_ename_index on emp;

#查看是否使用索引：解释语句

explain 搜索语句；

索引失效：

• 模糊搜索‘%’开头

• 使用or时必须两边都有索引

• 使用复合索引（联合索引）的时候，没有使用左侧的列查找，

• 参加运算

• 索引列使用了函数

索引分类：单一索引，复合索引，主键索引，唯一性索引

13、视图

视图（view）：站在不同的角度看待同一对象，对视图操作会影响原表，作用是方便，简化语句，引用作用，后面只能是查询语句，完成后可以进行CRUD

#创建视图：

create view 视图名 as sql语句；

#修改视图：

alter view 视图名 as DQL;

create or replace view 视图名 as DQL;

update shitu set 出生日期='1992-05-21' where 姓名='赵嘉盟';

insert into shitu(员工ID,姓名,性别) values(101015,'大炮','男');

delete from shitu where 员工ID=101015;

#删除视图：

drop view emp_view；

14、数据库设计三范式

数据库设计三范式：

表的设计依据，可以减少表中数据的冗余，空间的浪费，造成插入、更新、删除操作异常

第一范式（1NF）：

第一张表必须有主键，每一个字段原子（属性）不可再分，遵循原子性

（一对一，外键唯一）

第二范式（2NF）：

建立在第一范式之上，要求所有非主键字段完全依赖主键，不要产生部分依赖

（多对多，三张表，关系表两个外键）

第三范式（3NF）：

建立在第二范式之上，要求所有非主键字段直接依赖主键，不要产生传递依赖，非主键字段不能相互依赖（一对多，两张表，多的表加外键）

数据库三范式是理论上的，实践和理论可能有偏差，最终目的都是为了满足客户需求，有时候会拿冗余换执行速度, 因为在SQL中，表和表的连接次数越多，效率越低（笛卡尔积），有时候为了减少连接次数而存在冗余是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低

按照规范的设计方法，一个完成的数据库设计一般分为以下六个阶段：

15、触发器

触发器trigger，是一种特殊的存储过程，是由事件来触发的，是表定义的一部分

每个表最多支持6个触发器，单一触发器不能与多个事件或多个表关联

存储过程使用时需要调用，而触发器是在预先定义好的事件（如INSERT、DELETE等操作）发生时，才会被MySQL自动调用

创建触发器时需要与数据表相关联，当表发生特定事件（如INSERT、DELETE等操作）时，就会自动执行触发器中提前预定好的SQL代码。

功能：

实现插入数据前，强制检验或转换数据等操作，或是在触发器中代码执行错误后，撤销已执行成功的操作，保证数据的安全。

优点：

触发器可以通过数据库中的相关表实现级联无痕更改操作；保证数据安全，进行安全校验。

缺点：

触发器的使用会影响数据库的结构，同时增加了维护的复杂程度；触发器的无痕操作会造成数据在程序（如PHP、Java等）层面不可控。

作用：

可以通过触发器对数据库的相关表，实现级联更改

可以在写入数据表前，强制检验或转换数据，保证数据安全

触发器发生错误，异动的结果会被撤销

创建：

在创建触发器时需要指定触发器的操作对象——数据表，且该数据表不能是临时表或视图

CREATE TRIGGER 触发器名字 触发时机 触发事件 ON 表 FOR EACH ROW 触发顺序

BEGIN

操作的内容

END

指定数据库下的触发器名必须唯一，而不同数据库可含有名称相同的触发器。

“ON 表 FOR EACH ROW”指定触发器的操作对象。

触发时机表示数据表在发生变化前后的两种状态。

触发事件表示激活触发器的操作类型。

触发顺序表示指定同一个表中多个触发器的执行顺序，默认按创建顺序激活。

操作：

#触发时机
BEFORE 在表中数据发生改变前的状态

AFTER 在表中数据已经发生改变后的状态

#触发事件
INSERT 插入操作

UPDATE 更新操作

DELETE 删除操作

#触发顺序
FOLLOWS 其他触发器名称 新触发器在现有触发器之后激活

PRECEDES 其他触发器名称 新触发器在现有触发器之前激活

使用：

old.列名：关联现有行的一列再被更新或删除前的值，获取删除或更新以前的值，获取的字段值全部为只读形式，不能更新

new.列名：获取插入或更新时产生的新值

mysql> DELIMITER $$

mysql> CREATE TRIGGER insert_tri BEFORE INSERT

​ -> ON sh_user_shopcart FOR EACH ROW

​ -> BEGIN

​ -> DECLARE stocks INT DEFAULT 0;

​ -> SELECT stock INTO stocks FROM sh_goods WHERE id = new.goods_id;

​ -> IF stocks <= new.goods_num THEN

​ -> SET new.goods_num := stocks;

​ -> UPDATE sh_goods SET stock=0 WHERE id = new.goods_id;

​ -> ELSE

​ -> UPDATE sh_goods SET stock=stocks-new.goods_num

​ -> WHERE id = new.goods_id;

​ -> END IF;

​ -> END;

​ -> $$

Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;

年龄小于60：

CREATE TRIGGER gg BEFORE INSERT ON 职工 FOR EACH ROW

BEGIN

if new.年龄>60 then

signal sqlstate 'HY000' SET MESSAGE_TEXT = '年龄不能超过60';

end if;

END;

查看：

一种就是利用MySQL提供的SHOW TRIGGERS语句

SHOW TRIGGERS [{FROM | IN} 数据库名称] [LIKE '匹配模式' | WHERE 条件表达式]

当未通过FROM或IN指定数据库时，SHOW TRIGGERS获取的是当前选择的数据库下所有的触发器
WHERE用于指定查看触发器的条件。
LIKE子句的使用比较特殊，它用于匹配触发器作用的数据表，而非触发器名称。

一种就是利用SELECT直接查看information_schema数据库下的triggers数据表中的数据。

删除：

DROP TRIGGER [IF EXISTS] [数据库.]触发器名称

当删除数据表时，也会同时删除该表上创建的触发器

16、存储过程

对于SQL编程而言，存储过程是数据中的一个重要的对象，它是在大型数据库系统中一组为了完成特定功能的SQL语句集，在第一次使用经过编译后，再次调用就不需要重复编译，因此执行效率比较高。

存储过程与函数的不相同点有四个，具体如下:

• 语法中实现的标识符不同，存储过程使用PROCEDURE，函数为FUNCTION。

• 存储过程在创建时没有返回值，而函数在定义时必须设置返回值。

• 存储过程没有返回值类型，且不能将结果直接赋值给变量；而函数定义时需要设置返回值类型，且在调用时必须将返回值赋给变量。

• 存储过程必须通过CALL进行调用，不能使用SELECT调用；而函数则可在SELECT语句中直接使用。

创建：

DELIMITER 新结束符号

CREATE PROCEDURE 过程名字([[ IN | OUT | INOUT ] 参数名称 参数类型])

BEGIN

过程体

END

新结束符号

DELIMITER ;

IN：表示输入参数，即参数是在调用存储过程时传入到存储过程里面使用，传入的数据可以是直接数据（如5），也可以是保存数据的变量。

OUT：表示输出参数，初始值为NULL，它是将存储过程中的值保存到OUT指定的参数中，返回给调用者。

INOUT：表示输入输出参数，即参数在调用时传入到存储过程，同时在存储过程中操作之后，又可将数据返回为调用者。

查看存储过程的创建语句

SHOW CREATE PROCEDURE 过程名;

查看所有符合要求的存储过程

SHOW PROCEDURE STATUS [LIKE 匹配模式];

要想存储过程发挥作用，必须使用MySQL提供的CALL语句调用。

由于存储过程和数据库相关，如果要执行其他数据库中的存储过程，则调用时需要指定数据库名称。

调用：

CALL 数据库名.存储过程名称([实参列表]);

修改：

ALTER PROCEDURE 存储过程名称 [特征]

特征指的是存储过程需要修改哪个部分。

ALTER PROCEDURE不能更改存储过程的参数或主体。

删除：

DROP PROCEDURE [IF EXISTS] 存储过程名称

17、变量

系统变量也可称为全局变量，指的就是MySQL系统内部定义的变量，对所有MySQL客户端都有效。

默认情况下，会在服务器启动时使用命令行上的选项或配置文件完成系统变量的设置。

查看：

SHOW [GLOBAL | SESSION] VARIABLES [LIKE '匹配模式' | WHERE 表达式]

GLOBAL：显示全局系统变量值，当变量没有全局值时，则不显示任何值。

SESSION：默认的修饰符，可用LOCAL替换，也可省略，用于显示当前连接中有效的系统可变值，如果变量没有会话值，则显示全局变量值。

SHOW VARIABLES时不带任何条件则可以获取当前连接中系统所有有效的变量。

带有“@@”的变量，MySQL会将其判断为系统变量或全局变量

修改：

局部修改：SET 变量名 = 新值;

全局修改：

对所有正在连接的客户端无效，它只针对新连接的客户端永久生效。

SET GLOBAL 变量名 = 值;

会话变量：也可称为用户变量，指的是用户自定义的变量，跟MySQL当前客户端是绑定的，仅对当前用户使用的客户端生效。

会话变量的组成：“@”符号和变量名，在定义会话变量时必须为该变量赋值。

会话变量的赋值方式：一是利用SET语句，二是在SELECT语句中利用赋值符号“:=”完成赋值，最后一种是利用SELECT…INTO语句。

由于MySQL中的比较运算符与赋值运算符“=”相同，为了避免系统分不清楚“=”是比较运算符还是赋值运算符，强烈推荐为变量赋值时使用MySQL专门提供的赋值运算符“:=”。

局部变量：DECLARE 变量名1 [, 变量名2] … 数据类型 [DEFAULT 默认值]

局部变量的名称和数据类型是必选参数，当同时定义多个局部变量时，它们只能共用同一种数据类型。

DEFAULT用于设置变量的默认值，省略时变量的初始值为NULL。

18、流程控制

判断语句

IF(条件表达式, 表达式1, 表达式2)

当条件表达式的值为TRUE时，则判断的结果返回表达式1的值，否则返回表达式2的值。

条件表达式不能是与0或NULL进行比较的表达式。

IF 条件表达式1 THEN 语句列表

[ELSEIF 条件表达式2 THEN 语句列表] … [ELSE 语句列表]

END IF

当条件表达式1为真时，执行对应THEN子句后的语句列表。

条件表达式1为假时，继续判断条件表达式2是否为真，若为真，则执行其对应的THEN子句后的语句列表，依次类推。

若所有条件表达式都为假，则执行ELSE子句后的语句列表。

每个语句列表必须由一个或多个SQL语句组成，且不允许为空

CASE 条件表达式 WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2] … [ELSE 结果] END

用CASE的条件表达式与WHEN后子句中的表达式进行比较，直到与其中的一个表达式相等时，则输出对应的THEN子句后的结果

CASE WHEN 条件表达式1 THEN 结果1 [WHEN 条件表达式2 THEN 结果2] … [ELSE 结果] END

直接判断WHEN后的条件表达式，直到其中一个的判断结果为真时，输出对应的THEN子句后的结果；若WHEN子句的表达式都不满足，则执行ELSE子句后的结果，另外当CASE语句中不含ELSE子句时，判断结果直接返回NULL。

循环控制：

LOOP 语句列表 END LOOP [标签];

LOOP用于重复执行语句列表，在语句列表中需要给出结束循环的条件，否则会出现死循环。通常使用判断语句进行条件判断，使用“LEAVE 标签”语句退出循环。

标签的定义只要符合MySQL标识符的定义规则即可。

mysql> DELIMITER $$ //计算1-9数字之和

mysql> CREATE PROCEDURE proc_sum()

-> BEGIN

​ -> DECLARE i, sum INT DEFAULT 0;

​ -> sign: LOOP

​ -> IF i >= 10 THEN

​ -> SELECT i, sum;

​ -> LEAVE sign;

-> ELSE

-> SET sum = sum + i;

-> SET i = i + 1;

-> END IF;

-> END LOOP sign;

-> END

​ -> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

REPEAT 语句列表 UNTIL 条件表达式 END REPEAT [标签]

程序会无条件执行一次REPEAT的语句列表。然后再判断UNTIL后的条件表达式是否为真，若为真，则结束循环；否则继续循环REPEAT的语句列表。

mysql> DELIMITER $$ //计算10以内的奇数

mysql> CREATE PROCEDURE proc_odd()

-> BEGIN

-> DECLARE i, sum INT DEFAULT 0;

-> REPEAT

-> IF i % 2 != 0 THEN

-> SET sum = sum + i;

-> END IF;

-> SET i = i + 1;

-> UNTIL i > 10 END REPEAT;

-> SELECT i, sum;

-> END

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

WHILE语句用于创建一个带条件判断的循环过程，与REPEAT不同的是，WHILE在语句执行时，需要满足条件表达式的要求，才会执行对应的语句列表

只要WHILE的条件表达式为真，就会重复执行DO后的语句列表。 若如无特殊需求，一定要在WHILE的语句列表中设置循环出口，避免死循环

mysql> DELIMITER $$ //计算1~10之间的偶数和

mysql> CREATE PROCEDURE proc_even()

-> BEGIN

-> DECLARE i, sum INT DEFAULT 0;

-> WHILE i <= 10 DO

-> IF i % 2 = 0

-> THEN SET sum = sum + i;

-> END IF;

-> SET i = i + 1;

-> END WHILE;

-> SELECT i, sum;

-> END

-> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

跳转语句：

跳转语句用于实现程序执行过程中的流程跳转。MySQL中常用的跳转语句有LEAVE和ITERATE语句。

{ITERATE | LEAVE} 标签名;

ITERATE语句用于结束本次循环的执行，开始下一轮循环的执行操作，重新开始循环。

LEAVE语句用于终止当前循环，跳出循环体。

ITERATE只能应用在循环结构LOOP、REPEAT和WHILE语句中，LEAVE除可以在循环结构中应用外，还可在BEGIN…END中使用。

19、游标

在数据库的管理过程中，通过前面学习的SELECT语句仅能返回符合指定条件的结果集，但是没有办法对结果集中的数据进行下一行的检索或每次一条记录一条记录的单独处理等。此时，就可以利用MySQL提供的游标机制进行处理。

游标的本质是一种能从SELECT结果集中每次提取一条记录的指针。

它主要用于交互式的应用程序，用户可以根据需要浏览或修改结果集中的数据

游标的操作流程

定义游标–>打开游标–>利用游标检索数据–>关闭游标

游标在使用之前，必须通过定义，让其与指定的SELECT语句相关联。

目的就是确定游标要操作的SELECT结果集对象。

DECLARE 游标名称 CURSOR FOR SELECT语句

游标名称必须唯一，因为在存储过程或函数中可以存在多个游标，而游标名称是唯一用于区分不同游标的标识。

游标在定义时必须在错误处理程序的语句之前，局部变量声明之后。

另外，SELECT语句中不能含有INTO关键字。

使用：

DECLARE…CURSOR FOR语句定义游标后，与游标相关联的SELECT语句并没有执行，此时MySQL服务器的内存中并没有SELECT语句的查询结果集。

游标定义完成后，要想使用游标，首先需要打开游标，使SELECT语句根据查询条件将数据存储到MySQL服务器的内存中。

OPEN 游标名称

打开游标后，就可用MySQL提供的FETCH语句检索SELECT结果集中的数据。

每访问一次FETCH语句就获取一行记录，获取数据后游标的内部指针就会向前移动指向下一条记录，保证了每次获取的数据都不同。

FETCH [[NEXT] FROM] 游标名称 INTO 变量名 [, 变量名] …

FETCH语句根据指定的游标名称将检索出来的数据存放到对应的变量中。

变量名的个数必须与声明游标时通过SELECT语句查询的结果集的字段个数保持一致。

要想用FETCH语句检索所有数据，需要利用循环语句实现。最常与游标一起使用的是REPEAT语句。

当利用FETCH语句从游标中检索出最后一条记录后，再次执行FETCH语句，将产生“ERROR 1329(02000):No data to FETCH”错误信息。

在使用游标时通常利用DECLARE…HANDLER处理该错误，从而结束游标的循环遍历。

游标检索完数据后，应该利用MySQL提供的语法关闭游标，释放游标占用的MySQL服务器的内存资源

CLOSE 游标名称

利用CLOSE关闭游标后，若再次需要利用游标检索数据时，仅需使用OPEN打开游标即可，不需要再次利用DECLARE…CURSOR FOR语句重新定义游标。

没有利用CLOSE关闭游标，也会在到达程序最后的END语句的地方自动关闭。

使用游标检索数据

假设在活动日，将库存不足400的5星评分商品的库存量增加到1500

利用shop数据库下的sh_goods表进行具体的操作。

mysql> DELIMITER $$

mysql> CREATE PROCEDURE sh_goods_proc_cursor()

​ -> BEGIN

​ -> DECLARE mark, cur_id, cur_num INT DEFAULT 0;

​ -> # 定义游标

​ -> DECLARE cur CURSOR FOR

​ -> SELECT id, stock FROM sh_goods WHERE score = 5;

​ -> # 自定义错误处理程序，结束游标的遍历

​ -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET mark = 1;

​ -> # 打开游标

​ -> OPEN cur;

20、事件

MySQL中的事件是由事件调度器Event Scheduler（特殊的线程）执行和管理的，默认情况下处于关闭状态。

若想要创建的事件能够正常的执行需要开启事件调度器，常用的查看和设置事件调度器的方式是利用全局变量event_scheduler实现的

查看：

show variables link‘event_scheduler’

虽然MySQL中的事件信息都保存到mysql.event表中，但是不建议直接对该表进行操作，避免出现不可预知的错误。

推荐直接使用MySQL提供的CREATE EVENT语句在指定的数据库下实现即可

操作：

CREATE EVENT [IF NOT EXISTS] 事件名称

ON SCHEDULE 时间与频率

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT '事件的注释']

DO 事件执行的任务主体

事件名：不区分大小写，且必须是一个最大长度不超过64个字符的有效标识符。

ON SCHEDULE 时间与频率：定义事件开始、结束、持续的时间，执行的频率。

ON COMPLETION：用于定义事件一旦过期是否被立即删除，默认值为“NOT PRESERVE”表示删除，设置为PRESERVE表示不删除。

ENABLE和DISABLE：可指定当前创建的事件是否可用，默认为ENABLE表示可用，DISABLE表示禁用。

COMMENT：用于为事件设置注释，方便阅读与维护。

DO：后设置事件发生时执行的SQL语句，当SQL语句为多条时，需要放在BEGIN…END中。

时间戳：

AT 时间戳 [+ INTERVAL 时间间隔 时间单位] …

时间戳必须包括日期和时间。

时间间隔可以是任意的数字。

通过“+ INTERVAL 时间间隔 时间单位”任意组合时间间隔。

事件中时间与频率的设置最常使用的就是执行定期的重复操作。

EVERY时间间隔 时间单位 [STARTS 时间戳 [+ INTERVAL时间间隔 时间单位] …] [ENDS 时间戳 [+ INTERVAL时间间隔 时间单位] …]

EVERY用于指定事件的执行频率。 STARTS指定事件何时开始重复。 ENDS指定事件何时结束重复。

查看该事件的相关信息：SHOW EVENTS

查看事件的创建语句信息：SHOW CREATE EVENT 事件名

修改：

修改事件的语法，除比创建事件语法多一个RENAME TO外，其他选项全部相同

ALTER EVENT event_name

[ON SCHEDULE 时间与频率]

[ON COMPLETION [NOT] PRESERVE]

[RENAME TO 新事件名称]

[ENABLE | DISABLE]

[COMMENT '事件的注释']

[DO 事件执行的任务主体]

删除：

DROP EVENT [IF EXISTS] 事件名称

当删除的事件正在执行时，也会立即停止执行，并从服务器中完成删除

21、预处理

传统方式的SQL语句，在执行时每条SQL都需要经过分析、编译和优化的步骤。

预处理方式则是利用客户端与服务器的二进制协议，预先编译一次客户端发送的SQL语句模板，然后再根据客户端发送给服务器相应数量的变量进行执行操作，并且针对一条SQL语句模板可以执行多次，还无需考虑数据中含有未转义的SQL引号和分隔符字符。

准备预处理语句———->执行预处理的语句—————–>在使用完预处理语句后对其进行释放

准备预处理语句

预处理的SQL语句：有一定限制的，原因在于不是所有的SQL都可以被编译。

举例1（不符合）：查看警告和错误的语句以及与警告和错误相关的系统变量等。

举例2（符合）：常用增、删、改、查的语句以及大部分的SHOW语句。

PREPARE 预处理语句名称 FROM 预处理的SQL语句

预处理语句名称：不区分大小写，用于标识预处理的SQL模板，在后面进行执行或释放预处理语句时使用；

预处理的SQL语句：是一个字符串或包含SQL语句的会话变量，在此SQL模板中“?”作为需动态改变的数据信息，但不能表示SQL关键字或标识符。

当预处理语句的名称已存在时，若再次创建同名的预处理语句，则系统会先释放原来的语句，然后再重新创建。

为防止同时创建太多的预处理语句，可以通过max_prepared_stmt_count系统变量限定预处理的最多数量。

PREPARE语句可以在存储过程中使用，但是不能在自定义函数或触发器中使用。

执行预处理语句

EXECUTE 预处理语句名称 [USING @变量名 [, @变量名] …]

先定义变量存贮数据（set @变量=值），然后在SQL模板中按从左到右的顺序将其绑定到符号“?”占用的位置，最后执行此SQL语句。

释放预处理语句

{DEALLOCATE | DROP} PREPARE 预处理语句名称

预处理SQL语句属于会话级别的操作，因此它仅适用于创建预处理语句的当前用户会话，不适用于其他会话。同时在会话结束后，即使不执行DEALLOCATE或DROP语句，创建的预处理语句也会被自动释放。

22、函数

指的是一段用于完成特定功能的代码。 使用函数时，只需关心函数的参数和返回值，就可以完成一个特定的功能。 MySQL中的函数分类：内置函数，自定义函数。 内置函数：也称为系统函数，MySQL提供的函数，无需定义、直接使用即可。 从功能方面划分：大致可以分为数学函数、数据类型转换函数、字符串函数、日期和时间函数、加密函数、系统信息函数、JSON函数以及其他常用函数。

多行处理函数:

多个输入对应一个输出（分组函数）：分组函数必须分组，自动忽略null，不能直接使用在where子句中，可以一起组合使用 count： 计数 count（*）：统计总行数 sum（字段）： 求和 avg： 平均值 max： 最大值 min： 最小值

单行处理函数:

一个输入对应一个输出 select 函数（字段） form 表名； 常用： substr（字段，起始下标（从1开始），截取长度）： 取字串 concat（字段，字段）： 字符串拼接 length： 取长度 trim： 去空格 format（字段，$999.999）: 转化千分位 str_to_date（‘字符串日期’，‘日期格式’）: 将字符串转换成日期，常用于插入 mysql日期格式：%Y%m%d%h%i%s：年月日时分秒 date_format（字段，‘想要的格式’）: 格式化日期，设置展示的日期格式，常用于查询 now（）： 获取系统时间，常用于插入，带有时分秒信息 round（字段，保留）： 四舍五入 rand（）： 生成随机数 ifnull（数据，被当做那个值）： 空处理函数，将null转换成一个具体值 (case–when-than-when-than–else–end)： 条件选择

运算函数：

• abs（x）： 绝对值 sign（x）： 返回符号（正数1，负数-1,0返回0） div（x，y）： 除法（保留整数） mod（x，y）： 求模 sqrt（x）： 平方根 pow（x，y）： 幂运算

• rand（）： 随机数 rand（x）： 种子随机值（伪随机）

• ceil（x）： 向上取整 floor（x）： 向下取整

• //format（x，y）： 保留x的y位小数（四舍五入） round（x）： 四舍五入求整（可加y参数，与format相同） round（x，y）： 四舍五入求整保留y为小数 truncate（x，y）： 保留x的y位小数（截断）

• floot（x+rand（）*（y-x））：得到一个最小x，最大y的随机整数

比较运算的函数

IN（） 比较一个值是否在一组给定的集合内
NOT IN ()比较一个值是否不在一组给定的集合内

GREATEST（）返回最大的一个参数值，至少两个参数
LEAST（）返回最小的一个参数值，至少两个参数

ISNULL（）测试参数是否为空
COALESCE（）返回第一个非空参数
INTERVAL （）返回小于第一个参数的参数索引
STRCMP（）比较两个字符串

三角函数：

PI()计算圆周率

RADIANS(x)用于将角度x转换为弧度
DEGREES(x)用于将弧度x转换为角度

SIN(x)正弦函数
COS(x)余弦函数
TAN(x)正切函数
COT(x)余切函数
ASIN(x)反正弦函数
ACOS(x)反余弦函数
ATAN(x)反正切函数

指数和对数：

sqrt(x)求x的平方根
pow(x,y)或power(x,y)幂运算函数（计算x的y次方）

EXP(x)计算e（自然对数的底约为2.71828）的x次方
LOG(x)计算x的自然对数，等同ln函数
log2（x）返回以2为底的x的对数
LOG10(x)计算以10为底的对数

进制转换函数：

BIN(x)返回x的二进制数
OCT(x)返回x的八进制数
HEX(x)返回x的十六进制数
CHAR (c1,c2,c3,…)将c1、c2、c3、…的ASCII码转换为字符，然后返回这些字符组成的字符串
CONV(x,code1,code2)将code1进制的x变为code2进制数

类型转换函数：

CONVERT(x,type)以type类型返回x
CONVERT(x USING字符集)以指定字符集返回x数据
CAST(x AS type)以type类型返回x
UNHEX(x)将x转为十六进制数字，然后再转为由数字表示的字符

字符串函数：

ascll(c)返回字符c的ASCII码（ASCII码介于0～255）
char_length()获取字符串的长度
length()获取字符串占用的字节数

concat(s……)将参数连接成一个新字符串
concat_ws(x,s……)使用指定分隔符将参数连接成一个新字符串

inster（str,idx,len,repla）从str的idx(以一开始)开始使用repla替换len长度的字符串
replace(str,a,b)使用b替换str中出现的所有a

upper()将字符串全部转为大写字母，与UCASE()函数等价
lower()将字符串全部转为小写字母，与LCASE()函数等价

left(str,n)截取并返回字符串的左侧指定个字符
right(str,n)截取并返回字符串的右侧指定个字符

lpad(str,len,pad)按照len从左到右截取字符串，当str的长度小于len时在左侧填充pad
rpad(str,len,pad)按照len从左到右截取字符串，当str的长度小于len时在右侧填充pad

repeat(str,n)重复指定次数的字符串
space()重复指定次数的空格

ltrim()删除字符串左侧的空格
rtrim()删除字符串右侧的空格
trim()删除字符串左右两侧的空格

trim(s1 FROM s)去掉s开始与结尾的s1
trim(LEADING s1 FROM s)去掉s开始的s1
trim(TRAILING s1 FROM s)去掉s结尾的s1

strcmp()比较两个字符串的大小
reverse()颠倒字符串的顺序

substr(s,idx,len)从s的idx开始获取len长度的字符串。与substring和MID()函数等价
instr(str,substr)返回子串在一个字符串中第一次出现的位置。与LOCATE()和POSITION(…IN…)函数等价，但参数顺序不同

elt（m,s……）返回指定位置字符串（m=1，返回s1，以此类推）
field（s……）返回s第一次出现的位置
FIND_IN_SET(s1，s2)获取s1在s2（含有英文逗号分割的字符串）中的开始位置

日期函数：

获取时间日期：
curdate当前日期
curtime当前时间
now当前日期时间

utc_dateutc日期
utc_timeutc时间

日期时间戳转换
unix_timestamp以unix时间戳形式返回当前时间
unix_timestamp(date)以unix时间戳形式返回date

from_unixtime(timestamp)将时间戳转换为时间

日期转换函数
extract（type from date）返回指定格式日期

获取季月周天
year(date) / month / day返回具体的年月日
hour(time)/ minute / second返回具体的时分秒

monthname(date)返回月份: January,…
dayname(date)返回星期几:MONDAY，TUESDAY……UNDAY
weekday(date)返回周几，注意，周1是o，周2是1，。。。周日是6
quarter(date)返回日期对应的季度，范围为1～4
week(date)返回一年中的第几周
dayofyear(date)返回日期是一年中的第几天
dayofmonth(date)返回日期位于所在月份的第几天
dayofweek(date)返回周几，注意:周日是1，周一是2，。。。周六是7

日期计算函数

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距离o000年1月1日的天数

last_day(date)返回date所在月份的最后一天的日期

makedate(year,n)针对给定年份与所在年份中的天数返回一个日期
maketime(h,m,s)将给定的小时、分钟和秒组合成时间并返回
period_add(time,n)返回time加上n后的时间

格式化与解析函数

date_format(date,fmt)格式化日期
time_format(time,fmt)格式化时间

get_format(date_type,fmt_type)返回格式
str_to_date(str,fmt)按照字符串fmt对str进行解析

流程控制函数：

if（条件，v1，v2）如果条件为真，返回v1，否则返回v2
ifnull（v1，v2）如果v1不为null，返回v1，否则返回v2

case when 条件 then 结果 else 结果end if……else
case expr when 常量 then ……switch……case

加密函数：

#主要用于对数据进行加密。相对明文存储，经过算法计算后的字符串不会被人直接看出保存的是什么数据，在一定程度上能够保证数据的安全性。
password(str)返回str加密版本，41位长字符串（不可逆）
MD5()使用MD5计算并返回一个32位的字符串
sha（str）加密算法（同上：参数为null，返回null）

AES_ENCRYPT()使用密钥对字符串进行加密，默认返回一个128位的二进制数
AES_DECRYPT()使用密钥对密码进行解密
encode（v,pass）返回使用pass作为加密密码的加密v
decode（v,pass）返回使用pass作为加密密码的解密v

散列函数：
又称为哈希（Hash）函数，用于通过散列算法计算数据的散列值。
SHA1()或SHA()利用安全散列算法SHA-1字符串，返回40个十六进制数字组成的字符串
SHA2()利用安全散列算法SHA-2字符串

系统信息函数：

VERSION()用于获取当前MySQL服务实例使用的MySQL版本号
DATABASE()用于获取当前操作的数据库，与SCHEMA()函数等价
USER()用于获取登录服务器的主机地址及用户名，与SYSTEM_USER()和SESSION_USER()函数等价
CURRENT_USER()用于获取该账户名允许通过哪些登录主机连接MySQL服务器
CONNECTION_ID()用于获取当前MySQL服务器的连接ID
BENCHMARK()重复执行一个表达式
LAST_INSERT_ID()获取当前会话中最后一个插入的AUTO_INCREMENT列的值

JSON函数：

JSON_ARRAY()创建JSON数组
JSON_OBJECT()创建JSON对象
JSON_CONTAINS()JSON文档中是否包含路径中指定对象
JSON_CONTAINS_PATH()JSON文档中是否包含路径中的任意数据
JSON_EXTRACT()从JSON文档返回数据
JSON_KEYS()从JSON文档中获取数组中的键
JSON_SEARCH()获取JSON文档中值的路径
JSON_ARRAY_APPEND()将数据追加到JSON文档的指定路径中
JSON_ARRAY_INSERT()将数据插入到JSON数组指定路径前
JSON_DEPTH()获取JSON文档的最大深度
JSON_INSERT()将数据插入JSON文档
JSON_LENGTH()获取JSON文档中元素的数量
JSON_MERGE_PATCH()合并JSON文档，替换重复键的值
JSON_MERGE_PRESERVE()合并JSON文档，保留重复的键
JSON_PRETTY()以友好的格式打印JSON文档
JSON_REMOVE()从JSON文档中删除数据
JSON_REPLACE()替换JSON文档中的值
JSON_SET()向JSON文档中插入数据
JSON_TYPE()获取JSON值的类型
JSON_VALID()JSON值是否有效

用户自定义函数

是由多条语句组成的语句块，每条语句都是一个符合语句定义规范的个体，需要语句结束符——分号（;）。

MySQL一旦遇见语句结束符就会自动开始执行，但函数是一个整体，只有在被调用时才会被执行，那么在定义函数时就需要临时修改语句结束符。 定义：

DELIMITER 新结束符号
自定义函数
新结束符号
DELIMITER ;

自定义的新结束符号推荐使用系统非内置的符号，如$$。 DELIMITER修改语句结束符后，自定义函数中就可正常使用分号结束符，系统由于不再将分号作为语句结束符，从而不会自动执行自定义函数的SQL语句 完成函数的自定义后，首先需要使用新结束符号进行结束，然后需使用DELIMITER将语句结束符改回原来的分号（;）。

CREATE FUNCTION 函数名([参数名 数据类型, …]) RETURNS 返回值类型
[BEGIN]

函数体

RETURN 返回值数据; # 数据必须与结构中定义的返回值类型一致
[END]
mysql> DELIMITER $$
mysql> CREATE FUNCTION sayHello(name VARCHAR(30)) RETURNS VARCHAR(50)
-> BEGIN
-> RETURN CONCAT('Hello ', name, '!');
-> END
-> $$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

查看函数的创建语句： show

查看函数状态及系统中所有自定义函数： SHOW FUNCTION STATUS

调用函数：SELECT 函数名1(实参列表), 函数名2(实参列表), …;

删除函数：DROP FUNCTION [IF EXISTS] 函数名;