数据库----chapter03

数据库----chapter03

子查询

什么是子查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。当获得一个查询的答案需要多个步骤的操作，首先必须创建一个查询来确定用户不知道但包含在数据库中的值，将一个查询块嵌套在另一个查询块的WHERE字句或HAVING短语的条件中查询块称为子查询或内层查询。上层的查询块曾为父查询或外层查询。子查询的结果作为输入传递回“父查询”或“外部查询”。父查询将这个值结合到计算中，以便确定最后的输出。

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。以层层嵌套的方式来构造程序正是SQL中"结构化"的含义所在。

子查询是本质上就是一个完整 的SELECT 语句，它可以使一个 SELECT、SELECT…INTO 语句、INSERT…INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

示例

# 编写SQL语句，查看年龄比"李斯文"小的学生，要求显示这些学生的信息

分析

1. 查询到target的出生日期
2. 利用WHERE语句，筛选出生日期比"李斯文"大的学生

实现步骤

法一：

1. 查找出"李斯文"的出生日期

SELECT `bornDate` FROM `student` WHERE `studentName` = `李斯文`;

#结果
1993-07-23

2. 利用WHERE语句筛选出生日比"李斯文"大的学生

SELECT `studentNo`, `studentName`, `sex`, `bornDate`, `address` FROM `student` WHERE `bornDate` >"1993-07-23";

法二：

使用子查询实现

SELECT `studentNo`, `studentName`, `sex`, `bornDate`,`address` FROM `student`
WHERE `bornDate` > (SELECT `bornDate` FROM `student` WHERE `studentName` = "李斯文");

子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询

子查询在WHERE语句中的一般用法：

SELECT...FROM 表1 WHERE 字段1 比较运算符(子查询)

注意：

将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个。

---

IN子查询

由于一般的子查询方法必须要保证子查询的返回值不能多于一个，当超过一个的时候，使用比较运算符会出错，所以为了解决这个问题就需要使用到IN子查询。

示例

查询"Logic Java"课程考试成绩为60分的学生名单

一般的子查询方式

SELECT `studentName` FROM `student` WHERE `studentNo` = (SELECT `studentNo` FROM `result`
   INNER JOIN `subject` ON `result`.subjectNo = `subject`.subjectNO WHERE `studentResult` = 60 AND `subjectName` = "Logic Java");
   
   #结果
   错误码：1242
   ...

采用IN子查询

SELECT `studentName` FROM `student` 
WHERE `studentNo` IN(
	SELECT `studentNo` FROM `result`
    WHERE `subjectNo` = (
    	SELECT `subjectNo` FROM `subject`
        WHERE `subjectName` = "Logic Java"
    ) AND `studentResult` = 60
);

常用IN替换等于(=)的子查询，IN后面的子查询可以返回多条记录。

---

IN子查询练习

查询参加"Logic Java"课程最近一次考试的在读学生名单

实现步骤

1. 获得"Logic Java"课程的课程编号

   SELECT `subjectNo` FROM `subject`
   WHERE `subjectName` = `Logic Java`;
   

2. 根据课程编号查询到"Logic Java"课程最近一次的考试日期

   SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (
   	SELECT `subjectNo` FROM `subject`
   	WHERE `subjectName` = `Logic Java`
   );
   

3. 根据课程编号和最近一次的考试日期查询出在读学生信息

   SELECT `studentNo`, `studentName` FROM `student`
   WHERE `studentNo` IN(
   	SELECT `studentNo` FROM `result`
       WHERE `subjectNo` IN(
           SELECT `subjectNo` FROM `subject`
           WHERE `subjectName` = `Logic Java`
       ) AND `examDate` = (
       	SELECT MAX(`examDate`) FROM `result`
           WHERE `subjectNo` = (
           SELECT `subjectNo` FROM `subject`
           WHERE `subjectName` = `Logic Java`
   		)
       )
   );
   

NOT IN 子查询

