模糊查询

qq_49622608

于 2020-07-24 23:00:15 发布

阅读量215

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_49622608/article/details/107571320

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

7、模糊查询

#模糊查询
/*
1.like
一般搭配着通配符使用，用作字符型的判断
通配符：
% 任意多个字符
_ 任意单个字符 1

2.between and
特点：在…之间
①等价于使用字段>=值1 and 字段<=值2
②两个临界值是包含的 [1,10]
③两个临界值不能颠倒顺序

3.in
特点：判断某个字段的值是否在某个列表内
in(值1，值2，…)

等价于 or

4.is null/is not null
*/

#1.like

#案例1：查询姓名中包含字符 e的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘%e%’;

#案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘_e_a%’;

#案例3：查询姓名中第三个字符为_的员工信息
SELECT * FROM employees
WHERE last_name LIKE ‘___%’;

// 斜线 \ -> window 斜线
/ -> Linux 斜线
// 反斜线

#ESCAPE 自定义转移符
SELECT * FROM employees
WHERE last_name LIKE ‘__ $KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '$ ’;

// ESCAPE ’ $^{'}; 这句话是让$ 拥有转义字符的形式和功能

UPDATE employees SET last_name=’’ ‘’ WHERE employee_id=100;

#2.between and

#案例1：显示出表employees部门编号在80-100之间的姓名、职位
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

WHERE department_id BETWEEN 100 AND 80; ×

#等价于
SELECT last_name,job_id,department_id
FROM employees
WHERE department_id<=100 AND department_id>=80;

#3.in
#案例1：显示出表employees的manager_id 是 100,101,110 的员工姓名、职位

SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IN(100,101,110);

#等价于
SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id=100 OR manager_id=101 OR manager_id = 110;

#案例2：查询 job_id 为AD_VP或ST_MAN或SA_MAN
SELECT last_name,job_id,manager_id
FROM employees
WHERE job_id IN(‘AD_VP’,‘S__MAN’);# ×

#4.is null / is not null

#案例1：查询没有奖金的员工
SELECT * FROM employees
WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工
SELECT * FROM employees
WHERE commission_pct IS NOT NULL;

SELECT * FROM employees
WHERE NOT(commission_pct IS NULL);

SELECT * FROM employees WHERE last_name IS NULL;