数据库作业评讲.docx
- 文档编号:6313269
- 上传时间:2023-01-05
- 格式:DOCX
- 页数:33
- 大小:19.65KB
数据库作业评讲.docx
《数据库作业评讲.docx》由会员分享,可在线阅读,更多相关《数据库作业评讲.docx(33页珍藏版)》请在冰豆网上搜索。
数据库作业评讲
作业中存在的问题
1.没有验证自己写的对不对.
2.没有给出多种解决方法.
1.检索至少选修’陈军’老师所授全部课程的学生姓名.
方法1:
selectdistinctsnamefromswherenotexists(select*fromcwhereteacher='陈军'andnotexists(select*fromscwheresc.sno=s.snoando=o));
selectdistinctsnofromswherenotexists(select*fromcwhereteacher='t1'andnotexists(select*fromscwheresc.sno=s.snoando=o));
2.检索选修有’陈军’老师所授课程之一的学生姓名.
方法1:
SelectsnameFroms,scWheres.sno=sc.snoandcnoin(selectcnofromcwhereteacher=’陈军’);
方法2:
Selectsnofromscwhereexists(select*fromcwhereo=oandteacher=’陈军’);
方法3:
SelectsnoFromscWherecnoin(selectcnofromcwhereteacher=’陈军’);
方法4:
selectsnofromswheresnoin(selectsnofromscwherecnoin(selectc
nofromcwhereteacher='陈军'));
方法5:
selectsnofromswheresnoin(selectsc.snofromsc,cwhereo=oandteacher=’t1’);
3.检索选修课程号有k1和k5的学生学号.
方法1:
Selectsnofromscasfirst,scassecondwhereo=’k1’ando=’k5’;
方法2:
selectsnofromscwherecno=’k1’orcno=’k2’;
4.检索选修’陈军’老师所授全部课程的学生学号.
正确:
selectsnofromswherenotexists(select*fromcwhereteacher='t1'andnotexists(select*fromscwheresc.sno=s.snoando=o));
错误,得到空集:
selects.snofroms,sc,cwheres.sno=sc.snoando=oando=all(selectofromcwhereteacher=’t1’);
5.检索选修全部课程的学生姓名.
方法1:
Selectsno,count(sno)ascs1fromscgroupbysnohavingcs1=(selectcount(*)fromc);
方法2:
selectsnofromswherenotexists(select*fromcwherenotexists(select*fromscwheresc.sno=s.snoando=o));
方法3:
selectdistincts.snofroms,scwheres.snoin(selectsnofromscgroupbysnohavingcount(*)=(selectcount(*)fromc));
6.检索所选课程和s2完全相同的学生学号.
正确已验证:
Selectsnofromscsc1wherecnoin(Selectcnofromscwheresno=’s1’)groupbysc1.snohavingcount(o)=(Selectcount(cno)fromscsc2wheresno=’s1’);
没有验证,不完整:
Selectsnofromscsc1wherenotexists(select*fromscsc2whereo=’s2’andnotexists(select*fromscsc3wheresc3.sno=sc1.snoandsc3.sno=sc2.sno)intersect(selectsnofromscgroupbysnowherecount(cno)=(selectcount(cno)groupbysnowheresno=’s2’));
7.求至少选修了课程c2和c4的学生学号和姓名.
验证,不正确:
Selectsnofromswherenotexists(select*fromscassc1wheres.sno=sc1.snoandnotexists(select*fromscassc2wheres.sno=sc2.snoand(o=’c1’ando=’c2’)or(o=’c2’ando=’c1’)));
8.求至多选修了课程c2和c4的学生学号和姓名.
验证,不正确:
Selectsnofromswhereexists(select*fromscassc1wheres.sno=sc1.snoandnotexists(select*fromscassc2wheres.sno=sc2.snoand(o=’c1’ando=’c2’)or(o=’c2’ando=’c1’)));
9.检索同时选修课程k1和k5的学生学号.
方法1:
(selectsnofromscwherecno=’k1’)intersect(selectsnofromscwherecno=’k2’);
方法2:
selectsnofromscwherecno=’k1’andsnoin(selectsnofromscwherecno=’k2’);
方法3:
Selectsnosno1fromscwhere'c1'in(selectcnofromscwheresno=sno1)and'c2'in(selectcnofromscwheresno=sno1);
10.解释Selectsnamefroms,scwheres.sno=sc.snoandcnoin(selectcnofromc);完成的功能.
11.检索选修课程包含学号为s2的学生所修课程的学生学号.
12.检索选修课程包含学号为s2的学生所修全部课程的学生学号.
13.解释Selectsnofromscwherecnoin(selectcnofromscwheresno=’s2’);完成的功能.
14.检索李强同学不学课程的课程号.
方法1:
(selectsnofromc)except(selectcnofroms,scwheres.sno=sc.snoandsname=’李强’);
方法2:
selectdistinctcnofromscwhereonotin(selectcnofromsc,swheresc.sno=s.snoands.sno='s5');
方法3:
Selectcnofromcwherenotexists(select*froms,scwheres.sno=sc.snoands.sno=’s2’ando=o);
方法4:
Selectcnofromcwhereonotin(selectofromscwheresc.snoin(selects.snofromswheres.sno=’s2’));
15.检索至少选修两门课程的学生学号.
方法1:
selectfirst.snofromscasfirst,scassecondwherefirst.sno=second.snoando!
=o;
方法2:
selectsnofromscgroupbysnohavingcount(*)>=2;
16.检索全部学生都选修的课程号和课程名.
方法1:
Selectcno,sno,count(sno)asss1fromscgroupbycnohavingss1=(selectcount(*)froms);
方法2:
selectcno,cnamefromcwherenotexists(select*fromswherenotexists(select*fromscwheresc.sno=s.snoando=o));
解释:
select*fromscwheresc.sno=s.snoando=o表示指定的学生sno选修指定课程cno的元组集合T1,如果该集合T1不为空,则notexistsT1为假,select*fromswherenotexists(select*fromscwheresc.sno=s.snoando=o)集合就为空,notexists(select*fromswherenotexists(select*fromscwheresc.sno=s.snoando=o))就为真,cno就是被所有学生选修的课程.
方法3:
selectcnofromcwherenotexists(selectsnofromswherecnonotin(selectcnofromscwhereo=oandsc.sno=s.sno));
解释:
对于某一门课程cno和某个学生sno,如果该学生选修了该门课程,则selectcnofromscwhereo=oandsc.sno=s.sno集合(记为T1)不为空,该门课程cnonotinT1为假,selectsnofromswherecnonotinT1集合(记为T2)为空,notexistsT2为真,cno即是被所有学生选修的课程.
方法4:
selectcnofromcwherenotexists(selectsnofromswherecnonotin(selectcnofromscwheresc.sno=s.sno));
方法5:
Selectcnofromcwherecnoin(selectcnofromscgroupbycnohavingcount(sc.sno)=(selectcount(sno)froms));
17.selectofromc,scwhereo=oandsnoin(selectsnofroms);功能是什么?
-----------------------------------------------------------------------
C:
\mysql-5.0.22-win32\bin>mysql--default-character-set=gbk-uroot-p
Enterpassword:
****
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis2toserverversion:
5.0.22-community
Type'help;'or'\h'forhelp.Type'\c'toclearthebuffer.
mysql>useda1
Databasechanged
mysql>Selectsnofromscwhereexists(select*fromcwhereo=oandt
eacher='t1');
+-----+
|sno|
+-----+
|s1|
|s1|
|s2|
|s3|
|s3|
|s4|
+-----+
6rowsinset(0.02sec)
mysql>select*fromsc;
+-----+-----+-------+
|sno|cno|score|
+-----+-----+-------+
|s1|c1|98.0|
|s1|c2|98.0|
|s2|c1|88.0|
|s2|c3|88.0|
|s3|c1|0.0|
|s3|c2|0.0|
|s3|c3|0.0|
|s4|c2|0.0|
|s4|c3|0.0|
|s5|c3|0.0|
|s1|c3|99.9|
+-----+-----+-------+
11rowsinset(0.00sec)
mysql>selectsnofromswherenotexists(select*fromcwhereteacher='t1'an
dnotexists(select*fromscwheresc.sno=s.snoando=o));
+-----+
|sno|
+-----+
|s1|
|s3|
+-----+
2rowsinset(0.00sec)
mysql>
mysql>delectfromswhereage=19orage=20;
ERROR1064(42000):
YouhaveanerrorinyourSQLsyntax;checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'delec
tfromswhereage=19orage=20'atline1
mysql>deletefromswhereage=19orage=20;
QueryOK,2rowsaffected(0.00sec)
mysql>select*froms;
+-----+-------+------+----------+---------+
|SNO|SNAME|AGE|dname|address|
+-----+-------+------+----------+---------+
|s1||NULL|jisuanji|NULL|
|s2||NULL|NULL|NULL|
|s3||NULL|jisuanji|NULL|
|s4||NULL|NULL|NULL|
|s5||NULL|jisuanji|NULL|
+-----+-------+------+----------+---------+
5rowsinset(0.00sec)
mysql>selectcnofromcwherenotexists(select*fromswherenotexists(sel
ect*fromscwheresc.sno=s.snoando=o));
+-----+
|cno|
+-----+
|c3|
+-----+
1rowinset(0.00sec)
mysql>Selectcno,sno,count(sno)asss1fromscgroupbycnohavingss1=(select
count(*)froms);
+-----+-----+-----+
|cno|sno|ss1|
+-----+-----+-----+
|c3|s1|5|
+-----+-----+-----+
1rowinset(0.00sec)
mysql>selectcnofromcwherenotexists(select*froms,scwheres.sno=sc.sno
->ando=o);
Emptyset(0.00sec)
mysql>selectcnofromcwherenotexists(select*froms,scwheres.sno=sc.sno
->ando=o);
Emptyset(0.00sec)
mysql>selectcnofromcwherenotexists(select*froms,scwheres.sno=sc.sno
ando=o);
Emptyset(0.00sec)
mysql>selectcnofromcwherenotexists(select*froms,scwheres.sno=sc.sn
o
->ando!
=o);
Emptyset(0.00sec)
mysql>selectcnofromcwherenotexists(selectsnofromswherecnonotin(s
->electcnofromscwhereo=oandsc.sno=s.sno));
ERROR1064(42000):
YouhaveanerrorinyourSQLsyntax;checkthemanualthat
correspondstoyourMySQLserverversionfortherightsyntaxtousenear'elect
cnofromscwhereo=oandsc.sno=s.sno))'atline2
mysql>selectcnofromcwherenotexists(selectsnofromswherecnonotin(s
electcnofromscwhereo=oandsc.sno=s.sno));
+-----+
|cno|
+-----+
|c3|
+-----+
1rowinset(0.00sec)
mysql>selectcnofromcwherenotexists(selectsnofromswherecnonotin(s
electcnofromscwheresc.sno=s.sno));
+-----+
|cno|
+-----+
|c3|
+-----+
1rowinset(0.00sec)
mysql>selectofromc,sc,swherecnoin(selectsnofromscwheresnoin(s
electsnofroms));
ERROR1052(23000):
Column'cno'inIN/ALL/ANYsubqueryisambiguous
mysql>selectofromc,sc,swhereoin(selectsnofromscwheresnoin
(selectsnofroms));
Emptyset(0.00sec)
mysql>selectofromc,sc,s;
+-----+
|cno|
+-----+
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
|c3|
|c1|
|c2|
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 作业
![提示](https://static.bdocx.com/images/bang_tan.gif)