数据库（查询）

查询

基本命令格式：

SELECT [ALL|DISTINCT]   <目标列表达式>

 [，<目标列表达式>] …

FROM <表名或视图名>[，<表名或视图名> ] …

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ]；

一、单表查询

1、选择表中的若干列

Select <目标列表达式> from <表名或视图名>

【示例1：】查询全体学生姓名与学号

Select 姓名，学号 from student

【示例2：】查询全体学生的全部字段项目

Select * from student

2、选择表中的若干元组

（1）消除取值重复的行

Select distinct<目标列表达式>from<表或视图名>

【示例1：】查询选修了课程的学生学号

Select distinct 学号 from sc

【示例2：】观察命令的执行结果

Select distinct 学号，课程号  from sc

（2）查询满足条件的元组

select …  where <条件表达式>

条件表达式中常用运算符号：

◆比较：>,>=,<,<=,=,!=,<>(,!>,!<)

◆范围：between …and

◆集合：in

◆匹配：like (%:任意个字符，_:单个字符)

◆空值：is null；is not null

◆逻辑：and，or，not

【示例1：】显示所有男生记录

Select * from student where 性别=‘男’

【示例2：】显示20岁以下的学生姓名及年龄

Select 姓名，2007-year(出生日期)  年龄 from student where 2007-year(出生日期) <20

【示例3：】查询学分在1-3之间的所有课程名及学分

Select 课程名,学分 from coursewhere 学分>=1 and 学分<=3

Select 课程名,学分 from coursewhere 学分 between 1and 3

Select 课程名,学分 from coursewhere 学分 in (1,2,3)

【示例4：】查找所有姓王的学生的记录

Select * from student where 姓名 like ‘王%’

【示例5：】查找姓名中第2个字是“小”的女同学

Select * from student where 姓名 like ‘_ _小%’ and 性别=‘女’

【示例6：】查询以“DB_”开头的课程

Select * from course where 课程名 like 'DB\_%' escape '\' 　　说明： ‘\‘ 为换码字符

【示例7：】查询没有成绩的学生的学号和课程号

Select 学号,课程号 from scwhere 成绩 is null

3、对查询结果进行排序

select …  order by <列名>[asc|desc]

【示例1：】查询全体学生信息，结果按姓名降序排列。

Select * from student order by 姓名 desc

【示例2：】查询全体学生信息，结果按系升序排列，同一系内部按姓名升序排列

Select * from student order by 系,姓名

4、统计操作

在<目标列表达式>中使用集函数：

Count([distinct|all]*)           统计元组个数

Count([distinct|all]<列名>)  统计一列中值的个数

sum([distinct|all]<列名>)    求一列值的总和

avg([distinct|all]<列名>)     求一列值的平均值

max([distinct|all]<列名>)    求一列值中的最大值

min([distinct|all]<列名>)     求一列值中的最小值

其中：

◆Distinct：表示在计算时要取消指定列中的重复值。

◆All：缺省值，不取消重复值。

【示例1：】查询信息系的学生人数

Select count(*) from student where 系='信息’

【示例2：】查询选修了课程的学生人数

Select count(distinct(学号)) 人数 from sc

【示例3：】查询4号课程的最高分、最低分和平均分。

Select max(成绩),min(成绩),avg(成绩) from sc where 课程号='04'

5、对查询的结果进行分组

select …  group by <列名>[having <条件表达式>]

having <条件表达式>：作用的对象是组，即表示对分组后的记录进行筛选。

【示例1：】统计各个系的学生人数。

Select 系,count(*)  from student groupby 系

【示例2：】统计每位学生的所有课程的平均成绩

Select 学号,avg(成绩) from scgroup by 学号

【示例3：】统计每门课程的最高分、最低分

Select 课程号,max(成绩) ,min(成绩) from sc group by 课程号

【示例4：】统计信息系和管理系的学生人数

Select 系,count(*) as 学生人数 from student group by 系 having 系='信息' or 系='工管'

【示例5：】查询选修了3门以上课程的学生学号和门数

Select 学号,count(*) from sc group by 学号 having count(*)>3

二、多表查询（连接查询）

1、命令及运算符

