1. 创建表

1.1 创建表指令

语法

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

说明

• field 表示列名
• datatype 表示列的类型
• character set 字符集：如果没有指定字符集，则以所在数据库的字符集为准
• collate 校验规则：如果没有指定校验规则，则以所在数据库的校验规则为准

1.2 创建表案例

1. 新建数据库user_db，并使用

create database user_db;
use user_db;

2. 建表示例

create table user1(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '用户的密码',
    birthday date comment '用户的生日'
)engine MyISAM;

create table user2(
    id int,
    name varchar(20) comment '用户名',
    password char(32) comment '用户的密码',
    birthday date comment '用户的生日'
)engine=InnoDB;

说明

不同的存储引擎，创建表的文件不一样。

user1 表（MyISAM 引擎）：数据库目录下生成 3 个文件：

• user1.MYD：存储表的实际数据；
• user1.MYI：存储表的索引信息；
• user1.sdi：序列化字典信息，替代旧版本 .frm 文件存储表结构。

user2 表（InnoDB 引擎，默认配置）：数据库目录下仅生成 user2.ibd 文件，存储表的所有数据、索引，表结构信息统一存入 InnoDB 数据字典（无 .frm/.sdi 独立文件）；若关闭 innodb_file_per_table，则无独立文件，数据 / 索引存入共享表空间 ibdata1。

存储文件示例路径

-rw-r----- 1 mysql mysql   3996  2月 23 17:03 user1_702.sdi
-rw-r----- 1 mysql mysql      0  2月 23 17:03 user1.MYD
-rw-r----- 1 mysql mysql   1024  2月 23 17:03 user1.MYI
-rw-r----- 1 mysql mysql 114688  2月 23 17:05 user2.ibd

2. 向表中插入数据（insert）

2.1 标准语法（指定字段插入）

INSERT INTO 表名(字段1,字段2,字段3...) 
VALUES(值1,值2,值3...);

示例：

INSERT INTO student(id,name,age) 
VALUES(1,'张三',18);

2.2 省略字段（整表按顺序插入）

必须给所有字段都传值

INSERT INTO 表名 
VALUES(值1,值2,值3);

示例：

INSERT INTO student 
VALUES(2,'李四',20);

2.3 一次性插入多条数据（常用）

INSERT INTO 表名(字段1,字段2)
VALUES
(值1,值2),
(值3,值4),
(值5,值6);

示例：

INSERT INTO student(name,age)
VALUES
('王五',19),
('赵六',21);

注意

• 字符串、日期要用单引号 ''
• 值的顺序、类型要和字段一一对应
• 每条语句结尾加 ;

3. 查看表结构

1. 查看数据库中的表目录

show tables;

2. 查看某一个表的表结构

desc 表名;

# 示例
desc user1;

字段解读：

• Field：字段名字
• Type：字段类型
• Null：是否允许为空
• Key：索引类型
• Default：默认值
• Extra：扩充属性

2. 查看创建表时的详细信息

show create table user1;

# 补：把分号换成\G可以将不想需要的符号去掉
show create table user1 \G

4. 修改表的结构

在项目实际开发中，经常需要修改表的结构，比如字段名字、字段大小、字段类型、表的字符集类型、表的存储引擎等；也会有添加字段、删除字段、修改表名、修改字段名等需求，此时需要通过 ALTER TABLE 语句修改表。

核心语法

-- 添加字段
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][, column datatype]...);

-- 修改字段（类型/长度/默认值等）
ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][, column datatype]...);

-- 删除字段
ALTER TABLE tablename DROP (column);

-- 修改表名
ALTER TABLE tablename RENAME TO 新表名;

-- 修改字段名
ALTER TABLE tablename CHANGE 旧字段名 新字段名 字段类型;

操作案例

插入测试数据

mysql> insert into user1 values(1, 'a', 'b', '1982-01-04'), (2, 'b', 'c', '1984-01-04');

4.1 添加字段（保存图片路径）

mysql> alter table user1 add assets varchar(100) comment '用户头像路径' after birthday;

查看修改后表结构：

mysql> desc user1;

Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL
name	varchar(20)	YES		NULL
password	char(32)	YES		NULL
birthday	date	YES		NULL
assets	varchar(100)	YES		NULL

查看数据（新增字段默认值为 NULL）：

mysql> select * from users;

id	name	password	birthday	assets
1	a	b	1982-01-04	NULL
2	b	c	1984-01-04	NULL

4.2 修改字段（调整 name 字段长度为 60）

mysql> alter table user1 modify name varchar(60);

覆盖式修改，整个name字段被新的配置覆盖。

查看修改后表结构：

4.3 删除字段（删除 password 字段）

mysql> alter table users drop password;

注意：删除字段会永久删除该字段及其对应的所有数据，操作需谨慎。

查看删除后表结构：

mysql> desc users;

Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL
name	varchar(60)	YES		NULL
birthday	date	YES		NULL
assets	varchar(100)	YES		NULL

4.4 修改表名（users 改为 employee）

mysql> alter table user1 rename to users;
# "to"可以省略

验证表名修改：

mysql> select * from users;

id	name	birthday	assets
1	a	1982-01-04	NULL
2	b	1984-01-04	NULL

4.5 修改字段名（name 改为 xingming）

mysql> alter table users change name xingming varchar(60);

修改字段名的同时也要重新设置字段属性

查看修改后表结构：

mysql> desc users;

Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL
xingming	varchar(60)	YES		NULL
birthday	date	YES		NULL
assets	varchar(100)	YES		NULL

5. 删除表

语法

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

示例

drop table user2;

结论：

1. 不要轻易修改表和删除表！

…过云雨-CSDN博客