数据库系统-数据查询

发表于 | 分类于 | 浏览 次
字数统计: 2.9k | 阅读时长 ≈ 11

数据查询

单表查询

选择表中若干列

指定列
1
2
SELECT sno,sname
FROM Student;

image-20230626151644856

全部列
1
2
SELECT * 
FROM Student;

image-20230626151552111

查询计算过得值
1
2
3
SELECT子句的＜目标列表达式〉不仅可以是表中的属性列，也可以是表达式
SELECT Sname, 2014-Sage
FROM Student;

目标表达水不仅可以是算数表达式，也可以是字符串常量，函数等

修改查询列标题

用户可以通过指定别名来改变查询结果的列标题，这对于含算术表达式、常量、函数名的目标列表达式尤为有用 

1
SELECT Sname [AS] newName

选择表中的若干元组

系统查询的思路是，将关系中的所有元组一一进行条件匹配，然后在输出。当数据量大时，这种思路就不太合适了。可以创建索引解决

消除重复行
1
2
SELECT DISTINCT Sno
FROM SC;
查询满足条件的元组

通过WHERE来实现

查询条件：

G3sx3j.png 
1
2
3
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sdept IN('CS','MA','IS');

注意：’IS’ 有时不能用‘=’代替，如’IS NULL’

字符匹配时，可以有通配符，如下：

  • _

    代表任意单个字符

  • %

    任意长度的字符，零个也包括

当数据中本身就有通配符时，就需要用到ESCAPE ‘<换码字符>’

1
2
3
SELECT Cno,Credit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE'\';
ORDER BY子句

默认升序ASC

1
2
3
4
SELECT Sno, Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;

可以既有升序又有降序：

1
2
3
4
SELECT A, B 
FROM DD
WHERE ...
ORDER BY A, B DESC; //A可能是具有重复值，再重复值的情况下对B降序
1
在使用 GROUP BY 子句进行分组后，可以使用 ORDER BY 子句对分组后的结果进行排序。不过，需要注意的是，ORDER BY 子句只能对分组后的结果进行排序，而不能对每个分组内部的数据进行排序。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE Orders (
    OrderID int,
    CustomerID int,
    OrderDate date,
    Amount int
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount) VALUES
(1, 1, '2022-01-01', 100),
(2, 1, '2022-02-01', 200),
(3, 1, '2022-03-01', 150),
(4, 2, '2022-01-01', 50),
(5, 2, '2022-02-01', 75),
(6, 3, '2022-03-01', 300);

SELECT CustomerID, SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
ORDER BY TotalAmount DESC;

CustomerID | TotalAmount
-----------|------------
1          | 450
3          | 300
2          | 125
TOP
TOP N
1
2
SELECT TOP N * 
FROM SC
TOP N PERCENT
1
2
SELECT TOP N PERCENT *
FROM SC
聚集函数

聚集函数中除COUNT外，其它函数在计算过程中均省略NULL；WHERE子句中不能使用聚集函数

COUNT()

COUNT(*) 统计元组个数
COUNT（ [DISTINCT|ALL] ＜列名>） 统计一列中值的个数

SUM()

SUM（ [DISTINCT|ALL] ＜列名>） 计算一列值的总和（此列必须是数值型）
AVG（ [DISTINCT|ALL] ＜列名>） 计算一列值的平均值（此列必须是数值型）

MAX

MAX（ [DISTINCT|ALL] ＜列名>） 求一列值中的最大值

MIN

MIN（ [DISTINCTIALL] ＜列名>） 求一列值中的最小值

GROUP BY子句

GROUP B Y 子句将查询结果按某一列或多列的值分组，值相等的为一组。

分组后聚集函数将作用于每一个组，即每一组都有一个函数值。

如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用HAVING短语指定筛选条件。

WHERE子句与HAVING短语的区别在于作用对象不同。 WHERE子句作用于基本表或视图，从中选择满足条件的元组。 HAVING短语作用于组，从中选择满足条件的组

WHERE子句中是不能用聚集函数作为条件表达式的

1
2
3
4
/*查询课程号及相应选课人数*/
SELECT cno, COUNT(sno)
FROM SC
GROUP BY cno
1
2
3
4
5
/*查询选取三门课程以上的学生学号*/
SELECT sno
FROM SC
GROUP BY sno
HAVING count(sno) > 3
1
2
3
4
5
/*查询平均成绩大于等于90 分的学生学号和平均成绩。*/
SELECT Sno, AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade) >= 90

不一定是group by 单个属性

