SQLite 逻辑运算符

广告位

这里是在SQLite中所有的逻辑运算符一个列表 运算符 描述 AND The AND operator all…

这里是在SQLite中所有的逻辑运算符一个列表

运算符 描述
AND The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
BETWEEN The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN The IN operator is used to compare a value to a list of literal values that have been specified.
NOT IN The negation of IN operator which is used to compare a value to a list of literal values that have been specified.
LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
GLOB The GLOB operator is used to compare a value to similar values using wildcard operators. Also, GLOB is case sensitive, unlike LIKE.
NOT The NOT operator reverses the meaning of the logical operator with which it is used. Eg. NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is negate operator.
OR The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.
IS NULL The NULL operator is used to compare a value with a NULL value.
IS The IS operator work like =
IS NOT The IS operator work like !=
|| Adds two different strings and make new one.
UNIQUE The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

示例

考虑COMPANY 表有以下记录:

  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  1           Paul        32          California  20000.0  2           Allen       25          Texas       15000.0  3           Teddy       23          Norway      20000.0  4           Mark        25          Rich-Mond   65000.0  5           David       27          Texas       85000.0  6           Kim         22          South-Hall  45000.0  7           James       24          Houston     10000.0  

下面是简单的例子显示使用SQLite的逻辑运算符。下面的SELECT语句列出了所有的记录AGE 大于或等于25和工资大于或等于65000.00:

  sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  4           Mark        25          Rich-Mond   65000.0  5           David       27          Texas       85000.0

下面的SELECT语句列出了所有的记录年龄大于或等于25或薪金大于或等于65000.00

  sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  1           Paul        32          California  20000.0  2           Allen       25          Texas       15000.0  4           Mark        25          Rich-Mond   65000.0  5           David       27          Texas       85000.0

下面的SELECT语句列表年龄不为NULL,这意味着所有记录下来的所有记录,因为记录是没有年龄等于NULL:

  sqlite>  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  1           Paul        32          California  20000.0  2           Allen       25          Texas       15000.0  3           Teddy       23          Norway      20000.0  4           Mark        25          Rich-Mond   65000.0  5           David       27          Texas       85000.0  6           Kim         22          South-Hall  45000.0  7           James       24          Houston     10000.0

下面的SELECT语句列出了所有的记录名称以 'Ki' 开头, 无所谓什么在 'Ki' 的后面.

  sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  6           Kim         22          South-Hall  45000.0

下面的SELECT语句列出了所有的记录名称以 'Ki' 开头, 无所谓什么在 'Ki' 的后面.

  sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  6           Kim         22          South-Hall  45000.0

下面的SELECT语句列表年龄值是25或27的所有记录:

  sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  2           Allen       25          Texas       15000.0  4           Mark        25          Rich-Mond   65000.0  5           David       27          Texas       85000.0

下面的SELECT语句列表年龄值既不是25也不是27的所有记录:

  sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  1           Paul        32          California  20000.0  3           Teddy       23          Norway      20000.0  6           Kim         22          South-Hall  45000.0  7           James       24          Houston     10000.0

下面的SELECT语句列出了所有的年龄值是在25和27之间的记录:

  sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  2           Allen       25          Texas       15000.0  4           Mark        25          Rich-Mond   65000.0  5           David       27          Texas       85000.0

下面的SELECT语句使用SQL子查询子查询发现所有记录年龄字段的SALARY>65000,后跟 WHERE子句一起使用EXISTS操作列出了所有的记录存在年龄从外面查询子查询返回的结果:

  sqlite> SELECT AGE FROM COMPANY           WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);  AGE  ----------  32  25  23  25  27  22  24

下面的SELECT语句使用SQL子查询子查询发现所有记录年龄场有SALARY>65000后​​正在使用WHERE子句>运算符一起列出了所有的记录,其中从外部查询年龄大于年龄在子查询返回的结果:

  sqlite> SELECT * FROM COMPANY           WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);  ID          NAME        AGE         ADDRESS     SALARY  ----------  ----------  ----------  ----------  ----------  1           Paul        32          California  20000.0

  

洁姐我爱你

关于作者: 洁姐我爱你

为您推荐