Mysql

键值型数据库：键值型数据库通过 Key-Value 键值的方式来存储数据，Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，缺点是无法像关系型数据库一样使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。典型场景是作为内存缓存。 Redis 是最流行的键值型数据库。

文档型数据库：此类数据库可存放并获取文档，可以是XML、JSON等格式。在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录。文档数据库所存放的文档，就相当于键值数据库所存放的“值”。MongoDB是最流行的文档型数据库。此外，还有CouchDB等。

搜索引擎数据库：虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎数据库是应用在搜索引擎领域的数据存储形式，由于搜索引擎会爬取大量的数据，并以特定的格式进行存储，这样在检索的时候才能保证性能最优。核心原理是“倒排索引”。典型产品：Solr、Elasticsearch、Splunk 等。

图形数据库：就是一种存储图形关系的数据库。它利用了图这种数据结构存储了实体（对象）之间的关系。关系型数据用于存储明确关系的数据。如社交网络中人物之间的关系，如果用关系型数据库则非常复杂，用图形数据库将非常简单。典型产品：Neo4J、InfoGrid等。

1.5 总结

NoSQL 的分类很多，即便如此，在 DBMS 排名中，还是 SQL 阵营的比重更大，影响力前 5 的 DBMS 中有4 个是关系型数据库，而排名前 20 的 DBMS 中也有 12 个是关系型数据库。

排名网址：https://db-engines.com/en/ranking_trend

第二章：MYSQL基础

第一节：MYSQL安装

1.1 MYSQL版本

MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。

MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。

MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个Server。需要在社区版或企业版的基础上使用。

MySQL Cluster CGE 高级集群版，需付费。

1.2 MYSQL下载地址

官网：https://www.mysql.com，打开官网点击DOWNLOADS然后，点击 MySQL Community(GPL) Downloads。

下载图1

下载图2

下载MSI的文件

小的文件为在线方式需要链接互联网，下载第二个即可

1.3 MYSQL 安装

步骤1：Custom 自定义的方式

步骤2：选择8.0

步骤3：单击选中，点击Advanced Options

步骤4：第一个软件安装目录，数据库文件目录（data目录隐藏让显示出来）

步骤5:点击Execute安装

步骤6:安装完成，下一步为软件配置

步骤7:开发者的电脑占用比较少的内存资源

步骤8:设置密码，为root用户的密码

步骤9:MYSQL服务的名称（快捷键：services.msc）

步骤10:添加一个版本

1.4 环境变量配置

步骤1：右键我的电脑->属性->高级系统设置

步骤2：找到Path路径

步骤3：新建一个Path路径（C:\Program Files\MySQL\MySQL Server 8.0\bin\）

1.5 MYSQL的卸载

步骤1：停止MySQL服务(服务快捷键：services.msc)。

步骤2：卸载MySQL8.0的程序可以和其他桌面应用程序一样直接在“控制面板”选择“卸

载程序”，并在程序列表中找到MySQL8.0服务器程序，直接双击卸载即可。

步骤3：如果再次安装不成功，可以卸载后对残余文件进行清理后再安装。

（2）数据目录：默认在C:\ProgramData\MySQL

如果自己单独指定过数据目录，就找到自己的数据目录进行删除即可

步骤4：找到path环境变量，将其中关于mysql的环境变量删除，切记不要全部删除。

例如：删除 D:\develop_tools\mysql\MySQLServer8.0.26\bin; 这个部分

第二节：数据库创建

2.1 MYSQL服务的启动与停止

方式1：打开windows服务（快捷键：services.msc）

步骤1：计算机（点击鼠标右键）→ 管理（点击）→ 服务和应用程序（点击）→ 服务

步骤2：找到MySQL80（点击鼠标右键）→ 启动或停止（点击）

方式2：命令行的方式

启动： net start MySQL服务名，停止 MySQL： net stop MySQL服务名

2.2 登录MYSQL

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

举例：mysql -h localhost -P 3306 -uroot -pa123456,保证安全密码可以下一行输入。

注意：

1）-p与密码之间不能有空格，其他参数名与参数值之间可以有空格也可以没有空格。

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

