MySQL表的操作

创建表

可以使用 CREATE TABLE 语句创建表。其语法格式为:

CREATE TABLE table_name(
   field1 datatype,
   field2 datatype,
   field3 datatype
)character set 字符集 collate 校验规则 engine 存储引擎;

说明一下：

• SQL中大写的表示关键字，[ ]中代表的是可选项。
• field表示列名，datatype表示列的类型。
• CHARSET用于指定表所采用的编码格式，如果没有指定则以所在数据库的编码格式为准。
• COLLATE用于指定表所采用的校验规则，如果没有指定则以所在数据库的校验规则为准。
• ENGINE用于指定表所采用的存储引擎。
• COMMENT用于对指定列添加注释信息。

数据表属于数据库，在创建数据表之前，应使用语句“USE 数据库”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。

举例：创建表的数据库为test1;创建users数据表

create  table users (
 id int,
 name varchar(20) comment '用户名',
 password char(32) comment '密码是32位的md5值',
 birthday date comment '生日'
) character set utf8 engine MyISAM;

说明:

• 不同的存储引擎，创建表的文件不一样。
• users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：

1.users.frm：表结构

2.users.MYD：表数据

3.users.MYI：表索引

插入数据

我们为了方便后续操作，先在表中插入一些数据：

mysql> insert into stu (id,name,password,birthday) values(1,'张三','111','2001-02-19');
Query OK, 1 row affected (0.00 sec)

查看表结构

创建完数据表之后，经常需要查看表结构（表信息）。可以使用 DESCRIBE 和 SHOW CREATE TABLE 命令来查看数据表的结构。

DESCRIBE/DESC 语句会以表格的形式来展示表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，语法格式如下：

DESCRIBE <表名>; 或 DESC <表名>;

DESC 查看表 users 的表结构，SQL 语句和运行结果如下：

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

修改表

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。

其语法格式如下：

ALTER TABLE table_name ADD 新增列名 新增列的属性;

ALTER TABLE table_name MODIFY 列名 修改后的列属性;

ALTER TABLE table_name DROP 列名;

ALTER TABLE table_name RENAME [TO] 新表名;

ALTER TABLE table_name CHANGE 列名 新列名 新列属性;

• 修改表名

语法规则如下：

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

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

将数据表users改名为stu,语句和运行结果如下所示:

mysql> alter table users stu;

mysql> show tables;
+---------------+
| Tables_in_stu |
+---------------+
| stu           |
+---------------+
1 row in set (0.00 sec)

• 修改表字符集

将字符集修改为utf8,校对规则修改为utf8_general_ci.语句和运行结果如下:

mysql> alter table stu charset=utf8 default collate=utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

• 添加表中字段

mysql> alter table stu add path varchar(100) comment '图片路径' after birthday;

mysql> desc stu;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | YES  |     | NULL    |       |
| name     | varchar(20)  | YES  |     | NULL    |       |
| password | char(32)     | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
| path     | varchar(100) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

说明一下： 新增列SQL中的after表示将该列新增到哪一列之后，如果想要将新增的列放到第一列，可以将after及其之后的SQL换成not null first。

• 修改列类型

mysql> alter table stu modify name varchar(60);

• 修改列名

将user表中password列的列名改成passwd

mysql> alter table stu change passwd password varchar(20);

注意：在改列名的同时，需要重新指定新列名的类型

• 删除列

mysql> alter table stu drop password;

删除表

对于不再需要的数据表，我们可以将其从数据库中删除。在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

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

对语法格式的说明如下：

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

举例：

mysql> drop table stu;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

QL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

举例：

mysql> drop table stu;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)