管理单元

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
    • ：拷贝一个新表，步骤如下：
      1. 新建一个新表。
      2. 锁定原表，禁止 DML ，允许 DQL 。
      3. 将原表的数据逐行拷贝到新表。
      4. 删除原表，将新表 RENAME 为原表。
    • 缺点：
      • 需要两倍的存储空间。
      • 会阻塞 DML 操作，可能中断业务服务。
  • inplace
    • ：在原表中进行修改，分为两种方式：
      • rebuild-table ：需要重建数据表，因此会消耗大量时间、磁盘 IO 。
      • not-rebuild-table ：默认尽量采用这种。
    • 缺点：
      • 会锁定原表，阻塞 DML 操作。
    • 于 MySQL v5.5 加入。
  • instant
    • ：于 MySQL v8.0 加入，只支持少量 DDL 操作。

• 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.
      

数据行

查

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 。
• 一次查询的数据量过大时，客户端可能不能处理，比如传输量过大、内存不足。此时可以使用 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 命令不会立即释放磁盘空间，还可以回滚。