2.3 MYSQL 版本

方式1：mysql --version

方式2：登录后输入命令：select version();

2.4 退出MYSQL

quit 或者 exit

2.5 查询数据库

语法：show databases;

“information_schema”是MySQL系统自带的数据库，主要保存MySQL数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等

“performance_schema”是MySQL系统自带的数据库，可以用来监控MySQL的各类性能指标。

“sys”数据库是MySQL系统自带的数据库，主要作用是以一种更容易被理解的方式展示MySQL数据库服务器的各类性能指标，帮助系统管理员和开发人员监控MySQL的技术性能。

“mysql”数据库保存了MySQL数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等

2.6 创建数据库

语法：create database数据库名;

2.7 使用数据库

语法：use 数据库名;

说明：如果没有使用use语句，后面针对数据库的操作也没有加“数据名”的限定，那么会报“ERROR1046(3D000):Nodatabaseselected”（没有选择数据库）

使用完use语句之后，如果接下来的SQL都是针对一个数据库操作的，如果要针对另一个数据库操作，那么要重新use。

显示当前的数据库：select database();

2.8 数据库编码

语法：create database if not exists 数据库名character set utf8;

查看编码：show variables like 'character%';

修改编码：alter database 数据库名 character set latin1;

2.9 删除数据库

语法：drop database 数据库名;

第三节：数据表语法

3.1 创建表格

语法：create table表名称(

字段名数据类型,

字段名数据类型

);

举例：

create table student(

id int,

name varchar(20) //名字不超过20个字符

)

3.2 查看某个库的表格

语法：show tables from 数据库名;

3.3 查看表的创建信息

语法：show create table表名称\G

3.4 查询数据表的结构

语法：show columns from 数据表;desc reader;

3.5 MYSQL数据类型

Char 20字节长度就是20个字节男 3

Varchar 255 写多少字节他占多少字节个性签名

3.6 数据表操作

3.6.1修改列名

语法：alter table 表名 change 列名新列名数据类型;

3.6.2修改列的数据类型

语法： alter table表名 modify 列名数据类型;

3.6.3添加列

语法：alter table 表名 add 列名 数据类型 after 列

3.6.4修改列的排列位置

语法：alter table 表名 modify 列名 数据类型 FIRST|AFTER 列名;

3.6.5删除列

alter table 表名 drop 列名;

3.6.6修改表名

alter table 表名 rename to 新表名;

3.6.7删除数据表

语法：drop table 表名;

从数据库中删除表。删除后，表结构不在存在，无法再对该表进行任何操作

3.7 数据管理

3.7.1 查询数据

select * from 数据库表名称;

3.7.2 插入数据

语法：insert into 表名称 values(值列表);

3.7.3 删除数据

语法：delete from 表名；删除表的数据，表仍然存在，仍可以对表进行操作

3.8 修改数据库默认编码

查看编码：

show variables like 'character_%';

show variables like 'collation_%';

数据库5.7版本以前默认编码为：latin1 插入中文会出现错误，修改mysql的数据目录下的my.ini配置文件

default-character-set=utf8 #默认字符集 [mysqld] # 大概在76行左右，在其下添加

character-set-server=utf8

collation-server=utf8_general_ci

-- 修改表字符集

ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 修改列的字符集

ALTER TABLE  表名 CHANGE    列名  VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

第三章：SQL语句

第一节：SQL语句分类

SQL语言在功能上主要分为如下3大类：

DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。

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

DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。SELECT是SQL语言的基础，最为重要。

DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

第二节：SQL语句规范

- SQL 可以写在一行或者多行。为了提高可读性，各子句分行写

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

- 必须保证所有的()、单引号、双引号是成对结束的

- MySQL 在 Windows 环境下是大小写不敏感的

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

- 推荐写法：数据库名、表名、表别名、字段名、字段别名等都小写，SQL 关键字、函数名、绑定变量等都大写。

- 单行注释：#注释文字(MySQL特有的方式)

单行注释：-- 注释文字(--后面必须包含一个空格。)

多行注释：/* 注释文字 */

