数据库应用技术：第07章_数据查询与更新

1、第七章 数据查询与更新1本章内容7.1 数据查询7.2 数据更新 27.1 数据查询查询是对存储在SQL Server 2005中的数据的一种请求。Transact-SQL命令中的SELECT命令可以实现从SQL Server中检索出数据，然后以一个或多个结果集的形式返回给用户。与数据表相同，结果集由行和列组成。SELECT语句的基本框架是SELECT-FROM-WHERE，它包含输出字段、数据来源和查询条件等基本子句。在这种固定格式中，可以不要WHERE，但是SELECT和FROM是必备的。SELECT语句的子句很多，理解了这条语句各项的含义，就能从数据库中查询出各种数据。 37.1 数据查

2、询Transact-SQL查询语句 语法格式：SELECT select_list INTO new_table_name FROM table_list WHERE search_conditions GROUP BY group_by_list HAVING search_conditions ORDER BY order_list ASC | DESC 47.1 数据查询select_list：所要查询的选项的集合，多个选项之间用逗号分开。 INTO new_table_name：创建一个新表并将结果集写入新表new_table_name中。 table_list：要检索的数据表。 se

3、arch_conditions：定义检索条件,只有符合条件的行才向结果集提供数据。 57.1 数据查询group_by_list：GROUP BY子句根据group_by_list列中的值将结果集分成组。 search_conditions：HAVING子句通常与GROUP BY子句一起使用,search_conditions是应用于结果集的附加筛选条件。 order_list ASC | DESC ：定义了结果集中行的排序顺序 ，关键字ASC和DESC用于指定排序行的排列顺序是升序还是降序，若缺省，则默认为升序。 67.1 数据查询注：在SELECT命令中对数据库对象的每个引用都不得引起歧义

4、。下列情况可能导致多义性。在一个系统中可能有多个对象具有相同的名称。可以使用架构名称来限定表名称，解决多义性问题。例如，Schema1和Schema2都含有一个名为TableX的表，可以使用一下语句区分：SELECT *FROM Schema1.TableX77.1 数据查询在执行SELECT语句时，对象所驻留的数据库不一定总是当前数据库 ，在不考虑当前数据库设置的情况下要确保使用的对象始终是正确的，则应以数据库和架构来限定对象名称，如：SELECT *FROM AdventureWorks.Purchasing.ShipMethod 87.1 数据查询在FROM子句中所指定的表和视图可能有相

5、同的列名。若要解决重复名称之间的多义性问题，必须使用表或视图名称来限定列名。如： SELECT DISTINCT Sales.Customer.CustomerID, Sales.Store.NameFROM Sales.Customer JOIN Sales.Store ON( Sales.Customer.CustomerID = Sales.Store.CustomerID)WHERE Sales.Customer.TerritoryID = 1当表和视图名称都必须完全限定时，语法将变得复杂。可以在FROM子句中使用AS关键字为表指定一个相关名称(也称为作用域变量或别名)来解决此问题。9

6、7.1 数据查询 SELECT子句SELECT select_list INTO new_table_name 选择列表用于定义SELECT语句的结果集中的列，结果集中列的排列顺序与选择列表中表达式的排列顺序相同。选择列表中的表达式决定了结果集列的特性。 结果集列与定义该列的表达式的数据类型、大小、精度以及小数位数相同。 结果集列的名称与定义该列的表达式的名称相关联。可选的AS关键字可用于更改名称，或者在表达式没有名称时为其分配名称。107.1 数据查询 结果集列的数据值通过对结果集的每一行相应的表达式求值而得出。 选择列表中的项目可包括：简单表达式：对函数、局部变量、常量或者表或视图中的列的

7、引用。 标量子查询，它是用于对结果集每一行求得单个值的SELECT语句。 通过对一个或多个简单表达式使用运算符创建的复杂表达式。 * 关键字，可指定返回表中的所有列。 117.1 数据查询(1)选择所有列星号“*”用于对FROM子句中指定的所有表或视图中的所有列的引用。此时结果集中的列的顺序与CREATE TABLE、ALTER TABLE或CREATE VIEW语句中所指定的顺序相同。 (2)选择特定列若要选择表中的特定列，应在SELECT子句的选择列表中明确地列出每一列。(3)指定结果集列的名称对列的指定还可以使用别名或其他表达式 ，AS子句可用来为结果集列分配不同的名称或别名。 127.

