MySQL——条件查询

2023.9.2

        今天学习了尚硅谷MySQL基础DQL语言中的条件查询语言。相关SQL语句笔记如下：

#条件查询
SELECT * FROM employees ;
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE `salary`>=10000 AND `salary`<=15000;
SELECT `last_name`,`salary` FROM `employees` WHERE `last_name` LIKE '_l__m';# _为任意单个字符
SELECT `last_name`,`salary` FROM `employees` WHERE `last_name` LIKE '%lo%';# %为任意多个字符
SELECT `last_name`,`salary` FROM `employees` WHERE `last_name` LIKE '_\_%';# \为转义字符
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE `salary` BETWEEN 10000 AND 15000;
SELECT `last_name`,`job_id` FROM `employees` WHERE `job_id` IN ('AD_VP','ST_MAN','SA_REP');
SELECT `last_name`,`commission_pct` FROM `employees` WHERE `commission_pct` IS 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 的员工姓名、职位

#条件查询测试
SELECT `last_name`,`salary` FROM `employees` WHERE `salary`>12000;
SELECT `last_name`,`department_id`,salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪 FROM `employees`;
SELECT `last_name`,`salary` FROM `employees` WHERE `salary`>12000 OR `salary`<5000;
SELECT `last_name`,`department_id` FROM `employees` WHERE `department_id` IN (20,50);
SELECT `last_name`,`job_id` FROM `employees` WHERE `manager_id` IS NULL;
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE `commission_pct` != 'NULL';
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE `last_name` LIKE '__a%';
SELECT `last_name`,`salary`,`commission_pct` FROM `employees` WHERE `last_name` LIKE '%a%e';
SELECT * FROM `employees` WHERE `first_name` LIKE '%e';
SELECT `last_name`,`job_id` FROM `employees` WHERE `department_id` BETWEEN 80 AND 100;
SELECT `last_name`,`job_id`,`manager_id` FROM `employees` WHERE `manager_id` IN (100,101,110);