菜鸟崛起 DB Chapter 5 MySQL 5.6数据库表的基本操作

5   数据库表的基本操作

在数据库中，数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。数据表被定义为列的集合，数据在表中是按照行和列的格式来存储的。每一行代表一条唯一的记录，每一列代表记录中的一个域。在这里我们还可以认真思考一下什么是表数据，什么是表结构。这个前面提过。

5.1  创建数据表

创建完数据库之后，接下来的工作就是创建表了，因为将数据库比作存储商品的仓库的话，那么表就是存储数据的货架，是用来直接存储数据的。创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性约束的过程。

5.1.1 创建表的语法形式

在创建数据表的时候应该指定数据库。因为表示基于数据库操作的，如果没有选择数据库，操作表会出现“no database selected”错误.

创建表的语法如下：

CREATE TABLE 表名(

列名 列类型[列级别的约束条件][默认值],

列名 列类型[列级别的约束条件][默认值]，

......

[表级别约束条件]

);

使用CREATE TABLE创建表时，必须指定一下信息：

• 要创建的表的名词，不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
• 数据表中每一个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

练习：创建员工表tb_emp1，表结构如下：

CREATE DATABASE test_db; //创建表的容器数据库

USE test_db; //切换数据库

CREATE TABLE tb_emp1( //创建表

id INT(11),

NAME VARCHAR(25),

deptID INT(11),

salary FLOAT

);

SHOW TABLES;

表已经被创建

5.1.2 使用主键约束

主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constrain）要求主键列的数据唯一，并且不允许为空。主键能够惟一地标识表中的一条数据，可以结合外键来定义不同数据表之间的关系，并且可以加速数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系，它们之间是一一对应的。主键分为两种类型：单子段主键和多字段联合主键。表中并不一定要存在主键，主键的目的在于关联表关系，如果不存在关联关系，那么不需要设置主键。

单子段主键

单子段主键由一个字段构成，设置主键约束有两种方式。

定义列的同时指定主键：

CREATE TABLE tb_emp2(

id INT(11) PRIMARY KEY,

NAME VARCHAR(25),

deptID INT(11),

salary FLOAT

)

定义完列以后，再定义主键

CREATE TABLE tb_emp3(

id INT(11),

NAME VARCHAR(25),

deptID INT(11),

salary FLOAT,

PRIMARY KEY(id)

)

多字段主键

多字段主键是主键由多个字段组成

CREATE TABLE tb_emp4(

id  INT(11),

NAME VARCHAR(25),

deptID INT(11),

salary FLOAT,

PRIMARY KEY(id,NAME)

)

注意多字段主键的定义不能使用下面方式：

CREATE TABLE tb_emp4(

id  INT(11) PRIMARY KEY,

NAME VARCHAR(25) PRIMARY KEY,

deptID INT(11),

salary FLOAT

)

5.1.3 使用外键约束

外键用来在两个表的数据之间建立链接。它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以是空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键：首先保证他是表的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表tb_dept的主键是id，在员工表tb_emp5中有一个键deptID与这个id关联。

主键（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表既是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表既是从表。

创建外键约束的语法：

CONSTRAINT <外建名> FOREIGN KEY(字段名1，字段名2。。。) REFERENCES <主表名>(主键1，主键2.。。)

外键名为定义的外键约束的名称，一个表中不能有相同名称的外键，我们在一个表中可能有多个外键，它们在各自表中的字段名称相同，此时我们就可在进行外键约束的时候定义两个不同的外键名针对这两个外键。字段名表示子表需要添加外键约束的字段列：主表名既被子表外键所依赖的表的名称；主键列表示主表中定义的主键列。或者列组合。

练习：定义数据表tb_emp5，并在tb_emp5表上创建外键约束。

创建部门表tb_demp1，别结构如下：

定义数据表tb_emp5,让他的键作为外键关联到tb_demp1的主键；

CREATE TABLE tb_dept1(

id INT(11) PRIMARY KEY,

NAME VARCHAR(22),

location VARCHAR(50)

)

CREATE TABLE tb_emp5(

id INT(11) PRIMARY KEY,

NAME VARCHAR(25),

deptid INT(11),

salary FLOAT,

CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptid) REFERENCES tb_dept1(id)

)

SELECT * FROM tb_emp5;

多个外键：

/*订单项表*/

CREATE TABLE orderitem (

iid CHAR(32) PRIMARY KEY,/*主键*/

count INT,/*数量*/

subtotal DECIMAL(10,2),/*小计*/

oid CHAR(32),/*所属订单*/

bid CHAR(32),/*订单项所指的商品*/

FOREIGN KEY (oid) REFERENCES orders (oid),/*建立主外键关系*/

FOREIGN KEY (bid) REFERENCES book (bid)/*建立主外键关系*/

);

分析：上面主表是相关联的键所在的表既tb_dept1，从表是tb_emp5,

关联是在关系型数据库中，相关表之间的联系。它是通过相容或相同的属性或属性组来表示。子表的外键必须是关联父表的主键，并且关联字段的数据类型必须匹配，如果类型不匹配会出现如下错误：

