MySQL

最新推荐文章于 2022-11-16 15:01:11 发布

磊哥的小迷妹

最新推荐文章于 2022-11-16 15:01:11 发布

阅读量1k

点赞数 1

分类专栏：笔记文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_48496502/article/details/124616922

版权

笔记专栏收录该内容

112 篇文章 2 订阅

订阅专栏

MySQL

SET PASSWORD='new_password';MySQL基础
DQL
1、基本的SELECT语句
- 1.1、 SQL语言的规则与规范
- 1.2、基本的SELECT语句
- 1.3、位运算符
2、多表查询
- 连接查询
- 分页查询
- UNION 联合查询
- sql99自然连接
- sql99USING连接
3、函数
- 单行函数
- 聚合函数
4、子查询
DDL
- 1、管理库
- 2、管理表
- 约束
DML
- 1、表插入数据
- 2、表更新数据
- 3、表删除数据
- 4、MySQL8新特性：计算列
TCL
视图
- 1、创建
- 2、查看
- 3、修改
- 4、删除
- 5、视图数据增删改
- - 不可更新的视图
存储过程
- 1、创建
- 2、调用执行
- 3、删除、查看
存储函数
- 增删查
- 对比存储函数和存储过程
存储过程和函数的查看、修改、删除
变量
- 1、系统变量
- 2、自定义变量
程序出错的处理程序
- 定义条件
- 定义处理程序
流程控制
- IF 语句
- CASE 语句
- 循环结构之LOOP
- 循环结构之WHILE
- 循环结构之REPEAT
- 对比三种循环结构
- 跳转之LEAVE语句--break
- 跳转之ITERATE语句--continue
游标
- 使用游标步骤
- 示例
触发器
- 1 触发器的创建
- 2 查看、删除触发器
- 3 优缺点
MySQL架构篇
- 1、Linux下MySQL的安装与使用
- 2、MySQL的数据结构
- 3、用户与权限管理
- 4、逻辑架构
- 5、存储引擎

SET PASSWORD=‘new_password’;MySQL基础

DDL（数据定义语言）：CREATE 、 DROP 、 ALTER 、RENAME、TRNUCATE
DML（数据操作语言）：INSERT 、 DELETE 、 UPDATE 、 SELECT
DCL（数据控制语言）：GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT

把查询语句单拎出来一类：DQL（数据查询语言）

将 COMMIT 、 ROLLBACK 取出来称为TCL （事务控制语言）

DQL

1、基本的SELECT语句

1.1、 SQL语言的规则与规范

SQL大小写规范（建议遵守）

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

1.2、基本的SELECT语句

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。

在生产环境下，不推荐你直接使用 SELECT * 进行查询。

模糊查询使用的通配符：

%：表示任意多个字符，包含0个字符
_：表示任意单个字符

注意：

OR可以和AND一起使用，但由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合

基本关键字：

列的别名	AS
去除重复行	DISTINCT
显示表结构	DESCRIBE 或 DESC
空值参与运算	所有运算符或列值遇到null值，运算的结果都为null
安全等于运算符（<=>）	与 = 的区别：在两个操作数均为 null 时，其返回值为1，而不是 null
连接字符串	CONCAT
最小值运算符	LEAST(值1，值2，…，值n)
最大值运算符	GREATEST(值1，值2，…，值n)
转义符 \	查询姓名第二个字符为_ ：`WHERE` last_name `LIKE '_\_%'`
自定义转义符 ESCAPE	查询姓名第二个字符为_ ：`WHERE` last_name `LIKE '_^_%' ESCAPE '^'`
匹配字符串 REGEXP
排序 ORDER BY name, age	多列排序是以第一个值为主

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

REGEXP运算符在进行匹配时，常用的有下面几种通配符：

（1）‘^’匹配以该字符后面的字符开头的字符串

（2）‘$’匹配以该字符前面的字符结尾的字符串

（3）‘.’匹配任何一个单字符

（4）“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”或“b”或“c”。为了命名字符的范围，使用一个‘-’。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字

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

1.3、位运算符

按位与运算符按位与（&）运算符将给定值对应的二进制数逐位进行逻辑与运算。当给定值对应的二进制位的数值都为1时，则该位返回1，否则返回0

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

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

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

按位右移运算符按位右移（>>）运算符将给定的值的二进制数的所有位右移指定的位数。右移指定的位数后，右边低位的数值被移出并丢弃，左边高位空出的位置用0补齐

按位左移运算符按位左移（<<）运算符将给定的值的二进制数的所有位左移指定的位数。左移指定的位数后，左边高位的数值被移出并丢弃，右边低位空出的位置用0补齐

2、多表查询

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wYrQS14Y-1651836276199)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20220302144603803.png)]$

