/*
1:1
任选一个表当作主键表,另一个表当作外键表
并且外键列必须唯一
*/droptableifexists husband;droptableifexists wife;createtable wife(
id int auto_increment primarykey,
name varchar(100)
);createtable husband(
id int auto_increment primarykey,
name varchar(100),
wid intunique,
foreignkey(wid) references wife(id)
);/*
1:M
设置外键
*/createtable dept(
id int auto_increment primarykey,
name varchar(100)
);createtable emp(
id int auto_increment primarykey,
name varchar(100),
did int,
foreignkey(did) references dept(id) ondeletecascade
);/*
M:N
创建中间表
一般中间表也有用
*/createtable student(
id int auto_increment primarykey,
name varchar(100)
);createtable subject(
id int auto_increment primarykey,
name varchar(100)
);createtable student_subject(
id int auto_increment primarykey,
stuid int,
subid int,
foreignkey(stuid) references student(id),
foreignkey(subid) references subject(id)
);
2、连接查询
笛卡尔积
内连接
/*内连接*/select * from DEPT,EMP
where DEPT.DEPTNO = EMP.DEPTNO;select
EMP.EMPNO,EMP.ENAME,DEPT.DNAME
from
DEPT
innerjoin
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
leftjoin
EMP t2
on
t1.DEPTNO = t2.DEPTNO;
自连接
/*创建自关联的表*/createtable EMP2(
id int auto_increment primarykey,
name varchar(100),
mgr intforeignkey(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 leftjoin EMP t2
on t1.mgr = t2.empno;