多表设计和多表查询

多表查询
1. 添加外键约束
    1.1 在创建表的过程中申明外键
    外键是指引用另一个表的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的链接
    
    create table dept(
        id int primary key auto_increment,
        name varchar(40)
    );

    insert into dept values (null,'财务部'),(null,'人事部'),(null,'行政部'),(null,'科技部');

    create table emp(
        id int primary key auto_increment,
        name varchar(40),
        dept_id int,
        foreign key (id) references dept(id)
    );

    insert into emp values(null,'奥巴马',1),(null,'萨达姆',2),(null,'哈利波特',3),(null,'火车王',4);


    1.2 在表已经存在的情况下声明外键
    ALTER TABLE 表名 add constraint 外键约束名 foreign key (外键字段) REFERENCES 外表表名(主键字段名);

    create table dept(
        id int primary key auto_increment,
        name varchar(40)
    );

    create table emp(
        id int primary key auto_increment,
        name varchar(40),
        dept_id int
    );

    ALTER TABLE emp ADD CONSTRAINT FK_ID foreign key (dept_id) REFERENCES dept(id);

2. 删除外键约束
    alter table 表名 drop foreign key 外键名;
    alter table emp drop foreign key emp_ibfk_1;
    
3. 多表设计，设计和操作关联表
   3.1 一对多；
    我们可以在多的一方设计外键保存少的一方的主键
   3.2 一对一：
    可以在任意一方设计外键保存另一方的主键
   3.3 多对多
    需要设计第三张表，保存两张表的主键作为外键，存储两张表主键的对应关系
   添加数据：只有主表数据存在的情况下，附表的数据才能插入
   
4. 多表查询
    create table dept(
        id int primary key auto_increment,
        name varchar(40)
    );

    create table emp(
        id int primary key auto_increment,
        name varchar(40),
        dept_id int
    );

    insert into dept values (null,'财务部'),(null,'人事部'),(null,'行政部'),(null,'科技部'),(null,'销售部');    
    insert into emp values(null,'奥巴马',1),(null,'萨达姆',2),(null,'哈利波特',4),(null,'火车王',4),(null,'火车王',6);

    笛卡儿积查询；是两张表相乘的结果，如果左边有m条记录，右边有n条记录，则查询出m*n条记录，这样的结果往往包含大量的错误的数据，所以这种方式我们通常不使用
    select * from dept,emp;

    内链接查询：查询出左边表有且右边表也有的记录
        select * from dept,emp where dept.id = emp.dept_id;
        select * from dept inner join emp on dept_id = emp.dept_id;
    外链接查询
        左外链接查询：在内链接的基础上增加了左边表有而右边表没有的记录
        select * from dept left join emp on dept_id = emp.dept_id;
        右外链接查询：在内链接的基础上增加了右边表有而左边表么有的记录
        select * from dept right join emp on dept_id = emp.dept_id;
        全链接查询：在内链接的基础上增加了左边表有而右边表没有的 和 右边表有而左边表么有的记录
        select * from dept full join emp on dept dept_id = emp.dept_id; #mysql不支持全外链接

        select * from dept left join emp on dept.id = emp.dept_id
        union
        select * from dept right join emp on dept.id = emp.dept_id;   #mysql中可以用union操作左外+右外，就模拟一个全外链接查询
    
    ~ 查询4号部门的名称和其中的员工的名称
        select dept.name 部门名称,emp.name 学生名称 from dept inner join emp on dept.id = emp.dept_id where dept.id=4;