学习笔记：SQL语句-数据表操作(多表)

一、多表关系

在数据库设计上，表关系分为三种：一对多、多对多、一对一 。

（1） 一对多
    场景：班级和学生
    说明：一个班级对应多个学生，一个学生只能对应一个班级

 （2）多对多
    场景：学生和课程 
    说明：一个学生对应多个课程，一个课程对应多个学生 

（3）一对一
    场景：人和身份证号
    说明：一个人只有唯一的身份证号，身份证号也只代表一个人

那么在数据表中怎么表示表之间的联系呢，一般使用外键，外键的作用是关联两张表，约束两张表的数据，具体情况具体分析。

二、一对多

建表原则：在表设计时，我们把一的一方称为主表，多的一方称为从表 。

在从表中添加一列指向主表的主键列，新加列名字一般是（主表名_主键名）  

将新建的这一列称为外键列，它的作用是为了建立当前表和其他表的关联关系，一张表中，允许有多个外键列

比如：班级和学生。

-- 创建班级表
create table class(
    id int primary key,
    name varchar(30)
);

-- 创建学生表
create table student(
    id int primary key,
    name varchar(30),
    class_id int
);

-- 插入数据
insert into class values (1,'JAVAEE166'),(2,'JAVAEE167');
insert into student values (1,'张三',1),(2,'李四',2),(3,'王五',2);

外键约束

-- 创建表的时候添加
    create table 表名(
        列名  列类型,
                [constraint 约束名: 外键列_fk]  foreign key (列名)  references  主表(主键)
            )
-- 创建表之后单独添加
    alter table 表名 add [constraint 约束名]  foreign key (列名)  references  主表(主键)

由于上方已经创好了表,所以下面演示创建表之后单独添加外键，学生和班级，学生为多的一方面称为从表，将主表班级表的id起名为class_id作为从表的外键。

alter table student add constraint class_id_fk foreign key(class_id) references class(id);

使用外键会有一些缺点，这里只是学习一下思想还是推荐使用逻辑外键

 物理外键: 使用foreign key定义外键关联另外一张表
 问题：
    1. 影响增、删、改的效率（需要检查外键关系）
    2. 仅用于单节点数据库，不适用与分布式、集群场景
    3. 容易引发数据库的死锁问题，消耗性能

 逻辑外键：在业务层逻辑中，可以解决外键关联

注意：想要删除主表中的某一条数据，一定要看从表中对应的有没有引用主表的这条数据，不然是无法删除成功的。

三、多对多

建表原则：新建一张中间表，作为两张主表的从表存在

在中间表中创建两个外键，分别指向两张主表的主键  

多对多一个关系本质上是由两个一对多拼接而成

例如：学生和课程

-- 创建学生表
create table student(
        id int primary key,
        name varchar(30)
);

-- 创建课程表
create table course(
       id int primary key,
       name varchar(30)
);

-- 建立中间表
create  table student_course(
        id int primary key auto_increment,
        student_id int,
        course_id int,
        constraint student_id_fk foreign key (student_id) references student(id), -- 外键约束
        constraint course_id_fk foreign key (course_id) references course(id) -- 外键约束
);

四、一对一

建表原则： 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

例如：用户和身份证

-- 创建用户表
create table tb_user(
        id int unsigned  primary key auto_increment comment 'ID',
        name varchar(10) not null comment '姓名',
        gender tinyint unsigned not null comment '性别, 1 男  2 女',
        phone char(11) comment '手机号',
        degree varchar(10) comment '学历'
) comment '用户基本信息表';

-- 用户身份信息表
create table tb_user_card(
     id int unsigned  primary key auto_increment comment 'ID',
     nationality varchar(10) not null comment '民族',
     birthday date not null comment '生日',
     idcard char(18) not null comment '身份证号',
     issued varchar(20) not null comment '签发机关',
     expire_begin date not null comment '有效期限-开始',
     expire_end date comment '有效期限-结束',
     user_id int unsigned not null unique comment '用户ID', -- 外键,指向用户表主键, 必须有唯一约束,才能保证1对1
     constraint fk_user_id foreign key (user_id) references tb_user(id) -- 外键约束
) comment '用户身份信息表';

