mysql数据库—映射关系和连接查询（内连接、外连接、自连接）

1、三种映射关系

/*
    1:1
    任选一个表当作主键表，另一个表当作外键表
    并且外键列必须唯一
*/
drop table if exists husband;
drop table if exists wife;

create table wife(
    id int auto_increment primary key,
    name varchar(100)
    );

create table husband(
    id int auto_increment primary key,
    name varchar(100),
    wid int unique,
    foreign key(wid) references wife(id) 
    );


/*
    1:M
    设置外键
*/
create table dept(
    id int auto_increment primary key,
    name varchar(100)
    );

create table emp(
    id int auto_increment primary key,
    name varchar(100),
    did int,
    foreign key(did) references dept(id) on delete cascade
    );


/*
    M:N
    创建中间表
    一般中间表也有用
*/
create table student(
    id int auto_increment primary key,
    name varchar(100)
    );

create table subject(
    id int auto_increment primary key,
    name varchar(100)
    );

create table student_subject(
    id int auto_increment primary key,
    stuid int,
    subid int,
    foreign key(stuid) references student(id), 
    foreign key(subid) references subject(id)
    );

2、连接查询

笛卡尔积

内连接

/*内连接*/
select * from DEPT,EMP
where DEPT.DEPTNO = EMP.DEPTNO;

select  
    EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from 
    DEPT
inner join 
    EMP
on 
    DEPT.DEPTNO = EMP.DEPTNO;


/*查询员工的编号，姓名，所在部门的名字*/
select 
    EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from 
    DEPT,EMP
where 
    DEPT.DEPTNO = EMP.DEPTNO;

select 
    EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from 
    DEPT,EMP
where 
    DEPT.DEPTNO = EMP.DEPTNO
and
    DEPT.DEPTNO = 10;

外连接

/*外连接*/
select  
    t2.EMPNO,t2.ENAME,t1.DEPTNO,t1.DNAME
from 
    DEPT t1
left join 
    EMP t2
on 
    t1.DEPTNO = t2.DEPTNO;

自连接

/*创建自关联的表*/
create table EMP2(
    id int auto_increment primary key,
    name varchar(100),
    mgr int foreign key(mgr) references EMP2(id)
);


/*查询员工的编号，姓名，上级名字*/
select t1.empno 员工的编号,t1.ename 员工的姓名,t2.ename 上级的姓名
from EMP t1,EMP t2
where t1.mgr = t2.empno

select t1.empno 员工的编号 ,t1.ename 员工的姓名,t2.ename 上级的姓名
from EMP t1 left join EMP t2
on t1.mgr = t2.empno;