SQL第08章数据检索.ppt

第8章 数据检索,8.1 SELECT 语句 8.2 使用SELECT 语句进行简单查询 8.3 使用T-SQL语句进行高级查询 8.4 使用企业管理器进行查询 8.5 综合练习,返回目录,2,8.1 SELECT 语句,8.1.1 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 8.1.2 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】从student表中分别检索出学生的所有信息及学号、姓名信息。 USE jwgl GO SELECT * FROM student SELECT student_id , student_name FROM student 注意： 在SELECT后的列名的顺序决定了显示结果中的列序 在查找多列内容时，用,将各字段分开,本节首页,8,在缺省情况下，执行上面的SQL语句后，查询结果中显示的列标题是列名。可以在SELECT语句中用列标题=列名或列标题AS 列名 来改变列标题的显示。 【例8-2】从student表中分别检索出学生的学号、姓名信息并分别加上“学生”、“学号”的标题信息。 USE jwgl GO SELECT 学号=student_id , 姓名=student_name FROM student SELECT student_id AS 学号, student_name AS 姓名 FROM student,8.2.2 改变列标题的显示,本节首页,9,大部分查询都不是针对表中所有行的查询，而是从整个表中选出符合条件的信息，要实现这样的查询就要用到WHERE子句。 1. WHERE子句的语法形式 WHERE子句的语法形式如下： SELECT selctc_list FROM table_list WHERE select_condition 其中SQL Server支持的搜索条件如下： 比较： =、=、 范围：BETWEEN（在某个范围内） 、NOT BETWEEN（不在某个范围内）,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】从student_course表中检索成绩小于60分的学生信息。 USE jwgl GO SELECT * FROM student_course WHERE grade60 在写基于比较条件的选择行的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】从student_course表中检索成绩介于60至80分之间的学生信息。 USE jwgl GO SELECT * FROM student_course where grade BETWEEN 60 AND 80,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关键字进行模糊查询。 BETWEEN子句的语法形式如下： 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】分别从student表中检索出前5个及表中前面20%的学生的信息。 USE jwgl GO SELECT top 5 * FROM student SELECT top 20 PERCENT * FROM student 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】从student表中检索出所有姓名不重复的学生的信息。 USE jwgl GO SELECT DISTINCT student_name FROM student,本节首页,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】从student_course表中按成绩顺序检索出所有学生的信息。 USE jwgl GO SELECT * FROM student_course ORDER BY grade,本节首页,21,查询数据时，经常需要对表中数据计算后才能得到满意的结果，SQL Server在数据查询中提供了计算的能力。 【例8-11】从数据库northwind表products中检索出货品号（productid）、货品单价（unitprice）、货品库存量（unitsinstock）及货品的总价值。 USE northwind GO SELECT productid , unitprice, unitsinstock , unitprice * unitsinstock FROM products,8.2.6 计算列的使用,本节首页,22,在WHERE子句中，也可以使用逻辑运算符来连接多个条件，构成一个复杂的条件进行查询。可以使用以下3种逻辑运算符：AND、OR、NOT。 具体格式如下： SELECT select_list FROM table_list WHERE NOT expression1 AND/ORNOT expression2 【例8-12】从student_course表中检索出成绩大于等于80分、小于等于100分的学生的信息。 USE jwgl GO SELECT * FROM student_course WHERE (grade=80 AND grade=100 ),8.2.7 基于多个检索条件的查询,本节首页,23,8.3 使用T-SQL语句进行高级查,8.3.1 多表查询 8.3.2 使用UNION子句 8.3.3 使用GROUP子句和GROUP BY子句 8.3.4 使用COMPUTE和COMPUTE BY子句 8.3.5 嵌套查询 8.3.6 在查询的基础上创建新表,本章首页,24,8.3.1 多表查询,1. 连接查询的ANSI连接语法形式和SQL Server语法形式 Microsoft SQL Server 2000兼容以下两种连接形式：用于FROM子句的ANSI连接语法形式和用于WHERE子句的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 2000中默认的连接方式是内连接。 用于WHERE子句的SQL Server 连接语法形式 SQL Server 连接语法形式： SELECT column_list FROM table_list WHERE table_name.column_name JOIN_OPERATOR table_name.columnn ON 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,8.3.2 使用UNION子句,UNION子句的作用是把两个或多个SELECT语句查询的结果组合成一个结果集。这里查询的多个表不要求有关联。 UNION子句的语法形式如下： Select_statement UNION ALL Select_statement n 使用UNION时，请注意以下4点： UNION中从源表选择的所有列表必须具有相同列数、相似数据类型和相同的列序。 列名来自第一个SELECT语句。 如果希望整个结果集以特定的顺序出现，则UNION中应使用ORDER BY子句来指定对结果集的排序顺序。 在合并结果时，将从结果集中删除重复行。若使用ALL，结果集中包含所有的行。,30,【例8-15】用UNION子句将student表中学生的学号、姓名及teacher表中教师号、教师姓名组合在一个结果集中。 USE jwgl GO SELECT student_id , student_name FROM student UNION SELECT teacher_id , teacher_name FROM teacher,本节首页,31,8.3.3 使用GROUP子句和GROUP BY子句,如果要在数据检索时对表中数据按照一定条件进行分组汇总或求平均值，就要在SELECT语句中与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,32,其中： 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子句排除不满足条件的组。,33,2. 不带HAVING的GROUP BY 子句的用法 使用GROUP BY子句进行分组查询，SQL Server把查询到的数据划分成多个组，并且为每个组返回一个结果。GROUP BY子句是按列或表达式分组汇总，为每组产生一个值，一般和集合函数一起使用。 【例8-16】用GROUP BY句汇总出student_course表中学生的学号及总成绩。 USE jwgl GO SELECT 学号 = student_id , 总成绩 = sum(grade) FROM student_course GROUP BY student_id,34,3. 带HAVING的GROUP BY 子句的用法 对数据分组汇总时，也可以用HAVING子句对分组汇总后进入结果集的各组进行限制。 HAVING子句的作用同WHERE子句相似，都是给出查询条件。所不同的是，WHERE子句是检查每条记录是否满足条件，而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,本节首页,35,8.3.4 使用COMPUTE和COMPUTE BY子句,使用GROUP子句对查询出来的数据做分类求和或求平均值，只能显示统计的结果，看不到具体的数据。使用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),36,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,37,值得注意的是，在使用COMPUTE和COMPUTE BY时，有如下限制: DISTINCT不允许同集合函数一起用，不能包含text、ntext、image数据类型。 COMPUTE子句中的列必须在SELECT后面的选择列表中。 SELECT INTO不与COMPUTE子句一起使用。 若使用了COMPUTE BY，则必须使用ORDER BY。 COMPUTE BY后出现的列必须与ORDER BY后出现的列相同，或者是它的子集。它必须具有相同的从左到右顺序并且以相同的表达式开头，不能跳过任何表达式。,本节首页,38,8.3.5 嵌套查询,如果先通过一个查询查出一个结果集，再在这个结果集中进行查询的话就是嵌套查询。嵌套查询是用一条SELECT语句作为另一条SELECT语句的一部分。外层的SELECT语句叫外部查询，内层的SELECT语句叫内部查询（或子查询）。 嵌套查询的执行流程是，首先执行内部查询，它查询出来的数据并不被显示出来，而是传递给外层SELECT语句，作为该SELECT语句的查询条件使用。子查询可以多层嵌套。 1. 使用IN或NOT IN关键字 单值子查询是指子查询只返回一行数据。多值子查询是指子查询返回的不是一行而是一组行数据。前者可以用“=”、IN 或NOT IN和其外部查询相联系，后者则必须使用IN 或NOT IN和其外部查询相联系。IN表示属于的关系，即是否在所选数据集合之中。NOT IN则表示不属于集合或不是集合的成员。,39,【例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 ),40,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 ),41,3. 使用嵌套子查询的几点说明： 首先对子查询(内部查询)求值。 外部查询依赖于子查询的求值结果。 子查询必须被括在圆括号内。 以比较操作符引导的子查询的选择列表只能包括一个表达式或列名。否则SQL Server