学习情境3学习情境3〓数据查询技术【能力要求】
 能够使用查询语句完成数据查询任务。
 能够结合查询技术完成修改信息页面的功能。
 能够根据登录流程,结合查询完成登录页面的功能。
 能够结合查询技术完成添加课程、修改课程页面的功能。
 能够完成必要的存储过程、触发器和函数功能设计。
 能够合理配置SQL Server数据库的安全性。
【任务分解】
 任务31数据查询
 任务32使用视图
 任务33设计并实现“修改读者”页面
 任务34设计并实现“添加图书”页面
 任务35设计并实现“修改图书”页面
 任务36设计并实现“管理员登录”页面
 任务37存储过程设计
 任务38配置数据库安全性
【重难点】
 多表查询。
 查询在修改信息页面和登录页面中的应用。
 存储过程设计。
 数据库安全性配置。
【自主学习内容】
设计“邮箱应用系统”的“用户登录”页面,编写代码实现登录功能,登录后页面转向主页面。
任务31数 据 查 询3.1.1查询语句格式查询语句的语法格式如下: SELECT \[TOP n \[PERCENT\]\] <字段列表> \[INTO 表名\] FROM <表名/视图名列表> \[WHERE 条件表达式\] \[ORDER BY 字段名1\[ASC|DESC\]\[,字段名2 \[ASC|DESC\] \[,...\]\]\]\[GROUP BY 字段名列表 \[HAVING 条件\]\]其中,TOP n \[PERCENT\]关键字表示显示查询结果中的前n条或前百分之n条；［INTO 表名］关键字可以将查询结果存储在数据表中；WHERE 条件表达式关键字可以将符合条件表达式的记录显示在查询结果中；ORDER BY关键字将查询结果按照指定字段升序（ASC）或降序（DESC）排序；GROUP BY关键字将查询结果按照指定字段分组。
数据库案例与应用开发项目教程（SQL Server 2017 +Visual Studio 2017综合开发）学习情境3数据查询技术各关键字的使用方法请参照本学习情境的实际查询案例。
3.1.2查询数据介绍
本学习情境的查询任务基于library数据库中的booktype、readers、books和reader_book 4张数据表,4张数据表的记录如图31～图34所示。
图31booktype表结构及数据

图32readers表结构及数据

图33books表结构及数据
图34reader_book表结构及数据

提示: 数据查询与表记录无关,与表结构相关。要清楚所查询数据可以从哪些表找到,另外,还要清楚数据表之间的关联,这在设置多表查询的连接条件时非常关键,有关数据表间的联系请参考任务23中的外键约束。
3.1.3单表查询
单表查询是指查询数据可以从一张表得到,语法上体现为FROM关键字后的表名列表只有一张表,是数据查询中最简单的查询。
1. 查询语句的最简语法格式
SELECT <字段列表> FROM <表名/视图名列表> \[WHERE 条件表达式\]2. 单表查询案例及查询关键字的使用
（1） 查询数据表中部分字段。
将要查询的字段写在“字段名列表”处,字段之间用英文逗号隔开,字段顺序可以调换,如: SELECT reader_id,reader_name FROM readers语句执行结果如图35所示,因语句没有where条件,所以显示readers表中所有记录的reader_id和reader_name字段的值,查询字段顺序可以和字段的物理顺序不一致。
图35部分字段查询结果
（2） 查询数据表中的全部字段。
查询全部字段时,可以将所有字段一一列出,也可以用代替,例如: SELECT FROM readers将readers表中的所有字段按照物理顺序列出来,查询结果与图32相同。
（3） 为查询字段设置别名。
设置的别名仅在查询结果中有效,并不改变字段的实际名称,设置别名的方法有: 
 字段名 \[AS\] 别名
 别名=字段名SELECT姓名=reader_name, reader_id AS 读者编号, sex 性别 FROM readers查询结果如图36所示,字段名显示为汉字的“姓名”“读者编号”和“性别”。
