Transact-SQL语言概述.ppt

《Transact-SQL语言概述.ppt》由会员分享，可在线阅读，更多相关《Transact-SQL语言概述.ppt（81页珍藏版）》请在三一办公上搜索。

1、第2章 Transact-SQL语言,学习目标,了解T-SQL语言的起源、组成熟练使用SELECT、INSERT、UPDATE、DELETE语句,Transact-SQL语言概述,问题 我们需要对数据进行哪些维护操作？如何对数据进行查询、统计?结构化查询语言（Structured Query Language）是一种用于存取和查询数据，更新并管理关系数据库系统的数据库查询和编程语言。这种语言的语法结构类似于英语，易学易用，书写随意。ANSI SQL 92 Transact-SQL,1、数据定义语言DDL 用来定义和管理数据库中的对象。DDL主要的语句有：CREATE TABLE/*创建表*/A

2、LTER TABLE/*修改表的属性*/DROP TABLE/*删除表*/,Transact-SQL语言的组成,2、数据操作语言DML 用来操作数据库中的对象和数据，是T-SQL中最常用的部分。DML主要的语句有：SELECT/*从一个表或多个表中检索数据*/DELETE/*从表中删除数据*/INSERT/*向一个表中添加数据*/UPDATE/*修改表中已有的数据*/,3、数据控制语言DCL 用来控制用户对数据库对象操作的权限。主要的命令有：GRANT/*授予权限*/REVOKE/*回收所授予的权限*/,第1节 操作数据单表查询,切换数据库 USE Xk GO,SELECT子句SELECT子句

3、主要用于检索数据，其基本格式为：SELECT 选择列表（指示应该返回的数据）FROM 表的列表（提供数据的表或视图的名称）WHEAR 检索的条件,【例】从Student表中检索学号为00000001的学生的姓名（StuName），要求显示学生的学号和姓名。Use xk Go Select StuNo,StuName from Student where StuNo=00000001 GO【练习】要求检索系部编号为01的班级信息，要求显示班级编号和班级名称。,1、使用*和列名 在选择表中使用星号（*），则从FORM子句中指定的表或视图中检索并返回所有列。【例】从课程表中检索所有的信息。Use x

4、k Go Select*from Course Go【练习】从学生表中检索学生所在班的班级编码。,2、使用DISTINCT消除重复值 在Select之后使用Distinct关键字，会消除指定列的值都相同的那些行。【例】从课程表中检索课程分类，要求清除值相同的那些行。/消除Kind列值都相同的那些重复行 Select Distinct Kind from Course/消除Kind和Credit列值都相同的那些重复行 Select Distinct Kind,Credit from Course,3、使用TOP n PERCENT仅返回前n行 使用TOP，可以从结果集中仅返回前n行。如果指定了P

5、ERCENT，则仅返回前n%行，此时n必须介于0100之间。【例】从学生表中检索所有的信息，要求只显示前6行数据。Select top 6*from Student/返回6行 Select top 6 percent*from Student/返回180611,4、修改检索结果中列的标题修改检索列标题有三种方式：（1）将要显示的列标题用单引号括起来后接等号（），后接要检索的列名；（2）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用空格隔开；（3）将要显示的列标题用单引号括起来后，写在列名后面，两者之间使用AS关键字。,【问题】检索课程表中课程编码、课程名称、教师、上课时间、限制选

6、课人数和报名人数，并修改列标题。SELECT 课程编码=CouNo,课程名称=CouName,教师=Teacher,上课时间=SchoolTime,限制选课人数=LimitNum,报名人数=WIllNum FROM Course【练习】使用其他两种方法,5、在查询结果中显示字符串 将要增加的字符串用单引号括起来，然后和列的名字写在一起，中间用逗号隔开。【问题】检索课程表的信息，要求给出检索结果为：课程名称 课程编码 SQL Server实用技术 课程编码为：001 SELECT 课程名称=CouName,课程编码为:,课程编码=CouNo FROM Course,6、使用WHERE限制检索的条

7、件 使用WHERE子句限制检索的范围，通常情况下，必须定义一个或多个条件限制检索选择的数据行。【例】检索上“Linux操作系统”课程的教师名。SELECT 教师=Teacher FROM Course WHERE CouName=Linux操作系统,7、表达式作为SELECT语句中的列 SELECT子句中的选项列表可以是要指定的表达式或列的列表，表达式可以是列名、函数或常数的列表。【例】要求检索课程表的课程信息，并显示报名人数和限制选课人数之比。SELECT*,WillNum/LimitNum FROM Course,【练习】检索课程表中最小的报名人数、最大的报名人数以及平均报名人数。使用聚合

