# 管理单元
MySQL 的管理单元从上到下依次分为:
- 数据库(database)
- 一个 MySQL 实例中可以创建多个数据库。
- MySQL 中, database 又称为 schema 。
- 数据表(table)
- 一个数据库中可以创建多个数据表。
- 创建数据表时会定义它包含哪些列(column)。因此,插入一行数据时,需要包含与每列对应的字段(field)。
- 数据行(row)
- 一个数据表中可以插入多行数据。
- 可以单独修改一行数据中的某个字段,但是删除时只能删除这一整行。
# 数据库
show databases; -- 显示所有数据库
show databases like 'xx%'; -- 筛选出指定名称的数据库
use <db>; -- 切换到指定的数据库(客户端同时只能切换到一个数据库)
create database <db>; -- 创建一个数据库
drop database <db>; -- 删除一个数据库
- MySQL 自带了四个数据库:
- mysql :用于存储用户表、权限表、当前引擎、事件、日志等重要信息。
- information_schema :用于存储元数据,比如所有存在的数据库、数据表、索引,所有可用的引擎、字符集、文件。
- performance_schema :用于存储数据库的运行状态、性能指标。
- sys :用途与 performance_schema 相似,但内容少一些。
# 数据表
- 数据表中,每行记录一条数据,每列的字段表示该数据的一个属性。
- 同一个数据表中,可以新增任意行数据,它们拥有的字段数量、类型相同。
- 创建数据表时,至少要定义一个字段。
- 创建数据表之后,可以增加字段。也可以修改已有的字段,不过这会影响已有的所有数据行的该字段的值,可能不兼容。
# 主键
- 主键(Primary Key):数据表中每行数据的唯一标识。
- 通常选出一个取值不会重复的字段,作为主键,便于在数据表中定位某行数据。
- 每行数据都应该拥有主键字段,不允许为空。
- 主键的值应该固定不变,否则可能导致定位不到数据。
- 复合主键(Composite Key):在一个数据表中,由多个字段组合成主键。
# 设计范式
创建数据表时有一些流行的规范,称为设计范式(Normal Form ,NF)。
- 例如:1NF、2NF、3NF、BCNF、4NF、5NF、6NF 。
一个数据表的示例:
学号 姓名 课程 分数 20201201 张三 语文 80 20201201 张三 数学 90 20201202 李四 语文 80 20201202 李四 数学 80 20201203 张三 语文 70 20201203 张三 数学 90 依赖关系:
- 假设一个或一组属性 A 的值确定时,属性 B 的值就会确定不变,则称 B 依赖 A 。
- 例如上表中,“姓名” 依赖 “学号” ,但 “学号” 不依赖 “姓名” ,因为可能重名.
完全依赖:
- 假设属性 B 依赖 A ,A 是单个属性,或者 A 是一组属性但 B 并不依赖 A 的真子集,则称 B 完全依赖 A 。
部分依赖:
- 假设属性 B 依赖 A ,A 是一组属性,并且 B 依赖 A 的真子集,则称 B 部分依赖 A 。
- 部分依赖可以简化成完全依赖的形式。
- 例如上表中,“姓名” 完全依赖 “学号” ,部分依赖 “学号,课程” 。
传递依赖:
- 假设 C 依赖 B ,而 B 依赖 A ,则称 C 传递依赖 A 。
码:
- 假设一个或一组属性 A 的值确定时,其它属性的值都确定不变,则称 A 构成了候选码(candidate key),简称为码。
- 码可以用作每行数据的唯一标识。
- 一个数据表中可能存在零个、一个或多个码。通常只需选用一个码,称为主码(即主键)。
- 构成码的属性称为主属性,其它属性称为非主属性。
# 1NF
:第一范式。
特点:
- 数据的每个属性具有原子性。
即数据表中,字段是最小的管理单位。
- 数据的每个属性具有原子性。
例:
学号 课程分数 20201201 语文 80、数学 90 应该将 “课程分数” 拆分成更明确的字段:
学号 课程 分数 20201201 语文 80 20201201 数学 90 或者拆分成多张数据表,通过外键关联。
# 2NF
:第二范式。
特点:
- 满足第一范式作为基础。
- 每个非主属性对于码,都是完全依赖,不能部分依赖。
即数据表中,主键应该为单个字段,或者最简化的复合主键。
例:
学号 姓名 课程 分数 20201201 张三 语文 80 20201201 张三 数学 90 假设这里用 “学号,课程” 作为主键,则 “分数” 完全依赖它,但 “姓名” 部分依赖它。应该拆分为两张表:
学号 姓名 20201201 张三 学号 课程 分数 20201201 语文 80 20201201 数学 90
# 3NF
:第三范式。
特点:
- 满足第二范式作为基础。
- 每个非主属性对于码,不能传递依赖。
即非主键的其它字段之间,不存在依赖关系。
例:
学号 姓名 班级号 班主任 20201201 张三 001 刘老师 20201202 李四 002 李老师 假设这里用 “学号” 作为主键,则 “班主任” 依赖于 “班级” ,应该拆分为两张表:
学号 姓名 班级号 20201201 张三 001 20201202 李四 002 班级号 班主任 001 刘老师 002 李老师 这样能减少单张数据表中重复出现的字段。
# BCNF
- 特点:
- 满足第三范式作为基础。
- 每个主属性对于码,不能部分依赖、传递依赖。
即数据表中只存在一个码。
# 查
show tables like 'xx%'; -- 筛选出指定名称的数据表
show tables; -- 显示当前数据库的所有数据表
show table status; -- 显示所有数据表的状态
show columns from <db>; -- 显示指定数据表中所有列的配置信息
desc <tb>; -- 相当于 show columns from <tb>;
show create table tb1; -- 查看创建某个表的完整 create 命令,便于拷贝或修改该表
- 切换到一个数据库之后,就可以直接操作其中的数据表。否则每次操作数据表时,都需要指明是哪个数据库。如下:
show tables from <db>; desc <db>.<tb>;
# 增
例:
CREATE TABLE tb1( -- 创建一个数据表,名为 tb1
id int NOT NULL AUTO_INCREMENT, -- 定义一个字段,名为 id ,数据类型为 int
name varchar(255) NOT NULL,
update_time datetime NOT NULL,
PRIMARY KEY (id) -- 将 id 字段声明为主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 设置存储引擎为 InnoDB ,默认字符集为 utf8mb4
- 定义字段时,可以添加多种配置,如下:
id int -- 指定字段名、数据类型 default null -- 设置该字段的默认值,比如 default 0 not null -- 不允许写入的值为 null ,否则会报错 AUTO_INCREMENT -- 让写入的值自动递增 comment '编号' -- 添加注释
- 建议给每个字段设置默认值,并且不允许值为 null 。
- MySQL 中的 null 是一个特殊的值,比空字符更占存储空间,不能使用通用的查询语句,还不利于索引优化。
- 只有主键字段能设置成 AUTO_INCREMENT 。此时,如果新增的一行新数据中,主键字段为空或 null ,则 MySQL 会自动填写主键的值(等于前一个主键的值加一)。
- 一个数据表中最多声明一个主键,也可以不声明主键。
- 用
PRIMARY KEY (id)
的格式即可将一个字段声明为主键。 - 用
PRIMARY KEY (id, name)
的格式可以声明复合主键。
- 用
- 采用以下两种措施,可以避免重复创建某个表:
- 如果存在同名的表,则先删掉它,再创建:
DROP TABLE IF EXISTS tb1; CREATE TABLE tb1(...);
- 如果不存在同名的表,才创建它:
CREATE TABLE IF NOT EXISTS tb1(...);
- 如果存在同名的表,则先删掉它,再创建:
- 拷贝一张表:
create table tb2 like tb1; -- 拷贝表结构 insert into tb2 select * from tb1; -- 拷贝每行数据
- 创建临时表:
create temporary table tb1(...);
- 当客户端关闭连接时,临时表会被自动删除。
- 执行
show tables;
命令时看不到临时表,必须要指定表名才能看到临时表。
# 改
alter table tb1 rename to tb2; -- 修改数据表的名字
alter table tb1 engine = myisam; -- 修改数据表的引擎
alter table tb1 add time date; -- 添加一个列(默认排在最后一列),列名为 time ,数据类型为 date
[FIRST] -- 排在第一列
[AFTER <field>] -- 排在指定列之后
alter table tb1 change time `t` date; -- 将 time 列改名为 t ,数据类型为 date
alter table tb1 modify time char(10); -- 修改一个字段的定义
alter table tb1 drop time; -- 删除一个字段
- MySQL 不支持重命名数据库,但可以跨库移动表:
alter table db1.tb1 rename to db2.tb1;
# 删
drop table tb1; -- 删除数据表
# 视图
- MySQL 支持创建视图(View):根据一条 SELECT 语句的查询结果,生成虚拟的数据表。
- 视图也属于数据表,但不会实际存储。
- 视图的 SELECT 目标可以是多个数据表或视图,但不能是 temporary 表。
- 用法:
CREATE VIEW <view> AS SELECT ...; -- 创建一个视图 SELECT * from <view>; -- 查看视图的内容 ALTER VIEW <view> AS SELECT ...; -- 修改视图的 SELECT 语句 DROP VIEW <view>; -- 删除视图
# DDL
MySQL 的 DDL 有多种实现算法:
- copy
- :拷贝一个新表,步骤如下:
- 新建一个新表。
- 锁定原表,禁止 DML ,允许 DQL 。
- 将原表的数据逐行拷贝到新表。
- 删除原表,将新表 RENAME 为原表。
- 缺点:
- 需要两倍的存储空间。
- 会阻塞 DML 操作,可能中断业务服务。
- :拷贝一个新表,步骤如下:
- inplace
- :在原表中进行修改,分为两种方式:
- rebuild-table :需要重建数据表,因此会消耗大量时间、磁盘 IO 。
- not-rebuild-table :默认尽量采用这种。
- 缺点:
- 会锁定原表,阻塞 DML 操作。
- 于 MySQL v5.5 加入。
- :在原表中进行修改,分为两种方式:
- instant
- :于 MySQL v8.0 加入,只支持少量 DDL 操作。
- copy
Online DDL :用于在不中断服务的情况下执行 DDL 操作。
- MySQL v5.6 给 InnoDB 引擎增加了 Online DDL 功能,并默认启用。
- 可以主动指定 Online DDL 策略,如下:
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
- ALGORITHM 表示 DDL 算法,可取值:
- DAFAULT :自动选择最高效的算法。
- COPY
- INPLACE
- LOCK 表示加锁类型:
- DAFAULT :尽量选择最低的锁,从而允许并发 DML 。
- NONE :不加锁。
- SHARED :共享锁。
- EXCLUSIVE :排它锁。
- 如果指定的策略不支持当前的 DDL 操作,则会报错:
ALTER TABLE tb1 RENAME TO tb2, LOCK=NONE; > 1845 - LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE.
- ALGORITHM 表示 DDL 算法,可取值:
# 数据行
# 查
select <field>... from <tb>... -- 选择一个或多个数据表中,每行数据的指定字段
[where ...] -- where 子句,用于筛选数据
[order by <filed> [desc]] -- 按某个字段排序,desc 表示降序
[limit <n>] -- 最多返回 n 条数据
[limit <offset,n>] -- 返回从第 offset 条开始的数据(从 0 开始编号),最多返回 n 条
- 例:
select * from tb1; -- 通配符 * 会匹配所有字段 select name from tb1 where id=1; -- 只返回 id=1 的那行数据的 name 字段
- where 子句的语法:
where id=1; -- 严格匹配 where name is null; -- 判断 null 值 where id>1; -- 使用 > 或 < 运算符 where name in ('one', 'two'); -- 使用 in 运算符 where id=1 and name='one'; -- 使用 and 运算符 where id=1 or name='one'; -- 使用 or 运算符 where name like 'a%'; -- 字符串的模糊匹配。匹配时不区分大小写,可使用 % 作为通配符 where name REGEXP '^one$'; -- 字符串的正则匹配
- 判断 null 值只能用
is null
或is not null
,不能用=null
,因为在 MySQL 中它总是返回 False 。
- 判断 null 值只能用
- 一次查询的数据量过大时,客户端可能不能处理,比如传输量过大、内存不足。此时可以使用 limit 子句实现分页查询,即每次只获取少量的数据。
# 增
insert into tb1 values (null, 'one', '2019-12-01'); -- 插入一行数据,给所有字段赋值
insert into tb1 (name, date) values ('one', '2019-12-01'); -- 插入一行数据,给指定的几个字段赋值(前提是其它字段能自动赋值)
# 改
update <tb> set <field>=<value>... [where ...];
- 例:
update tb1 set name='one' where id=1; update tb1 set name='one', date='2019-12-02' where id=1;
# 删
delete from tb1; -- 删除数据表的全部内容
delete from tb1 where ...; -- 筛选出一部分数据再删除
- delete 命令不会立即释放磁盘空间,还可以回滚。