第03章 创建数据库和表

SQL（结构化查询语言）是由IBM公司提出的，不仅具有丰富的查询功能，而且具有数据定义和数据控制功能，是集数据定义（DDL），数据查询（DQL），数据操纵（DML）和数据控制（DCL）于一体的关系数据语言。它是关系数据库的标准语言。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着国际标准化组织（ISO）颁布了SQL正式国际标准。后来不断的发展，先后有SQL-86，SQL-89，SQL-92，SQL-99等标准。SQL92 和 SQL99 是经典的 SQL 标准，也分别叫做 SQL-2 和 SQL-3 标准。我们所学的大部分SQL语句都是在这两个版本中制定的。虽然制定了标准，但是不同产品（MYSQL,ORACLE,SQLSERVER）对标准的符合程度存在很大的差异。尽管很多产品都号称自己符合 SQL 标准，并不意味着应用可以容易的在它们(例如从MYSQL迁移到ORACLE上面)之间切换。MYSQL作为开源软件来讲，对于SQL标准的支持还是不错的。所以，我们几乎不用担心，本课程中讲解的SQL语句都能够在MYSQL上正确运行。

我们首先介绍SQL的数据定义（DDL），也就是创建数据库和表结构。

创建数据库

create database 数据库名称;

例如：create database student; 创建一个名称为“student”的数据库。

创建完毕之后我们需要显示数据库列表：show databases;

接下来使用数据库命令：use 数据库名称;

例如：use student;

上述命令的含义是说，接下来的SQL语句都将在“student”数据库内执行。

最后是删除数据库：drop database 数据库名称;

例如：drop database student;

其实我们也可以在图形化工具SQLyog中创建数据库。

在左边空白区域“右击”弹出快捷菜单，第一菜单是“Refresh Object Browser”刷新按钮，第二菜单就是“Create Database…”创建数据库。我们选择第二个菜单

我们数据库名称录入“student”，字符编码选择“utf8mb4”，字符集选择“utf8mb4_general_ci”。

然后点击“Create”按钮完成创建。

在左边的“数据库列表”区域就会显示我们刚刚创建的“student”数据库。如果没有显示的话，我们在空白区域右击选择第一个菜单“Refresh Object Browser”刷新按钮即可。这个过程实际上执行的就是如下命令：

CREATE DATABASE `student`CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

创建表

创建表的命令语法为：

create table 表名 (
	
	列名1 	数据类型 	[列级完整性约束条件] ,
	列名2 	数据类型 	[列级完整性约束条件] ,
    列名3 	数据类型 	[列级完整性约束条件] ,
    ……
    表级完整性约束条件
);

例如，我们创建一个班级表：

