Mysql8基础知识

系统表都变为InnoDb表

从MySQL 8.0开始，系统表全部换成事务型的InnoDB表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表

基本操作

创建数据表的语句为CREATE TABLE

查看表基本结构语句DESCRIBE：DESCRIBE 表名 / DESC 表名

• NULL：表示该列是否可以存储NULL值。
• Key：表示该列是否已编制索引。
• PRI表示该列是表主键的一部分
• UNI表示该列是UNIQUE索引的一部分
• MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，有的话指定值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

查看表详细结构语句SHOW CREATE TABLE：使用SHOW CREATE TABLE语句，不仅可以查看表创建时候的详细语句，还可以查看存储引擎和字符编码

加上参数‘/G’之后，可使显示结果更加直观，易于查看。（SHOW CREATE TABLE 表名 /G）

修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段> <数据类型>

• 修改字段的数据类型

ALTER TABLE <表名> CHANGE <旧字段> <新字段> <新数据类型>

• 添加字段

ALTER TABLE <表名> ADD <新字段> <数据类型>

更改表的存储引擎

存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同技术实现。可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。MySQL中主要的存储引擎有MyISAM、InnoDB、MEMORY（HEAP）、BDB、FEDERATED等。可以使用“SHOW ENGINES;”语句查看系统支持的存储引擎。

ALTER TABLE <表名> ENGINE = <更改后的引擎名>

自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题

数据库函数

条件判断函数

• IF(expr, v1, v2)：如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则返回值为v1；否则返回值为v2。
• IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。IFNULL()的返回值是数字或者字符串，具体情况取决于其所在的语境
• CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END：如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

系统信息函数

• VERSION()返回指示MySQL服务器版本的字符串。这个字符串使用utf8字符集；select version()
• 查看当前用户的连接数:SELECT CONNECTION_ID()
• SHOW PROCESSLIST命令输出当前用户的连接信息
• 使用SELECT LAST_INSERT_ID查看最后一个自动生成的列值

格式化函数FORMAT(x,n)

FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果函数不含小数部分。

窗口函数

在MySQL 8.0版本之前，没有排名函数，所以当需要在查询当中实现排名时，必须手写@变量

窗口函数的语法：

window_function_name(expression)

OVER (

[partition_defintion]

[order_definition]

[frame_definition]

)

即：窗口函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])

 使用正则表达式查询

MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。表7.3列出了REGEXP操作符中常用字符匹配列表。

 例如：查询f_name字段以“be”开头的记录

SELECT * FROM <表名> WHERE f_name REGEXP '^be'

MySQL 8.0 GROUP BY不再隐式排序

老版本在使用GROUP BY 后会自动排序，mysql8取消了这一操作，需要排序必须加上ORDER BY子句

 MySQL错误日志

通过错误日志可以监视系统的运行状态，便于及时发现故障、修复故障

使用SHOW VARIABLES语句查询错误日志的存储路径

SHOW VARIABLES LIKE 'log_error'

通过show variables like ‘%general%’;语句可以查询当前查询日志的状态

索引

show index from <表名>

适合作为索引：

1. 字段数值有唯一性
2. 频繁作为where查询条件的字段
3. 经常group by 和 order by的列
4. updata 和 delete的where条件列
5. distinct的列需要创建索引
6. 使用列类型小的创建索引
7. 字符串很长可以用字符串前缀创建索引
8. 联合索引优于单列索引

不适合索引：

1. where中使用不到的索引
2. 数据量很小的表不要使用索引了
3. 有大量重复数据的列上不要创建索引
4. 避免对经常更新的表创建过多的索引
5. 不建议对无需的值作为索引
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引

 优化查询

•  id 有几个select就会有几个id，id可能会存在相同的情况，相同id两条数据前面一条是驱动表，后一张是被驱动表（这里有个例外：涉及子查询优化器会进行重写，id可能不会按select计数）

1. id相同可以认为是从上往下执行
2. id越大优先级越高越有优先执行
3. id每个号码代表一个独立查询，查询越少越好

•  select_type:

1. SIMPLE表示简单查询，不包含UNION查询和子查询，连接查询也是SIMPLE
2. PRIMARY表示主查询，或者是最外层的查询语句，对于包含UNION，UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，最左边的查询就是PRIMARY.　　
3. UNION表示查询的第二个或者后面的查询语句。对于包含UNION，UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，最左边的查询以外的就是UNION
4. DEPENDENT UNION 链接查询中的第二个或后面的查询语句,相关的
5. SUBQUERY:不相关子查询
6. DEPENDENT SUBQUERY:相关子查询

• table：表示查询的表
• type：表示表的链接类型，下面从最优到最差

1. system级别：只有一条数据的系统表或衍生表只能有一条数据的主查询很明显实际开发当中 我们是不会只有一条数据的
2. const级别：仅仅能查出一条的SQL语句并且用于Primary key 或 unique索引；
3. eq_ref级别：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多，不能0）;此种情况常见于唯一索引和主键索引
4. ref级别：非唯一性索引：对于每个索引键的查询，返回匹配的所有行（可以是0，或多个）。
5. range级别：检索指定范围的行，查找一个范围内的数据，where后面是一个范围查询 （between,in,> < >=);in有时有可能会失效，导致为ALL；
6. index级别：与all链接相同，除了只扫描索引树
7. ALL级别：进行完整的表扫描

• possible_keys:能使用的索引
• key：表示实际使用到的索引
• key_len:表示选择的索引字段按字节计算的长度，对于联合索引有一定参考价值
• ref：表示使用那一列或常数与索引一起来查询
• rows：必须检查的行数，越小越好
• extra：

1. Distinct : 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
2. Range checked for each: 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
3. Using filesort: 发生了硬盘或内存排序,一般是由order by 或 group by触发的;典型的情况,排序的字段不是驱动表的字段,则会使用临时表将数据都添加进去,最后进行排序,如果数据大则硬盘排序,如果小则内存排序.可以考虑使用联合索引把where后的查询字段和排序字段联合索引
4. Using index:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,使用了覆盖索引优化
5. Using temporary:使用了临时表,通常和filesort一起发生. 典型情况:使用了派生表.
6. Using where: 数据库服务层从存储引擎提取了数据又进行了一次条件过滤，不代表一定有回表
7. using index condition: ICP优化,从5.6之后提供.将过滤条件下推到存储引擎层执行,能更好的利用复合索引来过滤数据,减少IO.
8. Using index for group-by:使用了松散索引扫描

约束

如何查询表中的约束

SELECT * FROM information_schema.table_constraints WHERE table_name = "表名"