数据查询语句-详解

最新推荐文章于 2022-11-04 17:08:12 发布

zhenchentl

最新推荐文章于 2022-11-04 17:08:12 发布

阅读量2.9k

点赞数 2

分类专栏：数据库基础整理文章标签：数据库 insert null delete join table

本文链接：https://blog.csdn.net/zhenchentl/article/details/7602332

版权

数据库基础整理专栏收录该内容

4 篇文章 0 订阅

订阅专栏

示例数据库：

学生-课程数据库

学生表：Student(Sno，Sname，Ssex，Sage，Sdept)

课程表：Course(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade)

一，单表查询

基本语法：

1，查询经过计算的值：

例：查全体学生的姓名及其出生年份。

SELECT Sname,2011-Sage FROM Student;

例：查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。

SELECT Sname NAME,’Year of Birth:’BIRTH,
2007-Sage BIRTHDAY,LOWER(Sdept)
FROM Student;

2，消除取值重复的行

例：查询选修了课程的学生学号。
SELECT DISTINCT Sno FROM SC;
3，查询满足条件的元组：
Where字句常用查询条件




(1)，基于数值的过滤条件。
例：查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT Sname,Sage FROM Student WHERE NOTSage>=20

(2)，基于范围的过滤条件。
例：查询年龄不在20~23岁之间的学生姓名、系别和年龄。

SELECT Sname，Sdept，Sage FROMStudent WHERE Sage NOT BETWEEN 20 AND 30;

(3)，基于给定集合的过滤条件
例：查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN (‘IS’,’MA’,’CS’);

(4)，基于文本的过滤条件。
格式：[NOT] LIKE  ‘<匹配串>’  [ESCAPE ‘ <换码字符>’]

<匹配串>：指定匹配模板

匹配模板：固定字符串或含通配符的字符串
当匹配模板为固定字符串时
用 = 运算符取代 LIKE 谓词
用 != 或 < >运算符取代 NOTLIKE 谓词
当模糊查询时
% (百分号)  代表任意长度（长度可以为0）的字符串
_ (下横线)  代表任意单个字符
当用户要查询的字符串本身就含有 % 或 _ 时，要使用ESCAPE'<换码字符>' 短语对通配符进行转义。
例：查询名字中第2个字为“阳”字的学生的姓名和学号。

SELECT Sname，Sno
FROM Student
FROM Student
WHERE Sname LIKE ‘_阳%’

例：查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT *
FROM Course
WHERE Cname LIKE ‘DB\_%i_ _’ ESCAPE ‘\’

(5)，针对空值的过滤条件。
例：查所有有成绩的学生学号和课程号。

SELECT Sno，Cno FROM SC WHEREGrade IS NOT NULL;

(6)，多重条件查询

用逻辑运算符AND和OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级
可用来实现多种其他谓词
[NOT] IN
[NOT] BETWEEN …   AND  …
例：查询计算机系年龄在20岁以下的学生姓名

SELECT Sname FROM Student
WHERE Sdept=’CS’ AND Sage<20

4，查询结果排序
使用ORDER BY子句
可以按一个或多个属性列排序
升序：ASC；降序：DESC；缺省值为升序
当排序列含空值时（Null为无穷小）
ASC：排序列为空值的元组最先显示
DESC：排序列为空值的元组最后显示
例：查询全体学生，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

SELECT * FROM Student ORDER BY Sdept，Sage DESC

5，聚集函数
COUNT（[DISTINCT|ALL] *） 
统计元组个数
COUNT（[DISTINCT|ALL]<column> ）
计算一列中值的个数
SUM（ [DISTINCT|ALL]<column>）

计算值的总和并返回总数
AVG（ [DISTINCT|ALL]<column> ）

返回指定列中的平均值
MIN（ [DISTINCT|ALL]<column> ）

返回自变量中指定列的最小值
MAX（ [DISTINCT|ALL]<column> ）

返回自变量中指定列的最大值
DISTINCT短语：在计算时要取消指定列中的重复值

ALL短语：不取消重复值

ALL为缺省值
例：查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno) FROM SC;

6，查询结果分组
使用GROUP BY子句分组     
细化聚集函数的作用对象
未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组 
只有满足HAVING短语指定条件的组才输出 
例：求各个课程号及相应的选课人数

SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;

例：查询选修了3门以上课程的学生学号。

SELECT Sno FROM SC
GROUP BY Sno HAVING COUNT(*) >3;

GROUP BY子句的作用对象是查询的中间结果表
分组方法：按指定的一列或多列值分组，值相等的为一组
使用GROUP BY子句后，SELECT子句的列名列表中只能出现分组属性和聚集函数
例：查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数

SELECT Sno,COUNT(*) FROM SC
WHERE Grade>=90
GROUP BY Sno HAVING COUNT(*)>=3; 

