前言

本文档主要以 CentOS 7 和 MySQL 5.7 作为示例，MySQL 8+ 有许多内容不通用，请注意甄别

1. MySQL 介绍

1.1 数据库与 MySQL

数据库用于存放数据，在软件开发过程中，这种方式更便于管理、速度快、效率高

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

数据库常用的专业术语及含义如下：

  • MySQL 服务器 (MySQL Server)：安装了 MySQL 程序的计算机，称为 MySQL 服务器
  • 数据库 (Database, DB)：存储数据表的仓库，一般会为每个应用都创建一个数据库
  • 数据表 (Table)：存放于数据库中，用于存放每个实体的数据
  • 记录 (Record)：即数据表的行
  • 字段 (Field)：即数据表的列
  • SQL 语句：对数据库、数据表、数据进行操作的语句，以 ; 为结尾
  • SQL 注释语句：用于解释 SQL 语句含义的语句，不会被执行，单行注释以 -- 开头，多行注释使用 /* 和 */ 包裹

1.2 SQL

SQL (Structured Query Language: 结构化查询语言) 是用于管理关系数据库管理系统（RDBMS），它通过一系列的语句和命令来执行数据定义、数据查询、数据操作和数据控制等功能,包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制

某些数据库系统要求在每条 SQL 语句的末端使用分号，分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句

SQL 对大小写不敏感

SQL 语句使用以下符号表示不同的注释：

-- 单行注释

/*
    多行注释
*/

/* 行内注释 */

1.3 数据表

表是数据库的最基本的构成对象，是存储数据的基本单位

一张表就是一个关系，由行和列组成，每行代表一条记录，每列代表记录的一个字段

一个数据库可以由一张或多张表构成

1.3.1 表的命名

一张表的名字由数据库名和数据表名两部分构成，如下：

数据库名.数据表名

那么 database 库中的 table 表应表示为 database.table

1.3.2 表的命名规则

数据表的命名应遵循 MySQL 对象命名规则，如下：

  • 名称由当前字符集的任意 字母、数字、字符 以及 下划线、美元符 组成
  • 名称长度不超过 64 个字符
  • 严格区分大小写

1.3.3 字段

