SQL基础

命令帮助

语法

help 命令

客户端命令

delimiter (\d) 定义分隔符
tee       (\T) 将输出内容追加至某一文件
use       (\u) 切换数据库
system    (\!) 调用Linux命令

服务端命令

Account Management（用户、权限管理）

   Administration（系统管理类语句）

   Components（组件应用）

   Compound Statements（过程函数复合语句）

   Contents（帮助目录）

   Data Definition（数据定义）

   Data Manipulation（数据操作）

   Data Types（数据类型）

   Functions（函数）

   Geographic Features（地理位置）

   Help Metadata（元数据帮助）

   Language Structure（语言结构）

   Plugins（插件管理）

   Storage Engines（存储引擎）

   Table Maintenance（表维护）

   Transactions（事务）

   User-Defined Functions（自定义函数）

   Utility（使用工具）

SQL_MODE详解

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

SQL_MODE	comments
ONLY _FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列、HAVING或者ORDER BY子句的列，没有在GROUP BY 中出现，或者不在函数中聚合，那么这个SQL会报错
STRICT_TRANS_TABLES	严格模式，进行数据的严格校验，错误数据不能插入，报error错误。如果不能将给定的值插入到事务表中，则放弃该语句；对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句
NO_ZERO_IN_DATE|	在严格模式，不接受月或日部分为0的日期，如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。
NO_ZERO_DATE	在严格模式，不要将”0000-00-00”作为合法日期
ERROR_FOR_DIVISION_BY_ZERO	在严格模式，在INSERT或UPDATE过程中，如果被零除（或MOD（X,0）），则产生错误（否则为警告）
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或者未编译，那么抛出错误
NO_AUTO_CREATE_USER	防止GRANT自动创建新用户，除非还指定了密码。

only_full_group_by解释

有only_full_group_by，所以我们要在MySQL中正确的使用group by语句的话，只能是select column1 from tb1 group by column1(即只能展示group by的字段，其他均都要报1055的错)

模拟only_full_group_by报错

mysql> select * from tt1;
+----+-------+--------+
| id | name  | gender |
+----+-------+--------+
|  1 | xiong |      0 |
|  2 | ying  |      0 |
|  3 | cai   |      0 |
|  4 | zhang |      0 |
|  5 | li    |      1 |
|  6 | wang  |      1 |
+----+-------+--------+
6 rows in set (0.00 sec)

mysql> select * from tt1 group by name;
ERROR 1055 (42000):

sql_mode配置

要使用能正确的使用group by 的话就必须删除掉only_full_group_by，可以使用如下语句来将空格替换掉

