[大数据]MySQL学习笔记

MySQL

启动与退出

bin/

mysqld.exe:服务端
mysql.exe:客户端

控制台访问：

-h:主机地址(本机 localhost可以省略)
-P:端口号 (默认3306可以省略)
-u:用户名
-p:密码  
连在一起写！
\q,quit,exit 退出服务
!!安全起见 -p后直接回车 然后输入密码

数据库操作

一、创建数据库

CREATE DATABASE 数据库名 数据库选项;

数据库选项:

• 字符集 charset/character set
• 校对集 collate

二、显示数据库

SHOW DATABASES; //显示所有数据库
SHOW CREATE DATABASE 数据库名; //显示数据库创建指令

三、修改数据库

ALTER DATABASE 数据库名 数据库选项;

四、删除数据库

DROP DATABASE 数据库名;

五、使用数据库

USE 数据库名;

表操作

一、创建表

CREATE TABLE 表名(
    字段名 字段类型,
    ...
    字段名 字段类型
)表选项;

表选项

• 存储引擎: engine=具体存储引擎
• 字符集:[ default ]charset 具体字符集 (继承数据库的)
• 校对集: collate (继承数据库的)

二、显示数据表

SHOW TABLES; //显示所有数据表
SHOW TABLES FROM 数据库名; //显示所有某数据库下数据表
SHOW TABLES LIKE ''; //显示匹配的表 _匹配一个字符 %匹配多个字符
SHOW CREATE TABLE 表名; //显示表创建指令

三、查看数据表

DESC 表名;
DESCRIBE 表名;
SHOW COLUMNS FROM 表名;

四、更改数据表

RENAME TABLE 表名 TO 新表名; //修改表名
ALTER TABLE 表名 表选项; //修改表选项

五、更改字段

1.ADD 添加字段

ALTER TABLE 表名 ADD [ COLUMN] 字段名 字段类型 [ 字段属性] [ 字段位置];

字段位置：(适用于增加字段，修改字段，更改字段名)

• FIRST
• AFTER 已经存在的字段名

2.CHANGE 更改字段名

ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 [ 字段属性] [ 位置];

3.MODIFY 修改类型

ALTER TABLE 表名 MODIFY 字段名 字段类型 [ 字段属性] [ 位置];

4.DROP 删除字段

ALTER TABLE 表名 DROP 字段名;

数据操作

一、新增数据

INSERT INTO 表名 VALUES (字段列表顺序对应所有值); //全字段插入
INSERT INTO 表名 (字段列表) VALUES (字段列表对应的值的顺序列表); //部分字段插入

二、查看数据

SELECT */字段列表 FROM 表名;

三、更新数据

UPDATE 表名 SET 字段=新值,...,字段=新值 [ WHERE 条件筛选];

四、删除数据

DELETE FROM 表名 [ WHERE 条件];

字符集

SHOW charset; //显示MySQL支持的所有字符集

字符集 : charset / character set,是各种文字和符号在计算机中的总称

SET NAMES 客户端字符集; 告诉服务器当前客户端的字符集

设置字符集

• character_set_client:客户端提供的数据字符集
• character_set_results:客户端需要服务的提供数据的字符集
• character_set_connection:连接使用的字符集，内部数据操作

校对集

SHOW collation; 查看MySQL支持的所有校对集

校对集 collate/collation 即数据比较时的对应规则

• _ci, case insensitive :大小写部敏感(不区分大小写)
• _cs, case sensitive :大小写敏感
• _bin, binary :二进制比较

校对集设置

collate 校对集规则;

字段类型

一、整数类型

• tinyint, 迷你整型，使用 一个字节存储整数，最多存储256个整数(-128~127)
• smallint, 短整型，使用 两个字节存储整数
• mediumint, 使用 三个字节
• int, 标准整型，四个字节
• bigint, 大整型，八个字节
• unsigned, 代表无符号（默认有符号）

显示宽度 ：整型数在数据库中显示的符号个数（包括正负号）

可以通过zerofill 让不够宽的数值补充到对应宽度

二、小数类型

