这些天都在写数据库的笔记,这些笔记其实大部分之前都学过,但是好久不用都忘记了,呵呵,所以找了本书《SQL入门经典》(第5版)在研究研究,学习一边,一边看,一边做笔记,希望这次能记得更牢固一些
一: 从多个表获取数据
1.等值结合:
select employee_tbl.emp_id , employee_pay_tbl.date_hire
from employee_tbl , employee_pay_tbl
where employee_tbl.emp_id = employee_pay_tbl.emp_id;
>>>>
select employee_tbl.emp_id , employee_pay_tbl.date_hire
from employee_tbl
inner join employee_pay_tblon employee_tbl.emp_id = employee_pay_tbl.emp_id;
2.不等值结合
select e.emp_id,e.last_name,p.position
from employee_tbl e,
employee_pay_tbl p
where e.emp_id<>p.emp_id;
3.外部结合
外部结合只能用于join条件的一侧,但可以在join条件里对同一个表里的多个字段进行外部结合
select p.prod_desc,o.qty
from products_tbl p,
orders_tbl o
where p.prod_id=o.prod_id(+);
》》》》》》》》》》》》》等同于
select p.prod_desc,o.qty
from products_tbl p
left outer join orders_tbl o
on p.prod_id=o.prod_id;
4.自结合
利用SQL语句对表进行重命名 像两个表处理结合到自身
select a.last_name,b.last_name,a.first_name
from employee_tbl a,
employee_tbl b
where a.last_name=b.last_name;
>>>>>>>>>>>>>>>>>>>>>等同于
select a.last_name,b.last_name,a.first_name
from employee_tbl a,
inner join employee_tbl b
on a.last_name=b.last_name;
5.使用基表
这个表与前两个表都有公有字段,这个表就被称为基表
select c.cust_name,p.prod_desc
from customer_tbl c,
products_tbl p,
orders_tbl o
where c.cust_id = o.cust_id
and p.prod_id = o.prod_id;
二:子查询
1.与select语句
select e.emp_id,e.last_name,e.firs_name,ep.pay_rate
from employee_tbl e, employee_pay_tbl ep
where e.emp_id = ep.emp_id
and ep.pay_rate>(select pay_rate
from employee_pay_tbl
where emp_id = '220984332';)
2.与insert 语句
insert into rich_employees
select e.emp_id , e.last_name , e.first_name , ep.pay_rate
from employee_tbl e, employee_pay_tbl ep
where e.emp_id = ep.emp_id
and ep.pay_rate > (select pay_rate
from employee_pay_tbl
where emp_id = '290129013')
3.与update语句 4. 与delete语句 (同上)
5.嵌套的子查询
最内层的子查询先被执行,然后在依次执行外层的子查询,直到主查询
select cust_id , cust_name
from customer_tbl
where cust_id in (select o.cust_id
from orders_tbl o,products_tbl p
where o.prod_id = p.prod_id
and o.qty+p.cust<(select sum(cost)
from products_tbl));
6.关联子查询
select c.cust_name
from customer_tbl c
where 10 <(select sum(o.qty)
from orders_tbl o
where o.cust_id = c.cust_id);
>>>>>>>>>>>>>>>>>>>>>>修改之后,显示每个顾客订购的物品的数量
select c.cust_name , sum(o.qty)
from customer_tbl c
orders_tbl o
where c.cust_id=o.cust_id
group by c.cust_name;
注意:如果在子查询中使用某个表,必须首先在主查询中引用这个表
三:组合查询
组合查询操作符
1.union (union不会返回重复的数据)
例如:组合两个不相关的重复的数据
select prod_desc from products_tbl
union
select last_name from employee_tbl
2. union all (包含重复的结果)
3.intersect(intersect 可以组合两个select语句,但返回第一个select语句里与第二个select语句里一样的记录)
select prod_desc from products_tbl
intersect
select last_name from employee_tbl
4.except (返回第一个select语句里有但是第二个select语句里没有的记录)(语句规格同上)
5.order by 组合查询里可以有多个select语句但是只能有一个order by ,而且只能以别名或者数字来引用字段
6.group by