MySQL基础

MySQL基础：三建（建库、建表、建约束），四操作（增、删、改、查），数据库的设计。

一、数据库相关概念

1.1 数据库

  • 存储和管理数据的仓库，数据是有组织的进行存储。
  • 数据库英文名是 database，简称DB。

数据库就是将数据存储在硬盘上，可以达到持久化存储的效果。那又是如何解决上述问题的？使用数据库管理系统。

1.2 数据库管理系统

  • 管理数据库的大型软件
  • 英文：database Management System，简称 DBMS

在电脑上安装了数据库管理系统后，就可以通过数据库管理系统创建数据库来存储数据，也可以通过该系统对数据库中的数据进行数据的增删改查相关的操作。我们平时说的MySQL数据库其实是MySQL数据库管理系统。

image-20260509203013096

通过上面的描述，大家应该已经知道了 数据库管理系统 和 数据库 的关系。那么有有哪些常见的数据库管理系统呢？

1.3 常见的数据库管理系统

接下来对上面列举的数据库管理系统进行简单的介绍：

  • Oracle：收费的大型数据库，Oracle 公司的产品
  • MySQL： 开源免费的中小型数据库。后来 Sun公司收购了 MySQL，而 Sun 公司又被 Oracle 收购
  • SQL Server：MicroSoft 公司收费的中型的数据库。C#、.net 等语言常使用
  • PostgreSQL：开源免费中小型的数据库
  • DB2：IBM 公司的大型收费数据库产品
  • SQLite：嵌入式的微型数据库。如：作为 Android 内置数据库
  • MariaDB：开源免费中小型的数据库

我们课程上学习的是MySQL数据库管理系统，PostgreSQL在一些公司也有使用，此时大家肯定会想以后在公司中如果使用我们没有学习过程的PostgreSQL数据库管理系统怎么办？这点大家大可不必担心，如下图所示：

image-20260509203036026

我们可以通过数据库管理系统操作数据库，对数据库中的数据进行增删改查操作，而怎么样让用户跟数据库管理系统打交道呢？就可以通过一门编程语言（SQL）来实现。

1.4 SQL

  • 英文：Structured Query Language，简称 SQL，结构化查询语言。
  • 操作关系型数据库的编程语言。
  • 定义操作所有关系型数据库的统一标准，可以使用SQL操作所有的关系型数据库管理系统，以后工作中如果使用到了其他的数据库管理系统，也同样的使用SQL来操作。

二、MySQL

2.1 MySQL安装

安装环境:Win11 64位
软件版本:MySQL 8.2.0 解压版

2.1.1 下载

https://downloads.mysql.com/archives/community/

点开上面的链接就能看到如下界面：

image-20260509203044346

2.1.2 安装(解压)

下载完成后我们得到的是一个压缩包，将其解压，我们就可以得到MySQL 8.2.0的软件本体了(就是一个文件夹)，我们可以把它放在你想安装的位置。

image-20260509203050544

2.2 MySQL卸载

右键开始菜单，选择命令提示符(管理员)，打开黑框。

  1. 敲入net stop mysql，回车。
net stop mysql

image-20260509203057192

  1. 再敲入mysqld -remove mysql，回车。
mysqld -remove mysql

image-20260509203102687

  1. 最后删除MySQL目录及相关的环境变量。

至此，MySQL卸载完成！

2.3 MySQL数据模型

关系型数据库：关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的 二维表 组成的数据库

如下图，订单信息表 和 客户信息表 都是有行有列二维表我们将这样的称为关系型数据库。

image-20260509203110376

接下来看关系型数据库的优点：

  • 都是使用表结构，格式一致，易于维护。
  • 使用通用的 SQL 语言操作，使用方便，可用于复杂查询。
    • 关系型数据库都可以通过SQL进行操作，所以使用方便。
    • 复杂查询。现在需要查询001号订单数据，我们可以看到该订单是1号客户的订单，而1号订单是李聪这个客户。以后也可以在一张表中进行统计分析等操作。
  • 数据存储在磁盘中，安全。

数据模型：

image-20260509203115629

如上图，我们通过客户端可以通过数据库管理系统创建数据库，在数据库中创建表，在表中添加数据。创建的每一个数据库对应到磁盘上都是一个文件夹。比如可以通过SQL语句创建一个数据库（数据库名称为db1）。

image-20260509203124292

我们可以在数据库安装目录下的data目录下看到多了一个 db1 的文件夹。所以，在MySQL中一个数据库对应到磁盘上的一个文件夹。

而一个数据库下可以创建多张表，我们到MySQL中自带的mysql数据库的文件夹目录下：

image-20260509203129894

而上图中右边的 db.frm 是表文件，db.MYD 是数据文件，通过这两个文件就可以查询到数据展示成二维表的效果。

小结：

  • MySQL中可以创建多个数据库，每个数据库对应到磁盘上的一个文件夹
  • 在每个数据库中可以创建多个表，每张都对应到磁盘上一个 frm 文件
  • 每张表可以存储多条数据，数据会被存储到磁盘中 MYD 文件中

三、SQL概述

了解了数据模型后，接下来我们就学习SQL语句，通过SQL语句对数据库、表、数据进行增删改查操作。

