开发者空间里玩转MySQL之基础知识与表创建-优快云博客

1 概述

1.1 背景介绍

在云计算时代，MySQL 凭借其开源、高性能、易用性，成为全球最受欢迎的数据库之一。无论是个人开发者还是大型企业，都可以利用 MySQL 构建稳定、高效的数据存储系统。通过实际操作，让大家能掌握MySQL的部署流程，深入理解数据库与表的设计原则，为后续复杂业务场景（如多表关联、事务处理）打下基础。

1.2 适用对象

企业
个人开发者
高校学生

1.3 案例时间

本案例总时长预计60分钟。

1.4 案例流程

{{{width="40%" height="auto"}}} ① 领取空间开发桌面； ② 在空间开发桌面中安装Mysql并进行设置； ③ 进行数据库的建库、建表、表的增删改查等操作；

1.5 资源总览

本案例预计花费总计0元。 | 资源名称 | 规格 | 单价（元） | 时长（分钟） | |--------------------|--------------------------------------------------------------------|----------------|------------------| |开发者空间 - 空间开发桌面|ARM| 4 vCPUs 8GB | Ubuntu | 0 |60|

2 数据库概论

2.1 数据库基本概念

2.1.1 什么是数据库

数据库（Database，简称DB）是结构化数据的集合，用于高效存储、管理和检索信息。它通过数据库管理系统（DBMS）进行控制，确保数据的安全性、一致性和持久性。现代数据库核心特点有： - 持久化存储：数据长期保存，不随程序关闭而丢失； - 结构化组织：数据按特定模型（如关系型、非关系型）存储； - 共享与并发控制：支持多用户同时访问，避免冲突； - 高效查询：通过SQL等语言快速检索数据；

2.1.2 数据管理技术的发展历程

随着计算机应用的演进，数据管理技术的发展历程经历了以下阶段： 1、人工管理阶段（1950年代前）：该阶段的数据库特点是数据与程序直接绑定，无独立存储，且冗余高、无法共享、依赖特定硬件； 2、文件系统阶段（1950s–1960s）：该阶段数据库特点是数据以文件（如文本、二进制）形式存储，其进步点在于数据与程序部分分离，可长期保存；但仍存在冗余仍高、缺乏统一管理、安全性差的缺点； 3、数据库系统阶段（1970s–至今）：数据库管理系统（DBMS）诞生（如Oracle、MySQL），相较于更早的数据库产品有几个核心突破： - 数据独立：逻辑与物理存储分离 - 减少冗余：通过规范化设计（如关系模型） - ACID特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

2.1.3 现代数据库

现代数据库主要分为三类： - 关系型数据库（RDBMS）：如MySQL、PostgreSQL，基于二维表结构，使用SQL操作 - 非关系型数据库（NoSQL）：如MongoDB、Redis，适用于灵活数据结构（文档、键值、图等） - 云数据库：如华为云RDS、AWS Aurora，提供高可用、弹性扩展能力

2.2 数据模型

数据模型（Database Data Model）是用于抽象化描述数据、数据关系及数据约束的理论框架，它定义了数据的组织方式、存储结构和操作规则。数据模型是数据库系统的核心，决定了如何高效地存储、管理和查询数据。

2.2.1 两类数据模型

概念数据模型（Conceptual Data Model）特点是面向业务，描述现实世界的实体及其关系，不涉及具体实现；该模型模型典型代表：实体 - 联系模型（E - R Model, Entity - Relationship Model），最经典的概念模型，由 Peter Chen 于 1976 年提出，用于描述现实世界的实体、属性和关系；模型核心组成：

组件	说明	示例
实体（Entity）	现实中的独立对象（如人、订单）	学生、课程
属性（Attribute）	实体的特征（如姓名、学号）	学生.姓名、课程、课程编号
关系（Relationship）	实体间的关联（如选课）	学生 - 选课 - 课程（M:N关系）

E - R图示例：

+---------------+       +------------------+       +------------------+
|学生     |       |选课     |       |课程     |
+---------------+       +------------------+       +------------------+
|学号(PK) |<-------->|学号(FK)      |<-------->|课程编号(PK)|
|姓名     |       |课程编号(FK)|       |课程名       |
|年龄     |       |成绩     |       |学分     |
+---------------+       +------------------+       +------------------+

逻辑/物理数据模型（Logical/Physical Data Model）数据库设计通常分为三个阶段：概念模型 -> 逻辑模型 -> 物理模型。其中，逻辑数据模型和物理数据模型是数据库实现的核心，直接影响数据存储、查询效率和系统性能。以下是两者的详细对比和说明： 1、逻辑数据模型（Logical Data Model），将概念模型（如ER图）转换为DBMS（数据库管理系统）支持的逻辑结构，独立于具体存储技术；核心特点是描述数据的逻辑结构（表、字段、关系），但不涉及存储细节，与具体DBMS无关（如MySQL、Oracle均可基于同一逻辑模型实现），关注数据完整性（主键、外键、约束）；常见逻辑数据模型类型：

模型类型	描述	示例
关系模型	基于二维表（关系），通过主键/外键关联数据	学生表（学号、姓名、年龄）
层次模型	树形结构，数据按父子关系组织（1:N）	XML文档、文件系统目录结构
网状模型	允许实体间多对多（M:N）关系，通过指针链接数据（现已较少使用）	早期的CODASYL数据库
面向对象模型	支持继承、封装等面向对象特性（如PostgreSQL的JSONB类型）	存储员工信息及其技能（嵌套结构）

2、物理数据模型（Physical Data Model），作用：定义数据在存储介质上的具体实现，优化性能；核心特点是与DBMS类型强相关（如MySQL的InnoDB与Oracle的存储结构不同）；关注存储细节如文件组织、索引、分区、压缩等；直接影响查询速度、存储空间和并发性能；物理模型的关键技术：

技术	作用	示例
存储引擎	决定数据如何存储和访问（如事务支持、锁机制）	MySQL的InnoDB（事务支持） vs MyISAM（非事务）
索引	加速查询，但增加写入开销	B+树索引、哈希索引、全文索引
分区	将大表拆分为物理文件，提高查询效率	按时间范围分区（如按年存储日志）
表空间	管理物理存储文件的位置和大小	Oracle的表空间（TABLESPACE）
数据压缩	减少存储空间占用，但可能增加CPU开销	PostgreSQL的TOAST压缩

2.2.2 上述两类数据模型的对比总结：

类别	面向对象	核心目标	典型技术
概念数据模型	业务需求	描述业务实体和关系	ER图、UML类图
逻辑数据模型	数据库设计	映射为DBMS可理解的结构	关系模型、层次模型
物理数据模型	数据库实现	优化存储和性能	索引、分区、存储引擎

