第二章SQL语言PPT课件

第2讲Transact SQL语言 2 1Transact SQL概述 SQL语言 StructuredQueryLanguage 结构化查询言 是目前使用最为广泛的关系数据库查询语言 70年代 由IBM公司开发出来的 1976年开始在商品化关系数据库系统中应用 1986 ANSI确认SQL为关系数据库语言的美国标准 1987年被ISO采纳为国际标准 称为SQL 86 1989 ANSI发布了SQL 89标准 后来被ISO采纳为国际标准 1992 ANSI ISO发布了SQL 92标准 习惯称为SQL2 1999年 ANSI ISO发布了SQL 99标准 习惯称为SQL3 2003 ANSI ISO共同推出了SQL2003标准 Transact SQL语言主要包括 1 数据定义语言DDL 用来定义和管理数据库中的对象 DDL主要的语句有 CREATTABLE 创建表 ALTERTABLE 修改表的属性 DROPTABLE 删除表 2 数据操作语言DML 用来操作数据库中的对象和数据 是T SQL中最常用的部分 DML主要的语句有 SELECT 从一个表或多个表中检索数据 DELETE 从表中删除数据 INSERT 向一个表中添加数据 UPDATE 修改表中已有的数据 3 数据控制语言DCL 用来控制用户对数据库对象操作的权限 主要的命令有 GRANT 授予权限 REMOVE 回收所授予的权限 2 2查询与统计数据 将XK数据库切换为当前数据库在查询窗口中执行如下语句 usexkgo 使用SELECT子句SELECT子句主要用于数据查询 其基本格式为 SELECT选择列表FROM表的列表WHEAR检索的条件 问题 从Student表中检索学号为00000001的学生的姓名 StuName 要求显示学生的学号和姓名在查询分析器中运行如下命令 usexkgoselectStuNo StunamefromStudentwherestuno 00000001 go 使用SELECT实现查询与统计 SELECT语句的各种使用方法 1 的使用 在选择列表中使用 则从from子句中指定的表或视图中检索并返回所有列 例 从课程表中检索所有信息 usexkgoselect fromcoursego 例 从学生表中检索学生所在班的班级编码USEXkGOSELECTClassNoFROMStudentGO 2 使用DISTINCT消除重复值 例 从学生表中检索学生所在班的班级编码 要求清除值相同的那些行 USEXkGOSELECTDistinctClassNoFromStudentGO 3 使用TOPn PERCENT 例 从学生表中检索所有的信息 要求只显示前6行数据 USEXkGOSELECTTOP6PERCENT FROMStudentGO 例 从学生表中检索所有的信息 要求只显示查询结果的前6 行 USEXkGOSELECTTOP6PERCENT FROMStudentGO 4 修改检索结果中列的标题在检索结果中 可以看到显示结果的列标题就是表的列名字 是否可以将显示结果的列标题修改为直观易懂的汉字信息呢 问题 检索课程表中的课程编号 课程名称 教师 上课时间限制选课人数和报名人数信息 并修改列标题方法有三 1 将要显示的列标题用单引号括起来后接等号 后接要检索的列名 即 改变后的列标题 从表中要查询的列名 2 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用空格隔开 即从表中要查询的列名 改变后的列标题 3 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用AS关键字 即从表中要查询的列名AS 改变后的列标题 1 将要显示的列标题用单引号括起来后接等号 后接要检索的列名USEXkGOSELECT 课程编码 CouNo 课程名称 CouName 教师 Teacher 上课时间 SchoolTime 限制选课人数 LimitNum 报名人数 WIllNumFROMCourseGO观察运行结果的列标题已修改为汉字 2 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用空格隔开USEXkGOSELECTCouNo 课程编码 CouName 课程名称 Teacher 教师 SchoolTime 上课时间 LimitNum 限制选课人数 WIllNum 报名人数 FROMCourseGO 3 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用AS关键字USEXkGOSELECTCouNoAS 课程编码 CouNameAS 课程名称 TeacherAS 教师 SchoolTimeAS 上课时间 LimitNumAS 限制选课人数 WIllNumAS 报名人数 FROMCourseGO 3 使用WHERE限制检索的条件WHERE子句限制查询的范围 可以定义一个或多个条件 WHERE子句中 可以包含比较运算符和逻辑运算符 例 检索课程编码为001的课程名称 要求只显示课程名称usexkgoselect 课程名称 counamefromcoursewherecouno 001 go 练习 检索上 Linux操作系统 课程的教师名USEXkGOSELECT 教师 TeacherFROMCourseWHERECouName Linux操作系统 GO 6 在查询结果中显示字符串 问题 检索课程表的信息 要求给出检索结果为 课程名称课程编码SQLServer实用技术课程编码为 001 课程编码为 课程编码为 方法 将要增加的字符串用单引号括起来 然后和列的名字写在一起 中间用逗号分隔开 USEXkGOSELECT 课程名称 CouName 课程编码为 课程编码 CouNoFROMCourseGO 7 表达式作为SELECT语句中的查询列SELECT子句中的选项列表可以为表达式或所指定的列的列表 表达式可以是列名 函数或常数的列表 例 要求检索课程表的课程信息 并显示报名人数和限制选课人数之比UseXkGOSELECT WillNum LimitNumFROMCourseGO思考 如何将上面的检索结果按报名人数和限制选课人数之比的升序排列 USEXkGOSELECT WillNum LimitNum 报名人数与限选人数之比 FROMCourseORDERBY报名人数与限选人数之比GO 8 使用ORDERBY子句重新排列检索结果可以使用orderby子句对检索结果重新进行排序 方法是使用关键字ASC 升序 或DESC 降序 系统默认为是升序 也可以在ORDERBY子句中指定多个列 例 检索课程表的教师名 课程号 课程名 要求检索结果首先按教师名降序排列 教师名相同时 则按课程号的升序排列USEXkGOSELECTTeacherAS 教师名 CouNoAS 课程号 CouNameAS 课程名 FROMCourseORDERBYTeacherDESC CouNoGO 检索开设了 信息技术 类课程的教师名及其所开的课程名 要求先按教师名升序排列 教师名相同时 按课程名的降序排列 并将显示的列名改为中文 usexkgoselectteacheras 教师名 counameas 课程名称 fromcoursewherekind 信息技术 orderbyteacher counamedescgo 9 使用IN NOTIN 关键字 例 检索课程编码为 001 004 013 的课程名称方法一 使用逻辑运算符ORUSEXkGOSELECTCouNameAS 课程名称 FROMCourseWHERECouNo 004 ORCouNo 007 ORCouNo 013 GO方法二 使用IN关键字 比使用逻辑运算符更为简单 USEXkGOSELECTCouNoAS 课程号 CouNameAS 课程名称 FROMCourseWHERECouNoNOTIN 004 007 013 GO 1 使用LIKE NOTLIKE 关键字是一个匹配运算符 它与字符串表达式相匹配 字符串表达式被定义为字符串和通配符 SQL的四个通配符为 通配符 百分号 匹配包括0个或多个字符的字符串 下划线 匹配任何单个字符 排列通配符 匹配任何在范围或集合中的单个字符 例如 m p 不在范围之内的字符 匹配任何不在范围内的单个字符 例如 m p mnop 通配符和字符串必须括在单引号中 练习 解释下面的通配符表示的意义 D a 如果要查找通配符本身 需要将它们用方括号括起来 D 问题 检索以 制作 两字结尾的课程名 使用LIKE USEXkGOSELECT FROMCourseWHERECouNameLIKE 制作 GO 练习 检索姓名的第二个字为 宝 的学生信息 要求显示学生学号和姓名USEXkGOSELECT FROMStudentWHEREStuNameLIKE 宝 问题 检索不姓 刘 的学生信息 使用NOTLIKE USEXkGOSELECT FROMStudentWHEREStuNameLIKE 刘 GO 2 使用ISNULL关键字 用于检索列中没有赋值的行 问题 检索课程表中教师未定的课程名称和教师名USEXkGOSELECT 课程名称 CouName 教师 TeacherFROMCourseWHERETeacherISNULLGO 3 使用BETWEEN AND NOTBETWEEN AND 关键字 用于检索在某一特定范围内的信息 问题 检索报名人数大于等于30并且小于等于40的课程信息 要求显示课程名称和报名人数 使用BETWEEN AND 注意 WHEREWillNumBETWEEN30AND40包括30和40这两个值也可以在WHERE字句中使用前面讲过的逻辑运算符检索某一范围内的信息 练习 检索报名人数是限制选课人数一倍以及小于两倍的课程信息 要求显示课程名称 报名人数和限制选课人数之比 并按该比例的降续排列usexkgoselectcouname 课程名称 willnum limitnum 报名人数和限制选课人数之比 fromcoursewherewillnumbetweenlimitnumandlimitnum 2orderby报名人数和限制选课人数之比descgo 4 使用UNION子句 用于将两个或多个检索结果合并成一个结果使用时注意 1 所有检索中的列数和列的顺序必须相同 2 所有检索中按顺序对应列的数据类型必须兼容 相同或者可以明确地转换 问题 从班级表中检索班级名 从系部表中检索系部名 并将这两个检索结果合并起来 USEXkGOSELECTClassNameFROMClassUNIONSELECTDepartNameFROMDepartmentGO 扩展 1 修改列标题USEXkGOSELECTClassNameAS 所有班名和所有系名 FROMClassUNIONSELECTDepartNameFROMDepartmentGO 2 按新的列标题降序排序 USEXkGOSELECTClassNameAS 所有班名和所有系名 FROMClassUNIONSELECTDepartNameFROMDepartmentORDERBY所有班名和所有系名DESCGO 5 使用COMPUTE子句 用来计算总计或进行分组小计 问题 检索课程表中课程分类为 信息技术 的课程编码 课程名称 课程分类 报名人数 并计算平均报名人数 说明 总计值或小计值作为附加新行出现在检索结果中 该子句用在WHERE子句之后 计算平均报名人数则需要使用求平均值的聚合函数 AVG 聚合函数也可以出现在SELECT子句中 USEXkGOSELECTCouNoAS 课程编码 CouNameAS 课程名称 KindAS 课程分类 WillNumAS 报名人数 FROMCourseWHEREKind 信息技术 COMPUTEAVG WillNum GO 6 使用COMPUTE BY子句使用COMPUTEBY子句对BY后面给出的列进行分组显示 并计算该列的分组小计 使用COMPUTEBY子句必须使用ORDERBY对COMPUTEBY中BY指定的列进行排序 例 按类检索课程表中的课程编码 课程名称 课程分类 报名人数 并计算每类课程的平均报名人数 注意 必须使用ORDERBY排序要分组的列 USEXkGOSELECTCouNoAS 课程编码 CouNameAS 课程名称 KindAS 课程分类 WillNumAS 报名人数 FROMCourseORDERBYKindCOMPUTEAVG WillNum BYKindGO 练习 检索周二上课的课程名称和教师名 并对符合条件的课程门数进行统计 提示 先完成前一部分 再使用聚合函数COUNT进行统计 usexkgoselectteacher counamefromcoursewhereschooltime 周二5 6节 orschooltime 周二晚 computecount couname go 问题COMPUTE子句是在一个明细行的基础上增加一个总计或小计 有BY时 但如果只想统计每类课程的平均报名人数 该如何处理 1 使用GROUPBY子句分组汇总查询结果 问题 按课程分类统计每类课程的平均报名人数USEXkGOSELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseGROUPBYKindGO 与上面例子的区别 只有一个结果集 说明 1 该子句用于将检索结果按照GROUPBY后指定的列进行分组 该子句写在WHERE子句后面2 GROUPBY子句经常用于SELECT子句中包含有聚合函数的情况 此时 SELECT子句中选项列表中出现的列 只能是GROUPBY子句中的列或者包含在聚合函数中 2 使用HAVING子句 问题 上面的例子中 如果只统计 信息技术 类课程的平均报名人数uSEXkGOSELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseGROUPBYKindHAVINGKind 信息技术 GO 说明 HAVING经常用在GROUPBY子句之后 用于限定结果集中的分组 也可以使用WHERE子句USEXkGOSELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseWHEREKind 信息技术 GROUPBYKindGO可以看到 HAVING子句是对结果进行过滤 而WHERE是对原始记录进行过滤 注意 HAVING子句中的列只能是GROUPBY子句中或者聚合函数中的列 问题 检索平均报名人数大于30人的课程类和每类平均报名人数USEXkGOSELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseGROUPBYKindHAVINGAVG WillNum 30GO 练习 在课程表中 按所开设课程的系部编码分别统计总共报名人数usexkgoselectdepartno 系部编号 sum willnum 总共报名人数 fromcoursegroupbydepartnoGo如果问 系部编码为01的系所开设的课程总共有多少学生报名 如何编程实现 usexkGoselectdepartno 系部编号 sum willnum 总共报名人数 fromcoursegroupbydepartnohavingdepartno 01 go 3 使用子查询 在一个查询中包含另一个查询 问题 检索报名人数大于平均报名人数的课程编码 课程名称和报名人数STEP1 如何求平均报名人数STEP2 完成题目要求uSEXkGOSELECT 课程编码 CouNo 课程名称 CouName 报名人数 WillNumFROMCourseWHEREWillNum SELECTAVG WillNum FROMCourse GO 第2节多表查询如果问 计算机应用工程系 下属的班级有哪些 应该查询哪些表格 这就是多表查询了 如果在多个表中进行查询 结果会怎样 例如 class表中有18行数据 student表中有180行数据 select语句如下 USEXkGOSELECT FROMClass studentGO 结果集中 有3240行 18 180 7列 3 4 问题 笛卡儿积 运算过程 从class表中每取出一行数据 就和student表中180行中的每一行进行组 合 形成结果集的180个数据行 总共从class表中取出18行 因此检索结果集为18 180行 3240行 这就是笛卡儿积 在笛卡儿积的结果中 分析存在的问题以及解决方法 1 两列classNo的列值不同解决方法 加连接条件 成为相等连接 注 为了避免笛卡儿积 必须在WHERE子句中给出表格的连接条件 通常对于N个表格的检索 要有N 1个连接条件 2 出现了相同的列 解决方法 去掉相同的列 成为自然连接 3 加入 计算机应用工程系 的条件 成为带有选择条件的连接 问题 检索 01电子商务 班的学生选修课程的情况 要求显示班级名称 学生学号 学生姓名 课程名称和上课时间 分析 班级名称 学生学号 学生姓名 课程名称 上课时间这些信息是分别在班级表 学生表 学生选课表和课程表中的 因此需要在多个表中进行查询 同时 必须在WHERE子句中给出连接条件 USEXkGOSELECTClassNameAS 班级 StuNoAS 学号 StuNameAS 姓名 CouNameAS 课程名 KindAS 课程分类 CreditAS 学分 TeacherAS 教师 SchoolTimeAS 上课时间 WillNumAS 报名人数 FROMClass Student Course StuCouWHEREClass ClassNo Student ClassNoANDCourse CouNo StuCou CouNoANDStudent StuNo StuCou StuNoANDClassName 01电子商务 GO运行结果出现出错信息 在StuNo列前增加表前缀Student 后的SELECT语句为 USEXkGOSELECTClassNameAS 班级 Student StuNoAS 学号 StuNameAS 姓名 CouNameAS 课程名 KindAS 课程分类 CreditAS 学分 TeacherAS 教师 SchoolTimeAS 上课时间 WillNumAS 报名人数 FROMClass Student Course StuCouWHEREClass ClassNo Student ClassNoANDCourse CouNo StuCou CouNoANDStudent StuNo StuCou StuNoANDClassName 01电子商务 GO 注意 在引用的多表中 如果列名在多个表中同名 为了避免列名不明确 在SELECT子句中必须在列名前加上表的前缀 即 表名 列名 练习 检索 林斌 同学的选修课信息 课程编号 课程名称 上课时间 usexkgoselectcourse couno couname schooltimefromcourse stucou studentwherestudent stuno stucou stunoandcourse couno stucou counoandstuname 林斌 go 练习 查询每个系所开课程的门数 显示系代码 系名称 课程名称 并计算每个系的开课门数 usexkgoselectcourse departno departname counamefromdepartment coursewheredepartment departno course departnoorderbycourse departnocomputecount couname bycourse departnogo 练习 显示所有系的班级名称 要求计算各系班级数量 显示要求如下 1 非相等连接 比较连接 连接条件不是等号 而是比较运算符 问题 检索各班可以选择的不是本系开设的选修课程的信息 课本的例子 USEXkGOSELECTClassNameAS 班级 CouNameAS 课程名 KindAS 课程分类 CreditAS 学分 TeacherAS 教师 SchoolTimeAS 上课时间 WillNumAS 报名人数 FROMClass CourseWHERECourse DepartNoClass DepartNoGO 2 自连接 自己和自己的连接 问题 检索课程类相同而系部编码不同的课程信息 意味着同一类的课程有多个系部开设 要求显示课程编码 课程名称 课程分类和系部编码 课本的例子 USEXkGOSELECTDISTINCTt1 CouNo t1 CouName t1 KindAS 课程分类 t1 DepartNoAS 系部编码 FROMCourset1 Courset2WHEREt1 Kind t2 KindANDt1 DepartNot2 DepartNOGO 外连接 上面介绍的都是内连接 以下是外连接 问题 检索学生的选课信息 要求显示学生学号 课程编码 课程名称1 左外连接2 右外连接3 全连接4 交叉连接 使用EXISTS关键字 如果要解决这样的问题 问题 检索已经报名选修了课程的学生的学号和姓名 分析 采用上面所学的方法能否解决 使用EXISTS关键字 测试跟随的子查询的行是否存在 要点 一旦找到 立即停止找到则返回TRUE 否则返回FALSE 而不是返回一些行 USEXkGOSELECTStuNo StuNameFROMStudentWHEREEXISTS SELECTStuNoFROMStuCouWHEREStuNo Student StuNO GO 使用NOTEXISTS 问题 查询没有选修 001 课程的学生学号和姓名 selectstuno stunamefromstudentwherenotexists select fromstucouwherestuno student stunoandcouno 001 向表中输入数据 1 使用INSERT语句向表中输入数据的方法有两种 使用INSERT语句和使用企业管理器Insert语句的简单语法 Insert into table name column list values default null expression n execute statement 注意 列的个数必须与values子句中给出的值得个数相同 数据类型必须和列的数据类型相对应 向表中输入数据 例 向department表中输入3行数据 USEXkGOINSERTDepartmentVALUES 11 数学系 INSERTDepartment DepartName DepartNo VALUES 物理系 12 INSERTDepartment DepartNo DepartName VALUES 13 化学系 GOSELECT FROMDepartmentGO 2 使用企业管理器输入数据3 使用select子句插入数据语法格式如下 Inserttable nameSelectcolumn listfromtable listwheresearch conditions例2 45使用SELECT子句将Newdepartment表中的数据插入到department表中 1 先创建一个新的表 表名为 newdepartmentUSEXkGOCreateTableNewDepartment Nochar 2 notnull Namechar 30 notnull GO 2 使用insert语句向newdepartment表输入两行数据 INSERTNewDepartmentVALUES 16 历史系 INSERTNewDepartmentVALUES 17 生物系 GO 3 使用