表与表之间的关系
11.1为什么要拆分表
拆分表的目的：
为了解决数据冗余问题，可以减少重复数据的存储，表的结构也更加清晰了
拆分之后：
在某些业务下，需要查询多个表，速度减低了
空间和时间
11.2表与表之间的关系
11.2.1 一对一
人与身份证的关系：
注意：
（1）一对一不存在数据冗余问题、可以不拆分表
（2）什么场景需要拆分？
用户信息+身份证+...-> 表中的字段太多了
11.2.2 一对多
最最常见的关系：
学生和成绩的关系：一个学生有多个成绩
部门和员工的关系：一个部门有多个员工
这种关系怎么去建立，怎么去表达
在“多”的一方，存储“一”的主键id
在成绩表中存储用户的id
在员工表中存储部门的id
建表：使用主外键实现
11.2.3多对多
常见的案列：
老师与学生的关系：一个老师对应多位学生，一个学生对应多位老师
用户与商品的关系：
用户和权限的关系：
这种关系怎么去建立，怎么去表达：借助第三张表
用户和商品：订单（谁）（用户id）什么时候买了什么商品（商品id）
|
订单Id |
用户id |
商品id |
|
1001 |
1 |
6 |
|
1002 |
1 |
5 |
|
1003 |
3 |
6 |
用户和权限的关系：谁拥有什么权限
|
Id |
用户id |
权限id |
|
1001 |
1 |
1001 |
|
1002 |
1 |
1003 |
|
1003 |
3 |
1001 |
11.3 多表查询
多表查询有如下几种：
（1）合并结果集：union、union all
（2）连接查询（列的合并）：
内连接：
外连接：
左外连接
右外连接
全连接
自然连接
（3）子查询
11.4合并结果集（行连接）
2个结果集（表）合并到一起
要求：2个结果集的列数（数据类型）应该要相同
11.4.1 union all（全部）
将2个表的结果直接放在一起。不做任何的处理
Select * from t1
Union all
Select * from t2;
行增加了 列没有改变
11.4.2 union
将2个表的结果直接放在一起。相同的数据只留下一个（去重）
Select * from t1
Union
Select * from t2;
11.5连接查询（列连接）
问题：学生表有十条数据，成绩表有十条数据
连接之后有多少条数据（笛卡尔积）10*10
连接查询会产生笛卡尔积；假设集合A={a,b}，集合B={c,d}
2个集合的笛卡尔积{(a,c),(a,d),(b,c),(b,d)}
Select * from emp，dept；
但是：
（1）从数据结果来讲，有很多无用的数据
（2）通过where条件过滤无用的数据：通过主外键来清楚无用数据
使用主外键清楚无用数据
Select * from emp,dept where emp.deptno=dept.deptno;
11.5.1 内连接 inner join
上面的写法其实就是内连接，但是不在SQL标准
标准写法：
select
*
from
emp e
inner join
dept d
On
e.deptno=d.deptno;
（1）inner 可以省略
（2）内连接的特点：查询的条件必须满足条件，不满足条件的数据将会丢失
部门是40的数据就没有了，员工emp中没有40这个部门的人
11.5.2 外连接 outer join （outer可以省略）
左连接：
A left B： A 左连接 B 表； A表示主表
B left A： B 左连接 A表； B表示主表
右连接：
A right B： A 右连接 B 表；B表示主表
B right A： B 右连接 A 表；A表示主表
特点：主表的数据全部都在，
然后去匹配 非主表的 数据 ，匹配不上为null
员工表emp是主表，去匹配dept
select
*
from
emp e
Left join
dept d
on
e.deptno=d.deptno;
部门表dept是主表，去匹配员工表 emp
select
*
from
emp e
Right join
dept d
on
e.deptno=d.deptno;
面试题：左连接和右连接的区别
11.5.3自然连接 natural join
2张表中，有字段名字和数据类型一样 会自然作为连接的条件
Dept表中 emp都有deptno
select
*
from
emp e
natural join
dept d
;
11.6子查询
一个select查询中包含另外一个完整的select语句
子查询就是嵌套查询，一个select嵌套着一个或者多个select
11.6.1 子查询出现的位置
From后：作为一个表
Where：作为条件
In：在什么里面
Any：有一个
All：所有
##子查询
###1.查询工资 高于 JONES 的员工
（1）先查JONES的工资。
select sal from emp where ename = 'JONES';
（2）他的工资作为条件
select * from emp where sal > (第一步得到工资)
（3）合并
select
*
from
emp
where
sal > (select sal from emp where ename = 'JONES');
##查询与SCOTT同一个部门的员工
select
*
from
emp
where
deptno = (select deptno from emp where ename = 'SCOTT');
##2.工资高于30号部门所有人的员工信息
##（1）查询30部门的所有人的工资
select sal from emp where deptno = 30;
##（2）查询高于30部门所有员工的信息
select
*
from
emp
where
sal > ALL (第一步所有的值)
##（3）合并
select
*
from
emp
where
sal > ALL (select sal from emp where deptno = 30)；
=============多行多列============
##查询 工作（job） 和 工资(sal) 与 MARTIN 完全一样的员工信息
#(1)查看MARTIN的 工作和工资
select job,sal from emp where ename = "MARTIN";
+----------+---------+
| job | sal |
+----------+---------+
| SALESMAN | 1250.00 |
+----------+---------+
| SALESMAN | 150.00 |
+----------+---------+
#(2)根据员工信息
select
*
from
emp
where
(job,sal) in (第一步结果)
##（3）合并之后
select
*
from
emp
where
(job,sal) in (select job,sal from emp where ename = "MARTIN") ;
==================多行单列--------
##找出有员工的部门信息
##（1）找出所有 有员工的部门ID
select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 30 |
| 20 |
| 10 |
+--------+
##（2）找部门信息
select * from dept where deptno in （第一步的结果）
##（3）合并
select * from dept where deptno in (select distinct deptno from emp);
##查询有2个以上直接下属的员工信息
##(1)找出各个领导的下属人数
select mgr, count(*) as cnt from emp group by mgr ;
+------+-----+
| mgr | cnt |
+------+-----+
| NULL | 1 |
| 7566 | 2 |
| 7698 | 5 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 3 |
+------+-----+
select
t.mgr,t.cnt
from
(
select mgr, count(*) as cnt from emp group by mgr
) t
where
t.cnt > 2;
+------+-----+
| mgr | cnt |
+------+-----+
| 7698 | 5 |
| 7839 | 3 |
+------+-----+
更简单方式：select mgr, count(*) as cnt from emp group by mgr having cnt > 2;
作业：
1.内存和硬盘的区别
2.IOE（马云，2016）、棱镜门事件
3.阿里云---王坚
4.Un
11.1为什么要拆分表
拆分表的目的：
为了解决数据冗余问题，可以减少重复数据的存储，表的结构也更加清晰了
拆分之后：
在某些业务下，需要查询多个表，速度减低了
空间和时间
11.2表与表之间的关系
11.2.1 一对一
人与身份证的关系：
注意：
（1）一对一不存在数据冗余问题、可以不拆分表
（2）什么场景需要拆分？
用户信息+身份证+...-> 表中的字段太多了
11.2.2 一对多
最最常见的关系：
学生和成绩的关系：一个学生有多个成绩
部门和员工的关系：一个部门有多个员工
这种关系怎么去建立，怎么去表达
在“多”的一方，存储“一”的主键id
在成绩表中存储用户的id
在员工表中存储部门的id
建表：使用主外键实现
11.2.3多对多
常见的案列：
老师与学生的关系：一个老师对应多位学生，一个学生对应多位老师
用户与商品的关系：
用户和权限的关系：
这种关系怎么去建立，怎么去表达：借助第三张表
用户和商品：订单（谁）（用户id）什么时候买了什么商品（商品id）
|
订单Id |
用户id |
商品id |
|
1001 |
1 |
6 |
|
1002 |
1 |
5 |
|
1003 |
3 |
6 |
用户和权限的关系：谁拥有什么权限
|
Id |
用户id |
权限id |
|
1001 |
1 |
1001 |
|
1002 |
1 |
1003 |
|
1003 |
3 |
1001 |
11.3 多表查询
多表查询有如下几种：
（1）合并结果集：union、union all
（2）连接查询（列的合并）：
内连接：
外连接：
左外连接
右外连接
全连接
自然连接
（3）子查询
11.4合并结果集（行连接）
2个结果集（表）合并到一起
要求：2个结果集的列数（数据类型）应该要相同
11.4.1 union all（全部）
将2个表的结果直接放在一起。不做任何的处理
Select * from t1
Union all
Select * from t2;
行增加了 列没有改变
11.4.2 union
将2个表的结果直接放在一起。相同的数据只留下一个（去重）
Select * from t1
Union
Select * from t2;
11.5连接查询（列连接）
问题：学生表有十条数据，成绩表有十条数据
连接之后有多少条数据（笛卡尔积）10*10
连接查询会产生笛卡尔积；假设集合A={a,b}，集合B={c,d}
2个集合的笛卡尔积{(a,c),(a,d),(b,c),(b,d)}
Select * from emp，dept；
但是：
（1）从数据结果来讲，有很多无用的数据
（2）通过where条件过滤无用的数据：通过主外键来清楚无用数据
使用主外键清楚无用数据
Select * from emp,dept where emp.deptno=dept.deptno;
11.5.1 内连接 inner join
上面的写法其实就是内连接，但是不在SQL标准
标准写法：
select
*
from
emp e
inner join
dept d
On
e.deptno=d.deptno;
（1）inner 可以省略
（2）内连接的特点：查询的条件必须满足条件，不满足条件的数据将会丢失
部门是40的数据就没有了，员工emp中没有40这个部门的人
11.5.2 外连接 outer join （outer可以省略）
左连接：
A left B： A 左连接 B 表； A表示主表
B left A： B 左连接 A表； B表示主表
右连接：
A right B： A 右连接 B 表；B表示主表
B right A： B 右连接 A 表；A表示主表
特点：主表的数据全部都在，
然后去匹配 非主表的 数据 ，匹配不上为null
员工表emp是主表，去匹配dept
select
*
from
emp e
Left join
dept d
on
e.deptno=d.deptno;
部门表dept是主表，去匹配员工表 emp
select
*
from
emp e
Right join
dept d
on
e.deptno=d.deptno;
面试题：左连接和右连接的区别
11.5.3自然连接 natural join
2张表中，有字段名字和数据类型一样 会自然作为连接的条件
Dept表中 emp都有deptno
select
*
from
emp e
natural join
dept d
;
11.6子查询
一个select查询中包含另外一个完整的select语句
子查询就是嵌套查询，一个select嵌套着一个或者多个select
11.6.1 子查询出现的位置
From后：作为一个表
Where：作为条件
In：在什么里面
Any：有一个
All：所有
##子查询
###1.查询工资 高于 JONES 的员工
（1）先查JONES的工资。
select sal from emp where ename = 'JONES';
（2）他的工资作为条件
select * from emp where sal > (第一步得到工资)
（3）合并
select
*
from
emp
where
sal > (select sal from emp where ename = 'JONES');
##查询与SCOTT同一个部门的员工
select
*
from
emp
where
deptno = (select deptno from emp where ename = 'SCOTT');
##2.工资高于30号部门所有人的员工信息
##（1）查询30部门的所有人的工资
select sal from emp where deptno = 30;
##（2）查询高于30部门所有员工的信息
select
*
from
emp
where
sal > ALL (第一步所有的值)
##（3）合并
select
*
from
emp
where
sal > ALL (select sal from emp where deptno = 30)；
=============多行多列============
##查询 工作（job） 和 工资(sal) 与 MARTIN 完全一样的员工信息
#(1)查看MARTIN的 工作和工资
select job,sal from emp where ename = "MARTIN";
+----------+---------+
| job | sal |
+----------+---------+
| SALESMAN | 1250.00 |
+----------+---------+
| SALESMAN | 150.00 |
+----------+---------+
#(2)根据员工信息
select
*
from
emp
where
(job,sal) in (第一步结果)
##（3）合并之后
select
*
from
emp
where
(job,sal) in (select job,sal from emp where ename = "MARTIN") ;
==================多行单列--------
##找出有员工的部门信息
##（1）找出所有 有员工的部门ID
select distinct deptno from emp;
+--------+
| deptno |
+--------+
| 30 |
| 20 |
| 10 |
+--------+
##（2）找部门信息
select * from dept where deptno in （第一步的结果）
##（3）合并
select * from dept where deptno in (select distinct deptno from emp);
##查询有2个以上直接下属的员工信息
##(1)找出各个领导的下属人数
select mgr, count(*) as cnt from emp group by mgr ;
+------+-----+
| mgr | cnt |
+------+-----+
| NULL | 1 |
| 7566 | 2 |
| 7698 | 5 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 3 |
+------+-----+
select
t.mgr,t.cnt
from
(
select mgr, count(*) as cnt from emp group by mgr
) t
where
t.cnt > 2;
+------+-----+
| mgr | cnt |
+------+-----+
| 7698 | 5 |
| 7839 | 3 |
+------+-----+
更简单方式：select mgr, count(*) as cnt from emp group by mgr having cnt > 2;
作业：
1.内存和硬盘的区别
2.IOE（马云，2016）、棱镜门事件
3.阿里云---王坚
4.Unix Linux
5.Svn git
6.隐式转换 ：就是不需要加强制转换, 系统会自动做这个操作
隐式转换有四种常见的使用场景：
将某一类型转换成预期类型
类型增强与扩展
模拟新的语法
类型类
7.Id号，不一定非得是数字
8.Sql的性能优化 （sql语句的优化背十个）
避免使用*，如果表中有100个字段 全部列出
模糊查询时候，避免使用通配符开头（避免全表扫描）
- 怎么获取中间的内容
SUBSTRING_INDEX(SUBSTRING_INDEX(原字
ix Linux
5.Svn git
6.隐式转换 ：就是不需要加强制转换, 系统会自动做这个操作
隐式转换有四种常见的使用场景：
将某一类型转换成预期类型
类型增强与扩展
模拟新的语法
类型类
7.Id号，不一定非得是数字
8.Sql的性能优化 （sql语句的优化背十个）
避免使用*，如果表中有100个字段 全部列出
模糊查询时候，避免使用通配符开头（避免全表扫描）
- 怎么获取中间的内容
SUBSTRING_INDEX(SUBSTRING_INDEX(原字
浙公网安备 33010602011771号