mysql常见查询、基础查询、条件查询习题

题目为小徐自己所做，小伙伴们可以自行下载数据或练习，欢迎讨论~

数据链接：https://pan.baidu.com/s/1E6LpxaikFO3MopLjtjA32A
提取码：h3qf
复制这段内容后打开百度网盘手机App，操作更方便哦

1.查询工资大于12000的员工姓名和工资

SELECT
last_name,
salary
FROM employees
WHERE salary>12000;

2.查询员工号为176的员工姓名和部门号和年薪

SELECT
last_name,
department_id,
salary
FROM employees
WHERE employee_id=176;

3.选择工资不在5000到12000的员工姓名和工资

方法一：
SELECT
last_name,
salary
FROM employees
WHERE salary<5000 OR salary>12000;

方法二：
SELECT
last_name,
salary
FROM employees
WHERE NOT(salary BETWEEN 6000 AND 12000);
或者
WHERE NOT salary BETWEEN 6000 AND 12000;

4.选择在20或50部门号工作的员工姓名和部门号

SELECT
last_name,
salary
FROM employees
WHERE department_id=20 OR department_id=50;

5.选择公司中没有管理者的员工姓名和job_ID

SELECT
last_name,
job_id,
manager_id
FROM employees
WHERE manager_id IS NULL;

6.选择公司中有奖金的员工姓名和工资和奖金级别

SELECT
last_name,
salary,
hiredate
FROM employees
WHERE commission_pct IS NOT NULL;

7.选择员工姓名的第三个字母是a的员工姓名

SELECT
last_name
FROM employees
WHERE last_name LIKE ‘__a%’

8.选择员工姓名中有字母a和c的员工姓名

SELECT
last_name
FROM employees
WHERE last_name LIKE ‘%a%c%’ OR last_name LIKE ‘%c%a%’

9.以e结尾的员工信息

SELECT
*
FROM employees
WHERE first_name LIKE ‘%e’;

10.部门编号在80-100的姓名和职位

SELECT
last_name,
department_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

11.manager_id 是100 101 110的员工姓名和职位

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

“任何数据都不能带给你好运，但是他们能让你悄悄成为你自己。”
小徐加油~
----2021.1.30