MySQL---多表联合查询（上）（多表关系、外键约束、学生成绩多表关系、交叉连接查询）

1. 多表关系

MySQL多表之间的关系可以概括为：

一对一：

       比如：一个学生只有一张身份证；一张身份证只能对应一学生。

       实现原则：在任一表中添加唯一外键，指向另一方主键，确保一对一关系。

        一般一对一关系很少见，遇到一对一关系的表最好是合并表。

一对多：

        比如：一个部门有多个员工，一个员工只能对应一个部门

        实现原则：在多的一方建立外键，指向一的一方的主键

多对多：

        比如：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

        实现原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多关

系，拆成一对多关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

定义一个外键时，需要遵守下列规则：

主表必须已经存在于数据库中，或者是当前正在创建的表。

必须为主表定义主键。

主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主

键中，这个外键的内容就是正确的。

 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

外键中列的数目必须和主表的主键中列的数目相同。

外键中列的数据类型必须和主表主键中对应列的数据类型相同。

2. 外键约束相关操作

创建外键约束：

-- 方式1：在创建表时创建外键
-- [constraint <外键名>] foreign key 字段名 [，字段名2，…] 
-- references <主表名> 主键列1 [，主键--列2，…]create database mydb3; use mydb3;-- 创建部门表create table if not exists dept(deptno varchar(20) primary key ,  -- 部门号name varchar(20) -- 部门名字
);create table if not exists emp(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int,  -- 员工年龄dept_id varchar(20),  -- 员工所属部门constraint emp_fk foreign key (dept_id) references dept (deptno) –- 外键约束
);

-- 方式2：在创建表结束后，设置外键约束
-- alter table <数据表名> add constraint <外键名> foreign key(<列名>) references 
-- <主表名> (<列名>);-- 创建部门表
create table if not exists dept2(deptno varchar(20) primary key ,  -- 部门号name varchar(20) -- 部门名字
);-- 创建员工表
create table if not exists emp2(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int,  -- 员工年龄dept_id varchar(20)  -- 员工所属部门);-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);

在外键约束下的数据操作：

 -- 1、添加主表数据-- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部’);

-- 2、添加从表数据-- 注意给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003'); 
insert into emp values('8','天山童姥',60, '1005');  -- 不可以

-- 3、删除数据/*注意：1：主表的数据被从表依赖时，不能删除，否则可以删除2: 从表的数据可以随便删除*/
delete from dept where deptno = '1001'; -- 不可以删除
delete from dept where deptno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除

删除外键：

-- 语法：alter table <表名> drop foreign key <外键约束名>;alter table emp2 drop foreign key dept_id_fk;

3. 建立一个多对多关系（学生成绩）

 

-- 学生表和课程表(多对多)-- 创建学生表student(左侧主表)create table if not exists student(sid int primary key auto_increment,name varchar(20),age int,gender varchar(20));-- 创建课程表course(右侧主表)create table course(cid  int primary key auto_increment,cidname varchar(20));-- 创建中间表student_course/score(从表)create table score(sid int,cid int,score double);-- 建立外键约束(2次)alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);-- 给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
-- 给中间表添加数据
insert into score values(1,1),(1,2),(2,1),(2,3),(3,2),(3,3);

4. 交叉连接查询

数据准备：

use mydb3;-- 创建部门表
create table if not exists dept3(deptno varchar(20) primary key ,  -- 部门号name varchar(20) -- 部门名字
);-- 创建员工表
create table if not exists emp3(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int,  -- 员工年龄dept_id varchar(20)  -- 员工所属部门
);-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');-- 给emp表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

交叉连接查询：

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积

笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配

假如A表有m行数据，B表有n行数据，则返回m*n行数据

笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

-- 语法：select * from 表1,表2,表3….; -- 交叉连接查询select * from dept3,emp3;

结果：

 （日常美图时间）