3.1 SQL简介

  • 英文：Structured Query Language，简称 SQL
  • 结构化查询语言，一门操作关系型数据库的编程语言
  • 定义操作所有关系型数据库的统一标准
  • 对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”

3.2 通用语法

  • SQL 语句可以单行或多行书写，以分号结尾。

    image-20260509203136807

    如上，以分号结尾才是一个完整的sql语句。

  • MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

    同样的一条sql语句写成下图的样子，一样可以运行处结果。

    image-20260509203142026

  • 注释

    • 单行注释: -- 或 # (MySQL 特有)

      image-20260509203149806

      image-20260509203154795

      注意：使用-- 添加单行注释时，–后面一定要加空格，而#没有要求。

    • 多行注释: /* */

3.3 SQL分类

  • DDL(Data Definition Language) ： 数据定义语言，用来定义数据库对象：数据库，表，列等。

    DDL简单理解就是用来操作数据库，表等。

  • DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改。

    DML简单理解就对表中数据进行增删改。

  • DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据)

    DQL简单理解就是对数据进行查询操作。从数据库表中查询到我们想要的数据。

  • DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。

  • TCL（Transaction Control Language）事务控制语言，用来管理数据库事务的一组命令。它们确保数据库操作的一致性和完整性。

    主要包括commit：提交事务，永久保存更改；rollback：回滚事务，撤销更改。

注意： 以后我们最常操作的是 DML 和 DQL ，因为我们开发中最常操作的就是数据。

四、DDL：操作数据库

我们先来学习DDL来操作数据库。而操作数据库主要就是对数据库的增删查操作。

4.1 查询

查询所有的数据库

show database;

运行上面语句效果如下：
image-20260509203254174
上述查询到的是的这些数据库是mysql安装好自带的数据库，我们以后不要操作这些数据库。

4.2 创建数据库

create database [if not exists] 数据库名称;

4.3 删除数据库

drop database [if exists] 数据库名称;

4.4 使用数据库

数据库创建好了，要在数据库中创建表，得先明确在哪个数据库中操作，此时就需要使用数据库。

  • 使用数据库
use 数据库名称;
  • 查看当前使用的数据库
select database();

五、DDL：操作表

5.1 查询表

  • 查询当前数据库下所有表名称
show tables;
  • 查询表结构
desc 表名称;

5.2 创建表

  • 创建表
create table 表名 (
	字段名1 数据类型1 [约束],
	字段名2 数据类型2 [约束],
	…
	字段名n 数据类型n [约束]
);

注意：最后一行末尾，不能加逗号

5.3 数据类型

MySQL 支持多种类型，可以分为三类：

  • 数值

    tinyint : 小整数型，占一个字节
int	： 大整数类型，占四个字节
double ： 浮点类型
	使用格式： 字段名 double(总长度,小数点后保留的位数)
	eg ： score double(5, 2)   
 decimal ： 浮点类型
	使用格式： 字段名 double(总长度,小数点后保留的位数)
	eg ： score decimal(10, 5)

  • 日期

    date：日期值。只包含年月日
time：时间值。只包含时分秒
datetime：混合日期和时间值。包含年月日时分秒

  • 字符串

    char ： 定长字符串。
	优点：存储性能高
	缺点：浪费空间
	eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间
varchar ： 变长字符串。
	优点：节约空间
	缺点：存储性能底
	eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间

【案例】需求：设计一张学生表，请注重数据类型、长度的合理性，字段包括：

  1. 编号
  2. 姓名，姓名最长不超过10个汉字
  3. 性别，因为取值只有两种可能，因此最多一个汉字
  4. 生日，取值为年月日
  5. 入学成绩，小数点后保留两位
  6. 邮件地址，最大长度不超过 64
  7. 家庭联系电话，不一定是手机号码，可能会出现 - 等字符
  8. 学生状态（用数字表示，正常、休学、毕业…）

语句设计如下：

create table student (
	id int,
    name varchar(10),
    gender char(1),
    birthday date,
    score double(5,2),
    email varchar(15),
    tel varchar(15),
    status tinyint
);

5.4 删除表

drop table [if exists] 表名;

5.5 修改表

  • 修改表名
alter table 表名 rename to 新的表名;

-- 将表名student修改为stu
alter table student rename to stu;
  • 添加一列
alter table 表名 add 列名 数据类型;

-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);
  • 修改数据类型
alter table 表名 modify 列名 新数据类型;

-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);
  • 修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;

-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);
  • 删除列
alter table 表名 drop 列名;

-- 将stu表中的addr字段 删除
alter table stu drop addr;

六、建约束

6.1 概念

  • 约束是作用于表中列上的规则，用于限制加入表的数据。
    例如：我们可以给id列加约束，让其值不能重复，不能为null值。

  • 约束的存在保证了数据库中数据的正确性、有效性和完整性。

