【数据库系统原理与设计】（三）SQL查询语言

三. SQL 查询语言

3.1 SQL概述

标准SQL语句包括：查询SELECT、插入INSERT、修改UPDATE、删除DELETE、创建CREATE、删除DROP ...

3.1.1 SQL发展

1. 发展过程

（略）

2. SQL语言由4个部分组成：

数据定义语言DDL：定义、修改、删除
数据操纵语言DML：检索、更新（插入、删除、修改）
数据控制语言DCL：对象授权、用户维护（创建、修改、删除）、完整性规则定义、事务定义
其他：嵌入式SQL语言、动态SQL语言...

3.1.2 SQL特点

1. 综合统一

SQL语言集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL 于一体
实体间联系均用关系表示

2. 高度非过程化

用户无需了解存取路径，只需使用SQL语言进行数据操作

3. 面向集合的操作方式

SQL语言采用集合操作方式，其操作对象、操作结果都是元组的集合；
（对应非关系数据模型，采用面向记录的操作方式，其操作对象是一条记录）

4. 同一种语法结构提供两种使用方式

作为自含式语言，SQL语言可以独立用于联机交互（直接在终端键盘上输入SQL命令）
作为嵌入式语言，SQL语言能够嵌入到高级语言（Java等）程序中

5. 语言简洁，易学易用

SQL语言的动词很少，主要包括：
- 数据查询：SELECT
- 数据定义：CREATE、DROP、ALTER
- 数据更新：INSERT、DELETE、UPDATE
- 数据控制：GRANT、REVOKE

3.1.3 SQL查询基本概念

1. 关系数据库管理系统的三级模式结构

视图：从一个或几个基本表/视图中导出的表
基本表：数据库中独立存在的表，SQL中一个关系对应一个基本表
查询表：查询结果对应的表
存储文件：数据库中存放关系的物理文件，一个/多个基本表对应一个存储文件，一个表可带若干索引，索引存放在存储文件里

（本章所用数据库）

3.2 单表查询

3.2.1 投影运算

1. SQL查询语句的基本结构包括3个子句：SELECT、FROM、WHERE

SELECT 查询所需属性/表达式
FROM 查询所涉及的表
WHERE 指定查询结果所需满足的条件（可选）

2. 查询指定列

eg：查询 Class表中所有班级的班级编号classNo、班级名称className、所属学院institute

SELECT classNo , className , institute
FROM Class
/* 
查询的执行过程：
从Class表中依次取出每个元组，对每个元组仅选取classNo，className，institute这3个属性的值，形成一个新元组，最后将这些新元组组织成一个结果关系输出
*/

3. 消除重复元组

eg：查询所有学院的名称

SELECT DISTINCT institute
FROM Class

4. 查询所有列

eg：查询所有班级的全部信息

SELECT classNo,className,classNum,grade,institute
FROM Class

SELECT *
FROM Class
-- * 符号表示所有属性

5. 给属性列取别名

eg：查询所有班级的所属学院、班级编号、班级名称，要求用中文名显示列名

SELECT institute 所属学院 , classNo 班级编号 , className 班级名称
FROM Class

SELECT institute AS 所属学院 , classNo AS 班级编号 , className AS 班级名称
FROM Class

6. 查询经过计算的列

eg：查询每门课程的课程号、课程名、周课时（周课时=课时数/16），并将课程名中大写改为小写输出

SELECT courseNo 课程号 , lower(courseName) 课程名 , courseHour/16 AS 周课时
FROM Course
-- lower()函数：将大写字母改为小写字母

3.2.2 选择运算

1. 比较运算

eg：查询 2015级的班级编号、班级名称、所属学院

SELECT classNo , className , institute
FROM Class
WHERE grade = 2015

eg：在学生 Student 表中查询年龄大于或等于19岁的同学学号、姓名、出生日期

SELECT studentNo , studentName , birthday
FROM Student
WHERE year(getdate()) - year(birthday) >= 19
/*
getdate()函数：获取当前系统的日期
year()函数：提取日期中的年份
*/

2. 范围查询

eg：在选课 Score 表中查询成绩在80-90分之间的同学学号、课程号、相应成绩

