第2章 使用Transact-SQL语言.

Presentation on theme: "第2章 使用Transact-SQL语言."— Presentation transcript:

1 第2章 使用Transact-SQL语言

2 使用Transact-SQL语言 学习目标 了解Transact-SQL的用法和作用
学会并能根据需要灵活使用SELECT语句查询用户表的信息 使用INSERT向用户表输入数据 使用UPDATE修改用户表的数据 使用DELETE语句删除用户表的数据。

3 内容框架

4 问题： 我们需要对数据进行哪些维护操 作？ 如何对数据进行查询、统计? Transact-SQL语言概述

5 Transact-SQL语言概述 结构化查询语言（Structured Query Language）
尽管不同的关系数据库使用各种略有不同的SQL版本，但多数都执行ANSI SQL标准。 将ANSI SQL-92的扩展集称为Transact-SQL，简写为T-SQL Transact-SQL语言概述

6 Transact-SQL语言的组成 数据定义语言DDL：用来定义和管理数据库中的对象。 DDL主要的语句有:
CREAT TABLE /*创建表*/ ALTER TABLE /*修改表的属性*/ DROP TABLE /*删除表*/ 数据操作语言DML：用来操作数据库中的对象和数据，是 T-SQL中最常用的部分。DML主要的语句有：  SELECT /*从一个表或多个表中查询数据*/ DELETE /*从表中删除数据*/ INSERT /*向一个表中添加数据*/ UPDATE /*修改表中已有的数据*/ 数据控制语言DCL：用来控制用户对数据库对象操作的权 限。主要的命令有： GRANT /*授予权限*/ REMOVE /*回收所授予的权限*/ Transact-SQL语言的组成

7 查询信息--单表 切换数据库:USE 数据库名 SELECT语句:用于查询数据。 SELECT 选择列表 FROM 表（视图）的列表
WHERE 查询的条件 【问题】从Student表中查询学号为 的学生的姓名（StuName）和选课密码（Pwd） 【实训】要求查询系部编号为’01’的班级信息，要求显示班级编号和班级名称 查询信息--单表

8 查询信息(单表) 1、查询所有列的信息（使用*） 【例】查询课程的所有信息 【例】查询课程的课程类别
2、消除显示结果重复值（使用DISTINCT） 【例】查询课程的课程类别，要求对值相同的那些行只保留一行 3、显示前部分行的数据（使用TOP n [PERCENT]） 【例】从学生表中查询所有信息，要求只显示查询结果的前6行。  查询信息(单表)

9 查询信息(单表) 4、修改查询结果中的列标题 【问题】查询学生表中的学号、班级编号和姓名信息，并修改列标题为汉字标题 方法：
 方法： （1）将要显示的列标题用单引号括起来后接等号（＝）， 后接要查询的列名 （2）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用空格隔开 （3）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用AS关键字  【练习】使用其他两种方法 查询信息(单表)

10 查询信息(单表) 5、在查询结果中显示字符串 【问题】查询课程表的信息，要求给出查询结果为： 思考： 如何查询课程编号是001的课程名称？
课程名称 课程编号 SQL Server实用技术 课程编号为： 001 …… 课程编号为： …… …… 课程编号为： …… 思考： 如何查询课程编号是001的课程名称？ 查询信息(单表)

11 查询信息(单表) 6、使用WHERE限制查询的条件 【例】查询课程编号为001的课程名称。
【例】查询课程编号为001的课程名称。  【练习】查询 “Linux操作系统”课程的任课教师 7、表达式、函数作为SELECT语句的列 【例】查询学生总数、课程总门数。 【例】查询课程信息、报名人数与限选人数之比 思考：能否将上面的查询结果按报名人数和限选人数之比的升序排序？ 查询信息(单表)

12 查询信息(单表) 8、重新排序查询结果（ORDER BY子句） 升序：ASC（默认） 降序：DESC
[例]：查询课程信息、报名人数与限选人数之比，要求按报名人数和限选人数之比升序排序查询结果 【例】查询课程表的教师名、课程号、课程名，要求查询结果首先按教师名降序排列，教师名相同时，则按课程号的升序排列 [问题] 查询课程类别为“信息技术”的课程的任课教师、课程名，要求先按教师名升序排列，教师名相同时，按课程名的降序排列，并将显示的列名改为中文。 查询信息(单表)

13 查询信息(单表) 9、使用IN（NOT IN）关键字 【例】查询课程编号为001’‘004’‘013’的课程名称。
方法一：使用逻辑运算符OR 方法二：使用IN关键字（比使用逻辑运算符更为简单） 查询信息(单表)

