三.数据库基础-MySQL入门到精通学习这一篇就够了（详细）

大云区人伍

已于 2023-05-15 09:35:49 修改

阅读量1k

点赞数 2

分类专栏： java启蒙之路文章标签： mysql

于 2022-04-15 17:11:29 首次发布

本文链接：https://blog.csdn.net/ThatsThePrice/article/details/124199359

版权

java启蒙之路专栏收录该内容

40 篇文章 8 订阅

订阅专栏

第一章简介

MySQL是一门不可不学的数据库开源技术。在我们实际工作中总能遇到。

第二章 Mysql的安装与配置

mysql8.0.25安装配置教程(windows 64位)最详细_聚精会神搞学习的博客-CSDN博客_mysql8.0.25安装

第三章数据库的基本操作

3.1 创建数据库
- 语法： CREATE DATABASE database_name;
- 查看数据库
  - 1使用命令 show databases 来查看当前所有数据库
  - 2使用 show create database database_name 来查看指定数据库定义。
3.2 删除数据库
- 语法：DROP DATABASE database_name;
3.3 Mysql 8.0新特性
- 1 系统表全部替换成事务型的InnoDB表。默认创建的表也为InnoDB存储引擎。

第四章数据表的基本操作

4.1 创建数据表
- 1语法：
  
  CREATE TABLE table_name
  
  {
  
  字段名字段类型 [列级约束] [默认值]，
  
  …
  
  [表级约束]
  
  }
- 2主键约束：主键约束包括唯一约束与非空约束。主键约束只能有一个。不允许有null值。
  - 写在字段声明之后：
    - 字段名字段类型 PRIMARY KEY [默认值]
  - 在所有列声明之后
    - [CONSTRAINT < 约束名>] PRIMARY KEY （字段名1，字段名2…）
- 3外键约束：
  - 在所有列声明之后，对应关联主表字段必须是主键，字段类型也必须兼容。
  - [CONSTRAINT < 约束名>] FOREIGN KEY 字段名 REFERENCES < 主表名> 主键列名
- 4非空约束：
  - 在列声明之后：
    - 字段名字段类型 NOT NULL
- 5唯一性约束：可以有null值，但只能有一个。
  - 在列声明之后：
    - 字段名字段类型 UNIQUE
  - 在所有列声明之后：
    - [CONSTRAINT <约束名>] UNIQUE (字段名)
- 6默认约束：
  - 在列声明之后：字段名字段类型 DEFAULT 默认值
- 7自增列
  - 一个表只能有一个自增列。默认初始值为1。可以是任何整数数据类型字段。
  - 字段名数据类型 AUTO_INCREMENT
  - 在插入数据时指定某值后，自增列之后数据按该值累加。
4.2 查看数据表
- 1 查看表基本结构：
  - 命令： DESCRIBE/DESC 表名；
- 2查看表详细结构：
  - 命令： SHOW CREATE TABLE 表名[ \G] 注：\G为调整显示格式。
4.3 修改数据表
- 1修改表名：ALTER TABLE < 旧表名> RENAME [ TO] < 新表名>；
- 2修改字段类型：ALTER TABLE table_name MODIFY < 字段名> < 字段类型>；
- 3修改字段名：ALTER TABLE table_name CHANGE < 旧字段名> < 新字段名> < 新数据类型>；
- 4添加字段：ALTER TABLE table_name ADD < 新字段名> < 新字段类型> [ 约束条件] [ FIRST | AFTER 已存在字段名]；
- 5删除字段：ALTER TABLE table_name DROP < 字段名>；
- 6修改字段的排列位置：ALTER TABLE table_name MODIFY < 字段1> < 数据类型> FIRST | AFTER < 字段2>；
- 7更改表的存储引擎：ALTER TABLE table_name ENGINE = < 新存储引擎名>；（MyISAM、InnoDB…）
- 8删除表的外键约束：ALTER TABLE table_name DROP FOREIGN KEY < 外键约束名>；
4.4 删除表
- 1删除有外键管理的表需先删除表的外键；
- 2 DROP TABLE [ IF EXISTS] 表1，表2…;
4.5 MySQL 8.0新特性
- 1从8.0开始，数据库的默认编码改为utf8mb4，避免页面开发时常用utf8编码而导致与数据库编码不一致问题。
  - 查看数据库的默认编码命令：SHOW VARIABLES LIKE ‘character_set_database’;
  - 查看数据表的默认编码命令：SHOW CREATE TABLE table_name;
- 2表自增变量优化为持久化。避免因数据库重启导致的自增变量冲突问题。

第五章数据类型和运算符

5.1数据类型介绍
- 1数值类型：
  - 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
  - 浮点小数类型：FLOAT、DOUBLE
  - 定点小数类型：DECIMAL
- 2日期时间类型：
  - YEAR、TIME、DATE、DATETIME、TIMESTAMP
- 3字符串类型：
  - CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET
5.1.1整数类型：
- 整数类型可以添加AUTO_INCREMENT自增约束
- TINYINT：1字节、SMALLINT：2字节、MEDIUMINT：3字节、INT：4字节、BIGINT：8字节。
- 创建表数据类型时，可以指定数据的显示范围如INT(10)，表示如果数值小于10位则由空格填充，如果数值位数大于10则完全保存。显示宽度与数据类型存储数据的范围无关。不指定时系统默认。
5.1.2小数类型：
- FLOAT：4字节、DOUBLE)：8字节、DECIMAL(M,D)：M+2字节
- DECIMAL实际是以串存放的，可能的最大取值范围与DOUBLE一样。
- FLOAT、DOUBLE也可以用(M,D)表示。M称为精度表示总共的位数，D称为标度表示小数位数。
- 数值超过指定精度范围会四舍五入。
5.1.3日期时间类型
- YEAR：1字节、TIME：3字节、DATE：3字节、DATETIME：8字节、TIMESTAMP：4字节
- YEAR表示年，可以用4位数字或2位数字表示。范围1901~2155。
- TIME表示时间，格式可以为：HH:MM:SS、HH:MM、D HH:MM、D HH、SS、HHMMSS
- DATE表示日期，格式可以为：YYYY-MM-DD、YYYYMMDD或YY-MM-DD、YYMMDD。使用2位数表示年时，由2位数值转换为对应年份。
  - 使用函数 CURRENT_DATE()、NOW()，可以获取当前日期
