模糊查询
比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,结果为真 |
is not null | a is not null | 如果操作符不为null,结果为真 |
between | a is between b and c | 若a在b和c之间,则结果为真 |
like | a like b | SQL匹配,如果a匹配b,则结果为真 |
in | a in (a1,a2,a3…) | 假设a在a1或者a2…其中的某一个值,结果为真 |
小红 like “小红XXX”
-- ==========模糊查询like===========
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符) _(一个字符)
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '刘%'
-- 查询姓刘的同学,名字后面只有一个字的
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '刘_'
-- 查询姓刘的同学,名字后面有两个字的
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '刘__'
-- 查询名字中间有强的同学
SELECT `studentno`,`studentname` FROM student
WHERE studentname LIKE '%强%'
-- ========模糊查询in (具体的一个或者多个值)===========
-- 查询1001,1002,1003学院
SELECT `studentno`,`studentname` FROM student
WHERE studentno IN (1001,1002,1003)
-- 查询地址无的学生
SELECT `studentno`,`studentname` FROM student
WHERE address IN ('无','北京朝阳')
-- ======null 和 not null========
-- 查询地址为空的学生 ''
SELECT `studentno`,`studentname` FROM student
WHERE address ='' OR address IS NULL
-- 查询有出生日期的同学
SELECT `studentno`,`studentname` FROM student
WHERE `borndate` IS NOT NULL
狂神说