数据

所使用的数据表均打包成数据库，下方安装文章内提供。

↓↓↓↓↓

↑↑↑↑↑

以下所有查询操作记得要先进数据库后操作！！

And 1.基本的select语句

And 1.1.1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY

select employee_id,last_name,salary*12 "ANNUAL SALARY" from employees;

And 1.1.2.查询employees表中去除重复的job_id以后的数据

select distinct job_id from employees;

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

select last_name,salary from employees where salary>12000;

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

select last_name,department_id from employees where employee_id=176;

And 1.1.5.显示表 departments 的结构，并查询其中的全部数据

desc departments;
select * from departments;

And 2.运算符

And 2.1.1.选择工资不在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;

And 2.1.2.选择在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);

And 2.1.3.选择公司中没有管理者的员工姓名及job_id

select last_name,job_id from employees where manager_id is null;

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

select last_name,salary,commission_pct from employees where commission_pct is not null;

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

select last_name from employees where last_name like "__a%";

And 2.1.6.选择姓名中有字母a和k的员工姓名

select last_name from employees where last_name like '%a%k%' or last_name like '%k%a%';

And 2.1.7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息

select employee_id,first_name,last_name from employees where first_name like "%e";

And 2.1.8.显示出表 employees 部门编号在 80-100 之间的姓名、工种

select last_name,job_id from employees where department_id between 80 and 100;

And 2.1.9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

select last_name,salary from employees where manager_id in(100,101,110);

And 3.排序与分页

And 3.1.1.查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

select last_name,department_id,salary*12 annual_sal from employees order by annual_sal desc, last_name asc;

And 3.1.2.选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc limit 20,20;

And 3.1.3.查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

select last_name,email,department_id from employees where email like '%e%' order by LENGTH(email) desc, department_id asc;

And 4.多表查询

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

select last_name, e.department_id, department_name from employees e left outer join departments s d ON e.`department_id` = d.`department_id`;

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

select job_id,location_id from employees e join departments d ON e.`department_id`=d.`departmentnt_id` where e.`department_id`=90;
select job_id,location_id from employees e,departments d where e.`department_id`=d.`department__id` AND e.`department_id`=90;

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

select last_name,department_name,d.location_id,city from employees e LEFT outer Join departmentts d ON e.`department_id`=d.`department_id` LEFT outer Join locations l ON d.`location_id`=l.`locationn_id` where e.`commission_pct` is not NULL;

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

select last_name,job_id,e.department_id,d.department_name from employees e LEFT outer Join depaartments d ON e.`department_id`=d.`department_id` LEFT outer Join locations l ON d.`location_id`=l.`loocation_id` where city='Toronto';
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 l.city='Torontoo';

And 4.1.5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’