连接查询

外连接查询结果=内连接结果+主表中有而从表中没有的记录

等值连接 VS 非等值连接

是根据查询条件 WHERE 语句来区分的

等值连接：

SELECT e.last_name, d.department_name,e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

非等值连接:

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

自连接 VS 非自连接

根据数据源 FROM 语句来区分的

自连接：

SELECT CONCAT(ew.last_name ,'works for', em.last_name)
FROM employees ew, employees em
WHERE ew.manager_id = em.employee_id ;

内连接 VS 外连接

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

内连接：

左外连接：

右外连接：

SQL92：

内连接

SELECT e.last_name, d.department_name,e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;

外连接：

使用 (+) 创建外连接，但是 MySQL 不支持，Oracle 支持

只有左外连接和右外连接，没有满（或全）外连接

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

SQL99：

内连接(INNER JOIN)

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

左外连接(LEFT OUTER JOIN) outer可省略

SELECT e.last_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

右外连接(RIGHT OUTER JOIN)

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

满外连接(FULL OUTER JOIN)，使用FULL JOIN 或 FULL OUTER JOIN来实现

但是 MySQL 不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替

SELECT e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

交叉连接（CROSS JOIN）就是笛卡尔积 a1,a2,b1,b2 的情况

分页查询

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

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

UNION 联合查询

UNION 操作符返回两个查询的结果集的并集，去除重复记录（A+B-重叠）

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重（A+B）

举例：查询部门编号>90或邮箱包含a的员工信息

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

