数据查询和管理

第2章 数据查询和管理 45 数据查询和管理 数据库对数据的管理主要是增、删、改、查这四个功能。他们是日常维护数据频繁使用的几个命令，他们是数据查询语言（SELECT语句）和数据操纵语言（INSERT语句，UPDATE语句，DELETE语句）。其中从数据库中筛选一个 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 集（SELECT），这是最常用的一个语句，功能强大，能有效地对数据库中一个或多个数据表中的数据进行访问，并兼有排序、分组等功能；在数据表中添加一个记录（INSERT）；删除符合条件的记录（DELETE）；更改符合条件的记录（UPDATE）。本章将分别描述各个语句的功能。 2.1 SELECT获取简单数据 2.1.1 SELECT语法 数据库应用中，最重要的操作就是查询操作。在Transact-SQL语言中，使用SELECT语句对数据库进行查询。SELECT语句从零个或更多个表(或视图)中检索数据。 首先我们来看看SELECT语句的基本结构： SELECT 数据列表[INTO 新表] [FROM 表（或视图、子查询）] [WHERE 查询条件 ] [ORDER BY 排序条件 [ ASC | DESC ] ] [GROUP BY 分组表达式 ] [HAVING 查询条件] 可在查询之间使用 UNION、EXCEPT 和 INTERSECT 运算符，以便将各个查询的结果合并或比较到一个结果集中。 说明 关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书 ： 数据列表，可以是常量、变量、函数、表达式、字段等的组合，也可以是“*”号。该列表是以逗号分隔的一系列表达式。可在该列表中指定的表达式的最大数目是 4096。 不带FROM的SELECT 语句，一般用于调试阶段测试某表达式的值，在数据库操作中我们常用的都是带有FROM的SELECT语句。下面给出几个简单的SELECT命令。 例如： SELECT 2 GO SELECT 2+3 GO SELECT GETDATE() GO SELECT 2,3+2,GETDATE() GO SELECT * FROM stu GO 图2-1几个简单的SELECT查询结果 【例2.1】查询学生表的信息。 SELECT * FROM dbo.stu 在SELECT命令后的“*”代表查询显示FROM关键字后面的表（也可能是视图或子查询，后面不再做特别说明）的所有列。也可以用下面的SQL命令，查询结果与上面SQL命令相同（如图2-2）。 SELECT id,name,sex,birthday,address,memo FROM dbo.stu 图2-2查询学生表的信息 学生表定义时使用的是英文，如图2-2的数据标题行，我们可以在查询时为它起汉字别名，查询的结果更符合我们的习惯。 请思考： 为什么不直接定义成汉字字段呢？ 有时数据库表中的字段比较多，且并不都是我们所需要的，可以有选择的显示其中某些字段，只需把我们想要查询的字段在SELECT命令后列出即可。 【例2.2】查询学生表的学生姓名、出生日期和家庭住址。 SELECT name,birthday,address FROM dbo.stu --别名常用格式：在字段名后面空一格再加上别名的字符串即可。 SELECT name 姓名,birthday 出生日期,address 家庭住址 FROM dbo.stu --别名 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 格式：在字段名后面加上AS再加上别名的字符串。 不省略AS是通用的SQL，在SQL Server中可以省略AS，大部分数据库软件一般也都可以省略AS，但并不是在所有数据库中通用，例如Microsoft Access就不能省略AS。 SELECT name AS 姓名,birthday AS 出生日期,address AS 家庭住址 FROM dbo.stu 图2-3查询学生姓名、出生日期和家庭住址 注意： 如果别名中带有空格或其他字符而导致SQL命令出错，则需要将整个别名用方括号< [ >和< ] >（有时也可以用两个单引号）括起来。 【例2.3】查询教师表的信息。 SELECT tea_no '工 号',name [姓 名],sal 工资,comm '奖；金',titile [职''":称] FROM teachers 图2-4查询教师表 在SELECT命令不仅可以用于显示表中的字段，也可以显示常量、函数、表达式等。关于函数表达式的详细使用请参考第五章。下面给出一个简单的SELECT命令： SELECT '学生：'+name AS 姓名, '年龄：' a,YEAR(GETDATE())- YEAR(birthday) AS 年龄,'岁' FROM dbo.stu 图2-5使用常量、函数和表达式的简单查询 DISTINCT 是从查询结果中删除那些重复的行。ALL （缺省）将返回所有候选行，包括重复的。 【例2.4】下面两条命令都是查询学生表中男女学生的年龄，但查询结果不一样。 SELECT DISTINCT sex,YEAR(GETDATE())- YEAR(birthday) AS 年龄 FROM STU GO SELECT ALL sex,YEAR(GETDATE())- YEAR(birthday) AS 年龄 FROM STU GO 图2-6 查询学生表中男女学生的年龄并比较两个结果 2.1.2 FROM子句 指定要在 Transact-SQL 语句中使用的表、视图或派生表源（有无别名均可）。在 SELECT 语句中，FROM 子句是必需的，除非选择列表只包含常量、变量或算术表达式（没有列名）。 虽然语句中可用的表源个数的限值根据可用内存和查询中其他表达式的复杂性而有所不同，但一个语句中最多可使用 256 个表源。单个查询可能不支持最多有 256 个表源。如果查询中引用了许多表，查询性能会受到影响。 表源在 FROM 关键字后的顺序不影响返回的结果集。如果 FROM 子句中出现重复的名称，SQL Server 会返回错误。解决 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 是为同名的表起不同的别名。 如果表或视图位于正在运行 SQL Server 实例的同一计算机上的另一个数据库中，请按照<数据库.模式.对象名>形式使用完全限定名。如果表或视图不在链接服务器上的本地服务器中，请按照<数据库连接.实例名.模式.对象名>形式使用四个部分的名称。 【例2.5】下面几条命令功能同例2.1，查询结果参考图2-2。 SELECT * FROM stu SELECT * FROM dbo.stu SELECT * FROM students.dbo.stu SELECT * FROM stu a SELECT * FROM stu AS a SELECT a.* FROM stu a SELECT stu.id,stu.name,stu.sex,stu.birthday,stu.address, stu.memo FROM stu SELECT [id],[name],[sex],[birthday],[address],[memo] FROM [students].[dbo].[stu] SELECT a.id,a.name,a.sex,a.birthday,a.address,a.memo FROM stu AS a SELECT a.id 学号,a.name 姓名,a.sex 性别,a.birthday 出生日期, a.address 家庭住址,a.memo 备注 FROM dbo.stu AS a 【例2.6】查询学生表中所有学生的学习成绩。 SELECT stu.id AS 学号,stu.name 姓名,score 成绩 FROM dbo.stu,dbo.score 图2-7查询学生表中所有学生的学习成绩 注意： 该题目查询的信息需要两张表，学生表stu和成绩表score，需要将这两张表放在关键字FROM后。这两张表都有字段id和字段name，在查询显示id列时，要指明是哪张表的id列，显示name列时，要指明是哪张表的name列。 思考： 成绩怎么都相同呢？ 2.1.3 WHERE子句 数据库表一般包含大量的数据，很少需要检索表中的所有记录，通常只会根据特定操作或报告的需要提取部分数据。这就需要指定搜索条件。在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。 WHERE子句可以使用各种逻辑表达式，作为查询的限定条件。SQL Server 2008支持下表列出的所有操作符。 表2.1 WHERE子句的操作符 操作符 说明 = 等于 <> 或 != 不等于 < 小于 <= 小于等于 !< 不小于 > 大于 >= 大于等于 !> 不大于 NOT 对谓词指定的布尔表达式求反。 AND 组合两个条件，并在两个条件都为 TRUE 时取值为 TRUE。 OR 组合两个条件，并在任何一个条件为 TRUE 时取值为 TRUE。 [ NOT ] BETWEEN 指定值的包含范围。使用 AND 分隔开始值和结束值。 IS [NOT] NULL 根据使用的关键字，指定是否搜索空值或非空值。 [NOT] LIKE 指示后续字符串使用时要进行模式匹配。 [ NOT ] IN 根据是在列表中包含还是排除某表达式，指定对该表达式的搜索。搜索表达式可以是常量或列名，而列表可以是一组常量，更常用的是子查询。将一组值用圆括号括起来。 注意： 上表中列出的某些操作符是冗余的（如<>与!=相同，!<（不小于）相当于>=（大于等于）），SQL Server2008支持上表中所有操作符，但并非所有DBMS都支持这些操作符，为了确定你的DBMS支持那些操作符，请参阅相应的文档。 表2.2 LIKE连接的表达式使用的通配符 通配符 说明 举例 % 包含零个或多个字符的任意字符串。 WHERE title LIKE '%computer%' 将查找在书名中任意位置包含 单词 英语单词 下载七年级上册英语单词表下载英语单词表下载深圳小学英语单词表 下载高中英语单词 下载 "computer" 的所有书名。 _（下划线） 任何单个字符。 WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）。 [] 指定范围 ([a-f]) 或集合 ([abcdef])中的任何单个字符。 WHERE au_lname LIKE '[C-P]arsen' 将查找以 arsen 结尾并且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如 Carsen、Larsen、Karsen 等。 [^] 不属于指定范围([a-f]) 或集合([abcdef]) 的任何单个字符。 WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始并且其后的字母不为 l 的所有作者的姓氏。 【例2.7】查询学号为“20040101006”的学生信息。 SELECT * FROM stu WHERE id='20040101006' 图2-8学号为“20040101006”的学生信息 【例2.8】查询成绩大于等于90的学生学号、姓名及成绩。 SELECT score.id,score.name,score.score FROM score WHERE score.score >=90 或 SELECT score.id,score.name,score.score FROM score WHERE NOT (score.score <90) 或 SELECT score.id,score.name,score.score FROM score WHERE (score.score !<90) 图2-9成绩大于等于90的学生学号、姓名及成绩 【例2.9】查询所有姓“王”的学生的学号和姓名。 SELECT id 学号,name 姓名 FROM stu WHERE name LIKE '王%' 或 SELECT id 学号,name 姓名 FROM stu WHERE (name LIKE '王_') OR (name LIKE '王__') 图2-10姓“王”的学生的学号和姓名 【例2.10】查询姓名为三个字的姓“王”的学生姓名及年龄 SELECT name 姓名,YEAR(GETDATE())-YEAR(birthday) AS 年龄 FROM dbo.stu WHERE name LIKE '王%' AND LEN(name)=3 图2-11姓名为三个字的姓“王”的学生姓名及年龄 【例2.11】查询学生姓名中带有“丽”字的学生学号和姓名。 SELECT id 学号,name 姓名,address FROM stu WHERE name LIKE '%丽%' 图2-12学生姓名中带有“丽”字的学生学号和姓名 【例2.12】查询学生学号、姓名及“Delphi程序设计”这个科目的成绩。 SELECT score.id,score.name,score.score FROM score ,subjects WHERE score.sub_no =subjects.sub_no AND subject ='Delphi程序设计' 图2-13学生学号、姓名及“Delphi程序设计”的成绩 【例2.13】查询学生成绩在80到90之间的学生信息（包括学号、姓名、性别、出生日期和成绩）。 SELECT score.id 学号,score.name 姓名,sex 性别,birthday 出生日期,score 成绩 FROM score ,stu WHERE score.id =stu.id AND score BETWEEN 80 AND 90 图2-14学生成绩在80到90之间的学生信息 【例2.14】查询学生成绩不在80到90之间的学生信息（包括学号、姓名、性别、出生日期和成绩）。 SELECT score.id 学号,score.name 姓名,sex 性别,birthday 出生日期,score 成绩 FROM score ,stu WHERE score.id =stu.id AND score NOT BETWEEN 80 AND 90 图2-15学生成绩不在80到90之间的学生信息 【例2.15】查询无备注信息的学生姓名。 SELECT name FROM stu WHERE memo is null --判断是否为空一定不要用“= null” 图2-16无备注信息的学生姓名 【例2.16】查询学生“王丽娟”和“徐丽波”的成绩。 SELECT id,name,score FROM score WHERE name IN ('王丽娟','徐丽波') 图2-17学生“王丽娟”和“徐丽波”的成绩 2.1.4 ORDER BY子句 按照前面的例子，我们可以看到查询返回的结果并不一定是按照某种比较规则的顺序显示的，实际上前面这些查询结果是按数据库中的存储顺序显示的。我们可以使用ORDER BY子句对查询结果进行排序，这个子句必须是SELECT语句的最后一条子句。可以根据需要，利用它在一个或多个列上对数据进行排序。除非同时指定了 TOP，否则 ORDER BY 子句在视图、内联函数、派生表和子查询中无效。 ORDER BY 子句可包含未显示在选择列表中的项。但是，如果已指定了 SELECT DISTINCT 或该语句包含 GROUP BY 子句，或者 SELECT 语句包含 UNION 运算符，则排序列必须显示在选择列表中。 此外，当 SELECT 语句包含 UNION 运算符时，列名或列的别名必须是在第一选择列表内指定的列名或列的别名。 注意：ntext、text、image 或 xml 列不能用于 ORDER BY 子句。 【例2.17】查询学生成绩在80到90之间的学生信息（包括学号、姓名、性别、出生日期和成绩），并按出生日期大小升序显示。 SELECT score.id 学号,score.name 姓名,sex 性别,birthday 出生日期,score 成绩 FROM score ,stu WHERE score.id =stu.id AND score BETWEEN 80 AND 90 ORDER BY birthday --或者ORDER BY 出生日期 图2-18 成绩在80到90之间的学生信息 【例2.18】查询学生所有信息，并按出生日期大小降序显示。 SELECT * FROM stu ORDER BY birthday DESC 图2-19 按出生日期降序显示学生所有信息 【例2.19】查询学生信息，并按姓名升序，出生日期降序显示。 SELECT id 学号,name 姓名,sex 性别,birthday 出生日期,address 籍贯 FROM stu ORDER BY name ASc,birthday desc 图2-20 按姓名升序，出生日期降序显示学生信息 请思考： 下面两个SELECT语句与上例的执行结果是否一致，为什么？ SELECT id 学号,name 姓名,sex 性别,birthday 生日,address 籍贯 FROM stu ORDER BY name ,birthday desc 或 SELECTT id 学号,name 姓名,sex 性别,birthday 生日,address 籍贯 FROM stu ORDER BY birthday desc,name ASc 2.1.5 GROUP BY子句 GROUP BY 子句实现按一个或多个列或表达式的值将一组选定行组合成一个摘要行集。针对每一组返回一行。SELECT子句数据列表中的聚合函数提供有关每个组（而不是各行）的信息。 【例2.20】分别查询男女学生人数。 SELECT sex 性别,count(*) AS 人数 --count(*)也可以写作count(1)或count(sex) FROM stu GROUP BY sex 图2-21 男女学生人数 【例2.21】查询男女学生的平均成绩。 SELECT sex 性别,AVG(score) 平均成绩 FROM stu,score WHERE stu.id =score.id GROUP BY sex 图2-22 男女学生的平均成绩 【例2.22】分别查询相同籍贯的男女学生人数。 SELECT address 籍贯,sex 性别,COUNT(2) 人数 FROM stu GROUP BY sex,address 图2-23 相同籍贯的男女学生人数 【例2.23】查询学生表中各个班级的学生人数。 SELECT SUBSTRING(id,1,9) AS 班级,COUNT(*) 人数 FROM stu GROUP BY SUBSTRING(id,1,9) 图2-24 学生表各个班级的学生人数 【例2.24】统计各个院系专业的学生人数。 SELECT dept.department 院系,specialties.specialty 专业,COUNT(*) FROM stu ,dept,specialties WHERE SUBSTRING(id,5,2)=dept.dept_no AND SUBSTRING(id,7,2)=spe_no GROUP BY department,specialty 图2-25 统计各个院系专业的学生人数 2.1.6 HAVING子句 HAVING子句与WHERE子句非常类似，功能是指定组或聚合的搜索条件。HAVING 通常与GROUP BY子句一起使用。HAVING支持所有的WHERE操作符（包括通配符条件和带多个操作符的子句），我们学过的所有关于WHERE子句的这些技术和选项都适用于HAVING。唯一的区别在于WHERE过滤行，HAVING过滤分组。 【例2.25】分别查询具有相同籍贯的男女学生的人数和籍贯，并要求只显示超过10人的记录。 SELECT address 籍贯,sex 性别,COUNT(*) 人数 FROM stu GROUP BY sex,address HAVING COUNT(*)>10 图2-26 具有相同籍贯的男女学生的人数和籍贯 【例2.26】分别查询成绩大于60且籍贯相同的男女学生人数，并按性别降序，人数降序排序显示。 SELECT sex 性别, address 籍贯,COUNT(*) AS 人数 FROM stu,score WHERE stu.id =score.id AND score >60 GROUP BY sex,address HAVING COUNT(*)>2 ORDER BY sex desc,COUNT(*) desc 图2-27 成绩大于60且籍贯相同的男女学生人数 这是个相对复杂的查询，要注意各个关键字的使用顺序，不可以随意颠倒。COUNT函数括号里面的内容可以是“*”或其它任意常量，但不能为空。 一般情况下，如果FROM子句中超过一张表，则在WHERE子句中通常含有这两张表的关联方式。GROUP BY 关键字和ORDER BY 关键字后面可跟多个字段依次进行分组或排序；HAVING关键字后面的字段只能是分组统计的字段而不能是原始表的字段；DESC仅能够说明它前面一个字段的排序规则为降序，ASC表示升序，一般可以省略不写。 2.1.7 使用TOP表达式 【例2.27】查询学生表中的第一条记录。 SELECT TOP 1 * FROM stu 图2-28 学生表中的第一条记录 【例2.28】按学号排序查询学生表中的前3%条记录。 SELECT TOP 3 PERCENT * FROM stu ORDER BY id 图2-29 学生表中的前3%的记录 2.1.8*[1] SELECT INTO子句 SELECT INTO子句可以动态创建新表，如果用 INTO 子句创建永久表，用户必须在目的数据库中具有 CREATE TABLE 权限。新表中的列按选择列表指定的顺序创建。新表中的每列与选择列表中的相应表达式具有相同的名称、数据类型和值。在新表创建完成后，将查询的结果写入到新表。 【例2.29】创建表tmp，其表结构与stu相同，并将stu表的数据同时复制到tmp表。 SELECT * INTO TMP FROM STU 【例2.30】创建表tmp，包含两个字段id和name，其类型与stu表的id、name相同，创建的同时插入与stu表中的这两个字段相同的记录。 SELECT * INTO TMP FROM (SELECT id,name FROM STU) a 2.2 SELECT操作多表数据 2.2.1 JOIN连接 【例2.31】查询学生信息（包括学号、姓名、性别、出生日期和成绩）。 SELECT score.id 学号,score.name 姓名,sex 性别,birthday 出生日期,score 成绩 FROM stu JOIN score ON stu.id=score.id 图2-30 学生信息 此例我们也可以用以前的方法来实现，命令如下： SELECT score.id 学号,score.name 姓名,sex 性别,birthday 出生日期,score 成绩 FROM stu ,score WHERE stu.id=score.id 2.2.2 内连接 内连接 使用INNER JOIN ON，下面的命令实现与例题2.1有相同的功能。 SELECT stu.*, score.* FROM stu INNER JOIN score ON stu.id=score.id 【例2.32】查询所有于2005年授课的教师姓名及课程编码等信息。 SELECT a.tea_no ,a.name ,a.titile ,b.* FROM teachers a INNER JOIN teacher_subjects b ON a.tea_no =b.tea_no WHERE b.year ='2005' 图2-31 于2005年授课的教师的相关信息 2.2.3 外连接 外连接包含三种情况，分别是左外连接、右外连接和全连接。 左外连接LEFT [OUTER] JOIN..ON，其中OUTER可以省略，以左表的记录为标准，按连接条件连接右表，若无对应记录，右表字段相应记录显示为空值。 【例2.33】查询所有教师姓名，有任课信息的显示任课科目编号等信息。 SELECT name ,b.* FROM teachers a LEFT JOIN teacher_subjects b ON a.tea_no =b.tea_no 图2-32 教师信息及其任课科目编号 右外连接RIGHT [OUTER] JOIN..ON，其中OUTER可以省略，以右表的记录为标准，按连接条件连接左表，若无对应记录，左表字段在显示时为空。 【例2.34】查询教师任课信息表，并根据教师编号显示教师姓名。 SELECT name ,b.* FROM teachers a RIGHT JOIN teacher_subjects b ON a.tea_no =b.tea_no 图2-33 教师任课信息表 根据本例题的查询结果，我们发现前三条记录有授课信息，但是没有教师名，明显这是不符合逻辑的。说明我们的数据完整性约束条件定义的不完善，需要改进，相关内容请参考建表、建约束等内容。 全连接FULL [OUTER] JOIN..ON，其中OUTER可以省略，功能相当于左右连接得到的结果集的交集。即满足连接条件的记录直接显示，不满足连接条件的，无记录对应的表的字段用NULL值替代，这里不分左右表。 【例2.35】显示所有教师姓名及所有任课信息。 SELECT name ,b.* FROM teachers a FULL JOIN teacher_subjects b ON a.tea_no =b.tea_no 图2-34所有教师姓名及所有任课信息 前面5个例子都是关于两张表的连接，其实更多张表的连接也是可以的，只需要再次使用JOIN..ON将第三张表连接上即可。 【2.36】查询于2005年授课的教师姓名、授课学期和授课科目。 SELECT name ,b.year,b.term , c.subject FROM teachers a LEFT JOIN teacher_subjects b ON a.tea_no =b.tea_no LEFT JOIN dbo.subjects c ON b.sub_no =c.sub_no WHERE year ='2005' 图2-35 教师姓名、授课学期和授课科目 2.2.4 交叉连接 没有WHERE子句的交叉连接(CROSS JOIN)将产生连接所涉及的表的笛卡尔积。笛卡尔积的结果集大小为第一个表的行数乘以第二个表的行数。 【例2.37】交叉连接 SELECT stu.*, score.* FROM stu CROSS JOIN score --或 SELECT stu.*, score.* FROM stu ,score 图2-36 交叉连接 第二个SQL命令同交叉连接的相同效果。一般这样的命令得到的数据集非常大，数据冗余也特别多，所以这样的命令通常没有实际意义，我们可以增加限定条件使其减少冗余。 【例2.38】带WHERE子句的交叉连接，此例效果请参考例题2.1，功能相同。 SELECT stu.*, score.* FROM stu CROSS JOIN score WHERE stu.id=score.id 2.2.5 自连接 自连接是SQL语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。两张表其实是同一张表，需要分别起不同的别名，看成两张不同表再进行连接，其连接操作和两张不同表的连接操作是一样的。 【例2.39】查询教师表中工资相同的教师姓名及其工资额。 SELECT a.name,a.name ,a.sal,b.sal FROM teachers a,teachers b WHERE a.sal =b.sal AND a.name <>b.name ORDER BY a.name 图2-37 工资相同的教师姓名及其工资额 2.2.6 联合查询 使用联合（UNION）运算符可以将两个或更多查询的结果合并为单个结果集，该结果集包含联合查询中的所有查询的全部行。联合（UNION）运算不同于使用连接（JOIN）合并两个表中的列的运算。 用UNION组合的两个查询结果集，列数和列的顺序必须相同，因为UNION运算符按照各个查询中给定的顺序一对一地比较各列。 两个查询结果集的对应列都具有相同的数据类型，或者可以在两种数据类型之间进行隐式数据转换，也可以提供显式类型转换。例如，datetime数据类型的列和binary数据类型的列之间的UNION运算符将不执行运算，直到进行了显式转换。但是，money数据类型的列和int数据类型的列之间的UNION运算符将执行运算，因为它们可以进行隐式转换。 【例2.40】列出所有工资为1800的教师信息和工资为2600的教师信息。 SELECT * FROM teachers WHERE sal =1800 UNION SELECT * FROM teachers WHERE sal =2600 图2-38 工资额为1800和2600的教师信息 【例2.41】列出工资大于1800的所有教师信息和工资小于2600的所有教师信息。 SELECT * FROM teachers WHERE sal >1800 UNION SELECT * FROM teachers WHERE sal <2600 上例其实就是列出所有教师信息，与命令“SELECT * FROM teachers”得到的结果相同，该表共21条记录，查询结果略。如果使用UNION ALL的话结果就不同了，我们看下面的命令。 SELECT * FROM teachers WHERE sal >1800 UNION ALL SELECT * FROM teachers WHERE sal <2600 图2-39 使用UNION ALL查询教师信息。 UNION与UNION ALL的区别：UNION操作，两个查询结果集有相同记录则仅保留一条，UNION ALL保留所有记录，包括多条重复记录。 2.2.7* EXCEPT和 INTERSECT EXCEPT 从左查询中返回右查询没有找到的所有非重复值。INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值。 EXCEPT 或 INTERSECT 返回的结果集的列名与操作数左侧的查询返回的列名相同。ORDER BY 子句中的列名或别名必须引用左侧查询返回的列名。 EXCEPT 或 INTERSECT 返回的结果集中的任何列的为空性与操作数左侧的查询返回的对应列的为空性相同。通过比较行来确定非重复值时，两个 NULL 值被视为相等。 【例2.42】通过下面两个执行结果相同的SQL语句学习EXCEPT。 SELECT * FROM score WHERE score BETWEEN 40 AND 80 EXCEPT SELECT * FROM score WHERE score BETWEEN 60 AND 90 --或 SELECT * FROM score WHERE score BETWEEN 40 AND 60 图2-40 关于EXCEPT的使用 【例2.43】通过下面两个执行结果相同的SQL语句学习INTERSECT。 SELECT * FROM score WHERE score BETWEEN 40 AND 80 INTERSECT SELECT * FROM score WHERE score BETWEEN 60 AND 90 --或 SELECT * FROM score WHERE score BETWEEN 60 AND 80 图2-41关于INTERSECT的使用 2.2.8 使用子查询 子查询是一个嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其他子查询中的查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。许多包含子查询的 Transact-SQL 语句都可以改用联接表示。 任何允许使用表达式的地方都可以使用子查询，只要它返回的是单个值。任何可以使用表的地方都可以使用子查询。一般用括号把它括起来。 子查询也可以嵌套在其他子查询内。尽管根据可用内存和查询中其他表达式的复杂程度的不同，嵌套限制也有所不同，但一般查询不建议超过32层嵌套。实际上超过五层的嵌套方案一般都不可取，需要改用其他途径解决，例如增加冗余，建立临时表等。 【例2.44】此例仅为了说明子查询的使用，无实际意义。 SELECT top 1 (SELECT top 1 '姓名'+name FROM teachers ORDER BY tea_no) AS 姓名 ,sal,hiredate ,titile FROM teachers ORDER BY tea_no 图2-42 查询教师表 【例2.45】显示在2005年第一学期有任课信息的教师信息。 SELECT * FROM teachers WHERE tea_no IN (SELECT tea_no FROM teacher_subjects WHERE year ='2005' AND term =1) 图2-43 在2005年第一学期有任课信息的教师信息 在FROM关键字后面使用子查询，作为虚拟表用。 【例2.46】查询教师表中所有奖金在500到700之间的副教授的信息。 SELECT * FROM (SELECT * FROM teachers WHERE comm between 500 AND 700) a --在FROM后的子查询要加别名 WHERE titile ='副教授' 图2-44奖金在500到700之间的副教授的信息 子查询的使用非常灵活可以在多处同时使用，具体见下例，请同学们使用其他方法完成下面例题实现的功能。 【例2.47】查询教师表中奖金在500到700之间的，与谢小明老师职称相同的教师信息及其每年各学期所授课的课程名称，查询结果按教师工号升序显示。 SELECT A1.tea_no,A1.name,A1.sal,A1.comm,A1.hiredate,A1.year,A1.term,A2.subject FROM (SELECT A.*,B.sub_no ,B.year,B.term FROM teachers A LEFT JOIN teacher_subjects B ON A.tea_no =B.tea_no WHERE comm BETWEEN 500 AND 700) A1, (SELECT sub_no ,subject FROM subjects) A2 WHERE A1.sub_no =A2.sub_no AND titile IN (SELECT titile FROM teachers WHERE name='谢小明' ) ORDER BY tea_no 图2-45 与谢小明老师职称相同的教师信息 【*例2.48】查询教师表信息，按工号升序排序，并给出序号。 SELECT (SELECT COUNT(tea_no)+1 FROM teachers A WHERE A.tea_no >B.tea_no ) AS 序号, tea_no '工号',name,sal,titile FROM teachers B ORDER BY B.tea_no DESC 图2-46 按工号升序排序并给出序号的教师表信息 【*例2.49】查询教师表信息，按职称升序排序，并给出序号。 SELECT (SELECT COUNT(titile)+1 FROM (SELECT DISTINCT titile FROM teachers) A1 WHERE A1.titile >B.titile ) AS 序号, (SELECT COUNT(titile)+1 FROM teachers A2 WHERE A2.titile >B.titile ) AS 序号, tea_no '工号',name,sal,titile FROM teachers B ORDER BY B.titile DESC 图2-47 按职称升序排序并给出序号的教师表信息 2.3 插入SQL数据 2.3.1 INSERT...VALUES语句语法 INSERT命令的功能是向表中插入数据。首先我们来看看INSERT命令的基本结构： INSERT INTO 表名[(字段列表)] VALUES(具体值或表达式列表)|子查询 【例2.50】向科目表中插入两条记录。 INSERT INTO subjects(sub_no ,subject)VALUES(1,'test') INSERT INTO subjects VALUES(12,'test2') INSERT INTO表名后面若没有字段列表，则在VALUES后面必须按建表时的字段顺序为该表的所有字段列出各个字段的值，如果某字段不想赋值，则使用NULL代替。 INSERT INTO表名后面若有字段列表，则表示向该表插入这几个字段的值，在VALUES后面必须按此处给出的字段顺序赋值，如果不想为某字段赋值，则使用NULL代替。注意字段列表的顺序可以任意给出，字段数可以少于该表的总字段数，但要保证未列出的字段可以为空或有默认值。 插入命令不会返回查询结果集，只会显示一个消息，例如“(1 行受影响)”等，如果想看插入后的效果一定要使用SELECT命令进行查询。例如本例可以使用下面的查询： SELECT * FROM subjects ORDER BY subject 图2-48 查询插入记录后的科目表 【例2.51】今天来了名新教师，请录入该教师的信息到教师表，该教师的信息是：工号0203006，姓名张三，工资1000，无奖金，职称助教，雇佣日期为今日。 INSERT INTO teachers VALUES('0203006','张三',1000,null,GETDATE(),'助教') 2.3.2 使用INSERT..SELECT语句 可以使用INSERT..SELECT命令向某张表插入来自其他表的数据。 【例2.52】新建一张学生表stu1，表结构与学生表stu相同，根据成绩表score中的学号和姓名两个字段的数据向stu1表插入数据。 INSERT INTO stu1(id,name) SELECT id,name FROM score 【例2.53】新建一张学生表stu2，有两个字段，字段属性与表stu的id和name字段相同，将stu表对应字段的数据写入到stu2表中。 INSERT INTO stu2 SELECT id,name FROM stu 【例2.54】新建一张学生表stu3，表结构与学生表stu相同，并将表stu中成绩在80到90之间的学生信息写入表stu3中。 INSERT INTO stu3 SELECT stu.* FROM score ,stu WHERE score.id =stu.id AND score between 80 AND 90 ORDER BY birthday 2.4 更新SQL数据 2.4.1 根据表中数据更新行 更新SQL数据，使用UPDATE语句，如果不加WHERE子句代表修改该表中的所有记录。如果修改表中部分记录，需要使用WHERE子句来限定修改条件。可以更新一个或多个字段，使用直接赋值法或使用子查询进行数据更新。 【例2.55】将表stu中学号为“20040101024”的学生姓名改为“冯伟华”。 UPDATE stu SET name ='冯伟华' WHERE id='20040101024' GO UPDATE score SET name ='冯伟华' WHERE id='20040101024' GO 【例2.56】修改表teachers中姓名为“张三”的教师的工资为2200，没有奖金。 UPDATE teachers SET sal=2200,comm=null WHERE name='张三' 【例2.57】修改表stu中学号为“20040102007”的学生的学号为“20040102006”，姓名为“范嘉琦”。 UPDATE stu SET id='20040102006',name ='范嘉琦' WHERE id='20040102007' GO UPDATE score SET id='20040102006',name ='范嘉琦' WHERE id='20040102007' GO 2.4.2 根据其他表更新行 【例2.58】按表stu的内容修改成绩表中学号为“20040101024”的学生姓名。 UPDATE score SET name =(SELECT name FROM stu WHERE id='20040101024') WHERE id='20040101024' 【例2.59】按表stu中学号为“20040102006”的学生信息修改表score中学号为“20040102007”的学生的学号、姓名。 UPDATE score SET (id,name)= (SELECT id,name FROM stu WHERE id='20040102006') WHERE id='20040102007' 【例2.60】修改教师表张三的工资与原教师表中工资最低者的工资相同。 UPDATE teachers SET sal=( SELECT TOP 1 sal FROM teachers ORDER BY sal ASC) WHERE name='张三' 2.5 删除SQL数据 2.5.1 使用DELETE语句 删除（DELETE）语句，用于删除数据库表或视图中的记录，其中FROM关键字可以省略。WHERE 子句用于指定限制删除行数的条件。如果没有提供 WHERE 子句，则 DELETE 删除表中的所有行。 【例2.61】删除教师表中名为“张三”的教师记录。 DELETE FROM teachers WHERE name='张三' 【例2.62】删除学生表中所有男同学的记录。 DELETE stu WHERE sex='男'。 【例2.63】删除成绩小于60的所有学生信息。 DELETE stu WHERE id IN (SELECT id FROM score WHERE score<60) GO DELETE score WHERE score<60 GO 2.5.2 删除基于其他表的数据行 删除表中的记录，删除条件有时需要参考其他表的数据。这样的问题在数据库使用中经常会遇到，具体请参考下面例题。 【例2.64】删除成绩表中学号不在学生表中存在的记录。 DELETE FROM score WHERE id NOT IN (SELECT id FROM STU) 2.5.3 使用TRUNCATE TABLE 语句 TRUNCATE 是SQL中的一条删除数据表内容的语句。特点是速度快、效率高。其语法是： TRUNCATE TABLE 表名 TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同，二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，会在事务日志中为所删除的每行记录日志。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放，不按行记录日志。 注意： 如果有ROLLBACK语句，DELETE操作将被撤销，但TRUNCATE不会撤销。 TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。 【例2.65】快速删除学生表中所有记录。 TRUNCATE TABLE stu 2.6 实验指导 实验目的 熟练掌握简单的SELECT、INSERT、UPDATE、DELETE的使用，并能够初步使用这四个SQL命令解决实际问题。 实验步骤 第一步：打开SQL Server Management Studio，在左面的对象资源管理器中展开数据库、STUDENTS、表。 图2-49 对象资源管理器 第二步：打开查询分析器，练习本章各个例题。 图2-50 使用查询分析器 第三步：右键单击对象资源管理器里面的“表”结点，选择“新建表”，在右面的区域可以填写字段信息，列名为col1、col2，类型分别为int、varchar(10)，可参考图2-50。然后保存表，填写表名“a”，表a建立完成。使用同样的方法建立表b，表b的列名分别为col3,col4，其他与表a相同。 图2-51 新建表a 图2-52 输入表名 第四步：向表a，表b插入记录几条，内容如下表。 表a 表b col1 col2 col3 col4 1 a 1 x 2 b 3 y 3 c 5 z 3 y 1 xx INSERT INTO A VALUES(1,'a') INSERT INTO A VALUES(2,'b') INSERT INTO A VALUES(3,'c') INSERT INTO A VALUES(3,'y') INSERT INTO B VALUES(1,'x') INSERT INTO B VALUES(3,'y') INSERT INTO B VALUES(5,'z') INSERT INTO B VALUES(1,'xx') 第五步：将表a中记录为（3，c）的记录修改为（5，cc）。 UPDATE A SET col1=5,col2='cc' WHERE col1=3 AND col2='c' 第六步：删除表a中记录与表b中记录完全相同的记录。 DELETE FROM A FROM A AS spqh INNER JOIN B AS sp ON COL1 = COL3 AND COL2 = COL4 或 DELETE FROM A WHERE col1 IN (SELECT col1 FROM A,B WHERE col1=col3 AND col2=col4) AND col2 IN (SELECT col2 FROM A,B WHERE col1=col3 AND col2=col4) 第七步：将数据恢复到第三步完成时的状态，做下面的查询，并比较查询结果。 第一组： SELECT * FROM A LEFT JOIN B ON col1=col3 SELECT * FROM B LEFT JOIN A ON col1=col3 SELECT * FROM A RIGHT JOIN B ON col1=col3 SELECT * FROM B RIGHT JOIN A ON col1=col3 SELECT * FROM B INNER JOIN A ON col1=col3 SELECT * FROM A INNER JOIN B ON col1=col3 SELECT * FROM A CROSS JOIN B SELECT * FROM B JOIN A ON col1=col3 SELECT * FROM A JOIN B ON col1=col3 SELECT * FROM B , A WHERE col1=col3 SELECT * FROM A , B 第二组： SELECT * FROM A UNION SELECT * FROM B SELECT *