1
where子句中也不能有group by

连接查询

等值与非等值连接

连接查询的WHERE子句中用来连接两个表的条件称为连接条件或连接谓词，其一般格式为 :

1
2
[＜表名1＞.] <列名l> <比较运算符> [＜表名2＞.] ＜列名2＞
/*其中比较运算符主要有=、＞、＜、＞=、＜=、！= (或o ) 等*/

当连接运算符为=时，称为等值连接。使用其他运算符称为非等值连接。

这就是嵌套循环连接算法： 一个表的全部元组的某个属性一一去和另一个元组的某个属性进行比较

等值连接
1
2
3
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno

结果中两个Sno都存在

一 条 SQ L语句可以同时完成选择和连接查询，这 时 WHERE子句是由连接谓词和选择谓词组成的复合条件。

自然连接

若在等值连接中把目标列中重复的属性列去掉则为自然连接。

1
2
3
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno = SC.Sno

结果只保留一个Sno

自身连接

Course表取两个别名，一个是 F IR S T ,另一个是SECOND。

1
2
3
SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;
外连接

有 时 想 以 Student表为主体列出每个学生的基本情况及其选课情况。若某个学生没有选课，仍 把 Student的悬浮元组保存在结果关系中，而 在 S C 表的属性上填空值NULL ,这时就需要使用外连接 

1
2
3
4
5
6
7
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
/*也可以使用USING來去掉结果中的重复值： FROM Student LEFT OUTER JOIN SC USING (Sno);*/

/*RIGHT OUTER JOIN ON 右连接
FULL OUTER JOIN ON 全连接
LEFT OUTER JOIN ON 左连接*/

GzUTIA.png

左外连接列出左边关系(如本例Student)中所有的元组，右外连接列出右边关系中所有的元组。

多表连接
1
2
3
SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
XXX INNER JOIN XXX ON(…..)
1
2
3
4
5
6
SELECT *
FROM Student INNER JOIN SC ON(Student.Sno=SC.Sno)

和普通的通过where Student.Sno=SC.Sno一样的效果

不过，使用 INNER JOIN 通常更为清晰明了，因为它明确地指出了连接条件。此外，在连接多个表时，使用 JOIN 语句通常更为方便。
例题
1
2
3
-- 查询每门课程的被选修情况和课程的名字。
SELECT SC.*, Cname  
FROM SC RIGHT JOIN Courses ON SC.Cno=Courses.Cno
1
2
3
-- 查询选修了课程的学生姓名
SELECT DISTINCT(Students.Sname)  
FROM Students INNER JOIN SC ON (Students.Sno=Sc.Sno)

嵌套查询

在 SQL语言中，一 个 SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或 HAVING短语的条件中的查询称为嵌套查询(nested query ) 

1
2
3
4
5
6
SELECT Sname /*外层查询或父査询*/
FROM Student
WHERE Sno IN
	(SELECT Sno /*内层查询或子査询*/
	FROM SC
	WHERE Cno= '2');

SQ L语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。需要特别指出的是，子查询的 SELECT语句中不能使用ORDER B Y 子句， ORDER B Y 子句只能对最终查询结果排序。

大多数嵌套查询可以用连接查询代替

带有IN谓词的子查询

在嵌套查询中，子查询的结果往往是一个集合，所 以 谓 词 IN 是嵌套查询中最经常使用的谓词。 