14 查询信息(单表) 10、模糊查询（使用LIKE（NOT LIKE）） 通配符： % 匹配包括0个或多个字符的字符串 _ 匹配任何一个字符
% 匹配包括0个或多个字符的字符串 _ 匹配任何一个字符 [] 匹配任何在范围内的单个字符，例如：[m-p] [^] 匹配任何不在范围内的单个字符，例如：[^m-p]、[^mnop] 通配符和字符串要括在单引号中  【练习】解释下面的通配符表示的意义 ％D% 、 _[^a] 、 [[]、[%]D 如果要查找通配符本身，需要将它们用方括号括起来 【问题】查询以“制作”两字结尾的课程名（使用LIKE） 【练习】查询姓名的第二个字为“丽”的学生信息，要求显示学生学号和姓名 【问题】查询不姓“刘”的学生信息（使用NOT LIKE） 查询信息(单表)

15 查询信息(单表) 11、使用IS NULL关键字——用于查询列中没有赋值的数据行 【问题】查询课程表中教师未定的课程名称和教师名
12、使用BETWEEN…AND（NOT BETWEEN…AND）关键字 用于查询在某一特定范围内的信息

16 查询信息(单表) 【问题】查询报名人数大于等于30并且小于等于40的课程信息，要求显示课程名称和报名人数（使用BETWEEN…AND）
 [注意]WHERE WillNum BETWEEN 30 AND 40包括30和40这两个值 【问题】对上例使用NOT BETWEEN…AND 也可以在WHERE字句中使用前面讲过的逻辑运算符查询某一范围内的信息 【练习】查询报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课人数之比，并按该比例的降续排列 查询信息(单表)

17 查询信息(单表) 13、使用UNION子句——用于将两个或多个查询结果合并成一个结果
 【问题】从班级表中查询班级名，从系部表中查询系部名，并将这两个查询结果合并起来  使用时注意： （1）要合并的查询结果的列个数要相同 （2）要合并的查询结果按顺序对应列的数据类型性同或者兼容  [扩展] （1）修改列标题 （2）按新的列标题排序 查询信息(单表)

18 查询信息(单表) 14、使用COMPUTE子句 ——用来计算总计或进行分组小计
【问题】查询课程表中的课程编号、课程名称、课程类别、报名人数，并计算平均报名人数  [说明] 总计值或小计值作为附加新行出现在查询结果中。该子句用在WHERE子句之后  从查询结果可以看到： 查询结果中有两个结果集 （1）包含选择的列的所有明细行 （2）包含COMPUTE子句中所指定的聚合函数的合计  聚合函数：AVG()、MAX()、MIN()、SUM()  [扩展] （1）只查询课程类别为“信息技术”的课程信息 （2）可以在查询结果中显示多个聚合函数的合计  上面的例子是对显示的所有行进行总计，如何对信息进行分组小计呢？ 查询信息(单表)

19 查询信息--单表 15、使用COMPUTE…BY子句
【例】按类查询课程表中的课程编号、课程名称、课程类别、报名人数，并计算每类课程的平均报名人数 查询结果中有多个组？ [注意]必须先使用ORDER BY排序要分组的列 【练习】查询周二上课的课程名称和教师名，并显示课程门数 （提示：先完成前一部分，再使用聚合函数COUNT进行统计） 查询信息--单表

20 问题 COMPUTE子句是在一个明细行的基础上增加一个总计或小计（有BY时），但如果只想统计每类课程的平均报名人数，该如何处理？ 问题

21 查询信息--单表 16、使用GROUP BY子句 【问题】按课程类别统计每类课程的平均报名人数 与上面例子的区别：只有一个结果集 [说明]
与上面例子的区别：只有一个结果集 [说明] （1）该子句用于将查询结果按照GROUP BY后指定的列进行分组，该子句写在WHERE子句后面 （2）GROUP BY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列表中出现的列，只能是GROUP BY子句中的列或者包含在聚合函数中。 查询信息--单表

22 查询信息--单表 17、使用HAVING子句 【问题】上面的例子中，如果只统计“信息技术”类课程的平均报名人数
 [说明]HAVING经常用在GROUP BY子句之后，用于限定结果集中的分组  也可以使用WHERE子句 可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。  [注意]HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列  【问题】查询平均报名人数大于30人的课程类和每类平均报名人数  【练习】在课程表中，按所开设课程的系部编号分别统计总共报名人数 如果问：系部编号为01的系所开设的课程总共有多少学生报名，如何编程实现？ 查询信息--单表

23 查询信息--单表 18、使用子查询——在一个查询中包含另一个查询 【问题】查询报名人数大于平均报名人数的课程编号、课程名称和报名人数
STEP 1：如何求平均报名人数 STEP 2：完成题目要求 查询信息--单表

24 查询信息--多表 “计算机应用工程系” 有哪些班级，应该查询哪些表？ 如果在多个表中进行查询，结果会怎 样？ USE Xk GO
“计算机应用工程系” 有哪些班级，应该查询哪些表？  如果在多个表中进行查询，结果会怎 样？ USE Xk GO SELECT * FROM Department, Class 结果集中，有54行（3×18），5列（2＋3）。 查询信息--多表

25 查询信息--多表 问题: [笛卡儿积] 运算过程？ 在笛卡儿积的结果中，分析存在的问题以及解决方法： （1）两列DepatNo的列值不同
问题: [笛卡儿积] 运算过程？ 在笛卡儿积的结果中，分析存在的问题以及解决方法： （1）两列DepatNo的列值不同 解决方法：加连接条件，成为相等连接 【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：通常对于N个表格的查询，要有N-1个连接条件 （2）出现了相同的列： 解决方法：去掉相同的列，成为自然连接 （3）加入“计算机应用工程系”的条件，成为带有选择条件的连接 查询信息--多表

26 查询信息--多表 【问题】查询“01电子商务”班学生选修课程的情况，要求显示班级名称、学号、姓名、课程名称和上课时间.
 [分析] 班级名称、学号、姓名、课程名称、上课时间 这些信息是分别在班级表、学生表、学生选课表和课程表中的，因此需要在多个表中进行查询。同时，必须在WHERE子句中给出连接条件。 [注意]在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，在SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。 【练习】查询“林斌”同学的选修课信息（课程编号、课程名称、上课时间） 查询信息--多表

27 查询信息--多表 【练习】查询每个系所开课程的门数，显示系代码、系名称、开课门数
【练习】显示所有系的班级名称，要求计算各系班级数量，显示要求如下： 查询信息--多表

28 查询信息--多表 19、非相等连接（比较连接） ——连接条件不是等号，而是比较运算符
【问题】查询各班可以选择的不是本系开设的选修课程的信息（教材的例子）

29 查询信息--多表 20、自连接 —一个表和自身进行的连接
【问题】查询课程类相同而系部编号不同的课程信息（意味着同一类的课程有多个系部开设），要求显示课程编号、课程名称、课程类别和系部编号（教材上的例子） 21、使用别名 【练习】查询学分大于等于2.5的课程信息，显示课程名称、学分、开设该课程的系部名称、报名人数，要求使用别名 查询信息--多表

30 查询信息--多表 上面介绍的都是内连接。 以下是外连接：主要解决信息丢失的问题
【问题】查询开设的所有选修课的信息和所有学生选修课程的情况，要求显示学生学号、课程编号、课程名称 自然连接会丢失信息：Course表的‘019’课程、StuCou表的‘020’课程 左外连接 右外连接、全连接 交叉连接 查询信息--多表

31 使用EXISTS关键字 如果要解决这样的问题： 【问题】查询已经报名选修了课程的学生的学号和姓名 [分析]采用上面所学的方法能否解决？
——测试跟随的子查询的行是否存在 [要点] 一旦找到，立即停止 找到则返回TRUE，否则返回FALSE，而不是返回一些行 使用EXISTS关键字

32 查询信息--多表 【问题】查询没有选修’001’课程的学生学号和姓名。 使用NOT EXISTS SELECT StuNo,StuName
FROM Student WHERE NOT EXISTS (SELECT * FROM StuCou WHERE StuNo=Student.StuNo AND CouNo='001')

33 查询信息--多表 阅读理解如下语句： IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = 'Xk') DROP DATABASE Xk GO

