数据库查询课件.ppt

1、授课授课13-13-数据库查询、维护数据库查询、维护a1数据库查询13.1 SELECT语句概述13.2 简单查询13.3 分类汇总13.4 连接查询13.5 子查询13.6 查询结果保存授课授课13-13-数据库查询、维护数据库查询、维护a2【任务（实验任务（实验1818，1919）】数据库简单查询、复杂数据库简单查询、复杂查询查询实验内容实验内容:P270-P276P270-P276授课授课13-13-数据库查询、维护数据库查询、维护a3查询步骤SQL Server查询查询用户用户发出发出一个关于表中的数一个关于表中的数据的请求或问题据的请求或问题回答该查询回答该查询 返回结果集返回结果集

2、由由 SQL Server处理处理表中的数据表中的数据授课授课13-13-数据库查询、维护数据库查询、维护a413.1 SELECT语句概述13.1.1 SELECT语句的语法格式13.1.2 SELECT语句的执行方式授课授课13-13-数据库查询、维护数据库查询、维护a513.1.1 SELECT语句的语法格式SELECT语句的基本语法格式如下：l SELECT l INTO l FROM l WHERE l GROUP BY l HAVING l ORDER BY ASC|DESC授课授课13-13-数据库查询、维护数据库查询、维护a613.1.2 SELECT语句的执行方式1使用企业管

3、理器执行授课授课13-13-数据库查询、维护数据库查询、维护a713.1.2 SELECT语句的执行方式2使用查询分析器执行授课授课13-13-数据库查询、维护数据库查询、维护a813.2 简单查询13.2.1 SELECT子句13.2.2 WHERE子句13.2.3 ORDER BY子句授课授课13-13-数据库查询、维护数据库查询、维护a913.2.1 SELECT子句 SELECT子句是对表中的列进行选择查询，也是SELECT语句最基本的使用，基本形式如下：SELECT SELECT 列名列名1,1,列名列名nn选择列是限定返回某些列组成结果集。1选择所有列 选择所有的列，既可以在查询中

4、逐一地列示出所有的列名，也可以使用符号“*”。例 查询本校教师信息SELECT SELECT*FROM FROM 本校教师本校教师授课授课13-13-数据库查询、维护数据库查询、维护a102选择指定的列 若要选择表中的特定列，应在选择列表中明确地列出每一列，在列与列之间用逗号隔开。例：SELECT SELECT 教师编号，教师姓名，系，专业方向，职称教师编号，教师姓名，系，专业方向，职称FROM FROM 本校教师本校教师授课授课13-13-数据库查询、维护数据库查询、维护a1113.2.1 SELECT子句3修改查询结果中的列标题 当希望查询结果中的某些列不显示表结构中规定的列标题，而使用用

5、户自己另外选择的列标题时，可以在列名之后使用AS子句来更改查询结果中的列标题名。例如：use xs select 学号 as number,姓名 as nameFrom XSDAgoNumber=学号授课授课13-13-数据库查询、维护数据库查询、维护a1213.2.1 SELECT子句4计算列值 使用SELECT语句对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为查询结果。格式为：SELECT expression ,expression 例如：use xs select 学号 课程标号，期末成绩=成绩*0.8From XSCJgo授课授课13-13-数据

6、库查询、维护数据库查询、维护a1313.2.1 SELECT子句4消除结果集中的重复行 对表只选择某些列时，可能会出现重复行，例如，若对XSDA表只选择系名和总学分，则会出现多行重复的情况。可以使用DISTINCT关键字消除结果集中的重复行。DISTINCT关键字对其后面所指定的列消除重复行。一个SELECT语句中只能有一个DISTINCT，而且必须放在所有的列名之前。语法格式：SELECT SELECT DISTINCT DISTINCT column_name,column_namecolumn_name,column_name 授课授课13-13-数据库查询、维护数据库查询、维护a141

7、3.2.1 SELECT子句5限制结果集返回行数 如果SELECT语句返回的结果集中的行数特别多，不利于信息的整理和统计，可以使用TOP选项限制其返回的行数。TOP选项的基本格式为：TOPTOP n PERCENT n PERCENT其中n是一个正整数，表示返回查询结果集的前n行。若带PERCENT关键字，则表示返回结果集的前n%行。例如：use xsselect top 3 *From XSCJgo授课授课13-13-数据库查询、维护数据库查询、维护a1513.2.2 WHERE子句 WHERE子句是对表中的行进行选择查询，即通过在SELECT语句中使用WHERE子句可以从数据表中过滤出符合