2.3 关系数据库

关系数据库（Relational Database）是基于关系模型的数据库，是目前应用最广泛的数据库类型（如MySQL、Oracle、PostgreSQL等）；其核心是通过二维表（关系）存储数据，并通过关系运算操作数据。

2.3.1 关系型数据库的基本概念

关系型数据库的核心术语：

术语	说明	示例
关系（Relation）	即二维表，由行和列组成	学生表（学号, 姓名, 年龄）
元组（Tuple）	表中的一行数据，表示一个实体	(1001, "张三", 20)
属性（Attribute）	表中的一列，表示实体的特征	学号、姓名、年龄
域（Domain）	属性的取值范围（数据类型）	学号：INT，姓名：VARCHAR(50)
主键（Primary Key）	唯一标识元组的属性（或属性组），不可重复且非空	学号是学生表的主键
外键（Foreign Key）	引用其他表主键的属性，用于维护表间关系	学生表中的班级ID引用班级表的主键

关系型数据库有几个特点： - 结构化存储：数据以规范的二维表形式存储 - 数据完整性：通过主键、外键、约束（如NOT NULL）保证数据有效性 - 操作语言：使用SQL（结构化查询语言）进行增删改查 - 事务支持：满足ACID（原子性、一致性、隔离性、持久性）特性

2.3.2 运算关系

关系运算是对关系（表）进行操作的数学基础，分为传统集合运算和专门关系运算； 1、传统集合运算假设有两个关系表R和S（两者必须具有相同的属性结构）：

运算	符号	说明	示例
并（Union）	R ∪ S	返回所有属于R或S的元组（去重）	合并两个班级的学生名单
差（Difference）	R - S	返回属于R但不属于S的元组	找出选修数学但未选修物理的学生
交（Intersection）	R ∩ S	返回同时属于R和S的元组	找出同时选修数学和物理的学生
笛卡尔积（Cartesian Product）	R × S	返回R和S所有可能的元组组合（行数=∣R∣×∣S∣）	生成所有学生和课程的选课可能性

2、专门关系运算

运算	符号/关键字	说明	SQL示例
选择（Selection）	σ（Sigma）	按条件筛选表中的行（水平操作）	SELECT * FROM Student WHERE age > 18;
投影（Projection）	π（Pi）	选择表中的指定列（垂直操作）	SELECT name, age FROM Student;
连接（Join）	⋈	根据关联条件合并多个表的行	SELECT * FROM Student JOIN Enrollment ON Student.id = Enrollment.student_id;
除（Division）	÷	找出满足“所有”条件的元组（如“选修了所有课程的学生”）	需通过多层嵌套查询实现

补充: 其中连接运算的常见类型：连接类型说明 SQL语法内连接（INNER JOIN）仅返回两表中匹配的行 SELECT * FROM A INNER JOIN B ON A.key = B.key; 左连接（LEFT JOIN） MySQL中是left outer join的简写，全称是左外连接；功能是返回左表所有行，右表无匹配时补NULL SELECT * FROM A LEFT JOIN B ON A.key = B.key; 右连接（RIGHT JOIN） MySQl中是right outer join的简写，全称是右外连接；功能是返回右表所有行，左表无匹配时补NULL SELECT * FROM A RIGHT JOIN B ON A.key = B.key; 全外连接（FULL OUTER JOIN）返回两表所有行，无匹配时补NULL（MySQL不支持，需用UNION模拟） SELECT * FROM A LEFT JOIN B ON A.key = B.key UNION SELECT * FROM A RIGHT JOIN B ON A.key = B.key;

2.3.3 关系型数据库的完整性约束

约束类型	作用	示例
实体完整性	主键不能为NULL且必须唯一	PRIMARY KEY (student_id)
参照完整性	外键必须引用其他表的主键或NULL	FOREIGN KEY (class_id) REFERENCES Class(class_id)
用户定义完整性	自定义规则（如年龄必须大于0）	CHECK (age > 0)

2.3.4 SQL与关系运算的对应关系

约束类型	作用
选择（σ）	WHERE
投影（π）	SELECT 列名
连接（⋈）	JOIN
并（∪）	UNION
差（−）	EXCEPT（或NOT IN）
交（∩）	INTERSECT（或INNER JOIN）

3 MySQL数据库

MySQL 是最流行的开源关系型数据库管理系统（RDBMS），由瑞典公司 MySQL AB 开发，后被 Oracle 收购。它广泛应用于 Web 应用、企业级系统、数据分析等领域，支持 SQL（结构化查询语言），并具有高性能、高可靠性、易用性和低成本等优势。

3.1 MySQL的特点

3.1.1 Mysql数据库的核心特点：

开源免费：MySQL 采用 GPL 开源协议，社区版（MySQL Community Edition）可免费使用；企业版（MySQL Enterprise Edition）提供高级功能和技术支持
跨平台支持：支持 Windows、Linux、macOS、Solaris 等多种操作系统
高性能优化：存储引擎优化 - 支持多种存储引擎（如 InnoDB、MyISAM），InnoDB 提供事务支持和行级锁，适用于高并发场景；索引优化 - 支持 B+树索引、哈希索引、全文索引等，提高查询效率；查询缓存（MySQL 8.0已移除） - 早期版本可缓存查询结果，减少重复计算；
高可用性和可扩展性：主从复制（Replication） - 支持数据同步，提高可用性和负载均衡；分片（Sharding） - 可通过中间件（如 Vitess、ShardingSphere）实现水平扩展；集群方案 - 如 MySQL NDB Cluster（高可用）、InnoDB Cluster（基于 Group Replication）；
安全性：访问控制 - 支持用户权限管理（GRANT/REVOKE）; 数据加密 - 支持 TLS/SSL 传输加密、表空间加密（MySQL Enterprise Edition）; 审计日志 - 记录数据库操作，便于安全审计;
易用性：具有丰富的工具支持，包括命令行工具（mysql、mysqldump、mysqladmin）和图形化管理工具（MySQL Workbench、phpMyAdmin、Navicat）；并兼容标准 SQL，支持存储过程、触发器、视图等高级功能

3.1.2 MySQL 8.0 的新特性

MySQL 8.0（2018年发布）是当前主流版本，相比5.7有重大改进,具体改进方面： - 性能优化：

特性	说明
原子 DDL	DDL（如 CREATE TABLE）操作具有原子性，失败时自动回滚，避免数据不一致
直方图统计	优化查询计划，提高复杂查询性能
并行查询	支持多线程执行查询（适用于大表分析）

