数据库原理及应用项目5表中数据操作课件

数据库原理与应用数据库原理与应用讲授人CONTENTS项目项目1 数据库设计数据库设计项目项目2 搭建搭建SQL Server 2008 数据库管理系统环境数据库管理系统环境项目项目3 创建和管理数据库创建和管理数据库项目项目4 创建和管理数据表创建和管理数据表项目项目5 表中数据操作表中数据操作项目项目6 进行进行T-SQL程序设计程序设计项目项目8 存储过程的创建和使用存储过程的创建和使用项目项目7 事务、索引和视图的创建事务、索引和视图的创建与应用与应用项目项目9 触发器的创建和使用触发器的创建和使用 项目项目10 SQL Server的安全性管理的安全性管理项目项目11 数据库的备份和恢复数据库的备份和恢复项目项目12 SQL Server数据转换数据转换项目项目13 SQL Server代理服务任务代理服务任务项目项目5 表中数据操作表中数据操作任务5.1-5.3项目目标项目导读项目实训知识巩固项目导读项目导读在完成“网络论坛”数据库BookBBSDB和数据表的创建之后，作为数据库管理员和系统开发人员需要进一步加入、维护测试数据，如何对数据表中的数据进行添加、修改、删除和查询操作？可以采用什么方法来完成这些操作？本项目通过完成“网络论坛”数据库BookBBSDB中数据表数据的操作，让用户熟练掌握添加、修改、删除和查询数据的方法和技巧，进而具有对数据表中数据进行操作和管理的能力。能力目标02知识目标01项目目标项目目标了解SQL Sever中的逻辑表达式、函数和运算符的使用。掌握向表中插入数据的方法。掌握更新、修改表中数据的方法。掌握删除表中数据的方法。理解查询的机制。掌握使用SELECT语句进行一般查询的方法和技巧。掌握进行多表联合查询的方法和技巧。掌握进行子查询的方法和技巧。能够完成“网络论坛”数据库中各数据表 数据的添加、修改和删除操作。能够完成“网络论坛”数据库中各数据表 数据的基本查询。能够实现“网络论坛”数据库中各数据表 数据的多表联合查询和子查询。CONTENTS任务任务5.1 5.1 知识准备知识准备任务任务5.3 5.3 知识和技能扩展知识和技能扩展任务任务5.2 5.2“网络论坛网络论坛”数据表记录管理数据表记录管理5.1.1 T-SQL5.1.1 T-SQL中的运算符和表达式中的运算符和表达式任务任务5.1 知识准备知识准备1.1.算术运算符算术运算符算术运算符对两个表达式执行数学运算，这两个表达式可以是数值数据类型中的一个或多个数据类型，见表5.1。表 5.1 算术运算符及其含义5.1.1 T-SQL5.1.1 T-SQL中的运算符和表达式中的运算符和表达式任务任务5.1 知识准备知识准备2.2.比较运算符比较运算符比较运算符测试两个表达式是否相同，除了 text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式，见表5.2。表 5.2 比较运算符及其含义5.1.1 T-SQL5.1.1 T-SQL中的运算符和表达式中的运算符和表达式任务任务5.1 知识准备知识准备3.3.逻辑运算符逻辑运算符逻辑运算符对某些条件进行测试，以获得其真实情况，见表5.3。逻辑运算符和比较运算符一样，返回带有True或False值的布尔（Boolean）数据类型。常用逻辑运算符的运算规则见表5.4。表 5.3 逻辑运算符及其含义表 5.4 常用逻辑运算符的运算规则5.1.1 T-SQL5.1.1 T-SQL中的运算符和表达式中的运算符和表达式任务任务5.1 知识准备知识准备4.4.赋值运算符赋值运算符等号（=）是唯一的T-SQL赋值运算符。5.1.1 T-SQL5.1.1 T-SQL中的运算符和表达式中的运算符和表达式任务任务5.1 知识准备知识准备5.5.字符串串联运算符字符串串联运算符加号（+）是字符串串联运算符，可以用它将字符串串联起来，其他所有字符串操作都使用字符串函数（如SUBSTRING）进行处理。5.1.1 T-SQL5.1.1 T-SQL中的运算符和表达式中的运算符和表达式任务任务5.1 知识准备知识准备6.6.通配符通配符某些T-SQL语句中还可以使用表5.5所示的通配符运算符。表 5.5 通配符5.1.2 5.1.2 常用函数常用函数任务任务5.1 知识准备知识准备1.1.系统函数系统函数系统函数使用户可以访问SQL Server 2008系统表中的信息而不必直接访问系统表。常用的系统函数见表5.6。表 5.6 常用的系统函数5.1.2 5.1.2 常用函数常用函数任务任务5.1 知识准备知识准备2.2.日期和时间函数日期和时间函数日期和时间函数可以用来更改日期和时间的值，其作用是对日期和时间类型的数据进行处理，并返回一个字符串、数字或日期和时间的值。表5.7列出了T-SQL的系统日期和时间函数。表 5.7 系统日期和时间函数5.1.2 5.1.2 常用函数常用函数任务任务5.1 知识准备知识准备3.3.字符串函数字符串函数字符串函数的作用是对字符串数据进行处理，并返回一个字符串或数值。常用的字符串函数见表5.8。表 5.8 常用的字符串函数5.1.2 5.1.2 常用函数常用函数任务任务5.1 知识准备知识准备4.4.数学函数数学函数数学函数的作用是对数字型数据进行处理，并返回处理结果。常用的数学函数见表5.9。表 5.9 常用的数学函数5.2.1 5.2.1 任务描述及分析任务描述及分析任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理在“网络论坛”数据库BookBBSDB中的users、topic、reply和section数据表创建完成并添加约束后，根据工作情况，有时需要对表里面的数据进行添加、更新、删除、查询操作，以对数据进行更改。同时，数据库管理员和用户还经常需要按照一定条件来把满足要求的表中数据查询出来。在SQL Server 2008中，完成这些操作的方法一般通过T-SQL语句来完成，当然也可以通过SQL Server Management Studio工具来实现，本任务通过T-SQL语句来实现数据表中数据的添加、更新、删除和查询操作。5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理1.1.使用使用INSERTINSERT插入数据行插入数据行使用INSERT语句一行一行插入数据是最常见的一种方式，其语法格式如下：【示例5-1】向users表中添加一行数据。SQL语句的执行一般在查询编辑器中进行，以上语句的执行如图5.1所示。在输入T-SQL语句时，如果输入数据的数量和数据类型与字段不一致，系统将给出如图5.2所示的错误信息，此时需要进行检查核对。图 5.1 在查询编辑器中执行语句正确图 5.2 在查询编辑器中执行语句错误5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理执行该语句后，将提示与表的约束冲突，插入失败，如图5.3所示。【示例5-2】在前面项目中设置“email”字段必须包含一个字符，如果插入语句修改为：图 5.3 执行插入语句时与检查约束发生冲突5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理2.2.一次插入多行数据一次插入多行数据（1）通过INSERT SELECT语句将现有表中的数据添加到新表。【示例5-3】创建一张新表来存储用户信息，从users表中提取相关的数据。SELECT语句用于查询，上面的SQL用来把学生信息表中已经存在的姓名、地址和email信息插入到新的user_back表，避免了录入大量重复的数据项，如图5.4所示。图 5.4 通过INSERT SELECT语句将现有表中的数据添加到新表5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理2.2.一次插入多行数据一次插入多行数据（2）通过SELECT INTO语句将现有表中的数据添加到新表【示例5-4】通过SELECT INTO语句将users表中的数据添加到新表图 5.5 通过SELECT INTO语句将现有表中的数据添加到新表将创建users_back表，把users表中的name,password,sex,class,regDate,point作为users_back的新列，并把查询到的数据全部添加到新表中，如图5.5所示。5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理2.2.一次插入多行数据一次插入多行数据在向一个新表中添加数据时，又会涉及一个新的问题，即如何插入新的标识列。因为标识列的数据是不能指定的，因此可以创建一个新的标识列，语法如下：图 5.6 通过SELECT INTO语句向一个新表中插入标识列上面的语句修改如下：执行结果如图5.6所示。5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理2.2.一次插入多行数据一次插入多行数据（3）通过UNION关键字合并数据进行插入。UNION语句用于将两个不同的数据或查询结果组合成一个新的结果集。当然，不同的数据或查询结果，也要求数据个数、顺序、数据类型都一致，因此，当向表中重复插入多次数据的时候，可以使用SELECTUNION语句来简化操作。语法格式如下：5.2.2 5.2.2 使用使用T-SQLT-SQL语句插入数据语句插入数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理2.2.一次插入多行数据一次插入多行数据【示例5-5】这样的效果其实与INSERT SELECT效果是一样的，只不过多行数据是手写的，然后用UNION合并组成多行，然后把这多行数据一起插入，执行结果如图5.7所示。图 5.7 通过UNION关键字合并数据进行插入5.2.3 5.2.3 使用使用T-SQLT-SQL语句更新数据语句更新数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理使用UPDATE语句更新表中某行数据的语法是：【示例5-6】在user表的所有记录的性别都改为女。5.2.4 5.2.4 使用使用T-SQLT-SQL语句删除数据语句删除数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）删除若干符合条件的记录使用T-SQL删除数据表中的数据，语法格式为：1.1.使用使用DELETEDELETE删除数据删除数据【示例5-7】在users表中删除姓名为“张无忌”的记录。【示例5-8】DELETE name FROM users。将报告错误信息。5.2.4 5.2.4 使用使用T-SQLT-SQL语句删除数据语句删除数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）删除全部记录如果删除记录语句中默认WHERE子句，功能是删除全部记录。1.1.使用使用DELETEDELETE删除数据删除数据【示例5-9】DELETE FROM user_back。将会删除user_back表中所有记录，但是表的结构、列、约束索引等还存在。5.2.4 5.2.4 使用使用T-SQLT-SQL语句删除数据语句删除数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理TRUNCATE TABLE 用来删除表中所有行的命令，功能上类似于没有WHERE子句的DELETE 语句。2.2.使用使用TRUNCATE TABLETRUNCATE TABLE删除数据删除数据【示例5-10】删除user_back表中的所有记录行。但TRUNCATE TABLE比DELETE执行速度快，而且使用的系统资源和事务日志资源更少。/提示/TRUNCATE TABLE 删除表中的所有行，但是表的结构、列、约束索引等不会被改动。TRUNCATE TABLE 不能用于有外键约束引用的表，这种情况下需要使用DELETE语句。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理查询使用SELECT语句，最简单的查询语句的格式可以表示为：1.1.使用使用SELECTSELECT语句进行查询语句进行查询（1）查询全部的行和列把数据表中的所有行和列都列举出来比较简单，这个时候可以使用“*”通配符来表示所有列：查询结果如图5.8所示。图 5.8 查询users中所有行和列5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）查询部分行和列1.1.使用使用SELECTSELECT语句进行查询语句进行查询查询部分列，需要列举部分列名，即条件查询和筛选列。而查询部分行需要使用WHERE子句进行条件限制。【示例5-11】SELECT name,sex,class,point FROM users WHERE point=20以上将只查询point为20的行，并且只显示name、sex、class、point 列。同理，当WHERE Point20的查询指point不是20的行都能被查询出来。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）在查询中使用列别名1.1.使用使用SELECTSELECT语句进行查询语句进行查询使用AS子句可以用来改变结果集列的名称，也可以为组合或者计算出来的列指定名称，还有一种情况是要让标题列的信息更易懂。在T-SQL中重新命名列名可以使用AS子句，AS关键字也可以缺省。【示例5-12】SELECT name as 姓名,sex as 性别,class as 等级,point as 积分 FROM users WHERE point=20执行的结果如图5.9所示。图 5.9 更改查询结果的列名5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理还有一种情况是计算、合并得到新列的列名。例如，在查询NorthWind数据库的Employees 表中的数据时，需要把FirstName 和LastName字段合并成一个叫“姓名”的字段。可以执行以下查询：1.1.使用使用SELECTSELECT语句进行查询语句进行查询重新命名列名还有一种方法，就是采用“列”=来命名。【示例5-13】SELECT 姓名FirstName+.+LastName FROM Employees5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（4）查询空行1.1.使用使用SELECTSELECT语句进行查询语句进行查询在SQL语句中采用“IS NULL”或者“IS NOT NULL”来判断是否为空行，因此要查询users表中没有填写email信息的用户，可以使用以下查询语句：5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（5）使用常量列1.1.使用使用SELECTSELECT语句进行查询语句进行查询有的时候，一些常量的默认信息需要添加到查询输出列中，以方便统计和计算，例如，查询用户信息的时候，查询籍贯统一都是“许昌”的查询输出的语句是：查询结果多出一列“籍贯”，该列的所有数据都是“许昌”，如图5.10所示。图 5.10 使用常量列进行查询5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（6）查询返回限制的行数1.1.使用使用SELECTSELECT语句进行查询语句进行查询在T-SQL中，限制行数使用TOP关键字来约束，如要查询返回5位女生的姓名信息可以使用下列语句：还有一种情况需要从表中按一定的百分比提取记录，这个时候还需要用到PERCENT关键字来限制，如要提取一半的女生数据：5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理如果需要按照一定的顺序排列查询语句选中的行，需要使用ORDER BY子句，并且排序可以是升序（ASC）或者降序（DESC）。如果不指定ASC 或者DESC记录集按ASC升序排列。2.2.查询排序查询排序在查询编辑器中执行的结果如图5.11所示。图 5.11 查询结果按照升序排列【示例5-14】查询users表中用户积分的时候，如果把所有人的积分都降低10%后再加5分，再按照积分超过20分的高低来进行排列，代码如下：上面讲述过的SQL语句，都可以在其后面再加上ORDER BY来进行排序。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理还可以按照多个字段进行排序。例如，首先在users表的point列排序的基础上，再按照姓名进行排序的语句如下：2.2.查询排序查询排序5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）字符串函数3.3.在查询中使用在查询中使用SQL ServerSQL Server的函数的函数字符串函数用于控制返回给用户的字符串，这些功能仅能用于字符型数据。【示例5-15】返回指定字符串的长度。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）日期函数3.3.在查询中使用在查询中使用SQL ServerSQL Server的函数的函数日期函数帮助提取日期值中的日、月以及年，以便分别操作它们。【示例5-16】SELECT GETDATE（）返回当前日期和时间。【示例5-17】SELECT DATEADD(dd,-1,GETDATE（）)结果是系统日期的前一天的日期SELECT DATEADD(dd,1,GETDATE（）)结果是系统日期的后一天的日期5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）数学函数3.3.在查询中使用在查询中使用SQL ServerSQL Server的函数的函数数学函数用于对数值进行代数运算，下面列出了SQL Server部分常用的数学函数，见表5.10。表 5.10 部分常用的数学函数5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（4）系统函数3.3.在查询中使用在查询中使用SQL ServerSQL Server的函数的函数系统函数用来获取有关SQL Server中对象和设置的系统信息。【示例5-18】数据类型转换。返回：字符串12345【示例5-19】测试登录的计算机的名字。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）使用LIKE进行模糊查询4.4.模糊查询模糊查询在数据更新、删除或者查询的时候，依然可以使用LIKE关键字来进行匹配查找。【示例5-20】查姓“张”的用户信息。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）使用BETWEEN在某个范围查询4.4.模糊查询模糊查询使用BETWEEN可以查找那些介于两个已知值之间的一组未知值。要实现这种查找，必须知道开始要查找的初始值和终值，这个最大值和最小值用单词AND分开。【示例5-21】查积分在20分到80分之间的用户信息。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）使用IN在列举范围内进行查询4.4.模糊查询模糊查询查询的值是指定的某些值之一，可以使用带列举值的IN关键字进行查询。列举值放在圆括号里，用逗号分开。【示例5-22】查询积分是30、40、50的用户姓名与积分。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）SUM函数5.5.使用聚合函数使用聚合函数SUM返回表达式中所有数值的总和，只能用于数字类型的列，不能够汇总字符、日期等其他数值类型。【示例5-23】汇总users表中积分在20以上的所有用户的总积分。得到的查询结果如图5.12所示。图 5.12 使用SUM聚合函数进行汇总【示例5-24】SELECT SUM(point)AS 总积分,name FROM usersWHERE point=20将报告错误信息。但是在一个查询中可以使用多个聚合函数。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）AVG函数5.5.使用聚合函数使用聚合函数VG返回表达式中所有数值的平均值，AVG只能用于数值型的数据列。【示例5-25】在users表中查询等级class为2的用户的平均积分。执行结果如图5.13所示。图 5.13 使用AVG聚合函数求平均值5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）MAX函数和MIN函数5.5.使用聚合函数使用聚合函数MAX函数返回表达式的最大值，MIN函数返回表达式的最小值，它们都可以用于数值型、字符型以及日期时间类型的列。【示例5-26】在users表中查询等级class为2的用户积分的平均分、最高分、最低分。查询结果如图5.14所示。图 5.14 使用MAX和 MIN聚合函数求最大值和最小值5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（4）COUNT函数5.5.使用聚合函数使用聚合函数COUNT函数返回表达式中非空值的计数，COUNT函数可以用于数字和字符类型的列。【示例5-27】查询等级class为2的用户人数。查询结果如图5.15所示。图 5.15 使用COUNT聚合函数得到行数5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）分组统计6.6.使用聚合函数使用聚合函数users表中存放了所有用户的积分point。在这种情况下，可能就需要统计不同级别class的平均积分。首先把相同级别class都分组，然后这些相同组的对应的积分值再使用前面的聚合函数去求平均值（最大值、最小值或者参与开始人数统计），如图5.16所示。图 5.16 在分组的基础上分别统计以上这种类型的查询，在SQL Server中叫分组查询，分组查询采用GROUP BY子句来实现。采用分组查询实现的SQL语句如下：查询结果如图5.17所示。图 5.17 分组查询的输出结果5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）分组统计6.6.使用聚合函数使用聚合函数【示例5-28】分组统计中如果写成如下形式：则会提示出错：选择列表中的列id无效，因为该列没有包含在聚合函数或GROUP BY子句中。不难理解，在使用GROUP BY关键字时，SELECT列表中指定的项目是有限制，SELECT语句中仅允许以下几项。被分组的列。为每个分组返回一个值的表达式。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）分组统计使用HAVING子句进行筛选6.6.使用聚合函数使用聚合函数继续考虑上面的分组查询，对分组后的条件筛选必须使用HAVING子句。满足以上要求的SQL语句如下：查询结果如图5.18所示。图 5.18 查询平均积分超过40分的级别5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）分组统计使用HAVING子句进行筛选6.6.使用聚合函数使用聚合函数HAVING和 WHERE子句可以在同一个SELECT语句中一起使用，使用的顺序应按照如图5.19所示的次序。图 5.19 WHERE、GROUP BY、HAVING的次序在SELECT语句中，WHERE、GROUP BY、HAVING和统计函数的执行次序如下：WHERE子句从数据源中去掉不符合其搜索条件的数据；GROUP BY子句搜集数据行到各个组中，统计函数为各个组计算统计值；HAVING子句去掉不符合其组搜索条件的各组数据行。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）分组统计使用HAVING子句进行筛选6.6.使用聚合函数使用聚合函数【示例5-29】再按照级别分组统计，要查询“有积分低于20的用户级别及人数”。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）多表连接查询的分类7.7.多表连接查询多表连接查询内连接。内连接是最典型的、最常用的连接查询，它根据表中共同的列进行匹配，特别是两个表存在主外键关系时通常会用到内连接查询。内连接查询通常会使用到“=”或“！=”之类的比较运算符来判断两列是否相等，内连接使用INNER JOIN关键字进行表之间的关联。外连接。外连接可以是左外连接、右外连接和完全外连接。左外连接的结果集包括LEFT OUTER指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表中的行在右表中没有匹配的行，则在相关联的结果集行中右表的所有选择列均为空值。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）多表连接查询的分类7.7.多表连接查询多表连接查询右外连接：RIGHT JOIN或者RIGHT OUTER JOIN右外连接是左外连接的反向连接，将返回右表的所有行，如果右表的某行在左表中没有匹配的行，则将为左表返回空值。完全外连接：FULL JOIN或者FULL OUTER JOIN完全外连接则返回左表和右表中所有行，当某行在另外一个表中没有匹配的行时，则另外一个表的选择列包含空值，如果表之间有匹配行，则整个结果集包含基表的数据值。交叉连接（CROSS JOIN）。交叉连接返回左表中的所有行，左表中所有行再与右表中的所有行一一组合，相当于两个表“相乘”。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）内连接查询7.7.多表连接查询多表连接查询在WHERE子句中指定连接条件。【示例5-30】查询发帖人的姓名、积分和帖子主题。发帖人的姓名、积分在users表中，而帖子主题在topic表中，需要从两个表中提取数据，要想得到正确的数据，需要满足topic表中的uid列与users表中的ID列相同。运行结果如图5.20所示。图 5.20 在WHERE子句中指定连接条件5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）内连接查询7.7.多表连接查询多表连接查询当然FROM子句中表名也可以使用别名，表中字段也可以使用表的别名来标识。例如，查询发帖人的姓名、积分和帖子主题的SQL语句可以改为：在WHERE子句中指定连接条件。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）内连接查询7.7.多表连接查询多表连接查询在FROM子句中使用JOINON。【示例5-31】中的查询也可以通过以下JOINON子句来实现：运行结果如图5.20所示。利用ON子句指明两个表的连接条件。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）外连接查询7.7.多表连接查询多表连接查询左外连接查询。例如，要统计所有已发帖用户的帖子题目情况，要求显示所有发过帖的帖子题目情况，没有发过帖的用户也要显示出来。这时候以users表为主表、topic表为从表的左外连接查询语句为：查询的结果可能有一部分用户的帖子题目没有出现在users表上，对应的帖子题目以NULL(空值)填充,查询的结果如图5.22所示。图 5.22 左外连接查询结果5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）外连接查询7.7.多表连接查询多表连接查询右外连接查询。右外连接查询与左外连接查询相类似，只不过要包含右表中的所有匹配的行。如果右表中有的项在左表中没有对应的项，则以空值来填充。例如，执行如下语句：运行结果如图5.22所示。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）简单子查询8.8.简单子查询简单子查询users表和topic表的数据如图5.24所示。图 5.24 简单子查询例图5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）简单子查询8.8.简单子查询简单子查询问题：根据以上数据，希望查看级别class与“赵云”一样的用户，要求显示这些用户的信息。分析：用户的信息可以从用户信息表（users）中查询，但条件是级别class与“赵云”的一样。如何实现呢？第一步：求出“赵云”的级别class。第二步：利用WHERE语句筛选级别与“赵云”一样的用户。实现方法一：采用T-SQL变量实现【示例5-32】5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）简单子查询8.8.简单子查询简单子查询实现方法二：采用子查询实现可以合并上述两步。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）简单子查询8.8.简单子查询简单子查询其中，上述查询中的（SELECT class FROM users WHERE name=赵云）部分，就是子查询，因为它嵌入查询中作为WHERE条件的一部分。所以它在WHERE语句中的一般用法如下:除了“”号外，还可以使用其他运算符号，习惯上，外面的查询称为父查询，括号中嵌入的查询称为子查询。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（1）简单子查询8.8.简单子查询简单子查询【实例5-33】采用多表连接。或者上述语句的输出结果如图5.25所示。图 5.25 查询uid为3的用户5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）IN和NOT IN子查询8.8.简单子查询简单子查询使用子查询进行“=”“”等比较运算符时，要求子查询只能返回一条或空的记录。SQL Server中，当子查询跟随在=、!=、=之后，不允许子查询返回多条记录。【示例5-34】查询已发帖人的姓名。实现方法一：采用多表连接其中DISTINCT是对查询结果进行筛选不重复显示，即发过多次帖子的用户姓名也只显示一次。查询结果如图5.26所示。图 5.26 查询发过帖子的用户5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）IN和NOT IN子查询8.8.简单子查询简单子查询实现方法二：采用子查询用多表联合查询解决没有问题，但是用子查询的结果参与外部查询比较运算SQL Server就不允许了，运行会提示如图5.27所示问题。图 5.27 比较运算符后的子查询不允许返回多条记录运行上述语句的输出结果如图5.25所示。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（2）IN和NOT IN子查询8.8.简单子查询简单子查询【实例5-35】采用NOT IN子查询，查看没有发过帖子的用户名单。上述语句的输出结果如图5.28所示。图 5.28 查看没有发过帖子的用户名单5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）EXISTS 和NOT EXISTS 子查询8.8.简单子查询简单子查询它是一个存在检测的子查询语句。例如，如果存在数据库students，则先删除它，然后重新创建。上述语句的输出结果如图5.28所示。其基本语法为：问题：查询已发过帖子的用户积分point低于40的，则该用户没有递增级别的资格。分析：用户积分point低于40，可以采用EXISTS检测。5.2.5 5.2.5 使用使用T-SQLT-SQL语句查询数据语句查询数据任务任务5.2 “网络论坛网络论坛”数据表记录管理数据表记录管理（3）EXISTS 和NOT EXISTS 子查询8.8.简单子查询简单子查询【示例5-36】上述语句的输出结果如图5.28所示。上述语句的输出结果如图5.29所示。图 5.29 EXISTS 子查询5.3.1 T-SQL5.3.1 T-SQL中的注释中的注释任务任务5.3 知识与技能扩展知识与技能扩展1.1.“-”注释注释“-”注释的有效范围只能到该行结束的地方，也就是说，从一开始，到本行结束为止，都是注释的内容。如果有多行注释内容，则每一行的最前面都必须加“-”。【示例5-37】5.3.1 T-SQL5.3.1 T-SQL中的注释中的注释任务任务5.3 知识与技能扩展知识与技能扩展2./*2./*/*/注释注释“/*/”可以对多行语句进行注释，其有效范围是从“/*”开始，到“*/”结束，中间可以跨越多行。【示例5-38】5.3.2 5.3.2 使用使用SQL Server Management StudioSQL Server Management Studio工具管理工具管理 表中数据表中数据任务任务5.3 知识与技能扩展知识与技能扩展1.1.查看和添加记录查看和添加记录在对象资源管理器中选择要添加数据的表，单击鼠标右键，选择弹出的快捷菜单中“编辑前200行”选项，右侧表编辑窗口中即可以查看表中数据，编辑某个字段值，或者在最下边全部为NULL的一行输入数据，选择工具栏中“工具”按钮或者按F5键，可以修改或添加记录到数据表。编辑“网络论坛”数据库BookBBSDB中的users表的设计视图如图5.30所示。图 5.30 添加记录5.3.2 5.3.2 使用使用SQL Server Management StudioSQL Server Management Studio工具管理工具管理 表中数据表中数据任务任务5.3 知识与技能扩展知识与技能扩展2.2.删除记录删除记录如前面介绍的方法打开表，在如图5.30所示的窗口中选择记录左侧的灰色选择块可以选中整条记录，单击鼠标右键，在弹出的快捷菜单中选取“删除”命令，弹出确认删除对话框，如图5.31所示。图 5.31 确认记录是否删除对话框单击如图5.31所示中“是（Y）”按钮即可删除记录。如果该表有关联表，且被删除的记录主键字段在外键表中有对应的记录，会弹出约束冲突对话框，如图5.32所示。图 5.32 删除数据时关联约束冲突提示对话框项目实训项目实训实训实训 1 使用使用SQL Server Management Studio为网为网 上书店数据库表添加数据上书店数据库表添加数据分析（1）会使用SSMS工具灵活高效地为数据表添加数据。（2）添加数据时注意各个表之间的约束。实现思路及关键步骤（1）单击“开始”菜单，选择Microsoft SQL Server 2008，展开，选中SQL Server Management Studio并启动。（3）右侧表编辑窗口中即可以看到表中数据，编辑某个字段值，或者在最下边全部为NULL的一行输入数据，选择工具栏中工具按钮或者按F5键，可以修改或添加记录到数据表，如图5.33所示。（2）在SQL Server Management Studio的“对象资源管理器”中，依次展开“数据库BookSaleDB表”，首先选中用户角色表userroles，单击鼠标右键，选择弹出的快捷菜单中“编辑前200行”选项。图 5.33 向userroles表添加数据（4）采用相同的方法依次向userstates和users表中添加如图5.34所示的数据。图 5.34 userstates和users表中要添加的数据项目实训项目实训实训实训 2 使用使用T-SQL语句为网上书店数据库表添加数据语句为网上书店数据库表添加数据（1）单击“开始”菜单，选择Microsoft SQL Server 2008，展开，选中SQL Server Management Studio并启动。实现思路及关键步骤（1）会使用T-SQL语句，一次插入一行数据。（2）会使用T-SQL语句，一次插入多行数据。（3）添加数据时注意各个表之间的约束。分析（2）在SQL Server Management Studio中单击“新建查询”按钮，打开查询编辑器。（3）在查询编辑器中输入以下语句：项目实训项目实训实训实训 3 使用使用T-SQL语句对网上书店数据库表中记录语句对网上书店数据库表中记录 进行更新和删除进行更新和删除分析（1）会使用T-SQL语句更新数据。（2）会使用T-SQL语句删除数据。（3）操作数据时注意各个表之间的约束。实现思路及关键步骤（1）单击“开始”菜单，选择Microsoft SQL Server 2008，展开，选中SQL Server Management Studio并启动。（2）在SQL Server Management Studio中单击“新建查询”按钮，打开查询编辑器。（3）在查询编辑器中输入以下语句：项目实训项目实训实训实训 4 使用使用T-SQL语句对网上书店数据库表中记录进语句对网上书店数据库表中记录进 行查询行查询实现思路及关键步骤（1）会使用T-SQL语句进行数据简单查询。（2）会使用T-SQL语句进行数据多表连接查询。（3）会使用T-SQL语句进行子查询。（4）操作数据时注意各个表之间的约束。分析（1）单击“开始”菜单，选择Microsoft SQL Server 2008，展开，选中SQL Server Management Studio并启动。（2）在SQL Server Management Studio中单击“新建查询”按钮，打开查询编辑器。（3）在查询编辑器中输入以下语句：知识巩固知识巩固1.设ABC表有三列A、B、C，并且都是整数类型，则以下（）查询语句能够按照B列进行分组，并在每一组中取C的平均值。A.SELECT AVG（C）FROM ABCB.SELECT AVG（C）FROM ABC ORDER BY A，BC.SELECT AVG（C）FROM ABC GROUP BY BD.SELECT AVG（C）FROM ABC GROUP BY C,B2.设ABC表的A列存储电话号码信息，则查询不是以开头的所有电话号码的查询语句是（）。A.SELECT A FROM ABC WHERE A IS NOT%7B.SELECT A FROM ABC WHERE A LIKE%7%C.SELECT A FROM ABC WHERE A NOT LIKE7%D.SELECT A FROM ABC WHERE A LIKE1-6%3.假设A表有4行数据，B表有3行数据，执行交叉连接查询，将返回（）行数据。A.1 B.3 C.4 D.12一、选择题 知识巩固知识巩固4.要查询一个班中低于平均成绩的学生需要使用到（）。A.TOP子句 B.ORDER BY子句C.HAVING子句 D.聚合函数AVG5.现在ABC表中已经存储了数据，C列的数据存储了学生的民族信息，默认值应该为“汉族”。可是在设计表的时候这个默认的特征没有考虑，现在已经输入大量数据。对于少数民族的学生，民族的信息已经输入，对于汉族的学生，数据都为空值。此时，要解决这个问题的比较好的办法是（）。A.在该表中为该列添加NOT NULL约束B.使用“UPDATE ABC SET C=汉族WHERE C IS NULL”进行数据更新C.使用“UPDATE ABC SET DEFAULT=汉族”进行数据更新D.手工输入所有的“汉族”信息一、选择题 知识巩固知识巩固6.表AB 中有A和B两列，要把A列的平方根写到B列，正确的SQL语句为（）。A.UPDATE AB SET B=SQRT(A)B.UPDATE AB SET A=A/2C.SELECT B FROM AB SET B=A SQRTD.SELECT A FROM AB SET B=A/27.以下（）能够得到今天属于哪个月份。A.SELECT DATEDIFF(mm,GetDate（）)B.SELECT DATEPART(month,GetDate（）)C.SELECT DATEPART(n,GetDate（）)D.SELECT DATENAME(dw,GetDate（）)一、选择题 知识巩固知识巩固8.下列执行数据的删除语句在运行时不会产生错误信息的选项是（）。A.DELETE*FROM A WHERE B=6B.DELETE FROM A WHERE B=6C.DELETE A WHERE B=6D.DELETE A SET B=69.合并多个表中的数据的方法有（）3种。A.联合 B.子查询 C.连接 D.角色10.有关子查询和连接的说法，错误的是（）。A.子查询一般可以代替连接B.连接能代替所有的子查询，所以一般优先采用子查询C.如果需要显示多表数据，优先考虑连接D.如果只是作为查询的条件部分，一般考虑子查询一、选择题 Thank You