4、DQL数据查询下

最新推荐文章于 2024-09-02 13:43:09 发布

言叶.

最新推荐文章于 2024-09-02 13:43:09 发布

阅读量93

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_43856453/article/details/115717452

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

4.4、联表查询

join对比

操作符名称	描述
INNER JOIN	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表中返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表中返回所有的行

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pbFmeVmv-1618059745835)(E:\学习\Topora\MySQL\image-20210409154049293.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JonxJWOr-1618059745837)(E:\学习\Topora\MySQL\image-20210409154106931.png)]$

测试

/*连接查询
	如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join 
    查询两个表中的结果集中的交集 
外连接 outer join 
	左外连接 left join 
		(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充) 
	右外连接 right join 
		(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
        
等值连接和非等值连接
自连接 
*/

-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数) 
SELECT * FROM student; SELECT * FROM result; 
/*思路: 
(1):分析需求,确定查询的列来源于两个类,student result,连接查询 
(2):确定使用哪种连接查询?(内连接) 
*/
SELECT s.studentno,studentname,subjectno,StudentResult 
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno

-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult 
FROM student s 
RIGHT JOIN result r 
ON r.studentno = s.studentno 

-- 等值连接 
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r 
WHERE r.studentno = s.studentno 

-- 左连接 (查询了所有同学,不考试的也会查出来) 
SELECT s.studentno,studentname,subjectno,StudentResult 
FROM student s 
LEFT JOIN result r 
ON r.studentno = s.studentno 

-- 查一下缺考的同学(左连接应用场景) 
SELECT s.studentno,studentname,subjectno,StudentResult 
FROM student s 
LEFT JOIN result r 
ON r.studentno = s.studentno 
WHERE StudentResult IS NULL 

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数) 
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub 
ON sub.subjectno = r.subjectno

自连接

核心思路：一张表拆为两张一样的表即可

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应子类关系

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

/*
	自连接
	数据表与自身进行连接 
	
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中 
	查询父栏目名称和其他子栏目名称 
*/
-- 创建一个表 
CREATE TABLE `category` (
    `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', 
    `pid` INT(10) NOT NULL COMMENT '父id', 
    `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', 
    PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 

-- 插入数据 
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`) 
VALUES('2','1','信息技术'), 
('3','1','软件开发'),
('4','3','数据库'), 
('5','1','美术设计'), 
('6','3','web开发'), 
('7','5','ps技术'),
('8','2','办公信息'); 

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接) 
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目' 
FROM category AS a,category AS b 
WHERE a.`categoryid`=b.`pid` 
-- 第二种
SELECT c1.categoryname as 父类,c2.categoryname as 子类
FROM category as c1
INNER JOIN category as c2
ON c1.categoryid = c2.pid

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数) 
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub 
ON sub.subjectno = r.subjectno 


-- 查询 高等数学-1 的所有考试结果(学号 学生姓名 科目名称 成绩) 
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub 
ON r.subjectno = sub.subjectno 
WHERE subjectname='高等数学-1'

自连接

/*
自连接
	数据表与自身进行连接
    
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中 
	查询父栏目名称和其他子栏目名称 
*/
-- 创建一个表 
CREATE TABLE `category` ( 
    `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id', 
    `pid` INT(10) NOT NULL COMMENT '父id', 
    `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字', 
    PRIMARY KEY (`categoryid`) 
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 
-- 插入数据 
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`) 
VALUES('2','1','信息技术'), 
('3','1','软件开发'),
('4','3','数据库'), 
('5','1','美术设计'), 
('6','3','web开发'), 
('7','5','ps技术'), 
('8','2','办公信息'); 

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称) 
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接) 

SELECT c1.categoryname as 父类,c2.categoryname as 子类
FROM category as c1
INNER JOIN category as c2
ON c1.categoryid = c2.pid

SELECT c1.categoryname as 父类,c2.categoryname as 子类
FROM category as c1,category as c2
WHERE c1.categoryid = c2.pid


-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,studentresult
FROM result as r
RIGHT JOIN student as s
ON s.studentno = r.studentno
INNER JOIN `subject` as sub
ON r.subjectno = sub.subjectno

-- 查询学员及所属的年级(学号,学生姓名,年级名) 
SELECT studentno,studentname,gradename
FROM student as s
INNER JOIN grade as g
ON s.gradeid = g.gradeid

-- 查询科目及所属的年级(科目名称,年级名称) 
SELECT subjectname,gradename
FROM `subject` as s
Inner JOIN grade as g
ON s.gradeid = g.gradeid

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩) 
SELECT s.studentno,studentname,subjectname,studentresult
FROM result as r
RIGHT JOIN student as s
ON s.studentno = r.studentno
INNER JOIN `subject` as sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'