- DATETIME表示日期与时间，格式可以为 YYYY-MM-DD HH:MM:SS,年份也可是使用2位表示，格式也可以为YYYYMMDDHHMMSS等非规则格式。
- TIMESTAMP表示时间戳，格式与DATETIME一样，范围比之小。区别是TIMESTAMP存储时是以UTC世界标准时间保存的，更换时区后时间值会自动变更。
- MySQL允许不严格的语法，任何标点符号都可以作为日期、时间部分之间的间隔符。如：‘.’、’：‘、’@‘等
5.1.4文本字符串类型
- CHAR(M)：M字节、VARCHAR(M)：L+1字节、TINYTEXT：L+1字节、TEXT：L+2字节、MEDIUMTEXT：L+3字节、LONGTEXT：L+4字节、ENUM：1或2字节取决于枚举值数目，最大65535、SET：1/2/3/4/8字节取决于集合成员数目，最大64个成员。
- CHAR(M)：表示定长字符串，M表示字符串长度，当存储数据位数小于M时，右侧用空格补充。0<M<255。显示时尾部的空格将被删除。在不严格模式下超过M的位数会截断，严格模式会报错。
- VARCHAR(M)：变长字符串，M表示最大字符串长度，0<M<65535。实际存储长度为存储数组长度L+1。显示时尾部空格会保留。
- TEXT类型：保存非二进制字符串，如文章内容、评论等。不删除尾部空格。根据不同的存储空间分为4种：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。
- ENUM类型：枚举类型，在取值时只能取枚举中一个值。MySQL内部存储的是每个枚举值的从1开始的编号。NULL编号为NULL、’‘空字符串编号为0、其他字符按顺序编号。
  - 总有一个默认值，可以为NULL时为NULL，不可以为NULL时为第一个枚举值。
  - 插入时也可以直接指定枚举值的编号。
- SET类型：集合类型，取值时可以取集合中多个成员。MySQL内部也用编号表示。
  - 插入时会自动删除重复、自动排序。
  - 插入非集合字符会自动忽略。
5.1.5二进制字符串类型
- BIT(M)：大约(M+7)/8字节、BINARY(M)：M字节、VARBINARY(M)：L+1字节、TINYBLOB(M)：L+1字节、BLOB(M)：L+2字节、MEDIUMBLOB(M)：L+3字节、LONGBLOB(M)：L+4字节
- BIT(M)类型：位字段类型，1<M<64,M默认为1。存储时位数不够左边用0填充。
- BINARY(M)与VARBINARY(M)：定长类型与变长类型二进制字符串。BINARY(M)值不够长度时右边填充 ’/0‘ 补齐。
- BLOB类型：二进制大对象类型，与TEXT类似。
5.2 如何选择数据类型
- 1尽量符合存储数值的范围，不浪费空间。
- 2小数计算时需使用定长小数类型DECIMAL。
- 3CHAR为固定长度，所以它的处理速度不VARCHAR块，但浪费空间。对于InnoDB存储引擎，因该使用VARCHAR变长类型，因为InnoDB数据表存储格式不分固定长度与变长。
5.3 常见运算符介绍
- 1算数运算符：加+、减-、乘*、除/、求余%
- 2比较运算符：大于>、小于<、等于=、大于等于>=、小于等于<=、不等于!=、以及IN、BETWEEN AND 、IS NULL、GREATEST、LEAST、LIKE、REGEXP。
- 3逻辑运算符：求得的值均为1(TRUE)、0(FALSE)。非NOT 或！、逻辑与AND 或&&、逻辑或OR 或||、逻辑异或XOR
- 4位运算符：位与&、为或|、位非~、位异或^、左移<<、右移>>
- 运算符可以使用在where条件中，也可以使用在select中。
- MySQL可以直接使用select xxx；后面不加from子句。
- 1算数运算符：
  - 计算时遇到如除数为0等错误，返回结果为NULL，不报错。
- 2比较运算符
  - 相对返回1，不等返回0，与NULL比较返回NULL
  - 安全等于比较 <=> 可以判断是否为NULL
  - LEAST(n1,…)返回其中最小值，当有NULL存在时返回NULL
  - GREATEST(n1,…)返回其中最大值，当有NULL存在时返回NULL
  - IN、NOT IN 、BETWEEN AND 、LIKE 等比较运算符都可以使用在select子句中。
  - REGEXP匹配字符串，使用正则表达式。
    - ’^‘ 匹配以该字符后面的字符开头的字符串
    - ’$‘ 匹配以该字符后面的字符结尾的字符串
    - ’.‘ 匹配任何一个单字符串
    - ’[…]‘ 匹配在方括号内的任意字符。
    - ’*‘ 匹配零个或多个在它前面的字符。
- 3逻辑运算符
  - 判断时，使用0表示false，非零表示true
  - 注意短路
- 4位运算符
- 特殊符号：当需要输入特殊符号’ ’ ‘、‘ ” ’、‘ \ ’ 等，可是使用转义字符 ‘ \ ’
- 字符串比较函数默认是不区分大小写的。需要区分时可以在字符串前面添加 BINARY 关键字。

第六章 MySQL函数

1数学函数
- 1绝对值函数ABS(x) 返回圆周率函数 PI()
- 2平方根函数 SQRT(x) 求余函数 MOD(x,y)
- 3向上取整CEIL(x)、CEILING(x) 向下取整FLOOR(x)
- 4获取随机数RAND()、RAND(x)
- 5四舍五入ROUND(x)、ROUND(x,y) y值取负值时，表示保留的小数点左边的相应位数直接保存为0，不进行四舍五入
  - TRUNCATE(x,y) 截取
