表的操作

# 创建表

CREATE TABLE [IF NOT EXISTS] table_name(
	field1 datatype1 [COMMENT '注释信息'],
	field2 datatype2 [COMMENT '注释信息'],
	field3 datatype3  [COMMENT '注释信息']
)[CHARSET=charset_name] [COLLATE=collation_name] [ENGINE=engine_name];

1
2
3
4
5

例如：

mysql> create table teacher(
    -> name varchar(32) comment '姓名',
    -> age int comment '年龄',
    -> password char(32) comment '密码'
    -> )
    -> ;
Query OK, 0 rows affected (0.04 sec)

1
2
3
4
5
6
7

# 查询表

使用命令：desc 表名

mysql> desc teacher;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8

注意：

Field表示该字段的名字。
Type表示该字段的类型。
Null表示该字段是否允许为空。
Key表示索引类型，比如主键索引为PRI。
Default表示该字段的默认值。
Extra表示该字段的额外信息说明。

另外，如果想要查看创建表时的相关细节，可以使用show create table 表名 \G

mysql> show create table teacher \G
*************************** 1. row ***************************
       Table: teacher
Create Table: CREATE TABLE `teacher` (
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `password` char(32) DEFAULT NULL COMMENT '密码'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

1
2
3
4
5
6
7
8

# 修改表

使用命令：

ALTER TABLE table_name ADD 新增列名 新增列的属性;

ALTER TABLE table_name MODIFY 列名 修改后的列属性;

ALTER TABLE table_name DROP 列名;

ALTER TABLE table_name RENAME [TO] 新表名;

ALTER TABLE table_name CHANGE 列名 新列名 新列属性;

1
2
3
4
5
6
7
8
9

修改表名

mysql> alter table teacher rename employee;
Query OK, 0 rows affected (0.02 sec)
mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
| id       | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11

新增列

mysql> alter table teacher add id int comment '工号' after password;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from teacher;
+--------+------+----------+------+
| name   | age  | password | id   |
+--------+------+----------+------+
| wangwu |   34 | 1234567  | NULL |
+--------+------+----------+------+

1
2
3
4
5
6
7
8
9

修改列的类型

mysql> alter table teacher modify password varchar(20);
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> desc teacher;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
| id       | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12

修改列名

mysql> alter table employee change id Id int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
| Id       | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

1
2
3
4
5
6
7
8
9
10
11
12

删除列

mysql> alter table employee drop Id;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from employee;
+--------+------+----------+
| name   | age  | password |
+--------+------+----------+
| wangwu |   34 | 1234567  |
+--------+------+----------+

1
2
3
4
5
6
7
8
9

# 删除表

使用命令：

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

上次更新: 2025/03/01, 15:31:03

← 库的操作 MySQL数据类型→