HAVING短语与WHERE子句的区别：作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组。
HAVING短语作用于组，从中选择满足条件的组。 
二，连接查询
同时涉及多个表的查询称为连接查询
用来连接两个表的条件称为连接条件或连接谓词
比较运算符：=、>、<、>=、<=、!=
连接字段
连接谓词中的列名称为连接字段
连接条件中的各连接字段类型必须是可比的，但不必是相同的
SQL中连接查询的主要类型
广义笛卡尔积
等值连接(含自然连接)
非等值连接查询
自身连接查询
外连接查询
复合条件连接查询
1，广义笛卡尔积
不带连接谓词的连接，很少使用
SELECTStudent.*,SC.* FROM Student,SC;
2，值连接
连接运算符为 = 的连接操作
任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。
例：查询每个学生及其选修课程的情况。

SELECTStudent.*,SC.* FROM Student,SC WHERE Student.Sno=SC.Sno;

3，自然连接
等值连接的一种特殊情况，把目标列中重复的属性列去掉。
例：查询每个学生及其选修课程的情况。

SELECTStudent.Sno, Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student,SCWHERE Student.Sno=SC.Sno;

4，值连接
连接运算符不是 = 的连接操作
比较运算符：>、<、>=、<=、!=
5，自身连接
一个表与其自己进行连接，称为表的自身连接
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀
例：查询每一门课的间接先修课（即先修课的先修课）

SELECTFIRST.Cno,SECOND.Cpno
FROM CourseFIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno

6，外连接
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
例：查询每个学生及其选修课程的情况，即使学生一门课也没有选也要输出学生信息。

SELECTStudent.Sno,Sname,Ssex,Sage,Cno,Grade
FROM StudentLEFT JOIN SC
OnStudent.Sno=SC.Sno

7，复合条件连接
WHERE子句中含多个连接条件时，称为复合条件连接

例：查询选修2号课程且其成绩在90分以上的所有学生的学号、姓名

SELECTStudent.Sno,Student.Sname
From Student,SC
WHEREStudent.Sno=SC.Sno AND
                            SC.Cno=’2’AND
                            SC.Grade>90;

8，多表连接
例：查询每个选修了课程的学生的学号、姓名、选修的课程名及成绩。

         SELECTStudent.Sno,Sname,Cname,Grade
         FROMStudent,SC,Course
         WHEREStudent.Sno=SC.Sno AND SC.Cno=Course.Cno;

三，嵌套子查询
基本含义
一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询，称为嵌套查询
子查询的限制：不能使用ORDER BY子句.
例：查询选修了2号课程的学生的姓名

SELECT Sname
         FROM Student
         WHERE Sno IN
                   (        SELECT Sno
                            FROM SC
                            WHERE Cno=’2’);

有些嵌套查询可以用连接运算替代.
例：查询选修课程名为“信息系统”的学生学号和姓名——使用连接方法

SELECT Sno，Sname
     FROM Student，SC，Course
     WHERE Student.Sno = SC.Sno  AND
             SC.Cno = Course.Cno AND
             Course.Cname=‘信息系统’；

1，带有IN谓词的子查询
例：查询选修课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname
FROM Student
WHERE Sno IN (        SELECT Sno
                                     FROM SC
                                     WHERE CnoIN(        SELECT Cno
                                                                           FROMCourse
                                                                           WHERECname=’信息系统’ )  );

2，带有比较运算符的子查询：
当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
例：查询与“刘晨”在同一个系学习的学生。假设一个学生只可能在一个系学习，并且必须属于一个系。

         SELECTSno,Sname,Sdept FROM Student
         WHERESdept=(        SELECT Sdept FROM Student
WHERE Sname=’刘晨’);

         注意： 子查询一定要跟在比较符之后
3，带有ANY或ALL谓词的子查询
ANY：任意一个值（某个值）
ALL：所有值
配合使用比较运算符的含义
> ANY                          大于子查询结果中的某个值       
> ALL                           大于子查询结果中的所有值
< ANY                          小于子查询结果中的某个值    
< ALL                           小于子查询结果中的所有值
>= ANY              大于等于子查询结果中的某个值    
>= ALL                大于等于子查询结果中的所有值
<= ANY              小于等于子查询结果中的某个值    
<= ALL                小于等于子查询结果中的所有值
= ANY                          等于子查询结果中的某个值        
=ALL                            等于子查询结果中的所有值（没有实际意义）
!=（或<>）ANY       不等于子查询结果中的某个值
!=（或<>）ALL                 不等于子查询结果中的任何一个值
4，带有ANY或ALL谓词的子查询
例：查询其他系中比信息系中某一个学生年龄小的学生姓名和年龄。

SELECT Sname，Sage
FROM Student
WHERE Sage <ANY (        SELECT Sage FROM Student
                                               WHERESdept=’IS’)
                   AND Sdept <> ‘IS’;

