子查询

1. 笛卡尔连接  如果一个表m行  一个表n行  笛卡尔积为n*m行   应尽量避免笛卡儿连接，即应该在from子句中有n个表时，在where子句中至少要有n-1

 

2. 自身连接   from table a,table b    table是同一个表，但是有两个不同的别名

 

3. 内连接   只有当连接中的列的值满足连接条件时才会返回相应的行的连接。前面的都是内连接。

 

select e.first_name,d.department_name
from employees e,departments d
where e.department_id=d.department_id
and e.employee_id=177;

 

select e.first_name,d.department_name
from employees e inner join departments d
on e.department_id=d.department_id
and e.employee_id=177;

 

 4. 单行子查询   子查询返回单行记录  可以使用单行运算符   =  < > ...

select * 
from employees
where salary = (
select max(salary)
from employees
)

 

 5. 子查询返回多行记录，使用多行运算符   in  not in   exists  all  any

select * 
from employees
where salary >= all (
select salary
from employees
where department_id=20
)

 

 4. 多列子查询

select * 
from employees
where (salary,job_id)= (
select salary,job_id
from employees
where employee_id=198
)

  

 

 5. 在DDL  DML中使用子查询   

create table
emp(emp_id,f_name,hire_date,sal,dept_id)
as
select employee_id,first_name,hire_date,salary,department_id
from employees 
where department_id in (90,110)
order by department_id,employee_id

 

 

 

insert into emp
select employee_id,first_name,hire_date,salary,department_id
from employees 
where department_id = 20

 

delete from emp
where sal> (select avg(max_salary) from jobs)