8、WHERE子句指定的选择条件的记录，从而实现行的查询。WHERE子句必须紧跟在FROM子句之后，其基本格式为：WHERE 1比较表达式作查询条件使用比较表达式作为查询条件的一般格式是：expression expression 比较运算符比较运算符 expressionexpression 例如：出生年月1990/1/1授课授课13-13-数据库查询、维护数据库查询、维护a1613.2.2 WHERE子句2逻辑表达式作查询条件使用逻辑表达式作为查询条件的一般格式是：expression AND expression expression AND expression 或或expression

9、OR eexpression OR expression xpression 或或NOT expression NOT expression 例如：性别=女and 出生年月1990/1/1授课授课13-13-数据库查询、维护数据库查询、维护a1713.2.2 WHERE子句3模式匹配LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。语法格式：string_expression NOT LIKE string_expressionstring_expression NOT LIKE string_expression通配符含义%包含零个或多个字符的任意字符串

10、。_任何单个字符。指定范围（例如 a-f）或集合（例如 abcdef）内的任何单个字符。不在指定范围（例如 a-f）或集合（例如 abcdef）内的任何单个字符LIKE子句中可使用的通配符授课授课13-13-数据库查询、维护数据库查询、维护a1813.2.2 WHERE子句4范围比较用于范围比较的关键字有两个：BETWEEN和IN。BETWEEN关键字可以方便地限制查询数据的范围。语法格式：expression NOT BETWEEN expression1 AND expressiexpression NOT BETWEEN expression1 AND expression2on2例：查

11、询成绩在60分到70分之间的学号、课程和成绩：use xsSELECT 学号,课程号,成绩FROM xscjWHERE 成绩 BETWEEN 60 AND 70授课授课13-13-数据库查询、维护数据库查询、维护a1913.2.2 WHERE子句5空值比较 空值表示值未知。空值不同于空白或零值。没有两个相等的空值。当需要判定一个表达式的值是否为空值时，使用IS NULL关键字，格式为：expression IS NOT NULLexpression IS NOT NULL例：查询成绩表中无成绩的学号和课程use xsSELECT 学号,课程号FROM xscjWHERE 成绩 is null授

12、课授课13-13-数据库查询、维护数据库查询、维护a2013.2.3 ORDER BY子句 在应用中经常要对查询的结果排序输出，例如将学生成绩由高到低排序输出。在SELECT语句中，使用ORDER BY子句对查询结果进行排序。语法格式：ORDER BY order_by_expression ASC|DESC,nORDER BY order_by_expression ASC|DESC,n例：查询成绩表中课程号为52315的记录按成绩从小到大排序输出use xsSELECT 学号,课程号FROM xscjWHERE 课程号=52315Order by 成绩 descgo授课授课13-13-数据

13、库查询、维护数据库查询、维护a2113.3 分类汇总13.3.1 常用聚合函数13.3.2 分组筛选13.3.3 计算与汇总授课授课13-13-数据库查询、维护数据库查询、维护a2213.3.1 常用聚合函数 在对表数据进行查询时，经常需要对结果进行汇总计算。可以使用聚合函数对数据进行计算。例 统计选修“301”号课程的总分，平均分和最高分。USE xsSELECT SUM(成绩)AS 总成绩,AVG(成绩)AS 平均成绩,MAX(成绩)AS 最高成绩FROM xscjWHERE 课程号=301GO授课授课13-13-数据库查询、维护数据库查询、维护a2313.3.1 常用聚合函数函 数 名功

14、 能SUM()返回表达式中所有值的和AVG()返回表达式中所有值的平均值MAX()求最大值MIN()求最小值COUNT()用于统计组中满足条件的行数或总行数授课授课13-13-数据库查询、维护数据库查询、维护a2413.3.2 分组筛选 分组是按照某一列数据的值或某个列组合的值将查询出的行分成若干组，每组在指定列或列组合上具有相同的值。分组可通过使用GROUP BY子句来实现。语法格式：GROUP BY group_by_expression,nGROUP BY group_by_expression,n授课授课13-13-数据库查询、维护数据库查询、维护a2513.3.2 分组筛选 sele

15、ct 系别,性别,年龄,avg(总学分)as 总学分平均值from xsdagroup by 系别,性别go列 xsda.年龄 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。当与 GROUP BY 子句一起使用时，每个聚合函数都为每一组生成一个值，而不是对整个表生成一个值 授课授课13-13-数据库查询、维护数据库查询、维护a2613.3.3 计算与汇总 除使用聚合函数进行汇总外，还可以使用COMPUTE关键字对结果进行汇总计算。COMPUTE可以生成合计作为附加的汇总列，出现在结果集的最后。语法格式：COMPUTE 聚合函数名(expression),