8、函数AVG()、MAX()、MIN()、SUM()。聚合函数也可以出现在SELECT子句中。SELECT 最小的报名人数=MIN(WillNum),最大的报名人数=MAX(WillNum),平均报名人数=AVG(WillNum)FROM Course,8、使用ORDER BY子句重新排列检索结果 升序：使用ASC关键字或者省略。降序：使用DESC关键字。也可以在ORDER BY子句中指定多个列，检索结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2行排序 ORDER BY要写在WHERE子句的后面。,【例】检索课程表的教师名、课程号、课程名，要求检索结果首先按教师名降序排列，教师

9、名相同时，则按课程号的升序排列。SELECT Teacher AS 教师名,CouNo AS 课程号,CouName AS 课程名 FROM Course ORDER BY Teacher DESC,CouNo,或者：SELECT Teacher AS 教师名,CouNo AS 课程号,CouName AS 课程名 FROM Course ORDER BY 教师名 DESC,课程号,9、使用IN（NOT IN）关键字【例】检索课程编码为001004013的课程名称方法一：使用逻辑运算符OR；SELECT CouName AS 课程名称 FROM Course WHERE CouNo=004 O

10、R CouNo=007 OR CouNo=013,方法二：使用IN关键字（比使用逻辑运算符更为简单）。SELECT CouName AS 课程名称 FROM Course WHERE CouNo IN(004,007,013)【练习】检索课程号不为004,007,013的课程号和课程名称。,10、使用LIKE（NOT LIKE）关键字 检索与给定的某些字符串相匹配的数据可以使用LIKE关键字。LIKE与字符串相匹配，字符串表达式被定义为字符串和通配符。,SQL的4个通配符：%百分号，匹配包括0个或多个字符的字符串。_下划线，匹配任何一个字符。排列通配符，匹配任何在范围内的单个字符，例如：m-p

11、匹配的是m、n、o、p单个字符。不在范围之内的字符，匹配任何不在范围内的单个字符，例如：m-p、mnop匹配的是除了m、n、o、p之外的任何字符。注：通配符和字符串要括在单引号中。,【练习】解释下面的通配符表示的意义 LIKED%匹配以字母D开始的字符串，LIKE制作匹配的是以“制作”两字结尾的字符串；LIKE-宝匹配的是第2个字为“宝”的字符串。如要查找通配符本身，需要将它们用方括号括起来。LIKE表示要匹配“”,LIKE5%表示要匹配“5”。,【问题】检索以“制作”两字结尾的课程名（使用LIKE）SELECT*FROM Course WHERE CouName LIKE%制作【问题】检索不

12、姓“刘”的学生信息（使用NOT LIKE）SELECT*FROM Student WHERE StuName LIKE 刘%OR SELECT*FROM Student WHERE StuName NOT LIKE 刘%,11、使用IS NULL关键字检索列中没有赋值的行【问题】检索课程表中教师未定的课程名称和教师名。SELECT 课程名称=CouName,教师=Teacher FROM Course WHERE Teacher IS NULL,12、使用BETWEENAND（NOT BETWEENAND）关键字用于检索在某一特定范围内的信息【问题】检索报名人数大于等于30并且小于等于40的课

13、程信息，要求显示课程名称和报名人数（使用BETWEENAND）；【注意】WHERE WillNum BETWEEN 30 AND 40包括30和40这两个值。,【问题】对上例使用NOT BETWEENAND 也可以在WHERE字句中使用前面讲过的逻辑运算符检索某一范围内的信息。【练习】检索报名人数比限制选课人数多一倍以及一倍以上的课程信息，要求显示课程名称、报名人数和限制选课人数之比，并按该比例的降续排列。,13、使用COMPUTE子句计算总计或进行分组小计【问题】检索课程表中课程分类为“信息技术”的课程编码、课程名称、课程分类、报名人数，并计算平均报名人数。SELECT CouNo AS 课

