KLucenの启示录

关系型数据库概述#

1. 数据持久化 - 将数据保存到能够长久保存数据的存储介质中，在掉电的情况下数据也不会丢失。

2. 数据库发展史 - 网状数据库、层次数据库、关系数据库、NoSQL 数据库、NewSQL 数据库。

   1970年，IBM的研究员E.F.Codd在Communication of the ACM上发表了名为A Relational Model of Data for Large Shared Data Banks的论文，提出了关系模型的概念，奠定了关系模型的理论基础。后来Codd又陆续发表多篇文章，论述了范式理论和衡量关系系统的12条标准，用数学理论奠定了关系数据库的基础。

3. 关系数据库特点。

   • 理论基础：关系代数（集合论、一阶谓词、关系运算）。
   • 具体表象：用二维表（有行和列）组织数据。
   • 编程语言：结构化查询语言（SQL）。
     • DDL：数据定义语言
     • DML：数据操作语言
     • DCL：数据控制语言
     • TCL：事务控制语言

4. ER模型（实体关系模型）和概念模型图。

   ER模型，全称为实体关系模型（Entity-Relationship Model），由美籍华裔计算机科学家陈品山先生提出，是概念数据模型的高层描述方式，如下图所示。

   
   • 实体 - 矩形框
   • 属性 - 椭圆框
   • 关系 - 菱形框
   • 重数 - 1:1（一对一） / 1（一对多） / M（多对多）

   实际项目开发中，我们可以利用数据库建模工具（如：PowerDesigner）来绘制概念数据模型，然后再设置好目标数据库系统，将概念模型转换成物理模型（如下图所示），最终生成创建二维表的 SQL（很多工具都可以根据我们设计的物理模型图以及设定的目标数据库来导出 SQL 或直接生成数据表）。

   

5. 关系数据库产品。

   • Oracle - 目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库，它实现了分布式处理的功能。在 Oracle 较新的版本中，还引入了多承租方架构，使用该架构可轻松部署和管理数据库云。
   • DB2 - IBM 公司开发的、主要运行于 Unix（包括 IBM 自家的 AIX）、Linux、以及 Windows 服务器版等系统的关系数据库产品。DB2 历史悠久且被认为是最早使用 SQL 的数据库产品，它拥有较为强大的商业智能功能。
   • SQL Server - 由 Microsoft 开发和推广的关系型数据库产品，最初适用于中小企业的数据管理，但是近年来它的应用范围有所扩展，部分大企业甚至是跨国公司也开始基于它来构建自己的数据管理系统。
   • MySQL - MySQL 是开放源代码的，任何人都可以在 GPL（General Public License）的许可下下载并根据个性化的需要对其进行修改。MySQL 因为其速度、可靠性和适应性而备受关注。
   • PostgreSQL - 在 BSD 许可证下发行的开放源代码的关系数据库产品。

MySQL 简介#

MySQL 最早是由瑞典的 MySQL AB 公司开发的一个开放源码的关系数据库管理系统，该公司于2008年被昇阳微系统公司（Sun Microsystems）收购。在2009年，甲骨文公司（Oracle）收购昇阳微系统公司，因此 MySQL 目前也是 Oracle 旗下产品。

MySQL 在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用于中小型网站开发。随着 MySQL 的不断成熟，它也逐渐被应用于更多大规模网站和应用，比如维基百科、谷歌（Google）、脸书（Facebook）、百度、淘宝、腾讯、新浪、去哪儿等都使用了 MySQL 来提供数据持久化服务。

甲骨文公司收购后昇阳微系统公司，大幅调涨 MySQL 商业版的售价，且甲骨文公司不再支持另一个自由软件项目 OpenSolaris 的发展，因此导致自由软件社区对于 Oracle 是否还会持续支持 MySQL 社区版（MySQL 的各个发行版本中唯一免费的版本）有所担忧，MySQL 的创始人麦克尔·维德纽斯以 MySQL 为基础，创建了 MariaDB（以他女儿的名字命名的数据库）分支。有许多原来使用 MySQL 数据库的公司（例如：维基百科）已经陆续完成了从 MySQL 数据库到 MariaDB 数据库的迁移。

安装 MySQL#

macOS环境#

macOS 系统安装 MySQL 是比较简单的，只需要从刚才说到的官方网站下载 DMG 安装文件并运行就可以了，下载的时候需要根据自己使用的是 Intel 的芯片还是苹果的 M1 芯片选择下载链接，如下图所示。

