黑猴子的家：mysql 模糊查询练习

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

SELECT last_name,salary FROM employees WHERE salary>12000;

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

SELECT IFNULL(commission_pct,0),commission_pct  FROM employees;

SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE employee_id=176;

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

SELECT last_name,salary
FROM employees
WHERE NOT(salary BETWEEN 5000 AND 12000);

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

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

SELECT last_name,department_id 
FROM employees
WHERE department_id IN(20,50);

5、选择公司中没有管理者的员工姓名及job_id

SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;

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

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

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

SELECT last_name FROM employees
WHERE last_name LIKE '__a%';

8、选择姓名中有字母a和e的员工姓名

SELECT last_name FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#或
SELECT last_name FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

9、显示出表employees表中 first_name 以 'e'结尾的员工信息

SELECT first_name FROM employees
WHERE first_name LIKE '%e';

10、案例

SELECT last_name,phone_number
FROM employees
WHERE email IS NULL;

常见错误

#1.表名不存在
# TABLE 'myemployees.employee' doesn't exist

#2.列名错误
# Unknown column 'phone_numbe' in 'FIELD LIST'

#3.语法错误
# You have an error in your SQL syntax; check the manual 
# that corresponds to your MySQL server version 
# for the right syntax to use near 'FROM employees