- 6返回参数的符号 SIGN(x)，负值、0、正值返回 -1、0、1
- 7返回x的y次方POW(x,y)、POWER(x,y)。返回e的x次方EXP(x)
2字符串函数
- 1CHAR_LENGTH(str)返回字符串str包含的字符个数。LENGTH(str)返回字符串的字节长度，使用utf8一个汉字3个字节。
- 2CONCAT(s1,s2…)合并字符串。CONCAT_WS(x,s1,s2…)合并字符串，参数x表示分隔符。
- 3INSERT(s1,x,len,s2)返回s1从起始位置x开始的len个字符替换为s2。
- 4LOWER(str)、LCASE(str)全部转换为小写。UPPER(str)、UCASE(str)全部转换为大写。
- 5LEFT(s,n)获取s字符串的左边n位。RIGHT(s,n)获取字符串s的右边n位。
- 6LPAD(s1,len,s2)将s1填充到len位，左边填充s2。RPAD(s1,len,s2)
- 7LTRIM(S)删除字符串s左边的空格。RTRIM(s)、TRIM(s)
- 8TRIM(s1 from s)删除字符串s两端的字符s1
- 9REPEAT(s,n)返回一个由重复的字符串s组成的字符串。n为重复次数。
- 10空格函数SPACE(n)、替换函数REPLACE(s,s1,s2)
- 11比较字符串大小函数STRCMP(s1,s2)
- 12获取子串的函数 SUBSTRING(s,n,len)和MID(s,n,len)。n可以为负值。
- 13匹配子串开始位置LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)，返回子串str1在str中的位置
- 14字符串逆置 REVERSE(s)
- 15返回指定位置的字符串 ELT(n,s1,s2,s3)，n为第几个字符串
- 16返回指定字符串位置 FIELD(s,s1,s2,…)，返回字符串s在列表s1,s2…中出现的位置。
- 17返回子串的位置 FIND_IN_SET(s1,s2) 返回s1在s2出现的位置。
- 18选取字符串 MAKE_SET(x,s1,s2…)按x的二进制数从s1,s2…中选取字符串。
3日期和时间函数
- 1获取当前日期 CURDATE()、CURRENT_DATE()。获取当前时间 CURTIME()、CURRENT_TIME()
- 2获取当前的日期和时间 CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()
- 3获取月份 MONTH(date)、MONTHNAME()
- 4获取星期 DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)
- 5获取星期数 WEEK(d)、WEEKOFYEAR(d),返回日期d是一年中第几周。
- 6获取天数 DAYOFYEAR(d)、DAYOFMONTH(d)
- 7获取年份 YEAR(d)、季度 QUARTER(d)、小时、分钟 MINUTE(t)、秒 SECOND(t)
- 8获取日期的指定值 EXTRACT( type FROM date)，type可以是 YEAR、YEAR_MONTH、 DAY_MINUTE
- 9时间和秒转换 TIME_TO_SEC(t) 将时间t转换为秒。 SEC_TO_TIME(t)将秒s转换为时间。
- 10计算日期和时间
  - DATE_ADD(date,INTERVAL expr type)、ADDDATE(date,INTERVAL expr type) 日期加
  - DATE_SUB(date, INTERVAL expr type)、SUBDATE(date, INTERVAL expr type) 日期减
  - ADDTIME(date,expr) 给date添加expr，expr是一个格式化时间表达式。
  - SUBTIME(date,expr) 给date减去expr。
  - DATEDIFF(date1,date2)返回date1和date2之间的天数。
- 11格式化日期和时间
  - DATE_FORMAT(date,format)
  - TIME_FORMAT(time,format)
  - GET_FORMAT(val_type,format_type)返回日期时间字符串的格式。
4条件判断函数
- 1 IF(expr,v1,v2) 如果expr为真返回v1，否则返回v2
- 2 IFNULL(v1,v2) 如果v1不为NULL则返回v1，否则返回v2
- 3 CASE expr WHEN v1 THEN r1… ELSE rn END
5系统信息函数
- 1获取MySQL版本号 VERSION()、连接数 CONNECTION_ID()、数据库名 DATEBASE()、SCHEMA()
  - 命令 show processlist 显示有哪些线程在运行。
- 2获取用户名 USER()、CURRENT_USER、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
- 3获取字符串的字符集和排序方式 CHARSET(str)、COLLATION(str)
- 4返回最后一个生成的自增值 LAST_INSERT_ID()，不能确定返回某个表的，只是系统最后一个自增列改变的自增值。
6 MySQL新特性
- 加密函数 MD5(str)、SHA(str)、SHA2(str,hash_length)
7其他函数
- 1格式化函数 FORMAT(x,n) 将数字x格式化，四舍五入保留n位。
- 2不同进制的数字转换 CONV(N,from_base,to_base)
- 3IP地址与数字相互转换 INET_ATON(expr)、INET_NTOA(expr)
- 4加锁函数和解锁函数 GET_LOCK(str,timeout)、IS_FREE_LOCK(str)、IS_USED_LOCK(str)、RELEASE_LOCK(str)。对字符串加锁。
- 5重复执行指定操作 BENCHMARK(cont,expr) 重复count次执行表达式expr。一般用于计算MySQL处理速度。
- 6改变字符集 CONVERT(str USIGN 字符集名称)
- 7改变数据类型 CAST(x AS type)
8MySQL新特性-窗口函数
- 如Oracle的 row_number() over(partition by c1 order by c2 asc|desc ) row

第七章查询数据

