比较操作符¶

比较操作符在作为条件的表达式中使用，将一个表达式与另一个表达式比较，比较结果可为TRUE、FALSE或者UNKNOWN。下表列出了神通数据库中支持的比较操作符：

表34神通数据库支持的比较操作符

操作符

描述

举例

=

相等

SELECT empname FROM employee

WHERE empnum = 'E3';

!=

<>

不等于

SELECT empnum FROM employee

WHERE grade <> 10;

SELECT empnum FROM employee

WHERE grade != 10;

>

<

大于

小于

SELECT empnum FROM employee

WHERE grade > 10;

>=

<=

大于或等于

小于或等于

SELECT empnum FROM employee

WHERE grade >= 10;

IN

等于任何成员

SELECT empname FROM employee

WHERE empnum IN

('E1','E3','E5');

NOT IN

不等于任何成员

SELECT empname FROM employee

WHERE empnum NOT IN

('E1','E3','E5');

ANY, SOME

返回集合中的任何一个元素

SELECT * FROM employee

WHERE grade = ANY

(SELECT grade FROM

employee

WHERE empnum<>'E3');

ALL

集合中的所有元素

SELECT * FROM employee

WHERE grade > ALL

(SELECT grade

FROM emp_clerk);

[NOT]

BETWEEN

X AND Y

[不]在[X,Y]区间

SELECT * FROM employee

WHERE grade BETWEEN

10 AND 20;

EXISTS

如果子查询返回不为空，则为TRUE

SELECT empname FROM employee

WHERE EXISTS

(SELECT * FROM employee,emp_clerk

WHERE employee.empnum =

emp_clerk.empnum);

X [NOT]

LIKE Y

[ESCAPE Z]

如果X[不]同模式Y 相匹配，则为TRUE。在Y 中:符号'%'可匹配0 个或多个字符，除去空值；符号'_'可匹配任何单个字符。关键字ESCAPE 用以

表示Y 字符串中紧跟在Z 字符后面的'%' 或'_'表示符号本身而不是匹配符。右例中的SQL 语句表示从表employee中查询出name 字段的前两个字符为'p_' 的所有记录，如'p_word'、'p_11'等

SELECT empname,empnum

FROM employee

WHERE empname LIKE 'ps_%'

ESCAPE 's';

X [NOT]

ILIKE Y

[ESCAPE Z]

ILIKE与LIKE用法相似，但是ILKE匹配时则不区分字符串的大小写。

SELECT empname ,empnum

FROM employee

WHERE empname ILIKE 'ps_%'

ESCAPE 's';

IS [NOT]

NULL

测试是否为空值

SELECT empnum FROM employee

WHERE city IS NULL;

/*************table employee*************/
create table employee
(empnum    char(3) NOT NULL,
 empname   char(20),
 grade     decimal(4),
 city      char(15)
);

INSERT INTO employee VALUES('E1','张三',10,'北京');
INSERT INTO employee VALUES('E2','P_p',15,NULL);
INSERT INTO employee VALUES('E1','王五',8,'杭州');
INSERT INTO employee VALUES('E3','p_p',20,NULL);
INSERT INTO employee VALUES('E1','陆七',1,'南京');

/*************table emp_clerk*************/
create table emp_clerk
(empnum    char(3) NOT NULL,
 empname   char(20),
 grade     decimal(4),
 city      char(15)
);

INSERT INTO emp_clerk VALUES('E1','张三',10,'北京');
INSERT INTO emp_clerk VALUES('E1','王五',8,'杭州');
INSERT INTO emp_clerk VALUES('E1','陆七',1,'南京');