二、MySQL 数据库实战

qddd
27 阅读30分钟

一. 数据持久化环境准备：MySQL数据库的安装与介绍

1.1 环境准备：基于windows操作系统的本地数据库搭建

1.1.1 获取下载文件

1.1.1.1 方式一

直接从我提供的“上课所需软件”中，找到mysql安装软件文件夹，里边一共包含了3个版本

  • windows : mysql-8.4.8-winx64.msi
  • MacOS X86_64 : mysql-8.4.8-macos15-x86_64.dmg
  • MacOS arm64 : mysql-8.4.8-macos15-arm64.dmg

根据自己的电脑型号进行选择

1.1.1.2 方式二

下载地址：dev.mysql.com/downloads/m…

这里的版本我们选择的是 8.4.8LTS 版本，其中LTS指的是长期维护版本，这个版本未来10年都将成为主流。

如果你是mac电脑：注意，下载的时候操作系统要选择macOS，并且根据自己电脑处理器的实际情况，选择arm或者是X86_64

1.1.2 安装过程

双击打开下载的安装文件，点击下一步

勾选“同意”，然后点击下一步

选择用户自定义安装

修改你的安装路径，切记：路径中一定不能包含中文，不然会出现莫名其妙的问题

开始安装

等待就可以

安装完成

配置MySQL

点击：Configure this server instance as a side-by-side installation

点击下一步

由于我们之前安装禅道的时候，禅道已经帮我们安装好了一个mysql，它占用的端口号是3306，所以这里我们需要修改一个端口号，我们改成3307

记住：MySQL的默认端口号是3306，只是这里我们之前安装了一个被占用了所以才改成3307

修改前：

修改后：

修改完端口号之后，我们点击下一步

这里mysql有一个默认的超级管理员用户，用户名就是root

我们需要给root用户设置一个密码。输入两次相同的密码后，点击下一步。

这里的密码一定要记住，后边我们连接MySQL的时候需要用到。

点击下一步

点击下一步

点击下一步

点击Excute

等待执行完成，然后点击下一步

点击完成

1.1.3 验证安装

点击开始菜单栏，找到MySQL8.4 Command Line Client，这个是MySQL的客户端工具，打开它

在安装的时候，我们设置了用户名为root的用户的密码，输入这个密码

输入后，按回车键

见到下图就是成功了

1.2 环境准备：MySQL数据库客户端操作利器DataGrip的安装

1.2.1 获取下载文件

不要到网上乱下载，这款软件以前是纯收费软件，现在对于个人学习者开放免费了，我给你的下载方式，都是可以免费使用的。

1.2.1.1 方式一

直接从我提供的“上课所需软件”中，找到mysql数据库连接软件datagrip文件夹，里边一共包含了3个版本

  • windows : datagrip-2025.3.4.exe
  • MacOS X86_64 : datagrip-2025.3.4.dmg
  • MacOS arm64 : datagrip-2025.3.4-aarch64.dmg

根据自己的电脑型号进行选择

1.2.1.2 方式二

下载地址：www.jetbrains.com.cn/datagrip/do…

1.2.2 安装DataGrip

双击打开对应安装文件

然后点击下一步

选择你的安装路径，切记：不能安装在中文路径下

这个里边的选项，勾选或者不勾选都行，也没啥太大影响，直接下一步也可以

点击安装

等待安装完成

点击完成

至此DataGrip就安装完成了。

二. 数据查询的基本技巧：单表的各种查询方法

2.1 数据准备：向数据库中插入数据

2.1.1 数据准备

向任意数据库中插入以下数据

/* 如果学生表students存在，就删除学生表students */
drop table if exists students;

/* 创建学生表students */
create table students (
  studentNo varchar(10) primary key, /*学号，主键，值不能重复*/
  name varchar(10), /*姓名*/
  sex varchar(1), /*性别*/
  hometown varchar(20), /*家乡*/
  age tinyint, /*年龄*/
  class varchar(10), /*班级*/
  card varchar(20) /*身份证号码*/
);

/* 向学生表students插入数据 */
insert into students values
('001', '王昭君', '女', '北京', '30', '1班', '110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班', '310102199104262354'),
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'),
('004', '白起', '男', '安徽', '35', '4班', '340202198505177654'),
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'),
('006', '孙尚香', '女', '河北', '25', '1班', '130502199506137654'),
('007', '百里玄策', '男', '山西', '39', '2班', '140102198107277654'),
('008', '小乔', '女', '河南', '25', '3班', null),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'),
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'),
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655');

2.2 初识查询语句：单表查询与运算符

2.2.1 单表查询与运算符

2.2.2 查询语句的基本结构

select 列名 from 表名;

2.2.3 查询表中所有的字段

select * from 表名;
select * from students;

2.2.4 字段的别名

  • 通过 字段名 as 别名 的语法,可以给字段起一个别名,别名可以是中文
  • as可以省略
  • 字段名 as 别名 和 字段名 别名 结果是一样的