第三节：管理工具 Navicat for MySQL

错误原因：mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 把mysql用户登录密码加密规则还原成mysql_native_password.

方法如下：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

#修改加密规则

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

#更新一下用户的密码

FLUSH PRIVILEGES; #刷新权限

单独重置密码命令：alter user 'root'@'localhost' identified by '111111';

Mysql忘记密码：https://www.cnblogs.com/rmxd/p/11236736.html

第四节：查询语句

4.1 SELECT...

没有任何的子句；举例：select 10;select 5+5;

4.2 SELECT ... FROM

语法：SELECT 列 FROM 表名;

-- select * from demo1; #选择demo1表中的所有的列

-- select id from demo1; #选择 demo1中id的列

4.3 SELECT列别名

语法：select 列名 as 别名,列名别名 from stu

4.4 SELEC去除重复行

语法：SELECT DISTINCT 列名 FROM 表名;

4.5 where过滤子句

语法：SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件

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

举例：select * from stu where name=’张三’;

第五节：运算符

5.1:算术运算符

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

加减法规则：

# 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；

# 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；

# 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；

# 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

乘除法规则：

# 一个数乘以整数1和除以整数1后仍得原数；

# 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；

# 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；

# 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；

# 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。

# 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。

5.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。(比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。)

5.2.1 等号运算符（=）

等号运算符（=）判断等号两边的值、字符串或表达式是否相等;

# 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。

# 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。

#如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。

# 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。

# 对比：SQL中赋值符号使用 :=时，其返回值为0，而不为NULL。

5.2.2 不等于运算符

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

注意：所有运算符或列值遇到null值，运算的结果都为null这里你一定要注意，在 MySQL 里面，空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

举例：

SELECT id*11 from stu;//可以计算

SELECT name*11 from stu;#空值相乘0，null结果为null

5.2.3 空运算符

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

查询语句：SELECT * FROM stu WHERE name IS NULL;

5.2.4 非空运算符

非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返

回0。

查询语句：SELECT * FROM stu WHERE name IS NOT NULL;

5.2.5 最小值运算符

语法：LEAST(值1，值2，...，值n)。其中，“值n”表示参数列表中有n个值。在有

两个或多个参数的情况下，返回最小值。

SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2); #返回 0|a|null

当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字

母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

5.2.6 最大值运算符

语法格式为：greatest(值1，值2，...，值n)。其中，n表示参数列表中有n个值。当有

两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL。

SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2); #返回 2 | c | NULL

5.2.7 BETWEEN AND运算符

语法： select * from 表名 where C between A and B;

当C大于或等于A，并且C小于或等于B时，结果为1，否则结果为0。

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

# 返回 1|0|1

5.2.8 IN运算符

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

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

#返回 1 | 0 | NULL | 1

查询语句：select * from 表名 where id in(1,10,11);

5.2.9 NOT IN运算符

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

个值，则返回1，否则返回0。

语法：SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);#返回 0|1

5.2.10 LIKE运算符

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

通配符使用：“%”：匹配0个或多个字符。 “_”：只能匹配一个字符。

select * from info where name like '%三%';# 包含三的字符

select * from info where name like '_三';#匹配一个字符

5.2.11 ESCAPE转义符

回避特殊符号的：使用转义符。如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。

Select * from stu where name like ‘IT\_%’;

Select * from stu where name like ‘IT_%’ ESCAPE '_';

5.2.12 REGEXP运算符

‘^’匹配以该字符后面的字符开头的字符串。
‘$’匹配以该字符前面的字符结尾的字符串。
‘.’匹配任何一个单字符。
“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

（5）‘*’匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的‘x’字符，“[0-9]*”匹配任何数量的数字，而“*”匹配任何数量的任何字符。

5.3 逻辑运算符

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

5.3.1 逻辑非运算符

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

当给定的值为NULL时，返回NULL。

SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL;#返回0|1|0|1|null

查询语句：where id not in(1,2,3) #id 不是，1,2,3；

5.3.2 逻辑与运算符

逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回

1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。

SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL; #返回1|0|0|null

