练习6复杂的结构化查询语句。
6．1上机目的。
1、掌握select语句的统计函数的作用和使用方法。
2、通过练习select语句的group by和order by字句的用法，理解其作用，掌握语句的写法。
3、通过练习涉及多张表的连接查询，掌握它的作用和写法。
6．2上机练习预备知识点。
6．2．1汇总函数。
函数语法说明。
sum（[all|distinct]表达式）**g（[all|distinct]表达式）count（[all|distinct]表达式）max（表达式）min（表达式）
功能。返回表达式的所有值的和返回表达式所有值的平均值返回表达式中值的个数表达式的最大值表达式的最小值。
说明：函数中distinct的作用是统计计算的过程中去掉重复值。②函数中all的作用是统计计算全部的值包括重复值。可省略。
6．2．2 group by子句。
作用：将记录根据groupby后所跟字段的值分成多个组，进行分组计算。一般情况groupby子句与汇总函数连用。
格式：group by（字段，n）
例14按照班级把学生信息表的数据分组，并且统计每个班级的人数。
分析：students表中班级人数这一列，因此需要计算才能得到。因此需要先按class分组，class列中有几个不同的值就要分成几组，再按各组进行统计计算。
select class,人数= count(*)from studentsgroup by class注意：
分组也可以根据多个字段；
不能对数据类型为ntext,text,image或bit的字段使用group by1、h**ing子句作用：h**ing子句将对groupby子句选择出来的结果进行再次筛选，最后输出符合h**ing子句条件的结果。h**ing子句必须与group by子句连用。
例15查询平均入学总分在350分以上的班级。
分析：students表中没有平均分这一列，因此需要计算才能得到。因此需要先按class分组，class列中有几个不同的值就要分成几组，再进行统计计算，最后用h**ing子句筛选出**g(mgrade) >350的记录。
select class,**g(mgrade)from studentsgroup by class
h**ing **g(mgrade)>=3502、all关键字。
作用：暂时忽略where子句中的查询条件。如果使用了all关键字那么查询结果将包括由group by子句所产生的所有组，无论这些组中的记录是否符合where子句中的查循条件。
但是对于不符合where子句中的查循条件的记录值不进行汇总计算。例16 select class,平均成绩=**g(mgrade)
from students
where class <>03物流3’group by all class
分析：使用了all关键字后，会出现表中所有班级的分组情况，但”03物流3班”的平均成绩为空，不再计算。3、cube关键字。
with cube关键字的主要作用是自动对group by子句中列出的每个字段都进行分组汇总运算。（注意有cube关键字和没有cube关键字的区别）例17 select class,ssex,平均分=**g(mgrade)from students
group by class,ssex with cube分析：在该例子的检索结果集不但包含按班级和性别的分组计算结果，而且包含按班级分组后的计算结果和按性别分组计算的结果，最后还包含整张表不分组的计算结果。说明：
正确理解where子句、group by子句和h**ing子句的作用及其作用顺序。①where子句用来筛选from子句中指定的数据源的记录②group by子句将where子句的结果集进行分组。
h**ing子句将从经过分组后的中间结果集中筛选记录。
6．2．3 order by子句。
作用：在select子句用orderby子句是对查询结果按照一列或多列进行排序[asc｜desc]。asc表示升序排列，desc表示降序排列。
默认情况下为升序排列。例18检索学生信息表，并且按照入学总分由高到低输出查询结果。
分析：入学总分由高到低排列，就表示检索结果要求按入学总分作降序排列，那么关键字为desc。降序排列不是默认的排列方式，需要特别表示。
select sno,sname,mgradefrom students
order by mgrade desc注意：
order by子句中可以指定多个字段，系统将根据子句中排序字段的顺序对查询结果进行嵌套排序。
order by子句中不能包含text,image的字段。
order by列表中不允许使用子查询或常量表达式。但可以在选择字段列表中为聚合表达指定一个名称，然后在order by子句引用这个名称。
6．2．4联合查询（使用union）
作用：联合运算符union可以将两个或着两个以上的查询的结果合并成一个结果集合显示。语法格式：
查询1union [all] all：显示所有记录（即使出现重复的记录）查询2
说明：查询的结果的列标题为第一个查询的列标题②还可以将不同数据库中表的查询结果联合起来。
例19将jxgl数据库中的students表和教学管理数据库中的学生表中的记录一起显示出来。select *
from studentsunion allselect *
from教学管理。dbo.学生注意：
参加union运算的结果集必须具有相同的结构，字段数目相同，数据类型兼容。②使用union运算符时，单独select语句不能包含orderby子句。只能在最后一个select语句中使用order by子句，对最终的组合结果集产生作用。
6．2．5用联接进行多表查询。
根据各个数据表之间的逻辑关系从两个或多个数据表中检索数据。联接一种关系运算，它是从两个关系的的笛卡儿积中选取属性间满足一定条件的元组。说明：
常用的联接表达式为：([表1.字段1]＝[表2.
字段2])1、内联接inner join
利用join关键字在from子句中指定连接条件。格式：数据表1 inner join数据表2 on联接表达式。
例20查询学号为‘0311101’的学生的学号，姓名，班级，所选修的课程号和成绩。
分析：检索的学号，姓名，班级属于students表，课程号和成绩属于sc表，检索的内容分别属于两张表的情况下，需要先把两张表连接起来，再作检索。首先看这两张表是否可以直接联接，有联接字段？
现在students表和sc表有公共的联接字段sno，那么就可以实现通过两张表的联接进行多表查询。如果两张没有公共的联接字段，无法联接，那么需要找其他的表，看是否可以通过第三张表将两张表联接起来。
select 学号，姓名，班级class,cno,gradefrom students inner join sc on
可以使用from分句和where分句指定数据表之间的连接select students,sc
where and 为了增机select语句的可读性，是select语句中的数据表名更加容易识别，可以使用数据表别名。
select students as a,sc as b
where and 外表联接。
左向外联接left join
格式：数据表1 left join数据表2 on联接表达式说明：使用左向外联接进行查询的结果集包括数据表1中的所有记录，而不仅仅是联接字段所匹配的记录。
例21显示所有学生的学号，姓名，如果有选修课程的显示课程编号和成绩。
分析：要检索学生的学号，姓名和选修课程课程编号和成绩，这些字段涉及到两张表students和sc，因此必须先把两张表联接起来。但是innerjoin只能显示两个表中相互匹配的记录即只显示选修了课程的学生，不显示还没有选修课程的学生。
要达到题目的要求就要选用其他的联接方式：left join，显示出students表中的所有记录也显示出sc表中相关的记录。
select
from students left join sc on =
说明：也可以使用from分句和where分句指定数据表之间的连接(但是为了防止出现混淆，不建议用该方法)
select students ,sc
where *=右向外联接right join
格式：数据表1 right join数据表2 on联接表达式。
同左向外连接相反，使用右向外联接进行查询的结果，将包含数据表2中的所有记录，而不仅仅是连接字段所匹配的记录。
注意：这种联接方式在显示表中记录上正好与left join相反。
说明：也可以使用from分句和where分句指定数据表之间的连接(但是为了防止出现混淆，不建议用该方法)
select students ,sc
where =*完整外联接full join
格式：数据表1 full join数据表2 on联接表达式。
使用完整外联接进行查询的结果将包含两个数据表中的所有记录，当某一条记录在另一条数据表中没有匹配记录时，则另一个数据表的选择列表字段将指定为空值注意：full join是将left join和right join的结果合并起来。④交叉联接cross join
格式：数据表1 cross join数据表2 on联接表达式。
如果select语句中没有使用where子句，那么交叉连接将返回数据表1和数据表2中记录的笛卡儿积。
6．3实验内容。
001统计学生信息表中的学生人数（count()函数）
002统计学生信息表中的班级个数（count()函数）
003统计学生信息表中所有学生“入学成绩”的平均分、最高分、最低分（**g( )max( )min( )
004分别统计每个班级的男女人数（要求显示出班级、性别、对应的人数）
005统计每个系的教师人数（group by和count（）函数）
006统计教师人数超过4人的部门名称和对应的人数（group byh**ing和count函数）
007计算选修了“0000027”的学生的平均成绩（要求显示出课程号和对应的平均成绩）
008查询全体学生情况，查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列（order by）
009统计每个部门老师的平均工资，按照平均工资作降序排列。
010查询选修课程学生的学号、姓名、所选课程号、取得的成绩（inner join）
011查询“03物流2”班同学的所有信息，如果有选修课的话还要对应的求列出选修的课程号以及成绩。
012查询选修了‘实用英语’课且成绩及格的学生的学号、姓名和成绩（复合条件连接）
013查询每个教师所上的课程（包括教师编号，姓名，课程号，课程名称）
014查询“陈红”所学的课程名和对应的成绩，并按照成绩升序排列。
6．3．3补充题。
015查询平均成绩大于70分的课程，要求显示课程号和平均成绩。
016查询选修了“电子商务”课程且成绩没有及格的学生的学号、姓名以及对应的成绩。
017查询第一学期所选课程平均成绩前三名的那些学生的姓名。
6．5课后练习。
1、查询图书信息表中，购买图书的总量和平均**。
2、查询该数据库中各个出版单位出版的图书数量。
3、查询还未还书的读者的姓名及其所在专业。
4、查询姓名为“汪函”的读者的借书信息（借书证号，姓名，图书编号，书名，借书日期）。
5、统计该数据库中读者男女生人数。
6、根据读者的借书情况表，统计每天借出书本的数量（日期，借出书本数量）
7、查询借阅“sql server2000教程”或“数据库设计凡人入门”的读者姓名，专业，借阅时间以及图书的出版单位。（联接）
8、查询在“2006－1－1”到“2006－4－1”之间借书的读者的姓名。
SQL高级查询
高级查询概念。在掌握查询的基础知识后，您就可以探知查询解决方案中使用的下列高级查询概念 在选择列表中使用聚合函数。使用 group by 对多行分组。使用 union 组合结果。子查询基础知识。使用 case 处理条件数据。并行查询。汇总数据 在选择列表中使用聚合函数。聚合函数 例如 sum g c...
SQL高级查询
sql server t sql 1 基本常用查询 3 select 3 all 查询所有 3 distinct 过滤重复 3 count 统计 3 top 取前n条记录 3 alias column name 列重命名 3 alias table name 表重命名 3 column 列运算 3 ...
SQL高级查询语句
交互式sql语句。1.1 创建数据库 unis db 日期 及其基本表 学生 课程 选课 1 建立一个 学生 表student，它由学号sno 姓名sname 性别ssex 年龄sage 所在系sdept五个属性组成，其中学号为主属性，ssex默认为 男 年龄大于0。2 建立 课程 表course，...