数据库应用——DQL查询数据（连表查询子查询详细案例）

2021-04-12 01:22 420人阅读评论(0)

DQL查询数据

DQL
连表查询（重要）
- 三种join的比较
- 连表查询练习
自连接
分页和排序
子查询
分组和过滤

DQL

(Data Query LANGUAGE：数据查询语言)

所有的查询操作都用它 Select
简单的查询，复杂的查询它都能用
数据库中最核心的语言，最重要的语句
使用频率最高的语句

Select完整的语法

SELECT [ALL | DISTINCT]
{
  * | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2]	 -- 联合查询
	[WHERE ...]		-- 指定结果需满足的条件
	[GROUP BY ...]	-- 指定结果按照哪几个字段来分组
	[HAVING]	-- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...]	-- 指定查询记录按一个或多个条件排序
	[LIMIT {
  [offset,]row_count | row_countOFFSET offset}];	-- 指定查询的记录从哪条至哪条

注意：[]里代表可选，{}里代表必选

练习用的sql脚本

“
CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `gender` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

”
“
-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

”

“

-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`loginpwd`,`studentname`,`gender`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','唐嘉','女',2,'13800001234','北京朝阳','1980-5-2','text123@qq.com','123456198001011298'),
(1001,'123456','刘美玲','女',1,'13800002222','广东深圳','1991-6-4','text111@qq.com','123456199001011191'),
(1002,'123456','潘辰','男',1,'13800002111','四川成都','1992-7-6','text222@qq.com','123456199001011333'),
(1003,'123456','小田','男',2,'13800002333','四川成都','1993-8-8','text333@qq.com','123456199001011555'),
(1004,'123456','胡杨','男',3,'13800002555','浙江杭州','1994-9-10','text444@qq.com','123456199001011777'),
(1005,'123456','猫猫','女',2,'13800002666','日本东京','1995-1-12','text555@qq.com','123456199001011888'),
(1006,'123456','飞飞','男',2,'13800002666','上海浦东','1997-7-12','text666@qq.com','123456199001011666');


-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,5,'2013-11-12 16:00:00',70),
(1000,9,'2013-11-11 09:00:00',68),
(1000,13,'2013-11-13 16:00:00',98),
(1000,17,'2013-11-14 16:00:00',58),
(1001,1,'2013-11-11 16:00:00',90),
(1001,5,'2013-11-12 16:00:00',88),
(1001,9,'2013-11-11 09:00:00',79),
(1001,13,'2013-11-13 16:00:00',90),
(1001,17,'2013-11-14 16:00:00',83),
(1002,1,'2013-11-11 16:00:00',90),
(1002,5,'2013-11-12 16:00:00',88),
(1002,9,'2013-11-11 09:00:00',79),
(1002,13,'2013-11-13 16:00:00',90),
(1002,17,'2013-11-14 16:00:00',83),
(1003,1,'2013-11-11 16:00:00',10),
(1003,5,'2013-11-12 16:00:00',28),
(1003,9,'2013-11-11 09:00:00',49),
(1003,13,'2013-11-13 16:00:00',60),
(1003,17,'2013-11-14 16:00:00',55),
(1004,1,'2013-11-11 16:00:00',25),
(1004,5,'2013-11-12 16:00:00',68),
(1004,9,'2013-11-11 09:00:00',99),
(1004,13,'2013-11-13 16:00:00',70),
(1004,17,'2013-11-14 16:00:00',45),
(1005,1,'2013-11-11 16:00:00',100),
(1005,5,'2013-11-12 16:00:00',98),
(1005,9,'2013-11-11 09:00:00',99),
(1005,13,'2013-11-13 16:00:00',90),
(1005,17,'2013-11-14 16:00:00',95),
(1006,1,'2013-11-11 16:00:00',75),
(1006,5,'2013-11-12 16:00:00',68),
(1006,9,'2013-11-11 09:00:00',69),
(1006,13,'2013-11-13 16:00:00',77),
(1006,17,'2013-11-14 16:00:00',75);


-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

”

“

-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

”

指定查询字段

-- 查询全部的学生   SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM studnet

-- 别名，给结果起一个名字  AS
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS s

-- 函数 Concat(a,b)
mysql [(none)]>select concat('姓名:' ,studentname) as '学生名字' from school.student;
+------------------+
| 学生名字         |
+------------------+
| 姓名:唐嘉        |
| 姓名:刘美玲      |
| 姓名:潘辰        |
| 姓名:小田        |
| 姓名:胡杨        |
| 姓名:猫猫        |
| 姓名:飞飞        |
+------------------+
7 rows in set (0.00 sec)

语法：SELECT 字段,... FROM 表

