山西农业大学20240906

Jia_dongyao

已于 2024-09-09 17:01:07 修改

阅读量1.3k

点赞数 19

分类专栏：山西农业大学文章标签： java 数据库 sql

于 2024-09-06 11:32:28 首次发布

本文链接：https://blog.csdn.net/2401_86991268/article/details/141949325

版权

山西农业大学专栏收录该内容

13 篇文章 2 订阅

订阅专栏

08-数据库- 续

- 一. DQL基本查询(续)

一. DQL基本查询(续)

DQL用来检索表中数据的语言, 涉及到的关键词SELECT

语法

---------------------------------执行顺序
SELECT 子句------------------- 6
FROM 子句 ------------------- 1
JOIN … ON… 子句 ------------------- 2
WHERE 子句 ------------------- 3
GROUP BY 子句 ------------------- 4
HAVING 子句 ------------------- 5
ORDER BY 子句 ------------------- 7
LIMIT 子句 ------------------- 8

2. DQL高级查询

2.1 LIKE模糊查询

LIKE 有两个可用的通配符

_: 表示一个字符, 确切的数量表示1个字符
%: 表示任意个字符, 不确切的数量表示 0-任意个字符

格式

LIKE ‘%X%’ : 表示字符串中包含X, eg: ‘aXa’, ‘X’,‘aaXbb’
LIKE ‘_X%’: 表示字符串中第二个字符是X, eg: ‘aX’, ‘aXbb’, ‘aXb’
LIKE ‘X%’: 表示字符串以X开始
LIKE ‘%X’: 表示字符串以X结束
LIKE ‘%X_Y’ : 表示字符串倒数第三个字符是X并且最后一个字符是Y

# 模糊查询
# 查看名字中苍的老师的姓名?
SELECT name,title
FROM teacher
WHERE name LIKE '%苍%';
# 查看姓张的学生都有谁?
SELECT name,gender
FROM student
WHERE name LIKE '张%';
# 查看三个字名字中, 第二个字是 '平'的学生都有谁?
SELECT name,gender
FROM student
WHERE name LIKE '_平_';
# 查看最后一个字是 '晶'的老师都有谁?
SELECT name
FROM teacher
WHERE name LIKE '%晶';
# 查看哪些学生是课代表? 列出他的名字和职位
SELECT name,job
FROM student
WHERE job LIKE '%课代表';
# 查看所有的2班在哪层?
SELECT name,floor
FROM class
WHERE name LIKE '%2班';

2.2 取别名(AS)

语法格式: 字段名 <空格> 别名
别名通常使用在SELECT语句和FROM语句中

在SELECT子句中我们为字段取别名
— 当字段是函数或表达式时,我们通常会给字段添加别名, 增加可读性
—为SELECT中子查询取别名
在FROM子句中为表添加别名

# 取别名
# 为字段重命名
# 查询所有老师的姓名,工资, 和年薪
SELECT name,salary,salary*12 AS annual FROM teacher;
# 简写: 取别名时AS可以省略
SELECT name,salary,salary*12 annual FROM teacher;

# 为表重命名
# 简写: 取别名时AS可以省略
SELECT t.name,t.salary,salary*12 annual
FROM teacher t;

2.3 ORDER BY 排序

ORDER BY子句用于对查询结果集进行排序, 可以按照ORDER BY 后指定的字段进行升序和降序排序

升序: ORDER BY 字段 [ASC] (默认值)
将当前结果集按照指定的字段值从小到大排序

降序: ORDER BY 字段 DESC
将当前结果集按照指定的字段值从大到小排序

ORDER BY可以按照多个字段排序,此时排序存在优先级
– 首先按照ORDER BY 后指定的第一个字段将结果集按照该字段排序方式(升序或降序)排序
– 排序后再按照第二个字段指定的值排序, 仅会对第一个字段值相同的记录按照第二个字段排序
– 若有第三个字段以此类推

不指定排序方式时,默认升序

# ORDER BY
# 查看老师的工资排名, 从多到少
SELECT name,salary FROM teacher
ORDER BY salary DESC;   # 降序

