数据库的基本操作2
（这里的例子有用到前面1的）
2.查看数据表结构
2.1查看表基本结构语句DESCRIBE
DESCRIBE/DESC可以查看字段的信息，其中包括：字段名、字段数据类型、是否为主键、是否有默认值等。语法规则：DESCRIBE 表名；或者简写为：DESC 表名；
例：分别使用DESCRIBE和DESC查看表tb_dept3和特别tb_emp8的表结构。
查看tb_dept3表结构：
mysql> DESCRIBE tb_dept3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | YES | UNI | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.30 sec)
查看tb_emp8表结构：
mysql> DESC tb_emp8;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(22) | NO | | NULL | |
| deptId | int(11) | YES | MUL | NULL | |
| salary | float | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
其中，各个字段的含义如下：
- NULL：表示该列是否可以存储NULL值。
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示某个给定值允许出现多次。
- Default：表示该列是否有默认值，如果有的话默认值是多少。
- Extra：表示可以获取的与给定列的附加信息，例如AUTO_INCREMENT。
2.2查看表详细结构语句SHOW CREATE TABLE
其语法格式如下:SHOW CREATE TABLE<表名\G>;
此语句不仅可以查看创建时候的详细语句，而且还可以查看存储引擎和字符编码。
。
例：使用SHOW CREATE TABLE 查看表tb_emp8的详细信息，语句如下：
mysql> SHOW CREATE TABLE tb_emp8;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_emp8 | CREATE TABLE `tb_emp8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(22) NOT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept5` (`deptId`),
CONSTRAINT `fk_emp_dept5` FOREIGN KEY (`deptId`) REFERENCES `tb_dept3` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
使用\G参数后，结果如下：；
mysql> SHOW CREATE TABLE tb_emp8\G;
*************************** 1. row ***************************
Table: tb_emp8
Create Table: CREATE TABLE `tb_emp8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(22) NOT NULL,
`deptId` int(11) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_emp_dept5` (`deptId`),
CONSTRAINT `fk_emp_dept5` FOREIGN KEY (`deptId`) REFERENCES `tb_dept3` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3.修改数据表
修改表：修改数据库中已经存在的数据表的结构。
常用修改表的操作有：修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。
3.1修改表名
MySQL通过ALTER TABLE语句来实现表名的修改，具体语法规则如下：ALTER TABLE <旧表名> RENAME [TO] <新表名>；
执行修改表名操作之前要先查看数据库中所有的表：
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_dept2 |
| tb_dept3 |
| tb_emp5 |
| tb_emp6 |
| tb_emp7 |
| tb_emp8 |
+-------------------+
7 rows in set (0.05 sec)
下面使用ALTER TABLE将表tb_dept3改为tb_deptment3，SQL语句如下：
mysql> ALTER TABLE tb_dept3 RENAME tb_deptment3;
Query OK, 0 rows affected (0.37 sec)
执行之后检查是否改名成功：
mysql> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1 |
| tb_dept2 |
| tb_deptment3 |
| tb_emp5 |
| tb_emp6 |
| tb_emp7 |
| tb_emp8 |
+-------------------+
7 rows in set (0.00 sec)
但是修改表名并不会影响表的结构，可用DESC查看：
mysql> DESC tb_deptment3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | YES | UNI | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)
3.2修改字段的数据类型
语法规则：ALTER TABLE <表名> MODIFY <字段名> <数据类型>
其中，表名是要修改数据类型的字段所在表的名称；字段名是需要修改的字段；数据类型是修改后字段的新数据类型。
例：将tb_dept1 中的name字段的数据类型由VARCHAR(22)改为VARCHAR(25)。
在修改之前先使用DESC查看tb_dept1表结构，结果如下：
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(22) | NO | | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.16 sec)
可以看到name字段数据类型为VARCHAR(22)，下面修改再查看，SQL语句如下：
mysql> ALTER TABLE tb_dept1 MODIFY name VARCHAR(25);
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| location | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
可见，修改已经成功。
3.3修改字段名
MySQL中修改表字段名的语法规则：ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>
注意：新数据类型不能为空，要想不改变则设置为与原来一样即可！
例：将数据表tb_dept1中的location字段名改为loc，数据类型保持不变，SQL语句如下：
mysql> ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
当然，CHANGE也可以和MODIFY一样只修改数据类型，只需把新字段名设置为旧字段名即可 。读者可以试试，在这里我不多加示范啦。
提示：修改数据类型的时候要相当谨慎，因为若表中已经有数据时可能会受到影响。
3.4添加字段
添加字段的语法格式如下：ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名]
其中，FIRST为可选参数，其作用是将新添加的字段设置为表的第一个字段；AFTER为可选参数，作用是将新添加的字段添加到指定的“已存在字段”的后面。如果没有使用这两个参数，则默认添加到数据表的最后列。
- 添加无完整性约束条件的字段
例：在数据表tb_dept1中添加一个无完整性约束条件的INT类型的字段manageId（部门经理编号），SQL语句如下：ALTER TABLE tb_dept1 ADD manageId INT(10);
使用DESC查看，可以看到：
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
- 添加有完整性约束条件的字段
例：在数据表tb_dept1中添加一个不能为空的VARCHAR(12)类型的字段column1，SQL语句如下：
mysql> ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用DESC查看有：
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- 在表的第一列添加一个字段
例：在表tb_dept1中添加一个INT类型的字段column2，SQL语句如下：
mysql> ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
Query OK, 0 rows affected (0.78 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用DESC查看有：
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| column2 | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
- 在表的指定列之后添加一个字段
例：在数据表tb_dept1中name列后添加一个INT类型的字段column3，SQL语句如下：
mysql> ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
Query OK, 0 rows affected (0.90 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用DESC有：
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| column2 | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
可以看到tb_dept1中新增的字段column3在name字段后面。
3.5删除字段
删除字段的语法格式：ALTER TABLE <表名> DROP <字段名>；
例：删除tb_dept1表中的字段column2字段：
mysql> ALTER TABLE tb_dept1 DROP column2;
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
| column1 | varchar(12) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
可以看到column2字段已经被删除成功。
3.6修改字段的排列位置
通过ALTER TABLE改变表中字段的相对位置，语法格式如下：ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST | AFTER <字段2>;
字段一为要修改位置的字段。
- 修改字段为表的第一个字段
例：将数据表tb_dept1中的column1修改为表的第一个字段，SQL语句如下：
mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
Query OK, 0 rows affected (0.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
使用DESC 查看表tb_dept1，发现移动成功：
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| column1 | varchar(12) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
- 修改字段到表的指定列之后
例：将数据表tb_dept1中的column1字段插入到loc字段后面，SQL语句如下：
mysql> ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER loc;
Query OK, 0 rows affected (0.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tb_dept1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(25) | YES | | NULL | |
| column3 | int(11) | YES | | NULL | |
| loc | varchar(50) | YES | | NULL | |
| column1 | varchar(12) | YES | | NULL | |
| manageId | int(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
3.7更改表的存储引擎
MySQL中主要存储引擎：MyISAM、InnoDB、MERORY(HEAP)、BDB、FEDERATED等。
可以用SHOW ENGINES;语句查看系统支持的存储引擎。
更改表的存储引擎的语法格式如下：ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;
例：将数据表tb_deptment3的存储引擎修改为MyIASM。
修改前先查看表tb_deptment3当前的存储引擎：
mysql> SHOW CREATE TABLE tb_deptment3\G
*************************** 1. row ***************************
Table: tb_deptment3
Create Table: CREATE TABLE `tb_deptment3` (
`id` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
`location` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `STH` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
（注意：这里如果出现ERROR: No query specified的错误，表示你的分号重复了，如果使用了\G则其代表了一个分号，所以\G后面可不带分号。）
可以看到表tb_deptment3当前的存储引擎为 ENGINE=InnoDB，下面修改存储引擎类型，SQL语句如下：
这里我一开始尝试的时候出现了错误：
mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
这为什么会出错呢？因为有外键约束束缚了tb_deptment3，而MyISAM存储引擎不支持外键，所以不能更改存储引擎。要想将存储引擎InnoDB更改为MyISAM存储引擎只能将外键关系先删除了。
mysql> ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp_dept1;
Query OK, 0 rows affected (0.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tb_emp8 DROP FOREIGN KEY fk_emp_dept5;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tb_emp6 DROP FOREIGN KEY fk_emp_dept2;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE tb_emp7 DROP FOREIGN KEY fk_emp_dept3;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
上述是我前面加了外键约束的表，所以删起来还得回去看记录，如果忘记了外键约束名称可以使用SHOW CREATE TABLE <表名> \G查看。
当将所有外键关系删除之后：
mysql> ALTER TABLE tb_deptment3 ENGINE=MyISAM;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查看表tb_deptment3的存储引擎，可以看到存储引擎更改成功了：
mysql> SHOW CREATE TABLE tb_deptment3\G
*************************** 1. row ***************************
Table: tb_deptment3
Create Table: CREATE TABLE `tb_deptment3` (
`id` int(11) NOT NULL,
`name` varchar(22) DEFAULT NULL,
`location` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `STH` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.10 sec)
3.8删除表的外键约束
在3.7的时候由于外键约束的存在让我们更改引擎变得麻烦，我们删除了外键约束，在这一过程中大概也明白了如何删除外键约束。
其语法格式为：ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
例子前面已经展示，这里不多做说明。