数据库笔记4————关系数据库标准语言SQL中(SQL的数据查询)

清疚 2022-06-06 08:58 505阅读 0赞

# 数据库笔记4————关系数据库标准语言SQL中(SQL的数据查询) #

## 一.主要内容和知识框架 ##

![这里写图片描述][format_png]

## 二.查询语句的一般格式和准备。 ##

### 1.格式(中括号内的内容可有可无) ###

> SELECT \[ALL|DISTINCT 目标表达式1 目标表达式2…\]  
> FROM 表名1 \[表名2…\]  
> \[WHERE 条件表达式\]  
> \[GROUP BY 列名1 \[HAVING 条件表达式\]\]  
> \[ORDER BY 列名2 \[ASC|DESC\]\];

### 2.解释 ###

*  SELECT的语句含义是根据WHERE语句中的条件表达式的条件，从FROM子句指定的表或者视图找出满足条件的元组，在根据SELECT子句的目标表达式，选出元组中的属性值行程结果表。
 *  如果没有WHERE语句，则表示所有元组，如果SELECT后面是\*表示所有属性。
 *  DISTINCT表示去除结果表中的重复行。
 *  若出现GROUP语句则将结果按照<列名1>进行分组，该属性值相等的元组为一个组。
 *  若出现HAVING语句，则符合条件的组才输出
 *  若出现ORDER语句，则将结果按照列名2的值进行升序或者降序处理。

### 3.下面例子所涉及的三张表 ###

#### a.表一： ####

*  表名：Student
 *  属性：学号Sno(主键),姓名Sname，性别Ssex，年龄Sage，所在系Sdept。  
    ![这里写图片描述][format_png 1]

#### b.表二 ####

*  表名：Course
 *  属性:课程号Cno(主键)，课程名Cname，先修课Cpno，学分Ccredit  
    ![这里写图片描述][format_png 2]

#### c.表三 ####

*  表名：SC
 *  属性: 学号Sno，课程号Cno，成绩Grade。  
    ![这里写图片描述][format_png 3]

## 三.单表查询 ##

### 1.选择若干属性(列) ###

#### a.查询指定的列 ####

*  对应关系代数中的投影运算，即从表中选择用户感兴趣的若干列，若不指定目标列，而用\*代替则表示所有列
 *  例: 查询全体学生的学号.姓名和所在系的相关信息

SELECT Sno,Sname,Sdept
    FROM Student;

![这里写图片描述][format_png 4]

#### b.查询经过计算 ####

*  SELECT语句中的目标列可以是表中的属性列，也可以是表达式。对于表达式可以自定义一个别名
 *  例1：查询全体学生的出生年月，并在出生年月日前加一个"year of Birth"

SELECT Sname,'Year of Birth',2017-Sage
    FROM Student;

![这里写图片描述][format_png 5]

*  例2: 查询全体学生的出生年，并为出生年份指定一个别名Birthday。查询所在系，并用小写输出。

SELECT Sname,2017-Sage Birthday,LOWER(Sdept)
    FROM Student;

![这里写图片描述][format_png 6]

### 2.选择若干元组(行) ###

*  主要是根据WHERE不同的条件表达进行不同的查询。

#### a.取消重复的行 ####

*  原来两个并不完全相同的列，投影指定集合后，可能会出现相同的行，在SQL中DISTINCT用来消除重复的行
 *  例：在SC表中，查询所有选了课程的学生的学号。并消除重复的学号。

SELECT DISTINCT Sno
    FROM SC;

![这里写图片描述][format_png 7]

#### b.基于比较运算的查询 ####

*  常用的比较运算符:=,>,<,>=,<=,!=,<>,!>,!<,NOT+上述运算符
 *  例1:查询计算机系所有学生的学号和姓名

SELECT Sno,Sname
    FROM Student
    WHERE Sdept ='CS';

*  例2:查询考试成绩及格的学生的学号。

SELECT Sno
    FROM SC
    WHERE Grade>60;//等价于WHERE NOT Grade<=60;

![这里写图片描述][format_png 8]

#### c.基于范围条件的查询 ####

*  确定范围的查询条件:BETWEEN ADD,NOT BETWEEN AND;
 *  例： 查询年龄在20~23岁之间的学生的姓名，所在系和性别

SELECT Sname,Sdept,Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 23;//等价于: WHERE Sage >= 20 AND Sage<=23;

![这里写图片描述][format_png 9]

#### d.基于集合的查询 ####

*  确定集合的查询条件:IN,NOT IN;
 *  例：查询计算机系,数学系和信息系的学生姓名和年龄

