4 MySQL 数据库和表

《第4章 MySQL数据库和表》文档总结

一、MySQL数据库简介

1. 查看已有数据库

使用SHOW DATABASES;命令查看MySQL服务器中的所有数据库。

SHOW DATABASES;

默认包含4个系统数据库，删除这些系统数据库会导致MySQL无法正常工作。

2. 系统数据库作用

数据库名称	主要作用
mysql	描述用户访问权限，记录用户账号、密码及权限分配等信息
information_schema	保存MySQL服务器维护的所有其他数据库信息（元数据字典）
performance_schema	收集数据库服务器性能参数，监控数据库运行状态
sys	包含存储过程、自定义函数和视图，简化数据库管理操作

二、定义数据库（核心操作与语法）

（一）创建数据库

语法：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name 
[[DEFAULT] CHARACTER SET charset_name] 
[[DEFAULT] COLLATE collation_name];

关键参数说明：

• {DATABASE | SCHEMA}：功能相同，二选一
• IF NOT EXISTS：避免重复创建时报错
• CHARACTER SET：指定数据库默认字符集
• COLLATE：指定字符集的校对规则

示例：

-- 创建学生信息数据库
CREATE DATABASE stusys;

-- 创建数据库时指定字符集
CREATE DATABASE stusys 
DEFAULT CHARACTER SET utf8mb4 
DEFAULT COLLATE utf8mb4_0900_ai_ci;

（二）选择数据库

语法：

USE db_name;

示例：

USE stusys;

（三）修改数据库

语法：

ALTER {DATABASE | SCHEMA} [db_name] 
[DEFAULT] CHARACTER SET charset_name 
[DEFAULT] COLLATE collation_name;

示例：

-- 修改数据库字符集和校对规则
ALTER DATABASE stusys 
DEFAULT CHARACTER SET gb2312 
DEFAULT COLLATE gb2312_chinese_ci;

（四）删除数据库

语法：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name;

示例：

DROP DATABASE stusys;

⚠️ 注意： 删除操作不可逆，需谨慎执行。

三、表的基本概念

核心术语

术语	定义	示例（学生表student）
表	数据库中存储数据的对象，由行和列组成	包含6行6列的数据表
表结构	表的固定属性（列数量、数据类型、长度等）	sno列数据类型为char(6)、NOT NULL
记录	表中的一行数据，代表一个具体实例	学号191001、姓名刘清泉等组成的一行
字段	表中的一列，代表数据的一个属性	学号（sno）、姓名（sname）等列
空值（Null）	表示未知、不可用或待添加的数据	专业未填写时speciality列为Null
关键字	唯一标识记录的字段或字段组合	主键为"学号（sno）"
默认值	插入数据时未指定列值的自动填充值	ssex列默认值为"男"

表结构设计示例（学生表student）

CREATE TABLE student (
    sno char(6) NOT NULL PRIMARY KEY,      -- 学号，主键
    sname char(8) NOT NULL,                -- 姓名
    ssex char(2) NOT NULL DEFAULT '男',    -- 性别，默认'男'
    sbirthday date NOT NULL,               -- 出生日期
    speciality char(12) NULL,              -- 专业，允许空
    tc tinyint NULL                        -- 总学分，允许空
);

四、MySQL数据类型

（一）数值类型

类型	字节数	无符号取值范围	有符号取值范围	说明
tinyint	1	0~255	-128~127	小范围整数（如总学分）
smallint	2	0~65535	-32768~32767	中等范围整数
int	4	0~4294967295	-2147483648~2147483647	常用整数类型
bigint	8	0~1.84×10¹⁹	±9.22×10¹⁸	大范围整数
decimal(m,d)	可变	/	/	定点数，精确计算
float(m,d)	4	/	/	单精度浮点数
double(m,d)	8	/	/	双精度浮点数

（二）字符串类型

类型	取值范围	说明
char(n)	0~255个字符	固定长度，尾部空格截断
varchar(n)	0~65535个字符	可变长度，不截断空格
tinytext	0~255个字符	短文本，可变长度
text	0~65535个字符	长文本，可变长度

（三）日期和时间类型

类型	取值范围	格式	说明
date	1000-01-01~9999-12-03	YYYY-MM-DD	仅存储日期
time	-838:58:59~835:59:59	HH:MM:SS	仅存储时间
datetime	1000-01-01 00:00:00~9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	日期+时间
timestamp	1970-01-01 00:00:00~2037年	YYYY-MM-DD HH:MM:SS	受时区影响

（四）其他类型

• 二进制类型：binary、varbinary、blob系列
• 枚举类型：enum('成员1','成员2',...)
• 集合类型：set('成员1','成员2',...)

五、定义表（核心操作与语法）

（一）创建表

1. 创建新表

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[ ( [column_definition],... [index_definition] ) ] 
[table_option] [SELECT_statement];

示例：

CREATE TABLE student (
    sno char(6) NOT NULL PRIMARY KEY,
    sname char(8) NOT NULL,
    ssex char(2) NOT NULL DEFAULT '男',
    sbirthday date NOT NULL,
    speciality char(12) NULL,
    tc tinyint NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 复制已有表

方式一：复制表结构（不含数据）

CREATE TABLE student1 LIKE student;

方式二：复制表结构和数据（不含索引）

CREATE TABLE student2 AS SELECT * FROM student;

（二）查看表

1. 查看表名称

SHOW TABLES FROM stusys;

2. 查看表基本结构

-- 方式一
SHOW COLUMNS FROM student;

-- 方式二
DESC student;
DESCRIBE student;

3. 查看表详细结构

SHOW CREATE TABLE student\G;

（三）修改表

1. 添加列

-- 在第一列添加自增ID
ALTER TABLE student 
ADD COLUMN sid int NOT NULL UNIQUE AUTO_INCREMENT FIRST;

2. 修改列

-- 修改默认值
ALTER TABLE student 
ALTER COLUMN ssex SET DEFAULT '女';

-- 修改列名和类型
ALTER TABLE student1 
CHANGE COLUMN sbirthday sage tinyint DEFAULT 18;

3. 删除列

ALTER TABLE student DROP COLUMN sid;

4. 重命名表

-- 方式一
ALTER TABLE student1 RENAME TO student2;

-- 方式二
RENAME TABLE student2 TO student3;

（四）删除表

语法：

DROP [TEMPORARY] TABLE [IF NOT EXISTS] table_name [, table_name]...;

示例：

DROP TABLE student3;

六、存储引擎

查看存储引擎

SHOW ENGINES;

MySQL 8.0主要存储引擎比较

存储引擎	事务支持	外键支持	锁级别	适用场景
InnoDB	✅	✅	行级锁	默认引擎，需要事务保障
MyISAM	❌	❌	表级锁	只读或读写少的场景
MEMORY	❌	❌	表级锁	临时数据，内存存储
CSV	❌	❌	表级锁	数据交换
ARCHIVE	❌	❌	行级锁	归档数据

存储引擎选择原则

• 需要事务支持：选择 InnoDB
• 只读或读多写少：选择 MyISAM
• 临时数据存储：选择 MEMORY
• 数据归档：选择 ARCHIVE

推荐： 大多数场景下使用默认的 InnoDB 存储引擎。