使用Transact-SQL语言.ppt

1、Select语句查询,SELECT 字段列表 FROM 数据源 WHERE 条件表达式 GROUP BY 分组表达式 HAVING 搜索表达式 ORDER BY 排序表达式ASC|DESC ,SELECT语句的语法结构为： SELECT 字段列表 FROM 数据源 WHERE 条件表达式 ORDER BY 排序表达式ASC|DESC Compute 聚合函数 by 要分组显示的列名,例如：按类别显示课程信息，并计算每类课程的平均报名人数。,GROUP BY子句在被定义的数据的基础上建立比较小的组，即按照分组表达式对某一列或多列的数据进行分组，将数据值相同的分为一组，并且对每一个组进行聚合函数计

2、算。换句话说，它产生每一组的总体信息。,GROUP BY子句,使用GROUP BY子句时，应该注意以下问题： （1） GROUP BY子句子句不能使用聚合函数。 （2）当select语句中包含有where子句和order by 子句时，group by子句只能放在这两个子句的后面。 （3）必须在group by 子句中列出select选择列表中的数据项。当使用group by子句进行分组时，select语句的列表中所选择的列一定是group by子句后面的分组依据列或聚合函数。,2.1 Transact-SQL概论 2.2 查询与统计数据 2.3 编辑用户表数据,第二章 使用Transact-

3、SQL语言,Transact-SQL语言主要组成部分,数据定义语言（DDL，Data Definition Language） 数据操纵语言（DML，Data Manipularion Language） 数据控制语言（DCL，Data Control Language）,2.1 Transact-SQL概论,数据定义语言（DDL）,数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句，这些语句包括CREATE、ALTER和DROP等语句。 在SQL Server 2000中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等

4、都可以通过使用CREATE、ALTER、DROP等语句来完成。,数据操纵语言（DML）,数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。,数据控制语言（DCL）,数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，,2.2 查询与统计数据,SELECT 语句 用来检索和显示满足特定条件的数据 select 在查询结果中要显示的列的名字 from 要显示数据的表的名字 where 要检索数据的条件 选择所有列 select *,案例2.1 在s

5、tudent表中查询学号为“000000001”同学的学号和姓名。,使用星号或列名,分析： 1. 识别要查询的数据库 XL 2.识别数据库中所需要的表student 3.识别所需的表中的字段stuno、stuname,USE Xk GO SELECT StuNo,StuName FROM Student WHERE StuNo=00000001 GO,案例2.2 从课程表（course）中查询课程类别，要求清除值相同的那些行。,使用distinct消除重复值,分析： 1. 识别要查询的数据库 XL 2.识别数据库中所需要的表Course 3.识别所需的表中的字段Kind,USE Xk GO -

6、消除Kind列值都相同的那些重复行 SELECT DISTINCT Kind FROM Course GO -消除Kind列和Credit列值都相同的那些重复行 SELECT DISTINCT Kind,Credit FROM Course GO,使用top n【percent】仅返回前n行,TOP关键字 与select 语句一起使用的top关键字限制了结果集合中返回的行数。 Select top n percent 字段 From 表名 Where 条件 order by 字段名 Top12 返回结果集的前12行 Top15 percent 返回结果集的前15%,案例2.3 从学生表sude

7、nt中查询所有的信息，要求只显示查询结果的前6行数据。,分析： 1. 识别要查询的数据库 XL 2.识别数据库中所需要的表student 3.识别所需的表中的字段所有列,USE Xk GO SELECT TOP 6 * FROM Student GO,改变查询结果列的标题,用户定义的列标题可以代替默认的列标题， 有三种方法： 1.使用等号“=”将用户定义列标题放在默认列名字之前。 2.用户定义列标题放在默认列标题名字之后。 3.使用AS关键字,案例2.4 查询课程表course中课程编号、课程名称、教师、上课时间、限制选课人数和报名人数，要求结果显示如下：,1. 识别要查询的数据库 XK 2.

8、识别数据库中所需要的表Course 3.识别所需的表中的字段,-方法1，在查询窗口中执行如下SQL语句： USE Xk GO SELECT 课程编码=CouNo,课程名称=CouName,教师=Teacher,上课时间=SchoolTime, 限制选课人数=LimitNum,报名人数=WillNum FROM Course GO,-方法2，在查询窗口中执行如下SQL语句： USE Xk GO SELECT CouNo 课程编号,CouName 课程名称,Teacher 教师, SchoolTime 上课时间,LimitNum 限制选课人数, WillNum 报名人数 FROM Course G