34 向表中输入数据 使用Management Studio 使用INSERT语句 主键值不允许出现重复值 不允许为空的列，必须输入值
注意事项： 主键值不允许出现重复值 不允许为空的列，必须输入值 数据类型要符合列的定义 values值列表的顺序要与表的列顺序一致 向表中输入数据

35 向表中输入数据（续） 使用SELECT语句 INSERT table_name SELECT column_list
FROM table_list WHERE search_conditions 注意事项： INSERT语句中和FROM子句中使用的表可以相同也可以不同，但必须是已经存在的表 SELECT子句得到的结果集一定要和INSERT中指定的表兼容（列的数量和顺序要相同） 向表中输入数据（续）

36 向表中输入数据 使用SELECT INTO语句 SELECT select_list INTO new_table_name
FROM table_list WHERE search_conditions 注意事项： 新表不能存在，否则会产生错误信息 新表中的列和行是基于查询结果集的 向表中输入数据

37 修改数据 修改表中的数据 问题：把学号为‘00000001’的学生姓名改为‘Alex’
问题：把课程号在‘002’到‘005’之间的课程的报名人数增加1。 涉及多个表的修改 问题：把学号为’ ’，选修课为’Java技术的开发应用’的自愿号（WillOrder）改为2。需要用到两个表StuCou和Course。 修改数据

38 、删除数据 删除表中的数据 DELETE table_name FROM … WHERE …
问题：删除学号‘ ’的学生的选课记录。 涉及多个表的删除 问题：学号为’ ’的同学取消了’中餐菜肴制作’课程的选修，请在表StuCou中把他的选课信息删除。

39 本章小节 能根据实际需要对所自己使用的数据库，进行查询与统计，编辑（输入、修改、删除）。 要掌握对单表、多表的查询与统计
能理解出表与表之间的关系和连接条件 能根据需要使用聚合函数，会进行分组显示并进行统计 本章小节