CREATE TABLE tb_emp6(

id INT(11) PRIMARY KEY,

NAME VARCHAR(25),

deptid VARCHAR(8),

salary FLOAT,

CONSTRAINT fu_emp6_dept1 FOREIGN KEY(deptid) REFERENCES tb_dept1(id)

)

提示不能添加外键约束。

5.1.4 使用非空约束

非空约束（Not NULL Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错。

练习：定义数据表tb_emp6，指定员工姓名不能为空；

CREATE TABLE tb_emp7(

id INT(11) PRIMARY KEY,

NAME VARCHAR(25) NOT NULL,

deptid INT(11),

salary FLOAT,

CONSTRAINT fk_emp7_dept1 FOREIGN KEY(deptid) REFERENCES tb_dept1(id)

)

5.1.5 使用唯一性约束

唯一约束（Unique Constraint）要求该列唯一，允许为空，但是只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

定义唯一约束的语法有两种：

字段名  数据类型  UNIQUE

CONSTRAINT <约束名> UNIQUE<字段名>

练习：定义部门表tb_dept2，指定部门表名称唯一；

CREATE TABLE tb_dept2(

id INT(11) PRIMARY KEY,

NAME VARCHAR(22) UNIQUE,

location VARCHAR(50)

)

另外一种方式是定义完所以列后指定唯一约束：

CREATE TABLE tb_dept3(

id INT(11) PRIMARY KEY,

NAME VARCHAR(22),

location VARCHAR(50),

CONSTRAINT STH UNIQUE(NAME)

)

其中 STH是约束名，类似于定义外键时的给外键的外键名。

UNIQUE和PRIMARY KEY的区别：一个表中可以有多个字段声明为UNIQUE，但只能有一个主键声明；声明为主键的类非空唯一。

5.1.6 使用默认约束

默认约束（Default Constraint）指定某列的默认值。如男性同学较多，性别就可以默认为‘男’。如果插入一条记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。

语法如下：

字段名  数据类型  DEFAULT 默认值

练习：定义数据表tb_emp8。指定员工的部门编号默认为1111；

CREATE TABLE tb_emp8(

id INT(11) PRIMARY KEY,

NAME VARCHAR(25) NOT NULL,

deptid INT(11) DEFAULT 1111,

salary FLOAT,

CONSTRAINT fk_emp8_dept2 FOREIGN KEY(deptid) REFERENCES tb_dept2(id)

)

5.1.7 设置表的属性值自动增加

在数据库应用中，有时候我们希望在每次插入数据时候，系统能够自动生成主键值。可以通过为主键添加AUTO_INCREMENT关键字来实现。默认的，在MySQL中AUTO_INCREMENT是默认自增长的，意思是从1开始增长。一个表中只能有一个字段使用AUTO_INCREMENT约束，且该字段必须是主键的一部分。AUTO_INCRMENT约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT等）。

练习：定义数据表tb_emp9，指定员工的编号自动增加，并插入两条数据验证

CREATE TABLE tb_emp9(

id INT(11) PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(25) NOT NULL,

deptid INT(11),

salary FLOAT

)

INSERT INTO tb_emp9(NAME,salary) VALUES('张三',1000),('李四',1200),('王五',1500)

//注意中英文符号

SELECT * FROM tb_emp9

5.2  查看数据表结构

我们在创建完表之后经常会查看表的结构，以查看是否是我们理想创建的表，下面我们来了解一下如何查看表结构，MySQL提供了两种查看表结构的方法。

5.2.1 查看表的基本结构

DESCRIBE/DESC语句可以查看表的字段信息，其中包括：字段表、字段数据类型，是否是主键，是否有默认值等。

查看tb_emp8表的结构：

NULL：表示该列是否可以存储NULL值。

Key：表示该列是否已编制索引，PRI表示该列是表主键的一部分，UNI表示该列是索引的一部分；MUL表示在列中某个给定值允许出现多次。

Default：表示该列是否有默认值，如果有的话默认值是多少

Extra:表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

5.2.2 查看表的详细结构

SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句（除了查看创建表的详细语句，还可以查询出存储引擎和字符编码），注意如果不加‘\G’参数，显示结果可能混乱，所以建议添加’\G’易于查看。

5.3  修改数据表

修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常见的修改表操作有修改表明、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置，更改表的存储引擎，删除表的外键约束等。下面一一实验一下。

5.3.1 修改表名

具体语法：

ALTER TABLE 旧表名 RENAME TO 新表名

其中TO是可选参数，使用与否不影响结果。

ALTER TABLE tb_dept3 RENAME TO tb_deptment3

注意：修改表明不会修改表结构，表的结构不会产生改变。

5.3.2 修改字段的数据类型

修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。在MySQL中修改字段数据类型的语法规则如下：

ALTER TABLE 表名 MODIFY 字段名 数据类型

修改表的字段name的数据类型

ALTER TABLE tb_dept1 MODIFY NAME VARCHAR(80)

5.3.3 修改字段名

在上面我们实现了修改字段数据类型，那么在这里我们再来看一下如何修改字段名；

修改字段名的语法规则如下：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型

通过上面我们发现，修改字段名称同时是可以修改数据类型的

