复杂条件的查询

1、主要内容：where子句中几种复杂的搜索条件，包括组合的搜索条件（and，or），in 运算符，not运算符，like运算符和相关的通配符。

2、实例表： 简单查询一文中的teacher表，和下面的course表

teacher表：

 

 

course表：

 

3、and 和or

eg：使用teacher表，查询计算机或者生物系的，并且工资高于1000的教师信息 ，如果采用下列查询代码：

select tname ,dname,sal from teacher where dname='计算机' or dname='生物'and sal>1000 order by sal 

查询 结果为：

工资为空，或者工资低于1000的也出现在结果中，查询的结果实际是计算机系所有教师信息，以及生物系工资高于1000的教师信息，不是我们期望的结果。

原因：当and 和or一起出现在where中时候，and的优先级高于or的优先级。

因此上面的查询正确的写法应该是：

select tname ,dname,sal from teacher where (dname='计算机'or dname='生物') and sal > 1000 

查询结果：

 

4、in，not in，not

eg1：in或者not in运算符的用法

从teacher表查询计算机系，生物系，机械工程系的所有教师姓名，系，年龄，性别信息。

select tname ,dname,age,tsex from teacher where dname in('计算机','生物','机械工程') order by dname 

等价于

select tname ,dname,age,tsex from teacher where dname ='计算机'or dname='生物'or dname='机械工程' order by dname 

比较上述两者，前者（in）运算符显得简单，运行效率也较or运算符高。此外，使用in运算符后面可以是一个子查询。

 

eg2：not运算符的用法

查询teacher表中工资不高于1500的教师信息

select * from teacher where not sal > 1500 order by sal 

查询结果：

由结果可以知道：sal的值为null的行并不包括在结果中

 

not 和<>运算符的比较

• 上述例子中not运算符可以用<>代替，即not sal>1500可以等价与sal<>1500.
• not运算符较<>运算符还有一些优势：not运算符可以和其他运算符共同结合使用，但<>则不可以。下面是一个not运算符和between运算符结合使用的例子。

eg3：not 和between结合使用

从teacher表中查询年龄不在40到50岁之间的所有教师姓名，所在系，年龄，性别

select tname,dname,age,tsex from teacher where age not between 40 and 50 order by age 

当然上述查询可以用or运算符来替代

select tname,dname,age,tsex from teacher where age< 40 or age> 50 order by age 

 

5、使用like进行模糊查询

like运算符需要和通配符进行结合使用。SQL提供的通配符有%，_,[],*四个

（1）%通配符

该通配符表示任意个（0个或者0个以上），任意字符。

eg1：where dname like'计算机' 等价于 where dname = '计算机'

eg2：where dname not  like'计算机' 等价于 where dname <> '计算机'

eg3:开头/结尾匹配

select cname,scount,ctest from course where cname like '计算机%' order by cname 

查询结果：

eg4：中间匹配

select cname,scount,ctest from course where cname like '%设计%' order by cname 

查询结果：

 

eg5：两端匹配

select cname,scount,ctest from course where cname like '计算机%基础%' order by cname 

查询结果：

 

（2）“_”通配符

“_”这个通配符表示0个或者1个字符。

eg：在course表中查询所有课程表名为6个字的课程名，人数，考试时间信息。

select cname,scount,ctest from course where cname like '______' order by cname 

查询结果为：

由结果可以发现此时查询结果中不仅包含6个字符的课程，也包含小雨6个字符的课程。这一点一定要引起注意。因为‘_’通配符表示0个或者1个字符。即如果记录中有一个课程名为“计算机”的课程，也将在查询的结果中。

（3）“[]”通配符

表示取“[]”通配符中的字符串中的某一个字符

另外在通配符“[]”中的通配符自动退化成普通字符

例如：

eg：

在course表中查询所有以“计”或者“生”开头的课程名称，上课人数，考试时间

select cname,scount,ctest from course where cname like '[计生]%' order by cname 

查询结果：

如果要查询课程名称不以“计”或者“生”开头的课程名称，上课人数和考试时间，

则查询语句为：

select cname,scount,ctest from course where cname like '[^计生]%' order by cname 

即在通配符[]里面的第一个字符是尖角符号^，表示不包含里面的任何一个字符。

当然也可以等价于：

select cname,scount,ctest from course where not cname like '[计生]%' order by cname 

 

6、使用escape定义转义字符

转义字符的引入是为了表示出字符串中包含“%”，“*”，“_”，“[]”这些字符的情况

例子：

like '%M%' escape 'M' 

这里使用escape定义了一个转义字符“M”（转义字符的名称并不重要，随便什么都可以），那么转义字符M后面的通配符“%”退化为普通字符（不再时候转义字符）。当然前面那个“%”还是转义字符

 

eg：

like 'AB&_%' escape '&' 

表示以“AB_”开头的所有字符串