第7章 SELECT 数据查询

第7章SELECT数据查询7.1数据查询（P144）SELECT语句的语法格式为：SELECT[*|DISTINCT]列名表达式序列FROM表名1[,表名2]…[WHERE条件表达式][GROUPBY分组列名[HAVING分组条件表达式]][ORDERBY排序列名[ASC|DESC]];单表查询1.查询表中的一个或者多个指定的列2.查询表中的所有列3.查询需要计算的列4.查询符合条件的元组（行）5.ORDERBY子句6.汇总数据和数据分组（行）例1：显示学生表的所有信息SELECT*FROM学生表例2：显示学生姓名、性别SELECT姓名,性别FROM学生表Select子句（P145）：查询：•指定列•全部列•前部分记录•经过计算的列selecttop3*from学生表♣例3：查询显示学生表的前3条记录。selecttop60percent*from成绩表♣例4：查询显示成绩表的前60%条记录。♫例6：查询显示学生的岁数select姓名,year(GETDATE())-YEAR(出生日期)as岁数from学生表♫例5：显示学生姓名、出生年份select姓名,出生年份=YEAR(出生日期)from学生表使用聚合函数P148例1：显示01002班的学生人数。selectcount(*)from学生表where班号='01002'例2:显示’C1’号课程的最高分。selectmax(成绩)from成绩表where课号='C1'例4：查询有考试成绩的学生人数。selectCOUNT(distinct学号)from成绩表where成绩isnotnull例3：查询学号为010102的学生的总成绩和平均成绩。selectSUM(成绩)as总分,AVG(成绩)as平均分from成绩表where学号='010102'例1：查询C2课程不及格的学生Select学号,成绩from成绩表where课程号=‘c2’and成绩607.1.4Where子句（P151）：其中使用：•比较运算符•逻辑运算符•范围运算符•字符匹配符like•列运算符例2：查询所有成绩在60至80分之间的学生select*from成绩表where成绩between60and80例3：查询所有姓王的同学Select*from学生表where姓名like'王%'例4：查询07031和01002班学生信息。Select*from学生表where班号in('07031','01002')例5：查询不是07031和01002班的学生Selectdistinct学号from学生表where班号notin('07031','01002')例1：显示各学生的学号及平均分。select学号,avg(成绩)from成绩表groupby学号例2：显示各门课程的课程号及该课程平均分。select班号,avg(成绩)as平均分from成绩表groupby班号7.1.5分组(group)与排序(order)（P155）例3：统计各班的男、女生人数。select班号,性别,COUNT(*)from学生表groupby班号,性别例4：显示所考成绩最低分大于70，最高分小于90的学生学号。select学号,min(成绩),max(成绩)from成绩表groupby学号havingmin(成绩)70andmax(成绩)90例6：以课程号升序、分数降序显示成绩表记录。select*from成绩orderby课程号ASC,成绩DESC例5：显示学生信息，以年龄升序（出生日期降序）排序。select*from学生表orderby出生日期DESC课堂练习：1、查询显示刘思思和陈成的信息。2、查询姓名的第二个字是‘晨’字的学生信息。3、显示学生的班号、姓名、出生日期，要求先按班级，再按出生日期降序排列。4、统计年龄大于20岁的学生共有几人。5、统计各门课程平均成绩，并按平均成绩降序排列。6、统计显示有两门以上成绩大于70分的学生学号。7.2多表操作--连接查询（P159）1.交叉连接2.内连接3.外连接4.自连接5.复合条件连接1.交叉连接,也即笛卡尔连接。7.2.2内连接(P160)即将存在于两个数据集合（表）中的元组，按连接条件进行连接。连接条件的一般语法格式为：[表名1.]列名1比较运算符[表名2.]列名2当比较运算符是等号时，称为等值连接，否则为非等值连接。例1：查询显示所有学生的姓名、课程号、分数。先看：例1：查询显示所有学生的姓名、课程号、成绩。select姓名,课程号,成绩from成绩表A,学生表BwhereA.学号=B.学号或：select姓名,课程号,成绩from成绩表innerjoin学生表on成绩表.学号=学生表.学号等值连接内连接例2、查询显示王国美的各门课程成绩，要求显示姓名、课程名、成绩。select姓名,课程号,成绩from学生表A,成绩表B,课程表CwhereA.学号=B.学号andB.课程号=C.课程号and姓名='王国美'例3：查询显示学生的成绩等级。”等级表”创建如下：createtable等级表(下限int,上限int,等级char(2))goinsertinto等级表values(90,100,'A')insertinto等级表values(80,89,'B')insertinto等级表values(70,79,'C')insertinto等级表values(60,69,'D')insertinto等级表values(0,59,'E')select学号,课程,等级from成绩表,等级表where成绩between下限and上限orderby等级select学号,课程号,等级from成绩表innerjoin等级表on成绩between下限and上限orderby等级非等值连接例3：查询显示学生的成绩等级课堂练习：1、查询显示学生姓名及其所在系名.2、查询显示课程名，及每门课程的平均分.select课程名,AVG(成绩)from课程表A,成绩表BwhereA.课程号=B.课程号groupby课程名select姓名,系名from学生表A,系部表whereA.系号=B.系号3、查询显示陈成的数学成绩，要求显示姓名、课程名、成绩.4、查询成绩最高的5个学生的姓名、课程名、成绩.selecttop5姓名,课程名,成绩from学生表A,成绩表B,课程表CwhereA.学号=B.学号andB.课程号=C.课程号orderby成绩descselect姓名,课程名,成绩from学生表A,成绩表B,课程表CwhereA.学号=B.学号andB.课程号=C.课程号and姓名=‘陈成’and课程名='数学'7.2.3.外连接(P161)外连接可以返回连接左侧或右侧的数据集中的非匹配元组。例：查询显示所有学生的学习情况。学生表：学号课程号分数1013-10590.01016-16685.01033-10592.01033-24586.01053-10588.01053-24575.01073-10593.01076-16678.01083-10578.01086-16680.0学号姓名性别民族101李军男彝103陆君男白105匡明男汉107王丽女傣108曾华男汉109王芳女汉111周方女汉成绩表：select学生表.学号,姓名,课程号,成绩from学生表leftouterjoin成绩表on学生表.学号=成绩表.学号课堂练习：1、列出课程名（无论是否被选修）、选修学生学号、成绩。select课程名,avg(成绩)from课程表leftjoin成绩表on课程表.课程号=成绩表.课程号groupby课程名2、列出所有课程（无论是否被选）的平均分。select课程名,学号,成绩from课程表leftouterjoin成绩表on课程表.课程号=成绩表.课程号3、查询选修了“大学计算机”课程的学生学号和姓名。4、统计每个班级的的学生人数,要求显示班级编号，班级名称和班级人数。5、检索教技8班的学生选课情况，要求显示学号、姓名、课程号、课程名。6、检索计算机科学系的学生信息。7、查询教师所任课程情况，要求显示教师叫号，教师名，课程名。7.2.4自连接(P163)自连接则是对同一个表进行的连接操作。注意：必须给这个表两个不相同的别名。例：查询同时选修了C1和C4课程的学生学号。selectX.课程号,X.学号,X.成绩from成绩表X,成绩表Ywherex.课程号=y.课程号andx.课程号='C3'andx.成绩y.成绩andy.学号='010102'例：查询选修了”C3”课程的并且成绩高于”010102”号学生成绩的所有学生。练习：查询与刘思思同班的同学.selectA.姓名,A.班号from学生表A,学生表BwhereA.班号=B.班号andB.姓名='刘思思'7.3子查询（P165）1.简单子查询(不相关子查询)2.相关子查询子查询返回单个值子查询作为列值使用子查询作为表使用例1：查询所有年龄大于平均年龄的学生姓名。子查询返回单个值的：例2：查询与刘晨在同一个系的学生。Select姓名from学生表Whereyear(getdate())-year(出生日期)(selectavg(year(getdate())-year(出生日期))from学生表)select学号,姓名,出生日期from学生表whereyear(出生日期)=(selectyear(出生日期)from学生表where学号='000101')练习：显示和学号为000101的学生同年出生的所有学生的学号、姓名、出生日期。子查询结果为单个值子查询返回一列值的：需要使用in、notin、any、all操作符。例1：查询没有选修数学的学生学号、姓名。或是：select学号,姓名from学生表where学号notin(select学号from课程表A,成绩表BwhereA.课程号=B.课程号and课程名='数学')子查询结果为值列表练习1、显示存在90分以上成绩的课程号、课程名。练习2、显示选修了C2课程的学生学号、姓名（用子查询方式）select学号,姓名from学生表where学号in(select学号from成绩表where课程号='C2')selectdistinct课程号,课程名from课程表where课程号in(select课程号from成绩表where成绩=90)练习：1、列出成绩表中没有成绩的学生学号、姓名。2、列出选修了操作系统的学生学号和姓名。select学号,姓名from学生表where学号notin(select学号from成绩表)select学号,姓名from学生表where学号in(select学号from成绩表A,课程表BwhereA.课程号=B.课程号and课程名='操作系统')selectA.学号,姓名from学生表innerjoin(select*from成绩表where课程号='C2')asyon学生表.学号=y.学号子查询返回一个表的：例：查询显示所有选修了课程号为C2课程的学生学号、姓名等效于：select学号,姓名from学生表where学号in(select学号from成绩表where课程号='C2')子查询结果作为表使用。2.相关子查询(P167)对于外部查询中的每一个元组都需要执行一次子查询，子查询的条件依赖于外部查询。不相关子查询：select姓名from学生表where学号in(select学号from成绩表where课程号='C1')例1：查询选修了C1号课程的学生学号和姓名。相关子查询：select姓名from学生表Awhereexists(select*from成绩表where学号=A.学号and课程号='C1')例2、查询选修了全部课程的学生姓名select姓名from学生表wherenotexists(sele