• 浮点数
  • float, 单精度，使用四个字节存储，精度范围7-8位有效数字
  • double, 双精度，使用八个字节存储，精度范围15-16位有效数字
  • 可以指定： float/double(总长度,小数部分长度)
• 定点型
  • decimal, 能够保证精度的小数，每九个数字用4个字节存储
  • 可以指定： decimal(有效数位,小数部分位数)

三、字符串类型

• char(L), 定长型，指定固定长度的存储空间存储字符串
  • 定长是指存储长度
  • 定长长度是字符长度而不是字节长度
    • L的最大值是255
    • 实际存储空间：L字符数 * 字符集对应的字节数
  • 定长的长度L是最大长度，不能超过，但是可以小于
  • 字符串数据使用单引号''或者双引号""包裹
• varchar(L), 变长型，根据实际存储的数据变化存储空间
  • 变长型的存储空间是由实际数据大小决定的
  • 变长长度L是字符长度而不是字节长度
    • L指定的是最大长度
    • L的最大理论值是65535
    • 变长需要额外产生1-2个字节，用来记录实际数据长度
• 文本型 专门用来存储较长的文本
  • text, 普通字符
    • tinytext:迷你文本，不超过2^8-1个字符
    • text:普通文本，不超过2^16-1个字符
    • mediumtext:中型文本，不超过2^24-1个字符
    • longtext:长文本，不超过2^32-1个字符(4G)
  • blob, 二进制字符(与text类似)
    • tinyblob
    • blob
    • mediumblob
    • longblob
• 枚举型 枚举 枚举是一种映射存储方式，以较小的空间存较多的数据
  • 枚举在定义的时候确定可能出现的可能
  • 枚举在定义后数据只能出现定义时的一种
  • 枚举类似于一种单选框
  • 枚举使用1-2个字节存储，最多可以设置65535个选项
  • 枚举实际存储是使用数值，映射对应的元素数据，从1开始
  • 枚举语法enum(元素1,...,元素N)
• 集合型 集合 是一种映射存储方式，以较小的空间存较多的数据
  • 集合在定义时确定可能出现的结果进行穷举
  • 集合在定义后数据只能出现定义时其中的元素（一个或者多个）
  • 集合类似于多选框
  • 集合使用1-8个字节存储，最多64个元素
  • 集合实际存储数值（二进制位），映射对应元素数据，每个元素对应一个比特位
  • 集合语法set(元素1,...,元素N)

四、时间日期类型

• 年 year
  • MySQL中用一个字节存储年份
  • year能够表示的范围是1901-2155年
    • year的特殊值是0000
  • year允许用户使用两种方式设计
    • year
    • year(4)
• 时间戳 timestamp
  • MySQL中的时间戳表示形式并不是秒数，而是年月日时分秒格式
    • YYYY-MM-DD HH:II:SS
    • YYYYMMDDHHIISS
  • timestamp使用四个字节
  • timestamp的特点是对应的记录无论哪个字段被跟新，该字段都会更新到当前的时间
• 日期 date
  • 使用3个字节存储存储格式 YYYY-MM-DD
  • 存储范围 1001-01-01~9999-12-31
• 日期时间 datetime
  • 使用8个字节
  • 格式 YYYY-MM-DD HH:II:SS
  • 存储区间 1000-01-01 00:00:00~9999-12-31 23:59:59
• 时间 time
  • 使用3个字节
  • 数据范围 -838:59:59~838:59:59
  • 数据格式
    • 时间格式 [H]HH:II:SS
    • 时间段格式 D HH:II:SS //D表示天

属性

属性 ：建立在字段类型之后，对字段除了类型之外的其他进行约束

• 属性是在定义表字段的时候对每个字段进行属性设定
• 设定好的属性可以通过desc进行查看
• 数据在进行增删改操作的时候需要在满足字段要求的同时满足属性要求

desc 表名; //查看表属性
// Field:字段名字
// Type:数据类型
// Null:是否为空（属性）
// Key:索引类型（属性）
// Default:默认值（属性）
// Extra:额外属性

一、Null 属性

NULL ：数据是否允许为空

• 默认情况是数据允许为空
• 不为空设计: Not Null
• 数据为空一般不具备运算和分析价值，所以数据通常都需要设定为 NOT NULL

