07条件查询.docx
《07条件查询.docx》由会员分享,可在线阅读,更多相关《07条件查询.docx(16页珍藏版)》请在冰豆网上搜索。
07条件查询
第章
条件查询
*1条件表达式
*1使用WHERE关键字设置查询条件
*1排序条件查询的结果
*1TOP与ROWNUM的使用
在日常工作中,数据库的查询并非只是简单地查询所有记录,多数情况下是指定搜索条件查询需要的数据,例如,查找计算机系的所有学生;查找4学分的所有课程等。
在查询语句中,指定条件需要使用WHERE子句。
本章将介绍编写条件表达式的方法和使用WHERE子句查询所需数据的一些简单方法。
7.1条件表达式
条件表达式是使用条件运算符将常量、字段值、函数以及字段名连接起来的表达式。
条件表达式的值只有两种,分别是真(True)和假(False)。
因为只要用到条件查询就要编写条件表达式,所以了解条件表达式的组成,掌握其编写方法非常重要。
本节将介绍条件表达式的相关内容。
7.1.1指针与字段变量的概念
为了后面能很好地说明WHERE子句中条件表达式的工作原理,首先介绍两个概念——指针与字段变量。
指针是人们虚拟出来的一个箭头(或者标记),实际上它并不存在。
指针可以指向数据表中的任何一条记录,当指针指向某条记录时该记录就被称为当前记录。
例如,指针指向了第3条记录时,第3条记录就会成为当前记录,如图7.1所示,当前记录为第3条记录(学号为0002的记录)。
图7.1指针示意图
了解了指针和当前记录后,下面介绍字段变量。
在表达式中出现的字段名其实就是字段变量,称其为字段变量的原因是字段名的值会随着指针的移动而变化。
例如,在图7.1中,姓名字段的当前值为“李四”,而如果指针移动到了第4条记录上,姓名字段的当前值就会变为“马六”,所以表达式中将字段名作为变量来使用。
7.1.2条件表达式
如果要使用WHERE子句,则必须学会编写条件表达式。
条件表达式其实是关系表达式、逻辑(布尔)表达式和几个SQL特殊条件表达式的统称。
条件表达式只有真(True)和假(False)两种值。
在学习编写条件表达式之前,首先应当了解条件运算符。
下面的表7.1列出了SQL语言中使用的条件运算符。
表7.1条件运算符
运算符说明举例
关系运算符
=
等于
姓名='王五',学分=4,出生日期='05/29/1973'
<
小于
考试成绩<90
续表
运算符说明举例
关系运算符
<=
小于等于
出生日期<='01/01/1974'
>
大于
平时成绩>90
>=
大于等于
平时成绩>=80
<>或!
=
不等于
所属院系<>'中文系'
逻辑(布尔)运算符
NOT
非
NOT考试成绩<90
AND
与(而且)
考试成绩>80AND平时成绩>=90
OR
或
平时成绩=100OR考试成绩>95
SQL特殊条件运算符
IN
在某个集合中
学分IN(2,3,4)
NOTIN
不在某个集合中
所属院系NOTIN('中文系','外语系')
BETWEEN
在某个范围内
学分BETWEEN2AND3
NOTBETWEEN
不在某个范围内
学号NOTBETWEEN'0001'AND'0005'
LIKE
与某种模式匹配
姓名LIKE'%三%'
NOTLIKE
不与某种模式匹配
课名NOTLIKE'%基础%'
ISNULL
是NULL值
联系方式2ISNULL
ISNOTNULL
不是NULL值
联系方式2ISNOTNULL
1.关系运算符
使用关系运算符编写条件表达式时,需要注意字段的类型。
如果是字符类型的字段,则必须与字符型常量相比较,例如:
姓名='王五'
因为姓名是字符型字段,所以一定要注意将“王五”放进单引号中,将其变为字符串。
该表达式在指针指向Student表的第2条记录时为真,其他情况下均为假。
因为只有在指针指向第2条记录时,字段变量“姓名”的值才会为'王五',此时表达式便成为:
'王五'='王五'
因此,表达式的结果为真。
如果是数值类型的字段,则必须与数值型常量比较,例如:
学分=4
在此,绝对不可以将数值4放进单引号内,因为学分是数值型常量。
使用关系运算符编写条件表达式时,最需要注意的是日期型字段。
有些DBMS中支持日期型常量,
例如Access。
所以在Access中编写1974年1月1日之前出生的条件表达式为:
出生日期<#01/01/1974#
而有些DBMS中,没有日期型常量的概念,例如,SQLServer和Oracle数据库系统,但是这类数
据库管理系统能够识别日期格式的字符串。
例如,在SQLServer中编写1974年1月1日之前出生的条
件表达式为:
出生日期<’01/01/1974’
在条件表达式中如果使用了日期型字段,则应当查看具体DBMS对日期型字段如何处理的说明。
2.逻辑运算符逻辑运算符在条件表达式中也是举足轻重的,多条件复合查询、多表连接等都需要用到逻辑运算
符。
3个逻辑运算符中,NOT的优先级最高,其次是AND,最后是OR运算符。
如果表达式中,既有
逻辑运算符又有关系运算符,则所有关系运算符的优先级都比逻辑运算符的高。
(1)NOT运算符
NOT运算符用于求反,其运算规则如下所示。
NOTTrue=FalseNOTFalse=True例如,想要查询非计算机系的所有学生,这时条件表达式可以写为如下形式。
NOT所属院系='计算机系'
(2)AND运算符
条件表达式中的AND表示“与”,或者可以说是表示“而且”。
其运算规则如下所示。
TrueANDTrue=TrueTrueANDFalse=FalseFalseANDTrue=FalseFalseANDFalse=False
从上面可以看出,使用AND运算符的表达式只有在两边都是真时,结果才会为真。
AND运算符可以表示“而且”,例如,想要查询平时成绩大于等于90分,而且考试成绩大于等于80分的记录,条件表达式可以写为如下形式。
平时成绩>=90AND考试成绩>=80
(3)OR运算符
条件表达式中的OR运算符表示“或”,其运算规则如下所示。
TrueORTrue=TrueTrueORFalse=TrueFalseORTrue=TrueFalseANDFalse=False
从上面可以看出,使用OR运算符的表达式,只要一边为真,则结果就会为真。
OR运算符表示“或者”,例如,想要查询来源地是北京市或者所属院系为物理系的学生,条件表达式可以写为如下形式。
来源地='北京市'OR所属院系='物理系'
上面简单介绍了NOT、AND和OR3个逻辑运算符,关于逻辑运算符的详细用法请读者查看本书第8章的内容。
3.SQL特殊条件运算符
关于特殊条件运算符的详细内容请查看本书后面的内容。
7.2使用WHERE关键字设置查询条件
本节将介绍WHERE子句的用法,并通过几个实例,带领读者学习使用WHERE子句设定查询条件,查询数值数据、字符数据、日期数据和空值等的方法。
7.2.1WHERE子句用法
WHERE子句用来设置搜索条件,例如,想要从数据表中查找来自内蒙古的所有学生,则可以编写如下带有WHERE子句的SELECT语句。
SELECT*FROMstudent
WHERE来源地='内蒙古自治区'
该语句运行结果如图7.2所示。
图7.2来源地为内蒙古的所有学生
从图中可以看出,查询结果集中只有来源地是内蒙古自治区的学生,其他非内蒙古籍的学生全部被筛选掉了,这与WHERE子句的执行原理有关系。
下面通过刚才的例子,说明WHERE子句的执行原理。
为了方便参考,表7.2列出了Student表的部分内容。
表7.2Student表部分内容
学号
姓名
性别
出生日期
来源地
……
所属院系
0001
张三
男
1973-5-29
广东省
……
中文系
0003
王五
女
1975-9-1
辽宁省
……
物理系
0002
李四
女
1980-1-8
浙江省
……
外语系
0007
马六
男
1975-7-12
浙江省
……
外语系
0004
周七
女
1977-9-21
北京市
……
计算机系
0005
刘八
女
1979-8-30
海南省
……
中文系
0008
杨九
男
1980-2-17
重庆市
……
计算机系
0009
吴一
男
1976-11-1
内蒙古自治区
……
外语系
0006
赵二
女
1978-2-2
江苏省
……
中文系
0010
徐零
女
1981-1-1
内蒙古自治区
……
计算机系
本例中,WHERE子句按照如下步骤执行。
(1)将指针指向Student表的第1条记录,此时,字段变量“来源地”的值为“广东省”,此时
条件表达式变为:
'广东省'='内蒙古自治区'
因为该条件表达式的值为False,所以这条记录被筛选掉,没有进入查询结果集中。
(2)指针向下移动指向第2条记录,与上面的原因相同,这条记录也被过滤掉。
(3)指针不断向下移动,将条件表达式的值为False的记录全部筛选掉。
(4)当指针移到第8条记录时,字段变量“来源地”的值为“内蒙古自治区”,此时条件表达式变为:
'内蒙古自治区'='内蒙古自治区'
因为条件表达式的值为True,所以这条记录没有被筛选掉,成为进入查询结果集的第1条记录。
(5)指针继续向下移动,将第9条记录筛选掉,又将第10条记录添加到查询结果集中。
(6)指针再次向下移动时,遇到了数据表结束标记,WHERE子句结束执行。
综上所述,WHERE子句的工作原理为:
从表中的第1条记录开始向下搜索直到遇见结束标记为
止。
在此过程中,将条件表达式的值为False的当前记录筛选掉,而将条件表达式的值为True的当前
记录添加到查询结果集中。
下面是带有WHERE子句的SELECT语句的语法格式。
SELECT[DISTINCT|ALL]select_list
FROMtable_source
WHERE条件表达式
其中,WHERE后的“条件表达式”就是前面7.1节介绍的条件表达式。
7.2.2查询数值数据
本小节将通过几个例题说明使用WHERE子句查询数值数据的方法。
【例7.1】从Course表中,查询所有3学分的课程信息。
SELECT*FROMcourse
WHERE学分=3
运行结果如图7.3所示。
从图中看出,结果集中有3条记录,这3条记录的学分都是3,满足WHERE子句中的条件。
而其他不是3学分的课程信息都被筛选掉了。
说明:
因为“学分”字段是数值型字段,因此必须与数值常量比较,所以表达式学分=3,不能写为学分='3',或者其他形式。
【例7.2】从Course表中,查询所有学分不小于3的课程的课名和课号。
SELECT课名,课号
FROMcourse
WHERE学分>=3
运行结果如图7.4所示。
从图中看出,结果集中的字段顺序(课名,课号)是根据SELECT子句后的字段列表顺序产生的,而并不是只能按照源表的字段顺序(课号,课名,……)排列。
结果集中的5条记录都满足了条件:
学分不小于3。
其他不满足条件的记录都被筛选掉了。
图7.3例7.1查询结果图7.4例7.2查询结果
【例7.3】从Score表中,查询总成绩大于等于90的学生学号和这门课的课号。
计算总成绩的公式为:
总成绩=考试成绩*0.7+平时成绩*0.3。
SELECT学号,课号,考试成绩*0.7+平时成绩*0.3AS总成绩
FROMScore
WHERE考试成绩*0.7+平时成绩*0.3>=90
运行结果如图7.5所示。
图7.5例7.3查询结果
注意:
上面WHERE子句中的条件表达式不可以写为如下形式。
总成绩>=90
因为WHERE子句在SELECT子句之前执行,所以在WHERE子句执行时并没有执行给计算字段
(考试成绩*0.7+平时成绩*0.3)取别名的操作。
7.2.3查询字符型数据
前面介绍了如何查询数值型数据的方法,下面仍旧通过几个例题介绍怎样查询字符型数据的方法。
【例7.4】从Student表中,查询名叫“张三”的学生。
SELECT*
FROMstudent
WHERE姓名='张三'
运行结果如图7.6所示。
图7.6例7.4查询结果
说明:
因为“姓名”字段是字符型字段,因此必须与字符常量比较,所以必须用单引号(')括住“张三”。
【例7.5】从Student表中,查询非计算机系的所有学生。
SELECT*
FROMstudent
WHERE所属院系<>'计算机系'
运行结果如图7.7所示。
图7.7例7.5查询结果
上面WHERE子句的条件表达式中使用了不等于(<>)符号。
有些DBMS中,不等于也可以用一个感叹号加一个等于号(!
=)表示。
【例7.6】从Course表中,查询课号大于“003”的课程信息。
SELECT*FROMcourse
WHERE课号>'003'
运行结果如图7.8所示。
图7.8例7.6查询结果
字符串比较大小,其实是在比较每个字符的ASCII码值,ASCII码大的字符为大。
人们经常使用的字符里数字字符“0”的ASCII码是48,“1”的ASCII码是49等,依此类推向后递增;大写英文字母“A”的ASCII码是65,“B”的ASCII码是66等,依此类推向后递增;小写英文字母“a”的ASCII码是97,“b”的ASCII码是98等,依此类推向后递增。
因此,每个排列的后面的字符都比前面的要大。
汉字比较大小时比较的是拼音,例如,“张”比“王”大,因为“z”大于“w”。
【例7.7】从Student表中,查询姓名按拼音排在“马六”后的所有学生的姓名、来源地和所属院系。
SELECT姓名,来源地,所属院系
FROMstudent
WHERE姓名>'马六'
运行结果如图7.9所示。
图7.9例7.7查询结果
7.2.4查询日期数据
使用WHERE子句也能查询日期型数据。
但需要注意的是:
在不同的DBMS中编写查询日期型数据的条件表达式也不同。
【例7.8】从Student表中,查询1977年1月1日之后出生的学生姓名、联系方式和所属院系。
(1)如果运行环境为SQLServer,则SELECT语句编写如下。
SELECT姓名,联系方式1,联系方式2,所属院系
FROMstudent
WHERE出生日期>'01/01/1977'
运行结果如图7.10所示。
图7.10例7.8查询结果
(2)如果运行环境为Access,则SELECT语句编写如下。
SELECT姓名,联系方式1,联系方式2,所属院系
FROMstudent
WHERE出生日期>#01/01/1977#
说明:
在Access中,日期型数据必须被包含在井字符(##)中。
(3)如果运行环境为Oracle,则SELECT语句编写如下。
SELECT姓名,联系方式1,联系方式2,所属院系
FROMstudent
WHERE出生日期>'01-JAN-1977'
说明:
在Oracle中,日期型数据必须被包含在单引号('')中。
而且,Oracle中日期的默认格式为
DD-MON-YY,其中DD代表日,MON代表月,并且必须是英文月份名的简写,YY代表用两位数字表示的年份,在此建议读者使用四位数字的年份。
在此还需要提醒读者一个问题,有些DBMS的日期型数据中包含时间,例如Oracle和SQLServer,因此在使用等值(=)查询日期时应当注意。
例如,下面的SELECT语句只能查询1980年1月8日0点0分0秒出生的人。
SELECT*
FROMstudent
WHERE出生日期='01/08/1980'
如果想查询1980年1月8日内出生的所有人,则需要使用其他方法。
下面列出一种比较通用的方法。
SELECT*
FROMstudent
WHERE出生日期>='01/08/1980'AND出生日期<'01/09/1980'
在SELECT语句中,AND运算符的详细使用方法将在本书后面的内容中介绍。
7.2.5按范围查询数据(BETWEEN)
有时需要查询某个范围内的数据,此时可以在WHERE子句中使用BETWEEN运算符,该运算符需要两个值,即范围的开始值和结束值。
【例7.9】从Score表中,查询考试成绩在70~80分之间的所有学生的学号和这门课程的课号和
考试成绩。
SELECT学号,课号,考试成绩
FROMscore
WHERE考试成绩BETWEEN70AND80
运行结果如图7.11所示。
图7.11例7.9查询结果
说明:
BETWEEN运算符包含开始值和结束值。
【例7.10】从Student表中,查询1977年1月1日~1980年1月1日之间出生的学生姓名、出生
日期和所属院系。
假设执行SQL的环境为SQLServer。
SELECT姓名,出生日期,所属院系
FROMstudent
WHERE出生日期BETWEEN'01/01/1978'AND'01/01/1980'
运行结果如图7.12所示。
图7.12例7.10查询结果
7.2.6查询空值
数据库操作中,有时需要查询表中的空值或者非空值,此时可以使用ISNULL(ISNOTNULL)运算符。
【例7.11】从Student表中,查询“联系方式2”字段为空的所有学生的信息。
SELECT*
FROMstudent
WHERE联系方式2ISNULL
运行结果如图7.13所示。
图7.13例7.11查询结果
注意:
查询空值不能写为(字段名=NULL)。
【例7.12】从Student表中,查询“联系方式2”字段不为空的学生姓名、所有联系方式和所属院系。
SELECT姓名,联系方式1,联系方式2,所属院系
FROMstudent
WHERE联系方式2ISNOTNULL
运行结果如图7.14所示。
图7.14例7.12查询结果
7.3排序条件查询的结果
在第6章中介绍了排序查询结果的方法,其实排序带有WHERE子句的查询结果与其大同小异,只是应当牢记一点——ORDERBY子句必须放在WHERE子句的后面。
【例7.13】从Student表中,查询“联系方式2”字段不为空的学生学号、姓名、所有联系方式和所属院系,并且按学号升序进行排序。
SELECT学号,姓名,联系方式1,联系方式2,所属院系
FROMstudent
WHERE联系方式2ISNOTNULL
ORDERBY学号
运行结果如图7.15所示。
图7.15例7.13查询结果
注意:
如果SELECT语句中有ORDERBY子句,则必须将其放在WHERE子句之后。
7.4TOP与ROWNUM的使用
在数据库操作中,有时需要限制查询返回的记录个数。
SQLServer中的TOP关键字和Oracle中的
ROWNUM可以完成这个任务。
1.SQLServer中的TOP
SQLServer中的TOP关键字可以限制返回到结果集中的记录个数。
下面通过例题介绍TOP关键字的用法。
【例7.14】从Student表中,查询生日最大的前5名学生的姓名和联系方式1。
SELECTTOP5姓名,联系方式1
FROMstudent
ORDERBY出生日期
运行结果如图7.16所示。
TOP关键字除了上述用法以外,还有一种用法:
TOPnPERCENT
其含义为从顶部开始获取结果集的百分之N。
例如,下面的语句查询Student表中以出生日期排序
后,前30%的学生信息。
SELECTTOP30PERCENT姓名,联系方式1
FROMstudent
ORDERBY出生日期
运行结果如图7.17所示。
2.Oracle
图7.16例7.14查询结果1图7.17例7.14查询结果2
在Oracle中使用ROWNUM限制返回的记录个数。
例如,下面的语句用于返回Student表中的前5
条记录。
SELECT*
FROMstudent
WHEREROWNUM<6;