SELECT studentNo , courseNo , score
FROM Score
WHERE score BETWEEN 80 AND 90

eg：在选课 Score 表中查询成绩不在80-90分之间的同学学号、课程号、相应成绩

SELECT studentNo , courseNo , score
FROM Score
WHERE score NOT BETWEEN 80 AND 90

3. 集合查询

eg：在选课 Score 表中查询选修了001、005或003课程的同学学号、课程号、相应成绩

SELECT studentNo , courseNo , score
FROM Score
WHERE courseNo IN ('001' , '005' , '003')

eg：在学生 Student 表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯、所属班级编号

SELECT studentName , native , classNo
FROM Student
WHERE native NOT IN('南昌' , '上海')

4. 空值查询

eg：在课程 Course 表中查询先修课程为空值的课程信息

SELECT *
FROM Course
WHERE priorCourse IS null

eg：在课程 Course 表中查询有先修课程的课程信息

SELECT *
FROM Course
WHERE priorCourse IS NOT null

5. 字符匹配查询

eg：在班级 Class 表中查询班级名称中含有会计的班级信息

SELECT *
FROM Class
WHERE className LIKE  '%会计%'
-- 匹配字符串必须用一对引号括起来

eg：在学生 Student 表中查询所有姓“王”且全名为3个汉字的同学学号、姓名

SELECT studentNo , studentName
FROM Student
WHERE studentName LIKE '王_ _'

eg：在学生 Student 表中查询名字中不含“福”的同学学号、姓名

SELECT studentNo , studentName
FROM Student
WHERE studentName NOT LIKE '%福%'

eg：在学生 Student 表中查询蒙古族的同学学号、姓名

SELECT studentNo , studentName
FROM Student
WHERE nation LIKE '蒙古族'

SELECT studentNo , studentName
FROM Student
WHERE nation = '蒙古族'

eg：在班级 Class 表中查询班级名称中含有“16_”符号的班级名称

SELECT className
FROM Class
WHERE className LIKE '%16\_' ESCAPE '\'
-- ESCAPE '\' 表示：、为换码字符，_不是通配符，而是用户要查询的符号

6. 逻辑运算

eg：在选课 Score 表中查询选修了001、005或003课程的同学学号、课程号、相应成绩

SELECT studentNo , courseNo , score
FROM Score
WHERE courseNo = '001' OR courseNo = '005' OR courseNo = '003'

eg：在学生 Student 表中查询1998年出生且民族为“汉族”的同学学号、姓名、出生日期

SELECT studentNo , studentName ,birthday
FROM Student
WHERE year(birthday) = 1998 AND nation = '汉族'

eg：在学生 Student 表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯、所属班级编号

SELECT studentName , native , classNo
FROM Student
WHERE native ！= '南昌'  AND  native ！= '上海'

eg：在选课 Score 表中查询成绩在 80-90 分之间的同学学号、课程号、相应成绩

SELECT studentNo ， courseNo ， score
FROM Score
WHERE score >=80 AND score <=90

eg：在选课 Score 表中查询成绩不在 80-90 分之间的同学学号、课程号、相应成绩

SELECT studentNo ， courseNo ， score
FROM Score
WHERE score <80 OR score >90

3.2.3 排序运算

eg：在学生 Student 表中查询籍贯既不是“南昌”也不是“上海”的同学姓名、籍贯、所属班级编号，并按籍贯降序输出

SELECT studentName , native , classNo
FROM Student
WHERE native ！= '南昌'  AND  native ！= '上海'
ORGER BY native DESC

eg：在学生 Student 表中查询“女”学生的学号、姓名、所属班级编号、出生日期，并按班级编号升序，出生日期的月份降序输出

SELECT studentNo , studentName , classNo , birthday
FROM Student
WHERE sex = '女'
ORDER BY classNo , month(birthday) DESC
-- month()函数：提取日期表达式的月份

3.2.4 查询表

eg：查询1999年出生的“女”同学基本信息