数据字典改进：元数据存储方式：从 .frm 文件改为 InnoDB 表，提高 DDL 操作速度；系统表不可见：用户无法直接修改 mysql 系统库的表，增强安全性；
增强的json支持： JSON 聚合函数：如 JSON_ARRAYAGG()、JSON_OBJECTAGG()，方便处理 JSON 数据； JSON 路径表达式：支持->> 操作符，简化 JSON 字段查询； JSON 部分更新：可更新 JSON 文档的特定部分，而非整个文档；
窗口函数（Window Functions）：支持 OVER() 子句，实现复杂分析查询（如排名、累计求和）；语句示例：

-- 计算每个部门的工资排名
SELECT name, salary, department, 
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

角色管理（Role - Based Access Control, RBAC）：可定义角色并分配权限，简化用户权限管理；语句示例： -- 创建角色并授权

CREATE ROLE 'read_only';
GRANT SELECT ON *.* TO 'read_only';

-- 将角色赋予用户
GRANT 'read_only' TO 'user1';

其他改进：默认存储引擎：从 MyISAM 改为 InnoDB，提供事务支持和崩溃恢复能力隐藏索引：可标记索引为“隐藏”，测试删除索引的影响而不实际删除 CTE（公共表表达式）：支持 WITH 子句，提高复杂查询的可读性

3.1.3 MySQL 在不同领域的实际应用案例

1、Web 应用开发领域典型应用：WordPress、Drupal、Joomla 等 CMS（内容管理系统）；典型案例： Facebook（早期）：使用 MySQL 存储用户数据（后迁移到自研数据库）； Twitter：早期采用 MySQL，后结合分布式存储优化； 2、电子商务领域典型应用：Magento、Shopify（部分版本）；典型案例：阿里巴巴：早期使用 MySQL 支持淘宝、天猫的交易系统； Amazon：部分业务使用 MySQL 管理商品和订单数据； 3、金融与支付领域典型应用：银行交易记录、支付系统（如 PayPal 早期架构）；典型案例： PayPal：使用 MySQL 处理高并发的支付事务（结合分库分表）； 4、游戏领域典型应用：玩家数据存储、排行榜、日志分析；典型案例：《王者荣耀》：部分数据存储使用 MySQL； 5、物联网（IoT）领域典型应用：设备数据采集、监控系统；典型案例：智能家居：存储传感器数据（如温湿度记录）； 6. 数据分析与报表领域典型应用：结合 BI 工具（如 Tableau、Power BI）进行数据分析；典型案例： Uber：早期使用 MySQL 存储行程数据（后迁移到大数据平台）；

3.2 定义数据库

在 MySQL 中，数据库（Database）是存储数据的逻辑容器，包含表、视图、存储过程等对象；本节介绍如何创建、选择、修改和删除数据库。

3.2.1 环境准备-开发者空间的配置

面向广大开发者群体，华为开发者空间提供一个随时访问的“开发桌面云主机”、丰富的“预配置工具集合”和灵活使用的“场景化资源池”，开发者开箱即用，快速体验华为根技术和资源。如果还没有领取开发者空间云主机，可以参考免费领取云主机文档领取。领取云主机后可以直接进入华为开发者空间工作台界面，点击进入桌面连接云主机。

3.2.2 MySQL安装

进入空间开发桌面后，打开命令终端窗口，进行Mysql的安装； 1、更新软件包列表：

sudo apt update

2、安装 MySQL 运行以下命令安装 MySQL 服务器：

sudo apt install mysql-server
# 按照系统会提示你确认安装，输入 Y 并按回车继续

3、启动 MySQL 服务

# 安装完成后，MySQL 服务会自动启动。你可以检查其状态：
sudo systemctl status mysql

# 如果未运行，手动启动：
sudo systemctl start mysql

4、运行安全配置脚本（可选推荐，本案例为简化操作未进行设置）

# 执行安全脚本以设置 root 密码、移除匿名用户等：
sudo mysql_secure_installation

# 按提示操作（可自定义）：
# 设置root密码（如果是首次安装）。
# 移除匿名用户（输入 Y）。
# 禁止远程 root 登录（可选，输入 Y）。
# 移除测试数据库（输入 Y）。
# 重新加载权限表（输入 Y）。

5、登录 MySQL

# 使用root用户登录：
sudo mysql -u root -p
# 输入你设置的密码即可进入 MySQL 命令行。

3.2.3 创建数据库

1、基本语法

CREATE DATABASE [IF NOT EXISTS] `数据库名`
[DEFAULT CHARACTER SET `字符集`]
[COLLATE `排序规则`];

IF NOT EXISTS：避免重复创建时报错；
DEFAULT CHARACTER SET：指定默认字符集（如 utf8mb4）；
COLLATE：指定排序规则（如 utf8mb4_general_ci）； 2、示例

-- 创建名为 `university` 的数据库，使用utf8mb4字符集
CREATE DATABASE IF NOT EXISTS university
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

说明： - utf8mb4 支持完整的 Unicode（包括 emoji），推荐使用； - utf8mb4_general_ci 是大小写不敏感的排序规则；通过命令查看创建结果：

show databases;

3.2.4 选择数据库

1、基本语法

USE `数据库名`;

2、示例

-- 切换到 `university` 数据库
USE university;

说明： - 执行USE后，后续SQL操作默认在该数据库中进行； - 若不选择数据库，需在表名前指定数据库名（如university.students）；

3.2.5 修改数据库

1、修改字符集或排序规则

ALTER DATABASE `数据库名`
CHARACTER SET `新字符集`
COLLATE `新排序规则`;

2、示例

-- 将 `university` 的字符集改为 `utf8mb4`，排序规则改为 `utf8mb4_unicode_ci`（支持中文字符）
ALTER DATABASE university
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

注意： - 修改仅影响后续创建的表，已有表需单独修改；通过命令查看表修改后规则：

SHOW CREATE DATABASE `数据库名`;

3.2.6 删除数据库

1、基本语法

DROP DATABASE [IF EXISTS] `数据库名`;

IF EXISTS：避免数据库不存在时报错； 2、示例

-- 删除 `university` 数据库
DROP DATABASE IF EXISTS university;

警告： - 删除操作不可逆，所有表和数据会被永久删除！之后可通过命令查看创建结果：

show databases;

注：这里就不演示删除操作了。

3.3 存储引擎

3.3.1 存储引擎概述

存储引擎是 MySQL 的核心组件，负责数据的存储、检索和管理。不同的存储引擎提供不同的特性，如事务支持、索引方式、锁机制等；其主要作用如下： - 数据存储方式：如何存储表数据（如InnoDB使用聚簇索引即主键索引和数据存储在一起。每个表必须有一个主键，如果没有定义主键，InnoDB 会自动生成一个隐藏的主键；MyISAM使用非聚簇索引） - 事务支持：是否支持 ACID 事务（如 InnoDB 支持，MyISAM 不支持） - 锁机制：如支持到行级锁的InnoDB，支持到表级锁（MyISAM） - 外键支持：是否支持外键约束（InnoDB 支持，MyISAM 不支持） - 性能优化：缓存机制、索引优化等

