2019独角兽企业重金招聘Python工程师标准>>>
先学习NOT(逻辑非)用法。
#查询StudentBirthday非空列
SELECT * FROM "public"."Students" WHERE "StudentBirthday" IS NOT NULL;
#注意对比两个语句查询结果
SELECT "StudentBirthday" FROM "public"."Students" WHERE "StudentBirthday" IS NOT NULL;
排除非条件值
#不查询StudentSalary=2值
SELECT * FROM "public"."Students" WHERE "StudentSalary" NOT IN('2');
#对比查询语句
SELECT "StudentName","StudentAddress" FROM "public"."Students" WHERE "StudentSalary" NOT IN('2');
LIKE支持模糊查询,LIKE与两个通配符(%与_)一起使用,百分号表示零个,一个或多个字符。下划线代表一个单一字符。通配符可以被组合使用。ILIKE和LIKE 类似,LIKE区分大小写,ILIKE不区分。SIMILAR TO和LIKE有相似性,不过后者支持模糊查询同时还支持正则表达式。
先插入两条数据用于练习。
INSERT INTO "Students" VALUES (13, '杜牧', '0803-1-1', '陕西省西安市', 3);
INSERT INTO "Students" VALUES (14, '李商隐', '0817-1-1', NULL, NULL);
#查询所有杜姓人名,匹配单个字符
SELECT * FROM "public"."Students" WHERE "StudentName" LIKE '杜_';
#匹配多个字符,本例匹配StudentAddress列中含有陕西查询结果
SELECT "StudentName","StudentAddress" FROM "public"."Students" WHERE "StudentAddress" LIKE '%陕西%';