安装成功后，可以在“系统偏好设置”中找到“MySQL”，在如下所示的画面中，可以启动和停止 MySQL 服务器，也可以对 MySQL 核心文件的路径进行配置。

MySQL 基本命令#

1
show databases;

1
show character set;

1
show collation;

1
show engines;

1
show binary logs;

1
show tables;

建库建表#

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

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

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

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

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

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

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

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

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

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

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

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

  1
  show character set;

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

  如果要设置 MySQL 服务启动时默认使用的字符集，可以修改MySQL的配置并添加以下内容。

  1
  [mysqld]
  2
  character-set-server=utf8

  提示：如果不清楚如何修改 MySQL 的配置文件就先不要管它。

• 创建和删除数据库时，关键字database也可以替换为schema，二者作用相同。

• 建表语句中的not null是非空约束，它限定了字段不能为空；default用于为字段指定默认值，我们称之为默认值约束；primary key是主键约束，它设定了能够唯一确定一条记录的列，也确保了每条记录都是独一无二的，因为主键不允许重复；foreign key是外键约束，它维持了两张表的参照完整性，举个例子，由于学生表中为 col_id 字段添加了外键约束，限定其必须引用（references）学院表中的 col_id，因此学生表中的学院编号必须来自于学院表中的学院编号，不能够随意为该字段赋值。如果需要给主键约束、外键约束等起名字，可以使用constriant关键字并在后面跟上约束的名字。

• 建表语句中的comment 关键字用来给列和表添加注释，增强代码的可读性和可维护性。

• 在创建表的时候，可以自行选择底层的存储引擎。MySQL 支持多种存储引擎，可以通过show engines命令进行查看。MySQL 5.5 以后的版本默认使用的存储引擎是 InnoDB，它是我们推荐大家使用的存储引擎（因为更适合当下互联网应用对高并发、性能以及事务支持等方面的需求），为了 SQL 语句的向下兼容性，我们可以在建表语句结束处右圆括号的后面通过engine=innodb来指定使用 InnoDB 存储引擎。

  1
  show engines\G

  说明：上面的 \G 是为了换一种输出方式，在命令行客户端中，如果表的字段很多一行显示不完，就会导致输出的内容看起来非常不舒服，使用 \G 可以将记录的每个列以独占整行的的方式输出，这种输出方式在命令行客户端中看起来会舒服很多。

  1
  *************************** 1. row ***************************
  2
        Engine: InnoDB
  3
       Support: DEFAULT
  4
       Comment: Supports transactions, row-level locking, and foreign keys
  5
  Transactions: YES
  6
            XA: YES
  7
    Savepoints: YES
  8
  *************************** 2. row ***************************
  9
        Engine: MRG_MYISAM
  10
       Support: YES
  11
       Comment: Collection of identical MyISAM tables
  12
  Transactions: NO
  13
            XA: NO
  14
    Savepoints: NO
  15
  *************************** 3. row ***************************
  16
        Engine: MEMORY
  17
       Support: YES
  18
       Comment: Hash based, stored in memory, useful for temporary tables
  19
  Transactions: NO
  20
            XA: NO
  21
    Savepoints: NO
  22
  *************************** 4. row ***************************
  23
        Engine: BLACKHOLE
  24
       Support: YES
  25
       Comment: /dev/null storage engine (anything you write to it disappears)
  26
  Transactions: NO
  27
            XA: NO
  28
    Savepoints: NO
  29
  *************************** 5. row ***************************
  30
        Engine: MyISAM
  31
       Support: YES
  32
       Comment: MyISAM storage engine
  33
  Transactions: NO
  34
            XA: NO
  35
    Savepoints: NO
  36
  *************************** 6. row ***************************
  37
        Engine: CSV
  38
       Support: YES
  39
       Comment: CSV storage engine
  40
  Transactions: NO
  41
            XA: NO
  42
    Savepoints: NO
  43
  *************************** 7. row ***************************
  44
        Engine: ARCHIVE
  45
       Support: YES
  46
       Comment: Archive storage engine
  47
  Transactions: NO
  48
            XA: NO
  49
    Savepoints: NO
  50
  *************************** 8. row ***************************
  51
        Engine: PERFORMANCE_SCHEMA
  52
       Support: YES
  53
       Comment: Performance Schema
  54
  Transactions: NO
  55
            XA: NO
  56
    Savepoints: NO
  57
  *************************** 9. row ***************************
  58
        Engine: FEDERATED
  59
       Support: NO
  60
       Comment: Federated MySQL storage engine
  61
  Transactions: NULL
  62
            XA: NULL
  63
    Savepoints: NULL
  64
  9 rows in set (0.00 sec)

  下面的表格对MySQL几种常用的数据引擎进行了简单的对比。

  特性	InnoDB	MRG_MYISAM	MEMORY	MyISAM
  存储限制	有	没有	有	有
  事务	支持
  锁机制	行锁	表锁	表锁	表锁
  B树索引	支持	支持	支持	支持
  哈希索引			支持
  全文检索	支持（5.6+）			支持
  集群索引	支持
  数据缓存	支持		支持
  索引缓存	支持	支持	支持	支持
  数据可压缩				支持
  内存使用	高	低	中	低
  存储空间使用	高	低		低
  批量插入性能	低	高	高	高
  是否支持外键	支持

  通过上面的比较我们可以了解到，InnoDB 是唯一能够支持外键、事务以及行锁的存储引擎，所以我们之前说它更适合互联网应用，而且在较新版本的 MySQL 中，它也是默认使用的存储引擎。