6.2 分类

  • 非空约束： 关键字是 not null
    保证列中所有的数据不能有null值。

  • 唯一约束：关键字是 unique
    保证列中所有数据各不相同。

  • 主键约束： 关键字是 primary key
    主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给每张表添加一个主键列用来唯一标识数据。

  • 检查约束： 关键字是 check
    保证列中的值满足某一条件。
    例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。
    注意：MySQL8以后才支持检查约束。

  • 默认约束： 关键字是 default
    保存数据时，未指定值则采用默认值。
    例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。

  • 外键约束： 关键字是 foreign key
    外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

6.3 非空约束

  • 概念：非空约束用于保证列中所有数据不能有NULL值。

  • 语法

    • 添加约束

      -- 创建表时添加非空约束
create table 表名(
   列名 数据类型 not null,
   …
); 

-- 建完表后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;

    • 删除约束

      alter table 表名 modify 字段名 数据类型;

6.4 唯一约束

  • 概念：唯一约束用于保证列中所有数据各不相同。

  • 语法

    • 添加约束

      -- 创建表时添加唯一约束
create table 表名(
   列名 数据类型 unique [auto_increment],
   -- auto_increment: 当不指定值时自动增长
   …
); 
create table 表名(
   列名 数据类型,
   …
   [constraint] [约束名称] unique(列名)
); 

-- 建完表后添加唯一约束
alter table 表名 modify 字段名 数据类型 unique;

    • 删除约束

      alter table 表名 drop index 字段名;

6.5 主键约束

  • 概念：主键是一行数据的唯一标识，要求非空且唯一
    注意：一张表只能有一个主键

  • 语法

    • 添加约束

      -- 创建表时添加主键约束
create table 表名(
   列名 数据类型 primary key [auto_increment],
   …
); 
create table 表名(
   列名 数据类型,
   [constraint] [约束名称] primary key(列名)
); 

-- 建完表后添加主键约束
alter table 表名 ADD primary key(字段名);

    • 删除约束

      alter table 表名 drop primary key;

6.6 默认约束

  • 概念：保存数据时，未指定值则采用默认值

  • 语法

    • 添加约束

      -- 创建表时添加默认约束
create table 表名(
   列名 数据类型 default 默认值,
   …
); 

-- 建完表后添加默认约束
alter table 表名 alter 列名 set default 默认值;

    • 删除约束

      alter table 表名 alter 列名 drop default;

6.7 约束练习

根据需求，为表添加合适的约束

-- 员工表
create table emp (
	id int,  -- 员工id，主键且自增长
    ename varchar(50), -- 员工姓名，非空且唯一
    joindate date,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0
);

上面一定给出了具体的要求，我们可以根据要求创建这张表，并为每一列添加对应的约束。建表语句如下：

drop table if exists emp;

-- 员工表
create table emp (
  id int primary key, -- 员工id，主键且自增长
  ename varchar(50) not null unique, -- 员工姓名，非空并且唯一
  joindate date not null , -- 入职日期，非空
  salary DOUBLE(7,2) not null , -- 工资，非空
  bonus DOUBLE(7,2) default 0 -- 奖金，如果没有奖金默认为0
);

通过上面语句可以创建带有约束的 emp 表，约束能不能发挥作用呢。接下来我们一一进行验证，先添加一条没有问题的数据

insert into emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);
  • 验证主键约束，非空且唯一
insert into emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

执行结果如下：

image-20260509203318907

从上面的结果可以看到，字段 id 不能为null。那我们重新添加一条数据，如下：

insert into emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

执行结果如下：

image-20260509203323967

从上面结果可以看到，1这个值重复了。所以主键约束是用来限制数据非空且唯一的。那我们再添加一条符合要求的数据

insert into emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

执行结果如下：

image-20260509203329987

  • 验证非空约束
insert into emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

执行结果如下：

image-20260509203335537

从上面结果可以看到，ename 字段的非空约束生效了。

  • 验证唯一约束
insert into emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

执行结果如下：

image-20260509203341522

从上面结果可以看到，ename 字段的唯一约束生效了。

  • 验证默认约束
insert into emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

执行完上面语句后查询表中数据，如下图可以看到王五这条数据的bonus列就有了默认值0。

image-20260509203347259

注意：默认约束只有在不给值时才会采用默认值。如果给了null，那值就是null值。

如下：

insert into emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

执行完上面语句后查询表中数据，如下图可以看到赵六这条数据的bonus列的值是null。

image-20260509203353157

  • 验证自动增长： auto_increment 当列是数字类型 并且唯一约束

重新创建 emp 表，并给id列添加自动增长

-- 员工表
create table emp (
  id int primary key auto_increment, -- 员工id，主键且自增长
  ename varchar(50) not null unique, -- 员工姓名，非空并且唯一
  joindate date not null , -- 入职日期，非空
  salary DOUBLE(7,2) not null , -- 工资，非空
  bonus DOUBLE(7,2) default 0 -- 奖金，如果没有奖金默认为0
);

接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

insert into emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
insert into emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
insert into emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

6.8 外键约束

(1) 概述

外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。

如何理解上面的概念呢？如下图有两张表，员工表和部门表：

image-20260509203359888

员工表中的dep_id字段是部门表的id字段关联，也就是说1号学生张三属于1号部门研发部的员工。现在我要删除1号部门，就会出现错误的数据（员工表中属于1号部门的数据）。而我们上面说的两张表的关系只是我们认为它们有关系，此时需要通过外键让这两张表产生数据库层面的关系，这样你要删除部门表中的1号部门的数据将无法删除。