二、Default 属性

Default：默认值，在设计字段的时候给定默认数据

• 默认值触发
  • 在系统进行插入数据的时候自动检测触发
  • 主动使用default关键字触发

三、primary key 主键

primary key：主键，用来保证整张表中对应的字段永远不会出现重复数据

• 主键在一张表中只能有一个
• 主键的另一个特性就是能够提升主键字段作为查询条件的效率（索引）
• 主键不能为空 NOT NULL默认
• 逻辑主键：数据没有具体业务意义，纯粹是一种数值数据
  • 逻辑主键通常是整数：int
  • 逻辑主键目的是方便检索和数据安全（不暴露数据的真实信息）
• 复合主键：多个字段共同组成不能重复的数据
  • primary key(字段1,...,字段n)

主键管理

主键管理：在创建表并且已经有数据后的维护

• ALTER TABLE 表名 DROP primary key; //删除主键
• ALTER TABLE 表名 ADD primary key 主键字段名; //追加主键
• 修改主键（先删除后增加）

四、auto_increment 自增长属性

auto_increment:自增长，被修饰的字段在新增时，自动增长数据

• 自增长属性只能是整数类型，而且对应的字段必须是一个索引（通常是逻辑主键）
• 一张表只能有一个自增长属性
• 自增长数据可以理解为一个默认值，如果主动给值，那么自增长不会触发
• 自增长由两个变量控制
  • 初始值：auto_increment_offset 默认值是1
  • 步长：auto_increment_increment 默认值是1
  • 查看自增长控制：SHOW VARIABLES LIKE 'auto_increment%';

自增长管理

• 修改表中自增长的值：让下次自增长按照指定值开始

ALTER TABLE 表名 auto_increment = 值; //下一次开始就会从设置的这个值开始

• 修改自增长控制：调整自增长的变化 （修改针对的整个数据库，而非单张表）

SET auto_increment_offset = 值; //修改初始值，当前连接当前用户有效（局部）
SET auto_increment_increment = 值; //修改步长
SET @@auto_increment_offset = 值; //修改初始值，所有用户一直有效（全局）
SET @@auto_increment_increment = 值;

五、unique key 唯一键

unique key:唯一键，用来维护数据的唯一性

• 一个表可以有多个唯一键
• 唯一键与主键的区别在于唯一键运行数据为NULL，而且NULL的数量不限
• 唯一键与主键一样，可以提升字段数据当作查询条件的查询效率
• 有复合唯一键unique key (字段1,...,字段n);

唯一键管理

• ALTER TABLE 表名 DROP index 唯一键名字;//删除唯一键
• ALTER TABLE 表名 ADD unique key 字段名; //添加唯一键

六、comment 属性

comment: 描述，用文字描述字段作用

• comment代表的是对字段的描述
  • 方便以后了解字段作用
  • 相对于注释，备注
• SHOW CREATE TABLE 表名; //查看comment

MySQL高级操作

一、新增数据

1.批量插入

• 全字段批量插入
  INSERT INTO 表名 VALUES(值列表1),…,(值列表n);
• 部分字段批量插入（注意字段默认值）
  INSERT INTO 表名 (字段列表) VALUES (值列表1),…,(值列表n);

2.蠕虫复制

蠕虫复制：从已有表中复制数据直接插入到另一张表

• 蠕虫复制的目标是快速增加表中数据
  • 实现表中数据复制（用于数据备份或者迁移）
  • 实现数据的指数级递增（多用于测试）
• 蠕虫复制语法
  INSERT INTO 表名[(字段列表)] SELECT 字段列表 FROM 表名;
• 注意事项
  • 字段列表必须对应上
  • 字段类型必须匹配
  • 数据冲突需要事先考虑

3.主键冲突

主键冲突： 在数据进行插入时包含主键指定，而主键在数据表已经存在

• 主键冲的业务通常是发生在业务主键上（业务主键本身具有业务意义）
• 主键冲突的解决方案
  • 忽略冲突：保留原始记录

		INSERT IGNORE INTO 表名[(字段列表)] VALUES(值列表);

