4.1 创建数据表
在创建完数据库之后,接下来的工作就是创建数据表。所谓创建数据表指的是在已经创建好了的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程,同时也是实施数据完整性(包括实体完整性、引用完整性和域完整性等)约束的过程。本节将介绍创建数据表的语法形式、如何添加主键约束、外键约束、非空约束等。
4.1.1 创建表的语法形式
数据表属于数据库,在创建数据表之前,应该使用语句“USE<数据库名>”指定操作是在哪个数据库中进行,如果没有选择数据库,会抛出“Nodatabase selected”的错误。
创建数据表的语句为 CREATE TABLE,语法规则如下:
CREATE TABLE <表名>
(
字段名1 数据类型 [列级别约束条件] [默认值],
字段名2 数据类型 [列级别约束条件] [默认值],
……
[表级别约束条件]
);
使用CREATE TABLE创建表时,必须指定以下信息:
⑴要创建的表的名称,不区分大小写,不能使用SQL语言中的关键字,如DROP,ALTER,INSERT等。
⑵数据表中每一个列(字段)的名称和数据类型,如果创建多个列,要用逗号隔开。
【例4.1】创建员工表tb_emp1,结构如【表4.1】
表4.1 tb_emp1 表结构
字段名称 |
数据类型 |
备注 |
id |
INT(11) |
员工编号 |
name |
VARCHAR(25) |
员工名称 |
deptId |
INT(11) |
所在部门编号 |
salary |
FLOAT |
工资 |
首先选择创建表的数据库,SQL语句如下:
USE test;
创建tb_emp1表,SQL语句为:
CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
语句执行后,便创建了一个名称为tb_emp1的数据表,使用SHOWTABLES;语句查看数据表是否创建成功,SQL语句如下:
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_ test |
+----------------------+
| tb_emp1 |
+----------------------+
1 row in set (0.00 sec)
可以看到test数据库中已经有了数据表tb_tmp1,数据表创建成功。
4.1.2 使用主键约束
主键,又称主码,是表中一列或多列的组合。主键约束(PrimaryKey constraint)要求主键列的数据唯一,并且不允许为空。主键能够惟一地标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询的速度。主键和记录之间的关系如同身份证和人之间的关系,它们之间是一一对应的。主键分为两种类型:单字段主键,多字段联合主键。
1. 单字段主键
主键由一个字段组成,SQL语句格式分以下两种情况。
⑴ 在定义列的同时指定主键,语法规则如下:
字段名 数据类型 PRIMARY KEY
【例4.2】定义数据表tb_emp 2,其主键为id,SQL语句如下:
CREATE TABLE tb_emp2
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
⑵ 在定义完所有列之后指定主键。
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]
【例4.3】定义数据表tb_emp 3,其主键为id,SQL语句如下:
CREATE TABLE tb_emp3
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);
上述两个例子执行后的结果是一样的,都会在id字段字段上设置主键约束。
2.多字段联合主键
主键由多个字段联合组成,语法规则如下:
PRIMARY KEY [字段1, 字段2,. . ., 字段n]
【例4.4】定义数据表tb_emp4,假设表中间没有主键id,为了唯一确定一个员工,可以把name、deptId联合起来做为主键,SQL语句如下:
CREATE TABLE tb_emp4
(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(name,deptId)
);
语句执行后,便创建了一个名称为tb_emp4的数据表,name字段和deptId字段组合在一起成为tb_emp4的多字段联合主键。
4.1.3 使用外键约束
外键用来在两个表的数据之间建立链接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。
外键:首先它是表中的一个字段,它可以不是本表的主键,但对应另外一个表的主键。外键主要作用是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行。例如,部门表tb_dept的主键是id,在员工表tb_emp5中有一个键deptId与这个id关联。
主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的那个表即是主表。
从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的那个表即是从表。
创建外键的语法规则如下:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [ ,字段名2,…]
REFERENCES <主表名> 主键列1 [ ,主键列2,…]
外键名为定义的外键约束的名称,一个表中不能有相同名称的外键;字段名表示从表的需要添加外键约束的字段列;主表名,即被从表外键所依赖的表的名称;主键列表示主表中定义的主键字段,或者字段组合。
【例4.5】定义数据表tb_emp5,并在tb_emp5表上创建外键约束。
表4.2 tb_dept1 表结构
字段名称 |
数据类型 |
备注 |
Id |
INT(11) |
部门编号 |
Name |
VARCHAR(22) |
部门名称 |
location |
VARCHAR(50) |
部门位置 |
创建一个部门表tb_dept1,表结构如【表4.2】,SQL语句如下:
CREATE TABLE tb_dept1
(
id i nt(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
定义数据表tb_emp5,让它的键deptId作为外键关联到tb_dept1的主键id,SQL语句为:
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)
);
以上语句执行成功之后,在表tb_emp5上添加了名称为fk_emp_dept1的外键约束,外键名称为deptId,其依赖于表tb_dept1的主键id。
4.1.4 使用非空约束
非空约束(NotNull constraint)指字段的值不能为空。对于使用了非空约束的字段如果用户在添加数据时,没有指定值,数据库系统会报错。
非空约束的语法规则如下:
字段名 数据类型 not null
【例4.6】定义数据表tb_emp6,指定员工的名称不能为空,SQL语句如下:
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL,
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept2 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
执行后在tb_emp6中创建了一个Name字段,其插入值不能为空(NOTNULL)。
4.1.5 使用唯一性约束
唯一性约束(UniqueConstraint)要求添加该约束的列字段的值唯一,允许为空,但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
添加唯一性约束的语法规则如下:
⑴ 在定义完列之后直接指定唯一约束,语法规则如下:
字段名 数据类型 UNIQUE
【例4.7】定义数据表tb_dept2,指定部门的名称唯一,SQL语句如下:
CREATE TABLE tb_dept2
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
);
⑵ 在定义完所有列之后指定唯一约束,语法规则如下:
[CONSTRAINT <约束名>] UNIQUE(<字段名>)
【例4.8】定义数据表tb_dept3,指定部门的名称唯一,SQL语句如下:
CREATE TABLE tb_dept3
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);
UNIQUE和PRIMARYKEY区别:一个表中可以有多个字段声明为UNIQUE,但只能由一个PRIMARYKEY 声明;声明为PRIMAYKEY的列不允许有空值,但是声明为UNIQUE的字段允许为空值(NULL)的存在。
4.1.6 使用默认约束
默认约束(DefaultConstraint)指定某列的默认值。如男性同学较多,性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值,那么系统会自动为这个字段赋值为‘男’。
默认约束的语法规则如下:
字段名 数据类型DEFAULT 默认值
【例4.9】定义数据表tb_emp7,指定员工的部门编号默认为1111,SQL语句如下:
CREATE TABLE tb_emp7
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL,
deptId INT(11) DEFAULT 1111,
salary FLOAT,
CONSTRAINT fk_emp_dept3 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
以上语句执行成功之后,表tb_emp7上得字段deptId拥有了一个默认的值1111,新插入的记录如果没有指定部门编号,则默认的都为1111。
4.1.7 设置表的属性值自动增加
在数据库应用中,经常希望在每次插入新记录时,系统就会自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。默认的,在MySQL中AUTO_INCREMENT的初始值值是1,每新增一条记录,字段值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束,且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型(TINYINT、SMALLIN、INT、BIGINT等)。
设置字段值自增属性的语法规则如下:
字段名 数据类型 AUTO_INCREMENT
【例4.10】定义数据表tb_emp8,指定员工的编号自动递增,SQL语句如下:
CREATE TABLE tb_emp8
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept5 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
上述例子执行后,会创建名称为tb_emp8的数据表。表tb_emp8中的id字段的值在添加新记录的时候会自动增加,在插入记录的时候,默认的自增字段id的值从1开始,每次添加一条新记录,该值自动加1。
例如,执行如下插入语句:
mysql> INSERT INTO tb_emp8 (name,salary)
-> VALUES('Lucy',1000), ('Lura',1200),('Kevin',1500);
语句执行完后,tb_emp8表中增加3条记录,在这里,并没有输入id的值,但系统已经自动添加该值,使用SELECT命令查看记录。
mysql> SELECT * FROM tb_emp8;
+----+-------+--------+--------+
| id | name | deptId| salary |
+----+-------+--------+--------+
| 1 |Lucy | NULL| 1000 |
| 2 |Lura | NULL| 1200 |
| 3 | Kevin | NULL| 1500 |
+----+-------+--------+--------+
3 rows in set (0.00 sec)
4.2 查看数据表结构
使用SQL语句创建好数据表之后,可以查看表结构的定义,以确认表的定义是否正确。MySQL中查看表结构分别可以使用DESCRIBE和SHOWCREATE TABLE语句。本节将针对这两个语句分别进行详细的讲解。
4.2.1 查看表基本结构语句DESCRIBE
DESCRIBE/DESC语句可以查看表的字段信息,其中包括:字段名、字段数据类型、是否为主键、是否有默认值等。语法规则如下:
DESCRIBE 表名;
或者简写为:
DESC 表名;
【例4.11】分别使用DESCRIBE和DESC查看表tb_dept1和表tb_emp1的表结构。
查看tb_dept1表结构,SQL语句如下:
mysql> DESCRIBE tb_dept1;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name |varchar(22) | NO | | NULL | |
| location | varchar(50) | YES | |NULL | |
+-----------+---------------+------+-----+---------+--------+
查看tb_emp1表结构,SQL语句如下:
mysql> DESC tb_emp1;
+--------+--------------+------+-----+---------+-------+
| Field |Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int (11) | YES | |NULL | |
| name |varchar(25) | YES | | NULL | |
| deptId | int (11) | YES | | NULL | |
| salary |float | YES | | NULL | |
+---------+-------------+-------+-----+---------+-------+
● NULL:表示该列是否可以存储NULL值。
● Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分。UNI表示该列是UNIQUE索引的一部分。MUL 表示在列中某个给定值允许出现多次。
● default:表示该列是否有默认值,如果有的话值是多少。
● Extra:表示可以获取的与给定列有关的附加信息,例如 AUTO_INCREMENT等。
4.2.2 查看表详细结构语句SHOW CREATE TABLE
SHOWCREATE TABLE语句可以用来显示创建表时的CREATETABLE 语句,语法格式如下:
SHOW CREATE TABLE <表名\G>;
如果不加‘\G’参数,显示的结果可能非常混乱,加上参数‘\G’之后,可使显示结果更加直观,易于查看。
【例4.12】使用SHOWCREATE TABLE查看表tb_emp1的详细信息,SQL语句如下:
mysql> SHOW CREATE TABLE tb_emp1;
+--------+----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+
| Table |Create Table
|
+--------+----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+
| fruits | CREATE TABLE `fruits` (
`f_id`char(10) NOT NULL,
`s_id`int(11) NOT NULL,
`f_name`char(255) NOT NULL,
`f_price`decimal(8,2) NOT NULL,
PRIMARYKEY (`f_id`),
KEY`index_name` (`f_name`),
KEY`index_id_price` (`f_id`,`f_price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------+
使用参数‘\G’之后的结果如下:
mysql> SHOW CREATE TABLE tb_emp1\G;
*************************** 1. row ***************************
Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
`id`int(11) DEFAULT NULL,
`name`varchar(25) DEFAULT NULL,
`deptId`int(11) DEFAULT NULL,
`salary`float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
4.3 修改数据表
修改表指的是修改数据库中已经存在的数据表的结构。常用的修改表的操作有:修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置,更改表的存储引擎,删除表的外键约束等。本节将对和修改表有关的操作进行讲解。
4.3.1 修改表名
MySQL是通过ALTERTABLE语句来实现表名的修改的,具体的语法规则如下:
ALTER TABLE <旧表名> RENAME [TO] <新表名>;
其中TO为可选参数,使用与否均不影响结果。
【例4.13】将数据表tb_dept3改名为tb_department3。
执行修改表名操作之前,使用SHOWTABLES查看数据库中所有的表。
mysql> SHOW TABLES;
+---------------------+
| Tables_in_test |
+---------------------+
| tb_dept1 |
| tb_dept2 |
| tb_dept3 |
省略部分内容
使用ALTERTABLE将表tb_dept3改名为tb_department3,SQL语句如下:
ALTER TABLE tb_dept3 RENAME tb_department3;
语句执行之后,检验表tb_dept3是否改名成功。使用SHOW TABLES查看数据库中的表,结果如下:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_test |
+---------------------+
| tb_department3 |
| tb_dept |
| tb_dept2 |
省略部分内容
经过比较可以看到数据表列表中已经有了名称为tb_department3的表。
4.3.2 修改字段的数据类型
修改字段的数据类型,就是把字段的数据类型转换成另一种数据类型。在MySQL中修改字段数据类型的语法规则如下:
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中“表名”指要修改数据类型的字段所在表的名称,“字段名”指需要修改的字段,“数据类型”指修改后字段的新数据类型。
【例4.14】将数据表tb_dept1中name字段的数据类型由VARCHAR(22)的修改成VARCHAR(30)。
执行修改字段数据类型操作之前,使用DESC查看tb_dept表结构,结果如下:
mysql> DESC tb_dept1;
+----------+---------------+---------+--------+-------------+-------+
| Field |Type |Null | Key |Default | Extra |
+----------+---------------+---------+--------+-------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | YES | | NULL | |
| location | varchar(50) | YES | |NULL | |
+----------+---------------+--------+---------+-------------+-------+
3 rows in set (0.00 sec)
可以看到现在name字段的数据类型为VARCHAR(22),下面修改其类型。输入如下SQL语句并执行。
ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
再次使用DESC查看表,结果如下:
mysql> DESC tb_dept1;
+----------+---------------+---------+--------+-------------+-------+
| Field |Type |Null | Key |Default | Extra|
+----------+---------------+---------+--------+-------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| location | varchar(50) | YES | |NULL | |
+----------+---------------+--------+---------+-------------+-------+
3 rows in set (0.00 sec)
语句执行之后,检验会发现表tb_dept表中name字段的数据类型已经修改成了VARCHAR(30),修改成功。
4.3.3 修改字段名
MySQL中修改表字段名的语法规则如下:
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
其中,“旧字段名”指修改前的字段名;“新字段名”指修改后的字段名;“新数据类型”指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样即可,但数据类型不能为空。
【例4.15】将数据表tb_dept1中的location字段名称改为loc,数据类型保持不变,SQL语句如下:
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
使用DESC查看表tb_dept1,会发现字段的名称已经修改成功,结果如下:
mysql> DESC tb_dept1;
+----------+---------------+---------+--------+-------------+-------+
| Field |Type |Null | Key |Default | Extra |
+----------+---------------+---------+--------+-------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| loc |varchar(50) | YES | |NULL | |
+----------+---------------+--------+---------+-------------+-------+
3 rows in set (0.00 sec)
【例4.16】将数据表tb_dept1中的loc字段名称改为location,同时将数据类型保持变为VARCHAR(60),SQL语句如下:
ALTER TABLE tb_dept1CHANGE loc location VARCHAR(60);
使用DESC查看表tb_dept1,会发现字段的名称和数据类型均已经修改成功,结果如下:
mysql> DESC tb_dept1;
+----------+---------------+---------+--------+-------------+-------+
| Field |Type |Null | Key |Default | Extra |
+----------+---------------+---------+--------+-------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| location | varchar(60) | YES | |NULL | |
+----------+---------------+--------+---------+-------------+-------+
3 rows in set (0.00 sec)
技巧:CHANGE也可以只修改数据类型,实现和MODIFY同样的效果,方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称,只改变“数据类型”。
4.3.4 添加字段
随着业务需求的变化,可能需要在已经存在的表中添加新的字段。一个完整字段包括字段名,数据类型,完整性约束。添加字段的语法格式如下:
ALTER TABLE <表名> ADD <新字段名> <数据类型>
[约束条件] [FIRST |AFTER 已存在字段名];
新字段名为需要添加的字段的名称;“FIRST”为可选参数,其作用是将新添加的字段设置为表的第一个字段;“AFTER”为可选参数,其作用是将新添加的字段添加到指定的“已存在字段名”的后面。
1.添加无完整性约束条件的字段
【例4.17】在数据表tb_dept1中添加一个没有完整性约束的INT类型的字段managerId(部门经理编号),SQL语句如下:
ALTER TABLE tb_dept1 ADD managerId INT(10);
使用DESC查看表tb_dept1,会发现在表的最后添加了一个名为managerId的INT类型的字段,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key |Default | Extra |
+-------------+-----------------+----------+--------+-------------+--------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| location | varchar(60) | YES | |NULL | |
| managerId | int(10) | YES | | NULL | |
+-------------+----------------+-----------+--------+-------------+--------+
4 rows in set (0.03 sec)
2.添加有完整性约束条件的字段
【例4.18】在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1,SQL语句如下:
ALTER TABLE tb_dept1 ADD column1 VARCHAR(12)not null;
使用DESC查看表tb_dept1,会发现在表的最后添加了一个名为column1的VARCHAR(12)类型且不为空的字段,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key |Default | Extra |
+-------------+-----------------+----------+--------+-------------+--------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| location | varchar(60) | YES | |NULL | |
| managerId | int(10) |YES | | NULL | |
| column1 |varchar(12) | NO | |NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
5 rows in set (0.00 sec)
3.在表的第一列添加一个字段
【例4.19】在数据表tb_dept1中添加一个INT类型的字段column2,SQL语句如下:
ALTER TABLE tb_dept 1ADD column2 INT(11) FIRST;
使用DESC查看表tb_dept1,会发现在表第一列添加了一个名为column2的INT(11)类型字段,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key |Default | Extra |
+-------------+-----------------+----------+--------+-------------+--------+
| column2 |int(11) | YES | |NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | |NULL | |
| location | varchar(60) | YES | |NULL | |
| managerId | int(10) |YES | | NULL | |
| column1 |varchar(12) | NO | |NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
6 rows in set (0.00 sec)
4.在表的指定列之后添加一个字段
【例4.20】在数据表tb_dept1中name列后添加一个INT类型的字段column3,SQL语句如下:
ALTER TABLE tb_dept1 ADD column3 INT(11) AFTERname;
使用DESC查看表tb_dept1,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+----------+--------+-------------+--------+
| column2 |int(11) | YES | |NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 |int(11) | YES | | NULL | |
| location | varchar(60) | YES | |NULL | |
| managerId | int(10) |YES | | NULL | |
| column1 |varchar(12) | NO | |NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
7 rows in set (0.03 sec)
可以看到,tb_dept1表中增加了一个名称为column3的字段,其位置在指定的name字段后面,添加字段成功。
4.3.5 删除字段
删除字段是将数据表中的某个字段从表中移除,语法格式如下:
ALTER TABLE <表名> DROP <字段名>;
“字段名”指需要从表中删除的字段的名称。
【例4.21】删除数据表tb_dept1表中的column2字段。
首先,执行删除字段之前,使用DESC查看tb_dept1表结构,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key | Default | Extr |
+-------------+-----------------+----------+--------+-------------+--------+
| column2 |int(11) | YES | |NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 |int(11) | YES | |NULL | |
| location | varchar(60) | YES | | NULL | |
| managerId | int(10) |YES | |NULL | |
| column1 |varchar(12) | NO | |NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
6 rows in set (0.03 sec)
删除column2字段,SQL语句如下:
ALTER TABLE tb_dept1 DROP column2;
再次使用DESC查看表tb_dept1,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key | Default | Extr |
+-------------+-----------------+----------+--------+-------------+--------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 |int(11) | YES | |NULL | |
| location | varchar(60) | YES | |NULL | |
| managerId | int(10) | YES | |NULL | |
| column1 |varchar(12) | NO | |NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
6 rows in set (0.03 sec)
可以看到,tb_dept1表中已经不存在名称为column2的字段,删除字段成功。
4.3.6 修改字段的排列位置
对于一个数据表来说,在创建的时候,字段在表中的排列顺序就已经确定了。但表的结构并不是完全不可以改变的,可以通过ALTERTABLE来改变表中字段的相对位置。语法格式如下:
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;
“字段1”指要修改位置的字段,“数据类型”指“字段1”的数据类型,“FIRST”为可选参数,指将“字段名1”修改为表的第一个字段,“AFTER 字段2”指将“字段1”插入到“字段2”后面。
1. 修改字段为表的第一个字段
【例4.22】将数据表tb_dept中的column1字段修改为表的第一个字段,SQL语句如下:
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12)FIRST;
使用DESC查看表tb_dept1,发现字段column1已经被移至表的第一列,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+----------+--------+-------------+--------+
| column1 |varchar(12) | NO | |NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 |int(11) | YES | | NULL | |
| location | varchar(60) | YES | |NULL | |
| managerId | int(10) |YES | | NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
6 rows in set (0.03 sec)
2 修改字段到表的指定列之后
【例4.23】将数据表tb_dept1中的column1字段插入到location字段后面,SQL语句如下:
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12)AFTER location;
使用DESC查看表tb_dept1,结果如下:
mysql> DESC tb_dept1;
+-------------+-----------------+----------+--------+-------------+--------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------+----------+--------+-------------+--------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
| column3 |int(11) | YES | |NULL | |
| location | varchar(60) | YES | |NULL | |
| column1 |varchar(12) | NO | |NULL | |
| managerId | int(10) |YES | | NULL | |
+--------------+----------------+-----------+--------+-------------+--------+
6 rows in set (0.03 sec)
可以看到,tb_dept1表中的字段column1已经被移至location字段之后。
4.3.7 更改表的存储引擎
存储引擎是MySQL中的数据存储在文件或者内存中时采用的不同技术实现。可以根据自己的需要,选择不同的引擎,甚至可以为每一张表选择不同的存储引擎。MySQL中主要存储引擎有:MyISAM、InnoDB、MEMORY(HEAP)、BDB、FEDERATED等。可以使用SHOWENGINES;语句查看系统所支持的存储引擎。【表4.3】列出了5.5.13版本的MySQL所支持的存储引擎。
表4.3 MySQL支持的存储引擎
引擎名 |
是否支持 |
FEDERATED |
否 |
MRG_MYISAM |
是 |
MyISAM |
是 |
BLACKHOLE |
是 |
CSV |
是 |
MEMORY |
是 |
ARCHIVE |
是 |
InnoDB |
默认 |
PERFORMANCE_SCHEMA |
是 |
更改表的存储引擎的语法格式如下:
ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
【例4.24】将数据表tb_department3的存储引擎修改为MyISAM。
在修改存储引擎之前,先使用SHOWCREATE TABLE查看表tb_department3当前的存储引擎,结果如下。
mysql> SHOW CREATE TABLE tb_department3 \G;
*************************** 1. row***************************
Table: tb_department3
Create Table: CREATE TABLE `tb_department3` (
`id`int(11) NOT NULL,
`name`varchar(22) DEFAULT NULL,
`location`varchar(50) DEFAULT NULL,
PRIMARYKEY (`id`),
UNIQUE KEY`STH` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,表tb_department3当前的存储引擎为ENGINE=InnoDB,接下来修改存储引擎类型,输入如下SQL语句并执行:
mysql> ALTER TABLE tb_department3ENGINE=MyISAM;
使用SHOWCREATE TABLE再次查看表tb_department3的存储引擎,发现表tb_department3的存储引擎变成了“MyISAM”,结果如下:
mysql> SHOW CREATE TABLE tb_department3 \G;
*************************** 1. row***************************
Table:tb_department3
Create Table: CREATE TABLE `tb_department3` (
`id`int(11) NOT NULL,
`name`varchar(22) DEFAULT NULL,
`location`varchar(50) DEFAULT NULL,
PRIMARYKEY (`id`),
UNIQUE KEY`STH` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
4.3.8 删除表的外键约束
对于数据库中定义的外键,如果不再需要,可以将其删除。外键一旦删除,就会解除主表和从表间的关联关系,MySQL中删除外键的语法格式如下:
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
“外键约束名”指在定义表时CONSTRAINT关键字后面的参数,详细内容请参考4.1.2节的【使用外键约束】。
【例4.25】删除数据表tb_emp9中的外键约束。
首先创建表tb_emp9,创建外键deptId关联tb_dept1表的主键id,SQL语句如下:
CREATE TABLE tb_emp9
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);
使用SHOWCREATE TABLE查看表tb_emp9结构,结果如下:
mysql> SHOW CREATE TABLE tb_emp9 \G;
*************************** 1. row***************************
Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
`id`int(11) NOT NULL,
`name`varchar(25) DEFAULT NULL,
`deptId`int(11) DEFAULT NULL,
`salary`float DEFAULT NULL,
PRIMARY KEY(`id`),
KEY`fk_emp_dept` (`deptId`),
CONSTRAINT`fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,已经成功添加了表的外键,下面删除外键约束,SQL语句如下:
ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;
执行完毕之后,将删除表tb_emp9的外键约束,使用SHOWCREATE TABLE次查看表tb_emp9结构,结果如下:
mysql> SHOW CREATE TABLE tb_emp9 \G;
*************************** 1. row***************************
Table:tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
`id`int(11) NOT NULL,
`name`varchar(25) DEFAULT NULL,
`deptId`int(11) DEFAULT NULL,
`salary`float DEFAULT NULL,
PRIMARYKEY (`id`),
KEY`fk_emp_dept` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,tb_emp9中已经不存在FOREIGNKEY,原有的名称为fk_emp_dept的外键约束删除成功。
4.4 删除数据表
删除数据表是将数据库中已经存在的表从数据库中删除。注意,删除表的同时,表的定义和表中所有的数据均会被删除,因此,在删除操作前,最好对表中的数据做个备份,以免造成无法挽回的后果。本节将详细讲解数据库表的删除方法。
4.4.1 删除没有被关联的表
MySQL中,使用DROPTABLE可以一次删除一个或多个没有被其它表关联的数据表。语法格式如下:
DROP TABLE [IF EXISTS]表1, 表2, . . . 表n;
其中“表n”指要删除的表的名称,后面可以同时删除多个表,只需要将要删除的表名依次写在后面,相互之间用逗号隔开即可。如果要删除的数据表不存在,则MySQL会提示一条错误信息,“ERROR1051 (42S02): Unknown table '表名'”。参数“IF EXISTS”用于在删除前判断删除的表是否存在,加上该参数后,再删除表的时候,如果表不存在,SQL语句可以顺利执行,但是会发出警告(warning)。
在前面的例子中,已经创建了名为tb_dept2的数据表,如果没有,请读者输入语句,创建该表,SQL语句如【例4.8】。下面使用删除语句将该表删除。
【例4.26】删除数据表tb_dept2,SQL语句如下:
DROP TABLE IF EXISTS tb_dept2;
语句执行完毕之后,使用SHOWTABLES命令查看当前数据库中所有的表,SQL语句如下:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_test |
+---------------------+
| tb_department3 |
| tb_dept1 |
……省略部分内容
执行结果可以看到,数据表列表中已经不存在名称为tb_dept2的表,删除操作成功。
4.4.2 删除被其它表关联的主表
数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败,原因是直接删除,将破坏表的参照完整性。如果必须要删除,可以先删除与之关联的子表,再删除父表。但是这样同时删除了两个表中的数据。有的情况下可能要保留子表,这时如要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表,下面讲解这种方法。
在数据库中创建两个关联表,首先,创建表tb_dept2,SQL语句如下:
CREATE TABLE tb_dept2
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50)
);
接下来创建表tb_emp,SQL语句如下:
CREATE TABLE tb_emp
(
id INT(11)PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
);
使用SHOWCREATE TABLE命令查看表tb_emp的外键约束,结果如下:
mysql> SHOW CREATE TABLE tb_emp\G;
*************************** 1. row ***************************
Table: tb_emp
Create Table: CREATE TABLE `tb_emp` (
`id` int(11) NOT NULL,
`name` varchar(25) DEFAULTNULL,
`deptId` int(11) DEFAULTNULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept`(`deptId`),
CONSTRAINT `fk_emp_dept`FOREIGN KEY (`deptId`) REFERENCES `tb_dept2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,以上执行结果创建了两个关联表tb_dept2和表tb_emp,其中tb_emp表为子表,具有名称为fk_emp_dept外键约束,tb_dept2为父表,其主键id被子表tb_emp所关联。
【例4.27】删除被数据表tb_emp关联的数据表tb_dept2。
首先直接删除父表tb_dept2,输入删除语句如下:
mysql> DROP TABLE tb_dept2;
ERROR 1217 (23000): Cannot delete or update aparent row: a foreign key constraint fails
可以看到,如前所讲,在存在外键约束时,主表不能被直接删除。
接下来,解除关联子表tb_emp的外键约束,SQL语句如下:
ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept;
语句成功执行后,将取消表tb_enp和表tb_dept2之间的关联关系,此时,可以输入删除语句,将原来的父表tb_dept2删除,SQL语句如下:
DROP TABLE tb_dept2;
最后通过SHOWTABLES;查看数据表列表,如下所示:
mysql> SHOW TABLES;
+---------------------+
| Tables_in_test |
+---------------------+
| tb_department3 |
| tb_dept1 |
……省略部分内容
可以看到,数据表列表中已经不存在名称为tb_dept2的表。
4.5 综合案例——数据表的基本操作
本章全面介绍了MySQL中数据表的各种操作,如创建表、添加各类约束、查看表结构,以及修改和删除表。读者应该掌握这些基本的操作,为以后的学习打下坚实的基础。本章给出一个综合案例,让读者全面回顾一下本章的知识要点,并通过这些操作来检验自己是否已经了掌握数据表的常用操作。
1. 案例目的:创建、修改和删除表,掌握数据表的基本操作。
创建数据库company,按照下面给出的表结构在company数据库中创建两个数据表offices和employees,按照操作过程完成对数据表的基本操作。
表4.2 offices表结构
字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
officeCode |
INT(10) |
是 |
否 |
是 |
是 |
否 |
city |
INT(11) |
否 |
否 |
是 |
否 |
否 |
address |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
country |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
postalCode |
VARCHAR(25) |
否 |
否 |
否 |
是 |
否 |
表4.3 employees表结构
字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
employeeNumber |
INT(11) |
是 |
否 |
是 |
是 |
是 |
lastName |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
firstName |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
mobile |
VARCHAR(25) |
否 |
否 |
否 |
是 |
否 |
officeCode |
VARCHAR(10) |
否 |
是 |
是 |
否 |
否 |
jobTitle |
VARCHAR(50) |
否 |
否 |
是 |
否 |
否 |
birth |
DATETIME |
否 |
否 |
是 |
否 |
否 |
note |
VARCHAR(255) |
否 |
否 |
否 |
否 |
否 |
sex |
VARCHAR(5) |
否 |
否 |
否 |
否 |
否 |
2. 案例操作过程
步骤1:登陆MySQL数据库。
打开windows命令行,输入登陆用户名和密码:
C:\>mysql –h localhost -u root -p
Enter password: **
或者打开MySQL5.5Command Line Client,只用输入用户密码也可以登陆。登陆成功后显示如下信息:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13 MySQL Community Server(GPL)
Copyright (c) 2000, 2010, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarks of theirrespective
owners.
Type 'help;' or '\h' for help. Type '\c' to clearthe current input statement.
mysql>
登陆成功,可以输入SQL语句进行操作。
步骤2:创建数据库company。
创建数据库company的语句如下:
mysql> CREATE DATABASE company;
Query OK, 1 row affected (0.00 sec)
结果显示创建成功,在company数据库中创建表,必须先选择该数据库,输入语句如下:
mysql> USE company;
Database changed
结果显示选择数据库成功。
步骤3:创建表offices。
创建表offices的语句如下:
CREATE TABLE offices
(
officeCode INT(10) NOT NULLUNIQUE,
city VARCHAR(50) NOTNULL,
address VARCHAR(50) NOTNULL,
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) NOTNULL,
PRIMARY KEY (officeCode)
);
执行成功之后,使用SHOWTABLES;语句查看数据库中的表,语句如下:
mysql> show tables;
+-----------------------+
| Tables_in_company |
+-----------------------+
| offices |
+-----------------------+
1 row in set (0.00 sec)
可以看到,数据库中已经有了数据表offices,创建成功。
步骤4创建表employees。
创建表employees的语句如下:
CREATE TABLE employees
(
employeeNumber INT(11) NOTNULL PRIMARY KEY AUTO_INCREMENT,
lastName VARCHAR(50)NOT NULL,
firstName VARCHAR(50)NOT NULL,
mobile VARCHAR(25) NOT NULL,
officeCode INT(10) NOTNULL,
jobTitle VARCHAR(50)NOT NULL,
birth DATETIME,
note VARCHAR(255),
sex VARCHAR(5),
CONSTRAINT office_fk FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
);
执行成功之后,使用SHOWTABLES;语句查看数据库中的表,语句如下:
mysql> show tables;
+------------------------+
| Tables_in_company |
+------------------------+
| employees |
| offices |
+------------------------+
2 rows in set (0.00 sec)
可以看到,现在数据库中已经创建好了employees和offices两个数据表。要检查表的结构是否按照要求创建,使用DESC分别查看两个表的结构,如果语句正确,则显示结果如下:
mysql>DESC offices;
+--------------+-------------+-------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+-------+------+---------+-------+
| officeCode | int(10) | NO | PRI | NULL | |
| city | varchar(50) | NO | |NULL | |
| address | varchar(50) |NO | | NULL | |
| country | varchar(50) | NO | | NULL | |
| postalCode | varchar(15) | NO | |NULL | |
+------------+---------------+--------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default |Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName | varchar(50) | NO | | NULL | |
| firstName |varchar(50) | NO | | NULL| |
| mobile |varchar(25) | NO | | NULL | |
| officeCode |int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| birth |datetime | YES | |NULL | |
| note |varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到,两个表中字段分别满足【表4.2】和【表4.3】中要求的数据类型和约束类型。
步骤5:将表employees的mobile字段修改到officeCode字段后面。
修改字段位置,需要用到ALTERTABLE语句,输入语句如下:
mysql> ALTER TABLE employees MODIFY mobile VARCHAR(25)AFTER officeCode;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功,使用DESC查看修改后的结果如下:
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default |Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName |varchar(50) | NO | | NULL | |
| firstName |varchar(50) | NO | | NULL| |
| officeCode |int(10) | NO | MUL | NULL | |
| mobile | varchar(25) | NO | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth | datetime | YES | | NULL | |
| note |varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到,mobile字段已经插入到officeCode字段的后面。
步骤6:将表employees的birth字段改名为employee_birth。
修改字段名,需要用到ALTERTABLE语句,输入语句如下:
ALTER TABLE employees CHANGE birth employee_birthDATETIME;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功,使用DESC查看修改后的结果如下:
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default |Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName |varchar(50) | NO | | NULL | |
| firstName |varchar(50) | NO | | NULL| |
| mobile |varchar(25) | NO | | NULL | |
| officeCode |int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth |datetime | YES | |NULL | |
| note |varchar(255) | YES | | NULL | |
| sex | varchar(5) | YES | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到,表中只有employee_birth字段,已经没有名称为birth的字段了,修改名称成功。
步骤7:修改sex字段,数据类型为CHAR(1),非空约束。
修改字段数据类型,需要用到ALTERTABLE语句,输入语句如下:
mysql>ALTER TABLE employees MODIFY sex CHAR(1)NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行成功,使用DESC查看修改后的结果如下:
mysql>DESC employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default |Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
| employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName |varchar(50) | NO | | NULL | |
| firstName |varchar(50) | NO | | NULL| |
| mobile |varchar(25) | NO | | NULL | |
| officeCode |int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth | datetime | YES | |NULL | |
| note |varchar(255) | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
执行结果可以看到,sex字段的数据类型由前面的VARCHAR(5)修改为CHAR(1),且其Null列显示为NO,表示该列不允许空值,修改成功。
步骤8:删除字段note。
删除字段,需要用到ALTER TABLE语句,输入语句如下:
mysql> ALTER TABLE employees DROP note;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行语句成功,使用DESCemployees;查看语句执行后的结果:
mysql> desc employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field | Type | Null | Key | Default |Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
|employeeNumber | int(11) | NO | PRI | NULL | auto_increment |
| lastName |varchar(50) | NO | | NULL | |
| firstName |varchar(50) | NO | | NULL| |
| mobile |varchar(25) | NO | | NULL | |
| officeCode |int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth | datetime | YES | |NULL | |
| sex | char(1) | NO | | NULL | |
+--------------------+----------------+--------+-------+---------+--------------------+
8 rows in set (0.00 sec)
可以看到,DESC语句返回了8个列字段,note字段已经不在表结构中,删除字段成功。
步骤9:增加字段名favoriate_activity,数据类型为VARCHAR(100)。
增加字段,需要用到ALTERTABLE语句,输入语句如下:
mysql> ALTER TABLE employees ADD favoriate_activity VARCHAR(100);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行语句成功,使用DESCemployees;查看语句执行后的结果:
mysql> desc employees;
+----------------------+--------------+--------+-------+---------+--------------------+
| Field |Type | Null | Key | Default | Extra |
+----------------------+--------------+--------+-------+---------+--------------------+
|employeeNumber | int(11) |NO | PRI | NULL | auto_increment |
| lastName |varchar(50) | NO | | NULL | |
| firstName |varchar(50) | NO | | NULL| |
| mobile |varchar(25) | NO | | NULL | |
| officeCode |int(10) | NO | MUL | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
| employee _birth |datetime | YES | | NULL | |
| sex | char(1) | NO | | NULL | |
| favoriate_activity | varchar(100) | YES | |NULL | |
+----------------------+-----------------+--------+-------+---------+--------------------+
9 rows in set (0.00 sec)
可以看到,数据表employees中增加了一个新的列favoriate_activity,数据类型为VARCHAR(100),允许空值,添加新字段成功。
步骤10:删除表offices。
在创建表employees表时候,设置了表的外键,该表关联了其父表的officeCode主键,如前面所述,删除关联表时,要先删除子表employees的外键约束,才能删除父表,因此,必须先删除employees表的外键约束。
1)删除employees表的外键约束,输入如下语句:
mysql>ALTER TABLE employees DROP FOREIGN KEYoffice_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
其中office_fk为employees表的外键约束的名称,即创建外键约束时CONSTRAINT关键字后面的参数,结果显示语句执行成功,现在可以删除offices父表。
2)删除表offices,输入如下语句:
mysql>DROP TABLE offices;
Query OK, 0 rows affected (0.00 sec)
结果显示执行删除操作成功,使用SHOWTABLES;语句查看数据库中的表,结果如下:
mysql> show tables;
+-----------------------+
| Tables_in_company |
+-----------------------+
| employees |
+-----------------------+
1 row in set (0.00 sec)
可以看到,数据库中已经没有名称为offices的表了,删除表成功。
步骤11:修改表employees存储引擎为MyISAM。
修改表存储引擎,需要用到ALTERTABLE语句,输入语句如下:
mysql>ALTER TABLE employees ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
结果显示执行修改存储引擎操作成功,使用SHOWCREATE TABLE 语句查看表结构,结果如下:
mysql> show CREATE TABLE employees\G;
*************************** 1. row***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL AUTO_INCREMENT,
`lastName`varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`officeCode` int(10) NOT NULL,
`mobile`varchar(25) DEFAULT NULL,
`jobTitle`varchar(50) NOT NULL,
`employee_birth` datetime DEFAULT NULL,
`sex`char(1) NOT NULL,
`favoriate_activity` varchar(100) DEFAULT NULL,
PRIMARYKEY (`employeeNumber`),
KEY`office_fk` (`officeCode`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
可以看到,倒数第二行中的ENGINE后面的参数已经修改为MyISAM,修改成功。
步骤12:将表employees名称修改为employees_info。
修改数据表名,需要用到ALTERTABLE语句,输入语句如下:
mysql>ALTER TABLE employees RENAMEemployees_info;
Query OK, 0 rows affected (0.00 sec)
结果显示执行语句成功,使用SHOWTABLES;语句查看执行结果:
mysql> show tables;
+-----------------------+
| Tables_in_company |
+-----------------------+
| employees_info |
+----------------------+
1 rows in set (0.00 sec)
可以看到数据库中已经没有名称为employees的数据表。
4.6 经典习题
1.创建数据库Market,在Market中创建数据表customers,customers表结构如【表4.4】,按要求进行操作。
表4.4 customers表结构
字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
c_num |
INT(11) |
是 |
否 |
是 |
是 |
是 |
c_name |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
c_contact |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
c_city |
VARCHAR(50) |
否 |
否 |
否 |
否 |
否 |
c_birth |
DATETIME |
否 |
否 |
是 |
否 |
否 |
⑴.创建数据库Market。
⑵.创建数据表customers,在c_num字段上添加主键约束和自增约束,在c_birth字段上添加非空约束。
⑶.将c_contact字段插入到c_birth字段后面。
⑷.将c_name字段数据类型改为VARCHAR(70)。
⑸.将c_contact字段改名为c_phone。
⑹.增加c_gender字段,数据类型为CHAR(1)。
⑺.将表名修改为customers_info。
⑻.删除字段c_city。
⑼.修改数据表的存储引擎为MyISAM。
2.在Market中创建数据表orders,orders表结构如表4.4,按要求进行操作。
表4.5 orders表结构
字段名 |
数据类型 |
主键 |
外键 |
非空 |
唯一 |
自增 |
o_num |
INT(11) |
是 |
否 |
是 |
是 |
否 |
o_date |
DATE |
否 |
否 |
否 |
否 |
否 |
c_id |
VARCHAR(50) |
否 |
是 |
否 |
否 |
否 |
⑴.创建数据表orders,在o_num字段上添加主键约束和自增约束,在c_id字段上添加外键约束,关联customers表中的主键c_num。
⑵.删除orders表的外键约束,然后删除表customers。