练习：将tb_dept1中的location字段修改为loc,修改数据类型为varchar(120);

如果修改时不修改数据类型那么将原数据类型坠在后面，这里一定不能删除

注意：CHANGE也可以只修改数据类型，实现和MODIFY同样的效果，方法是将SQL语句中的新字段名和旧字段名设置为相同的名称，只改变数据类型。

由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录，因此数据库中存在数据时，不要轻易修改数据类型。

5.3.4 添加字段

随着业务需求的变化，可能需要在已经存在的表中添加新的字段。一个完整字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下：

ALTER TABLE 表名 ADD 新字段名 新数据类型 约束条件 FIRST|AFTER 已存在的字段名

注意;first或after已存在字段名用于指定新增字段在表结构中的位置，如果没有这两个参数，默认添加到表的最后一列。这两个是可选参数。

添加无完整性约束条件的字段

在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId部门类型编号

默认添加在最后一行；

添加有完整性约束的字段

在数据表tb_dept1中添加一个不能为空的varchar(12)类型的column1：

在指定位置添加字段

在表tb_dept1中添加一个INT类型的字段column2，column3分别添加到第一列与name列后

5.3.5 删除字段

删除字段是将某个字段从数据库表中移除，语法如下：

ALTER TABLE 表名 DROP 字段名

练习：删除tb_dept1中column2字段

5.3.6 修改字段的排列位置

在添加字段中，我们看到提那家字段可以决定一个字段的添加位置，那么对于已经存在的字段，该如何修改其位置呢？其实也是可以的，语法如下：

ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2

练习：将tb_dept1表中的column1修改为第一列，将column3列放在location后面

修改列的排列位置可以顺便修改列的类型；

5.3.7 更改表的存储引擎

通过我们在存储引擎中的学习，我们知道存储引擎是MySQL中数据存储在文件或者内存中时采用的不同技术实现。可以根据自己的需要，选择不同的引擎，甚至可以为每一张表选择不同的存储引擎。详细的存储引擎介绍参见数据库引擎；

查看该数据库支持的引擎：

SHOW ENGINES；

更改存储引擎的语法格式如下：

ALTER TABLE 表名 ENGINE=更改后的存储引擎名称

练习：修改tb_deptment3的存储引擎修改为MyISAM；

首先查询未修改前的引擎

修改后发现存储引擎改变了。

修改存储引擎可以使我们在以后工作中根据业务需求为不同的表选择不同的引擎。

5.3.8 删除表的外键约束

在上面我们为表设置外键后，删除表发现会报错，不让删除，那么如果我们想删除该怎么办呢？其实是可以做到的，但是我们知道外键的存在是实现表的关联，保证数据完整性，如果我们要删除表，那么就意味着关联已经不存在了，所以此时我们应该首先解除关联然后进行删除；删除外键的语法格式如下：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

注意：外键约束名指的是关键字CONSTRAINT关键字后面的参数。

练习：删除tb_emp8的外键约束；

我们发现解除外键前后的表结构发现，外键已经解除了；

5.4  删除数据表

数据库表既然能创建，那么自然就能删除，再删除表的时候，表的定义和表中的所有数据都会被删除，所以删除表之前要特别注意，最好做备份以防万一。下面我们就来介绍一下怎么删除数据表；

5.4.1 删除没有被关联的表

在MySQL中，使用DROP TABLE可以一次删除一个或者多个数据库表。语法格式如下：

DROP TABLE IF EXISTS 表名1，表名2....

其中IF EXISTS用于在删除时判断表是否存在，若存在正常删除，否则仍然正常执行SQL语句，但会发出warning；

以上是删除不存在的表，并且没有使用IF EXISTS判断，所以爆出错误信息；

如果加上判断就可以正常执行，但会有警告；

删除表dept2,dept3

5.4.2 删除被其他表关联的主表

上面我们删除未经关联的表，那么关联表怎么删除呢？下面我们做一下尝试：

首先创建两个表，然后创建关联关系：

CREATE TABLE tb_dept2(

id INT(11) PRIMARY KEY,

NAME VARCHAR(22) UNIQUE,

location VARCHAR(50)

)

CREATE TABLE tb_emp10(

id INT(11) PRIMARY KEY,

NAME VARCHAR(25) NOT NULL,

deptid INT(11) DEFAULT 1111,

salary FLOAT,

CONSTRAINT fk_emp8_dept2 FOREIGN KEY(deptid) REFERENCES tb_dept2(id)

)

查询tb_emp10的表结构：

我们首先尝试删除父表：

当存在主外键关系时我们删除父表会显示错误，这是因为此时会破坏表的参照完整性，所以数据库的安全机制是不会允许我们删除的；

我们尝试首先删除子表，然后删除父表：

我们发现成功了，所以删除关联表应该首先删除子表，然后再删除父表；

但是有的情况下我们可能需要保存子表，但是的确需要删除父表，此时该怎么做呢？删除外键约束，然后此时参照关系不存在了，然后就可以删除父表了;解除外键约束语法：

ALTER TABLE 子表名 DROP FOREIGN KEY 外键名