37.SQL详解之DDL

SQL详解之DDL

我们通常可以将 SQL 分为四类，分别是 DDL（数据定义语言）、DML（数据操作语言）、 DCL（数据控制语言）和 TCL（事务控制语言）。DDL 主要用于创建、删除、修改数据库中的对象，比如创建、删除和修改二维表，核心的关键字包括create、drop和alter；DML 主要负责数据的插入、删除、更新和查询，关键词包括insert、delete、update和select；DCL 用于授予和召回权限，核心关键词是grant和revoke；TCL 通常用于事务控制。

说明：SQL 是不区分大小写的语言，一般情况下我们建议将关键字大写，其他部分小写。 如果公司的 SQL 编程规范有强制规定，那么就按照公司的要求来，个人的喜好不应该凌驾于公司的编程规范之上，这一点对职业人来说应该是常识。

建库建表

下面我们来实现一个非常简单的学校选课系统的数据库。我们将数据库命名为school，四个关键的实体分别是学院、老师、学生和课程，其中，学生跟学院是从属关系，这个关系从数量上来讲是多对一关系，因为一个学院可以有多名学生，而一个学生通常只属于一个学院；同理，老师跟学院的从属关系也是多对一关系。一名老师可以讲授多门课程，一门课程如果只有一个授课老师的话，那么课程跟老师也是多对一关系；如果允许多个老师合作讲授一门课程，那么课程和老师就是多对多关系。简单起见，我们将课程和老师设计为多对一关系。学生和课程是典型的多对多关系，因为一个学生可以选择多门课程，一门课程也可以被多个学生选择，而关系型数据库需要借助中间表才能维持维持两个实体的多对多关系。最终，我们的学校选课系统一共有五张表，分别是学院表（tb_college）、学生表（tb_student）、教师表（tb_teacher）、课程表（tb_course）和选课记录表（tb_record），其中选课记录表就是维持学生跟课程多对多关系的中间表。

-- 如果存在名为school的数据库就删除它
DROP DATABASE IF EXISTS `school`;

-- 创建名为school的数据库并设置默认的字符集和排序方式
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 切换到school数据库上下文环境
USE `school`;

-- 创建学院表
CREATE TABLE `tb_college`
(
`col_id`    int unsigned AUTO_INCREMENT      COMMENT '编号',
`col_name`  varchar(50)  NOT NULL            COMMENT '名称',
`col_intro` varchar(500) NOT NULL DEFAULT '' COMMENT '介绍',
PRIMARY KEY (`col_id`)
);

-- 创建学生表
CREATE TABLE `tb_student`
(
`stu_id`    int unsigned NOT NULL           COMMENT '学号',
`stu_name`  varchar(20)  NOT NULL           COMMENT '姓名',
`stu_sex`   boolean      NOT NULL DEFAULT 1 COMMENT '性别',
`stu_birth` date         NOT NULL           COMMENT '出生日期',
`stu_addr`  varchar(255) DEFAULT ''         COMMENT '籍贯',
`col_id`    int unsigned NOT NULL           COMMENT '所属学院',
PRIMARY KEY (`stu_id`),
CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
);

-- 创建教师表
CREATE TABLE `tb_teacher`
(
`tea_id`    int unsigned NOT NULL                COMMENT '工号',
`tea_name`  varchar(20)  NOT NULL                COMMENT '姓名',
`tea_title` varchar(10)  NOT NULL DEFAULT '助教' COMMENT '职称',
`col_id`    int unsigned NOT NULL                COMMENT '所属学院',
PRIMARY KEY (`tea_id`),
CONSTRAINT `fk_teacher_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`)
);

-- 创建课程表
CREATE TABLE `tb_course`
(
`cou_id`     int unsigned NOT NULL COMMENT '编号',
`cou_name`   varchar(50)  NOT NULL COMMENT '名称',
`cou_credit` int          NOT NULL COMMENT '学分',
`tea_id`     int unsigned NOT NULL COMMENT '授课老师',
PRIMARY KEY (`cou_id`),
CONSTRAINT `fk_course_tea_id` FOREIGN KEY (`tea_id`) REFERENCES `tb_teacher` (`tea_id`)
);

