一.基础查询语句
SELECT * FROM employees
#查询员工号为176的员工姓名和部门号和年薪
SELECT last_name AS 姓名, department_id AS 部门, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees WHERE employee_id = 176 select last_name , job_id , salary as sal from employees; select * from employees; SELECT employee_id, last_name, salary * 12 "ANNUAL SALARY" FROM employees; DESC employees SELECT DISTINCT job_id FROM employees SELECT CONCAT( employee_id, ',', job_id, ',', last_name, ',', IFNULL( commission_pct, 0 )) AS OUT_PUT FROM employees
#1. 查询工资大于12000的员工姓名和工资
SELECT last_name AS NAME, salary FROM employees WHERE salary > 12000
#3. 选择工资不在5000到12000的员工的姓名和工资
SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000
#4. 选择在20或50号部门工作的员工姓名和部门号
SELECT last_name, employee_id FROM employees WHERE employee_id IN ( '20', '50') SELECT last_name, employee_id FROM employees WHERE employee_id = 20 OR employee_id = 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%e%' or '%e%a%'
#9. 显示出表employees表中 first_name 以 'e'结尾的员工信息
SELECT first_name FROM employees WHERE first_name LIKE '%e'
#10. 显示出表employees部门编号在80-100之间 的姓名、职位
SELECT last_name,department_id FROM employees WHERE employee_id between 80 AND 100
#11. 显示出表employees的manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name,department_id,manager_id FROM employees WHERE manager_id in ('100','101','110')
#1. 查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
SELECT last_name, department_id, salary * 12 *( IFNULL( 1+commission_pct, 0 )) AS 年薪 FROM employees ORDER BY 年薪 DESC, last_name ASC
#2. 选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC
#3. 查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH( email ) DESC, department_id ASC