有的时候，列名字不是那么的简明知意。我们起别名 AS 字段名 as 别名表名 as 别名

去重distinct

作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
select * from result 	-- 查询全部的考试成绩
SELECT `StudentNo` FROM result	-- 查询有哪些同学参加了考试

-- 发现重复数据，我们可以去重
SELECT DISTINCT `StudentNo` FROM result

数据库的列

SELECT VERSION()	-- 查看系统版本
SELECT 100*5 AS 计算结果	-- 用来计算
SELECT @@auto_increment_increment 	-- 查询自增的步长

-- 学员考试成绩 +1分查看
SELECT `StudentNo`,`studentresult`+1 AS '提分后' FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…

语法：select 表达式 from 表

where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成！结果是布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个都为真，结果为真
or \|\|	a or b a\|\|b	逻辑或，其中一个为真，结果为真
Not !	not a ! a	逻辑非，真为假，假为真取反

尽量使用英文字母

-- ==============  where  ===============
SELECT studentNo,`studentresult` FROM result

-- 查询考试成绩在 95~~100分之间的
SELECT studentNo,`studentresult` FROM result
WHERE studentresult>=95 AND studentresult<=100

-- and &&
SELECT studentNo,`studentresult` FROM result
WHERE studentresult>=95 && studentresult<=100

-- 模糊查询（区间）
SELECT studentNo,`studentresult` FROM result
WHERE studentresult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`studentresult` FROM result
WHERE studentNo!=1000

-- !=  not
SELECT studentNo,`studentresult` FROM result
WHERE NOT studentNo = 1000

模糊查询比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为NULL，结果为真
IS NOT NULL	a is not null	如果操作符不为null，结果为真
BETWEEN	a between b and c	若a在b和c之间，结果为真
Like	a like b	SQL 匹配，如果a能够匹配b，结果为真
In	a in (a1,a2,a3…)	假设a在a1，或者a2… 其中的某一个值中，结果为真

-- ===============  模糊查询  =================
-- 查询小田同学
-- like结合 %(代表0到任意个字符)  _(代表一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%田'

-- 查询姓田的同学，名字前面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '_田'

-- 查询三个字名字的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '___'

-- 查询名字中间有猫字的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%猫%'

-- =======================  in (具体的一个或者多个值)  ==========================================
-- 查询1001，1002，1003号学员
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1001,1002,1003);

-- 查询在四川成都和日本东京的学生
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN ('四川成都','日本东京');

+-----------+-------------+
| studentno | studentname |
+-----------+-------------+
|      1002 | 潘辰        |
|      1003 | 小田        |
|      1005 | 猫猫        |
+-----------+-------------+
3 rows in set (0.00 sec)

-- ==============  null  not null ================
-- 查询地址为空的学生 null  ''
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL;

-- 查询有出生日期的同学   不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询没有出生日期的同学  为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL

连表查询（重要）

JOIN 对比

-- =============  连表查询 join ================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/* 思路
1.分析需求,分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的StudentNo = 成绩表 StudentNo
*/

-- INNER Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno;

select 
s.studentno as '学号',studentname as '学生姓名',subjectno as '学号',studentresult as '成绩'
from student s 
inner join result r 
on s.studentno = r.studentno;

-- Right Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno;

-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno;


mysql [school]>select 
    -> s.studentno as '学号',studentname as '学生姓名',subjectno as '考试编号',studentresult as '
成绩'    -> from student s 
    -> inner join result r 
    -> on s.studentno = r.studentno;
+--------+--------------+--------+--------+
| 学号   | 学生姓名     | 考试编号   | 成绩   |
+--------+--------------+--------+--------+
|   1000 | 唐嘉         |      1 |     85 |
|   1000 | 唐嘉         |      5 |     70 |
|   1000 | 唐嘉         |      9 |     68 |
|   1000 | 唐嘉         |     13 |     98 |
|   1000 | 唐嘉         |     17 |     58 |
|   1001 | 刘美玲       |      1 |     90 |
|   1001 | 刘美玲       |      5 |     88 |
|   1001 | 刘美玲       |      9 |     79 |
|   1001 | 刘美玲       |     13 |     90 |
|   1001 | 刘美玲       |     17 |     83 |
|   1002 | 潘辰         |      1 |     90 |
|   1002 | 潘辰         |      5 |     88 |
|   1002 | 潘辰         |      9 |     79 |
|   1002 | 潘辰         |     13 |     90 |
|   1002 | 潘辰         |     17 |     83 |
|   1003 | 小田         |      1 |     10 |
|   1003 | 小田         |      5 |     28 |
|   1003 | 小田         |      9 |     49 |
|   1003 | 小田         |     13 |     60 |
|   1003 | 小田         |     17 |     55 |
|   1004 | 胡杨         |      1 |     25 |
|   1004 | 胡杨         |      5 |     68 |
|   1004 | 胡杨         |      9 |     99 |
|   1004 | 胡杨         |     13 |     70 |
|   1004 | 胡杨         |     17 |     45 |
|   1005 | 猫猫         |      1 |    100 |
|   1005 | 猫猫         |      5 |     98 |
|   1005 | 猫猫         |      9 |     99 |
|   1005 | 猫猫         |     13 |     90 |
|   1005 | 猫猫         |     17 |     95 |
|   1006 | 飞飞         |      1 |     75 |
|   1006 | 飞飞         |      5 |     68 |
|   1006 | 飞飞         |      9 |     69 |
|   1006 | 飞飞         |     13 |     77 |
|   1006 | 飞飞         |     17 |     75 |
+--------+--------------+--------+--------+
35 rows in set (0.01 sec)