9、O,-方法3，在查询窗口中执行如下SQL语句： USE Xk GO SELECT CouNo AS 课程编号,CouName AS 课程名称, Teacher AS 教师,SchoolTime AS 上课时间, LimitNum AS 限制选课人数, WillNum AS 报名人数 FROM Course GO,使用where限制查询条件,在where子句中，可以包含比较运算符、逻辑运算符、范围运算符。 逻辑运算符 OR 当任何一个指定查找条件是真时返回结果 AND 当所有指定的查找条件是真时返回结果 NOT 否定其后的表达式 范围运算符,案例2.5 在课程表中查询课程类别为“信息技术”而且学

10、分为2的课程信息。,USE Xk GO SELECT * FROM Course WHERE Kind=信息技术 AND Credit=2 GO,表达式作为查询列,在select子句中的选项列表可以为表达式或指定的列的列表，表达式可以是列名、函数或常数的列表。,案例2.6 查询课程表中最小的报名人数、最大的报名人数和平均报名人数。 分析： 可以使用max、min、avg函数 1. 识别要查询的数据库 XK 2.识别数据库中所需要的表Course 3.识别所需的表中的字段willnum,USE Xk GO SELECT 最小的报名人数=MIN(WillNum),最大的报名人数=MAX(WillN

11、um),平均报名人数=AVG(WillNum) FROM Course GO,使用order by子句重新排序查询结果,ORDER BY 子句 在select 语句中使用order by 子句，以指定顺序检索和显示数据。 select 字段 from 指定所在表名 order by 指定要排序的列、相关列号或表达式 asc / desc 指定按递增/递减顺序对指定列的值进 行排序。,案例2.7 查询课程信息、报名人数与限选人数之比。要求查询结果按照报名人数升序排列。 分析： 使用表达式作为查询列 1. 识别要查询的数据库 XK 2.识别数据库中所需要的表Course 3.识别所需的表中的字段w

12、illnum/limitnum,-（1）ORDER BY后面为列名 USE Xk GO SELECT *,WillNum/LimitNum 报名人数与限选人数之比 FROM Course ORDER BY WillNum GO,-（2）ORDER BY后面为中文标题 USE Xk GO SELECT *,WillNum/LimitNum 报名人数与限选人数之比 FROM Course ORDER BY 报名人数与限选人数之比 GO,使用in子句给出列值范围,案例2.7 查询课程编号为“004”、“007”、“013”的课程信息。,USE Xk GO SELECT * FROM Course W

13、HERE CouNo=004 OR CouNo=007 OR CouNo=013 GO,-使用IN关键字的SQL语句如下： USE Xk GO SELECT * FROM Course WHERE CouNo IN(004,007,013) GO,-（1）ORDER BY后面为列名 USE Xk GO SELECT *,WillNum/LimitNum 报名人数与限选人数之比 FROM Course ORDER BY WillNum GO,-（2）ORDER BY后面为中文标题 USE Xk GO SELECT *,WillNum/LimitNum 报名人数与限选人数之比 FROM Cours

14、e ORDER BY 报名人数与限选人数之比 GO,使用like实现模糊查询,串运算符like与通配符,案例2.8 查询课程名以字母D开始的课程信息,USE Xk GO SELECT * FROM Course WHERE CouName LIKE D% GO,USE Xk GO SELECT * FROM Student WHERE StuName LIKE _宝% GO,案例2.9 查询第二个字为“宝”的学生信息,案例2.10 查询不姓“刘”的学生信息,-使用 USE Xk GO SELECT * FROM Student WHERE StuName LIKE 刘% GO,-使用NOT L

15、IKE USE Xk GO SELECT * FROM Student WHERE StuName NOT LIKE 刘% GO,使用is null 查询指定列表未输入值的数据行,IS NULL 和 IS NOT NULL关键字 null是一个未知值，或者是以给还没有提供数据的值。每当null值与任何其他值进行比较时（使用比较运算符），或对null值进行计算时，其结果总是null.,案例2.10 查询课程表中教师姓名未定的课程信息,USE Xk GO SELECT * FROM Course WHERE teacher is null GO,查询某一范围内的信息,查询在某一范围内的信息，要使用

