第四章 表中数据的查询.docx

第四章 表中数据的查询.docx

《第四章 表中数据的查询.docx》由会员分享，可在线阅读，更多相关《第四章 表中数据的查询.docx（21页珍藏版）》请在冰豆网上搜索。

第四章表中数据的查询

第四章表中数据的查询

第一讲：

使用SELECT子句选取行和列

一、SELECT语句的基本语法格式

SELECT语句的主要功能是从数据库中检索行，并允许从一个或多个表中选择一个或多个行和列。

基本语法如下：

SELECT<选择列表>---------------指定由查询返回的列

[INTO<新表>]----------------创建新表并将结果行从查询插入新表中

FROM<表来源>------------------指定从其中检索行的表

[WHERE<搜索条件>-----------指定用于限制返回行的搜索条件

[GROUPBY<分组表达式>]--------指定用来放置输出行的组，并在SELECT子句<选择列表>中包含聚合函数时计算每组的汇总值

[HAVING<组搜索表达式>]------指定组或聚合的搜索条件，通常与GROUPBY一起用

[ORDERBY<排序表达式>[ASC|DESC]]-----指定结果集的排序

二、使用SELECT子句选取列和行

1、从表选择全部列

语法：

SELECT*FROM表源

例：

从studentinfo数据库中选择Student表的全部列

USEstudentinfo

SELECT*FROMStudents

2、从表中选择部分列

语法：

SELECT列名1<,…,列名n>FROM表源

注意：

1.如果创建表时在表名和某列名中使用了空格，编写SELECT语句时就应当用方括号将表明或该列名括起来，否则就会出现错误信息。

2.如果在FROM子句中指定了多个表，而这些表中又有同名的列，使用这些列时就应该在其列名前加上表明，以指明该列属于哪个表。

例1从StudentInfo数据库中检索学生数据，要求从Students表中选取StudentNo、StudentName、StudentSex和BirthDate四个列作为查询的输出列，试写出相应的语句。

【方法要点】：

若要从一个表中检索部分列的数据，在SELECT子句中给出这些列名的列表即可。

USEStudentInfo

SELECTStudentNo,StudentName,StudentSex,BirthDate

FROMStudents

例2从StudentInfo数据库中检索学生数据，要求从Students表中选取StudentName，从Score表中选取Scores

3、设置列别名

显示选择查询的结果集时，在第一行中显示的时各个列的名称。

为了便于阅读，也可以制定更容易理解的列名来取代原来的列名。

语法如下：

<原列名>[AS]<列别名>

‘列别名’=<原列名>

<原列名><列别名>

例：

从StudentInfo数据库中检索学生数据，要求从Students表中选取StudentNo、StudentName、StudentSex和BirthDate四个列作为查询的输出列，并为这些列制定中文别名。

USEStudentInfo

SELECTStudentNoAS学号，StudentNameAS姓名,StudentSexAS性别,BirthDateAS生日

FORMStudents

4、使用计算列

计算列是表中的虚拟列，其值并未存储在表中，而是基于定义改列的表达式计算得到。

该表达式可以包含列名、常量、运算符和SQLServer的内置函数，表达式的值在执行语句时得到。

语法：

<表达式>[[AS]<别名>]

其中AS子句用于制定计算列的别名，如果不为计算列指定别名，则显示查询结果时会在改列标题行显示“（无列名）”字样。

例：

从StudentInfo数据库中检索学哼数据，要求用CONVER函数将datetime类型列BirthDate转换为yyyy-mm-dd格式，以避免显示这些列中包含的时间信息。

【方法要点】：

设置计算列时，在选择列表中给出所用表达式，并通过AS子句制定该列的别名。

USEStudentInfo

SELECTStudentNoAS学号,StudentNameAS姓名,StudentSexAS性别，

CONVERT（CHAR（10）,BirthDate，21）AS出生日期

FROMStudents

三、使用选择谓词限制行数

1、使用谓词ALL返回全部行

如果在SELECT子句中使用ALL关键字，则选择查询将返回符合条件的全部行，而且允许在结果集内包含重复行。

ALL是默认设置。

例：

在StudentInfo数据库中，检索Scores表中StudentNo列的全部数据。

【方法要点】：

若要从表检索所有行，在SELECT列表使用ALL关键字即可，该关键字可以省略。

USEStudentInfo

SELECTALLStudentNoFORMScores

2、使用谓词DISTINCT过滤重复行

DISTINCT关键字指定在结果集内只能显示唯一行。

在选择列表前面加上DISTINCT关键字，可以出去结果集内的重复行。

当使用DISTINCT关键字时，空值被认为相等。

例：

本案例从表中检索唯一行的数据。

在StudentInfo数据库中，检索Students表中StudentSex列的数据，要求出去结果集内的重复行。

【方法要点】：

若要从表中检索唯一行的数据在SELECT列表中使用DISTINCT关键字即可。

USEStudentInfo

SELECTDISTINCTStudentSexFROMStudents

3、使用谓词Top仅显示前面若干行

如果在选择列表前面使用Topn[PERCENT]，则指定只从查询结果集中输出前N行。

n是介于0和4292967295之间的整数。

如果还制定了PERCENT，则只从结果集中输出前面n％行，此时n介于0到100之间的整数。

例：

在StudentInfo数据库中，从Students表中检索前3个学生的数据。

【方法要点】若要从表中检索前面的若干行记录，在SELECT选择列表中使用TOPn即可。

USEStudentInfo

SELECTTOP3StudenNoAS学号,StudentNameAS姓名,StudentSexAS性别,

CONVERT（CHAR（10）,BirthDate,21）AS出生日期

FORMStudents

第二讲：

使用FROM子句指定数据源

一、使用内部连接组合两个表中的行

内部连接是一种最常用的连接类型。

使用内部连接时，如果两个来源表的相关列满足连接条件，则从这个表中提取数据并组合成新的行。

内部连接可以通过在FROM子句中使用INNERJOIN运算来实现。

语法：

FROM<表1>[INNER]JOIN<表2>ON<条件表达式>

1、在StudentInfo数据库中，从Students表和Scores表中检索学生的课程成绩，要求一辆个表的StudentInfo列作为连接列。

【方法要点】当两个来源表的相关列满足连接条件时，可以使用INNERJOIN运算符连接它们并通过ON关键字指定列之间的连接条件。

USEStudentInfo

SELECTScores.StudentNo,StudentName,CourseNo,Score

FROMScoresINNERJOINStudentsONScores.StudentNo=Students.StudentNo

在FROM子句中，如果表名比较长，同样可以用AS子句来指定别名：

USEStudentInfo

SELECTsc.StudentNo,StudentName,CourseNo,Score

FROMScoresASscINNERJOINStudentsONsc.StudentNo=Students.StudentNo

如果打算从两个以上表中检索符合连接条件的行，则需要使用嵌套形式的INNERJOIN运算。

语法：

SELECT<选择列表>

FROM<表1>[INNER]JOIN<表2>ON<条件表达式>

[INNER]JOIN<表3>ON<条件表达式>

2、在StudentInfo数据库中，从StudentInfo数据库中，从Students表、Coures表和Scores表中检索学生的课程成绩。

【方法要点】若要从多个表中检索数据，可以使用潜逃INNERJOIN运算，可以对表指定别名。

USEStudentInfo

SELECTsc.StudentNo,StudentName,CourseName,Score

FROMScoresASscINNERJOINStudentsASstONsc.StudentNo=st.StudentNo

INNERJOINCoursesAScoONco.CourseNo=sc.CourseNo

二、使用左边外部连接组合两个表中的行

语法：

FROM<表1>LEFT[OUTER]JOIN<表2>ON<条件表达式>

功能：

使用左边外部连接时，除了返回内部连接的行以外，还在查询结果中显示除主表中不符合连接条件的行，并在从表中相应列中填上NULL值。

例：

在StudentInfo数据库中，从Students表和Scores表中检索数据，以查看有哪些学生以及这学生的学习成绩如何。

【方法要点】：

在使用左边外部连接时，首先要确定主表和从表。

在本案例中，因Students表中的个别学生可能没有参加考试，所以确定Students表为主表。

USEStudentInfo

SELECTst.StudentNo,StudentName,CourseNo,Score

FROMStudentsASstLEFTOUTERJOINScoresONst.StudentNo=Scores.StudentNo

三、使用右边外部连接组合两个表中的行

语法：

FROM<表1>RIGHT[OUTER]JOIN<表2>ON<条件表达式>

功能：

使用右边外部链接时，除了返回内部连接的行以外，还在查询结果中显示出从表中不符合条件的行，并在表中相应的列上填上NULL值。

例如：

【方法要点】：

因为时使用右边外部连接组合，所以主表和从表的选择刚好相反。

USEStudentInfo

SELECTst.StudentNo,StudentName,CourseNo,Score

FORMScoresRIGHTOUTERJOINStudentsASstONScores.StudentNo=st.StudentNo

四、使用完全外部连接组合两个表中的行

语法：

FROM<表1>FULL[OUTER]JOIN<表2>ON<条件表达式>

功能：

使用完全外部连接时，除了返回内部连接的行以外，还在查询结果中显示出两个表中不符合连接条件的全部行，并在左表和右表的相应列中填上NULL值。

五、使用交叉连接组合两个表

语法：

FROM<表1>CROSSJOIN<表2>

功能：

使用交叉连接时，将返回两个来源表交叉匹配的结果。

没有WHERE子句的交叉连接将产生连接涉及的表的笛卡儿积。

第一个表的行数乘以第二个表的行数等于笛卡儿积结果的大小，此时查询结果中包含行数等于两个来源表中行数的乘积。

例：

在StudentInfo数据库中，使用交叉连接组合Students表和Scores表。

USEStudentInfo

SELECTst.StudentNo,StudentName,CourseNo,Score

FORMStudentsASstCROSSJOINSc

第三讲：

使用WHERE子句指定返回行的条件

一、WHERE子句的语法格式

在SELECT语句中，可以使用WHERE子句来指定返回行的条件，格式如下：

WHERE<查询条件>

查询条件是用运算符连接列名、常量、变量、函数等而得到的表达式，其取值范围为TRUE、FALSE或UNKNOWN,用于指定返回行的条件。

二、使用单一查询条件

单一查询条件可以使用下面的布尔表达式来表示：

<表达式><比较运算符><表达式>

【表达式】：

可以是一个列名或子查询，也可以是用运算符连接列名、常量、函数以及变量等所得到的表达式。

【比较运算符】：

用于比较两个表达式之间的值，并返回TRUE、FALSE或UNKNOWN。

（当有一个表达式取NULL值时，查询条件的结果就为UNKNOWN）。

比较运算符取值：

=><>=<=<>!

=!

>

例：

在StudentInfo数据库中，从Students表中检索1班的学生记录。

【方法要点】：

若要使用单一条件限制返回的行，在WHERE子句中给出一个布尔表达式即可。

USEStudentInfo

SELECT*FROMStudents

WHEREClass=‘1’

三、使用AND和OR运算符

在WHERE子句中可以包含多个查询条件，使用逻辑运算符AND或OR将多个查询条件组合起来，能够起到缩小或扩大搜索范围的作用，从而完成比较复杂的数据检索。

1.使用AND缩小搜索范围

AND预算府用于脸颊两个布尔型表达式，当两个表达式都取TRUE时返回TRUE。

当语句中有多个逻辑表达式时，AND运算符将首先计算。

语法：

<布尔表达式1>AND<布尔表达式2>

例：

在StudentInfo数据库中，从Students表中检索1班的男生记录。

【方法要点】：

如果检索数据时要求同事满足两个条件，则使用AND运算符组合两个布尔表达式。

USEStudentInfo

SELECT*FROMStudents

WHEREClass=‘1’ANDStudentSex=‘男’

2.使用OR扩大搜索范围

语法格式：

<布尔表达式1>OR<布尔表达式2>

利用OR运算符时，当两个表达式之间任何一个表达式为TRUE时，运行结果就为TRUE，否则都为FALSE。

例：

在StudentInfo数据库中，从Students表中检索专业为电子商务或者计算机的所有学生。

【方法要点】：

如果检索数据时只需要满足两个条件中的一个即可，则使用OR运算符来组合两个表达式。

USEStudentInfo

SELECT*FROMStudents

WHERESperiality=‘电子商务’ORSperiality=‘计算机’

四、使用BETWEEN和NOTBETWEEN运算符

BETWEEN是一个逻辑运算符，用于测试一个值是否位于指定的范围之内，BETWEEN运算符格式如下：

<测试表达式>[NOT]BETWEEN<起始值>AND<终止值>

其中<测试表达式>必须与<起始值>和<终止值>的数据类型相同，并由<起始值>和<终止值>指定了一个范围，BETWEEN用于搜索范围内的数据。

若测试表达式的值介于<起始值>和<终止值>之间，则运算结果为TRUE。

NOTBETWEEN正好与BETWEEN相反，用于搜索不在指定范围之内的数据。

使用NOTBETWEEN时，若测试表达式的值不在<起始值>和<终止值>范围之内，则运算结果为TRUE。

例：

在StudentInfo数据库中，检索《电子商务概论》在85到95之间的学生记录。

【方法要点】：

因为是在某个范围之内，所以用BETWEEN。

USEStudentInfo

SELECT*FROMScores

WHERECourseNo=‘1’ANDScoresBETWEEN85AND95

五、使用IN和NOTIN运算符

IN是一个逻辑运算符，用于测试给定的值是否在一个子查询或项目列表中。

语法格式：

<测试表达式>[NOT]IN（<子查询>|<表达式列表>）

在上述语法格式中，<子查询>应当能够返回一个具有单个列的结果集，该列必须与测试表达式的数据类型相同；<表达式列表>由若干个表达式组成，用逗号加以分隔，其中的所有表达式必须与测试表达式的数据类型相同。

如果测试表达式的值等于子查询返回的某个值，或等于表达式列表中某个表达式的值，则运算结果为TRUE，否则运算结果为FALSE。

例：

从Students表中检索姓刘、姓张或姓王的学生记录。

【方法要点】：

由于“刘”、“张”、“王”只是StudentName列值的一部分，需要用SUBSTRING函数。

USEStudentInfo

SELECT*FROMStudents

WHERESUBSTRING（StudentName,1,1）IN（‘刘’,’张’,’王’）

六、使用LIKE和通配符过滤行

【概念】LIKE运算符用于测试一个字符串是否与给定的模式相匹配，所谓模式就是一种特殊的字符串，其特殊之处在于它不仅可以包含普通字段，还可以包含通配符。

通配符用于表示任意字符串。

【实际运用范围】如果需要从数据库中检索一批数据，但又不能给出精确的查询条件，在这种情况下，就可以使用LIKE运算符和通配符来实现模糊查询。

【变通】：

在LIKE运算符前面可以使用NOT运算符，表示对运算的结果取一次反。

【语法】：

<字符串表达式>[NOT]LIKE<模式>

语法中的<模式>是一个字符串，其中可以包含普通字符和通配符两种成分。

通配符种类如下：

_（下划线）――――任意一个字符。

％（百分号）――――由0个活多个字符组成的任意字符串。

[]――――位于指定范围内的任意一个字符。

（[a-c]或[abc]）

[^]――――不再指定范围内的任意一个字符。

（[^a-c]或[^abc]）

例1从Students表中查找姓名第二个字为“四”的学生。

【方法要点】：

再模式字符串中用下划线表示任一字符，本案例中模式字符应该为’_四_’

USEStudentInfo

SELECT*FROMStudents

WHEREStudentsNameLIKE‘_四_’

例2从Courses表中查找名称中带有“设计”二字的课程。

【方法要点】：

在模式字符串中用百分号表示任一字符串，在本案例中模式字符串应该是’%设计%’

USEStudentInfo

SELECT*FROMCourses

WHERECourseNameLIKE‘%设计%’

例3在StudentInfo数据库中从表Students表中查找姓张和姓王的学生记录.

【方法要点】：

在模式字符串中用方括号表示一个字符列表，在本案例中模式字符串应该为’[张王]%’

USEStudentInfo

SELECT*FROMStudents

WHEREStudentNameLIKE‘[张王]%’

例4在StudentInfo数据库中，从Students表中检索学号末尾为3－5范围内的学生记录。

【方法要点】：

在模式字符串中也可以用方括号表示一个字符范围，在本案例中模式字符串应该为‘%[3-6]’

USEStudentInfo

SELECT*FROMStudents

WHEREStudentNoLIKE‘%[3-6]’

七、利用ESCAPE子句检索包含通配符的行

前面所介绍的通配符可能会作为模糊查询中的模式字符串的一个字符，所以需要作出是否是通配符的判断。

此时就需要用到ESCAPE子句。

格式如下：

ESCAPE有效字符

其中模式字符中需要包含这个有效字符，而有效字符并不作为查询的依据，只是作为一个分界字符。

在这个有效字符后面的字符即为普通字符，而不是通配符。

例：

在StudentInfo数据库中对Students表格进行查询，查询出生日以“1985-”开头的所有人的信息。

【方法要点】：

因为-是下划线，是属于通配符的一种，所以需要用ESCAPE将其视为普通字符。

USEStudentsInfo

SELECT*FORMStudents

WHERECONVERT（CHAR（10）,BirthDate,21）like'1985s-%'ESCAPE's'

第四讲：

使用ODRERBY子句对行进行排序

一、ORDERBY子句的语法表达式

ORDERBY子句按查询结果中的一列或多列对查询结果进行排序，用于指定结果集的排序，其语法格式为：

ORDERBY{<排序表达式>[ASC|DESC]}[….n]

在上述语法格式中<排序表达式>用于指定排序的依据，既可以是列名也可以是列别名，也可以是表示列名或别名在选择列中位置的非负整数。

如果ORDERBY子句以多个列作为排序的依据，这些列在改子句中出现的顺序决定了结果集如何排序，此时首先按照前面的列值进行排序，如果若干条记录的改列值相同，则按照后面的列值进行排序。

二、注意：

Ø作为排序的列可以不出现在SELECT子句指定的选择列表中。

Ø计算列也可以作为排序的依据。

Øntext、text或image类型的列不能作为排序的依据。

ASC：

表示按照递增的顺序进行排序。

DESC：

表示按照递减的顺序进行排序。

两种排序方法中ASC是默认值。

NULL将被处理为最小值。

三、例：

从StudentInfo中检索数据，要求在查询结果中列出“ASP动态网页设计”课程的成绩，并按照成绩从高到低的顺序对查询结果进行排序，若两个学生的成绩相同，则按照姓名的音序从低到高进行排序。

【方法要点】：

为了便于查阅，需要在查询结果中给出学生姓名和课程名，故选择Students、Scores和Courses三个表作为查询的数据来源，并通过内部连接方式进行关联。

再次，在这个选择查询中还需要使用WHERE子句来限制返回的行和ORDERBY子句对其进行排序。

USEStudentInfo

SELECTsc.StudentNoAS学号,StudentNameAS姓名,CourseNameAS课程名,ScoreAS成绩

FROMScoreASscINNERJOINStudentsASstONsc.StudentNo=st.StudentNO

INNERJOINCoursesAScoONsc.CourseNo=co.CourseNo

WHEREco.CourseName=’ASP动态网站设计’

ORDERBYsc.ScoreDESC,st.StudentsASC

第五讲：

使用聚合函数返回汇总值

一、概述：

聚合函数用于对一组值进行计算并返回一个单值。

除COUNT函数外，聚合函数在计算过程中将忽略NULL值。

它的作用是在查询结果集内产生累加和、平均值、行数目、最大值以及最小值等汇总性的数据。

作用范围：

可以是一个表中的全部行，也可以是由WHERE子句指定的该表的一个子集。

同事还可以作用于表中的一组或多组行，并针对每组行产生一个单值。

二、使用SUM函数计算列的累加和

SUM函数用于统计数值型列的总和，它只能用于数值型列，而且NULL值将被忽略。

语法：

SUM（[ALL|DISTINCT]<表达式>）

ALL和DISTINCT关键字用于指定求和范围，前者指示SUM函数对所选列的所有值求和，后者指示SUM函数仅对唯一值求和。

A