即表的列，每一张表都拥有多个字段，为方便识别，每个字段必须拥有一个名称，规则如下：

  • 字段名可以包含 中文、字母、数字、下划线、美元符、井号(#)、at符号(@)
  • 长度不超过 64 个字符
  • 字段名不可重复

1.4 MySQL 数据类型

MySQL 支持多种数据类型，主要有：

  • 数值数据类型
  • 字符串类型
  • 浮点数类型
  • 定点数类型

所有数值类型都支持有符号或无符号

1.4.1 整数类型

类型存储空间有符号范围无符号范围
tinyint1 Byte-128 ~ 1270 ~ 255
smallint2 Byte-32768 ~ 327670 ~ 65535
mediumint3 Byte-8388608 ~ 83886070 ~ 16777215
int4 Byte-2147483648 ~ 21474836470 ~ 4294967295
tinyint8 Byte-2^63 ~ 2^63-10 ~ 2^64-1

1.4.2 浮点和定点数类型

类型存储空间说明
float(m,d)4 Byte单精度浮点型
double(m,d) / real(m,d)8 Byte双精度浮点数
decimal(m,d) / numeric(m,d)若 p>s 则为 p+2 Byte，否则为 s+2 Byte定点数，默认精度为 10，小数点位数为 0

p 为精度，即整体位数(含小数点)是多少位，s 表示小数部分占的位数

如 float(5,2) 表示该数值类型一共有 5 位，整数和小数部分分别有 2 位

1.4.3 位类型

类型存储空间取值范围说明
bit(m)约 (m+7)/8 Byte0 和 1，若 m 省略，默认值为 1m 表示每个值的位数，从 1 到 64

1.4.4 字符串类型

类型说明存储空间说明
char(m)m Byte固定长度字符串
binary(m)m Byte
varchar(m), varbinary(m)若字符个数少于 255，则为总长 +1 Byte，否则为总长 +2 Byte可变长字符串
tniytext总长 +1 Byte特殊字符串类型
text总长 +2 Byte
mediumtext总长 +3 Byte
longtext总长 +4 Byte

1.4.5 日期类型

类型存储空间数值范围说明
date3 Byte1001-01-01 ~ 9999-12-31存储日期格式：YYYY-MM-DD
time3 Byte + 小数秒存储-838:59:59 ~ -838:59:59存储时间格式：hh:mm:ss
datetime5 Byte + 小数秒存储1001-01-01 00:00:00 ~ 9999-12-31 23:59:59日期时间格式：YYYY-MM-DD hh:mm:ss
timestamp4 Byte + 小数秒存储1970-01-01 00:00:01 ~ 2038-01-19 03:14:07日期时间格式：YYYY-MM-DD hh:mm:ss
year1 Byte1901 ~ 2155表示年，格式 YYYY

1.5 数据完整性约束

  • NULL 和 NOT NULL：分别表示允许空值和不允许空值，在建表时会对字段设定该参数
  • AUTO_INCREMENT：属性值自动增加，初始值为 1，每插入一条记录时基于上一条记录的值 +1
  • PRIMARY KEY：主键，是该表的唯一认证标准，一张表中仅允许有一个主键，值不可重复且不许为空
  • FOREIGN KEY：外键，可以是一列或多列的组合，用于表示两表之间的关系
  • UNIQUE：强制表中一列或多列的唯一性，即输入表中的记录中某列或多列的值不会重复，可以为空但不允许多行连续为空，可以有多个 UNIQUE 约束
  • DEFAULT：即当没有为列指定值时，使用默认值进行自动补充
  • CHECK：对输入的值进行检查，满足条件才有效，否则不允许将值插入到表中，一个 CHECK 约束可用于多列，一列也可以有多个 CHECK 约束

2. MySQL 安装与基本配置

2.1 在 Windows 上安装和配置 MySQL

略

2.2 在 Linux 上安装和配置 MySQL

2.2.1 安装 MySQL

# 1. 获取 mysql 软件包并解压
[root@mysql ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-1.el7.x86_64.rpm-bundle.tar
[root@mysql ~]# mkdir mysqlpack
[root@mysql ~]# tar -xvf mysql-5.7.44-1.el7.x86_64.rpm-bundle.tar -C ./mysqlpack/

# 2. 导入 mysql 软件包
[root@mysql ~]# cd mysqlpack/
[root@mysql mysqlpack]# rpm -ivh \
mysql-community-common-5.7.44-1.el7.x86_64.rpm \
mysql-community-libs-5.7.44-1.el7.x86_64.rpm \
mysql-community-client-5.7.44-1.el7.x86_64.rpm \
mysql-community-server-5.7.44-1.el7.x86_64.rpm

# 3. 更新并安装 mysql
[root@mysql ~]# yum update -y
[root@mysql ~]# yum install -y mysql-community-server

[!NOTE]

  • 如遇到诸如 file /usr/share/mysql/charsets/hp8.xml from install of mysql-community-common-5.7.44-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.68-1.el7.x86_64 的报错，需要使用命令 rpm -e --nodeps 旧软件包名 如 rpm -e --nodeps mariadb-libs 清理旧版本
  • 如遇到诸如 错误：依赖检测失败：net-tools 被 mysql-community-server-5.7.44-1.el7.x86_64 需要 的报错，则需要手动安装依赖软件包，如 yum install net-tools -y

2.2.2 初始化 MySQL

# 1. 将 mysql 目录授予 mysql 用户所有权
[root@mysql ~]# chown -R mysql:mysql /var/lib/mysql/

# 2. 启动并启用 mysql
[root@mysql ~]# systemctl enable mysqld --now

# 3. 初始化 mysql
[root@mysql ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: # 输入 root 密码，MySQL 5.7 及以上版本需使用命令 grep 'temporary password' /var/log/mysqld.log 查看临时密码

The existing password for the user account root has expired. Please set a new password.

New password: # 设置新密码，要求包含：大小写字母、数字、特殊符号

Re-enter new password: # 确认新密码

Change the password for root ? (Press y|Y for Yes, any other key for No) : # 是否修改 root 密码，输入 y/Y 确认，其它任意键取消

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y  # 是否删除匿名用户，输入 y/Y 确认，其它任意键取消
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y  # 是否禁用 root 远程登录，输入 y/Y 确认，其它任意键取消
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y  # 是否删除测试数据库，输入 y/Y 确认，其它任意键取消
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y  # 是否立刻重新加载配置，输入 y/Y 确认，其它任意键取消
Success.

All done!

[!NOTE]

如果觉得严格密码策略不便于学习，可以在 /etc/my.cnf 配置文件中进行以下修改

[mysqld]
validate_password_policy=LOW # 设置密码验证强度等级为 LOW
validate_password_length=4 # 设置密码验证长度为 4 位

完成后使用命令 systemctl restart mysqld 重启服务，在 mysql 命令行使用命令 SHOW VARIABLES LIKE 'validate_password%'; 查看修改是否生效，并使用命令 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD'; 修改 root 密码

2.3 图形化数据库管理管理工具

市面上有众多图形化数据库管理工具，这里推荐使用 Navicat 或 DBeaver，前者需要购买正版，后者有免费的社区版

使用教程待更新

3. mysql 基本操作

3.1 登录 mysql

使用 mysql 命令登录数据库，语法如下：

mysql [参数]

# 常用操作：
# -h HOSTNAME: 登录到 HOSTNAME 主机的数据库
# -u USERNAME: 使用 USERNAME 用户登录数据库
# -P PORT: 使用 PORT 端口登录数据库
# -p: 交互式输入密码

示例：

[root@mysql ~]# mysql -u root -p
Enter password: # 输入密码

mysql> # Success!

3.2 退出 mysql

在 mysql 命令行中，输入 exit 命令退出数据库，示例：

mysql> exit
Bye
[root@mysql ~]#

3.3 mysql 基本配置

配置 mysql 数据库设置有两种方式，一种是在 mysql 命令行使用命令修改，另一种则是直接修改数据库配置文件

在 Linux 中，mysql 的配置文件默认路径为 /etc/my.cnf

3.3.1 配置允许 root 远程登录

如果不配置 root 远程登录，则会无法使用 Navicat 等图形化工具通过 root 连接至远程 MySQL 主机

解决方案有两个

  • 创建一个数据库账号专用于远程连接的账号，给它有限的权限，这也是生产环境建议使用的方法
  • 设置允许 root 远程登录

这里暂时为了学习方便，介绍第二种方法

  1. 从本地登录到 mysql 中
  2. 确认权限
mysql> USE mysql;
mysql> SELECT User, Host FROM user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost | -- root 仅允许从 localhost 登录
+---------------+-----------+
  1. 授予 root 远程登录的权限
-- 1. 授予权限
-- ! 注：该语句仅适用于 MySQL 5.7 及以下版本
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;

-- 2. 刷新权限
mysql> FLUSH PRIVILEGES;

-- 3. 确认权限
mysql> SELECT User, Host FROM user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| root          | %         | -- root 被允许从任何地址登录
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

3.3.2 设置 mysql 默认字符集

mysql 中与字符集相关的配置有 8 个，查看它们当前设置的命令如下

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

如果当我们创建数据库、表或者其它类似操作时没有指定字符集和排序方式，则会按照默认的设置执行，这有时会导致与我们的预期不符（如我想要数据库使用 utf8mb4 字符集，但默认 latin1）

此时就需要修改部分默认的配置

以下是配置过程和内容，仅供参考

# 1. 打开配置文件
[root@mysql ~]# vim /etc/my.cnf

# 2. 添加配置
[mysqld]
# 服务器级默认字符集
character-set-server = utf8mb4
# 服务器级默认排序规则
collation-server = utf8mb4_general_ci
# 支持索引长度超过767字节（解决utf8mb4索引长度限制问题）
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON

[client]
# 客户端连接时的默认字符集
default-character-set = utf8mb4

[mysql]
# MySQL命令行客户端的默认字符集
default-character-set = utf8mb4

# 3. 重启 mysqld 服务
[root@mysql ~]# systemctl restart mysqld

# 4. 验证配置
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

4. 数据库基本操作

4.1 创建数据库

使用 CREATE DATABASE 命令创建数据库，具体语法如下：

CREATE DATABASE 数据库名 [选项与参数];

/*
 * 常用选项与参数：
 * CHARACTER SET: 设置数据库使用指定字符集，常用参数为 uft8mb4
 * 
 * COLLATE: 设置数据库使用的排序方式，常用参数为 utf8mb4_general_ci
*/

示例：

mysql> CREATE DATABASE test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

4.2 查看数据库

使用命令 SHOW DATABASES; 查看数据库，示例：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.02 sec)

4.3 删除数据库

使用 DROP DATABASE 命令删除数据库，具体语法如下：

DROP DATABASE 数据库名;

示例：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            | -- test_db 存在
+--------------------+
5 rows in set (0.02 sec)

-- 删除 test_db
mysql> DROP DATABASE test_db;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+ -- test_db 消失
4 rows in set (0.00 sec)

4.4 切换数据库

使用 USE 命令切换到数据库中，语法如下：

USE 数据库名;

示例：

mysql> SHOW TABLES;
ERROR 1046 (3D000): No database selected -- 未选中任何数据库时，查看表操作会报错

mysql> USE mysql;
Database changed

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| ...                       |
+---------------------------+

4.5 数据库导入

使用 SOURCE 命令可以将 sql 文件导入数据库，语法如下：

SOURCE 文件路径;

示例：导入一个学生信息管理系统数据库，这个库也是本篇主要用的数据库

  1. 准备文件
[root@mysql ~]# vim edu_db.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `clno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程号',
  `clname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名',
  `mno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '专业号',
  PRIMARY KEY (`clno`) USING BTREE,
  INDEX `fk_class_major`(`mno`) USING BTREE,
  CONSTRAINT `fk_class_major` FOREIGN KEY (`mno`) REFERENCES `major` (`mno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('13012201', '22计算机科学与技术1班', '1301');
INSERT INTO `class` VALUES ('13012202', '22计算机科学与技术2班', '1301');
INSERT INTO `class` VALUES ('13022201', '22软件工程1班', '1302');
INSERT INTO `class` VALUES ('13022202', '22软件工程2班', '1302');
INSERT INTO `class` VALUES ('13022301', '23软件工程1班', '1302');
INSERT INTO `class` VALUES ('13032301', '23计算机网络1班', '1303');

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `cno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程号',
  `cname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名',
  `mno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '专业号',
  PRIMARY KEY (`cno`) USING BTREE,
  INDEX `fk_course_major`(`mno`) USING BTREE,
  CONSTRAINT `fk_course_major` FOREIGN KEY (`mno`) REFERENCES `major` (`mno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('C01', '离散数学', '1301');
INSERT INTO `course` VALUES ('C02', '计算机基础', '1301');
INSERT INTO `course` VALUES ('C03', '操作系统原理', '1301');
INSERT INTO `course` VALUES ('C04', 'C 语言程序设计', '1301');
INSERT INTO `course` VALUES ('C05', 'Windows Server 操作系统', '1301');
INSERT INTO `course` VALUES ('C06', 'CAD 制图', '1301');
INSERT INTO `course` VALUES ('C07', 'C++ 程序设计', '1302');
INSERT INTO `course` VALUES ('C08', 'Java 语言基础', '1302');
INSERT INTO `course` VALUES ('C09', 'J2EE 程序设计', '1302');
INSERT INTO `course` VALUES ('C10', '软件系统设计', '1302');
INSERT INTO `course` VALUES ('C11', 'Linux 操作系统', '1302');
INSERT INTO `course` VALUES ('C12', 'MySQL 数据库', '1302');
INSERT INTO `course` VALUES ('C13', '计算机网络', '1303');
INSERT INTO `course` VALUES ('C14', '网页设计', '1303');
INSERT INTO `course` VALUES ('C15', '图形设计', '1303');
INSERT INTO `course` VALUES ('C16', 'UI 设计', '1303');
INSERT INTO `course` VALUES ('C17', '动态网页制作', '1303');
INSERT INTO `course` VALUES ('C18', '大数据应用', '1303');

-- ----------------------------
-- Table structure for major
-- ----------------------------
DROP TABLE IF EXISTS `major`;
CREATE TABLE `major`  (
  `mno` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '专业号码',
  `mname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '专业名',
  PRIMARY KEY (`mno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of major
-- ----------------------------
INSERT INTO `major` VALUES ('1301', '计算机科学与技术');
INSERT INTO `major` VALUES ('1302', '软件工程');
INSERT INTO `major` VALUES ('1303', '计算机网络');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `sno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学号',
  `cno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程号',
  `peacetime` decimal(5, 1) NULL DEFAULT NULL COMMENT '平时成绩',
  `final` decimal(5, 1) NULL DEFAULT NULL COMMENT '期末成绩',
  `total` decimal(5, 1) GENERATED ALWAYS AS (((`peacetime` * 0.4) + (`final` * 0.6))) VIRTUAL NULL,
  PRIMARY KEY (`sno`, `cno`) USING BTREE,
  INDEX `tk_score_course`(`cno`) USING BTREE,
  CONSTRAINT `tk_score_course` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tk_score_student` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('S1301220101', 'C01', 92.0, 95.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220101', 'C02', 85.0, 78.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220101', 'C03', 88.0, 91.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220102', 'C01', 76.0, 88.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220102', 'C02', 90.0, 83.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220102', 'C03', 84.0, 79.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220103', 'C01', 68.0, 48.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220103', 'C02', 72.0, 66.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220103', 'C03', 60.0, 71.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220104', 'C01', 89.0, 93.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220104', 'C02', 67.0, 75.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220104', 'C03', 81.0, 69.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220105', 'C01', 74.0, 62.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220105', 'C02', 78.0, 81.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220105', 'C03', 69.0, 64.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220106', 'C01', 83.0, 59.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220106', 'C02', 86.0, 88.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220106', 'C03', 77.0, 72.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220201', 'C01', 71.0, 67.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220201', 'C02', 65.0, 72.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220201', 'C03', 79.0, 74.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220202', 'C01', 88.0, 94.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220202', 'C02', 82.0, 86.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220202', 'C03', 91.0, 89.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220203', 'C01', 60.0, 52.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220203', 'C02', 68.0, 70.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220203', 'C03', 73.0, 65.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220204', 'C01', 95.0, 97.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220204', 'C02', 87.0, 90.0, DEFAULT);
INSERT INTO `score` VALUES ('S1301220204', 'C03', 84.0, 88.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220101', 'C07', 90.0, 93.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220101', 'C08', 85.0, 89.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220101', 'C09', 78.0, 82.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220101', 'C10', 80.0, 76.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220102', 'C07', 88.0, 54.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220102', 'C08', 82.0, 87.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220102', 'C09', 75.0, 79.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220102', 'C10', 70.0, 73.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220103', 'C07', 67.0, 72.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220103', 'C08', 72.0, 69.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302220103', 'C09', 74.0, 68.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230101', 'C07', 93.0, 96.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230101', 'C08', 89.0, 92.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230101', 'C11', 86.0, 84.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230102', 'C07', 82.0, 85.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230102', 'C08', 75.0, 80.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230102', 'C11', 78.0, 73.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230103', 'C07', 79.0, 58.0, DEFAULT);
INSERT INTO `score` VALUES ('S1302230103', 'C08', 70.0, 66.0, DEFAULT);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学号',
  `sname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `ssex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '男' COMMENT '性别',
  `sbirthday` date NULL DEFAULT NULL COMMENT '出生年月',
  `sphone` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系方式',
  `clno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '班级号',
  PRIMARY KEY (`sno`) USING BTREE,
  INDEX `fk_student_class`(`clno`) USING BTREE,
  CONSTRAINT `fk_student_class` FOREIGN KEY (`clno`) REFERENCES `class` (`clno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('S1301220101', '张伟', '男', '2003-02-10', '1380000001', '13012201');
INSERT INTO `student` VALUES ('S1301220102', '李婷婷', '女', '2003-05-12', '1380000002', '13012201');
INSERT INTO `student` VALUES ('S1301220103', '王子涵', '男', '2003-08-18', '1380000003', '13012201');
INSERT INTO `student` VALUES ('S1301220104', '赵伟', '男', '2003-11-22', '1380000004', '13012201');
INSERT INTO `student` VALUES ('S1301220105', '孙丽丽', '女', '2003-03-15', '1380000005', '13012201');
INSERT INTO `student` VALUES ('S1301220106', '周六', '男', '2003-06-09', '1380000006', '13012201');
INSERT INTO `student` VALUES ('S1301220107', '吴怡', '女', '2003-09-01', '1380000007', '13012201');
INSERT INTO `student` VALUES ('S1301220108', '陈广飞', '男', '2003-12-12', '1380000008', '13012201');
INSERT INTO `student` VALUES ('S1301220109', '王静文', '女', '2003-04-20', '1380000009', '13012201');
INSERT INTO `student` VALUES ('S1301220110', '李强', '男', '2003-07-07', '1380000010', '13012201');
INSERT INTO `student` VALUES ('S1301220111', '陈佳怡', '女', '2003-10-10', '1380000011', '13012201');
INSERT INTO `student` VALUES ('S1301220112', '刘鹏', '男', '2003-01-01', '1380000012', '13012201');
INSERT INTO `student` VALUES ('S1301220201', '陈涛', '男', '2003-03-03', '1380000013', '13012202');
INSERT INTO `student` VALUES ('S1301220202', '李娜', '女', '2003-06-06', '1380000014', '13012202');
INSERT INTO `student` VALUES ('S1301220203', '王刚', '男', '2003-09-09', '1380000015', '13012202');
INSERT INTO `student` VALUES ('S1301220204', '赵思雨', '女', '2003-12-12', '1380000016', '13012202');
INSERT INTO `student` VALUES ('S1301220205', '孙强', '男', '2003-02-02', '1380000017', '13012202');
INSERT INTO `student` VALUES ('S1301220206', '周敏', '女', '2003-05-05', '1380000018', '13012202');
INSERT INTO `student` VALUES ('S1301220207', '刘文轩', '男', '2003-08-08', '1380000019', '13012202');
INSERT INTO `student` VALUES ('S1301220208', '郑子龙', '男', '2003-11-11', '1380000020', '13012202');
INSERT INTO `student` VALUES ('S1301220209', '王诗雨', '女', '2003-04-04', '1380000021', '13012202');
INSERT INTO `student` VALUES ('S1301220210', '李文武', '男', '2003-07-07', '1380000022', '13012202');
INSERT INTO `student` VALUES ('S1302220101', '蒋东宇', '男', '2004-02-01', '1380000023', '13022201');
INSERT INTO `student` VALUES ('S1302220102', '刘娜', '女', '2004-05-03', '1380000024', '13022201');
INSERT INTO `student` VALUES ('S1302220103', '夏刚', '男', '2004-08-05', '1380000025', '13022201');
INSERT INTO `student` VALUES ('S1302220104', '王静怡', '女', '2004-11-07', '1380000026', '13022201');
INSERT INTO `student` VALUES ('S1302220105', '刘大力', '男', '2004-03-09', '1380000027', '13022201');
INSERT INTO `student` VALUES ('S1302220106', '王桂兰', '女', '2004-06-11', '1380000028', '13022201');
INSERT INTO `student` VALUES ('S1302220107', '孙文涛', '男', '2004-09-13', '1380000029', '13022201');
INSERT INTO `student` VALUES ('S1302220108', '丁思蕊', '女', '2004-12-15', '1380000030', '13022201');
INSERT INTO `student` VALUES ('S1302220109', '赵家辉', '男', '2004-04-17', '1380000031', '13022201');
INSERT INTO `student` VALUES ('S1302220110', '吕雨辰', '女', '2004-07-19', '1380000032', '13022201');
INSERT INTO `student` VALUES ('S1302230101', '周天宇', '男', '2002-03-10', '1380000033', '13022301');
INSERT INTO `student` VALUES ('S1302230102', '杨圆圆', '女', '2002-06-15', '1380000034', '13022301');
INSERT INTO `student` VALUES ('S1302230103', '张鹏', '男', '2002-09-20', '1380000035', '13022301');
INSERT INTO `student` VALUES ('S1302230104', '王磊', '男', '2002-12-25', '1380000036', '13022301');
INSERT INTO `student` VALUES ('S1302230105', '刘雨桐', '女', '2002-04-18', '1380000037', '13022301');
INSERT INTO `student` VALUES ('S1302230106', '张伟', '男', '2002-07-22', '1380000038', '13022301');
INSERT INTO `student` VALUES ('S1302230107', '赵丽', '女', '2002-10-30', '1380000039', '13022301');
INSERT INTO `student` VALUES ('S1302230108', '陈玉杰', '男', '2002-01-12', '1380000040', '13022301');
INSERT INTO `student` VALUES ('S1303230101', '张三', '男', '2003-08-20', '1380000041', '13032301');
INSERT INTO `student` VALUES ('S1303230102', '李四', '男', '2003-11-25', '1380000042', '13032301');
INSERT INTO `student` VALUES ('S1303230103', '王五', '男', '2003-09-03', '1380000043', '13032301');
INSERT INTO `student` VALUES ('S1303230104', '赵六', '男', '2004-01-15', '1380000044', '13032301');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `tno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师号',
  `tname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师姓名',
  `tsex` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '男' COMMENT '教师性别',
  `tbirthday` date NULL DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`tno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('T01', '张伟', '男', '1980-03-15');
INSERT INTO `teacher` VALUES ('T02', '李红梅', '女', '1985-07-22');
INSERT INTO `teacher` VALUES ('T03', '王强', '男', '1978-11-09');
INSERT INTO `teacher` VALUES ('T04', '刘小洋', '男', '1990-01-18');
INSERT INTO `teacher` VALUES ('T05', '王娅', '女', '1987-05-30');
INSERT INTO `teacher` VALUES ('T06', '赵磊', '男', '1982-09-12');
INSERT INTO `teacher` VALUES ('T07', '孙丽丽', '女', '1992-06-25');
INSERT INTO `teacher` VALUES ('T08', '高建国', '男', '1975-12-03');
INSERT INTO `teacher` VALUES ('T09', '吴小敏', '女', '1988-04-17');
INSERT INTO `teacher` VALUES ('T10', '孙涛', '男', '1993-10-08');
INSERT INTO `teacher` VALUES ('T11', '陈岩', '男', '1985-04-29');

-- ----------------------------
-- Table structure for teaplan
-- ----------------------------
DROP TABLE IF EXISTS `teaplan`;
CREATE TABLE `teaplan`  (
  `tpno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教学计划编号',
  `tno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师号',
  `clno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '班级号',
  `cno` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程号',
  `state` tinyint(4) NULL DEFAULT 0 COMMENT '授课进度，0=未开课，1=授课中，2=结课',
  PRIMARY KEY (`tpno`) USING BTREE,
  INDEX `fk_teaplan_teacher`(`tno`) USING BTREE,
  INDEX `fk_teaplan_class`(`clno`) USING BTREE,
  INDEX `fk_teaplan_course`(`cno`) USING BTREE,
  CONSTRAINT `fk_teaplan_class` FOREIGN KEY (`clno`) REFERENCES `class` (`clno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_teaplan_course` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_teaplan_teacher` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of teaplan
-- ----------------------------
INSERT INTO `teaplan` VALUES ('1', 'T01', '13012201', 'C01', 2);
INSERT INTO `teaplan` VALUES ('10', 'T06', '13022201', 'C07', 2);
INSERT INTO `teaplan` VALUES ('11', 'T07', '13022201', 'C08', 2);
INSERT INTO `teaplan` VALUES ('12', 'T08', '13022201', 'C09', 2);
INSERT INTO `teaplan` VALUES ('13', 'T09', '13022201', 'C10', 2);
INSERT INTO `teaplan` VALUES ('14', 'T10', '13022201', 'C11', 2);
INSERT INTO `teaplan` VALUES ('15', 'T06', '13022202', 'C07', 2);
INSERT INTO `teaplan` VALUES ('16', 'T07', '13022202', 'C08', 2);
INSERT INTO `teaplan` VALUES ('17', 'T08', '13022202', 'C09', 2);
INSERT INTO `teaplan` VALUES ('18', 'T09', '13022202', 'C10', 2);
INSERT INTO `teaplan` VALUES ('19', 'T06', '13022301', 'C07', 2);
INSERT INTO `teaplan` VALUES ('2', 'T02', '13012201', 'C02', 2);
INSERT INTO `teaplan` VALUES ('20', 'T07', '13022301', 'C08', 2);
INSERT INTO `teaplan` VALUES ('21', 'T08', '13022301', 'C11', 2);
INSERT INTO `teaplan` VALUES ('22', 'T09', '13022301', 'C12', 2);
INSERT INTO `teaplan` VALUES ('23', 'T10', '13032301', 'C13', 0);
INSERT INTO `teaplan` VALUES ('24', 'T11', '13032301', 'C14', 0);
INSERT INTO `teaplan` VALUES ('25', 'T01', '13032301', 'C15', 0);
INSERT INTO `teaplan` VALUES ('26', 'T02', '13032301', 'C16', 0);
INSERT INTO `teaplan` VALUES ('27', 'T03', '13032301', 'C17', 0);
INSERT INTO `teaplan` VALUES ('3', 'T03', '13012201', 'C03', 2);
INSERT INTO `teaplan` VALUES ('4', 'T04', '13012201', 'C04', 2);
INSERT INTO `teaplan` VALUES ('5', 'T05', '13012201', 'C05', 2);
INSERT INTO `teaplan` VALUES ('6', 'T01', '13012202', 'C01', 2);
INSERT INTO `teaplan` VALUES ('7', 'T02', '13012202', 'C02', 2);
INSERT INTO `teaplan` VALUES ('8', 'T03', '13012202', 'C03', 2);
INSERT INTO `teaplan` VALUES ('9', 'T04', '13012202', 'C04', 2);

SET FOREIGN_KEY_CHECKS = 1;
  1. 登入数据库并执行导入命令，导入前请先确保数据库已创建
[root@mysql ~]# pwd
/root
[root@mysql ~]# mysql -u root -p
mysql> CREATE DATABASE edu_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
mysql> use edu_db;
mysql> SOURCE /root/edu_db.sql

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| edu_db             | -- <== HERE
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+

mysql> USE edu_db;
mysql> SHOW TABLES;
+------------------+
| Tables_in_edu_db |
+------------------+
| class            |
| course           |
| major            |
| score            |
| student          |
| teacher          |
| teaplan          |
+------------------+

4.6 数据库导出

导出数据库一般在 Linux 命令行中使用 mysqldump 命令执行，语法如下：

mysqldump -u 用户名 -p -h 数据库主机名 数据库名 > 数据库文件名.sql

# 示例：以 root 身份导出本地主机的 edu_db 数据库到 /root/edu_db.sql
[root@mysql ~]# mysqldump -u root -p edu_db > /root/edu_db.sql
Enter password:
[root@mysql ~]# ls /root/edu_db.sql
/root/edu_db.sql

5. 数据表基本操作

5.1 表操作

5.1.1 查看当前库中的数据表

使用命令 SHOW TABLES; 可以查看当前库中有哪些数据表，示例：

mysql> SHOW TABLES;
+------------------+
| Tables_in_edu_db |
+------------------+
| class            |
| course           |
| major            |
| score            |
| student          |
| teacher          |
| teaplan          |
+------------------+
7 rows in set (0.00 sec)

5.1.2 查看表结构

使用命令 DESC 表名; 或 DESCRIBE 表名; 可以查看数据表的结构，示例：

# 查询数据表 class 的结构
mysql> DESC class;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| clno   | char(20)    | NO   | PRI | NULL    |       |
| clname | varchar(30) | NO   |     | NULL    |       |
| mno    | char(20)    | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+

5.2 单表查询

数据库使用 SELECT 语句实现查询信息的功能

5.2.1 基本查询

查询语句的基本语法如下：

-- 查询所有信息
SELECT * FROM 表名;

-- 指定具体字段查询
SELECT 字段名1, 字段名2, ... FROM 表名;

-- 查询并以别名输出
SELECT 字段名1 AS '别名1', 字段名2 AS '别名2', ... FROM 表名; -- AS 可省略

示例：

-- 1. 查询专业信息表的所有信息
mysql> SELECT * FROM major;
+------+--------------------------+
| mno  | mname                    |
+------+--------------------------+
| 1301 | 计算机科学与技术         |
| 1302 | 软件工程                 |
| 1303 | 计算机网络               |
+------+--------------------------+

-- 2. 从班级表中查询班级号和班级名
mysql> SELECT clno, clname FROM class;
+----------+--------------------------------+
| clno     | clname                         |
+----------+--------------------------------+
| 13012201 | 22计算机科学与技术1班          |
| 13012202 | 22计算机科学与技术2班          |
| 13022201 | 22软件工程1班                  |
| 13022202 | 22软件工程2班                  |
| 13022301 | 23软件工程1班                  |
| 13032301 | 23计算机网络1班                |
+----------+--------------------------------+

-- 3. 从班级表中查询班级号和班级名，并使用中文别名输出
mysql> SELECT clno AS '班级号', clname AS '班级名' FROM class;
+-----------+--------------------------------+
| 班级号    | 班级名                         |
+-----------+--------------------------------+
| 13012201  | 22计算机科学与技术1班          |
| 13012202  | 22计算机科学与技术2班          |
| 13022201  | 22软件工程1班                  |
| 13022202  | 22软件工程2班                  |
| 13022301  | 23软件工程1班                  |
| 13032301  | 23计算机网络1班                |
+-----------+--------------------------------+

5.2.2 查询并格式化输出

在查询时，可以使用 CONCAT() 函数实现自定义输出的格式，这个函数的主要作用是拼接字符串，语法如下：

SELECT CONCAT("字符串1", 字段名1, "字符串2", 字段名2, ...) FROM 表名;

示例：

-- 查询并输出：学生 XXX，学号 XXX，性别 XXX
mysql> SELECT CONCAT("学生", sname, "，学号", sno, "，性别", ssex) AS "学生信息" FROM student;
+-------------------------------------------------+
| 学生信息                                        |
+-------------------------------------------------+
| 学生张伟，学号S1301220101，性别男               |
| 学生李婷婷，学号S1301220102，性别女             |
| 学生王子涵，学号S1301220103，性别男             |
| ...                                           |
+-------------------------------------------------+

5.2.3 查询并预处理数据

在查询时可以直接对数据进行预处理，包括且不限于：算术运算、数值替换等操作

1. 算术运算

在查询语句中，可以添加算术运算符组成表达式，对数据进行处理

示例：

-- 手动计算学生的总评成绩
-- 总评成绩 = peacetime * 0.4 + final * 0.6
mysql> SELECT sno AS "学号", cno AS "课程号", (peacetime * 0.4 + final * 0.6) AS "总评成绩" FROM score;
+-------------+-----------+--------------+
| 学号        | 课程号    | 总评成绩     |
+-------------+-----------+--------------+
| S1301220101 | C01       |        86.80 |
| S1301220101 | C02       |        81.20 |
| S1301220101 | C03       |        76.20 |
| S1301220102 | C01       |        91.20 |
| ...         | ...       |          ... |
+-------------+-----------+--------------+
2. 空值替换

在查询语句中，可以通过添加IFNULL()函数对空值数据进行处理，语法如下：

IFNULL(字段名, 替换值)

示例：

-- 手动计算学生的总评成绩，空值使用 0 替换
mysql> SELECT sno AS "学号", cno AS "课程号", IFNULL((peacetime * 0.4 + final * 0.6), 0) AS "总评成绩" FROM score;
+-------------+-----------+--------------+
| 学号        | 课程号    | 总评成绩     |
+-------------+-----------+--------------+
| S1301220101 | C01       |        86.80 |
| S1301220101 | C02       |        81.20 |
| S1301220101 | C03       |        76.20 |
| ...         | ...       |          ... |
| S1303230101 | C14       |         0.00 | -- 原文为 NULL
| S1303230101 | C15       |         0.00 | -- 原文为 NULL
| S1303230101 | C16       |         0.00 | -- 原文为 NULL
| ...         | ...       |          ... |
+-------------+-----------+--------------+
3. 过滤重复值

在查询语句中添加 DISTINCT 关键字可以过滤掉重复的结果

示例：

-- 查询成绩表有记录的学生学号
mysql> SELECT DISTINCT sno AS "学号" FROM score;
+-------------+
| 学号        |
+-------------+
| S1301220101 |
| S1301220102 |
| S1301220103 |
| S1301220104 |
| ...         |
+-------------+

5.2.4 带条件查询

在查询数据时，可以使用 WHERE 子句设定查询条件，大致语法如下：

SELECT 字段信息 FROM 表名 WHERE 条件;

条件信息的表示方法有很多种，一下是一些例子：

运算符类型运算符用途 / 含义示例代码说明
比较运算符=等于id = 5不可用于 NULL 比较
>大于score > 60
<小于score < 60
>=大于等于score >= 60
<=小于等于score <= 60
!= 或 <>不等于id != 5 或 id <> 5不可用于 NULL 比较
空判断运算符IS NULL为空address IS NULL不可搭配比较运算符
IS NOT NULL不为空address IS NOT NULL不可搭配比较运算符
逻辑运算符AND与运算job = 'salesman' AND sal > 5000优先级高于 OR
OR或运算job = 'manager' OR sal > 5000
NOT非运算NOT(sal > 5000)逻辑取反
范围运算符BETWEEN AND在某个范围之间score BETWEEN 80 AND 100
IN在某个指定数据列表内id IN (10, 11, 12, 13)
NOT IN不在某个指定数据列表内id NOT IN (10, 11, 12, 13)
模糊查询运算符LIKE模糊查询name LIKE '张%'需要搭配通配符使用，如 % 匹配任意长度字符，_ 匹配单个字符

示例：

-- 1. 查询 13012301 班级的学生信息
mysql> SELECT * FROM student WHERE clno = "13012201";
+-------------+-----------+------+------------+------------+----------+
| sno         | sname     | ssex | sbirthday  | sphone     | clno     |
+-------------+-----------+------+------------+------------+----------+
| S1301220101 | 张伟      | 男   | 2003-02-10 | 1380000001 | 13012201 |
| S1301220102 | 李婷婷    | 女   | 2003-05-12 | 1380000002 | 13012201 |
| S1301220103 | 王子涵    | 男   | 2003-08-18 | 1380000003 | 13012201 |
| ...         | ...       | ...  | ...        | ...        | ...      |
+-------------+-----------+------+------------+------------+----------+

-- 2. 查询期末成绩小于 60 的学生学号和成绩
mysql> SELECT sno, final FROM score WHERE final < 60;
+-------------+-------+
| sno         | final |
+-------------+-------+
| S1301220103 |  48.0 |
| S1301220106 |  59.0 |
| S1301220203 |  52.0 |
| S1302220102 |  54.0 |
| S1302230103 |  58.0 |
+-------------+-------+

-- 3. 查询总评成绩在 70-80 的学生学号和成绩
mysql> SELECT sno, total FROM score WHERE total BETWEEN 70 AND 80;
+-------------+-------+
| sno         | total |
+-------------+-------+
| S1301220104 |  71.8 |
| S1301220104 |  73.8 |
| S1301220105 |  79.8 |
| ...         |   ... |
+-------------+-------+

-- 4. 查询 13012201 班级中王姓同学的学生信息
mysql> SELECT * FROM student WHERE sname LIKE "王%" AND clno = "13012201";
+-------------+-----------+------+------------+------------+----------+
| sno         | sname     | ssex | sbirthday  | sphone     | clno     |
+-------------+-----------+------+------------+------------+----------+
| S1301220103 | 王子涵    | 男   | 2003-08-18 | 1380000003 | 13012201 |
| S1301220109 | 王静文    | 女   | 2003-04-20 | 1380000009 | 13012201 |
+-------------+-----------+------+------------+------------+----------+

-- 5. 查询考取了 C04, C07, C09 三门课的学生的总评成绩
mysql> SELECT sno, cno, final FROM score WHERE cno IN ("C04", "C07", "C09");
+-------------+-----+-------+
| sno         | cno | final |
+-------------+-----+-------+
| S1302220101 | C07 |  93.0 |
| S1302220101 | C09 |  82.0 |
| S1302220102 | C07 |  54.0 |
| ...         | ... |   ... |
+-------------+-----+-------+

5.2.5 查询并排序

在数据库中，可以在查询语句末尾使用 ORDER BY 关键字设定排序方式，参数 ASC 和 DESC 分别表示升序和降序，默认为升序，语法如下：

SELECT ...
FROM ...
WHERE ...
ORDER BY 字段名1 ASC|DESC, 字段名2 ASC|DESC, ...

示例：

-- 将成绩表按总评成绩由高到低排序，若总评成绩相同，则按学号从低到高排序
mysql> SELECT *
    -> FROM score
    -> ORDER BY total DESC, sno ASC;
+-------------+-----+-----------+-------+-------+
| sno         | cno | peacetime | final | total |
+-------------+-----+-----------+-------+-------+
| S1301220204 | C01 |      95.0 |  97.0 |  96.2 |
| S1302230101 | C07 |      93.0 |  96.0 |  94.8 |
| S1301220101 | C01 |      92.0 |  95.0 |  93.8 |
| S1302220101 | C07 |      90.0 |  93.0 |  91.8 |
| ...         | ... |       ... |   ... |   ... |
+-------------+-----+-----------+-------+-------+

5.3 多表查询

顾名思义，多表查询指在多个表中检索并合并查询结果

查询时，可以给表名设置别名以减少 SQL 语句的编辑复杂度

多表查询时，字段名需要通过 表名.字段名 进行精确定位

语法与示例如下：

SELECT 表名1.字段名1 表名2.字段名2, ...
FROM 表名1 别名1, 表名2 别名2, ...
...;

-- 示例
SELECT s.sname, c.cname
FROM Student s, Course c
...;

5.3.1 笛卡尔积多表查询

笛卡尔乘积（Cartesian product）又称直积，是数学中两个集合 X 和 Y 的所有可能有序对组成的集合，表达式为 X×Y ，其中每个有序对的第一个元素来自 X，第二个元素来自 Y

语法：

SELECT ...
FROM 表名1, 表名2, ...
WHERE ...;

示例：

mysql> SELECT s.sname, c.cname
    -> FROM student s, course c
    -> WHERE c.mno = '1301';
+-----------+-----------------------------+
| sname     | cname                       |
+-----------+-----------------------------+
| 张伟      | 离散数学                    |
| 张伟      | 计算机基础                  |
| 张伟      | 操作系统原理                |
| 张伟      | C 语言程序设计              |
| 张伟      | Windows Server 操作系统     |
| 张伟      | CAD 制图                    |
| 李婷婷    | 离散数学                    |
| 李婷婷    | 计算机基础                  |
| 李婷婷    | 操作系统原理                |
| 李婷婷    | C 语言程序设计              |
| 李婷婷    | Windows Server 操作系统     |
| 李婷婷    | CAD 制图                    |
| ...       | ...                         |
+-----------+-----------------------------+

5.3.2 多表连接多表查询

一般情况下直接查询多个表的所有数据，会生成大量无用的冗余数据，而为了获取有效的信息，在多表查询时一般会指定字段信息和查询条件

语法如下：

SELECT 表名.字段名1, 表名.字段名2, ...
FROM 表名1, 表名2, ...
WHERE 表名1.字段名1 = 表名2.字段名2
...;

示例：

-- 1. 生成总评成绩表，包含学生名字和课程名
-- - 指定要查询的字段名
SELECT sc.sno AS "学号", st.sname AS "姓名", sc.cno AS "课程号", c.cname AS "课程名", sc.total AS "总评成绩"
-- - 指定要查询的表
FROM student st, score sc, course c
-- - 指定连接条件
WHERE sc.sno = st.sno AND sc.cno = c.cno;
+-------------+-----------+-----------+--------------------+--------------+
| 学号        | 姓名      | 课程号    | 课程名             | 总评成绩     |
+-------------+-----------+-----------+--------------------+--------------+
| S1301220101 | 张伟      | C01       | 离散数学           |         93.8 |
| S1301220101 | 张伟      | C02       | 计算机基础         |         80.8 |
| S1301220101 | 张伟      | C03       | 操作系统原理       |         89.8 |
| S1301220102 | 李婷婷    | C01       | 离散数学           |         83.2 |
| S1301220102 | 李婷婷    | C02       | 计算机基础         |         85.8 |
| ...         | ...       | ...       | ...                | ...          |
+-------------+-----------+-----------+--------------------+--------------+

-- 2. 查询学生学号、学生姓名、所属班级名、所学课程名、总评成绩、任课教师姓名，并按总评成绩降序排序
mysql> SELECT sc.sno AS "学生学号",
    ->        st.sname AS "学生姓名",
    ->        cl.clname AS "班级",
    ->        c.cname AS "课程",
    ->        t.tname AS "任课教师",
    ->        sc.total AS "总评成绩"
    -> FROM score sc, student st, class cl, course c, teacher t, teaplan tp
    -> WHERE sc.sno = st.sno AND
    ->       sc.cno = c.cno AND
    ->       st.clno = cl.clno AND
    ->       tp.tno = t.tno AND
    ->       tp.cno = c.cno AND
    ->       tp.clno = cl.clno;
+--------------+--------------+--------------------------------+--------------------+--------------+--------------+
| 学生学号     | 学生姓名     | 班级                           | 课程               | 任课教师     | 总评成绩     |
+--------------+--------------+--------------------------------+--------------------+--------------+--------------+
| S1301220101  | 张伟         | 22计算机科学与技术1班          | 离散数学           | 张伟         |         93.8 |
| S1301220101  | 张伟         | 22计算机科学与技术1班          | 计算机基础         | 李红梅       |         80.8 |
| S1301220101  | 张伟         | 22计算机科学与技术1班          | 操作系统原理       | 王强         |         89.8 |
| S1301220102  | 李婷婷       | 22计算机科学与技术1班          | 离散数学           | 张伟         |         83.2 |
| S1301220102  | 李婷婷       | 22计算机科学与技术1班          | 计算机基础         | 李红梅       |         85.8 |
| S1301220102  | 李婷婷       | 22计算机科学与技术1班          | 操作系统原理       | 王强         |         81.0 |
| ...          | ...          | ...                            | ...                | ...          | ...          |
+--------------+--------------+--------------------------------+--------------------+--------------+--------------+

5.4 高级查询

5.4.1 多表连接

按照 SQL99 标准，多表连接的方式分为内连接和外连接两种

1 内连接

内连接通过在 FROM 子句中添加 INNER JOIN ... ON 关键字连接多个表，语法如下：

SELECT ...
FROM 表名1 
    INNER JOIN 表名2 ON 连接条件1
    INNER JOIN 表名3 ON 连接条件2
    ...

-- INNER 可省略

示例：

-- 查询学号 S1301220101 学生的姓名和所考试科目的总评成绩
mysql> SELECT st.sname AS "姓名", c.cname AS "科目", sc.total AS "总评成绩"
    -> FROM score sc
    ->   INNER JOIN student st ON sc.sno = st.sno
    ->   INNER JOIN course c ON sc.cno = c.cno
    -> WHERE sc.sno = "S1301220101";
+--------+--------------------+--------------+
| 姓名   | 科目               | 总评成绩     |
+--------+--------------------+--------------+
| 张伟   | 离散数学           |         93.8 |
| 张伟   | 计算机基础         |         80.8 |
| 张伟   | 操作系统原理       |         89.8 |
+--------+--------------------+--------------+
2 外连接

外连接又分为左连接和右连接，这种连接方式指定了连接方式，保留左表所有记录使用左连接，反之使用右连接

外连接通过在 FROM 子句中添加 LEFT|RIGHT OUTER JOIN ... ON 关键字实现，语法如下：

FROM 表名1
    LEFT|RIGHT OUTER JOIN 表名2 ON 连接条件1
    LEFT|RIGHT OUTER JOIN 表名3 ON 连接条件2

-- OUTER 可省略

示例：

-- 1. 查询总评成绩 > 90 的学生的姓名、班级和科目信息，使用左连接，并按照降序排序
mysql> SELECT st.sname AS "姓名", cl.clname AS "班级", c.cname AS "科目", sc.total AS "总评成绩"
    -> FROM score sc
    ->   LEFT OUTER JOIN student st ON sc.sno = st.sno
    ->   LEFT OUTER JOIN class cl ON st.clno = cl.clno
    ->   LEFT OUTER JOIN course c ON sc.cno = c.cno
    -> AND sc.final > 90
    -> ORDER BY sc.final DESC;
+-----------+--------------------------------+--------------------+--------------+
| 姓名      | 班级                           | 科目               | 总评成绩     |
+-----------+--------------------------------+--------------------+--------------+
| 赵思雨    | 22计算机科学与技术2班          | 离散数学           |         96.2 |
| 周天宇    | 23软件工程1班                  | C++ 程序设计       |         94.8 |
| 张伟      | 22计算机科学与技术1班          | 离散数学           |         93.8 |
| 李娜      | 22计算机科学与技术2班          | 离散数学           |         91.6 |
| 赵伟      | 22计算机科学与技术1班          | 离散数学           |         91.4 |
| 蒋东宇    | 22软件工程1班                  | C++ 程序设计       |         91.8 |
| 周天宇    | 23软件工程1班                  | Java 语言基础      |         90.8 |
| 张伟      | 22计算机科学与技术1班          | 操作系统原理       |         89.8 |
+-----------+--------------------------------+--------------------+--------------+

-- 2. 查询学号 S1301220103 学生目前所需要学习的课程名与任课老师名，使用右连接
mysql> SELECT st.sname AS "姓名", c.cname AS "课程", t.tname AS "任课老师"
    -> FROM teaplan tp
    ->   RIGHT OUTER JOIN class cl ON tp.clno = cl.clno
    ->   RIGHT OUTER JOIN student st ON cl.clno = st.clno
    ->   RIGHT OUTER JOIN teacher t ON tp.tno = t.tno
    ->   RIGHT OUTER JOIN course c ON tp.cno = c.cno
    -> WHERE st.sno = "S1301220103";
+-----------+-----------------------------+--------------+
| 姓名      | 课程                        | 任课老师     |
+-----------+-----------------------------+--------------+
| 王子涵    | 离散数学                    | 张伟         |
| 王子涵    | 计算机基础                  | 李红梅       |
| 王子涵    | 操作系统原理                | 王强         |
| 王子涵    | C 语言程序设计              | 刘小洋       |
| 王子涵    | Windows Server 操作系统     | 王娅         |
+-----------+-----------------------------+--------------+

5.4.2 聚合函数

SQL 语句可以在子句中添加聚合函数对查询的数据进行处理，常用的聚合函数有：

函数名用途
SUM()求和
AVG()求平均数
COUNT()记录总数量
MAX()记录最大值
MIN()记录最小值

示例：

-- 1. 计算学号 S1301220101 学生的总评平均分
mysql> SELECT AVG(total) FROM score WHERE sno="S1301220101";
+------------+
| AVG(total) |
+------------+
|   88.13333 |
+------------+


-- 2. 从 student 表计算 13012202 班级有多少学生
mysql> SELECT COUNT(sno) FROM student WHERE clno="13012202";
+------------+
| COUNT(sno) |
+------------+
|         10 |
+------------+

-- 3. 查询 C03 课程期末考试最高分
mysql> SELECT MAX(final) FROM score WHERE cno="C03";
+------------+
| MAX(final) |
+------------+
|       91.0 |
+------------+

5.4.3 分组统计

SQL 语句可以通过 GROUP BY 和 HAVING 关键字将查询信息组合，语法如下：

GROUP BY 字段
HAVING 组合条件

示例：

-- 查询总评平均分数 > 80 的班级号和班级名
mysql> SELECT cl.clno, cl.clname, AVG(sc.total)
    -> FROM score sc 
    ->   JOIN student st ON sc.sno = st.sno
    ->   JOIN class cl ON st.clno = cl.clno
    -> GROUP BY cl.clno
    -> HAVING AVG(sc.total) > 80;
+----------+--------------------+---------------+
| clno     | clname             | AVG(sc.total) |
+----------+--------------------+---------------+
| 13022301 | 23软件工程1班      |      80.15000 |
+----------+--------------------+---------------+

5.4.4 子查询

在一个查询语句中嵌套一个查询，称为子查询

子查询语句可以出现在任意位置，但必须被 () 包裹

示例：

-- 1. 查询选修了 C01 课程的学生姓名
mysql> SELECT sname
    -> FROM student
    -> WHERE sno IN (
    ->     SELECT sno FROM score WHERE cno = 'C01'
    -> );
+-----------+
| sname     |
+-----------+
| 张伟      |
| 李婷婷    |
| 王子涵    |
| 赵伟      |
| 孙丽丽    |
| 周六      |
| 陈涛      |
| 李娜      |
| 王刚      |
| 赵思雨    |
+-----------+


-- 2. 查询期末成绩大于全校平均水平的学生及分数，按照降序排序
mysql> SELECT st.sname AS "姓名", AVG(sc.total) AS "平均成绩"
    -> FROM score sc
    -> JOIN student st ON sc.sno = st.sno
    -> GROUP BY sc.sno
    -> HAVING AVG(sc.total) > (
    ->     SELECT AVG(total) FROM score
    -> )
    -> ORDER BY AVG(sc.total) DESC;
+-----------+--------------+
| 姓名      | 平均成绩     |
+-----------+--------------+
| 赵思雨    |     90.46667 |
| 周天宇    |     90.13333 |
| 李娜      |     88.60000 |
| 张伟      |     88.13333 |
| 蒋东宇    |     84.30000 |
| 李婷婷    |     83.33333 |
| 赵伟      |     79.00000 |
| 杨圆圆    |     78.93333 |
+-----------+--------------+
# mysql # 学习
Logo
AI编程社区

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

码道 · 寻艺——从零构建“非遗交互式对话平台“的完整实践

avatar AI编程社区

码道：用 Python + FastAPI 快速构建一个学生信息管理接口：从零到 Swagger 在线文档

在日常开发和教学中，我们经常需要一套简单、可控、能快速上手的后端接口来演示"前后端分离"、练习 Restful API 设计，或者作为前端联调的 Mock 服务。传统做法往往是搭一套完整的 Web 工程：配置数据库、编写 DAO、引入 ORM、做请求分发……一套流程下来，光环境搭建就要花去不少时间，对于想快速验证想法、或者刚入门 API 开发的同学来说，显得过于沉重。

avatar AI编程社区

码道：从零构建学生信息管理系统 API：FastAPI 内存版 CRUD 项目全解析

FastAPI 的强大，很大程度上来自 Pydantic 的校验能力。我们写下类型声明，FastAPI 在运行时自动把它变成边界检查器。name：字符串，长度限制 1 到 50；age：整数，限制在 6 到 120 之间（ge大于等于、le小于等于）；gender：字符串，用正则限定只能是"男"“女”“其他”；email：邮箱格式字符串；grade与major：可选字段，代表班级和专业方向。

avatar AI编程社区