- 冲突更新：冲突后部分字段变成更新（替换部分字段数据）

		INSERT INTO 表名 [(字段列表)] VALUES(值列表) ON duplicate key update 字段 = 新值[,字段=新值...]; //1.尝试新增  2.更新

- 冲突替换：先删除原有记录，后新增记录（替换全部字段数据）

		REPLACE INTO 表名 [(字段列表)] VALUES(值列表); //效率低

二、数据查询

1.查询选项

• ALL :默认，表示保留所有记录
• DISTINCT :去重重复的记录（所有字段都重复）

2.字段选择&别名

字段选择 ：根据实际需要选择要获取的字段信息

• • 获取所有字段
• 字段数据可以不一定来只自数据源（SELECT只要有结果就行）
  • 数据常量 ：SELECT 1
  • 函数或者变量： SELECT unix_timestamp(),@@version （@@是系统变量的前缀，后面跟变量名）

字段别名

	字段名 AS 别名;
	字段名 别名;

• 字段别名防止字段冲突
• 使用字段别名，数据更安全

3.数据源

	FROM 数据源;

• 单表数据源：数据源就是一张表 FROM 表名
• 多表数据源：数据来源是多张表 FROM 表名1,...,表名n
• 子查询数据源：数据来源是一个查询结果 FROM (SELECT ...)
• 数据表也可以指定别名

4.WHERE 子句

WHERE ：跟在FROM数据源之后，对数据进行条件匹配

• WHERE 是在磁盘读取后，对数据进行条件匹配
  • 不符合条件的数据不会进入内存
• WHERE 筛选的内容因为还没有进入内存，所以数据是没有被加工过的
  • 字段别名不能在WHERE 中使用

5.运算符

• 比较运算符

  > //大于
  < //小于  
  = //等于  
  >= //大于等于  
  <= //小于等于  
  != //不等于
  
  BETWEEN A AND B  //A和B之间 ，包括A,B
  IN(数据1,...,数据n) //在列举的数据
  
  IN(数据1,...,数据n) //在列举的数据
  
  LIKE 'pattern'  //像这样的，用于字符串比较
  	_  //匹配任意一个字符
  	%  //匹配任意零个或多个字符
  

• 逻辑运算符

  AND //与
  OR //或
  NOT //非
  

• NULL运算符

  IS NULL //为空
  IS NOT NULL //不为空
  

6.GROUP BY

GROUP BY : 分组统计，根据某个字段结果分类，并进行数据统计分析

• 分组的目的不是为了显示数据，而是统计数据
• GROUP BY子句写在 WHERE子句之后 （如果同时存在）
• 分组可以嵌套
• 分组统计需要使用统计函数
  • group concat():将组里某个字段全部保留
  • any_value():不属于分组字段的任意一个组里的值
  • count():求分组的记录数量
    • count(*):统计所有记录的数量（每组）
    • count(字段名):统计某字段值的数量（NULL不统计）
  • sum():求组内某字段的和
  • max()/min():求某字段最大/最下值
  • avg():求对应分组中某字段平均值

7.回溯统计

回溯统计： 在进行分组的时候（通常是多分组），每次结果的回溯都要进行一次汇总统计

• 回溯统计语法： 在统计后使用 with rollup

8.分组排序

分组排序 ：在分组后统计结果时可以根据分组字段进行升序或者降序排序显示数据

• 默认就是升序排序显示的
• 可以设定分组结果的排序方式
  • GROUP BY 字段名 [ASC] 升序
  • GROUP BY 字段名 DESC 降序

9.HAVING 子句

HAVING 子句 ：类似于WHERE 子句，是用来进行条件筛选的

• HAVING 子句本身是针对分组统计结果进行筛选的
• HAVING 子句必须在 GROUP BY 后 （如果同时存在）
• HAVING 子句针对的数据是已经在内存中加载了的数据
• HAVING 几乎能够做到WHERE的全部功能，但是WHERE不能
  • 字段别名（WHERE针对磁盘数据，那时还没有）
  • 统计结果（WHERE在GROUP BY 之前）
  • 分组统计函数

10.ORDER BY 子句

ORDER BY ：排序，根据某个指定的字段进行升序或者降序的排序

