【数据库】sql错题详解

  

  1. 执行子查询

1. SELECT 供应商号
2. FROM 订购单
3. WHERE 职工号 IN ('E1', 'E3')
4. GROUP BY 供应商号
5. HAVING COUNT(DISTINCT 职工号) = 2

复制代码

• 筛选职工号为 E1 或 E3 的纪录：
  依据 WHERE 职工号 IN ('E1', 'E3') 这个条件，从 订购单 表中把职工号为 E1 大概 E3 的纪录筛选出来。得到的效果如下：
  | E3 | S7 | OR67 | 2002/06/23 |
  | E1 | S4 | OR73 | 2002/07/28 |
  | E3 | S4 | OR79 | 2002/06/13 |
  | E3 | S3 | OR91 | 2002/07/13 |
  
• 按 供应商号 分组：
  运用 GROUP BY 供应商号 对筛选出来的效果进行分组，分组环境如下：
  
  
  
  • 供应商号 S3：包含纪录 (E3, S3, OR91, 2002/07/13)
    
  • 供应商号 S4：包含纪录 (E1, S4, OR73, 2002/07/28) 和 (E3, S4, OR79, 2002/06/13)
    
  • 供应商号 S7：包含纪录 (E3, S7, OR67, 2002/06/23)
    
  
  
• 使用 HAVING 子句筛选分组效果：
  HAVING COUNT(DISTINCT 职工号) = 2 的作用是只保存分组中不同职工号数量为 2 的分组。
  
  
  
  • 对于供应商号 S3，其分组里只有职工号 E3，不同职工号数量为 1，不满足条件。
    
  • 对于供应商号 S4，分组中有职工号 E1 和 E3，不同职工号数量为 2，符合条件。
    
  • 对于供应商号 S7，分组里只有职工号 E3，不同职工号数量为 1，不符合条件。
    所以，子查询最终返回的 供应商号 是 S4。
    
  
  

  2. 执行主查询

1. SELECT *
2. FROM 供应商
3. WHERE 地址 = '北京'
4.   AND 供应商号 IN ('S4')

复制代码

   

• 筛选地址为北京的供应商：
  根据 WHERE 地址 = '北京' 条件，从 供应商 表中筛选出地址为北京的供应商纪录。效果如下：
  | 供应商号 | 供应商名 | 地址 |
  | S4 | 华通电子公司 | 北京 |
  | S7 | 爱华电子厂 | 北京 |
  
• 筛选 供应商号 在子查询效果中的纪录：
  按照 AND 供应商号 IN ('S4') 条件，从上述筛选效果中进一步筛选出 供应商号 为 S4 的纪录。最终效果如下：
  | 供应商号 | 供应商名 | 地址 |
  | S4 | 华通电子公司 | 北京 |
  

  

   1. 子查询部门

1. SELECT 仓库号, AVG(工资) AS avg_salary
2. FROM 职工
3. GROUP BY 仓库号

复制代码

• GROUP BY 堆栈号：按照 堆栈号 对 职工 表中的纪录进行分组。也就是说，会把在同一个堆栈工作的职工纪录分为一组。
  
• SELECT 堆栈号, AVG(工资) AS avg_salary：对于每一组，盘算该组内职工工资的平均值，并将效果定名为 avg_salary，同时选择 堆栈号 作为分组标识。比方，假如有两个堆栈 WH1 和 WH2，那么会分别盘算 WH1 堆栈和 WH2 堆栈中职工的平均工资。
  

   这个子查询的效果是一个暂时表，包含两列：堆栈号 和该堆栈对应的 avg_salary。
  2. JOIN ON 部门

1. JOIN (
2.     -- 子查询
3.     SELECT 仓库号, AVG(工资) AS avg_salary
4.     FROM 职工
5.     GROUP BY 仓库号
6. ) s ON t.仓库号 = s.仓库号

复制代码

   

• JOIN：JOIN 是用于将两个或多个表毗连起来的操纵符。在这里，将 职工 表（用别名 t 表示）和子查询效果（用别名 s 表示）进行毗连。
  
• (...) s：子查询被当作一个暂时表，使用别名 s 来引用它。
  
• ON t.堆栈号 = s.堆栈号：ON 关键字后面的条件指定了毗连的规则。这里表示将 职工 表中的 堆栈号 与子查询效果中的 堆栈号 进行匹配，只有当两者相等时，对应的纪录才会被毗连在一起。比方，假如 职工 表中有一条纪录的 堆栈号 是 WH1，子查询效果中也有 WH1 对应的平均工资纪录，那么这两条纪录就会被毗连起来。
  

  3. 主查询和 WHERE 部门

