使用Transact-SQL语言.ppt

Select语句查询,SELECT 字段列表FROM 数据源WHERE 条件表达式 GROUP BY 分组表达式 HAVING 搜索表达式 ORDER BY 排序表达式ASC|DESC,SELECT语句的语法结构为：SELECT 字段列表FROM 数据源WHERE 条件表达式 ORDER BY 排序表达式ASC|DESC Compute 聚合函数 by 要分组显示的列名,例如：按类别显示课程信息，并计算每类课程的平均报名人数。,GROUP BY子句在被定义的数据的基础上建立比较小的组，即按照分组表达式对某一列或多列的数据进行分组，将数据值相同的分为一组，并且对每一个组进行聚合函数计算。换句话说，它产生每一组的总体信息。,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-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中，数据库对象包括表、视图、触发器、存储过程、规则、缺省、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE、ALTER、DROP等语句来完成。,数据操纵语言（DML）,数据操纵语言是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。,数据控制语言（DCL）,数据控制语言（DCL）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，,2.2 查询与统计数据,SELECT 语句用来检索和显示满足特定条件的数据 select 在查询结果中要显示的列的名字 from 要显示数据的表的名字 where 要检索数据的条件 选择所有列 select*,案例2.1 在student表中查询学号为“000000001”同学的学号和姓名。,使用星号或列名,分析：1.识别要查询的数据库 XL 2.识别数据库中所需要的表student 3.识别所需的表中的字段stuno、stuname,USE XkGOSELECT StuNo,StuNameFROM StudentWHERE StuNo=00000001GO,案例2.2 从课程表（course）中查询课程类别，要求清除值相同的那些行。,使用distinct消除重复值,分析：1.识别要查询的数据库 XL 2.识别数据库中所需要的表Course 3.识别所需的表中的字段Kind,USE XkGO-消除Kind列值都相同的那些重复行SELECT DISTINCT KindFROM Course GO-消除Kind列和Credit列值都相同的那些重复行SELECT DISTINCT Kind,CreditFROM Course GO,使用top n【percent】仅返回前n行,TOP关键字 与select 语句一起使用的top关键字限制了结果集合中返回的行数。Select top n percent 字段From 表名Where 条件order by 字段名Top12 返回结果集的前12行Top15 percent 返回结果集的前15%,案例2.3 从学生表sudent中查询所有的信息，要求只显示查询结果的前6行数据。,分析：1.识别要查询的数据库 XL 2.识别数据库中所需要的表student 3.识别所需的表中的字段所有列,USE XkGOSELECT TOP 6*FROM StudentGO,改变查询结果列的标题,用户定义的列标题可以代替默认的列标题，有三种方法：1.使用等号“=”将用户定义列标题放在默认列名字之前。2.用户定义列标题放在默认列标题名字之后。3.使用AS关键字,案例2.4 查询课程表course中课程编号、课程名称、教师、上课时间、限制选课人数和报名人数，要求结果显示如下：,1.识别要查询的数据库 XK 2.识别数据库中所需要的表Course 3.识别所需的表中的字段,-方法1，在查询窗口中执行如下SQL语句：USE XkGOSELECT 课程编码=CouNo,课程名称=CouName,教师=Teacher,上课时间=SchoolTime,限制选课人数=LimitNum,报名人数=WillNumFROM CourseGO,-方法2，在查询窗口中执行如下SQL语句：USE XkGOSELECT CouNo 课程编号,CouName 课程名称,Teacher 教师,SchoolTime 上课时间,LimitNum 限制选课人数,WillNum 报名人数FROM CourseGO,-方法3，在查询窗口中执行如下SQL语句：USE XkGOSELECT CouNo AS 课程编号,CouName AS 课程名称,Teacher AS 教师,SchoolTime AS 上课时间,LimitNum AS 限制选课人数,WillNum AS 报名人数FROM CourseGO,使用where限制查询条件,在where子句中，可以包含比较运算符、逻辑运算符、范围运算符。逻辑运算符OR 当任何一个指定查找条件是真时返回结果AND 当所有指定的查找条件是真时返回结果NOT 否定其后的表达式范围运算符,案例2.5 在课程表中查询课程类别为“信息技术”而且学分为2的课程信息。,USE XkGOSELECT*FROM CourseWHERE Kind=信息技术 AND Credit=2GO,表达式作为查询列,在select子句中的选项列表可以为表达式或指定的列的列表，表达式可以是列名、函数或常数的列表。,案例2.6 查询课程表中最小的报名人数、最大的报名人数和平均报名人数。分析：可以使用max、min、avg函数 1.识别要查询的数据库 XK 2.识别数据库中所需要的表Course 3.识别所需的表中的字段willnum,USE XkGOSELECT 最小的报名人数=MIN(WillNum),最大的报名人数=MAX(WillNum),平均报名人数=AVG(WillNum)FROM CourseGO,使用order by子句重新排序查询结果,ORDER BY 子句 在select 语句中使用order by 子句，以指定顺序检索和显示数据。select 字段 from 指定所在表名 order by 指定要排序的列、相关列号或表达式 asc/desc 指定按递增/递减顺序对指定列的值进 行排序。,案例2.7 查询课程信息、报名人数与限选人数之比。要求查询结果按照报名人数升序排列。分析：使用表达式作为查询列 1.识别要查询的数据库 XK 2.识别数据库中所需要的表Course 3.识别所需的表中的字段willnum/limitnum,-（1）ORDER BY后面为列名USE XkGOSELECT*,WillNum/LimitNum 报名人数与限选人数之比FROM Course ORDER BY WillNumGO,-（2）ORDER BY后面为中文标题USE XkGOSELECT*,WillNum/LimitNum 报名人数与限选人数之比FROM Course ORDER BY 报名人数与限选人数之比GO,使用in子句给出列值范围,案例2.7 查询课程编号为“004”、“007”、“013”的课程信息。,USE XkGOSELECT*FROM CourseWHERE CouNo=004 OR CouNo=007 OR CouNo=013GO,-使用IN关键字的SQL语句如下：USE XkGOSELECT*FROM CourseWHERE CouNo IN(004,007,013)GO,-（1）ORDER BY后面为列名USE XkGOSELECT*,WillNum/LimitNum 报名人数与限选人数之比FROM Course ORDER BY WillNumGO,-（2）ORDER BY后面为中文标题USE XkGOSELECT*,WillNum/LimitNum 报名人数与限选人数之比FROM Course ORDER BY 报名人数与限选人数之比GO,使用like实现模糊查询,串运算符like与通配符,案例2.8 查询课程名以字母D开始的课程信息,USE XkGOSELECT*FROM CourseWHERE CouName LIKE D%GO,USE XkGOSELECT*FROM StudentWHERE StuName LIKE _宝%GO,案例2.9 查询第二个字为“宝”的学生信息,案例2.10 查询不姓“刘”的学生信息,-使用USE XkGOSELECT*FROM StudentWHERE StuName LIKE 刘%GO,-使用NOT LIKEUSE XkGOSELECT*FROM StudentWHERE StuName NOT LIKE 刘%GO,使用is null 查询指定列表未输入值的数据行,IS NULL 和 IS NOT NULL关键字 null是一个未知值，或者是以给还没有提供数据的值。每当null值与任何其他值进行比较时（使用比较运算符），或对null值进行计算时，其结果总是null.,案例2.10 查询课程表中教师姓名未定的课程信息,USE XkGOSELECT*FROM CourseWHERE teacher is nullGO,查询某一范围内的信息,查询在某一范围内的信息，要使用where子句限制查询条件，该条件通常是一个逻辑表达式。,案例2.11 查询报名人数少于25人并且多于15人的课程信息,USE XkGOSELECT*FROM CourseWHERE willnum15 and willnum25GO,使用compute进行计算,使用带select语句的Compute子句，使用聚合函数生成查询结果中的汇总行。Compute by 子句进一步按列汇总结果集。,Select 字段From 表名Where 条件Compute 聚合函数,聚合函数,案例2.12 查询课程表中“信息技术”类课程信息，并计算平均报名人数。,USE XkGOSELECT*FROM CourseWHERE 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 XkGOSELECT*FROM CourseOrder by kindCompute avg(willnum)by kindGO,使用group by 分组汇总查询结果,GROUP BY 子句,Group by 子句用聚合函数，把结果集汇总成查询中定义的组。HAVING子句进一步对结果集进行限制，以根据条件产生数据。Select 字段名From 表名Where 条件Group by all 指出要分组的select语句的结果集的列名或表达式。having 是产生结果集所依据的条件表达式,案例2.14 按课程类别分组统计各类课程的平均报名人数。,USE XkGOSELECT kind as 课程类别，avg(willnum)as 每类平均 报名人数FROM CourseGroup by kindGO,注意：使用group by 子句时，在select子句中出现的列名或者出现在聚合函数中，或者出现在group by子句的后面，否则，在执行语句时就会出错。,Having子句：Having 子句用于限定对组或者聚合函数的查询条件。该子句常用在group by 子句之后，在查询结果分组之后对组判断是否满足条件。如果在分组之前判断是否满足查询条件则使用where子句。,案例2.15 查询平均报名人数大于25人的课程类别和每类的平均报名人数。,course 表,USE XkGOSELECT kind as 课程类别，avg(willnum)as 每类平均报名人数FROM CourseWhere avg（willnum）25Group by kindGO,使用子查询,子查询可被定义作返回单值的select查询。它是查询中包含的另一个查询，可以使用子查询代替表达式。子查询只能返回一列数据，有时只能返回单个值。,子查询可被定义作返回单值的select查询。子查询是嵌套在selectinsertupdate或delete。子查询必须包括在圆括号内，并且不能用order by 和compute by 的子句。,SELECT 字段名FROM 表1名WHERE 条件名=(SELECT字段名 FROM 表2名 WHERE 条件名=),案例2.16 查询报名人数大于平均报名人数的课程信息,course 表,USE XkGOSELECT*FROM CourseWHERE WillNum(SELECT AVG(WillNum)FROM Course)GO,带有EXIST子句的子查询 在where 子句中可以使用exist子句，它用于测试跟随的子查询中行是否存在，当用EXIST子句时，总是返回用TRUE或者FALSE值表示的数据。,案例2.17 查询已报名选修课程的学生信息，要求显示学号和姓名。,student表,stucou表,USE XkGOSELECT StuNo,StuNameFROM StudentWHERE 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 是两个表中公共列中的某行相等连接两个表的行。,案例2.20 查询学生的信息，显示信息包括学生基本信息和班级名称。,Class 表,student表,USE XkGOSELECT Student.*,ClassNameFROM Class JOIN StudentON Class.ClassNo=Student.ClassNoGO,stucou表,案例2.21 查询学生选课信息，要求显示姓名、课程名称、志愿号，按姓名和志愿号排序。,student表,course 表,USE XkGOSELECT StuName,CouName,WillOrder FROM StuCouJOIN Student ON StuCou.StuNo=Student.StuNoJOIN Course ON StuCou.CouNo=Course.CouNoORDER BY StuName,WillOrderGO,stucou表,案例2.22 查询学生报名“计算机应用工程系”开设的选修课程情况，显示信息包括学生姓名、课程名称和授课教师。,course 表,student表,department表,USE XkGOSELECT StuName,CouName,TeacherFROM StuCou,Student,Course,DepartmentWHERE StuCou.StuNo=Student.StuNo AND StuCou.CouNo=Course.CouNo AND Course.DepartNo=Department.DepartNoAND DepartName LIKE 计算机应用工程系GO,外连接,如果结果集包含来自一个表的所有行和另一个表中的匹配行，那么这种连接称为外连接。Select 字段From 表1名 left/right outer join 表2名On 表1.ref=表2.ref 其中显示第一个表中的所有行使用“left”,显示第二个表中的所有行使用“right”.,stucou表,案例2.23 使用右外连接查询学生的已报名和未报名的课程信息（课程编号、课程名称、学号）。,course 表,USE XkGOSELECT Course.CouNo,CouName,StuNoFROM Course RIGHT JOIN StuCouON Course.CouNo=StuCou.CouNoGO,自连接 如果一个表中的一行与同一个表中的其他行进行相关联，那么这种连接被称为自连接。由于比较两次使用了同一个表，要先将一个表定义为两个不同的名字，然后在from子句中使用这两个名字，在where子句中需要写出一个连接条件。注意：在select子句中对列的引用形式为：所定义的表名.列名,案例2.24 查询课程类别相同但开课系部不同的课程信息，要求显示课程编号、课程名称、课程类别和系部编号，并按照课程编号升序排序查询结果。,course 表,USE XkGOSELECT DISTINCT C1.CouNo AS 课程编号,C1.CouName AS 课程名称,C1.Kind AS 课程类别,C1.DepartNo AS 系部编号FROM Course C1,Course C2WHERE C1.Kind=C2.Kind AND C1.DepartNoC2.DepartNoORDER BY 课程编号GO,交叉连接 使用关键字CROSS包含一个以上的表的连接称为交叉连接。这种连接输出叫迪卡尔积。两个表的交叉连接中，第一表中的每一行 与第二个表中的每一行进行连接。结果集当中的行数是第一个表中的行数乘以第二个表中的行数。,全连接 为了包含两个表中都不匹配的那些数据行，可以使用全外连接，完成左外连接和右外连接的操作，包括了左表和右表中所有不满足条件的数据行。,2.3 编辑用户表数据,Insert 语句：必须向数据库添加数据，以维护与机构有关的最新信息和机构处理的事务。Insert into 要插入行的表名任选参数Values(values-list)Values,指出在表的列中要插入的数据值values-list，是要作为标的行出入的列的值列表。,使用INSERT语句,2.3.1 向用户表数据数据,案例2.25 向department表中输入3行数据。,USE XkGOINSERT DepartmentVALUES(11,数学系)INSER T Department(DepartName,DepartNo)VALUES(物理系,12)INSERT Department(DepartNo,DepartName)VALUES(13,化学系)GOSELECT*FROM DepartmentGO,使用insert与Select 子句,使用insert语句每一次只能输入一行数据。Insert与select语句配合使用可以将表中的数据行输入到表中，并且可以一次输入多行数据。,案例2.25 向department表中输入3行数据。,USE XkGOCreate Table Department1(DepartNo nvarchar(2)not null,DepartName nvarchar(30)not null)GOUSE XkGOINSERT Department1 SELECT*FROM DepartmentGO,案例2.26 将department表中的数据行输入到department1中。,SELECT INTO 语句,一个带有INTO子句的SELECT语句用来把结果集存储到不带有数据定义过程的一个新表中。,Select 旧表的字段 into 新表名From 旧表名Where 是在新表中包含行的条件,案例2.25 向department表中输入3行数据。,USE XkGOSELECT*INTO Department2FROM DepartmentGO,案例2.27 将department表中的数据行输入到department2中。,UPDATE 语句更新行 update语句：如果用户、客户、事务或机构要维护的其他数据的规格说明发生变化，数据库中的数据就需要修改。UPDATE table-nameSET column-name=value,column-name=valueFROM table-nameWHERE conditiontable-name，指定要修改的表名 column-name，指出所指表中要修改的列Value，指出要更新的表的列应取的值table-name，指出update使用的表Condition，指出需要更新的行。,2.3.2 修改用户表数据,案例2.28 需要将“电子出版概论”课程的上课时间修改为“周二晚”,course 表,USE XkGOUPDATE CourseSET SchoolTime=周二晚WHERE CouName=电子出版概论GO,案例2.29 现需要“01数据库”班所有学生的选课密码初始化为“123456”,student表,Class 表,USE XkGOUPDATE StudentSET Pwd=123456FROM Student,ClassWHERE ClassName=01数据库 AND Class.ClassNo=Student.ClassNoGO,Delete语句删除行Delete from table_name 表名from table(s)是用于设定删除条件所需的表名 where condition 要删除行的条件,Truncate table 语句删除所有行,Truncate table table_name 是剪切或删除这个表中的所有行的表名,2.3.3 删除用户表数据,案例2.30 学号为“00000005”的同学因故取消课程编号为“017”的选修课。,stucou表,course 表,USE XkGODELETE StuCouWHERE StuNo=00000005 AND CouNo=017GOUPDATE CourseSET WillNum=WillNum-1WHERE CouNo=017GO,案例2.31 学号为“00000005”的同学因故取消”中餐菜肴制作“。,stucou表,course 表,USE XkGODELETE StuCouFROM StuCou,CourseWHERE StuCou.StuNo=00000005 AND CouName=中餐菜肴制作 AND StuCou.CouNo=Course.CouNoGOUPDATE Course SET WillNum=WillNum-1 WHERE CouName=中餐菜肴制作,