14、程编码,CouName AS 课程名称,Kind AS 课程分类,WillNum AS 报名人数 FROM Course WHERE Kind=信息技术 COMPUTE AVG(WillNum),【说明】总计值或小计值作为附加新行出现在检索结果中。该子句用在WHERE子句之后。从检索结果可以看到：检索结果中有两个结果集。（1）包含选择的列的所有明细行；（2）包含COMPUTE子句中所指定的聚合函数的合计。,14、使用COMPUTE BY子句 使用COMPUTE BY对BY后面给出的列进行分组显示，并计算该列的分组小计。使用COMPUTE BY必须使用ORDER BY对COMPUTE BY中BY

15、指定的列进行排序。,【例】按类检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算每类课程的平均报名人数。SELECT CouNo AS 课程编码,CouName AS 课程名称,Kind AS 课程分类,WillNum AS 报名人数FROM Course ORDER BY KindCOMPUTE AVG(WillNum)BY Kind,从检索结果可以看到：检索结果中有多个组，每个组有两个结果集；这两个结果集中的内容同上。【注意】必须使用ORDER BY排序要分组的列。【练习】检索周二上课的课程名称和教师名，并对符合条件的课程门数进行统计。（提示：先完成前一部分，再使用聚合函数COU

16、NT进行统计）,问题 COMPUTE子句是在一个明细行的基础上增加一个总计或小计（有BY时），但如果只想统计每类课程的平均报名人数，该如何处理？,15、使用GROUP BY子句【问题】按课程分类统计每类课程的平均报名人数 SELECT Kind AS 课程分类,AVG(WillNum)AS 每类平均报名人数 FROM Course GROUP BY Kind,与上面例子的区别：只有一个结果集说明：（1）该子句用于将检索结果按照GROUP BY后指定的列进行分组，该子句写在WHERE子句后面。（2）GROUP BY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列

17、表中出现的列，只能是GROUP BY子句中的列或者包含在聚合函数中。,16、使用HAVING子句【问题】检索“信息技术”类课程的平均报名人数。SELECT Kind AS 课程分类,AVG(WillNum)AS 每类平均报名人数 FROM Course GROUP BY Kind HAVING Kind=信息技术,说明：1.HAVING经常用在GROUP BY子句之后，用于限定结果集中的分组。2.也可以使用WHERE子句。3.可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。【注意】HAVING子句中的列只能是GROUP BY子句中或者聚合函数中的列。,【问题】检索

18、平均报名人数大于30人的课程分类和每类平均报名人数。SELECT Kind AS 课程分类,AVG(WillNum)AS 每类平均报名人数 FROM Course WHERE AVG(WillNum)25 GROUP BY Kind,OR SELECT Kind AS 课程分类,AVG(WillNum)AS 每类平均报名人数 FROM Course GROUP BY Kind HAVING AVG(WillNum)25【练习】在课程表中，按所开设课程的系部编码分别统计总共报名人数。,17、使用子查询在一个查询中包含另一个查询【问题】检索报名人数大于平均报名人数的课程编码、课程名称和报名人数。S

19、TEP 1：如何求平均报名人数 STEP 2：完成题目要求 SELECT 课程编码=CouNo,课程名=CouName,报名人数=WillNum FROM Course WHERE WillNum(SELECT AVG(WillNum)FROM Course),18、使用UNION子句用于将两个或多个检索结果合并成一个结果 UNION用于将两个或多个检索结果合并成一个结果。使用时注意：（1）所有检索中的列数和列的顺序必须相同；（2）所有检索中按顺序对应列的数据类型必须兼容（相同或者可以明确地转换）。,【问题】从班级表中检索班级名，从系部表中检索系部名，并将这两个检索结果合并起来。SELECT

20、ClassName FROM Class UNION SELECT DepartName FROM Department,【扩展】（1）修改列标题（2）按新的列标题排序 SELECT ClassName AS 所有班名和所有系名 FROM Class UNION SELECT DepartName FROM Department,本节小结,本节小结 SQL语言的组成 SELECT语句的基本格式 介绍了各种单表查询语句,多表查询,如果问：“计算机应用工程系”下属的班级有哪些，应该查询哪些表格？这就是多表查询了。,如果在多个表中进行查询，结果会怎样？USE Xk GO SELECT*FROM De

