SQLServer数据库应用第四章.pptVIP

1,第四章 数据检索,2,本章主要内容,SELECT语句 带条件的查询 多表查询 UNION查询 子查询,3,在了解了表的创建和修改之后，为获取必要信息，还必须对表进行查询等操作。,查询主要是根据用户提供的限定条件进行，查询的结果还是一张表。,4,select * from tsing_DB1.dbo.stud_score,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 FOR BROWSE,允许用一条SELECT命令同时查询数据，并作数据总计动作,8,例：查询数据库My_DB2中表device_manage中的所有信息,use My_DB2 select * from device_manage,9,例：把表stud_score中comp_score字段名在查询结果的显示中改为computer_score,本查询要求列出学年度(year)、学号(stud_id)、计算机科目(computer#)及计算机科目的成绩(表中字段名为comp_score，在输出时改为computer_score)等信息,10,select year, stud_id, computer#=Computer score, computer_score=comp_score from stud_score,11,例：输出数学成绩math_score，且输出值在原有值得基础上提高了5%,SELECT year, stud_id, new_score=math_score*1.05 FROM stud_score,12,在查询结果中还可以引入某些函数进行计算，如：,SELECT caption= function_name(col_name),13,总计函数有六个，它们分别是： SUM():求总和 AVG():求平均值 MIN():求最小值 MAX():求最大值 COUNT():传回非NULL值的字段数目 COUNT(*):传回符合查询条件的数目,14,例：通过AVG()函数返回每年度学生的平均成绩,select year,average =avg(comp_score) from stud_score group by(year) order by(year),15,例：查询stud_score 表中成绩字段comp_score中的总成绩、最高分、最低分、最高分和最低分的差额,SELECT sum=sum(comp_score), max=max(comp_score), min=min(comp_score), sub=max(comp_score)-min(comp_score) FROM stud_score,16,4.2 带条件的检索,在检索过程中，经常需要对数据根据一定的条件进行过滤，成为带条件的检索,WHERE HAVING GROUP BY,主要子句,17,4.2.1 WHERE 子句,它常与NOT/AND/OR一起使用,例：查询计算机课成绩(comp_score)不大于数学成绩(math_score)大于化学成绩(chem_score)，或者化学成绩大于数学成绩的学生,18,USE My_DB1 SELECT year,stud_id,comp_score FROM stud_score WHERE (comp_scorechem_score) OR chem_scoremath_score,括号换 一个位置呢？,19,USE My_DB1 SELECT year,stud_id,comp_score FROM stud_score WHERE comp_scorechem_score OR chem_scoremath_score),20,4.2.2 Where子句中条件的指定,SQL_Server 支持的运算符如下： 1)逻辑运算符：AND/OR/NOT 2)比较符：，!，,=,= in,not in,any,all判断为集合成员否 between and判断满足指定区间否 like 匹配模式 is not null 测试空值,21,例：模式匹配举例，查询stud_info中的姓氏为陈的学生姓名,USE My_DB1 SELECT name FROM stud_info WHERE name LIKE 陈%,22,关于字符匹配 ：匹配任意长度的字符串 _ ：只匹配一个字符 _ean ：匹配对指定范围 (例如a-f) 或集合 (例如abcdef) 中的任何单个字符 可以匹配不属于指定范围的任何单个字符,23,包含区间的两个端点,例：查询stud_score表中学年年度在1997（含1997）到1998（含1998）的学生信息,USE My_DB1 SELECT year, stud_id, comp_score FROM stud_score WHERE year BETWEEN 1997 AND 1998,24,例：查询表stud_info中出生日期在1979.1.1到1980.6.1之间的学生的学号，姓名，以及出生年月,USE My_DB1 SELECT stud_id, name, birthday FROM stud_info WHERE birthday BETWEEN 1-1-1979 AND 6-1-1980 ,25,例：查询数据库My_DB2中表device_manage 中supply_id 字段值为null的设备信息，请读者注意对字段为空或非空时的判断时应该在WHERE子句中使用 IS NULL 或 IS NOT NULL判断,26,Use My_DB2 SELECT dev_id,dev_name,lab_id FROM device_manage WHERE supply_id is null,27,例：查询数据库My_DB1中表stud_score中学生数学成绩为60,70或80的学生的姓名,学年年度和该学年的数学成绩,Use My_DB1 SELECT year, stud_id, math_score FROM stud_score WHERE math_score in (60,70,80),使用in 、any子句用于判定元素是否在集合中,28,例：在数据库My_DB2中根据对表device_use的查询结果来确定对表device_manage的查询条件,Use My_DB2 SELECT dev_id,dev_name,lab_id FROM device_manage WHERE dev_id= (SELECT dev_id FROM device_use WHERE experiment_lab is null),29,例：在数据库My_DB2中查询表device_manage中dev_id字段值不在表device_use中dev_id字段值范围的所有仪器信息,SELECT * from device_manage WHERE dev_id NOT IN (SELECT dev_id FROM device_use),30,4.2.3 Having 子句,该子句也能指定查询条件，类似where子句,例：在数据库My_DB1 中从表stud_score中查询学年年度早于1998年的每年comp_score的总和,31,Use My_DB1 SELECT year,total= sum(comp_score ) FROM stud_score GROUP BY(year) HAVING year1998,请与WHERE子句对照,32,例：查询学年年度在1996与1998年之间(含1996和1998年)的年度comp_score的总和,USE My_DB1 SELECT year,total= sum(comp_score) FROM stud_score GROUP BY(year) HAVING year BETWEEN 1996 AND 1998,33,4.2.4 COMPUTE BY及COMPUTE子句,COMPUTE BY子句可以通过BY指定字段进行分组计算，COMPUTE子句则计算所有的字段值之和,查询学号为97*的学生并按入学年年度分别计算他们的总分，最后计算出comp_score 的总和,34,USE My_DB1 SELECT stud_id,year,comp_score FROM stud_score WHERE stud_id LIKE 97% ORDER BY year COMPUTE SUM(comp_score)BY year COMPUTE SUM(comp_score),35,4.3 多表查询,前面介绍的都是从一张表上查询数据，更多的情况是从多张表中查询数据。 例如一个数据库包含学生基本信息表和学生成绩信息表，若要得知道学生的基本信息和成绩，那就必须进行多表查询,36,例:查询stud_info和stud_score两表，得到学号为980814的学生的姓名、性别及其在1998年的数学科目的成绩,SELECT stud_info.stud_id,stud_,stud_info.gender,stud_score.math_score FROM stud_info ,stud_score,37,WHERE stud_info.stud_id=980814 AND stud_info.stud_id= stud_score.stud_id AND stud_score.year=1998,同名字段加表名,38,该查询由于两张表中出现同名字段stud_id，为了区分字段，加上了表名，整个查询命令显得十分冗长。为了简洁起见，用户可以采用为表指定别名的方法,39,SELECT a.stud_id,,a.gender,b.math_score FROM stud_score b,stud_info a WHERE a.stud_id=980814 AND a.stud_id=b.stud_id AND b.year=1998,40,如果在输出时能考虑排序，这样输出的结果就容易阅读,如在上例中，要求将查询结果按学号stud_id字段的顺序输出，增加下面一行命令即可：,ORDER BY a.stud_id,41,不仅可以给不同的表赋以不同的别名，还可以给同一张表赋以不同的别名，操作起来就象是在操作两张表一样,SELECT a.stud_id,b.stud_id, FROM stud_info a,stud_info b WHERE a.stud_id=b.stud_id,42,4.4 UNION操作,通过UNION操作可以把从两个或两个以上的查询结果合并到一个结果集中,43,例：从表stud_info和表Add_stud_info中查询stud_id和name信息，并把查询结果合并在一起,44,SELECT stud_id, name FROM stud_info UNION SELECT stud_id, name FROM Add_stud_info,45,例:创建temp_stud_info表，把stud_info及Add_stud_info表中的stud_id和name字段的内容复制到temp_stud_info表中，同时创建临时字段tpColumn，值为Temp,46,SELECT stud_id,name,tpColumn=Temp INTO temp_stud_info FROM stud_info UNION SELECT stud_id,name,tpColumn=Temp FROM Add_stud_info,47,4.5子查询,通过子查询获得表Add_stud_ info的信息，并根据子查询的结果更新表temp_stdu_ info中的字段tpColumn数据,可在INSERT、SELECT、UPDATE、DELETE等处嵌套SELECT查询子句,48,UPDATE temp_stud_info SET t