# 管理单元

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 nullis 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 命令不会立即释放磁盘空间,还可以回滚。