查询语句：where id=1 and name=’张三’;#id为1并且name等于张三

5.3.3 逻辑或运算符

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

回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。

语法：SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL;#返回1|1|1|NULL|NULL

查询语句：where id=1 or name=’张三’;#查询id=1的或者name等于张三。

注意：AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。

5.3.4 逻辑异或运算符

逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果

两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。

语法： 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

5.4 位运算符

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

5.4.1 按位与运算符

按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二

进制位的数值都为1时，则该位返回1，否则返回0。

举例：SELECT 1 & 10, 20 & 30;

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

5.4.2 按位或运算符

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

语法：SELECT 1 | 10, 20 | 30;

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

5.4.3 按位异或

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

5.4.4 按位取反

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

5.5 运算符的优先级

第六节：排序子句

6.1 排序规则

语法：ORDER BY 子句在SELECT语句的结尾。规则：ASC（ascend）: 升序，DESC（descend）:降序。

6.2 单列排序

语法：select * from 表名 order by id desc # desc 为降序排列

6.3 多列排序

语法：select * from 表名 order by id asc,name desc;

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

第七节：分页子句

问题：返回记录多的时候如何分页？只需其中的某一条或者某几条怎么办？

语法：LIMIT [位置偏移量,] 行数；(放入select 语句后)

举例：

Select * from 表名 limit 0,10 # 去前10行

Select * from 表名 limit 0,10 # 11到20行

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

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

第四章：常用函数

第一节：字符函数

ASCII(S) 返回字符串S中的第一个字符的ASCII码值。

CHAR_LENGTH(s) 返回字符串s的字符数。同CHARACTER_LENGTH(s)。

LENGTH(s) 返回字符串s的字节数，和字符集有关 utf8一个汉字三个字节

CONCAT(s1,s2,......,sn) 连接s1,s2,......,sn为一个字符串

CONCAT_WS(x, s1,s2,......,sn) 链接字符串，在每个字符串之间要加上x

INSERT(str, idx, len, replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr

REPLACE(str, a, b) 字符串str中出现a的字符串替换为b

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开始与结尾的空格

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中首次出现的位置

REVERSE(s) 返回s反转后的字符串

NULLIF(value1,value2) 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

第二节：数学函数

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的随机值，包括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

第三节：日期函数

CURDATE()，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME()，CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW(),SYSDATE(),CURRENT_TIMESTAMP() , LOCALTIME() , LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间
YEAR(date) / MONTH(date) / DAY(date)	返回具体的年月日
HOUR(time)/MINUTE(time)/ SECOND(time)	返回具体的时间值
MONTHNAME	返回月份名称：January
WEEKDAY(date)	返回周几，注意，周1是0，。。。周日是6
DAYNAME(date)	返回星期几：MONDAY，TUESDAY...SUNDAY
QUARTER(date)	返回日期对应的季度，范围为1～4
WEEK(date) , WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，。。。周六是7
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值
EXTRACT(type FROM date)函数中type的取值如下：

3.1 时间和秒钟转换的函数

TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。公式为：小时*3600+分钟 *60+秒

SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间

3.2 计算日期函数

DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)

函数向日期添加指定的时间间隔。

DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)

返回与date相差INTERVAL时间间隔的日期

上述间隔类型：

举例：

ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,

DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,

DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数

DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号

DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,

SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,

DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3

3.3 计算时间的函数

ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数举例：SELECT DATEDIFF(CURDATE(),'2022-02-17')
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后的时间

3.4 日期的格式化与解析

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进行解析，解析为一个日期

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小时制	%h	两位数字表示小时，12小时制（01,02..）
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%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
%%	表示%

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

举例：

SELECT DATE_FORMAT(NOW(), '%H:%i:%s');



SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y') FROM DUAL;

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') FROM DUAL;

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s') FROM DUAL;



SELECT GET_FORMAT(DATE, 'USA');

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')), FROM DUAL;

第四节：转换函数

4.1 if函数

- IF(value,value1,value2)  如果value的值为TRUE，返回value1，否则返回value2

4.2 IFNULL函数

- IFNULL(value1, value2)  如果value1不为NULL，返回value1，否则返回value2

