基本使用

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

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

外键

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

results matching " "

No results matching " "

results matching " "

No results matching " "