3.3.2 常用存储引擎

MySQL支持多种存储引擎，以下是最常用的几种：

存储引擎	特点	适用场景	默认支持
InnoDB	支持事务、行锁、外键，数据安全可靠	高并发事务（如订单、支付系统）	MySQL 5.5+ 默认
MyISAM	不支持事务，表锁，查询速度快	读多写少（如日志、统计报表）	旧版 MySQL 默认
Memory	数据存储在内存，速度快，重启丢失	临时表、缓存	需要手动启用
Archive	高压缩比，只支持插入和查询	日志归档、历史数据存储	需要手动启用
CSV	数据以 CSV 格式存储	数据导出/导入	需要手动启用

各存储引擎有其特点与使用场景；

3.3.2.1 InnoDB（推荐）

核心特性： - 支持事务（ACID 兼容） - 行级锁（减少锁冲突，提高并发） - 支持外键 - 崩溃恢复（通过 redo log 保证数据安全） - 聚簇索引（数据按主键顺序存储，提高查询效率）

适用场景：需要事务的应用（如银行、电商系统）；高并发写入（如订单、支付系统）；需要外键约束的表；演示语句示例：

CREATE TABLE userInfo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE
) ENGINE=InnoDB;

3.3.2.2 MyISAM

核心特性： - 不支持事务 - 表级锁（并发写入性能差） - 查询速度快（适合读多写少） - 支持全文索引（FULLTEXT） - 数据文件独立存储（.MYD 数据文件 + .MYI 索引文件）

适用场景：读密集型应用（如博客、新闻网站）；不需要事务的静态数据（如日志表）；全文索引搜索（但 MySQL 8.0+ InnoDB 也支持全文索引）；演示语句示例：

CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

3.3.2.3 Memory（内存引擎）

核心特性： - 数据存储在内存（速度极快） - 服务器重启后数据丢失 - 不支持 TEXT/BLOB 类型 - 哈希索引（适合等值查询）

适用场景：临时表（如会话数据）；缓存数据（如频繁查询的配置表）；演示语句示例：

CREATE TABLE temp_sessions (
    session_id VARCHAR(100) PRIMARY KEY,
    user_id INT,
    data JSON
) ENGINE=MEMORY;

3.3.2.4 Archive（归档引擎）

核心特性： - 高压缩比（节省存储空间） - 只支持 INSERT 和 SELECT - 不支持索引（仅支持自增主键）

适用场景：日志归档（如历史订单）；审计数据（只读需求）；演示语句示例：

CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50),
    details TEXT
) ENGINE=ARCHIVE;

3.3.3 选择存储引擎

根据前述了解的引擎特点，在实际使用中，我们可以根据场景特点和引擎能力选择合适的引擎，下面列举一些典型场景的引擎使用推荐：

需求	推荐引擎
需要事务支持	InnoDB
高并发写入	InnoDB
读多写少，查询快	MyISAM
临时数据，内存加速	Memory
日志归档，高压缩	Archive
具体选择建议：
- 默认使用 InnoDB（MySQL 5.5+ 默认引擎，支持事务、行锁、外键）；
- MyISAM 仅用于只读或低频写入场景（如报表统计）；
- Memory 引擎用于临时数据（如缓存表）；
- Archive 用于历史数据归档（节省存储空间）；

3.3.4 存储引擎相关操作

3.3.4.1 查看当前支持的引擎

SHOW ENGINES;

输出示例：

3.3.4.2 查看表的存储引擎

演示语句示例：

SHOW TABLE STATUS LIKE '表名';
# 或
SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = '数据库名';

3.3.4.3 修改表的存储引擎

ALTER TABLE 表名 ENGINE=InnoDB;

演示语句示例：

ALTER TABLE logs ENGINE=InnoDB;  
-- 将 MyISAM 表转为 InnoDB

4 SQL语言

4.1 SQL语言

4.1.1 SQL语言的特点

SQL（Structured Query Language，结构化查询语言）是用于管理关系型数据库的标准语言，具有以下核心特点：

特点	说明
声明式语言	只需告诉数据库"做什么"，而不需要关心"如何做"（由数据库引擎优化执行）
标准化	遵循 ANSI/ISO SQL 标准，但不同数据库（MySQL、Oracle、SQL Server）有各自扩展
多功能	支持数据查询、操作、定义、控制等所有数据库操作
不区分大小写	关键字（如 SELECT、FROM）通常大写，但 SELECT 和 select 是等效的
可嵌套	支持子查询（一个 SQL 语句内嵌入另一个 SQL 语句）
高可读性	语法接近自然语言（如 SELECT name FROM users WHERE age > 18）

4.1.2 SQL语言的分类

SQL 按功能可分为 4 大类：

分类	关键字示例	作用
DQL（数据查询语言）	SELECT	从数据库查询数据
DML（数据操作语言）	INSERT, UPDATE, DELETE	增删改数据
DDL（数据定义语言）	CREATE, ALTER, DROP	定义/修改表结构
DCL（数据控制语言）	GRANT, REVOKE	控制访问权限

4.1.2.1 DQL（Data Query Language，数据查询语言）

核心作用是从数据库中查询数据，不修改数据本身；部分典型语句：

SELECT 列名1, 列名2 
FROM 表名 
WHERE 条件 
GROUP BY 分组列 
HAVING 分组条件 
ORDER BY 排序列 
LIMIT 返回行数;

示例： -- 查询年龄大于18岁的用户，按姓名排序

SELECT id, name, age 
FROM users 
WHERE age > 18 
ORDER BY name;

4.1.2.2 DML（Data Manipulation Language，数据操作语言）

作用是对数据库中的数据进行增删改操作；部分典型语句：

操作	语法	示例
插入数据	INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);	INSERT INTO users (name, age) VALUES ('张三', 25);
更新数据	UPDATE 表名 SET 列1=值1 WHERE 条件;	UPDATE users SET age=26 WHERE id=1;
删除数据	DELETE FROM 表名 WHERE 条件;	DELETE FROM users WHERE id=1;

4.1.2.3 DDL（Data Definition Language，数据定义语言）

用于定义和管理数据库结构（如表、索引、视图等）；主要用于数据库结构管理、约束定义、数据完整性控制和性能优化；部分典型语句： |操作 |语法 |示例| |-----------|--------------|------------| |创建表 |CREATE TABLE 表名 (列1 数据类型, 列2 数据类型); |CREATE TABLE users (id INT, name VARCHAR(50));| |修改表 |ALTER TABLE 表名 ADD/DROP/MODIFY 列名数据类型; |ALTER TABLE users ADD email VARCHAR(100);| |删除表 |DROP TABLE 表名; |DROP TABLE users;|