4.3 CASE WHEN函数

- CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END



SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END

#结果->1 > 0 相当于 if elseif else

第五节：聚合函数

概念：聚合函数作用于一组数据，并对一组数据返回一个值。

5.1 AVG和SUM函数

avg：求一组数据的平均数，sum：求一组数据的和。

举例：

SELECT AVG(id) avg,SUM(id) sum from info

5.2 MIN和MAX函数

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

MIN :求一组数据的最小值 MAX：求一组数据的最大值。

举例：

SELECT MIN(id) min,MAX(id) max from info

5.3 COUNT函数

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

举例：

SELECT COUNT(*) count from info



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

举例：

SELECT COUNT(name) count from info

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

5.4 GROUP BY分组

可以使用GROUP BY子句将表中的数据分成若干组，WHERE放在FROM后面。

举例：

SELECT count(*) count from info GROUP BY name

#把名字相同的分为一组，并统计个数。

多列排序

GROUP BY X,Y ;

意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里.

举例：

SELECT count(*) count from info GROUP BY name,nickname。

5.5 WITH ROLLUP

可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

语法：

SELECT name,sum(统计的列名) from info GROUP BY name WITH ROLLUP

更改空列名：

SELECT IFNULL(name,'平均数'),avg(统计的列名) from info GROUP BY name with ROLLUP

5.6 HAVING子句

HAVING 子句可以让我们筛选分组后的各组数据。满足条件：使用聚合函数，HAVING不能单独使用，必须要跟GROUPBY一起使用。

SELECT department_id,MAX(salary) FROM  employees GROUPBY department_id

HAVING MAX(salary)>10000;

5.7 WHERE和HAVING的区别

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

区别2：如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选，WHERE更高效，而HAVING执行效率更低

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

第六节：Select执行顺序

6.1 关键字执行顺序

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

6.2 SELECT语句顺序

FROM...WHERE...GROUP BY...HAVING...SELECT...字段...DISTINCT...ORDER BY...LIMIT

举例：

SELECT DISTINCT id,count(*) as num  #顺序5

FROM 表         #顺序1

WHERE height>1.80   #顺序2

GROUP BY id         #顺序3

HAVING num>2       #顺序4



ORDER BY num DESC   #顺序6 

LIMIT 2              #顺序7

第五章：多表查询

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

第一节：笛卡尔积

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

比如：a表中2两条数据，b表中两条数据，组合起来就是4条数据。

第二节：多表查询分类

2.1 等值连接

为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件。比如student表中stu_id(主键)，student_info表中有stu_id(外键)，语法如下：

Select  * from student,student_info where student.stu_id=student_info.stu_id

规则：

-- 多个条件用and去链接；

-- 区分重复的列名，可在列表前面加上表名；

-- 表可以加别名可以简化查询，如：from student a,student_info b where .....；

2.2 非等值连接

表与表之间没有相同项（或有相同但是不能用），但是表之间某个或某些项直接存在着一定的关系。

比如：学生分数等级表格式(grade)：

等级(gra)	最低分(lowest)	最高分(highest)
A	90	100
B	70	89
C	60	69
D	0	59

语句：

Select  b.gra gra,a.name name from stu a,gra b where a.id=b.id and a.score between b.lowest and a.highest;

2.3 自连接

mysql有时在信息查询时需要进行对自身连接（自连接），所以我们需要为表定义别名。

比如：需要查询表中大于“张三的”分数；

步骤一：select  score  from stu where name=’张三’; #返回10

步骤二：select  * from where score>60;

内连接的方式：

Select b.* from stu a,stu b where a.name=’张三’ and a.score<b.score

2.4 内连接

关键词：inner join

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

语法：

select * from info a inner join grade b on a.id=b.id;

2.4 外链接

2.4.1 左外链接 left join

全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

语法：

select * from info a left join grade b on a.id=b.id;

2.4.2 右连接 right join

全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

语法：

select * from info a right join grade b on a.id=b.id;

第六章：子查询

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

第一节：单行子查询

实例：

select * from info where score > (select score from info where name=’张三’)；

