当前位置：首页 > news >正文

SQL 数据查询

news 来源：原创 2024/9/20 0:59:27

文章目录

- - - 3.4.1 单表查询
    - - 定义
      - 特点
      - 单表无条件查询
      - 单表带条件查询
      - 对查询结果进行排序
      - 限制查询结果数量
    - 3.4.2 分组查询
    - - 定义
      - 特点：
      - 聚集函数
      - GROUP BY短语
      - HAVING子句
      - 分组查询小结
    - 3.4.3 连接查询
    - - 定义
      - 特点：
      - 等值连接与非等值连接查询
      - 自然连接（内连接）查询
      - 外连接查询
      - 自身连接查询
    - 3.4.4 嵌套查询
    - - 定义
      - 特点：
      - 嵌套查询的分类
      - 嵌套查询的执行过程
      - 带比较运算符的子查询
      - 带IN(或NOT IN)谓词的子查询
      - 带ANY/ALL谓词的子查询
      - 带EXISTS(或NOT EXISTS)谓词的子查询
      - 用存在量词实现全称量词
      - 用存在量词实现蕴涵逻辑
    - 3.4.5 集合查询
    - - 定义
      - 特点：
      - 并运算
      - 交运算
      - 差运算
    - 3.4.6 多表查询的等价形式
    - - 定义
      - 多表查询的肯定形式
      - 多表查询的否定形式
  - 总结

3.4.1 单表查询

定义
1. 单表查询是指从单个基本表中选择满足条件的字段和元组。
特点
1. 无条件查询：从表中选择所有字段或部分字段，不加条件。
2. 带条件查询：根据特定条件筛选满足条件的字段和元组。
3. 查询结果排序：使用ORDER BY短语对查询结果进行排序。
4. 限制查询结果数量：使用LIMIT短语限制查询结果的行数。

单表无条件查询

查询所有字段：
```
SELECT * FROM Department;
```
查询部分字段：
```
SELECT dno, dname FROM Department;
```
查询没有重复行数据的字段：
```
SELECT DISTINCT dno FROM Student;
```

查询含有表达式的字段：

SELECT sname 姓名, YEAR(CURRENT_DATE()) - YEAR(birth) 年龄 FROM Student;

单表带条件查询

比较判断：

SELECT sname, birth FROM Student WHERE dno = 'D1';
SELECT sno, cno FROM SC WHERE score < 60;
SELECT sno, sname FROM Student WHERE dno <> 'D2';

确定范围：

SELECT * FROM SC WHERE score BETWEEN 60 AND 90;
SELECT * FROM SC WHERE score NOT BETWEEN 60 AND 90;

确定集合：

SELECT sno, sname, sex, birth, dno FROM Student WHERE dno IN ('D1', 'D2');
SELECT sno, sname, sex, birth, dno FROM Student WHERE dno NOT IN ('D1', 'D2');

空值查询：

SELECT * FROM SC WHERE score IS NULL;
SELECT * FROM SC WHERE score IS NOT NULL;

多重条件查询：

SELECT * FROM SC WHERE score >= 60 AND score <= 90;
SELECT * FROM SC WHERE score < 60 OR score > 90;

字符匹配：

SELECT * FROM Course WHERE cname LIKE '数据库';
SELECT * FROM Course WHERE cname LIKE '%数据库%';

对查询结果进行排序

按单个字段排序：

SELECT sno, score FROM SC WHERE cno = 'C1' ORDER BY score DESC;

按多个字段排序：

SELECT sno, sname, sex, birth, dno FROM Student ORDER BY dno, birth DESC;

限制查询结果数量

限制查询结果数量：

SELECT sno, sname, dno FROM Student LIMIT 1, 3;
SELECT * FROM SC ORDER BY score DESC LIMIT 5;

3.4.2 分组查询

定义
1. 分组查询是将基本表中满足条件的元组按指定字段的值分成若干组，每组返回一个结果。
特点：
- 聚集函数：COUNT、SUM、AVG、MAX、MIN等，用于对每组数据进行汇总和统计。
- GROUP BY短语：根据指定字段的值对查询结果中的元组进行分组。
- HAVING子句：对分组后的查询结果再进一步选择满足条件的分组。
聚集函数
- 定义：COUNT、SUM、AVG、MAX、MIN等，用于对每组数据进行汇总和统计。
- 常用聚集函数：
  - | 聚集函数 | 功能 |
    | --------------------------- | ------------------------------------------ |
    | COUNT() | 统计元组的个数，包括值为NULL 的元组 |
    | COUNT([DISTINCTALL] 字段名) | 统计某一字段值的个数，忽略NULL 值 |
    | SUM([DISTINCTALL] 字段名) | 计算某一字段值的总和（此列必须是数值型） |
    | AVG([DISTINCTALL] 字段名) | 计算某一字段值的平均值（此列必须是数值型） |
    | MAX([DISTINCTALL] 字段名) | 返回某一字段值中的最大值 |
    | MIN([DISTINCTALL] 字段名) | 返回某一字段值中的最小值 |
  - 统计学生总人数：SELECT COUNT(*) 学生总人数 FROM Student;
  - 统计学生来自几个学院：SELECT COUNT(DISTINCT dno) 学院数 FROM Student;
  - 计算所有学生的平均成绩、最高分和最低分：SELECT ROUND(AVG(score), 2) 平均成绩, MAX(score) 最高分, MIN(score) 最低分 FROM SC;
  - 按学院分组查询每名学生的学号及其成绩的最高分和最低分：SELECT sno 学号, MAX(score) 最高分, MIN(score) 最低分 FROM SC GROUP BY sno;