16、nBY expression,n例 查询家住广东省的学生，输出学号、姓名和家庭住址，并统计学生人数。USE xsSELECT 学号,姓名,家庭住址FROM 学生WHERE 家庭住址 LIKE 广东%COMPUTE COUNT(学号)GO授课授课13-13-数据库查询、维护数据库查询、维护a2713.3.3 计算与汇总USE pubsSELECT type,priceFROM titlesWHERE price$10 AND type LIKE%cookORDER BY type,priceCOMPUTE SUM(price)BY type授课授课13-13-数据库查询、维护数据库查询、维护a2

17、813.4 连接查询13.4.1 连接谓词13.4.2 JOIN关键字指定的连接授课授课13-13-数据库查询、维护数据库查询、维护a2913.4.1 连接谓词在XS数据库中需要查找选修了数据结构课程的学生的姓名和成绩授课授课13-13-数据库查询、维护数据库查询、维护a3013.4.1 连接谓词 在实际查询中，有时所需要的信息来自于不同的表，需要在查询中将这些表进行连接方能得到完整的信息。例如：在XS数据库中需要查找选修了数据结构课程的学生的姓名和成绩，就需要将XSDA，KCXX，XSCJ三个表进行连接，才能查找到结果。授课授课13-13-数据库查询、维护数据库查询、维护a3113.4.2

18、JOIN关键字指定的连接（1）内连接 内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。自然连接有两种形式的语法。内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。语法格式：FROM INNER JOIN ON 授课授课13-13-数据库查询、维护数据库查询、维护a3213.4.2 JOIN关键字指定的连接如：SELECT 列 FROM 表1 insert JION 表2 ON 表1.列=表2.列语法二：SELECT 列 FROM 表1,表2 WHERE表1.列=表2.列授课授课13-13-数据库查询、维护数据

19、库查询、维护a3313.4.2 JOIN关键字指定的连接例：从titles和titleauthor表中查询书的书号、书名、作者号、类型和价格。授课授课13-13-数据库查询、维护数据库查询、维护a3413.4.2 JOIN关键字指定的连接usepubsgoselect titles.title_id,title,au_id,type,pricefrom titles join titleauthoron titles.title_id=titleauthor.title_id授课授课13-13-数据库查询、维护数据库查询、维护a3513.4.2 JOIN关键字指定的连接得到结果为：title_

20、id title au_idtypeprice-BU1032The Busy Executives Database Guide213-46-8915business19.9900BU1032The Busy Executives Database Guide409-56-7008business19.9900BU1111Cooking with Computers:Surreptitious Balance Sheets267-41-2394 business11.9500BU1111Cooking with Computers:Surreptitious Balance Sheets724

21、-80-9391 business11.9500BU2075You Can Combat Computer Stress!213-46-8915business2.9900（所影响的行数为 25 行）在上述查询中titles表与titleauthor表通过title_id列进行连接，这样可以在一次查询中从两个表获得数据。授课授课13-13-数据库查询、维护数据库查询、维护a3613.4.2 JOIN关键字指定的连接（2）外连接外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下3种。左外连接左外连接的结果表中除了包括满足连接条件的行外，还包括左表的所有行。语法格式：F

22、ROM LEFT OUTER JOIN ON 例：SELECT 列FROM 表1 LEFTOUTERJOIN 表2ON 表1.列1=表2.列2授课授课13-13-数据库查询、维护数据库查询、维护a3713.4.2 JOIN关键字指定的连接 右外连接右外连接的结果表中除了包括满足连接条件的行外，还包括右表的所有行。语法格式：FROM RIGHT OUTER JOIN ON 例如：SELECT select_listFROM 表1 RIGHTOUTERJOIN 表2ON 表1.列1=表2.列2授课授课13-13-数据库查询、维护数据库查询、维护a3813.4.2 JOIN关键字指定的连接 完全外连

23、接完全外连接的结果表中除了包括满足连接条件的行外，还包括两个表的所有行。语法格式：FROM FULL OUTER JOIN ON 例如：SELECT select_listFROM 表1 FULLOUTER JOIN 表2ON 表1.列1=表2.列2授课授课13-13-数据库查询、维护数据库查询、维护a3913.4.2 JOIN关键字指定的连接例如：查询书的书号、书名 和作者的作者号、作者名。usepubsgoselect title_id,title,au_id,au_lnamefrom titles full join authorson titles.title_id=authors.a