规则：

--- 子查询（内查询）在主查询之前一次执行完成。

--- 子查询的结果被主查询（外查询）使用。

--- 子查询要包含在括号内。

--- 常用操作符：= | > |>= | < | <= | <>

第二节：多行子查询

多行子查询，是指执行查询语句获得的结果集中返回了多行数据的子查询。在子查询中可以使用in关键字、exists关键字和比较运算符来连接表。

2.1 in关键字

in关键字用来判断一个表中指定列的值是否包含在已定义的列表中，或在另一个表中

语法：select * from 表名称 where 列名称 in(select * from 表名称);

2.2 比较运算符

2.2.1 All 运算符

它将单个值与子查询返回的单列值集进行比较。

ALL运算符必须以比较运算符开头，例如：>，>=，<，<=，<>，=，后跟子查询。

语法：select * from 表名称 where 列名称 > all (select score from 表名称);

#大于子查询集合中score 最大的分数

2.2.2 ANY运算符

ANY运算符是一个逻辑运算符，它将值与子查询返回的一组值进行比较。 ANY运算符必须以比较运算符：>，>=，<，<=，=，<>开头，后跟子查询。

2.3.3 EXISTS 和 NOT EXISTS关键字

将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。

-- exist适合子查询中表数据大于外查询表中数据的业务场景（in相反）

-- 先执行一次外部查询，遍历外部查询的每一次结果集时，各执行一次子查询。

第七章：数据处理

第一节：插入数据

1. VALUES的方式添加

使用这种语法一次只能向表中插入一条数据。

-- INSERT INTO 表名 VALUES (value1,value2,....);#值得顺序必须与表中的值顺序相同

-- INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

#为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

1. 插入多条数据

INSERT INTO table_name VALUES (value1 [,value2, …, valuen]), (value1 [,value2, …, valuen]), ……(value1 [,value2, …, valuen]);

 INSERT INTO table_name(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]), (value1 [,value2, …, valuen]), ……(value1 [,value2, …, valuen]);

总结：一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。

1. 插入select语句

INSERT INTO 目标表名 (tar_column1 [, tar_column2, …, tar_columnn]) SELECT (src_column1 [, src_column2, …, src_columnn]) FROM 源表名 [WHERE condition]

第二节：更新数据

update更新数据

语法：UPDATE 表名 SET 字段1=值, 字段2=值[WHERE 条件]；

举例：

update info set name=’张三’;#所有表中的name都改为张三。

Where：update info set name=’张三’ where id=3;#id为3的名字张三。

第三节：删除数据

语法：

DELETE FROM table_name [WHERE <condition>];

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

语法2：

truncate table table_name；

Truncate 与delete相似都是删除数据，删除部分数据用delete,清空表用truncate。

truncate和delete的区别

1、事务：truncate是不可以rollback的，但是delete是可以rollback的；

原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback

2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引

3、 truncate 不能触发任何Delete触发器。

4、delete 删除可以返回行数

第四节：约束

4.1 为什么要使用约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

--实体完整性：例如，同一个表中，不能存在两条完全相同无法区分的记录

--域完整性：例如：年龄范围0-120，性别范围“男/女”

--引用完整性：例如：员工所在部门，在部门表中要能找到这个部门

--用户自定义完整性：例如：用户名唯一、密码不能为空等

-- SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

#查询表中所有的约束。

4.2 非空约束

限定某个字段/某列的值不允许为空，关键词：NOT NULL。

语法：CREATE TABLE 表名称(

字段名数据类型,

字段名数据类型 NOT NULL,

字段名数据类型 NOT NULL

);

alter table 表名称 modify 字段名数据类型 not null;#建表后

alter table 表名称 modify 字段名数据类型 NULL;#删除非空约束

注意：空字符串''不等于NULL，0也不等于NULL。

4.3 唯一性约束

用来限制某个字段/某列的值不能重复。关键字：UNIQUE。

-- 同一个表可以有多个唯一约束。

-- 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。

-- 唯一性约束允许列值为空。

-- 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

-- MySQL会给唯一约束的列上默认创建一个唯一索引。