16、where子句限制查询条件，该条件通常是一个逻辑表达式。,案例2.11 查询报名人数少于25人并且多于15人的课程信息,USE Xk GO SELECT * FROM Course WHERE willnum15 and willnum25 GO,使用compute进行计算,使用带select语句的Compute子句，使用聚合函数生成查询结果中的汇总行。Compute by 子句进一步按列汇总结果集。,Select 字段 From 表名 Where 条件 Compute 聚合函数,聚合函数,案例2.12 查询课程表中“信息技术”类课程信息，并计算平均报名人数。,USE Xk GO SELECT

17、 * FROM Course WHERE kind=信息技术 Compute avg(willnum) GO,compute by子句 compute by子句对by后面给出的列进行分组显示，并计算该列的分组小计。 在使用compute by之前必须先使用order by对compute by中指定的列进行排序,Select 字段 From 表名 Where 条件 Order by 要分组显示的列名 Compute 聚合函数 by 要分组显示的列名,案例2.13 按类别显示课程信息，并计算给类课程的平均报名人数。,USE Xk GO SELECT * FROM Course Order by

18、kind Compute avg(willnum) by kind GO,使用group by 分组汇总查询结果,GROUP BY 子句,Group by 子句用聚合函数，把结果集汇总成查询中定义的组。HAVING子句进一步对结果集进行限制，以根据条件产生数据。 Select 字段名 From 表名 Where 条件 Group by all 指出要分组的select语句的结果集的列名或表达式。 having 是产生结果集所依据的条件表达式,案例2.14 按课程类别分组统计各类课程的平均报名人数。,USE Xk GO SELECT kind as 课程类别 ，avg(willnum) as 每

19、类平均 报名人数 FROM Course Group by kind GO,注意： 使用group by 子句时，在select子句中出现的列名或者出现在聚合函数中，或者出现在group by子句的后面，否则，在执行语句时就会出错。,Having子句： Having 子句用于限定对组或者聚合函数的查询条件。该子句常用在group by 子句之后，在查询结果分组之后对组判断是否满足条件。如果在分组之前判断是否满足查询条件则使用where子句。,案例2.15 查询平均报名人数大于25人的课程类别和每类的平均报名人数。,course 表,USE Xk GO SELECT kind as 课程类别，a

20、vg(willnum) as 每类平均报名人数 FROM Course Where avg（willnum） 25 Group by kind GO,使用子查询,子查询可被定义作返回单值的select查询。它是查询中包含的另一个查询，可以使用子查询代替表达式。 子查询只能返回一列数据，有时只能返回单个值。,子查询可被定义作返回单值的select查询。子查询是嵌套在selectinsertupdate或delete。子查询必须包括在圆括号内，并且不能用order by 和compute by 的子句。,SELECT 字段名 FROM 表1名 WHERE 条件名=(SELECT字段名 FROM 表

21、2名 WHERE 条件名=),案例2.16 查询报名人数大于平均报名人数的课程信息,course 表,USE Xk GO SELECT * FROM Course WHERE WillNum(SELECT AVG(WillNum) FROM Course) GO,带有EXIST子句的子查询 在where 子句中可以使用exist子句，它用于测试跟随的子查询中行是否存在，当用EXIST子句时，总是返回用TRUE或者FALSE值表示的数据。,案例2.17 查询已报名选修课程的学生信息，要求显示学号和姓名。,student表,stucou表,USE Xk GO SELECT StuNo,StuNam

22、e FROM Student WHERE EXISTS (SELECT StuNo FROM StuCou WHERE StuNo=Student.StuNO) GO,连接 Inner join 内连接 Outer join 外连接 Cross join 交叉连接 Self join 自连接 Full join 全连接 当两个表已被连接时，他们必须共享一个公共键，这个键定义了表中的行如何彼此对应的。,内连接 在内连接里，只有那些值满足公共列中连接条件的行被显示，两个表中那些不满足条件的行将不被显示。 Select 字段 From 表1名 inner Join 表2名 On 是两个表中公共列中的

23、某行相等连接两个表的行。,案例2.20 查询学生的信息，显示信息包括学生基本信息和班级名称。,Class 表,student表,USE Xk GO SELECT Student.*, ClassName FROM Class JOIN Student ON Class.ClassNo=Student.ClassNo GO,stucou表,案例2.21 查询学生选课信息，要求显示姓名、课程名称、志愿号，按姓名和志愿号排序。,student表,course 表,USE Xk GO SELECT StuName,CouName,WillOrder FROM StuCou JOIN Student O