24、u_id授课授课13-13-数据库查询、维护数据库查询、维护a4013.4.2 JOIN关键字指定的连接查询结果如下：title_id titleau_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 GreeneNUL

25、LNULL648-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-20

26、35 RingerNULLNULL998-72-3567 RingerBU1032The Busy Executives Database GuideNULLNULLBU1111Cooking with Computers:Surreptitious Balance SheetsNULLNULLBU2075You Can Combat Computer Stress!NULLNULLBU7832Straight Talk About ComputersNULLNULL（所影响的行数为 41 行）授课授课13-13-数据库查询、维护数据库查询、维护a4113.4.2 JOIN关键字指定的连接（3

27、）交叉连接交叉连接实际上是将两个表进行笛卡儿积运算，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。语法格式：SELECT 列 FROM 表1 CROSS JOIN 表2SELECT 列 FROM 表1，表2授课授课13-13-数据库查询、维护数据库查询、维护a4213.4.2 JOIN关键字指定的连接例如：查询所有书号、书名 和所有的作者号、作者名。usepubsgoselect title_id,title,au_id,au_lnamefrom titles,authors结果为：title_id titleau_idau_lname-PC10

28、35 But Is It User Friendly?409-56-7008BennetPC1035 But Is It User Friendly?648-92-1872Blotchet-HallsPC1035 But Is It User Friendly?238-95-7766CarsonPC1035 But Is It User Friendly?722-51-5454DeFrancePC1035 But Is It User Friendly?712-45-1867del Castillo（所影响的行数为 414 行）在实际应用中使用交叉连接产生的结果集一般没有什么意义，但在数据库的

29、数学模式上有重要的作用。授课授课13-13-数据库查询、维护数据库查询、维护a4313.4.2 JOIN关键字指定的连接（4）自连接 连接操作不仅可以在不同的表上进行，也可以在同一张表内进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需要为表指定两个别名，使之在逻辑上成为两张表。对所有列的引用均要用别名限定。授课授课13-13-数据库查询、维护数据库查询、维护a4413.4.2 JOIN关键字指定的连接例如：在titles表中查询同名的作者。usepubsgoselect a1.au_fna

30、me,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-Albert AnneRingerAnneAlbert Ringer（所影响的行数为 2 行）授课授课13-13-数据库查询、维护数据库查询、维护a4513.5子查询子查询：一个 SELECT 语句嵌套在另一个 SELECT 语句中。Select From TableWHERE 父查询父查询Select From WHE

31、RE =操作符操作符子查询子查询授课授课13-13-数据库查询、维护数据库查询、维护a46子查询1 1、嵌套子查询的执行不依赖于外部嵌套。、嵌套子查询的执行不依赖于外部嵌套。2 2、嵌套子查询的执行过程为：首先执行子查询，子、嵌套子查询的执行过程为：首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。示查询结果。子查询可以多层嵌套。嵌套子查询一般也分为两种：嵌套子查询一般也分为两种：子查询返回单个值子查询返回单

32、个值子查询返回一个值列表。子查询返回一个值列表。授课授课13-13-数据库查询、维护数据库查询、维护a47use pubs use pubs gogoselect titleselect titlefrom titlesfrom titleswhere pricewhere price (select averageprice=avg(price)(select averageprice=avg(price)from titles)from titles)示例：示例：查询所有价格高于平均价格的书。查询所有价格高于平均价格的书。在这个例子中，SQL Server首先获得“select avera

33、geprice=avg(price)from titles”的结果集，该结果集为单行单列，然后将其作为外部查询的条件执行外部查询，并得到最终的结果。授课授课13-13-数据库查询、维护数据库查询、维护a48use pubsuse pubsgogoselect au_id,au_lname,au_fnameselect au_id,au_lname,au_fnamefrom authorsfrom authorswhere au_id=(select au_idwhere au_id=(select au_id from titleauthor from titleauthorwhere tit

34、le_id=pc1035)where title_id=pc1035)示例：示例：查询书号为查询书号为pc1035pc1035的作者的作者号、作者姓名。的作者的作者号、作者姓名。use pubsuse pubsgogoselect authors.au_id,au_lname,select authors.au_id,au_lname,au_fnameau_fnamefrom authors,titleauthorfrom authors,titleauthorwhere authors.au_id=titleauthowhere authors.au_id=titleauthor.au_id