它于IN有相反的含义

示例

查询未参加"Logic Java"课程最近一次考试的在读学生名单

SELECT `studentNo`, `studentName` FROM `student`
WHERE `studentNo` NOT IN(
	SELECT `studentNo` FROM `result`
    WHERE `subjectNo` IN(
        SELECT `subjectNo` FROM `subject`
        WHERE `subjectName` = `Logic Java`
    ) AND `examDate` = (
    	SELECT MAX(`examDate`) FROM `result`
        WHERE `subjectNo` = (
        SELECT `subjectNo` FROM `subject`
        WHERE `subjectName` = `Logic Java`
		)
    )
);

---

高级查询

模糊查询

关键字LIKE

LIKE 关键字支持百分号“%”和下划线“_”通配符。

1. "%"是 MySQL 中最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为 0。

   -- 查学生表姓李的学生信息
   SELECT * FROM `student`
   WHERE `studentName` LIKE "李%";
   
   -- 查询以宝字结尾的学生信息
   SELECT * FROM `student`
   WHERE `studentName` LIKE "%宝";
   

2. "_"只能代表单个字符，字符的长度不能为 0。

   -- 查询名字有三个字组成，中间的一个字为"荣"的姓名信息
   SELECT * FROM `student`
   WHERE `studentName` LIKE "_荣_";
   

区间查询

查询出成绩表里，考试成绩再90-100区间的学生信息。

-- 条件直接筛选
SELECT * FROM `result`
WHERE `studentResult` >= 90 AND `studetnResult` <= 100;


-- between ... and,包含90和100
SELECT * FROM `result`
WHERE `studentResult` BETWEEN 90 AND 100;

EXISTS 和 NOT EXISTS关键字

EXISTS关字键字表示存在。

EXISTS 判断某个sql语句的有没有查到结果 有就返回真 true 否则返回假 False, 外层查询不执行。

如果条件成立 返回另外一条sql语句的返回结果

EXISTS语法

SELECT ... FROM 表名 WHERE EXISTS (子查询);

示例

-- 需求： 如果有阶段一的学生，就查询阶段二考试的学员学号、科目编号、考试成绩、考试时间

-- 1、查询出阶段一的年级编号
SELECT `id` FROM `grade`
WHERE `name` = "阶段一";
-- 2、根据阶段一年级编号查询出学生学号
SELECT `studentNo
` FROM `student`
WHERE `gradeID` = (
SELECT `id` FROM `grade`
WHERE `name` = "阶段一"
);
-- 3、根据学号， 查询成绩表里阶段二的信息
SELECT `studentNo`,`subjectNo`,`studentResult`,`examDate` FROM `result`
WHERE EXISTS(
SELECT `studentNo` FROM `student`
WHERE gradeID IN (
	SELECT `ID` FROM `grade`
	WHERE `NAME` = "阶段一"
)) AND subjectNo IN (
	SELECT `SUBJECTNO` FROM `subject`
	WHERE `GRADEID` IN (
		SELECT `ID` FROM `grade`
		WHERE `NAME` = "阶段二"
		)
);

NOT EXISTS语法

SELECT ... FROM 表名 WHERE NOT EXISTS (子查询);

和EXISTS的用法相同，但它的结果与EXISTS结果相反。

示例

-- 检查"Java Basic"课程最近一次考试成绩，如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分
SELECT AVG(`studentResult`) + 5 AS "平均分" FROM `result`
WHERE NOT EXISTS(
	SELECT * FROM `result`
    WHERE `subjectNo` = (
		SELECT `SUBJECTNO` FROM `subject`
        WHERE `SUBJECTNAME` = "javaBasic"
    ) AND `examDate` = (
		SELECT MAX(`examDate`) FROM `result`
        WHERE `subjectNo` = (
        SELECT `SUBJECTNO` FROM `subject`
        WHERE `SUBJECTNAME` = "javaBasic"
        )
    )AND `studentResult` > 60
)AND `subjectNo` = (
	SELECT `SUBJECTNO` FROM `subject`
    WHERE `SUBJECTNO` = "javaBasic"
    )AND `examDate` = (
    SELECT MAX(`examDate`) FROM `result`
    WHERE `subjectNo` = (
    SELECT `SUBJECTNO` FROM `subject`
    WHERE `SUBJECTNAME` = "javaBasic"
    )
);

