数据检索ppt课件

第8章 数据检索,8.1 SELECT 语句 8.2 使用SELECT 语句进行简单查询 8.3 使用T-SQL语句进行高级查询 8.4 用户定义函数在数据检索中的应用 8.5 数据查询的优化 8.6 分布式查询简介,返回目录,2,8.1 SELECT 语句,SELECT 语句的基本语法 SELECT ALL|DISTINCT column_list INTO new_table_name FROM table_list WHERE search_condition GROUP BY group_by_list HAVING search_condition ORDER BY order_list ASC | DESC SELECT语句中各子句的说明 SELECT：此关键字用于从数据库中检索数据。 ALL|DISTINCT：ALL指定在结果集中可以包含重复行，ALL 是默认设置；关键字DISTINCT指定SELECT 语句的检索结果不包含重复的行。,3,column_list：描述进入结果集的列，它是由逗号分隔的表达式的列表。每个列表中表达式通常是对从中获取数据的源表或视图的列的引用，但也可能是其它表达式，例如常量或 Transact-SQL 函数。如果select_list使用 *，表明指定返回源表中的所有列。 INTO new_table_name ：指定查询到的结果集存放到一个新表中。new_table_name为指定新表的名称。 FROM table_list：用于指定产生检索结果集的源表的列表。这些源表包括：SQL Server的本地服务器中的基表、本地 SQL Server 中的视图、链接表。SQL Server 将一个视图引用内部解析为针对组成视图的一个或多个基表的引用，链接表是从SQL Server 进行访问的 OLE DB 数据源中的表，这种访问方式称为分布式查询。有关视图的概念将在第九章中介绍。,4,WHERE search_condition：用于指定检索的条件，它定义了源表中的行数据进入结果集所要满足的条件，只有满足条件的行才能出现在结果集中。 GROUP BY group_by_list：GROUP BY子句根据group_by_list 列中的值将结果集分成组。 HAVING search_condition：HAVING子句是应用于结果集的附加筛选。从逻辑上讲，HAVING子句从中间结果集对行进行筛选，这些中间结果集是用SELECT语句中的 FROM、WHERE或GROUP BY子句创建的。HAVING 子句通常与 GROUP BY 子句一起使用，尽管HAVING子句前面不必有GROUP BY子句。 ORDER BY order_list ASC | DESC ：ORDER BY子句定义结果集中的行排列的顺序。order_list 指定依据哪些列来进行排序。ASC和DESC关键字用于指定结果集是按升序还是按降序排序，DESC降序排序，ASC升序排序。ORDER BY 是一个重要的子句，要想获得有序的查询结果，必须使用ORDER BY子句，因为关系理论规定表中的数据行是没有次序的。,5,在使用SELECT语句时应注意如下几点： 必须按照正确的顺序指定 SELECT 语句中的子句。 对数据库对象的每个引用必须具有唯一性。 在系统中，可能有多个数据库对象带有相同的名称。例如，User1和User2可能都创建了一个名为Table_0的表。在引用表Table_0时，为了区分引用的是User1的Table_0表，还是引用的是User2的Table_0，就必须对引用名称有所限定，如：User1. Table_0，User2. Table_0。 在执行SELECT 语句时，对象所驻留的数据库不一定总是当前数据库。若要确保总是使用正确的对象，则不论当前数据库是如何设置的，均应使用数据库和所有者来限定对象名称，如：JWGL.dbo.student。 在 FROM 子句中所指定的表或视图可能有相同的列名，外键很可能和相关主键同名。加上对象名称来限定列名可解决重复列名称的问题，如：department.department_no、teacher. department_no。,本章首页,6,8.2.1 最基本的SQL查询语句 8.2.2 改变列标题的显示 8.2.3 使用WHERE子句的查询 8.2.4 TOP和DISTINCT关键字 8.2.5 使用ORDER BY子句对结果进行排序 8.2.6 计算列的使用 8.2.7 基于多个检索条件的查询,8.2 使用SELECT语句进行简单查询,本章首页,7,8.2.1 最基本的SQL查询语句,SQL语言中最主要、最核心的部分是它的查询功能。查询语言用来对已经存在于数据库中的数据按照特定的组合、条件表达式或次序进行检索。 8.2.1 最基本的SQL查询语句 格式：SELECT *|column_name,.n FROM table_name 功能：从指定表中查询所有信息或指定列的信息。 【例8-1】从teacher表中分别检索出教师的所有信息及教师、教师姓名信息。 USE jwgl GO SELECT * FROM student SELECT teacher_id , teacher_name FROM teacher 注意： 在SELECT后的列名的顺序决定了显示结果中的列序 在查找多列内容时，用,将各字段分开,本节首页,8,在缺省情况下，执行上面的SQL语句后，查询结果中显示的列标题是列名。可以在SELECT语句中用列标题=列名或列标题AS列名 来改变列标题的显示。 【例8-2】从teacher表中分别检索出教师的教师号、教师姓名信息并分别加上“教师”、“教师号”的标题信息。 USE jwgl GO SELECT 教师号=teacher_id , 教师姓名 = teacher_name FROM teacher SELECT teacher_id AS 教师号, teacher_name AS 教师姓名 FROM teacher,8.2.2 改变列标题的显示,本节首页,9,大部分查询都不是针对表中所有行的查询，而是从整个表中选出符合条件的信息，要实现这样的查询就要用到WHERE子句。 1. WHERE子句的语法形式 WHERE子句的语法形式如下： SELECT selctc_list FROM table_list WHERE select_condition 其中SQL Server支持的搜索条件如下： 比较： =、=、 范围：BETWEENAND（在某个范围内） 、NOT BETWEENAND（不在某个范围内）,8.2.3 使用WHERE子句的查询,10,列表：IN（在某个列表中）、NOT IN（不在某个列表中） 字符串匹配：LIKE（和指定字符串匹配）、NOT LIKE（和指定字符串不匹配） 空值判断：IS NULL（为空）、 IS NOT NULL（不为空） 组合条件：AND（与）、 OR（或） 取反：NOT 要注意的是，应该避免使用否定条件，查询优化器不能识别否定条件。 2. 基于比较条件的WHERE子句 使用基于比较条件的WHERE子句对表中数据进行查询，系统在执行这种条件查询时，逐行地对表中的数据进行比较，检查它们是否满足条件。,11,如果满足条件，则取出该行，如果不满足条件则不取该行。使用WHERE子句时，若该列为字符型，需要使用单引号将字符串括起来，而且应该注意单引号内的字符串要区分大小写形式。 【例8-3】从book表中检索出价格小于15元的书本信息。 USE jwgl GO SELECT * FROM book WHERE price 15 在写基于比较条件的选择行的SELECT句子时，要注意以下几点： 表达式可以是嵌套查询，也可以包含常量、列名和函数。 对CHAR、VARCHAR、TEXT、DATETIME和SMALLDATETIME类型的值要用单引号引起来。 为了和ANSI兼容，推荐使用单引号。,12,3. 基于BETWEEN关键字的WHERE子句 使用基于BETWEEN关键字的WHERE子句是为了对表中某一范围内的数据进行查询，系统将逐行检查表中的数据是否在/不在BETWEEN关键字设定的范围内。如果满足条件，则取出该行，如果不满足条件则不取该行。BETWEEN关键字一般应用于数字型数据。 BETWEEN子句的语法形式如下： SELECT select_list FROM table_name WHERE expression NOT BETWEEN expression1 AND expression2 【例8-4】从book 表中检索价格介于15至20元之间的书本信息。 USE jwgl GO SELECT * FROM book where price BETWEEN 15 AND 20,13,4. 基于IN关键字的WHERE子句 使用基于IN关键字的WHERE子句对表中数据进行查询，系统将逐行检查表中的数据是否在/不在IN关键字设定的列表内。如果满足条件，则取出该行，如果不满足条件则不取该行。IN关键字一般应用于字符型数据。 IN子句的语法形式如下： SELECT select_list FROM table_name WHERE expression NOT IN （value_list） 【例8-5】从student_course表中检索学号为g9940202,g9940204,g9940206的学生信息。 USE jwgl GO SELECT * FROM student_course WHERE student_id IN (g9940202,g9940204,g9940206),14,5. 基于LIKE关键字的WHERE子句 使用基于LIKE关键字的WHERE子句对表中数据进行查询，系统将逐行对表中的数据进行字符串的模糊匹配。如果满足条件，则取出该行，如果不满足条件则不取该行。使用LIKE关键字进行模糊查询，具有一定的实际应用背景，如查询某公司一个姓张的人，但不知道叫什么名字，前面介绍的精确查询就不管用了，必须使用LIKE关键字进行模糊查询。 LIKE 子句的语法形式如下： SELECT select_list FROM table_name WHERE expression NOT LIKE string 在SQL Server2000中，共提供了4个通配符 ： % ：代表任意多个字符 _（下划线）： 代表一个任意字符 ： 代表方括号内的任意一个字符 ：表示任意一个在方括号内没有的字符,15,【例8-6】从student表中分别检索出姓张的所有同学的资料；名字的第二个字是“红”或“虹”的所有同学的资料；名字的第二个字不是“红”或“虹”的同学的资料；李红大（student表中有两个学生“李红”同名同姓，分别以李红大、李红小加以区分）同学的信息。 USE jwgl GO SELECT * FROM student WHERE student_name LIKE 张% SELECT * FROM student WHERE student_name LIKE _红,虹% SELECT * FROM student WHERE student_name LIKE _红,虹% SELECT * FROM student WHERE student_name LIKE 李红b大b ESCAPE b 注意： 含通配符的字符串须用单引号引起来，单引号中的字符都被认为是通配字符，无论是空格还是字符。 如果使用的匹配字符中含有通配符，可以使用转义字符。在上例的最后一个查询语句中，在ESCAPE子句中，定义“b”为转义字符，这样，LIKE子句中紧跟字符“b”后面的字符被定义为匹配字符而不再是通配符。,16,6. 基于空值判断的WHERE子句 有时，会存在表中数据为空值的情况，例如，学生的家庭住址没有输入，图书还没有定价等。这时，就会在相应列上产生空值。空值的概念前面已经介绍过，空值通常用NULL表示，它仅仅是一个符号，既不等于0，也不等于空格，它不能像0那样进行算术运算。 使用空值判断的SELECT语句的语法形式如下： SELECT select_list FROM table WHERE column_name IS NOT NULL 【例8-7】从student表中检索出家庭地址列为空值的同学的信息。 USE jwgl GO SELECT * FROM student WHERE home_addr IS NULL,本节首页,17,1. TOP 关键字 使用TOP 关键字可以返回表中前n行或前一个百分数的数据。 【例8-8】分别从teacher表中检索出前5个及表中前面20%的教师的信息。 USE jwgl GO SELECT top 5 * FROM teacher SELECT top 20 PERCENT * FROM teacher 2. DISTINCT关键字 有时，一个许多列的值不是唯一的，在进行数据检索时，可以用DISTINCT消除重复行。使用DISTINCT关键字的语法形式如下：,8.2.4 TOP和DISTINCT关键字,18,SELECT ALL|DISTINCT select_list FROM table_name WHERE search_condition 其中： ALL|DISTINCT：如使用ALL则检索出全部信息，如使用DISTINCT则剔除重复信息 【例8-9】从teacher表中检索出所有姓名不重复的教师的信息。 USE jwgl GO SELECT DISTINCT teacher_name FROM teacher,本节首页,19,前面介绍的数据检索所查询出来的数据都没有经过排序，这不利于对数据结果的查看。通过ORDER BY子句，可以改变查询结果的显示顺序。 ORDER BY 子句的语法形式如下： SELECT column_list FROM table_name ORDER BY column_name|expression ASC|DESC ,column_name|expression ASC|DESC.,8.2.5 使用ORDER BY子句对结果进行排序,20,在使用ORDER BY时，请注意以下几点: 如果没有指定是升序，还是降序，则缺省为升序。 可以对多达16个列执行ORDER BY语句。 ORDER BY结果依赖于安装时确定的排序规则。 【例8-10】从book表中按价格顺序检索出所有书本的信息。 USE jwgl GO SELECT * FROM book ORDER BY price,本节首页,21,查询数据时，经常需要对表中数据计算后才能得到满意的结果，SQL Server在数据查询中提供了计算的能力。 【例8-11】从表book中查询书本价格打九折后的书本信息。 USE jwgl GO SELECT book_id,book_name,price*0.9 FROM book,8.2.6 计算列的使用,本节首页,22,在WHERE子句中，也可以使用逻辑运算符来连接多个条件，构成一个复杂的条件进行查询。可以使用以下3种逻辑运算符：AND、OR、NOT。 具体格式如下： SELECT select_list FROM table_list WHERE NOT expression1 AND/ORNOT expression2 【例8-12】从book表中检索出书本价格大于等于15元、小于等于20元的书本的信息。 USE jwgl GO SELECT * FROM book WHERE (price =15 AND price =20 ),8.2.7 基于多个检索条件的查询,本节首页,23,8.3 使用T-SQL语句进行高级查,8.3.1 多表查询 8.3.2 使用UNION子句 8.3.3 使用GROUP BY子句 8.3.4 使用COMPUTE和COMPUTE BY子句 8.3.5 嵌套查询 8.3.6 在查询的基础上创建新表,本章首页,24,8.3.1 多表查询,1. 连接查询的ANSI连接语法形式和SQL Server语法形式 用于FROM子句的ANSI连接语法形式 SELECT column_list FROM table_name join_type JOIN table_name ON connection_condition WHERE search_condition ANSI的语法形式中： FROM子句：通过JOIN子句给出连接时使用到的表名。 join_type：连接的类型。 connection_condition：表与表之间的连接条件。 search_condition：表中行数据进入结果集所应满足的条件。,25,在ANSI标准中，连接的类型有如下三种： 内连接（INNER JOIN）：内连接返回的结果集中只包括满足连接条件的行。 交叉连接（CROSS JOIN）：交叉连接包括两个表中所有行的笛卡儿积。如一个表有10条记录，另如一个表有20条记录，交叉连接后将会产生200条记录。 外连接（OUTER JOIN）：外连接除了包括满足连接条件的行外，还包括其中某个表的全部行。 SQL Server 2005中默认的连接方式是内连接。 用于WHERE子句的SQL Server 连接语法形式 SQL Server 连接语法形式： SELECT column_list FROM table_list WHERE table_name.column_name JOIN_OPERATOR table_name.columnn and search_condition,26,SQL Server的语法形式中： FROM子句：列出连接时使用到的全部表名。 JOIN_OPERATOR连接操作符为：=、 =、。 search_condition：表中行数据进入结果集所应满足的条件。 2. 进行连接查询的要点： 一般而言，基于主键和外键指定查询条件，连接条件可使用“主键=外键”。 如果一个表有复合关键字，在连接表时，必须引用整个关键字。 应尽可能限制连接语句中表的数目，连接的表越多，查询处理的时间越长。 对于连接表的两个列应有相同或类似的数据类型。 不要使用空值作为连接条件，因为空值计算不会和其它任何值相等。,27,【例8-13】从student及student_course两个表中检索学生的学号、姓名、学习课程号及课程成绩。 USE jwgl GO select student.student_id , student.student_name , student_course.course_id , student_course.grade from student , student_course WHERE student.student_id = student_course.student_id,28,【例8-14】从student、course及student_course三个表中检索学生的学号、姓名、学习课程号、学习课程名及课程成绩。 USE jwgl GO SELECT student.student_id , student.student_name , student_course.course_id , course.course_name , student_course.grade from student , student_course , course WHERE student.student_id = student_course.student_id AND course.course_id = student_course.course_id,29,3. 使用别名 方法一: use jwgl select s.student_id , s.student_name , s_c.course_id , s_c.grade from student s , student_course s_c WHERE s.student_id = s_c.student_id 方法二: select s.student_id , s.student_name , s_c.course_id , s_c.grade from student AS s , student_course AS s_c WHERE s.student_id = s_c.student_id,本节首页,30,8.3.2 使用UNION子句,UNION子句的作用是把两个或多个SELECT语句查询的结果组合成一个结果集。UNION子句的语法形式如下： Select_statement UNION ALL Select_statement n 使用UNION时，请注意以下4点： UNION中从源表选择的所有列表必须具有相同列数、相似数据类型和相同的列序。 列名来自第一个SELECT语句。 如果希望整个结果集以特定的顺序出现，则UNION中应使用ORDER BY子句来指定对结果集的排序顺序。 在合并结果时，将从结果集中删除重复行。若使用ALL，结果集中包含所有的行。,31,【例8-15】用UNION子句将student表中学生的学号、姓名及teacher表中教师号、教师姓名组合在一个结果集中。 USE jwgl GO SELECT student_id , student_name FROM student UNION SELECT teacher_id , teacher_name FROM teacher,本节首页,32,8.3.3 使用GROUP BY子句,使用GROUP BY子句进行数据检索可得到数据分类的汇总统计、平均值或其它统计信息。 1. GROUP BY子句的语法形式 GROUP BY子句的语法形式如下： SELECT column_name_list FROM table_name WHERE search_condition GROUP BY ALL aggregate_expressionn HAVING search_condition,33,其中： aggregate_expression：分组表达式 search_condition：对分组汇总后数据进入结果集的筛选条件 在使用GROUP BY 子句时，注意以下几点： SQL Server为每个定义的组产生一个列值，每个组只返回一行，不返回详细信息。 如果包括WHERE子句，SQL Server只分组汇总满足WHERE条件的行。 在包含GROUP BY子句的查询语句中，SELECT子句后的所有字段列表，除集合函数外，都应包含在GROUP BY子句中，否则将出错。 GROUP BY子句的列表中最多只能有8060个字节。 不要在含有空值的列上使用GROUP BY子句，因为空值将作为一个组来处理。 如果GROUP BY子句使用ALL关键字，WHERE子句将不起作用。 HAVING子句排除不满足条件的组。,34,2. 不带HAVING的GROUP BY 子句的用法 GROUP BY子句是按列或表达式分组汇总，为每组产生一个值，一般和集合函数一起使用。 【例8-16】用GROUP BY句汇总出student_course表中学生的学号及总成绩。 USE jwgl GO SELECT 学号 = student_id , 总成绩 = sum(grade) FROM student_course GROUP BY student_id,35,3. 带HAVING的GROUP BY 子句的用法 可以用HAVING子句对分组汇总后进入结果集的各组进行限制。 HAVING子句是针对GROUP BY子句的，没有GROUP BY子句时使用HAVING子句是没有意义的。 【例8-17】用GROUP BY句汇总出student_course表中总分大于450分的学生的学号及总成绩。 USE jwgl GO SELECT 学号 = student_id , 总成绩 = sum(grade) FROM student_course GROUP BY student_id HAVING sum(grade)450,本节首页,36,8.3.4 使用COMPUTE和COMPUTE BY子句,使用COMPUTE和COMPUTE BY就既能浏览数据又看到统计的结果。 COMPUTE BY子句的语法形式如下： COMPUTE row_aggregate (column_name ) n BY column_name_list 【例8-18】用COMPUTE子句汇总出student_course表中每个学生的学号及总成绩。 USE jwgl GO SELECT 学号 = student_id , 成绩 = grade FROM student_course ORDER BY student_id COMPUTE SUM(grade),37,COMPUTE类似于总计。如在COMPUTE后加上BY关键字，则查询的结果为带具体内容的分类统计。 【例8-19】用COMPUTE BY子句按学号汇总出student_course表中每个学生的的学号及总成绩。 USE jwgl GO SELECT 学号 = student_id , 成绩 = grade FROM student_course ORDER BY student_id COMPUTE SUM(grade) BY student_id,38,值得注意的是，在使用COMPUTE和COMPUTE BY时，有如下限制: DISTINCT不允许同集合函数一起用，不能包含text、ntext、image数据类型。 COMPUTE子句中的列必须在SELECT后面的选择列表中。 SELECT INTO不与COMPUTE子句一起使用。 若使用了COMPUTE BY，则必须使用ORDER BY。 COMPUTE BY后出现的列必须与ORDER BY后出现的列相同，或者是它的子集。它必须具有相同的从左到右顺序并且以相同的表达式开头，不能跳过任何表达式。,本节首页,39,8.3.5 嵌套查询,如果先通过一个查询查出一个结果集，再在这个结果集中进行查询的话就是嵌套查询。嵌套查询是用一条SELECT语句作为另一条SELECT语句的一部分。外层的SELECT语句叫外部查询，内层的SELECT语句叫内部查询（或子查询）。 嵌套查询的执行流程是，首先执行内部查询，它查询出来的数据并不被显示出来，而是传递给外层SELECT语句，作为该SELECT语句的查询条件使用。子查询可以多层嵌套。 1. 使用IN或NOT IN关键字 单值子查询是指子查询只返回一行数据。多值子查询是指子查询返回的不是一行而是一组行数据。前者可以用“=”、IN 或NOT IN和其外部查询相联系，后者则必须使用IN 或NOT IN和其外部查询相联系。IN表示属于的关系，即是否在所选数据集合之中。NOT IN则表示不属于集合或不是集合的成员。,40,【例8-20】 查询出“g99402”班所有男生的学号、课程号及相应的成绩。 USE jwgl GO SELECT student_course.student_id , student_course.course_id , student_course.grade FROM student_course WHERE student_id IN ( SELECT student_id FROM student WHERE class_id = g99402 AND sex = 1 ),41,2. 使用EXSISTS 或NOT EXSISTS关键字 EXISTS关键字用来确定数据是否在查询列表中存在。EXISTS表示一个子查询至少返回一行时条件成立。 和使用IN关键字不同的是，IN连接的是表中的列，而EXISTS连接的是表和表，通常不需要特别指出列名，可以直接使用 *。由于EXISTS连接的是表，所以，子查询中必须加入表与表之间的连接条件。 【例8-21】 使用EXSISTS关键字查询出“g99403”班学生的学号、课程号及相应的成绩。 USE jwgl GO SELECT student_course.student_id , student_course.course_id , student_course.grade FROM student_course WHERE EXISTS ( SELECT * FROM student WHERE student_course.student_id = student.student_id AND student.class_id = g99403 ),42,3. 使用嵌套子查询的几点说明： 首先对子查询(内部查询)求值。 外部查询依赖于子查询的求值结果。 子查询必须被括在圆括号内。 以比较操作符引导的子查询的选择列表只能包括一个表达式或列名。否则SQL Server会报错。,本节首页,43,8.3.6 在查询的基础上创建新表,SELECT INTO的作用是，在查询的基础上创建新表。若建临时表，必须在表前设置#（局部临时表）或# #（全局临时表）。新表的行和列是来自查询结果，临时表是创建在tempdb数据库上。 【例8-22】创建#temp_grade的临时表，该临时表有两个列：student_id,grade，要求学生的成绩grade大于95分。然后，再从临时表#temp_grade查询数据。 USE jwgl GO SELECT student_id , grade into #temp_grade FROM student_course WHERE grade=95 USE tempdb GO SELECT * FROM #temp_grade,44,也可以用SELECT语句创建一个永久表。永久表是创建在基表所在的数据库里。 【例8-23】创建grade_table的永久表，该临时表有两个列：student_id,grade.要求学生的成绩grade大于95分。 USE jwgl GO SELECT student_id , grade into grade_table FROM student_course WHERE grade95,本节首页,45,8.4 用户自定义函数在数据检索中的应用,8.4.1 用户自定义函数的定义及分类 用户自定义函数最多可以有 1024 个输入参数并返回一个简单的数值。 可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、使用 DROP FUNCTION 语句除去用户定义函数。每个完全合法的用户定义函数名必须唯一 。 分类:标量型用户自定义函数、表值函数 ； 表值函数又分为内联表值用户自定义函数、多语句表值用户自定义函数。,46,8.4.2 标量值型用户自定义函数的定义、使用及删除 标量值型用户自定义函数的函数体被封装在以BEGIN语句开始，END语句结束的范围内。在 BEGIN.END 块中定义的函数主体包含返回该值的 Transact-SQL 语句系列。 【例8-24】定义一个标量值型用户自定义函数，按出生年月计算年龄，然后从学生情况表student中检索出含有年龄的学生信息。 CREATE FUNCTION js_old ( vardate datetime , curdate datetime ) RETURNS tinyint AS BEGIN RETURN datediff ( yy , vardate , curdate ) END,47,【例8-25】从学生情况表student中检索出含有年龄的学生信息。 use jwgl go select student_id as 学号 , student_name as 姓名 , class_id as 班级 , dbo.js_old(birth,getdate() as 年龄 from student 【例8-26】使用SQL语句删除用户自定义函数js_old。 drop function js_old,48,8.4.3 表值用户自定义函数的定义、使用及删除 1. 内联表值用户自定义函数的定义、使用及删除 内联表值用户自定义函数返回一个Table型数据，对内联表值用户自定义函数而言，返回的结果只是一个SELECT语句所返回的一系列表值的结果集。 【例8-27】定义一个内联自定义函数，用班级号作参数从学生情况表student中检索出含有学号、学生姓名的学生信息。 CREATE FUNCTION table_call ( class_Parameter nvarchar(30) ) RETURNS table AS RETURN ( SELECT student_id, student_name FROM student WHERE class_id = class_Parameter ),49,【例8-28】以班级号“g99403”作参数从学生情况表student中检索出含有学号、学生姓名的学生信息。 SELECT * FROM table_call(g99403) 内联表值用户自定义函数遵从以下规则： 1） RETURNS 子句仅包含关键字 table。不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置。 2）function_body 不由 BEGIN 和 END 分隔。 3）RETURN 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表。直接表值自定义用户函数中使用的 SELECT 语句受到与视图中使用的 SELECT 语句相同的限制。,50,2. 多语句表值用户自定义函数的定义、使用及删除 对于多语句表值函数，在 BEGIN.END 块中定义的函数主体包含 TRANSACT-SQL 语句，这些语句可生成行并将行插入将返回的表中。 【例8-29】定义一个自定义函数，用班级号作参数，然后从学生情况表student中检索出含有学号、学生姓名、性别及选课信息。 CREATE FUNCTION muti_table_call(name_class char(6) RETURNS returntable TABLE ( student_id char(8), student_name nvarchar(8), sex bit, class_id char(8), course_id char(10) ),51,AS BEGIN INSERT returntable SELECT s.student_id, s.student_name, s.sex, s.class_id,s_c.course_id FROM student AS S INNER JOIN student_course as s_c ON s.student_id = s_c.student_id and s.class_id=name_class RETURN END,52,在返回 table 的多语句用户定义函数中： 1）RETURNS 子句为函数返回的表定义局部返回变量名。RETURNS 子句还定义表的格式。局部返回变量名的作用域位于函数内。 2）函数主体中的 Transact-SQL 语句生成行并将其插入 RETURNS 子句所定义的返回变量。 3）当执行 RETURN 语句时，插入变量的行以函数的表格格式输出形式返回。RETURN 语句不能有参数。 4）函数中返回 table 的 Transact-SQL 语句不能直接将结果集返回用户。函数返回用户的唯一信息是由该函数返回的 table。 【例8-29】以班级号“g99402”作参数从学生情况表student和student_course中检索出该班学生选修课的信息 SELECT * FROM muti_table_call(g99402),本章首页,53,8.5 数据查询的优化,1.提高SQL语句的可读性 多使用别名、括号、回车，将查询的各个组成部分简单化或分隔开来。 例如： （方法一） select student.student_id,student.student_name, student_course.course_id,student_course.grade from student,student_course WHERE student.student_id=student_course.student_id,54,方法二 select s.student_id,s.student_name,s_c.course_id,s_c.grade from student s,student_course s_c WHERE s.student_id=s_c.student_id,55,2.影响数据检索性能的因素 1）不经常使用COMMIT命令。 2）批处理过程会影响终端用户的日常处理。 3）使用复杂的SQL语句。 4）SQL查询语句中条件的安排顺序问题。 5）不正确地在小表上使用索引或在具有大量重复数据的表上使用索引。 6）数据库的排序区是一个临时表，它用来操作和SQL语句有关的排序工作。,56,3.优化检索语句、提高查询性能的方法 合理安排查询中WHERE字句中条件成分的排列次序 合理、适当地增加索引 在查询中，有些操作符是应当尽量避免的，如OR操作符（例如P155） 经常提交数据 对于相同规律的查询，建议使用存储过程,本章首页,57,8.6 分布式查询简介,分布式查询支持 SQL Server 用户访问： 存储在多个 SQL Server 实例中的分布式数据； 存储在各种可使用 OLE DB 提供程序访问的关系和非关系数据源中的异类数据 。 使用系统存储过程 sp_addlinkedserver可建立到远程服务器的链接。假设链接服务器的名称是AccuntingSQLSrvr，数据库名称是Market，要访问的表是suppliers，执行如下语句： EXEC sp_addlinkedserver server = AccuntingSQLSrvr , product_name = SQL Server 可在本地服务器上定义一个远程服务器AccuntingSQLSrvr，并指定OLE DB提供者。,58,使用系统存储过程sp_addlinkedsrvlogin可建立本地SQL Server和远程SQL Server之间登录ID和密码的映射。执行如下语句： EXEC sp_addlinkedsrvlogin AccuntingSQLSrvr,Accountwriter,ABC,123 执行此系统存储过程后，将允许以Accountwriter登录名登录到本地SQL Server的用户以ABC作为登录帐号、123为密码访问远程服务器AccuntingSQLSrvr上的数据。 Transact-SQL 语句中引用链接服务器中对象的格式是“链接服务器名.数据库名.对象属主.对象名”。下面的语句将访问AccuntingSQLSrvr服务器上Market数据库中的表suppliers： SELECT * FROM AccuntingSQLSrvrdbo.suppliers,本章首页,