数据库查询
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