1基本查询语句 SELECT
- LIMIT [< offset>,]< row count> 告诉MySQL每次显示查询出来的数据条数。
- 带GROUP BY 的语句中，select 子句可以使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来。
- 在GROUP BY 子句中可是加上 WITH ROLLUP，在所有记录后增加一条记录，显示所有记录的总和。不可以与 ORDER BY 一起使用
- LIMIT [位置偏移量，] 行数。第一条数据偏移量为0。LIMIT 4 OFFSET 3 与 LIMIT 4,3 意思相同。
2集合函数
- COUNT(*)汇总所有行，包括空值行。
- COUNT(字段名) 忽略空值行。
3连接查询
- 内连接 INNER JOIN
- 外连接
  - 左连接 LEFT JOIN
  - 右连接 RIGHT JOIN
4子查询
- 操作符可以使用 ANY(SOME)、ALL、IN、EXISTS
- 运算符可以是 <、>、<=、>=、!=等
5使用正则表达式查询
- 关键字 REGEXP
- 字符匹配列表
6MySQL8 新特性
- 1GROUP BY 不再进行隐性排序。
- 2通用表达式：简称CTE，命名临时结果集，作用范围是当前语句。
  - CTE可以引用其他CTE包括自己。
  - 语法：WITH [RECURSIVE] cte_name [(col_name [,col_name] …)] AS (subquery)
  - 相比于子查询，CTE的效率更高，因为非递归的CTE只会查询一次。
  - 递归CTE:引用自己的CTE,WITH子句必须以WITH RECURSIVE 开头。

第八章插入、更新与删除数据

1插入数据
- INSERT INTO table_name (column_list) VALUSE (value_list);
- 允许省略 column_list，则value_list中字段顺序必须与表定义顺序相同。
- MySQL允许同时插入多条数据，使用多个 (value_list)来实现。效率比单条插入高。且允许部分提交。
- 可以使用 SELECT子句代替value_list。
2更新数据
- UPDATE table_name SET col1=value1,col2=value2… WHERE (condition);
3删除数据
- DELETE FROM table_name [WHERE < condition>];
- TRUNCATE TABLE table_name;
表的计算列
- 语法：列名类型 [GENERATED ALWAYS] AS 计算表达式 [VIRTUAL | STORED] [UNIQE [KEY]] [COMMENT comment] [NOT NULL | NULL] [ [PRIMARY] KEY]
MySQL8新特性
- 1InnoDB表的DDL操作支持原子性。

第九章索引

所有MySQL列类型都可以被索引，索引是实现在存储引擎中的，所以不同存储引擎索引不一定相同。MySQL支持两种索引BTREE和HASH，InnoDB只支持BTREE，MEMORY/HEAP支持HASH和BTREE。
- 普通索引和唯一索引
- 组合索引
- 全文索引：索引类型为FULLTEXT，可以在CHAR、VARCHAR、TEXT类型上使用，只有MyISAM存储引擎支持。
- 空间索引：对空间数据类型建立的索引。空间字段类型有GEOMETRY、POINT、LINESTRING、POLYGON。只有MyISAM支持。
创建索引
- 语法：
  - 1在 CREATE TABLE 中指定索引列
    - CREATE TABLE table_name(
      
      [col_name data_type]
      
      [UNIQUE |FULLTEXT |SPATIAL] [INDEX |KEY] [index_name] (col_name [length]) [ASC |DESC]
      
      )
    - UNIQUE、FULLTEXT、SPATIAL表上唯一索引、全文索引、空间索引
    - length表上索引的长度。 ASC|DESC 表示索引值存储顺序。
    - 可以使用命令 EXPLAIN select语句来查看索引是否被使用。
    - 组合索引使用“最左前缀”方式匹配是否使用索引。
  - 2使用 ALTER TABLE在表上创建索引
    - ALTER TANBLE table_name ADD 与创建表时相同
    - 可以使用 SHOW INDEX table_name 来查看表索引
  - 3使用 CREATE INDEX语句创建索引
    - CRTETA [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name [length]) [ASC |DESC]
    - CREATE INDEX 语句会映射到一个ALTER TABLE 语句上。
删除索引
- ALTER TABLE table_name DROP INDEX index_name;
- DROP INDEX index_name;
- 自增字段的唯一索引不能删除
MySQL8新特性
- 1支持降序索引
- 2统计直方图
  - 分为等宽直方图、等高直方图
  - 语法：ANALYZE TABLE table_name
    
    [UPDATE HISTOGRAN on col_name with N BUCKETS
    
    |DROP HISTOGRAM ON col_name]
    - BUCKETS 默认值100，范围0~1024。
  - 直方图能近似的获得一列数据非分布情况，从而帮助查询优化器找到更好的执行计划。
  - 统计直方图信息存储在数据字典“column_statistcs”中，可以通过视图information_schema.COLUMN_STATISTICS访问。

第十章存储过程和函数

1创建存储过程
- 语法：CREATE PROCEDURE sp_name ( [proc_parameter])
  
  [characteristics …] rountine_body
  - 其中 proc_parameter表示参数列表可以是 IN |OUT |INOUT 类型
  - characteristics表示存储过程的特性:
    - LANGUAGE SQL:说明rountine_body 部分有SQL组成。
    - [NOT] DETERMINISTIC：表明存储过程执行的结果是否正确。同样参数每次返回同样结果即为正确。
    - {CONTAINS SQL |NO SQL |READS SQL DATA |MODIFIES SQL DATA}:指明子程序使用SQL语句的限制。
    - SQL SECURITY {DEFINER |INVOKER}：值明由谁来执行存储过程。
    - COMMENT’string’:注释信息。
  - rountine_body 是主体信息，可以由BEGIN …END 包括。
- 命令 DELIMITRE // 表示将//做为语句结束符（代替；）。
2创建函数
- 语法：CREATE FUNCTION fun_name( [fun_parameter])
  
  RETURNS type
  
  [characteristic …] routine_body
  - fun_parameter表示参数，可以是 IN |OUT |INOUT 。但只有IN是合法的。
  - RETURNS 必须指定，表示返回类型，type可以说数据库任意类型。
  - characteristic与存储过程一样。
3变量的使用
- 1定义变量
  - 语法：DECLARE var_name[,var_name …] data_type [DEFAULT value];
  - 变量值除了可以是常数外，也可以是个表达式。默认为NULL。
