Transact-SQL语言概述.ppt

第2章Transact SQL语言 学习目标 了解T SQL语言的起源 组成熟练使用SELECT INSERT UPDATE DELETE语句 Transact SQL语言概述 问题我们需要对数据进行哪些维护操作 如何对数据进行查询 统计 结构化查询语言 StructuredQueryLanguage 是一种用于存取和查询数据 更新并管理关系数据库系统的数据库查询和编程语言 这种语言的语法结构类似于英语 易学易用 书写随意 ANSISQL92Transact SQL 1 数据定义语言DDL用来定义和管理数据库中的对象 DDL主要的语句有 CREATETABLE 创建表 ALTERTABLE 修改表的属性 DROPTABLE 删除表 Transact SQL语言的组成 2 数据操作语言DML用来操作数据库中的对象和数据 是T SQL中最常用的部分 DML主要的语句有 SELECT 从一个表或多个表中检索数据 DELETE 从表中删除数据 INSERT 向一个表中添加数据 UPDATE 修改表中已有的数据 3 数据控制语言DCL用来控制用户对数据库对象操作的权限 主要的命令有 GRANT 授予权限 REVOKE 回收所授予的权限 第1节操作数据 单表查询 切换数据库USEXkGO SELECT子句SELECT子句主要用于检索数据 其基本格式为 SELECT选择列表 指示应该返回的数据 FROM表的列表 提供数据的表或视图的名称 WHEAR检索的条件 例 从Student表中检索学号为00000001的学生的姓名 StuName 要求显示学生的学号和姓名 UsexkGoSelectStuNo StuNamefromStudentwhereStuNo 00000001 GO 练习 要求检索系部编号为01的班级信息 要求显示班级编号和班级名称 1 使用 和列名在选择表中使用星号 则从FORM子句中指定的表或视图中检索并返回所有列 例 从课程表中检索所有的信息 UsexkGoSelect fromCourseGo 练习 从学生表中检索学生所在班的班级编码 2 使用DISTINCT消除重复值在Select之后使用Distinct关键字 会消除指定列的值都相同的那些行 例 从课程表中检索课程分类 要求清除值相同的那些行 消除Kind列值都相同的那些重复行SelectDistinctKindfromCourse 消除Kind和Credit列值都相同的那些重复行SelectDistinctKind CreditfromCourse 3 使用TOPn PERCENT 仅返回前n行使用TOP 可以从结果集中仅返回前n行 如果指定了PERCENT 则仅返回前n 行 此时n必须介于0 100之间 例 从学生表中检索所有的信息 要求只显示前6行数据 Selecttop6 fromStudent 返回6行Selecttop6percent fromStudent 返回180 6 11 4 修改检索结果中列的标题修改检索列标题有三种方式 1 将要显示的列标题用单引号括起来后接等号 后接要检索的列名 2 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用空格隔开 3 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用AS关键字 问题 检索课程表中课程编码 课程名称 教师 上课时间 限制选课人数和报名人数 并修改列标题 SELECT 课程编码 CouNo 课程名称 CouName 教师 Teacher 上课时间 SchoolTime 限制选课人数 LimitNum 报名人数 WIllNumFROMCourse 练习 使用其他两种方法 5 在查询结果中显示字符串将要增加的字符串用单引号括起来 然后和列的名字写在一起 中间用逗号隔开 问题 检索课程表的信息 要求给出检索结果为 课程名称课程编码SQLServer实用技术课程编码为 001 SELECT 课程名称 CouName 课程编码为 课程编码 CouNoFROMCourse 6 使用WHERE限制检索的条件使用WHERE子句限制检索的范围 通常情况下 必须定义一个或多个条件限制检索选择的数据行 例 检索上 Linux操作系统 课程的教师名 SELECT 教师 TeacherFROMCourseWHERECouName Linux操作系统 7 表达式作为SELECT语句中的列SELECT子句中的选项列表可以是要指定的表达式或列的列表 表达式可以是列名 函数或常数的列表 例 要求检索课程表的课程信息 并显示报名人数和限制选课人数之比 SELECT WillNum LimitNumFROMCourse 练习 检索课程表中最小的报名人数 最大的报名人数以及平均报名人数 使用聚合函数AVG MAX MIN SUM 聚合函数也可以出现在SELECT子句中 SELECT 最小的报名人数 MIN WillNum 最大的报名人数 MAX WillNum 平均报名人数 AVG WillNum FROMCourse 8 使用ORDERBY子句重新排列检索结果升序 使用ASC关键字或者省略 降序 使用DESC关键字 也可以在ORDERBY子句中指定多个列 检索结果首先按第1列进行排序 对第1列值相同的那些数据行 再按照第2行排序 ORDERBY要写在WHERE子句的后面 例 检索课程表的教师名 课程号 课程名 要求检索结果首先按教师名降序排列 教师名相同时 则按课程号的升序排列 SELECTTeacherAS 教师名 CouNoAS 课程号 CouNameAS 课程名 FROMCourseORDERBYTeacherDESC CouNo 或者 SELECTTeacherAS 教师名 CouNoAS 课程号 CouNameAS 课程名 FROMCourseORDERBY教师名DESC 课程号 9 使用IN NOTIN 关键字 例 检索课程编码为 001 004 013 的课程名称方法一 使用逻辑运算符OR SELECTCouNameAS 课程名称 FROMCourseWHERECouNo 004 ORCouNo 007 ORCouNo 013 方法二 使用IN关键字 比使用逻辑运算符更为简单 SELECTCouNameAS 课程名称 FROMCourseWHERECouNoIN 004 007 013 练习 检索课程号不为 004 007 013 的课程号和课程名称 10 使用LIKE NOTLIKE 关键字检索与给定的某些字符串相匹配的数据可以使用LIKE关键字 LIKE与字符串相匹配 字符串表达式被定义为字符串和通配符 SQL的4个通配符 百分号 匹配包括0个或多个字符的字符串 下划线 匹配任何一个字符 排列通配符 匹配任何在范围内的单个字符 例如 m p 匹配的是m n o p单个字符 不在范围之内的字符 匹配任何不在范围内的单个字符 例如 m p mnop 匹配的是除了m n o p之外的任何字符 注 通配符和字符串要括在单引号中 练习 解释下面的通配符表示的意义LIKE D 匹配以字母D开始的字符串 LIKE 制作 匹配的是以 制作 两字结尾的字符串 LIKE 宝 匹配的是第2个字为 宝 的字符串 如要查找通配符本身 需要将它们用方括号括起来 LIKE 表示要匹配 LIKE 5 表示要匹配 5 问题 检索以 制作 两字结尾的课程名 使用LIKE SELECT FROMCourseWHERECouNameLIKE 制作 问题 检索不姓 刘 的学生信息 使用NOTLIKE SELECT FROMStudentWHEREStuNameLIKE 刘 ORSELECT FROMStudentWHEREStuNameNOTLIKE 刘 11 使用ISNULL关键字检索列中没有赋值的行 问题 检索课程表中教师未定的课程名称和教师名 SELECT 课程名称 CouName 教师 TeacherFROMCourseWHERETeacherISNULL 12 使用BETWEEN AND NOTBETWEEN AND 关键字 用于检索在某一特定范围内的信息 问题 检索报名人数大于等于30并且小于等于40的课程信息 要求显示课程名称和报名人数 使用BETWEEN AND 注意 WHEREWillNumBETWEEN30AND40包括30和40这两个值 问题 对上例使用NOTBETWEEN AND也可以在WHERE字句中使用前面讲过的逻辑运算符检索某一范围内的信息 练习 检索报名人数比限制选课人数多一倍以及一倍以上的课程信息 要求显示课程名称 报名人数和限制选课人数之比 并按该比例的降续排列 13 使用COMPUTE子句 计算总计或进行分组小计 问题 检索课程表中课程分类为 信息技术 的课程编码 课程名称 课程分类 报名人数 并计算平均报名人数 SELECTCouNoAS 课程编码 CouNameAS 课程名称 KindAS 课程分类 WillNumAS 报名人数 FROMCourseWHEREKind 信息技术 COMPUTEAVG WillNum 说明 总计值或小计值作为附加新行出现在检索结果中 该子句用在WHERE子句之后 从检索结果可以看到 检索结果中有两个结果集 1 包含选择的列的所有明细行 2 包含COMPUTE子句中所指定的聚合函数的合计 14 使用COMPUTEBY子句使用COMPUTEBY对BY后面给出的列进行分组显示 并计算该列的分组小计 使用COMPUTEBY必须使用ORDERBY对COMPUTEBY中BY指定的列进行排序 例 按类检索课程表中的课程编码 课程名称 课程分类 报名人数 并计算每类课程的平均报名人数 SELECTCouNoAS 课程编码 CouNameAS 课程名称 KindAS 课程分类 WillNumAS 报名人数 FROMCourseORDERBYKindCOMPUTEAVG WillNum BYKind 从检索结果可以看到 检索结果中有多个组 每个组有两个结果集 这两个结果集中的内容同上 注意 必须使用ORDERBY排序要分组的列 练习 检索周二上课的课程名称和教师名 并对符合条件的课程门数进行统计 提示 先完成前一部分 再使用聚合函数COUNT进行统计 问题COMPUTE子句是在一个明细行的基础上增加一个总计或小计 有BY时 但如果只想统计每类课程的平均报名人数 该如何处理 15 使用GROUPBY子句 问题 按课程分类统计每类课程的平均报名人数SELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseGROUPBYKind 与上面例子的区别 只有一个结果集说明 1 该子句用于将检索结果按照GROUPBY后指定的列进行分组 该子句写在WHERE子句后面 2 GROUPBY子句经常用于SELECT子句中包含有聚合函数的情况 此时 SELECT子句中选项列表中出现的列 只能是GROUPBY子句中的列或者包含在聚合函数中 16 使用HAVING子句 问题 检索 信息技术 类课程的平均报名人数 SELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseGROUPBYKindHAVINGKind 信息技术 说明 1 HAVING经常用在GROUPBY子句之后 用于限定结果集中的分组 2 也可以使用WHERE子句 3 可以看到 HAVING子句是对结果进行过滤 而WHERE是对原始记录进行过滤 注意 HAVING子句中的列只能是GROUPBY子句中或者聚合函数中的列 问题 检索平均报名人数大于30人的课程分类和每类平均报名人数 SELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseWHEREAVG WillNum 25GROUPBYKind ORSELECTKindAS 课程分类 AVG WillNum AS 每类平均报名人数 FROMCourseGROUPBYKindHAVINGAVG WillNum 25 练习 在课程表中 按所开设课程的系部编码分别统计总共报名人数 17 使用子查询 在一个查询中包含另一个查询 问题 检索报名人数大于平均报名人数的课程编码 课程名称和报名人数 STEP1 如何求平均报名人数STEP2 完成题目要求SELECT 课程编码 CouNo 课程名 CouName 报名人数 WillNumFROMCourseWHEREWillNum SELECTAVG WillNum FROMCourse 18 使用UNION子句 用于将两个或多个检索结果合并成一个结果UNION用于将两个或多个检索结果合并成一个结果 使用时注意 1 所有检索中的列数和列的顺序必须相同 2 所有检索中按顺序对应列的数据类型必须兼容 相同或者可以明确地转换 问题 从班级表中检索班级名 从系部表中检索系部名 并将这两个检索结果合并起来 SELECTClassNameFROMClassUNIONSELECTDepartNameFROMDepartment 扩展 1 修改列标题 2 按新的列标题排序SELECTClassNameAS 所有班名和所有系名 FROMClassUNIONSELECTDepartNameFROMDepartment 本节小结 本节小结SQL语言的组成SELECT语句的基本格式介绍了各种单表查询语句 多表查询 如果问 计算机应用工程系 下属的班级有哪些 应该查询哪些表格 这就是多表查询了 如果在多个表中进行查询 结果会怎样 USEXkGOSELECT FROMDepartment ClassGO结果集中 有54行 3 18 5列 2 3 问题 笛卡儿积运算过程 在笛卡儿积的结果中 分析存在的问题以及解决方法 1 两列DepatNo的列值不同解决方法 加连接条件 成为相等连接 注 为了避免笛卡儿积 必须在WHERE子句中给出表格的连接条件 通常对于N个表格的检索 要有N 1个连接条件 2 出现了相同的列 解决方法 去掉相同的列 成为自然连接 3 加入 计算机应用工程系 的条件 成为带有选择条件的连接 问题 检索 01电子商务 班的学生选修课程的情况 要求显示班级名称 学生学号 学生姓名 课程名称和上课时间 分析 班级名称 学生学号 学生姓名 课程名称 上课时间这些信息是分别在班级表 学生表 学生选课表和课程表中的 因此需要在多个表中进行查询 同时 必须在WHERE子句中给出连接条件 SELECTClassNameAS 班级 StuNoAS 学号 StuNameAS 姓名 CouNameAS 课程名 SchoolTimeAS 上课时间FROMClass Student Course StuCouWHEREClass ClassNo Student ClassNoANDCourse CouNo StuCou CouNoANDStudent StuNo StuCou StuNoANDClassName 01电子商务 注意 在引用的多表中 如果列名在多个表中同名 为了避免列名不明确 在SELECT子句中必须在列名前加上表的前缀 即 表名 列名 练习 显示所有系的班级名称 要求计算各系班级数量 显示要求如下 连接 连接的类型有内连接 外连接和交叉连接 内连接就是只包含满足连接条件的数据行 它主要有自然连接和相等连接等形式 外连接是连接运算的扩展 可以处理缺失信息 它又分为左外连接 右外连接和全连接 两个表的交叉连接的结果集的总行数等于所连接的两个表的行数相乘 多表查询 1 相等连接相等连接是将要连接的列作相等比较后所作的连接 相等连接会产生冗余 因为连接的列要显示两次 例 检索学生信息和学生相应的班级信息 SELECT FROMClass StudentWHEREClass ClassNo Student ClassNo 2 自然连接自然连接是将要连接的列作相等比较的连接 但是连接的列只显示一次 自然连接消除相等连接产生的冗余 问题 检索学生信息和学生对应的班级信息 要求连接的列只显示一列 SELECTClass StuNo StuName PwdFROMClass StudentWHEREClass ClassNo Student ClassNo 练习 检索学分大于等于2 5的课程信息 显示课程名称 学分 开设该课程的系部名称 报名人数 要求使用别名 3 带有选择条件的连接在对多表检索时 在指定的连接条件之外也可以包括其他的选择条件 例 检索计算机应用工程系承担的选修课程的学生报名信息 姓名 课程名称 授课教师 SELECTStuName CouName TeacherFROMStuCou Student Course DepartmentWHEREStuCou StuNo Student StuNoANDStuCou CouNo Course CouNoANDCourse DepartNo Department DepartNoANDDepartNameLIKE 计算机应用工程系 4 非相等连接 比较连接 连接条件不是等号 而是比较运算符 问题 检索各班可以选择的不是本系开设的选修课程的信息 课本的例子 SELECTClassNameAS 班级 CouNameAS 课程名 KindAS 课程分类 CreditAS 学分 TeacherAS 教师 SchoolTimeAS 上课时间 WillNumAS 报名人数 FROMClass CourseWHERECourse DepartNoClass DepartNo 5 自连接自连接就是一个表和它自身进行连接 表名在FORM子句中出现两次 可以对表定义一个别名 在SELECT子句中引用的列名也要使用表的别名进行限定 例 检索课程类相同但系部编码不同的课程信息 意味着同一种类的课有多个系部开设 SELECTDISTINCTt1 CouNo t1 CouName t1 KindAS 课程分类 t1 DepartNoAS 系部编码 FROMCourset1 Courset2WHEREt1 Kind t2 KindANDt1 DepartNot2 DepartNO 外连接 上面介绍的都是内连接 以下是外连接 1 左外连接2 右外连接3 全连接4 交叉连接 问题 检索学生的选课信息 要求显示学生学号 课程编码 课程名称 使用EXISTS关键字 如果要解决这样的问题 问题 检索已经报名选修了课程的学生的学号和姓名 分析 采用上面所学的方法能否解决 使用EXISTS关键字 测试跟随的子查询的行是否存在 要点 一旦找到 立即停止 找到则返回TRUE 否则返回FALSE 而不是返回一些行 使用NOTEXISTS 问题 查询没有选修 001 课程的学生学号和姓名 selectstuno stunamefromstudentwherenotexists select fromstucouwherestuno student stunoandcouno 001 本节小结 多表查询非相等连接自连接使用别名外连接 左外连接 右外连接 全连接 和交叉连接使用EXISTS关键字 向表中输入数据 1 使用INSERT语句INSERT语句用来向表中或视图中输入新的数据行 输入数据的方法有两种 使用INSERT语句和使用企业管理器 INSERT语句的简单语法 INSERT INTO table name column list VALUES DEFUALT NULL expression n execute statement 例 向Department表插入3行数据 INSERTDepartmentVALUES 11 数学系 INSERTDepartment DepartName DepartNo VALUES 物理系 12 INSERTDepartment DepartNo DepartName VALUES 13 化学系 注意 1 输入新的数据时 因为系部的值是唯一的 对一存在的值 系统不允许输入重复值 2 如果表中的一些列定义为不允许为空 在输入数据时 省略该列情况下系统向该列输入空值 导致出错 3 应在半角状态下输入标点符号 2 使用企业管理器输入数据步骤如下 1 打开企业服务器 展开数据库 点击表 选择 操作 菜单下的 打开表 下的 返回所有行 2 系统弹出登录窗口 输入登录帐号和密码 系统显示窗口 此时可以输入数据 3 输入数据完毕后 关闭窗口即可 3 使用SELECT子句插入数据INSERT语句的特点时每次只能输入一行数据 在INSERT语句中使用SELECT子句可以将表中的数据行插入到表中 并且可以一次输入多行数据 使用SELECT子句插入数据的语法形式为 INSERTtable nameSELECTcolumn listFROMtable listWHEREsearch conditions 例 使用SELECT子句将NewDepartment表中的数据插入到Department表中 1 创建一个新的表 表名为NewDepartment 表中定义2列No和Name 均为字符型 不允许为空 CreateTableNewDepartment Nochar 2 notnull Namechar 30 notnull 2 使用INSERT语句向NewDepartment表输入两行数据 INSERTNewDepartmentVALUES 16 历史系 INSERTNewDepartmentVALUES 17 生物系 3 使用SELECT子句将NewDepartment表的两行数据插入到Department表中 INSERTDepartmentSELECTNo NameFROMNewDepartment 注意 1 INSERT语句中和FROM子句中使用的表可以相同也可以不相同 但必须是已存在的表 2 INSERT中指定的表和SELECT子句得到的结果集一定要兼容 即列的数量和顺序必须相同 列的数据类型和长度要相同 或者进行转换 4 使用SELECTINTO插入数据使用SELECTINTO语句允许用户定义一个新表 并且把SELECT的数据插入到新表中 前面三种方法的共同点是在数据插入之前表已经存在 而使用SELECTINTO插入数据的方法是在插入数据的过程中建立新表 其语法形式如下 SELECTselect listINTOnew table nameFROMtable listWHEREsearch conditions 例 将Course表中报名人数超过20人的数据行插入到NewCourse表中 SELECTCouNo CouName Teacher SchoolTimeINTONewCourseFROMCourseWHEREWillNum 20 注意 1 新表不能存在 否则会产生错误信息 2 新表中的列和行是基于查询结果集的 修改表中的数据 企业管理器使用UPDATE语句UPDATE语句用来修改表中以及存在的数据 UPDATE语句的语法为 UPDATEtable nameSET column name expression DEFAULT NULL n FROMtable name n WHERE 例 将 电子出版概论 课程的上课时间修改为 周二晚 UPDATECourseSETSchoolTime 周二晚 WHERECouName 电子出版概论 练习 将 01数据库 班学号为 01000025 的学生的名字修改为 陈晓梅 练习 将已选修了课程且学号为 00000005 的同学的密码修改为 7F5E5D3D 使用DELETE从表中删除数据 DELETE语句可从表中删除数据 可以一次从一个表删除一行或者多行数据 DELETE语句的简化语法 DELETE FROM table name WHERE 例 学号为 00000005 的同学因故取消课程号为 017 的选修课 在StuCou表中删除此行 DELETEStuCouWHEREStuNo 00000005 ANDCouNo 017 UPDATECourseSetWillNum WillNum 1WhereCouNo 017 练习 学号为 00000005 的同学因故取消 中餐菜肴制作 选修课 在表中进行删除处理