图36设置别名后的查询结果
（4） 查询经过计算的值。
有些查询结果不一定能够直接从表中得到,可能需要计算,如“查询所有学生的姓名和年龄”,年龄在readers数据表中没有直接给出,但有学生的出生日期,年龄可以通过出生日期计算出来,查询语句如下: SELECT reader_name 姓名,YEAR (GETDATE ())-YEAR (birthday) 年龄 FROM readers查询结果如图37所示。
图37计算值查询结果
（5） 去除查询结果的重复值。
若要去除查询结果中的重复值,可以在字段前加DISTINCT关键字,例如: SELECT reader_id FROM reader_book
SELECT distinct reader_id FROM reader_book 查询结果如图38所示。
图38去除重复前后比较
（6） 显示部分查询结果。
可使用TOP n 或TOP n PERCENT关键字返回部分查询结果,SQL Server中必须将TOP关键字放在SELECT关键字之后,字段名列表之前,例如: SELECT TOP 2 reader_id,reader_name FROM readers
SELECT TOP 2 PERCENT reader_id,reader_name FROM readers查询结果如图39所示。
图39TOP关键字查询结果
（7） 保存查询结果到数据表。
使用INTO关键字将查询结果保存到数据表,SQL Server中必须将INTO关键字放在字段名列表之后,FROM关键字之前,例如:SELECT INTO readers_new FROM readers WHERE sex=1功能说明: 将readers表中sex为1的记录另存在readers_new表中。SELECT reader_id, reader_name INTO #readers1 FROM readers WHERE sex=0功能说明: 将readers表中sex为0的记录另存在临时表#readers1中,#readers1表只包含reader_id和reader_name两个字段。
提示: 临时表存储在系统数据库tempdb中,当服务器重启后,所有的临时表将被自动清除,临时表的最大特点是可以被所有数据库共享,因此如果要临时共享数据表,可以将数据表保存为临时表。
（8） 排序查询结果。
对查询结果排序的关键字为ORDER BY,排序字段可以直接用字段名表示,也可以用字段列表中的序号表示。查询语句中如果无WHERE关键字,ORDER BY关键字放在FROM关键字之后,有WHERE关键字,则放在WHERE关键字之后,排序有升序和降序两种方式,默认为升序,也可加关键字ASC,降序加关键字DESC。SELECT FROM readers ORDER BY birthday功能说明: 查询readers表中的所有记录,按birthday升序排序。SELECT FROM readers ORDER BY birthday DESC功能说明: 查询readers表中的所有记录,按birthday降序排序。SELECT FROM reader_book ORDER BY score DESC,reader_id功能说明: 查询reader_book表中的所有记录,先按score字段降序排序,score值相同按reader_id字段升序排序。SELECT type_id,type_name FROM booktype ORDER BY 2功能说明: 查询booktype表的所有记录,只显示type_id、type_name字段,按照第2个字段（type_name）升序排序查询结果。
（9） 查询条件。
使用WHERE关键字指定查询条件,查询语句无WHERE关键字会显示所有记录,有WHERE关键字,显示符合条件的记录,条件表达式中常用的运算符如表31所示。表31条件表达式中常用的运算符类别运 算 符关系运算=、 >、 <、 >=、 <=、 <>、 !=逻辑运算AND、OR、NOT集合运算IN、 NOT IN、 ANY、 ALL类别运 算 符模糊运算LIKE空值运算IS \[NOT\] NULL范围运算BETWEENANDSELECT  FROM readers WHERE sex<>1功能说明: 查询readers表中sex值不等于1的记录,<>可以用!=表示。SELECT  FROM readers WHERE birthday between '2011-1-1' AND '2018-1-1'功能说明: 查询readers表中birthday在201111到201811之间的数据,条件等价于“birthday>='201111' AND birthday <= '201811' ”。SELECT  FROM reader_book WHERE btime is null功能说明: 查询reader_book表中btime为空的数据。SELECT  FROM reader_book WHERE book_id IN('1000001','1000002','1000003')功能说明: 查询reader_book表中book_id为1000001、1000002或1000003的数据,条件等价于“book_id='1000001' or book_id='1000002' or book_id='1000003' ”。
模糊运算符LIKE关键字中所用的通配符如表32所示。表32LIKE关键字中所用的通配符通 配 符含义％表示若干任意字符_表示单个任意字符\[\]表示方括号里列出的任意一个字符\[^\]任意一个没有在方括号里列出的字符SELECT reader_id,reader_name FROM readers WHERE reader_name like '赵%'功能说明: 查询readers表中姓赵的记录,条件等价于“SUBSTRING (reader_name,1,1)='赵' ”。SELECT reader_id,reader_name FROM readers WHERE reader_name like '%赵%'功能说明: 查询readers表reader_name中含有“赵”字的记录。SELECT reader_id,reader_name FROM readers WHERE reader_name like '_赵%'功能说明: 查询readers表reader_name中第二个字是“赵”字的记录。SELECT reader_id,reader_name FROM readers WHERE reader_name like '_赵_'功能说明: 查询readers表reader_name中有三个字且第二个字是“赵”字的记录。SELECT reader_id,reader_name FROM readers WHERE reader_name like '\[赵,钱,孙\]%'功能说明: 查询readers表中姓赵、钱、孙的读者的记录。SELECT reader_id,reader_name FROM readers WHERE reader_name like '\[^赵,钱,孙\]%'功能说明: 查询readers表中不姓赵、钱、孙的读者的记录。
（10） 集合函数的统计功能。
常用集合函数如表33所示。表33常用集合函数函数功能SUM()求数值型字段的和AVG()求数值型字段的平均COUNT()统计数量（行数）MAX()求最大MIN()求最小SELECT sum(days),avg(days),max(days),min(days),count() FROM readers功能说明: 查询readers数据表中的days字段的和、平均值、最高值、最低值和记录条数值,结果如图310所示。
图310集合函数查询结果
SELECT MAX(reader_name),MIN(reader_name) FROM readers功能说明: 字符类按照字母顺序排序,结果如图311所示。
图311reader_name字段的MAX和MIN
3.1.4多表查询
当FROM关键字后的表名数量多于1时称为多表查询,是数据库中最主要的查询方式,多表查询为了保证查询结果的准确性应带上连接条件。连接条件有内连接、左连接、右连接、完全连接4种。本节通过不同案例说明连接条件的使用方法。
1. 最简单的内连接条件表示
（1） 查询“孙倩”读者的借阅时间。
分析: 借阅时间（btime）只有reader_book表有,条件“孙倩”是readers表中reader_name字段的值,因此FROM关键字后的表名有两个,语句如下: SELECT btime FROM readers,reader_book WHERE reader_name='孙倩' 查询结果如图312（a）所示,显然结果比实际多很多,是因为没有带上连接条件。加上简单的连接条件,语句如下: SELECT btime FROM readers,reader_book WHERE reader_name='孙倩' AND readers.reader_id=reader_book.reader_idreaders表和reader_book有一个相同意义的字段reader_id,直接在题目条件后加上“AND readers.reader_id=reader_book.reader_id”,即可查询到正确的结果,如图312（b）所示。
图312查询结果
提示: 查询若涉及多张表,为了查询的正确性,一定要带上连接条件,一般情况下,2张表至少要有1个连接条件,3张表至少要有2个连接条件,表数量越多,则连接条件越多。
（2） 查询读者的姓名、所借书名和借阅时间。SELECT reader_name,book_name,btime FROM readers,reader_book,books WHERE readers .reader_id =reader_book .reader_id AND books .book_id=reader_book .book_id查询结果如图313所示。
图3133张表的查询结果
提示: 如果查询数据仅来自readers表和books表,reader_book表也一定要带上,因为readers表和books表没有直接相关联的字段,它们通过reader_book表实现了多对多联系,reader_book表是readers表和books表的桥梁。
（3） 查询读者的姓名、所借图书的书名、借阅时间和图书类型名。SELECT reader_name,book_name,btime,type_name FROM readers,reader_book,books,booktype WHERE readers.reader_id =reader_book.reader_id AND books.book_id=reader_book.book_id AND booktype.type_id=books.type_id查询结果如图314所示。
图3144张表的查询结果
2. 内连接
内连接（INNER JOIN）与上述所说的连接条件的查询结果一样,均将表中有等价关系的数据显示出来。内连接使用INNER JOIN关键字表示,语法格式如下: 表1 INNER JOIN 表2 ON 表1.连接字段=表2.连接字段内连接查询结果为表1、表2中连接字段等价的数据,上面3个查询可分别用如下3条内连接的条件表示。SELECT btime FROM readers INNER JOIN reader_book ON readers .reader_id= reader_book .reader_id WHERE reader_name='孙倩'
SELECT reader_name,book_name,btime FROM readers INNER JOIN reader_book ON readers.reader_id =reader_book .reader_id INNER JOIN books ON reader_book .book_id=books .book_id
SELECT reader_name,book_name,btime,type_name FROM readers INNER JOIN reader_book ON readers .reader_id =reader_book .reader_id INNER JOIN books ON reader_book.book_id =books.book_id INNER JOIN booktype ON booktype.type_id=books.type_id3. 左连接
左连接（LEFT JOIN）也叫左外连接,有左表和右表之分,语法格式如下: 表1 LEFT \[OUTER\] JOIN 表2 ON 表1.连接字段=表2.连接字段该连接条件会将左表中的数据全部显示出来,右表记录在左表中有对应值就显示出来,没有对应值显示为NULL,例如: SELECT  FROM readers LEFT OUTER JOIN reader_book ON readers .reader_id=reader_book .reader_id结果如图315所示,readers为左表,reader_book为右表,OUTER关键字可以省略。
图315readers表和reader_book表左连接查询结果
4. 右连接
右连接（RIGHT JOIN）和左连接相似,只是将右表结果全部显示,左表与右表有对应的值就显示出来,无对应值以NULL显示,关键字为RIGHT \[OUTER\] JOIN,例如。SELECT  FROM reader_book RIGHT JOIN readers ON readers .reader_id=reader_book .reader_id查询结果与上述左连接结果一样。
5. 完全连接
完全连接（FULL JOIN）是左连接与右连接的综合,会将左表、右表的数据全部显示,有对应的值就对应显示,无对应的以NULL填充,关键字为FULL \[OUTER\] JOIN,例如: SELECT  FROM booktype FULL OUTER JOIN books ON booktype .type_id =books .type_id 查询结果如图316所示。
图316booktype表和books表完全连接查询结果
因books表中的type_id全部能在booktype中找到对应的值,所以此时的FULL JOIN与LEFT JOIN的查询结果一样。
6. 交叉连接
交叉连接（CROSS JOIN）也叫无连接,查询结果是表中数据的所有组合可能,例如: SELECT  FROM booktype CROSS JOIN books等价于SELECT  FROM booktype , books因books表有8条记录,booktype表有5条记录,其所有组合可能记录数为8×5=40,故无连接的查询结果有40条记录。
7. 自连接
自连接是指同一张表内进行的连接,此时要为表取别名。
例如,查询和“孙倩”性别相同且出生日期在2000年之后的读者信息,使用自连接表示的语句如下: SELECT B. FROM readers A, readers B WHERE A.reader_name ='孙倩' AND B.birthday>'2000-12-31' AND A.sex =B.sex AND B.reader_name<>'孙倩'提示: 此时readers表分别取别名A、B,WHERE关键字后的A、B不能用反了,否则查询结果不正确,查询结果如图317所示。
图317自连接查询结果
上述查询任务也可使用子查询实现,语句如下: SELECT  FROM readers WHERE birthday>'2000-12-31' AND sex=(SELECT sex FROM readers WHERE reader_name='孙倩') AND reader_name <>'孙倩'子查询将在3.1.6节中介绍。
8. 合并多个查询结果
合并多个查询结果也叫联合查询（UNION \[ALL\]）,可以将两个以上的查询结果集合并成一个结果集,例如: SELECT reader_id,reader_name FROM readers 
union
SELECT book_id,book_name FROM books查询结果如图318所示。
图318合并查询结果
提示: 联合查询是将查询结果集顺序合并,要求每个查询结果集中的字段数、数据类型相同,宽度不同时以最宽的字段宽度输出结果。结果集中的字段名来自第一个SELECT语句。最后一个SELECT语句可以带ORDER BY子句,对整个查询结果起作用,但只可用第一个SELECT子句中的字段为排序关键字。不带ALL关键字只保存结果集中重复值中的一个,有ALL关键字会保留所有结果,例如: SELECT reader_id,reader_name FROM readers 
union all
SELECT book_id,book_name FROM books ORDER BY reader_id查询结果如图319所示。
图319排序合并查询结果
3.1.5使用数据查询添加记录1. 语法格式查询结果可以添加到数据表记录中,语法格式如下:INSERT表名\[(字段名列表)\] SELECT 字段名列表 FROM 表名列表 WHERE 条件2. 举例
下列TSQL程序段可将查询结果添加到数据表。--生成一个新表reader,其结构与readers表一样,但记录为空
SELECT  INTO reader FROM readers WHERE 2>3
--将readers表中sex为1的记录添加到read表中
INSERT reader SELECT  FROM readers WHERE sex=1
--将readers表中sex为0的记录添加到reader表中,只需要reader_id、sex和reader_name的值
INSERT reader(reader_id,sex,reader_name) SELECT reader_id,sex ,reader_name FROM readers WHERE sex=0执行后reader表记录如图320所示。
图320reader表记录3.1.6子查询1. 引入问题查询任务: 查询没有借书读者的姓名、性别。
分析: 读者没借书的特点是该读者的编号在readers表中,而在reader_book表中没有。
2. 嵌套子查询基本语法格式
当一个查询作为另一个查询的条件时,称为子查询,常见子查询的语法格式如下: SELECT字段名列表 FROM 表名列表 WHERE 字段名 IN|NOT IN|关系表达式 ANY|关系表达式 ALL (SELECT 字段名 FROM 表名列表 WHERE 条件表达式)提示: 圆括号内的SELECT块可称为内查询或子查询,圆括号外的查询叫父查询或外层查询,通过父查询WHERE后的字段名与子查询SELECT后的字段名相关联,通常子查询的字段名列表只有一个,应与父查询中的字段名含义相同。
3. 解决问题
查询没有借书读者的姓名、性别的语句如下: SELECT reader_name,sex FROM readers WHERE reader_id NOT IN (SELECT reader_id FROM reader_book)查询结果如图321所示。
图321没有借书读者的姓名、性别
同类问题: 查询没有读者借阅的图书信息,即book_id在books表而在reader_book表中没有,语句如下: SELECT  FROM books WHERE book_id NOT IN(SELECT book_id FROM reader_book )查询结果如图322所示。
图322没有读者借阅的图书信息
其他使用子查询的案例有: SELECT FROM readers WHERE birthday>(SELECT birthday FROM readers WHERE reader_ 
name='孙倩')功能说明: 查询birthday比孙倩读者birthday大的学生的信息,结果集中没有孙倩的数据,如果条件将“>”改成“>=”,则结果集中有孙倩的数据。SELECT  FROM readers WHERE birthday>(SELECT birthday FROM readers WHERE sex=0)功能说明: 语句出错,因为readers表中sex为0的记录有4条,子查询中的birthday结果集有4个,即子查询的结果不止一个,此时可加上ALL或ANY 关键字。SELECT  FROM readers WHERE birthday>any (SELECT birthday FROM readers WHERE sex=0)功能说明: >ANY表示只要比子查询中的任意一个大即可,即比子查询结果中最小的大即可,查询结果如图323所示；>ALL关键字表示要比结果集中所有的结果都大,即比子查询结果中最大的大才成立。图323加入ANY关键字后的查询结果
SELECT  FROM readers WHERE sex=1 AND birthday>(SELECT birthday FROM readers WHERE reader_name='孙倩')功能说明: 查询readers表中sex为1且birthday比孙倩的birthday大的读者信息。
几乎所有的内连接查询都可以用子查询实现,例如: SELECT distinct readers.FROM readers INNER JOIN reader_book ON readers .reader_id=reader_book .reader_id 等价于SELECT  FROM readers WHERE reader_id IN(SELECT reader_id FROM reader_book)提示:  前一个查询要去除重复值,否则结果与后一个语句不同。
4. 相关子查询
相关子查询是指在子查询的条件中引用了父查询表中的字段值。相关子查询与前面的嵌套子查询执行顺序不同,嵌套子查询先执行子查询,然后将子查询作为父查询的条件；相关子查询是以父查询中的行为单位,先选取父查询中的第一行,然后子查询利用此行中的相关字段值进行查询,父查询根据子查询返回的结果,判断此行是否满足条件,满足就记录在结果集中,不满足就抛弃,然后继续选取父查询中的下一行,直到父查询中的所有行都判断完。
（1） 查询没有借阅1000001图书的读者姓名和性别。
使用嵌套子查询语句,如下: SELECT reader_name,sex FROM readers WHERE reader_id NOT IN(SELECT reader_id FROM reader_book WHERE book_id='1000001')使用相关子查询语句,如下: SELECT reader_name,sex FROM readers WHERE NOT EXISTS(SELECT reader_id FROM reader_book WHERE readers.reader_id=reader_book.reader_id AND book_id!='1000001')结果如图324所示。
提示:  相关子查询中的查询字段列表可以用,而嵌套子查询不可以。图324没有借阅1000001号图书的读者信息
（2） 查询所有借书读者的编号、姓名和性别。
使用相关子查询的语句如下: SELECT reader_id,reader_name,sex FROM readers WHERE EXISTS (SELECT   FROM reader_book WHERE readers .reader_id=reader_book .reader_id )结果如图325所示。
图325已借书读者的信息
SELECT reader_id,reader_name,sex FROM readers WHERE NOT EXISTS (SELECT  FROM reader_book WHERE readers .reader_id=reader_book .reader_id )加上NOT关键字是将未借书读者的信息检索出来,结果如图326所示。
图326未借书读者的信息
3.1.7分组查询1. 引入问题（1） 查询2021001读者借阅图书的数量。SELECT count() FROM reader_book WHERE reader_id='2021001'查询结果如图327所示。
图3272021001读者的借书数量
（2） 查询每个读者的借书数量。
查询指定读者的借书数量可以直接查询到。如果要把每个读者的借书数量都计算出来,需要按照读者编号分别计算,此时要用到分组关键字。
2. 分组关键字
GROUP BY可以按照某字段将将记录分成若干小组,语法格式如下: GROUP BY字段或计算字段 \[HAVING 条件\]GROUP BY关键字要放置在WHERE关键字之后,可以按照字段分隔表中数据,将值相同的分成一组。
3. 解决问题
（1） 查询每个读者的借书数量。SELECT reader_id 读者编号,count() 借书数量 FROM reader_book GROUP BY reader_id查询结果如图328所示。