8、1 数据查询使用AS子句可以提高可读性，其中AS子句的语法：column_name AS column_alias 或result_column_expression AS derived_column_name例如：指定课程名列显示的别名USE teachingGOSELECT CNAME AS Course nameFROM courseGO执行结果如图7.4所示137.1 数据查询(4) 使用DISTINCT消除重复项DISTINCT关键字可从SELECT命令的结果中消除重复的行。如果没有指定DISTINCT，将返回包括重复行的所有行。注意：对于DISTINCT关键字来说，空值将被认为是

9、相互重复的内容。 (5)使用TOP和PERCENT限制结果集 使用TOP子句限制结果集中返回的行数，格式如下 ：TOP ( expression ) PERCENT WITH TIES expression：指定返回行数的数值表达式，如果指定了PERCENT，则返回的是结果集行的百分比(由expression指定)。如：TOP(15)PERCENT 表示返回结果集中前15%的结果行147.1 数据查询例如：从course表中返回2门学分最多的课程。USE teachingGOSELECT TOP 2 CNO, CNAME, CREDITFROM courseORDER BY CREDIT DE

10、SCGO执行结果如图7.7所示。如果指定了WITH TIES，将返回包含ORDER BY子句返回的最后一个值的所有行，即便超过expression指定的数量 。157.1 数据查询(6)选择列表中的计算值 在SELECT子句的选择列表中可包含一个或多个使用运算符生成的表达式。这使结果集中得以包含基表中不存在但可以根据基表中存储的值计算得到的值。这些结果集列被称为派生列。在SELECT子句中可以使用算术运算符或函数进行运算。算术运算符允许对int、smallint、tinyint、decimal、numeric、float、real、money或smallmoney数值列或表达式中使用加(+)、

11、减(-)、乘(*)、除(/)以及int、smallint或tinyint列或表达式中使用模(%)运算。 167.1 数据查询在SELECT子句中还可以使用聚合函数。 聚合函数对一组值执行计算并返回单个值 。注：除了COUNT以外，聚合函数都会忽略空值； 聚合函数只在SELECT子句的选择列表(子查询或外部查询)、COMPUTE或COMPUTE BY子句、HAVING子句 这些位置作为表达式使用。聚合函数包括AVG平均函数、SUM求和函数、COUNT求个数函数、MIN最小函数、MAX最大函数、 177.1 数据查询 FROM子句格式：FROM table_list在一个要从表或视图中检索数据的S

12、ELCET语句，都需要使用FROM子句。 使用 FROM子句可以指明如下信息：列出SELECT子句中的选择列表和WHERE子句中所引用的列所在的一个或多个表和视图。可以使用AS子句为表和视图的名称指定别名。两个或多个表或视图之间的联接类型。这些类型由ON子句中指定的联接条件限定。FROM子句使用逗号分隔表名、视图名和JOIN子句的列表。187.1 数据查询WHERE子句和HAVING子句 格式： WHERE search_conditions HAVING search_conditions WHERE和HAVING是筛选器。这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用于生成结果