35、 and title_id=pc1035r.au_id and title_id=pc1035连接操作要比子查询快，连接操作要比子查询快，所以能使用表连接的时所以能使用表连接的时候应尽量使用表连接。候应尽量使用表连接。授课授课13-13-数据库查询、维护数据库查询、维护a49返回一个值列表 例如：查询所有出版了书的作者的信息。例如：查询所有出版了书的作者的信息。use pubsuse pubsgogo select au_id,au_lname,au_fname select au_id,au_lname,au_fname from authors from authors where au_

36、id where au_id inin (select au_id (select au_id from titleauthor)from titleauthor)该列表被外部查询的IN、NOT IN、ANY或ALL比较操作使用。IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等；NOT IN表示不属于。授课授课13-13-数据库查询、维护数据库查询、维护a5013.6 查询结果保存1INTO子句 使用SELECT INTO语句可以在查询的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。语法格式为：SELECT 列INTO 新

37、表FROM 源表WHERE 条件1GROUP BY 表达式1HAVING 条件2ORDER BY 表达式2ASC|DESC授课授课13-13-数据库查询、维护数据库查询、维护a5113.6 查询结果保存例如：有xsda表创建“管理系学生表”，包括学号、姓名、系名和总学分Use xsSelect 学号，姓名，系名，总学分Into 管理系学生表Where 系名=管理授课授课13-13-数据库查询、维护数据库查询、维护a5213.6 查询结果保存2UNION子句使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集。语法格式：|()|()UNIUNIONON ALL|(query

38、expre ALL|()ssion)授课授课13-13-数据库查询、维护数据库查询、维护a5313.6 查询结果保存说明：（1）UNION中的每一个查询所涉及的列必须具有相同的列数、相同的数据类型，并以相同的顺序出现。（2）最后结果集中的列名来自第一个SELECT语句。（3）若UNION中包含ORDER BY子句，则将对最后的结果集排序。（4）在合并结果集时，默认从最后的结果集中删除重复的行，除非使用ALL关键字。授课授课13-13-数据库查询、维护数据库查询、维护a5413.6 查询结果保存例如：将两个结构相同表的记录合并到一个新表中Use xsSelect*From xsda Union

39、all Select*From 软一班学生表Union all Select*From 软二班学生表授课授课13-13-数据库查询、维护数据库查询、维护a55字段名字段名数据类型长数据类型长度度是否主键是否主键空否空否学号int（4）是not null姓名char（8）null性别char（2）null年龄tinyint（1）null系别char（20）null专业char（20）null家庭住址char（50）null表1、XSDA授课授课13-13-数据库查询、维护数据库查询、维护a56表2、xscc表字段名字段名数据类型数据类型长度长度是否主是否主键键空否空否课程名称char（10）是n

40、ot null课程编号char（30）null课程性质char（10）null课程学分tinyint（1）null授课授课13-13-数据库查询、维护数据库查询、维护a57表3、xscj表字段名字段名数据类型长数据类型长度度是否主键是否主键空否空否学号int（4）null课程编号char（30）null成绩Float（8）not null授课授课13-13-数据库查询、维护数据库查询、维护a58使用使用T_SQLT_SQL语句，完成下面操作语句，完成下面操作查询查询xscjxscj表，输出学号及其所选修的课程门数表，输出学号及其所选修的课程门数查询查询xsdaxsda表中系别为计算机的记录，同

41、时把结果集保存在计表中系别为计算机的记录，同时把结果集保存在计算机学生表中算机学生表中查询每位学生的学号查询每位学生的学号,姓名姓名,性别性别,专业专业,课程编号课程编号,成绩成绩查询查询“张三张三”同学所在专业的全部同学的姓名同学所在专业的全部同学的姓名查询选修了英语课程的学生的姓名查询选修了英语课程的学生的姓名查询专业不为查询专业不为“软件应用软件应用”且年龄大于所有软件应用专业学生且年龄大于所有软件应用专业学生的学生姓名和年龄的学生姓名和年龄1.查询年龄最大学生的学号和姓名查询年龄最大学生的学号和姓名授课授课13-13-数据库查询、维护数据库查询、维护a591、select 学号,COU

42、NT(*)as 选修门数 from xscjGROUP BY 学号HAVING COUNT(*)12.select*INTO 计算机学生表 from xsda WHERE 系别=计算机select*FROM 计算机学生表3.select A.学号,姓名,性别,专业,课程编号,成绩 from xsda A,xscj Bwhere A.学号=B.学号授课授课13-13-数据库查询、维护数据库查询、维护a604、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 专业!=软件应用授课授课13-13-数据库查询、维护数据库查询、维护a617、select*from xsda where 年龄=(select MAX(年龄)from xsda)