SQL基础知识 - 数据查询

关键字：SELECT，

• 数据查询的一般格式
  • SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式>] ...
  • FROM <表名或视图名> [, <表名或视图名>] ...  
  • [ WHERE <条件表达式> ]
  • [ GROUP BY <列名1> [ HAVING <条件表达式> ]]    
  • [ ORDER BY <列名2> [ ASC | DESC ]]
    • 如果有GROUP BY，结果按照<列名1>进行分组；带HAVING短语，满足条件的组才会输出
    • 如果有ORDER BY，结果还要按照<列名2>进行升序(默认)或者降序
• 单表查询
  • 选择表中的若干列
    • 查询指定列：SELECT Sno, Sname FROM Student;    # 查询全体学生的学号和姓名
    • 查询全部列：SELECT * FROM Student;  # 或者 SELECTALLFROM Student;
    • 查询经过计算的值：SELECT Sname, 2013-Sage FROM Student;  # 查询全体学生的姓名和出生年份
      • 输出结果：Sname    2013-Sage
      • 修改别名：SELECT Sname Name, 2013-Sage BirthYear FROM Student; 
      •                     或者SELECT SnameAS Name, 2013-Sage AS BirthYear FROM Student; 
      • 输出结果：Name    BirthYear
    • 
      
  • 选择表中若干元组
    • 消除取值重复的行，添加DISTINCT。
      • SELECT DISTINCT Sname from Student;  
    • 查询满足条件的元组
      • 常用的查询条件
        • 比较：=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比较运算符
        • 确定范围：BETWEEN AND，NOT BETWEEN AND
        • 确定集合：IN，NOT IN
        • 字符匹配：LIKE，NOT LIKE
          • %：代表任意长度
          • _：代表任意单个字符
        • 空值：IS NULL， IS NOT NULL
        • 多重条件(逻辑运算)：AND，OR，NOT
      • 举例：
        • 比较大小
          • SELECT Sname FROM Student WHERE Sage < 20;    # 查询年龄小于20岁的学生姓名
          • SELECT DISTINCT Sno FROM Student WHERE Grade < 60;    # 查询不及格的学生学号
        • 确定范围
          • SELECT Sname FROM Student WHERE Sage NOT BETWEEN 20 AND 25;   # 查询年龄不在20到25岁之间的学生姓名
            
        • 确定集合
          • SELECT Sname FROM Student WHERE Sdpt IN ('CS', 'MA', 'IS');    # 查询在CS，MA，IS系的学生姓名
        • 字符匹配
          • SELECT Sname FROM Student WHERE Sname LIKE '刘%';    # 查询姓"刘"的同学姓名
            
          • SELECT Sname FROM Student WHERE Sname LIKE '欧阳__';    # 查询查询姓为"欧阳"，全名只有3个汉字的学生的姓名。
            
          • SELECT Sname FROME Student WHERE Cname LIKE 'DB\_Design' ESCAPE'\';   # 查询课程中有DB_Design的学生姓名
            • ESCAPE '\'表示把"\"当做转换码字符，这样后面的字符"_"不具有通配符的含义，变为普通的"_"。
        • 涉及空值的查询
          • SELECT Sname FROM Student WHERE Sgrade IS NULL;    # 查询分数为空的学生姓名
        • 多重条件查询
          • SELECT Sname FROM Student WHERE Sname LIKE '刘%' AND Sage < 20;
            
          • SELECT Sname FROM Student WHERE Sname LIKE '刘%' OR Sname LIKE '张%';
            
  • ORDER BY 子句
    • SELECT Sname FROM Student Where Sdpt = 'CS' ORDER BY Sgrade;    # 查询CS学院中学生的姓名按照成绩升序排列(默认)。
  • 聚集函数
    • 主要函数有
      • COUNT([DISTINCT | ALL] *)              统计元组的个数
      • COUNT([DISTINCT | ALL]  <列名>)  统计一列中值的个数
      • SUM([DISTINCT | ALL]  <列名>)       计算一列值的总和(次列必须是数值型)
      • AVG([DISTINCT | ALL]  <列名>)       计算一列值的平均值(次列必须是数值型)
      • MAX([DISTINCT | ALL]  <列名>)       求一列值中的最大值
      • MIN([DISTINCT | ALL]  <列名>)         求一列值中的最小值
        • 如果指定DISTINCT短语，则计算式要取消指定列中的重复值，反之不去掉重复值。缺省为ALL。
        • 除COUNT(*)外，都跳过空值而只处理非空值
    • 举例：
      • SELECT COUNT(*) FROM Student;    # 计算学生表中总行数
      • SELECT MAX(Sgrade) FROM Student WHERE Cno= 1;    # 计算课程1中学生成绩最高分
      • SELECT SUM(Ccredit) FROM Student WHERE Sno = '20081624';    # 计算学生20081624的总学分数
  • GROUP BY 子句
    • GROUP BY将查询结果按某一列或者多列的值分组，值相等的为一组。对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果；分组后的积极函数将作用于每一个组，即每一个组都有一个函数值。
    • 如果分组后还要去按照一定的条件对这些组进行筛选，则使用HAVING(作用组)指定筛选条件。
    • 举例：
      • SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;    # 查询各个课程及相应的选课人数
      • SELECT Cno FROM SC GROUP BY Cno HAVING COUNT(*)>30;    # 查询超过30个选的课程号