- 2为变量赋值
  - SET var_name = expr [, var_name = expr] …;
  - 也可以使用select …into 方式为变量赋值。
4定义条件和处理程序
- 可以定义错误或者子程序的一般流程控制。保证程序遇到错误等情况可以正常运行。
- 1定义条件
  - 语法： DECLARE condition_name CONDITION FOR [condition_type]
    - 其中condition_type格式为：SQLSTATE [VALUE] sqlstate_value |mysql_error_code
    - sqlstate_value为长度5的字符串类型错误代码，mysql_error_code为数值类型错误代码。
- 2定义处理程序
  - DECLARE {CONTONUE |EXIT |UNDO} HANDLER FOR condition_value[, …] sp_statement
  - 其中condition_value为：SQLSTATE [VALUE] sqlstate_value |condition_name |SQLWARNING |NOT FOUND |SQLEXCEPTION |mysql_error_code
  - CONTONUE表示语句错误不处理。EXIT表示直接推出。UNDO表示回滚，但MySQL暂不支持
  - SQLWARNING表示01开头的错误码。NOT FOUND 表示02开头的错误码。SQLEXCEPTION表示剩余其他错误码。
- @var_name 表示用户变量，使用SET赋值，用户变量与连接有关。
5光标的使用
- 1声明光标：DECLARE cursor_name CURSOR FOR select_statement;
- 2打开光标：OPENT cur_name;
- 3使用光标：FETCH cur_name INTO var_name [,var_name …]
- 4关闭光标：CLOSE cur_name;
- MySQL光标只能在存储过程中使用
6流程控制的使用
- 1 IF语句： IF expr THEN statement_list [ELSEIF expr THEN statement_list] …[ELSE statement_list]
  - 是否为空用 val is null 来判断
- 2 CASE语句：
  - CASE case_expr
  WHEN when_value THEN sta_list …
  
  [ELSE sta_list]
  
  END CASE;
  - CASE
    
    WHEN expr THEN sta_list;
    
    …
    
    [ELSE sta_list]
    
    END CASE;
- 3 LOOP 语句
  - [loop_label:] LOOP
    
    statement_list
    
    END LOOP [loop_label];
- 4 LEAVE语句：用来推出任何被标注的流程控制构造。
  - 语法：LEAVE label;
- 5 ITERATE语句：将执行顺序转到语句段开头处，再次循环。如continue。
  - 语法：ITERATE label;
- 6 REPEAT语法：创建一个带条件的循环。如do…while
  - [repeat_label:] REPEAT
    
    statement_list
    
    UNTIL expr
    
    END REPEAT [repeat_label];
- 7 WHILE 语句
  - [while_label:] WHILE expr DO
    
    statement_list
    
    END WHILE [while_label];
7调用存储过程和函数
- 调用存储过程：CALL
- 调用函数：与预定义函数一样。
8查看存储过程和函数
- 1使用 SHOW STARUS 查看存储过程和函数的状态
- 2使用 SHOW CREATE 查看存储过程和函数的定义
- 从information_schema.Routines表查看存储过程和函数的信息。
9修改存过程和函数，只能修改特性。修改主体内容需要删除后重建。
- 使用ALTER语句修改特性。
10删除：DORP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;
11MySQL8新特性：
- 1全局变量持久化，重启服务不会导致全局变量变为默认值。
  - 使用 SET GLOBAL var_name = value;来定义全局变量。临时生效，重启后会从配置文件中取默认值。
  - 使用 SET PERSIST var_name = value;来定义的全局变量，重启后值不会变。
  - 使用 show max_connections 来查看全局变量。

第十一章视图

1创建视图
- 语法：
  
  CREATR [OR REPLACE] [ALGORITHM = {UNDEFINED |MERGR |TEMPTABLE}]
  
  VIEW view_name [(column_list)]
  
  AS SELECT_statement
  
  [WITH [CASCADED |LOCAL] CHECK OPTION]
  - ALGORITHM表示视图的算法
  - UNDEFINED 表示由MySQL自动选择算法
  - MERGE 表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分。
  - TEMPTABLE 表示将视图的结果存入临时表，然后用临时表来执行语句。
  - WITH …CHECK OPTION 表示视图在更新时是否保证视图的权限范围之内
    - CASCADED 表示更新视图时需满足所有视图和表的条件
    - LOCAL 表示更新视图时只需要满足视图本身定义的条件。
  - 视图可以带有传入参数。
  - 对单表查询视图，可以执行INSERT/UPDATE/DELETE操作。
2查看视图
- 1使用DESCRIBE 语句查看视图基本信息。
- 2使用 SHOW TABLE STATUS 语句查看视图基本信息。
- 3 使用 SHOW CREATE VIEW 语句查看视图定义。
- 4 在 information.views库中查看所有视图定义。
3修改视图
- 1使用 CREATE OR REPLACE 修改视图
- 2使用 ALTER语句修改视图
  - 语法：
    
    ALTER [ALGORITHM = {UNDEFINED |MERGE |TEMPTABLE}]
    
    VIEW view_name [(column_list)]
    
    AS SELECT_statement
    
    [WITH [CASCADED |LOCAL] CHECK OPTION]
4更新视图，可以对视图进行更新操作。一下情况不行。
- 1视图中不包含基表中被定义为非空的列。
- 2select子句中使用了数学表达式
- 3select子句中使用了聚合函数
- 4select子句中使用了 DISTINCT/UNION/TOP/GROUP BY/HAVING
5删除视图：DROP VIEW

第十二章 MySQL触发器

MySQL触发器只有行级触发器。
1创建触发器
- 语法：
  
  CREATE TRIGGER trigger_name before |afler INSERT |UPDATE |DELETE
  
  ON tbl_name FOR EACH ROW trigger_stmt
  - 当trigger_stmt内容只有一条时，可以省略begin…end