4.1.2.4 DCL（Data Control Language，数据控制语言）

用数据库权限管理和访问控制；主要作用是权限管理、用户管理、角色管理（MySQL 8.0+）和审计与安全；部分典型语句： |操作 |语法 |示例| |-----------|--------------|------------| |授予权限 |GRANT 权限 ON 数据库.表 TO 用户; |GRANT SELECT ON school.* TO 'user1'@'localhost';| |撤销权限 |REVOKE 权限 ON 数据库.表 FROM 用户; |REVOKE DELETE ON school.users FROM 'user1'@'localhost';|

4.1.3 其他 SQL 分类（扩展）

分类	说明	示例
TCL（事务控制语言）	管理数据库事务	COMMIT, ROLLBACK, SAVEPOINT
SCL（会话控制语言）	控制数据库会话	SET, ALTER SESSION (Oracle)

4.2 MySQL语言组成

MySQL 的 SQL 语言由多个组成部分构成，主要包括关键字、函数、运算符、数据类型、变量和控制语句。下面先简单介绍下常用的部分（后续章节会有其他不常用部分的详细介绍）：

4.2.1 MySQL 关键字（Keywords）

关键字是 MySQL 中具有特殊含义的保留字，用于构建 SQL 语句。例如： - 数据查询：SELECT， FROM， WHERE，GROUP BY，HAVING，ORDER BY，LIMIT； - 数据操作：INSERT，UPDATE，DELETE， REPLACE； - 数据定义：CREATE，ALTER， DROP，TRUNCATE； - 事务控制：COMMIT，ROLLBACK，SAVEPOINT； - 权限管理：GRANT，REVOKE；示例：

SELECT * FROM users WHERE age > 18 ORDER BY name LIMIT 10;

4.2.2 MySQL 函数（Functions）

MySQL 提供多种内置函数，用于数据处理和计算，主要分为以下几类：

4.2.2.1 聚合函数（Aggregate Functions）

COUNT()：计算行数
SUM()：求和
AVG()：求平均值
MAX() / MIN()：求最大值/最小值
GROUP_CONCAT()：合并多行数据为字符串示例：

-- 统计用户总数
SELECT COUNT(*) FROM users;
-- 计算平均年龄
SELECT AVG(age) FROM users;

4.2.2.2 字符串函数（String Functions）

CONCAT(str1, str2)：字符串拼接
SUBSTRING(str, start, length)：截取子串
UPPER(str) / LOWER(str)：转大写/小写
TRIM(str)：去除两端空格
REPLACE(str, old, new)：替换字符串示例：

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

4.2.2.3 日期和时间函数（Date & Time Functions）

NOW()：当前日期和时间
CURDATE() / CURTIME()：当前日期/时间
DATE_FORMAT(date, format)：格式化日期
DATEDIFF(date1, date2)：计算日期差
DATE_ADD(date, INTERVAL expr unit)：日期加减示例：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS today;

4.2.2.4 数学函数（Mathematical Functions）

ABS(x)：绝对值
ROUND(x, d)：四舍五入
FLOOR(x) / CEIL(x)：向下/向上取整
RAND()：随机数（0~1）示例：

-- 输出 3.14
SELECT ROUND(3.14159, 2);

4.2.2.5 条件控制语句（Conditional Functions）

IF(condition, true_value, false_value)：条件判断
CASE WHEN ... THEN ... ELSE ... END：多条件判断
COALESCE(value1, value2, ...)：返回第一个非 NULL 值示例：

SELECT name, IF(age >= 18, 'Adult', 'Minor') AS age_group FROM users;

4.2.3 MySQL 运算符（Operators）

MySQL 支持多种运算符，用于表达式计算和条件判断：

4.2.3.1 算术运算符

运算符	说明	示例
+	加法	-- 5 + 3 = 8 SELECT 5 + 3;
-	减法	-- 10 – 4 = 6 SELECT 10 - 4;
*	乘法	-- 2 X 3 = 6 SELECT 2 * 3;
/	除法	-- 10 ÷ 2 = 5 SELECT 10 / 2;
% 或 MOD	取模	-- 10 % 3 = 1 SELECT 10 % 3;

4.2.3.2 比较运算符

运算符	说明	示例
=	等于	SELECT * FROM users WHERE age = 18;
!= 或 <>	不等于	SELECT * FROM users WHERE age != 18;
> / <	大于/小于	SELECT * FROM users WHERE age > 18;
>= / <=	大于等于/小于等于	SELECT * FROM users WHERE age >= 18;
BETWEEN	范围查询	SELECT * FROM users WHERE age BETWEEN 18 AND 30;
IN	在列表中	SELECT * FROM users WHERE id IN (1, 2, 3);
LIKE	模糊匹配	SELECT * FROM users WHERE name LIKE '张%';

4.2.3.3 逻辑运算符

运算符	说明	示例
AND	逻辑与	SELECT * FROM users WHERE age > 18 AND gender = 'M';
OR	逻辑或	SELECT * FROM users WHERE age < 18 OR age > 60;
NOT	逻辑非	SELECT * FROM users WHERE NOT age = 18;

4.2.3.4 位运算符（较少使用）

运算符	说明	示例
&	按位与	-- 5 & 3 → 1 SELECT 5 & 3;
		按位或
^	按位异或	-- 5 ^ 3 → 6 SELECT 5 ^ 3;
<< / >>	左移/右移	-- 5 << 3 → 4 SELECT 1 << 2;

4.2.4 MySQL 变量（Variables）

MySQL支持变量存储临时数据，分为系统变量和用户变量：

4.2.4.1 系统变量（@@）

-- 查看所有系统变量
SHOW VARIABLES;

-- 查看特定变量（如字符集）
SELECT @@character_set_database;

4.2.4.2 用户变量（@）

-- 定义变量，兼容的Oracle语法
SET @name = '张三';

-- 使用变量
SELECT * FROM users WHERE name = @name;

4.2.5 MySQL 控制语句（Control Flow）

MySQL 支持流程控制语句，主要用于存储过程和触发器：

4.2.5.1 IF语句

-- 存储过程示例
DELIMITER //
CREATE PROCEDURE check_age(IN user_id INT)
BEGIN
    DECLARE user_age INT;
    SELECT age INTO user_age FROM users WHERE id = user_id;
    IF user_age >= 18 THEN
        SELECT 'Adult';
    ELSE
        SELECT 'Minor';
    END IF;
END //
DELIMITER ;

4.2.5.2 CASE语句

SELECT 
    name,
    CASE 
        WHEN age < 18 THEN 'Minor'
        WHEN age BETWEEN 18 AND 60 THEN 'Adult'
        ELSE 'Senior'
    END AS age_group
