数据库应用基础第四章数据查询.ppt

1,第四章 数据检索,2,复习题,1 表的创建方法 2 修改表的SQL语句,3,本章主要内容,4.1SELECT语句 4.2带条件的查询 4.3多表查询 4.4UNION查询 4.5子查询,4,在了解了表的创建和修改之后，为获取必要信息，还必须对表进行查询等操作。,查询主要是根据用户提供的限定条件进行，查询的结果还是一张表。,5,4.1 SELECT 语句,SELECT ALL | DISTINCT select_list INTO new_table_name FROM table_name,table_name16,要查询的字段名，可以是从多个表中取出来的字段,查询结果放到新的临时表中,欲查询数据的表的名称,6,WHERE condition GROUP BY clause HAVING clause,依字段类别作总计函数处理,功能同WHERE,不同在于可再次过滤WHERE语句查到的结果,7,ORDER BY COMPUTE clause,允许用一条SELECT命令同时查询数据，并作数据总计动作,8,例6-1：查询数据库library_db中表borrower_info中的所有信息,use library_db select *from borrower_info,9,例6-2：查询表book_info中书的编号、名称以及该书分类编码在class表中所对应的分类名，并将class表中分类名字段名name在查询结果的显示中改为classname，以便于阅读，本查询要求列出图书编号（book_id）、图书名称(name)以及分类名称（表中字段名为name,在输出时改为classname）等信息。,10,use library_db select book_id,, classname= from book_info a,class b where a.class_id=b.class_id,11,例6-3把图书的价格price进行输出，且输出值在原有值的基础上提高10%。 select book_id,name,price,new_price=price*1.10 from library_db.dbo.book_info,12,在查询结果中还可以引入某些函数进行计算，如：,SELECT caption= function_name(col_name),13,总计函数有六个，它们分别是： SUM():求总和 AVG():求平均值 MIN():求最小值 MAX():求最大值 COUNT():传回非NULL值的字段数目 COUNT(*):传回符合查询条件的数目,14,例6-4：通过count(*)函数返回每本图书的借阅次数。,select book_id,count(*)as lend_count from lend_list group by(book_id) order by(lend_count)desc,15,例6-5：查询book_info表中价格字段price中的总价值、最高书价、最低书价、最高价和最低价的差价。,select sum=sum(price), max=max(price), min=min(price), sub=max(price)-min(price) from book_info,16,4.2 带条件的检索,在检索过程中，经常需要对数据根据一定的条件进行过滤，成为带条件的检索,WHERE HAVING GROUP BY,主要子句,17,4.2.1 WHERE 子句,它常与NOT/AND/OR一起使用,例6-6：查询满足如下条件的借书记录，其中还书日期（字段名为back_date）小于2010年1月1日，而且借阅日期（字段名为lend_date）不小于2009年6月23日，或者尚未还书，即还书日期为空。,18,use library_db select * from lend_list where (back_date=2009-6-23) or back_date is null,19,use library_db select * from lend_list where back_date=2009-6-23or back_date is null),20,4.2.2 Where子句中条件的指定,SQL_Server 支持的运算符如下： 1)逻辑运算符：AND/OR/NOT 2)比较符：，!，,=,= in,not in,any,all判断为集合成员否 between and判断满足指定区间否 like 匹配模式 is not null 测试空值,21,例6-8 查询book_info表的图书名称中包含“网站开发”字样的书籍，本例牵涉模式匹配。,use library_db select name from book_info where name like %网站开发%,22,关于字符匹配 ：匹配任意长度的字符串 _ ：只匹配一个字符 _ean ：匹配对指定范围 (例如a-f) 或集合 (例如abcdef) 中的任何单个字符 可以匹配不属于指定范围的任何单个字符,23,例6-9 查询表borrower_info中出生日期在1979.6.1到1980.6.1之间的借阅人的编号、姓名以及出生年月。,select id,name,birthday from borrower_info where birthday between 1979-6-1 and 1980-6-1,24,例6-10：查询数据库library_db中表lend_list中尚未归还的图书记录，即back-date字段值为null的借阅记录，同时在book_info表中查询相应的图书名称，请读者注意对字段为空或非空时的判断时应该在WHERE子句中使用 IS NULL 或 IS NOT NULL判断,25,use library_db select a.book_id,, borrower_id,lend_date from lend_list a,book_info b where a.book_id=b.book_id and back_date is null,26,例6-11查询数据库library_db中表class中分类编号为1,3和5的分类名称。,使用in 、any子句用于判定元素是否在集合中,use library_db select name from class where class_id in(1,3,5),27,例6-12在数据库library_db中查询书名包含“词汇”字样的书籍的编号和剩余库存量。根据对表book_info的查询结果来确定对表book_state的查询条件。,use library_db select book_id,leftnum from book_state where book_id= (select book_id from book_info where name like %词汇%),28,例6-13在数据库library_db中查询表borrower_info中字段值不在表lend_list中borrower_id字段值范围的所有借阅人信息。,use library_db select *from borrower_info where id not in (select borrower_id from lend_list),29,4.2.3 Having 子句,该子句也能指定查询条件，类似where子句,30,请与WHERE子句对照,例6-14在数据库library_db中从表lend_list中查询借阅次数小于等于2次的书籍的编号和借阅次数。,select book_id,total=count(*) from lend_list group by(book_id) having coun(*)=2,31,例6-15查询借阅次数为2-3次的书籍的编号和借阅次数。,select book_id,total=count(*) from lend_list group by(book_id) having count(*) between 2 and 3,32,4.2.4 COMPUTE BY及COMPUTE子句,COMPUTE BY子句可以通过BY指定字段进行分组计算，COMPUTE子句则计算所有的字段值之和,例6-16 查询编号为97*的借阅人并分别计算他们的借书次数，最后计算出符合查询条件的总借书次数。,33,select borrower_id,book_id from lend_list where borrower_id like 97% order by borrower_id compute count(book_id) by borrower_id compute count(book_id),34,4.3 多表查询,前面介绍的都是从一张表上查询数据，更多的情况是从多张表中查询数据。 例如一个数据库包含学生基本信息表和学生成绩信息表，若要得知道学生的基本信息和成绩，那就必须进行多表查询,35,例6-17通过查询book_info和lend_list两张表，以获得图书名称包含“VC”的图书的编号、名称以及其在2009年的借阅记录。,SELECT book_info.book_id,book_, lend_list.borrower_id,lend_list.lend_date,lend_list.back_date from book_info,lend_list,36,同名字段加表名,where book_ like %VC% and book_info.book_id=lend_list.book_id and datepart(year,lend.lend_date)=2009,37,该查询由于两张表中出现同名字段book_id，为了区分字段，加上了表名，整个查询命令显得十分冗长。为了简洁起见，用户可以采用为表指定别名的方法,38,39,不仅可以给不同的表赋以不同的别名，还可以给同一张表赋以不同的别名，操作起来就象是在操作两张表一样,40,4.4 UNION操作,通过UNION操作可以把从两个或两个以上的查询结果合并到一个结果集中,41,例6-19从表borrower_info和表Add_borrower_info中查询id和name信息，并把查询结果合并在一起,42,create table add_borrower_info (id int, name nvarchar(6), gender nchar(1) go insert into add_borrower_info values(990356,黄大明,1) go insert into add_borrower_info values(994427,王小玉,2) go,43,44,例6-20创建temp_borrower_info表，把borrower_info及Add_borrower_info表中的id和name字段的内容复制到temp_borrower_info表中，同时创建临时字段tpColumn，值为Temp,select id,name,tpcolumn=Temp into temp_borrower_info from borrower_info union select id,name,tpcolumn=Temp from add_borrower_info,45,46,4.5子查询,通过子查询获得表Add_borrower_ info的信息，并根据子查询的结果更新表temp_borrower_ info中的字段tpColumn数据,可在INSERT、SELECT、UPDATE、DELETE等处嵌套SELECT查询子句,47,update temp_borrower_info set t