第四章数据库查询

第四章第四章 数据库查询数据库查询 4.1 SELECT 语句结构语句结构 4.1.1 SELECT 语句的语法语句的语法 4.1.2SELECT 语句的基本结构语句的基本结构 4.2 SELECT 子句的使用子句的使用 4.2.1 指定查询列指定查询列 4.2.2 查询结果集中包括导出列查询结果集中包括导出列 4.2.3 指定结果集中列的别名指定结果集中列的别名 4.2.4 显示常数列显示常数列 4.2.5 删除重复行删除重复行 4.2.6 排序排序 4.2.7 限制返回行数限制返回行数 4.3 WHERE 子句的使用子句的使用 4.3.1 比较运算比较运算 4.3.2 范围范围 4.3.3 模式模式 4.4 聚合和汇总聚合和汇总 4.5 多表查询多表查询 4.6 嵌套查询嵌套查询 4.7 通过查询改变数据表数据通过查询改变数据表数据 4.8 视图视图 本章主内容要 1、SELECT 语句结构 2、SELECT 使用 3、WHERE 子句的使用 4、聚合和汇总 5、多表查询 6、视图 本章重点 1、SELECT 使用 2、WHERE 子句的使用 3、聚合和汇总 4、多表查询 5、视图 本章难点 1、聚合和汇总 2、多表查询 3、视图 授课方法 讲授+实验+作业 学时：12 课时 授课过程如下： 4.1 SELECT 语句语句 基本的结构格式如下：基本的结构格式如下： SELECT select_list &指明要查询的列指明要查询的列(SELECT 子句子句) INTO new_table &指定用查询结果集创建新表指定用查询结果集创建新表 FROM table_source &指明查询各表的表名指明查询各表的表名 WHERE search_condition &指明查询条件指明查询条件 GROUP BY group_by_expression & group_by_expression 指定的列对结果指定的列对结果 集进行分组集进行分组 HAVING search_condition &从从 FROM、where 或或 GROUP BY 子句中创子句中创 建中间结果集对行进行筛选。通常与建中间结果集对行进行筛选。通常与 GROUP BY 一起使用。一起使用。 ORDER BY order_expression ASC | DESC &对对 order_expression 进进 行排序行排序 ASC(默认默认)升序排序升序排序. DESC:降序排序降序排序 4.2 SELECT 子句的使用子句的使用 SELECT ALL | DISTINCT &ALL(默认值默认值),显示所有满足条件的行显示所有满足条件的行. DISTINCT 删除重复的行删除重复的行 TOP n PERCENT WITH TIES & TOP n:指定只返回查指定只返回查询询 结果集中的前结果集中的前 N 行行. PERCENT(n 只能是到只能是到 100)按百分比返回按百分比返回 With ties 返回附加的行和并列的行返回附加的行和并列的行 := * | table_name | view_name | table_alias .*& “*”表示返回指定表示返回指定 表中所有列表中所有列 | column_name | expression | IDENTITYCOL | ROWGUIDCOL AS column_alias | column_alias = expression ,.n 参数说明参数说明: 例例 4.1:use teachdb Select s_name,s_department Into stu1 From student Where s_sex=女女 例例 4.2 Use teachdb Select * From student 例例 4.3 Use teachdb Select s_no,s_department,S_name From student 例例 4.4:查询结果包括导出列查询结果包括导出列 Use teachdb Select s_no,c_no,score,score+10,round(score*0.8),2) From choice 例例 4.5 结果集指定别名结果集指定别名 Use teachdb Select s_no AS 学号学号,c_no 课课,程代号程代号, 原成绩原成绩=score, 加分后的成绩加分后的成绩=score+10 From choice 定义别名的方法定义别名的方法: 列列 别名别名 列列 AS 别名别名 别名别名=列列 例例 4.5 显示常数列显示常数列 Use teachdb Select s_no AS 学号学号,c_no 课程代号课程代号, 原成绩原成绩=score, 加加 10 分分=AS, 修改成绩修改成绩=score+10 From choice 例例 4.6 删除重复行删除重复行 use teachdb Select DISTINCT s_department From student 例例 4.7 (order by)排序排序 格式格式:(见见 P114) use teachdb Select s_name,s_birthday From student Order by s_birthday desc 例例 4.8 4.9 指定返回行数指定返回行数 use teachdb Select top 3 s_name,s_birthday & Select top 20 PERCENT s_name,s_birthday From student Order by s_birthday 例例 4.10 显示年龄最大的同学显示年龄最大的同学 use teachdb Select top 1 WITH TIES s_name,s_birthday From student Order by YEAR(s_birthday) 4.3 WHERE 子句的使用子句的使用 很多情况下，用户只需查询满足某些条件的数据行，这就需要使用很多情况下，用户只需查询满足某些条件的数据行，这就需要使用 WHERE 子子 句。句。 WHERE 子句用于指定检索限定条件，从而获得需要检索的特定行。子句用于指定检索限定条件，从而获得需要检索的特定行。 语法：语法： WHERE | 4.3.1 比比 较较 显示显示 choice 表中成绩小于表中成绩小于 60 分的同学信息分的同学信息 USE teachdb SELECT * FROM choice WHERE score大于大于 =大于等于大于等于 ,!=不等于不等于 !不大于不大于 !AVG(c_score) 4.4.2 分组分组(GROUP BY) GROUP BY 子句可以对表进行分组汇总.为划分的每一个组产生的个汇总值. 与聚合函数配合使用. 格式: GROUP BY ALL分组表达式 说明: ALL:对表中所有列进行分组,不满足汇总条件的显示空值. 分组表达式也称分组列:列表中定义的别名不能指定分组列. Tsxt,image 的列不能分组. Use teachdb Select s_no,avg=AVG(score) From choice Where s_no160 ORDER BY c_no 工作过程: 先用 GROUP BY 对 c_no 进行分组汇总,用 HAVING 设置条件限定分 组后返回汇总的行. 4.4.4 计算和汇总(compute/ compute by) 使用(compute 聚合函数)生成汇总行:查询结果的最后生成汇总数据行. 例: Use teachdb Select c_no, score From choice Where c_no like 10% ORDER BY c_no Compute sum(score) 注意: Compute 子句中指定的列必须是 Select 中的列 Compute 子句中聚合函数不能使用 DISTINCT 使用 Compute 子句后不能生成新表. 使用 compute by 生成分组汇总行 Use teachdb Select c_no, score From choice Where c_no like 10% ORDER BY c_no Compute sum(score) by c_no Compute sum(score) 生成明细行和汇总行 注意: compute by 与 ORDER BY 一起使用, compute by 指定的列必须与 ORDER BY 指定的列相同或者为其子集,顺序也必须相同. compute by 和 Compute 可出现在同一条 SELSCT 语句中以得到分组汇总值和总 汇总值. compute by 和 Compute 子句中不能使用 ntext,text,image 数据类型. 在大多数情况下，使用统计函数返回的是所有行数据的统计结果。如果需要按 某一列数据的值进行分类，在分类的基础上再进行查询，就要使用 GROUP BY 子句了。 简单分组 【例 24】按书的种类分类，求出 3 种类型书籍的价格总和、平均价格以及各 类书籍的数量。 use pubs go select type,sum(price)sum_price,avg(price) avg_price,count(*) from titles where type in(business,mod_cook,trad_cook) group by type go 查询结果是： typesum_priceavg_price - - - - business54.920013.7300 4 mod_cook22.980011.4900 2 trad_cook47.890015.9633 3 （所影响的行数为 3 行） 通过这个结果可以看出，所有的统计函数都是对查询出的每一行数据进 行分类以后再进行统计计算。所以在结果集合中，对所进行分类的列的 每一种数据都有一行统计结果值与之对应。 说明：GROUP BY 子句中不支持对列分配的假名，也不支持任何使用了统计函 数的集合列。另外，对 SELECT 后面每一列数据除了出现在统计函数中的列以 外，都必须在 GROUP BY 子句中应用。 下面的查询是错误的。 use pubs select pub_id, type, avg(price)avg_price,sum(price)sum_price from titles where type in (business,trade_cook,mod_cook) group by type go 服务器返回错误信息： 服务器: 消息 8120，级别 16，状态 1，行 2 列 titles.pub_id 在选择列表中无效，因为该列既不包含在聚合函数中， 也不包含在 GROUP BY 子句中。 我们也可以根据多列进行分组。这时统计函数按照这些列的惟一组合来进 行统计计算。 【例 25】按书的种类和出版商代号分类，返回一个平均价格和总价格。 use pubs go select type,pub_id,avg(price)avg_price,sum(price)sum_price from titles where type in (business,trade_cook,mod_cook) group by type,pub_id go 查询结果是： typepub_idavg_price sum_price - - - - business07362.99002.9900 mod_cook087711.490022.9800 business138917.310051.9300 （所影响的行数为 3 行） 使用 HAVING 筛选结果 当完成数据结果的查询和统计后，可以使用 HAVING 关键字来对查询和统计的 结果进行进一步的筛选。 【例 26】查询所有价格超过 10 美元的书的种类和平均价格。 use pubs go select type , avg(price) avg_price from titles where price$10 group by type go 查询结果如下： typeavg_price - - business17.3100 mod_cook19.9900 popular_comp21.4750 psychology 17.5100 trad_cook 15.9633 （所影响的行数为 5 行） 【例 27】在所有价格超过 10 美元的书中，查询所有平均价格超过 18 美元的书 的种类和平均价格。 use pubs go select type , avg(price) avg_price from titles where price10 group by type having avg(price)$18 go 查询的结果是： typeavg_price - - mod_cook19.9900 popular_comp21.4750 （所影响的行数为 2 行） WHERE 子句在求平均值之前从表中选择所需要的行，HAVING 子句在进行统 计计算后产生的结果中选择所需要的行。 45 多表查询多表查询 在数据库的应用中，经常需要从多个相关的表中查询数据，这就需要使用连接 查询。 连接（Inner join） 内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列 进行比较，将两个表中满足连接条件的行组合起来，作为结果。自然连接有两 种形式的语法。 语法一： SELECT 列 FROM 表 1 insert JION 表 2 ON 表 1.列=表 2.列 语法二： SELECT 列 FROM 表 1,表 2 WHERE 表 1.列=表 2.列 【例 28】从 titles 和 titleauthor 表中查询书的书号、书名、作者号、类型和价格。 use pubs go select titles.title_id,title,au_id,type,price from titles join titleauthor on titles.title_id=titleauthor.title_id 得到结果为： title_idtitleau_idtype price - - - - - - BU1032The Busy Executives Database Guide213-46-8915 business19.9900 BU1032The Busy Executives Database Guide409-56-7008 business19.9900 BU1111Cooking with Computers: Surreptitious Balance Sheets267-41-2394 business11.9500 BU1111Cooking with Computers: Surreptitious Balance Sheets724-80-9391 business11.9500 BU2075You Can Combat Computer Stress!213-46-8915 business2.9900 （所影响的行数为 25 行） 在上述查询中 titles 表与 titleauthor 表通过 title_id 列进行连接，这样可以在一次 查询中从两个表获得数据。 两个以上的表也可以进行连接。 【例 29】从 titles、authors 和 titleauthor 表中查询书的书号、书名、作者号和作 者名。 use pubs go select titles.title_id,title,authors.au_id,au_lname from titles join titleauthor on titles.title_id=titleauthor.title_id join authors on authors.au_id=titleauthor.au_id 得到的结果为： title_idtitleau_id au_lname - - - - - PC1035But Is It User Friendly?238-95-7766Carson PS1372Computer Phobic AND Non-Phobic Individuals: Behavior Variations724-80-9391 MacFeather PS1372Computer Phobic AND Non-Phobic Individuals: Behavior Variations756-30-7391 Karsen BU1111Cooking with Computers: Surreptitious Balance Sheets267-41-2394 OLeary BU1111Cooking with Computers: Surreptitious Balance Sheets724-80-9391 MacFeather （所影响的行数为 25 行） 通过上述查询可以将 titles、authors 和 titleauthor 组合起来，把每一本书和它的 作者对应。 在从两个或两个以上的表中进行查询时，如果两个表中的列名相同，需要在列 名前面加上表名（或表的别名）作为前缀，如上述例子中的 titles.title_id 与 titleauthor.title_id。在列名不同时，列名前可以不加表名，但有时也会加上表名， 以增强可读性。 外连接（Outer join） 在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中 可以只限制一个表，而对另外一个表不加限制（即所有的行都出现在结果集中） 。 外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的 表不加限制；右外连接是对右边的表不加限制；全外连接对两个表都不加限制， 所有两个表中的行都会包括在结果集中。 左外连接的语法为： SELECT 列 FROM 表 1 LEFTOUTERJOIN 表 2 ON 表 1.列 1=表 2.列 2 右外连接的语法为： SELECT select_list FROM 表 1 RIGHTOUTERJOIN 表 2 ON 表 1.列 1=表 2.列 2 全外连接的语法为： SELECT select_list FROM 表 1 FULLOUTER JOIN 表 2 ON 表 1.列 1=表 2.列 2 例如：查询书的书号、书名 和作者的作者号、作者名。 use pubs go select title_id,title,au_id,au_lname from titles full join authors on titles.title_id=authors.au_id 查询结果如下： title_idtitleau_id au_lname - - NULLNULL267-41-2394 OLeary NULLNULL274-80-9391 Straight NULLNULL341-22-1782 Smith NULLNULL09-56-7008 Bennet NULLNULL427-17-2319 Dull NULLNULL472-27-2349 Gringlesby NULLNULL86-29-1786 Locksley NULLNULL527-72-3246 Greene NULLNULL648-92-1872 Blotchet-Halls NULLNULL672-71-3249 Yokomoto NULLNULL712-45-1867 del Castillo NULLNULL722-51-5454 DeFrance NULLNULL724-08-9931 Stringer NULLNULL724-80-9391 MacFeather NULLNULL756-30-7391 Karsen NULLNULL807-91-6654 Panteley NULLNULL846-92-7186 Hunter NULLNULL893-72-1158 McBadden NULLNULL899-46-2035 Ringer NULLNULL998-72-3567 Ringer BU1032The Busy Executives Database GuideNULLNULL BU1111Cooking with Computers: Surreptitious Balance SheetsNULLNULL BU2075You Can Combat Computer Stress!NULLNULL BU7832Straight Talk About ComputersNULLNULL （所影响的行数为 41 行） 3自连接（Self join） 连接操作不仅可以在不同的表上进行，而且在同一张表内可以进行自身连接，即将同一个 表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须 为表指定两个别名，使之在逻辑上成为两张表。 例如：在 titles 表中查询同名的作者。 use pubs go select a1.au_fname,a2.au_fname,a1.au_lname from authors a1 join authors a2 on a1.au_lname=a2.au_lname where a1.au_id.view_name(column,n) WITHWITH ,n,n ASAS Select_statementSelect_statement WITHWITH CHECKCHECK OPTIONOPTION 说明说明: : ENCRYPTION:ENCRYPTION:加密加密 CREATECREATE VIEWVIEW 语句文本语句文本. . SCHEMABINDING:SCHEMABINDING:表示在表示在 SELECTSELECT 语句中如果包含表、视图或者引用用户自定义函数语句中如果包含表、视图或者引用用户自定义函数, ,则表名、则表名、 视图名或函数名前必须包含所有者前缀视图名或函数名前必须包含所有者前缀 。 4.10.24.10.2 修改视图修改视图 ALTERALTER VIEWVIEW view_nameview_name (column,n)(column,n) WITHWITH ENCRYPTIONENCRYPTION ASAS Select_statementSelect_statement WITHWITH CHECKCHECK OPTIONOPTION 4.10.34.10.3 删除视图删除视图 DROPDROP VIEWVIEW view_nameview_name ,n,n 4