SELECT studentNo , studentName , birthday
FROM (SELECT * FROM student WHERE sex = '女') AS a
WHERE year(birthday) = 1999
/*
FROM 后面是一个查询表（套娃行为），必须为该查询表取一个名字叫a，也可以写成：FROM (SELECT * FROM student WHERE sex = '女') a
*/

SELECT studentNo , studentName , birthday
FROM student
WHERE year(birthday) = 1999 AND sex = '女'

3.2.5 聚合查询

1. SQL提供的聚合函数（aggregate function）主要包括：

count()函数：统计个数
sum()函数：求和（数值型）
avg()函数：求平均（数值型）
max()函数：最大值
min()函数：最小值

eg：查询学生总人数

-- 不带列名
SELECT count(*) 
FROM Student

-- 带列名
SELECT count(*) 学生人数
FROM Student

eg：查询所有选课学生的人数

SELECT count(DISTINCT studentNo) 学生人数
FROM Score
--DISTINCT 消除重复元组

eg：查询学号为1500003同学所选课程的平均分

SELECT avg(score) 平均分
FROM Score
WHERE studentNo = '1500003'
-- 除count(*)以外的聚合函数，都会自动跳过空值

2. 分组聚合（分类运算）主要包括：

group by
having

eg：查询每个同学的选课门数、平均分、最高分

SELECT studentNo , count(*)门数 , avg(score)平均分 , max(score)最高分
FROM Score
GROUP BY studentNo

eg：查询平均分在80分以上每个同学的选课门数、平均分、最高分

SELECT studentNo , count(*)门数 , avg(score)平均分 , max(score)最高分
FROM Score
GROUP BY studentNo
HAVING avg(score) >= 80
-- 判断平均值是否大于80，如果不大于则丢弃该组

3.3 连接查询

3.3.1 等值与非等值连接

（等值连接和非等值连接是在 where 子句中加入多个条件）

1. 等值连接

eg：查找会计学院全体同学的学号、姓名、籍贯、班级编号、所在班级名称。

SELECT studentNo, studentName, native, Student.classNo, className
FROM Student, Class
WHERE Student.classNo=Class.classNo AND institute='会计学院'

SELECT studentNo, studentName, native, b.classNo, className
FROM Student AS a, Class AS b
WHERE a.classNo=b.classNo AND institute='会计学院'

SELECT studentNo, studentName, native, b.classNo, className
FROM Student a, Class b
WHERE a.classNo=b.classNo AND institute='会计学院'

eg：查找选修了课程名称为“计算机原理”的同学学号、姓名。

SELECT a.studentNo, studentName
FROM Student a, Course b, Score c
WHERE b.courseNo=c.courseNo      // 表b与表c的连接条件
      AND c.studentNo=a.studentNo    // 表c与表a的连接条件
      AND b.courseName='计算机原理'

eg：查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。

SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a, Score b, 
             (SELECT * FROM Score WHERE courseNo='002') c
WHERE b.courseNo='001' 
      AND a.studentNo=b.studentNo     // 表a与表b的连接条件
      AND a.studentNo=c.studentNo     // 表a与表c的连接条件
ORDER BY a.studentNo

SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
     FROM Student a, 
                  (SELECT * FROM Score WHERE courseNo='001') b, 
                  (SELECT * FROM Score WHERE courseNo='002') c
     WHERE a.studentNo=b.studentNo      // 表a与表b的连接条件
           AND a.studentNo=c.studentNo       // 表a与表c的连接条件
     ORDER BY a.studentNo

eg：查询获得的总学分(注：只有成绩合格才能获得该课程的学分)大于或等于28的同学的学号、姓名和总学分，并按学号排序输出

SELECT a.studentNo, studentName, sum(creditHour)
FROM Student a, Course b, Score c
WHERE a.studentNo=c.studentNo AND c.courseNo=b.courseNo AND score>=60
GROUP BY a.studentNo, studentName     -- 输出结果的需要
HAVING sum(creditHour)>=28
ORDER BY a.studentNo

-- where 子句：作用于整个查询对象
-- having 子句：仅作用于分组

2. 自然连接

eg：实现成绩表Score和课程表Course的自然连接。

SELECT studentNo, a.courseNo, score, courseName, 
                 creditHour, courseHour, priorCourse
