第六章数据查询

第六章数据查询

SQL是关系数据库标准语言,该语言是基于关系运算的,其中最常用的是SELECT语句，因为使用数据库的最终目的是为了利用数据库中的数据，而SELECT语句的功能就是从数据库中检索出符合用户需求的数据，任何从数据库中取得数据的操作最终都将体现为SELECT语句。本章主要讲述了SELECT语句结构和使用方法。6.1SQL语言的数据理论基础

SQLServer是一个关系数据库管理系统。关系数据库建立在关系模型基础之上，具有严格的数学理论基础。关系数据库对数据的操作除了包括集合代数的并、差、交等运算之外，还定义了一组专门的关系运算：选择、投影和联接。关系运算的特点是运算的对象和结果都是表。6.1SQL语言的数据理论基础6.1.1选择选择(Selection)是单目运算，其运算对象是一个表。该运算按给定的条件对关系作水平分割，即选择符合条件的行。条件可用命题公式F表示，F中的运算对象是常数(用引号括起来)或元组分量(属性名或列的序号)，运算符有算术比较运算符(<，≤，>，≥，≠)和逻辑运算符(∩，∪，！)。关系R关于公式F的选择操作用σF(R)表示。形式定义如下:σF(R)＝{t|t∈R∧F(t)＝true}其中，F表示选择条件，它是一个逻辑表达式，取逻辑值‘真’或‘假’。R是被操作的表。6.1SQL语言的数据理论基础【例6-1】有一个学生基本情况表student如表6-1所示,若要在表中找出政治面貌是党员的女同学的行并形成一个新表，则选择运算表达式是：σ政治面貌=’党员’∧性别=‘女’(student)运行结果如图6-2所示.表6-1学生基本情况表表6-2选择运算结果学号姓名出生日期性别政治面貌010548周长贵1983-11-10男党员010389刘娜1985-11-14女党员039484张颖1985-12-18女团员930032张博闻1984-2-22男党员学号姓名出生日期性别政治面貌010389刘娜1985-11-14女党员6.1SQL语言的数据理论基础6.1.2投影

关系R上的投影是从R中选择出若干属性列组成新的关系。即对一个关系进行垂直分割，消去某些列，并重新安排列的顺序，再删去重复行。记作πA(R)＝{t[A]|t∈R}其中，A为R中的属性列。投影操作是从列的角度进行的运算。投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组，因为取消了某些属性列后，就可能出现重复行，应取消这些完全相同的行。6.1SQL语言的数据理论基础【例6-2】利用投影运算查询学生的学号和姓名。投影运算表达式为：Π学号,姓名(student)或Π1,2(S)6.1SQL语言的数据理论基础【例6-3】查询姓名为张颖的学生的姓名、性别、政治面貌。可以通过两个运算的组合，查询出所需要的列和行,其表达式为：Π2,4,5(σ姓名=‘张颖’(student))结果如表6-4所示

表6-4选择、投影组合运算结果姓名性别政治面貌张颖女团员6.1SQL语言的数据理论基础6.1.3联接

联接也称为θ联接。它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。记作：。。。。。其中A和B分别为R和S上度数相等且可比的属性组。θ是比较运算符。联接运算从R和S的笛卡尔积R×S中选取（R关系）在A属性组上的值与（S关系）在B属性组上的值满足比较关系θ的元组。θ为“＝”的联接运算称为等值联接。它是从关系R与S的笛卡尔积中选取A、B属性值相等的那些元组。即等值联接为。。。。。6.1SQL语言的数据理论基础

若A、B是相同的属性组，就可以在结果中把重复的属性去掉。这种去掉了重复属性的等值联接称为自然联接。自然联接可记作R。。。。。

6.2SELECT语句结构

数据查询是从数据库中获得所需要的数据。查询是数据库的主要操作之一，也是SQL语言最主要的功能。由于用户对数据库查询的要求多种多样，因此SQL的查询功能非常强大，但SQL只有一条SELECT查询语句，该语句的语法很复杂，以满足各种不同的需求。SELECT语句是SQL最基本和最重要的语句。用户使用SELECT语句不但可以查看普通数据库中的表格和视图的信息，还可以查看SQLServer的系统信息。

6.2SELECT语句结构

SELECT语句的语法格式如下:SELECTselect_list[INTOnew_table_name]FROMsource_table[WHEREsearch_condition1][GROUPBYexpression1][HAVINGsearch_condition2][ORDERBYexpression2[ASC|DESC]]其中：●Select_list：指明要查询的选择列表。列表可以包括若干个列名或表达式，列名或表达式之间用逗号隔开，用来指明应该返回哪些数据。表达式可以是列名、函数或常数的列表。6.2SELECT语句结构

