MySQL小节练习一

小节练习一"/>

MySQL小节练习一

一：选择与过滤联系

SELECT employee_id , last_name,
salary * 12  "ANNUAL  SALARY"
FROM employees;

– 去除重复行

SELECT DISTINCT job_id
FROM employees;

– 连接字符串

SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT
FROM employees;

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

SELECT last_name, salary
FROM employees
WHERE salary > 12000;

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

SELECT last_name, department_id
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 salary NOT BETWEEN 5000 AND 12000;

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

SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 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%e%' OR last_name LIKE '%e%a%';

二：多表查询联系

1.显示所有员工的姓名，部门号和部门名称。

SELECT last_name, e.department_id, department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT job_id, location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;

或

SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;

3.选择所有有奖金的员工的 last_name , department_name , location_id , city

SELECT last_name , department_name , d.location_id , city
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name

SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';

或

SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';

5.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100

SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager, mgr.employee_id "Mgr#"
FROM employees emp 
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

三：单行函数练习

1.显示系统时间(注：日期+时间)

SELECT NOW() FROM DUAL;

2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;

3.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT last_name, LENGTH(last_name)
FROM employees
ORDER BY last_name DESC;

4.做一个查询，产生下面的结果
– <last_name> earns monthly but wants <salary*3>
– Dream Salary
– King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ', TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;

5.使用case-when，按照下面的条件：
– job grade
– AD_PRES A
– ST_MAN B
– IT_PROG C
– SA_REP D
– ST_CLERK E

– 产生下面的结果
– Last_name Job_id Grade
– king AD_PRES A

SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'WHEN 'ST_MAN' THEN 'B'WHEN 'IT_PROG' THEN 'C'WHEN 'SA_REP' THEN 'D'WHEN 'ST_CLERK' THEN 'E'ELSE 'F'END "grade"
FROM employees;