FROM users;

4.2.5.3 循环语句（WHILE, LOOP, REPEAT）

-- WHILE 循环示例
DELIMITER //
CREATE PROCEDURE countdown(IN start INT)
BEGIN
    WHILE start > 0 DO
        SELECT start;
        SET start = start - 1;
    END WHILE;
END //
DELIMITER ;

5 MySQL表

5.1 表的基本概念

在数据库系统中，表（Table）是组织和存储数据的基本结构。表由行和列组成，类似于电子表格。

5.1.1 表和表结构（字段、记录）等概念

5.1.1.1 表（Table）

也称为关系（Relation）（在关系型数据库中），是数据的二维结构；
每个表有一个唯一（前提是在同一个库或者同schema下）的名称（如学生表、订单表），用于存储特定类型的数据（如学生信息、订单记录）；

5.1.1.2 字段（Field）/ 列（Column）

表的垂直方向称为列或字段，代表数据的某一类属性：例如：学生表可能有“学号”、“姓名”、“年龄”等字段；
每个字段包含一些字段的属性：字段名：标识属性的名称（如name）；数据类型：规定字段中数据的类型（如整数、字符串、日期等）；约束：如是否允许为空（NULL）、是否唯一（UNIQUE）等；

5.1.1.3 记录（Record）/ 行（Row）或元组（Tuple）

表的水平方向称为行或记录，代表一条具体的数据；例如：学生表中一行可能是 (1001, "张三", 20)，表示一个学生的信息。
每一行由多个字段的值组成，且通常通过主键（Primary Key）唯一标识；

5.1.1.4 表结构（Table Schema）

指表的定义，包括：表名，所有字段的名称、数据类型和约束，主键、外键等关系定义；例子：学生表的结构定义

-- 学生表的结构可能定义为（示例，并非可执行语句）
CREATE TABLE 学生 (
    学号 INT PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,
    年龄 INT
);

5.2 数据类型

在数据库中，数据类型定义了字段可以存储的数据种类（如整数、文本、日期等），并决定了数据的存储方式、取值范围和操作规则。合理选择数据类型能优化存储空间、提高查询效率并确保数据完整性；

5.2.1 数值类型

用于存储数字，分为精确数值和近似数值、布尔类型几类，下面是部分类型的说明：

类型	说明	示例
TINYINT	1字节，范围：-128~127 或 0~255（无符号）	年龄、状态码（如0/1）
SMALLINT	2字节，范围：-32768~32767 或 0~65535（无符号）	小型ID、计数器
INT/INTEGER	4字节，范围：约±21亿	主键、订单编号
BIGINT	8字节，范围：约±922亿亿	大型ID（如用户ID）
DECIMAL(p,s)	变长，精确小数，p为总位数，s为小数位数	金额（如DECIMAL(10,2)）
NUMERIC(p,s)	变长，精确小数，p为总位数，s为小数位数，DECIMAL的同义词	金额（如NUMERIC (10,2)）
FLOAT	4字节，近似浮点数，可能丢失精度	科学计算数据
DOUBLE	8字节，比FLOAT精度更高	高精度测量数据
BIT(M)	约(M+7)/8字节，位字段，M范围1-64
BOOL	1字节，0/1的精确值，实际类型TINYINT(1)	0表示FALSE，非0表示TRUE
BOOLEAN	1字节，0/1的精确值，实际类型TINYINT(1)	BOOL的同义词

注意： - 无符号类型（如INT UNSIGNED）仅存储非负数，范围扩大一倍。 - DECIMAL精确但计算慢，FLOAT/DOUBLE快速但可能有舍入误差。

5.2.2 字符串类型

存储文本或二进制字符串，需注意字符集（如UTF-8），以下是部分字符串类型的说明：

类型	说明	示例
CHAR(n)	定长字符串，长度固定为n，不足补空格。效率高但浪费空间	固定长度编码（如CHAR(10)存储身份证号）
VARCHAR(n)	变长字符串，最大长度n，按实际长度存储。节省空间但需额外计算开销	姓名、地址等变长文本
TEXT	存储长文本（如文章），最大支持GB级数据	博客内容、产品描述
ENUM	枚举类型，只能从预定义列表中选择值	性别（‘男’,‘女’）
JSON	存储结构化JSON数据（现代数据库支持）	配置信息、API响应
BINARY(n)	定长二进制数据，类似CHAR	加密后的定长密钥
VARBINARY(n)	变长二进制数据，类似VARCHAR	用户上传的文件
BLOB	大型二进制对象（如图片、视频），分为TINYBLOB、BLOB、LONGBLOB	存储PDF或JPEG文件
ENUM	枚举，预定义值集合	如性别
SET	多选集合，标签、多选项	如省市选项

注意： - CHAR适合短且长度固定的字段（如手机号），VARCHAR适合长度变化的字段； - 避免对TEXT字段直接排序或分组，可能性能低下； - 通常将大文件存储在文件系统中，数据库中只保存路径（而非直接用BLOB）；

5.2.3 日期和时间类型

存储时间点或时间间隔：

类型	说明	示例
DATE	仅存储日期，格式：YYYY-MM-DD	生日、订单日期
TIME	仅存储时间，格式：HH:MM:SS	会议开始时间
DATETIME	存储日期+时间，格式：YYYY-MM-DD HH:MM:SS，范围：1000-9999年	用户注册时间戳
TIMESTAMP	时间戳，范围：1970-2038年，自动转换为UTC时间存储	日志记录时间
YEAR	年份，格式：YYYY，范围1901到2155	年份值

注意： - TIMESTAMP占用4字节（比DATETIME更省空间），但受时区影响； - 需要精确到毫秒时，可使用DATETIME(3)（如2023-01-01 12:00:00.123）；

5.2.4 其他数据类型

类型	说明	示例
BOOLEAN	布尔值（实际用TINYINT(1)表示，0=False，1=True）	是否启用（True/False）
UUID	全局唯一标识符（如PostgreSQL的UUID类型）	分布式系统的主键
GEOMETRY	空间数据类型（如点、线、多边形）	地图坐标数据

5.2.5 数据类型的选择

1、最小化存储：选择能满足需求的最小类型。例如： - 年龄用TINYINT而非INT； - 状态码用ENUM('Y','N')而非VARCHAR(10)； 2、确保精度： - 金额用DECIMAL，避免FLOAT舍入误差； - 时间用DATETIME或TIMESTAMP（根据时区需求）； 3、考虑扩展性： - 用户ID用BIGINT而非INT（防止未来溢出）； 4、性能优化： - 频繁查询的字段避免用TEXT或BLOB； - 定长字段（如邮编）用CHAR，变长字段用VARCHAR； 5、兼容性： - 确保数据类型在所有支持的数据库系统中兼容（如迁移时）；