等价于：

SELECTSname,Sage
FROM Student
WHERE Sage <(        SELECT MAX(Sage) FROM Student
WHERE Sdept=’IS’)
                            ANDSdept <> ‘IS’;

5，ANY、ALL与聚集函数的对应关系。




用聚集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数。
6，嵌套查询分类
不相关子查询
子查询的查询条件不依赖于父查询
处理方式：由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
相关子查询
子查询的查询条件依赖于父查询
处理方式：首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。
7，带有EXISTS谓词的子查询
EXISTS谓词
存在量词
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则返回真值
若内层查询结果为空，则返回假值
由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义
例：查询所有选修了1号课程的学生姓名。

SELECT Sname
FROM Student
WHERE EXISTS (       SELECT * FROM SC
WHERE Sno=Student.Sno
AND Cno=’1’);

求解思路分析：
本查询涉及Student和SC关系。
在Student中依次取每个元组的Sno值，用此值去检查SC关系。
若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= '1'，则取此Student.Sname送入结果关系。
例：查询所有选修了1号课程的学生姓名。（用连接方法实现）

                   SELECTSname FROM Student,SC
                   WHEREStudent.Sno=SC.Sno AND SC,Cno=’1’;

例：查询没有选修1号课程的学生姓名。

                   SELECTSname FROM Student
                   WHERENOT EXISTS
                                     (        SELECT * FROM SC
WHERE Sno=Student.Sno AND Cno=’1’);

8，不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
9，用EXISTS / NOT EXISTS实现全称量词。
例：查询选修了全部课程的学生姓名。

         SELECTSname FROM Student
         WHERENOT EXISTS(SELECT * FROM Course
                         WHERE NOT EXISTS( SELECT * FROM SC
                                           WHERE Sno=Student.Sno
                                           AND Cno=Course.Cno));

10，用EXISTS/NOT EXISTS实现逻辑蕴涵。
例：查询（至少）选修了学生95002选修的全部课程的学生号码。

         SELECTDISTINCT Sno FROM SC SCX
         WHERENOT EXISTS( SELECT * FROM SC SCY
                          WHERE SCY.Sno=’95002’ AND
                          NOT EXISTS (    SELECT * FROMSC SCZ
                                          WHERE SCZ.Sno=SCX.Sno
                                          AND SCZ.Cno=SCY.Cno));

11，子查询的其他应用
运用到数据操纵语言中。
除了使用在嵌套式SELECT语句之中外，子查询还可以被放在 CREATE VIEW 语句中、CREATE TABLE 语句、UPDATE 语句、INSERT 语句和DELETE语句中。
例：将计算机科学系全体学生的成绩置零。

UPDATE SC
SET Grade=0
WHERE EXISTS(        SELECT *
FROM Student
WHEREStudent.Sno=SC.Sno
AND Student.Sdept=’CS’);

四，集合查询
1，并
例：查询计算机科学系的学生或年龄不大于19岁的学生。

SELECT * FROMStudent WHERE Sdept=’CS’
UNION
SELECT * FROMStudent WHERE Sage<=19;

等价于：

SELECT DINSTINCT*
FROM Student
WHERE Sdept=’CS’OR Sage<=19;

2，交
例1：查询计算机科学系的学生及年龄不大于19岁的学生集合的交集

SELECT * FROMStudent WHERE Sdept=’CS’
INTERSECT
SELECT * FROMStudent WHERE Sage<=19;

等价于：

SELECT DINSTINCT*
FROM Student
WHERE Sdept=’CS’AND Sage<=19;

例2：查询选修课程1的学生集合与选修课程2的学生集合的交集

         SELECTSno FROM SC WHERE Cno=’1’
         INTERSECT
         SELECTSno FROM SC WHERE Cno=’2’;

         等价于：

         SELECTSno FROM SC
         WHERECno=’1’ AND Sno IN ( SELECT Sno FROM SC
         WHERE Cno=’2’);

3，差
例：查询计算机科学系的学生与年龄不大于19岁的学生的差集。

         SELECT* FROM Student WHERE Sdept=’CS’
         MINUS
         SELECT* FROM Student WHERE Sage<=19;

         等价于：

         SELECT* FROM Student
         WHERESdept=’CS’ AND Sage>19;

4，对集合操作结果的排序
ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序
任何情况下，ORDER BY子句只能出现在最后
对集合操作结果排序时，ORDER BY子句中可用数字指定排序属性

SELECT * FROMStudent WHERE Sdept=’CS’
UNION
SELECT * FROMStudent WHERE Sage<=19
         ORDERBY 1;

zhenchentl

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
数据查询语句-详解

示例数据库：学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade) 一，单表查询基本语法：1，查询经过计算的值：例：查全体学生的姓名及其出生年份。SELECT Sname,2011-Sage
复制链接

扫一扫

专栏目录