21、partment,Class GO结果集中，有54行（318），5列（23）。,【问题】笛卡儿积 运算过程？在笛卡儿积的结果中，分析存在的问题以及解决方法：（1）两列DepatNo的列值不同 解决方法：加连接条件，成为相等连接【注】为了避免笛卡儿积，必须在WHERE子句中给出表格的连接条件：通常对于N个表格的检索，要有N-1个连接条件。,（2）出现了相同的列：解决方法：去掉相同的列，成为自然连接。（3）加入“计算机应用工程系”的条件，成为带有选择条件 的连接。,【问题】检索“01电子商务”班的学生选修课程的情况，要求显示班级名称、学生学号、学生姓名、课程名称和上课时间。【分析】班级名称、学生学

22、号、学生姓名、课程名称、上课时间 这些信息是分别在班级表、学生表、学生选课表和课程表中的，因此需要在多个表中进行查询。同时，必须在WHERE子句中给出连接条件。,SELECT ClassName AS 班级,StuNo AS 学号,StuName AS 姓名,CouName AS 课程名,SchoolTime AS 上课时间 FROM Class,Student,Course,StuCou WHERE Class.ClassNo=Student.ClassNo AND Course.CouNo=StuCou.CouNo AND Student.StuNo=StuCou.StuNo AND Cl

23、assName=01电子商务,【注意】在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，在SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。,【练习】显示所有系的班级名称，要求计算各系班级数量，显示要求如下：,连接,连接的类型有内连接、外连接和交叉连接。内连接就是只包含满足连接条件的数据行，它主要有自然连接和相等连接等形式。外连接是连接运算的扩展，可以处理缺失信息，它又分为左外连接、右外连接和全连接。两个表的交叉连接的结果集的总行数等于所连接的两个表的行数相乘。,多表查询,1、相等连接 相等连接是将要连接的列作相等比较后所作的连接，相等连接会产生冗余，因为连接的列要显示两

24、次。【例】检索学生信息和学生相应的班级信息。SELECT*FROM Class,Student WHEREClass.ClassNo=Student.ClassNo,2、自然连接 自然连接是将要连接的列作相等比较的连接，但是连接的列只显示一次。自然连接消除相等连接产生的冗余。【问题】检索学生信息和学生对应的班级信息，要求连接的列只显示一列。SELECT Class.*,StuNo,StuName,Pwd FROM Class,Student WHERE Class.ClassNo=Student.ClassNo【练习】检索学分大于等于2.5的课程信息，显示课程名称、学分、开设该课程的系部名称、

25、报名人数，要求使用别名。,3、带有选择条件的连接 在对多表检索时，在指定的连接条件之外也可以包括其他的选择条件。【例】检索计算机应用工程系承担的选修课程的学生报名信息（姓名、课程名称、授课教师）。SELECT StuName,CouName,Teacher FROM StuCou,Student,Course,Department WHERE StuCou.StuNo=Student.StuNo AND StuCou.CouNo=Course.CouNo AND Course.DepartNo=Department.DepartNo AND DepartName LIKE 计算机应用工程系,4

26、、非相等连接（比较连接）连接条件不是等号，而是比较运算符。【问题】检索各班可以选择的不是本系开设的选修课程的信息（课本的例子）。SELECT ClassName AS 班级,CouName AS 课程名,Kind AS 课程分类,Credit AS 学分,Teacher AS 教师,SchoolTime AS 上课时间,WillNum AS 报名人数 FROM Class,Course WHERE Course.DepartNoClass.DepartNo,5、自连接 自连接就是一个表和它自身进行连接。表名在FORM子句中出现两次，可以对表定义一个别名，在SELECT子句中引用的列名也要使用表

27、的别名进行限定。【例】检索课程类相同但系部编码不同的课程信息（意味着同一种类的课有多个系部开设）。SELECT DISTINCT t1.CouNo,t1.CouName,t1.Kind AS 课程分类,t1.DepartNo AS 系部编码 FROM Course t1,Course t2 WHERE t1.Kind=t2.Kind AND t1.DepartNot2.DepartNO,外连接,上面介绍的都是内连接，以下是外连接：1、左外连接 2、右外连接 3、全连接 4、交叉连接【问题】检索学生的选课信息，要求显示学生学号、课程编码、课程名称。,使用EXISTS关键字,如果要解决这样的问题：

28、【问题】检索已经报名选修了课程的学生的学号和姓名【分析】采用上面所学的方法能否解决？,使用EXISTS关键字测试跟随的子查询的行是否存在【要点】一旦找到，立即停止。找到则返回TRUE，否则返回FALSE，而不是返回一些行。,使用NOT EXISTS【问题】查询没有选修001课程的学生学号和姓名。select stuno,stuname from student where not exists(select*from stucou where stuno=student.stuno and couno=001),本节小结,多表查询非相等连接自连接使用别名外连接（左外连接、右外连接、全连接）和交