三种join的比较

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

-- =============  连表查询 join ==============
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/* 思路
1.分析需求,分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的StudentNo = 成绩表 StudentNo
*/

-- join （连接的表）on（判断的条件）连接查询
-- where  等值查询

SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- Right Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno

-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON s.studentno = r.studentno
WHERE studentresult IS NULL

select s.studentno,studentname,subjectno,studentresult
from result r
right join student s
on s.studentno = r.studentno
where studentresult is null;

连表查询练习

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
/* 思路
1.分析需求,分析查询的字段来自哪些表，student、result、subject（连接查询）
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的StudentNo = 成绩表 StudentNo
*/

SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 FROM 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加

-- From a left join b
-- From a right join b

select  
s.studentno as '学号',studentname as '学生姓名',subjectname as '科目名',studentresult as '成绩' from student s 
inner join result r 
on r.studentno = s.studentno 
inner join subject sub 
on r.subjectno = sub.subjectno;

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

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','办公信息');

父类

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

子类

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

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

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

-- 查询父子信息:把一张表看为两个一模一样的表
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

分页和排序

排序

-- ================= 分页 limit 和排序 order by ===============

-- 排序：升序ASC, 降序DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果 根据成绩降序 排序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '高等数学-1'
-- ORDER BY studentresult ASC 升序
ORDER BY studentresult DESC -- 降序

+--------+-----------+----------------+--------+
| 学号   | 姓名      | 科目名         | 成绩   |
+--------+-----------+----------------+--------+
|   1005 | 猫猫      | 高等数学-1     |    100 |
|   1001 | 刘美玲    | 高等数学-1     |     90 |
|   1002 | 潘辰      | 高等数学-1     |     90 |
|   1000 | 唐嘉      | 高等数学-1     |     85 |
|   1006 | 飞飞      | 高等数学-1     |     75 |
|   1004 | 胡杨      | 高等数学-1     |     25 |
|   1003 | 小田      | 高等数学-1     |     10 |
+--------+-----------+----------------+--------+
7 rows in set (0.00 sec)

分页

-- 分页
-- 缓解数据库压力，给人的体验更好，瀑布流
-- 分页，每页只显示五条数据
-- 语法：limit 起始值，页面的大小
-- 网页应用：当前，总的页数，页面的大小

-- LIMIT 0,5	1~5条数据
-- LIMIT 1,5	2~6
-- LIMIT 6,5
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '高等数学-1'
ORDER BY studentresult ASC
LIMIT 5,5

-- 第一页 limit 0,5	(1-1)*5
-- 第二页 limit 5,5	(2-1)*5
-- 第三页 limit 10,5	(3-1)*5
-- 第N页 limit 0,5	(n-1)*pageSize，pageSize
-- [pageSize：页面大小]
-- [(n-1)* pageSize:起始值]
-- [n:当前页]
-- [数据总数/页面大小 = 总页数]

语法：

limit(查询起始下标，pageSize)

练习：

-- 查询 JAVA第一学年 课程成绩排名前十的学生，并且分数要大于49的学生信息(学号，姓名，课程名称，分数)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE subjectname = 'Java程序设计-1' AND studentresult>=49
ORDER BY studentresult DESC
LIMIT 0,10

子查询

where（这个值是计算出来的）

本质：在where语句中嵌套一个子查询语句

-- =================== 子查询 =================
-- 1、查询Java程序设计-1 的所有考试结果（学号，科目名称，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = 'Java程序设计-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询（由里及外）
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE subjectno = (
	SELECT subjectno FROM `subject` 
	WHERE subjectname = 'Java程序设计-1'
)
ORDER BY studentresult DESC

