Transact-SQL语言综合性概述-数据库维护与管理 池瑞楠.ppt

第2讲 Transact-SQL语言,Transact-SQL语言概述,第1节 单表查询 问题： 我们需要对数据进行哪些维护操作？如何对数据进行查询、统计? 结构化查询语言（Structured Query Language）。这种语言的语法结构类似于英语，易学易用，书写随意。例如前面第一章讲过的例子,Transact-SQL语言的组成,1、数据定义语言DDL：用来定义和管理数据库中的对象。 DDL主要的语句有: CREAT TABLE /*创建表*/ ALTER TABLE /*修改表的属性*/ DROP TABLE /*删除表*/ 2、数据操作语言DML：用来操作数据库中的对象和数据，是T-SQL中最常用的部分。 DML主要的语句有： SELECT /*从一个表或多个表中检索数据*/ DELETE /*从表中删除数据*/ INSERT /*向一个表中添加数据*/ UPDATE /*修改表中已有的数据*/ 3、数据控制语言DCL：用来控制用户对数据库对象操作的权限。 主要的命令有： GRANT /*授予权限*/ REMOVE /*回收所授予的权限*/,操作数据,打开一个数据库 使用SELECT子句 SELECT子句主要用于检索数据，其基本格式为： SELECT 选择列表 FROM 表的列表 WHEAR 检索的条件 【例2-1】从Student表中检索学号为00000001的学生的姓名（StuName），要求显示学生的学号和姓名 【练习1】要求检索系部编号为01的班级信息，要求显示班级编号和班级名称,1、*的使用 例2.2 【练习2】显示学生表中所有信息 例2.3 【练习3】从学生表中检索学生所在班的班级编码 2、使用DISTINCT消除重复值 例2.4 【练习4】从学生表中检索学生所在班的班级编码，要求清除值相同的那些行 3、使用TOP n PERCENT仅返回前N行：例2.5 【练习5】从学生表中检索所有的信息，要求只显示前6行数据,4、修改检索结果中列的标题：例2.6 【练习6】检索学生表中的学号、班级编码和姓名信息，并修改列标题 方法有三： （1）将要显示的列标题用单引号括起来后接等号（）， 后接要检索的列名 （2）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用空格隔开 （3）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用AS关键字 【练习】使用其他两种方法,5、在查询结果中显示字符串：例2.7 【问题】检索课程表的信息，要求给出检索结果为： 课程名称 课程编码 SQL Server实用技术 课程编码为： 001 课程编码为： 课程编码为： 思考：如何检索课程编码是001的课程名称？ (条件),6、使用WHERE限制检索的条件 【练习7】检索课程编码为001的课程名称，要求只显示课程名称 【练习8】检索上“Linux操作系统”课程的教师名 7、表达式作为SELECT语句中的列 【例】要求检索课程表的课程信息，并显示报名人数和限制选课人数之比 算求函数：见右表 回顾：报名人数和限制选课人数之比列名显示为： 报名人数比例 思考：如何将上面的检索结果按报名人数和限制选课人数之比的升序排列？,8、使用ORDER BY子句重新排列检索结果 【升序例子2.11】 【降序例子】 也可以在ORDER BY子句中指定多个列 【例2.12】检索课程表的教师名、课程号、课程名，要求检索结果首先按教师名降序排列，教师名相同时，则按课程号的升序排列 9、使用IN（NOT IN）关键字 【例】检索课程编码为001004013的课程名称 方法一：使用逻辑运算符OR 方法二：使用IN关键字（比使用逻辑运算符更为简单）,练习,检索开设了“信息技术”类课程的教师名及其所开的课程名，要求先按教师名升序排列，教师名相同时，按课程名的降序排列，并将显示的列名改为中文。,综合练习,PART 表（ PNO（存储部件的数字）， PNAME（名称）， PRICE（价格） 例1：从表 PART 里面把字段 PRICE 大于 10 的所有记录找出来， 我们写出下面查询： SELECT * FROM PART WHERE PRICE 10; 然后得到表： PNO | PNAME | PRICE -+-+- 3 | Bolt | 15 4 | Cam | 25,PART: PNO | PNAME | PRICE -+-+- 1 | Screw | 10 2 | Nut | 8 3 | Bolt | 15 4 | Cam | 25,例2：从表 PART 里面把字段 PRICE 等于 0或小于等于15的并且 PNAME 为Bolt的所有记录找出来，结果： PNAME | PRICE -+- Bolt | 15 查询语句： SELECT PNAME, PRICE FROM PART WHERE PNAME = Bolt AND (PRICE = 0 OR PRICE = 15);,例3：如果我们想知道如果我们买两个部件的话要多少钱，如下结果： PNAME | DOUBLE -+- Screw | 20 Nut | 16 Bolt | 30 查询语句为： SELECT PNAME, PRICE * 2 AS DOUBLE FROM PART WHERE PRICE * 2 50;,例4：查询PART 里面所有部件的平均价格，结果： 结果是： AVG_PRICE - 14.5 查询语句： SELECT AVG(PRICE) AS AVG_PRICE FROM PART;,操作数据（续）,1、使用LIKE（NOT LIKE）关键字 通配符： % 匹配包括0个或多个字符的字符串 _ 匹配任何一个字符 匹配任何在范围内的单个字符，例如：m-p 匹配任何不在范围内的单个字符，例如：m-p、mnop 通配符和字符串要括在单引号中 【练习】解释下面的通配符表示的意义 D% _a 如果要查找通配符本身，需要将它们用方括号括起来 %D 【例2.16】检索以“制作”两字结尾的课程名（使用LIKE） 【练习】检索姓名的第二个字为“丽”的学生信息，要求显示学生学号和姓名 【例2.18】检索不姓“刘”的学生信息（使用NOT LIKE）,练习LIKE：表student(name),例1，查询name字段中包含有“明”字的。 例2，查询name字段中以“李”字开头。 例3，查询name字段中含有数字的。 例4，查询name字段中含有小写字母的。 例5，查询name字段中不含有数字的。,例1， select * from student where name like %明% 例2， select * from student where name like 李% 例3， select * from student where name like %0-9% 例4， select * from student where name like %a-z% 例5， select * from student where name like %0-9%,2、使用IS NULL关键字用于检索列中没有赋值的行 【例2.19】检索课程表中教师未定的课程名称和教师名,3、使用BETWEENAND（NOT BETWEENAND）关键字 用于检索在某一特定范围内的信息 【问题】检索报名人数大于等于30并且小于等于40的课程信息，要求显示课程名称和报名人数（使用BETWEENAND） 注意WHERE WillNum BETWEEN 30 AND 40包括30和40这两个值 【问题】对上例使用NOT BETWEENAND 也可以在WHERE字句中使用前面讲过的逻辑运算符检索某一范围内的信息 【练习】检索报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课人数之比，并按该比例的降续排列,4、使用COMPUTE子句 用来计算总计或进行分组小计 【问题】检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算平均报名人数 说明 总计值或小计值作为附加新行出现在检索结果中。该子句用在WHERE子句之后 从检索结果可以看到： 检索结果中有两个结果集 （1）包含选择的列的所有明细行 （2）包含COMPUTE子句中所指定的聚合函数对筛选出的数据的合计 使用聚合函数AVG()、MAX()、MIN()、SUM() 聚合函数也可以出现在SELECT子句中， 例PAGE 32【例2.10】 扩展 （1）只检索课程分类为“信息技术”的课程信息 （2）可以在检索结果中显示多个聚合函数的合计 上面的例子是对显示的所有行进行总计，如何对信息进行分组小计呢？,5、使用COMPUTEBY子句 【例】按类检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算每类课程的平均报名人数 从检索结果可以看到： 检索结果中有多个组，每个组有两个结果集 这两个结果集中的内容同上 注意必须使用ORDER BY排序要分组的列 【练习】检索周三上课的课程名称和教师名，并对符合条件的课程门数进行统计 （提示：先完成前一部分，再使用聚合函数COUNT进行统计）,问题,问题 COMPUTE子句是在一个明细行的基础上增加一个总计或小计（有BY时），但如果只想统计每类课程的平均报名人数，该如何处理？,操作数据（续）,1、使用GROUP BY子句 【问题】按课程分类统计每类课程的平均报名人数 与上面例子的区别：只有一个结果集 说明 （1）该子句用于将检索结果按照GROUP BY后指定的列进行分组，该子句写在WHERE子句后面 （2）GROUP BY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列表中出现的列，只能是GROUP BY子句中的列或者包含在聚合函数中。,2、使用HAVING子句 【问题】上面的例子中，如果只统计“信息技术”类课程的平均报名人数 说明HAVING经常用在GROUP BY子句之后，用于限定结果集中的分组 也可以使用WHERE子句 可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。 注意HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列 【问题】检索平均报名人数大于30人的课程类和每类平均报名人数 【练习1】在课程表中，按所开设课程的系部编码分别统计总共报名人数 【练习2】如果问：系部编码为01的系所开设的课程总共有多少学生报名，如何编程实现？,3、使用子查询在一个查询中包含另一个查询 【问题】检索报名人数大于平均报名人数的课程编码、课程名称和报名人数 STEP 1：如何求平均报名人数 STEP 2：完成题目要求,4、使用UNION子句用于将两个或多个检索结果合并成一个结果 【例2.31】从班级表中检索班级名，从系部表中检索系部名，并将这两个检索结果合并起来 使用时注意： （1）所有检索中的列数和列的顺序必须相同 （2）所有检索中按顺序对应列的数据类型必须兼容（相同或者可以明确地转换） 扩展 （1）修改列标题 （2）按新的列标题排序,本节小结,本节小结 SQL语言的组成 SELECT语句的基本格式 介绍了各种单表查询语句,多表查询,第2节 多表查询 如果问：“计算机应用工程系”下属的班级有哪些，应该查询哪些表格？ 这就是多表查询了。 如果在多个表中进行查询，结果会怎样？ USE Xk GO SELECT * FROM Department, Class GO 结果集中，有54行（318），5列（23）。,问题: 笛卡儿积 运算过程？ 在笛卡儿积的结果中，分析存在的问题以及解决方法： （1）两列DepatNo的列值不同 解决方法：加连接条件，成为相等连接 【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：通常对于N个表格的检索，要有N-1个连接条件 （2）出现了相同的列： 解决方法：去掉相同的列，成为自然连接 （3）加入“计算机应用工程系”的条件，成为带有选择条件的连接,【问题】检索“01电子商务”班的学生选修课程的情况，要求显示班级名称、学生学号、学生姓名、课程名称和上课时间 分析 班级名称、学生学号、学生姓名、课程名称、上课时间 这些信息是分别在班级表、学生表、学生选课表和课程表中的，因此需要在多个表中进行查询。同时，必须在WHERE子句中给出连接条件。 注意在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，在SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。 【练习】检索“林斌”同学的选修课信息（课程编号、课程名称、上课时间）,【练习1】查询每个系所开课程的门数，显示系代码、系名称、课程名称、开课门数 【练习2】显示所有系的班级名称，要求计算各系班级数量，显示要求如下：,多表查询,1、非相等连接（比较连接） 连接条件不是等号，而是比较运算符 【例2-36】检索各班可以选择的不是本系开设的选修课程的信息（课本的例子）,2、自连接 自己和自己的连接 【例2-37】检索课程类相同而系部编码不同的课程信息（意味着同一类的课程有多个系部开设），要求显示课程编码、课程名称、课程分类和系部编码 使用别名 【练习】检索学分大于等于2.5的课程信息，显示课程名称、学分、开设该课程的系部名称、报名人数，要求使用别名,外连接,上面介绍的都是内连接，以下是外连接： 【例】检索学生的选课信息，要求显示学生学号、课程编码、课程名称 1、左外连接(left join) 2、右外连接(right join) 3、全连接(full join) 4、交叉连接(cross join)(不带WHERE子句的是两个表的笛卡尔积） 练习：显示各部门及其上级部门,使用EXISTS关键字,如果要解决这样的问题： 【例2-42】检索已经报名选修了课程的学生的学号和姓名 分析采用上面所学的方法能否解决？ 使用EXISTS关键字 测试跟随的子查询的行是否存在 要点 一旦找到，立即停止 找到则返回TRUE，否则返回FALSE，而不是返回一些行,使用NOT EXISTS 【问题】查询没有选修001课程的学生学号和姓名。 select stuno,stuname from student where not exists (select * from stucou where stuno=student.stuno and couno=001),本节小结,多表查询 非相等连接 自连接 使用别名 外连接（左外连接、右外连接、全连接）和交叉连接 使用EXISTS关键字,编辑用户表数据：添加,1、使用 INSERT 语句 INSERT 语句在简单的情况下有如下形式： INSERT INTO 表 (列名列表) values (数据值)。见：例2-43 用INSERT和 SELECT 子查询添加一行或多行指定数据值 见例：2-44 INSERT testTable1 (元素1,元素2) SELECT ColA, ColB FROM testtable 使用 SELECTinto 插入符合某种条件的多行见例：2-45,练习1：使用Inse