1. SELECT t.*
2. FROM 职工 t
3. JOIN (
4.     SELECT 仓库号, AVG(工资) AS avg_salary
5.     FROM 职工
6.     GROUP BY 仓库号
7. ) s ON t.仓库号 = s.仓库号
8. WHERE t.工资 < s.avg_salary;

复制代码

   

• SELECT t.*：选择 职工 表（别名 t）中的全部列。
  
• WHERE t.工资 < s.avg_salary：WHERE 子句用于筛选满足条件的纪录。这里表示只选择 职工 表中工资小于其地点堆栈平均工资的纪录。
  

  示例阐明

  假设 职工 表数据如下：
   堆栈号职工号工资WH1E12000WH1E23000WH2E34000WH2E45000    子查询盘算出的平均工资效果如下：
   堆栈号avg_salaryWH12500WH24500    通过 JOIN ON 毗连后，会将 职工 表和子查询效果进行匹配，得到：
   堆栈号职工号工资avg_salaryWH1E120002500WH1E230002500WH2E340004500WH2E450004500    最后，通过 WHERE 子句筛选出工资小于平均工资的纪录，即：
   堆栈号职工号工资avg_salaryWH1E120002500WH2E340004500    这样就得到了工资低于地点堆栈平均工资的职工信息。
  FROM 职工：指定从   职工 表中获取数据。   （7）检索学习全部课程的学生姓名

   我们照旧基于之前的示例数据来详细分析这个 SQL 语句每一步的执行效果。下面是涉及的三个表数据：
  学生表 S

  SNOSNAMEAGESEXSDEPTS1张三20男盘算机系S2李四21女数学系S3王五20男盘算机系  课程表 C

  CNOCNAMECDEPTTNAMEC1数据库原理盘算机系赵老师C2高等数学数学系钱老师C3编程语言盘算机系孙老师  学生选课表 SC

  SNOCNOGRADES1C185S1C390S2C278S3C188S3C292S3C380  1. 最内层子查询（针对每个学生和课程组合）

  最内层子查询为 SELECT * FROM SC sc WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO，它会针对每个学生和每门课程的组合进行查询，判定该学生是否选修了这门课程。
  对于学生 S1

  

• 当课程为 C1 时，查询效果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S1 | C1 | 85 |
  
• 当课程为 C2 时，由于 S1 未选修 C2，查询效果为空表。
  
• 当课程为 C3 时，查询效果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S1 | C3 | 90 |
  

  对于学生 S2

  

• 当课程为 C1 时，由于 S2 未选修 C1，查询效果为空表。
  
• 当课程为 C2 时，查询效果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S2 | C2 | 78 |
  
• 当课程为 C3 时，由于 S2 未选修 C3，查询效果为空表。
  

  对于学生 S3

  

• 当课程为 C1 时，查询效果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S3 | C1 | 88 |
  
• 当课程为 C2 时，查询效果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S3 | C2 | 92 |
  
• 当课程为 C3 时，查询效果：
  | SNO | CNO | GRADE |
  | ---- | ---- | ---- |
  | S3 | C3 | 80 |
  

  2. 中心层子查询（针对每个学生）

  中心层子查询为 SELECT * FROM C c WHERE NOT EXISTS (SELECT * FROM SC sc WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO)，它会针对每个学生，找出该学生未选修的课程。
  对于学生 S1

  由于 S1 未选修 C2，所以查询效果：
   CNOCNAMECDEPTTNAMEC2高等数学数学系钱老师  对于学生 S2

  由于 S2 未选修 C1 和 C3，所以查询效果：
   CNOCNAMECDEPTTNAMEC1数据库原理盘算机系赵老师C3编程语言盘算机系孙老师  对于学生 S3

  由于 S3 选修了全部课程，中心层子查询效果为空表。
  3. 最外层查询

  最外层查询为 SELECT SNAME FROM S s WHERE NOT EXISTS (SELECT * FROM C c WHERE NOT EXISTS (SELECT * FROM SC sc WHERE sc.SNO = s.SNO AND sc.CNO = c.CNO))，它会筛选出选修了全部课程的学生姓名。
   

• 对于学生 S1 和 S2，中心层子查询有效果返回，NOT EXISTS 为 FALSE，不会被选中。
  