-- 通过as 给字段起一个别名
select card as 身份证, name as 姓名,sex as 性别 from students;

-- 别名的as可以省略
select card 身份证, name 姓名,sex 性别 from students;

2.2.5 表的别名

  • 通过 表名 as 别名 给表起一个别名
  • as可以省略
-- 通过as 给表students起一个别名
select * from students as stu;

-- 可以省略as
select * from students stu;

2.2.6 distinct过滤重复记录

  • 单字段去重

    SELECT DISTINCT sex from students;
SELECT DISTINCT class from students;

  • 通过select distinct 字段名, 字段名 from 表名 来过滤select查询结果中的重复记录(多字段联合去重)

SELECT DISTINCT sex, class from students;

2.2.7 where子句

  • where 后面跟一个条件,实现有选择的查询
  • select * from 表名 where 条件
-- 例 1：查询 students 表中学号 studentNo 等于’001’ 的记录
select * from students where studentNo = '001';

-- 例 2：查询 students 表中年龄 age 等于 30 的姓名 name,班级 class
select name, class from students where age = 30;

2.2.8 select查询的基本规律

  • select * 或者select 字段名 控制了查询返回什么样的字段(列)
  • where 条件 控制了查询返回什么样的记录(行)

2.2.9 比较运算符

  • =等于
  • < 小于
  • <= 小于等于
  • >大于
  • >=大于等于
  • !=和<>不等于
-- 例 1：查询 students 表中 name（姓名）等于’小乔’学生的 age（年龄）
select age from students where name = '小乔';

-- 例 2：查询 students 表中 30 岁以下的学生记录
SELECT * from students where age < 30;

-- 例 2：查询 students 表中 30 岁和30岁以下的学生记录
SELECT * from students where age <= 30;

-- 查询家乡不在'北京'的学生记录
select * from students where hometown != '北京';
select * from students where hometown <> '北京';
  • 课堂练习答案
-- 练习 1：查询 studentNo 学号是'007'的学生的身份证号 card；
SELECT card from students where studentNo = '007';
-- 练习 2：查询 class 班级为'1 班'以外的学生记录；
SELECT * from students where class <> '1班';
-- 练习 3：查询 age 年龄大于 25 的学生的 name 姓名和 sex 性别；
SELECT name, sex from students where age > 25;

2.3 单表进阶：范围查找与空的判断

2.3.1 范围查找

  • in (值, 值, 值)
    • 非连续范围查找
  • between 开始值 and 结束值
    • 连续范围查找,包含开始值 包含 结束值
-- 例 1：查询 hometown 家乡是’北京’或’上海’或’广东’的学生记录
SELECT * from students where hometown = '北京' or hometown = '上海' or hometown = '广东';
SELECT * from students where hometown in ('北京', '上海', '广东');

-- 例 2：查询 age 年龄为 25 至 30 的学生记录
SELECT * from students where age >= 25 and age <= 30;
SELECT * from students where age BETWEEN 25 and 30;
  • 课堂练习答案
-- 练习 1：查询 age 年龄在 20 或 25 或 30 的女生记录；
SELECT * from students where age in (20, 25, 30) and sex = '女';
-- 练习 2：查询 age 年龄 25 到 30 以外的学生记录；
SELECT * from students where not age BETWEEN 25 and 30;

2.3.2 空的判断

  • null不是0,也不是'',null在SQL里面代表空,什么也没有
  • null不能用比较运算符的判断，例如>null，是不可以的
  • is null ---是否为null
  • is not null ---是否不为null
    • 不能用 字段名 = null 字段名 != null这些都是错误的
-- 例 1：查询 card 身份证为 null 的学生记录
SELECT * from students where card is null;

-- 例 2：查询 card 身份证非 null 的学生记录
SELECT * from students where card is not null;

## 2.4 单表进阶：逻辑运算符与模糊查询

### 2.4.1 逻辑运算符

- and与
  - 条件1 and 条件2
  - 两个条件必须都满足
- or或
  - 条件1 or 条件2
  - 两个条件只要有一个满足即可
- not非
  - not 条件
  - 条件成立,not以后就不成立；条件不成立,not以后就成立

