QT学习-07-sqllite数据库-查询

数据库查询

1.数据库查询简述

SELECT语句的功能非常强大，其选项也非常丰富。

语法：

SELECT [ALL|DISTINCT][TOP n[PERCENT]]<目标列表达式>[, … n] [INTO <新表名>]
FROM <表名>|<视图名>[, … n]
[WHERE <条件表达式>]
[GROUP BY <列名l>
[HAVING <条件表达式>]]
[ORDER BY <列名2>[ASC|DESC]]；

ALL： 表示输出所有记录，包括重复记录。DISTINCT表示输出无重
复结果的记录。TOP n [PERCENT]指定返回查询结果的前n行数据，如
果指定PERCENT关键字，则返回查询结果的前n%行数据。

<目标列表达式>： 描述结果集的列，它制定了结果集中要包含的列的名称。
INTO <新表名>： 指定使用结果集来创建新表，<新表名>指定新表的名称。
FROM <表名>|<视图名>： 该子句指定从中查询到结果集数据的源表名或源视图名。
WHERE <条件表达式>： 该子句是一个筛选条件，它定义了源表或源视图中的行要满足SELECT语句的要求所必须达到的条件。
GROUP BY <列名l>： 该子句将结果按<列名l>的值进行分组，该属性列值相等的元组为一个组，通常需要在每组上取聚集函数值。
HAVING <条件表达式>： 该子句是应用于结果集的附加筛选，用来向使用GROUP BY子句的查询中添加数据过滤准则。
ORDER BY <列名2> [ASC|DESC]： 该子句定义了结果集中行的排序顺序，升序使用ASC关键字，降序使用DESC关键字，默认为升序。

基本语句SELECT—FROM—WHERE的含义是： 根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。

查询分类：

• 单表查询
• 多表联表查询
• 分组聚合查询
• 嵌套子查询
• 查询集合操作

2.简单查询：

语法：

SELECT [*]|[列名] 
   FROM [表名][,表名]
   WHERE [查询条件]

应用示例：

select * from tab_student where stu_no=‘GN1003’  --查询学号为GN1003的学生的座位号

带where的查询：

--查询年龄在18岁到23之间的所有学生
     select * from t_student where sage>=18 and sage<=23;
     
--查询年龄不在18~23岁的所有学生
     select * from t_student where sage<18 or sage>23;
     
--查询非jn200405001班级的学生信息
     select * from t_student where not sclass='jn200405';
--或  
     select * from t_student where sclass<>'jn200405';

带order by的查询：

--查询t_student 表中所有数据，按降序输出
select * from t_student order by sno   desc

--查询t_student 表中所有数据，按降序输出（默认是升序，asc不写也是升序）
select * from t_student order by sno  asc

带between…and的查询：

--查询jn200505班002课程成绩在60~80分的成员记录
 select * from t_score where sno like 'jn200505%' and Cno='002' and  score between 60 and 80
 
 --说明：选取该列数据属于between.. and区间,包含边界值的，可以使用如下语句替代：
  select * from t_score
    where sno like 'jn200505%' and scNo='002'
    and  score>=60 and score<=80

带有GROUP BY子句的查询

• 在实际应用中，经常需要将查询结果进行分组，然后再对每个分组利用统计函数进行统计。
• SELECT的GROUP BY子句和HAVING子句来实现分组统计。
• GROUP BY子句可以将查询结果按属性列或属性组合对元组进行分组，每组元组在属性或属性列组合上具有相同的统计函数值。在 GROUP BY
  对记录进行分组之后，HAVING 将显示由满足 HAVING 子句条件的 GROUP BY 子句进行分组的任何记录。

--例 查询选修每门课程的课程号及参加该门课程考试的学生总人数。 （CNO课程号，GRADE分数）

SELECT CNO,COUNT(*) AS '人数' 
FROM SC
WHERE GRADE IS NOT NULL
GROUP BY CNO

--该查询在WHERE语句中给出了已经参加考试，有了成绩的学生人数。
--注意：在统计函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值。 

带HAVING子句的分组查询：

--例 查询出选课人数超过8人的课程号。 

    SELECT CNO AS '课程号',COUNT(SNO) AS '人数'
    FROM SC
   GROUP BY CNO
   HAVING COUNT(SNO)>=8

SQL通配符：

3.嵌套查询

嵌套查询概述：

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

带有IN谓词的子查询：

[例题]： 查询与“刘晨”在同一个系学习的学生。(假设刘晨在IS系)

此查询要求可以分步来完成

① 确定“刘晨”所在系名

   SELECT  Sdept  
    FROM     Student                            
    WHERE  Sname= ' 刘晨 '；

② 查找所有在IS系学习的学生。

      SELECT   Sno，Sname，Sdept     
      FROM      Student                 
      WHERE  Sdept= ' IS '；

