五、单表查询

1.   数据查询的一般格式：

      SELECT ALL|DISTINCT|省略 目标列表达式列表

             FROM 表名

                 WHERE 条件表达式

                 GROUP BY 列名

                       HAVING 条件表达式

                 ORDER BY 列名 ASC|DESC,列名 ASC|DESC....

2.   单表查询（按照指定列进行查询）：

       a. 即查询只涉及一张表，不包括表自己跟自己的嵌套查询；

       b. 按照顺序查询若干列：

            i.   查询全体学生的学号与姓名：

                 SELECT Sno, Sname

                       FROM Student

                 实质是从表中一个个地取出元组，并从里面选取指定的字段组成一个新元组，最后这些新元组组成一张新表作为查询结果；

            ii. SELECT后面的属性的顺序可以和定义表时属性的声明顺序不一样，最后打印的结果也是按照SELECT后指定的顺序进行显示：查询全体学生的姓名、学号和所在系

                SELECT Sname, Sno, Sdept

                       FROM Student

                实质是在从元组中取出指定字段时按照用户指定的字段顺序拼接新元组；

       c. 查询全部列：使用通配符*表示整个元组的所有属性：查询全体学生的详细信息

                                    SELECT *

                                          FROM Student

       d. 查询列的计算结果：

            i.   用列的表达式的形式进行查询：查询全体学生的姓名和出生年份

                 SELECT Sname, 2004 - Sage

                      FROM Student

                 实质是从元组中取出指定字段时，拿该字段进行表达式运算，将得到的结果作为新字段按照指定顺序拼接到新元组中；

            ii.  表达式同样可以是函数表达式：查询全体学生姓名、出生年份、所在院系（用小写输出）

                 SELECT Sname, 2004 - Sage, LOWER(Sdept)

                        FROM Student

       e. 为查找结果表的列取新的名字：

            i.   规则是：在列表达式列表中，每个单元由两部分组成，前面的部分是列的内容，后面部分是列的名字，如果省略后面部分，则最终列的名字将是表达式本身（就是之前的例子）；

            ii.  查找d.ii.

                 SELECT Sname NAME, 2004 - Sage BIRTHYEAR, LOWER(Sdept) DEPARTMENT

                         FROM Student

3.   单表查询（按照指定行进行查询）：

       a. 取消重复的行：

            i.   当多个元组投影到某列时可能会出现重复行，用DISTINCT消除重复；

            ii.  查询选修了课程的学号，因为有些学生一个人选多个课程，因此查询时会出现重复行，用DISTINCT消除重复

                 SELECT DISTINCT Sno

                      FROM SC

            iii. 缺省情况下为ALL，表示全部显示出来（重复的行也都显示出来）；

       b. 条件查询：

            i.   即使用WHERE语句的查询；

            ii.  WHERE语句后面跟的是一个条件表达式，返回的是逻辑真假值；

            iii. 在挑选元组是只会挑选那些能让条件表达式为真的那些元组；

       c. 使用比较运算符：

            i.   SQL语句中的比较运算符和C语言相同；

            ii.  C语言中相等比较是==，而SQL中就是=；

            iii. SQL还有更多的比较运算符：

                 <>也表示不等于和!=一样；

                 !>不大于

                 !<不小于

                 NOT可以加在任何一个比较运算符之前，表示逻辑非

            iv. 查询计算机科学系全体学生的名单：

                 SELECT Sname

                       FROM Student

                            WHERE Sdept = ‘CS’

            v.  查询所有年龄在20岁以下的学生姓名及其年龄：

                 SELECT Sname, Sage

                      FROM Student

                           WHERE Sage < 20

            vi. 查询考试成绩不合格的学生的学号：

                 SELECT DISTINCT Sno

                      FROM SC

                           WHERE Grade < 60

       d. 确定范围：

            i.   使用(NOT) BETWEEN ... AND ..

            ii. AND后面的值一定要比前面的值大，前面是下线，后面上上限；

            iii. 查询年龄在20~23岁之间的学生的姓名、系别、年龄：

                 SELECT Sname, Sdept, Sage

                      FROM Student

                           WHERE Sage BETWEEN 20 AND 23

            iv.  查询年龄不在20~23岁之间的学生的性别、写别、年龄：

                  SELECT Sname, Sdept, Sage

                        FROM Student

                           WHERE Sage NOT BETWEEN 20 AND 23

       e. 确定集合：

            i.   使用(NOT) IN；

            ii.  查询所有计算机科学系、数学系、信息系学生的姓名和性别：

                 SELECT Sname, Ssex

                      FROM Student

                           WHERE CS IN('CS', 'MA', 'IS')

            iii. 查询不在ii.中的系的学生姓名和性别：

                 SELECT Sname, Ssex

                      FROM Student

                           WHERE CS NOT IN('CS', 'MA', 'IS')

       f.  字符匹配模糊查询：

           i.   使用(NOT) LIKE；

           ii.  最主要用于字符串类型数据的模糊匹配；

           iii. 通配符：

                %：表示任意长度的字符串，包括0个字符；

                _：表示任意长度为1的字符，不能为0个字符；

           iv. 查询所有姓刘的学生的姓名、学号和性别：

                SELECT Sname, Sno, Ssex

                     FROM Student

                           WHERE Sname LIKE '刘%'

           v.  查询姓“欧阳”并且全名为三个字的学生的姓名：

                SELECT Sname

                       FROM Student

                             WHERE Sname LIKE '欧阳__'

                 注意：一个中文字占两个字符，所以后面要跟两个_；

           vi. 查询名字中第二字为’阳‘的学生的姓名和学号：

                SELECT Sname, Sno

                      FROM Student

                            WHERE Sname LIKE '__阳%'

           v.  查询所有不姓”刘“的学生的姓名：

                SELECT Sname

                     FROM Student

                            WHERE Sname NOT LIKE '刘%'

           vi. 对字符进行转码：

                *1. 如果模糊查询的字符串中本身就含有%和_就必须对这两个字符进行转码；

                *2. 使用时需要ESCAPE关键字并制定一个转码字符，例如：

                       LIKE ‘DB\_Design’ ESCAPE '\'

                       这里将\作为转码字符，因此字符串中\_被转码为_而不被当做通配符看待了；

           vii. 查询以”DB_“为开头并且倒数第三个字符为”i“的课程的详细情况：

                 SELECT *

                        FROM Course 

                              WHERE Cname LIKE 'DB\_%i__' ESCAPE '\'

       g. 设计空值的查询：

            i.   使用关键字IS (NOT) NULL；

            ii.  查询没有成绩的学生的学号和课程号：

                 SELECT Sno, Cno

                      FROM SC

                             WHERE Grade IS NULL  //有成绩的就是Grade IS NOT NULL

       h. 多重条件查询：

            i.   使用逻辑运算符AND、OR对多个条件进行连接；

            ii.  查询计算机科学系年龄在20岁以下的学生的姓名：

                 SELECT Sname

                        FROM Student

                                WHERE Sdept = ‘CS’ AND Sage < 20

             iii. 查询计算机科学系、数学系和信息系学生的姓名和性别：

                  SELECT Sname, Ssex

                        FROM Student

                                WHERE Sdept = ‘CS’ OR Sdept = ‘MA’ OR Sdept = ‘IS’

