MySQL进阶二

#进阶2
/*
语法：
SELECT 查询列表
FROM 表名
WHERE 筛选条件;

分类：
一，按条件表达式筛选
条件运算符：> < = != <> >= <=
二，按逻辑表达式筛选
逻辑运算符：
&& || !
and or not
&&和and:两个条件都为true，结果为true，反之为false
||和or:只要有一个条件为true，结果为true，反之为false
!和not:如果连接的条件本身为false，结果为true，反之为false
三，模糊查询
like
between and
in
is null
*/

#一.按条件表达式

#案例一：查询工资>12000的员工信息
SELECT *
FROM employees
WHERE salary>12000;

#案例二：查询部门编号不等于90号员工名和部门编号
SELECT last_name,department_id
FROM employees
WHERE department_id<>90;

#二，按逻辑表达式筛选
#案例一：查询工资在10000到20000之间的员工名，工资以及奖金
SELECT last_name,salary,commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;

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

#三，模糊查询
/*
like
between and
in
is null
is not null
*/

#1.LIKE
/*
特点；
@1：一般和通配符搭配使用
通配符：
%任意多个字符，包含0个字符
_任意单个字符
*/
#案例一：查询员工名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

#案例二：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '__n_l%';

#案例三：查询员工名中第二个字符为_的员工名。可以使用转义符,ESCAPE标识符，负责自定义一个转义符
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';

#2.BETWEEN AND
/*
@1：使用BETWEEN AND可以提高语句的简洁度
@2：包含临界值 是先>=，后<=
@3：两个临界值不要调换顺序
*/
#案例一：查询员工编号在100到120之间的员工信息
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 120;

#3.IN
/*
含义：判断某字段的值是否属于IN列表中的某一项
特点：
@1：使用IN提高语句的简洁度
@2：IN列表的值类型必须统一或兼容
*/
#案例一：查询员工的工种编号IT_PROT,AD_VP,AD_PRES的一个员工名和工种编号
SELECT last_name,job_id
FROM employees
WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');

#4.is null
/*
=或<>不能用于判断null值
is null或is not null可以判断null值
*/
#案例一：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#案例二：查询有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#安全等于<=>
#案例一：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct <=>NULL;

#案例二：查询工资为12000的员工信息
SELECT last_name,salary
FROM employees
WHERE salary <=>12000;

/*
is not null：仅仅可以判断NULL值，可读性较高
<=>：既可以判断NULL的值，又可以判断普通的数值，可读性叫低
*/

#作业
/*
1. 查询工资大于 12000 的员工姓名和工资
2. 查询员工号为 176 的员工的姓名和部门号和年薪
3. 选择工资不在 5000 到 12000 的员工的姓名和工资
4. 选择在 20 或 50 号部门工作的员工姓名和部门号
5. 选择公司中没有管理者的员工姓名及 job_id
6. 选择公司中有奖金的员工姓名，工资和奖金级别
7. 选择员工姓名的第三个字母是 a 的员工姓名
8. 选择姓名中有字母 a 和 e 的员工姓名
9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
*/

#1. 查询工资大于 12000 的员工姓名和工资
SELECT CONCAT(first_name,last_name) AS 姓名,salary AS 工资
FROM employees
WHERE salary>12000;

#2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT CONCAT(first_name,last_name) AS 姓名,department_id AS 部门号,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees
WHERE employee_id=176;

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

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

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

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

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

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

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

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

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