MySQL数据表的基本操作二：表结构查看、修改与表操作

一、查看数据表结构

1) 查看表基本结构语句 DESCRIBE

语法：DESCRIBE 表名;

示例：

?

1 2 3 4 5 6 7 8

mysql> DESCRIBE product; + --------------+--------------+------+-----+---------+----------------+ | Field        | Type         | Null | Key | Default | Extra          | + --------------+--------------+------+-----+---------+----------------+ | product_id   | int (11)      | NO   | PRI | NULL    | auto_increment | | product_name | varchar (50)  | NO   |     | NULL    |                | | description  | varchar (200) | YES  |     | NULL    |                | + --------------+--------------+------+-----+---------+----------------+

语法：DESC 表名;

示例：

?

1 2 3 4 5 6 7 8

mysql> DESC product; + --------------+--------------+------+-----+---------+----------------+ | Field        | Type         | Null | Key | Default | Extra          | + --------------+--------------+------+-----+---------+----------------+ | product_id   | int (11)      | NO   | PRI | NULL    | auto_increment | | product_name | varchar (50)  | NO   |     | NULL    |                | | description  | varchar (200) | YES  |     | NULL    |                | + --------------+--------------+------+-----+---------+----------------+

说明：

NULL: 表示该列是否可以存储NULL值;Key: 表示该列是否已编制索引。PRI表示是表主键的一部分;UNI表示该列是UNIQUE索引一部分;MUL表示该列中某个给定值允许出现多次;Default: 表示该列是否有默认值,如果有是多少;Extra: 表示可以获取的与给定列有关的附加信息。例如AUTO_INCREMENT等;

2) 查看表详细结构语句 SHOW CREATE TABLE

功能说明：

用来显示创建表时的语句

语法：

?

1	SHOW CREATE TABLE <表名\G>;

提示：

该语句可以用来查看创建表的详细语句；还可以用来查看存储引擎和字符编码；加上参数'\G'后，可使显示结果更加直观，易于查看；

示例：

?

1 2 3 4 5 6 7 8 9 10 11

mysql> show create table bm\G; *************************** 1. row ***************************         Table : bm Create Table : CREATE TABLE `bm` (    `id` int (11) NOT NULL ,    ` name ` varchar (22) DEFAULT NULL ,    `location` varchar (20) DEFAULT NULL ,    PRIMARY KEY (`id`),    UNIQUE KEY `STH` (` name `) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.02 sec)

二、修改数据表

1) 修改表名

语法

?

1	ALTER TABLE <旧表名> RENAME [ TO ] <新表名>;

示例

?

1	ALTER TABLE bm RENAME department;

2) 修改字段的数据类型

语法

?

1	ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

示例

?

1	ALTER TABLE department MODIFY id VARCHAR (11);

3) 修改字段名

语法

?

1	ALTER TABLE <表名> CHANGE <旧字段名><新字段名><新数据类型>;

示例

?

1	ALTER TABLE employees CHANGE location loc VARCHAR (350);//类型可以和原来保持一样，不用修改

4) 添加字段

语法

?

1	ALTER TABLE <表名> ADD <新字段名> <数据类型>  [约束条件]  [ FIRST | AFTER 已存在字段名];//默认添加到最后一列后面

示例

?

1 2 3 4

ALTER TABLE employees ADD manager_id INT (10); ALTER TABLE employees ADD age INT (11) NOT NULL ;//非空约束 ALTER TABLE employees ADD sal FLOAT FIRST ;//在表的第一列添加 ALTER TABLE employees ADD hire_date DATE AFTER manager_id;//在指定列后添加

5) 删除字段

语法

?

1	ALTER TABLE <表名> DROP <字段名>;

示例

?

1 2	ALTER TABLE employees DROP manager_id; ALTER TABLE employees DROP hire_date;

6) 修改字段的排列位置

语法

?

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

示例

?

1 2	ALTER TABLE employees MODIFY name VARCHAR (22) FIRST ;//把 name 移动到第一列 ALTER TABLE department MODIFY location VARCHAR (350) AFTER department_id;//把location列移到department_id列之后

7) 更改表的存储引擎

MySQL支持的主要存储引擎

引擎名	是否支持
FEDERATED	否
MRG_MYISAM	是
MyISAM	是
BLACKHOLE	是
CSV	是
MEMORY	是
ARCHIVE	是
InnoDB	默认
PERFORMANCE_SCHEMA	是

语法

?

1	ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>;

示例

?

1	ALTER TABLE department ENGINE=MyISAM;

8) 删除表的外键约束

语法

?

1	ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

示例 

?

1	ALTER TABLE employees DROP FOREIGN KEY fk_emp_dept;

三、删除数据表

1) 删除没有被关联的表

语法

?

1	DROP TABLE [IF EXISTS] 表1,表2,...表n;

示例

?

1	DROP TABLE IF EXISTS employees;

2) 删除被其他表关联的主表

说明：先要删除外键

示例  

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

mysql> create database rm_tab; Query OK, 1 row affected (0.00 sec)   mysql> use rm_tab; Database changed mysql> create table department      -> (      -> id int (11) primary key ,      -> name varchar (22),      -> location varchar (200)      -> ); Query OK, 0 rows affected (0.07 sec)   mysql> create table employee      -> (      -> id int (11) primary key ,      -> name varchar (25),      -> department_id int (11),      -> salary float ,      -> constraint fk_emp_dept foreign key (department_id) references department(id)      -> ); Query OK, 0 rows affected (0.10 sec)   mysql> drop table department; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> alter table employee drop foreign key fk_emp_dept; Query OK, 0 rows affected (0.22 sec) Records: 0  Duplicates: 0  Warnings: 0   mysql> drop table department; Query OK, 0 rows affected (0.04 sec)   mysql> show tables; + ------------------+ | Tables_in_rm_tab | + ------------------+ | employee         | + ------------------+ 1 row in set (0.00 sec)