4.   升序或降序查询：

       i.   使用ORDER BY关键字对查询结果中的元组按照指定的一个或者多个属性的升序（ASC）或者降序（DESC）排列；

       ii.  查询选修3号课程的学生的学号及成绩，结果按照成绩的降序排列：

            SELECT Sno, Grade

                  FROM SC

                       WHERE Cno = ‘3’

                       ORDER BY Grade DESC

       iii. 注意：

             *1. 对于缺省情况，系统默认为ASC；

             *2. 对于排序依据的属性如果为NULL，ASC情况下NULL最后显示，DESC情况下NULL最先显示；

       iv. 多重排序：查询全体学生，结果按照所在系的名字进行升序排序，同一系中的学生按照年龄降序排列：

                                SELECT *

                                      FROM Student

                                              ORDER BY Sdept, Sage DESC

5.   聚集函数：

       a. 即统计函数，对一整张表的某列属性进行统计，其统计结果作为一个元组中的一个字段；

       b. 常用的聚集函数：

            COUNT：统计元组或者某一列的个数；

            SUM：对某一列的值求和；

            AVG：对某一列的值求平均值；

            MAX：求某一列的最大值；

            MIN：求某一列的最小值；

       c. 参数：

            i.   通常有两个：选项 列名

            ii.  选项为DISTINCT或ALL，是用于消除重复值的，缺省情况下为ALL；

            iii. 当COUNT用于计算总人数时列名为*；

       d. 查询学生总人数：

           SELECT COUNT(*)

                 FROM Student

       e. 查询了选修了可的学生总人数：

            SELECT COUTN(DISTINCT Sno)

                 FROM SC

       f.  查询了选修了1号课程的学生的平均成绩：

           SELECT AVG(Grade)

                FROM SC

                     WHERE Cno = ’1‘

       g. 查询了选修1号课程的学生的最高分数：

            SELECT  MAX(Grade)

                 FROM SC

                      WHERE Cno = ’1‘

        h. 查询200215012号学生选修的课程的总学分（使用了多表查询的技术，这里先不管）：

             SELECT SUM(Ccredit)

                  FROM Course, SC

                      WHERE Sno = ’200215012‘ AND SC.Cno = Course.Cno

        i.  注意：

            *1. WHERE条件表达式中不得用聚集函数，否则将报错；

            *2. COUNT函数将略过所有具有NULL值的元组，对于*，只要某个字段时NULL值就被略过，对于某列，只要该列字段时NULL值就会被略过；

6.   分组查询：

       a. 对查询结果按照某一列的值进行分组，值相同的为一组；

       b. 结果并不是每一组为一张表的形式，而是将多个组合并成一张表的形式呈现出来；

       c. 使用GROUP BY关键字；

       d. 求各个课程号及相应的选课人数：

            SELECT Cno, COUNT(Sno)

                   FROM SC

                        GROUP BY Cno

       e. 注意： 一旦分组后，聚集函数可以作用于每一组，在每一组中都形成一个结果！！！

       f.  对组进行筛选：

           i.   即分组后只选取满足一定条件的组；

           ii.  对元组的筛选使用WHERE条件表达式，类似，对组进行筛选用HAVING条件表达式；

           iii. 在HAVING条件表达式中可以使用聚集函数（作用于每个组的），而在WHERE条件表达式中则不能使用聚集函数；

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

                SELECT Sno

                    FROM SC

                        GROUP BY Sno

                                HAVING COUNT(*) > 3

                 本质是先分组，然后对每组计算总数，根据条件筛选符合条件的组作为结果；