●INTOnew_table_name：指定用查询的结果创建成一个新表。●FROMsource_table：指定要查询的表或视图的名称。●WHEREsearch_condition：指明查询所要满足的条件。●GROUPBYexpressin1：根据指定列中的值对结果集进行分组，分组的条件是由expressin1指定。●HAVINGsearch_conditin2：对用FROMWHERE或GROUPBY子句创建的中间结果集进行行的筛选。它通常与GROUPBY子句一起使用。●ORDERBYexpression2[ASC|DESC]：对查询结果集中的行重新排序。ASC和DESC关键字分别用于指定按升序或降序排序。如果省略ASC或DESC，则系统默认为升序。6.3简单查询6.3.1使用SELECT子句SELECT子句的语法格式为:SELECT[ALL|DISTINCT][TOPn[PERCENT][WITHTIES]]<select_list>其中：●ALL|DISTINCT选项：指定查询结果集的所有行，其中，ALL指明结果集中包括重复行，DISTINCT是用于删除结果集中重复的行。ALL是该选项的默认设置；●TOPn[PERCENT]:指定只返回查询结果集中的前n行。如果加了PERCENT，则表示只返回查询结果集中的前n%行。6.3简单查询●WITHTIES:指定从基本结果集中返回附加的行。●selectlist—指明要查询的选择列表。列表可以包括若干个列名或表达式，列名或表达式之间用逗号隔开，用来指示应该返回哪些数据。如果使用星号*则表示返回FROM子句中指定的表或视图中的所的列。表达式可以是列名、函数或常数的列表。【例6-5】查询学生所有的基本信息。由于学生的基本信息都保存在表student中，所以在查询分析器执行下列语句。usestudent_msgoselect*fromstudentgo6.3简单查询运行结果如图所示，他将student表中的所有信息均显示出来。6.3简单查询【例6-6】查询学生的政治面貌情况由于只需要查看学生的政治面貌，而不需要性别、出生日期等其他信息，所以可以在查询分析器中执行下列语句：usestudent_msgoselect姓名,性别,政治面貌fromstudentgo6.3简单查询结果如下图所示6.3简单查询

【例6-7】查询前10%记录中的学生的学号、姓名、出生日期情况在查询分析器中执行下列语句：usestudent_msgoselecttop10percent学号，姓名,出生日期fromstudentgo分析：表中原有记录17条，其中的10%约有2条数据，所以使用top10percent语句可以显示出前2条记录。6.3简单查询

6.3.2使用INTO子句使用INTO子句可以创建一个新表，新表中所包含的内容就是查询的结果，通常使用这种方法可以创建一个临时表，以便在以后的开发过程中使用，但是查询的用户必须在该数据库中拥有CREATETABLE权限，而且INTO子句不能和COMPUTE子句一起使用。【例6-8】将学生的学号、姓名、性别、出生日期的查询结果作为一个新建的表student_info_1保存起来。在查询分析器中运行下列语句：USEstudent_msGOSELECT学号，姓名,性别，出生日期INTOstudent_info_1FROMstudentGO6.3简单查询

由于该查询建立了一个新表，这时，在企业管理器中stument_ms数据库中增加了一个student_info_1，可以打开并看到表中的字段和记录。6.3.3使用WHERE子句

WHERE子句是用于指明查询所需要满足的条件。一般情况下，在数据的查询过程中必须定义一个或多个条件限制查询选择的数据行。该条件是由WHERE子句中的逻辑表达式所确定，结果集将返回表达式为真的数据行。其中逻辑表达式主要由比较运算符、逻辑运算符构成。6.3简单查询【例6-9】在学生基本信息表student中查找政治面貌是“党员”的学生的姓名、性别。在查询分析器中运行下列命令语句：USEstudent_msGOSELECT姓名,性别FROMstudentWhere政治面貌='党员'GO运行结果如下图所示。6.3简单查询6.3简单查询【例6-10】在学生基本信息表中查询1985年以后出生的女生的基本信息在查询分析器中运行下列命令语句：USEstudent_msGOSELECT学号,姓名,出生日期FROMstudentWHERE性别='女'AND出生日期>1985-1-1G0运行结果如下图所示6.3简单查询6.3简单查询6.3.4使用GROUPBY子句