将第一步查询嵌入到第二步查询的条件中

    SELECT Sno，Sname，Sdept
    FROM Student
    WHERE Sdept  IN
          (SELECT Sdept
           FROM Student
           WHERE Sname= ‘ 刘晨 ’)；

此查询为不相关子查询。DBMS求解该查询时也是分步去做的。

带有ANY或ALL谓词的子查询：
需要配合使用比较运算符

>ANY 大于子查询结果中的某个值       
 > ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值    
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值    
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值    
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值        
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值

[例题] 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄

SELECT Sname，Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
                      FROM    Student
                      WHERE Sdept= ' IS ')
                     AND Sdept <> ' IS ' ;  
    /* 注意这是父查询块中的条件 */

带有EXISTS谓词的子查询:

EXISTS谓词

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则返回真值
若内层查询结果为空，则返回假值
由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义

[例题] 查询所有选修了1号课程的学生姓名。
用嵌套查询

     SELECT Sname
     FROM Student
     WHERE EXISTS
       (SELECT *
        FROM SC          /*相关子查询*/
        WHERE Sno=Student.Sno AND  Cno= ' 1 ')；

4.连接查询

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

内连接：

内连接是从两个表的笛卡尔积中，选出符合连接条件的元组。它使用INNER JOIN连接运算符，并且使用ON关键字指定连接条件。内连接是一种常用的连接方式，如果在JOIN关键字前面没有指定连接类型，那么默认的连接类型就是内连接。
内连接的语句格式如下：

SELECT <目标列表达式> [, … n]
FROM <表1> INNER JOIN <表2>
ON <连接条件表达式>[, … n]

注意：连接条件表达式中的各连接字段类型必须是可比的，但名称不必相同。
查询每个学生及其选修课程的情况。学生情况存放在S表中，学生选课情况存放在SC表中，所以本查询实际上涉及S与SC两个表。这两个表之间的联系是通过公共属性SNO实现的。

SELECT S.*,SC.*
FROM S INNER JOIN SC
ON S.SNO=SC.SNO 

**SQL Server执行该连接操作的一种可能过程是：**首先在表S中找到第一个元组，然后从头开始扫描SC表，逐一查找与S第一个元组的SNO相等的SC元组，找到后就将S中的第一个元组与该元组拼接起来，形成结果表中的一个元组。SC全部查找完后，再找S中第二个元组，然后再从头开始扫描SC，逐一查找满足连接条件的元组，找到后就将S中的第二个元组与该元组拼接起来，再形成结果表中的一个元组。重复上述操作，直到S中的全部元组都处理完毕为止。

查询计算机科学系（CS）的学生所选课程的课程号和平均成绩。

SELECT SC.CNO,ROUND(AVG(SC.GRADE),2) AS 'AVERAGE'
FROM S INNER JOIN SC
ON S.SNO=SC.SNO  AND S.SDEPT='CS' 
GROUP BY CNO

为了使平均成绩四舍五入保留两位小数，引入了函数ROUND()。查询结果如图所示。

外连接：

在外连接中，不仅包含那些满足连接条件的元组，而且某些表不满足条件的元组也会出现在结果集中。

(1) 左外连接

左外连接是对连接条件左边的表不加限制。当左边表元组与右边表元组不匹配时，与右边表的相应列值取NULL。语句格式如下：
SELECT <目标列表达式>[, … n]
FROM <表1>LEFT[OUTER]JOIN <表2>[, … n]
ON <连接条件表达式>

查询每个学生及其选修课程的成绩情况（含未选课程的学生信息）。

SELECT S.*,CNO,GRADE
FROM S LEFT JOIN SC
ON S.SNO=SC.SNO 

有时在查询学生选修课程情况时，既需要查询那些有选课信息的学生情况，又需要查询那些没有选课信息的学生情况，因此会用到左外连接查询。

(2) 右外连接
右外连接是对连接条件右边的表不加限制。当右边表元组与左边表元组不匹配时，与左边表的相应列值取NULL。语句格式如下：
SELECT <目标列表达式>[, … n]
FROM <表1> RIGHT [OUTER] JOIN <表2>[, … n]
ON <连接条件表达式>

(3) 全外连接
全外连接是对连接条件的两个表都不加限制。当一边表元组与另一边表元组不匹配时，与另一边表的相应列值取NULL。
语句格式如下：
SELECT <目标列表达式> [, … n]
FROM <表1> FULL [OUTER] JOIN <表2>[, … n]
ON <连接条件表达式>

交叉连接：

交叉连接（cross join）也称为笛卡尔积，它是在没有连接条件下的两个表的连接，包含了所连接的两个表中所有元组的全部组合。
该连接方式在实际应用中是很少的。语句格式如下：
SELECT <目标列表达式> [,1 …n]
FROM <表1> CROSS JOIN <表2>[,1 …n]

例6.38 查询所有学生可能的选课情况。

SELECT S.*,SC.CNO,GRADE
FROM S  CROSS JOIN SC