set sql_mode=(select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

临时修改sql_mode

 

 永久修改

 

库、表属性

mysql逻辑结构

库：库名、属性（字符集、校验规则、表空间加密）
表：表名，表属性（存储引擎，字符集，校对规则，表空间加密），列（列名，列属性），数据行

字符集

 

UTF8             ：字符最大长度3个字节

UTF8mb4          ：字符最大长度4个字节

校对规则（影响排序：大小写）

设置成含bin的就是区分大小写

存储引擎

 

 加密表空间

从5.7.11开始，mysql开始支持物理表空间的加密，它使用两层加密架构。
包括：master key 和 tablespace key

master key用于加密tablespace key，加密后的结果存储在tablespace的header中。tablespace key用于加密数据
当用户想访问加密的表时，innoDB会先用master key对之前存储在header中的加密信息进行解密，得到tablespace key。再用tablespace key解密数据信息。tablespace key是不会被改变的（除非进行alter table t1 encrytion=NO, alter table t1 encrytion=YES）。而master key可以随时改变（ALTER INSTANCE ROTATE INNODB MASTER KEY;）

InnoDB表空间加密依赖于keyring插件。总共有四个插件

keyring插件 开始版本 类型
keyring_file  5.7.11 社区版
keyring_encrypted_file  5.7.21 企业版
keyring_okv  5.7.12 企业版
keyring_aws  5.7.19 企业版

由于上面四种类型的加密后三种都只有在企业版本有，所以接下来的介绍主要以社区版本的keyring_file为例进行说明。

ENCRYPTION= 'N'/'Y'
临时生效：
INSTALL PLUGIN keyring_file soname 'keyring_file.so';
mkdir -p /data/3306/msyql-keyring/
chown -R mysql.mysql /data/3306/mysql-keyring
chmod 750 /data/3306/mysql-keyring

报错需要看mysql错误日志：

ERROR 1123 (HY000): Can't initialize function 'keyring_file'; Plugin initialization function failed.

[root@localhost ~]# mkdir -p /usr/local/mysql/keyring/keyring
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql/keyring/
[root@localhost ~]# chmod 755 /usr/local/mysql/keyring/

永久生效：
在my.cnf的[mysqld]段，加这两行
early-plugin-load=keyring_file.so
keyring_file_data=/data/3306/mysql-keyring/keyring

 

 

master key需周期性地改变，增强安全性，master key rotation是原子的、实例级别的操作。轮换时，所有的tablespace key都会被重新用新master key加密并存储到相关的tablespace header中。
更换master key只会改变master key和重新加密tablespace key,不会解密及重加密表空间数据。
更换语句：ALTER INSTANCE ROTATE INNODB MASTER KEY;
更换时，能够进行并发地DML操作，但是不能和CREATE TABLE ... ENCRYPTED 或 ALTER TABLE ... ENCRYPTED 并行。

当在进行master key 更换时，实例挂掉。那在实例重启时会继续此操作。
在重启过程中，keyring插件将优先于innoDB初始化执行，因为innoDB需要keyring插件来解密相关的数据，这也是为什么需要用early-plugin-load参数的原因(用--plugin-load 或 --plugin-load-add 加载的插件是在InnoDB初始化后才加载的。用 INSTALL PLUGIN加载的插件是注册在mysql.plugin表中，但它也是一个innodb表，里面注册的插件也是在innodb初始化后才执行的)。同时，mysql会去扫描所有的加密表空间文件。对于依旧使用旧master key的文件，innodb会先用old master key来解密tablespace key，再用new master key加密tablespace key后重写回tablespace header中。

当导出一个加密的表时，InnoDB生成一个transfer key用来加密tablespace key,加密的tablespace key及transfer key存储在tablespace_name.cfp文件中。当导入时，InnoDB会使用transfer key来解密tablespace key。

执行ALTER INSTANCE ROTATE INNODB MASTER KEY时，主从实例必须都得支持表空间加密才行
ALTER INSTANCE ROTATE INNODB MASTER KEY执行成功后会被记录在binlog中。

 

找出加密的表空间：
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| test | t1 | ENCRYPTION="Y" |
+--------------+------------+----------------+

 

注意：
1.不能一次使用多个加密插件。
2.确保master key的安全性，如果丢失，就会造成数据文件的不可用。在初次建立表空间加密及master key rotation时，建议备份
3.ALTER TABLE ... ENCRYPTION 操作用 ALGORITHM=COPY重建表，不支持ALGORITHM=INPLACE
4.如果实例退出，建立用同样的加密配置启动实例。
5.第一个新的或存在的表加密时，会自动生成master key
6.轮换Master key时只是重新加密tablespace key，并没有改变tablespace key,要改变tablespace key,需要用 ALTER TABLE tbl_name ENCRYPTION先禁用加密再重新加密，它使用ALGORITHM=COPY来重建表。
7.如果一个表创建时使用compression及encryption选项，compression会在encryption前执行。
8.如果keyring 数据文件（keyring_file_data变量）是空的或不存在，第一次执行 ALTER INSTANCE ROTATE INNODB MASTER KEY 会生成master key.
9.卸载keyring_file 插件时并不会删除keyring 数据文件。
10.不建议把keyring数据文件和tablespace表放在同一个目录中。
11.在运行或重启时修改 keyring_file_data 变量会使之前加密的表不可访问，造成数据丢失。

限制：
1.AES是唯一支持的加密算法。在InnodB表空间中，tablespace key使用Electronic Codebook (ECB)块加密模式，data使用Cipher Block Chaining (CBC)块加密模式.
2.改变一个有的ENCRYPTION属性使用的是ALGORITHM=COPY，不支持ALGORITHM=INPLACE
3.只支持加密存储在file-per-table的表，general tablespaces, system tablespace, undo log tablespaces,temporary tablespace不支持加密。
4.不能把一个加密的表空间移到一个不支持的表空间类型。
5.表空间加密只加密表空间中的数据，在redo log,undo log,binary log中的数据是不加密的。
6.已经被加密的表不允许修改成其它存储引擎。