4.5、排序和分页

测试

/*============== 排序 ================ 
语法 : ORDER BY 
	ORDER BY 语句用于根据指定的列对结果集进行排序。 
	ORDER BY 语句默认按照ASC升序对记录进行排序。 
	如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。 
*/

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩) 
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult 
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub 
ON r.subjectno = sub.subjectno 
WHERE subjectname='数据库结构-1' 
ORDER BY StudentResult DESC 

/*============== 分页 ================ 
语法 : SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 
好处 : (用户体验,网络传输,查询压力) 

推导:
	第一页 : limit 0,5 
	第二页 : limit 5,5 
	第三页 : limit 10,5 
	...... 
	第N页 : limit (pageNo-1)*pageSzie,pageSzie 
	[pageNo:页码,pageSize:单页面显示条数] 
*/

-- 每页显示5条数据 
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno 
WHERE subjectname='数据库结构-1' 
ORDER BY StudentResult DESC , studentno 
LIMIT 0,5 

-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数) 
SELECT s.studentno,studentname,subjectname,StudentResult 
FROM student s 
INNER JOIN result r 
ON r.studentno = s.studentno 
INNER JOIN `subject` sub 
ON r.subjectno = sub.subjectno 
WHERE subjectname='JAVA第一学年' 
ORDER BY StudentResult DESC 
LIMIT 0,10

4.6、子查询

/*============== 子查询 ================ 
什么是子查询? 
	在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句 
	嵌套查询可由多个子查询组成,求解的方式是由里及外; 
	子查询返回的结果一般都是集合,故而建议使用IN关键字; 
*/

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询 
ELECT studentno,r.subjectno,StudentResult 
FROM result r 
INNER JOIN `subject` sub 
ON r.`SubjectNo`=sub.`SubjectNo` 
WHERE subjectname = '数据库结构-1' 
ORDER BY studentresult DESC; 

-- 方法二:使用子查询(执行顺序:由里及外) 
SELECT studentno,subjectno,StudentResult 
FROM result 
WHERE subjectno=( 
    SELECT subjectno 
    FROM `subject` 
    WHERE subjectname = '数据库结构-1' 
)
ORDER BY studentresult DESC; 

-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名 
-- 方法一:使用连接查询 
SELECT s.studentno,studentname 
FROM student s 
INNER JOIN result r 
ON s.`StudentNo` = r.`StudentNo` 
INNER JOIN `subject` sub 
ON sub.`SubjectNo` = r.`SubjectNo` 
WHERE subjectname = '高等数学-2' 
AND StudentResult>=80 

-- 方法二:使用连接查询+子查询 
-- 分数不小于80分的学生的学号和姓名 
SELECT r.studentno,studentname 
FROM student s 
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo` 
WHERE StudentResult>=80 

-- 在上面SQL基础上,添加需求:课程为 高等数学-2 
SELECT r.studentno,studentname 
FROM student s 
INNER JOIN result r 
ON s.`StudentNo`=r.`StudentNo` 
WHERE StudentResult>=80 AND subjectno=( 
    SELECT subjectno 
    FROM `subject` 
    WHERE subjectname = '高等数学-2' 
)

-- 方法三:使用子查询 
-- 分步写简单sql语句,然后将其嵌套起来 
SELECT studentno,studentname 
FROM student 
WHERE studentno IN( 
    SELECT studentno 
    FROM result 
    WHERE StudentResult>=80 
    AND subjectno=( 
        SELECT subjectno 
        FROM `subject` 
        WHERE subjectname = '高等数学-2' 
    ) 
)

/*练习题目: 
	查 C语言-1 的前5名学生的成绩信息(学号,姓名,分数) 
	使用子查询,查询郭靖同学所在的年级名称 
*/

4.7、分组以及过滤

-- 查询不同课程的平均分，最高分，最低分
SELECT subjectname as 科目,AVG(studentresult) as 平均分, MAX(studentresult) as 最高分,MIN(studentresult) as 最低分
FROM result r
INNER JOIN `subject` s
ON s.subjectno=r.subjectno
GROUP BY r.subjectno
HAVING 平均分>60
/*
where写在group by前面. 
要是放在分组后面的筛选 
要使用HAVING..
因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的 
*/

言叶.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
4、DQL数据查询下

4.4、联表查询join对比操作符名称描述INNER JOIN如果表中有至少一个匹配，则返回行LEFT JOIN即使右表中没有匹配，也从左表中返回所有的行RIGHT JOIN即使左表中没有匹配，也从右表中返回所有的行测试/*连接查询如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询内连接 inner join 查询两个表中的结果集中的交集外连接 outer join 左外连接 left join (以左表作为
复制链接

扫一扫