-- 查询所有Java程序设计-1 的学生学号
SELECT subjectno FROM `subject` WHERE subjectname = 'Java程序设计-1'


-- 查询课程为 高等数学-1 且分数不小于 80 的同学的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1' AND `studentresult` >= 80

-- 查询分数不小于49分的学生的学号和姓名	
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE `studentresult` >= 49

-- 在这个基础上增加一个科目，高等数学-1  
-- 查询 高等数学-1 的编号
SELECT DISTINCT s.`studentno`,`studentname`,`studentresult`
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE `studentresult`>=49 AND `subjectno` = (
  SELECT subjectno FROM `subject`
  WHERE `subjectname` = '高等数学-1'
)


-- 嵌套再改造（由里及外）
SELECT studentno,studentname 
from student WHERE studentno IN (
   SELECT studentno FROM result WHERE studentresult>80 AND subjectno = (
	SELECT subjectno FROM `subject` WHERE `subjectname` = '高等数学-1'
   )
)


-- 练习 查询 高等数学-1 前5名同学的信息（学号，姓名，分数）
-- 先使用连表查询

SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '高等数学-1'
ORDER BY `studentresult` DESC
LIMIT 0,5

-- 然后使用子查询
SELECT s.`studentno`,`studentname`,`studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE `subjectno` = (
  SELECT `subjectno` FROM `subject`
  WHERE `subjectname` = '高等数学-1'
)
ORDER BY `studentresult` DESC
LIMIT 0,5

+-----------+-------------+---------------+
| studentno | studentname | studentresult |
+-----------+-------------+---------------+
|      1005 | 猫猫        |           100 |
|      1001 | 刘美玲      |            90 |
|      1002 | 潘辰        |            90 |
|      1000 | 唐嘉        |            85 |
|      1006 | 飞飞        |            75 |
+-----------+-------------+---------------+
5 rows in set (0.00 sec)

练习

-- 练习 查询 C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 连表查询
select s.studentno,studentname,studentresult 
from student s 
inner join result r 
on s.studentno = r.studentno 
inner join subject sub 
on r.subjectno = sub.subjectno 
where subjectname = 'C语言-1' 
order by studentresult desc;

-- 子查询
select s.studentno,studentname,studentresult 
from student s 
inner join result r 
on s.studentno = r.studentno 
where subjectno = ( 
    select subjectno from subject where subjectname = 'C语言-1'
) 
order by studentresult desc;

分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：根据不同的课程分组
SELECT any_value(`subjectname`) AS 学科名,AVG(studentresult) AS 平均分,MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.subjectno	-- 通过什么字段来分组

+--------------------------+-----------+-----------+-----------+
| 学科名					   | 平均分     | 最高分    | 最低分    |
+--------------------------+-----------+-----------+-----------+
| 高等数学-1               |   67.8571 |       100 |        10 |
| C语言-1                  |   72.5714 |        98 |        28 |
| Java程序设计-1           |   77.4286 |        99 |        49 |
| 数据库结构-1             |   82.1429 |        98 |        60 |
| C基础                   |   70.5714 |        95 |        45 |
+--------------------------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
SELECT any_value(`subjectname`),AVG(studentresult) AS 平均分,MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.subjectno	-- 通过什么字段来分组
HAVING 平均分>80

+--------------------------+-----------+-----------+-----------+
| any_value(`subjectname`) | 平均分    | 最高分    | 最低分    |
+--------------------------+-----------+-----------+-----------+
| 数据库结构-1             |   82.1429 |        98 |        60 |
+--------------------------+-----------+-----------+-----------+
1 row in set (0.00 sec)

转载：https://blog.csdn.net/Cantevenl/article/details/115522417

查看评论

飞道的博客

飞道的博客

个人资料

文章分类

文章存档

阅读排行

评论排行

推荐文章

数据库应用——DQL查询数据（连表查询子查询详细案例）

DQL查询数据

DQL

Select完整的语法

练习用的sql脚本

指定查询字段

where 条件子句

连表查询（重要）

三种join的比较

连表查询练习

自连接

分页和排序

子查询

分组和过滤

* 以上用户言论只代表其个人观点，不代表本网站的观点或立场

飞道的博客

飞道的博客

个人资料

文章分类

文章存档

阅读排行

评论排行

推荐文章

数据库应用——DQL查询数据（连表查询 子查询 详细案例）

DQL查询数据

DQL

Select完整的语法

练习用的sql脚本

指定查询字段

where 条件子句

连表查询（重要）

三种join的比较

连表查询练习

自连接

分页和排序

子查询

分组和过滤

* 以上用户言论只代表其个人观点，不代表本网站的观点或立场

数据库应用——DQL查询数据（连表查询子查询详细案例）