数据库操作005

1、练习

eg1、查询编号>3的女神的男朋友详细信息，如果没有使用null填充

mysql> select b.id,b.name,bo.*
    -> from beauty b left outer join boys bo
    -> on b.'boyfriend_id'=bo.'id'
    -> where b.'id'>3;

eg2、查询哪个城市没有部门？

mysql> select city,d.*
    -> from locations l left outer join departments d  # 城市是主表，从表的主键是null
    -> on d.'location_id'=l.'location_id'
    -> where d.'department_id' is NULL;

eg3、查询部门名是sal活着it的部门名的员工信息

mysql> select e.*,d.department_name
    -> from department d left join  employees e  # 有部门名的员工信息，所以部门表是主表
    -> on d.'department_id'=e.'department_id'
    '> where d.'department_name' in('sal','it');

2、子查询

select语句出现在其他子句中

2.1标量子查询

eg1、谁的工资比abe高？
1、先查abe的工资
2、再查工资和abe作比较

mysql> select *
    -> from employees
    -> where salary > (
	->    select salary
	->    from employees
	->    where last_name='abe'
    -> );

3、返回job_id 与141号相同，salary比143号员工多的员工姓名，job_id,和salary

1、mysql> select job_id
    -> from employees e
    -> where employee_id=141;

2、mysql> select salary
    -> from employee e
    -> where employee_id=143;




mysql> select last_name,job_id,slary
    -> from employee
    -> where job_id =(1)
    -> and  salary>(2);

eg4、查询部门的最低工资比50号部门最低工资高的部门id和其最低工资?

having也是条件筛选，只是是分组之后的筛选而where是分组之前的筛选
step1：50号部门的最低工资：

			select min(salary)
    ->     from employees
    ->     where department_id=50

step2:
查询每个部门的最低工资：

 select min(salary),department_id
    -> from employees
    -> group by department_id

step3:在2的基础上筛选，满足min(salary)>1

mysql> select min(salary),department_id
    -> from employees
    -> group by department_id
    -> having min(salary) > (
    ->     select min(salary)
    ->     from employees
    ->     where department_id=50
    -> );

2.2列子查询

in not in 等于列表中的任意一个
any some 和子查询的某一个值比较
all 和子查询的所有值进行比较
eg1.返回location_id是1400和1700的部门中的所有员工姓名
step1:找到location_id是1400和1700的部门

select distinct department_id
from departments
where location_id in (1400,1700);

step 2：在部门中寻找员工姓名

mysql> select last_name
    -> from employees
    -> where department_id in(
    ->     select distinct department_id
    ->     from departments
    ->     where location_id in(1400,1700)
    -> );

eg3、返回其他工种中，比job_id为“IT-prog”工种任一工资低的员工的：工号，姓名，job_id, salary

mysql> select last_name,employee_id,job_id,salary
    -> from employees
    -> where salary < (
    ->    select max (salary)
    ->    from employees
    ->    from employees
    ->    where job_id='IT_prog'
    -> )and job_id <>'IT_prog';