• 排序参照的是校对集
• 排序分为升序和降序：默认升序
  • ORDER BY 字段 [ASC] : 升序
  • ORDER BY 字段 DESC：降序
• 多字段排序：在根据某个字段排序后，可以再细分排序

11.LIMIT 子句

LIMIT 子句 ： 限制数据的获取数量（记录数）

• LIMIT 子句必须在 ORDER BY 子句后使用（如果同时存在）
• LIMIT 限制数量的两种方式
  • LIMIT 数量 ：限制获取的数量（不保证一定能获取到指定的数量）
  • LIMIT 起始的位置,数量 限制数据的获取的位置以及数量

三、数据更新

数据更新 ：即更新时对更新的记录数进行限制

• 限制更新通过LIMIT实现
• 限制更新实际上是局部更新的一种手段，一般更多情况下是根据条件精确更新

四、数据删除

数据删除 ：限制要删除的记录数

• 使用LIMIT限制删除数量
• 一般很少使用限制删除，通常通过WHERE条件精确限制

清空数据

清空数据 ：将表中数据全部清除，并将表的所有状态回到原始状态

• 清空数据的本质是先删除表然后创建表
• 清空数据可以让表的一些变量回到原始状态
  • 自增长重新回到初始值
• 清空语法：TRUNCATE 表名

MySQL 多表操作

一、联合查询

1.联合查询

UNION :联合查询，是指将多个结果合并为一个结果显示

• 联合查询是针对查询结果的合并（多条SELECT语句的合并）
• 联合查询语法

SELECT 查询; /*决定字段名字*/
	UNION 查询选项
SELECT 查询;
...

• 联合查询要求：联合查询是结果联合显示
  • 多个联合查询的字段结果数量一致
  • 联合查询的字段来源于第一个查询语句的字段
• 查询选项：与SELECT选项一致
  • ALL : 保留所有记录
  • DISTINCT : 保留去重记录(默认)
• 联合查询不要求前后字段类一定一致，只要字段数量对应就行

2.联合查询排序

联合查询排序 ：针对联合查询结果进行排序

• ORDER BY 本身是对内存结果进行排序，UNION的优先级高于ORDER BY，所以ORDER BY 默认是对UNION结果进行排序
• 如果想要对单独SELECT的结果进行排序，需要两个步骤
  • 将需要排序的SELECT指令进行括号包裹（括号里面使用ORDER BY）
  • ORDER BY 必须配合LIMIT才能生效（LIMIT一个足够大的数值即可）

二、连接查询

1.交叉连接

CROSS JOIN : 交叉连接，不需要连接的条件连接

• 交叉连接产生的结果就是笛卡尔积
  • 左表的每一条记录都会与右表的所有记录连接并保存
• 交叉连接没有实际的数据值，只有丰富了连接查询的完整性

2.内连接

[INNER] JOIN ：内连接，将两张表根据指定的条件连接起来，严格连接

• 内连接是将一张表的每一条记录去另外一张表根据条件匹配
  • 匹配成功：保留连接的数据
  • 匹配失败：都不保留
• 内连接语法：左表 join 右表 on 连接条件

3.外连接

OUTER JOIN :外连接，是一种不严格的连接方式

• 外连接分为两种
  • 左外连接（左连接）：LEFT JOIN
  • 右外连接（右连接）：RIGHT JOIN
• 外连接有主表和从表之分
  • 左连接 ：左表为主表
  • 右连接 ：右表为主表
• 外连接是将主表的记录去匹配从表的记录
  • 匹配成功保留
  • 匹配失败（全表）：也保留，只是从表字段置空

4.自然连接

NATURAL JOIN :自然连接，是一种自动寻找连接条件的连接查询

• 自然连接包括自然内连接和自然外连接
  • 自然内连接：NATURAL JOIN
  • 自然外连接：NATURAL LEFT JOIN / NATURAL RIGHT JOIN
• 自然连接条件匹配模式：自动寻找相同字段名作为连接条件

5.USING关键字

USING 关键字 ：连接查询时如果是同名字段作为连接条件，USING 可以代替 ON 出现（比ON更好）