Select …from <表名1>,<表名2> where [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>=、>、>=、<、<=、!=、between … and

2、连接查询的分类

◆按连接的运算符分类：等值连接与非等值连接两类。

◆按连接的类型分类：内连接、外连接和交叉连接。

3、内连接

只返回与连接条件相匹配的元组。

【示例1：】查询所有学生的选课情况

Select student.*,sc.* from student,sc wherestudent.学号=sc.学号

【示例2：】查询选修“数据库”的学生的学号及成绩

Select x.学号,x.成绩 from coursec,sc x where c.课程号=x.课程号 and c.课程名='数据库'           

【示例3：】查询“王一”的姓名、课程名及成绩

Select s.姓名, c.课程名 ,x.成绩 from student s,sc x,course cwhere s.学号=x.学号 and c.课程号=x.课程号 and s.姓名='王一'

【示例4：】查询每一门课程的先修课的名称(自连接)

Select a.课程号,a.课程名,b.课程名 from course a,course b where a.先修课=b.课程号

4、外连接

不但返回与连接条件相匹配的元组，而且还会根据外连接类型不同返回与连接条件不匹配的元组。左外连接、右外连接、全外连接。

【示例1：】显示所有学生的姓名及选课情况（没有选课的学生名单也要列出来）

Select student.姓名,sc.课程号,sc.成绩 fromstudent,sc where student.学号*=sc.学号

5、交叉连接（广义笛卡尔积）

返回两个表中元组的交叉乘积。

6、SQL-92中连接查询的表示方法

将连接条件放在From子句的后面，基本格式为：

R [natural] {连接类型} join S {on 条件}

连接类型：

◆Cross  join：笛卡尔积

◆Left outerjoin：左外连接

◆Right outerjoin：右外连接

◆Full outerjoin：全外连接

◆Inner join：内连接

三、嵌套查询

在一个select查询语句的where子句或having子句中插入另一个查询语句称为嵌套查询。

【例如：】查询选修了2号课程的学生名单。

Select s.姓名 from student s,sc x where s.学号=x.学号 and x.课程号='02'

Select 姓名 from student where 学号 in

        ( Select 学号 from scwhere 课程号='02')

上层的查询模块称为外层查询或父查询。下层的查询模块称为内层查询或子查询。子查询中不能使用order by 子句。

1、带有IN谓词的子查询

【示例1：】查询与“王一”同一个系的学生姓名

Select 姓名 from student where 系 in (select 系 from student where 姓名='王一')

◆查询“数据库”课程的学生选修名单

Select 姓名 from student where 学号 in (select 学号 from sc where 课程号 in (select 课程号 from course where 课程名='数据库'))

不相关子查询：子查询的查询条件不依赖于父查询。

2、带有比较运算符的子查询

当确信子查询返回的是单值时，可使用比较运算符。

【示例1：】查询与“王一”同一个系的学生姓名

Select 姓名 from student where 系 = (select 系 from student where 姓名='王一')

3、带有some(any)或all谓词的子查询

使用some或all时，必须同时使用比较运算符。

【示例1：】查询其他系中比信息系所有学生年龄都小的学生的姓名。(即比信息系中年龄最小的还要小)

Select 姓名 from student where 出生日期>all (select 出生日期 from student where 系='信息')

And 系<>'信息'

Select 姓名 from student where 出生日期 >(select max(出生日期) from student where 系='信息')

And 系<>'信息’

带有some、all的子查询往往可以用集函数来代替，而且查询的效率更高。

4、带有exists谓词的子查询

该类子查询不返回任何数据，只产生逻辑真或假。

【示例1：】查询选修了1号课程的学生姓名

Select 姓名 from student where 学号 in

        ( Select 学号 from scwhere 课程号='01')

Select 姓名 from student where exists(select * from sc where 学号=student.学号 and 课程号='01')

相关子查询：子查询的查询条件依赖于外层父查询的某个属性值。

所有的带in、比较运算符、some和all谓词的子查询都可以用带exists的子查询替换。反之却不一定。

【示例2：】查询选修了全部课程的学生姓名

Select 姓名 from student where not exists (select * from course where notexists (select * from sc where 学号=student.学号 and 课程号=course.课程号))

语义：没有一门课程没有被选修。

四、集合查询

集合运算包括并（union）、差（intersect）、交（except）。其中差和交运算是SQL-92中规定的。

【示例1：】查询选修了1号课程和3号课程的并集

Select * from sc where 课程号=“1” union

(Select * from sc where 课程号=“3”)

Select * from sc where 课程号=“1” or 课程号= “3”

【示例2：】查询选修了1号课程和3号课程的交集

Select * from sc where 课程号=“1” and 学号 in(select 学号 from sc where 课程号=“3”)

Select * from sc where 课程号=“1” intersect

(Select * from sc where 课程号=“3”)

【示例3：】查询选修了1号课程和3号课程的差集

Select * from sc where 课程号=“1” and 学号 notin(select 学号 from scwhere 课程号=“3”)

Select * from sc where 课程号=“1” except

(select 学号 from sc where 课程号=“3”)