(2) 语法

  • 添加外键约束
-- 创建表时添加外键约束
create table 表名(
   列名 数据类型,
   …
   [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名) 
); 

-- 建完表后添加外键约束
alter table 表名 ADD constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
  • 删除外键约束
alter table 表名 drop foreign key 外键名称;

(3) 练习

根据上述语法创建员工表和部门表，并添加上外键约束：

-- 删除表
drop table if exists emp;
drop table if exists dept;

-- 部门表
create table dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
create table emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	constraint fk_emp_dept foreign key(dep_id) references dept(id)	
);

添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
insert into emp (NAME, age, dep_id) values 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop foreign key fk_emp_dept;

重新添加外键

alter table emp add constraint fk_emp_dept foreign key(dep_id) references dept(id);

七、DML

DML主要是对数据进行增（insert）删（delete）改（update）操作。

7.1 添加数据

  • 给指定列添加数据
insert into 表名(列名1,列名2,…) values(值1,值2,…);
  • 给全部列添加数据
insert into 表名 values(值1,值2,…);
  • 批量添加数据
insert into 表名(列名1,列名2,…) values(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
insert into 表名 values(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
  • 添加存在自动增长主键的数据
-- 方式①
insert into 表名 values(default/null, ...); -- 自动增长列对应位置写default或null

-- 方式②
insert into 表名(列名) values(...);    -- 提供非自动增长列的列名

7.2 修改数据

update 表名 set 列名1=值1,列名2=值2,… [where 条件] ;

注意： 修改语句中如果不加条件，则将所有数据都修改！

7.3 删除数据

方式 delete TRANCATE
语法 delete from 表名 [where 条件];删除部分或全部数据 TRANCATE table 表名;清空表，删除全部数据
性能 一条一条删，慢 截断，快
外键 若子表有对应记录，默认会阻止删除 无法用于有外键引用的主表
自动增长 不重置自增计数器 自动重置为初始值

八、DQL

8.1 select语句与关系代数之间的关系

select A1, A2, ... , An
from R1, R2, ... , Rm
where P;

在关系代数中等价于π A1, A2, … ,An (σP (R1×R2×… ×Rm) )
① select子句：对应关系代数中的投影π运算，用于列出查询结果的各属性。
② from子句：对应关系代数中的广义笛卡尔积，用于列出被查询的关系——表或视图。
③ where子句：对应关系代数中的选择谓词σ，用于指出连接、选择等运算要满足的查询条件。

8.2 select语句的一般格式以及子句评估顺序

(1) select语句的一般格式

select [all| distinct] {* | <字段名或表达式1> [别名1] [, <字段名或表达式2> [别名2]]...}
from <表名或视图名> [表别名1] [,<表名或视图名> [别名2] ]…]
[where <条件表达式>]
[group by <字段名1> [, <字段名2>...]
[having <条件表达式>]
[order by <列名> [asc| desc]]
[limit  起始索引 , 查询条目数]
  • select子句：投影列。其中distinct表示去重。
  • from子句： 导入表或视图。源表、虚拟表（select语句后的表）、视图。
  • where子句：对行筛选。
  • group子句：分组。
  • having子句：对组筛选。
  • order子句：排序。默认为升序，desc表示降序。
  • limit子句：分页查询，起始索引是从0开始。

(2) select语句中各子句的评估顺序
from----->where----->group by----->having----->select----->order by----->limit

  • 在上述select语句中，from、where、group by和having子句被称为表表达式。当处理select语句时，这一部分总是首先被评估。
  • 表表达式中的每个子句按照次序被依次评估，评估的结果是一个虚拟表，用于以后的评估。也就是说，前一个子句的评估结果将用于下一个子句，直到表表达式的每一个子句都被评估完为止。
  • where 和 having 区别：
    • 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
    • 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。

8.3 单表查询

8.3.1 基础查询
  • 查询多个字段
select 字段列表 from 表名;
select * from 表名; -- 查询所有数据
  • 去除重复记录
select distinct 字段列表 from 表名;
  • 起别名
as: as 也可以省略
8.3.2 条件查询
  • 语法
select 字段列表 from 表名 where 条件列表;

  • 条件
    条件列表可以使用以下运算符：
    image-20260509203417550

  • 模糊查询
    模糊查询使用like关键字，可以使用通配符进行占位:

    • _ : 代表单个任意字符
    • % : 代表任意个数字符
8.3.3 排序查询
select 字段列表 from 表名 order by 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

上述语句中的排序方式有两种，分别是：

  • asc ： 升序排列 （默认值）
  • desc ： 降序排列

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序。

8.3.4 聚合函数

将一列数据作为一个整体，进行纵向计算。常用的聚合函数如下：

函数名 功能
count(列名) 统计数量（一般选用不为null的列）
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值

注意：null 值不参与所有聚合函数运算。

8.3.5 分组查询
select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义。

【示例】

  • 查询男同学和女同学各自的数学平均分

    select sex, avg(math) from stu group by sex;

    注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

    select name, sex, avg(math) from stu group by sex;  -- 这里查询name字段就没有任何意义

  • 查询男同学和女同学各自的数学平均分，以及各自人数

    select sex, avg(math),count(*) from stu group by sex;

  • 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组

    select sex, avg(math),count(*) from stu where math > 70 group by sex;

  • 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的

    select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;
8.3.6 分页查询
select 字段列表 from 表名 limit  起始索引, 查询条目数;

注意：上述语句中的起始索引是从0开始。

【示例】

  • 从0开始查询，查询3条数据

    select * from stu limit 0 , 3;

  • 每页显示3条数据，查询第1页数据

    select * from stu limit 0 , 3;

  • 每页显示3条数据，查询第2页数据

    select * from stu limit 3 , 3;

  • 每页显示3条数据，查询第3页数据

    select * from stu limit 6 , 3;

从上面的练习推导出起始索引计算公式：

起始索引 = (当前页码 - 1) * 每页显示的条数

8.4 多表查询

多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。我们通过具体的sql给他们演示，先准备环境

drop table if exists emp;
drop table if exists dept;


# 创建部门表
	create table dept(
        did int primary key auto_increment,
        dname varchar(20)
    );

	# 创建员工表
	create table emp (
        id int primary key auto_increment,
        NAME varchar(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date date, -- 入职日期
        dep_id int,
        foreign key (dep_id) references dept(did) -- 外键，关联部门表(部门表的主键)
    );
	-- 添加部门数据
	insert into dept (dNAME) values ('研发部'),('市场部'),('财务部'),('销售部');
	-- 添加员工数据
	insert into emp(NAME,gender,salary,join_date,dep_id) values
	('孙悟空','男',7200,'2013-02-24',1),
	('猪八戒','男',3600,'2010-12-02',2),
	('唐僧','男',9000,'2008-08-08',2),
	('白骨精','女',5000,'2015-10-07',3),
	('蜘蛛精','女',4500,'2011-03-14',1),
	('小白龙','男',2500,'2011-02-14',null);

执行下面的多表查询语句

select * from emp , dept;  -- 从emp和dept表中查询所有的字段数据

结果如下：

image-20260509203436849

从上面的结果我们看到有一些无效的数据，如 孙悟空 这个员工属于1号部门，但也同时关联的2、3、4号部门。所以我们要通过限制员工表中的 dep_id 字段的值和部门表 did 字段的值相等来消除这些无效的数据，

select * from emp , dept where emp.dep_id = dept.did;

执行后结果如下：

image-20260509203443116

上面语句就是连接查询，那么多表查询都有哪些呢？

  • 连接查询

    image-20260509203448350

    • 内连接查询 ：相当于查询AB交集数据
    • 外连接查询
      • 左外连接查询 ：相当于查询A表所有数据和交集部门数据
      • 右外连接查询 ： 相当于查询B表所有数据和交集部分数据

  • 子查询

8.4.1 内连接查询
  • 语法
-- 隐式内连接
select 字段列表 from 表1,表2… where 条件;

-- 显示内连接
select 字段列表 from 表1 
[inner] join 表2 on 条件1
[inner] join 表3 on 条件2...;

内连接相当于查询 A B 交集数据

image-20260509203455130

  • 案例

    • 隐式内连接

      select
	*
from
	emp,
	dept
where
	emp.dep_id = dept.did;

      执行上述语句结果如下：

      image-20260509203459940

    • 查询 emp的 name， gender，dept表的dname

      select
	emp. NAME,
	emp.gender,
	dept.dname
from
	emp,
	dept
where
	emp.dep_id = dept.did;

      执行语句结果如下：

      image-20260509203505955

      上面语句中使用表名指定字段所属有点麻烦，sql也支持给表指别名，上述语句可以改进为

      select
	t1. NAME,
	t1.gender,
	t2.dname
from
	emp t1,
	dept t2
where
	t1.dep_id = t2.did;

    • 显式内连接

      select * from emp inner join dept on emp.dep_id = dept.did;
-- 上面语句中的inner可以省略，可以书写为如下语句
select * from emp join dept on emp.dep_id = dept.did;

      执行结果如下：

      image-20260509203511110

8.4.2 外连接查询

  • 语法

    -- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;

-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

    左外连接：相当于查询A表所有数据和交集部分数据

    右外连接：相当于查询B表所有数据和交集部分数据

    image-20260509203517352

  • 案例

    • 查询emp表所有数据和对应的部门信息（左外连接）

      select * from emp left join dept on emp.dep_id = dept.did;

      执行语句结果如下：

      image-20260509203523029

      结果显示查询到了左表（emp）中所有的数据及两张表能关联的数据。

    • 查询dept表所有数据和对应的员工信息（右外连接）

      select * from emp right join dept on emp.dep_id = dept.did;

      执行语句结果如下：

      image-20260509203529707

      结果显示查询到了右表（dept）中所有的数据及两张表能关联的数据。

      要查询出部门表中所有的数据，也可以通过左外连接实现，只需要将两个表的位置进行互换：

      select * from dept left join emp on emp.dep_id = dept.did;
8.4.3 子查询

  • 概念

    查询中嵌套查询，称嵌套查询为子查询。

    什么是查询中嵌套查询呢？我们通过一个例子来看：

    需求：查询工资高于猪八戒的员工信息。

    来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

    select salary from emp where name = '猪八戒'

    第二步：查询工资高于猪八戒的员工信息

    select * from emp where salary > 3600;

    第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

    select * from emp where salary > (select salary from emp where name = '猪八戒');

    这就是查询语句中嵌套查询语句。

  • 子查询根据查询结果不同，作用不同

    • 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断。
    • 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断。
    • 子查询语句结果是多行多列，子查询语句作为虚拟表。

⭐注意：所有子查询都必须用括号 ( ) 包围，无论其出现在SQL语句的哪个位置。

  • 案例

    • 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息

      -- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
select did from dept where dname = '财务部' or dname = '市场部';

select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

    • 查询入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息

      -- 查询入职日期是 '2011-11-11' 之后的员工信息
select * from emp where join_date > '2011-11-11' ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;
8.4.4 案例
  • 环境准备：
drop table if exists emp;
drop table if exists dept;
drop table if exists job;
drop table if exists salarygrade;

-- 部门表
create table dept (
  did int primary key primary key, -- 部门id
  dname varchar(50), -- 部门名称
  loc varchar(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
create table job (
  id int primary key,
  jname varchar(20),
  description varchar(50)
);

-- 员工表
create table emp (
  id int primary key, -- 员工id
  ename varchar(50), -- 员工姓名
  job_id int, -- 职务id
  mgr int , -- 上级领导
  joindate date, -- 入职日期
  salary decimal(7,2), -- 工资
  bonus decimal(7,2), -- 奖金
  dept_id int, -- 所在部门编号
  constraint emp_jobid_ref_job_id_fk foreign key (job_id) references job (id),
  constraint emp_deptid_ref_dept_id_fk foreign key (dept_id) references dept (id)
);
-- 工资等级表
create table salarygrade (
  grade int primary key,   -- 级别
  losalary int,  -- 最低工资
  hisalary int -- 最高工资
);
				
-- 添加4个部门
insert into dept(did,dname,loc) values 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
insert into job (id, jname, description) values
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');


-- 添加员工
insert into emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) values 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',null,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',null,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',null,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',null,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',null,20),
(1009,'罗贯中',1,null,'2001-11-17','50000.00',null,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',null,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',null,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',null,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',null,10);


-- 添加5个工资等级
insert into salarygrade(grade,losalary,hisalary) values 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

  • 需求

    1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

      /*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
*/
-- 方式一 ：隐式内连接
select
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
from
	emp,
	job
where
	emp.job_id = job.id;

-- 方式二 ：显式内连接
select
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description
from
	emp
inner join job on emp.job_id = job.id;

    2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

      /*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
*/

-- 方式一 ：隐式内连接
select
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
from
	emp,
	job,
	dept
where
	emp.job_id = job.id
	and dept.id = emp.dept_id
;

-- 方式二 ：显式内连接
select
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc
from
	emp
inner join job on emp.job_id = job.id
inner join dept on dept.id = emp.dept_id

    3. 查询员工姓名，工资，工资等级

      /*
	分析：
		1. 员工姓名，工资 信息在emp 员工表中
		2. 工资等级 信息在 salarygrade 工资等级表中
		3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
select
	emp.ename,
	emp.salary,
	t2.*
from
	emp,
	salarygrade t2
where
	emp.salary >= t2.losalary
and emp.salary <= t2.hisalary

    4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

      /*
	分析：
		1. 员工编号，员工姓名，工资 信息在emp 员工表中
		2. 职务名称，职务描述 信息在 job 职务表中
		3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id

		4. 部门名称，部门位置 来自于 部门表 dept
		5. dept 和 emp 一对多关系 dept.id = emp.dept_id
		6. 工资等级 信息在 salarygrade 工资等级表中
		7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
*/
select
	emp.id,
	emp.ename,
	emp.salary,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	t2.grade
from
	emp
inner join job on emp.job_id = job.id
inner join dept on dept.id = emp.dept_id
inner join salarygrade t2 on emp.salary between t2.losalary and t2.hisalary;

    5. 查询出部门编号、部门名称、部门位置、部门人数

      /*
	分析：
		1. 部门编号、部门名称、部门位置 来自于部门 dept 表
		2. 部门人数: 在emp表中 按照dept_id 进行分组，然后count(*)统计数量
		3. 使用子查询，让部门表和分组后的表进行内连接
*/
-- 根据部门id分组查询每一个部门id和员工数
select dept_id, count(*) from emp group by dept_id;

select
	dept.id,
	dept.dname,
	dept.loc,
	t1.count
from
	dept,
	(
		select
			dept_id,
			count(*) count
		from
			emp
		group by
			dept_id
	) t1
where
	dept.id = t1.dept_id

九、数据库设计

9.1 概述

(1) 软件的研发步骤

image-20260509203543642

(2) 数据库设计概念

  • 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
  • 建立数据库中的表结构以及表与表之间的关联关系的过程。
  • 有哪些表？表里有哪些字段？表和表之间有什么关系？

(3) 数据库设计的步骤

① 第一阶段：需求分析与规划
这是项目的基石，目标是明确 “要做什么”。

② 第二阶段：概念结构设计
将需求分析的结果抽象为一个独立于任何数据库管理系统（DBMS）的概念模型。

  • 任务：识别实体、实体的属性、实体之间的关系，并用图形化方式表示。
  • 核心工具：实体-联系模型。
  • 产出：E-R图。这是数据库设计人员的“设计草图”，用于与用户确认业务逻辑是否正确。

③ 第三阶段：逻辑结构设计（实际开发可跳过）
将概念模型转换为特定DBMS所支持的数据模型（如关系模型、网状模型）。对于关系数据库，就是将E-R图转换为表结构。

  • 任务：
    • 转换：将E-R图中的实体和关系转换为关系模式（即二维表）。实体通常转为表，属性转为列，关系通过主键和外键来实现。
    • 规范化：运用规范化理论（通常是1NF, 2NF, 3NF, BCNF）分析这些关系模式，消除数据冗余和更新异常，确保数据的完整性和一致性。
    • 优化：根据实际查询需求，对规范化后的模式进行适度调整（反规范化），以提升查询性能。
  • 产出：一组定义明确的关系模式，包括表名、列名、数据类型、主键、外键。

④ 第四阶段：物理结构设计
为逻辑模型选择最适合的物理存储结构和访问路径，以实现最优性能。它与具体的DBMS紧密相关。

  • 任务：
    • 确定数据的存储结构：如堆文件、索引文件等。
    • 设计索引：在哪些列上建立何种索引（B树、哈希等），以加速查询。
    • 设计分区策略：是否对表进行水平分区或垂直分区。
    • 确定存储参数：如数据块大小、填充因子等。
    • 设计安全方案：用户权限、数据加密等。
  • 产出：物理数据库设计方案，通常是可执行的SQL脚本框架。

⑤ 第五阶段：数据库实施
根据物理设计，创建实际的数据库、表、索引等，并装入初始数据。

  • 任务：
    • 使用DDL（数据定义语言）编写并执行建库、建表、建约束、建索引的SQL语句。
    • 组织数据入库，可以通过SQL插入语句或专用工具进行数据迁移。
    • 编写和调试应用程序中访问数据库的代码。
    • 进行试运行，在模拟或小范围真实环境下测试系统。
  • 产出：可运行的数据库系统。

⑥ 第六阶段：数据库运行与维护
数据库正式上线后，进入长期的维护、监控和优化阶段。

9.2 表关系

  • 一对多：在多的一方建立外键，指向一的一方的主键。

  • 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，即一个多对多拆成两个多（中间表）对一。

  • 一对一：在任意一方加入外键，关联另一方主键，并且设置外键为唯一(unique)。
    注：一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。

9.3 规范化理论（范式）

一个好的关系模式应该具备以下四个条件：
① 没有插入异常、没有删除异常、没有更新异常。
② 尽可能少的数据冗余（3NF）。

9.3.1 函数依赖

(1) 基本概念

  • 定义：函数依赖是指关系中各属性或属性组之间的相互依赖关系。它是关系规范化的理论基础。
  • 函数依赖的类型
    • 完全函数依赖
    • 部分函数依赖
    • 传递函数依赖

(2) 完全函数依赖
定义：如果属性Y函数依赖于复合属性（属性组）X，且不与X的任何子集函数相依赖，则称“Y完全函数依赖于X”,表示为X—F—>Y 。

【示例】有如下的关系模式:
生产(工厂编号,产品编号,计划数量)
(工厂编号+产品编号 )→计划数量，是完全函数依赖。

(3) 部分函数依赖
定义：对于一个函数依赖：X→Y，如果存在Z∈X（Z属于X），且有Z→Y成立，则称“Y部分函数依赖于X”,表示为X—P—>Y 。

【示例】有如下的关系模式
SCD (学号，姓名，课程号，成绩，系名，系主任)
主码为复合码：学号+课程号
“姓名、系名、系主任”与主码是部分函数依赖（只依赖于学号）。

(4) 传递函数依赖
定义：在关系模式中，如果X→Y(不能Y→X)，Y→Z，若存在X→Z ，则称“Z传递依赖于X”。

【示例】有如下的关系模式
SD（学号，姓名，系名，系主任）
(学号)→(系名)，(系名)→(系主任)”，则系主任传递依赖于学号。

9.3.2 关系的规范化

在关系数据库的设计过程中，对于同一个问题，选用不同的关系模式，其性能的优劣是大不相同的，为了区分关系模式的优劣，人们常常把关系模式分为各种不同等级的范式。

  • 范式的类型（由低到高）

    • 第一范式(1NF)
    • 第二范式(2NF)
    • 第三范式(3NF)
    • BC范式（BCNF）
    • 第四范式（4NF）
    • 第五范式（5NF，又称完美范式）

    数据库只需满足第三范式或第二范式就行了。

  • 关系规范化的概念：将低级范式转换为若干个高级范式过程。

9.3.2.1 第一范式（1NF）

定义：如果关系模式R的所有属性的值域中每一个值都是不可再分解值，则称R是属于1NF模式。
是针对属性值，也就是数据而言的。

【示例】
QQ20260509-204224
SC1不属于1NF，SC2属于1NF。

第一范式可能存在的问题：

  • 数据冗余：如各位老师的信息重复。
  • 插入异常：如果要插入刘老师的个人信息，但刘老师未开课，会造成缺关键字“学号”、“课程号”。
  • 删除异常：当要删除 课程号=“C3” 的元组，会丢失李老师的信息。
  • 修改量大。
9.3.2.2 第二范式（2NF）

定义：如果关系模式R为第一范式，且R中每个非主属性 完全函数依赖 于R的主码(复合码)，则称R为2NF模式。

【示例】
下列关系模式是1NF，是否是2NF？为什么？
SCD（学号，姓名，课程号，成绩，系名，系主任）
解析：
主码为复合码：学号+课程号
不是。因为：“姓名、系名、系主任”与主码是部分函数依赖（即：只依赖于学号）

解决方法：消除部分函数依赖。
分解为2NF的方法：

  • 把关系模式中对主码完全函数依赖的非主属性与决定它们的主码放在一个关系模式中。
  • 把对主码部分函数依赖的非主属性和决定它们的主属性放在一个关系模式中。
  • 检查分解后的新模式，如果仍不是2NF，则继续按照前面的方法进行分解，直到达到要求。

关系模式SCD的分解结果如下：
SC（学号，课程号，成绩）
SD（学号，姓名，系名，系主任）

9.3.2.3 第三范式（3NF）

定义：如果关系模式R为第二范式，且R中每个非主属性都不传递函数依赖于R的某个候选码（主属性），则称R为3NF模式。

【示例】
下列关系模式是2NF，是否为3NF？为什么？
SD（学号，姓名，系名，系主任）
解析：
主码为复合码：学号
不是。因为：学号→系名，系名→系主任，存在传递依赖关系。

解决方法：消除传递函数依赖。
分解为3NF的方法：

  • 把直接对主码函数依赖的非主属性与决定它们的主码放在一个关系模式中。
  • 把造成传递函数依赖的决定因素连同被它们决定的属性放在一个关系模式中。
  • 检查分解后的新模式，如果不是3NF，则继续按照前面的方法进行分解，直到达到要求。

关系模式SD的分解结果如下：
SD（学号，姓名，系名）
SD（系名，系主任）

9.3.2.4 各范式间的关系

1NF
  ↓  消除非主键对主键的部分依赖
2NF
  ↓  消除非主键对主键的传递依赖
3NF
  ↓  消除主键对主键的部分依赖和传递依赖
BCNF
  ↓  消除非平凡的多值依赖
4NF

9.4 设计书籍交易平台

① 确定实体（表）

  • 用户表：用户id、用户名、密码、电话、邮箱、所选城市、邮编
  • 书籍（商品）表：、书籍id、书籍名称、检验码、作者、出版社、出版日期、书籍简介、价格、折扣率、库存数量
  • 书籍类别表：书籍类别id、书籍id、类别id
  • 类别表（第三范式）：类别id、类别名
  • 订单表：订单id（订单号）、用户id（外键）、交付时间、订单状态、订单创建时间
  • 订单明细（书籍订单）表：明细id、订单id（订单号）、书籍id（外键）、书籍数量、订单总金额（可间接计算）
  • 购物车记录表：、购物车记录项id、用户id（外键）、书籍id（外键）、书籍数量、是否选中、添加时间、最近一次修改时间

② 各实体关系

  • 用户与购物车记录：1：n（拥有）
  • 用户与订单：1：n（归属）
  • 书籍与购物车记录：1：n
  • 书籍与订单：n：m --> 订单详情表与书籍：n：1，订单详情表与订单：n：1
  • 书籍与类别：n：m --> 书籍类别表与书籍：n：1，书籍类别表与类别：n：1

③ 建立E-R图
④ 转换为物理模型
⑤ 生成sql

# mysql # 数据库
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

cover

从词嵌入到RNN（其一）

avatar AtomGit开源社区

AI Agent Harness Engineering 私有化部署：难点、成本与最佳实践

本文将带你深入探讨AI Agent Harness Engineering私有化部署的全过程。我们将从什么是AI Agent Harness Engineering开始，逐步深入到私有化部署的技术难点、成本分析，最后分享业界的最佳实践。AI Agent Harness Engineering的核心概念和架构私有化部署的必要性和适用场景技术栈选择和环境准备部署过程中的关键难点和解决方案成本构成分析和

avatar AtomGit开源社区

ragflow v0.25.4 版本更新：RESTful API 数据源连接器、Agent 标签管理、Widget 持久化、GPT-5.4 模型支持全面升级

本次更新中，Widget 自定义和持久化也是一个非常值得关注的改进点。Widget 往往是界面中承担展示、操作或信息汇总功能的组件。能够自定义 Widget，说明用户可以根据自己的使用习惯或业务需求，对界面组件进行更灵活的配置。这会让界面更贴近实际使用，而不是只停留在固定布局和固定展示方式上。这一项已经在前面详细说明，本次更新明确包含了这项能力，说明它是本版的重要改进之一。代码地址：github.

avatar AtomGit开源社区