SQL-基础多表查询(datagrip开发工具）

一.多表关系

概念：在项目的开发中进行的数据库结构设计时，根据业务的需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以以各表结构之间存在着各种联系。

表联系分类：

1. 一对多：主要有部门与员工之间的关系，主要通过多的一方建立外键，直向一的一方的主键。
2. 多对多：主要有学生与课程关系(一个学生可以选修多门课程，一门课程也可以供多个学生选择)，主要通过建立第三张中间表，中间表至少包括两个外键，分别关联两方主键。
3.    一对一：用户与用户详情的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中。主要通过在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(unique)。

二.多表查询

概念：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况(多表查询需要消除无效的笛卡尔积)

多表查询分类：

  连接查询：1.内连接：相当于查询A,B交集部分数据。

                    2.外连接：

                             左外连接：查询左表所有数据，以及两张表交集部分数据。

                             右外连接：查询右表所有数据，以及两张表交集部分数据。

                    3.自连接：当前表与自身的连接查询，自连接必须使用表别名。

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

内连接查询语法：

1.   隐式内连接：select 字段列表 from 表1，表2 where 条件

--查询每一个员工及所属的部门
--表结构：emp2，dept
--连接条件：emp2.dept_id = dept.id

select emp2.name ,dept.name from emp2 , dept where emp2.dept_id = dept.id;

1.   显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件....;

--inner join...on....

select e.name, d.name from emp2 e inner join dept d on  e.dept_id = d.id

结果：

外连接查询语法：

1. 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件......(相当于查询表1(左表)的所有数据和表1和表2 交集的数据）

用于将员工表和部门表连接在一起

--查询emp2表所有数据，和对应部门直接的信息
--表结构：emp2，dept
--连接条件：emp2.dept_id = dept.id

select e.*, d.name from emp2 e left join dept d on e.dept_id = d.id;

1. 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件.....（相当于查询表1(右表)的所有数据和表1和表2 交集的数据）

--查询emp2表所有数据，和对应部门直接的信息(右外连接)
--表结构：emp2，dept
--连接条件：emp2.dept_id = dept.id

select d.*, e.* from emp2 e right join dept d on e.dept_id = d.id;

 

自连接的语法结构：

1.   自连接：select 字段列表 from 表A 别名A join 表A 别名B on 条件......

--自连接
--查询员工及所属领导的名字(将一张员工表看作两张表相互连接)
--表结构：emp2
select a.name, b.name from emp2 a, emp2 b where a.managerid = b.id;

联合查询union(去重)-union all(会将查询到的所有数据全部合并)

1. 联合查询：就是把多次查询的结果合并起来，形成一个新的查询结果集。(查询的多张表的列数必须保持一致，字段类型也需要保持一致）

   select 字段列表 from 表a
   union [all]
   select 字段列表 from 表b
   

子查询：嵌套查询

1. 语法：select * from t1 where column = (select column 1 from t2);-----外部语句可以是insert/update/delete/select的任意一个
2. 查询结果不同：标量子查询(单个数据)，列子查询(查询一列)，行子查询(查询一行)，表子查询(查询多行多列)。
3. 根据子查询位置，分为：where之后，from之后，select之后。

标量子查询：返回的结果是单个值（数字，字符串，日期等）。

--1.查询‘销售部’的所有员工
--首先查询‘销售部’部门id
select id from dept where name = '销售部';
--根据部门id查询员工信息
select * from emp2 where dept_id = 4;/
select * from emp2 where dept_id =(select id from dept where name = '销售部');/一样的/

--2.查询员工入职信息
--查询相应员工入职日期
select entrydate from emp2 where name = '猪八戒';

--查询指定入职时间后入值的员工
select * from emp2 where entrydate > (select entrydate from emp2 where name = '猪八戒');

  列子查询：返回的数据是一列，可以是多行。

--列子查询
--1.查询‘销售部’和‘技术部’的所有员工信息
--首先查询部门id
select id from dept where name = '销售部' or name = '技术部';
--根据id查询员工信息
select * from emp2 where dept_id in (select id from dept where name = '销售部' or name = '技术部');

--2.查询研发所有人工资都高的员工信息
--首先查询研发部id
select id from dept where name = '研发部';
--查询财务部工资
select salary from emp2 where dept_id = (select id from dept where name = '研务部');
--比研发部工资高的员工
select * from emp2 where salary > all(select salary from emp2 where dept_id = (select id from dept where name = '研务部'));

行子查询：返回数据为一行 

--3.查询唐僧薪资和直属领导
--首先查询唐僧相关信息
select salary, managerid from emp2 where name = '唐僧';
--查询和唐僧相同的员工信息
select * from emp2 where (salary,managerid) = (select salary, managerid from emp2 where name = '唐僧');

 表子查询：返回结果多行多列