CREATE TABLE `class_info` (
  `class_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT '班级id',
  `class_name` varchar(10) NOT NULL DEFAULT '' COMMENT '班级名称',
  `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  PRIMARY KEY (`class_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级信息表';

我们创建了一个名称为“class_info”的数据表，它的结构类似于Excel二维表格

二维表格中的“列”就代表我们的字段，例如：class_id,class_name,add_time三个字段，他们分别代表班级id，班级名称和添加时间。二维表格中的每一行都代表一条“记录”。创建一个数据库表table，主要就是创建该表的字段和一些约束。字段是用来存储数据的，所以它应该有“数据类型”的概念，首先是MySQL的整数类型，如下所示：

tinyint			1 字节	(-128，127)，(0，255)
smallint		2 字节	(-32 768，32 767)，(0，65 535)
mediumint		3 字节	(-8 388 608，8 388 607)，(0，16 777 215)
int				4 字节	(-2 147 483 648，2 147 483 647)，(0，4 294 967 295)
bigint			8 字节  ( 非常大了 )

这个就不用解释了，类型空间不一样，存储的整数范围也不一样。默认情况下，整数类型可以存储负数，我们可以使用关键字“UNSIGNED”来声明该字段只存储正数，那么显然它的存储范围会增加一倍。整数类型后面的括号指定显示宽度，例如 int(4) ，指示最大显示宽度，与整数类型的存储数值范围无关。也就是说int(4)并不是说只能存储4个长度的数值。

接下来就是小数类型，也称之为浮点类型，如下所示

float		4 字节	单精度浮点数值
double		8 字节	双精度浮点数值
decimal		定点数类型（精确的数值）

这里需要说明的是decimal小数类型。decimal类型不同于float和double，它是以字符串存储。它的语法是decimal(m,d)，m是数字的最大数（精度），d是小数位。

接下来是字符串类型，如下所示

char		0-255字节			定长字符串
varchar		0-65535 字节		变长字符串

char(m)为固定长度字符串， m就是字符串存储的最大长度， 最大存储255字节空间。varchar(m)是长度可变的字符串，m也是字符串存储的最大长度，最大存储65535字节空间。假如我们定义char(10) 和varchar(10)两个字段类型，两者均可以存储10个字符，包括英文，中文或者特殊字符等等。如果超过10个字符，两者都不能存储超过的部分，会被自动截断。如果没有超过10个字符的话，char类型会添加空格方式来填充剩余空间，而varchar类型不会。这就是固定长度和变动长度的区别。请注意，字符类型是有最大长度限制的，我们不能在括号内随意填写数值。例如，char()中只能填写小于255的数值。为什么呢？这里需要说明的是，字符串涉及到编码格式的影响。我们这里使用的是“utf8mb4”编码格式，也就是我们平时所说的“utf-8”编码格式。在我们MySQL 5.7.44版本中，经过测试，char类型最大可以存储255个中文字符，显然它已经超出了255个字节空间。对于varchar类型的话，最大65535字节空间，按照“utf-8”编码格式，3个字节存储一个字符的话，最大可以存储个65535/3=21845 字符。 而经过实际测试，它的括号内可以设置16300 长度的字符个数。所以，为了保险起见，我们使用varchar存储字符数据的时候，尽量控制在1000以内吧。

如何选择char和varchar这两个数据类型呢？varchar是自适应长度，因为存储空间上相比较char来讲，会小一些，这样不浪费存储空间和内存空间，查询性能会好一些。但是，varchar修改数据比较麻烦，因为它需要计算修改前后的长度，并再次合理安排存储空间。char由于是固定长度，因此它会浪费存储空间，也就是上面提到的空格。但是，char类型在进行数据修改的时候就比较方便了，因为是固定长度，不需要考虑存储空间的操作了。所以，char和varchar的选择就在于是优先考虑查询，还是优先考虑修改。如果数据频繁修改的话，可以考虑使用char类型，如果查询频繁的话，可以考虑使用varchar类型。

如果需要进行超长字符串，也就是大量文本存储的时候，可以考虑如下数据类型：

tinytext		0-255字节			    短文本
text			0-65 535字节			长文本
mediumtext	    0-16 777 215字节		中等长度文本
longtext		0-4 294 967 295字节	    超大长度文本

tinyblob		0-255字节			    短的二进制数据
blob			0-65 535字节			长的二进制数据
mediumblob	    0-16 777 215字节		中等长度的二进制数据
longblob		0-4 294 967 295字节	    超大长度的二进制数据

一般在保存少量字符串的时候可以选择char或varchar，而在保存大文本时选择使用text或 blob，二者之间的主要差别是blob能用来保存二进制数据，比如照片等；而text只能保存字符数据，比如一篇文章。建议：把blob或text字段分离到单独的表中。

最后，我们介绍一下时间类型

date			3字节	yyyy-mm-dd				日期值
time			3字节	hh:mm:ss				时间值
year			1字节	yyyy					年份值
datetime		8字节	yyyy-mm-dd hh:mm:ss		混合日期和时间值
timestamp	    4字节	1970-01-01 00:00:00 	混合日期和时间值

这里主要介绍一下datetime和timestamp的区别。datetime在存储日期的时候，按照实际输入的格式存储，输入什么存储什么，与时区无关。而timestamp存储的是utf(世界标准时间)格式保存，存储的时候根据当前时区转换，检索时候再转换为当前时区。即查询的时候，根据当前时区不同，显示不同的时间值。我们可以将timestamp理解成时间戳，然后按照本地时区转化成当地时间。也就是说，本地时区不同，它显示的时间是不一样的。时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。在MySQL 5.6.4之前，datetime存储占用8个字节，而timestamp是占用4字节。但是在5.6.4之后，由于这两个类型允许有小数部分，所以占用的存储空间和以前不同；MySQL规范规定，datetime的非小数部分需要5个字节，而不是8个字节，而timestamp的非小数部分是需要4个字节，并且这两个部分的小数部分都需要0到3个字节，具体取决于存储值的小数秒精度。总结，一般情况使用推荐datetime类型，如果你的需求是需要对应不同的国家时区，那么就选择这个timestamp类型。

接下来介绍完整性约束，它主要作用就是对字段进行数据约束，保证其完全性。

1. 主键，是表中一列或多列。主键能唯一标示表中的一条记录，同时加速查询速度。主键和记录的关系，就如同身份证编号与人之间的关系一样。主键分单字段和多字段两种。在上面建表sql中的primary key (`class_id`)就表示将`class_id`设置为主键。还有一种写法，就是在`class_id`后面的约束部分添加primary key。什么情况下会使用多字段呢？一般是在一些关联表中，例如订单商品表，该表的一条记录由订单id和商品id共同决定。

2. 外键，是表中的一列或多列，但这些列必须是其他表的主键。也就是说，外键主要用来表示表与表之间的联系。例如一对多的关系中，学生属于某一个班级，那么学生表中就应该有一个班级id字段，这个字段在学生表中就是外键，在班级表中就是主键。外键的用户是维护数据完整性，它要求外键的值必须在主键中存在。也就是说，学生表里面的班级id的所有数值，在班级表中都必须存在。举例，张三所在的班级id是20，那么在班级表中就必须存在一个id=20的班级记录信息。因为张三不可能存在于一个不存在的班级中。

这里需要大家注意的是，很多时候，我们不会创建外键约束，那是因为它会给我们数据插入的时候带来一些麻烦。还是上面的例子，我们必须先插入班级信息表数据，才能拆入学生信息表数据。否则，学生的班级id在班级表中找不到，就不允许插入操作。但是，实际情况，我们的插入sql语句只是前后执行顺序的区别，所有的数据都是完整的。怎么办呢？可以先取消外键约束，然后插入数据，最后再恢复外键。

3. 非空约束，也就是not null。指字段的值不能为空（null），通常搭配默认值（default）使用。也就是说，如果插入新记录的时候，不给非空字段数值的话，就使用默认值填充。非空约束可以防止很多不必要的错误发生。因为空（null）数据读取后赋予编程语言中的变量之后，也可能造成null值，也就是空指针异常。

4. 唯一性约束，也就是unique。指字段的数值不能重复（唯一），这个类似于主键（唯一）。比如，人员信息表中，如果出现身份证编号字段的话，我们就可以设置其唯一性约束。但是，这样的话，这个字段就必须搭配非空约束。因为空的重复也属于重复，破坏唯一性原则。也就是说，该字段可以为空，但只允许一条记录为空（没有意义），所以搭配非空约束使用。

5. 自增属性，也就是auto_increment。该约束一般配合数值类型主键使用。自增字段在插入数据的时候，可以不用提供数值，由MySQL自己来进行递增计算。

回到我们刚刚创建“class_info”的SQL脚本中，我们详细介绍一下该表的结构

字段`class_id`，数据类型为SMALLINT(5)整型，UNSIGNED无符号代表只存储正数，NOT NULL不允许为空，AUTO_INCREMENT自增属性，COMMENT是备注说明“班级id”。

字段`class_name`，数据类型为VARCHAR(10)变长字符串型，最多存储10个字符（含中文），NOT NULL不允许为空，DEFAULT '' 默认为空字符串，COMMENT备注“班级名称”。

字段`add_time`，数据类型为DATETIME时间类型，NOT NULL不为空， 可以使用“DEFAULT CURRENT_TIMESTAMP”设置当前时间为默认值。

接下来PRIMARY KEY (`class_id`)代表当前表的主键为`class_id`字段。

最后介绍ENGINE=INNODB设置表引擎为INNODB类型，默认utf8mb4编码格式。

使用SQL语句创建表的好处在于，我们可以将脚本保存成文件，以便于存储和传递。当然，我们还可以通过图形化工具SQLyog来创建表结构。

我们在“Tables”处右击，然后选择“Create Table……”

在这里，我们就可以添加一个个字段了，首先添加`class_id`字段

字段名称（Field Name）就是class_id；数据类型（Datatype）就是smallint，长度为5；没有默认值（Default），因为主键自增的话，不需要默认值；接下来是否主键（PK？），我们勾选即可表示主键；接下来是否不为空（Not Null？），我们勾选不为空即可；接下来是否无符号（Unsigned？），我们勾选即可；接下来是否自增（Auto Incr？），我们勾选即可；接下来是否补零（Zerofill？），我们不用选择；最后就是备注（Comment），填写“班级id”即可。接下来，我们将“class_name”和“add_time”两个字段也添加上去。

字段填写完毕之后，我们点击下面的“Advanced Properties…”高级属性按钮。

这里我们选择“InnoDB”引擎和表备注名称“班级信息表”即可，然后点击“OK”回到刚才的界面，点击“Create Teable…”按钮。

输入表名称“class_info”后点击“OK”按钮。

这里询问是否继续创建表，我们直接点击“否”就行了。

我们可以展开树形结构，查看表的详细信息。

通过图形化工具SQLyog来创建表结构还是比较简单的，如何获取SQL脚本呢？

先选中表“class_info”，然后点击“info”选项卡，就可以看到该表的详细信息，滚动下拉

就可以看到创建表的SQL语句了，我们可以复制出来保存成文件。

接下来，我们来创建另一张表，我们直接给出SQL脚本内容：

CREATE TABLE `student_info` (
  `stu_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `class_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' COMMENT '班级id',
  `stu_name` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '姓名',
  `stu_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' COMMENT '年龄',
  `stu_sex` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '性别',
  `add_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
  PRIMARY KEY (`stu_id`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

我们可以直接复制上面的SQL脚本内容放到SQLyog中去执行。

一般情况下，我在开发阶段都会使用图形化工具来操作数据库。但是，如果遇到修改数据库表结构的时候，为了方便记录日志，或者保存现有数据的安全性，我们一般都采用SQL脚本来修改表结构。或者说，在正式生产环境中，我们不会存在图形化工具来辅助我们做修改，只能通过SQL脚本来完成。首先，我们先介绍如何查看表结构。使用 describe/desc 命令可以查看表字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，其语法格式如下： 

describe 表名;

或者

desc 表名;

例如：desc class_info;

在MySQL自带的客户端中执行效果如下：

在SQLyog中执行效果如下

其实两者的结果数据是一样的，只不过图形化显示效果更好一下。接下来，我们开始介绍如何修改表结构，它主要是添加字段，删除字段和修改字段及其属性。

1. 修改表名，具体语法格式如下：

alter table 旧表名 rename 新表名;
例如：alter table `class_info` rename `class_info2`;

2. 修改字段名，具体语法格式如下：

alter table 表名 change 旧名称 新名称 数据类型;
例如：alter table `class_info` change `class_name` `class_name2` varchar(10);

3. 修改字段数据类型，具体语法格式如下：

alter table 表名 modify 字段名 新的数据类型;
例如：alter table `class_info` modify `class_name` char(10);

4. 添加字段，具体语法格式如下：

alter table 表名 add 字段名 数据类型 列级完整性约束;
例如：alter table `class_info` add `add_time2` int(10);
例如：alter table `class_info` add `new_id` int(10) first;
例如：alter table `class_info` add `class_name2` char(10) after `class_name`;

以上三条语句主要区别在于添加新字段的位置不同。第一个添加在最后位置，第二个添加在最前位置，第三是指定某字段的后面。

5. 修改字段排序，具体语法格式如下：

alter table 表名 modify 字段名 数据类型 first | after 字段2;
例如：alter table `class_info` modify `add_time` datetime after `class_id`;

6. 删除字段，具体语法格式如下：

alter table 表名 drop 字段名;
例如：alter table `class_info` drop `add_time2`;

7. 添加主键约束（先取消class_id的主键和自增属性）：

alter table 表名 add constraint 主键名 primary key 表名(字段名);
例如：alter table `class_info` add constraint `class_id` primary key `class_info`(`class_id`);

一般情况下，主键名同字段名一致即可。

8. 删除主键约束：

alter table 表名 drop primary key;
例如：alter table `class_info` drop primary key;

注意，自增的主键不允许删除。

9. 添加外键约束：

alter table 从表 add constraint 外键名 foreign key 从表(字段名) references 主表(字段名);
例如：alter table `student_info` add constraint `class_id` foreign key `student_info`(`class_id`) references `class_info`(`class_id`);

10. 删除表的外键约束：

alter table 表名 drop foreign key 外键名;
例如：alter table `student_info` drop foreign key `class_id`;

11. 添加唯一约束：

alter table 表名 add unique key 约束名(字段名);
例如：alter table `student_info` add unique key `stu_name`(`stu_name`);

12. 删除唯一约束：

alter table 表名 drop index 约束名;
例如：alter table `student_info` drop index `stu_name`;

13. 删除表：

drop table 表名;
例如：drop table `student_info`;

备注：当存在外键约束时主表不能被直接删除。