表的创建
有两种方式：1. 新创建一个表；2. 根据旧表结构创建新表；格式如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| #################################################1.自定义创建新表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
########示例1
mysql> CREATE TABLE demo_0 (
-> `name` VARCHAR(32),
-> age TINYINT,
-> gender CHAR(1),
-> `resume` TEXT); #如果不指定字符集和校对规则，则采用默认对象
########示例2
mysql> CREATE TABLE demo_1 (
-> `name` VARCHAR(32),
-> age TINYINT,
-> gender CHAR(1),
-> `resume` TEXT)
-> CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;
#################################################2.模仿旧表结构创建新表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];
########示例1
mysql> CREATE TABLE IF NOT EXISTS demo_2 LIKE demo_1;
Query OK, 0 rows affected (0.02 sec)
|
详尽的 table_options ， select_statement 与 create_definition 内容请查看Mysql5.1手册 .
-
CREATE TABLE用于创建带给定名称的表。您必须拥有表 CREATE 权限。
-
第 7、10 行的 name 和 resume 加上了反引号 ` ，这是因为 name 和 resume 是 Mysql 的保留字，为了避免冲突，需要加反引号 。
-
创建表时默认在本数据库下创建，如果要在其他数据库中创建表，除了先切换到其他数据库再创建表，还可以使用以下方式：
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> CREATE TABLE staffInfo.demo_0 (
-> `name` VARCHAR(32));
Query OK, 0 rows affected (0.01 sec)
mysql> USE staffInfo;
Database changed
mysql> SHOW TABLES;
+
| Tables_in_staffinfo |
+
| demo_0 |
+
1 row in set (0.00 sec)
|
表名称被指定为 db_name.tbl_name ，以便在特定的数据库中创建表。不论是否有当前数据库，都可以通过这种方式创建表。如果您使用加引号的识别名，则应对数据库和表名称分别加引号。例如，`mydb` . `mytbl`是合法的，但是 `mydb.mytbl` 不合法。
-
在创建表格时，您可以使用 TEMPORARY 关键词。只有在当前连接情况下，TEMPORARY 表才是可见的。当连接关闭时，TEMPORARY 表被自动取消。这意味着两个不同的连接可以使用相同的临时表名称，同时两个临时表不会互相冲突，也不与原有的同名的非临时表冲突。（原有的表被隐藏，直到临时表被取消时为止 ）您必须拥有 CREATE TEMPORARY TABLES 权限，才能创建临时表。如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| mysql> CREATE TEMPORARY TABLE `aha`(data INT); #在stuinfo数据库中创建临时表
Query OK, 0 rows affected (0.01 sec)
mysql> quit; #断开当前连接并重新登入
Bye
C:\Windows\system32>mysql -u root -p
Enter password: **********
Welcome to the MySQL monitor.
mysql> use stuinfo;
Database changed
mysql> show tables;
+
| Tables_in_stuinfo |
+
| test_0 |
| test_1 |
| test_2 |
| test_3 |
| test_5 | #之前的临时表消失
+
|
-
要求某列不能为空，且指定默认值：
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> CREATE TABLE demo (
-> country VARCHAR(32) NOT NULL DEFAULT "China");
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO demo VALUES(); #未插入具体值
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM demo;
+
| country |
+
| China | #默认值为china
+
|
使用 NOT NULL DEFAULT 为某列指定默认值。
表的查看
查看当前数据库下的表
1
2
3
4
5
6
7
8
9
10
11
12
| mysql> SHOW TABLES;
+
| Tables_in_stuinfo |
+
| demo_1 |
| demo_3 |
| test_0 |
| test_1 |
| test_2 |
| test_3 |
| test_5 |
+
|
查看表的结构
- 使用
DESC 或 DESCRIBE ：
1
2
3
4
5
6
7
8
9
10
| mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| age | tinyint(4) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
| address | varchar(32) | YES | | NULL | |
+
4 rows in set (0.00 sec)
|
- 使用
SHOW CREATE TABLE ：
1
2
3
4
5
6
7
8
9
10
11
| mysql> SHOW CREATE TABLE demo_1;
+
| Table | Create Table
| demo_1 | CREATE TABLE `demo_1` (
`age` tinyint(4) DEFAULT NULL,
`gender` char(1) COLLATE utf8_bin DEFAULT NULL,
`resume` text COLLATE utf8_bin,
`address` varchar(32) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+
1 row in set (0.00 sec)
|
和 DESCRIBE 相比，SHOW CREATE TABLE 展示的内容更加丰富，它可以查看表的存储引擎和字符编码；在 SHOW CREATE TABLE 语句的结尾处（分号前面）添加 \g 或者 \G 参数可以改变展示形式。
"查看表的状态
使用 SHOW TABLE STATUS ：
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> SHOW TABLE STATUS \G;
*************************** 1. row ***************************
Name: customer_info
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-09-09 17:03:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: fuck
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
|
表的修改
其语法格式如下：
修改选项的语法格式如下：
1
2
3
4
5
6
7
8
9
10
| {
ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME [TO] <新表名>
| CHARACTER SET <字符集名> #或 CHARSET
| COLLATE <校对规则名>
}
|
更详细的选项参见Mysql5.1手册
修改表名
1
2
| mysql> ALTER TABLE demo_2 RENAME TO demo_3;
Query OK, 0 rows affected (0.01 sec)
|
TO 可加可不加。
修改字符集
- 修改表的默认字符集
1
2
3
4
| ################修改表的字符集
mysql> ALTER TABLE demo_1 CHARSET utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
- 修改列的字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
| ################修改列的字符集
mysql> ALTER TABLE demo_5 CHANGE country country CHAR(10) CHARSET ascii;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE demo_5 \G;
*************************** 1. row ***************************
Table: demo_5
Create Table: CREATE TABLE `demo_5` (
`name` varchar(32) DEFAULT NULL,
`country` char(10) CHARACTER SET ascii DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
|
修改列的字符集时，可以不改名和列类型，但必须写出更改后(未更改)的名字和类型，这是捆绑在一起的操作。
添加列
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
| mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| name | varchar(32) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
+
4 rows in set (0.00 sec)
mysql> ALTER TABLE demo_1 ADD COLUMN address VARCHAR(32)
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| name | varchar(32) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
| address | varchar(32) | YES | | NULL | |
+
|
默认是添加到最后一行，可使用关键字 AFTER 和 FIRST 在指定位置插入新行：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| mysql> DESC demo_5;
+
| Field | Type | Null | Key | Default | Extra |
+
| name | varchar(32) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+
3 rows in set (0.01 sec)
mysql> ALTER TABLE demo_5 ADD COLUMN addr VARCHAR(48) AFTER age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC demo_5;
+
| Field | Type | Null | Key | Default | Extra |
+
| name | varchar(32) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| addr | varchar(48) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
+
4 rows in set (0.00 sec)
|
修改列名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| mysql> ALTER TABLE demo_1 CHANGE COLUMN `name` `tel` VARCHAR(11);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| tel | varchar(11) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
| address | varchar(32) | YES | | NULL | |
+
|
如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空 。
修改列类型
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| mysql> ALTER TABLE demo_1 MODIFY COLUMN tel VARCHAR(32);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| tel | varchar(32) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
| address | varchar(32) | YES | | NULL | |
+
|
使用 MODIFY 来改变列的类型，此时不需要重命名。 使用 CHANGE 修改列类型时，必须重命名(名字可相同)：
1
| mysql> ALTER TABLE demo_1 CHANGE COLUMN `name` `name` VARCHAR(32);
|
删除列
1
2
3
4
5
6
7
8
9
10
11
12
13
| mysql> ALTER TABLE demo_1 DROP tel;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| age | tinyint(4) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
| address | varchar(32) | YES | | NULL | |
+
|
表的删除
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| mysql> SHOW TABLES;
+
| Tables_in_stuinfo |
+
| demo_0 |
| demo_1 |
+
mysql> DROP TABLE demo_0;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW TABLES;
+
| Tables_in_stuinfo |
+
| demo_1 |
+
|
其他事项
-
如果您使用 ALTER TABLE 更改列规约，但是 DESCRIBE tbl_name 提示您列规约并没有改变，则可能是因为 MySQL 忽略了您所做的更改。例如，如果您试图把VARCHAR列更改为CHAR列，此时，如果表包含其它长度可变的列，则 MySQL 可能仍会使用VARCHAR。
-
ALTER TABLE 运行时会对原表进行临时复制( 除了 RENAME )，在副本上进行更改，然后删除原表，再对新表进行重命名 。在执行 ALTER TABLE 时，其它用户可以阅读原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。
-
要使用 ALTER TABLE，您需要获得表的 ALTER, INSERT 和 CREATE 权限。
-
您可以在一个 ALTER TABLE 语句里写入多个 ADD, ALTER, DROP 和 CHANGE 子句，中间用逗号分开。这是 MySQL 相对于标准 SQL 的扩展。在标准 SQL 中，每个 ALTER TABLE 语句中每个子句只允许使用一次。例如，在一个语句中取消多个列：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| gender | char(1) | YES | | NULL | |
| resume | text | YES | | NULL | |
| address | char(8) | YES | | NULL | |
+
mysql> ALTER TABLE demo_1 DROP gender, DROP resume;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC demo_1;
+
| Field | Type | Null | Key | Default | Extra |
+
| address | char(8) | YES | | NULL | |
+
1 row in set (0.00 sec)
|
-
COLUMN 为可选关键字。CHARSET 与 CHARACTER SET 相同。
-
表中各列数据的字符集可以不同 。如果您在向表中添加一个新列时没有指定字符集，则此时使用表的默认字符集。注意，以下语句只改变了表的默认字符集：
1
| mysql> ALTER TABLE demo_1 CHARSET utf8;
|
如果您想要把表默认的字符集和所有字符列（CHAR, VARCHAR, TEXT）改为新的字符集，应使用如下语句：
1
| ALTER TABLE tbl_name CONVERT TO CHARSET utf8;
|