• 在定义表结构为每个字段选择数据类型时，如果不清楚哪个数据类型更合适，可以通过 MySQL 的帮助系统来了解每种数据类型的特性、数据的长度和精度等相关信息。

  1
  ? data types

  说明：在 MySQLWorkbench 中，不能使用?获取帮助，要使用对应的命令help。

  1
  You asked for help about help category: "Data Types"
  2
  For more information, type 'help <item>', where <item> is one of the following
  3
  topics:
  4
     AUTO_INCREMENT
  5
     BIGINT
  6
     BINARY
  7
     BIT
  8
     BLOB
  9
     BLOB DATA TYPE
  10
     BOOLEAN
  11
     CHAR
  12
     CHAR BYTE
  13
     DATE
  14
     DATETIME
  15
     DEC
  16
     DECIMAL
  17
     DOUBLE
  18
     DOUBLE PRECISION
  19
     ENUM
  20
     FLOAT
  21
     INT
  22
     INTEGER
  23
     LONGBLOB
  24
     LONGTEXT
  25
     MEDIUMBLOB
  26
     MEDIUMINT
  27
     MEDIUMTEXT
  28
     SET DATA TYPE
  29
     SMALLINT
  30
     TEXT
  31
     TIME
  32
     TIMESTAMP
  33
     TINYBLOB
  34
     TINYINT
  35
     TINYTEXT
  36
     VARBINARY
  37
     VARCHAR
  38
     YEAR DATA TYPE

  获取 varchar 类型的帮助：

  1
  ? varchar

  执行结果：

  1
  Name: 'VARCHAR'
  2
  Description:
  3
  [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE
  4
  collation_name]
  5
  
  6
  A variable-length string. M represents the maximum column length in
  7
  characters. The range of M is 0 to 65,535. The effective maximum length
  8
  of a VARCHAR is subject to the maximum row size (65,535 bytes, which is
  9
  shared among all columns) and the character set used. For example, utf8
  10
  characters can require up to three bytes per character, so a VARCHAR
  11
  column that uses the utf8 character set can be declared to be a maximum
  12
  of 21,844 characters. See
  13
  http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html.
  14
  
  15
  MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus
  16
  data. The length prefix indicates the number of bytes in the value. A
  17
  VARCHAR column uses one length byte if values require no more than 255
  18
  bytes, two length bytes if values may require more than 255 bytes.
  19
  
  20
  *Note*:
  21
  
  22
  MySQL follows the standard SQL specification, and does not remove
  23
  trailing spaces from VARCHAR values.
  24
  
  25
  VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the
  26
  standard SQL way to define that a VARCHAR column should use some
  27
  predefined character set. MySQL uses utf8 as this predefined character
  28
  set. http://dev.mysql.com/doc/refman/5.7/en/charset-national.html.
  29
  NVARCHAR is shorthand for NATIONAL VARCHAR.
  30
  
  31
  URL: http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html

  在数据类型的选择上，保存字符串数据通常都使用 VARCHAR 和 CHAR 两种类型，前者通常称为变长字符串，而后者通常称为定长字符串；对于 InnoDB 存储引擎，行存储格式没有区分固定长度和可变长度列，因此 VARCHAR 类型和 CHAR 类型没有本质区别，后者不一定比前者性能更好。如果要保存的很大字符串，可以使用 TEXT 类型；如果要保存很大的字节串，可以使用 BLOB（二进制大对象）类型。在 MySQL 中，TEXT 和 BLOB又分别包括 TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB 三种不同的类型，它们主要的区别在于存储数据的最大大小不同。保存浮点数可以用 FLOAT 或 DOUBLE 类型，FLOAT 已经不推荐使用了，而且在 MySQL 后续的版本中可能会被移除掉。而保存定点数应该使用 DECIMAL 类型，它可以指定小数点前后有效数字的位数。如果要保存时间日期，DATETIME 类型优于 TIMESTAMP 类型，因为前者能表示的时间日期范围更大，后者底层其实就是一个整数，记录了指定的日期时间和 1970-01-01 00:00:00 相差多少个毫秒，该类型在 2038-01-19 03:14:07 之后就会溢出。

  对于自增字段 AUTO_INCREMENT，如果使用 MySQL 5.x 版本要注意自增字段的回溯问题，当然这个问题在 MySQL 8.x 中已经得到了很好的解决，当然，MySQL 8.x 还有很多其他的好处，不管是功能还是性能上都有很多的优化和调整，因此强烈推荐大家使用 MySQL 8.x 版本。对于高并发访问数据库的场景，AUTO_INCREMENT 不仅存在性能上的问题，还可能在多机结构上产生重复的 ID 值，在这种场景下，使用分布式 ID 生成算法（SnowFlake、TinyID等）才是最好的选择，有兴趣的读者可以自行研究。

