1、新增数据表

在test数据库中新增一个数据表，表名t1，整数字段k, 字符串字段name，浮点数字段salary。

mysql> use test;
mysql> create table t1 (k_id int, name varchar(255), salary float);

2、查看数据表

2.1 查看表结构

describe 数据表名; 或者 desc 数据表名;

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

2.2 查看创建表时的sql语句

show create table 数据表名;

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，是结果更加直观；

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=In

3、修改数据表

3.1 修改表名

alter table 旧表名 rename to 新表名;

mysql> alter table t1 rename to t2;

3.2 修改表字符集

alter table 表名 default character set 字符集 default collate 校对规则;

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
) ENGIN

3.3 修改表的字段名

alter table 表名 change 旧字段名新字段名新数据类型;
新数据类型指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但不能为空。
例，把t1表的k_id字段名改为kk_id：

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    |       |
+--------+--------------+------+-----+---------+-------+

3.4 修改字段数据类型

注意：由于不同类型的数据存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。
使用modify，alter table 表名 modify 字段名数据类型;

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)类型。

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    |       |
+--------+--------------+------+-----+---------+-------+

3.5 添加字段

alter table 表名 add 新字段名数据类型约束条件;约束条件非必填。

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 已经存在的字段名;

3.6 删除字段

alter table 表名 drop 字段名;
例，删除t1表的kk_id字段。

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    |       |
+--------+--------------+------+-----+---------+-------+

4、删除数据表

4.1 删除当前表

drop table if exists 表名1 表名2 ……;
例，删除数据表newt;

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)

4.2 清空表数据

truncate table 表名; 或者 delete * from 表名;
truncate是整体删除 (速度较快)，delete 是逐条删除 (速度较慢)。
truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因。
truncate不激活trigger(触发器)，但是会重置Identity(自增字段)，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而delete删除以后，identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。

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)

4.3 解除外键约束

alter table 表名 drop foreign key 外键字段

mysql> alter table t1 drop foreign key fk_t2_id;

原创文章，转载请注明出处：http://b.nwumba.cn/article/114/

mysql表操作