- 在触发器中，可以使用NEW.xxx表示新数据、OLD.xxx表示老数据
2查看触发器
- 1使用 SHOW TRIGGERS 语句查看所有触发器信息
- 2使用 information_schema.triggers表查看触发器定义。
3删除触发器：DROP TRIGGER trigger_name

第十三章 MySQL权限与安全管理

存储账户权限信息的表主要是user、db、host、tables_priv、columns_priv和procs_priv。
1user表：记录允许连接到服务器的账户信息，里面的权限是全局级的。
- 用户列：记录用户账户信息，包括Host、User、authentication_string。修改密码时就是修改authentication_string字段。
- 权限列：决定用户的权限。字段类型为ENUM，取值为Y和N。修改权限可以使用GRANT语句或UPDATE更新user表。
- 安全列：用于安全验证。
- 资源控制列：用来限制用户资源。
  - 1max_questions 用户每小时允许执行的查询操作次数。
  - 2max_updates ~~更新次数。
  - 3max_connections ~~连接操作次数。
  - 4max_user_connections 用户允许同时建立的连接次数。
2db表：存储了用户对某个数据库的操作权限。
- 用户列：Host、User、Db，这3个字段是db表的主键。
- 权限列：create_routine_priv 和 alter_routine_priv表名用户是否有创建和修改存储过程的权限。等其他权限列。
3tables_priv表和columns_priv表
- tables_priv用于对表设置权限。columns_priv用于对列设置权限。
4procs_priv表：可以对存储过程和存储函数设置权限。
5权限管理
- 1登陆退出MySQL服务器
  - 使用mysql命令登陆服务器。如： mysql -h localhost -u root -p mysql -P 3360 -e “DESC person;”
  - 使用 -e时，执行完-e后命令会自动退出mysql服务器。
- 2新建普通用户
  - 1使用CREATE USER语句创建用户
    - CREATE USER user_specification [, user_specification] …
      
      user_specification:
      
      user@host [
      
      INENTIFIED BY [PASSWORD] ‘password’
      
      | INENTIFIED WITH auth_plugin [AS ‘auth_string’]
      
      ]
    - INENTIFIED WITH auth_plugin 表示为用户指定一个身份认证插件。
    - 使用CREATE USER后会在user表新加一条记录，但没有任何权限。添加已存在用户时会报错。
    - 若指定用户登陆不需要密码，则可以省略INENTIFIED BY部分。
  - 2直接操作user表。使用INSERT语句添加用户。
- 3删除普通用户
  - 1使用DROP USER命令删除用户
    - DROP USER user [,user…];
    - DROP USER; 可以删除来自所有授权表的账户权限记录。
  - 2直接操作user表，使用DELETE语句。
- 4root修改自己密码
  - 可以通过直接修改user表修改root密码，修改后需执行命令 FLUSH FRIVILEGES来重新加载用户信息。
- 5root用户修改普通用户密码
  - 1使用命令 SET
    - SET PASSWORD FOR ‘user’@‘localhost’ = ‘123’;
  - 2使用UPDATE语句修改user表。
  - 修改密码后需执行 FLUSH FRIVILEGES命令，重新加载用户。
6权限管理
- 主要控制用户在数据库上的 SELECT、INSERT、UPDATE、DELETE权限。
- 权限信息主要在表user、db、host、tables_priv、columns_priv和procs_priv中，可以使用GRANT、REVOKE授权和收回权限。
- 1授权
  - 1全局权限：GRANT ALL ON * .* 和REVOKE ALL ON * .*
  - 2数据库层级：GRANT ALL ON db_name.xx 和REVOKE ALL ON db_name. xx
  - 3表层级：GRANT ALL ON db_name.tbl_name 和 REVOKE ALL ON db_name
  - 4列层级：GRANT ALL ON db_name.tbl_name.col_name 和 ~~
  - 子程序级：
    - CREATE ROUTINE、ALTER ROUTINE、EXECUTE、GRANT权限适用于已存储的子程序。这些权限可以被授权为全局层级和数据库层级。
    - 除CREATE ROUTINE外，这些权限也可以被授予子程序级。
    - GRANT priv_type [(columns)] [, priv_type [(columns)]] …
      
      ON [TABLE |FUNCTION |PROCEDURE] tbl1,tbl2…
      
      TO user [WITH GRANT OPTION |…]
      - priv_type 表示权限类型。columns表示授权的列
      - WITH 后可以更多个参数
        GRANT OPTION表示权限可以授权给别人。
        MAX_QUERIES_PER_HOUR count:设置每小时可以执行count次查询。
        MAX_UPDATES_PER_HOUR count:~~count次更新。
        MAX_CONNECTIONS_PER_HOUR count: ~~count个连接
        MAX_USER_COUNTECTIONS count:每个用户同时可以建立count个连接。
- 2收回权限
  - 1使用REVOKE语句收回权限
    - REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘user’@‘host’ [, ‘user’@‘host’ …] 收回所有权限。
    - REVOKE priv_type [(columns)] [,priv_type [(columns)]] …
      
      ON tablel1,table2…
      
      FROM ‘user’@‘host’ [,‘user’@‘host’ …] 收回指定权限。
- 3查看权限
  - SHOW GRANTS FOR ‘user’@‘host’ ;
7访问控制
- 1连接核实阶段。核实账号密码使用user表。
- 2请求核实阶段。核实权限。
8提升安全性
- 18.0新增。AES 256加密
  - 加密函数 AES_ENCODE(str,秘钥)
  - 解密函数 AES_DECODE(str,秘钥)
- 28.0新增。密码到期更换策略
  - 通过设置 user.password_lifetime参数控制。
  - ALTER USER root@localhost PASSWORD EXPIRE INTERVAL 260 DAY;
- 3 8.0新增。安全模式安装。提升MySQL安装的安全性，可以通过
  - 1为ROOT用户设置密码。
  - 2移除能从本地主机以外地址能访问数据库的root账户。
  - 3移除匿名账户
  - 4删除test数据库。
