小型电商网站模板,网站在百度的图标显示不正常显示,百度地图api wordpress,一般网站用什么数据库文章目录 前言多表关系一对一关系一对多/多对一关系多对多关系 外键约束创建外键约束插入数据删除带有外键约束的表的数据删除外键约束 多表联合查询数据准备交叉连接查询内连接查询外连接查询左外连接查询右外连接查询满外连接查询 子查询子查询关键字ALL 关键字ANY 和 SOME 关… 文章目录 前言多表关系一对一关系一对多/多对一关系多对多关系 外键约束创建外键约束插入数据删除带有外键约束的表的数据删除外键约束 多表联合查询数据准备交叉连接查询内连接查询外连接查询左外连接查询右外连接查询满外连接查询 子查询子查询关键字ALL 关键字ANY 和 SOME 关键字IN 关键字EXISTS 关键字 自关联查询 前言
在日常的数据库使用过程中我们处理的数据不只是单张表而是需要我们同时处理多个表的数据那么今天我将为大家分享关于MySQL的多表操作。
多表关系
在学习多表操作之前我们需要知道有哪些多表关系一对一关系、一对多关系/多对一关系、多对多关系。
一对一关系
一对一关系是指一个表的一条记录只能对应另外一个表的一条记录它们之间通过共同的主键或唯一键进行关联。 一对多/多对一关系
多表一对多或多对一关系是指一个表的一条记录可以对应另一个表的多条记录它们之间通过共同的主键或外键进行关联。
多对多关系
多表多对多关系是指两个或多个表之间相互关联并且每个表的记录可以对应其他表的多个记录。处理多表多对多关系的常见方法是使用中间表关联表来连接关系。 外键约束 MySQL外键约束是一种数据库约束用于确保表之间的关系的完整性。它定义了一个字段或一组字段在一个表中的值必须与另一个表中的值保持一致或满足特定条件。 外键约束通过指定一个外键关系将一个表的字段称为外键与另一个表的主键或唯一键称为参照键进行关联。这样如果某个表中的外键值发生改变MySQL会自动检查参照表中是否存在匹配的键值以保持数据的一致性。
外键约束有以下几个作用
强制引用完整性外键约束确保表中的数据在引用时存在于关联表中。维护数据一致性外键约束防止在关联表中删除或修改数据时引发不一致的结果。提供查询优化MySQL优化器可以使用外键关系来加速查询操作。级联操作外键约束可以定义级联操作当参照表中的数据更新或删除时自动对关联表中的数据进行相应操作。
MySQL外键约束具有以下特点 强制引用完整性外键约束确保引用表中的数据在被引用表中存在防止出现无效的引用。这可以保证数据的一致性和完整性。 自动验证和维护关系MySQL会自动验证和维护外键约束确保关联的数据被正确更新、插入或删除。如果违反了外键约束MySQL会报错并拒绝执行操作。 支持级联操作外键约束可以定义级联操作在参照表中进行删除或更新操作时自动对关联表中相关的数据进行相应操作。例如可以实现级联删除删除参照表中的数据时同时删除关联表中的相关数据。 优化查询性能MySQL优化器可以使用外键约束来优化查询操作。通过正确设置外键关系MySQL可以更有效地执行JOIN操作提高查询性能。 可以禁用和启用需要时可以禁用或启用外键约束以允许执行某些需要暂时违反外键约束的操作。这个特性在某些情况下很有用但需要谨慎使用以防止数据不一致。 对数据完整性的依赖使用外键约束可以在数据库级别维护数据完整性而不是依赖于应用程序。这样可以减少错误和数据不一致的可能性使数据库更加可靠。
创建外键约束
MySQL 创建外键约束有两种方式1.在创建表的时候创建外键约束create table 表名(列名1 类型,列名2 类型,constraint 外键名 foreign key(列名) references 主表(主表列名); 2.在创建表之后创建外键约束alter table 表名 add constraint 外键名 foreign key(列名) referrnces 主表(主表列名);
我们在创建表之后添加外键约束的前提是从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。
在创建表的时候创建外键约束
create table class(class_id int primary key,name varchar(10)); -- 主表
insert into class values(1,高三1班), (2,高三2班),(3,高三3班);create table student(class_id int,student_id int,name varchar(20),gender varchar(10),constraint student_fk foreign key(class_id) references class(class_id));创建表之后添加外键约束
create table class(class_id int primary key,name varchar(10)); -- 主表
insert into class values(1,高三1班), (2,高三2班),(3,高三3班);create table student(class_id int,student_id int,name varchar(20),gender varchar(10));alter table student
add constraint student_fk
foreign key(class_id) references class(class_id);插入数据
当我们插入数据的时候如果我们先向主表中插入数据的时候不需要注意什么但是当我们向从表中插入数据的时候我们需要注意插入的记录的外键列的数据在主表的主键列中是否存在如果存在则插入成功否则就插入失败。所以建议插入数据的时候先向主表中插入数据然后再向从表中插入数据。
insert into student values(1,2301,张三,男);insert into student values(4,2302,王五,男);如果我们在新建外键约束的时候表中已经存在了数据该怎么办
create table student(class_id int,student_id int,name varchar(20),gender varchar(10));insert into student values(1,2301,张三,男),(3,2302,王五,男);
-- 我们插入的数据都是与主键列中的数据相对应alter table student
add constraint student_fk
foreign key(class_id) references class(class_id);上面存在的数据外键列与主键列中的数据是相对应的所以插入成功。
create table student(class_id int,student_id int,name varchar(20),gender varchar(10));insert into student values(1,2301,张三,男),(4,2302,王五,男);
-- 这里class_id 4 在主键列中并不存在我们看看是否能
alter table student
add constraint student_fk
foreign key(class_id) references class(class_id);这里class_id 4 在主键列中并不存在所以插入失败。
删除带有外键约束的表的数据
如果我们需要删除带有外键约束的表中的数据该怎么办呢
删除从表中的数据可以随便删除当我们删除主表中的数据时我们需要注意我们删除的记录的主键列还被外键列所依赖时不能删除。 此时主键列 1 还被外键列依赖着我们删除试试。
delete from class where class_id 1;删除失败所以我们删除主表的数据时需要考虑该记录的主键列是否还被外键列依赖着。在删除数据的时候建议先删除从表中的数据然后再删除主表中未被依赖的数据
删除外键约束
但我们不需要外键约束的时候我们也可以删除外键约束。alter table 表名 drop foreign key 外键名; 此时外键约束已删除。
多表联合查询
多表查询是 MySQL 操作中最重要也是使用最多的操作所以我们需要学好 MySQL 多表查询操作。 MySQL多表查询是指在一个查询语句中涉及到多个表通过指定表之间的连接条件关联条件来获取联合数据的过程。多表查询可以用来检索、过滤和组合来自多个表的数据。 MySQL多表操作主要包括
交叉连接查询内连接查询 隐式内连接SQL92标准 显式内连接SQL99标准 外连接 左外连接 右外连接 满外连接 子查询自关联查询
并且我们实行的是查询操作外键约束不会对多表查询造成影响。
数据准备
我们为后面的多表查询准备数据。
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,人事部);-- 给emp3表添加数据
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);交叉连接查询 MySQL交叉连接查询Cross Join是一种用于从两个或多个表中返回所有可能的组合的查询方式。它会返回左边表的每一行与右边表的每一行的所有组合并且不使用任何连接条件。因此交叉连接查询会产生一个结果集其行数等于左边表的行数乘以右边表的行数 select * from 表1,表2 交叉连接查询实际上就是多个表之间进行 笛卡尔积 。 两个分别有m和n条记录的表进行笛卡尔积之后会出现 m*n 条记录。
select * from dept3,emp3;但是我们看到笛卡尔积之后的表的数据会发现有很多不适合的记录所以我们还需要对笛卡尔积生成的表做出筛选。
内连接查询 MySQL内连接查询Inner Join是一种用于从两个或多个表中返回满足连接条件的记录集的查询方式。内连接基于指定的连接条件将两个表中的行进行匹配并返回匹配的行。 内连接是最常用的连接类型它可以帮助我们在多个表之间建立关联关系并获取相关的数据。内连接的结果集只包含匹配的行因此可以根据连接条件进行数据的关联和过滤提供更具体和有意义的结果。
隐式内连接select * from 表A,表B where 条件; 显式内连接select * from 表A inner join B on 条件; inner 可省略。
MySQL还支持两张以上的表进行多表操作select * from 表A join B on 条件 join C on 条件...; 隐式内连接
select * from dept3,emp3 where dept3.deptno emp3.dept_id;
-- 如果两张表的列相同可使用 表名.列名 来区分显式内连接
select * from dept3 inner join emp3 on dept3.deptno emp3.dept_id;外连接查询 MySQL外连接Outer Join是一种用于从两个或多个表中返回满足连接条件以及一些或全部不满足连接条件的记录的查询方式。外连接可以帮助我们获取主表和连接表之间的关系并包含未匹配的行。 外连接查询分为
左外连接查询右外连接查询满外连接查询
左外连接查询 MySQL左外连接查询Left Outer Join是一种通过从左边表主表返回所有行以及满足连接条件的右边表连接表的匹配行来获取表之间关联数据的查询方式。左外连接会保留左边表中的所有行即使在右边表中没有匹配的行。 左外连接会将左边表的每一行与右边表的匹配行进行连接并返回匹配的结果集。如果右边表中没有匹配的行则返回NULL值。
select * from 表A left outer join 表B on 条件;
select * from dept3 left outer join emp3 on dept3.deptno emp3.dept_id;这里因为人事部没有员工但因为是左外连接要显示左表中的所有数据所以人事部中的数据就用 NULL 填充。
右外连接查询 MySQL右外连接查询Right Outer Join是一种通过从右边表连接表返回所有行以及满足连接条件的左边表主表的匹配行来获取表之间关联数据的查询方式。右外连接会保留右边表中的所有行即使在左边表中没有匹配的行。 右外连接会将右边表的每一行与左边表的匹配行进行连接并返回匹配的结果集。如果左边表中没有匹配的行则返回NULL值。
select * from 表A right outer join 表B on 条件; select * from dept3 right outer join emp3 on dept3.deptno emp3.dept_id;1005 没有对应的部门所以使用 NULL 填充。
左外连接可以根据两个表互换位置来实现相互之间的转换。
select * from emp3 left outer join dept3 on dept3.deptno emp3.dept_id;满外连接查询 满外连接是一种同时返回左边表和右边表中的所有行以及满足连接条件的匹配行的查询方式。它能够获取左右两个表的全部数据并关联它们。 满外连接实际上就是将右外连接的数据拼接到左外连接数据的下面或者是将左外连接的数据拼接到右外连接数据的下面。
但是MySQL不支持满外连接 full outer join 我们可以用 union 来实现满外连接 。并且 union 满外连接还有两种方式左外连接 union 右外连接; 去重满外连接 左外连接 union all 右外连接; 不去重满外连接。 去重满外连接
select * from dept3 left outer join emp3 on dept3.deptno emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno emp3.dept_id;不去重满外连接
select * from dept3 left outer join emp3 on dept3.deptno emp3.dept_id
union all
select * from dept3 right outer join emp3 on dept3.deptno emp3.dept_id;子查询 MySQL子查询Subquery是指在一个SQL查询中嵌套另一个完整的SQL查询。子查询可以像普通的查询一样包含SELECT、FROM、WHERE等子句并且可以返回一个结果集这个结果集可以在外部查询中直接使用。 子查询通常用于将复杂的查询分解为更小的、可管理的查询块并通过嵌套来达到更灵活和精确的查询目的。它可以嵌套在SELECT、FROM、WHERE等子句中用于过滤、排序、计算和联合数据。
子查询可以返回的数据类型分为四种
单列单行返回的是一个具体列的内容可以理解为一个单值数据。单行多列返回一行数据中多个列的内容多行多列返回多行记录之中同一列的内容相当于给出了一个操作范围。多行多列查询返回的结果是一张临时表。
如果我们需要知道某个部分有哪些人我们是不是需要先知道这个部门所代表的部门编号因为员工信息表中存储的是部门编号知道了部门编号之后我们才能找出有哪些人。
-- 查询销售部有哪些人
select deptno from dept3 where name 销售部;当我们知道了部门编号之后我们再在员工表中找出部门编号为1002的人的信息。
select ename from emp3 where dept_id 1002;因为 select deptno from dept3 where name 销售部; 的结果为1002所以我们可以直接把这个查询嵌套进 select ename from emp3 where dept_id 1002; 中也就是 select ename from emp3 where dept_id (select deptno from dept3 where name 销售部);
select ename from emp3
where dept_id (select deptno from dept3 where name 销售部); -- 返回单行单列查询研发部和销售部人的信息。
select eid,ename from emp3
where dept_id
in (select deptno from dept3 where name in (研发部,销售部));-- 查询研发部30岁以下的员工信息包括员工号员工名字部门名字
select eid,ename,name from (select * from emp3 where age 30) t1
join (select * from dept3 where name 研发部) t2
on t1.dept_id t2.deptno;因为最终要求显示员工号、员工名字、部门名字这些数据分别在两个表中所以我们需要将两个表联合起来并且需要对每张表中的数据做出判断。 子查询关键字
在子查询中有一些常用的逻辑关键字
ALL 关键字ANY 关键字SOME 关键字IN 关键字EXISTS 关键字
ALL 关键字
在MySQL中关键字ALL可以用于子查询中用于比较子查询返回的结果与外部查询条件进行比较。
关键字ALL与子查询配合使用时表示外部查询条件必须与子查询返回的所有值进行比较只有当外部查询条件大于或小于子查询中的所有值时才被视为满足条件。
select ... from ... where c all(查询语句);
-- 查询员工表中年龄大于1003部门所有员工的年龄的信息
select * from emp3 where age all(select age from emp3 where dept_id 1003);-- 查询不属于任何部门的员工的信息
select * from emp3 where dept_id ! all(select deptno from dept3);ANY 和 SOME 关键字
在MySQL中ANY和SOME关键字可以与子查询一起使用用于比较外部查询条件与子查询返回的结果集中的任意一个值进行比较。
这两个关键字的作用是类似的它们都表示外部查询条件只需与子查询返回的结果集中的任意一个值相匹配即可判断为满足条件。
select ... from ... where c any/some(查询语句);
-- 查询年龄大于1003部门任何一个员工的员工信息
select * from emp3 where age any(select age from emp3 where dept_id 1003);IN 关键字
IN关键字用于检查外部查询条件是否与子查询结果集中的任意一个值相匹配。如果外部查询条件与子查询结果集中的某个值匹配则被视为满足条件。
select ... from ... where c in(查询语句);
-- 查询部门为研发部和销售部的员工的信息
select * from emp3
where dept_id
in (select deptno from dept3 where name 研发部 or name 销售部);EXISTS 关键字 EXISTS关键字用于判断子查询是否有结果如果存在结果则被视为满足条件会执行前面的查询语句如果子查询没有结果则被视为不满足条件不执行前面的查询。 select ... from ... where exists(查询语句);
假设我们要查询年龄大于60岁的员工的信息。
-- 查询年龄大于60岁的员工信息
select * from emp3 where exists (select * from emp3 where age 60);会发现这里将所有员工的信息都显示出来了这是为什么呢因为在 EXISTS 后面的子查询中查找的对象是emp3 的整个表所以每次的查询都有结果所以emp3中的所有信息都打印了那么我们应该怎么办呢
我们可以形象的将MySQL的查询过程看作是一条记录一条记录的筛选所以我们可以使用表别名然后用表别名来判断每一条记录的员工的年龄是否大于60。
-- 查询年龄大于60岁的员工信息
select * from emp3 t where exists (select * from emp3 where t.age 60);使用exists的情况大多也可使用in但是我们推荐使用exists因为MySQL底层exists做了优化查询效率更高。
自关联查询 MySQL自关联查询是指在查询中使用同一个表进行连接操作创建一个虚拟的关联。在自关联查询中可以将表视为两个或多个不同的表并在查询中引用它们。 自关联查询通常涉及到在查询中使用表的别名以便可以引用同一个表的不同实例。这样可以通过列与自身的比较来建立关联关系。
自关联查询在处理具有层级结构、关系链等的数据时非常有用常见的应用场景包括获取管理层级关系、查找相关的记录、获取亲属关系等。
MySQL自查询必须得使用别名否则就会出现错误
我们准备一些自关联用到的数据。
create table t_sanguo(eid int primary key,ename varchar(20),manager_id int,foreign key (manager_id) references t_sanguo (eid));insert into t_sanguo values(1,刘协,NULL),(2,刘备,1),(3,关羽,2),(4,张飞,2),(5,曹操,1),(6,许褚,5),(7,典韦,1),(8,孙权,1),(9,周瑜,8),(10,鲁肃,8);我们需要显示出每个人对应的上级信息。
select t1.ename,t2.ename manager
from t_sanguo t1 join t_sanguo t2
on t1.manager_id t2.eid; 我们还应该显示出刘协的上级信息所以我们使用左外连接。
select t1.ename,t2.ename manager
from t_sanguo t1 left join t_sanguo t2
on t1.manager_id t2.eid;