GROUP BY短语

定义：根据指定字段的值对查询结果中的元组进行分组。

按单字段分组：

SELECT sno 学号, MAX(score) 最高分, MIN(score) 最低分 FROM SC GROUP BY sno;

按多字段分组：

SELECT dno 学院编号, YEAR(CURRENT_DATE()) - YEAR(birth) 年龄, COUNT(*) 人数 FROM Student GROUP BY dno, YEAR(CURRENT_DATE()) - YEAR(birth) ORDER BY dno;

HAVING子句
- 定义：对分组后的查询结果再进一步选择满足条件的分组。
- 筛选分组：
```
SELECT sno FROM SC GROUP BY sno HAVING COUNT(*) > 3;
```

分组查询小结

完整的分组查询语法：

SELECT 分组字段, 聚集函数表达式 FROM 表名 [WHERE 查询条件] [GROUP BY 字段名1, 字段名2..] [HAVING 分组条件表达式] [ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC],..];

3.4.3 连接查询

定义
1. 连接查询是指对两个或两个以上的基本表或视图进行的查询，包括等值连接查询、非等值连接查询、自然连接（内连接）查询、外连接查询和自身连接查询。
特点：
- 等值连接与非等值连接：根据两个表中的字段值进行比较，确定当前的两条元组是否可以连接。
- 自然连接（内连接）：去掉重复列的等值连接。
- 外连接：包括左外连接、右外连接和全外连接，确保某个表的所有元组都出现在结果中。
- 自身连接：一个表与其自身进行连接。

等值连接与非等值连接查询

等值连接查询：

SELECT Student.sno, sname, sex, birth, dno, SC.cno, score FROM Student, SC WHERE Student.sno = SC.sno;

非等值连接查询：

SELECT sno, score, grade FROM SC, SG WHERE score BETWEEN minscore AND maxscore;

自然连接（内连接）查询

显式内连接查询：

SELECT sname, score FROM Student INNER JOIN SC ON Student.sno = SC.sno WHERE sex = '男';

隐式内连接查询：

SELECT sname, score FROM Student, SC WHERE Student.sno = SC.sno AND sex = '男';

外连接查询

左外连接查询：

SELECT dname, tname FROM Department LEFT JOIN Teacher ON Department.dno = Teacher.dno;

右外连接查询：

SELECT dname, tname FROM Department RIGHT JOIN Teacher ON Teacher.dno = Department.dno;

全外连接查询：

SELECT dname, tname FROM Department LEFT JOIN Teacher ON Department.dno = Teacher.dno UNION SELECT dname, tname FROM Department RIGHT JOIN Teacher ON Teacher.dno = Department.dno;

自身连接查询

自身连接查询：

SELECT First.cno, Second.cpno FROM Course First INNER JOIN Course Second ON First.cpno = Second.cno;

3.4.4 嵌套查询

定义
1. 嵌套查询是将一个查询块嵌套在另一个查询块的WHERE子句或HAVING子句的条件中的查询。
特点：
- 单行子查询和多行子查询：根据子查询返回的行数分类。
- 相关子查询和不相关子查询：根据子查询的查询条件是否依赖于父查询分类。
- 带比较运算符的子查询：父查询与子查询之间用比较运算符进行连接。
- 带IN/NOT IN谓词的子查询：用谓词IN/NOT IN引出子查询。
- 带ANY/ALL谓词的子查询：用谓词ANY/ALL引出子查询。
- 带EXISTS/NOT EXISTS谓词的子查询：用谓词EXISTS/NOT EXISTS引出子查询。
嵌套查询的分类
- 按子查询返回元组的行数：单行子查询、多行子查询
- 按子查询的查询条件是否依赖于父查询：相关子查询、不相关子查询
- 按引出子查询的谓词的不同：由比较运算符引出的子查询、由IN/NOTIN谓词引出的子查询、由ANY/ALL谓词引出的子查询、由EXISTS/NOT EXISTS谓词引出的子查询
嵌套查询的执行过程
- 不相关子查询的执行过程：从内向外依次执行
- 相关子查询的执行过程：循环往返执行

带比较运算符的子查询

比较运算符引出的子查询：

