Mysql索引与约束

什么时候需要给字段添加索引：
-表中该字段中的数据量庞大
-经常被检索，经常出现在where子句中的字段
-经常被DML操作的字段不建议添加索引

• 索引等同于一本书的目录
  主键会自动添加索引，所以尽量根据主键查询效率较高。
  如经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下：

1、create unique index 索引名 on 表名(列名);
create unique index u_ename on emp(ename);
2、alter table 表名 add unique index 索引名 (列名);
create index test_index on emp (sal);

查看索引
show index from emp;

使用索引
注意一定不可以用select * …可以看到type!=all了，说明使用了索引
explain select sal from emp where sal > 1500;条件中的sal使用了索引

假如我们要查找sal大于1500的所有行，那么可以扫描索引，索引时排序的，结果得出7行，我们知道不会再有匹配的记录，可以退出了。
如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于DBA的工作。

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，
mysql> ALTER TABLE EMP DROP INDEX test_index;
删除后就不再使用索引了，查询会执行全表扫描。

• 数据库设计的三范式
  第一范式 ：关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求。
  第二范式 ：第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖，
  
  如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系
  以上是一种典型的“多对多”的设计

第三范式 ： 建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

三范式总结
第一范式：有主键，具有原子性，字段不可分割
第二范式：完全依赖，没有部分依赖
第三范式：没有传递依赖
数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

一对一设计，有两种设计方案：
第一种设计方案：主键共享
第二种设计方案：外键唯一

• • MyISAM存储引擎是MySQL最常用的引擎。
  • 它管理的表具有以下特征：
  – 使用三个文件表示每个表：
  • 格式文件— 存储表结构的定义（mytable.frm）
  • 数据文件— 存储表行的内容（mytable.MYD）
  • 索引文件— 存储表上索引（mytable.MYI）
  – 灵活的AUTO_INCREMENT字段处理
  – 可被转换为压缩、只读表来节省空间

• 常用的存储引擎：

  MyISAM存储引擎
  • MyISAM存储引擎是MySQL最常用的引擎。
  • 它管理的表具有以下特征：
  – 使用三个文件表示每个表：
  • 格式文件— 存储表结构的定义（mytable.frm）
  • 数据文件— 存储表行的内容（mytable.MYD）
  • 索引文件— 存储表上索引（mytable.MYI）
  – 灵活的AUTO_INCREMENT字段处理
  – 可被转换为压缩、只读表来节省空间

  InnoDB存储引擎
  • InnoDB存储引擎是MySQL的缺省引擎。
  • 它管理的表具有下列主要特征：
  – 每个InnoDB表在数据库目录中以.frm格式文件表示
  – InnoDB表空间tablespace被用于存储表的内容
  – 提供一组用来记录事务性活动的日志文件
  – 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
  – 提供全ACID兼容
  – 在MySQL服务器崩溃后提供自动恢复
  – 多版本（MVCC）和行级锁定
  – 支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎
• 使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
• MEMORY存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm格式的文件表示。
– 表数据及索引被存储在内存中。
– 表级锁机制。
– 不能包含TEXT或BLOB字段。
• MEMORY存储引擎以前被称为HEAP引擎。

选择合适的存储引擎
• MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
• 如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
• 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

条件查询
条件查询需要用到where语句，where必须放到from语句表的后面
支持如下运算符
运算符 说明

=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between… and ….	两个值之间,等同于>= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配
%匹配任意个字符
下划线，一个下划线只匹配一个字符

分组函数/聚合函数/多行处理函数

count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
select count(*) from emp where xxx;			符合条件的所有记录总数。
select count(comm) from emp;	comm这个字段中不为空的元素总数。

注意：分组函数不能直接使用在where关键字后面。
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function

分组查询
分组查询主要涉及到两个子句，分别是：group by和having

select语句总结
一个完整的select语句格式如下
select 字段
from 表名
where…….
group by ………
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ………

以上语句的执行顺序
1.	首先执行where语句过滤原始数据
2.	执行group by进行分组
3.	执行having对分组数据进行操作
4.	执行select选出数据
5.	执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据
进行过滤的。

子查询：
在where语句中使用子查询，也就是在where语句中加入select语句
在from语句中使用子查询，可以将该子查询看做一张表
在select语句中使用子查询：

 第一种做法，将员工表和部门表连接
 select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
 第二种做法，在select语句中再次嵌套select语句完成部分名称的查询
 select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

union可以合并集合（相加）
合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同。

select * from emp where job='MANAGER'
union
select * from emp where job='SALESMAN'

limit 的使用
mySql提供了limit ，主要用于提取前几条或者中间某几行数据

select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录

创建表加入约束：

	常见的约束
a)	非空约束，not null
b)	唯一约束，unique 
c)	主键约束，primary key
d)	外键约束，foreign key
e)	自定义检查约束，check（不建议使用）(在mysql中现在还不支持)
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，
那么该字段的值必须来源于参照的表的主键

级联更新与级联删除：

mysql对有些约束的修改比较麻烦，所以我们可以先删除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) 
references t_classes(classes_id)  on update cascade;

mysql对有些约束的修改时不支持的，所以我们可以先删除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) 
references t_classes(classes_id)  on delete cascade;
delete from t_classes where classes_id = 20;
 
我们只删除了父表中的数据，但是子表也会中的数据也会删除。