MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

每一次写博客对技术都会有更深入的理解 积少成多 百天计划我也想看看自己有多少成长 祝君好运 工作顺利

本文正在参加「技术专题19期 漫谈数据库技术」活动

概要

最近在做数据汇总 数据比较复杂 发觉自己的SQL知识有点不够稳固了 打算一步步整理一下数据库常用的相关知识点 温故而知新 有些东西写出来好像记下来更清晰

基础了解

在实际开发中数据表之间存在着各种关联关系 比较常用的是Mysql中三种关联关系

多对一

多对一(亦称为一对多)是数据表中最常见的一种关系。例如：员工与部门之间的关系，一个部门可以有多个员工；而一个员工不能属于多个部门只属于某个部门。在多对一的表关系 中，应将外键建在多的一方否则会造成数据的冗余。

多对多

多对多是数据表中常见的一种关系。例如：学生与老师之间的关系，一个学生可以有多个老师而且一个老师有多个学生。通常情况下，为了实现这种关系需要定义一张中间表(亦称为连接表)该表会存在两个外键分别参照老师表和学生表。

一对一

在开发过程中，一对一的关联关系在数据库中并不常见；因为以这种方式存储的信息通常会放在同一张表中。 接下来，我们来学习在一对多的关联关系中如果添加和删除数据

详细整理

多对一

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

先准备一些测试数据

-- 创建班级表
CREATE TABLE class(
    cid int(4) NOT NULL PRIMARY KEY,
    cname varchar(30) 
);

-- 创建学生表
CREATE TABLE student(
    sid int(8) NOT NULL PRIMARY KEY,
    sname varchar(30),
    classid int(8) NOT NULL
);

-- 为学生表添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_classid FOREIGN KEY(classid) REFERENCES class(cid);
-- 向班级表插入数据
INSERT INTO class(cid,cname)VALUES(1,'Java');
INSERT INTO class(cid,cname)VALUES(2,'Python');

-- 向学生表插入数据
INSERT INTO student(sid,sname,classid)VALUES(1,'tome',1);
INSERT INTO student(sid,sname,classid)VALUES(2,'lucy',1);
INSERT INTO student(sid,sname,classid)VALUES(3,'lili',2);
INSERT INTO student(sid,sname,classid)VALUES(4,'domi',2);

一对一

在实际开发中应用不多，因为一对一可以创建成一张表

建表原则： 外键唯一， 主表的主键和从表的唯一外键，形成主外键关系， 外键唯一用UNIQUE修饰

多对多

这个是比较常见东西 我们经常使用这个思想 建表原则： 需要创建第三行表，中间表中至少有两个字段，这两个字段分别作为外键，指向各自一方的主键

表连接

自连接

主要就是自己连接自己 能够将表中数据重新组合 进一步处理 如果这个时候出现连个关联的数据在一张表里 我们就可以使用自连接

问题

找出下表中超过经理收入的员工

准备表

• id 是主键
• name 是姓名
• salary 是工资
• managerId 经理

CREATE TABLE Employee(
id BIGINT PRIMARY KEY auto_increment,
name VARCHAR(30) not null,
salary DECIMAL,
managerId BIGINT
)

解决方案

经理和员工在同一张表 我们可以使用自连接解决问题

select e1.name as employee
    from employee e1, employee e2
        where e1.managerid = e2.id 
            and e1.salary > e2.salary;

内连接

两张表连接

多张表连接语法

select a.name,b.name,c.name from A a inner join B b on a.b_id = b.id inner join C c on b.c_id = c.id where a.age > 0 and b.age >0 and c.age > 0 

左连接

MySQL LEFT JOIN子句允许您从两个或多个数据库表查询数据。LEFT JOIN子句是SELECT语句的可选部分，出现在FROM子句之后。

我们假设要从两个表student和class查询数据。以下语句说明了连接两个表的LEFT JOIN子句的语法 我们都知道左连接就是左表为主表 返回左表中的所有数据 如果右表没有匹配就是空

上面的表如果通过左连接进行查询 返回的结果就会如下图

SQL语句

select  s.id,s.sname,c.cname from student s left join class c on s.classid = c.cid

小总结

看一下 内连接和左连接的区别

• 内连接 只返回相交的数据 学生表中的 变成派大星和学生4 没有对应的班级所以就没有返回这两条数据
• 左连接 可以看到返回了左表中的所有数据 变成派大星 和学生4 没有对应的班级 所以班级信息就返回空

右连接

这个看字面意思就知道是和连接相反 返回右表中的所有信息 SQL

select  s.id,s.sname,c.cname from student s right join class c on s.classid = c.cid

可以看出 返回右表中所有信息 左表中没有匹配的数据就是空 但是还是会显示右表中的所有数据

交叉连接

MySQL CROSS JOIN 用于组合两个或多个表的所有可能性，并返回包含所有贡献表的每一行的结果。CROSS JOIN 也称为 CARTESIAN JOIN，它提供所有关联表的笛卡尔积。笛卡尔积可以解释为第一个表中存在的所有行乘以第二个表中存在的所有行。它类似于 Inner Join，其中连接条件在此子句中不可用。 SQL

select c.cname,s.sname from class c CROSS join student s

交叉连接也可以带 on 不带的话就是返回两表中的数据

总结

自连接

说到底就是多张表都是同一张表添加一定条件的限制出来的数据。这简单理解吧

左连接

简单点来说就是最后的结果不管怎么样，左边的表格全部显示，右边的表格根据条件显示 当左表比较重要的时候就使用左连接

右连接

简单点来说就是最后的结果不管怎么样，右边的表格全部显示左右边的表格根据条件显示 与上相反

交叉连接

返回两个或者多个表中所有数据 这个使用场景还没用到过