【MySQL】-- 表的操作

1. 查看所有表

1.1 语法

1 show tables;

2. 创建表

2.1 语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 field datatype [约束] [comment '注解内容']
 [, field datatype [约束] [comment '注解内容']] ...
) [engine 存储引擎] [character set 字符集] [collate 排序规则];

• TEMPORARY： 表示创建的是⼀个临时表
• field：列名
• datatype：数据类型
• comment：对列的描述或说明
• engine：存储引擎，不指定则使用默认存储引擎
• character set：字符集，不指定则使用默认字符集
• collate：排序规则，不指定则使用默认排序规则
  

2.2 示例

• 创建一个用户表，其中包含用户编号、用户名、密码、生日，并指定字符集为utf8mb4，排序规则为utf8mb4_0900_ai_ci，表名是user。

• 创建一个表并指定存储引擎为MyISAM

2.3 表在磁盘上对应的文件

• 创建一个存储引擎为 InnoDB 的表时，会在对应的数据库目录下生成一个用来存储真实数据的物理文件，命名格式为 表名.ibd ，以当前为例会在 java01 目录下生成一个 users.ibd 的数据文件。

• 创建一个存储引擎为 MyISAM 的表时，会在对应的数据库目录下分别生成三个以不同后缀名结尾的文件，分别是 表名.MYD ( MYData )的数据文件， 表名.MYI ( MYIndex ) 的索引文件，以表名.sdi 的表信息描述文件(JSON格式)。
  

3. 查看表结构

3.1 语法

desc 表名;

3.2 示例

mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

• Field：表中的列名
• Type：列的数据类型
• Null：该列的值是否允许为Null
• Key：该列的索引类型
• Default：该列的默认值
• Extra：扩展信息

4. 查看创建表的语句

5. 修改表

5.1 语法

ALTER TABLE tbl_name [alter_option [, alter_option] ...];
alter_option: {
 table_options
 | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] # 新增列
 | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] # 修改列
 | DROP [COLUMN] col_name # 删除列
 | RENAME COLUMN old_col_name TO new_col_name # 重命名列
 | RENAME [TO | AS] new_tbl_name # 重命名表

• tbl_name：要修改的表名
• ADD：向表中添加列
• MODIFY：修改表中现有的列
• DROP：删除表中现有的列
• RENAME COLUMN：重命名表中现有的列
• RENAME [TO | AS] new_tbl_name：重命名当前的表

5.2 示例

5.2.1 向表中添加一列

# 执⾏SQL语句
mysql> alter table users ADD assets varchar(100) comment '头像地址' after
birthday;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看结果
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(100) | YES | | NULL | | # 添加列成功
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5.2.2 修改某列的长度

# 执⾏SQL语句
mysql> alter table users MODIFY assets varchar(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看结果
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(255) | YES | | NULL | | # ⻓度修改成功
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5.2.3 重命名某列

# 执⾏SQL语句
mysql> alter table users RENAME COLUMN assets TO avatar;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看结果
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| avatar | varchar(255) | YES | | NULL | | # 重命名成功
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5.2.4 删除某个字段

# 执⾏SQL语句
mysql> alter table users DROP avatar;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查看表结构，avatar列已被删除
mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5.2.5 修改表名

# 执⾏SQL语句
mysql> alter table users RENAME TO employee;
Query OK, 0 rows affected (0.01 sec)
# 查看所有表，users表已经不存在
mysql> show tables;
+------------------+
| Tables_in_java01 |
+------------------+
| employee |
| t_myisam |
+------------------+
2 rows in set (0.00 sec)
# 查看表结构，与之前的users表结构相同
mysql> desc employee;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

也可以使用 RENAME TABLE old_table TO new_table; 为表重命名

6. 删除表

6.1 语法

1 DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

TEMPORARY：表示临时表
tbl_name：将要删除的表名

6.2 示例

# 删除employee表
mysql> drop table employee;
Query OK, 0 rows affected (0.02 sec)
# 删除t_myisam表
mysql> drop table t_myisam;
Query OK, 0 rows affected (0.01 sec)
# 查看所有表
mysql> show tables;
Empty set (0.00 sec)

6.3 注意事项

• 删除表是一个危险操作，执行删除语句时⼀定要谨慎
• 删除表成功后，磁盘上对应的数据文件也会被删除
• 一次可以删除多个表，表与表之间用逗号隔开