SQL基础-查询

数据库应用软件,计算机科学与工程学院网信系魏永山,第4章SQL基础,单表查询条件查询排序分组聚合统计多表查询子查询,其基本格式是由SELECT子句、FROM子句和WHERE子句组成的SQL查询语句：SELECT列名表FROM表或视图名WHERE查询限定条件完整的SELECT语句的用法如下所示：SELECT列名表INTO新表名FROM表或视图名WHERE查询限定条件GROUPBY分组表达式HAVING分组条件ORDERBY次序表达式ASC|DESC,SQL查询,使用SELECT语句可以选择查询表中的任意列。FROM子句指出从什么表中提取数据。如果要去掉重复的显示行，可以在字段名前加上DISTINCT关键字来说明,Student(Sno,SName,SSex,Age,Class)Teacher(Tno,TName,Tsex,Tage,Prof,Dept)1查询学生的学号和姓名2查询学生的所有信息3查询教师所属系,4.1单表查询,Student(Sno,SName,SSex,Age,Class)Teacher(Tno,TName,Tsex,Tage,Prof,Dept)1查询学生的学号和姓名selectsno,snamefromstudent;2查询学生的所有信息selectsno,sname,ssex,age,classfromstudent;3查询教师所属系selectdeptfromteacher;,4.1单表查询,select*fromstudent;,selectdistinctdeptfromteacher;,为了选择表中满足查询条件的某些行，可以使用SQL命令中的WHERE子句。WHERE子句的查询条件是一个逻辑表达式，它是由多个关系表达式通过逻辑运算符（AND、OR、NOT）连接而成的。,Score(Sno,Cno,Degree)1查询成绩大于85的记录2查询成绩在85-90之间的记录3查询成绩等于85、86、90的记录4查询课程号是3-245的所有记录,4.2条件查询,Score(Sno,Cno,Degree)1查询成绩大于85的记录select*fromscorewheredegree852查询成绩在85-90之间的记录select*fromscorewheredegree=85anddegree,3,4.4聚合统计,聚合函数可以在HAVING子句使用，但不能在WHERE子句使用。,Student(Sno,SName,SSex,Age,Class)Score(Sno,Cno,Degree)6计算每个班级的学生数selectclass,count(*)stu_countfromstudentgroupbyclass;,4.4聚合统计,Student(Sno,SName,SSex,Age,Class)Score(Sno,Cno,Degree)7计算每个学生的平均分selectsno,avg(degree)avg_degreefromscoregroupbysno;,4.4聚合统计,Student(Sno,SName,SSex,Age,Class)Score(Sno,Cno,Degree)8按照班级统计每门课程的平均成绩SELECTs.classas班号,ameas课程,avg(sc.degree)as平均分FROMstudents,coursec,scorescWHEREs.sno=o=oGROUPBYs.class,ame,4.4聚合统计,Student(Sno,SName,SSex,Age,Class)Score(Sno,Cno,Degree)9统计每门课程的选课人数，并按照人数降序排列selectcno,count(*)fromscoregroupbycnoorderbycount(*)desc,4.4聚合统计,Groupby子句的高级选项WITHCUBE|ROLLUPCUBE：在结果集中返回每个可能的组和子组组合的GROUPBY汇总行。汇总行在结果中显示为null,用来表示所有值。ROLLUP：结果集按层次结构顺序，依据GROUPBY后面所列第一个字段做汇总运算。,4.4聚合统计,Groupby子句的高级选项WITHCUBE|ROLLUP9按照班级统计每门课程的平均成绩，以及班级平均分和课程平均分SELECTs.classas班号,ameas课程,avg(sc.degree)as平均分FROMstudents,coursec,scorescWHEREs.sno=o=oGROUPBYs.class,ameWITHCUBE,4.4聚合统计,在一个查询中，当需要对两个或多个表连接时，可以指定连接列，在WHERE子句中给出连接条件，在FROM子句中指定要连接的表，其格式如下：SELECT列名1，列名2，FROM表1，表2，WHERE连接条件,Student(Sno,SName,SSex,Age)Score(Sno,Cno,Degree)1查询学生姓名、课程号和成绩selectsname,cno,degreefromstudent,scorewherestudent.sno=score.sno;,4.5多表查询,SELECT列名1，列名2，FROM表1，表2，WHERE连接条件,Student(Sno,SName,SSex,Age)Course(Cno,Cname,Ccredit,Tno)Score(Sno,Cno,Degree)2查询学生姓名、课程名称和成绩selectsname,cname,degreefromstudent,score,coursewherestudent.sno=score.snoo=o,4.5多表查询,SELECT列名1，列名2，FROM表1，表2，WHERE连接条件,Course(Cno,Cname,Ccredit,Tno)Score(Sno,Cno,Degree)3查询数学分析课程的平均分selectavg(degree)fromscore,o=oame=数学分析,4.5多表查询,SELECT列名1，列名2，FROM表1，表2，WHERE连接条件,Score(Sno,Cno,Degree)4查询比学生2002010401的6-166号课程成绩高的所有记录selects2.*fromscores1,scores2wheres1.sno=2002010401o=6-166ands2.degrees1.degree,4.5多表查询,SELECT列名1，列名2，FROM表1，表2，WHERE连接条件也可以这样表示SELECT列名1，列名2，FROM表1join_type表2on连接条件Join_type有：(1)Innerjoin，简称join,即内连接(2)Leftjoin，左外连接(3)rightjoin,右外连接(4)fulljoin,全连接(5)crossjoin，交叉连接,4.5多表查询,SELECT列名1，列名2，FROM表1join_type表2on连接条件,Student(Sno,SName,SSex,Age)Course(Cno,Cname,Ccredit,Tno)Score(Sno,Cno,Degree)5查询学生姓名、课程名称和成绩selectsname,cname,degreefromstudentjoinscoreonstudent.sno=o=o,4.5多表查询,SELECT列名1，列名2，FROM表1join_type表2on连接条件,Student(Sno,SName,SSex,Age)Course(Cno,Cname,Ccredit,Tno)Score(Sno,Cno,Degree)6查询学生姓名、课程名称和成绩,包含没有选课的学生以及没有被选的课程情况。selectsname,cname,degreefromstudentleftjoinscoreonstudent.sno=score.snoo=o,4.5多表查询,子查询是一个SELECT查询，它可嵌套在SELECT、INSERT、UPDATE、DELETE语句或其他子查询中。子查询最常用于SQL命令的WHERE子句和FROM子句中。子查询可以使用几个简单命令构造功能强大的复合命令。子查询称为内层查询，对应的称之为外层查询常见的三类子查询：带比较运算符(=,=,(selectdegreefromscorewheresno=2002010401andcno=6-166);,Student(Sno,SName,SSex,Age)Course(Cno,Cname,Ccredit,Tno)Score(Sno,Cno,Degree)4查询选修某课程的学生人数多于30人的教师姓名,4.6子查询,selecttnamefromcoursec,teachertwherec.tno=t.tnoandcnoin(selectcnofromscoregroupbycnohavingcount(*)=30),另外一种分类方法：内层查询是否需要外层查询的参数？内外相关子查询：外层查询的字段的值做为参数传入内层查询，内层查询要执行多次。内外不相关子查询：反之。,Score(Sno,Cno,Degree)5查询成绩比该课程平均成绩低的学生成绩表,4.6子查询,select*fromscoreawheredegree(selectavg(degree)o=o),EXISTS子查询在子查询中，还可以使用EXISTS，它一般用在WHERE子句中，其后紧跟一个SQL子查询，从而构成一个条件，当该子查询至少存在一个返回值时，这个条件为真（.T.），否则为假（.F.）。,Teacher(Tno,TName,TSex,TAge,Prof,Dept)Course(Cno,Cname,Tno)6查询所有有课教师的信息,4.6子查询,select*fromteacherawhereexists(select*fromcoursebwherea.tno=b.tno),Teacher(Tno,TName,TSex,TAge,Prof,Dept)Course(Cno,Cname,Tno)7查询所有没课教师的信息,4.6子查询,select*fromteacherawherenotexists(select*fromcoursebwherea.tno=b.tno),查询没有选课的学生？查询没有被选的课程？,Student(Sno,SName,SSex,Age)Score(Sno,Cno,Degree)8查询选修6-166号课程的学生姓名,4.6子查询,selectsnamefromstudentwhereexists(selectsnofromscorescwheres.sno=sc.snoandcno=6-166),查询没有选修6-166号课程的学生姓名？,Student(Sno,SName,SSex,Age)Course(Cno,Cname,Tno)Score(Sno,Cno,Degree)9查询选修了全部课程的学生姓名,4.6子查询,selectsnamefromstudentswherenotexists(select*fromcoursecwherenotexists(select*fromscorewheresc.sno=s.snoo=o);,换种说法：查找这样的学生：不存在一门课他不选,使用into子句创建新表,4.7INTO子句,Student(Sno,SName,SSex,Age)Course(Cno,Cname,Ccredit,Tno)Score(Sno,Cno,Degree)1查询学生姓名、课程名称和成绩,创建并保存在scd数据表中selects