• USING 是针对同名字段
• USING 关键字使用后会自动合并对应字段为一个
• USING 可以同时使用多个字段作为条件

三、子查询

1.子查询分类

• 位置分类
  • FROM子查询：子查询出现在FROM后做数据源
  • WHERE子查询：子查询出现在WHERE后作数据条件
• 按子查询得到的结果分类
  • 标量子查询：子查询返回的结果是一行一列（一个数据）
  • 列子查询：结果是一列多行（一列数据）
  • 行子查询：结果是一行多列（一行数据）
  • 表子查询：结果是多行多列（一个表）
  • EXISTS子查询：结果是布尔结果（验证型）
• 子查询都需要使用()进行包裹，必要时对子查询结果进行别名处理

2.EXISTS子查询

EXISTS子查询 ：带入查询，将主表（外部查询）的每一行代入到子表（子查询表）进行校验

• 子查询返回的结果是布尔结果
• EXISTS子查询通常是作为WHERE条件使用
  • WHERE EXISTS （子查询）

3.比较方式

• all:满足后面的全部条件
  • >all(结果集)：数据要大于结果集中的全部数据
• any:满足任意条件
  • =any(结果集)：数据只要等于结果集中任意一个即可
• some:满足任意条件（）与any完全一样
• 结果集：可以是直接的数据，也可以是子查询的结果

MySQL安全管理

一、外键约束

1.外键

外键 ：FOREIGN KEY，表中指向外部表主键的字段为外键

• 外键定义
  • [CONSTRAINT 外键名] FOREIGN KEY(当前表字段名) REFERENCES 外部表(主键字段)
• 外键构成条件
  • 外键字段必须与对应表主键字段类型一致
  • 外键字段本身要求是一个索引（创建外键时会自动生成一个索引）

2.外键约束

外键约束：当表建立外键关系后，外键就会对主表（外键指向的表）和子表（外键所在的表）里的数据产生约束效果

• 外键约束的是写操作（默认操作）
  • 新增：子表插入的数据对应的外键必须在主表中存在
  • 修改：主表的记录如果在子表中存在，那么主表的主键不能修改
  • 删除：主表的记录如果在子表中存在，那么主表的主键不能删除
• 外键约束控制：外键可以在定义的时候控制外键的约束作用
  • 控制类型
    • ON UPDATE：主表更新时子表的表现
    • ON DELETE：主表删除时子表的表现
  • 控制方式
    • CASCADE：级联操作，主表操作后子表跟随操作
    • SET NULL：置空操作，主表操作后，子表关联的外键字段置空
    • RESTRICT/ON ACTION：严格模式，不允许主表操作（默认的)
    • NO ACTION：子表不执行任何操作

3.外键管理

• 新增外键

  ALTER TABLE 表名 ADD [CONSTRAINT '外键名'] FOREIGN KEY(外键字段) REFERENCES 表名(主键) [ON 外键约束];
  

• 删除外键

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
  

• 跟新外键：先删除后新增

二、事务安全

1.事务

事务 ：要做的事情

• 计算机中的事务是指的某个程序的执行单元（写操作）
• 事务安全：当事务执行后，保障事务事务执行有效，数据不会错乱
• 事务安全通常针对一连串操作（多个事务）而产生的统一结果
• MySQL中默认的写操作是直接写入的
  • 执行写操作SQL
  • 同步到数据表

2.事务处理

• 自动事务处理：系统默认，操作结束自动同步到数据表（事务关闭状态）
  • 系统控制：变量 AUTOCOMMIT 值为ON，自动提交
• 手动事务处理
  • 开启事务：START TRANSATION
  • 关闭事务
    • 提交事务：COMMIT
    • 回滚事务：ROLLBACK
• 事务回滚:在事务执行中，可以在某个已经成功的结点设置回滚点，后续回滚的话可以回到某成功点
  • 设置回滚点：SAVEPOINT 回滚点名字
  • 回滚到回滚点：ROLLBACK TO 回滚点名字

3.事务特点