FROM Score a, Course b
WHERE a.courseNo=b.courseNo     // 表a与表b的连接条件

3. 非等值连接（用的比较少）

3.3.2 自表连接

某个表跟自己连接，叫自表连接

eg：查找同时选修了编号为“001”和“002”课程的同学学号、姓名、课程号和相应成绩，并按学号排序输出。

SELECT a.studentNo, studentName, b.courseNo, b.score, c.courseNo, c.score
FROM Student a, Score b, Score c
WHERE b.courseNo='001' AND c.courseNo='002'
      AND  a.studentNo=b.studentNo AND b.studentNo=c.studentNo
ORDER BY a.studentNo

eg：在学生表Student中查找与“李宏冰”同学在同一个班的同学姓名、班级编号和出生日期。

SELECT a.studentName, a.classNo, a.birthday
 FROM Student a, Student b
 WHERE b.studentName='李宏冰' AND a.classNo=b.classNo

SELECT a.studentName, a.classNo, a.birthday
 FROM Student a, 
              ( SELECT * FROM Student WHERE studentName='李宏冰' ) b
 WHERE a.classNo=b.classNo

3.3.3 外连接

只有满足条件的元组才会被检索出来。

外连接分为：左外连接、右外连接、全外连接

（from 子句中，写在左边的表称为“左关系”，右边的叫“右关系”）

eg：查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称排序输出。

SELECT className, institute, studentNo, studentName
   FROM Class a, Student b
   WHERE a.classNo=b.classNo AND grade=2015
   ORDER BY className

1. 左外连接

eg：使用左外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。

   SELECT className, institute, studentNo, studentName
   FROM Class a LEFT OUTER JOIN Student b 
                 ON a.classNo=b.classNo
   WHERE grade=2015
   ORDER BY className, studentNo

2. 右外连接

eg：使用右外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。

SELECT className, institute, studentNo, studentName
   FROM Class a RIGHT OUTER JOIN Student b 
                 ON a.classNo=b.classNo
  WHERE grade=2015 
  ORDER BY className, studentNo

3. 全外连接

eg：使用全外连接查询2015级每个班级的班级名称、所属学院、学生学号、学生姓名，按班级名称和学号排序输出。

SELECT className, institute, studentNo, studentName
   FROM Class a FULL OUTER JOIN Student b 
                 ON a.classNo=b.classNo
   WHERE grade=2015
   ORDER BY className, studentNo

3.4 嵌套子查询

将一个查询块（select-from-where）嵌入到另一个查询块的 where子句 / having子句中，称为嵌套子查询

3.4.1 使用 IN 的子查询

eg：查询选修过课程的学生姓名。

SELECT studentName
From Student
WHERE Student.studentNo IN 
                         (SELECT Score.studentNo FROM Score)

eg：查找选修过课程名中包含“系统”的课程的同学学号、姓名和班级编号。

SELECT studentNo, studentName, classNo
FROM Student
WHERE studentNo IN 
                 ( SELECT studentNo FROM Score
                    WHERE courseNo IN 
                                     ( SELECT courseNo FROM Course
                                        WHERE courseName LIKE '%系统%' )
                 )

eg：查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及该同学所选修的所有课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。

SELECT  a.studentNo, studentName, courseName, score
FROM Student a, Course b, Score c
WHERE  a.studentNo=c.studentNo AND b.courseNo=c.courseNo
      AND   a.studentNo IN 
                  ( SELECT studentNo FROM Score
                     WHERE courseNo IN 
                                      ( SELECT courseNo FROM Course
                                         WHERE courseName='计算机原理' ) ) 
      AND   a.studentNo IN 
                  ( SELECT studentNo FROM Score
                     WHERE courseNo IN 
                                      ( SELECT courseNo FROM Course
                                         WHERE courseName='高等数学' ) ) 
ORDER BY a.studentNo, score DESC

