新增数据表

  • 新增一个数据表,表名t1,整数字段k, 字符串字段name,浮点数字段salary。
1
2
mysql> use testdb;
mysql> create table t1 (k_id int(11), name varchar(255), salary float);

查看数据表

查看表结构

  • describe 数据表名; 或者 desc 数据表名;
1
2
3
4
5
6
7
8
mysql> describe t1;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| k_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+

查看创建表时的sql语句

  • show create table 数据表名;
1
2
3
4
5
6
7
8
9
10
mysql> show create table t1;
+-------+-------------------------------+
| Table | Create Table |
+-------+-------------------------------+
| t2 | CREATE TABLE `t2` (
`k_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------+
  • 也可以在后面加上\G,是结果更加直观;
1
2
3
4
5
6
7
8
9
mysql> show create table t1\G;

*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`k_id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

修改数据表

修改表名

  • alter table 旧表名 rename to 新表名;
1
mysql> alter table t1 rename to t2;

修改表字符集

  • alter table 表名 default character set 字符集 default collate 校对规则;
1
2
3
4
5
6
7
8
9
10
mysql> alter table t1 default character set gb2312 default collate gb2312_chinese_ci;

mysql> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`k_id` int(11) DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

修改表的字段名

  • alter table 表名 change 旧字段名 新字段名 新数据类型;
  • 新数据类型指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成与原来一样,但不能为空。
  • 例,把t1表的k_id字段名改为kk_id:
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table t1 change k_id kk_id int(11);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe t1;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| kk_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+

修改字段数据类型

  • 注意:由于不同类型的数据存储方式及长度并不相同,修改数据类型可能会影响数据表中已有的数据记录,因此,当数据表中已经有数据时,不要轻易修改数据类型。
  • 使用modifyalter table 表名 modify 字段名 数据类型;
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table t1 modify kk_id int(11);
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> desc t1;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| kk_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
  • 也可以使用之前修改字段数据类型 的方法,可以将SQL语句中的’新字段名‘和’旧字段名‘设置为相同的名称,只改变’新数据类型‘。
  • 例,把t1表的kk_id字段改成varchar(255)类型。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    mysql> alter table t1 change kk_id kk_id varchar(255);
    Query OK, 1 row affected (0.05 sec)
    Records: 1 Duplicates: 0 Warnings: 0

    mysql> desc t1;
    +--------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +--------+--------------+------+-----+---------+-------+
    | kk_id | varchar(255) | YES | | NULL | |
    | name | varchar(255) | YES | | NULL | |
    | salary | float | YES | | NULL | |
    +--------+--------------+------+-----+---------+-------+

添加字段

  • alter table 表名 add 新字段名 数据类型 约束条件;,约束条件非必填。
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table t1 add k_id int(11);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t1;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| salary | float | YES | | NULL | |
| k_id | int(11) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
  • 默认是在最后追加一列字段,在开头添加字段:alter table 表名 add 新字段名 数据类型 约束条件 first;
  • 在某个字段后添加字段:alter table 表名 add 新字段名 数据类型 约束条件 after 已经存在的字段名;

删除字段

  • alter table 表名 drop 字段名;
  • 例,删除t1表的kk_id字段。
1
2
3
4
5
6
7
8
9
10
11
mysql> alter table t1 drop kk_id;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc t1;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name | varchar(255) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+

删除数据表

删除当前表

  • drop table if exists 表名1 表名2 ……;
  • 例,删除数据表newt;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| newt |
| t1 |
+------------------+
2 rows in set (0.00 sec)

mysql> drop table if exists newt;
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
+------------------+
1 row in set (0.01 sec)

清空表数据

  • truncate table 表名; 或者 delete * from 表名;
  1. truncate是整体删除 (速度较快),delete 是逐条删除 (速度较慢)。
  2. truncate不写服务器log,delete写服务器log,也就是truncate效率比delete高的原因。
  3. truncate不激活trigger(触发器),但是会重置Identity(自增字段),相当于自增列会被置为初始值,又重新从1开始记录,而不是接着原来的ID数。而delete删除以后,identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
+------------------+
1 row in set (0.01 sec)

mysql> select * from t1;
+--------+--------+------+
| name | salary | k_id |
+--------+--------+------+
| 你好 | 1000 | NULL |
+--------+--------+------+
1 row in set (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t1;
Empty set (0.00 sec)

解除外键约束

  • alter table 表名 drop foreign key 外键字段
1
mysql> alter table t1 drop foreign key fk_t2_id;