删除表和修改表#

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

1
DROP TABLE `tb_student`;

或

1
DROP TABLE IF EXISTS `tb_student`;

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

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

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

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

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

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

1
ALTER TABLE `tb_student` DROP COLUMN `stu_tel`;

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

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

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

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

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

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

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

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

或

1
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。

1
ALTER TABLE `tb_student` RENAME TO `tb_stu_info`;

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

insert操作#

顾名思义，insert是用来插入行到二维表中的，插入的方式包括：插入完整的行、插入行的一部分、插入多行、插入查询的结果。我们通过如下所示的 SQL 向学院表中添加一个学院。

1
INSERT INTO `tb_college`
2
VALUES
3
    (DEFAULT, '计算机学院', '学习计算机科学与技术的地方');

其中，由于学院表的主键是一个自增字段，因此上面的 SQL 中用default表示该列使用默认值，我们也可以使用下面的方式完成同样的操作。

1
INSERT INTO `tb_college` (`col_name`, `col_intro`)
2
VALUES
3
    ('计算机学院', '学习计算机科学与技术的地方');

我们推荐大家使用下面这种做法，指定为哪些字段赋值，这样做可以不按照建表时设定的字段顺序赋值，可以按照values前面的元组中给定的字段顺序为字段赋值，但是需要注意，除了允许为null和有默认值的字段外，其他的字段都必须要一一列出并在values后面的元组中为其赋值。如果希望一次性插入多条记录，我们可以在values后面跟上多个元组来实现批量插入，代码如下所示。

1
INSERT INTO `tb_college`
2
    (`col_name`, `col_intro`)
3
VALUES
4
    ('外国语学院', '学习歪果仁的语言的学院'),
5
    ('经济管理学院', '经世济民，治理国家；管理科学，兴国之道'),
6
    ('体育学院', '发展体育运动，增强人民体质');

在插入数据时，要注意主键是不能重复的，如果插入的数据与表中已有记录主键相同，那么insert操作将会产生 Duplicated Entry 的报错信息。再次提醒大家，如果insert操作省略了某些列，那么这些列要么有默认值，要么允许为null，否则也将产生错误。在业务系统中，为了让insert操作不影响其他操作（主要是后面要讲的select操作）的性能，可以在insert和into之间加一个low_priority来降低insert操作的优先级，这个做法也适用于下面要讲的delete和update操作。

假如有一张名为tb_temp的表中有a和b两个列，分别保存了学院的名称和学院的介绍，我们也可以通过查询操作获得tb_temp表的数据并插入到学院表中，如下所示，其中的select就是我们之前提到的 DQL，在下一课中会详细讲解。

1
INSERT INTO `tb_college`
2
    (`col_name`, `col_intro`)
3
SELECT `a`, `b`
4
  FROM `tb_temp`;

delete 操作#

如果需要从表中删除数据，可以使用delete操作，它可以帮助我们删除指定行或所有行，例如我们要删除编号为1的学院，就可以使用如下所示的 SQL。

1
DELETE
2
  FROM `tb_college`
3
 WHERE col_id=1;

