原文博客：Doi技术团队
链接地址：https://blog.doiduoyi.com
初心：记录优秀的Doi技术团队学习经历
先进行sql语句的练习，然后再根据语句进行优化以及建立索引。
# 1查看雇员编号、名字和部门
select e.emp_no,e.last_name,de.dept_name from
employees as e,dept_emp as emp,departments as de where
e.emp_no = emp.emp_no and de.dept_no=emp.dept_no ;
# 2显示所有雇员的编号、姓名、工资
select e.emp_no,concat(e.first_name,' ', e.last_name) `name`,sa.salary from
employees as e,salaries as sa where
e.emp_no=sa.emp_no limit 100;
# 3查找在d005号部门工作的雇员
select e.emp_no,concat(e.first_name,' ', e.last_name) `name`,de.dept_name from
employees as e,dept_emp as emp,departments as de where
de.dept_no='d005' and de.dept_no=emp.dept_no and e.emp_no=emp.emp_no;
# 4要求查找职位为Engineer和Senior Engineer的雇员姓名(last_name)
select e.last_name from
employees as e ,titles as ti where
e.emp_no=ti.emp_no and
(ti.title='Engineer' or ti.title='Senior Engineer') ;
select e.last_name from
employees as e ,titles as ti where
e.emp_no=ti.emp_no and
ti.title in ('Engineer','Senior Engineer') ;
# 5查找职位不是Engineer和Senior Engineer的部门编号，雇员部门及姓名。将姓名显示为(first_name+last_name命名为”Name”)
select concat(e.first_name,' ',last_name) `name`,de.dept_no,de.dept_name from
employees as e ,dept_emp as emp , departments as de , titles as ti where
e.emp_no = ti.emp_no and e.emp_no = emp.emp_no and emp.dept_no=de.dept_no and
ti.title != 'Engineer' and ti.title != 'Senior Engineer' ;
## 会发现数据有重复，那是因为在员工职称表(title)里，有些员工是兼顾多个职称的。
# 6查找哪些雇员的工资在60000到90000之间
select concat(e.first_name,' ',last_name) `name`,salary from
employees as e,salaries where
e.emp_no = salaries.emp_no and
salaries.salary between 60000 and 90000;
# 7查找哪些雇员的工资不在60000到90000之间
select concat(e.first_name,' ',last_name) `name`,salary from
employees as e,salaries where
e.emp_no = salaries.emp_no and
salaries.salary not between 60000 and 90000;
# 8查找first_name以P开头，后面仅有四个字母的雇员信息
select distinct concat(e.first_name,' ',last_name) `name` from
employees as e where
employees.first_name like "P____";
# 9查找last_name以K开头的雇员信息
select concat(e.first_name,' ',last_name) `name` from
employees as e where
e.last_name like "K%";
# 10查找名字以字母K开头，以i结尾，并且第三个字母为o的雇员名字(First_name)、职位和所在部门号
select concat(e.first_name,' ',last_name) `name` , emp.dept_no,title from
employees as e,dept_emp as emp,titles where
e.emp_no = emp.emp_no and
e.emp_no = titles.emp_no and
e.first_name like 'k_o%i';
# 11查找哪些雇员的职位名不以Se开头
select concat(e.first_name,' ',last_name) `name` , title from
employees as e , titles where
e.emp_no = titles.emp_no and
titles.title not like 'Se%';
# 12查找d005号部门里不是Staff的雇员信息
select concat(e.first_name,' ',last_name) `name` , e.emp_no,title from
employees as e ,titles as ti , dept_emp as emp where
emp.dept_no = 'd005' and
e.emp_no=emp.emp_no and
e.emp_no = ti.emp_no and
ti.title !='Staff';
# 13查找d005号部门工资大于100000的员工的信息
select concat(e.first_name,' ',last_name) `name` , e.emp_no,salary from
employees as e ,salaries , dept_emp as emp where
e.emp_no = emp.emp_no and
emp.dept_no='d005'and
e.emp_no = salaries.emp_no and
salary >100000;
# 14按字母顺序显示雇员的名字(last_name)
select last_name from
employees
order by last_name;
# 15按部门编号降序显示雇员信息
select concat(e.first_name,' ',last_name) `name` , dept_no from
employees as e ,dept_emp as emp where
e.emp_no = emp.emp_no order by
emp.dept_no desc ;
# 16计算每个部门的平均工资和工资总和.(显示平均值，总值，部门编号，部门名称 )
select avg(salary) , count(salary) ,emp.dept_no,de.dept_name from
employees as e ,salaries as sa ,dept_emp as emp,departments as de where
e.emp_no=sa.emp_no and
emp.emp_no=e.emp_no and
emp.dept_no =de.dept_no group by
dept_name ,dept_no order by dept_no;
# 17请算出employees表中每位雇员的平均工资
select first_name,last_name,avg(salary) salary from
employees as e , salaries as sa where
e.emp_no=sa.emp_no group by
first_name,last_name order by salary ;
# 18请算出employees表中所有雇员的平均工资
select avg(salary) from
employees as e , salaries as sa where
e.emp_no=sa.emp_no ;
# 19请查询出employees表中的最低工资的员工信息
## 典型的慢查询
select concat(e.first_name,' ',last_name) `name` ,salary from
employees as e , salaries as sa where
e.emp_no=sa.emp_no order by
salary limit 1;
## 正确姿势
select concat(e.first_name,' ',last_name) `name` ,salary from
employees as e , salaries as sa where
e.emp_no=sa.emp_no and
sa.salary=
(select min(salary) from salaries);
# 20请计算出每个部门的平均工资、最高工资和最低工资
select avg(salary) , count(salary) ,max(salary),min(salary),emp.dept_no,de.dept_name from
employees as e ,salaries as sa ,dept_emp as emp,departments as de where
e.emp_no=sa.emp_no and
emp.emp_no=e.emp_no and
emp.dept_no =de.dept_no group by
dept_name ,dept_no order by dept_no;
# 21查询薪水发放时间在1986-06-26 ~ 1987-06-25薪水高于46135号雇员并且工种与他相同的雇员情况。
select e.* ,sa.from_date from
employees as e ,salaries as sa ,titles as ti where
ti.title in
(select title from titles where emp_no=46135) and
sa.from_date>'1986-06-26' and
sa.to_date < '1987-06-25' and
e.emp_no=sa.emp_no and
e.emp_no = ti.emp_no and
sa.salary >
( select salary from salaries as sa where
sa.from_date>'1986-06-26' and sa.to_date< '1987-06-25' and emp_no=46135);
# 22查询工资在10000到50000之间的雇员所在部门的所有人员的信息。
select e.*,de.dept_no,de.dept_no,salary from
employees as e ,dept_emp as emp,departments as de ,salaries as sa where
sa.salary in (select salary from salaries where salary between 10000 and 50000 ) and
e.emp_no=emp.emp_no and
emp.dept_no = de.dept_no and
sa.emp_no =e.emp_no order by salary;
# 23查询出在薪水发放时间在1986-06-26 ~ 1987-06-25的员工信息
###（工号，姓名，性别，薪水，职位)
select e.emp_no , concat(e.first_name,' ',last_name) `name`, e.gender,salary,title from
employees as e , salaries as sa , titles as ti where
e.emp_no = sa.emp_no and
e.emp_no = ti.emp_no and
sa.from_date > '1986-06-26' and
sa.to_date < '1987-06-25';