分组查询(GROUP BY)

---

语法

SELECT ... FROM<表名>
WHERE...
GROUP BY ...

注意

SELECT 列表中只能包含：

1. 被分组的列
2. 为每个分组返回一个值的表达式，如聚合函数。

示例

-- 统计每门课程平均分
SELECT `subjectNo` AS "课程编号", avg(`studentResult`) AS "平均分" FROM `result`
GROUP BY 课程编号
-- 成绩排序，从到低
ORDER BY 平均分 DESC;



-- 分别统计每个年级男，女生人数
SELECT `gradeID` AS "年级编号", `sex` AS "性别", COUNT(1) AS "人数" FROM `student`
GROUP BY 年级编号, 性别;

分组筛选

语法

SELECT...FROM<表名>
WHERE...
GROUP BY ...
HAVING ...

示例

-- 分组和筛选，获得课程平均分及格的课程成绩
SELECT `SUBJECTNO` AS "课程编号", AVG(`studentResult`) AS "平均分" FROM `result`
GROUP BY 课程编号
HAVING 平均分 >= 60;

WHERE与HAVING对比

1. WHERE子句

   用来筛选FROM子句中指定的操作所产生的行

2. GROUP BY子句

   用来分组WHERE子句的输出

3. HAVING子句

   用来从分组的结果中筛选行

---

多表连接查询

内连接(INNER JOIN)

内连接使用比较运算符根据每个表的通用列中的值匹配两个表中的行。

语法

SELECT ...
FROM 表1
INNER JOIN 表2
ON(匹配通用列)

他和以下的写法等价，但效率更高。

SELECT ...
FROM 表1, 表2
WHERE...

示例

-- 查询学生表和成绩表，显示学生姓名、科目编号、考试成绩
-- 法一： 逗号分开，主外键放在where条件语句中
SELECT s.`studentName`, r.`subjectNo`, r.`studentResult`
FROM `student` AS s, `result` AS r
WHERE s.`studentNo` = r.`studentNo`;

-- 法二：inner join表名 on 主外键关联

SELECT s.`studentName`, r.`subjectNo`, r.`studentResult`
FROM `student` AS s 
INNER JOIN `result` AS r
ON s.`studentNo` = r.`studentNo`;

多表内连接

-- 查询四张表，显示学生学号、姓名、年级名称、科目名称、考试成绩
SELECT s.`studentNo`, s.`studentName`, g.`name`, su.`subjectName`, r.`studentResult` FROM `student` AS s
INNER JOIN `result` AS r ON r.`studentNo` = s.`studentNo`
INNER JOIN `subject` AS su ON r.`subjectNo` = su.`SUBJECTNO`
INNER JOIN `grade` AS g ON g.`ID` = su.`GRADEID`;

---

外连接

左外连接(LEFT JOIN)

左外连接，主表（左表）student中数据逐条匹配目标表中的数据。

1. 匹配，返回到结果集
2. 无匹配，NULL值返回到结果集

sql语句实现

SELECT S.studentName,R.subjectNo,R.studentResult
FROM student AS S
LEFT JOIN result AS R
ON S.studentNo = R.studentNo;

右外连接(RIGHT JOIN)

右外连接的原理和左外连接的原理相同，右表（主表）逐条去匹配记录；否则NULL填充。

示例

-- 右外连接
SELECT s.`studentNo`, s.`studentName`, r.`studentResult` FROM `result` AS r
RIGHT JOIN `student` AS s
ON s.`studentNo` = r.`studentNo`;