sql基础--多表连接及子查询

首先，多表连接肯定是多个表之间进行操作，这里先定义三个表:

供应商表(t_supplier):

(f_sid,f_sname,f_city)

零件表(t_part):

(f_pid,f_pname,f_price)

销售表(t_sells):

(f_id,f_date,f_sid,f_pid,f_count)

一、多表连接查询

    1、交叉连接查询（笛卡尔积连接）：第一个表中的每条数据与第二个表中的每条数据记录配对

    1.1传统方式连接：查询每个供应商可能供应零件情况

select f_sid,f_pid
from t_supplier,t_part;

----------表1有m条数据，表2有n条数据，那么笛卡尔积得到的数据总数是m*n条

    1.2join连接  oracle中，可以使用cross与join一起使用来创建笛卡尔积

select f_sid,f_pid
from t_supplier cross join t_part;

   2、为了避免出现笛卡尔积

    内连接：等值连接、非等值连接

    2.1、等值连接(等同连接、内部连接、简单连接)

            公共列是两个或更多表中共有的列

select f_sid,f_sname,f_pid,f_count
from t_supplier t_su,t_sells t_se   /*添加别名*/
where t_su,f_sid=t_se.f_sid;

--注意：(1)、查询公共列，要指出具体出自哪个表

            (2)、where子句中的f_sid列名称前面包括了对应的表名称

 3、join方法

    3.1natural join 关键字将在包括一个同等命名和定义的字段之间将自动连接

select f_sid,f_name,f_pid,f_count
from t_supplier
natural join sells;                        /*注意是natural，而不是natura*/

    3.2join ...... using  可以根据在两个表中具有相同名称和定义的列创建连接

select f_sid,f_name,f_pid,f_count
from t_supplier
natural join sells using(f_sid); 

    3.3join  on 当using子句中没有包含一个同等命名和定义的字段时，必须添加

select t_supplier.f_sid,f_name f_pid,f_count
from t_supplier
where t_sells on t_supplier.f_sid=t_sells.f_sid;

    4、非等值连接

        4.1查询每个供应商编号，姓名，零件编号，零件名称，零件数量>=50

  select t_supplier.f_sid,f_sname,t_part.f_pid,f_pname,f_count
  from t_supplier,t_part,t_sells
  where t_supplier.f_sid=t_sells.f_sid and t_part.f_pid=t_sells.f_pid;

--注意点：当表名是别名时，其他地方也要用别名表示，比如select语句中用了表的原名称而在where语句中用了别名，运行的时候就会报错。

        4.2自连接：一个表中的数据引用了本表之中其他的信息

                        也就是说一个表连接他本身，将这个表看做是两个表来对待，之后给这一个表起两个不同的别名就成了名称不一                         样，但内容相同的两个表，也就实现了自连接

        首先创建表：employees表，（em_id,em_name,mana_id）

         插入数据若干

    --查询员工表中所有的管理者信息

select distinct mna.*
from employees work,employess mana
where work.em_id=mana.em_id;

    4.3外连接     当需要在结果中包括存在于一个表中

           --左外连接     left join

select su.f_sid,f_pid,f_id,f_count
from t_supplier su
left join t_sells se
on su.f_sid=se.f_sid;

            --右外连接   right join

select su.f_sid,f_pid,f_id,f_count
from t_supplier su
right join t_sells se
on su.f_sid=se.f_sid;

            --全外连接  full join

select su.f_sid,f_pid,f_id,f_count
from t_supplier su
all join t_sells se
on su.f_sid=se.f_sid;

    注意：(+)是一个操作符，可以在where子句中表示左右外连接，(+)跟着从表

select su.f_sid,f_pid,f_id,f_count
from t_supplier su,t_sells se
where su.f_sid=se.f_sid(+);

二、子查询：      嵌套

        单行子查询              单个结果

        多行子查询              多个结果

        相关（关联）子查询

        不相关（不关联）子查询

     规则：(1)自身就是一个完整的查询，也就是说有select from 语句

               (2)不能包括order by 子句

                (3)必须在一组括号中

                (4)if将子查询在外部查询where或having，那么子句在比较运算符右边

--单行子查询

    --统计出所有比名为"screv"贵的零件

    --1、查询出名为"screv"的价格

select f_price 
from t_part
where f_pname="screv";

    --2、查询出价格高于10的零件信息

select *
from f_part
where f_price >10;

    --3、合起来

select *
from t_part
where f_pricr >(select f_price 
from t_part
where f_pname="screv");

--多行子查询    IN运算

    --在books表中查询一个类型最贵的图书价格

 select *
  from books
  where bretail in(select max(bretail) from books group by bcategory);

         --NOT IN  这种情况适用于子查询中返回多个值时的情况

  --统计哪些不销售任何零件的供应商
  select *
  from t_supplier
  where f_sid not in(select distinct f_sid
  from t_sells);

多行子查询：all和any 子查询

--查询零售价大于computer类的最贵的书>all
select *
from books
where bretail>all(select bretail 
from books
where bcategory='computer');

--另一种方法
select *
from books
where bretail>(select max(bretail)
from books
where bcategory='computer');

--查找价格低于computer类中最便宜的书<all
select *
from books
where bretail<all(select bretail 
from books
where bcategory='computer');

--另一种方法

select *

from books

where bretail<(select min(bretail) from bookswhere bcategory='computer');