前言
开心一刻
我要飞的更高,飞的更高,啊!
谓词
SQL 中的谓词指的是:返回值是逻辑值的函数。我们知道函数的返回值有可能是数字、字符串或者日期等等,但谓词的返回值全部是逻辑值(TRUE/FALSE/UNKNOW),谓词是一种特殊的函数。关于逻辑值,可以查看:神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !
SQL 中的谓词有很多,如 =、>、<、<> 等,我们来看看 SQL 具体有哪些常用的谓词
比较谓词
创建表与初始化数据
-- 1、表创建并初始化数据 DROP TABLE IF EXISTS tbl_student; CREATE TABLE tbl_student ( id INT(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', sno VARCHAR(12) NOT NULL COMMENT '学号', name VARCHAR(5) NOT NULL COMMENT '姓名', age TINYINT(3) NOT NULL COMMENT '年龄', sex TINYINT(1) NOT NULL COMMENT '性别,1:男,2:女', PRIMARY KEY (id) ); INSERT INTO tbl_student(sno,name,age,sex) VALUES ('20190607001','李小龙',21,1), ('20190607002','王祖贤',16,2), ('20190608003','林青霞',17,2), ('20190608004','李嘉欣',15,2), ('20190609005','周润发',20,1), ('20190609006','张国荣',18,1); DROP TABLE IF EXISTS tbl_student_class; CREATE TABLE tbl_student_class ( id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键', sno varchar(12) NOT NULL COMMENT '学号', cno varchar(5) NOT NULL COMMENT '班级号', cname varchar(20) NOT NULL COMMENT '班级名', PRIMARY KEY (`id`) ) COMMENT='学生班级表'; INSERT INTO tbl_student_class VALUES ('1', '20190607001', '0607', '影视7班'), ('2', '20190607002', '0607', '影视7班'), ('3', '20190608003', '0608', '影视8班'), ('4', '20190608004', '0608', '影视8班'), ('5', '20190609005', '0609', '影视9班'), ('6', '20190609006', '0609', '影视9班'); SELECT * FROM tbl_student; SELECT * FROM tbl_student_class;
相信大家对 =、>、<、<>(!=)等比较运算符都非常熟悉,它们的正式名称就是比较谓词,使用示例如下
-- 比较谓词示例 SELECT * FROM tbl_student WHERE name = '王祖贤'; SELECT * FROM tbl_student WHERE age > 18; SELECT * FROM tbl_student WHERE age < 18; SELECT * FROM tbl_student WHERE age <> 18; SELECT * FROM tbl_student WHERE age <= 18;
LIKE
当我们想用 SQL 做一些简单的模糊查询时,都会用到 LIKE 谓词,分为 前一致、中一致和后一致,使用示例如下
-- LIKE谓词 SELECT * FROM tbl_student WHERE name LIKE '李%'; -- 前一致 SELECT * FROM tbl_student WHERE name LIKE '%青%'; -- 中一致 SELECT * FROM tbl_student WHERE name LIKE '青%'; -- 后一致
如果name字段上建了索引,那么前一致会利用索引;而中一致、后一致会走全表扫描。
BETWEEN
当我们想进行范围查询时,往往会用到 BETWEEN 谓词,示例如下
-- BETWEEN谓词 SELECT * FROM tbl_student WHERE age BETWEEN 15 AND 22; SELECT * FROM tbl_student WHERE age NOT BETWEEN 15 AND 22;
BETWEEN 和它之后的第一个 AND 组成一个范围条件;BETWEEN 会包含临界值 15 和 22
SELECT * FROM tbl_student WHERE age BETWEEN 15 AND 22; -- 等价于 SELECT * FROM tbl_student WHERE age >= 15 AND age <= 22;
若不想包含临界值,那就需要这么写了
SELECT * FROM tbl_student WHERE age > 15 AND age < 22;
IS NULL 和 IS NOT NULL
NULL 的水很深,具体可看:神奇的 SQL 之温柔的陷阱 → 三值逻辑 与 NULL !
IN
有这样一个需求:查询出年龄等于 15、18以及20的学生,我们会用 OR 来查
-- OR SELECT * FROM tbl_student WHERE age = 15 OR age = 18 OR age = 20;
用 OR 来查没问题,但是有一点不足,如果选取的对象越来越多,SQL会变得越来越长,阅读性会越来越差。所以我们可以用 IN 来代替
-- IN SELECT * FROM tbl_student WHERE age IN(15,18,20);
IN 有一种其他谓词没有的使用方法:使用子查询作为其参数,这个在平时项目中也是用的非常多的,例如:查询出影视7班的学生信息
-- IN实现,但不推荐 SELECT * FROM tbl_student WHERE sno IN ( SELECT sno FROM tbl_student_class WHERE cname = '影视7班' ); -- 联表查,推荐 SELECT ts.* FROM tbl_student_class tsc LEFT JOIN tbl_student ts ON tsc.sno = ts.sno WHERE tsc.cname = '影视7班';
很多情况下,IN 是可以用联表查询来替换的
EXISTS
EXISTS也是 SQL 谓词,但平时用的不多,不是说适用场景少,而是它不好驾驭,我们用不好它。它用法与其他谓词不一样,而且不好理解,另外很多情况下我们都用 IN 来替代它了。
理论篇
在真正讲解 EXSITS 示例之前,我们先来了解下理论知识:实体的阶层 、全称量化与存在量化
实体的阶层
SQL 严格区分阶层,不能跨阶层操作。就用我们常用的谓词来举例,同样是谓词,但是与 = 、BETWEEN 等相比,EXISTS 的用法还是大不相同