-- 创建选课记录表
CREATE TABLE `tb_record`
(
`rec_id`   bigint unsigned AUTO_INCREMENT COMMENT '选课记录号',
`stu_id`   int unsigned    NOT NULL       COMMENT '学号',
`cou_id`   int unsigned    NOT NULL       COMMENT '课程编号',
`sel_date` date            NOT NULL       COMMENT '选课日期',
`score`    decimal(4,1)                   COMMENT '考试成绩',
PRIMARY KEY (`rec_id`),
CONSTRAINT `fk_record_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `tb_student` (`stu_id`),
CONSTRAINT `fk_record_cou_id` FOREIGN KEY (`cou_id`) REFERENCES `tb_course` (`cou_id`),
CONSTRAINT `uk_record_stu_cou` UNIQUE (`stu_id`, `cou_id`)
);

上面的DDL有几个地方需要强调一下：

• 首先，上面 SQL 中的数据库名、表名、字段名都被反引号（`）包裹起来，反引号并不是必须的，但是却可以解决表名、字段名等跟 SQL 关键字（SQL 中有特殊含义的单词）冲突的问题。

• 创建数据库时，我们通过default character set utf8mb4指定了数据库默认使用的字符集为utf8mb4（最大4字节的utf-8编码），我们推荐使用该字符集，它也是 MySQL 8.x 默认使用的字符集，因为它能够支持国际化编码，还可以存储 Emoji 字符。可以通过下面的命令查看 MySQL 支持的字符集以及默认的排序规则。

  show character set;
  

  +----------+---------------------------------+---------------------+--------+
  | Charset  | Description                     | Default collation   | Maxlen |
  +----------+---------------------------------+---------------------+--------+
  | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
  | dec8     | DEC West European               | dec8_swedish_ci     |      1 |
  | cp850    | DOS West European               | cp850_general_ci    |      1 |
  | hp8      | HP West European                | hp8_english_ci      |      1 |
  | koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
  | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
  | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
  | swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
  | ascii    | US ASCII                        | ascii_general_ci    |      1 |
  | ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
  | sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
  | hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
  | tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
  | euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
  | koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
  | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
  | greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
  | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
  | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
  | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
  | armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
  | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
  | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
  | cp866    | DOS Russian                     | cp866_general_ci    |      1 |
  | keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
  | macce    | Mac Central European            | macce_general_ci    |      1 |
  | macroman | Mac West European               | macroman_general_ci |      1 |
  | cp852    | DOS Central European            | cp852_general_ci    |      1 |
  | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
  | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
  | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
  | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
  | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
  | cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
  | cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
  | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
  | binary   | Binary pseudo charset           | binary              |      1 |
  | geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
  | cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
  | eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
  | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
  +----------+---------------------------------+---------------------+--------+
  41 rows in set (0.00 sec)
  

删除表和修改表

下面以学生表为例，为大家说明如何删除表和修改表。删除表可以使用drop table，代码如下所示。

DROP TABLE `tb_student`;

或

DROP TABLE IF EXISTS `tb_student`;

需要注意的是，如果学生表已经录入了数据而且该数据被其他表引用了，那么就不能删除学生表，否则上面的操作会报错。在下一课中，我们会讲解如何向表中插入数据，到时候大家可以试一试，能否顺利删除学生表。

如果要修改学生表，可以使用alter table，具体可以分为以下几种情况：

修改表，添加一个新列，例如给学生表添加一个联系电话的列。

ALTER TABLE `tb_student` ADD COLUMN `stu_tel` varchar(20) NOT NULL COMMENT '联系电话';

注意：如果新增列的时候指定了非空约束（not null），那么学生表不能够有数据，否则原来的数据增加了 stu_tel 列之后是没有数据的，这就违反了非空约束的要求；当然，我们在添加列的时候也可以使用默认值约束来解决这个问题。

修改表，删除指定的列，例如将上面添加的联系电话列删除掉。

ALTER TABLE `tb_student` DROP COLUMN `stu_tel`;

修改表，修改列的数据类型，例如将学生表的 stu_sex 修改为字符。

ALTER TABLE `tb_student` MODIFY COLUMN `stu_sex` char(1) NOT NULL DEFAULT 'M' COMMENT '性别';

修改表，修改列的命名，例如将学生表的 stu_sex 修改为 stu_gender。

ALTER TABLE `tb_student` CHANGE COLUMN `stu_sex` `stu_gender` boolean DEFAULT 1 COMMENT '性别';

修改表，删除约束条件，例如删除学生表的 col_id 列的外键约束。

ALTER TABLE `tb_student` DROP FOREIGN KEY `fk_student_col_id`;

修改表，添加约束条件，例如给学生表的 col_id 列加上外键约束。

ALTER TABLE `tb_student` ADD FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);

或

ALTER TABLE `tb_student` ADD CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`);

说明：在添加外键约束时，还可以通过on update和on delete来指定在被引用的表发生删除和更新操作时，应该进行何种处理，二者的默认值都是restrict，表示如果存在外键约束，则不允许更新和删除被引用的数据。除了restrict之外，这里可能的取值还有cascade（级联操作）和set null（设置为空），有兴趣的读者可以自行研究。

修改表的名字，例如将学生表的名字修改为 tb_stu_info。

ALTER TABLE `tb_student` RENAME TO `tb_stu_info`;

提示：一般情况下，请不要轻易修改数据库或表的名字。