PostgreSQL-5-条件过滤

基本语法

SELECT column1, column2, columnN FROM table_name WHERE [search_condition] 

操作符

=等于；<>不等于；!=不等于；>大于；<小于；>=大于等于;<=小于等于；!<不小于；!>不大于

 

SELECT * FROM student WHERE age>14;  筛选年龄大于14岁的数据

SELECT studentname,age,classno FROM student WHERE classno = 2;  筛选2班的数据

 

 

组合语句，AND/OR

SELECT * FROM student WHERE age>14 AND classno <3;  AND → 且，需要同时满足多个条件

SELECT * FROM student WHERE classno = 2 OR classno = 4; OR → 或，只要满足任意条件即可

 

 

是否为空值

SELECT * FROM student WHERE age IS NOT NULL;

SELECT * FROM student WHERE age IS NULL;

 

 

IN条件，用来制定条件范围，多个条件用()括起来，只要满足其中一个条件即成立（类似OR）

SELECT column1, column2, ..... columnN FROM table_name WHERE [search_condition] IN [condition];

 

SELECT * FROM student WHERE classno IN (3,4,5); 

SELECT * FROM student WHERE age IN (14,15); 

 

 

BETWEEN，指定两个值之间

SELECT * FROM student WHERE classno BETWEEN 2 AND 5; 

 

 

NOT，否定条件

SELECT * FROM student WHERE age IS NOT NULL;

SELECT * FROM student WHERE classno NOT IN (3,4,5);

SELECT * FROM student WHERE NOT age>14;

 

 

LIKE，通配符过滤

SELECT * FROM student WHERE studentname LIKE '张%';  查询姓张的人

SELECT * FROM student WHERE studentname LIKE '%三';  查询名为三的人

%通配符：任意字符，出现任意次数包括：0次、1次、多次，但空值不能匹配

 

SELECT * FROM student WHERE studentname LIKE '小_';  1个字匹配，小X

SELECT * FROM student WHERE studentname LIKE '小__'; 2个字匹配，小XX

SELECT * FROM student WHERE studentname LIKE '小%';  任意个字匹配

_通配符：任意字符，但只出现1次，注意和%的区别

 

SELECT * FROM student WHERE studentname NOT LIKE '张%';

SELECT * FROM student WHERE studentname NOT LIKE '小__';

和NOT的配合使用