1
2
3
4
5
6
SELECT Sno,Sname,Sdept /*例 3.55 的解法一*/
FROM Student
WHERE Sdept IN
	(SELECT Sdept
	FROM Student
	WHERE Sname='刘晨，)；

本例中，子查询的查询条件不依赖于父查询，称为不相关子查询。如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询(correlated subquery) , 整个查询语句称为相关嵌套查询(correlated nested query) 语句

1
2
3
SELECT Sl.Sno,Sl.Sname,Sl.Sdept /*例 3.55 的解法二 */
FROM Student SI,Student S2
WHERE Sl.Sdept=S2.Sdept AND S2.Sname='刘晨'；

可见，实现同一个查询请求可以有多种方法，当然不同的方法其执行效率可能会有差别 ，甚至会差别很大。这就是数据库编程人员应该掌握的数据库性能调优技术 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT Sno,Sname
FROM Student ③ 最后在Student关系中
				取tLl Sno 和 Sname
WHERE Sno IN
	(SELECT Sno		② 然 后 在 SC 关系中找出
	 FROM SC				选修了 3 号课程的学生学号
	 WHERE Cno IN				
		(SELECT Cno				① 首 先 在 Course关系中
		 FROM Course				找 出 “信息系统”的课
		 WHERE Cname=’信息系统'		 程号，结果为3 号
		)；
/*等价于*/
SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Cname='信息系统'；

有些嵌套查询可以用连接运算替代，有些是不能替代的

JtBRK0.png JtBgvq.png Jt0I1I.png Jt059A.png Jt0hhd.png

image-20230626152622019

带有比较运算符的子查询

可以用>,<,>=,<=,!=或<>等比较运算符

1
2
3
4
5
6
SELECT Sno ,Cno
FROM SC x
WHERE Grade ＞=(SELECT AVG(Grade) /*某学生的平均成绩*/
FROM SC y
WHERE y.Sno=x.Sno);
/*执行过程是，外层先取一个元组，传递给内层，内层得到平均值，在外层执行*/

该值是与父查询相关的，因此这类查询称为相关子查询

求解相关子查询不能像求解不相关子查询那样一次将子查询求解出来，然后求解父查询

JtD4yt.png JtDhQI.png

带有ANY(SOME)或ALL谓词的子查询

Jml6iT.png

1
2
3
4
5
6
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY (SELECT Sage  /*这里也可以用MAX()代替*/
	FROM Student
	WHERE Sdept='CS')
AND Sdept <> 'CS' ; /*注意这是父査询块中的条件*/

用ANY后形成集合

事实上，用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高。ANY,ALL与聚集函数的对应关系如表：

Jm32r9.png

J8twwt.png J8YXi8.png

image-20230626153200761

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真假值TRUE,FALSE

1
2
3
4
5
6
SELECT Sname
FROM Student
WHERE EXISTS
	（SELECT *
	FROM SC
	WHERE Sno=Student.Sno AND Cno='1'）;

这也是相关子查询。先取一个外层的元组

由 EXISTS引出的子查询，其目标列表达式通常都用**, 因为带 EXISTS的子查询只返回真值或假值，给出列名无实际意义

JtskE8.png

与 EXISTS谓词相对应的是NOT EXISTS谓词。使用存在量词NOT EXISTS后，若内层查询结果为空，则外层的WHERE子句返回真值，否则返回假值 

1
2
3
4
5
6
SELECT Sname
FROM Student
WHERE NOT EXISTS
	(SELECT *
	FROM SC
	WHERE Sno=Student.Sno AND Cno='1');

由于带EXISTS量词的相关子査询只关心内层查询是否有返回值，并不需要查具体值,因此其效率并不一定低于不相关子查询，有时是高效的方法

用EXISTS/NOT EXISTS实现全称量词

JD4C0P.png
JD49mt.png

用EXISTS/NOT EXISTS实现逻辑蕴涵

JzutVH.md.png
Jzu8KO.md.png
JzuJqe.md.png
JzuGrD.md.png

集合查询

UNION , INTERSECT, EXCEPT

列数相等，对应属性相同

属性名无关，属性名取第一个结果属性名

自动除去重复; 不去重UNION ALL

最后使用ORDER BY(写在最后)；可以用数字代替对应第几个属性，ORDER BY 3

UNION并

1
2
3
4
5
6
7
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2'

image-20230626154958084

INTERSECT交

1
2
3
4
5
6
7
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19
J5Po2d.png

EXCEPT差

1
2
3
4
5
6
7
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage <=19;

基于派生表的查询

image-20230626155309038

子查询不仅可以出现在WHERE子句中，还可以出现在FROM 子句中，这时子查询生成的临时派生表(derived table )成为主查询的查询对象

JbxGT0.png

有top的子查询可以把order by放在子查询

JqSNM4.png

1
2
3
4
SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)
AS Avg_sc(avg_sno,avg_grade) // 生成表Avg_sc,具有两个属性
WHERE SC.Sno = Avg_sc.avg_sno and SC.Grade ＞= Avg_sc.avg grade

如果子查询中没有聚集函数，派生表可以不指定属性列

通 过 FROM 子句生成派生表时， A S 关键字可以省略，但必须为派生
关系指定一个别名

image-20230626155524668

从此无心爱良夜，任他明月下西楼。

本文标题:数据库系统-数据查询

文章作者:TTYONG

发布时间:2020年04月01日 - 16:04

最后更新:2023年06月26日 - 16:06

原始链接:http://tianyong.fun/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F-%E6%95%B0%E6%8D%AE%E6%9F%A5%E8%AF%A2-5.html

许可协议: 转载请保留原文链接及作者。

多少都是爱
0%