```sql
-- 例 1：查询 age 年龄小于 30，并且 sex 性别为’女’的同学记录
SELECT * from students where age < 30 and sex = '女';

-- 例 2：查询 sex 性别为’女’或者 class 班级为'1 班'的学生记录
SELECT * from students where sex = '女' or class = '1班';

-- 例 3：查询 hometown 老家非’天津’的学生记录
SELECT * from students where not hometown = '天津';

-- 例 3：查询 hometown 老家’天津’的学生记录
SELECT * from students where not hometown != '天津';
  • 课堂练习
-- 练习 1：查询 hometown 老家是’河南’或’河北’的学生记录；
SELECT * from students where hometown = '河南' or hometown = '河北';
-- 练习 2：查询 class 班级为'1 班'，并且 hometown 老家为'北京'的学生记录；
SELECT * from students where class = '1班' and hometown = '北京';
-- 练习 3：查询 age 年龄非 30 岁的学生记录；
SELECT * from students where not age = 30;
SELECT * from students where age != 30;
SELECT * from students where age <> 30;

2.4.2 模糊查询

  • like实现模糊查询
  • %代表任意多个字符
  • _代表任意一个字符
  • 字段名 like '字符%'
    • 指定字符开始,后面任意多个字符
-- 例 1：查询 name 姓名中以’孙’开头的学生记录
SELECT * from students where name like '孙%';

-- 例 2：查询 name 姓名以’孙’开头，且名只有一个字的学生记录
SELECT * from students where name like '孙_';

-- 例 3：查询 name 为任意姓，名叫’乔’的学生记录
SELECT * from students where name like '%乔';

-- 查询 name 姓名有’白’子的学生记录
SELECT * from students where name like '%白%';
  • 课堂练习答案
-- 练习 1：查询 name 姓名为两个字的学生记录；
SELECT * from students WHERE name like '__';
-- 练习 2：查询 name 姓’白’且年龄大于 30 的学生记录；
SELECT * from students WHERE name like '白%' and age > 30;
-- 练习 3：查询 studentNo 学号以 1 结尾的学生记录；
select * from students WHERE studentNo like '%1';

2.5 单表进阶：表中数据的排序

2.5.1 order by排序

  • order by 字段名 [asc/desc]
    • asc代表从小到大,升序,asc可以省略
    • desc代表从大到小,不可以省略
-- 例 1：查询所有学生记录，按 age 年龄从小到大排序

select * from students order by age asc;
select * from students order by age;
-- 例 2：查询所有学生记录，按 age 年龄从大到小排序
select * from students order by age desc;
  • 两个字段排序的例子
-- 例 2：查询所有学生记录，按 age 年龄从大到小排序，
--  年龄相同时，再按 studentNo 学号从小到大排序
SELECT * from students ORDER BY age desc, studentNo;
  • 当一条select语句出现了where和order by
    • select * from 表名 where 条件 order by 字段1,字段2;
    • 一定要把where写在order by前面
-- 练习：查询所有男学生记录，按 class 班级从小到大排序，班级相同时，
-- 再按 studentNo 学号再按学号从大到小排序
SELECT * from students where sex = '男' order by class, studentNo desc;

2.6 字段值的运算：SQL中的聚合函数

2.6.1 聚合函数

2.6.1.1 count求select返回的记录总数
  • count(字段名)
-- 查询学生总数(查询stuents表有多少记录)
select count(*) from students;
select count(name) from students;
select count(DISTINCT class) from students;
select count(DISTINCT sex) from students;
-- 查询女同学数量
SELECT count(name) from students where sex = '女';
SELECT count(*) from students where sex = '女';
SELECT count(sex) from students where sex = '女';
2.6.1.2 max查询最大值
  • max(字段名)
  • 查询指定字段里的最大值
-- 查询students中的最大年龄
SELECT max(age) from students;

-- 查询students中的女生最大年龄
SELECT max(age) from students where sex = '女';

-- 查询students中的'1班'最大年龄
SELECT max(age) from students where class = '1班';

聚合函数不能用到where后面的条件里

2.6.1.3 min查询最小值
  • min(字段名)
  • 查询指定字段的最小值
-- 查询students中的最小年龄
SELECT min(age) from students;

-- 查询students中的女生最小年龄
SELECT min(age) from students where sex = '女';

-- 查询students中的'1班'最小年龄
SELECT min(age) from students where class = '1班';
2.6.1.4 sum求和
  • sum(字段名)
  • 指定字段的值求和
-- 查询students中的年龄总和
SELECT sum(age) from students;

-- 查询students中的女生年龄总和
SELECT sum(age) from students where sex = '女';

-- 查询students中的'1班'年龄总和
SELECT sum(age) from students where class = '1班';
2.6.1.5 avg求平均数
  • avg(字段名)
  • 指定字段的平均值
-- 查询students中的年龄的平均值
SELECT avg(age) from students;

-- 查询students中的女生年龄的平均值
SELECT avg(age) from students where sex = '女';

-- 查询students中的'1班'年龄的平均值
SELECT avg(age) from students where class = '1班';
  • avg的字段中如果有null,null不做为分母计算平均
create table aa (age int, name varchar(10));
insert into aa values (10, 'a'), (20, 'b'), (null, 'c');
select avg(age) from aa;-- 结果为15,而不是10
  • 课堂练习答案
-- 练习 1：查询所有学生的最大年龄、最小年龄、平均年龄；
SELECT max(age) 最大年龄, min(age) 最小年龄, avg(age) 平均年龄 from students;
-- 练习 2：查询’1 班’共有多少个学生；
SELECT count(*) from students where class = '1班';
-- 练习 3：查询’3 班’中年龄小于 30 岁的同学有几个；
select count(*) from students where class = '3班' and age < 30;

2.7 单表进阶：分组与聚合后的筛选

2.7.1 数据分组

  • group by 字段名
  • select 聚合函数 from 表名 where 条件 group by 字段
  • select 聚合函数 from 表名 group by 字段
  • group by就是配合聚合函数使用的
-- 分别查询男女同学的数量
SELECT count(*) from students where sex = '男';
SELECT count(*) from students where sex = '女';

select sex, count(*) from students group by sex;
  • group by的例子
-- 分别查询各个年龄段的同学数量

select age, count(*) from students group by age;
  • where与group by
-- 分别查询'1班'不同性别学生数量

select sex, count(*) from students where class = '1班' group by sex;
  • 课堂练习答案
-- 练习：用数据分组方法，统计各个班级学生总数、平均年龄、最大年龄、最 小年龄。
SELECT class, count(*), avg(age), max(age), min(age) from students GROUP BY class

-- 练习：统计各个班级学生总数、平均年龄、最大年龄、最小年龄。
-- 但不统计'3班',统计结果按班级名称从大到小排序
SELECT class, count(*), avg(age), max(age), min(age) from students
where class <> '3班' GROUP BY class ORDER BY class desc;
  • where和group by 和order by的顺序
    • select * from 表名 where 条件 group by 字段 order by 字段

2.7.2 分组聚合之后的数据筛选

  • having子句
  • 总是出现在group by之后
  • select * from 表名 group by 字段 having 条件
-- 用where查询男生总数
-- where先筛选复合条件的记录,然后在聚合统计
SELECT count(*) from students where sex = '男';

-- 用having查询男生总数
-- having先分组聚合统计,在统计的结果中筛选
SELECT count(*) from students GROUP BY sex HAVING sex = '男';

2.7.3 having配合聚合函数的使用

  • where后面条件不能使用聚合函数, having可以使用聚合函数
-- 求班级人数大于3人的班级名字
select class from students GROUP BY class HAVING count(*) > 3;

2.7.4 having与where筛选的区别

  • where是对标的原始数据进行筛选

  • having是对group by之后已经分过组的数据进行筛选

  • having可以使用聚合函数, where不能用聚合函数

  • 课堂练习答案

-- 练习：查询班级总人数大于 2 人的班级名称以及班级对应的总人数;
select class, count(*) from students GROUP BY class HAVING count(*) > 2;

-- 练习 :  查询平均年龄大于30岁的班级名称和班级总人数
SELECT class, count(*) from students GROUP BY class HAVING avg(age) > 30;

2.8 系统中的分页：limit控制返回数据条数

2.8.1 limit显示指定的记录数

  • select * from 表名 where 条件 group by 字段 order by 字段 limit start, count
  • limit总是出现在select语句的最后,
  • start代表开始行号,行号从0开始编号
  • count代表要显示多少行
  • 省略start,默认从0开始,从第一行开始
-- 查询前三行记录
SELECT * from students limit 0, 3;
SELECT * from students limit 3;

-- 查询从第4条记录开始的三条记录
SELECT * from students limit 3, 3;
  • 当有where或者group by或者order by, limit总是出现在最后
-- 查询年龄最大同学的name
select name from students ORDER BY age desc limit 1;

-- 查询年龄最小的女同学信息
SELECT * from students where sex = '女' ORDER BY age LIMIT 1;

2.8.2 数据分页显示

-- 每页显示4条记录,第3页的结果
select * from students limit 8, 4;
-- 每页显示4条记录,第2页的结果
select * from students limit 4, 4;
  • 课堂练习答案
-- 每页显示5条记录,分别多条select显示每页的记录
-- 第一页
SELECT * from students limit 5;
-- 第二页:
SELECT * from students limit 5, 5;
-- 第三页:
SELECT * from students limit 10, 5;

三. 数据获取的高级技巧：多表关联的各种查询方式

3.1 数据准备：向数据库中插入学生、分数及课程表的数据

3.1.1 向任意数据库中插入以下多个表的数据

/* 如果学生表students存在，就删除学生表students */
drop table if exists students;

/* 创建学生表students */
create table students (
  studentNo varchar(10) primary key, /*学号，主键，值不能重复*/
  name varchar(10), /*姓名*/
  sex varchar(1), /*性别*/
  hometown varchar(20), /*家乡*/
  age tinyint, /*年龄*/
  class varchar(10), /*班级*/
  card varchar(20) /*身份证号码*/
);

/* 向学生表students插入数据 */
insert into students values
('001', '王昭君', '女', '北京', '30', '1班', '110101199003157654'),
('002', '诸葛亮', '男', '上海', '29', '2班', '310102199104262354'),
('003', '张飞', '男', '南京', '30', '3班', '320102199003047654'),
('004', '白起', '男', '安徽', '35', '4班', '340202198505177654'),
('005', '大乔', '女', '天津', '28', '3班', '120101199204067654'),
('006', '孙尚香', '女', '河北', '25', '1班', '130502199506137654'),
('007', '百里玄策', '男', '山西', '39', '2班', '140102198107277654'),
('008', '小乔', '女', '河南', '25', '3班', null),
('009', '百里守约', '男', '湖南', '31', '1班', ''),
('010', '妲己', '女', '广东', '24', '2班', '440701199607147654'),
('011', '李白', '男', '北京', '30', '4班', '110202199005017754'),
('012', '孙膑', '男', '新疆', '36', '3班', '650102198401297655');

/* 如果课程表courses存在，删除课程表courses */
drop table if exists courses;

/* 创建课程表courses */
create table courses (
courseNo int unsigned primary key auto_increment, /*课程号 主键，值不能重复，且自增长*/
coursename varchar(10) /*课程名称*/
);

/* 向课程表courses插入数据 */
insert into courses (coursename) values
('数据库'),
('html'),
('linux'),
('系统测试'),
('单元测试'),
('测试过程'),
('python');

/* 如果成绩表scores存在，删除课程表scores */
drop table if exists scores;

/* 创建scores表 */
create table scores (
id int unsigned primary key auto_increment,/*主键，值不能重复，且自增长*/
courseNo int unsigned, /*课程号，与courses表中courseNo关联*/
studentNo varchar(10), /*学号，与students表中studentNo关联*/
score tinyint /*成绩*/
);

/* 向scores表插入数据 */
insert into scores (courseNo, studentNo,score) values
(1, '001', 90),
(1, '002', 75),
(2, '002', 98),
(3, '001', 86),
(3, '003', 80),
(4, '004', 79),
(5, '005', 96),
(6, '006', 80);

3.2 多表关联：内连接查询

3.2.1 内连接: 把两张表相同的地方查询出来

  • 语法:
    • select * from 表1 inner join 表2 on 表1.字段 = 表2.字段
    • 内连接最重要的是,找对两张表要关联的字段
SELECT * from a INNER JOIN b on a.id = b.id;
  • students表和scores内连接查询
SELECT * from students INNER JOIN scores on students.studentNo = scores.studentNo;
  • 隐式内连接语法
    • 语法:select * from 表1,表2 where 两个表的连接条件
-- 隐式内连接
SELECT * from students, scores where students.studentNo = scores.studentNo;
  • 内连接查询,显示指定的字段
-- students表与socres内连接,只显示name 课程号 成绩
SELECT name, courseNo, score from students
INNER JOIN scores on students.studentNo = scores.studentNo;
  • 表的别名在查询中的使用
SELECT name 姓名, courseNo 课程编号, score 成绩 from students st
INNER JOIN scores sc on st.studentNo = sc.studentNo;
  • 课堂练习答案
-- 练习 1: 查询成绩表信息，同时显示成绩对应的课程名称
SELECT * from scores s INNER JOIN courses c on s.courseNo = c.courseNo;
  • 带有where的内连接
    • 语法select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件
-- 例 5：查询王昭君的信息，要求只显示姓名、课程号、成绩
select name, courseNo, score from students s1
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君';
  • 带有and的where条件
-- 例 6：查询姓名为’王昭君’,并且成绩小于 90 的信息，要求只显示姓名、成绩
select name, score from students s1
INNER JOIN scores s2 on s1.studentNo = s2.studentNo
where s1.name = '王昭君' and s2.score < 90;
  • 多表内连接
-- 例 7：查询学生信息和成绩以及成绩对应的课程名称
SELECT * from students inner join scores on students.studentNo = scores.studentNo
inner join courses on scores.courseNo = courses.courseNo;

3.2.2 写SQL三步法

  • 搭框架
    • 基本的select语句框架搭建起来,如果有多表,把相应的多表也联合进来
  • 看条件
    • 决定where后面的具体条件
  • 显示的字段
    • select后面到底要显示什么字段
-- 练习 2：查询所有学生的’linux’课程成绩，要求只显示姓名、成绩、课程名
-- 第一步:搭框架
SELECT * from students INNER JOIN scores
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo;
-- 第二步:看条件
SELECT * from students INNER JOIN scores
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where courseName = 'linux';
-- 第三步:返回字段名
SELECT name, score, courseName from students INNER JOIN scores
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where courseName = 'linux';
  • 带有order by的联合查询
-- 例 8：查询成绩最高的男生信息，要求显示姓名、课程名、成绩
SELECT name, score, courseName from students INNER JOIN scores
on students.studentNo = scores.studentNo
INNER JOIN courses on scores.courseNo = courses.courseNo
where sex = '男'
order by score desc LIMIT 1;

SELECT s1.name, s2.score, c1.courseName from students s1
    INNER JOIN scores s2
        on s1.studentNo = s2.studentNo
INNER JOIN courses  c1
    on s2.courseNo = c1.courseNo
where s1.sex = '男'
order by s2.score desc LIMIT 1;

3.3 多表关联：左连接查询与右连接查询

3.3.1 左连接查询

  • 左连接
    • 包括了内连接,同时还查询左表特有的内容

  • 语法
    • select * from 表1 left join 表2 on 表1.字段 = 表2.字段
-- 例 1：查询所有学生的信息以及成绩，包括没有成绩的学生
SELECT * from students left JOIN scores ON
students.studentNo = scores.studentNo;

3.3.2 右连接查询

  • 右连接
    • 包括了内连接,同时还查询右表特有的内容

  • 语法
    • select * from 表1 right join 表2 on 表1.字段 = 表2.字段
-- 例 1：查询所有课程的信息，包括没有成绩的课程
SELECT * from scores RIGHT JOIN courses ON
scores.courseNo = courses.courseNo;

3.3.3 多表联合查询,同名字段的处理方式

  • 如果一条select要用到多个表,表中有同名字段.就需要 表名.字段名 加以区分
  • 表名字太长，书写不方便就用别名
select students.studentNo from students INNER JOIN scores ON
students.studentNo = scores.studentNo;

select s1.studentNo from students s1 INNER JOIN scores s2 ON
s1.studentNo = s2.studentNo;

3.4 数据准备：向库中插入区域表

3.4.1 向任意数据库中插入以下多个表的数据


/* 如果区域表areas存在，删除areas */
drop table if exists areas;

/* 创建区域表areas */
create table areas(
id int primary key,
name varchar(20),
pid int);

/* 向区域表areas插入数据 */
insert into areas
values ('130000', '河北省', NULL),
('130100', '石家庄市', '130000'),
('130400', '邯郸市', '130000'),
('130600', '保定市', '130000'),
('130700', '张家口市', '130000'),
('130800', '承德市', '130000'),
('440000', '广东省', NULL),
('440300', '深圳市', '440000'),
('440100', '广州市', '440000'),
('440500', '汕头市', '440000'),
('440600', '佛山市', '440000'),
('440200', '韶关市', '440000');

3.5 把一个表看成是两个表的自连接查询

3.5.1 自连接查询

-- 查询一共有多少个省
SELECT count(*) from areas where pid is null;
-- 查询有多少市
SELECT count(*) from areas where pid is not null;
  • 自关联,是同一张表做连接查询
  • 自关联下,一定找到同一张表可关联的不同字段
-- 例 2：查询广东省的所有城市
SELECT * from areas a1 INNER JOIN areas a2
on a1.id = a2.pid
WHERE a1.name = '广东省';

3.6 嵌套查询：子查询的方法

3.6.1 子查询

  • 子查询是嵌套到主查询里面的
  • 子查询做为主查询的数据源或者条件
  • 子查询是独立可以单独运行的查询语句
  • 主查询不能独立运行,依赖子查询的结果
-- 例 1：查询大于平均年龄的学生记录
SELECT avg(age) from students;
--
select * from students where age > 30.1667;

-- 用子查询实现
select * from students where age > (SELECT avg(age) from students);
  • 标量子查询------子查询返回结果只有一行,一列
-- 例 2：查询 30 岁的学生的成绩
-- 1,查询30岁学生的studentNO
-- select studentNo from students where age = 30;
--
-- SELECT * from scores where studentNo in ('001', '003', '011');

-- 用子查询实现
SELECT * from scores where studentNo in
(select studentNo from students where age = 30);
  • 列子查询------子查询返回一列多行
-- 例 3：用子查询，查询所有女生的信息和成绩
-- 用内连接实现
SELECT * from students INNER JOIN scores ON
students.studentNo = scores.studentNo
where sex = '女';
-- 用子查询实现
select * from (SELECT * from students where sex = '女') stu
INNER JOIN scores sc on stu.studentNo = sc.studentNo;

3.7 函数进阶：补充函数讲解

3.7.1 concat拼接字符串函数

  • concat(参数1, 参数2, 参数3, 参数n)
    • 参数可以是数字,也可以是字符串
    • 把所有的参数连接成一个完整的字符串
-- 例 1：把 12,34，’ab’拼接为一个字符串’1234ab’
select concat(12, 34, 'ab');

3.7.2 length返回字符串字符的个数

  • 一个utf8格式的汉字,length返回3
-- 例 2：计算字符串’abc’的长度
select length('abc');

-- 例 3：计算字符串’我和你’的长度
SELECT length('我和你');

-- 例 4：计算字符串’我和you’的长度
SELECT length('我和you');

3.7.3 mysql内置函数可以在where条件后面使用

-- 例 4：查询表 students 中 name 长度等于 9（三个 utf8 格式的汉字）的学生信息
SELECT * from students where length(name) = 9;

3.7.4 left从字符串左侧截取指定数量字符

  • left(字符串, n)
    • n代表从字符串左侧截取n个字符
-- 例 5：截取字符串’我和你abc’的左端 3 个字符
select left('我和你abc', 3);

-- 例 6：截取字符串’我和你abc’的左端 4 个字符
select left('我和你abc', 4);

-- 例 6：截取字符串’abc我和你’的左端 4 个字符
select left('abc我和你', 4);

3.7.5 right从字符串右侧截取指定数量的字符

  • rigth(字符串, n)
    • n代表从字符串右侧截取n个字符
-- 例 5：截取字符串’我和你abc’的右端 3 个字符
select right('我和你abc', 3);

-- 例 5：截取字符串’我和你abc’的右端 4 个字符
select right('我和你abc', 4);

3.7.6 substring从字符串指定位置截取指定数量字符

  • substring(字符串, 起始位置, n)
    • 起始位置从1开始
    • n代表截取的数量
-- 例 7：截取字符串’我和你abc’从第 2 个字符开始的 3 个字符
select substring('我和你abc', 2, 3);

-- 例 7：截取字符串’我和你abc’从左侧开始的 3 个字符
select substring('我和你abc', 1, 3);

-- 例 7：截取字符串’我和你abc’从第 4 个字符开始的 1 个字符
select substring('我和你abc', 4, 1);

3.7.7 内置函数可以用在select显示的字段名中

-- 例 8：截取 students 表中所有学生的姓
SELECT left(name, 1) from students;
SELECT substring(name, 1, 1) from students;
  • 课堂练习答案
-- 练习 1：查询 students 表的 card 字段，截取出生年月日，显示李白的生日
SELECT substring(card, 7, 8) from students where name = '李白';

-- 练习 2：查询 students 表的所有学生信息，按生日从大到小排序(
SELECT * from students ORDER BY substring(card, 7, 8);

3.7.8 ltrim去除字符串左侧空格

  • ltrim(带空格的字符串)
-- 例 1：去除字符串' abcd '左侧空格

SELECT ltrim('         abcd ');

3.7.9 rtrim去除字符串右侧空格

  • rtrim(带空格的字符串)
-- 例 1：去除字符串' abcd     '右侧空格
SELECT rtrim(' abcd     ');
select concat(rtrim(' abcd     '), '测试字符');

3.7.10 trim去除字符串两侧空格

  • trim(带空格的字符串)
-- 例 1：去除字符串'     abcd     '两侧空格
SELECT trim('     abcd     ');

3.7.11 round四舍五入

  • round(数字, d)
    • d代表要保留的小数位,省略d默认为0
-- 例 1：1.653 四舍五入，保留整数位
SELECT round(1.653);

-- 例 2：1.653 四舍五入，保留2位小数
SELECT round(1.653, 2);
  • 课堂案例
-- 例 3：查询 students 表中学生的平均年龄，并四舍五入,保留两位小数
SELECT round(avg(age), 2) from students;

3.7.12 rand随机数

  • rand()

    • 每次运行会产生一个从0到1之间的浮点数

  • 经常用rand对一个数据进行随机排序

    • order by rand()
    select rand();

-- 小技巧：从学生表中随机抽出一个学生
SELECT * from students order by rand() LIMIT 1;

3.7.13 current_date返回系统日期

  • current_date()

3.7.14 current_time返回系统时间

  • current_time()

3.7.15 返回系统日期与时间

  • now()
select current_date();

select current_time();

select now();
  • 日期和时间函数的案例
-- 插入记录时,插入系统当前时间
create table a (id int, indate datetime);
insert into a values (1, '2010-09-10 12:01:02');
insert into a values (1, now());
select * from a;

3.7.16 md5加密函数

  • md5()
select md5(123456)

3.8 数据准备：向库中插入人员设备表

3.8.1 向任意库中创建表并向表中插入数据

3.8.1.1 创建表
-- 创建用户设备信息表，包含json类型字段
CREATE TABLE IF NOT EXISTS t_user_device (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID，自增',
    user_id VARCHAR(32) NOT NULL COMMENT '用户唯一标识，如手机号/用户编号',
    user_name VARCHAR(50) NOT NULL COMMENT '用户姓名',
    gender TINYINT COMMENT '性别：1-男，2-女，0-未知',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据更新时间',
    device_detail JSON NOT NULL COMMENT '用户设备详细信息，JSON格式：存储多设备的品牌、型号、系统版本、使用时长等'
)
3.8.1.2 向表中插入数据
-- 插入5条测试数据，device_detail为JSON格式字段
INSERT INTO t_user_device (user_id, user_name, gender, device_detail)
VALUES
-- 数据1：单安卓手机用户
('13800138000', '张三', 1, '{"devices": [{"device_type": "手机", "brand": "小米", "model": "14 Ultra", "system": "MIUI 16", "use_days": 120}]}'),
-- 数据2：苹果手机+苹果平板用户
('13900139000', '李四', 2, '{"devices": [{"device_type": "手机", "brand": "苹果", "model": "iPhone 15 Pro", "system": "iOS 18", "use_days": 89}, {"device_type": "平板", "brand": "苹果", "model": "iPad Pro 12.9", "system": "iPadOS 18", "use_days": 60}]}'),
-- 数据3：华为手机+联想电脑用户
('13700137000', '王五', 1, '{"devices": [{"device_type": "手机", "brand": "华为", "model": "Mate 60 Pro", "system": "HarmonyOS 4.2", "use_days": 205}, {"device_type": "电脑", "brand": "联想", "model": "拯救者Y9000P", "system": "Windows 11", "use_days": 310}]}'),
-- 数据4：多设备（2手机+1平板+1电脑）用户
('13600136000', '赵六', 0, '{"devices": [{"device_type": "手机", "brand": "vivo", "model": "X100s", "system": "OriginOS 4", "use_days": 95}, {"device_type": "手机", "brand": "三星", "model": "S24 Ultra", "system": "One UI 6.1", "use_days": 78}, {"device_type": "平板", "brand": "华为", "model": "MatePad Pro 11", "system": "HarmonyOS 4.0", "use_days": 156}, {"device_type": "电脑", "brand": "苹果", "model": "MacBook Pro 16", "system": "macOS Sonoma", "use_days": 280}]}'),
-- 数据5：平板+电脑（无手机）用户
('13500135000', '孙七', 2, '{"devices": [{"device_type": "平板", "brand": "荣耀", "model": "V8 Pro", "system": "MagicOS 7.2", "use_days": 112}, {"device_type": "电脑", "brand": "华硕", "model": "ROG 幻16", "system": "Windows 11", "use_days": 189}]}');

3.8.2 json字段说明

JSON 字段设计：device_detail 为 json 类型，内部采用数组 + 对象结构（devices 数组存储多个设备对象），每个设备对象包含device_type（设备类型）、brand（品牌）、model（型号）等固定字段，可根据业务需求灵活新增 / 删减字段（如添加device_sn（设备序列号）、battery_health（电池健康度）等），无需修改表结构，这是 json 字段的核心优势。

3.9 特殊的字段格式：Json格式的理解与练习

3.9.1 理解json

Json就是使用大括号{}包裹起来的一组信息，其中包含至少一组的 key：value

 {
      "设备类型": "手机",
      "品牌": "小米",
      "型号": "14 Ultra",
      "使用天数": 120
}

3.9.2 理解数组（列表）

数组就是使用中括号[]包裹起来的一组信息，其中包含多个value

  • 例如 [1,2,3,4,5]

数组中的元素可以是多种数据类型，当然也可以是json

3.9.3 数组中包含json

[
    {"device_type": "手机", "brand": "苹果", "model": "iPhone 15 Pro", "system": "iOS 18", "use_days": 89},
 	{"device_type": "平板", "brand": "苹果", "model": "iPad Pro 12.9", "system": "iPadOS 18", "use_days": 60}
]

3.9.4 Json中也可以包含数组

{
    "devices":
 	[
        {"device_type": "手机", "brand": "华为", "model": "Mate 60 Pro", "system": "HarmonyOS 4.2", "use_days": 205},
     	{"device_type": "电脑", "brand": "联想", "model": "拯救者Y9000P", "system": "Windows 11", "use_days": 310}
    ]
}

3.9.5 练习

-- 提取每个用户第一个设备的品牌和型号
SELECT
  user_name,
  user_id,
  -- -> 提取JSON原始值（带引号），->> 提取无引号的字符串值
  device_detail->'$.devices[0].brand' AS device_brand,
  device_detail->>'$.devices[0].model' AS device_model
FROM t_user_device;

--  查询所有使用苹果手机的用户
SELECT * FROM t_user_device WHERE device_detail->'$.devices[*].brand' LIKE '%苹果%' AND device_detail->'$.devices[*].device_type' LIKE '%手机%';

--  提取用户张三的手机型号
SELECT user_name, device_detail->'$.devices[0].model' AS phone_model FROM t_user_device WHERE user_name = '张三';

-- 查询使用HarmonyOS（鸿蒙系统）设备的用户
SELECT * FROM t_user_device WHERE JSON_CONTAINS(device_detail->'$.devices[*].system', '"HarmonyOS"');

3.9.6 核心json函数(了解即可)

函数作用常用场景
->提取 JSON 值，保留原始格式（字符串带引号、数字无引号）简单取值、条件判断
->>提取 JSON 值并转换为无引号的字符串结果展示、字段拼接
JSON_CONTAINS判断 JSON 文档中是否包含指定值精准包含查询
JSON_EXTRACT通用 JSON 值提取，等价于 ->，支持复杂路径（如条件筛选）复杂 JSON 结构取值
JSON_LENGTH计算 JSON 数组 / 对象的元素个数统计数组长度、判断数量
JSON_TABLE将 JSON 数组转换为关系型虚拟表，支持多字段提取聚合计算、分组统计（MySQL8.0+）
JSON_SEARCH查找 JSON 中指定值的路径精准定位 JSON 值的位置
avatar
文章
阅读
粉丝