欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-25 20:57:34
...

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、连接查询

笛卡尔积

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

内连接

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

/*内连接*/
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;

外连接

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

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

自连接

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

/*创建自关联的表*/
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;