遵循的原则就是：数据类型的选择需权衡存储效率、查询性能和业务需求，避免过度设计或空间浪费。

5.3 约束

数据库约束（Constraints）用于限制表中数据的规则，确保数据的完整性（Integrity）和一致性（Consistency）。以下是常见的约束类型及其用法：

5.3.1 主键约束：PRIMARY KEY

主键约束的作用：唯一标识表中的每一条记录，确保字段值唯一且非空；特点： - 一个表只能有一个主键（但可以是复合主键，即多个字段组合）； - 主键通常用于关联其他表（作为外键引用）； - 主键默认会创建唯一索引；使用语法示例：

-- 方式1：建表时定义单字段主键
CREATE TABLE 学生 (
    学号 INT PRIMARY KEY,
    姓名 VARCHAR(50)
);

-- 方式2：建表时定义复合主键
CREATE TABLE 选课 (
    学号 INT,
    课程号 INT,
    PRIMARY KEY (学号, 课程号)  -- 复合主键
);

-- 方式3：建表后添加主键
ALTER TABLE 学生 ADD PRIMARY KEY (学号);

注意： - 如果插入重复学号（如1001），数据库会报错；

5.3.2 外键约束：FOREIGN KEY

外键约束的作用：确保一个表的字段值必须引用另一个表的主键，维护表之间的关联关系；特点： - 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性； - 必须为主表定义主键； - 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的； - 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键； - 外键中列的数目必须和主表的主键中列的数目相同； - 外键中列的数据类型必须和主表主键中对应列的数据类型相同； - 可以定义级联操作（如删除主表记录时自动删除从表记录）；

使用语法示例：

-- 主表（被引用的表）
CREATE TABLE 课程 (
    课程号 INT PRIMARY KEY,
    课程名 VARCHAR(50)
);

-- 从表（包含外键的表）
CREATE TABLE 选课 (
    学号 INT,
    课程号 INT,
    FOREIGN KEY (课程号) REFERENCES 课程(课程号)  -- 外键约束
);

-- 支持级联操作（可选）
CREATE TABLE 选课 (
    学号 INT,
    课程号 INT,
    FOREIGN KEY (课程号) REFERENCES 课程(课程号)
        ON DELETE CASCADE  -- 主表删除时，从表自动删除
        ON UPDATE CASCADE  -- 主表更新时，从表自动更新
);

5.3.3 唯一约束：UNIQUE

唯一约束的作用：确保字段值唯一（允许NULL值，但最多一个NULL）；其与主键的区别： - 一个表可以有多个UNIQUE约束，但只能有一个PRIMARY KEY； - UNIQUE允许NULL，而PRIMARY KEY不允许；

使用语法示例：

-- 方式1：建表时定义
CREATE TABLE 用户 (
    用户ID INT PRIMARY KEY,
    邮箱 VARCHAR(100) UNIQUE  -- 邮箱必须唯一
);

-- 方式2：建表后添加
ALTER TABLE 用户 ADD UNIQUE (邮箱);

5.3.4 非空约束：NOT NULL

非空约束的作用：强制字段不允许为NULL（必须提供值）；使用语法示例：

CREATE TABLE 员工 (
    员工ID INT PRIMARY KEY,
    姓名 VARCHAR(50) NOT NULL,  -- 姓名不能为空
    部门 VARCHAR(50)
);

5.3.5 默认值：DEFAULT

默认值的作用：当插入数据时，如果未指定字段值，则使用默认值。使用语法示例：

CREATE TABLE 订单 (
    订单ID INT PRIMARY KEY,
    订单日期 DATE DEFAULT CURRENT_DATE,  -- 默认当前日期
    状态 VARCHAR(20) DEFAULT '待支付'   -- 默认状态
);
-- 如新建语句只包含主键
INSERT INTO 订单 (订单ID) VALUES (1001);  -- 自动填充默认值

记录插入结果： |订单ID |订单日期（DEFAULT） |状态（DEFAULT）| |-------|-----------------------|--------------| |1001 |2023-10-01| 待支付|

5.3.6 其他约束

约束	说明
CHECK	自定义条件检查（如年龄 > 0）
AUTO_INCREMENT	自增字段（常用于主键）

使用语法示例：

-- 示例（CHECK）    
CREATE TABLE 产品 (
    产品ID INT PRIMARY KEY,
    价格 DECIMAL(10,2) CHECK (价格 > 0)  -- 价格必须大于0
);

5.4 定义表

在数据库中，表（Table）是存储数据的基本结构。本节介绍如何创建、查看、修改和删除表，涵盖SQL语句的基本操作。

5.4.1 项目背景

本系列通过模拟一个基于MySQL关系型数据库设计，涵盖用户权限管理、学生信息管理、教师信息管理、课程管理、选课管理等核心模块的系统，主要功能包括： - 角色权限控制：通过区分管理员、教师、学生三类用户，确保操作权限隔离； - 学生选课与成绩管理：学生通过选课，教师录入成绩后自动关联学生与课程； - 课程容量控制：记录课程容量和当前选课人数，避免超额选课； - 数据关联性：通过外键约束确保数据一致性和完整性；下面开始系统的第一步，进入前面创建的数据库，进行表的创建；

USE university;

5.4.2 创建表

使用“CREATE TABLE“语句定义一个新表，指定表名、字段名、数据类型及约束；基本语法：

CREATE TABLE 表名 (
    字段1 数据类型 [约束],
    字段2 数据类型 [约束],
    ...
    [PRIMARY KEY (字段名)]  -- 主键（可选）
    [FOREIGN KEY (字段名) REFERENCES 其他表(字段名)]  -- 外键（可选）
);

语句示例：

-- 创表
-- 用户角色表
CREATE TABLE `role` (
  `role_id` TINYINT PRIMARY KEY COMMENT '角色唯一标识符',
  `role_name` ENUM('admin', 'teacher', 'student') NOT NULL UNIQUE COMMENT '角色类型（管理员/教师/学生）'
) ENGINE=InnoDB COMMENT='系统角色权限表';

-- 用户表
CREATE TABLE `user` (
    `user_id` VARCHAR(50) PRIMARY KEY COMMENT '用户唯一标识符',
    `username` VARCHAR(50) NOT NULL COMMENT '用户登录账号（可重名）',
    `real_name` VARCHAR(50) NOT NULL COMMENT '用户真实中文姓名',
    `password` VARCHAR(255) NOT NULL COMMENT '加密后的用户密码',
    `role_id` TINYINT NOT NULL COMMENT '关联角色表的外键，定义用户权限等级',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '用户账号创建时间',
    FOREIGN KEY (`role_id`) REFERENCES `role`(`role_id`)
) ENGINE=InnoDB COMMENT='系统用户基本信息表';

