ORACLE50个sql语句.docx
《ORACLE50个sql语句.docx》由会员分享，可在线阅读，更多相关《ORACLE50个sql语句.docx（37页珍藏版）》请在冰豆网上搜索。
ORACLE50个sql语句
备注：
以下语句都已经改成了oracle支持的语句。
不熟的sql：
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
？
？
？
是不是有having的时候就不能用distinct而只能用groupby呢？
？
？
貌似是的
说明：
以下五十个语句都按照测试数据进行过测试，最好每次只单独运行一个语句。
问题及描述：
--1.学生表
Student（Sno,Sname,Sage,Ssex）--Sno学生编号,Sname学生姓名,Sage出生年月,Ssex学生性别
--2.课程表
Course（Cno,Cname,Tno）--Cno--课程编号,Cname课程名称,Tno教师编号
--3.教师表
Teacher（Tno,Tname）--Tno教师编号,Tname教师姓名
--4.成绩表
SC（Sno,Cno,score）--Sno学生编号,Cno课程编号,score分数
*/
--创建测试数据
createtablekxsh.student（
snochar（10）primarykeynotnull,
snamevarchar2（10）notnull,
ssexchar
（2）check（ssexin（'男','女'））notnull,
sagedate
）;
insertintokxsh.studentvalues（'2008111001','赵雷','男','1990-01-01'）;
insertintokxsh.studentvalues（'2008111002','钱电','男','1990-12-21'）;
insertintokxsh.studentvalues（'2008111003','孙风','男','1990-05-20'）;
insertintokxsh.studentvalues（'2008111004','李云','男','1990-08-06'）;
insertintokxsh.studentvalues（'2008111005','周梅','女','1991-12-01'）;
insertintokxsh.studentvalues（'2008111006','吴兰','女','1992-03-01'）;
insertintokxsh.studentvalues（'2008111007','郑竹','女','1989-07-01'）;
insertintokxsh.studentvalues（'2008111008','王菊','女','1990-01-20'）;
补充1：
date包含年月日时分秒，oracle默认格式为’dd-mon月-yy’（dd日子、mon月份、yy2位的年，如’06-4月-99’1999年4月6日）。
修改日期的默认格式（临时修改，数据库重启之后仍未默认。
永久修改需要修改注册表）：
altersessionsetnls_date_format=‘yyyy-mm-dd’;
修改之后，我们可以用熟悉的格式添加日期类型
createtablekxsh.cource（
cnochar
（2）primarykeynotnull,
cnamevarchar（10）notnull,
tnochar（3）referenceskxsh.teacher（tno）
）;
insertintokxsh.courcevalues（'01','语文','002'）;
insertintokxsh.courcevalues（'02','数学','001'）;
insertintokxsh.courcevalues（'03','英语','003'）;
createtablekxsh.teacher（
tnochar（3）primarykeynotnull,
tnamevarchar2（10）notnull
）;
insertintokxsh.teachervalues（'001','张三'）;
insertintokxsh.teachervalues（'002','李四'）;
insertintokxsh.teachervalues（'003','王五'）;
createtablekxsh.sc（
snochar（10）notnullreferenceskxsh.student（sno）,
cnochar
（2）notnullreferenceskxsh.cource（cno）,
scoredecimal（18,2）,
primarykey（sno,cno）
）;
decimal（a,b）：
a代表精度，b代表小数位数。
精度是指总的数字位数（小数点左边和右边位数的总和），小数位数是指小数点右边的位数。
精度和小数位数的默认值分别为18和0。
如果在decimal类型中不指定这两个值，Oracle将截断数字的小数部分，而不会产生错误。
insertintokxsh.scvalues（'2008111001','01',80）;
insertintokxsh.scvalues（'2008111001','02',90）;
insertintokxsh.scvalues（'2008111001','03',99）;
insertintokxsh.scvalues（'2008111002','01',70）;
insertintokxsh.scvalues（'2008111002','02',60）;
insertintokxsh.scvalues（'2008111002','03',80）;
insertintokxsh.scvalues（'2008111003','01',80）;
insertintokxsh.scvalues（'2008111003','02',80）;
insertintokxsh.scvalues（'2008111003','03',80）;
insertintokxsh.scvalues（'2008111004','01',50）;
insertintokxsh.scvalues（'2008111004','02',30）;
insertintokxsh.scvalues（'2008111004','03',20）;
insertintokxsh.scvalues（'2008111005','01',76）;
insertintokxsh.scvalues（'2008111005','02',87）;
insertintokxsh.scvalues（'2008111006','01',31）;
insertintokxsh.scvalues（'2008111006','03',34）;
insertintokxsh.scvalues（'2008111007','02',89）;
insertintokxsh.scvalues（'2008111007','03',98）;
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
--1.1、查询同时存在"01"课程和"02"课程的情况
Oracle中必须用双引号！
！
selecta.*,b.score[课程'01'的分数],c.score"[课程'02'的分数]"
fromStudenta,SCb,SCc
wherea.Sno=b.Snoanda.Sno=c.Snoandb.Cno='01'andc.Cno='02'and
b.score>c.score
--1.2、查询同时存在"01"课程和"02"课程的情况和存在"01"课程但可能不存在"02"课程的情况（不存在时显示为null）（以下存在相同内容时不再解释）
selecta.*,b.score[课程"01"的分数],c.score[课程"02"的分数]
fromStudenta
leftjoinSCbona.Sno=b.Snoandb.Cno='01'
leftjoinSCcona.Sno=c.Snoandc.Cno='02'
whereb.score>nvl（c.score,0）
补充：
nvl函数的作用：
如果c.score为null，则用0来代替。
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
--2.1、查询同时存在"01"课程和"02"课程的情况
selecta.*,b.score[课程'01'的分数],c.score[课程'02'的分数]fromStudenta,SCb,SCc
wherea.Sno=b.Snoanda.Sno=c.Snoandb.Cno='01'andc.Cno='02'andb.score--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
selecta.*,b.score[课程"01"的分数],c.score[课程"02"的分数]fromStudenta
leftjoinSCbona.Sno=b.Snoandb.Cno='01'
leftjoinSCcona.Sno=c.Snoandc.Cno='02'
whereisnull（b.score,0）--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
selecta.Sno,a.Sname,cast（avg（b.score）asdecimal（18,2））avg_score
fromStudenta,scb
wherea.Sno=b.Sno
groupbya.Sno,a.Sname
havingcast（avg（b.score）asdecimal（18,2））>=60
orderbycast（avg（b.score）asdecimal（18,2））desc
补充2：
最后三句不好理解，可以一行一行的测试，体验它们的功能。
groupby:
用于对查询的结果分组统计（注意：
select中列举了哪些内容，groupby中就要原样的列举出来，聚合函数除外）。
having：
一般同groupby一起使用，用来筛选结果，但也可以不用
Where用在结果集聚合之前，having用在结果集聚合之后。
orderby：
对查询结果进行排序（asc、desc），默认为asc。
cast（AasB）函数：
A代表的是原值，B代表的是一种数据类型。
作用是将A转换成B类型。
decimal（a,b）：
a代表精度，b代表小数位数。
精度是指总的数字位数（小数点左边和右边位数的总和），小数位数是指小数点右边的位数。
精度和小数位数的默认值分别为18和0。
如果在decimal类型中不指定这两个值，Oracle将截断数字的小数部分，而不会产生错误。
补充3：
groupby和distinct的区别
这两个作用都是分组查询,实现的功能是一样的。
但是当select语句中有聚合函数时，只能用groupby，用distinct的话会报错。
Selectdistinctsno,sname
Fromstudent
Where.....
如上，distinctsno，sname是将sno、sname这两个字段看成一体，只要sno、sname这两个字段相同，就将其视为重复记录。
Selectsno,sname，count（sno）
Fromstudent
Where.....
Groupbysno,sname
如上，groupbysno，sname是将sno、sname这两个字段看成一体，只要sno、sname这两个字段相同，就将其视为重复记录。
因为select语句中有聚合函数，所以只能使用groupby。
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--4.1、查询在sc表存在成绩的学生信息的SQL语句。
selecta.Sno,a.Sname,cast（avg（b.score）asdecimal（18,2））avg_score
fromStudenta,scb
wherea.Sno=b.Sno
groupbya.Sno,a.Sname
havingcast（avg（b.score）asdecimal（18,2））<60
orderbya.Sno
--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。
selecta.Sno,a.Sname,nvl（cast（avg（b.score）asdecimal（18,2））,0）avg_score
fromStudenta
leftjoinscbona.Sno=b.Sno
groupbya.Sno,a.Sname
havingnvl（cast（avg（b.score）asdecimal（18,2））,0）<60
orderbya.Sno
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--5.1、查询所有有成绩的SQL。
selecta.Sno[学生编号],a.Sname[学生姓名],count（b.Cno）选课总数,sum（score）[所有课程的总成绩]
fromStudenta,SCb
wherea.Sno=b.Sno
groupbya.Sno,a.Sname
orderbya.Sno
--5.2、查询所有（包括有成绩和无成绩）的SQL。
selecta.Sno,a.Sname,count（b.Cno）,nvl（sum（score）,0）
fromStudenta
leftjoinSCbona.Sno=b.Sno
groupbya.Sno,a.Sname
orderbya.Sno
--6、查询"李"姓老师的数量
selectcount（Tname）["李"姓老师的数量]
fromTeacher
whereTnamelike'李%'
注意：
%表示0到多个字符，_表示任意单个字符
--7、查询学过"张三"老师授课的同学的信息
selectdistinctStudent.*
fromStudent,SC,Course,Teacher
whereStudent.Sno=SC.SnoandSC.Cno=Course.Cnoand
Course.Tno=Teacher.TnoandTeacher.Tname='张三'
orderbyStudent.Sno
注意：
distinct关键字用来过滤掉多余的重复记录，只保留一条。
（其实这里根本用不到这个关键字！
）
--8、查询没学过"张三"老师授课的同学的信息
selectm.*
fromStudentm
whereSnonotin（
selectdistinctSC.Sno
fromSC,Course,Teacher
whereSC.Cno=Course.CnoandCourse.Tno=Teacher.Tno
andTeacher.Tname='张三'）
orderbym.Sno
注意：
请先仔细分析下面这个例子，体会exists的作用。
selectNAME
fromS
whereexists
（select‘*’
fromSC
whereCNO='C1‘
andSNO=S.SNO）
in和exists的区别。
in后的子查询与外层查询无关，每个子查询只执行一次；而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询。
exists：
带有exists关键字的子查询不返回任何数据，只产生逻辑真值true或逻辑假值false。
若内层查询结果非空，则返回真值；若内层查询结果为空，则返回假值。
由exists引出的子查询，其目标列表达式通常都用固定值，因为带exists的子查询只返回真值或假值，给出列名无实际意义。
相关子查询：
首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where字句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
其实最简单的方法如下：
selectdistincta.sno,a.sname
fromstudenta,scb,scc
wherea.sno=b.snoand
a.sno=c.snoand
o='01'and
o='02';
--方法1
selectStudent.*
fromStudent,SC
whereStudent.Sno=SC.SnoandSC.Cno='01'andexists（
Select1
fromSCSC_2
whereSC_2.Sno=SC.SnoandSC_2.Cno='02'）
orderbyStudent.Sno
--方法2（这个方法虽然看上去很复杂，但是必须看懂它，进而掌握union和unionall的用法和区别）
selectm.*
fromStudentm
whereSnoin（
selectSno
from（
selectdistinctSnofromSCwhereCno='01'
unionall
selectdistinctSnofromSCwhereCno='02'
）groupbySnohavingcount
（1）=2
）
orderbym.Sno
注意：
union和unionall的用法
Union：
得到两个查询结构的并集，并且自动去掉重复行，不会排序。
Unionall：
得到两个查询结果的并集，不会去掉重复行，也不会排序。
上边方法2的意思是先查出选了‘01’课程的学号和选了‘02’课程的学号（用的unionall，不会去掉重复行，也不会排序），将他们都列出来。
然后选择出现了两次的学号，然后查询这个学号对应的学生的信息
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
--方法1
selectStudent.*
fromStudent,SC
whereStudent.Sno=SC.SnoandSC.Cno='01'andnotexists（
Select1
fromSCSC_2
whereSC_2.Sno=SC.SnoandSC_2.Cno='02'）
orderbyStudent.Sno
--方法2
selectStudent.*
fromStudent,SC
whereStudent.Sno=SC.SnoandSC.Cno='01'andStudent.Snonotin（
SelectSC_2.Sno
fromSCSC_2
whereSC_2.Sno=SC.SnoandSC_2.Cno='02'）
orderbyStudent.Sno
方法三：
使用关键字minus
select*
fromstudent
wheresnoin（
selectsno
from（
selectsnofromscwherecno='01'
minus
selectsnofromscwherecno='02'））
orderbysno;
注意：
minus和intersect的用法。
Minux：
得到两个查询结果的减集，以第一列进行排序。
Intersect：
得到两个查询结果的交集，按查询结果的第一列进行排序。
上边方法三的意思是：
先查出选修了‘01’和选修了‘02’课程的学生的学号，用‘01’的减去‘02’的，查询所得到的学号。
然后根据学号查询学生的信息。
--11、查询没有学全所有课程的同学的信息
--11.1、
selectStudent.*
fromStudent,SC
whereStudent.Sno=SC.Sno
groupbyStudent.Sno,Student.Sname,Student.Sage,Student.Ssex
havingcount（Cno）<（selectcount（Cno）fromCourse）
注意：
刚开始时写的如下sql语句，试解释为什么不行。
selectStudent.*
fromStudent,SC，Course
whereStudent.Sno=SC.Sno
groupbyStudent.Sno,Student.Sname,Student.Sage,Student.Ssex
havingcount（SC.Cno）答：
通过比较发现上面两个sql语句的主要区别在Cource表的连接位置。
那么为什么第二个就错了呢？
？
注意理解表连接的意思。
在下边这个sql语句中，因为Cource和student、SC进行了表连接，最后生成的结果就是一组数据。
此时数据的行数已经是确定的了，因此count（SC.Cno）和count（Cource.Cno）查出来的结果是一样的，所以执行第二个sql语句永远查不出数据。
--11.2
selectStudent.*
fromStudent
leftjoinSConStudent.Sno=SC.Sno
groupbyStudent.Sno,Student.Sname,Student.Sage,Student.Ssex
havingcount（Cno）<（selectcount（Cno）fromCourse）
--12、查询至少有一门课与学号为"2008111001"的同学所学相同的同学的信息
selectdistinctStudent.*
fromStudent,SC
whereStudent.Sno=
升级会员 