总的来说，一对多：在多的一方添加外键，关联另外一方的主键。

                  多对多：通过中间表来维护，中间表的两个外键，分别关联另外两张表的主键。

                  一对一：任意一方，添加外键，关联另外一方的主键; 外键列还应该有唯一约束。

五、多表查询

所谓的多表联合查询就是使用一条SQL语句将多张表的数据一起查询展示出来 ，这里使用的是逻辑外键。

-- 创建部门表（主表）
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),
  gender CHAR(1),  -- 性别（sex）
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT -- 外键字段
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('张三','男',7200,'2020-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('李四','男',3600,'2018-12-02',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('王五','女',6666,'2023-03-14',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('赵六','男',4500,'2017-03-04',null);

1.交叉连接（笛卡尔积）

使用左表中的每一条数据分别去连接右表中的每一条数据, 将所有的连接结果都展示出来

语法：select * from  左表,右表

根据上面的表信息

select * from emp,dept

2.内连接

使用左表中的每一条数据分别去连接右表中的每一条数据, 仅仅显示出匹配成功的那部分

语法：隐式内连接: select * from  左表,右表 where 连接条件
           显示内连接: select * from 左表 [inner] join 右表 on 连接条件

两种连接的区别：1.语法：隐式内连接使用where 条件进行连接,显示内连接使用inner join  on 进行连接。

2.执行顺序：隐式内连接会先做笛卡尔积,再过滤不匹配的行.显示内连接先匹配两表的连接条件,然后返回匹配的行

3.性能：数据量大的情况下,显示内连接的性能更好

例子：

 -- 隐式内连接
    select * from emp,dept where emp.dept_id = dept.id;
    select * from emp e,dept d where e.dept_id = d.id;-- 推荐大家使用别名的形式

    -- 显示内连接
    select * from emp e inner join dept d on e.dept_id = d.id; -- 记住这个
    select * from emp e join dept d on e.dept_id = d.id;

3.外连接

（1）左外连接

首先要显示出左表的全部, 然后使用连接条件匹配右表,能匹配中的就显示,匹配不中的显示为null。

语法：select * from 左表 left [outer] join 右表 on 连接条件

例子： 

	select * from emp e left outer join dept d on e.dept_id = d.id;

（2）右外连接

首先要显示出右表的全部, 然后使用连接条件匹配左表,能匹配中的就显示,匹配不中的显示为null

语法：select * from 左表 right outer join 右表 on 连接条件

例子： 

select * from emp e right outer join dept d on e.dept_id = d.id;

4.子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

语法：select  *  from   t1   where  column1 =  ( select  column1  from  t2  … );

子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。

子查询根据查询结果不同，作用不同

标量子查询：子查询返回的结果为单个值

列子查询：子查询返回的结果为一列

表子查询：子查询返回的结果为一张表

（1）标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= >  < 等          语法：select 字段列表 from 表 where 字段名 = (子查询)

-- 1: 查询工资小于平均工资的员工有哪些？(子查询结果为一个值)
-- 1 查询平均工资
select avg(salary) from emp; -- 6391.5
-- 2 查询谁的工资小于上面的数
select * from emp where salary < 6391.5;
-- 3. 合并
select * from emp where salary < (select avg(salary) from emp);

（2）列子查询

子查询返回的结果是一列（可以是多行）

常用的操作符：in  、not in等     语法：select 字段列表 from 表 where 字段名 in (子查询) 

-- 2: 查询工资大于5000的员工，所在部门的名字 (子查询结果为多个值)
-- 1 查询工资大于5000的员工的部门id
select distinct dept_id from emp where salary > 5000; -- 1 2
-- 2 查询这些部门id的名称
select name from dept where id in (1,2);
-- 合并
select name from dept where id in (select distinct dept_id from emp where salary > 5000);

（3） 表子查询

子查询返回的结果是多行多列，常作为临时表

常用的操作符：as 临时表          语法：select 字段列表 from (子查询) as 临时表 join 表 on 条件

-- 3: 查询出2019年以后入职的员工信息，包括部门信息 (子查询结果为一张表)

-- 查询出2019年以后入职的员工信息
select * from emp where join_date >= '2019-01-01';

-- 使用上面的结果连接部门表,获取部门信息
select * from
    (select * from emp where join_date >= '2019-01-01')
as t1 left outer join dept d on t1.dept_id = d.id

以上是我学习到的内容，有什么不对欢迎指正哦！