建表时：

create table 表名称(

字段名数据类型,

字段名数据类型 unique,

字段名数据类型 unique key,

字段名数据类型

);

create table 表名称(

字段名数据类型,

[constraint 约束名] unique key(字段名)

建表后：

#方式1：alter table 表名称 add unique key(字段列表);

#方式2：alter table 表名称 modify 字段名字段类型 unique;

4.4 复合唯一约束

create table 表名称(

字段名数据类型,

unique key(字段列表)

#字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复合唯一，即多个字段的组合是唯一的

);

4.5 删除唯一约束

-- 删除唯一约束只能通过删除唯一索引的方式删除。

-- 如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

-- show index from 表名称; 查看表的索引

 ALTER TABLE USER DROP INDEX uk_name_pwd;

4.6 主键约束

关键词：primary key，用来唯一标识表中的一行记录。（主键约束相当于唯一约束+非空约束不能重复也不可以为空）

添加主键：

create table temp(

id int primary key, #primary key 主键

name varchar(20)

);

复合主键举例：

create table student(

sid int,

 cid int,

 score int,

 primary key(sid,cid) #复合主键 ，可以更多的字段

);

增加主键约束：ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

#字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

删除主键约束：alter table 表名称 drop primary key;

#不需要指定主键名称，一个表只有一个主键

4.7 自增列

关键词:AUTO_INCREMENT。

-- 一个表最多只能有一个自增长列

-- 自增长列约束的列必须是键列（主键列，唯一键列）

-- 自增约束的列的数据类型必须是整数类型

添加语法：

create table temp(

id int primary key AUTO_INCREMENT, #primary key 主键,AUTO_INCREMENT自增长，

name varchar(20)

);

修改自增长：

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

删除自增长：

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

4.8 外键约束(了解)

关键词：FOREIGN KEY，限定某个表的某个字段的引用完整性。主表（父表）：被引用的表，被参考的表从表（子表）：引用别人的表，参考别人的表。

举例：

create table user( #主表

userid int primary key,

...

...

 );



create table info( #学生信息表

Id   int  primary key,

name varchar(255)，

...

Userid int

foreign key (userid) references user(userid) #在从表中指定外键约束

 );

说明：

主表user必须先创建成功，然后才能创建info表（主键或唯一约束的列），指定外键成功。
删除表时，先删除从表info，再删除主表user。
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。

已创建表，添加外键：

ALTER TABLE emp1 ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

举例：

ALTER TABLE info ADD [CONSTRAINT info_userid_fk] FOREIGN KEY(userid) REFERENCES info(userid);

删除外键：

alter table +表名 + drop foreign key +外键名字

外键总结：

-- 添加了外键约束后，主表的修改和删除数据受约束

-- 添加了外键约束后，从表的添加和修改数据受约束

-- 在从表上建立外键，要求主表必须存在

-- 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

扩展：

一：外键约束的限制

建外键约束，你的操作（创建表、删除表、添加、修改、删除）会受到限制，从语法层面受到限制。

例如：在员工表中不可能添加一个员工信息，它的部门的值在部门表中找不到。

不建外键约束，你的操作（创建表、删除表、添加、修改、删除）不受限制，要保证数据的引用完整性，只能依靠程序员的编程素养。

二：建外键成本高

在 MySQL 里，外键约束是有成本的，需要消耗系统资源。对于大并发的 SQL 操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。

所以设计表可以不带外键约束，但逻辑层面需要保持一致。

三：阿里开发规范

阿里开发规范【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；

级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

4.9 default约束

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

语法：

create table 表名称(

字段名数据类型 primary key,

字段名数据类型 unique key not null,

字段名数据类型 unique key,

字段名数据类型 not null default 默认值,

);

说明：默认值约束一般不在唯一键和主键列上加。

总结：

建表时需要加not null default，让值不会出现null。
Null 值比较特殊不好比较，碰到运算符，通常返回null。

第五节：视图

5.1 视图的定义

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

5.2 为什么使用视图

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如：其他班级老师查看学生信息，把分数影藏。

-- 视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。

-- 视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。

5.3 创建单表视图

语法：CREATE VIEW 视图名称 AS 查询语句。

举例：

CREATE VIEW user_info_v as SELECT id,name from user_info

5.4 查询视图

语法：

SELECT * FROM user_info;#user_info 查询的表名

5.5 创建多表联合视图

举例1：

CREATE VIEW demo view AS select a.userid userid,
a.username username,b.name name from user_login a,
user_info b where a.userid=b.userid。

举例2：

CREATE VIEW demo view AS select a.userid userid,
a.username username,
b.name name from user_login a inner join user_info b on a.userid=b.userid。

5.6 利用视图对数据格式化

举例：

CREATE VIEW user_info_v2 as SELECT CONCAT(name,phone) allinfo from user_info

5.7 查看视图

Show tables;#查看所有的表和视图。

Desc 表名称;#查看视图结构

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

SHOW CREATE VIEW 视图名称;#查看视图的创建信息。

5.8 视图数据更新

一般情况：MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

举例：

更新视图或基表，查看视图数据更新情况。

5.9 视图不可更新

要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。如下情况时，视图也不支持更新操作：

-- 数据格式化后，数据不对应的情况，视图无法更新。

-- 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；

-- 在定义视图的SELECT语句中使用了 JOIN联合查询，视图不支持INSERT和DELETE操作；

-- 在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；

总结：虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据。对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。

5.10 修改视图

语法：ALTER VIEW 视图名称 AS查询语句

5.11 删除视图

语法：

DROP VIEW IF EXISTS 视图名称;

说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。

5.12 视图优点

操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。

减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。

数据安全

MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。

分解复杂逻辑

能够分解复杂的查询逻辑数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑。

总结：

但是我们不能过分的依赖视图，如果实际数据表的结构变更了，我们就需要及时对

相关的视图进行相应的维护。如果视图过多，会导致数据库维护成本的问题，可读性不好，容易变成系统的潜在隐患，所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，正确的使用视图。

第八章：管理权限

1：图形化界面的使用。

2：数据的导入与导出。

第九章：存储过程

第一节存储过程概念

定义：存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。

存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处：

-- 简化操作，提高了sql语句的重用性，减少了开发程序员的压力。

-- 减少操作过程中的失误，提高效率。

-- 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）

-- 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性。

比较：

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表。

视图通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

创建存储过程

2.1 参数说明

语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)



 [characteristics ...] BEGIN



