MySQL: 4.数据表相关操作

数据表相关操作

1.创建数据表 CREATE TABLE

创建表 CREATE TABLE

在 MySQL 中，可以使用CREATE TABLE 语句创建表。其语法格式为：

CREATE TABLE <表名> ([表定义选项]) [表选项] [分区选项];

其中最重要的，[表定义选项]的格式为：

<列名1> <类型1> [空值说明、约束等], ..., <列名n> <类型n> [空值说明、约束等]

例如：

CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE语句的主要语法及使用说明如下：

• CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
• <表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。如果表名称被指定为db_name.tbl_name，则在特定的数据库中创建表，无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略db_name。如果使用加引号的识别名，则应对数据库和表名称应分别加引号。例如，'mydb'.'mytbl'是合法的，但'mydb.mytbl'不合法。
• <表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。
• 默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

提示：使用 CREATE TABLE 创建表时，必须注意：

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

查看表结构(表定义) DESCRIBE或DESC

DESCRIBE/DESC语句可以查看表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法规则如下：

DESCRIBE <表名>;

或简写成：

DESC <表名>;

例如：

mysql> DESC `websites`;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| name    | char(20)     | NO   |     |         |                |
| url     | varchar(255) | NO   |     |         |                |
| alexa   | int(11)      | NO   |     | 0       |                |
| country | char(10)     | NO   |     |         |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.06 sec)

其中，各个字段的含义如下：

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

查看创建表语句 SHOW CREATE TABLE

SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句，语法格式如下：

SHOW CREATE TABLE <表名>[\G]； 

提示：使用SHOW CREATE TABLE语句不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码。如果不加“\G”参数，会以表格的形式呈现，显示的结果可能非常混乱，加上“\G”参数之后，以详细信息的形式显示，可使显示的结果更加直观，易于查看。

例如：

mysql> SHOW CREATE TABLE `websites`;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table


         |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| websites | CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> SHOW CREATE TABLE `websites` \G;
*************************** 1. row ***************************
       Table: websites
Create Table: CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.修改数据表（含修改字段） ALTER TABLE

在MySQL中可以使用ALTER TABLE语句来改变原有表的结构，例如修改表名、修改字段数据类型或字段名、增加或删减字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等、创建或取消索引等。

基本语法

语法如下：

ALTER TABLE <表名> [修改选项]

[修改选项] 的语法格式如下：

{ ADD [COLUMN] <列名> <类型>
| CHANGE [COLUMN] <旧列名> <新列名> <新列类型>
| ALTER [COLUMN] <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY [COLUMN] <列名> <类型>
| DROP [COLUMN] <列名>
| RENAME [TO] <新表名> }

上面的COLUMN也可以不写。

添加字段 ADD

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

• <新字段名>为需要添加的字段的名称；
• FIRST 为可选参数，其作用是将新添加的字段设置为表的第一个字段；
• AFTER 为可选参数，其作用是将新添加的字段添加到指定的已存在的字段名的后面；
• 没有FIRST或者AFTER参数的话默认将字段添加到最后。

例如：

mysql> ALTER TABLE `staff` ADD COLUMN `id` INT(11);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 或者不加"COLUMN"关键字
mysql> ALTER TABLE `staff` DROP `id`;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `staff` ADD `id` INT(11);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段数据类型 MODIFY

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

• <表名>指要修改数据类型的字段所在表的名称；
• <字段名>指需要修改的字段；
• <数据类型>指修改后字段的新数据类型。

删除字段 DROP

ALTER TABLE <表名> DROP <字段名>；

• <字段名>指需要从表中删除的字段的名称。

例如：

ALTER TABLE `staff` DROP `id`;

修改字段名称和数据类型 CHANGE

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

• <旧字段名>指修改前的字段名；
• <新字段名>指修改后的字段名；
• <新数据类型>指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

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

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

修改表名 RENAME

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

• TO 为可选参数，使用与否均不影响结果。

例如：

mysql> ALTER TABLE `user` RENAME `users`;
Query OK, 0 rows affected (0.08 sec)

3.删除数据表 DROP TABLE

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

• 表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

两点注意：

• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
• 表被删除时，用户在该表上的权限不会自动删除。

例如：

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| number         |
| numbertest     |
| users          |
| websites       |
+----------------+
4 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `number`, `numbertest`, `users`;
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| websites       |
+----------------+
1 row in set (0.00 sec)

参考

MySQL教程：MySQL数据库学习宝典（从入门到精通）——C语言中文网
MySQL 教程 | 菜鸟教程