基本使用

SQL 模式

STRICT_ALL_TABLES 和 STRICT_TRANS_TABLES：
TRADITIONAL
ANSI_QUOTES
- 告诉服务器把双引号是识别为标识符引用字符
PIPES_AS_CONCAT
- 将 || 当做连接运算符
ANSI
- 启动多种组合模式

设置模式：

mysql ... --sql-model="xxx"

SET sql_mode='xx'
SET GLOBAL sql_mode='xx' -- 全局模式

标识符与命名

不加双引号的标识符：

大小写字母数字美元符号下划线组成可以以数字开头没有引号的命名不能全由数字构成

如果启用了 IGNORE_SPACE 模式函数名会变成保留字

标识符的最大长度为64个字符别名最大长度256字符

完全限定表名与完全限定列名：db.table.column

大小写规则

SQL 关键字与和函数名，存储过程的名字不区分大小写
数据库名表名视图名区分大小写取决于操作系统
列名、索引名不区分
别名区分
字符串区分大小写取决于是否是二进制串

为避免出现问题，统一采用小写

字符集与校对规则

字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每一种字符集都会对应一系列的校对规则

MySQL采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）

SHOW CHARSET -- 列出可用字符集

unicode支持：utf8 utf8mb4

数据库操作

USE db; -- 切换数据库
CREATE DATABASE [IF NOT EXISTS] db [CHARSET xx] [COLLATE xxx]; -- 创建数据库
DROP DATABASE db; -- 删除数据库
ALTER DATABASE db [CHARSET xx] [COLLATE xxx]; -- 更改字符集与排序规则

表操作

CREATE TABLE [IF NOT EXISTS] (..) ENGINE = xxx DEFAULT CHARSET = xxx; -- 创建
ALTER TABLE (..) ENGINE = xxx DEFAULT CHARSET = xxx; -- 修改
CREATE TEMPORARY TABLE table (...); -- 临时表 会话关闭丢失
CREATE TABLE table LIKE xxx -- 复制其他表的结构
CREATE TABLE table SELECT ... -- 复制查询的数据 一些列的属性不会被复制

CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (1995),
    PARTITION p2 VALUES LESS THAN (2000)
); -- 分区表

DROP TABLE [IF EXISTS] tb; -- 删除表

ALTER TABLE tb CHANGE cname cname INT; -- 改变列名并修改列类型
ALTER TABLE tb ENGINE = xxx; -- 更改存储引擎
ALTER TABLE tb RENAME TO tb1; -- 表重命名

索引

屏幕截图 2020-08-24 150159

除了 PRIMARY KEY ,绝大部分索引可以使用CREATE IDNEX创建

CREATE UNIQUE INDEX name ON table (...); -- 唯一索引 单列中不允许有重复值出现
CREATE FULLTEXT INDEX name ON table (...); -- 全文索引
CREATE SPATIAL INDEX name ON table (...); -- myisam 空间索引

CREATE TABLE (
  ...
  INDEX (name(10)) -- 对字符串前10个字符简历索引
)

DROP INDEX name ON tb; --删除索引

获取数据库元数据

show语句：

可以使用 LIKE 'xx' 限制输出范围

SHOW DATABASES;
SHOW CREATE DATABASE db; -- 显示创建数据库时的语句

SHOW CREATE TABLE tb;
SHOW TABLES;
SHOW TABLE STATUS;

SHOW COLUMNS FROM tb;
SHOW INDEX FROM tb;

INFORMATION_SCHEMA 库

事务处理

默认是开启执行SQL自动提交的

SET AUTOCOMMIT = 0; -- 关闭自动提交
START TRANSACTION; -- 开启事务
INSERT INTO person SET name = 'cxk'; -- 执行事务操作
COMMIT; -- 提交事务
-- ROLLBACK; 回滚事务

事务保存点

START TRANSACTION;
insert into person SET name = 'cxkk';
SAVEPOINT myp; -- 保存一个事务点
insert into person SET name = 'cxkd';
ROLLBACK TO SAVEPOINT myp; -- 回滚到事务保存点
COMMIT;

事务隔离

设置隔离级别：SET [GLOBAL, SESSION] TRANSACTION ISOLATION LEVEL

在同一个事务中混合使用不同的存储引擎是不可靠的，有的存储引擎不支持事务

隔离级别的选择：

上松下严：应用层来适配数据库的隔离级别
当Spring开启了事务并设置了传播机制，那么会覆盖Mysql已有的事务隔离级别。如果Mysql不支持该隔离级别，Spring的事务就也不会生效。
RR （可重复读）RD（读已提交）搭配使用

外键

CREATE TABLE tb(
  ..
  FOREIGN KEY (id) REFERENCES parent (id)
    ON DELETE CASCADE -- 删除父表相关行 子表也会被删除
    ON UPDATE CASCADE -- 更新相关
    -- ON DELETE SET NULL 删除时将相关列设置NULL
)

全文搜索

自然语言搜索
- 搜索字符串会被解析成单词进行搜索
布尔模式搜索
- 搜索出现某些单词的行
查询扩展搜索
- 先进行自然语言搜索
- 第二阶段将搜索字符串与第一阶段的结果进行拼接后搜索

特点：

需要建立全文索引
会忽略常见词
- 至少在一半的行都出现过的词
忽略内置常用词
- after the
忽略过短的单词

使用：

CREATE FULLTEXT INDEX index_actor_info ON actor_test(film_info); -- 创建相关全文索引
select * from actor_test where match(film_info) against('LUKE'); -- 自然语言搜索
select * from actor_test where match(film_info) against('LUKE CHISUM' IN BOOLEAN MODE); -- 布尔模式
select * from actor_test where match(film_info) against('LUKE CHISUM' WITH QUERY EXPANSION); -- 查询扩展

配置：

ft_min_word_len=3 # 最小单词长度