存储过程体

END

参数说明：

没有参数（无参数无返回）

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 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

2.2 结束标记DELIMITER

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

举例：

DELIMITER $

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

sql语句1;

sql语句2;

 END $



DELIMITER ;



说明：定义$开头 与结尾。写完后定义 “DELIMITER ;”恢复。
同样也可定义”//”,但避免”\”,反斜杠为mysql转义字符。

2.3 代码实例

举例1：

#查询所有人信息

delimiter $



create PROCEDURE select_all()



BEGIN



  SELECT * from user_info;



end $



delimiter ;

举例2：

#查询所有人平均年龄

delimiter $



create PROCEDURE select_all_avg()



BEGIN



  SELECT name,avg(age) age from user_info;



end $



delimiter ;

举例3：

#查看年龄最大的学生 并返回参数mx out模式



DELIMITER  $



 CREATE PROCEDURE show_max_age(OUT mx INT)



BEGIN

SELECT max(age)  INTO mx FROM user_info;

 END$

DELIMITER ;

SET @name;

 CALL sp1(@name);

SELECT @name;

举例4：

#查看某个学生的信息，用in传入参数



DELIMITER  $



 CREATE PROCEDURE show_one_info(IN tempname  varchar(20))



BEGIN

SELECT * FROM user_info where name=tempname;

 END$

DELIMITER ;

举例5：

#查询某个人的信息，并返回他变量myage存储他的年龄



DELIMITER  $

 CREATE PROCEDURE show_one_info(IN myname VARCHAR(20),OUT myage int)



BEGIN

         SELECT age INTO myage FROM user_info where name = myname;

 END$

DELIMITER ;



存储过程调用



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