数据库中的数据查询

发表于 更新于 分类于

数据查询（单表查询）

语句格式

SELECT [ALL | DEISTINCT] <目标列表达式>[, <目标列表达式>]...
FROM <表名或视图名>[, <表名或视图名>]...|(SELECT 语句) [AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC]];
SELECT: 要查询的内容 (选定列) (ALL不去重(缺省) / DEISTINCT去重) ;
FROM: 从哪个表查询 (选定表) ;
WHERE: 查询的约束条件 (选定行) (条件表达式里面不能使用聚集函数) ;
——前三者为一组,用于查询出结果集——
GROUP BY: 结果集的分组方式 (按照某列值分组,相同值为一组统计数量) ;
HAVING: 只输出满足条件的结果 ;
——中间二者为一组,用于对结果集的输出限制,通常伴随使用聚集函数——
ORDER BY: 结果集的排序方式 (ASC升序(缺省) / DESC降序) .
——最后一条为一组,用于对结果排序——

功能分解

avatar

SELECT FROM WERE组合 (查询出结果集)

SELECT: 要查询的内容 (选定列) (ALL不去重(缺省) / DEISTINCT去重) ;
FROM: 从哪个表查询 (选定表) ;
WHERE: 查询的约束条件 (选定行) ;

代码示例(简单查询-灵活调整目标表达式):

1
2
3
4
5
6
7
8
9
10
11
12
#查询全体学生的学号与性名
SELECT Sno, Sname FROM Student;
#查询全部列(将<目标表达式指定为*即表示指定所有列>)
SELECT * FROM Student;
#查询全体学生的姓名及其出生年份(<目标表达式>可以是简单的计算表达式)
SELECT * FROM Sname, 2020-Sage FROM Student;
#查询签体学生的姓名,出生年份,院系(院系用小写)(<目标表达式>可以是字符串或使用函数)
SELECT Sname, 'Birth:', 2020-Sage, LOWER(Sdept) FROM Student; #这里Birth单独做一个列,每列值都是Birth:
#同上,使用别名改变查询结构的列标题(在<目标表达式后可以加上自定义结果集的列名>)
SELECT Sname NAME, 'Birth:' BIRTH, 2020-Sage BIRTHDAT, LOWER(Sdept) DEPARTMENT;
#查询先修了课程的学生学号(去重)
SELECT DISTINCT Sno FROM SC;

聚集函数(对结果集做一些计算)

  • 统计元组个数 COUNT(*)
  • 统计一列中值的个数 COUNT ( [DISTINCT | ALL] <列名> )
  • 计算一列值的总和(此列值必须是数值型) SUM ( [DISTINCT | ALL] <列名> )
  • 计算一列值的平均值(此列值必须是数值型) AVG ( [DISTINCT | ALL] <列名> )
  • 求一列中的最大值/最小值 MAX ( [DISTINCT | ALL] <列名> ) / MIN ( [DISTINCT | ALL] <列名> )

代码示例:

1
2
3
4
5
6
7
8
9
10
#查询学生总人数
SELECT COUNT(*) FROM Student;
#查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno) FROM SC; #DISTINCT要写在括号里面
#计算1号课程的学生平均成绩
SELECT AVG(Grade) FROM SC WHERE Cno = '1';
#查询选修1号课程学生的最高分
SELECT MAX(Grade) FROM SC WHERE Cno = '1';
#查询学生201215012选修课程的总学分数
SELECT SUM(Ccredit) FROM SC, Course WHERE Sno = '201215012' AND SC.Cno = Course.Cno; #涉及多表查询

常用的查询条件 (<条件表达式>中用到的运算符)

查询条件 谓词
比较 =, >, <, >=, <=, !=, <>, !>, !<
确定范围 BETEWEEN AND, NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE, NOT LIKE (通配符: _ % ESCAPE(一个字符,任意个字符,转义字符))
空值 IS NULL, IS NOT NULL
多重条件(逻辑运算) AND, OR, NOT

