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)