注意，上面的delete操作中的where子句是用来指定条件的，只有满足条件的行会被删除。如果我们不小心写出了下面的 SQL，就会删除学院表中所有的记录，这是相当危险的，在实际工作中通常也不会这么做。

1
DELETE
2
  FROM `tb_college`;

需要说明的是，即便删除了所有的数据，delete操作不会删除表本身，也不会让 AUTO_INCREMENT 字段的值回到初始值。如果需要删除所有的数据而且让 AUTO_INCREMENT 字段回到初始值，可以使用truncate table执行截断表操作，truncate的本质是删除原来的表并重新创建一个表，它的速度其实更快，因为不需要逐行删除数据。但是请大家记住一点，用truncate table删除数据是非常危险的，因为它会删除所有的数据，而且由于原来的表已经被删除了，要想恢复误删除的数据也会变得极为困难。

update 操作#

如果要修改表中的数据，可以使用update操作，它可以用来删除指定的行或所有的行。例如，我们将学生表中的“杨过”修改为“杨逍”，这里我们假设“杨过”的学号为1001，代码如下所示。

1
UPDATE `tb_student`
2
   SET `stu_name`='杨逍'
3
 WHERE `stu_id`=1001;

注意上面 SQL 中的where子句，我们使用学号作为条件筛选出对应的学生，然后通过前面的赋值操作将其姓名修改为“杨逍”。这里为什么不直接使用姓名作为筛选条件，那是因为学生表中可能有多个名为“杨过”的学生，如果使用 stu_name 作为筛选条件，那么我们的update操作有可能会一次更新多条数据，这显然不是我们想要看到的。还有一个需要注意的地方是update操作中的set关键字，因为 SQL 中的=并不表示赋值，而是判断相等的运算符，只有出现在set 关键字后面的=，才具备赋值的能力。

如果要同时修改学生的姓名和生日，我们可以对上面的update语句稍作修改，如下所示。

1
UPDATE `tb_student`
2
   SET `stu_name`='杨逍'
3
     , `stu_birth`='1975-12-29'
4
 WHERE `stu_id`=1001;

update语句中也可以使用查询的方式获得数据并以此来更新指定的表数据，有兴趣的读者可以自行研究。在书写update语句时，通常都会有where子句，因为实际工作中几乎不太会用到更新全表的操作，这一点大家一定要注意。

完整的数据#

下面我们给出完整的向 school 数据库的五张表中插入数据的 SQL。

1
USE `school`;
2

3
-- 插入学院数据
4
INSERT INTO `tb_college`
5
    (`col_name`, `col_intro`)
6
VALUES
7
    ('计算机学院', '计算机学院1958年设立计算机专业，1981年建立计算机科学系，1998年设立计算机学院，2005年5月，为了进一步整合教学和科研资源，学校决定，计算机学院和软件学院行政班子合并统一运作、实行教学和学生管理独立运行的模式。 学院下设三个系：计算机科学与技术系、物联网工程系、计算金融系；两个研究所：图象图形研究所、网络空间安全研究院（2015年成立）；三个教学实验中心：计算机基础教学实验中心、IBM技术中心和计算机专业实验中心。'),
8
    ('外国语学院', '外国语学院设有7个教学单位，6个文理兼收的本科专业；拥有1个一级学科博士授予点，3个二级学科博士授予点，5个一级学科硕士学位授权点，5个二级学科硕士学位授权点，5个硕士专业授权领域，同时还有2个硕士专业学位（MTI）专业；有教职员工210余人，其中教授、副教授80余人，教师中获得中国国内外名校博士学位和正在职攻读博士学位的教师比例占专任教师的60%以上。'),
9
    ('经济管理学院', '经济学院前身是创办于1905年的经济科；已故经济学家彭迪先、张与九、蒋学模、胡寄窗、陶大镛、胡代光，以及当代学者刘诗白等曾先后在此任教或学习。');
10

11
-- 插入学生数据
12
INSERT INTO `tb_student`
13
    (`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`)
14
VALUES
15
    (1001, '杨过', 1, '1990-3-4', '湖南长沙', 1),
