数据库笔记3-嵌套查询，索引，视图，优化，范式

嵌套查询也是多表联合查询的内容

在一个select中镶嵌入另一个select

#查询research部门的所有员工姓名和工资
#查询jack所在的部门信息
#查询总监的部门信息
#查询李军的平均分
#查询陈冰能讲的课程名

#笛卡尔积方式
SELECT ename,sal FROM emp,dept  #查询姓名工资
WHERE emp.deptno=dept.deptno AND  #关联
dept.dname='research'; #查询research部门

SELECT dept.*,emp.* FROM emp,dept
WHERE emp.deptno=dept.deptno AND  #关联
emp.ename='jack';

SELECT dept.* FROM emp,dept
WHERE emp.deptno=dept.deptno
AND emp.job='总监';

SELECT students.sname,AVG(degree)
FROM students,scores
WHERE students.sno=scores.sno
AND students.sname='李军';

SELECT teachers.tname,courses.cname 
FROM teachers,courses
WHERE teachers.tno=courses.tno
AND teachers.tname='陈冰';


#连接方式
SELECT emp.ename,emp.sal FROM emp JOIN dept
ON emp.deptno=dept.deptno
WHERE dept.dname='research'; 

SELECT dept.* FROM emp JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.ename='jack';

SELECT dept.* FROM emp JOIN dept #通过内连接连接
ON emp.deptno=dept.deptno    #关联
WHERE emp.job='总监';        #查询职位是总监的

SELECT students.sname,AVG(degree) #查询平均分
FROM students JOIN scores         #内连接
ON students.sno=scores.sno        #连接条件
GROUP BY students.sno             #按非聚合分组
HAVING students.sname='李军';     #查询李军

SELECT courses.cname FROM teachers LEFT JOIN courses
ON teachers.tno=courses.tno
WHERE teachers.tname='陈冰';

#嵌套查询
SELECT emp.ename,emp.sal FROM emp WHERE deptno=(          #在用另一个去查，注意要括号括起来
SELECT dept.deptno FROM dept WHERE dept.dname='research'  #先查一个条件
);

SELECT dept.* FROM dept WHERE deptno=(
SELECT emp.deptno FROM emp WHERE emp.ename='jack'
);

SELECT dept.* FROM dept WHERE deptno=(
SELECT emp.deptno FROM emp WHERE emp.job='总监'
);

SELECT AVG(scores.degree) FROM scores WHERE sno=(
SELECT students.sno FROM students WHERE students.sname='李军'
);

SELECT cname FROM courses WHERE tno=(  #根据tno查
SELECT tno FROM teachers WHERE tname='陈冰'); #查询陈冰的tno

索引

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。

索引分为三种：

单值索引，唯一索引，复合索引

#查询索引,表里的主键数据库已经自己创建好了索引
SHOW INDEX FROM dept;
SHOW INDEX FROM emp;
#2.创建索引-不是所有的字段都适合加索引，可以给
#常用的字段加索引
#语法：create index 索引名 on 表名（字段名）
CREATE INDEX dname_index ON dept(dname);
SHOW INDEX FROM dept;

#使用索引
SELECT * FROM dept WHERE dname='research';

#查看SQL的执行计划/性能，观察查询结果里的key值
EXPLAIN
SELECT * FROM dept WHERE dname='research';

CREATE INDEX name_index ON emp(ename);
#查询名字jack
EXPLAIN
SELECT * FROM emp WHERE ename='jack';


#创建唯一索引：要求字段的值必须唯一
#create unique index 索引名 on 表名(字段名)
CREATE UNIQUE INDEX loc_index ON dept(loc)#失败不唯一
SHOW INDEX FROM dept
CREATE UNIQUE INDEX dname_index ON dept(dname)
#使用 dname字段拥有两种索引：唯一和单值，优先唯一
EXPLAIN SELECT * FROM dept WHERE dname='research';

#创建复合索引:最左特性（必须包含最左边的元素）
CREATE INDEX fuhe_index ON emp(job,deptno);

SHOW INDEX FROM emp;

EXPLAIN SELECT * FROM emp WHERE job='员工' AND deptno=2;
EXPLAIN SELECT * FROM emp WHERE job='员工';
EXPLAIN SELECT * FROM emp WHERE deptno=2;   #失效
EXPLAIN SELECT * FROM emp WHERE deptno=2 AND job='员工' ;
EXPLAIN SELECT * FROM emp WHERE deptno=2 OR job='员工' ;#失效，有可能不用

#删除索引
#语法：alter table 表名 drop index 所有名
ALTER TABLE emp DROP INDEX fuhe_index;

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描，其次慢的方式
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询，system是const的特殊情况
null MySQL不访问任何表或索引，直接返回结果

为何索引快？
明显查询索引表比直接查询数据表要快的多，首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。

其过程如下图，先到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

排序，tree结构，类似二分查找
索引表小

小结
优点:

索引是数据库优化
表的主键会默认自动创建索引
每个字段都可以被索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，大大提高了查询效率

缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但对数据的增、删、改的操作需要更新索引表信息，如果数据量非常巨大，更新效率就很慢,因为更新表时,MySQL不仅要保存数据,也要保存一下索引文件
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

视图：

概念

可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。现在用的少,了解即可.

#1.创建视图
#语法：create view 视图名 as SQL语句
#查询名字包含a的员工信息
CREATE VIEW a_view AS 
SELECT * FROM emp WHERE ename LIKE '%a%';

#2.查视图，当作表来查
SELECT * FROM a_view;

CREATE INDEX sname_index ON students(sname);
SHOW INDEX FROM students;

EXPLAIN SELECT * FROM students WHERE sname LIKE '李%';#生效
EXPLAIN SELECT * FROM students WHERE sname LIKE '%李';#失效

INSERT INTO dept VALUES(NULL,'123','北京');

EXPLAIN
SELECT * FROM dept WHERE dname=123;

SQL优化
1, 用字段名称代替*
2, where里,能用=就别用!=,能用and就别用or,用=就比用in
3, 字段的类型,能用varchar就别用char. 字段的值能用数字就别用字符串
4, 索引,单表的索引最多5个.
5, 模糊查询,最好确定以啥开头,高效而且索引会生效
6, 字符串,SQL对于数字不严格,where name=123,应该把123加引号,因为name是把人varchar类型
 

范式

数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。
所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。
在关系型数据库中这些规范就可以称为范式,也是作为数据库 设计的一些规则.

关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）
巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）
范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。
所以有时为了提高运行效率，可以让数据冗余.

数据库的三范式
第一范式：所有的属性都不可在分割
第二范式：每个表都有主键，且所有的属性都围绕主键，比如
学生表主键学生1，属性都是1的名字1的性别等
第三范式：每个属性都和主键有直接关系，而不是间接关系
比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）
应该拆分（学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）