DDL语言：数据定义语言，主要负责库和表的管理

1. 库的管理

1.1 库的创建

• 语法：

CREATE DATABASE 库名;

• 示例：

# 案例：创建图书库
CREATE DATABASE IF NOT EXISTS books;

1.2 库的修改

在MySQL5.7中可以使用rename database books to 新库名的方式修改，但是会丢失数据，不推荐使用

• 语法：

ALTER DATABASE 库名 CHARACTER SET gbk

• 示例：修改数据库的字符集

ALTER DATABASE books CHARACTER SET gbk;

1.3 库的删除

• 语法：

DROP DATABASE IF EXISTS 库名;

• 示例：

DROP DATABASE IF EXISTS books;

2. 表的管理

2.1 表的创建

• 语法：

CREATE TABLE 表名
(
    列名 列的类型 [(长度)] [约束],
    列名 列的类型 [(长度)] [约束],
    列名 列的类型 [(长度)] [约束],
    列名 列的类型 [(长度)] [约束],
    ......
)

• 示例：创建一个books表

CREATE TABLE book
(
    id           INT,         #编号
    b_name       VARCHAR(20), # 书名
    price        DOUBLE,      # 价格
    author       INT,         # 作者
    publish_date DATETIME     # 出版日期
);

• 示例：创建一个author表

# 案例：创建表author
CREATE TABLE author
(
    id      INT,
    au_name VARCHAR(20),
    nation  VARCHAR(10)
);

2.2 表的修改

• 语法：

ALTER TABLE 表名
    CHANGE|ADD|DROP|MODIFY COLUMN 列名 修改的值;

2.2.1 修改列名

ALTER TABLE book
    CHANGE COLUMN publish_date pub_date DATETIME;

2.2.2 修改列的类型

# 修改列的类型
ALTER TABLE book
    MODIFY COLUMN pubdate TIMESTAMP;

2.2.3 添加新的列

# 添加新的列
ALTER TABLE author
    ADD COLUMN annual DOUBLE;

2.2.4 删除列

# 删除列
ALTER TABLE author
    DROP COLUMN annual;

2.2.5 修改表名

# 修改表名
ALTER TABLE author RENAME TO book_author;

2.3 表的删除

# 表的删除
DROP TABLE IF EXISTS book_author;

在实际开发中，为了保证sql的正确性，一般使用如下写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表名();

2.4 表的复制

2.4.1 复制表结构

CREATE TABLE copy LIKE author;

2.4.2 复制表的结构和数据

CREATE TABLE copy2
SELECT *
FROM
    author;

2.4.3 复制部分数据

CREATE TABLE copy3
SELECT
    id,
    au_name
FROM
    author
WHERE
    nation = '中国';

2.4.4 只复制部分结构

CREATE TABLE copy4
SELECT
    id,
    au_name
FROM
    author
WHERE
    0;

3. 常见数据类型

3.1 整型

• 分类：

  • tinyint：1字节
  • smallint：2字节
  • mediumint：3字节
  • int/integer：4字节
  • bigint：8字节

• 特点：

  • 如果不设置无符号还是有符号，则默认是有符号，如果希望设置为无符号，则需要添加unsigned关键字
  • 如果插入的数值超过了整型的范围，则会报out of range异常，并且插入对应类型的临界值
  • 如果不设置长度，会有默认的长度，长度代表最大的显示宽度
  • 宽度可以使用zerofill设置零填充，开启后有符号类型会失效

CREATE TABLE tab_int
(
    t1 INT ZEROFILL, # 有符号
    t2 INT UNSIGNED  # 无符号
);

3.2 小数

• 分类：

  • 浮点型

    • float(M, D)
    • double(M, D)

  • 定点型

    • dec(M, D)
    • decimal(M, D)

• 特点：

  • M、D可以省略，M代表整数部位+小数部位的长度，D代表小数部位的长度
  • 如果是decimal，M默认为10，D默认为0，如果是float，则根据插入的值得精度来确定
  • 定点型的精度较高，如果要求插入的数值精度较高，则优先使用，如货币运算等场景

• 原则：

  • 所选择的类型越简单越好，能保存的数值越小越好

CREATE TABLE tab_float
(
    f1 FLOAT(5, 2),
    f2 DOUBLE(5, 2),
    f3 DECIMAL(5, 2)
);

INSERT INTO
    tab_float
VALUES
    (123.45, 123.45, 123.45);

INSERT INTO
    tab_float
VALUES
    (123.456, 123.456, 123.456);

3.3 字符型

• 分类：

  • 短文本：char(M)、varchar(M)，M表示最大的字符数。两者的区别：char为固定长度，varchar为可变长度
  • 长文本：text、blob（较大的二进制）
• 特点：

CREATE TABLE tab_char
(
    c1 ENUM ('a', 'b', 'c')
);

# Enum
INSERT INTO
    tab_char
VALUES
    ('a');


# Set 类型，和枚举类似
CREATE TABLE tab_set
(
    s1 SET ('a', 'b', 'c', 'd')
);

INSERT INTO
    tab_set
VALUES
    ('a');
INSERT INTO
    tab_set
VALUES
    ('b');
INSERT INTO
    tab_set
VALUES
    ('a,b,C');

3.4 日期型

• 分类：

  • date：只保存日期
  • time：只保存时间
  • year：只保存年份
  • datetime：保存日期和时间
  • timestamp：保存日期和时间
• 特点：

CREATE TABLE tab_date
(
    t1 DATETIME,
    t2 TIMESTAMP
);

INSERT INTO
    tab_date
