Transact-SQL语言.ppt

第2章Transact SQL语言 Transact SQL语言概述 第1节单表查询问题 我们需要对数据进行哪些维护操作 如何对数据进行查询 统计 结构化查询语言 StructuredQueryLanguage 这种语言的语法结构类似于英语 易学易用 书写随意 例如前面第一章讲过的例子 Transact SQL语言的组成 1 数据定义语言DDL 用来定义和管理数据库中的对象 DDL主要的语句有 CREATTABLE 创建表 ALTERTABLE 修改表的属性 DROPTABLE 删除表 2 数据操作语言DML 用来操作数据库中的对象和数据 是T SQL中最常用的部分 DML主要的语句有 SELECT 从一个表或多个表中检索数据 DELETE 从表中删除数据 INSERT 向一个表中添加数据 UPDATE 修改表中已有的数据 3 数据控制语言DCL 用来控制用户对数据库对象操作的权限 主要的命令有 GRANT 授予权限 REMOVE 回收所授予的权限 操作数据 打开一个数据库从当前数据库切换到要使用的数据库时必须使用USE语句 并使用GO语句作为结束行 使用SELECT子句SELECT子句主要用于检索数据 其基本格式为 SELECT选择列表FROM表的列表WHEAR检索的条件 问题 从Student表中检索学号为00000001的学生的姓名 StuName 要求显示学生的学号和姓名 练习 要求检索系部编号为01的班级信息 要求显示班级编号和班级名称 1 的使用 例 显示学生表中所有信息 例 从学生表中检索学生所在班的班级编码2 使用DISTINCT 例 从学生表中检索学生所在班的班级编码 要求清除值相同的那些行3 使用TOPn PERCENT 例 从学生表中检索所有的信息 要求只显示前6行数据 4 修改检索结果中列的标题 问题 检索学生表中的学号 班级编码和姓名信息 并修改列标题方法有三 1 将要显示的列标题用单引号括起来后接等号 后接要检索的列名 2 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用空格隔开 3 将要显示的列标题用单引号括起来后 写在列名后面 两者之间使用AS关键字 练习 使用其他两种方法 5 在查询结果中显示字符串 问题 检索课程表的信息 要求给出检索结果为 课程名称课程编码SQLServer实用技术课程编码为 001 课程编码为 课程编码为 思考 如何检索课程编码是001的课程名称 6 使用WHERE限制检索的条件 例 检索课程编码为001的课程名称 要求只显示课程名称 练习 检索上 Linux操作系统 课程的主讲教师7 表达式作为SELECT语句中的列 例 要求检索课程表的课程信息 并显示报名人数和限制选课人数之比思考 如何将上面的检索结果按报名人数和限制选课人数之比的升序排列 8 使用ORDERBY子句重新排列检索结果 升序例子 降序例子 也可以在ORDERBY子句中指定多个列 例 检索课程表的教师名 课程号 课程名 要求检索结果首先按教师名降序排列 教师名相同时 则按课程号的升序排列9 使用IN NOTIN 关键字 例 检索课程编码为 001 004 013 的课程名称方法一 使用逻辑运算符OR方法二 使用IN关键字 比使用逻辑运算符更为简单 问题 检索开设了 信息技术 类课程的教师名及其所开的课程名 要求先按教师名升序排列 教师名相同时 按课程名的降序排列 并将显示的列名改为中文 操作数据 续 1 使用LIKE NOTLIKE 关键字通配符 匹配包括0个或多个字符的字符串 匹配任何一个字符 匹配任何在范围内的单个字符 例如 m p 匹配任何不在范围内的单个字符 例如 m p mnop 通配符和字符串要括在单引号中 练习 解释下面的通配符表示的意义 D a 如果要查找通配符本身 需要将它们用方括号括起来 问题 检索以 制作 两字结尾的课程名 使用LIKE 练习 检索姓名的第二个字为 丽 的学生信息 要求显示学生学号和姓名 问题 检索不姓 刘 的学生信息 使用NOTLIKE 2 使用ISNULL关键字 用于检索列中没有赋值的行 问题 检索课程表中教师未定的课程名称和教师名 3 使用BETWEEN AND NOTBETWEEN AND 关键字 用于检索在某一特定范围内的信息 问题 检索报名人数大于等于30并且小于等于40的课程信息 要求显示课程名称和报名人数 使用BETWEEN AND 注意 WHEREWillNumBETWEEN30AND40包括30和40这两个值 问题 对上例使用NOTBETWEEN AND也可以在WHERE字句中使用前面讲过的逻辑运算符检索某一范围内的信息 练习 检索报名人数比限制选课人数多一倍以及一倍以上的课程信息 要求显示课程名称 报名人数和限制选课人数之比 并按该比例的降续排列 4 使用UNION子句 用于将两个或多个检索结果合并成一个结果 问题 从班级表中检索班级名 从系部表中检索系部名 并将这两个检索结果合并起来使用时注意 1 所有检索中的列数和列的顺序必须相同 2 所有检索中按顺序对应列的数据类型必须兼容 相同或者可以明确地转换 扩展 1 修改列标题 2 按新的列标题排序 5 使用COMPUTE子句 用来计算总计或进行分组小计 问题 检索课程表中 信息技术 类的课程编码 课程名称 课程分类 报名人数 并计算平均报名人数 说明 总计值或小计值作为附加新行出现在检索结果中 该子句用在WHERE子句之后从检索结果可以看到 检索结果中有两个结果集 1 包含选择的列的所有明细行 2 包含COMPUTE子句中所指定的聚合函数的合计 使用聚合函数 AVG MAX MIN SUM 聚合函数也可以出现在SELECT子句中 例PAGE39 例2 10 扩展 1 只检索课程分类为 信息技术 的课程信息 2 可以在检索结果中显示多个聚合函数合计 6 使用COMPUTE BY子句使用COMPUTE BY子句时必须使用ORDERBY对COMPUTE BY中的BY指定的列进行排序 例 按类检索课程表中的课程编码 课程名称 课程分类 报名人数 并计算每类课程的平均报名人数从检索结果可以看到 检索结果中有多个组 每个组有两个结果集 这两个结果集中的内容同上 问题 问题COMPUTE子句是在一个明细行的基础上增加一个总计或小计 有BY时 但如果只想统计每类课程的平均报名人数 该如何处理 操作数据 续 1 使用GROUPBY子句 问题 按课程分类统计每类课程的平均报名人数与上面例子的区别 只有一个结果集 说明 1 该子句用于将检索结果按照GROUPBY后指定的列进行分组 该子句写在WHERE子句后面 2 GROUPBY子句经常用于SELECT子句中包含有聚合函数的情况 此时 SELECT子句中选项列表中出现的列 只能是GROUPBY子句中的列或者包含在聚合函数中 2 使用HAVING子句 问题 上面的例子中 如果只统计 信息技术 类课程的平均报名人数 说明 HAVING经常用在GROUPBY子句之后 用于限定结果集中的分组也可以使用WHERE子句可以看到 HAVING子句是对结果进行过滤 而WHERE是对原始记录进行过滤 注意 HAVING子句中的列只能是GROUPBY子句中或者聚合函数中的列 问题 检索平均报名人数大于30人的课程分类和每类平均报名人数 3 使用子查询 在一个查询中包含另一个查询 问题 检索报名人数大于平均报名人数的课程编码 课程名称和报名人数STEP1 如何求平均报名人数STEP2 完成题目要求 本节小结 本节小结SQL语言的组成SELECT语句的基本格式介绍了各种单表查询语句 多表查询 第2节多表查询如果问 计算机应用工程系 下属的班级有哪些 应该查询哪些表格 这就是多表查询了 如果在多个表中进行查询 结果会怎样 USEXkGOSELECT FROMDepartment ClassGO结果集中 有54行 3 18 5列 2 3 问题 笛卡儿积 运算过程 在笛卡儿积的结果中 分析存在的问题以及解决方法 1 两列DepatNo的列值不同解决方法 加连接条件 成为相等连接 注 为了避免笛卡儿积 必须在WHERE子句中给出表格的连接条件 通常对于N个表格的检索 要有N 1个连接条件 2 出现了相同的列 解决方法 去掉相同的列 成为自然连接 3 加入 计算机应用工程系 的条件 成为带有选择条件的连接 问题 检索 01电子商务 班的学生选修课程的情况 要求显示班级名称 学生学号 学生姓名 课程名称和上课时间 分析 班级名称 学生学号 学生姓名 课程名称 上课时间这些信息是分别在班级表 学生表 学生选课表和课程表中的 因此需要在多个表中进行查询 同时 必须在WHERE子句中给出连接条件 注意 在引用的多表中 如果列名在多个表中同名 为了避免列名不明确 在SELECT子句中必须在列名前加上表的前缀 即 表名 列名 练习 检索 林斌 同学的选修课信息 课程编号 课程名称 上课时间 多表查询 1 非相等连接 比较连接 连接条件不是等号 而是比较运算符 问题 检索各班可以选择的不是本系开设的选修课程的信息 课本的例子 2 自连接 自己和自己的连接 问题 检索课程类相同而系部编码不同的课程信息 意味着同一类的课程有多个系部开设 要求显示课程编码 课程名称 课程分类和系部编码 课本的例子 外连接 上面介绍的都是内连接 以下是外连接 问题 检索学生的选课信息 要求显示学生学号 课程编码 课程名称1 左外连接2 右外连接3 全连接4 交叉连接 使用EXISTS关键字 如果要解决这样的问题 问题 检索已经报名选修了课程的学生的学号和姓名 分析 采用上面所学的方法能否解决 使用EXISTS关键字 测试跟随的子查询的行是否存在 要点 一旦找到 立即停止找到则返回TRUE 否则返回FALSE 而不是返回一些行 使用NOTEXISTS 问题 查询没有选修 001 课程的学生学号和姓名 selectstuno stunamefromstudentwherenotexists select fromstucouwherestuno student stunoandcouno 001 本节小结 多表查询非相等连接自连接使用别名外连接 左外连接 右外连接 全连接 和交叉连接使用EXISTS关键字 第三节向表中输入数据 1 使用INSERT语句列的个数必须与VALUES子句中给出的个数相同 数据类型必须和列的数据类型相对应 例2 44向Department表中输入3行数据 USEXkGOINSERTDepartmentVALUES 11 数学系 INSERTDepartment DepartName DepartNo VALUES 物理系 12 INSERTDepartment DepartNo DepartName VALUES 13 化学系 GOSELECT FROMDepartmentGO 2 使用企业管理器输入数据步骤如教材P723 使用SELECT子句插入数据INSERT语句的特点是每一次只能输入一行数据 在INSERT语句中使用SELECT子句可以将表中的数据行插入到表中 并且可以一次输入多行数据 INSERTtable nameSELECTcolumn listFROMtable listWHEREsearch condition 例2 45使用SELECT子句将NewDepartment表中的数据插入到Department表中 1 创建新表USEXkGOCreateTableNewDepartment Nochar 2 notnull Namechar 30 notnull GO 2 向新表插入数据INSERTNewDepartmentVALUES 16 历史系 INSERTNewDepartmentVALUES 17 生物系 GO 3 将新表中的数据插入到目标表中USEXkGOINSERTDepartmentSELECTNo NameFROMNewDepartmentGO 使用SELECT子句插入数据时 应注意 1 INSERT语句中和FROM子句中所使用的表可以相同也可以不相同 但必须是已存在的表 2 INSERT中指定的表和SELECT子句子句得到的结果集一定要兼容 即列的数量和顺序必须相同 列的数据类型和长度要相同 或者进行交换 4 使用SELECTINTO插入数据允许用户定义一个新表 并且把SELECT的数据插入到新表中 使用SELECTINTO插入数据时应注意 1 新表不能存在 否则会产生错误信息 2 新表中的列和行是基于查询结果集的 第四节修改表中的数据 方法有两种 1 使用UPDATE语句2 使用企业管理器例2 48例2 49 第五节使用DELETE从表中删除数据 可以一次从一个表中删除一行或者多行数据 DELETE FROM table name WHERE 例2 51例2 52