24、N StuCou.StuNo=Student.StuNo JOIN Course ON StuCou.CouNo=Course.CouNo ORDER BY StuName, WillOrder GO,stucou表,案例2.22 查询学生报名“计算机应用工程系”开设的选修课程情况，显示信息包括学生姓名、课程名称和授课教师。,course 表,student表,department表,USE Xk GO SELECT StuName,CouName,Teacher FROM StuCou,Student,Course,Department WHERE StuCou.StuNo=Student.

25、StuNo AND StuCou.CouNo=Course.CouNo AND Course.DepartNo=Department.DepartNo AND DepartName LIKE 计算机应用工程系 GO,外连接,如果结果集包含来自一个表的所有行和另一个表中的匹配行，那么这种连接称为外连接。 Select 字段 From 表1名 left/right outer join 表2名 On 表1.ref=表2.ref 其中显示第一个表中的所有行使用“left”,显示第二个表中的所有行使用“right”.,stucou表,案例2.23 使用右外连接查询学生的已报名和未报名的课程信息（课程编

26、号、课程名称、学号）。,course 表,USE Xk GO SELECT Course.CouNo,CouName,StuNo FROM Course RIGHT JOIN StuCou ON Course.CouNo=StuCou.CouNo GO,自连接 如果一个表中的一行与同一个表中的其他行进行相关联，那么这种连接被称为自连接。 由于比较两次使用了同一个表，要先将一个表定义为两个不同的名字，然后在from子句中使用这两个名字，在where子句中需要写出一个连接条件。 注意：在select子句中对列的引用形式为： 所定义的表名.列名,案例2.24 查询课程类别相同但开课系部不同的课程信息

27、，要求显示课程编号、课程名称、课程类别和系部编号，并按照课程编号升序排序查询结果。,course 表,USE Xk GO SELECT DISTINCT C1.CouNo AS 课程编号,C1.CouName AS 课程名称, C1.Kind AS 课程类别,C1.DepartNo AS 系部编号 FROM Course C1,Course C2 WHERE C1.Kind=C2.Kind AND C1.DepartNoC2.DepartNo ORDER BY 课程编号 GO,交叉连接 使用关键字CROSS包含一个以上的表的连接称为交叉连接。这种连接输出叫迪卡尔积。两个表的交叉连接中，第一表中

28、的每一行 与第二个表中的每一行进行连接。结果集当中的行数是第一个表中的行数乘以第二个表中的行数。,全连接 为了包含两个表中都不匹配的那些数据行，可以使用全外连接，完成左外连接和右外连接的操作，包括了左表和右表中所有不满足条件的数据行。,2.3 编辑用户表数据,Insert 语句：必须向数据库添加数据，以维护与机构有关的最新信息和机构处理的事务。 Insert into 要插入行的表名任选参数 Values(values-list) Values ,指出在表的列中要插入的数据值 values-list ，是要作为标的行出入的列的值列表。,使用INSERT语句,2.3 .1 向用户表数据数据,案例

29、2.25 向department表中输入3行数据。,USE Xk GO INSERT Department VALUES (11,数学系) INSER T Department(DepartName,DepartNo) VALUES (物理系,12) INSERT Department(DepartNo,DepartName) VALUES (13,化学系) GO SELECT * FROM Department GO,使用insert与Select 子句,使用insert语句每一次只能输入一行数据。Insert与select语句配合使用可以将表中的数据行输入到表中，并且可以一次输入多行数据。

30、,案例2.25 向department表中输入3行数据。,USE Xk GO Create Table Department1 (DepartNo nvarchar(2) not null, DepartName nvarchar(30) not null) GO USE Xk GO INSERT Department1 SELECT * FROM Department GO,案例2.26 将department表中的数据行输入到department1中。,SELECT INTO 语句,一个带有INTO子句的SELECT语句用来把结果集存储到不带有数据定义过程的一个新表中。,Select 旧表的字段 into 新表名 From 旧表名 Where 是在新表中包含行的条件,案例2.25 向department表中输入3行数据。,USE Xk GO SELECT * INTO Department2 FROM Department GO,案例2.27 将department表中的数据行输入到department2中。,UPDATE 语句更新行 update语句：如果用户、客户、事务或机构要维护的其他数据的规格说明发生变化，数据库中的数据就需要修改。 UPDATE table-name SET column-name=value,column-name=val