SELECT sno, sname FROM Student WHERE dno = (SELECT dno FROM Student WHERE sname = '贾哲');

带IN(或NOT IN)谓词的子查询

IN谓词引出的子查询：

SELECT sname FROM Student WHERE sno IN (SELECT sno FROM SC WHERE cno = 'C3');

NOT IN谓词引出的子查询：

SELECT sname FROM Student WHERE sno NOT IN (SELECT sno FROM SC WHERE cno = 'C3');

带ANY/ALL谓词的子查询

ANY谓词引出的子查询：

SELECT sname, birth FROM Student WHERE birth > ANY (SELECT birth FROM Student WHERE dno = 'D3') AND dno <> 'D3';

ALL谓词引出的子查询：

SELECT sname, birth FROM Student WHERE birth > ALL (SELECT birth FROM Student WHERE dno = 'D3') AND dno <> 'D3';

带EXISTS(或NOT EXISTS)谓词的子查询

EXISTS谓词引出的子查询：

SELECT sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE cno = 'C3' AND sno = Student.sno);

NOT EXISTS谓词引出的子查询：

SELECT sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE cno = 'C3' AND sno = Student.sno);

用存在量词实现全称量词

全称量词的表达：

SELECT sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE sno = Student.sno AND cno = Course.cno));

用存在量词实现蕴涵逻辑

蕴涵逻辑的表达：

SELECT DISTINCT sno FROM SC SCX WHERE SCX.sno <> 'S3' AND NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.sno = 'S3' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.sno = SCX.sno AND SCZ.cno = SCY.cno));

3.4.5 集合查询

定义
1. 集合查询是对两个或多个SELECT查询结果的集合进行并、交、差等集合运算。
特点：
- 并运算：包括两个查询语句结果的所有元组。
- 交运算：包括两个查询结果中共有的元组。
- 差运算：包括属于第一个结果集但不属于第二个结果集中的元组。

并运算

并运算：

SELECT sno FROM SC WHERE cno = 'C1' UNION SELECT sno FROM SC WHERE cno = 'C3';

交运算

交运算：

SELECT sno FROM SC WHERE cno = 'C1' AND sno IN (SELECT sno FROM SC WHERE cno = 'C3');

差运算

差运算：

SELECT sno FROM SC WHERE cno = 'C1' AND sno NOT IN (SELECT sno FROM SC WHERE cno = 'C3');

3.4.6 多表查询的等价形式

定义
1. 多表查询的等价形式指的是通过不同的查询方法实现相同的查询结果。常见的等价形式包括自然连接、带有IN谓词的子查询、带有比较运算符的子查询、ANY/ALL引出的子查询、EXISTS/NOT EXISTS引出的子查询以及多种混合形式。

多表查询的肯定形式

自然连接（显式内连接和隐式内连接）

显式

SELECT sno, sname
FROM Student
INNER JOIN SC ON Student.sno = SC.sno
INNER JOIN Course ON Course.cno = SC.cno
WHERE score < 60 AND cname = '数据库';

隐式

SELECT Student.sno, sname
FROM Student, SC, Course
WHERE Student.sno = SC.sno 
AND Course.cno = SC.cno 
AND score < 60 
AND cname = '数据库';

IN嵌套

SELECT sno, sname
FROM Student
WHERE sno IN (SELECT snoFROM SCWHERE score < 60AND cno IN (SELECT cnoFROM CourseWHERE cname = '数据库')
);

ANY/ALL嵌套及其等价的表达

SELECT sno, sname
FROM Student
WHERE sno = ANY (SELECT snoFROM SCWHERE score < 60AND cno = ANY (SELECT cnoFROM CourseWHERE cname = '数据库')
);

EXISTS嵌套

SELECT sno, sname
FROM Student
WHERE EXISTS (SELECT *FROM SCWHERE score < 60AND SC.sno = Student.sno AND EXISTS (SELECT *FROM CourseWHERE cname = '数据库'AND Course.cno = SC.cno)
);

多种混合形式

SELECT sno, sname
FROM Student
WHERE sno IN (SELECT snoFROM SC, CourseWHERE SC.cno = Course.cno AND score < 60 AND cname = '数据库'
);

多表查询的否定形式

NOT IN嵌套

SELECT cno, cname
FROM Course
WHERE cno NOT IN (SELECT cnoFROM SCWHERE sno IN (SELECT snoFROM StudentWHERE sname = '陈茹')
);

<>ALL嵌套

SELECT cno, cname
FROM Course
WHERE cno <> ALL (SELECT cnoFROM SCWHERE sno IN (SELECT snoFROM StudentWHERE sname = '陈茹')
);

NOT EXISTS嵌套

SELECT cno, cname
FROM Course
WHERE NOT EXISTS (SELECT *FROM SCWHERE EXISTS (SELECT *FROM StudentWHERE sname = '陈茹'AND sno = SC.snoAND SC.cno = Course.cno)
);