MySQL—连接查询、子查询

一、连接查询

表的关系简介

        现实生活中，实体与实体之间是有关系的，比如：部门和员工，老师和学生等。那么在设计表的时候，我们应该体现出表与表之间的这种关系。

一对多

一对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品

一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

多对多

多对多（m ：n）例如：老师和学生，学生和课程，用户和角色

多对多关系建表原则：需要创建第三张表，中间表至少两个字段，这两个字段分别作为外键指向各自一方的主键。

SQL实现代码：

-- 学生表
create table stu(
  `学号` int primary key,
  `姓名` varchar(30)
);

insert into stu values(1,'小明');
insert into stu values(2,'小龙');
insert into stu values(3,'小红');

-- 课程表
create table course(
  `课程号` int primary key,
  `课程名` varchar(30)
);

insert into course values(1,'Java');
insert into course values(2,'C++');
insert into course values(3,'MySQL');


-- 学生选课表(中间表)
create table stu_course(
	s_id int,
	c_id int,
	foreign key(s_id)  references stu(`学号`),
	foreign key(c_id)  references course(`课程号`)
);


-- 代表小明选修了Java课程
insert into stu_course values(1,1);

-- 代表小明选修了MySQL课程
insert into stu_course values(1,3);

-- 代表小龙选修了Java课程
insert into stu_course values(2,1);

-- 代表小龙选修了C++课程
insert into stu_course values(2,2);

-- 代表小红选修了C++课程
insert into stu_course values(3,2);

-- 代表小红选修了Java课程
insert into stu_course values(3,1);

 一对一

一对一（1  ：1）在实际开发中用的并不多，因为一对一可以创建成一张表。

SQL实现代码：

-- 先创建主表

-- 学生表
create table s2 (
	`学号` int primary key,
	`姓名` varchar(20)
);

-- 个人信息表
create table info(
	`编号` int primary key, -- 主键
	age int,
	addr varchar(20),
	foreign key (`编号`) references s2(`学号`)	-- 创建外键
);

insert into s2 values(1,'小明');
insert into s2 values(2,'小龙');
insert into s2 values(3,'小红');

-- 小明的年龄为23岁,籍贯为江西
insert into info values(1,23,'江西');

-- 小龙的年龄为25岁,籍贯为广西
insert into info values(2,25,'广西');

-- 小红的年龄为34岁,籍贯为山西
insert into info values(3,34,'山西');

表关系小结

表与表的关系	关系的维护
一对多	通过表中外键来维护
多对多	通过中间表，将两个一对多加到一起变成了一个多对多
一对一	1.添加外键约束 2.从表的主键又是外键 可以简化成一张表

笛卡尔积

数据准备：

create database db03;

use db03;

-- 创建部门表
create table dept(
	id int primary key auto_increment,
	name varchar(20)
);

insert into dept(name) values ('研发部'),('销售部'),('财务部');

-- 创建员工表
create table emp (
	id int primary key auto_increment,
	name varchar(10),
	addr varchar(30), 
	age int, 
	sex char(1), 
	dept_id int,
	foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);

INSERT INTO `emp` VALUES (1, '小明', '南昌', 24, '男', 1);
INSERT INTO `emp` VALUES (2, '小红', '九江', 20, '女', 1);
INSERT INTO `emp` VALUES (3, '小兰', '抚州', 19, '女', 2);
INSERT INTO `emp`