创建表
create table 表名
create table if not exists 表名
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
mysql> create database company;Query OK, 1 row affected (0.00 sec)mysql> use company;Database changedmysql> create table if not exists t_dept( -> deptno int, -> dname varchar(20), -> loc varchar(40));Query OK, 0 rows affected (0.20 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| t_dept |+-------------------+1 row in set (0.00 sec)mysql> |
显示当前库下的所有表
show tables;
|
1
2
3
4
5
6
7
|
mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| t_dept |+-------------------+1 row in set (0.00 sec) |
查看表的结构
describe 表名
简写
desc 表名
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> describe t_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> desc t_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec) |
查看表的详细
show create table 表名
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> show create table t_dept;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t_dept | CREATE TABLE `t_dept` ( `deptno` int(11) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)show create table t_dept \Gmysql> show create table t_dept \G*************************** 1. row *************************** Table: t_deptCreate Table: CREATE TABLE `t_dept` ( `deptno` int(11) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(40) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) |
删除表
drop table 表名
drop table if exists 表名
|
1
2
3
4
|
mysql> drop table if exists t_dept;Query OK, 0 rows affected (0.12 sec)mysql> show tables;Empty set (0.00 sec) |
修改表名
ALTER TABLE old_table_name RENAME [TO] new_table_name
old_table_name 原表名
new_table_name 新表名
将t_dept修改为tab_dept
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> alter table t_dept rename tab_dept;Query OK, 0 rows affected (0.09 sec)mysql> show tables;+-------------------+| Tables_in_company |+-------------------+| tab_dept |+-------------------+1 row in set (0.00 sec)mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec) |
为表增加一个字段默认在最后
ALTER TABLE table_name ADD 属性名 属性类型
为tab_dept增加一个字段descri varchar(20)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> alter table tab_dept add descri varchar(20);Query OK, 0 rows affected (0.33 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | varchar(20) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec) |
在表的第一个位置增加一个字段
ALTER TABLE table_name ADD 属性名 属性类型 first
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table tab_dept add id int first;Query OK, 0 rows affected (0.38 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | varchar(20) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
在表的指定字段之后增加字段
ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> alter table tab_dept add comm varchar(20) after dname;Query OK, 0 rows affected (0.31 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || comm | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | varchar(20) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec) |
删除字段
ALTER TABLE table_name DROP 属性名
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table tab_dept drop comm;Query OK, 0 rows affected (0.32 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | varchar(20) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
字段修改-修改字段数据类型
ALTER TABLE table_name MODIFY 属性名 数据类型
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table tab_dept modify descri int;Query OK, 0 rows affected (0.45 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | int(11) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
字段修改-修改字段名称
ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table tab_dept change id deptid int;Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptid | int(11) | YES | | NULL | || deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | int(11) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
字段修改-同时修改字段名称与数据类型
ALTER TABLE table_name CHANGE 旧属性名 新属性名 新数据类型
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table tab_dept change deptid id varchar(32);Query OK, 0 rows affected (0.49 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | varchar(32) | YES | | NULL | || deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | int(11) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
修改顺序
ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST|AFTER 属性名2
2个属性必须存在
将deptno调到第一个位置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> alter table tab_dept modify deptno int first;Query OK, 0 rows affected (0.33 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || id | varchar(32) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | int(11) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
将ID放在最后
|
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
|
mysql> alter table tab_dept modify deptno int after descri;Query OK, 0 rows affected (0.29 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | varchar(32) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | int(11) | YES | | NULL | || deptno | int(11) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)mysql> alter table tab_dept modify deptno int first;Query OK, 0 rows affected (0.34 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table tab_dept modify id int after descri;Query OK, 0 rows affected (0.47 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc tab_dept;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| deptno | int(11) | YES | | NULL | || dname | varchar(20) | YES | | NULL | || loc | varchar(40) | YES | | NULL | || descri | int(11) | YES | | NULL | || id | int(11) | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec) |