SELECT  a.studentNo, studentName, courseName, score
FROM Student a, Course b, Score c
WHERE  a.studentNo=c.studentNo AND b.courseNo=c.courseNo
      AND   a.studentNo IN 
                  ( SELECT studentNo FROM Score x, Course y
                     WHERE x.courseNo=y.courseNo 
                            AND courseName='计算机原理' )
      AND   a.studentNo IN 
                  ( SELECT studentNo FROM Score x, Course y
                    WHERE x.courseNo=y.courseNo 
                           AND courseName='高等数学' )
ORDER BY a.studentNo, score DESC

eg：查找同时选修过“计算机原理”和“高等数学”两门课程的同学学号、姓名以及所选修的这两门课程的课程名和相应成绩，按学号(升序)、成绩(降序)排序输出。

SELECT  a.studentNo,   studentName,   courseName,   score
FROM  Student a, Course b, Score c
WHERE  a.studentNo=c.studentNo AND b.courseNo=c.courseNo
      AND   a.studentNo IN 
                  ( SELECT studentNo FROM Score x, Course y
                     WHERE x.courseNo=y.courseNo AND courseName='计算机原理' )
      AND   a.studentNo IN 
                  ( SELECT studentNo FROM Score x, Course y
                     WHERE x.courseNo=y.courseNo AND courseName='高等数学' )
      AND   ( b.courseName='高等数学' OR b.courseName='计算机原理' )
ORDER BY a.studentNo, score DESC
-- ( b.courseName='高等数学' OR b.courseName='计算机原理' )要括起来

3.4.2 使用比较运算符的子查询

ANY表示子查询结果中的某个值（ANY也可以用SOME替代）

ALL表示子查询结果中的所有值

eg：查询所选修课程的成绩大于所有002号课程成绩的同学学号及相应课程的课程号和成绩。

SELECT studentNo, courseNo, score
FROM Score
WHERE score>ALL
                 ( SELECT score
                   FROM Score
                   WHERE courseNo='002' )

eg：查询成绩最高分的学生的学号、课程号和相应成绩

SELECT studentNo, courseNo, score
FROM Score
WHERE score=( SELECT max(score)
                              FROM Score )
-- 聚合函数可直接用在HAVING子句中，子查询中，但不可以直接使用在WHERE子句中

eg：查询年龄小于“计算机科学与技术16-01班”某个同学年龄的所有同学的学号、姓名和年龄。

SELECT studentNo, studentName, year(getdate())-year(birthday) AS age
FROM Student
WHERE birthday>ANY
                 ( SELECT birthday
                    FROM Student a, Class b
                    WHERE className='计算机科学与技术16-01班' 
                           AND a.classNo=b.classNo )
-- 在比较运算符中，=ANY 等价于 IN，!=ALL 等价于 NOT IN

3.4.3 使用存在量词EXISTS的子查询

SQL查询提供量词运算：存在量词EXISTS、全称量词NOT EXISTS
WHERE子句中的谓词EXISTS用来判断其后的子查询的结果集合中是否存在元素
谓词EXISTS大量用于相关子查询中

eg：查询选修了“计算机原理”课程的同学姓名、所在班级编号。

SELECT studentName, classNo 
FROM Student x
WHERE EXISTS 
                 ( SELECT * FROM Score a, Course b
                    WHERE a.courseNo=b.courseNo 
                          AND a.studentNo=x.studentNo 
                          AND courseName='计算机原理' )
-- 相关子查询

eg：查询选修了所有课程的学生姓名。

SELECT studentName 
FROM Student x
WHERE NOT EXISTS 
                 ( SELECT * FROM Course c
                    WHERE NOT EXISTS   
                                     -- 判断学生x.studentNo没有选修课程c.courseNo
                                     ( SELECT * FROM Score
                                        WHERE studentNo=x.studentNo 
                                               AND courseNo=c.courseNo )
                 )

eg：查询至少选修了学号为1600002学生所选修的所有课程的学生姓名。

SELECT studentName
FROM Student x
WHERE NOT EXISTS 
                 ( SELECT * FROM Score y      // 不能用Course表
                    WHERE studentNo='1600002'  
                                     -- 查询学生'1600002'所选修课程的情况
                         AND NOT EXISTS    
                                   -- 判断学生x.studentNo没有选修课程y.courseNo
                                  ( SELECT * FROM Score
                                    WHERE studentNo=x.studentNo 
                                           AND courseNo=y.courseNo )
                 )