SELECT Sname,Sage
    FROM Student
    WHERE Sdept IN ('CS','MA','IS');//等价于: Sdept='CS' OR Sdept='MA' OR Sdept='IS';

![这里写图片描述][format_png 10]

#### e.基于字符串匹配的查询 ####

*  即模糊查询，所涉及的两个通配符：%(表示任意长度)，\_表示任意单个字符，如果查询的字符串本身就带有%和\_,则使用ESCAPR进行转义。
 *  语法格式: \[NOT\] LIKE ‘匹配串’
 *  例1：查询所以名字姓李，第三个字为勇的学生的姓名，学号，和学号。

SELECT Sname,Sno,Ssex
    FROM Student
    WHERE Sname LIKE '李_勇%';

![这里写图片描述][format_png 11]

*  例2:查询DB\_Design课程的课程号和学分

SELECT Cno,Ccredit
    FROM Course
    WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

#### f.基于空置的查询 ####

*  空值的查询条件：IS NU,IS NOT NULL;
 *  例:查询没有考试成绩的学生及相应的课程号

SELECT Sno,Cno
    FROM SC
    WHERW Grade IS NULL;

![这里写图片描述][format_png 12]

#### g.基于多个条件的查询 ####

*  多条件的查询条件:AND,OR,NOT
 *  例：查询计算机系年龄在20岁以下的学生的学号，姓名，和性别.

SELECT Sno,Sname,Ssex
    FROM Student
    WHERE Sdept='CS'AND Sage<20;

![这里写图片描述][format_png 13]

### 3.查询结果排序 ###

*  在SQL中,可以使用ORDER BY 子句实现查询结果的排序，其中ASC为升序，DESC为降序，默认升序
 *  例1：查询选修了2号课程的学生的学号及成绩，查询结果按成绩降序排序

SELECT Sno,Grade
    FROM SC
    WHERE Cno=2
    ORDER BY Grade DESC;

*  例2：查询全体学生情况，查询结果所在系的升序排序，同一系安装学生年龄降序排序

SELECT *
    FROM Student
    ORDER BY Sdept,Sage DESC;

![这里写图片描述][format_png 14]

### 4.聚集函数 ###

*  聚集函数可以对表中的数据进行计算，得到统计结果。在SQL中提供的聚集函数有下列结果。
 *  AVG:用于计算查询结果的平均值。
 *  MIN:查询结果中的最小值
 *  MAX:查询结果中的最大值
 *  SUM:该函数用来计算某列的和
 *  COUNT:该函数用来统计表中或者结果中元组的个数
 *  上述函数若指定DISTINCT短语，则表示统计时取消重复值，默认为ALL
 *  例1:计算选修2号课程的学生的平均分

SELECT AVG(Grade) AS '平均成绩'
    FROM SC
    WHERE Cno=2;

*  例2:统计学生的总人数

SELECT COUNT(*) AS '总人数'
    FROM Student;

![这里写图片描述][format_png 15]

### 5.查询结果分组 ###

*  GROUP语句则将结果按指定列进行分组，该属性值相等的元组为一个组。
 *  若出现HAVING语句，则符号条件的组才输出。
 *  HAVIGN语句和WHERE子句的区别
    
     *  WHERE子句中的条件的作用对象是基本表或者视图，而HAVING子句的对象是每个分组。
     *  HAVING子句可以在条件中出现在聚集函数，但WHERE子句不可以
 *  例1:查询每门课程的课程号及相应人数

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

*  例2:查询有两门以上课程在85分以上的学生的学号。

SELECT Sno
    FROM SC
    WHERE Grade>85
    GROUP BY Sno HAVING COUNT(*)>=2;

![这里写图片描述][format_png 16]

## 四.连接查询 ##

*  查询时涉及两个及两个以上表时，称为连接查询。

### 1.等值连接和非等值连接 ###

*  连接查询的WHERE子句可以连接两个表的条件称为连接条件，或者连接谓词。

> 格式为：表名1.列名1 比较运算符 表名2.列名2  
> 比较运算符可以是 = > < <= >= !=

*  格式中，如果表中的属性名唯一，可以省略表名前缀
 *  如果比较运算符是=时，可以称为等值连接，其他为非等值连接
 *  例：查询每个学生及其选修课的情况。

> 分析：学生的情况放在Student表中，学生的选课情况放在SC表中，因此该查询涉及两个表的查询。而Student表中和SC表中都有Sno属性，所以可以通过属性Sno将Student表和SC表进行等值连接。

SELECT Student.*,SC.*
    FROM Student,SC
    WHERE Student.Sno = Sc.Sno;