代码示例(运算符的使用-灵活调整条件表达式):

  • 比较 =等于, >大于, <小于, >=大于等于, <=小于等于, != <>不等于, !>不大于, !<不小于
    1
    2
    3
    4
    5
    6
    #查询计算机系全体学生名单
    SELECT Sname FROM Student WHERE Sdept='CS';
    #查询所有年龄在20以下的学生姓名及其年龄
    SELECT Sname, Sage FROM Student WHERE Sage < 20;
    #查询考试成绩不及格的所有学生学号
    SELECT DISTINCT Sno FROM SC WHERE Grade NOT > 60;
  • 确定范围 BETWEEN ... AND ... / NOT BETWEEN ... AND ...
    1
    2
    3
    4
    #查询年龄在20到23之间的学生姓名,院系,年龄(也包含20与23)
    SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
    #查询年龄不在20到23之间的学生姓名,院系,年龄(也排除20与23)
    SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23;
  • 确定集合 IN <值表> / NOT IN <值表>
    1
    2
    3
    4
    #查询计算机系,数学系,信息系学生的姓名与性别
    SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS', 'MA', 'IS');
    #查询非计算机系,数学系,信息系学生的姓名与性别
    SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('CS', 'MA', 'IS');
  • 字符匹配 [NOT] LIKE '<匹配串>' [ESCAPE '<转换码字符>']
    1
    2
    3
    4
    5
    6
    7
    8
    #查询姓刘的学生的姓名,学号,性别
    SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%';#%代表任意数量字符,也包括0个
    #查询姓刘且全名是三个字的学生的姓名,学号,性别
    SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘____';#这里有四个_,代表2个汉字字符(1个汉字2个字符)
    #查询不姓刘的学生的姓名,学号,性别
    SELECT Sname, Sno, Ssex FROM Student WHERE Sname NOT LIKE '刘%';
    #查询DB_Design课程的课程号和学分(ESCAPE '\'表示\字符之后的一个字符不做转换,除了\也可以换成其他字符)
    SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Desgin' ESCAPE '\';
  • 涉及空值的查询 IS NULL / IS NOT NULL
    1
    2
    #查询缺少成绩的学生的学号和相应的课程号
    SELECT Sno, Cno FROM SC WHERE Grade IS NULL; #IS不能用=代替
  • 多重条件查询 AND / OR (AND的优先级高于OR, 可以用括号改变优先级)
    1
    2
    #查询计算机系年龄在20以下的学生姓名
    SELECT Sname FROM Student WHERE Sdept = 'CS' AND Sage < 20;

GROUP BY HAVING组合 (对结果集的输出限制,通常伴随使用聚集函数)

GROUP BY: 结果集的分组方式 (按照某列值分组,相同值为一组统计数量) ;
HAVING: 只输出满足条件的结果 ;

该分组虚化聚集函数的作用对象:

  • 如果未对查询结果分组,聚集函数将作用与整个查询结果
  • 对结果集分组后,聚集函数将分别作用于每个组
  • 按照指定的一列或多列值分组,值相等的为一组

代码示例:

1
2
3
4
5
6
#求每个课程号及相应的选课人数
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno; #可以这么理解:先按照Cno分组,再对每个分组COUNT(Sno)
#查询选修了3门以上课程的学生学号
SELECT Cno FROM SC GROUP BY Sno HAVING COUNT(*) > 3;
#查询平均成绩大于等于90的学生学号和平均成绩
SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) > 90;

HAVING短语和WHERE子句的区别:

  • 作用对象不同
  • WHERE子句作用于基表或视图,从中选择满足条条件的的元组
  • HAVING短语作用于组,从中选择满足条件的组.

ORDER BY组合 (对结果的排序)

ORDER BY: 结果集的排序方式 (ASC升序(缺省) / DESC降序).
对于空值的排序,由系统具体的实现决定.

代码示例:

1
2
3
4
#查询选修3号课程的学生的学号,成绩. 结果按照分数降序排序
SELECT Sno, Grade FROM SC WHERE Cno = '3' ORDER BY Grade DESC;
#查询全体学生情况,按照院系号升序,同系按年龄降序排列
SELECT * FROM Student ORDER BY Sdept ASC, Sage DESC; #ASC缺省可省略

综合练习

1
2
3
4
5
6
7
8
9
10
#列出计算机系姓刘的同学的信息,按照学号大小排序
SELECT *
FROM Student
WHERE Sdept='CS' AND Sname LIKE '刘%'
ORDER BY Sno ASC;
#按系并区分男女统计各系学生的人数,并按照人数降序排序
SELECT Sdept 院系, Ssex 性别, COUNT(Sno) 学生人数
FROM Student
GROUP BY Sdept, Ssex
ORDER BY COUNT(Sno) DESC;