基本使用

SQL 模式

设置模式:

mysql ... --sql-model="xxx"
SET sql_mode='xx'
SET GLOBAL sql_mode='xx' -- 全局模式

标识符与命名

不加双引号的标识符:

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

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

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

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

大小写规则

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

字符集与校对规则

字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。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

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

隔离级别的选择:

外键

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

全文搜索

特点:

使用:

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 # 最小单词长度