# 查看老师的奖金排名
SELECT name,comm FROM teacher
ORDER BY comm;

# 查看学生的生日, 按照从远到近 2024 -- 2010,2015(2010离得远)
# 日期是可以比较大小的, 规则 "远小近大"
SELECT name,birth FROM student
ORDER BY birth ASC;

# 查看7-10岁的学生信息,学生按照年龄从大到小排序(同年龄看生日)
SELECT name,age,birth FROM student
ORDER BY birth ASC;
# 查看老师的工资和奖金,首先按照奖金的升序, 再按照工资降序
SELECT name,salary,comm FROM teacher
ORDER BY comm ASC,salary DESC;

2.4 LIMIT 分页查询

LIMIT 子句用于限制由SELECT语句返回的数据数量,或者UPDATE, DELETE语句的操作数量

2.4.1 使用场景

当一条SQL语可以查询出非常大量的数据, 我们可以分批将数据查询, 例如京东检索商品时,分出第一页,第二页等信息,每页展示30条数数据, 一次只会查询30条数据, 避免因为数据量庞大导致系统过度资源开销

2.4.2 语法说明

SELECT 子句
FROM 子句
WHERE 子句
ORDER BY 子句
LIMIT M,N

字段说明

LIMIT M,N

M: 表示跳过结果集M条记录
N: 表示从跳过的M条记录后, 连续检索N条数据
参数
页数 : 当前显示第几页的内容
条目数: 每页显示的条目数

分页公式

M: (页数-1)*条目数
N: 条目数

LIMIT 0,30 ----- 第1页数据 M: 0 —(1-1)*30
LIMIT 30,30 ----- 第2页数据 M:30 —(2-1)*30
LIMIT 60,30 ----- 第3页数据 M:60 —(3-1)*30
…
每页显示5条记录, 显示第8页的数据: LIMIT 35,5
每页显示20条记录, 显示第6页的数据: LIMIT 100,20

# LIMIT 分页查询
# 查看老师工资的前5名
SELECT name,salary FROM teacher
ORDER BY salary DESC LIMIT 0,5;
# 查看老师的奖金信息, 按照降序排序后,每页显示3条,显示第5页
SELECT name,salary,comm FROM teacher
ORDER BY comm DESC LIMIT 12,3;

2.4.3 综合练习

# 1. 查询所有10岁学生的生日,按生日对应的年纪从大到小.
SELECT name,age,birth FROM student
WHERE age=10 ORDER BY birth ASC;
# 2. 查询8岁同学中名字含有"苗"的学生信息
SELECT name,age FROM student
WHERE age=8 AND name LIKE '%苗%';
# 3. 查询负责课程编号1和2号且工资高于6000的老师信息
SELECT name,salary,subject_id FROM teacher
WHERE subject_id IN(1,2) AND salary>6000;
# 4. 查询10岁以上的语文课代表和数学课代表
SELECT name,job,age FROM student
WHERE age=10 AND job IN ('语文课代表','数学课代表');
# 5. 查询不教课程编号1的老师信息,按照工资降序排序
SELECT name,salary,subject_id FROM teacher
WHERE subject_id != 1
ORDER BY salary DESC;
# 6. 查询所有老师的奖金，并按照奖金降序排序
SELECT name,comm FROM teacher
ORDER BY comm DESC;
# 7. 查看工资高于8000的老师负责的课程编号都有那些?
SELECT name,subject_id,salary FROM teacher
WHERE salary>8000;
# 8. 查看全校年龄最小学生的第11-15名
SELECT name,age,birth FROM student
ORDER BY birth DESC
LIMIT 0,5;

3. 聚合查询

聚合查询在数据查找的基础上, 对数据做进一步的整理筛选工作, 实际聚合操作也属于数据查询的范围

3.1 聚合函数

方法	功能
avg(字段名)	该字段的平均值
max(字段名)	该字段的最大值
min(字段名)	该字段的最小值
sum(字段名)	该字段所有记录的和
count(字段名)	统计该字段记录的个数

注意: MIN,MAX,AVG,SUM 都是对值统计, COUNT是对记录的操作