16
    (1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
17
    (1033, '王语嫣', 0, '1989-12-3', '四川成都', 1),
18
    (1572, '岳不群', 1, '1993-7-19', '陕西咸阳', 1),
19
    (1378, '纪嫣然', 0, '1995-8-12', '四川绵阳', 1),
20
    (1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
21
    (2035, '东方不败', 1, '1988-6-30', NULL, 2),
22
    (3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
23
    (3755, '项少龙', 1, '1993-1-25', '四川成都', 3),
24
    (3923, '杨不悔', 0, '1985-4-17', '四川成都', 3);
25

26
-- 插入老师数据
27
INSERT INTO `tb_teacher`
28
    (`tea_id`, `tea_name`, `tea_title`, `col_id`)
29
VALUES
30
    (1122, '张三丰', '教授', 1),
31
    (1133, '宋远桥', '副教授', 1),
32
    (1144, '杨逍', '副教授', 1),
33
    (2255, '范遥', '副教授', 2),
34
    (3366, '韦一笑', DEFAULT, 3);
35

36
-- 插入课程数据
37
INSERT INTO `tb_course`
38
    (`cou_id`, `cou_name`, `cou_credit`, `tea_id`)
39
VALUES
40
    (1111, 'Python程序设计', 3, 1122),
41
    (2222, 'Web前端开发', 2, 1122),
42
    (3333, '操作系统', 4, 1122),
43
    (4444, '计算机网络', 2, 1133),
44
    (5555, '编译原理', 4, 1144),
45
    (6666, '算法和数据结构', 3, 1144),
46
    (7777, '经贸法语', 3, 2255),
47
    (8888, '成本会计', 2, 3366),
48
    (9999, '审计学', 3, 3366);
49

50
-- 插入选课数据
51
INSERT INTO `tb_record`
52
    (`stu_id`, `cou_id`, `sel_date`, `score`)
53
VALUES
54
    (1001, 1111, '2017-09-01', 95),
55
    (1001, 2222, '2017-09-01', 87.5),
56
    (1001, 3333, '2017-09-01', 100),
57
    (1001, 4444, '2018-09-03', NULL),
58
    (1001, 6666, '2017-09-02', 100),
59
    (1002, 1111, '2017-09-03', 65),
60
    (1002, 5555, '2017-09-01', 42),
61
    (1033, 1111, '2017-09-03', 92.5),
62
    (1033, 4444, '2017-09-01', 78),
63
    (1033, 5555, '2017-09-01', 82.5),
64
    (1572, 1111, '2017-09-02', 78),
65
    (1378, 1111, '2017-09-05', 82),
66
    (1378, 7777, '2017-09-02', 65.5),
67
    (2035, 7777, '2018-09-03', 88),
68
    (2035, 9999, '2019-09-02', NULL),
69
    (3755, 1111, '2019-09-02', NULL),
70
    (3755, 8888, '2019-09-02', NULL),
71
    (3755, 9999, '2017-09-01', 92);

注意：上面的insert语句使用了批处理的方式来插入数据，这种做法插入数据的效率比较高。

创建用户#

我们可以使用下面的 SQL 来创建一个用户并为其指定访问口令。

1
CREATE USER 'wangdachui'@'%' IDENTIFIED BY 'Wang.618';

上面的 SQL 创建了名为 wangdachui 的用户，它的访问口令是 Wang.618，该用户可以从任意主机访问数据库服务器，因为 @ 后面使用了可以表示任意多个字符的通配符 %。如果要限制 wangdachui 这个用户只能从 192.168.0.x 这个网段的主机访问数据库服务器，可以按照下面的方式来修改 SQL 语句。

1
DROP USER IF EXISTS 'wangdachui'@'%';
2

3
CREATE USER 'wangdachui'@'192.168.0.%' IDENTIFIED BY 'Wang.618';

此时，如果我们使用 wangdachui 这个账号访问数据库服务器，我们几乎不能做任何操作，因为该账号没有任何操作权限。

授予权限#

我们用下面的语句为 wangdachui 授予查询 school 数据库学院表（tb_college）的权限。

1
GRANT SELECT ON `school`.`tb_college` TO 'wangdachui'@'192.168.0.%';

我们也可以让 wangdachui 对 school 数据库的所有对象都具有查询权限，代码如下所示。

1
GRANT SELECT ON `school`.* TO 'wangdachui'@'192.168.0.%';

如果我们希望 wangdachui 还有 insert、delete 和 update 权限，可以使用下面的方式进行操作。

1
GRANT INSERT, DELETE, UPDATE ON `school`.* TO 'wangdachui'@'192.168.0.%';

如果我们还想授予 wangdachui 执行 DDL 的权限，可以使用如下所示的 SQL。

1
GRANT CREATE, DROP, ALTER ON `school`.* TO 'wangdachui'@'192.168.0.%';