• 对于学生 S3，中心层子查询效果为空表，NOT EXISTS 为 TRUE，会被选中。
  

   最终的查询效果为：
   SNAME王五  （8）查询所学课程包含学生 S3 所学课程的学生学号

   学生选课表 SC

  SNOCNOGRADES1C185S1C390S2C278S3C188S3C292S3C380  语句详解

  

• 最外层查询：SELECT DISTINCT SNO FROM SC s1 WHERE... 从学生选课表 SC 中选择不同的学生学号 SNO，表 SC 使用别名 s1，并且后面跟着筛选条件。
  
• 中心层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3' AND NOT EXISTS (...) 从学生选课表 SC（使用别名 s2）中筛选出学号为 S3 的纪录，并且对于每一条这样的纪录，再嵌套一个子查询进行进一步判定。
  
• 最内层子查询：SELECT 1 FROM SC s3 WHERE s3.SNO = s1.SNO AND s3.CNO = s2.CNO 从学生选课表 SC（使用别名 s3）中查找是否存在满足学号等于外层 s1 的学号且课程号等于中心层 s2 的课程号的纪录。假如存在，该子查询返回一行（值为 1），否则不返回行。
  

   团体逻辑是：对于 SC 表中的每一个学生 s1，查抄 S3 选修的每一门课程，看 s1 是否也选修了这门课程。假如 S3 选修的全部课程 s1 都选修了，那么 s1 就满足条件，会被选入最终效果。
  每一步效果

  

• 初始化外层循环，遍历 SC 表中的每一个学生（以 s1 表示）
  
  
  
  • 当 s1.SNO = S1 时：
    
    
    
    • 中心层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3'，得到 S3 选修课程的纪录：
      | SNO | CNO | GRADE |
      | ---- | ---- | ---- |
      | S3 | C1 | 88 |
      | S3 | C2 | 92 |
      | S3 | C3 | 80 |
      
    • 对于上述每一条纪录进行最内层子查询：
      
      
      
      • 当 s2.CNO = C1 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S1' AND s3.CNO = 'C1'，有纪录，返回一行（值为 1）。
        
      • 当 s2.CNO = C2 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S1' AND s3.CNO = 'C2'，无纪录，不返回行。
        
      • 当 s2.CNO = C3 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S1' AND s3.CNO = 'C3'，有纪录，返回一行（值为 1）。
        
      
      
    • 由于存在 S3 选修的课程（C2）而 S1 未选修，所以中心层子查询的 NOT EXISTS 部门对于 S1 为 FALSE，S1 不满足最外层查询条件。
      
    
    
  • 当 s1.SNO = S2 时：
    
    
    
    • 中心层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3'，得到 S3 选修课程的纪录（同上面 S3 的选修课程纪录）。
      
    • 对于上述每一条纪录进行最内层子查询：
      
      
      
      • 当 s2.CNO = C1 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S2' AND s3.CNO = 'C1'，无纪录，不返回行。
        
      • 当 s2.CNO = C2 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S2' AND s3.CNO = 'C2'，有纪录，返回一行（值为 1）。
        
      • 当 s2.CNO = C3 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S2' AND s3.CNO = 'C3'，无纪录，不返回行。
        
      
      
    • 由于存在 S3 选修的课程（C1 和 C3）而 S2 未选修，所以中心层子查询的 NOT EXISTS 部门对于 S2 为 FALSE，S2 不满足最外层查询条件。
      
    
    
  • 当 s1.SNO = S3 时：
    
    
    
    • 中心层子查询：SELECT 1 FROM SC s2 WHERE s2.SNO = 'S3'，得到 S3 选修课程的纪录（同上面 S3 的选修课程纪录）。
      
    • 对于上述每一条纪录进行最内层子查询：
      
      
      
      • 当 s2.CNO = C1 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S3' AND s3.CNO = 'C1'，有纪录，返回一行（值为 1）。
        
      • 当 s2.CNO = C2 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S3' AND s3.CNO = 'C2'，有纪录，返回一行（值为 1）。
        
      • 当 s2.CNO = C3 时，SELECT 1 FROM SC s3 WHERE s3.SNO = 'S3' AND s3.CNO = 'C3'，有纪录，返回一行（值为 1）。
        
      
      
    • 由于 S3 选修的全部课程 S3 自己都选修了，所以中心层子查询的 NOT EXISTS 部门对于 S3 为 TRUE，S3 满足最外层查询条件。
      
    
    
  
  
• 最终效果：
  颠末最外层的 DISTINCT 处置惩罚后，得到满足条件的学生学号：
  | S3 |
  

   

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告