##同上
select department_name,street_address,last_name,job_id,salary from employees e,departments d,locations l where e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` And d.`departtment_name`='Executive';

And 4.1.6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式\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 mgr.`employee_id`=emp.`manager_id`;

And 4.1.7.查询哪些部门没有员工

select d.department_id,d.department_name from departments d LEFT outer Join employees e ON e.`departmedepartment_id`=d.`department_id` where e.`department_id` is null;
select d.department_id,d.department_name from departments d where not EXISTS( select * from employees e where e.`department_id`=d.`department_id` );

And 4.1.8.查询哪个城市没有部门

select l.location_id,l.city from locations l LEFT outer Join departments d ON l.`location_id`=d.`location_id` where d.`location_id` is null;

And 4.1.9.查询部门名为 Sales 或 IT 的员工信息

select employee_id,last_name,department_name from employees e,departments d where e.`department_id`=d.`department_id` AND d.`department_name` in ('Sales','IT');
select employee_id,last_name,department_name from employees e LEFT outer Join departments d ON e.`department_id`=d.`department_id` where d.`department_name` in ('Sales','IT');

And 4.2.【练习】

暂定。

And 5.单行函数

And 5.1.【题目】

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

SELECT NOW() FROM DUAL;

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

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

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

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

And 5.1.4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT

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

And 5.1.5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(),hire_date) worked_days FROM employees ORDER BY worked_years DESC;

And 5.1.6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id为80 或 90 或110, commission_pct不为空

SELECT last_name, hire_date, department_id FROM employees
#WHERE hire_date >= '1997-01-01'
#WHERE hire_date >= STR_TO_DATE('1997-01-01', '%Y-%m-%d')
WHERE DATE_FORMAT(hire_date,'%Y') >= '1997'
AND department_id IN (80, 90, 110) AND commission_pct IS NOT NULL

And 5.1.7.查询公司中入职超过10000天的员工姓名、入职时间

SELECT last_name,hire_date FROM employees
#WHERE TO_DAYS(NOW()) - to_days(hire_date) > 10000;
WHERE DATEDIFF(NOW(),hire_date) > 10000;

And 5.1.8.做一个查询，产生下面的结果：

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

And 5.1.9.使用case-when，按照下面的条件：

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;

And 6.聚合函数

And 6.1.【题目】

And 6.1.1.where子句可否使用组函数进行过滤?

不能

And 6.1.2.查询公司员工工资的最大值，最小值，平均值，总和

select max(salary),min(salary),avg(salary),sum(salary) from employees;

And 6.1.3.查询各job_id的员工工资的最大值，最小值，平均值，总和

select job_id,max(salary),min(salary),avg(salary),sum(salary) from employees grooup by job_id;

And 6.1.4.选择具有各个job_id的员工人数

select job_id,COUNT(*) from employees group by job_id;

And 6.1.5.查询员工最高工资和最低工资的差距（DIFFERENCE）

select max(salary),min(salary),max(salary)-min(salary) DIFFERENCE from employees;

And 6.1.6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary)>6000;

And 6.1.7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

select department_name,location_id,count(employee_id),avg(salary) from employees e right Join departments d ON e.`department_id`=d.`department_id` group by departmentt_name,location_id order by avg(salary) desc;

And 6.1.8.查询每个工种、每个部门的部门名、工种名和最低工资

select department_name,job_id,min(salary) from employees e RIGHT outer Join deparartments d ON e.`department_id`=d.`department_id` group by department_name,job_id;

And 7.子查询

And 7.1.【题目】

And 7.1.1.查询和Zlotkey相同部门的员工姓名和工资

select last_name,salary from employees where department_id=(
    select department_id from employees where last_name="Zlotkey"
    );

And 7.1.2.查询工资比公司平均工资高的员工的员工号，姓名和工资。

select employee_id,last_name,salary from employees where salary>(
    select avg(salary) from employees
    );

And 7.1.3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

select last_name,job_id,salary from employees where salary>ALL(
    select salary from employees where job_id="SA_MAN" 
    );

And 7.1.4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名

select employee_id,last_name from employees where department_id=ANY(
    select DISTINCT department_id from employees where last_name like "%u%"
    );

And 7.1.5.查询在部门的location_id为1700的部门工作的员工的员工号

select employee_id from employees where department_id in (
    select department_id from departments where location_id=1700
    );

And 7.1.6.查询管理者是King的员工姓名和工资

select last_name,salary from employees where manager_id in (
    select employee_id from employees where last_name="King"
    );

And 7.1.7.查询工资最低的员工信息: last_name, salary

select last_name,salary from employees where salary=(
    select min(salary) from employees
    );

And 7.1.8.查询平均工资最低的部门信息

SELECT d.* FROM departments d,(
        SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S ASC
    ) dd WHERE dd.department_id=d.department_id LIMIT 0,1;
##嵌入语句使用 group by 将平均工资按照的department_id(部门id)聚合分组，以部门的平均工资asc降序排列，使用别名表将departments表依照嵌入查询的别名表dd排序，limit 0,1 取升序中的第一行数据。

And 7.1.9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

SELECT d.*,dd.avg_S FROM departments d,(
        SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S ASC
    ) dd WHERE dd.department_id=d.department_id LIMIT 0,1;
##在上方的语句d.*后加上dd别名表的平均工资字段列

And 7.1.10.查询平均工资最高的 job 信息

SELECT j.* FROM jobs j,(
        SELECT job_id, AVG(salary) FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC
    ) e WHERE j.job_id=e.job_id LIMIT 0,1;

And 7.1.11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING AVG(salary)>(
        SELECT AVG(salary) FROM employees
    );

And 7.1.12.查询出公司中所有 manager 的详细信息

SELECT employee_id, last_name, salary FROM employees WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);

SELECT e.employee_id, e.last_name, e.salary FROM employees e JOIN employees ee WHERE e.employee_id=ee.manager_id;

SELECT employee_id, last_name, salary FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id);

And 7.1.13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?

SELECT MIN(salary) FROM employees WHERE department_id=(
    SELECT department_id FROM employees GROUP BY department_id HAVING MAX(salary)=(
        SELECT MIN(max_S) FROM (
            SELECT MAX(salary) max_S FROM employees GROUP BY department_id ORDER BY department_id
        ) tabale_max_S
    )
);

And 7.1.14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name, department_id, email, salary FROM employees WHERE employee_id IN(
    SELECT DISTINCT manager_id FROM employees e,(
        SELECT department_id, AVG(salary) avg_S FROM employees GROUP BY department_id ORDER BY avg_S DESC LIMIT 0,1
    ) ee WHERE e.department_id=ee.department_id
);

And 7.1.15.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

SELECT department_id FROM departments WHERE department_id NOT IN (
    SELECT department_id FROM employees WHERE job_id="ST_CLERK"
);

And 7.1.16.选择所有没有管理者的员工的last_name

SELECT last_name FROM employees e WHERE  NOT EXISTS(
    SELECT * FROM employees ee WHERE e.manager_id=ee.employee_id
);

And 7.1.17.查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

SELECT employee_id, last_name, hire_date, salary FROM employees WHERE manager_id = (
    SELECT employee_id FROM employees WHERE last_name="De Haan"
);

And 7.1.18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

SELECT employee_id, last_name, salary FROM employees e,(
    SELECT department_id,AVG(salary) avg_S FROM employees GROUP BY department_id
) ee WHERE e.department_id=ee.department_id AND e.salary>ee.avg_S;

And 7.1.19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

SELECT department_name, department_id FROM departments d WHERE 5<(
    SELECT COUNT(*) FROM employees e WHERE d.department_id=e.department_id 
);

And 7.1.20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）

SELECT country_id FROM locations l WHERE 2<(
    SELECT COUNT(*) FROM departments d WHERE l.location_id=d.location_id
)