DQL条件查询和模糊查询

DQL条件查询

条件查询
​ 1.where字句后跟条件

 

​ 2.运算符

​ 　　> 、<、<=、>=、=、<>

-- 查询年龄大于20
SELECT * FROM student WHERE age > 20;
-- 查询年龄大于等于20
SELECT * FROM student WHERE age >= 20;
-- 查询年龄等于20
SELECT * FROM student WHERE age = 20;
-- 查询年龄不等于20
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

​ 　　BETWEEN…AND

-- 查询年龄大于等于20 小于等于20
SELECT * FROM student WHERE age >= 20 && age<=30;
SELECT * FROM student WHERE age >= 20 AND age<=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;

​ 　　IN(集合)

-- 查询年龄22岁，18岁，25岁信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN(22,18,25);

​ 　　LIKE

 

​　　 IS NULL

-- 查询英语成绩为null
SELECT * FROM student WHERE english = null; -- 错误写法，null值不能使用=（!=）判断
SELECT * FROM student WHERE english IS NULL;

​ 　　and 或 &&

-- 查询年龄大于等于20 小于等于20
SELECT * FROM student WHERE age >= 20 && age<=30;
SELECT * FROM student WHERE age >= 20 AND age<=30;

​　　 or 或 ||

-- 查询年龄22岁，18岁，25岁信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age = 22 || age = 18 || age = 25;

​ 　　not 或 ！

-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;

 

DQL模糊查询

LIKE：模糊查询

​ 占位符：
​ 1、_：单个任意字符
​ 2、%：多个任意字符

-- 查询姓马的有哪些
SELECT * FROM student WHERE name LIKE '马%';
-- 查询第二个字是化的人
SELECT * FROM student WHERE name LIKE '_化%';
-- 查询姓名是三个字的人
SELECT * FROM student WHERE name LIKE '___';
-- 查询姓名中包含德的人
SELECT * FROM student WHERE name LIKE '%德%';