*  通过查询结果可以看出，等值连接中存在重复列，去掉重复的列就是自然连接

SELECT Student.*,SC.Cno,SC.Grade
    FROM Student,SC
    WHERE Student.Sno = Sc.Sno;

![这里写图片描述][format_png 17]

### 2.自身连接 ###

*  连接操作不仅可以在不同表之间进行，也可以在同一个表内进行连接，称之为自身连接。
 *  自身连接可以看做一个表的两个副本进行连接，在自身连接中，必须为表指定两个别名，使之成为逻辑上的两张表。
 *  例：查询每门课的间接先修课

> 分析：在Course表中，只有每门课的先修课，而没有先修课的先修课。要得到该信息，则必须对一门课先找其先修课，在按照先修课的课程号在Course表中查找它的先修课。这样，必须想象有两个完全相同的Course表，把这两个表按照课程表和先修课进行连接得到结果即可。为Course表指定两个别名：First和Second。

SELECT First.Cno,Second.Cpno
    FROM Course First,Course Second
    WHERE First.Cpno = Second.Cno;

![这里写图片描述][format_png 18]

### 3.外连接 ###

*  上面提到的连接，只有满足条件的元组，才能出现在结果集中。如果想要输出那些不满足的条件的元组，就要使用外连接。

> 格式为：SELECT 目标表达式  
> FROM 表名1 \[LEET|RIGHT|FULL\[OUTER\]\] JOIN 表名2  
> ON 表名1.列名1 =表名2.列名2；

*  中括号中的子句可以出现，也可以不出现。
 *  LEET左外连接，RIGHT右外连接，FULL全外连接
 *  例: 查询每个学生及其选修课的情况(使用左外连接)

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

![这里写图片描述][format_png 19]

### 4.复合条件连接 ###

*  WHERE子句有多个连接条件，称为复合条件连接
 *  例：查询选修了数据库并且成绩在89分以上的学生的学号和姓名

SELECT Student.Sno,Sname
    FROM Student,Course,SC
    WHERE Student.Sno = Sc.Sno AND SC.Cno = Course.Cno
        AND Cname ='数据库'AND Grade>=90;

![这里写图片描述][format_png 20]

## 五.嵌套查询 ##

*  将一个查询块嵌入到另外一个查询块的WHERE子句中，其中上层的查询块称为外层查询/父查询，下层的查询块称为内层查询/子查询。SQL中允许多层嵌套，但是子查询中SELECT语句不能出现ORDER BY子句。

### 1.不相关子查询 ###

*  即内层查询不依赖外层的查询。
 *  不相关子查询的执行过程：先执行内查询，内查询结果不在结果集中显示，而是传递给外查询，作为外层查询的条件来使用并显示查询结果。
 *  例1：查询选修了1号课程的学生的姓名

SELECT Sname
    FROM Student
    WHERE Sno IN
            (SELECT Sno
            FROM SC
            WHERE Cno =1);
    
    //也可以用连接查询实现
    
    SELECT Sname
    FROM Student,SC
    WHERE Student.Sno = SC.Sno AND Cno=1;

![这里写图片描述][format_png 21]

*  只有在确定结果是单值时，才可以使用><=等比较运算符，否则只能使用IN谓词。
 *  例2：查询和李大勇在同一系学习的学生姓名和学号

SELECT Sno,Sname
    FROM Student
    WHERE Sdept = 
            (SELECT Sdept
            FROM Student
            WHERE Sname = '李大勇');

![这里写图片描述][format_png 22]

*  使用ANY和ALL谓词时，必须使用运算符，ANY表示和一组值的任意一个，ALL表示和一组值的每一个。
 *  例3：查询其他系中比计算系某一学生年龄小的学生姓名和年龄

SELECT Sname ,Sage
    FROM Student
    WHERE Sdept != 'CS'
        AND Sage < ANY(SELECT Sage
             FROM Student
            WHERE Sdept = 'CS');
        
    //等价于下面这个查询
    SELECT Sname ,Sage
    FROM Student
    WHERE Sdept != 'CS'
        AND Sage < (SELECT MAX(Sage)
                FROM Student
                WHERE Sdept = 'CS');

![这里写图片描述][format_png 23]

### 2.相关子查询 ###

*  在相关子查询中，子查询的结果依赖外层查询，通常是在子查询WHERE子句中引用父查询的表
 *  和不相关查询不同，不相关查询只执行一次，而相关查询中，子查询需要重复的执行。
 *  例：查询每个学生超过他所选课程的平均分的课程号

