数据库系统查询.docx
- 文档编号:11627144
- 上传时间:2023-06-01
- 格式:DOCX
- 页数:27
- 大小:588.52KB
数据库系统查询.docx
《数据库系统查询.docx》由会员分享,可在线阅读,更多相关《数据库系统查询.docx(27页珍藏版)》请在冰点文库上搜索。
数据库系统查询
3.3.1查询单表
1、选择表中的若干列
*关系代数的表示:
ПA(R)
1)查询指定列
selectsno,snamefromstudent;
selectsname,sno,sdeptfromstudent;
2)查询全部列
(1)依表中的列序
select*fromstudent;
select*fromsc;
(2)依用户指定列序
3)查询经计算的列
(1)计算列
selectsname,2010-sagefromstudent;
selectsname,'生日:
',2010-sage,islower(sdept)fromstudent;
【注:
oracle的小写函数为lower(sdept)】【20101203】
目标列表达式:
*属性列
*有关表达式:
算术表达式、字符串常量、函数等。
(2)列的别名表示
selectsname,2010-sage"生日"froms;--student;
selectsname,2010-sage生日froms;--student;
列标题
*缺省
*改变列标题
2、选择表中的若干元组
1)消除查询结果重复元组
selectsnofromsc;//重复元组,等价selectallsnofromsc;
注:
教材P55最后一句话的结果与此不一致
selectdistinctsnofromsc;//唯一,缺省为all
2)查询满足条件的元组
条件:
where<条件表达式>
1比较大小
selectgradefromscwherenotgrade<60;//演示去掉not情况
比较运算符有:
=,><>=<=!
=,<>,!
>,!
<,not+前述比较运算符
2确定范围between…..and和notbetween…..and
*selectsname,sagefromstudentwheresagebetween20and30;
*selectsname,sagefromstudentwheresagenotbetween20and30;
3确定集合:
in、notin
*selectsname,sage,sdeptfromstudentwheresdeptnotin('信电系');//或in('信电系');
*selectsname,sagefromstudentwheresagein(20,21);
4字符匹配
%任意长度字符串(可以0个字符)
*selectsname,sage,sdeptfromstudentwheresdeptnotlike'化工%';
*selectsname,sage,sdeptfromstudentwheresdeptlike'%系';//或notlike'%系';
_任意单个字符
*selectsname,sage,sdeptfromstudentwheresnamelike'a_';//或notlike'a_';
P78例15~18(自练)
转义符ESCAPE’<换码字符>’
*selectcno,cnamefromcoursewherecnamelike'%\_%'escape'\';
*selectcno,cnamefromcoursewherecnamelike'%_%';
5涉及空值isnull,isnotnull
*select*fromcoursewhereccreditisnull;//或ccreditisnotnull;
6多重条件
用and或or连接多个查询条件,考虑优先级
in与or的关系
*selectsnamefromstudentwheresdept='信息系’andsage<21;
3、对查询结果排序
1)查询的输出结果
*查询语句中不指定查询结果的显示顺序:
不能保证结果集显示顺序(SQLSERVER)。
书中指方便顺序(通常元组在表中的先后顺序)
*查询语句中指定查询结果显示顺序:
按指定顺序显示
2)举例
*select*fromsc
*select*fromscorderbysnodesc,cno;//缺省为升序asc
*select*fromscwheregradeisnotnull
*select*fromsc
wheregradeisnotnull
orderbysno,gradedesc
//selectcno,sno,gradefromscwherecno=3orderbygradedesc;
//selectcno,sno,gradefromscwherecnoin(1,2,3)orderbygradedesc;//空值排在最前面
//selectcno,sno,gradefromscwherecnoin(1,2,3)orderbygrade;//空值排在最后面,缺省表示升序asc
selectsno,cno,gradefromscwherecnoin(1,2,3)orderbysnoasc,gradedesc;//学号升序,同一学号成绩降序。
3、使用集函数
1)统计元组个数count(*)
count(*)
count(distinct*)//语句执行错误(没有指定列名)
//select“行数”=count(*)fromstudent;
//或selectcount(*)“行数”fromstudent;
//或selectcount(*)as“行数”fromstudent;
//selectcount(distinct*)fromstudent;//书上语句执行错误(没有指定列名)
2)统计一列中值的个数
count(distinct<列名>)
count(<列名>)
selectcount(sno)fromsc;//5个
selectcount(sno)fromsc;//5个
selectcount(distinctsno)fromsc;//3个
selectcount(distinctsno)“选课学生数“fromsc;//别名
3)计算一列值的平均值
selectavg(grade)fromsc;//空值不计算
计算1号课程的平均成值
selectavg(grade)fromscwherecno=1;//空值不计算
4)计算一列最大值、最小值、平均值、总和
查询某一课程号的最高分与最低分
selectmax(grade),min(grade)fromscwherecno=1;
查询所有课程的最高分与最低分
selectmax(grade)“最大”,min(grade)“最小”,avg(grade)”平均”,sum(grade)”总和”fromsc;
selectmax(distinctgrade)“最大”,min(distinctgrade)“最小”,avg(distinctgrade)”平均”,sum(distinctgrade)”总和”fromsc;
5)一般格式:
P80第一种情况在SQLanywhere加唯一短语出错!
selectcount(*)
count(distinct*)在SQLanywhere语句执行错误
count([distinct|all]<列名>)
max([distinct|all]grade)“最大”,
min([distinct|all]grade)“最小”,
avg([distinct|all]grade)”平均”,
sum([distinct|all]grade)”总和”
**缺省为all
4、对查询结果分组
例查询各课程的课程号与相应的选修人数
selectcno,count(sno)fromscgroupbycno;
selectcno,avg(grade)fromscgroupbycno;
不加集函数分组selectcnofromscgroupbycno;
selectcno,snofromscgroupbycno;//执行出错,分组条件应改为groupbycno,sno
说明:
分组集函数的作用对象为组
不分组集函数作用于整个查询结果
5、分组后只输出满足条件的组
1)求每门课的平均成绩:
**依cno分组
**求每组的平均成绩
selectcno,avg(grade)fromscgroupbycno;
2)从前面的分组结果中查出平均成绩为90分及以上的元组
**在前面的查询中加作用在分组上的条件:
havingavg(grade)>=90
selectcno,avg(grade)
fromsc
groupbycno
havingavg(grade)>=90;
3)作用在分组上的条件可以是复杂条件,在avg(grade)>=90基础上课程号不为1
selectcno,avg(grade)
fromsc
groupbycno
havingavg(grade)>=90andcno<>1;
6、where与having子句的区别;
where作用于基本表
having作用于分组
3.3.2连接查询
1、等值与非等值连接查询
等值连接
:
例:
查询每个学生及其选修课程情况。
结果表
执行过程:
1)选表1中的第一个元组;2)与表2中的所有元组比较,满足连接条件(等值)的元组进行拼接,形成结果表中的一个元组;3)表1中选下一元组重复步骤2)直到结束。
selectstudent.*,sc.*
fromstudent,sc
wherestudent.sno=sc.sno;//等值
非等值连接
where后为其它比较运算符:
>,<,<>,>=,<=等。
笛卡儿积
例:
selectstudent.*,sc.*fromstudent,sc;
自然连接
例:
selectstudent.sno,sname,ssex,sage,sdept,cno,grade
fromstudent,sc
wherestudent.sno=sc.sno;
表名前缀的作用:
避免混淆。
属性名在不同表中如为唯一,则可以省略表名前缀。
cno只出现在sc表中,所以可不加前缀。
Sno则不行。
2、自身连接
例:
查询每一门课的间接先修课号(先修课的先修课)
selecto,second.cpno
fromcoursef,coursesecond/*取两个别名f与second*/
wheref.cpno=o;
(用first出错,为保留字)
思考题:
每门课的先修课程名?
selectcno,cnamefromcourse
wherecnoin(
selectsecond.cpno
fromcoursef,coursesecond/*取两个别名f与second*/
wheref.cpno=o);
3、外连接
从连接操作的结果可看出,只有满足条件的元组才能作为结果输出
对学生表来说,没有选课的学生(97004、97005、97101)学其基本情况信息没有出现在结果表中。
如希望这些信息出现在基本表中,如下表:
selectstudent.sno,sname,ssex,sage,sdept,cno,grade
fromstudentleftouterjoinsc
onstudent.sno=sc.sno;
*leftouterjoin左连接,
*on用where代替将出错
*rightouterjoin右连接
下面语句的查询结果与上一语句同
selectstudent.sno,sname,ssex,sage,sdept,cno,grade
fromscrightouterjoinstudent
onstudent.sno=sc.sno;
4、复合条件连接
含义:
where子句中有多个查询条件
例:
查询选修了2号课程且成绩在90分以上所有学生的学号、姓名
selectstudent.sno,sname
fromstudent,sc
wherestudent.sno=sc.snoando=3andsc.grade>90;
*条件student.sno=sc.sno
*加o=3
*加sc.grade>90
例:
查询每个学生(学号,姓名)选修的课程名及其成绩
selectstudent.sno,sname,cname,gradefromstudent,sc,coursewherestudent.sno=sc.snoando=o;
3.3.3嵌套查询
1、带有IN条件的子查询
例:
查询与“刘晨”在同一个系学习学生的学号、姓名、系
第一步:
刘晨所在的系;
selectsdept
fromstudent
wheresname='刘晨';
第二步:
查询与“刘晨”在同一个系学习学生的学号、姓名、系
selectsno,sname,sdept
fromstudent
wheresdeptin(selectsdeptfromstudentwheresname='刘晨');//
●selectsdeptfromstudentwheresname='刘晨'为子查询
●查询结果包括刘晨本人
●DBMS处理:
第一步求解子查询,确定刘晨所在的系;第二步求解父查询
●如果查询结果中不包括刘晨本人,where中加条件:
andsname<>'刘晨'
●本查询确切知道内层查询结果是单值,in可以用比较运算符=代替。
(根据题目需要也可以用其它比较符)
●本例的查询可以通过表的自连接查询来完成(见P88)
例38查询选修了课程名为‘数据库原理’的学生学号和姓名。
已知条件:
课程名为‘数据库原理’(课程表course)
查找:
的学生学号和姓名(学生表student)
步骤分析:
1)根据‘数据库原理’在course表中查找cno;2)根据cno在sc表中求sno;3)根据sno在student表中求sname。
1)Selectcnofromcoursewherecname='数据库原理'
1)selectsnofromscwherecnoin
(1)
2)selectsno,snamefromstudentwheresnoin
(2);
语句合并:
selectsno,sname
fromstudentwheresnoin
(selectsnofromscwherecnoin
(Selectcnofromcoursewherecname='数据库原理));
注:
1、本例查询可以通过连接查询实现(见P90)
2、本查询明确知道最内层的查询结果为单值,所以in也可以用比较运算符=代替,同上例
用连接查询实现
selectstudent.sno,snamefromstudent,sc,course
wherestudent.sno=sc.snoand
o=oand
ame='数据库原理';//课本上语句执行出错,因为sno前无student。
2、带有比较运算符的子查询
在上例中已经讲述
3、带ANY或ALL的子查询
1)、运算符及语义
例:
查询其他系中比“信电系”任一学生年龄小的学生名单
SELECTSname,Sage
fromstudent
wheresage (SELECTsagefromstudentwheresdept=’信电系’) andsdept<>’信电系’/*红部分不加情况*/ orderbysagedesc; 注: *蓝部分等价: sage< (SELECTmax(sage)fromstudentwheresdept=’信电系’) *书中错误: >不正确 例: 查询其他系中比“信电系”所有学生年龄都小的学生名单 SELECTSname,Sage fromstudent wheresage (SELECTsagefromstudentwheresdept=’信电系’) andsdept<>’信电系’/*红部分不加情况*/ orderbysagedesc; 注: *蓝部分等价: sage< (SELECTmin(sage)fromstudentwheresdept=’信电系’) 需要特别指出: 子查询中不能使用排序子句,排序子句永远只能对最终查询结果排序 4、带有EXISTS和NOTEXISTS关键字的相关子查询 例: 查询所有选修了1号课程的学生姓名 学生表: Sno Sname Ssex Sage Sdept 97001 '李勇' 'f' 22 '信电系' 97002 '刘晨' 'f' 21 '信电系' 97003 '王名' 'f' 20 '信电系' 97004 '张立' 'm' 22 '信电系' 97005 'ee' 'm' 21 '信电系' 97101 'ee' 'm' 18 '化工系' 课程表: Sno Cno Grade 97001 1 90 97002 2 89 97002 3 89 97003 3 95 97003 1 null 思考: *方法1: 用前述的不相关子查询实现(子查询与父查询不相关)(IN、比较、ANY或ALL) 1)根据cno求学号集合 SELECTSnoFROMSCWHERECno=1 2)SELECTsnameFROMstudent WHEREsnoin (2)) 即: SELECTsnameFROMstudent WHEREsnoin (SELECTSnoFROMSCWHERECno=1) *方法2: 用相关的子查询实现: 1)每个学生{从学生(Student)表中依次取每个学生的学号(Sno)} 2)选修了1号课程(SC表中存在1)中的学号选修了课程号1的元组) SELECTSname FROMStudent /*1)每个学生*/ WHEREEXISTS (SELECT*FROMSC WHERESno=Student.SnoANDCno=1) /*2)主查询中指定的学生选修了1号课程*/ *带有EXISTS和NOTEXISTS关键字相关子查询的执行过程与前述的不相关子查询的区别 **不相关子查询: 先执行子查询,后执行外查询 **相关子查询: 子查询的的查询条件依赖于外层父查询的某个属性值,外查询的结果集为满足子查询的行。 (例中揭示: 对外层查询的每一元组,根据它与内层查询的相关属性值处理内查询,内层的查询结果非空,则取此元组放入结果表中) 例: 查询所有未修1号课程的学生姓名 将EXISTS改为NOTEXISTS 3.3.4集合查询 1、并(UNION) 例: 查询信电系学生及年龄不大于19的学生 SELECT*FROMStudentWHERESdept=’信电系’ Union SELECT*FROMStudentWHERESage<=19; 同: select*fromyj_studentwheresdept='j'orsage<=19; 例: 选修了1号或选修了2号课程的学生 2、交 例: 查询信电系学生与年龄不大于19的学生的交集 SELECT*FROMStudent WHERESdept=’信电系’ANDSage<=20; 例: 查询选修了2号与选修了3号课程的学生交集(既选修了2号课程又选修了3号课程) SELECTSnoFROMSC WHEREcno=2AND snoIN(SELECTSnoFROMSCWHERECno=3); 如下实现是否正确: SELECTSnoFROMSC WHEREcno=2ANDCno=3 3、差 例: 查询信电系学生与年龄不大于20的学生差集 SELECT*FROMStudent WHERESdept=’信电系’ANDSage>20; 例: 查询选修了2号与选修了3号课程的学生差集(选修了2号但没选修3号课程的学生) SELECTSnoFROMSC WHEREcno=2AND snoNOTIN(SELECTSnoFROMSCWHERECno=3); 3.4数据更新 数据更新包括插入、修改、删除三条语句 3.4.1插入数据 *两种形式 1)插入单个元组(两种情况), 2)插入子查询结果 *数据的完整性 插入操作时,插入数据必须满足完整性约束条件 1、插入单个元组 1)表名后不加属性: 每属性值需要全部列出。 insertintoscvalues(97006,1,null); 2)表名后加属性: 值与属性需要一一对应,未列出属性自动取空值。 insertintosc(sno,cno)values(97006,1); 成绩自动取空值 完整性考虑: *表定义中如果属性说明非空则不能取空值。 *其它完整性考虑 一般格式: INSERTINTO<表名>[(<属性列1>[,<属性列2>]…)]] VALUES(<常量1>[,<常量2>]…); 2、插入子查询 将子查询结果的一组元组插入指定表中。 例: 对每个系,求学生的平均年龄,并把结果存入数据库 Sno Sname Ssex Sage Sdept 97001 '李勇' 'f' 22 '计算机' 97002 '刘晨' 'f' 21 '计算机' 97003 '王
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 查询