第4章_SQLSERVER_数据库的操作(查询语句).

1第四章数据库的操作四数据查询与视图4.1SELECT语句概述4.2单表查询4.3多表查询4.4嵌套查询4.5视图2第四章数据库的操作SQL语言最主要的功能是数据库查询，它是数据库其他操作（如统计、插入、修改、删除）的基础，也是DBMS的核心功能之一。使用SQL语句将要连接的表、查询所需的字段、筛选记录的条件、记录分组的依据、排序的方式以及查询结果的显示方式，写在一条SQL语句中，就可以从表或视图中迅速、方便地检索数据，具有十分灵活的使用方式和丰富的功能。3第四章数据库的操作4.1SELECT语句概述SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]]select_list::={*|{table_name|view_name|table_alias}.*|{column_name|expression}[[AS]column_alias]|column_alias=expression}[,...n]将查询结果存入新表new_table中。指定查询条件。指定将查询结果进行分组。与GROUPBY联合使用，指定分组条件。指定查询结果按order_expression进行排序。指定在FROM子句内返回表或视图的所有列。指定查询的列名。指定列名、常量、函数以及由运算符连接的列名、常量和函数的任意组合，或者是子查询。指定查询结果集内列名的别名。整个SELECT语句的含义是：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的记录，再按照SELECT子句中的select_list，选出记录中的列值、或计算值、或汇总值形成的查询结果。SELECT语句既可以完成简单的单表查询，也可以完成复杂的多表查询和嵌套查询。4第四章数据库的操作4.2单表查询——选择表中若干列如果用户只对表中部分列感兴趣，查询时可不使用WHERE子句，选择表中的全部列或部分列，也称作投影查询。基本语法为：SELECT[ALL|DISTINCT][TOPn[PERCENT]]select_listFROMtable_source参数说明：ALL：默认设置，指定在结果集中可以显示重复行。DISTINCT：指定在结果集中去除重复行，只显示唯一行。TOPn[PERCENT]：指定只从查询结果集中输出前n行或前百分之n行。5第四章数据库的操作【例4.1】查询全体学生的学号、姓名和年龄。SELECTSNO,SNAME,SAGEFROMXS查询指定列4.2单表查询——选择表中若干列6第四章数据库的操作【例4.2】查询全体学生的情况。SELECTSNO,SNAME,SDEPT,SSEX,SAGE,SHEIGHT,SCPM,SRMKFROMXS或SELECT*FROMXS查询全部列4.2单表查询——选择表中若干列7第四章数据库的操作【例4.3】查询全体学生的姓名及出生年份。（假设当前年份为2009年）。SELECTSNAME,2009-SAGEFROMXS查询结果中包含计算项4.2单表查询——选择表中若干列8第四章数据库的操作【例4.4】查询全体学生的姓名，并将姓和名分别显示。SELECTSNAME,SUBSTRING(SNAME,1,1),SUBSTRING(SNAME,2,2)FROMXS查询结果中包含计算项4.2单表查询——选择表中若干列9第四章数据库的操作【例4.5】对例4.4中的查询结果指定列标题。查询结果中的列标题显示SELECTSNAMEAS学生姓名,SUBSTRING(SNAME,1,1)AS姓,SUBSTRING(SNAME,2,2)AS名FROMXS或SELECT学生姓名=SNAME,姓=SUBSTRING(SNAME,1,1),名=SUBSTRING(SNAME,2,2)FROMXS4.2单表查询——选择表中若干列10第四章数据库的操作【例4.6】对比以下两条查询语句的执行结果，理解DISTINCT的作用。查询结果中消除重复行①SELECTSDEPT,SSEXFROMXS②SELECTDISTINCTSDEPT,SSEXFROMXS4.2单表查询——选择表中若干列11第四章数据库的操作【例4.7】查询XS表中前6个学生的情况。限制结果集的返回行数①SELECTTOP6*FROMXS②SELECTTOP60PERCENT*FROMXS4.2单表查询——选择表中若干列12第四章数据库的操作【例4.8】查询xs_kc表中学生成绩，对其成绩按以下规则进行替换：成绩90~100为“优秀”，80~89为“良好”，70~79为“中等”，60~69为“及格”，60为“不及格”。替换显示结果的内容SELECT学号=SNO,课程号=CNO,成绩=SCORE,等级=CASEWHENSCORE60THEN'不及格'WHENSCORE=60ANDSCORE=69THEN'及格'WHENSCORE=70ANDSCORE=79THEN'中等'WHENSCORE=80ANDSCORE=89THEN'良好'ELSE'优秀'ENDFROMXS_KC4.2单表查询——选择表中若干列13第四章数据库的操作查询时使用WHERE子句，选择表中满足条件的若干行，也称作选择查询。基本语法为：SELECTselect_listFROMtable_sourceWHEREsearch_condition查询条件运算符比较=、、、=、=、或!=等；确定范围BETWEENAND,NOTBETWEENAND确定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重条件AND,OR4.2单表查询——选择表中若干行14第四章数据库的操作【例4.9】查询选修课程号为‘C01’的学生的学号和成绩。SELECTSNO,SCOREFROMXS_KCWHERECNO='C01'比较运算符【例4.10】查询成绩高于85分的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMXS_KCWHERESCORE854.2单表查询——选择表中若干行15第四章数据库的操作【例4.11】查询年龄在20~23岁（含20岁和23岁）之间的学生姓名、系别。SELECTSNAME,SDEPTFROMXSWHERESAGEBETWEEN20AND23确定范围4.2单表查询——选择表中若干行16第四章数据库的操作【例4.12】查询选修C01或C02的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMXS_KCWHERECNOIN('C01','C02')确定集合4.2单表查询——选择表中若干行17第四章数据库的操作【例4.13】查询所有姓张的学生的学号和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'张%'字符匹配【例4.14】查询姓名中第二个汉字是“一”的学生的学号和姓名。SELECTSNO,SNAMEFROMXSWHERESNAMELIKE'_一%'4.2单表查询——选择表中若干行18第四章数据库的操作【例4.15】查询没有备注信息的学生情况。SELECT*FROMXSWHERESRMKISNULL涉及空值4.2单表查询——选择表中若干行19第四章数据库的操作【例4.16】查询电力专业所有男生的情况。SELECT*FROMXSWHERESDEPT='电力'ANDSSEX='男'多重条件【例4.17】查询选修C01或C02且分数大于85分的学生的学号及成绩。SELECTSNO,SCOREFROMXS_KCWHERE(CNO='C01'ORCNO='C02')ANDSCORE854.2单表查询——选择表中若干行20第四章数据库的操作4.2单表查询——聚集函数计算诸如平均值和总和的函数被称为聚集函数。使用聚集函数时，系统对整个表或表的某个组中的列进行汇总、计算，然后为它创建相应字段的单个的值。在SELECT语句中可以单独使用聚集函数，也可以与语句GROUPBY联合使用，列的数据类型决定了在该列上可以使用的聚集函数类型。函数作用数据类型COUNT统计列中元组的个数任意类型MIN求一列值中的最小值除bit以外的数据类型MAX求一列值中的最大值SUM计算一列值的总和只能用于数值型字段，如int、decimal、float、money等AVG计算一列值的平均值21第四章数据库的操作【例4.18】查询电力系学生的总人数。SELECTCOUNT(*)FROMXSWHERESDEPT='电力'COUNT函数【例4.19】查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSNO)FROMXS_KC4.2单表查询——聚集函数22第四章数据库的操作【例4.20】查询课程号为‘C01’的学生成绩的最高分和最低分。SELECTMAX(SCORE)最高分,MIN(SCORE)最低分FROMXS_KCWHERECNO='C01'MAX函数和MIN函数【例4.21】查询课程号为‘C01’的学生成绩的总分和平均分。SELECTSUM(SCORE)总分,AVG(SCORE)平均分FROMXS_KCWHERECNO='C01'SUM函数和AVG函数4.2单表查询——聚集函数23第四章数据库的操作4.2单表查询——查询结果分组在SELECT语句中使用GROUPBY子句可以将查询结果分组，并返回行的汇总信息，即对于GROUPBY子句中定义的每个组，各返回一个结果。此时，SELECT子句中的列名必须为分组列或列函数。基本语法为：[GROUPBY[ALL]group_by_expression[,...n][WITH{CUBE|ROLLUP}]]其中，WITHCUBE或WITHROLLUP用于在查询结果中附加汇总结果。24第四章数据库的操作【例4.22】查询各系学生人数。SELECTSDEPT系别,COUNT(*)人数FROMXSGROUPBYSDEPTGROUPBY基本用法4.2单表查询——查询结果分组25第四章数据库的操作【例4.23】查询各门课程的平均成绩和选修人数。SELECTCNO课程号,AVG(SCORE)平均成绩,COUNT(*)选修人数FROMXS_KCGROUPBYCNOGROUPBY基本用法4.2单表查询——查询结果分组26第四章数据库的操作【例4.24】查询各系男生人数、女生人数。SELECTSDEPT系别,SSEX性别,COUNT(*)人数FROMXSGROUPBYSDEPT,SSEXGROUPBY基本用法4.2单表查询——查询结果分组27第四章数据库的操作【例4.25】查询各系男生人数、女生人数、各系人数和总人数。SELECTSDEPT系别,SSEX性别,COUNT(*)人数FROMXSGROUPBYSDEPT,SSEXWITHROLLUP使用ROLLUP和CUBE子句4.2单表查询——查询结果分组28第四章数据库的操作【例4.26】除例4.25的查询要求外，还需汇总男生总数和女生总数。SELECTSDEPT系别,SSEX性别,COUNT(*)人数FROMXSGROUPBYSDEPT,SSEXWITHCUBE使用ROLLUP和CUBE子句4.2单表查询——查询结果分组29第四章数据库的操作【例4.27】查询平均成绩80分以上的学生学号和平均成绩。SELECTSNO,AVG(SCORE)AS平均成绩FROMXS_KCGROUPBYSNOHAVINGAVG(SCORE)=80使用HAVING子句4.2单表查询——查询结果分组30第四章数据库的操作【例4.28】查询全体学生的情况，查询结果按所在系的系号升序排列，同系学生按年龄降序排列。SELECT*FROMXSORDER