关系数据库标准语言SQL---数据查询（单表、连接、嵌套、集合、基于派生表）

一、单表查询

 语句格式：

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …          #SELECT子句：指定要显示的属性列

FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)               #FROM子句：指定查询对象（基本表或视图）

       [AS]<别名>

[ WHERE <条件表达式> ]                                                                           #WHERE子句：指定查询条件

[ GROUP BY <列名1>                                           #GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。

[ HAVING <条件表达式> ] ]                              #HAVING短语：只有满足指定条件的组才予以输出

[ ORDER BY <列名2> [ ASC|DESC ] ];                # ORDER BY子句：对查询结果表按指定列值的升序或降序排序   

 

eg:

查询全体学生的学号与姓名。  

       SELECT Sno,Sname   

         FROM Student;

查询全部列:   SELECT  *             FROM Student; 

查询经过计算的值:      SELECT   Sname,2020-Sage     FROM Student;      #查询学生出生年份。

SELECT DISTINCT Sno     FROM SC;        # DISTINCT去除重复行

 

常用查询条件：（where子句中）

查 询 条 件	谓    词
比    较	=, >, <, >=, <=, !=, <>, !>, !<; NOT+上述比较运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空    值	IS NULL, IS NOT NULL（“IS” 不能用 “=” 代替）
多重条件（逻辑运算）	AND, OR, NOT（AND的优先级高于OR）