eg：查询至少选修了学号为1600002学生所选修的所有课程的学生学号、姓名以及该学生所选修所有课程的课程名和成绩，按学生姓名、课程名排序输出。

SELECT  x.studentNo,  studentName,  courseName,  score 
FROM  Student x, Course y, Score z
WHERE  x.studentNo=z.studentNo AND y.courseNo=z.courseNo 
      AND   NOT EXISTS
                 ( SELECT * FROM Score b
                    WHERE studentNo='1600002'   -- 查询学生'1600002'所选修课程的情况
                           AND NOT EXISTS    -- 判断学生x.studentNo没有选修课程b.courseNo
                                    ( SELECT * FROM Score
                                       WHERE studentNo=x.studentNo AND courseNo=b.courseNo )
                  )
ORDER BY studentName, courseName                 //  排序输出

*3.4.4 复杂子查询实例

eg：查询至少选修了28个学分的同学的学号、姓名以及所选修各门课程的课程名、成绩和学分，按学号排序输出。

SELECT a.studentNo, studentName, courseName, score, creditHour
FROM Student a, Course b, 
               ( SELECT studentNo, courseNo, max(score) score
                  FROM Score
                  WHILE score>=60    -- 仅列示已经获得学分(即及格了)的课程
                  GROUP BY studentNo, courseNo ) AS c      -- 查询表c
WHERE a.studentNo=c.studentNo AND c.courseNo=b.courseNo 
       AND a.studentNo IN 
   ( SELECT studentNo     -- 子查询Q
     FROM Course x, ( SELECT studentNo, courseNo, max(score) score
                                        FROM Score
                                        WHILE score>=60    -- 只有及格才能获得学分
                                        GROUP BY studentNo, courseNo ) AS y
     WHERE y.courseNo=x.courseNo
     GROUP BY studentNo
     HAVING sum(creditHour)>=28 )
ORDER BY a.studentNo

eg：查询至少选修了5门课程且课程平均分最高的同学的学号和课程平均分。如果一个学生选修同一门课程多次，则选取最高成绩。

SELECT studentNo, avg(score) avgScore
FROM  ( SELECT studentNo, courseNo, max(score) score
                 FROM Score
                 GROUP BY studentNo, courseNo ) AS a
GROUP BY studentNo
HAVING count(*)>=5 
 AND avg(score)=
            ( SELECT max(avgScore)    -- 子查询Q2
              FROM ( SELECT studentNo, avg(score) avgScore    -- 子查询Q1
                              FROM  ( SELECT studentNo, courseNo, max(score) score
                                               FROM Score
                                               GROUP BY studentNo, courseNo ) AS b
                              GROUP BY studentNo
                              HAVING count(*)>=5 ) AS x
            )

eg：查询选修了所有4学分课程(即学分为4的课程)的同学的学号、姓名以及所选修4学分课程的课程名和成绩。

SELECT a.studentNo, studentName,   courseName, score
    FROM Student a, Course b, Score c
    WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo 
  AND NOT EXISTS
          ( SELECT * FROM Course x
             WHERE creditHour=4    --查询4学分课程的情况
                  AND NOT EXISTS     --判断学生a.studentNo没有选修课程x.courseNo
                          ( SELECT * FROM Score
                             WHERE studentNo=a.studentNo AND courseNo=x.courseNo 
                          )
          )
  AND creditHour=4   
            -- 只显示满足上述要求的学生所选修4学分课程的课程名和成绩

3.5 集合查询

eg：查询“信息管理学院”1999年出生的同学的学号、出生日期、班级名称和所属学院以及“会计学院”1998年出生的同学的学号、出生日期、班级名称和所属学院。

SELECT studentNo, birthday, className, institute
FROM Student a, Class b
WHERE a.classNo=b.classNo AND year(birthday)=1999 
       AND institute='信息管理学院'
UNION
SELECT studentNo, birthday, className, institute
FROM Student a, Class b
WHERE a.classNo=b.classNo AND year(birthday)=1998 
       AND institute='会计学院'

