表间关系

子程序（嵌套查询） subquery

3.2.1 概念

在查询SQL语句中含有另外查询
a.子查询返回一个值
b.子查询返回多个值，多条查询

–列出tony所在部门的所有人员
外键：两张表的关联，部门表dept，员工表emp
在员工表中增加一个字段，就是部门表的主键
在这个字段的值必须引用部门表deptno的外键约束(sql规定)

根据deptno部门编号去部门表dept查询部门名称

#根据deptno部门编号去部门表dept查询部门名称
SELECT * FROM emp 
WHERE dept_id=
(
 SELECT dept_id FROM emp WHERE NAME='齐雷'
);

用子查询实现，列出部门2 所在员工的信息

#用子查询实现，列出部门2 所在员工的信息
SELECT * FROM emp
WHERE deptno IN  #in子查询
(
SELECT deptno FROM emp
WHERE deptno = 2 OR deptno =1
);

#用子查询实现，列出部门为 所在的分析员和办事员的信息
SELECT * FROM emp
WHERE job IN  #in子查询
(
SELECT job FROM emp
WHERE job ='分析员'  OR job ='办事员' 
);

表关联(表直接关系)

1-dept部门表
2-emp员工表
3-empext 员工信息扩展表
4.student学生表
5.tb_user 用户表

a.表必然有关联吗?
a1)没有关系 (最常见) student、tb_user
a2)有关系:

1.表之间的关系:4种

1)一对一：

2)一对多：一个教室和多个学生

3)多对一：多个学生和一个教室

4)多对多:登录用户和菜单，可以写成一对多和多对一

#一个部门下可以有多个员工(一对多)
dept 和 emp 有关（一对多）

#一个员工可以从属多个部门(多对一)
要求:一个员工只能一个实际部门(一对一)
emp 和dept 有关 （多对一、一对一）

#一个员工有一个扩展信息
emp和empext有关（一对一）

#一个扩展信息对一个员工
empext和demp有关（一对一、多对一）

描述表关系时，有一个主体(主表)，有一个附属的(从表)

2.在实际开发中

不光有编码阶段:
1.需求调研
2.需求评审 (专家)
3.概述设计 (框架)
4.详细设计 (伪代码) PDM表设计
5.编码
6.测试
7.部署客服服务器上
8.用户评审
9.正式上线运行
10.维护期
11.项目结束

3.表设计最好的工具 Power Desinger

部门和用户:一对多
表中一对一时，箭头线为橘色的
表中一对多时，箭头线为蓝色的
主表中的主键（primary key） 在子表中出现，也称为子表的外键自己自关联
自关联:（自己是自己的子表）

学生和课程:多对多
多对多需要3张表来表达，
多对多，把两个主表主键都拿过来

表关系解决什么问题？

多表联查
需求:查询tony他的信息，还包括部门名称

select * from emp where empno = 200

4种:
a) where 硬查
b) inner join 内连接
c) left join 左连接
d) right join 右连接

SELECT * FROM emp WHERE empno=200

SELECT * FROM dept

#代表两张表所有的字段，前面emp.*，后面dept.*
SELECT * FROM emp,dept
WHERE empno=200 AND emp.deptno=dept.deptno

#某个员工的名字和他所在部门的名称
SELECT ename,dname 
FROM emp,dept
WHERE empno=200 AND emp.deptno = dept.deptno

SELECT COUNT(*) FROM emp; #5
SELECT COUNT(*) FROM dept; #3
SELECT * FROM emp,dept  #笛卡尔积，
#然后在过滤数据where

内连接 inner join

#内连接 inner join
#1.单独写出各自的查询SQL语句
#2.把他们信息连接起来，写上关联条件 on
#3.在打的结果集上挑去要的字段
SELECT
d.deptno,dname,ename
FROM
(SELECT deptno ,dname FROM dept) d
INNER JOIN
(SELECT ename,deptno FROM emp) e
ON d.deptno = e.deptno

inner join 和 left join
内连接 这个值必须在对应表出现，才算数，
如果没有就不在最终结果集中
部门表中3条记录，只有2条在员工表中出现，
2条是在结果中有的，第3条没有

左连接，左边表内记录，不管后边表有没有这个值，都出现
部门表的3条记录都出现
后面没有就可以颠倒顺序就是左连接

内连接，两边都有的匹配记录才出现(严谨)
左连接，左边的表的所有记录都出现，没有匹配，其数据null
右连接，右边的表的所有记录都出现，没有匹配，其数据null

当两边数据都匹配 (业务如果正常情况下)
inner join =left join =right join

简写

SELECT d.name,e.name
FROM dept d LEFT JOIN emp e on d.deptno = e.deptno

在开发中，表关联小于等于3张

阿里为什么要进行这样的要求呢? 强制
执行性能，多表联查会比单表慢很多
a)单表直接结果，结果如果非常频繁，数据库缓存支持，内存
内存要比磁盘快100倍
b)多表联查，底层先实现笛卡尔积，构建一个大大的结果集，放在内存中
构建过程非常浪费时间，数据量非常大(mysql单表千万条，oracle单表支持)

怎么去解决不让我很多表联查
1）构建中间结果集时，尽量小，提前筛选数据
2）分而治之，多次查询

怎么提高表的查询性能？

1. 索引！ 第一个想到，成本最低
   2）分库，分表 (mysal百万)
   3）主从复制，读写分离

index 你用过
创建一个表，设置主键，主键查询速度非常快？
以为有索引，（表的主键，是自动创建索引）

索引为什么快？
1）索引是独立存在，索引占空间
查询数据时，无需到表中去查，先到索引去查
主键：int/bigint(long)，排序，
查询一个1，一条一条记录遍历（全表遍历）
1,5,2,3,4
排序：数据预处理
1,2,3,4,5（二分查找）
唯一值 unique

索引快的原因：
1）独自空间
2）排序
3）算法：mysql中算法，btree+（数据结构）

索引分类：
1）唯一索引 unique，数据库这个字段内容不能重复
2）单值索引，索引是一个字段
3）符合索引，多个字段形成一个索引

原则：索引不要建立太多字段？
索引也占空间，
索引字段非整数类型，性能没有整数高

操作数据库表：insert/update/delete
会对索引有影响吗？造成重新构造索引，如果记录很多，构建时间长

小结：
1）事务：transaction
如果数据库处理有多个SQL语句，非查询SQL，此时需要事务
事务会造成一定性能损耗，一般不推荐

2）数据库支持事务，mysql，redis不支持
ACID
Atomicity 原子性，多步改动，
、 必须要么都成功commit，要么都失败rollback，不能在中间状态
Consistency 一致性，9w+1w
Isolation 隔离性，多个用户访问（高并发）多线程（锁）
a update salary = 100;
b update salary = 2000;
锁表 lock，行集锁，DBA高级
Durability 持久性，在改变内存数据，会同步写日志（磁盘）

3）事务：commit提交（成功），rollback回滚（出错）
savepoint 提高性能

4）子查询（嵌套查询）subquery
查询中还有一个查询
=(子查询)
in(子查询)
5）表关联
一对一
一对多，多对一
多对多
6）sql实现表联查
笛卡尔积（底层实现）
内连接 inner join
左外连接 left (outer) join
右外连接 right (outer) join
7）索引是必须建的吗？不是
独占空间，字段少，遍历快
索引：int/bigint/long 二分查找，btree