【DataBase】MySQL 06 条件查询 & 排序查询

视频参考自：P28 - P42

https://www.bilibili.com/video/BV1xW411u7ax

 

条件查询概述

# 进阶2 条件查询
-- 语法：SELECT 查询列表 FROM 表名 WHERE 筛选条件;
-- WHERE 关键字：当...筛选条件成立时，返回条件结果

-- 分类1：按条件表达式筛选，条件运算符：大于>、小于<、等于=、不等于<>(!=)、大于等于>=、小于等于<=
-- 分类2：按逻辑表达式筛选，逻辑运算符：且&&、或||、非！、MySQL推荐关键字[AND OR NOT]来表达
-- 分类3：模糊查询：LIKE,BETWEEN AND,IN,IS NULL

条件表达式

# 按条件表达式筛选

-- 案例1：查询工资大于12000的员工信息【如果单纯的说信息，那可以默认视为表的所有数据】
SELECT * FROM `employees` WHERE `salary` > 12000;

-- 案例2：查询部门编号不等于90的【员工名】和【部门编号】
SELECT 
  `first_name`,`last_name`,`department_id` 
FROM 
  `employees` 
WHERE 
  `department_id` <> 90;

逻辑表达式

# 按逻辑表达式筛选  

-- 案例1：查询工资在10000~20000之间的员工名，工资&奖金
SELECT    
  `first_name`,`last_name`,`salary`,`commission_pct`
FROM
  `employees`
WHERE
  10000 <= `salary` AND `salary` <= 20000;  

-- 案例2：查询部门编号不是在90~110之间，或者工资高于15000的员工信息
SELECT 
  *
FROM
  `employees`
WHERE
  NOT(90 <= `department_id` AND `department_id` <= 110) OR (`salary` > 15000);
  -- `department_id` < 90 OR `department_id` > 110 OR `salary` > 15000;

模糊条件查询

# 按模糊条件查询
-- LIKE        和通配符一起使用
-- BETWEEN ... AND ...
-- IN
-- IS NULL | IS NOT NULL
-- 通配符： %任意无或单或多字符 _任意1个字符


-- LIKE 像...
-- 案例1：查询员工名中包含字符a的员工信息
SELECT * 
FROM `employees` 
WHERE `last_name` LIKE '%a%'; --  %通配符，表示无字符或者任意字符

-- 案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT `last_name`,`salary`
FROM `employees`
WHERE `last_name` LIKE '__n_l%'; -- '__e_a%' [无结果]

转义处理

-- 案例3：查询员工名中第二个字符为下划线的员工名[转义处理]
SELECT `last_name`
from `employees`
WHERE `last_name` LIKE '_\_%';

-- 或者我们自定义转义符 ESCAPE关键字
SELECT `last_name`
FROM `employees`
WHERE `last_name` LIKE '_@_%' ESCAPE '@';
--  LIKE '_a_%' ESCAPE 'a';
--  LIKE '_$_%' ESCAPE '$'; 都可以

BETWEEN ... AND ...

-- BETWEEN .. AND .. 在...和...之间 
-- 1、简化SQL，语义化SQL
-- 2、这个取值包含值本身
-- 3、值的位置不允许颠倒

-- 案例1：查询员工编号100~120之间的员工信息
SELECT * FROM `employees` WHERE `employee_id` BETWEEN 100 AND 120;

IN（...）

-- IN 关键字
-- 1、IN 等同Java的枚举
-- 2、提高SQL简洁和性能，语义化SQL
-- 3、IN列表的值类型必须一致，或者兼容
-- 4、IN的列表内容，不支持通配符【谁TM会往这里面写通配符啊!?】

-- 案例：查询员工的工种是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT `last_name`,`job_id`
FROM `employees` 
WHERE `job_id` IN('IT_PROG','AD_VP','AD_PRES');
-- 非IN的WHERE条件  job_id = 'IT_PROG' or job_id = 'AD_VP' or job_id = 'AD_PRES';

空值判断与安全判断

-- ISNULL
-- = or <> 不能判断Null值
-- is null 或者 is not null 只用于判断null值
 
-- 案例：查询没有奖金的员工名和奖金串

-- 有奖金 IS NOT NULL
-- 没奖金 IS NULL
-- 那怎么没有0的情况？

SELECT `last_name`,`commission_pct`
FROM `employees`
WHERE `commission_pct` IS NOT NULL;

# 什么是安全等于？ <=>
SELECT `last_name`,`commission_pct`
FROM `employees`
WHERE `commission_pct` <=> NULL; -- 判断是否为null值？

