数据表操作

主键：非空且唯一 not null unique

查看表

mysql> show tables;
+----------------+
| Tables_in_bibi |
+----------------+
| employee       |
| runoob_tbl     |
+----------------+
2 rows in set (0.00 sec)

查看某张表

mysql> desc employee;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.01 sec)

查看创建表的信息

mysql> show create table employee;
+----------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                         |
+----------+--------------------------------------------------------------------------------------------------------------------------------------+
| employee | CREATE TABLE `employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表结构
添加一个字段

mysql> alter table employee add name varchar(25);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

添加多个字段

mysql> alter table employee add A int,
    -> add B varchar(20);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(25)      | YES  |     | NULL    |                |
| gender     | tinyint(1)       | YES  |     | NULL    |                |
| age        | int(11)          | YES  |     | NULL    |                |
| department | varchar(20)      | YES  |     | NULL    |                |
| salary     | double(7,2)      | YES  |     | NULL    |                |
| is_married | tinyint(1)       | YES  |     | NULL    |                |
| entry_date | date             | NO   |     | NULL    |                |
| A          | int(11)          | YES  |     | NULL    |                |
| B          | varchar(20)      | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

删除一个字段

mysql> alter table employee drop A;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除多个字段

mysql> alter table employee drop B,
    -> drop entry_date;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改一列类型

mysql> alter table employee modify age smallint not null default 18 after id;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| age        | smallint(6)      | NO   |     | 18      |                |
| name       | varchar(25)      | YES  |     | NULL    |                |
| gender     | tinyint(1)       | YES  |     | NULL    |                |
| department | varchar(20)      | YES  |     | NULL    |                |
| salary     | double(7,2)      | YES  |     | NULL    |                |
| is_married | tinyint(1)       | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

修改列名

mysql> alter table employee change department depart varchar(20) after salary;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employee;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| age        | smallint(6)      | NO   |     | 18      |                |
| name       | varchar(25)      | YES  |     | NULL    |                |
| gender     | tinyint(1)       | YES  |     | NULL    |                |
| salary     | double(7,2)      | YES  |     | NULL    |                |
| depart     | varchar(20)      | YES  |     | NULL    |                |
| is_married | tinyint(1)       | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

修改表名

mysql> rename table employee to emp;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_bibi |
+----------------+
| emp            |
| runoob_tbl     |
+----------------+
2 rows in set (0.00 sec)

删除表

drop table tab_name;