MySQL一对多的操作
一、一对多的关系与一对多类似,通过外键关联,只是在创建外键关联的时候不要加unique限制
1、创建
class表create table class( id int primary key auto_increment comment "班级id", class_name varchar (100) not null comment "班级名称", create_at datetime default now() );2、创建
student表create table student( id int primary key auto_increment comment "用户id", name varchar(100) not null comment "名字", age int not null comment "年龄", create_at timestamp default current_timestamp comment "创建时间", class_id int comment "班级id", foreign key(class_id) references class(id) on delete cascade );3、插入数据
insert into class(class_name) values ("一班"), ("二班"), ("三班"); insert into student(name, age, class_id) values ("张三", 10, 1), ("李四", 22, 1), ("王五", 25, 1), ("马六", 40, 2);
二、查询
1、笛卡尔积的查询方式
所谓的笛卡尔积是指两个表的数量相✖️
mysql> select * from class,student; +----+------------+---------------------+----+--------+-----+---------------------+----------+ | id | class_name | create_at | id | name | age | create_at | class_id | +----+------------+---------------------+----+--------+-----+---------------------+----------+ | 1 | 一班 | 2018-06-30 10:26:19 | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | | 2 | 二班 | 2018-06-30 10:26:19 | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | | 3 | 三班 | 2018-06-30 10:26:19 | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | | 2 | 二班 | 2018-06-30 10:26:19 | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | | 3 | 三班 | 2018-06-30 10:26:19 | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | | 2 | 二班 | 2018-06-30 10:26:19 | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | | 3 | 三班 | 2018-06-30 10:26:19 | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | | 2 | 二班 | 2018-06-30 10:26:19 | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | | 3 | 三班 | 2018-06-30 10:26:19 | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | +----+------------+---------------------+----+--------+-----+---------------------+----------+ 12 rows in set (0.00 sec) mysql> select count(*) from class; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) mysql>2、内连接的查询方式(根据一个字段来匹配)
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果
mysql> select * from class, student where class.id = student.class_id; +----+------------+---------------------+----+--------+-----+---------------------+----------+ | id | class_name | create_at | id | name | age | create_at | class_id | +----+------------+---------------------+----+--------+-----+---------------------+----------+ | 1 | 一班 | 2018-06-30 10:26:19 | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | | 2 | 二班 | 2018-06-30 10:26:19 | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | +----+------------+---------------------+----+--------+-----+---------------------+----------+ 4 rows in set (0.00 sec) mysql>3、左查询
上面明显我是有班级表中有三班字段,但是内连接查询不到(没有学生)
mysql> select * from class left join student on class.id = student.class_id; +----+------------+---------------------+------+--------+------+---------------------+----------+ | id | class_name | create_at | id | name | age | create_at | class_id | +----+------------+---------------------+------+--------+------+---------------------+----------+ | 1 | 一班 | 2018-06-30 10:26:19 | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | | 2 | 二班 | 2018-06-30 10:26:19 | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | | 3 | 三班 | 2018-06-30 10:26:19 | NULL | NULL | NULL | NULL | NULL | +----+------------+---------------------+------+--------+------+---------------------+----------+ 5 rows in set (0.00 sec) mysql>4、右边查询
mysql> select * from student right join class on class_id = class.id; +------+--------+------+---------------------+----------+----+------------+---------------------+ | id | name | age | create_at | class_id | id | class_name | create_at | +------+--------+------+---------------------+----------+----+------------+---------------------+ | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | 1 | 一班 | 2018-06-30 10:26:19 | | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | 1 | 一班 | 2018-06-30 10:26:19 | | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | 1 | 一班 | 2018-06-30 10:26:19 | | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | 2 | 二班 | 2018-06-30 10:26:19 | | NULL | NULL | NULL | NULL | NULL | 3 | 三班 | 2018-06-30 10:26:19 | +------+--------+------+---------------------+----------+----+------------+---------------------+ 5 rows in set (0.00 sec) mysql>5、全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
- mysql不支持全外连接 full JOIN
- mysql可以使用此种方式间接实现全外连接
mysql> select * from class left join student on class.id = student.class_id union select * from student right join class on student.class_id = class.id; +------+------------+---------------------+---------------------+--------+------+---------------------+---------------------+ | id | class_name | create_at | id | name | age | create_at | class_id | +------+------------+---------------------+---------------------+--------+------+---------------------+---------------------+ | 1 | 一班 | 2018-06-30 10:26:19 | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | | 1 | 一班 | 2018-06-30 10:26:19 | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | | 2 | 二班 | 2018-06-30 10:26:19 | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | | 3 | 三班 | 2018-06-30 10:26:19 | NULL | NULL | NULL | NULL | NULL | | 1 | 张三 | 10 | 2018-06-30 10:26:35 | 1 | 1 | 一班 | 2018-06-30 10:26:19 | | 2 | 李四 | 22 | 2018-06-30 10:26:35 | 1 | 1 | 一班 | 2018-06-30 10:26:19 | | 3 | 王五 | 25 | 2018-06-30 10:26:35 | 1 | 1 | 一班 | 2018-06-30 10:26:19 | | 4 | 马六 | 40 | 2018-06-30 10:26:35 | 2 | 2 | 二班 | 2018-06-30 10:26:19 | | NULL | NULL | NULL | NULL | NULL | 3 | 三班 | 2018-06-30 10:26:19 | +------+------------+---------------------+---------------------+--------+------+---------------------+---------------------+ 10 rows in set (0.00 sec) mysql>
三、表关系之间不通过约束来关联
外键关联毕竟都有点损耗性能,使用左右连接查询的时候,只要根据id匹配,不需要真的外键关联起来
1、创建一个部门表
create table dep( id int primary key auto_increment comment "部门id", name varchar (100) unique not null comment "部门名字", create_at datetime default now() );2、创建一个员工表
create table emp( id int primary key auto_increment comment "员工id", name varchar (100) unique not null comment "员工名字", age int comment "员工年龄", dep_id int comment "员工所属部门id", create_at timestamp default current_timestamp comment "创建时间" );3、插入数据
insert into dep(name) values ("财务部"), ("技术部"), ("人事部"), ("行政部"); insert into emp(name, age, dep_id) values ("A", 20, 1), ("B", 18, 2), ("C", 25, 3); insert into emp(name, age) values ("D", 20);4、笛卡尔积查询方式
mysql> select * from dep, emp; +----+-----------+---------------------+----+------+------+--------+---------------------+ | id | name | create_at | id | name | age | dep_id | create_at | +----+-----------+---------------------+----+------+------+--------+---------------------+ | 1 | 财务部 | 2018-06-30 10:56:42 | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | | 2 | 技术部 | 2018-06-30 10:56:42 | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | | 3 | 人事部 | 2018-06-30 10:56:42 | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | | 4 | 行政部 | 2018-06-30 10:56:42 | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | | 1 | 财务部 | 2018-06-30 10:56:42 | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | | 2 | 技术部 | 2018-06-30 10:56:42 | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | | 3 | 人事部 | 2018-06-30 10:56:42 | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | | 4 | 行政部 | 2018-06-30 10:56:42 | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | | 1 | 财务部 | 2018-06-30 10:56:42 | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | | 2 | 技术部 | 2018-06-30 10:56:42 | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | | 3 | 人事部 | 2018-06-30 10:56:42 | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | | 4 | 行政部 | 2018-06-30 10:56:42 | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | | 1 | 财务部 | 2018-06-30 10:56:42 | 4 | D | 20 | NULL | 2018-06-30 10:57:23 | | 2 | 技术部 | 2018-06-30 10:56:42 | 4 | D | 20 | NULL | 2018-06-30 10:57:23 | | 3 | 人事部 | 2018-06-30 10:56:42 | 4 | D | 20 | NULL | 2018-06-30 10:57:23 | | 4 | 行政部 | 2018-06-30 10:56:42 | 4 | D | 20 | NULL | 2018-06-30 10:57:23 | +----+-----------+---------------------+----+------+------+--------+---------------------+ 16 rows in set (0.00 sec) mysql>5、内查询(从左边的表查询到部门)
mysql> select * from emp,dep where emp.dep_id = dep.id; +----+------+------+--------+---------------------+----+-----------+---------------------+ | id | name | age | dep_id | create_at | id | name | create_at | +----+------+------+--------+---------------------+----+-----------+---------------------+ | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | 1 | 财务部 | 2018-06-30 10:56:42 | | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | 2 | 技术部 | 2018-06-30 10:56:42 | | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | 3 | 人事部 | 2018-06-30 10:56:42 | +----+------+------+--------+---------------------+----+-----------+---------------------+ 3 rows in set (0.00 sec) mysql>6、左查询(从左边表开始查询[会查询出全部的])
mysql> select * from emp left join dep on emp.dep_id = dep.id; +----+------+------+--------+---------------------+------+-----------+---------------------+ | id | name | age | dep_id | create_at | id | name | create_at | +----+------+------+--------+---------------------+------+-----------+---------------------+ | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | 1 | 财务部 | 2018-06-30 10:56:42 | | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | 2 | 技术部 | 2018-06-30 10:56:42 | | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | 3 | 人事部 | 2018-06-30 10:56:42 | | 4 | D | 20 | NULL | 2018-06-30 10:57:23 | NULL | NULL | NULL | +----+------+------+--------+---------------------+------+-----------+---------------------+ 4 rows in set (0.00 sec) mysql>7、右查询(先查询右边的表)
mysql> select * from emp right join dep on emp.dep_id = dep.id; +------+------+------+--------+---------------------+----+-----------+---------------------+ | id | name | age | dep_id | create_at | id | name | create_at | +------+------+------+--------+---------------------+----+-----------+---------------------+ | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | 1 | 财务部 | 2018-06-30 10:56:42 | | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | 2 | 技术部 | 2018-06-30 10:56:42 | | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | 3 | 人事部 | 2018-06-30 10:56:42 | | NULL | NULL | NULL | NULL | NULL | 4 | 行政部 | 2018-06-30 10:56:42 | +------+------+------+--------+---------------------+----+-----------+---------------------+ 4 rows in set (0.00 sec) mysql>8、全查询
mysql> select * from emp right join dep on emp.dep_id = dep.id union select * from emp left join dep on emp.dep_id = dep.id; +------+------+------+--------+---------------------+------+-----------+---------------------+ | id | name | age | dep_id | create_at | id | name | create_at | +------+------+------+--------+---------------------+------+-----------+---------------------+ | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | 1 | 财务部 | 2018-06-30 10:56:42 | | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | 2 | 技术部 | 2018-06-30 10:56:42 | | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | 3 | 人事部 | 2018-06-30 10:56:42 | | NULL | NULL | NULL | NULL | NULL | 4 | 行政部 | 2018-06-30 10:56:42 | | 4 | D | 20 | NULL | 2018-06-30 10:57:23 | NULL | NULL | NULL | +------+------+------+--------+---------------------+------+-----------+---------------------+ 5 rows in set (0.00 sec) mysql>
四、多表查询之复合条件查询
1、查询年龄大于20岁的所在的部门
mysql> select * from emp,dep where emp.dep_id = dep.id and emp.age > 20; +----+------+------+--------+---------------------+----+-----------+---------------------+ | id | name | age | dep_id | create_at | id | name | create_at | +----+------+------+--------+---------------------+----+-----------+---------------------+ | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | 3 | 人事部 | 2018-06-30 10:56:42 | +----+------+------+--------+---------------------+----+-----------+---------------------+ 1 row in set (0.00 sec) mysql>2、查询部门所在员工,并根据升序排序
mysql> select * from emp, dep where emp.dep_id = dep.id order by age asc; +----+------+------+--------+---------------------+----+-----------+---------------------+ | id | name | age | dep_id | create_at | id | name | create_at | +----+------+------+--------+---------------------+----+-----------+---------------------+ | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | 2 | 技术部 | 2018-06-30 10:56:42 | | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | 1 | 财务部 | 2018-06-30 10:56:42 | | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | 3 | 人事部 | 2018-06-30 10:56:42 | +----+------+------+--------+---------------------+----+-----------+---------------------+ 3 rows in set (0.00 sec) mysql>
五、多表查询之子表查询
- 1、子查询是将一根查询语句嵌套在另外一个查询语句中
- 2、内层查询语句的查询结果,可以为外层查询语句提供查询条件
- 3、子查询中可以包含：
IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字 - 4、还可以包含比较运算符：= 、 !=、> 、<等
5、案例
mysql> select * from emp where dep_id in (select id from dep); +----+------+------+--------+---------------------+ | id | name | age | dep_id | create_at | +----+------+------+--------+---------------------+ | 1 | A | 20 | 1 | 2018-06-30 10:56:50 | | 2 | B | 18 | 2 | 2018-06-30 10:56:50 | | 3 | C | 25 | 3 | 2018-06-30 10:56:50 | +----+------+------+--------+---------------------+ 3 rows in set (0.00 sec) mysql>