- 4 8.0新增。管理角色。角色可以看做时权限的集合，可以赋予用户。
  - 1创建角色：CREATE ROLE role_name;
  - 2给角色授权：GRANT priv_type ON db_name TO role_name;
  - 3将角色赋予用户：GRANT ‘role_name’ TO ’user‘@‘host’
  - 4收回角色权限：REVOKE priv_type ON db_name FROM ‘role_name’;
  - 5删除角色：DROP ROLE role_name;

第十四章数据备份与恢复

1数据备份
- 1使用MySQLdump命令备份
  - mysqldump -u user -h host -p password dbname [tbname…] > filename.sql
  - 备份文件中包含多个CREATE和INSERT语句。
  - 可以在 dbname前加参数 --database 来备份多个数据库，默认不加时只生成INSERT文件。或加 --all-database 备份所有数据库
- 2直接复制整个数据库目录
  - 复制前需要对表执行LOCK TABLES锁表操作，然后对表执行FLUSH TABLES将所有激活的索引页写入硬盘。
  - 对InnoDB表不适用。
- 3使用MySQLhotcopy工具快速备份
  - mysqlhotcopy是一个PERL程序，它使用LOCK TABLES 、FLUSH TABLES 和cp、scp来快速备份数据库。其逻辑也是将数据库文件目录复制保存。
  - mysqlhotcopy db_name_1,db_name_2 … /path
  - 只能在数据库目录所在的机器上运行，且只能备份MyISAM表。
2数据恢复
- 1使用mysql命令恢复。mysql命令可以直接执行备份的create、insert、drop命令。
  - mysql -u user -p [db_name] < filename.sql
  - 2如果已经登陆服务器，可以直接执行 source命令导入sql文件。执行前需使用user选定数据库。
    - source filename
- 2直接复制到数据库目录
  - 1备份数据库需和恢复数据库主版本号相同。
  - 2对于InnoDB表不适用。
- 3MySQLhotcopy快速恢复
  - 将备份文件复制到MySQL数据文件位置。需停止服务器。
  - chown -R mysql.mysql /var/lib/mysql/dbname 如果以根用户执行，必须指定数据库文件的所有者。
  - cp -R /user/back/test user/local/mysql/data
  - 如果复制前数据库已存在，则需要先删除该数据库。
3数据库迁移
- 1相同版本的数据库迁移，直接在新数据库上恢复mysqldump备份文件即可。或直接复制数据库文件到新地址，但不支持InnoDB表。
  - mysqldump -h host1 -u root -p password dbname | mysql -h host2 -u root -p password。将数据从host1迁移到host2。
  - 迁移所有数据库可以使用 --all–database
- 2不同版本的MySQL数据库之间的迁移
  - 从旧版本到新版迁移，也之间备份后恢复就可以。主要新版本的新特性。
- 3不同数据之间的迁移
  - 需要注意不同数据库间的差异，修改对应脚本。MySQL官方提供了数据迁移工具MySQL Migration Toolkit。
4表的导出和导入
- 1使用SELECT … INTO OUTFILE导出文本文件
  - 只有在数据库所在服务器上使用。
  - select colu_list FROM table_name WHERE condtion INTO OUTFILE ‘filename’ [OPTIONS]
    - options为可选参数，包括FIELDS子句和LINES子句
    - FIELDS TERMINATED BY ‘value’:设置字段之间的分隔字符。
    - FIELDS [OPTIONALIY] ENCLOSED BY ‘value’:设置字段的包围字符。
    - FIELDS ESCAPED BY ‘value’:设置如何写入和读写特殊字符。
    - LINES STARTTING BY ‘value’:设置每行数据开头的字符。
    - LINES TERMINATED BY ‘value’:设置每行数据结尾的字符。
  - 如果想在服务器之外的客户机上创建结果文件，不能使用select …into outfile 可以使用: mysql -e “select …” > file_name
  - MySQL默认对导出目录有限制。
    - show global variables like ‘%secure%’; 查看导出文件目录
    - 可以修改my.ini配置文件中 secure-file-priv参数，修改导出目录。
- 2使用MySQLdump命令导出文本文件
  - mysqldump命令不仅可以备份数据库和表，生成create和insert语句。还可以生成create和数据文本文件。
  - mysqldump -T path -u root -p dbname [table] [option]
    - option参数效果与select…into outfile 差不太多。
- 3使用mysql命令来导出文本文件
  - mysql -u root -p --execute=“SELECT …” dbname > filename.txt
    - –execute 表示执行完自动退出
    - 可以加 --vartical格式显示数据
    - 加 --html为导出html文件
    - 加 --xml为导出xml文件
- 4使用LOAD DATA INFILE方式导入文本文件
  - LOAD DATA INFILE ‘filename.txt’ INTO TABLE tablename [OPTIONS] [IGNORE number LINES]
  - IGNORE 表示忽略文件开始的行数
- 5使用MySQLimport命令导入文本文件
  - mysqlimport -u root -p dbname filename.txt [OPTIONS]

第十五章 MySQL日志

MySQL日志分为4类
- 1错误日志：记录MySQL服务器启动、运行、停止时出现的问题。
- 2查询日志：记录客户端连接和执行的语句。
- 3二进制日志：记录所有更改数据的语句，可用于数据复制。
- 4慢查询日志：记录所有执行时间超过long_query_time的查询或不使用索引的查询。
1二进制日志
- 事务安全的，以统一的事务提交。
- 默认是开启的，可以通过修改MySQL的配置文件来启动和设置二进制文件
  - my.ini中[MySQLd]组下面关于二进制文件的设置：
  - log-bin [=path/ [filename]]
  - expire_logs_days定义了清除过期日志的时间。默认为0，不自动删除。
  - max_binlog_size定义了单个文件的大小限制。
