1、第6章 数据库的查询,查询是数据库的其他操作的基础 视图是由一个或多个基本表导出的数据信息， 可以根据用户需要去创建视图。 6.1 选择、连接和投影 一、选择（Selection）单目运算，运算对象为基本表。即按照给定的 条件，从表中选择出满足指定条件的行形成一个新表。 选择运算的记号：F (R)。其中，是选择运算符，下标F是一个条件表达式，R是被操作的表,若要在学生表中查找性别为“女”且平均成绩在80分以上的行形成一个新表，则运算表达式为： 性别=”女”平均成绩80(学生) 二、投影单目运算，该运算从表中选出指定的属性值组成一个新表,关系R上的投影运算记为A(R)。其中，A是属性名表，R是表
2、名。 若在学生表中，对“姓名”和“平均成绩”投影，其运算式为：姓名，平均成绩 (学生) 表的选择和投影分别从行和列两个方向分割一个表。,三、连接（join）连接是把两个表中的行按给定的条件进行拼接而形成新表。R S 两表连接最常用的的条件是两个表的某些列值相等，称为等值连接.,其中R，S是被操作的表，F是条件,F,数据库应用中最常用的就是“自然连接”，进行自然连接要求两个表有共同的属性.自然连接运算的结果是参与操作的两个表的共同属性上进行等值连接后，再出去重复的属性后所得的新表。 R S,6.2数据库查询用SELECT 语句从SQL SERVER 数据库中提取数据是DBA最频繁的管理操作。,6
3、.2.1查询工具的使用SQL Server Management Studio 1.选择当前工作数据库 2.更改查询显示结果（1）文本格式显示 （2）表格显示（3）将结果保存为rpt报表文件。,执行计划：客户端统计信息：在编辑器中设计查询：,6.2.2查询语句的语法结构 T-SQL的select 语句可以实现对表的选择、投影和连接 select 语句是T-SQL的核心 主要的子句如下：,select select_list /*指定要选择的行或列及其限定*/into new-table /*into 子句，指定结果存入新表*/from table-source /*from 子句，指定表或视图
4、*/ where search-condition /*where子句，指定查询条件*/ group by group-by-expression /*group子句，指定分组表达式*/ having search-condition /*having 子句，指定分组统计条件*/ order by order-expressionascdesc /*order 子句，指定排序表达式和顺序*/Xs.xh xs_kc.xh,一、选择列 select all|distincttop npercentwith ties 的格式为： * |table_name|view_name|table_alias
5、.*|column-name|expression as column_alias |column_alias=expression ,n,Example: 从XS表中查询各个学生的的姓名、专业名和总学分,1 选择一个表中指定的列用SELECT 语句选择一个表中的某些列，各列之间用逗号分隔。 Select column_name,column_namefrom table_namewhere search_condition,use xscjselect 姓名,专业,总学分from xs Example:use xscjselect 姓名,专业,总学分from xswhere 专业名=“计算机
6、” Example:,Select * from xs （教材示例5.1） use student go select * from lesson_info 2 修改查询结果的列标题: 使用AS子句来更改查询结果的列标题。As子句的格式为：as column_alias 或使用“column_alias=expression”example:select 学号 as numbers,姓名 as namefrom xswhere 专业名=计算机,or: select numbers=学号, name=姓名from xswhere 专业名=计算机 注：当自定义的列标题中含有空格时，必须使用引号将标
7、题括起来。 例：select student number=学号,姓名 AS student name,mark=总分from XSWHERE 专业名计算机,（教材示例5.2） select stud_id as 学号,name as 姓名,出生日期=birthdayfrom stud_infowhere gender=女,3 替换查询结果中的数据使用case 表达式 Casewhen condition1 then expression1when condition1 then expression2.else End,Example:select 学号，姓名，等级=case when 总学
8、分 is null then 尚未选课when 总学分 =50 and总学分 =52 then 合格else 优秀endfrom xswhere 专业=计算机,4 计算列值使用SELECT 对列进行查询时，在结果中可以输出对列值计算后的结果 例：use cpxsselect cpmc as 产品名称，产品总值=jg*kclfrom cp,5 消除结果集中的重复行对表中只选择其中某些列时，可能出现重复行。使用distinct消除结果集中的重复行。格式为：select distinct column_name,与 distinct相反，使用关键字all时，保留结果集中的所有行 默认为all,exa
9、mple1:对xs表中只选择专业名和总学分，消除总学分select distinct 专业，总学分from xs Example2:select all 专业，总学分from xs 教材练习5.1,6 限制结果集返回行数： 如果select语句返回的结果集的行数非常多，可以使用top选项限制其返回的行数。 基本格式：top n percent n 为正数，返回查询结果的前n行，若带percent ,则表示返回结果集的前n%行 例：select top 5 姓名，专业名，总学分from xs,二、选择行WHERE 子句 1 表达式比较 比较运算符，共9个。，,=,!,!=expression=|
10、42,例（教材示例5.5） select name,address,telcode,zipcodefrom stud_infowhere gender=女,2 模式匹配like 谓词用于指出一个字符串是否与指定字符串相匹配，其运算对象可以是char、varchar，text，ntext，datetime和smalldatetime类型的数据，返回值为true或false 格式为： String_expression not like string_expression escape escape_character,例 查询XSCJ数据库XS表中计算机系学生的情况 select * from
11、xs where 专业 like 计算机 使用通配符可进行模糊查询,例：查询xs表中王姓同学情况 Select * from xs where 姓名like 王,若要匹配用作通配符的字符，可使用关键字ESCAPE。ESCAPE关键字指出其后的每个字符均作为实际的字符对待，而不再作为通配符使用。 例：查询cpxs数据库中cp表中产品名含有“冰箱_A”产品的情况select *from cpwhere cpmc like %冰箱_A% escape_,（教材示例5.6） select stud_id,name,birthday,gender,mark from stud_info where na
12、me like 郑_,3 范围比较 Between和in （1）当要查询的条件是某个值的范围时，用between。格式为： Expression not between expression1 and expression2 注：expression1的值不能大于expression2的值。 例：查询cpxs数据库中cp表中价格在2000元和2400元之间的产品情况。,select * from cp where jg between 2000 and 2400 （2）使用IN可以指定一个值表，值表中列出所有可能的值。格式为： Expression in （expression1,n） 例：查
13、询xscj数据库xs表中专业名为计算机或通信工程或无线电的学生的情况select * from cp where jg=2000 and jg=2400,Select * from xs where zhy in (计算机,通信工程,无线电) 例（教材示例5.7） select teacher_id,name,tech_title,salary from teacher_info where tech_title in (助教,讲师,副教授)4 空值比较 当需要判断一个表达式的值是否为空值时，用Is null。格式为： Expression is notnull 例：查询xscj数据库xs表中
14、总学分尚未确定的学生情况。select * from xswhere 总学分 is null,5 子查询 在查询条件中，可以使用另一个查询的结果作为条件的一部分，如判定列值是否与某个查询的结果集中的值相同，作为查询条件一部分的查询称为子查询。 （1）in 子查询 用于进行一个给定值是否在子查询结果中的判断。格式为：expressionnot in (subquery) 例 在XSCJ数据库中有描述课程情况的表kc和描述学生成绩的表xs_kc,查找选修了课程号为101的课程的学生的情况,select * from xswhere xh in (select xh from xs_kc where
15、 kch101) 注：in和not in 子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。,(2)比较子查询。这种子查询可以认为是IN子查询的扩展。格式为： Expression |=|all | some | any ( subquery ) All 指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE. Some或any表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回TRUE,否则返回FALSE。 例：查找比所有计算机系学生年龄都大的学生。,select *from xswhere chsh a
16、ll(select chshfrom xs where zhy=计算机),例 查找课程号为206且成绩不低于课程号为101的最低成绩的学生的学号。 Select xhfrom xs_kcwhere kch206 and cj !any (select cjfrom xs_kcwhere kch=101),(3) exists子查询 格式为：notexists (subquery)exists用于测试子查询的结果是否为空. 例 查找选修了206号课程的学生的姓名。 Select xm from xswhere exists(select * from xs_kcwhere xh=xs.xh an
17、d kch=206),From子句 格式： fromtable_sourcee,n可以用AS子句为表指定别名，AS也可以省略，直接给出别名。别名主要用在相关子查询及连接中。,Example：查找选修了001102号同学所选修的全部课程的同学的学号。 Select distinct 学号from xs_kc as cj1where exists（select * from xs_kc as cj2where cj2.学号001102 and exists（select * from xs_kc as cj3where cj3.学号cj1.学号 and cj3.课程号cj2.课程号）,四 连接 连
18、接是两元运算，可以对两个或多个表进行查询，结果通常是含有参加连接运算的的两个或多个表的指定列的表。 在TSQL语言中，连接查询有两大类表示形式：一是符合SQL标准连接谓词表示形式，一是使用关键字join的表示形式 1. 连接谓词在WHERE子句中使用运算符给出连接条件对表进行连接-连接谓词表示形式,Example1：查找xscj数据库中每个学生的情况及选修的课程情况。 Select xs.*，xs_kc.*from xs，xs_kcwhere xs.学号xs_kc.学号,自然连接查询：在目标列中去除相同的列 Example1： Select xs.*,xs_kc.课程号,xs_kc.成绩fro
19、m xs,xs_kcwhere xs.学号xs_kc.学号 若选择的字段名在各个表中是唯一的，则可以省略字段前面的表名。,Ex：查找选修了课程号为206且成绩在80分以上的同学的姓名及 成绩 Select 姓名 成绩from xs，xs_kcwhere xs.学号xs_kc.学号 and课程号206 and 成绩=80,多表查询： 查找选修了计算机课程且成绩在80分以上的学生的学号、课程名及成绩。 Select xs.学号，姓名，课程名，成绩from xs，kc， xs_kcwhere xs.学号xs_kc.学号 and kc.课程号xs_kc.课程号 and 课程名计算机 and 成绩=80
20、,2 以join关键字指定的连接.from子句的joined_table表示将多个表连接起来。 joined_table格式：table_sourceon |cross join |,其中table_source为需要连接的表，join_type 表示连接类型，on用于指定连接条件。Join_type的格式为： Inner|left|right|fullouter join,其中inner 表示内连接，outer表示外连接,join_hint表示连接提示。Cross join表示交叉连接。 即以join关键字指定的连接有三种类型： （1）内连接 按照ON所指定的连接条件合并两个表，返回满足条件
21、的行。 EX: 查找XSCJ数据库每个学生的情况以及选修的课程情况。SELECT * FROM XS INNER JOIN XS_KC ON XS.学号=XS_KC.学号 结果表中将包含xs表和xs_kc表的所有字段（不去除重复字段）,若要去除重复的学号字段，select语句如下：SELECT xs.*,课程号,成绩 内连接是系统默认的，可以省去 INNER关键字。 使用内连接仍可以使用WHERE子句指定条件。 EX. 查找选修了206号课程且成绩在80分以上的学生姓名及成绩。 SELECT 姓名，成绩FROM XS JOIN XS_KC ON XS.学号=xs_kc.学号 where 课程号
22、=206 and 成绩=80,内 连接还可以用于多个表的连接 Ex: 查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。 Select xs.学号，姓名，课程名，成绩from xs join xs_kc on xs.学号=xs_kc.学号Join kc on xs_kc.课程号=kc.课程号where 课程名=计算机基础 and 成绩=80,自连接：一个表和自身进行连接。通过自连接在表中实现查找具有相同列值的行。自连接时，需要为表指定两个别名，且对所有的列均要用别名限定。 EX. 查找不同课程成绩相同的学生的学号、课程号和成绩 select a.学号,a.课程号,b
23、.课程号,a.成绩from xs_kc a join xs_kc bon a.成绩= b.成绩 and a.学号= b.学号 and a.课程号!= b.课程号,(2)外连接 外连接的结果不仅包括满足连接条件的的行，还包括相应表中的所有行。又分为三种： 左外连接（LEFT OUTER JOIN）：结果表中除了包含满足指定条件的行，还包括相应左表中所有行。 右外连接（RIGHT OUTER JOIN）：结果表中除了包含满足指定条件的行，还包括右表中的所有行 完全连接（FULL OUTER JOIN）：结果表中除了包含满足指定条件的行，还包括两个表中的所有行其中OUTER 可以省略,EX ：查找所
24、有学生情况及他们选修的课程号，若学生为选修任何课程，也要包括他们的信息。 SELECT XS.*,课程号FROM XS LEFT OUTER JOIN XS_KC ON XS.学号=XS_KC.学号 EX：查找被选修了的课程的选修情况和所有开设的课程。SELECT xs_kc.*,kcmFROM XS_KC RIGHT OUTER JOIN KC ON XS_KC.kch=kc.kch 注：外连接只能对两个表进行连接,（3）交叉连接 交叉连接的结果表是由第一个表的每行与第二个表的每行拼接后形成的。结果表中的行数是两个表行数的积,EX：列出学生所有可能的选课情况。 SELECT 学号，姓名，课程
25、号，课程名from xs cross join kc 注意：交叉连接不能有条件，也不能有where子句。,五 数据汇总 1.聚合函数 常用： Avg：求组中值的平均值 Count：求组中项数，返回INT类型整数Max:求最大值 Min：求最小值 Sum：返回表达式中所有值的和,（1） sum和 avg sum /avg( all | distinct expression) 例：求选修202课程的学生的平均成绩Select AVG(成绩) as “课程202的平均成绩” from xs_kcwhere 课程号=202,（2）max/min max/min ( all | distinct ex
26、pression) 例：求选修202课程的学生的最高分和最低分Select max(成绩) as “课程202的最高分” , min(成绩) as “课程202的最低分” from xs_kcwhere 课程号=202,（3）count.用于统计组中满足条件的行数或总行数 count ( all | distinct expression |*) 例：求学生总数 Select count(*) as 学生总数from xs 例：求选修了课程的学生人数 Select count( distinct 学号)from xs_kc,2. group by子句：用于对表或视图中的数据按字段分组。 gro
27、up by all group_by_expression,n with cube |rollup,对group by 指定的各列产生汇总行：按列的排列逆序一次进行汇总,对group by 子句中的各列所有可能均产生汇总行,例：将XSCJ数据库中各专业名输出 Select zhyfrom xsgroup by zhy 例：求XSCJ数据库中各专业的学生数 Select zhy，count(*) as 学生人数from xsgroup by zhy,例：求被选修的课程的平均成绩和选修该课程的人数 Select KCH，AVG(CHJ) AS平均成绩，count（xh）as选修人数from xs_
28、kcgroup by kch,例：在XSCJ数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数及学生总人数 Select zhy,xb, count(*) AS人数from xsgroup by zhy,xbwith rollup,例：在XSCJ数据库上产生一个结果集，包括每个专业的男生人数，女生人数、总人数及男生总数、女生总数、学生总数Select zhy,xb, count(*) AS人数from xsgroup by zhy,xbwith cube,3 .having子句使用GROUP BY 和聚合函数对数据进行分组后，还可以用having子句对数据进行进一步的筛选。例：查
29、找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩select xh,avg(chj) as 平均成绩from xs_kcgroup by xhhaving avg(chj)85,在select语句中，当where、 group by、 having子句都被使用时，顺序要注意： where用于筛选由from指定的数据对象， group by用于对where结果进行分组， having则是对group by以后的分组进行过滤。,例：查找选修课程超过2门且成绩都在80分以上的学生的学号。 Select xh from xs_kc where chj=80group by xhhaving
30、 count(*)2,例：查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩select xh,avg(chj) as 平均成绩from xs_kcwhere xh in (select xh from xs where zhy=通信工程)group by xhhaving avg(chj)85,Compute 子句 Compute将产生额外的汇总行 例：查找工业工程专业的学号、姓名和出生时间，并产生一个学生总人数行。 Select xh,xm,chsh from xswhere zhy= 工业工程compute count（xh） 教材示例（5.12）,Compute by 练习5.
31、13 几个注意点：,六 排序 Order byorder_by_expressionasc |desc ,n 例：将通信工程专业的学生按出生时间先后排序 Select * from xswhere zhy=通信工程order by chsh,例：将计算机专业学生的“计算机原理”课程成绩按降序排列 Select xm,kcm,chj from xs, kc,xs_kcwhere xs.xh=xs_kc.xh and xs_kc.kch=kc.kch and kcm=计算机原理 and zhy=计算机order by chj desc,6.2.3 select 语句的其他子句,1 into子句 使
32、用into子句可以将select查询结果保存到一个新建的表中 例 由xs表创建“计算机系学生表”,包括学号和姓名:select 姓名,学号into 计算机学生from xswhere 专业名=计算机2 union子句,使用union子句可以将两个或多个select查询的结果合并成一个结果集. 使用union组合两个查询的结果集的基本规则是: (1)所有查询中的列数和列的顺序必须相同 (2)数据类型必须兼容 例 设在xscj数据库中建两个表:数学系学生,外语系学生,表结构与xs表相同,要求将两表的内容合并到xs表中:select * from xsunion allselect * from 数
33、学系学生union allselect * from 外语系学生,P117 (2)子查询的应用 1） Select * from teacher_info Where substring(teach_id,1,2 )=(select deptcode from dept_code where deptname=计算机工程系）,Select * from teacher_info Where substring(teach_id,1,2 )=(select deptcode from dept_code where deptname=计算机工程系）and substring(teach_id,3
34、,2)= (select substring(jysh_id,3,2) from staffroom_info Where jysh_name= 计算机应用）,2）select teach_id,name from teacher_info Where substring(teach_id,1,2 )=(select deptcode from dept_code where deptname=计算机工程系） And course_id= (select course_id from lesson_info Where course_name= 计算机专业英语),Select * from s
35、tud_grade Where substring(stud_id,3,2)=(select Deptcode from dept_code where dept_name=计算机工程)and courese_id =(select course_id from lesson_info where coure_name=计算机专业英语),3） Select stud_id,name,age,telcode,address From stud_info Where substring(stud_id,3,4)=(select substring(speccode,3,4) from specia
36、lty_code where specname=计算机专业英语),Select teach_id,name,course_id From teacher_info Where subsring(teach_id,1,2)= (select deptcode from dept_name where deptname=计算机工程系) And substring(teach_id,3,2)= (select substring(jysh_id,3,2) from staff_info where jysh_name=计算机应用),4）select name,telephone,course_idfrom teacher_infowhere substring(teach_id,1,2) in (select substring(teach_id,1,2) from teach_schedule where course_id=(select course_id from lesson_info where coure_name =多媒体技术) ),