• 原子性（Atomicity）：一个事务是一个整体，不可拆分，要么成功，要么失败
• 一致性（Consistency）：事务执行之前和执行之后都必须处于一致性状态，数据完整性没有被破坏（事务逻辑的准确性）
• 隔离性（Lsolation）：事务操作过程中，其他事务不可见
• 持久性（Durability）：事务一旦提交，结果不可改变

三、预处理

1.预处理

预处理 ：PREPARE STATEMENT,一种预先编译SQL指令的方式（然后命令执行）

• 预处理不同于直接处理，是将要执行的SQL指令先发送给服务器编译，然后通过指令执行
  • 发送预处理：PREPARE 预处理名字 FROM '要执行的SQL指令'
  • 执行预处理：EXECUTE 预处理名字
• 预处理管理
  • 预处理属于会话级别：即当前用户当次连接有效（断开会被服务器清理掉）
  • 删除预处理：DROP/DEALLOCATE PREPARE 预处理名字

2.预处理传参

• 一般的预处理都不会是固定的SQL指令，而是需要参数的

  • 参数使用占位符?占位

    PREPARE 预处理名字 FROM '预处理指令，参数用?替代';
    

• 在执行预处理时将数据参数传入

  • 数据存储到变量中（预处理传入的值必须由变量保存）

    SET @变量名 = 值
    

  • 使用USING 关键字传参数

    EXECUTE 预处理名字 USING @变量名1,...,@变量名n
    

  • 参数顺序要保持一致

四、视图

1.视图

视图 ：VIEW，是一种由SELECT指令组成的虚拟表

• 视图是虚拟表，可以使用表管理（结构管理）

  • 为视图提供数据的表叫基表

  CREATE VIEW 视图名字 AS SELECT指令;  /*创建视图*/
  SELECT */字段名 FROM 视图名字;  #访问视图，一般都是查询
  

• 视图有结构，但不存储数据

  • 结构：SELECT 选择的字段
  • 数据：访问视图时执行的SELECT指令

2.视图管理

• 视图查看：显示视图结构和具体视图信息

  SHOW TABLES;  #查看全部视图
  SHOW CREATE TABLE / VIEW 视图名字;  #查看视图创建指令
  DESC 视图名字;  #查看视图结构
  

• 视图修改：更改视图逻辑

  ALTER VIEW 视图名 AS 新的查询指令;  #更改视图
  CREATE OR REPLACE VIEW 视图名 AS 新的查询指令;  #创建或者替换为新的
  

• 视图删除

  DROP VIEW 视图名;
  

3.视图数据操作

视图数据操作 ：直接对视图进行写操作（增删改）然后实现基表数据的变化

• 视图的所有操作最终都是对基表进行操作
• 视图操作条件
  • 多基表视图：不允许操作
  • 单基表视图：允许增删改
    • 新增条件：视图字段必须包含基表中不允许为空的全部字段
  • WITH CHECK OPTION：操作检查规则
    • 默认不需要这个规则（创建视图时指定）：视图操作只要满足上述条件即可
    • 增加此规则：视图的数据操作后，必须要保证视图还能把通过视图操作的数据查出来（否则失败）

4.视图算法

视图算法 ：指视图在执行过程中对于内部的SELECT指令的处理方式

• 视图算法在创建视图时指定

  CREATE ALGORITHM = 算法 VIEW 视图名字 AS SELECT指令;
  

• 视图算法一共三种

  • UNDEFINED：默认的，未定义算法，即系统自动选择算法
  • MERGE：合并算法，就是将视图外部查询语句跟视图内部查询语句合并后执行，效率高（系统优先选择）
  • TEMPTABLE：临时表算法，即系统将视图的SELECT语句查出来先得到一张临时表，然后外部在查询（TEMPTABLE算法视图不允许写操作）

五、数据备份与还原

1.表数据备份

表数据备份 ：单独针对表里面的数据部分进行备份（数据导出）