- 使用SHOW VARIABLES语句查询日志设置。
- 数据库文件最好不要和日志文件放在同一个磁盘下，为了放在磁盘损坏。
- SHOW BINARY LOGS语句可以查看二进制文件个数和文件名。可以使用MySQLbinlog命令查看日志内容。
- 使用RESET MASTER语句删除所有二进制日志。
- 使用PURGE MASTER LOGS语句删除指定二进制日志。
- 可以使用MySQLbinlog工具以二进制文件恢复数据库。
  - mysqlbinlog [option] filename | mysql -u user -p pass
- 通过SET sql_log_bin = {0|1}来暂停和启动二进制日志。
2错误日志
- 1启动和设置错误日志
  - 默认是启动的。
  - 文件名默认是 hsstname.err。可以执行FLUSH LOGS重新加载。
  - 启动和停止都可以通过修改my.ini文件实现
    - [mysql]
      
      log-error=[path / [file_name]]
- 2查看错误日志：SHOW VARIABLES like ‘log_error’;
- 3删除错误日志：flush logs可以重建日志，5.5之前会备份之前日志，5.5之后会覆盖之前日志。
3通用查询日志
- 默认是关闭的，通过SHOW VARIABLES LIKE '%general%'查看日志状态。
- 启动日志： set @@global.general_log =1;
- 关闭：set @@global.general_log = 0;
- 日志文件可以直接打开查看内容。
- 可以用直接删除日志文件的方式删除通用查询日志。可以用过MySQLadmin-flush logs重建日志。
4慢查询日志
- 默认是关闭的。可以同修改my.ini或my.cnf中的log-slow-queries选项打开，或MySQL启动的时候用 --log-slow-queries[=file_name]启动。
  - my.ini或者my.cnf中可以配置long_query_time选项指定记录阈值。
- 日志文件可以直接打开查看内容。可以使用慢查询分析工具分析，如：MySQL Dump Slow。
- 文件可以直接删除，也可以使用flush logs重新生成。

第十六章性能优化

1数据库优化原则：减少系统的瓶颈、减少资源的占用、增加系统的反应速度。如：优化文件系统，提供磁盘读写数据、优化操作系统调度策略，提高负载能力、优化表、索引、查询语句，提高响应速度。
2优化查询：
- 1使用 EXPLAIN语句、DESCRIBE语句查看select语句执行情况。
- 2使用索引查询
  - 1使用 LIKE 条件查询时，实行左匹配原则，不要把“%”写在字符串最左边
  - 2对于多列索引，使用索引符合“左前缀”原则。
  - 3当查询条件中只有OR时，当OR两端的字段都有索引时才会使用索引。
- 3子查询虽然灵活，但效率不高。应该使用表连接来替换。
3优化数据库结构
- 1将字段很多的表分解为多个表。把不常用的字段单调列出，提高效率。
- 2对经常需要联合查询的表，建立中间表。
- 3合理的增加冗余字段，减少表关联。
4优化插入记录速度
- 对于MyISAM引擎来说：
- 1禁用索引： ALTER TABLE table_name DISABLE KEYS;
  - 开启索引：ALTER TABLE table_name ENABLE KEYS;
- 2禁用唯一性检查：SET UNIQUE_CHECK = 0;
  - 开启唯一性检查：SET UNIQUE_CHECK = 1;
- 3使用批量插入语句
- 4使用LOAD DATA INFILE 批量导入
  - 比INSERT快
- 对于InnoDB来说:
- 1禁用唯一性检查
- 2禁用外键检查：SET FOREIGN_KEY_CHECKS = 0;
- 3禁用自动提交：SET AUTOCOMMIT = 0;
5分析表、检查表、优化表
- 1分析表：分析关键字的分布。
  - ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]…
  - LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名，二则都是执行过程不写入二进制日志。
  - 分析表会对表增加只读锁。可以分析InnoDB、BOB、MyISAM类型的表。
- 2检查表：检查表中是否存在错误。
  - CHECK TABLE tbl_name [,tbl_name] … [QUICK |FAST |MEDIUM |EXTENDED |CHANGED] …
  - 检查过程会加只读锁，只对MyISAM类型有效。
- 3优化表:消除删除或更新造成的空间浪费
  - OPTIMIZE [LOCAL |NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name] …
  - 对于使用了TEXT或者BLOB类型的字段，删除更新后应该使用 OPTIMIZE清理磁盘碎片。或对于可变长字段，进行了很多的更新后。平常使用不需要经常清理碎片，一周一次或一月一次即可。
6优化MySQL服务器。
7临时表性能优化：
- 8.0以后，可以使用CREATE TABLESPACE来创建通用表空间。MySQL8.0优化了临时表，其将使用单独的表空间。
- 去掉了不必要的表持久化、移除了不必要的日志、改变了缓存和锁。
- 临时表在连接断开或数据库关闭时会进行删除。
8服务器语句超时处理
- 8.0以后，可以设置服务器语句的超时限制，单位时毫秒。
- 通过设置系统变量max_execution_time实现：
  - SET GLOBAL MAX_EXECUTION_TIME =2000;
    - 默认情况下为0表示没有限制。
  - SET SESSION MAX_EXECUTION_TIME = 2000;
    - 会话的超时时间
9创建全局通用表空间
- 8.0以后，支持全局通用表空间。可以被所有数据库表共享。
- CREATE TABLESPACE dxy ADD detafile ‘dxv.ibd’ file_block_size =16k;
- 删除全局通用表空间时，需删除所有使用该表空间的表。
10 MySQL8.0新特性
- 不可见索引用于性能调试非常有用。可以隐藏和显示索引。可以在创建表时定义或使用alter语句修改。
  - 1隐藏：invisible。隐藏后索引也会更新。
  - 2显示：visible
- 资源组功能可以用于调控线程优先级和绑定CPU
  - 用户需要有RESOURCE_GROUP_ADMIN权限才能使用
  - 在Linux下用户需要有CAP_SYS_NICE权限才能使用
  - 默认的资源组：USR_DAFAULT和SYS_DAFAULT