-- 学生表
CREATE TABLE `student` (
    `student_id` VARCHAR(50) PRIMARY KEY COMMENT '学生唯一标识符',
    `user_id` VARCHAR(50) UNIQUE NOT NULL COMMENT '关联用户表的外键，确保学生账号唯一性',
    `age` INT CHECK (age >= 0) COMMENT '学生年龄（非负整数）',
    `gender` CHAR(1) CHECK (gender IN ('M', 'F')) COMMENT '性别（M=男，F=女）',
    `department` VARCHAR(100) COMMENT '所属院系（如“计算机科学学院”）',
    `class` VARCHAR(20) DEFAULT '未分配' COMMENT '所属班级（如“计算机科学1班”）',
    `enrollment_date` DATE COMMENT '学生入学日期',
    FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) -- 外键
) ENGINE=InnoDB COMMENT='学生详细信息表';

-- 教师表
CREATE TABLE `teacher` (
    `teacher_id` VARCHAR(50) PRIMARY KEY COMMENT '教师唯一标识符',
    `user_id` VARCHAR(50) UNIQUE NOT NULL COMMENT '关联用户表的外键，确保教师账号唯一性',
    `title` VARCHAR(50) COMMENT '职称（如教授、副教授、讲师）',
    `department` VARCHAR(100) COMMENT '所属院系（如“计算机科学学院”）',
    FOREIGN KEY (`user_id`) REFERENCES `user`(`user_id`) -- 外键
) ENGINE=InnoDB COMMENT='教师详细信息表';

-- 课程表
CREATE TABLE `course` (
    `course_id` VARCHAR(50) PRIMARY KEY COMMENT '课程唯一标识符',
    `course_name` VARCHAR(100) NOT NULL COMMENT '课程中文名称（如“数据库系统原理”）',
    `credits` DECIMAL(3,1) NOT NULL COMMENT '课程学分（如3.0、2.5）',
    `teacher_id` VARCHAR(50) NOT NULL COMMENT '关联教师表的外键，指定授课教师',
    `course_capacity` INT CHECK (course_capacity >= 0) COMMENT '课程最大容量（允许选课人数上限）',
    `current_enrollment` INT DEFAULT 0 COMMENT '当前已选课人数（初始值为0）',
    `description` TEXT COMMENT '课程详细描述（如教学目标、内容）',
    `semester` VARCHAR(20) COMMENT '开课学期（如“2023 Fall”）',
    `college_name` VARCHAR(50) COMMENT '所属学院名称',
    FOREIGN KEY (`teacher_id`) REFERENCES `teacher`(`teacher_id`) -- 外键
) ENGINE=InnoDB COMMENT='课程信息及排课表';

-- 选课表
CREATE TABLE `enrollment` (
    `enrollment_id` BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '选课记录唯一标识符（自增长整型）',
    `student_id` VARCHAR(50) NOT NULL COMMENT '关联学生表的外键，指定选课学生',
    `course_id` VARCHAR(50) NOT NULL COMMENT '关联课程表的外键，指定选修课程',
    `semester` VARCHAR(20) NOT NULL COMMENT '选课学期（需与课程学期一致）',
    `grade` DECIMAL(5,2) COMMENT '课程成绩（选修后填写，如85.5）',
    `enrollment_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间（自动记录选课时刻）',
    UNIQUE KEY `student_course_semester` (`student_id`, `course_id`, `semester`) COMMENT '唯一约束：同一学生同一课程同一学期只能选一次',
    FOREIGN KEY (`student_id`) REFERENCES `student`(`student_id`), -- 外键
    FOREIGN KEY (`course_id`) REFERENCES `course`(`course_id`) -- 外键
) ENGINE=InnoDB COMMENT='学生选课记录及成绩表';

建表语句运行示例：

5.4.3 查看表

5.4.3.1 查看数据库中的所有表

SHOW TABLES;

输出示例：

5.4.3.2 查看表结构

DESCRIBE “表名“;  -- 或简写 DESC 表名

语句示例：

DESCRIBE student;

输出示例： (3) 查看建表语句（SQL）

SHOW CREATE TABLE “表名”;

语句示例：

SHOW CREATE TABLE student;

输出示例：

5.4.4 修改表

使用“ALTER TABLE” 修改表结构，如添加、删除、修改字段或调整约束；

5.4.4.1 添加字段

ALTER TABLE 表名 ADD 字段名 数据类型 [约束];

语句示例：

ALTER TABLE `role`
ADD COLUMN `description` VARCHAR(200) COMMENT '角色用途描述（如管理员拥有系统最高权限）',
ADD COLUMN `is_active` BOOLEAN DEFAULT TRUE COMMENT '角色启用状态（TRUE=启用，FALSE=禁用）',
ADD COLUMN `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '角色创建时间';

结果示例：

5.4.4.2 删除字段

ALTER TABLE `表名` DROP COLUMN `字段名`;

语句示例：

ALTER TABLE role DROP COLUMN is_active;

结果示例： 5.4.4.2.png

5.4.4.3 修改字段（数据类型或约束）

ALTER TABLE `表名` MODIFY `字段名` `新数据类型` `[新约束]`;

语句示例：

ALTER TABLE role MODIFY description VARCHAR(300) DEFAULT NULL;

结果示例：

5.4.4.3.png

5.4.4.4 重命名字段

ALTER TABLE `表名` RENAME COLUMN `旧字段名` TO `新字段名`;

语句示例：

ALTER TABLE student RENAME COLUMN name TO student_name;

结果示例：

5.4.4.4.png

5.4.4.5 添加/删除约束

1、添加主键：

ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

2、添加外键

ALTER TABLE 表名 ADD FOREIGN KEY (字段名) REFERENCES 其他表(字段名);

3、删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

4、删除外键（需知道约束名）

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

语句示例：

-- 添加外键
ALTER TABLE student ADD FOREIGN KEY (user_id) REFERENCES user(user_id);

结果示例： 5.4.4.5.png

-- 删除外键（需先查看约束名）
SHOW CREATE TABLE student;  -- 找到外键名（如`student_ibfk_2`）
ALTER TABLE student DROP FOREIGN KEY student_ibfk_2;

结果示例：

5.4.5 删除表

删除表及其所有数据（谨慎使用！）；语法：

DROP TABLE [IF EXISTS] 表名;
-- IF EXISTS 可避免表不存在时报错；

语句示例：

-- 删除学生表
DROP TABLE IF EXISTS student;

-- 删除多个表
DROP TABLE student, course;

注意事项: - 删除表会清除所有数据，不可恢复； - 如果表被其他表的外键引用，需先删除外键约束或关联表。