# 聚合函数
# 查看老师的平均工资
SELECT AVG(salary) avgsal FROM teacher;
# 查看老师的最高总工资,最低工资,平均工资和工资总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM teacher;
# 查看负责课程编号为1的老师的平均薪资
SELECT AVG(salary) FROM teacher WHERE subject_id=1;
# 查看总共有多少名老师
SELECT COUNT(name) FROM teacher; # 统计name字段值不为null的记录共有多少条
SELECT COUNT(comm) FROM teacher; # 因为comm字段有null值,聚合函数会忽略对null的统计
SELECT COUNT(*) FROM teacher;

3.2 GROUP BY 分组

GROUP BY 子句可以将结果集按照指定字段值相同的记录进行分组, 配合聚合函数实现组内统计

在SELECT子句中出现聚合函数时, 那么不在聚合函数中的字段都要出现在GROUP子句中
GROUP BY子句是配合聚合函数使用的, 如果SELECT子句中没有聚合函数,通常不写GROUP BY

3.2.1 单字段分组

在这里插入图片描述

# 1.单字段分组
SELECT name,salary,title FROM teacher ORDER BY title;
# 查看每种职位(一级讲师,二级讲师,三级讲师)的老师的平均工资是多少
SELECT AVG(salary),title FROM teacher
GROUP BY title;

# 查看每个班级有多少人?
# 班级id一样的应该是同一个班的学生,因为可以按照班级号相同的记录进行分组,在组内求记录
SELECT COUNT(*),class_id FROM student
GROUP BY class_id;


# 查看学生每种职位有多少人, 以及学生的最大生日和最小生日
SELECT COUNT(*) '人数',job,MIN(birth) '最大生日',MAX(birth) '最小生日'
FROM student
GROUP BY job;

3.2.2 多字段分组

GROUP BY后面指定多个字段时,分组方式为: 这些字段值都一样的记录看做一个组

# 多字段分组
# 查看同班级同性别的学生分别有多少人?
SELECT COUNT(*),class_id,gender FROM student
GROUP BY class_id, gender; # 班级号相同且性别相同的记录分为一组

# 查看每个班每种职位共多少人?
SELECT COUNT(*),class_id,job FROM student
GROUP BY class_id, job;

3.2.3 分组聚合排序

# 查看每个科目老师的平均工资降序排列
SELECT AVG(salary) avg_sal,subject_id FROM teacher
GROUP BY subject_id
ORDER BY avg_sal DESC;

3.3 HAVING 子句

HAVING子句用于分组中的过滤条件

3.3.1 HAVING子句

HAVING子句是紧跟在GROUP BY 子句之后, 用于对分组进行过滤的子句

# 查看每个科目的老师的平均薪资, 但是仅查看平均薪资高于6000的那些
SELECT AVG(salary),subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id;
# 错误: 聚合函数不能再WHERE子句中使用
# 修改
SELECT AVG(salary),subject_id   # 4. 查询到符合要求的分组统计对应信息
FROM teacher                    # 1. 数据来源,数据从teacher表中获取的
GROUP BY subject_id             # 2. 确定分组,按照科目分组
HAVING AVG(salary)>6000;        # 3. 过滤分组,比如只有2组符合条件

# 查看每个科目老师的平均信息, 前提是该科目老师最高工资要超过9000
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
HAVING MAX(salary)>9000;

3.3.2 HAVING子句与WHERE子句的区别

WHERE子句在第一次检索表数据时用于添加过滤条件,确定结果集
HAVING子句是在GROUP BY之后(奖结果集分组之后)添加过滤条件的,用来确定的分组

3.4 DISTINCT去重

DISTINCT可以将结果集按照指定的字段去除重复行
DISTINCT必须紧跟在SELECT关键字之后

# 查看老师的职称有哪些?
SELECT DISTINCT title FROM teacher;

# 查看学生有哪些职位?
SELECT DISTINCT job FROM student;

# 查看各年龄段学生都有哪些职位?
SELECT DISTINCT age,job FROM student;
# 当age和job值相同的记录会被去除, 8岁大队长可能有4个,但是仅仅保留一个