• 连接查询
  • 等值与非等值连接查询
    • 一般格式
      • WHERE [<表名1>.]<列名> <比较运算符> [<表名2>.]<列名2>
      • 比较运算符主要有：=、>、<、>=、<=、!-(<>)等
      • 连接谓词还可以为：[<表名1>.]<列名> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
    • 例
      • SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno;  # 如果属性名唯一可以省略表名前缀
      • 将Student中Sno项循环雨SC中Sno进行匹配，并输出查找成功的项。
  • 自身连接
    • 需要为表取两个别名，例如
    • SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno=SECOND.Cno;
      • FIRST和SECOND为表Course的别名
  • 外连接
    • 左外连接：列出左边关系中所有元组，连接的元素如果没有值，填空置NULL.
    • 右外连接：列出右边关系中所有元组
    • 例如：
      • SELECT Student.Sno, Sname, Sage, Cno,, Cgrade
      • FROM Student LEFT OUT JOIN SCON(Student.Sno = SC.Sno);
      • /*或者FROM Student LEFT OUT JOIN SC USING(Sno)去掉重复值*/
  • 复合条件连接：
    • 在WHERE条件中加入多个连接条件；或者多个表之间连接，例如
    • SELECT Student.*, SC.* FROM Student, SC WHERE Student.Sno = SC.Sno AND SC.Sno='2' AND SC.Cgrade>90;
      
• 嵌套查询
  • SELECT-FROM-WHERE称为一个查询块，将一个查询块中嵌套在另一个查询块的WHERE子句或者HAVING短语中。
  • 带有IN谓词的子查询(IN代表一个取值区间)
    • SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN
    • (SELECT Sdept FROM Student WHERE Sname='刘成');    # 查询和"刘成"同一个系的学生
  • 带有比较运算符的子查询，例如
    • SELECT Sno, Cno FROM SC x WHERE Cgrade >= 
    • (SELECT AVG(Cgrade) FROM SC y WHERE y.Sno=x.Sno);    # 查询每个学生超过他选修课程平均成绩的课程号
  • 带有ANY(某一个)或者ALL(所有)谓词的子查询
    • 查询其它系中比计算机科学系某一学生年龄小的学生姓名和年龄
      • SELECT Sname, Sage FROM Student WHERE Sage < ANY
      • (SELECT Sage FROM Student WHERE Sdept='CS') AND Sdept <> 'CS';
      • 或者SELECT Sname, Sage FROM Student WHERE Sage <
      • (SELECT MAX(Sage) FROM Student WHERE Sdept='CS') AND Sdept != 'CS';
    • 可以用谓词IN，NOT IN和聚合函数MAX，MIN和ANY，ALL替换
  • 带有EXISTS谓词的子查询
    • EXISTS代表存在量词，带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值true或这逻辑假值false。
    • 例如，查询没有选修1号课程的学生名字：
      • SELECT Sname FROM Student WHERE NOT EXISTS
      • (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1');
• 集合查询
  • 集合操作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT。
    • UNION：并集。将多个查询结果合并起来，系统自动去掉重复元组。如果需要保留重复元组用UNION ALL
      • 例如：SELECT * FROM Student WHERE Sdept = 'CS' UNION SELECT * FROM Student WHERE Sage <=19;  # 查询计算机科学系的学生和不大于19岁的学生
    • INTERSECT：交集。
      • SELECT * FROM Student WHERE Sdept = 'CS' INTERSECT SELECT * FROM Student WHERE Sage <=19;  # 查询计算机学院系不大于19岁的学生
        
    • EXCEPT：差集。
      • SELECT * FROM Student WHERE Sdept = 'CS' EXCEPT SELECT * FROM Student WHERE Sage <=19;    # 查询计算机学员中年龄大于19岁的学生