29、叉连接使用EXISTS关键字,向表中输入数据,1、使用INSERT语句 INSERT语句用来向表中或视图中输入新的数据行。输入数据的方法有两种：使用INSERT语句和使用企业管理器。INSERT语句的简单语法：INSERT INTO table_name(column_list)VALUES(DEFUALTNULLexpression,n)execute_statement,【例】向Department表插入3行数据。INSERT Department VALUES(11,数学系)INSERT Department(DepartName,DepartNo)VALUES(物理系,12)INSER

30、T Department(DepartNo,DepartName)VALUES(13,化学系),【注意】1、输入新的数据时，因为系部的值是唯一的，对一存在的值，系统不允许输入重复值。2、如果表中的一些列定义为不允许为空，在输入数据时，省略该列情况下系统向该列输入空值，导致出错。3、应在半角状态下输入标点符号。,2、使用企业管理器输入数据步骤如下：（1）打开企业服务器，展开数据库，点击表，选择“操作”菜单下的“打开表”下的“返回所有行”。（2）系统弹出登录窗口，输入登录帐号和密码，系统显示窗口，此时可以输入数据。（3）输入数据完毕后，关闭窗口即可。,3、使用SELECT子句插入数据 INSERT

31、语句的特点时每次只能输入一行数据。在INSERT语句中使用SELECT子句可以将表中的数据行插入到表中，并且可以一次输入多行数据。使用SELECT子句插入数据的语法形式为：INSERT table_name SELECT column_list FROM table_list WHERE search_conditions,【例】使用SELECT子句将NewDepartment表中的数据插入到Department表中。（1）创建一个新的表，表名为NewDepartment，表中定义2列No和Name，均为字符型，不允许为空。Create Table NewDepartment(No char(

32、2)not null,Name char(30)not null),（2）使用INSERT语句向NewDepartment表输入两行数据：INSERT NewDepartment VALUES(16,历史系)INSERT NewDepartment VALUES(17,生物系)（3）使用SELECT子句将NewDepartment表的两行数据插入到Department表中。INSERT Department SELECT No,Name FROM NewDepartment,【注意】1、INSERT语句中和FROM子句中使用的表可以相同也可以不相同，但必须是已存在的表。2、INSERT中指定的

33、表和SELECT子句得到的结果集一定要兼容，即列的数量和顺序必须相同，列的数据类型和长度要相同，或者进行转换。,4、使用SELECT INTO插入数据 使用SELECT INTO语句允许用户定义一个新表，并且把SELECT的数据插入到新表中。前面三种方法的共同点是在数据插入之前表已经存在。而使用SELECT INTO插入数据的方法是在插入数据的过程中建立新表。其语法形式如下：SELECT select_list INTO new_table_name FROM table_list WHERE search_conditions,【例】将Course表中报名人数超过20人的数据行插入到NewC

34、ourse表中。SELECT CouNo,CouName,Teacher,SchoolTime INTO NewCourse FROM Course WHERE WillNum20,【注意】1、新表不能存在，否则会产生错误信息。2、新表中的列和行是基于查询结果集的。,修改表中的数据,企业管理器使用UPDATE语句 UPDATE语句用来修改表中以及存在的数据。UPDATE语句的语法为：UPDATE table_name SET column_name=expression|DEFAULT|NULL,n FROM table_name,n WHERE,【例】将“电子出版概论”课程的上课时间修改为“

35、周二晚”。UPDATE Course SET SchoolTime=周二晚 WHERE CouName=电子出版概论【练习】将“01数据库”班学号为“01000025”的学生的名字 修改为“陈晓梅”。【练习】将已选修了课程且学号为“00000005”的同学的密码修改为“7F5E5D3D”。,使用DELETE从表中删除数据,DELETE语句可从表中删除数据。可以一次从一个表删除一行或者多行数据。DELETE语句的简化语法：DELETE FROM table_name WHERE,【例】学号为“00000005”的同学因故取消课程号为“017”的选修课，在StuCou表中删除此行。DELETE StuCou WHERE StuNo=00000005 AND CouNo=017 UPDATE Course Set WillNum=WillNum-1 Where CouNo=017【练习】学号为“00000005”的同学因故取消“中餐菜肴制作”选修课，在表中进行删除处理。,