-- 查询奖金为12000的信息
SELECT `last_name`,`salary`
FROM `employees`
WHERE `salary` <=> 12000;

-- IS NULL 仅判断NULL值
-- <=> 既可以判断NULL值，也可以判断普通的数值
-- 但是可读性很差，不建议使用

练习案例：

-- 1. 查询工资大于 12000 的员工姓名和工资
SELECT `first_name`,`last_name` ,`salary`
FROM `employees`
WHERE `salary` > 12000;

-- 2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT `first_name`,`last_name`,`department_id`,`salary` * 12
FROM`employees`
WHERE `employee_id` = 176;

-- 3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT `first_name`,`last_name`,`salary`
FROM `employees`
WHERE `salary` < 5000 OR `salary` > 12000;

-- 4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT `first_name`,`last_name`,`department_id`
FROM `employees`
WHERE `department_id` IN (20,50);

-- 5. 选择公司中没有管理者的员工姓名及 job_id
SELECT    `first_name`,`last_name`,`job_id`
FROM `employees`
WHERE `manager_id` IS NULL;

-- 6. 选择公司中有奖金的员工姓名，工资和奖金级别
SELECT `first_name`,`last_name`,`salary`,`commission_pct`
FROM `employees`
WHERE `commission_pct` IS NOT NULL;

-- 7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT `first_name`,`last_name`
FROM `employees`
WHERE `last_name` LIKE '__a%';

-- 8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT `first_name`,`last_name`
FROM `employees`
WHERE `last_name` LIKE '%a%' OR `last_name` LIKE '%e%';

-- 9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *
FROM `employees`
WHERE `first_name` LIKE "%e";

-- 10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT `last_name`,`job_id`
FROM `employees`
WHERE `department_id` BETWEEN 80 AND 100;

-- 11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT `last_name`,`job_id`
FROM `employees`
WHERE `manager_id` IN(100,101,110);

排序查询

-- 排序查询
/*
SELECT 查询列表
FROM 表名
[WHERE 筛选条件]
ORDER BY 排序列表

1、ORDER BY 子句 支持单个字段，多个字段，表达式，函数，别名
2、ASC 默认升序排序，DESC 默认降序排序
3、ORDER BY 子句总是放在SQL语句的最后面[LIMIT 是例外] 
4、执行顺序就按照默认SQL填写的字段先后来执行
*/
SELECT * FROM `employees` ORDER BY `first_name`;

-- 案例：查询员工信息，要求工资从高到底排序[顺序是低到高，逆序高到低]
SELECT * FROM `employees` ORDER BY `salary` DESC;
SELECT * FROM `employees` ORDER BY `salary` aSC; -- [默认不写就是ASC]

-- 案例：查询员工编号大于等于90的员工信息，按入职时间的先后顺序排序
SELECT * 
FROM `employees`
where `employee_id` >= 90 ORDER BY `hiredate`;

-- 案例：按年薪的高低显示员工信息和年薪【表达式排序】 [升序降序不限]
SELECT *,`salary` * 12 *(1 + IFNULL(`commission_pct`,0)) AS YS
from `employees`
ORDER BY `salary` * 12 *(1 + IFNULL(`commission_pct`,0))  DESC; -- [asc or desc]
 
-- 案例：按年薪的高低显示员工信息和年薪【别名排序】 [升序降序不限]
SELECT *,`salary` * 12 *(1 + IFNULL(`commission_pct`,0)) AS YS
from `employees`
ORDER BY YS DESC;

-- 案例：按姓名的长度[LENGTH()]显示员工姓名和工资 [升序降序不限]
select LENGTH(`last_name`) AS `name_length`,`last_name`,`salary`
from `employees`
order by `name length`;

-- 案例：查询员工信息，要求先按工资排序，再按员工编号排序[升序降序不限]
SELECT *
FROM `employees`
ORDER BY `salary` desc,`employee_id` asc; -- 多个字段排序可以允许独立的升序降序


-- 练习案例：
-- 查询员工的姓名和部门号和年薪，按年薪降序、按姓名升序
SELECT `last_name`,`department_id`,`salary` * 12 as ys
from `employees`
order by ys DESC,`last_name` asc;

-- 选择工资不再8000到17000的员工的姓名和工资，按工资降序
SELECT `last_name`,`salary`
from `employees`
where `salary` not between 8000 and 17000
order by `salary` desc;

-- 查询邮箱中包含e的员工信息，按邮箱的字节降序，再按部门号升序
SELECT *,LENGTH(`email`) AS elength
FROM `employees`
WHERE `email` LIKE '%e%'
order by length(`email`) desc,`department_id` asc;