13、集。HAVING子句通常与GROUP BY子句一起使用以筛选聚合值结果 。WHERE搜索条件在进行分组操作之前应用，HAVING搜索条件在进行分组操作之后应用 。HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数 197.1 数据查询WHERE和HAVING子句中的搜索条件或限定条件可包括如下条件 ：(1)比较搜索条件Microsoft SQL Server 2005使用表7.1中的=、=、=、!、!=80GO执行结果如图7.17所示 。217.1 数据查询注：表示所有记录可用ALL关键字(=ALL、ALL、 ALL (SELECT s2.GRADE FROM s_c s2

14、WHERE s2.CNO = C2)GO执行结果如图7.19所示 227.1 数据查询(2)范围搜索条件 BETWEEN关键字指定要检索的包括范围 . NOT BETWEEN查找指定范围之外的所有行 .例如：查询年龄不在2022之间的学生 。USE teachingGOSELECT SNO, SNAMEFROM studentWHERE AGE NOT BETWEEN 20 AND 22ORDER BY SNOGO 执行结果如图7.21所示。237.1 数据查询(3)列表搜索条件 IN 关键字可以选择与列表中的任意值匹配的行 ，IN关键字之后的各项必须用逗号隔开，并且括在括号中。 例如：检索s

15、tudent表中姓名为WANG或LIU的学生 。USE teachingGOSELECT SNO, SNAMEFROM studentWHERE SNAME IN (WANG, LIU)ORDER BY SNOGO 执行结果如图7.22所示。247.1 数据查询(4)搜索条件中的模式匹配 LIKE关键字搜索与指定模式匹配的字符串、日期或时间值 ,字符串中可包含如表7.2所示的%、_、 、四种通配符的任意组合。注意：不与LIKE一同使用的通配符将解释为常量而非模式，换言之，这些通配符仅代表其本身的值 。有两种方法可指定平常用作通配符的字符：使用ESCAPE关键字定义转义符。在模式中，当转义符置于

16、通配符之前时，该通配符就解释为普通字符。在方括号 ( )中只包含通配符本身。若要搜索破折号(-)而不是用它指定搜索范围，可以将破折号指定为方括号内的第一个字符。 257.1 数据查询(5) NULL比较搜索条件 NULL值表示列的数据值未知或不可用 。当搜索的列中包括定义为允许空值的列时，可以通过以下模式查找数据库中的空值或非空值。WHERE column_name IS NOT NULL注意：NULL值与零(数值或二进制值)、零长度的字符串或空白(字符值)的含义不同；指定= NULL与指定IS NULL是不同的 。267.1 数据查询(6)逻辑运算符逻辑运算符包括AND、OR和NOT。AND

17、和OR用于连接WHERE子句中的搜索条件。NOT用于反转搜索条件的结果 。 AND连接两个条件，只有当两个条件都符合时才返回TRUE。 OR也用于连接两个条件，但只要有一个条件符合便返回TRUE。 可以通过添加括号强制先计算OR来改变查询的含义 注意：当一个语句中使用了多个逻辑运算符时，计算顺序依次为NOT、AND和OR。算术运算符和位运算符优先于逻辑运算符。277.1 数据查询 GROUP BY子句 格式： GROUP BY group_by_list GROUP BY子句用来为结果集中的每一行产生聚合值 。可以在包含GROUP BY子句的查询中使用WHERE子句。在完成任何分组之前，将消除

18、不符合WHERE子句中的条件的行 。287.1 数据查询注意：WHERE 子句用来筛选 FROM 子句中指定操作所产生的行。 GROUP BY 子句用来分组WHERE子句的输出。 HAVING 子句用来从分组的结果中筛选行。 ORDER BY子句可用于排序GROUP BY子句的输出 。297.1 数据查询 ORDER BY子句格式： ORDER BY order_by_expression COLLATE collation_name ASC | DESC ,.n 指定在SELECT语句返回的列中所使用的排序顺序。 307.1 数据查询 order_by_expression ：指定要排序的列

19、 。可以将排序列指定为一个名称或列别名，也可以指定一个表示该名称或别名在SELECT子句的选择列表中所处位置的非负整数的顺序号。列名和别名可由表名或视图名加以限定 。 COLLATE collation_name：指定根据collation_name中指定的排序规则，而不是表或视图中所定义的列的排序规则。 ASC：指定按升序。DESC：指定按降序 。 317.1 数据查询注意：列名已在SELECT子句的选择列表中有了别名，则ORDER BY子句中只能使用别名 。表名已在FROM子句中有了别名，则ORDER BY子句中只能使用别名来限定它们的列。 ORDER BY子句中指定了多个列，则排序是嵌套

20、的 。 ORDER BY子句的准确结果取决于被排序的列的排序规则。可以指定Windows排序规则名称或SQL排序规则名称。 无法对数据类型为text、ntext、image或xml的列使用ORDER BY 。在ORDER BY列表中不允许使用子查询、聚合和常量表达式。 327.1 数据查询联接查询 联接查询通过联接条件指明了两个或多个表之间的逻辑关系来检索数据，指明了应如何使用一个表中的数据来选择另一个表中的行 。联接条件可通过以下方式定义两个表在查询中的关联方式： 指定每个表中要用于联接的列(例如外键)。指定用于比较各列的值的逻辑运算符(例如 = 或 ) 。337.1 数据查询联接可分为以下

21、几类：内部联接 包括同等联接和自然联接,使用比较运算符(=)根据每个表的通用列中的值匹配两个表中的行。 外部联接 左向外部联接 (LEFT JOIN 或 LEFT OUTER JOIN )不管第二个表中是否有匹配的数据，结果中都将包括第一个表中的所有行;右向外部联接 (RIGHT JOIN 或 RIGHT OUTER JOIN)不管第一个表中是否有匹配的数据，结果中都将包括第二个表中的所有行。;完整外部联接 (FULL JOIN或 FULL OUTER JOIN)将包括两个表中的所有行，不论另一个表中是否有匹配的值。交叉联接左表中的每一行均与右表中的所有行组合。交叉联接也称作笛卡尔积。第一个表

22、的行数乘以第二个表的行数等于笛卡尔积结果集的大小。347.1 数据查询子查询 子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询。 子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。注意：许多包含子查询的Transact-SQL语句都可以改用联接表示 ，其他问题只能通过子查询提出。 联接总是可以表示为子查询 。357.1 数据查询(1)子查询规则通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称(对SELECT * 执行的EXISTS或对列表执行的IN子查询除外)。如果外部查询的WHERE子句包括列名称，它必须

23、与子查询选择列表中的列是联接兼容的。ntext、text和image数据类型不能用在子查询的选择列表中。由于必须返回单个值，所以由未修改的比较运算符(即后面未跟关键字ANY或ALL的运算符)引入的子查询不能包含GROUP BY和HAVING子句。包含GROUP BY的子查询不能使用DISTINCT关键字。不能指定COMPUTE和INTO子句。只有指定了TOP时才能指定ORDER BY。不能更新使用子查询创建的视图。367.1 数据查询(2)在子查询中限定列名 一般的规则是，语句中的列名通过同级FROM子句中引用的表来隐性限定。如果子查询的FROM子句中引用的表中不存在列，则它是由外部查询的FR

24、OM子句中引用的表隐性限定的。如果子查询的FROM子句中引用的表中不存在子查询中引用的列，而外部查询的FROM子句引用的表中存在该列 。377.1 数据查询例如：USE teachingGOSELECT student.SNAMEFROM studentWHERE student.SNO IN (SELECT s_c.SNO FROM s_c WHERE s_c.CNO = C2 )GO387.1 数据查询(3)子查询类型 使用别名的子查询子查询和外部查询引用同一表的SELECT语句可称为自联接(将某个表与自身联接) ，由于自联接的表会以两种不同的角色出现，因此必须有表别名 。使用IN和NOT

25、 IN的子查询 通过IN或 NOT IN引入的子查询结果是包含零个值或多个值的列表。子查询返回结果之后，外部查询将利用这些结果。397.1 数据查询例如：下面的查询查找学生S3所选所有课程的名称 。USE teachingGOSELECT CNAMEFROM courseWHERE CNO IN (SELECT CNO FROM s_c WHERE SNO = S3)GO执行结果如图7.46所示 。407.1 数据查询使用比较运算符的子查询 子查询可以由一个比较运算符=、 =、! 或 (SELECT AVG(AGE) FROM student)执行结果如图7.50所示 。 417.1 数据查询

26、(4)用ANY、SOME或ALL修改的比较运算符 可以用ALL或ANY关键字修改引入子查询的比较运算符 。若要使带有ALL的子查询中的行满足外部查询中指定的条件，引入子查询的列中的值必须大于子查询返回的值列表中的每个值。同样，ANY表示要使某一行满足外部查询中指定的条件，引入子查询的列中的值必须至少大于子查询返回的值列表中的一个值。=ANY运算符与IN等效，ANY运算符则不同于NOT IN ，ALL与NOT IN表示的意思相同 。427.1 数据查询(5)使用EXISTS和NOT EXISTS的子查询使用EXISTS关键字引入一个子查询时，相当于进行一次存在测试。外部查询的WHERE子句测试子

27、查询返回的行是否存在。子查询实际上不产生任何数据，它只返回TRUE或FALSE值 。使用EXISTS引入的子查询的语法格式如下。WHERE NOT EXISTS (subquery)注意： EXISTS关键字前面没有列名、常量或其他表达式 。由EXISTS引入的子查询的选择列表通常几乎都是由星号(*)组成 。437.1 数据查询例如：查询选修C2课程的所有学生的姓名 USE teachingGOSELECT SNAMEFROM studentWHERE EXISTS (SELECT * FROM s_c WHERE SNO = Student.SNO AND CNO = C2)GO执行结果如图

28、7.54所示 447.2 数据更新若对数据进行更新需要对目标表有相应的INSERT权限、UPDATE权限和DELETE权限 。默认情况下，INSERT、UPDATE和DELETE权限被授予sysadmin固定服务器角色成员、db_owner和db_datawriter固定数据库角色成员以及表的所有者。sysadmin、db_owner和db_securityadmin角色成员和表所有者可以将权限传递给其他用户。457.2 数据更新注意：如果UPDATE语句包含WHERE子句，或SET子句中的expression使用了表中的某个列，则还要求更新的表具有SELECT权限 。如果DELETE语句包含

29、WHERE子句，则还必须有SELECT权限。467.2 数据更新 INSERT插入数据(1)使用INSERT命令插入数据 INSERT命令的语法格式如下 ：INSERT INTO table_or_view (column_list) VALUES data_valuescolumn_list是用逗号分隔的一些列名称，可用来指定接收数据的列,未列出的所有列中插入默认值(如果为列定义了默认值)或NULL值 。如果未指定column_list，表或视图中的所有列都将接收到数据。 VALUES data_values子句用于指定插入的一行数据值，所提供的数据值必须与列表中的列匹配 。477.2 数据

30、更新注意：插入的数据值的数目必须与列数相同，每个数据值的数据类型、精度和小数位数也必须与相应的列的这些属性匹配。 如果没有指定column_list，VALUES指定值的顺序必须与表或视图中的列顺序一致 487.2 数据更新例如：向数据表student中插入一新学生的信息 USE teachingGO INSERT INTO student (SNO, SNAME, SEX, AGE)VALUES (S7, WU, F, 22)GOSELECT * FROM studentGO执行结果如图7.58所示。497.2 数据更新(2)将查询结果插入数据表 用SELECT子查询为一行或多行指定数据值。

31、例如：将所有女学生的学号、姓名放入新表F_student中 USE teaching GO CREATE TABLE F_student (SNO char (4) PRIMARY KEY, SNAME char (10) not NULL) GOINSERT INTO F_student (SNO, SNAME) SELECT SNO, SNAME FROM student WHERE SEX= FGOSELECT * FROM F_studentGO 执行结果如图7.61所示。507.2 数据更新(3)可以通过使用图形工具插入数据 具体步骤详见教材517.2 数据更新UPDATE 更新数据

32、(1)使用UPDATE命令更新数据 UPDATE命令的语法格式如下：UPDATE TOP ( expression ) PERCENT SET column_name = expression | DEFAULT | NULL ,.n FROM ,.n WHERE ; 527.2 数据更新 := server_name . database_name . schema_name . | database_name . schema_name . | schema_name . table_or_view_name537.2 数据更新 TOP ( expression ) PERCENT ：指定

33、将要更新的行数或行百分比 。 server_name：表或视图所在服务器的名称(使用链接服务器名称) 。 database_name：数据库的名称 。 schema_name：表或视图所属架构的名称。 table_or_view_name：需要更新行的表或视图的名称。 SET column_name = expression | DEFAULT | NULL ：指定要更新的列或变量名称的列表 。547.2 数据更新 column_name包含要更改的数据的列 expression指返回单个值的变量、文字值、表达式或嵌套select语句(加括号)。 FROM ：指定将表、视图或派生表源用于为更新操作提供条件。 WHERE ：指定条件来限定所更新的行。 557.2 数据更新例如：使用简单UPDATE语句对student表中的所有行更新AGE列中的值 。USE teachingGOSELECT * FROM studentGOUPDATE studentSET AGE=AGE+1GOSELECT * FROM studentGO 执行结果如图7.64所示。 567.2 数据更新 DELETE 删除数据(1