【教学课件】第2章使用Transact-SQL语言.ppt

第第2 2章章 使用使用Transact-SQLTransact-SQL语言语言使使用用TraTransansact-ct-SQLSQL语语言言|学习目标学习目标|了解了解Transact-SQLTransact-SQL的用法和作的用法和作用用|学会并能根据需要灵活使用学会并能根据需要灵活使用SELECTSELECT语句查询用户表的信息语句查询用户表的信息|使用使用INSERTINSERT向用户表输入数据向用户表输入数据|使用使用UPDATEUPDATE修改用户表的数据修改用户表的数据|使用使用DELETEDELETE语句删除用户表的语句删除用户表的数据。数据。内容框架Transact-SQL语言概述|问题：问题：我们需要对数据进行哪些维护操我们需要对数据进行哪些维护操作？作？如何对数据进行查询、统计如何对数据进行查询、统计?Transact-SQL语言概述|结构化查询语言（结构化查询语言（Structured Structured Query LanguageQuery Language）|SQLSQL语言是一种用于存取、查询语言是一种用于存取、查询数据以及管理数据库的数据库查数据以及管理数据库的数据库查询和编程语言。询和编程语言。|尽管不同的关系数据库使用各种尽管不同的关系数据库使用各种略有不同的略有不同的SQLSQL版本，但多数都版本，但多数都执行执行ANSI SQLANSI SQL标准。标准。|将将ANSI SQL-92ANSI SQL-92的扩展集称为的扩展集称为Transact-SQLTransact-SQL，简写为，简写为T-SQLT-SQLTransact-SQL语言的组成 数据定义语言数据定义语言DDLDDL：用来定义和管理数据库中的对象。：用来定义和管理数据库中的对象。DDLDDL主要的语句有主要的语句有:CREAT TABLECREAT TABLE/*/*创建表创建表*/ALTER TABLEALTER TABLE/*/*修改表的属性修改表的属性*/DROP TABLE /*DROP TABLE /*删除表删除表*/数据操作语言数据操作语言DMLDML：用来操作数据库中的对象和数据，是：用来操作数据库中的对象和数据，是T-SQLT-SQL中最常用的部分。中最常用的部分。DMLDML主要的语句有：主要的语句有：SELECTSELECT/*/*从从一一个个表表或或多多个个表表中中查查询询数数据据*/DELETEDELETE/*/*从表中删除数据从表中删除数据*/INSERT INSERT/*/*向一个表中添加数据向一个表中添加数据*/UPDATEUPDATE/*/*修改表中已有的数据修改表中已有的数据*/数据控制语言数据控制语言DCLDCL：用来控制用户对数据库对象操作的权：用来控制用户对数据库对象操作的权限。限。主要的命令有：主要的命令有：GRANTGRANT/*/*授予权限授予权限*/REMOVEREMOVE/*/*回收所授予的权限回收所授予的权限*/查询信息-单表|切换数据库切换数据库:USE:USE 数据库名数据库名|SELECTSELECT语句语句:用于查询数据。用于查询数据。SELECT SELECT 选择列表选择列表FROM FROM 表（视图）的列表表（视图）的列表WHERE WHERE 查询的条件查询的条件【问问 题题】从从 StudentStudent表表 中中 查查 询询 学学 号号 为为0000000100000001的的学学生生的的姓姓名名（StuNameStuName）和和选选课密码（课密码（PwdPwd）【实训实训】要求查询系部编号为要求查询系部编号为0101的班级的班级信息，要求显示班级编号和班级名称信息，要求显示班级编号和班级名称 查询信息(单表)1 1、查询所有列的信息（使用、查询所有列的信息（使用*）【例例】查询课程的所有信息查询课程的所有信息【例例】查询课程的课程类别查询课程的课程类别2 2、消消 除除 显显 示示 结结 果果 重重 复复 值值（使使 用用DISTINCTDISTINCT）【例例】查查询询课课程程的的课课程程类类别别，要要求求对对值值相同的那些行只保留一行相同的那些行只保留一行3 3、显显示示前前部部分分行行的的数数据据（使使用用TOP TOP n n PERCENTPERCENT）【例例】从从学学生生表表中中查查询询所所有有信信息息，要要求求只显示查询结果的前只显示查询结果的前6 6行。行。查询信息(单表)4 4、修改查询结果中的列标题、修改查询结果中的列标题【问问题题】查查询询学学生生表表中中的的学学号号、班班级级编编号号和和姓姓名信息，并修改列标题为汉字标题名信息，并修改列标题为汉字标题方法：方法：（1 1）将将要要显显示示的的列列标标题题用用单单引引号号括括起起来来后后接接等号（），等号（），后接要查询的列名后接要查询的列名（2 2）将将要要显显示示的的列列标标题题用用单单引引号号括括起起来来后后，写在列名后面，两者之间使用空格隔开写在列名后面，两者之间使用空格隔开（3 3）将将要要显显示示的的列列标标题题用用单单引引号号括括起起来来后后，写在列名后面，两者之间使用写在列名后面，两者之间使用ASAS关键字关键字【练习练习】使用其他两种方法使用其他两种方法 查询信息(单表)5 5、在查询结果中显示字符串、在查询结果中显示字符串【问问题题】查查询询课课程程表表的的信信息息，要要求求给给出出查询结果为：查询结果为：课程名称课程名称 课程编号课程编号SQL ServerSQL Server实用技术实用技术 课程编号为：课程编号为：001001 课程编号为：课程编号为：课程编号为：课程编号为：思考：思考：如何查询课程编号是如何查询课程编号是001001的课程名称？的课程名称？查询信息(单表)6 6、使用、使用WHEREWHERE限制查询的条件限制查询的条件【例例】查查询询课课程程编编号号为为001001的的课课程程名名称称。【练练习习】查查询询 “LinuxLinux操操作作系系统统”课课程程的任课教师的任课教师7 7、表达式、函数作为、表达式、函数作为SELECTSELECT语句的列语句的列【例例】查询学生总数、课程总门数。查询学生总数、课程总门数。【例例】查查询询课课程程信信息息、报报名名人人数数与与限限选选人数之比人数之比思思考考：能能否否将将上上面面的的查查询询结结果果按按报报名名人人数和限选人数之比的升序排序？数和限选人数之比的升序排序？查询信息(单表)8 8、重新排序查询结果（、重新排序查询结果（ORDER BYORDER BY子句）子句）升序：升序：ASCASC（默认默认）降序：降序：DESCDESC也可以在也可以在ORDER BYORDER BY子句中指定多个列子句中指定多个列 例例：查查询询课课程程信信息息、报报名名人人数数与与限限选选人人数数之之比比，要要求求按按报报名名人人数数和和限限选选人人数数之之比比升升序序排排序查询结果序查询结果【例例】查查询询课课程程表表的的教教师师名名、课课程程号号、课课程程名名，要要求求查查询询结结果果首首先先按按教教师师名名降降序序排排列列，教教师师名相同时，则按课程号的升序排列名相同时，则按课程号的升序排列 问问题题 查查询询课课程程类类别别为为“信信息息技技术术”的的课课程程的的任任课课教教师师、课课程程名名，要要求求先先按按教教师师名名升升序序排排列列，教教师师名名相相同同时时，按按课课程程名名的的降降序序排排列列，并将显示的列名改为中文。并将显示的列名改为中文。查询信息(单表)9 9、使用、使用ININ（NOT INNOT IN）关键字）关键字【例例】查询课程编号为查询课程编号为001004013001004013的课程名称。的课程名称。方法一：使用逻辑运算符方法一：使用逻辑运算符OR OR 方法二：使用方法二：使用ININ关键字（比使用逻辑关键字（比使用逻辑运算符更为简单）运算符更为简单）查询信息(单表)1010、模糊查询（使用、模糊查询（使用LIKELIKE（NOT LIKENOT LIKE）通配符：通配符：%匹配包括匹配包括0 0个或多个字符的字符串个或多个字符的字符串_ _匹配任何一个字符匹配任何一个字符匹配任何在范围内的单个字符，例如：匹配任何在范围内的单个字符，例如：m-pm-p 匹匹配配任任何何不不在在范范围围内内的的单单个个字字符符，例例如如：m-pm-p、mnopmnop通配符和字符串要括在单引号中通配符和字符串要括在单引号中【练习练习】解释下面的通配符表示的意义解释下面的通配符表示的意义D%D%、_a _a 、%D%D如果要查找通配符本身，需要将它们用方括号括起来如果要查找通配符本身，需要将它们用方括号括起来【问问题题】查查询询以以“制制作作”两两字字结结尾尾的的课课程程名名（使使用用LIKELIKE）【练练习习】查查询询姓姓名名的的第第二二个个字字为为“丽丽”的的学学生生信信息息，要要求显示学生学号和姓名求显示学生学号和姓名【问题问题】查询不姓查询不姓“刘刘”的学生信息（使用的学生信息（使用NOT LIKENOT LIKE）查询信息(单表)1111、使使用用IS IS NULLNULL关关键键字字用用于查询列中没有赋值的数据行于查询列中没有赋值的数据行【问题问题】查询课程表中教师未定查询课程表中教师未定的课程名称和教师名的课程名称和教师名 1212、使使 用用 BETWEENANDBETWEENAND（NOT NOT BETWEENANDBETWEENAND）关键字）关键字用用于于查查询询在在某某一一特特定定范范围围内内的的信信息息查询信息(单表)【问问题题】查查询询报报名名人人数数大大于于等等于于3030并并且且小小于于等等于于4040的的课课程程信信息息，要要求求显显示示课课程程名名称称和和报名人数（使用报名人数（使用BETWEENANDBETWEENAND）注注意意WHERE WHERE WillNum WillNum BETWEEN BETWEEN 30 30 AND AND 4040包括包括3030和和4040这两个值这两个值【问题问题】对上例使用对上例使用NOT BETWEENANDNOT BETWEENAND也也可可以以在在WHEREWHERE字字句句中中使使用用前前面面讲讲过过的的逻逻辑辑运算符查询某一范围内的信息运算符查询某一范围内的信息【练练习习】查查询询报报名名人人数数比比限限制制选选课课人人数数多多一一倍倍以以及及一一倍倍以以上上的的课课程程信信息息，要要求求显显示示课课程程名名称称、报报名名人人数数和和限限制制选选课课人人数数之之比比，并按该比例的降续排列并按该比例的降续排列查询信息(单表)1313、使使用用UNIONUNION子子句句用用于于将将两两个个或或多个查询结果合并成一个结果多个查询结果合并成一个结果【问问题题】从从班班级级表表中中查查询询班班级级名名，从从系系部部表表中中查查询询系系部部名名，并并将将这这两两个个查查询结果合并起来询结果合并起来使用时注意：使用时注意：（1 1）要要合合并并的的查查询询结结果果的的列列个个数数要要相相同同（2 2）要要合合并并的的查查询询结结果果按按顺顺序序对对应应列列的数据类型性同或者兼容的数据类型性同或者兼容 扩展扩展（1 1）修改列标题）修改列标题（2 2）按新的列标题排序）按新的列标题排序 查询信息(单表)1414、使用、使用COMPUTECOMPUTE子句子句用来计算总计或进行分组小计用来计算总计或进行分组小计【问问题题】查查询询课课程程表表中中的的课课程程编编号号、课课程程名名称称、课课程程类类别、报名人数，并计算平均报名人数别、报名人数，并计算平均报名人数 说说明明 总总计计值值或或小小计计值值作作为为附附加加新新行行出出现现在在查查询询结结果中。该子句用在果中。该子句用在WHEREWHERE子句之后子句之后从查询结果可以看到：从查询结果可以看到：查询结果中有两个结果集查询结果中有两个结果集（1 1）包含选择的列的所有明细行）包含选择的列的所有明细行（2 2）包含）包含COMPUTECOMPUTE子句中所指定的聚合函数的合计子句中所指定的聚合函数的合计聚合函数：聚合函数：AVG()AVG()、MAX()MAX()、MIN()MIN()、SUM()SUM()扩展扩展（1 1）只查询课程类别为）只查询课程类别为“信息技术信息技术”的课程信息的课程信息（2 2）可以在查询结果中显示多个聚合函数的合计）可以在查询结果中显示多个聚合函数的合计上上面面的的例例子子是是对对显显示示的的所所有有行行进进行行总总计计，如如何何对对信信息息进行分组小计呢？进行分组小计呢？查询信息-单表1515、使用、使用COMPUTEBYCOMPUTEBY子句子句【例例】按按类类查查询询课课程程表表中中的的课课程程编编号号、课课程程名名称称、课课程程类类别别、报报名名人人数数，并并计算每类课程的平均报名人数计算每类课程的平均报名人数查询结果中有多个组？查询结果中有多个组？注注意意 必必须须先先使使用用ORDER ORDER BYBY排排序序要要分分组组的列的列【练练习习】查查询询周周二二上上课课的的课课程程名名称称和和教教师名，并显示课程门数师名，并显示课程门数（提提示示：先先完完成成前前一一部部分分，再再使使用用聚聚合合函数函数COUNTCOUNT进行统计）进行统计）问题|问题问题 COMPUTECOMPUTE子句是在一个明细行的基子句是在一个明细行的基础上增加一个总计或小计（有础上增加一个总计或小计（有BYBY时），但如果只想统计每类课程时），但如果只想统计每类课程的平均报名人数，该如何处理？的平均报名人数，该如何处理？查询信息-单表1616、使用、使用GROUP BYGROUP BY子句子句【问问题题】按按课课程程类类别别统统计计每每类类课课程程的的平平均均报报名人数名人数与上面例子的区别：只有一个结果集与上面例子的区别：只有一个结果集 说明说明（1 1）该该子子句句用用于于将将查查询询结结果果按按照照GROUP GROUP BYBY后后指指定定的的列列进进行行分分组组，该该子子句句写写在在WHEREWHERE子句后面子句后面（2 2）GROUP GROUP BYBY子子句句经经常常用用于于SELECTSELECT子子句句中中包包含含有有聚聚合合函函数数的的情情况况。此此时时，SELECTSELECT子子句句中中选选项项列列表表中中出出现现的的列列，只只能能是是GROUP GROUP BYBY子句中的列或者包含在聚合函数中。子句中的列或者包含在聚合函数中。查询信息-单表1717、使用、使用HAVINGHAVING子句子句【问问题题】上上面面的的例例子子中中，如如果果只只统统计计“信信息息技术技术”类课程的平均报名人数类课程的平均报名人数 说说明明HAVINGHAVING经经常常用用在在GROUP GROUP BYBY子子句句之之后后，用于限定结果集中的分组用于限定结果集中的分组也可以使用也可以使用WHEREWHERE子句子句可可以以看看到到，HAVINGHAVING子子句句是是对对结结果果进进行行过过滤滤，而而WHEREWHERE是对原始记录进行过滤。是对原始记录进行过滤。注注意意HAVINGHAVING子子句句中中的的列列只只能能是是GROUP GROUP BYBY子句中或者聚合函数中的列子句中或者聚合函数中的列【问问题题】查查询询平平均均报报名名人人数数大大于于3030人人的的课课程类和每类平均报名人数程类和每类平均报名人数【练练习习】在在课课程程表表中中，按按所所开开设设课课程程的的系系部编号分别统计总共报名人数部编号分别统计总共报名人数如如果果问问：系系部部编编号号为为0101的的系系所所开开设设的的课课程程总总共有多少学生报名，如何编程实现？共有多少学生报名，如何编程实现？查询信息-单表1818、使使用用子子查查询询在在一一个个查查询询中中包含另一个查询包含另一个查询【问问题题】查查询询报报名名人人数数大大于于平平均均报报名名人人数数的的课课程程编编号号、课课程程名名称称和和报名人数报名人数STEP 1STEP 1：如何求平均报名人数：如何求平均报名人数STEP 2STEP 2：完成题目要求：完成题目要求 查询信息-多表“计计算算机机应应用用工工程程系系”有有哪哪些些班班级级，应该查询哪些表？应该查询哪些表？如果在多个表中进行查询，结果会怎如果在多个表中进行查询，结果会怎样？样？USE XkUSE XkGOGOSELECT*SELECT*FROM Department,ClassFROM Department,ClassGOGO结果集中，有结果集中，有5454行（行（318318），），5 5列（列（2 23 3）。）。查询信息-多表问题问题:笛卡儿积笛卡儿积 运算过程？运算过程？在在笛笛卡卡儿儿积积的的结结果果中中，分分析析存存在在的的问问题题以以及及解解决方法：决方法：（1 1）两列）两列DepatNoDepatNo的列值不同的列值不同解决方法：加连接条件，成为解决方法：加连接条件，成为相等连接相等连接【注注】为为了了避避免免笛笛卡卡儿儿积积，必必须须在在WHEREWHERE子子句句中中给给出出表表格格的的连连接接条条件件：通通常常对对于于N N个个表表格格的的查查询，要有询，要有N-1N-1个连接条件个连接条件（2 2）出现了相同的列：）出现了相同的列：解决方法：去掉相同的列，成为解决方法：去掉相同的列，成为自然连接自然连接（3 3）加加入入“计计算算机机应应用用工工程程系系”的的条条件件，成成为为带有选择条件的连接带有选择条件的连接 查询信息-多表【问问题题】查查询询“0101电电子子商商务务”班班学学生生选选修修课课程程的的情情况况，要要求求显显示示班班级级名名称称、学学号号、姓姓名名、课程名称和上课时间课程名称和上课时间.分分析析 班班级级名名称称、学学号号、姓姓名名、课课程程名名称称、上上课课时时间间 这这些些信信息息是是分分别别在在班班级级表表、学学生生表表、学学生生选选课课表表和和课课程程表表中中的的，因因此此需需要要在在多多个个表表中中进进行行查查询询。同同时时，必必须须在在WHEREWHERE子子句中给出连接条件。句中给出连接条件。注注意意 在在引引用用的的多多表表中中，如如果果列列名名在在多多个个表表中中同同名名，为为了了避避免免列列名名不不明明确确，在在SELECTSELECT子子句句中中必必须须在在列列名名前前加加上上表表的的前前缀缀，即即“表表名名.列名列名”。【练练习习】查查询询“林林斌斌”同同学学的的选选修修课课信信息息（课课程编号、课程名称、上课时间）程编号、课程名称、上课时间）查询信息-多表【练习练习】查询每个系所开课程的门数，显示系查询每个系所开课程的门数，显示系代码、系名称、开课门数代码、系名称、开课门数【练习练习】显示所有系的班级名称，要求计算各显示所有系的班级名称，要求计算各系班级数量，显示要求如下：系班级数量，显示要求如下：查询信息-多表1919、非相等连接（比较连接）、非相等连接（比较连接）连接条件不是等号，而是比较连接条件不是等号，而是比较运算符运算符【问题问题】查询各班可以选择的不是查询各班可以选择的不是本系开设的选修课程的信息（教本系开设的选修课程的信息（教材的例子）材的例子）查询信息-多表2020、自连接、自连接 一个表和自身进行的连接一个表和自身进行的连接【问问题题】查查询询课课程程类类相相同同而而系系部部编编号号不不同同的的课课程程信信息息（意意味味着着同同一一类类的的课课程程有有多多个个系系部部开开设设），要要求求显显示示课课程程编编号号、课课程程名名称称、课课程程类类别别和和系部编号（教材上的例子）系部编号（教材上的例子）2121、使用别名、使用别名【练练习习】查查询询学学分分大大于于等等于于2.52.5的的课课程程信信息息，显显示示课课程程名名称称、学学分分、开开设设该该课课程程的的系系部部名名称称、报报名名人人数数，要求使用别名要求使用别名 查询信息-多表上面介绍的都是上面介绍的都是内连接。内连接。以以下下是是外外连连接接：主主要要解解决决信信息息丢丢失失的问题的问题【问问题题】查查询询开开设设的的所所有有选选修修课课的的信信息息和和所所有有学学生生选选修修课课程程的的情情况况，要要求求显显示示学学生生学学号号、课课程程编编号号、课课程名称程名称自自然然连连接接会会丢丢失失信信息息：CourseCourse表表的的019019课课程程、StuCouStuCou表表的的020020课程课程左外连接左外连接 右外连接、全连接右外连接、全连接交叉连接交叉连接 使用EXISTS关键字如果要解决这样的问题：如果要解决这样的问题：【问问题题】查查询询已已经经报报名名选选修修了了课课程程的的学学生的学号和姓名生的学号和姓名 分析分析 采用上面所学的方法能否解决？采用上面所学的方法能否解决？使用使用EXISTSEXISTS关键字关键字测试跟随的子查询的行是否存在测试跟随的子查询的行是否存在 要点要点 一旦找到，立即停止一旦找到，立即停止找找到到则则返返回回TRUETRUE，否否则则返返回回FALSEFALSE，而而不是返回一些行不是返回一些行查询信息-多表l使用使用NOT EXISTSNOT EXISTS 【问题问题】查询没有选修查询没有选修001001课程课程的学生学号和姓名的学生学号和姓名。SELECT SELECT StuNo,StuNameStuNo,StuNameFROM StudentFROM StudentWHERE NOT EXISTSWHERE NOT EXISTS(SELECT*FROM(SELECT*FROM StuCouStuCouWHERE WHERE StuNoStuNo=Student.StuNoStudent.StuNo AND ANDCouNoCouNo=001)=001)查询信息-多表阅读理解如下语句：阅读理解如下语句：IF EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name=Xk)DROP DATABASE XkGO向表中输入数据|使用使用Management StudioManagement Studio|使用使用INSERTINSERT语句语句z注意事项：注意事项：1.1.主键值不允许出现重复值主键值不允许出现重复值2.2.不允许为空的列，必须输不允许为空的列，必须输入值入值3.3.数据类型要符合列的定义数据类型要符合列的定义4.4.valuesvalues值列表的顺序要与值列表的顺序要与表的列顺序一致表的列顺序一致向表中输入数据（续）|使用使用SELECTSELECT语句语句 INSERT INSERT table_nametable_name SELECT SELECT column_listcolumn_list FROM FROM table_listtable_list WHERE WHERE search_conditionssearch_conditionsz注意事项：注意事项：1.1.INSERTINSERT语句中和语句中和FROMFROM子句中使用的表子句中使用的表可以相同也可以不同，但必须是已经可以相同也可以不同，但必须是已经存在的表存在的表2.2.SELECTSELECT子句得到的结果集一定要和子句得到的结果集一定要和INSERTINSERT中指定的表兼容（列的数量和中指定的表兼容（列的数量和顺序要相同）顺序要相同）向表中输入数据|使用使用SELECT INTOSELECT INTO语句语句 SELECT SELECT select_listselect_list INTO INTO newnew_table_name_table_name FROM FROM table_listtable_list WHERE WHERE search_conditionssearch_conditionsz注意事项：注意事项：新表不能存在，否则会产生错误信息新表不能存在，否则会产生错误信息新表中的列和行是基于查询结果集的新表中的列和行是基于查询结果集的修改数据|修改表中的数据修改表中的数据问题：把学号为问题：把学号为0000000100000001的学生的学生姓名改为姓名改为AlexAlex问题：把课程号在问题：把课程号在002002到到005005之间的课程的报名人数增加之间的课程的报名人数增加1 1。v涉及多个表的修改涉及多个表的修改问题：把学号为问题：把学号为0000000100000001，选修，选修课为课为JavaJava技术的开发应用技术的开发应用的自的自愿号（愿号（WillOrderWillOrder）改为）改为2 2。需要用。需要用到两个表到两个表StuCouStuCou和和CourseCourse。、删除数据|删除表中的数据删除表中的数据DELETE table_nameDELETE table_nameFROM FROM WHERE WHERE 问题：删除学号问题：删除学号0000001100000011的学生的学生的选课记录。的选课记录。v涉及多个表的删除涉及多个表的删除问题：学号为问题：学号为0000000200000002的同学取的同学取消了消了中餐菜肴制作中餐菜肴制作课程的选修，课程的选修，请在表请在表StuCouStuCou中把他的选课信息删中把他的选课信息删除。除。本章小节|能根据实际需要对所自己使用能根据实际需要对所自己使用的数据库，进行查询与统计，的数据库，进行查询与统计，编辑（输入、修改、删除）。编辑（输入、修改、删除）。|要掌握对单表、多表的查询与要掌握对单表、多表的查询与统计统计|能理解出表与表之间的关系和能理解出表与表之间的关系和连接条件连接条件|能根据需要使用聚合函数，会能根据需要使用聚合函数，会进行分组显示并进行统计进行分组显示并进行统计