数据库查询课件.ppt

a,1,数据库查询,13.1 SELECT语句概述13.2 简单查询13.3 分类汇总13.4 连接查询13.5 子查询13.6 查询结果保存,a,2,【任务（实验18，19）】数据库简单查询、复杂查询,实验内容:P270-P276,a,3,查询步骤,a,4,13.1 SELECT语句概述,13.1.1 SELECT语句的语法格式13.1.2 SELECT语句的执行方式,a,5,13.1.1 SELECT语句的语法格式,SELECT语句的基本语法格式如下：SELECT INTO FROM WHERE GROUP BY HAVING ORDER BY ASC|DESC,a,6,13.1.2 SELECT语句的执行方式,1使用企业管理器执行,a,7,13.1.2 SELECT语句的执行方式,2使用查询分析器执行,a,8,13.2 简单查询,13.2.1 SELECT子句13.2.2 WHERE子句13.2.3 ORDER BY子句,a,9,13.2.1 SELECT子句,SELECT子句是对表中的列进行选择查询，也是SELECT语句最基本的使用，基本形式如下：SELECT 列名1,列名n选择列是限定返回某些列组成结果集。1选择所有列 选择所有的列，既可以在查询中逐一地列示出所有的列名，也可以使用符号“*”。例 查询本校教师信息SELECT*FROM 本校教师,a,10,2选择指定的列 若要选择表中的特定列，应在选择列表中明确地列出每一列，在列与列之间用逗号隔开。例：SELECT 教师编号，教师姓名，系，专业方向，职称FROM 本校教师,a,11,13.2.1 SELECT子句,3修改查询结果中的列标题 当希望查询结果中的某些列不显示表结构中规定的列标题，而使用用户自己另外选择的列标题时，可以在列名之后使用AS子句来更改查询结果中的列标题名。,例如：use xs select 学号 as number,姓名 as nameFrom XSDAgo,Number=学号,a,12,13.2.1 SELECT子句,4计算列值 使用SELECT语句对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为查询结果。格式为：SELECT expression,expression,例如：use xs select 学号 课程标号，期末成绩=成绩*0.8From XSCJgo,a,13,13.2.1 SELECT子句,4消除结果集中的重复行 对表只选择某些列时，可能会出现重复行，例如，若对XSDA表只选择系名和总学分，则会出现多行重复的情况。可以使用DISTINCT关键字消除结果集中的重复行。DISTINCT关键字对其后面所指定的列消除重复行。一个SELECT语句中只能有一个DISTINCT，而且必须放在所有的列名之前。语法格式：SELECT DISTINCT column_name,column_name,a,14,13.2.1 SELECT子句,5限制结果集返回行数 如果SELECT语句返回的结果集中的行数特别多，不利于信息的整理和统计，可以使用TOP选项限制其返回的行数。TOP选项的基本格式为：TOP n PERCENT其中n是一个正整数，表示返回查询结果集的前n行。若带PERCENT关键字，则表示返回结果集的前n%行。,例如：use xsselect top 3*From XSCJgo,a,15,13.2.2 WHERE子句,WHERE子句是对表中的行进行选择查询，即通过在SELECT语句中使用WHERE子句可以从数据表中过滤出符合WHERE子句指定的选择条件的记录，从而实现行的查询。WHERE子句必须紧跟在FROM子句之后，其基本格式为：WHERE 1比较表达式作查询条件使用比较表达式作为查询条件的一般格式是：expression 比较运算符 expression 例如：出生年月1990/1/1,a,16,13.2.2 WHERE子句,2逻辑表达式作查询条件使用逻辑表达式作为查询条件的一般格式是：expression AND expression 或expression OR expression 或NOT expression 例如：性别=女and 出生年月1990/1/1,a,17,13.2.2 WHERE子句,3模式匹配LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。语法格式：string_expression NOT LIKE string_expression,LIKE子句中可使用的通配符,a,18,13.2.2 WHERE子句,4范围比较用于范围比较的关键字有两个：BETWEEN和IN。BETWEEN关键字可以方便地限制查询数据的范围。语法格式：expression NOT BETWEEN expression1 AND expression2,例：查询成绩在60分到70分之间的学号、课程和成绩：use xsSELECT 学号,课程号,成绩FROM xscjWHERE 成绩 BETWEEN 60 AND 70,a,19,13.2.2 WHERE子句,5空值比较 空值表示值未知。空值不同于空白或零值。没有两个相等的空值。当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为：expression IS NOT NULL,例：查询成绩表中无成绩的学号和课程use xsSELECT 学号,课程号FROM xscjWHERE 成绩 is null,a,20,13.2.3 ORDER BY子句,在应用中经常要对查询的结果排序输出，例如将学生成绩由高到低排序输出。在SELECT语句中，使用ORDER BY子句对查询结果进行排序。语法格式：ORDER BY order_by_expression ASC|DESC,n,例：查询成绩表中课程号为52315的记录按成绩从小到大排序输出use xsSELECT 学号,课程号FROM xscjWHERE 课程号=52315Order by 成绩 descgo,a,21,13.3 分类汇总,13.3.1 常用聚合函数13.3.2 分组筛选13.3.3 计算与汇总,a,22,13.3.1 常用聚合函数,在对表数据进行查询时，经常需要对结果进行汇总计算。可以使用聚合函数对数据进行计算。,例 统计选修“301”号课程的总分，平均分和最高分。USE xsSELECT SUM(成绩)AS 总成绩,AVG(成绩)AS 平均成绩,MAX(成绩)AS 最高成绩FROM xscjWHERE 课程号=301GO,a,23,13.3.1 常用聚合函数,a,24,13.3.2 分组筛选,分组是按照某一列数据的值或某个列组合的值将查询出的行分成若干组，每组在指定列或列组合上具有相同的值。分组可通过使用GROUP BY子句来实现。语法格式：GROUP BY group_by_expression,n,a,25,13.3.2 分组筛选,select 系别,性别,年龄,avg(总学分)as 总学分平均值from xsdagroup by 系别,性别go,列 xsda.年龄 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。,当与 GROUP BY 子句一起使用时，每个聚合函数都为每一组生成一个值，而不是对整个表生成一个值,a,26,13.3.3 计算与汇总,除使用聚合函数进行汇总外，还可以使用COMPUTE关键字对结果进行汇总计算。COMPUTE可以生成合计作为附加的汇总列，出现在结果集的最后。语法格式：COMPUTE 聚合函数名(expression),nBY expression,n,例 查询家住广东省的学生，输出学号、姓名和家庭住址，并统计学生人数。USE xsSELECT 学号,姓名,家庭住址FROM 学生WHERE 家庭住址 LIKE 广东%COMPUTE COUNT(学号)GO,a,27,13.3.3 计算与汇总,USE pubsSELECT type,priceFROM titlesWHERE price$10 AND type LIKE%cookORDER BY type,priceCOMPUTE SUM(price)BY type,a,28,13.4 连接查询,13.4.1 连接谓词13.4.2 JOIN关键字指定的连接,a,29,13.4.1 连接谓词,在XS数据库中需要查找选修了数据结构课程的学生的姓名和成绩,a,30,13.4.1 连接谓词,在实际查询中，有时所需要的信息来自于不同的表，需要在查询中将这些表进行连接方能得到完整的信息。,例如：在XS数据库中需要查找选修了数据结构课程的学生的姓名和成绩，就需要将XSDA，KCXX，XSCJ三个表进行连接，才能查找到结果。,a,31,13.4.2 JOIN关键字指定的连接,（1）内连接 内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。自然连接有两种形式的语法。内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。语法格式：FROM INNER JOIN ON,a,32,13.4.2 JOIN关键字指定的连接,如：SELECT 列 FROM 表1 insert JION 表2 ON 表1.列=表2.列语法二：SELECT 列 FROM 表1,表2 WHERE表1.列=表2.列,a,33,13.4.2 JOIN关键字指定的连接,例：从titles和titleauthor表中查询书的书号、书名、作者号、类型和价格。,a,34,13.4.2 JOIN关键字指定的连接,usepubsgoselect titles.title_id,title,au_id,type,pricefrom titles join titleauthoron titles.title_id=titleauthor.title_id,a,35,13.4.2 JOIN关键字指定的连接,得到结果为：title_idtitle au_idtypeprice-BU1032The Busy Executives Database Guide213-46-8915business19.9900BU1032The Busy Executives Database Guide409-56-7008business19.9900BU1111Cooking with Computers:Surreptitious Balance Sheets267-41-2394business11.9500BU1111Cooking with Computers:Surreptitious Balance Sheets724-80-9391business11.9500BU2075You Can Combat Computer Stress!213-46-8915business2.9900（所影响的行数为 25 行）在上述查询中titles表与titleauthor表通过title_id列进行连接，这样可以在一次查询中从两个表获得数据。,a,36,13.4.2 JOIN关键字指定的连接,（2）外连接外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下3种。左外连接左外连接的结果表中除了包括满足连接条件的行外，还包括左表的所有行。语法格式：FROM LEFT OUTER JOIN ON 例：SELECT 列FROM 表1 LEFTOUTERJOIN 表2ON 表1.列1=表2.列2,a,37,13.4.2 JOIN关键字指定的连接,右外连接右外连接的结果表中除了包括满足连接条件的行外，还包括右表的所有行。语法格式：FROM RIGHT OUTER JOIN ON 例如：SELECT select_listFROM 表1 RIGHTOUTERJOIN 表2ON 表1.列1=表2.列2,a,38,13.4.2 JOIN关键字指定的连接,完全外连接完全外连接的结果表中除了包括满足连接条件的行外，还包括两个表的所有行。语法格式：FROM FULL OUTER JOIN ON 例如：SELECT select_listFROM 表1 FULLOUTER JOIN 表2ON 表1.列1=表2.列2,a,39,13.4.2 JOIN关键字指定的连接,例如：查询书的书号、书名 和作者的作者号、作者名。usepubsgoselect title_id,title,au_id,au_lnamefrom titles full join authorson titles.title_id=authors.au_id,a,40,13.4.2 JOIN关键字指定的连接,查询结果如下：title_idtitleau_idau_lname-NULLNULL267-41-2394 OLearyNULLNULL274-80-9391 StraightNULLNULL341-22-1782 SmithNULLNULL09-56-7008 BennetNULLNULL427-17-2319DullNULLNULL472-27-2349 GringlesbyNULLNULL86-29-1786 LocksleyNULLNULL527-72-3246 GreeneNULLNULL648-92-1872Blotchet-HallsNULLNULL672-71-3249 YokomotoNULLNULL712-45-1867 del CastilloNULLNULL722-51-5454 DeFranceNULLNULL724-08-9931 StringerNULLNULL724-80-9391 MacFeatherNULLNULL756-30-7391KarsenNULLNULL807-91-6654 PanteleyNULLNULL846-92-7186 HunterNULLNULL893-72-1158 McBaddenNULLNULL899-46-2035 RingerNULLNULL998-72-3567 RingerBU1032The Busy Executives Database GuideNULLNULLBU1111Cooking with Computers:Surreptitious Balance SheetsNULLNULLBU2075You Can Combat Computer Stress!NULLNULLBU7832Straight Talk About ComputersNULLNULL（所影响的行数为 41 行）,a,41,13.4.2 JOIN关键字指定的连接,（3）交叉连接交叉连接实际上是将两个表进行笛卡儿积运算，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。语法格式：SELECT 列 FROM 表1 CROSS JOIN 表2SELECT 列 FROM 表1，表2,a,42,13.4.2 JOIN关键字指定的连接,例如：查询所有书号、书名 和所有的作者号、作者名。usepubsgoselect title_id,title,au_id,au_lnamefrom titles,authors结果为：title_idtitleau_idau_lname-PC1035But Is It User Friendly?409-56-7008BennetPC1035But Is It User Friendly?648-92-1872Blotchet-HallsPC1035But Is It User Friendly?238-95-7766CarsonPC1035But Is It User Friendly?722-51-5454DeFrancePC1035But Is It User Friendly?712-45-1867del Castillo（所影响的行数为 414 行）在实际应用中使用交叉连接产生的结果集一般没有什么意义，但在数据库的数学模式上有重要的作用。,a,43,13.4.2 JOIN关键字指定的连接,（4）自连接 连接操作不仅可以在不同的表上进行，也可以在同一张表内进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需要为表指定两个别名，使之在逻辑上成为两张表。对所有列的引用均要用别名限定。,a,44,13.4.2 JOIN关键字指定的连接,例如：在titles表中查询同名的作者。usepubsgoselect a1.au_fname,a2.au_fname,a1.au_lname from authors a1 join authors a2 on a1.au_lname=a2.au_lnamewhere a1.au_id a2.au_id查询结果为：au_fnameau_fnameau_lname-AlbertAnneRingerAnneAlbertRinger（所影响的行数为 2 行）,a,45,13.5子查询,子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。,Select From Table,WHERE,父查询,Select From WHERE=,操作符,子查询,a,46,子查询,1、嵌套子查询的执行不依赖于外部嵌套。2、嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。嵌套子查询一般也分为两种：子查询返回单个值子查询返回一个值列表。,a,47,use pubs goselect titlefrom titleswhere price(select averageprice=avg(price)from titles),示例：查询所有价格高于平均价格的书。,在这个例子中，SQL Server首先获得“select averageprice=avg(price)from titles”的结果集，该结果集为单行单列，然后将其作为外部查询的条件执行外部查询，并得到最终的结果。,a,48,use pubsgoselect au_id,au_lname,au_fnamefrom authorswhere au_id=(select au_id from titleauthorwhere title_id=pc1035),示例：查询书号为pc1035的作者的作者号、作者姓名。,use pubsgoselect authors.au_id,au_lname,au_fnamefrom authors,titleauthorwhere authors.au_id=titleauthor.au_id and title_id=pc1035,连接操作要比子查询快，所以能使用表连接的时候应尽量使用表连接。,a,49,返回一个值列表,例如：查询所有出版了书的作者的信息。use pubsgo select au_id,au_lname,au_fname from authors where au_id in(select au_id from titleauthor),该列表被外部查询的IN、NOT IN、ANY或ALL比较操作使用。IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOT IN表示不属于。,a,50,13.6 查询结果保存,1INTO子句 使用SELECT INTO语句可以在查询的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。语法格式为：SELECT 列INTO 新表FROM 源表WHERE 条件1GROUP BY 表达式1HAVING 条件2ORDER BY 表达式2ASC|DESC,a,51,13.6 查询结果保存,例如：有xsda表创建“管理系学生表”，包括学号、姓名、系名和总学分Use xsSelect 学号，姓名，系名，总学分Into 管理系学生表Where 系名=管理,a,52,13.6 查询结果保存,2UNION子句使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集。语法格式：|()UNION ALL|(),a,53,13.6 查询结果保存,说明：（1）UNION中的每一个查询所涉及的列必须具有相同的列数、相同的数据类型，并以相同的顺序出现。（2）最后结果集中的列名来自第一个SELECT语句。（3）若UNION中包含ORDER BY子句，则将对最后的结果集排序。（4）在合并结果集时，默认从最后的结果集中删除重复的行，除非使用ALL关键字。,a,54,13.6 查询结果保存,例如：将两个结构相同表的记录合并到一个新表中Use xsSelect*From xsda Union all Select*From 软一班学生表Union all Select*From 软二班学生表,a,55,表1、XSDA,a,56,表2、xscc表,a,57,表3、xscj表,a,58,使用T_SQL语句，完成下面操作查询xscj表，输出学号及其所选修的课程门数查询xsda表中系别为计算机的记录，同时把结果集保存在计算机学生表中查询每位学生的学号,姓名,性别,专业,课程编号,成绩查询“张三”同学所在专业的全部同学的姓名查询选修了英语课程的学生的姓名查询专业不为“软件应用”且年龄大于所有软件应用专业学生的学生姓名和年龄查询年龄最大学生的学号和姓名,a,59,1、select 学号,COUNT(*)as 选修门数 from xscjGROUP BY 学号HAVING COUNT(*)12.select*INTO 计算机学生表 from xsda WHERE 系别=计算机select*FROM 计算机学生表3.select A.学号,姓名,性别,专业,课程编号,成绩 from xsda A,xscj Bwhere A.学号=B.学号,a,60,4、select 姓名 from xsdawhere 专业=(select 专业 from xsda where 姓名=张三)5.select 姓名 from xsdawhere 学号 IN(select 学号 from xscj where 课程编号 IN(select 课程编号 from xscc where 课程名称=英语)6.select 姓名,年龄 from xsdawhere 年龄=ALL(select 年龄 from xsda where 专业=软件应用)AND 专业!=软件应用,a,61,7、select*from xsda where 年龄=(select MAX(年龄)from xsda),