使用Transact-SQL语言.ppt

SQL Server 2005数据库及应用,主讲人： 匡芳君,课题内容：使用Transact-SQL语言（4） 教学目的：了解Transact-SQL的用法和作用; 掌握SELECT语句查询表的信息。 教学方法: 讲授、演示 重 点：SELECT语句的灵活应用 难 点： SELECT语句的灵活应用 课堂类型： 实训课 教 具: 投影仪 多媒体设备,第2章 使用Transact-SQL语言,使用Transact-SQL语言,学习目标 了解Transact-SQL的用法和作用 学会并能根据需要灵活使用SELECT语句查询用户表的信息 使用INSERT向用户表输入数据 使用UPDATE修改用户表的数据 使用DELETE语句删除用户表的数据。,5,问1：查询课程表中的课程编号、课程名称、课程类别、报名人数，并计算所有课程的平均报名人数、报名总人数、最多报名人数。 问2：按课程类别查询课程表中的课程编号、课程名称、课程类别、报名人数，并计算每类课程的平均报名人数、报名总人数、最多报名人数。 问3：分组计算每类课程的平均报名人数、报名总人数、最多报名人数。,复习提问,查询信息-单表,17、使用HAVING子句 【问题】上面的例子中，如果只统计“信息技术”类课程的平均报名人数 说明HAVING经常用在GROUP BY子句之后，用于限定结果集中的分组，也可以使用WHERE子句 可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。 注意HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列 【问题】查询平均报名人数大于30人的课程类和每类平均报名人数 【练习】在课程表中，按所开设课程的系部编号分别统计总共报名人数 如果问：系部编号为01的系所开设的课程总共有多少学生报名，如何编程实现？,实例分析 1）分组统计各班的学生人数 select ClassNo, count(StuNo) from Student group by ClassNo 对比分析下面语句： select * from Student order by ClassNo compute count(StuNo) by ClassNo,查询信息-单表,实例分析 2）分组统计每位老师承担的选修课程的门数 select Teacher, count(*) from Course group by Teacher 3）按课程类别分组统计各类课程的平均报名人数 select Kind , AVG(WillNum) from Course group by Kind 添加语句 having Kind=信息技术,实例分析 4） 查询平均报名人数大于25人的课程类别和每类平均报名人数。 select Kind, AVG(WillNum) from Course where AVG(WillNum)25 group by Kind select Kind, AVG(WillNum) from Course group by Kind having AVG(WillNum)25 注：聚合函数可以在SELECT语句和HAVING语句中使用，但是不能在WHERE语句中使用,10,当在一个SQL查询中同时使用WHERE子句，GROUP BY 子句和HAVING子句时，其顺序是WHEREGROUP BY HAVING。 WHERE与HAVING子句的根本区别在于作用对象不同。 WHERE子句作用于基本表或视图，从中选择满足条件的元组； HAVING子句作用于组，选择满足条件的组，必须用于GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。,查询信息-单表,11,查询“信息技术”类课程的平均报名人数 法一： select Kind, AVG(WillNum) from Course where kind=信息技术 group by Kind 法二： select Kind, AVG(WillNum) from Course group by Kind having kind=信息技术,查询信息-单表,查询信息-单表,18、使用子查询(嵌套查询)在一个查询中包含另一个查询 【问题】查询报名人数大于平均报名人数的课程编号、课程名称和报名人数 STEP 1：如何求平均报名人数 STEP 2：完成题目要求,13,在查询中包含另一个查询的查询，即在WHERE子句中又包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。 嵌套查询可以将一系列简单查询构成复杂查询，增强查询能力。以层层嵌套的方式构造查询充分体现了SQL“结构化”的特点。 嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成，父查询要用到子查询的结果。,子查询的返回值往往不止一个，而是一个集合。 【解决】查询实际报名人数大于平均报名人数的课程信息 SELECT * FROM Course WHERE WillNum ( SELECT AVG(WillNum) FROM Course ) 先执行子查询，再执行父查询,查询信息(单表),19、使用UNION子句用于将两个或多个查询结果合并成一个结果 【问题】从班级表中查询班级名，从系部表中查询系部名，并将这两个查询结果合并起来 使用时注意： （1）要合并的查询结果的列个数要相同 （2）要合并的查询结果按顺序对应列的数据类型性同或者兼容 扩展举例 （1）修改列标题（按第一个查询列为准） （2）按新的列标题排序（按第一个查询列为准）,查询信息(单表),实例分析 从班级表中查询班级名，从系部表中查询系部名，并将这两个查询结果合并起来。 select classname from class union select departname from department go,查询信息(单表),实例分析 从班级表中查询班级名，从系部表中查询系部名，并将这两个查询结果合并起来，并按降序排序。 select classname 班级名和系名 from class union select departname from department Order by classname DESC go,查询信息-多表,“计算机应用工程系” 有哪些班级，应该查询哪些表？ 如果在多个表中进行查询，结果会怎样？ USE Xk GO SELECT * FROM Department, Class GO 结果集中，有54行（318），5列（23）。 Select * from class,student Go 结果集中，有3240行（18180），7列（34） 多表查询来自多个用户表或视图的信息，用到用户表的连接 用户表主要用各表属性间的关系来连接,查询信息-多表,问题: 笛卡儿积 运算过程？ 在笛卡儿积的结果中，分析存在的问题以及解决方法： （1）两列DepartNo的列值不同 解决方法：加连接条件，成为相等连接 【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：通常对于N个表格的查询，要有N-1个连接条件 （2）出现了相同的列： 解决方法：去掉相同的列，成为自然连接 （3）加入“计算机应用工程系”的条件，成为带有选择条件的连接,查询信息-多表,【问题】查询学生选修课程的情况，要求显示班级名称、学号、姓名、课程名称和上课时间. 分析 班级名称、学号、姓名、课程名称、上课时间 这些信息是分别在班级表、学生表、学生选课表和课程表中的，因此需要在多个表中进行查询。同时，必须在WHERE子句中给出连接条件。 select classname,student.stuno,stuname, couname,schooltime from class,student,course,stucou where class.classno=student.classno and student.stuno=stucou.stuno and course.couno=stucou.couno go 注意在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，在SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。,查询信息-多表,【例】查询“01电子商务”班学生选修课程的情况，要求显示班级名称、学号、姓名、课程名称和上课时间. 分析 班级名称、学号、姓名、课程名称、上课时间 这些信息是分别在班级表、学生表、学生选课表和课程表中的，因此需要在多个表中进行查询。同时，必须在WHERE子句中给出连接条件。 select classname,student.stuno,stuname, couname,schooltime from class,student,course,stucou where class.classno=student.classno and student.stuno=stucou.stuno and course.couno=stucou.couno and classname=01电子商务 go,查询信息-多表,【练习】查询班级信息，要求显示班级编号、班级名称、班级系部编号、系部名称。 【练习】查询学生信息，显示信息包括学生基本信息和班级名称。 【练习】查询学生选课信息，要求显示