SELECT studentNo, birthday, className, institute
FROM Student a, Class b
WHERE a.classNo=b.classNo 
   AND ( year(birthday)=1999 AND institute=‘信息管理学院'
               OR year(birthday)=1998 AND institute='会计学院' )
ORDER BY institute

eg：查询同时选修了“001”号和“005”号课程的同学的学号和姓名。

SELECT a.studentNo, studentName
FROM Student a, Score b
WHERE a.studentNo=b.studentNo AND courseNo='001'
INTERSECT
SELECT a.studentNo, studentName
FROM Student a, Score b
WHERE a.studentNo=b.studentNo AND courseNo='005'

SELECT a.studentNo, studentName
FROM Student a, Score b
WHERE a.studentNo=b.studentNo AND courseNo='001'
       AND a.studentNo IN ( 
                SELECT studentNo FROM Score WHERE courseNo='005' )

eg：查询没有选修“计算机原理”课程的同学的学号和姓名。

SELECT studentNo, studentName
FROM Student
EXCEPT
SELECT DISTINCT a.studentNo, studentName
FROM Student a, Score b, Course c
WHERE a.studentNo=b.studentNo 
      AND  b.courseNo=c.courseNo 
      AND  courseName='计算机原理'

SELECT studentNo, studentName
FROM Student
WHERE studentNo NOT IN
        ( SELECT studentNo 
           FROM Score x, Course y
           WHERE x.courseNo=y.courseNo 
                 AND  courseName='计算机原理' )

3.6 SQL查询一般格式

SELECT [ALL | DISTINCT] <目标列表达式> [AS] [<别名>]
                 [, <目标列表达式> [AS] [<别名>] ... ]
FROM {<表名> | <视图名> | <查询表>} [AS] [<别名>]
             [, {<表名> | <视图名> | <查询表>} [AS] [<别名>] ... ]

[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [, <列名2> ... ]
   [ HAVING <条件表达式> ] ]
[ ORDER BY <列名表达式> [ASC | DESC] 
                        [, <列名表达式> [ASC | DESC] ... ] ]

1. from 子句：指定查询的表，一般取个别名

2. where 子句：给出查询条件，可包含子查询，但不可以直接使用聚合函数

3. group by 子句：分组属性、聚合属性

4. having 子句：给出分组后的选择条件，可直接使用聚合函数

5. order by 子句：必须放在最后（默认升序，desc降序）

eg：查询每一个同学的学号以及该同学所修课程中成绩最高的课程的课程号和相应成绩。

SELECT studentNo, courseNo, score
FROM Score a
WHERE score=( SELECT max(score)
                              FROM Score
                              WHERE studentNo=a.studentNo )

eg：查询学生人数不低于500的学院的学院名称及学生人数。

SELECT institute, count(*) 人数
FROM Student a, Class b
WHERE a.classNo=b.classNo
GROUP BY institute
HAVING count(*)>=500

eg：查询平均分最高的课程的课程号、课程名和平均分。

SELECT a.courseNo, courseName, avg(score) 最高平均分
FROM Course a, Score b
WHERE a.courseNo=b.courseNo
GROUP BY a.courseNo, courseName
HAVING avg(score)=
                 ( SELECT max(avgScore)
                    FROM ( SELECT avg(score) avgScore
                                    FROM Score
                                    GROUP BY courseNo ) x
                 )

posted @ 2022-10-07 22:26 哟吼--小文文公主阅读(391) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 【数据库系统原理与设计】（六）SQL数据定义、更新及数据库编程

· 【数据库系统原理与设计】（二）关系模型与关系代数

· 数据库系统笔记 - chap3 - SQL

· 数据库___标准语言SQL

· [转]史上最全的MSSQL复习笔记

阅读排行：
· winform 绘制太阳，地球，月球运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具

公告

昵称：哟吼--小文文公主
园龄： 2年5个月
粉丝： 38
关注： 7

+加关注

2025年3月

日

一

二

三

四

五

六

Long Time No See

欢迎光临我的博客[不定期更新]~ 请配合 ReadMe 置顶目录找到您想要了解的部分