# 查询
show tables;
# 查询表结构
desc 表名;
# 查询指定表的建表语句
show create table 表名;
# 表的创建
create table 表名(
字段1 字段1类型[comment 字段1注释],
字段2 字段2类型[comment 字段2注释],
字段3 字段3类型[comment 字段3注释],
......
字段n 字段n类型[comment 字段n注释]
)[comment 表注释];
# 表操作-修改
alter table 表名 add 字段名 类型(长度) [comment 注释][约束]; #添加
alter table 表名 modify 字段名 新数据类型(长度); #修改数据类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment注释][约束];
#修改字段名和字段类型
alter table 表名 drop 字段名;# 删除
alter table 表名 rename to 新表名; # 修改表名
drop table [if exists]表名;# 删除表
truncate table 表名; # 删除表,并重新创建该表头。两者都会删除全部的内容。
################# 运行及结果 ####################
mysql> use itheima;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| itheima |
+------------+
1 row in set (0.00 sec)
mysql> show tables;
Empty set (0.01 sec)
mysql> use sys;
Database changed
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
101 rows in set (0.01 sec)
mysql> use itcast;
Database changed
mysql> create table tb_user(
-> id int comment '编号',
-> name varchar(50) comment '姓名',
-> age int comment '年龄',
-> gender varchar(1) comment '性别'
-> ) comment '用户表';
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| tb_user |
+------------------+
1 row in set (0.00 sec)
mysql> desc tb_user;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> show create table tb_user;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (
`id` int DEFAULT NULL COMMENT '编号',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`age` int DEFAULT NULL COMMENT '年龄',
`gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table emp add nikename varchar(20) comment '昵称';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
| nikename | varchar(20) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> alter table emp change nikename username varchar(30) comment '用户名';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
| username | varchar(30) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> alter table emp drop username;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> alter table emp rename to employee;
Query OK, 0 rows affected (0.02 sec)
mysql> desc emp;
ERROR 1146 (42S02): Table 'itcast.emp' doesn't exist
mysql> desc employee;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| workno | varchar(10) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| idcard | char(18) | YES | | NULL | |
| entrydate | date | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_itcast |
+------------------+
| employee |
| tb_user |
+------------------+
2 rows in set (0.00 sec)
SQL-DDL-表操作-创建&查询&修改
于 2024-08-02 11:07:40 首次发布