eg:

    SELECT Sname     FROM     Student     WHERE  Sdept=‘CS’;   #查询计算机科学系全体学生的名单。

    SELECT Sname, Sdept, Sage   FROM  Student     WHERE   Sage BETWEEN 22 AND 23;    #查询年龄在22~23岁（包括22岁和23岁）之间的学生的姓名、系别和年龄

    SELECT Sname, Ssex     FROM Student      WHERE Sdept NOT IN ('IS','MA’,'CS' );#查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。

字符匹配  ：LIKE 、NOT  LIKE

 SELECT Sname, Sno, Ssex       FROM Student     WHERE  Sname LIKE '刘%';  #查询所有姓刘学生的姓名、学号和性别。

SELECT Sname       FROM   Student       WHERE  Sname LIKE '欧阳__';  #询姓"欧阳"且全名为三个汉字的学生的姓名。

% （百分号）  代表任意长度（长度可以为0）的字符串

                          例如a%b表示以a开头，以b结尾的任意长度的字符串

_ （下横线）  代表任意单个字符。    （注意一个汉字占两个字符）

                           例如a_b表示以a开头，以b结尾的长度为3的任意字符串

换码字符：ESCAPE                           eg：ESCAPE '＼' 表示“ ＼” 为换码字符

SELECT  *       FROM    Course       WHERE  Cname LIKE  'DB\_%i_ _' ESCAPE '\ ' ;  #查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

 

ORDER BY子句

ORDER BY：按一个或多个属性列排序     升序：ASC;      降序：DESC;       默认升序

eg:    SELECT  *         FROM  Student         ORDER BY Sdept, Sage DESC;     #查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

 

聚集函数：

统计元组个数      COUNT(*)

统计一列中值的个数      

COUNT([DISTINCT|ALL] <列名>)

计算一列值的总和（此列必须为数值型） SUM([DISTINCT|ALL] <列名>)    

计算一列值的平均值（此列必须为数值型） AVG([DISTINCT|ALL] <列名>)

求一列中的最大值和最小值       MAX([DISTINCT|ALL] <列名>)      MIN([DISTINCT|ALL] <列名>)

eg：

SELECT COUNT(DISTINCT Sno)      FROM SC;   #查询选修了课程的学生人数。

  SELECT AVG(Grade)           FROM    SC           WHERE Cno= ' 1 ';   #计算1号课程的学生平均成绩。

SELECT SUM(Ccredit)          FROM  SC,Course         WHERE Sno='201215012' AND SC.Cno=Course.Cno;   #查询学生201215012选修课程的总学分数。

 

GROUP BY子句    （细化聚集函数的作用对象）    

1.如果未对查询结果分组，聚集函数将作用于整个查询结果  

2.对查询结果分组后，聚集函数将分别作用于每个组

3.按指定的一列或多列值分组，值相等的为一组

eg：  

（对SC表进行分组，将Sno值相等的分为一组，对每组Sno数量大于3的进行查询）

SELECT Sno      FROM  SC      GROUP BY Sno      HAVING  COUNT(*) >3;   #查询选修了3门以上课程的学生学号。 

 

！！！注意！！！     WHERE子句中是不能用聚集函数作为条件表达式，聚集函数只能用于SELECT子句和GROUP BY中的 HAVING子句。

查询平均成绩大于等于90分的学生学号和平均成绩。

    SELECT Sno, AVG(Grade)     FROM  SC     WHERE AVG(Grade)>=90     GROUP BY Sno;    ##错误

    SELECT  Sno, AVG(Grade)     FROM  SC     GROUP BY Sno     HAVING AVG(Grade)>=90;  ##正确

 

HAVING短语与WHERE子句的区别：

作用对象不同 WHERE子句作用于基表或视图，从中选择满足条件的元组 HAVING短语作用于组（GROUP BY所分得组），从中选择满足条件的组。

 

二、连接查询

分为等值连接、非等值连接、自身连接。

连接条件或 连接谓词出现在where子句中。

连接字段：连接谓词中的列名称。

连接条件中的各连接字段类型必须是可比的，但名字不必相同。

等值与非等值连接查询

eg：

SELECT  Student.*, SC.*       FROM    Student, SC        WHERE  Student.Sno = SC.Sno;#查询每个学生及其选修课程的情况

若将等值连接中重复列去除，则变成自然连接。

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade    FROM     Student,SC  WHERE  Student.Sno = SC.Sno;

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

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

SELECT Student.Sno, Sname    

FROM     Student, SC    

WHERE  Student.Sno=SC.Sno   AND  SC.Cno=' 2 ' AND  SC.Grade>90;

执行过程：1.从SC中挑选出Cno='2'并且Grade>90的元组形成一个中间关系

                  2.和Student中满足连接条件的元组进行连接得到最终的结果关系

自身连接（一个表与其自己进行连接）

1.需要给表起别名以示区别

2.由于所有属性名都是同名属性，因此必须使用别名前缀

eg：        查询每一门课的间接先修课（即先修课的先修课）

SELECT   FIRST.Cno,  SECOND.Cpno   FROM  Course  FIRST, Course  SECOND   WHERE FIRST.Cpno=SECOND.Cno;

外连接

 左外连接 ：列出左边关系中所有的元组 （右表中多余部分用NULL）

 右外连接 ：列出右边关系中所有的元组 （左表中多余部分用NULL）

eg：    查询每个学生及其选修课程的情况（左外连接）

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade  FROM  Student  LEFT OUT JOIN SC ON  (Student.Sno=SC.Sno);

多表连接（两个以上的表进行连接）

eg：  查询每个学生的学号、姓名、选修的课程名及成绩

  SELECT Student.Sno, Sname, Cname, Grade  

   FROM    Student, SC, Course    /*多表连接*/    

   WHERE Student.Sno = SC.Sno    AND SC.Cno = Course.Cno;

 

三、嵌套查询

将一个查询块（一个SELECT-FROM-WHERE语句称为一个查询块）嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

  eg：

       SELECT Sname                               /*外层查询/父查询*/

       FROM Student

      WHERE Sno  IN   ( SELECT Sno   /*内层查询/子查询*/    FROM SC     WHERE Cno= ' 2 ');

注意：SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询，子查询不能使用ORDER BY子句。

子查询

相关子查询（子查询的查询条件依赖于父查询）

不相关子查询（ 子查询的查询条件不依赖于父查询    由里向外，逐层处理）（可转化为连接）

带有IN谓词的子查询

eg：查询与“刘晨”在同一个系学习的学生。

SELECT Sno, Sname, Sdept     

FROM Student        

WHERE Sdept   IN   (SELECT Sdept        FROM Student           WHERE Sname= ' 刘晨 ');    #不相关子查询

转为连接：（自身连接）

SELECT  S1.Sno, S1.Sname,S1.Sdept      

FROM     Student S1,Student S2      

WHERE  S1.Sdept = S2.Sdept  AND    S2.Sname = '刘晨';   ##由于所有属性名都是同名属性，因此必须使用别名前缀

带有比较运算符的子查询（>，<，=，>=，<=，!=或< >）（子查询返回单值可用）

eg：找出每个学生超过他自己选修课程平均成绩的课程号。

SELECT Sno, Cno    

FROM    SC  x    

WHERE Grade >=(SELECT AVG（Grade）    FROM  SC  y   WHERE  y.Sno=x.Sno);

带有ANY（SOME）或ALL谓词的子查询（子查询返回多值时用）

比较运算符及语义：

ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系 ：

带有EXISTS谓词的子查询（存在量词 彐 ）

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

EXISTS谓词

1.若内层查询结果非空，则外层的WHERE子句返回真值

2.若内层查询结果为空，则外层的WHERE子句返回假值

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

NOT EXISTS谓词

1.若内层查询结果非空，则外层的WHERE子句返回假值

2.若内层查询结果为空，则外层的WHERE子句返回真值

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

SELECT Sname      

FROM     Student      

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

四、集合查询（参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同）

1.并操作UNION：将多个查询结果合并起来时，系统自动去掉重复元组（UNION ALL：不去除重复元组）

SELECT Sno     FROM SC    WHERE Cno=' 1 '     UNION     SELECT Sno      FROM SC    WHERE Cno= ' 2 ';##查询选修了课程1或者选修了课程2的学生。

2.交操作INTERSECT：既......又......（连接的查询是不同属性时可转换为AND连接）

SELECT *  FROM Student  WHERE Sdept='CS'  INTERSECT  SELECT * FROM Student WHERE Sage<=19； ##查询计算机科学系的学生与年龄不大于19岁的学生的交集。

可转化为：  SELECT *        FROM Student         WHERE Sdept= 'CS' AND  Sage<=19;

3.差操作EXCEPT：除了....

五、基于派生表的查询

子查询出现在FROM子句中，这时子查询生成的临时派生表，成为主查询的查询对象。

SELECT Sno, Cno    

FROM SC, (SELECT Sno, Avg(Grade)        FROM SC      GROUP BY Sno)  

                   AS   Avg_sc(avg_sno,avg_grade)  

WHERE  SC.Sno = Avg_sc.avg_sno    and   SC.Grade >=Avg_sc.avg_grade

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。

SELECT Sname    

FROM     Student,  (SELECT Sno FROM SC WHERE Cno=' 1 ')

                                 AS SC1   ##相当于自动补充Sno属性列

WHERE  Student.Sno=SC1.Sno;