《第2章使用Transact-SQL语言.ppt》由会员分享,可在线阅读,更多相关《第2章使用Transact-SQL语言.ppt(39页珍藏版)》请在三一文库上搜索。
1、第2章 使用Transact-SQL语言,使用Transact-SQL语言,学习目标 了解Transact-SQL的用法和作用 学会并能根据需要灵活使用SELECT语句查询用户表的信息 使用INSERT向用户表输入数据 使用UPDATE修改用户表的数据 使用DELETE语句删除用户表的数据。,内容框架,Transact-SQL语言概述,问题: 我们需要对数据进行哪些维护操 作? 如何对数据进行查询、统计?,Transact-SQL语言概述,结构化查询语言(Structured Query Language) SQL语言是一种用于存取、查询数据以及管理数据库的数据库查询和编程语言。 尽管不同的关
2、系数据库使用各种略有不同的SQL版本,但多数都执行ANSI SQL标准。 将ANSI SQL-92的扩展集称为Transact-SQL,简写为T-SQL,Transact-SQL语言的组成,数据定义语言DDL:用来定义和管理数据库中的对象。 DDL主要的语句有: CREAT TABLE /*创建表*/ ALTER TABLE /*修改表的属性*/ DROP TABLE /*删除表*/ 数据操作语言DML:用来操作数据库中的对象和数据,是 T-SQL中最常用的部分。DML主要的语句有: SELECT /*从一个表或多个表中查询数据*/ DELETE /*从表中删除数据*/ INSERT /*向一
3、个表中添加数据*/ UPDATE /*修改表中已有的数据*/ 数据控制语言DCL:用来控制用户对数据库对象操作的权 限。主要的命令有: GRANT /*授予权限*/ REMOVE /*回收所授予的权限*/,查询信息-单表,切换数据库:USE 数据库名 SELECT语句:用于查询数据。 SELECT 选择列表 FROM 表(视图)的列表 WHERE 查询的条件 【问题】从Student表中查询学号为00000001的学生的姓名(StuName)和选课密码(Pwd) 【实训】要求查询系部编号为01的班级信息,要求显示班级编号和班级名称,查询信息(单表),1、查询所有列的信息(使用*) 【例】查询课
4、程的所有信息 【例】查询课程的课程类别 2、消除显示结果重复值(使用DISTINCT) 【例】查询课程的课程类别,要求对值相同的那些行只保留一行 3、显示前部分行的数据(使用TOP n PERCENT) 【例】从学生表中查询所有信息,要求只显示查询结果的前6行。,查询信息(单表),4、修改查询结果中的列标题 【问题】查询学生表中的学号、班级编号和姓名信息,并修改列标题为汉字标题 方法: (1)将要显示的列标题用单引号括起来后接等号(), 后接要查询的列名 (2)将要显示的列标题用单引号括起来后,写在列名后面,两者之间使用空格隔开 (3)将要显示的列标题用单引号括起来后,写在列名后面,两者之间使
5、用AS关键字 【练习】使用其他两种方法,查询信息(单表),5、在查询结果中显示字符串 【问题】查询课程表的信息,要求给出查询结果为: 课程名称 课程编号 SQL Server实用技术 课程编号为: 001 课程编号为: 课程编号为: 思考: 如何查询课程编号是001的课程名称?,查询信息(单表),6、使用WHERE限制查询的条件 【例】查询课程编号为001的课程名称。 【练习】查询 “Linux操作系统”课程的任课教师 7、表达式、函数作为SELECT语句的列 【例】查询学生总数、课程总门数。 【例】查询课程信息、报名人数与限选人数之比 思考:能否将上面的查询结果按报名人数和限选人数之比的升序
6、排序?,查询信息(单表),8、重新排序查询结果(ORDER BY子句) 升序:ASC(默认) 降序:DESC 也可以在ORDER BY子句中指定多个列 例:查询课程信息、报名人数与限选人数之比,要求按报名人数和限选人数之比升序排序查询结果 【例】查询课程表的教师名、课程号、课程名,要求查询结果首先按教师名降序排列,教师名相同时,则按课程号的升序排列 问题 查询课程类别为“信息技术”的课程的任课教师、课程名,要求先按教师名升序排列,教师名相同时,按课程名的降序排列,并将显示的列名改为中文。,查询信息(单表),9、使用IN(NOT IN)关键字 【例】查询课程编号为001004013的课程名称。
7、方法一:使用逻辑运算符OR 方法二:使用IN关键字(比使用逻辑运算符更为简单),查询信息(单表),10、模糊查询(使用LIKE(NOT LIKE) 通配符: % 匹配包括0个或多个字符的字符串 _ 匹配任何一个字符 匹配任何在范围内的单个字符,例如:m-p 匹配任何不在范围内的单个字符,例如:m-p、mnop 通配符和字符串要括在单引号中 【练习】解释下面的通配符表示的意义 D% 、 _a 、 、%D 如果要查找通配符本身,需要将它们用方括号括起来 【问题】查询以“制作”两字结尾的课程名(使用LIKE) 【练习】查询姓名的第二个字为“丽”的学生信息,要求显示学生学号和姓名 【问题】查询不姓“刘
8、”的学生信息(使用NOT LIKE),查询信息(单表),11、使用IS NULL关键字用于查询列中没有赋值的数据行 【问题】查询课程表中教师未定的课程名称和教师名 12、使用BETWEENAND(NOT BETWEENAND)关键字 用于查询在某一特定范围内的信息,查询信息(单表),【问题】查询报名人数大于等于30并且小于等于40的课程信息,要求显示课程名称和报名人数(使用BETWEENAND) 注意WHERE WillNum BETWEEN 30 AND 40包括30和40这两个值 【问题】对上例使用NOT BETWEENAND 也可以在WHERE字句中使用前面讲过的逻辑运算符查询某一范围内
9、的信息 【练习】查询报名人数比限制选课人数多一倍以及一倍以上的课程信息,要求显示课程名称、报名人数和限制选课人数之比,并按该比例的降续排列,查询信息(单表),13、使用UNION子句用于将两个或多个查询结果合并成一个结果 【问题】从班级表中查询班级名,从系部表中查询系部名,并将这两个查询结果合并起来 使用时注意: (1)要合并的查询结果的列个数要相同 (2)要合并的查询结果按顺序对应列的数据类型性同或者兼容 扩展 (1)修改列标题 (2)按新的列标题排序,查询信息(单表),14、使用COMPUTE子句 用来计算总计或进行分组小计 【问题】查询课程表中的课程编号、课程名称、课程类别、报名人数,并
10、计算平均报名人数 说明 总计值或小计值作为附加新行出现在查询结果中。该子句用在WHERE子句之后 从查询结果可以看到: 查询结果中有两个结果集 (1)包含选择的列的所有明细行 (2)包含COMPUTE子句中所指定的聚合函数的合计 聚合函数:AVG()、MAX()、MIN()、SUM() 扩展 (1)只查询课程类别为“信息技术”的课程信息 (2)可以在查询结果中显示多个聚合函数的合计 上面的例子是对显示的所有行进行总计,如何对信息进行分组小计呢?,查询信息-单表,15、使用COMPUTEBY子句 【例】按类查询课程表中的课程编号、课程名称、课程类别、报名人数,并计算每类课程的平均报名人数 查询结
11、果中有多个组? 注意必须先使用ORDER BY排序要分组的列 【练习】查询周二上课的课程名称和教师名,并显示课程门数 (提示:先完成前一部分,再使用聚合函数COUNT进行统计),问题,问题 COMPUTE子句是在一个明细行的基础上增加一个总计或小计(有BY时),但如果只想统计每类课程的平均报名人数,该如何处理?,查询信息-单表,16、使用GROUP BY子句 【问题】按课程类别统计每类课程的平均报名人数 与上面例子的区别:只有一个结果集 说明 (1)该子句用于将查询结果按照GROUP BY后指定的列进行分组,该子句写在WHERE子句后面 (2)GROUP BY子句经常用于SELECT子句中包含
12、有聚合函数的情况。此时,SELECT子句中选项列表中出现的列,只能是GROUP BY子句中的列或者包含在聚合函数中。,查询信息-单表,17、使用HAVING子句 【问题】上面的例子中,如果只统计“信息技术”类课程的平均报名人数 说明HAVING经常用在GROUP BY子句之后,用于限定结果集中的分组 也可以使用WHERE子句 可以看到,HAVING子句是对结果进行过滤,而WHERE是对原始记录进行过滤。 注意HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列 【问题】查询平均报名人数大于30人的课程类和每类平均报名人数 【练习】在课程表中,按所开设课程的系部编号分别统计总共报
13、名人数 如果问:系部编号为01的系所开设的课程总共有多少学生报名,如何编程实现?,查询信息-单表,18、使用子查询在一个查询中包含另一个查询 【问题】查询报名人数大于平均报名人数的课程编号、课程名称和报名人数 STEP 1:如何求平均报名人数 STEP 2:完成题目要求,查询信息-多表,“计算机应用工程系” 有哪些班级,应该查询哪些表? 如果在多个表中进行查询,结果会怎 样? USE Xk GO SELECT * FROM Department, Class GO 结果集中,有54行(318),5列(23)。,查询信息-多表,问题: 笛卡儿积 运算过程? 在笛卡儿积的结果中,分析存在的问题以及
14、解决方法: (1)两列DepatNo的列值不同 解决方法:加连接条件,成为相等连接 【注】为了避免笛卡儿积,必须在WHERE子句中给出表格的连接条件:通常对于N个表格的查询,要有N-1个连接条件 (2)出现了相同的列: 解决方法:去掉相同的列,成为自然连接 (3)加入“计算机应用工程系”的条件,成为带有选择条件的连接,查询信息-多表,【问题】查询“01电子商务”班学生选修课程的情况,要求显示班级名称、学号、姓名、课程名称和上课时间. 分析 班级名称、学号、姓名、课程名称、上课时间 这些信息是分别在班级表、学生表、学生选课表和课程表中的,因此需要在多个表中进行查询。同时,必须在WHERE子句中给
15、出连接条件。 注意在引用的多表中,如果列名在多个表中同名,为了避免列名不明确,在SELECT子句中必须在列名前加上表的前缀,即“表名.列名”。 【练习】查询“林斌”同学的选修课信息(课程编号、课程名称、上课时间),查询信息-多表,【练习】查询每个系所开课程的门数,显示系代码、系名称、开课门数 【练习】显示所有系的班级名称,要求计算各系班级数量,显示要求如下:,查询信息-多表,19、非相等连接(比较连接) 连接条件不是等号,而是比较运算符 【问题】查询各班可以选择的不是本系开设的选修课程的信息(教材的例子),查询信息-多表,20、自连接 一个表和自身进行的连接 【问题】查询课程类相同而系部编号不
16、同的课程信息(意味着同一类的课程有多个系部开设),要求显示课程编号、课程名称、课程类别和系部编号(教材上的例子) 21、使用别名 【练习】查询学分大于等于2.5的课程信息,显示课程名称、学分、开设该课程的系部名称、报名人数,要求使用别名,查询信息-多表,上面介绍的都是内连接。 以下是外连接:主要解决信息丢失的问题 【问题】查询开设的所有选修课的信息和所有学生选修课程的情况,要求显示学生学号、课程编号、课程名称 自然连接会丢失信息:Course表的019课程、StuCou表的020课程 左外连接 右外连接、全连接 交叉连接,使用EXISTS关键字,如果要解决这样的问题: 【问题】查询已经报名选修
17、了课程的学生的学号和姓名 分析采用上面所学的方法能否解决? 使用EXISTS关键字 测试跟随的子查询的行是否存在 要点 一旦找到,立即停止 找到则返回TRUE,否则返回FALSE,而不是返回一些行,查询信息-多表,使用NOT EXISTS 【问题】查询没有选修001课程的学生学号和姓名。 SELECT StuNo,StuName FROM Student WHERE NOT EXISTS (SELECT * FROM StuCou WHERE StuNo=Student.StuNo AND CouNo=001),查询信息-多表,阅读理解如下语句: IF EXISTS (SELECT name
18、FROM master.dbo.sysdatabases WHERE name = Xk) DROP DATABASE Xk GO,向表中输入数据,使用Management Studio 使用INSERT语句 注意事项: 主键值不允许出现重复值 不允许为空的列,必须输入值 数据类型要符合列的定义 values值列表的顺序要与表的列顺序一致,向表中输入数据(续),使用SELECT语句 INSERT table_name SELECT column_list FROM table_list WHERE search_conditions 注意事项: INSERT语句中和FROM子句中使用的表可以相
19、同也可以不同,但必须是已经存在的表 SELECT子句得到的结果集一定要和INSERT中指定的表兼容(列的数量和顺序要相同),向表中输入数据,使用SELECT INTO语句 SELECT select_list INTO new_table_name FROM table_list WHERE search_conditions 注意事项: 新表不能存在,否则会产生错误信息 新表中的列和行是基于查询结果集的,修改数据,修改表中的数据 问题:把学号为00000001的学生姓名改为Alex 问题:把课程号在002到005之间的课程的报名人数增加1。 涉及多个表的修改 问题:把学号为00000001,选修课为Java技术的开发应用的自愿号(WillOrder)改为2。需要用到两个表StuCou和Course。,、删除数据,删除表中的数据 DELETE table_name FROM WHERE 问题:删除学号00000011的学生的选课记录。 涉及多个表的删除 问题:学号为00000002的同学取消了中餐菜肴制作课程的选修,请在表StuCou中把他的选课信息删除。,本章小节,能根据实际需要对所自己使用的数据库,进行查询与统计,编辑(输入、修改、删除)。 要掌握对单表、多表的查询与统计 能理解出表与表之间的关系和连接条件 能根据需要使用聚合函数,会进行分组显示并进行统计,
链接地址:https://www.31doc.com/p-2548800.html