GROUPBY子句用于对表或视图中的数据按字段分组，语法格式为：GROUPBY[ALL|分组表达式[，…n][WITH{CUBE|ROLLUP}]]其中分组表达式通常包含字段名。指定ALL将显示所有组。使用GROUPBY子句后，SELECT子句中的列表中只能包含在GROUPBY中指出的列或在聚合函数中指定的列。WITH指定CUBE或ROLLUP操作符，CUBE或ROLLUP与聚合函数一起使用，在查询结果中增加附加记录。6.3简单查询【例6-11】查询每位同学的课程门数、总成绩、平均成绩。查询每位同学的课程成绩情况，实际上就是按照“学号”列进行分类统计，在GROUPBY子句中可以使用聚合函数COUNT（）、SUN（）、AVG（）分别统计课程的门数、总成绩、平均成绩。在查询分析器中运行下列命令语句：SELECT学号,COUNT(课程号)课程门数,SUM(成绩)AS总成绩,平均成绩=AVG(成绩)FROMscoreGROUPBY学号GO运行结果如图所示。6.3简单查询6.3简单查询说明：在上例中使用了列的别名的三种定义方式，分别为：

●列名表达式列别名●列名表达式AS列别名●列别名=列名表达式6.3.5HAVING子句

使用GROUPBY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据进一步的筛选，以限制SELECT语句返回的行数。HAVING子句应该处在GROUPBY子句之后，并且不能在HAVING子句中使用text、image和ntext数据类型。

6.3简单查询语法格式为：HAVING<search_condition>其中：search_condition为条件表达式【例6-12】查询参加“计算机基础”课程（课程号为“01005”）考试的学生人数。在查询分析器中运行下列命令语句：SELECT课程号,COUNT(课程号)人数FROMscoreGROUPBY课程号HAVING课程号='01005'Go运行结果下图所示。6.3简单查询含有HAVING子句查询结果

6.3简单查询6.3.6ORDERBY子句

在查询过程中，如果需要对查询结果集重新排序输出，则可以使用ORDERBY子句，其语法格式为：ORDERBYcolum_nmae[ASC|DESC][，column_name[ASC|DESC][,…]]其中，ASC和DESC关键字分别用于指定按升序或降序排序。如果省略ASC或DESC，则系统默认为升序。可以在ORDERBY子句中指定多个排序列，即嵌套排序，检索结果首先按第1列进行排序，对第1列值相同的那些数据行，再按照第2列排序……依此类推。注意在ORDERBY子句中不能使用ntext,text和image列。6.3简单查询【例6-13】查询所有男生的学号、姓名和年龄，并按照年龄从小到大的顺序进行排列。由于按照年龄从小到大的顺序排列，所以，应该按照出生日期的降序排序。在查询分析器中运行下列命令语句：SELECT学号,姓名,YEAR(GETDATE())-YEAR(出生日期)年龄FROMstudentWHERE性别='男'ORDERBY出生日期DESCgo运行结果如图所示6.3简单查询含有ORDERBY子句查询结果

6.4联接查询

在设计关系数据库管理系统时，为了保证数据的完整性，一致性，而且在建立数据表初期时各数据之间的关系不确定，因此，常把一个实体的所有信息存放在一个表中。而在实际查询过程中，为了得到满足要求的查询结果，需要从多个表中选取不同的数据，这时，就要求有联接查询操作，从而可以查询出存放在多个表中的不同实体的信息。联接操作给用户带来很大的灵活性，通过联接可以为不同实体创建新的数据表。联接的基本语法格式如下:FROM<left_table>[<join_type>]JOIN<right>ON<join_condition>6.4联接查询

其中，join_type(联接类型)有如下5种:1．INNER:内联接，这是默认类型。2．LEFT或LEFTOUTER左联接。当左表中的记录根据<联接条件>在右表中没有匹配的记录时，仍在结果集中产生一条记录，3．RIGHT或RIGHTOUTER右联接。4．FULL或FULLOUTER完整联接。5．CROSS交叉联接。6.4.1内联接内联接(INNERJOIN)是用比较运算符比较表中列值，返回符合联接条件的数据行，从而将两个表联接成一个新表。内联接通常有三种联接方式。6.4联接查询

1.等值联接

等值内联接在联接条件中使用等号（=）运算符比较被联接列的列值，因为联接列值是相等的列，联接的列要显示两次，所以会产生冗余行。【例6-14】运用等值联接方法，通过学号把student表和score表进行等值联接。在查询分析器中运行下列命令语句：SELECT*FROMstudentINNERJOINscoreONstudent.学号=score.学号Go6.4联接查询等值联接查询结果如图6.4联接查询2．不等值联接