sql99自然连接

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

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
等同于
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`;

sql99USING连接

使用 USING 指定数据表里的 同名字段 进行等值连接，但是只能配合JOIN一起使用

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
等同于
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;    必须这里是同名字段

3、函数

单行函数

字符函数	用法
LENGTH(str)	获取参数str的字节个数
CONCAT(str1,str2…)	拼接字符串
UPPER(str)、LOWER(str)	改变大小写
SUBSTR(str,pos)、SUBSTR(str,pos,len)	字符截取
INSTR(str,substr)	返回substr在str中首次出现的索引，若无，则返回0
TRIM()	去掉字符前后空格，或去掉字符前后的指定字符
LPAD(str,len,padstr)	实现左填充，右边减短
RPAD(str,len,padstr)	实现右填充，右边减短
REPLACE(str,from_str,to_str)	替换

数字函数	用法
CEIL(x)，CEILING(x)	向上取整，返回>=该参数的最小整数
MOD(x,y)	返回X除以Y后的余数，取余，注意取余结果的正负号与X保持一致
RAND()	返回0~1的随机值
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果，不按照四舍五入，直接截断

日期函数、其他函数	用法
NOW()	返回当前系统日期+时间
CURDATE()	返回当前系统日期，不包含时间
CURTIME()	返回当前时间，不包含日期
STR_TO_DATE(str,format)	将日期格式的字符转换成指定格式的日期
DATE_FORMAT()	将日期转换为字符
DATEDIFF(d1,d2)	将 d1 的日期减去 d2 日期，得到天数的差额
VERSION()	查询MySQL版本
DATABASE()	查询当前数据库
USER()	查询当前用户
CHARSET(value)	返回字符串value自变量的字符集
CONVERT(value USING char_code)	将value所使用的字符编码修改为char_code

流程控制函数	用法
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…

# 相当于Java的if...else if...else...
SELECT employee_id,salary, 
					CASE WHEN salary>=15000 THEN '高薪'
						 WHEN salary>=10000 THEN '潜力股'
						 WHEN salary>=8000 THEN '屌丝'
						 ELSE '草根' END "描述"
FROM employees;

# 相当于Java的switch...case...
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;

加密解密函数	用法
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

聚合函数

AVG和SUM函数

对数值型数据使用AVG 和 SUM 函数。

MIN和MAX函数

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

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

不能在 WHERE 子句中使用聚合函数 WHERE AVG(salary) > 8000

HAVING 必须要与 GROUP BY 配合使用

WHERE 是先筛选后连接，而 HAVING 是先连接后筛选

SELECT执行顺序：

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

4、子查询

注意事项：

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

单行操作符：= 、 > 、 >= 、 < 、 <= 、 <>

多行操作符

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

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

#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
			) dept_avg_sal
)

#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
				SELECT AVG(salary) avg_sal
				FROM employees
				GROUP BY department_id
)

示例：查询有员工的部门名

#查询有员工的部门名
SELECT 
  `department_name` 
FROM
  `departments` d 
WHERE EXISTS (
    		SELECT * 
  			FROM  `employees` e 
  			WHERE e.`department_id` = d.`department_id`
)

DDL

1、管理库

创建

CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE IF NOT EXISTS 数据库名;

使用

SHOW DATABASES;           #查看所有库
SELECT DATABASE();          #查看当前库
SHOW TABLES FROM 数据库名;     #查看所有表
SHOW CREATE DATABASE 数据库名;   #查看当前库信息
USE 数据库名;           #切换库

修改

ALTER DATABASE 数据库名 CHARACTER SET 字符集;

删除

DROP DATABASE IF EXISTS 数据库名;

2、管理表

创建

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

查看

DESC 表名
SHOW CREATE TABLE 表名\G

修改

ALTER TABLE 表名 
	ADD|DROP|MODIFY|CHANGE 【COLUMN】 列名 【列的类型 约束】;

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST】|【AFTER 字段名】;    #添加列
ALTER TABLE 表名 CHANGE 【COLUMN】 旧列名 新列名 新的列的类型 ;      #列重命名
ALTER TABLE 表名 MODIFY 【COLUMN】 列名 新的列类型  【DEFAULT 默认值】;    #改类型等       
ALTER TABLE 表名 DROP 【COLUMN】 IF EXISTS 列名 ;   #删除列


ALTER TABLE 旧表名 RENAME 【TO】 新表名;   #表重命名
RENAME TABLE 旧表名 TO 新表名;            #表重命名
DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];   #删除表
TRUNCATE TABLE 表名;              #清空表，不可回滚，自增长列的值从 1 开始
DELETE FROM 表名 WHERE 筛选条件;   #清空表，可回滚，自增长列的值从断点开始
SELECT * FROM information_schema.table_constraints WHERE table_name = 表名;  #查看表约束
SHOW INDEX FROM 表名             #查看表中所有索引


#复制表的结构
CREATE TABLE 表名 LIKE 表名;
#复制表的结构+数据
CREATE TABLE 表名 SELECT * FROM 表名;

删除

DROP TABLE IF EXISTS 表名;

约束

PRIMARY KEY：主键

NOT NULL：非空

DEFAULT：默认

UNIQUE：唯一

CHECK：检查约束【 mysql 8.0 才有效果】

FOREIGN KEY：外键

添加约束：

create table 表名称(      #列级模式，不支持外键，mysql8.0支持CHECK
	字段名 数据类型 primary key auto_increment, 
	字段名 数据类型 not null,
    字段名 数据类型 default 默认值,
	字段名 数据类型 unique [key],
    字段名 数据类型 check ('男' or '女')
);
 
create table 表名称(      #表级模式，不支持非空、默认
	字段名 数据类型,
	字段名 数据类型,
	[constraint 约束名] primary key(字段名),
    [constraint 约束名] unique [key](字段名1,字段名2...),
    [constraint 约束名] CHECK(字段名='' or 字段名=''),
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);

SET auto_increment_increment = 3;       #修改步长
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束类型;    #添加列级约束
alter table 表名称 drop primary key;        #删除主键约束，但是非空还在
ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用
字段) [on update xx][on delete xx];        #添加外键

主键和唯一的对比：

都保证了唯一性
主键为非空，唯一允许为空（注意，NULL值永远唯一，即可以重复插入NULL值）
主键只可有一个要么就没有，唯一可以有多个
可以有组合成为主键，也可以组合成为唯一

DML

如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

1、表插入数据

INSERT INTO 表名 (列名1,...) VALUES (值1,...);
INSERT INTO 表名 SET 列名1=值1, 列名2=值2...;
#将查询结果插入
INSERT INTO 表名 (列名1,...) SELECT (列名1,...) FROM 源表名 [WHERE 筛选条件];

2、表更新数据

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2... [WHERE 筛选条件];

UPDATE 表1 别名 INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件 
		SET 列名1 = 值1, 列名2 = 值2... WHERE 筛选条件 ;

案例：

# 将小说类型(novel)的书的价格都增加5
UPDATE books SET price=price+5 WHERE note = 'novel';

3、表删除数据

DELETE FROM table_name [WHERE 筛选条件];    #有返回值，能回滚，自增长列的值从断点开始
TRUNCATE TABLE 表名;   #没有返回值，不能回滚，自增长列的值从 1 开始

4、MySQL8新特性：计算列

简单来说就是某一列的值是通过别的列计算得来的

例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

CREATE TABLE 表名(
    a INT, b INT, c INT GENERATED ALWAYS AS (a+b) VIRTUAL
)

TCL

开启事务

SET autocommit = 0;  可选
START TRANSACTION;  可选

结束事务

COMMIT;提交事务
ROLLBACK; 回滚事务
DDL 或 DCL 语句（自动提交）
用户会话正常结束
系统异常终了

MySQL中的存储引擎

在mysql中的数据用各种不同的技术存储在文件（或内存）中，其中innodb支持事务，而myisam、memory等不支持事务

# 查看存储引擎
SHOW ENGINES;

# 查看当前隔离级别
select @@tx_isolation;
# MySQL8的版本查看当前隔离级别
select @@transaction_isolation;

# 修改隔离级别
set session|global transaction isolation level read uncommitted;

# 修改字符集
set names gbk;

保存点

保存点只可与回滚搭配使用，类似于复活点

# 设置保存点
SAVEPOINT 节点名;
# 回滚到保存点，节点名可以任意设置
ROLLBACK TO 节点名;

视图

视图是一种虚拟表 ，本身是不具有数据的

视图建立在已有表的基础上，视图赖以建立的这些表称为基表。视图只保存 SQL 逻辑，即 as 后面的语句

视图一般仅用于查，不用于增删改

常见的数据库对象

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

1、创建

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

2、查看

SHOW TABLES;         #查看数据库的表对象、视图对象
DESC / DESCRIBE 视图名称;      # 查看视图的结构
SHOW CREATE VIEW 视图名称;     #查看视图的详细定义信息

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

3、修改

CREATE OR REPLACE VIEW 视图名 AS 查询语句;
ALTER VIEW 视图名 AS 查询语句;

4、删除

DROP VIEW IF EXISTS 视图名称1, 视图名称2, 视图名称3...;

5、视图数据增删改

注意，原表中的数据也会修改

# 插入数据
INSERT INTO 视图名(字段名,字段名) VALUES(值,值);

# 修改
UPDATE 视图名 SET 字段名=值 WHERE ...;

# 删除
DELETE FROM 视图名 WHERE ...;

不可更新的视图

包含以下关键字的 SQL 语句：分组函数、distinct、group by、having、union或者union all

因为以上条件生成的视图的数据是原表动态生成的，所以视图的更新无法同步更改原表，所以此类视图无法更新
在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
常量视图
CREATE VIEW test2 AS SELECT 'tom';
在定义视图的 Select 中包含子查询
视图定义基于一个不可更新视图；
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表

存储过程

就是一组经过 预先编译的 SQL 语句 的封装，提高了sql语句的重用性

一旦存储过程被创建出来，就像使用函数，直接通过调用存储过程名即可。但是 存储过程是没有返回值 的

1、创建

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

默认是 IN
如果存储过程体仅有一句话，BEGIN END可省略
存储过程体中的每条SQL语句以分号结尾
存储过程的结尾可以使用 DELIMITER 重新设置，DELIMITER 结束标记
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语句的限制
1. CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
2. NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
3. READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
4. MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句；
5. 默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程
1. DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
2. INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程；
3. 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
COMMENT ‘string’ ：注释信息，可以用来描述存储过程

2、调用执行

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

格式：

1、调用in模式的参数：

CALL sp1('值');

2、调用out模式的参数：

SET @name;
CALL sp1(@name);
SELECT @name;

3、调用inout模式的参数：

SET @name=值;
CALL sp1(@name);
SELECT @name;

举例：

# 插入到stu表中3条数据
DELIMITER $

CREATE PROCEDURE p1 () 
BEGIN
	INSERT INTO stu VALUES(1, 'jerry'),(2, 'tom'),(3, 'timi') ;
END $

DELIMITER ;

# 存储过程的调用
CALL p1();

3、删除、查看

删除：

DROP PROCEDURE 存储过程名1,存储过程名2;

查看：

SHOW CREATE PROCEDURE 存储过程名;

存储函数

调用方式与调用MySQL预定义的系统函数一样

增删查

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
 BEGIN 
   函数体      #函数体中肯定有 RETURN 语句
 END 结束标记

如果函数体仅有一句话，BEGIN END可省略
使用 DELIMITER 设置结束标记，DELIMITER 结束标记

调用（执行）：

SELECT 函数名(参数列表);

查看：

SHOW CREATE FUNCTION 函数名

删除：

DROP FUNCTION 函数名;

对比存储函数和存储过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1wVUt0o7-1651836276204)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20220309115405774.png)]

此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的

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

# 查看创建信息
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

# 查看状态信息
#[LIKE 'pattern']：匹配存储过程或函数的名称，可以省略
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

#  从information_schema.Routines表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];

修改

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

删除

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

变量

1、系统变量

分为全局变量和局部变量，global指全局变量，session指局部变量，不写默认为局部变量

语法：

查看所有或部分系统变量

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

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

查看指定系统变量

#查看指定的系统变量的值
SELECT @@global.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
或
SELECT @@变量名;   # 不写的默认是会话变量

修改系统变量的值

#为某个系统变量赋值
#方式1：
SET @@global.变量名=变量值;
#方式2：
SET GLOBAL 变量名=变量值;

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

全局变量作用域：
针对于所有的会话（连接）有效，但不能跨重启
服务器每次启动将为所有的全局变量赋初始值

会话变量作用域
仅仅对于当前会话（连接）有效

2、自定义变量

又分为会话用户变量和局部变量

用户变量作用域：
仅仅对于当前会话（连接）有效，同于会话变量作用域

局部变量作用域：
仅仅在定义它的begin end 中有效

位置：只能放在 BEGIN … END 中，而且只能放在第一句

会话用户变量

语法：

定义

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

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

查看用户变量的值（查看、比较、运算等）

SELECT @用户变量

# 举例
SET @a = 1;
SELECT @num := COUNT(*) FROM employees;

局部变量

定义：可以使用 DECLARE 语句定义一个局部变量

位置：只能放在 BEGIN … END 中，而且只能放在第一句

语法：

BEGIN
    #声明局部变量
    DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; # 如果没有DEFAULT子句，初始值为NULL
    DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
    
    #为局部变量赋值
    SET 变量名1 = 值;
    SET 变量名:=值;
    SELECT 值 INTO 变量名2 [FROM 子句];
    
    #查看局部变量的值
    SELECT 变量1,变量2,变量3;
END

对比会话用户变量与局部变量

在这里插入图片描述

程序出错的处理程序

定义条件

将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的 DECLARE HANDLER 语句中

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

错误码的说明：

MySQL_error_code 和 sqlstate_value 都可以表示MySQL的错误
- MySQL_error_code是数值类型错误代码**（默认）**
- sqlstate_value是长度为5的字符串类型错误代码
例如，在**ERROR 1418 (HY000)**中，1418是MySQL_error_code，'HY000’是sqlstate_value
例如，在**ERROR 1142 (42000)**中，1142是MySQL_error_code，'42000’是sqlstate_value。

定义处理程序

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

流程控制

条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

IF 语句

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

CASE 语句

CASE 语句的语法结构1：

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

CASE 语句的语法结构2：

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

循环结构之LOOP

[标签:] LOOP
	循环执行的语句;
END LOOP [标签];

循环结构之WHILE

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

循环结构之REPEAT

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

对比三种循环结构

这三种循环都可以省略名称，但如果循环中添加了**循环控制语句（LEAVE或ITERATE）**则必须添加名称。
LOOP：一般用于实现简单的"死"循环
WHILE：先判断后执行
REPEAT：先执行后判断，无条件至少执行一次

跳转之LEAVE语句–break

LEAVE和BEGIN … END或循环一起被使用

表示跳出循环或者跳出程序体的操作

可以把 LEAVE 理解为 break

LEAVE 标记名

跳转之ITERATE语句–continue

只能用在循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环

可以把 ITERATE 理解为 continue，意思为“再次循环”

ITERATE label

游标

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

使用游标步骤

声明游标

适用于 MySQL，SQL Server，DB2 和 MariaDB
```
DECLARE 游标名 CURSOR FOR 查询语句;
```
如果是用 Oracle 或者 PostgreSQL
```
DECLARE 游标名 CURSOR IS 查询语句;
```
打开游标
```
OPEN 游标名;
```
使用游标（从游标中取得数据）

读取当前行、将数据保存到变量、并且游标移动指向下一行
```
FETCH 游标名 INTO var_name [, var_name] ...
```
注意： var_name必须在声明游标之前就定义好

注意： 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致
关闭游标
```
CLOSE 游标名;
```
游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束

游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

示例

实现累加薪资最高的几个员工的薪资值，直到薪资总和达到 limit_total_salary 参数的值，返回累加的人数

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salary DOUBLE,OUT
total_count INT)

BEGIN
    DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资
    DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值
    DECLARE emp_count INT DEFAULT 0; #记录循环个数
    
    #定义游标
    DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
    
    #打开游标
    OPEN emp_cursor;
    
    REPEAT
        #使用游标（从游标中获取数据）
        FETCH emp_cursor INTO cursor_salary;
        
        SET sum_salary = sum_salary + cursor_salary;
        SET emp_count = emp_count + 1;
        
        UNTIL sum_salary >= limit_total_salary
    END REPEAT;
    
    SET total_count = emp_count;  #返回累加的人数
    
    #关闭游标
    CLOSE emp_cursor;
END //

DELIMITER ;

触发器

有 2 个或者多个相互关联的表，如商品信息和库存信息，添加一条新商品记录的时候，必须同时在库存表中添加一条库存记录

除了使用事务包裹起来，还可以使用触发器

可以创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作 MySQL 5.0.2 版本支持触发器

当需要自动执行一些数据库逻辑时，可以使用触发器来实现

1 触发器的创建

创建触发器的语法结构是：

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

说明：

表名：表示触发器监控的对象
BEFORE|AFTER ：表示触发的时间
- BEFORE 表示在事件之前触发
- AFTER 表示在事件之后触发
INSERT|UPDATE|DELETE ：表示触发的事件
INSERT 表示插入记录时触发；
UPDATE 表示更新记录时触发；
DELETE 表示删除记录时触发
触发器执行的语句块：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块

示例：

创建名称为 before_insert 的触发器，向 trigger 数据表插入数据之前，向 log 数据表中插入信息

DELIMITER //

CREATE TRIGGER before_insert  #创建触发器

BEFORE INSERT ON trigger
FOR EACH ROW
BEGIN
    INSERT INTO log (t_log)
    VALUES('before_insert');

END //

DELIMITER ;

#向test_trigger数据表中插入数据
INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

定义触发器 trigger，基于员工表 “employees” 的INSERT事件，在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资，如果大于领导薪资，则报 sqlstate_value 为’HY000’的错误，从而使得添加失败

DELIMITER //

CREATE TRIGGER salary_check_trigger  #创建触发器

BEFORE INSERT ON employees FOR EACH ROW
BEGIN
    DECLARE mgrsalary DOUBLE;
    SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
    IF NEW.salary > mgrsalary THEN  #NEW关键字代表INSERT添加语句的新记录
    SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
    END IF;
END //

DELIMITER ;

2 查看、删除触发器

查看当前数据库的所有触发器的定义
```
SHOW TRIGGERS\G
```
查看当前数据库中某个触发器的定义
```
SHOW CREATE TRIGGER 触发器名
```
从系统库 information_schema 的 TRIGGERS 表中查询 “salary_check_trigger” 触发器的信息
```
SELECT * FROM information_schema.TRIGGERS;
```

删除

DROP TRIGGER IF EXISTS 触发器名称;

3 优缺点

优点

触发器可以确保数据的完整性
触发器可以帮助我们记录操作日志
触发器还可以用在操作数据前，对数据进行合法性检查

缺点：

触发器最大的一个问题就是可读性差

如果你不了解这个触发器，很可能会认为是更新语句本身的问题
相关数据的变更，可能会导致触发器出错

MySQL架构篇

1、Linux下MySQL的安装与使用

2、MySQL的数据结构

1 查看默认数据库

查看计算机上当前有哪些数据库：可以看到有4个数据库是属于MySQL自带的系统数据库

mysql

核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等
information_schema

这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表，用于表示内部系统表
performance_schema

这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息
sys

这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能

2 表在文件系统中的表示

2.1 InnoDB存储引擎模式

表结构

为了保存表结构， InnoDB 在 数据目录 下对应的数据库子目录下创建了一个专门用于 描述表结构的文件 ，文件名是这样：
```
表名.frm
```
当在数据库 test 下创建表 aha ，那在数据库 test 对应的子目录下就会创建一个名为 aha.frm 的用于描述表结构的文件

.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以 二进制格式 存储的，我们直接打开是乱码的
表中数据和索引
1. 系统表空间（system tablespace）
  
  默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。怎么才12M？注意这个文件是 自扩展文件 ，当不够用的时候它会自己增加文件大小。可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下my.cnf 配置文件：
```
[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend
```
2. 独立表空间(file-per-table tablespace)
  
  在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为 每一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，扩展名为 .ibd ，文件名是这样：
```
表名.ibd
```
  比如：我们使用了 独立表空间 去存储数据库 test 下的 aha 表的话，那么在该表所在数据库对应的 test 目录下会为 aha 表创建这两个文件：
```
aha.frm
aha.ibd
```
  其中 test.ibd 文件就用来存储 test 表中的数据和索引
3. 系统表空间与独立表空间的设置
  
  我们可以指定使用 系统表空间 还是 独立表空间 来存储数据，由启动参数 innodb_file_per_table 控制
```
[server]
innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间
```
4. 其他类型的表空间
  
  随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等

2.2 MyISAM存储引擎模式

表结构

在存储表结构方面， MyISAM 和 InnoDB 一样，也是在 数据目录 下对应的数据库子目录下创建了一个专门用于描述表结构的文件：
```
表名.frm
```
表中数据和索引

在MyISAM中的索引全部都是 二级索引 ，该存储引擎的 数据和索引是分开存放 的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。假如 aha 使用MyISAM存储引擎的话，那么在它所在数据库对应的 test 目录下会为 aha 表创建这三个文件：
```
aha.frm 存储表结构
aha.MYD 存储数据 (MYData)
aha.MYI 存储索引 (MYIndex)
```
创建表时，使用 ENGINE 选项显式指定引擎
```
CREATE TABLE `student_myisam` (
    ...
)ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;
```

3 小结

举例： 数据库a ，表b 。

1、如果表b采用 InnoDB ，data\a中会产生1个或者2个文件：

b.frm ：描述表结构文件，字段长度等
如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
如果采用 独立表空间 存储模式，data\a中还会产生 b.ibd 文件（存储数据信息和索引信息）

此外：

① MySQL5.7 中会在data/a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件。

② MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中。

2、如果表b采用 MyISAM ，data\a中会产生3个文件：

MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。

MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等
b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
b.MYI (MYIndex)：存放索引信息文件

3、用户与权限管理

1 用户管理

1.1 登录MySQL服务器

启动MySQL服务后，通过mysql命令来登录MySQL服务器：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

参数解读：

-h参数 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP
-P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口，port为连接的端口号
-u参数 后面接用户名，username为用户名
-p参数 会提示输入密码
DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库
-e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL 服务器

1.2 创建用户

语法：

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成
“[ ]”表示可选，如果指定密码值，这里需要使用 IDENTIFIED BY指定明文密码值
CREATE USER语句可以同时创建多个用户

1.3 修改用户

语法：

UPDATE mysql.user SET USER='li4' WHERE USER='wang5';

为了让新设置的用户生效，要么重启 mysql

要么执行以下语句

FLUSH PRIVILEGES;

1.4 删除用户

使用DROP方式删除（推荐）

必须用于DROP USER权限
```
DROP USER user[,user]…;
```

使用DELETE方式删除

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;

FLUSH PRIVILEGES; #执行完DELETE命令后要使用FLUSH命令来使用户生效

不推荐通过DELETE方式进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现 mysql.user 表和 mysql.db 表的相应记录都消失了

1.5 设置当前用户密码

旧的写法如下

# 修改当前用户的密码：（MySQL5.7测试有效）
SET PASSWORD = PASSWORD('123456');

使用ALTER USER命令来修改当前用户密码（推荐）
```
ALTER USER USER() IDENTIFIED BY 'new_password';
```
使用SET语句来修改当前用户密码

SET PASSWORD='new_password';

1.6 修改其它用户密码

使用ALTER语句来修改普通用户的密码

ALTER USER user [IDENTIFIED BY '新密码']
[,user[IDENTIFIED BY '新密码']]…;

使用SET命令来修改普通用户的密码

SET PASSWORD FOR 'username'@'hostname'='new_password';

使用UPDATE语句修改普通用户的密码（不推荐）

UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";

2 权限管理

2.1 权限列表

# 查看权限列表
show privileges;

CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库
SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作
SELECT权限 只有在它们真正从一个表中检索行时才被用到
INDEX权限 允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义
ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表
CREATE ROUTINE权限 用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限用来执行保存的程序
GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序
FILE权限 使用户可以使用 LOAD DATA INFILE 和 SELECT … INTO OUTFILE 语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）

2.2 授予权限

语法：

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

该权限如果发现没有该用户，则会直接新建一个用户

注意：库名、表名可以使用通配符 *

示例：

授予通过网络方式登录的joe用户，对所有库所有表的全部权限，密码设为123

注意这里唯独不包括grant的权限

GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

% 表示所有远程通过 TCP方式的连接

2.3 查看权限

查看当前用户权限

SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();

查看某用户的全局权限

SHOW GRANTS FOR 'user'@'主机地址' ;

2.4 收回权限

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限

注意：须用户重新登录后才能生效

语法：

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

示例：

#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

3 角色管理

3.1 创建角色

如果 host_name省略，默认为% ， role_name不可省略 ，不可为空

语法：

CREATE ROLE '角色名'[@'host_name'] [,'角色名'[@'host_name']]...

3.2 给角色赋予权限

GRANT 权限名,权限名... ON 库名.表名 TO '角色名'[@'host_name'];

3.3 查看角色的权限

SHOW GRANTS FOR '角色名';

每个角色，系统就会自动给一个“ USAGE ” 权限，意思是 连接登录数据库的权限

3.4 回收角色的权限

REVOKE 权限名,权限名... ON 库名.表名 FROM 'rolename';

3.5 删除角色

DROP ROLE role [,role2]...

注意， 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限

3.6 给用户赋予角色

要赋给用户并处于 激活状态 才能发挥作用

GRANT role [,role2,...] TO '角色名'[@'host_name'];

可查询当前角色角色是否激活，如果角色未激活，结果将显示NONE

SELECT CURRENT_ROLE();

激活角色

使用set default role 命令激活角色

SET DEFAULT ROLE ALL TO '角色名'@'host_name';

将activate_all_roles_on_login设置为ON
```
SET GLOBAL activate_all_roles_on_login=ON;
```
这条 SQL 语句的意思是，对 所有角色永久激活

3.7 撤销用户的角色

REVOKE role FROM '角色名'@'host_name';

4、逻辑架构

1 逻辑架构剖析

服务器处理客户端请求

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eYYWCfdu-1651836276206)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20220416215352773.png)]$

小结

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mBzrxxve-1651836276208)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20220416221612099.png)]$

简化为三层结构：

连接层： 客户端和服务器端建立连接，客户端发送 SQL 至服务器端
SQL 层（服务层）： 对 SQL 语句进行查询处理；与数据库文件的存储方式无关
存储引擎层： 与数据库文件打交道，负责数据的存储和读取。

第1层：连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接

经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做 身份认证、权限获取

用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销

第2层：服务层

SQL Interface: SQL接口

接收用户的SQL命令，并且返回用户需要查询的结果

MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
Parser: 解析器

在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的

在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树 ，并根据数据字典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写
Optimizer: 查询优化器

SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划

这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户

它使用“ 选取-投影-连接 ”策略进行查询。例如：
```
SELECT id,name FROM student WHERE gender = '女';
```
1 上面语句先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行条件过滤

2 上面查询先根据 id 和 name 进行属性投影，而不是将属性全部取出以后再进行过滤

3 将这两个查询条件连接起来生成最终查询结果
Caches & Buffers：查询缓存组件

比如Query Cache用来缓存查询语句的执行结果，如果有缓存，那么就不必再进行查询解析、优化和执行的整个过程了，直接返回结果

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

这个查询缓存可以在 不同客户端之间共享

从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除

第3层：引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样可以根据自己的实际需要进行选取

存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以文件的方式存在的，并完成与存储引擎的交互

当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN 等各种存储系统

2 SQL执行流程

3 数据库缓冲池(buffer pool)

5、存储引擎

1 设置系统默认的存储引擎

查看默认的存储引擎：

show variables like '%storage_engine%';
#或
SELECT @@default_storage_engine;

修改默认的存储引擎

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或者修改 my.cnf 文件：

default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service

2 设置表的存储引擎

2.1 创建表时指定存储引擎

CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;

2.2 修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

3 引擎介绍

3.1 InnoDB 引擎：具备外键支持功能的事务存储引擎

大于等于5.5之后，默认采用InnoDB引擎
InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)
除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎
数据文件结构：（2、MySQL数据目录已讲）
- 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
- 表名.ibd 存储数据和索引
InnoDB是 为处理巨大数据量的最大性能设计
- 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了
- 比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了
对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和索引
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较高 ，而且内存大小对性能有决定性的影响

3.2 MyISAM 引擎：主要的非事务处理存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复
5.5之前默认的存储引擎
优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
数据文件结构：（2、MySQL数据目录已讲）
- 表名.frm 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
应用场景：只读应用或者以读为主的业务

3.3 Archive 引擎：用于数据存档

备份/时间点恢复 （在服务器中实现，而不是在存储引擎中）
**压缩数据 **
加密数据（加密功能在服务器中实现）
地理空间数据类型支持
**锁粒度 **
没有任何存储限制
更新数据字典的统计信息

3.4 Blackhole 引擎：丢弃写操作，读操作会返回空内容

3.5 CSV 引擎：存储数据时，以逗号分隔各个数据项

创建表时会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相同，后缀为

表名.CSM
表名.CSV

3.6 Memory 引擎：置于内存的表

主要特征：

Memory同时 支持哈希（HASH）索引 和 B+树索引
Memory表至少比MyISAM表要 快一个数量级
采用的逻辑介质是内存， 响应速度很快 。进程崩溃的时候 数据会丢失
MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size
- max_rows可以在创建表时指定；
- max_heap_table_size的大小默认为16MB，可以按需要进行扩大
数据文件与索引文件分开存储
缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心
要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

使用场景：

目标数据比较小，而且非常 频繁的进行访问 ，在内存中存放数据，如果太大的数据会造成 内存溢出 。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大小
如果 数据是临时的 ，而且 必须立即可用 得到，那么就可以放在内存中
存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系

3.7 Federated 引擎：访问远程表

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的 跨服务器的灵活性，但也经常带来问题，因此 默认是禁用的 。

3.8 Merge引擎：管理多个MyISAM表构成的表集合

3.9 NDB引擎：MySQL集群专用存储引擎

也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集群

4 MyISAM 和 InnoDB 对比

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ur2I7SkI-1651836276209)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20220416223557840.png)]$