MySQL笔记6

1)视图:
视图是一张虚拟表，并不在数据库中以存储数据值集的形式存在；在引用过程中依据基表动态生成
Create or replace view 视图名
As
–查询语句

2)with check option
如果创建的视图中带有where等条件，则with check option 子句可以保证让你只能在视图的条件之内对视图进行DML
3) 删除视图：drop view 视图名
4) 视图的优点：
安全：有的数据是需要保密的，那么直接将表给出来进行操作将会造成泄密，那么可以通过创建视图，把相应视图的权限给出来即可保证数据的安全。
高效：复杂的连接查询，每次执行时效率比较低，可以考虑新建视图，每次从视图中获取，将会提高效率。
定制数据： 可以从一张表映射数据 ，也可以从多表中映射数据

索引：为提高查询效率而创建的数据结构

索引的选择：
索引是供服务器快速在表中查询一行数据的数据库结构。
适合在数据量较大的表上添加索引。
适合在高基数(不一样的数据)列上添加索引。
索引列不能经常变化，索引维护需要花费时间

有两个特殊的索引，主键约束，唯一约束,系统自动会添加唯一索引.
创建普通索引
create INDEX ix_ename on emp(ename);
创建唯一索引
Create unique INDEX ix_ename on emp(ename);
全文索引
哈希索引
空间索引

Mysql: b-tree结构 (b+树) 不同的引擎索引的存储方式也不同
MyIsam：叶子节点存地址 不能处理事务 效率高
InnoDB: 叶子节点存数据 处理事务

总结：索引的注意事项
适合创建索引:
1)数据量比较大表
2)如查经常对某一列作为条件进行查询，此时就对该列创建（普通）索引
3)高基列(很少重复)
4)如果表数据增删改操作比较少，
特殊情况下也会索引失效，创建索引尽量少

面试题：ename列添加了索引
Select　* from emp where ename like ‘s%’ and deptno=20;
Select　* from emp where deptno=20 and ename like ‘s%’;

总结：如果有索引列作为了查询条件，要放在最前面。

b)索引失效的情况:
　① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
　② 索引列上不要使用函数,
SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = ‘ABC’（不）
SELECT Col FROM tbl WHERE name LIKE ‘%ABC%’ （不）
SELECT Col FROM tbl WHERE name LIKE ‘ABC%’ （使用）。
　③ 索引列上不能进行计算
SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效
应该改成SELECT Col FROM tbl WHERE col > 10 * 10
　④ 索引列上不要使用NOT （ != 、 <> ）
如:SELECT Col FROM tbl WHERE col ! = 10 应该
改成：union。

不在10号部门中的员工信息
SELECT * from emp where deptno!=10; #失效
SELECT * from emp WHERE deptno>10 or deptno<10;# 失效
建议
SELECT * from emp WHERE deptno>10
UNION all
SELECT * from emp WHERE deptno<10

工资大于2000或部门编号在20部门
SELECT * from emp where sal>2000 or deptno=20;#不用
建议
SELECT * from emp where sal>2000
UNION
SELECT * from emp where deptno=20

数据库优化
a)SELECT子句中避免使用(),ORACLE在解析的过程中, 会将’’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间
b)索引失效的情况:
　① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
　② 索引列上不要使用函数,
SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = ‘ABC’（不）
SELECT Col FROM tbl WHERE name LIKE ‘%ABC%’ （不）
SELECT Col FROM tbl WHERE name LIKE ‘ABC%’ （使用）。
　③ 索引列上不能进行计算
SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效
应该改成SELECT Col FROM tbl WHERE col > 10 * 10
　④ 索引列上不要使用NOT （ != 、 <> ）
如:SELECT Col FROM tbl WHERE col ! = 10 应该
改成：union。
#不在10号部门中的员工信息
SELECT * from emp where deptno!=10; #失效
SELECT * from emp WHERE deptno>10 or deptno<10;# 失效
#建议
SELECT * from emp WHERE deptno>10
UNION all
SELECT * from emp WHERE deptno<10
#工资大于2000或部门编号在20部门
SELECT * from emp where sal>2000 or deptno=20;#不用
#建议
SELECT * from emp where sal>2000
UNION
SELECT * from emp where deptno=20

c)用UNION替换OR(适用于索引列)
union:是将两个查询的结果集进行追加在一起，它不会引起列的变化。 由于是追加操作，需要两个结果集的列数应该是相关的，并且相应列的数据类型也应该相当的。
union 返回两个结果集，同时将两个结果集重复的项进行消除。 如果不进行消除，用UNOIN ALL.
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.
注意, 以上规则只针对多个索引列有效.
如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.
d)用EXISTS替代IN、用NOT EXISTS替代NOT IN
在许多基于基础表的查询中, 为了满足一个条件, 往往需要对另一个表进行联接. 在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
在子查询中, NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下, NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历).
为了避免使用NOT IN, 我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
高效: SELECT * FROM EMP (基础表) WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
低效: SELECT * FROM EMP (基础表) WHERE DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)

五、数据库的备份和还原
Mysqldump -uroot -proot dbname > d:/t.sql #备份
mysql -uroot -p dbname < C:\backup.sql #还原