数据表相关操作
文章目录
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)