在联接条件中使用除等于运算符以外的其他比较运算符（>、>=、<、<=、!>、!<、<>）来比较被联接的列的列值。【例6-15】将学生表中的学号与成绩表中的学号相比较,在两个表联接所产生的结果中选择非团员学生的成绩。在查询分析器中运行下列命令语句：SELECT姓名,政治面貌,成绩FROMstudentINNERJOINscoreONstudent.学号>score.学号WHERE政治面貌<>'团员'Go6.4联接查询不等值查询结果如下：6.4联接查询3.自然联接

自然联接是将要联接的列作相等比较的联接，由于该联接使用选择列表指定查询结果结合中所包含的列，并删除联接表中的重复列，从而消除了等值联接产生的冗余。【例6-16】查询学生的学号、课程名称和成绩SELECT学号,课程名称,成绩FROMcourseINNERJOINscoreONcourse.课程号=score.课程号ORDERBY学号Go6.4联接查询自然联接查询结果如图：6.4联接查询6.4.2外联接

外联接只限制一个表，而对另外一个表不加限制（所有的行都出现在结果集中）。外联接分为左外联接（LEFT）、右外联接（RIGHT）和全外联接（FULL）。1．左外联接左外联接对联接条件中左边的表不加限制。【例6-17】查询所有课程的成绩信息。在查询分析器中运行下列命令语句：SELECT学号,course.课程号,course.课程名称,成绩FROMcourseLEFTJOINscoreONcourse.课程号=score.课程号GO6.4联接查询

运行结果如图所示，由于在course表中，专业英语、多媒体技术和软件工程没有学生成绩，因此出现了null值。6.4联接查询

2．右外联接

右外联接对右边的表不加限制。【例6-18】查询已输入成绩的课程的成绩信息在查询分析器中运行下列命令语句：SELECT学号,course.课程号,course.课程名称,成绩FROMcourseRIGHTJOINscoreONcourse.课程号=score.课程号GO运行结果如图所示，从图中可以看出，由于是不对score表不加限制，因此，结果中出现“网页制作”这门课程的成绩的两个null值。6.4联接查询右外联接查询结果如图：6.4联接查询3．全外联接

全外联接对两个表都不加限制，两个表中的所有行都包含在结果集中。【例6-19】查询所有课程以及考试成绩的信息在查询分析器中运行下列命令语句：SELECT学号,course.课程号,course.课程名称,成绩FROMcourseFULLJOINscoreONcourse.课程号=score.课程号GO由于对两个表都没有限制，因此出现了四个null值。6.4联接查询

6.4.3交叉联接

交叉联接也叫非限制联接，它将两个表没有任何约束限制地组合在一起，也就是对两个表进行笛卡尔乘积。它不使用WHERE子句，它所产生的结果集包含了所联接的两个表中的所有行的组合，其数目为第一个表中符合联接条件的行数与第二个表中符合联接条件的行数的乘积。【例6-20】查询所有学生和课程信息在查询分析器中运行下列命令语句：USEstudent_msSELECT*FROMstudentCROSSJOINcourseGO可以通过查询结果窗口，计算查询结果中的行数。6.4联接查询6.4.4嵌套查询

嵌套查询是在查询中包含另一个查询的查询。在SQL语言中，一个完整的SELECT—FROM—WHERE语句被称之为查询块，将一个查询块嵌套在另一个查询块的WHERE子句中，这就是嵌套查询。【例6-21】在score表中，查询成绩大于平均成绩的成绩信息。在查询分析器中运行下列命令语句：SELECTAVG(成绩)AS'平均成绩'FROMscoreGOSELECT*FROMscoreWHERE成绩>(SELECTAVG(成绩)FROMscore)GO6.4联接查询嵌套查询结果如图：6.5使用其它关键字查询数据

在检索数据的过程中，需要使用WHERE子句限定查询条件，这个条件通常是一个逻辑表达式。在表达式中除了可以使用比较运算符=、<、>、<>等外，还可使用范围运算符BETWEEN,NOTBETWEEN,IN,LIKE,ISNULL以及逻辑运算符NOT(非)、OR(或)、AND(与)等来限定查询条件。1.使用BETWEEN关健字BETWEEN关键字用来查询在一个指定范围内的信息，与AND一起使用；NOTBETWEEN用于查询不在某一范围内的信息。6.5使用其它关键字查询数据【例6-22】从表score中查询成绩在75~90分之间的学生信息在查询分析器中运行下列命令语句：SELECTstudent.学号,姓名,性别,课程名称,成绩F