SELECT Sno,Cno
    FROM SC x
    WHERE Grade >=(SELECT AVG(Grade)
            FROM SC y
            WHERE y.Cno = x.Cno);

![这里写图片描述][format_png 24]

### 3.带有EXISTS谓词的查询 ###

*  EXISTS表示存在量词，可以用来判断查询结果是否存在数据。由EXISTS引出的嵌套子查询不返还结果，只返回真假。
 *  由于EXISTS不返还数据，所以一般查询效率高于不相关查询
 *  例1: 查询所有选修3号课程的学生的姓名

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

![这里写图片描述][format_png 25]

*  例2: 查询选修了选修全部课程的学生姓名

//选修了选修全部课程=没有一门课程是他不选的
    SELECT Sname
    FROM Student
    WHERE NOT EXISTS
        (SELECT *
        FROM Course
        WHERE NOT EXISTS
            (SELECT *
            FROM SC
            WHERE Sno = Student.Sno AND Cno =Course.Cno));

![这里写图片描述][format_png 26]

## 六.集合查询 ##

*  利用UNION语句把两个或者两个以上的查询结果合并为一个结果集，称为集合查询
 *  合并结果集有以下限制
    
     *  参与UNION运算的各查询结果的列数必须相同，且对应的数据类型也相同。
     *  最后结果集中的列名来自第一个SELECT语句
     *  ORDER BY子句只能出现在末尾，将最后结果排序
     *  在合并结果时，默认将最后结果集中的重复元组删除，除非使用ALL关键字说明
 *  例：查询选修了1号课程和2号课程的所有学生的学号

SELECT Sno
    FROM SC
    WHERE Cno = 1
    UNION
    SELECT Sno
    FROM SC
    WHERE Cno = 2;
    //也可以使用复合条件OR

![这里写图片描述][format_png 27]

[format_png]: /images/20220606/0dead585d8a442abb4f2d0f070f7bcf0.png
[format_png 1]: /images/20220606/f9a984e0c63c4cec965afb55502e1ec5.png
[format_png 2]: /images/20220606/bd6cf3e7101d4de1a4c24306a2e9214e.png
[format_png 3]: /images/20220606/d4d46d9d046b4e22bc1cb5f0322d41f0.png
[format_png 4]: /images/20220606/aca07a5ffc4748d49358c944b064be1c.png
[format_png 5]: /images/20220606/77dc4108479a4606b59e1ae1d94b9e0d.png
[format_png 6]: /images/20220606/1969bbf59d6e45d6817b373cc58711a3.png
[format_png 7]: /images/20220606/b462d110b1db4fc6915a2c0db0f13146.png
[format_png 8]: /images/20220606/277e3412ccc74b8f947f425019d443fa.png
[format_png 9]: /images/20220606/e8e1ac4e181c4ad8b388a57f0c6befa4.png
[format_png 10]: /images/20220606/0ae1892b056c4a42ae527355b5b5669e.png
[format_png 11]: /images/20220606/e27059ac9e2d414fa0b5ce4270acdd32.png
[format_png 12]: /images/20220606/77679fd40b4447aa8c2f9c7c34730600.png
[format_png 13]: /images/20220606/1601b274815040a0b751c67369c7ef12.png
[format_png 14]: /images/20220606/fd56d1f3b67249df89ebed0a59e6ea5e.png
[format_png 15]: /images/20220606/14506b7f12f54fbfb975787a67fa3d47.png
[format_png 16]: /images/20220606/520744bd7fcf49bbb4f86b92017b6ea9.png
[format_png 17]: /images/20220606/839392c54b0349f58bf1404ceda6dd77.png
[format_png 18]: /images/20220606/f911993761414693bf8737141ce3fca2.png
[format_png 19]: /images/20220606/3fea883579f6481dbbe9501073ed3503.png
[format_png 20]: /images/20220606/1fbef2b6f13d48febfa7bee66dcae531.png
[format_png 21]: /images/20220606/4d6aa72a73c440ee88e52b788ee71865.png
[format_png 22]: /images/20220606/64cb9f56b04541e0b6fce722ceb30ea2.png
[format_png 23]: /images/20220606/fba64796a22e4a8ba175a790563f7701.png
[format_png 24]: /images/20220606/5571d158ca6f41c3a8a24c50bd262381.png
[format_png 25]: /images/20220606/855ddd231691426abc2cf9c2e3e89eb0.png
[format_png 26]: /images/20220606/d3a5c882113445c1bfe9bcb095a44408.png
[format_png 27]: /images/20220606/c851ca5658ed45b397b099548d000c9d.png