• 将数据从表中查出，按照一定格式存储到外部文件中

  • 字段格式化：FIELDS
    • TERMINATED BY ：字段数据结束后使用的符号，默认空格
    • ENCLOSED BY ：字段数据包裹，默认什么都没有
    • ESCAPED BY ：特殊字符的处理，默认是转义
  • 行格式化：LINES
    • TERMINATED BY：行结束符号，默认是’\n’，自动换行
    • STARTING BY：行开始符号，默认为空

  SELECT */字段列表 INTO OUTFILE 外部文件路径
  [FIELDS TERMINATED BY 格式字符串 ENCLOSED BY 格式字符串 ESCAPED BY 格式字符串]
  [LINES TERMINATED BY 格式字符串 STARTING BY 格式字符串]
  FROM 数据表;
  

• 表数据备份不限制数据的来源是一张表还是多张表（可以连表）

2.表数据还原

表数据还原 ：将符合数据表结构的数据导入到数据表中（数据导入）

• 将一定格式的数据按照一定的解析方式解析成符合表字段格式的数据导入到表中

  • 字段处理
  • 行处理

  LOAD DATA INFILE '数据文件所在路径' INTO TABLE 表名
  [FIELDS TERMINATED BY 格式字符串 ENCLOSED BY 格式字符串 ESCAPED BY 格式字符串]  #与备份导出时意义相同
  [LINES TERMINATED BY 格式字符串 STARTING BY 格式字符串]  #与备份导出时意义相同
  [(字段列表)];  #如果是部分字段，需要字段列表
  

3.文件备份

文件备份 ：直接对数据表进行文件保留，属于物理备份

• 文件备份操作简单，直接将数据表（或者数据库文件夹）进行保存迁移
• MySQL中不同表存储引擎产生的文件不一致，保存手段也不一样
  • InnoDB: 表结构文件在ibd文件中，数据和索引存储在外部统一的ibdata文件中（MySQL7以前是frm文件）
  • Mylsam: 每张表的数据、结构和索引都是独立的文件，直接找到三个文件迁移即可
    • sdi: 表结构文件
    • MYI: 索引文件
    • MYD: 数据文件

4.文件还原

• 直接将上述备份文件放到对应位置

5.SQL备份

SQL备份 ：将数据库的数据以SQL指令形式保存到文件中，属于逻辑备份

• SQL备份是利用Mysqldump.exe客户端实现备份

• SQL备份是将备份目标（数据表）以SQL指令形式，从表的结构、数据和其他信息保存到文件（.sql）

  mysqldump.exe -h -P -u -p [备份选项] 数据库名字 [数据表列表]>SQL文件路径  //cmd等控制台，不是SQL语句
  

• 常见备份选项

  • 全库备份： --all-databases 所有数据库所有表，也不需要指定数据库名字
  • 单库备份：[--databases] 数据库 指定数据库里的所有表（不需要表名）
  • 部分表（单表）备份：数据库名 表1,...,表n

6.SQL还原

• SQL还原可以使用mysql.exe进行操作

  mysql.exe -h -P -u -p [数据库名]<SQL文件路径
  

• SQL还原还可以在进入到数据库后利用SQL指令还原

  SOURCE SQL文件路径;
  

六、用户管理

1.角色管理

角色管理 ：ROLE,即根据角色来分配权限，然后用户只需要关联角色即可（分配角色）：MySQL8以后才有

• 角色存在可以更方便用户维护多个具有相同权限的用户（核心价值）
• 角色相关操作和语法
  • 创建角色：CREATE ROLE 角色名字1,...,角色名字n（可批量创建）
  • 分配权限：GRANT 权限列表 ON */数据库.*/数据表 TO 角色名字
  • 绑定角色：GRANT 角色名字 TO 用户名@主机地址
  • 撤销角色：REVOKE 角色名字 FROM 用户名@主机地址
  • 回收角色权限：REVOKE 权限列表 ON */数据库.*/数据表 FROM 角色名字
  • 删除角色：DROP ROLE 角色名字1,...,角色名字n

2.权限管理

• 账号创建之初除了登录，没有其他任何权限

• 账号的管理通常需要配合权限使用

  • 赋权：GRANT 权限列表 ON */数据库.*/数据表 TO 角色名字
  • 回收：REVOKE 权限列表 ON */数据库.*/数据表 FROM 角色名字
  • 刷新权限：FLUSH PRIVILEGES
  • 查看权限：SHOW GRANTS FOR 用户名@主机地址

• MySQL提供的权限列表