MySQL多表

一、外键约束

多表的概念_外键约束：

1、外键：'从表中'引用'主表中'主键字段的字段。
2、外键字段的取值的特点：
    1).允许为NULL
    2).可以重复；
    3).所有的值，必须在主表的主键中出现。
3、创建外键约束：
    alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
    示例：
    ALTER TABLE emp ADD CONSTRAINT fk_emp FOREIGN KEY (deptId) REFERENCES dept (deptId);

二、多表查询:

1、内连接：也叫：等值连接。结果：两个表中的"等值部分"的记录
    A).隐式内连接【常用】：
        select 字段列表 from 表1 , 表2 where 两个表的等值条件 and 其它条件 and ...;
        示例：
        SELECT * FROM product,category WHERE product.cid = category.cid;
       1).from 表1,表2：表1和表2的顺序可以随意，会影响结果的列的排列顺序，但不会影响结果。
       2).where 两个表的等值条件：等号两边的左右位置也可以随意。
       3).select 字段列表：字段列表可以来自于两个表，如果两个表中的字段没用同名的，那么可以不使用"表名限定"，
          但如果两个表中有同名字段，必须使用"表名限定"；建议：使用"表名限定"。
          可以为"表"起个"别名"，之后可以使用"别名"来限定：
        SELECT p.pname,p.price,c.cname FROM product p,category c WHERE p.cid = c.cid;
       4).练习：查询电脑类商品，要同时显示类别名称：
        SELECT * FROM product p,category c WHERE p.cid = c.cid AND c.cname = '电脑';
    B).显示内连接：
        select 字段列表 from 表1 INNER JOIN 表2 ON 两个表的等值关系 where 其它条件 and ... and ...;
        示例：
        SELECT * FROM product p INNER JOIN category c ON p.cid = c.cid WHERE c.cname = '电脑';
    C).两种连接的写法说明：
        1).select * from 表1 , 表2 ON 两个表的等值关系;//错误
        2).select * from 表1 INNER JOIN 表2 where 两个表的等值关系;//OK的
2、外连接：
    A).左外连接：查询出左表的所有记录 + 右表的等值记录
        select 字段列表 from 表1 LEFT JOIN 表2 ON 两个表的等值关系;
        示例：
        SELECT * FROM product p LEFT JOIN category c ON p.cid = c.cid;
    B).右外连接：查询出右表的所有记录 + 左表的等值记录
        select 字段列表 from 表1 RIGHT JOIN 表2 ON 两个表的等值关系；
        示例：
        SELECT * FROM product p RIGHT JOIN category c ON p.cid = c.cid;

三、三表联查：

#隐式内连接：
    select * from 表1,中间表,表2 where 表1和中间表的等值关系 and 中间表和表2的等值关系；
    例如：
    SELECT * FROM orders o , order_product op , product p 
        WHERE o.oid = op.oid AND op.pid = p.pid 
        AND o.orderDate = '2018-01-30' 
        ORDER BY o.orderNO;
#显示内连接：
    select * from 表1 INNER JOIN 中间表 ON 中间表和表1的等值关系
              INNER JOIN 表2 ON 中间表和表2的等值关系
    例如：

    SELECT * FROM orders o INNER JOIN order_product op ON o.oid = op.oid
                   INNER JOIN product p ON op.pid = p.pid
                   WHERE o.orderDate = '2018-01-30' 
                   ORDER BY o.orderNO;
#左外连接
    select * from 表1 LEFT JOIN 中间表 ON 中间表和表1的等值关系
              LEFT JOIN 表2 ON 中间表和表2的等值关系
    例如：
SELECT * FROM orders o LEFT JOIN order_product op ON o.oid = op.oid
               LEFT JOIN product p ON op.pid = p.pid
               WHERE o.orderDate = '2018-01-30' ORDER BY o.orderNO;
#右外连接
    select * from 表1 RIGHT JOIN 中间表 ON 中间表和表1的等值关系
              RIGHT JOIN 表2 ON 中间表和表2的等值关系
    例如：
SELECT * FROM orders o RIGHT JOIN order_product op ON o.oid = op.oid
               RIGHT JOIN product p ON op.pid = p.pid
               ORDER BY o.orderNO;

四、五表联查：

#隐式内连接
SELECT u.uname,p.pname FROM USER u,user_role ur,role r,role_privilage rp,privilage p 
    WHERE u.uid = ur.userId AND
         ur.roleId = r.rid AND
         r.rid = rp.roleId AND
         rp.privilageId = p.pid
#显示内连接：
SELECT u.uname,p.pname FROM  USER u INNER JOIN user_role ur ON u.uid = ur.userId
        INNER JOIN role r ON ur.roleId = r.rid
        INNER JOIN role_privilage rp ON r.rid = rp.roleId
        INNER JOIN privilage p ON rp.privilageId = p.pid;
#左外连接
SELECT u.uname,p.pname FROM  USER u LEFT JOIN user_role ur ON u.uid = ur.userId
            LEFT JOIN role r ON ur.roleId = r.rid
            LEFT JOIN role_privilage rp ON r.rid = rp.roleId
            LEFT JOIN privilage p ON rp.privilageId = p.pid;
#右外连接
SELECT u.uname,p.pname FROM  USER u RIGHT JOIN user_role ur ON u.uid = ur.userId
            RIGHT JOIN role r ON ur.roleId = r.rid
            RIGHT JOIN role_privilage rp ON r.rid = rp.roleId
            RIGHT JOIN privilage p ON rp.privilageId = p.pid;

五、子查询：

1).查询价格高于"劲霸"的商品：
    select * from product where price > (select price from product where pname = '劲霸')
2).概念：写在一个查询内部的查询就是：子查询。
3).作用：
    1).作为外部查询的"查询条件"
    2).作为外部查询的"虚拟表"
4).示例：
    1).查询条件的示例：查询所有的电脑、服装类商品
        select * from product where cid in (select cid from category where cname in ('电脑','服装'));
    2).作为虚拟表：查询所有的电脑、服装类商品
        select * from product p,category c where p.cid = c.cid and c.cname in ('电脑','服装');
        使用子查询：
        select * from product p,(select * from category where cname in('电脑','服装')) c where p.cid = c.cid;