VALUES
    (now(), now());

SET TIME_ZONE = '+9:00';

SELECT *
FROM
    tab_date;

4. 常见约束

• 含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性

• 分类：六大约束

  • NOT NULL：非空约束，用于保证该字段的值不能为空，如id、姓名等
  • DEFAULT：默认约束，用于保证该字段的值有默认值，如果不插入该字段会显示默认值，如性别
  • PRIMARY KEY：主键约束，用于保证该字段的唯一性，且主键不能为空，一般选择可以唯一代表该行数据的字段，如员工编号、学号等
  • UNIQUE：唯一键，用于保证该字段具有唯一性，但是可以为空，如座位号
  • CHECK：检查约束（MySQL中不支持）
  • FOREIGN KEY：用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外键约束，用于引用表中某列的值，如学生表的专业编号、 员工的部门编号和工种编号等

• 添加约束的时机（必须是数据添加之前）：

  • 创建表时
  • 修改表时

• 约束添加的分类：

  • 列级约束：六大约束均可充当，但是外键约束没有效果
  • 表级约束：添加在建表语句的最后，除了非空、默认，其他的都支持
• 格式：

CREATE TABLE 表名
(
    字段名 字段类型 约束
);

4.1 创建表时添加约束

4.1.1 列级约束

• 语法：直接在字段名和字段类型后追加，只支持默认、非空、主键、唯一
• 示例：

CREATE TABLE stuinfo
(
    id      INT PRIMARY KEY,                               #主键
    stuname VARCHAR(20) NOT NULL,                          #姓名
    gender  CHAR(1) CHECK ( gender = '男' OR gender = '女'), #姓名
    seat    INT UNIQUE,                                    # 座位
    age     INT DEFAULT 18,
    majorid INT REFERENCES major (id)                      # 外键（此处无效果）
);

CREATE TABLE major
(
    id        INT PRIMARY KEY,
    majorname VARCHAR(20)
);

4.1.2 表级约束

• 语法：

[constraint 约束名] 约束类型(字段名)

• 示例：

CREATE TABLE stuinfo
(
    id      INT,
    stuname VARCHAR(20),
    gender  CHAR(1),
    seat    INT,
    age     INT,
    majorid INT,

    CONSTRAINT pk PRIMARY KEY (id),
    CONSTRAINT uq UNIQUE (seat),
    CONSTRAINT ck CHECK ( gender = '男' OR gender = '女'),
    CONSTRAINT fk_studeng_major FOREIGN KEY (majorid) REFERENCES major (id)
);

• 通用写法：

# 通用写法：
CREATE TABLE stuinfo
(
    id      INT PRIMARY KEY,
    stuname VARCHAR(20) UNIQUE,
    gender  CHAR(1),
    age     INT DEFAULT 18,
    seat    INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major (id)
);

• 主键和唯一键的对比：

• 外键的特点：

  • 要求在从表中设置外键
  • 从表的外键列的类型和主表的关联列的类型一致或兼容，名称无要求
  • 主表的关联列必须是一个key（一般是主键或唯一键）
  • 插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表

4.2 修改表时添加约束

• 两种约束类型的添加方式：

  • 添加列级约束：ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束
  • 添加表级约束：ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键引用]

4.2.1 添加非空约束

ALTER TABLE stuinfo
    MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

4.2.2 添加默认约束

ALTER TABLE stuinfo
    MODIFY COLUMN age INT DEFAULT 18;

4.2.3 添加主键约束

# 列给约束写法
ALTER TABLE stuinfo
    MODIFY COLUMN id INT PRIMARY KEY;
# 表级约束写法
ALTER TABLE stuinfo
    ADD PRIMARY KEY (id);

4.2.4 添加唯一约束

# 列级约束写法
ALTER TABLE stuinfo
    MODIFY COLUMN seat INT UNIQUE;

# 表级约束写法
ALTER TABLE stuinfo
    ADD UNIQUE (seat);

4.2.5 添加外键约束

# 表级约束写法
ALTER TABLE stuinfo
    ADD FOREIGN KEY (majorid) REFERENCES major (id);

4.3 修改表时删除约束

对于列级约束，删除约束的方式和添加时类似，即在修改时不写对应的约束即可

4.3.1 删除非空约束

ALTER TABLE stuinfo
    MODIFY COLUMN stuname VARCHAR(20) NULL;

4.3.2 删除默认约束

ALTER TABLE stuinfo
    MODIFY COLUMN age INT;

4.3.3 删除主键约束

ALTER TABLE stuinfo
    MODIFY COLUMN id INT;
    
ALTER TABLE stuinfo
    DROP PRIMARY KEY;

4.3.4 删除唯一建

SHOW INDEX FROM stuinfo;
ALTER TABLE stuinfo
    DROP INDEX seat;

4.3.5 删除外键约束

ALTER TABLE stuinfo
    DROP FOREIGN KEY fk_studeng_major;

5. 标识列

• 介绍：又称为自增长列，可以不用手动插入值，系统自动提供默认的序列值

• 特点：

  • 标识列必须是一个key
  • 一个表中只能有一个标识列
  • 标识列的类型只能是数值型
  • 标识列可以通过SET AUTO_ONCREMENT_INCREMENT = 3设置步长，也可以通过手动插入值设置起始点

5.1 创建表时设置标识列

CREATE TABLE tab_identity
(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

5.2 修改表时设置标识列

ALTER TABLE tab_identity
    MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

5.3 修改表时删除标识列

ALTER TABLE tab_identity
    MODIFY COLUMN id INT;