一、索引
1.聚集索引
聚集索引可以理解为顺序排列，比如主键自增的表即为聚集索引，所以聚集索引一个数据库表只能有一个。在mysql中，不能自己创建聚集索引，主键即为聚集索引，如果没有创建主键，那么默认非空的列为聚集索引，如果没有非空的列那么会自动生成一个隐藏列为聚集索引。一般在mysql中，创建的主键即为聚集索引，数据是按照我们的主键顺序进行排列。因此根据主键进行查询时会非常快。
2.非聚集索引
非聚集索引可以简单理解为有序目录，是以空间换取时间的方法。
在user表中，有一个id_num，即身份号，此不为主键id，那么这些数据在存储的时候都是无序的，如果要查找某个id_num，那么只能一条条遍历，n条就需要查询n次，时间复杂度为O(n)，这是非常耗费性能的。
所以，现在就需要为id_num增加非聚集索引，添加了非聚集索引后，会给id_num进行排序（内部使用结构为B+树），只需要查询此目录(即查询B+树)，很快就知道某个id_num位置，而不需要在去遍历表中的所有数据。在非聚集索引中，不重复的数据越多，那么索引的效率越高。
在数据库中使用的索引一般是非聚集索引，使用方法：
1.1、创建普通索引
模式： CREATE INDEX 索引名 ON 表名(列名1，列名2,...);
或者 修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);
或者 创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );
eg： CREATE INDEX name_index ON index_test(name);
此为在index_test表上的name列上创建一个索引name_index。
测试的表为：
CREATE TABLE index_test ( id INT NOT NULL, name VARCHAR(50), idNum INT, PRIMARY KEY (id) );
1.2、创建唯一索引
表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique 下面三种模式都可以创建唯一索引：
1、创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
2、在表上增加索引：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
3、创建表时指定索引：
CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );
eg: CREATE UNIQUE INDEX id_num_index ON index_test(idNum);
也可以写成下面的形式： ALTER TABLE index_test ADD UNIQUE id_num_index(idNum);
此为在index_test表的idNum列上创建一个唯一索引id_num_index在创建了唯一索引之后，列中即不能重复
比如，现在我给表中插入一条重复的值，会报： Error Code: 1062. Duplicate entry '3' for key 'id_num_index' 即在id_num_index唯一索引上出现了重复。
1.3、删除索引：
以下两种模式都可以删除索引：
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
eg:
DROP INDEX name_index ON index_test;
ALTER TABLE index_test DROP INDEX name_index;
此为删除在index_test表上的name_index索引
1.4、查看索引：
SHOW INDEX FROM index_test;
即返回index_test表中的所有索引。
在返回的字段中，
Table：表的名称
Non_unique：是否不唯一，0为唯一，1不为唯一
Key_name：索引的名称
Seq_in_index：索引中的列序列号，从1开始
Column_name：列名称
Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。
基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment：更多评注。
3、索引的选择原则：
非聚集索引在数据库创建、增加、删除、修改的时候都需要作出相应的修改，所以，使用索引也是有一定的原则，即：
1、较频繁的作为查询条件的字段应该创建索引
2、重复太多的字段不适合单独创建索引，即使频繁作为查询条件
3、不会出现在WHERE子句中的字段不应该创建索引
以下两种情况不建议使用索引：
a.表的记录比较少，比如只有几百，一千条记录，那么没必要建立索引，直接全表查询即可。
b.不重复的字段越多，那么索引的价值越高，查看不重复的字段占总体的比例可以使用下面的sql语句：
SELECT count(DISTINCT(name))/count(*) AS Selectivity FROM index_test;
比如上面这个sql就是判断index_test表中name字段中不重复的值占整体的比例，这个比例应该在(0,1]之间，这个数值越大，越应该使用索引。
二、视图
1、视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，是一种不存在的虚拟表；视图中的字段是来自一个或多个数据库中的真实的表中的字段，可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。
数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据，视图总是显示最近的数据。此外，可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、join 等等，我们可以向用户精确地提交我们希望提交的数据。
2、SQL CREATE VIEW 语法：
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
--创建视图--
create or replace view v_student as
select Sno,Sname
from student;
--从视图中检索数据，即查询上面这个视图--
select * from v_student;
--也可以向查询添加条件--
select * from v_student
where Sname like '%云';
--删除视图--
drop view v_student;
3、视图的作用
（1）简化了操作，把经常使用的数据定义为视图，可以将复杂的SQL查询语句进行封装。如在实际工作中，不同的人员只关注与其相关的数据，而与他无关的数据，对他没有任何意义。根据这一情况，可以专门为其创建一个视图，定制用户数据，聚焦特定的数据。此后当他查询数据时，只需 select * from view_name; 就可以了。
（2）安全性，用户只能查询和修改能看到的数据。使用视图，基表中的数据就有了一定的安全性。因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以不通过视图将基表中重要的字段信息给用户。视图是动态的数据的集合，数据是随着基表的更新而更新的。同时，用户对视图，不可以随意的更改和删除，可以保证数据的安全性。方便了权限管理，让用户对视图有权限而不是对底层表有权限进一步加强了安全性
（3）逻辑上的独立性，屏蔽了真实表的结构带来的影响。视图的存在: 主要是为了对外提供数据支持(外部系统)；隐藏了基表字段(隐私)；保证了数据库的数据安全(保护数据库内部的数据结构)；可以灵活的控制对外的数据: 保证针对每个接口都有一个单独的数据支持，增强了用户友好性。
4、视图的缺点:
（1）性能差数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
（2）修改限制当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。在定义数据库对象时，不能不加选择地来定义视图，应该权衡视图的优点和缺点，合理地定义视图。
5、对视图的修改：
（1）单表视图操作: 可以进行增删改, 但是要实现新增: 前提是视图必须包含基表的所有不能为空的字段。
（2）多表视图(基表来源两个以上)不能插入数据, 也不能删除数据，但是可以修改数据。使用视图还是有很多局限性的，并没有像直接使用表那么方便。如果视图定义中包含了group by、union、聚合函数以及其他一些特殊情况，就不能被更新了；更新视图的查询也可以是一个关联语句，但是被更新的列必须来自同一张表；而且所有使用临时表算法实现的视图都无法被更新。
三、窗口函数
窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。
窗口函数的基本语法如下：
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
<窗口函数>的位置，可以放以下两种函数：
a. 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
b. 聚合函数，如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。
eg.
select * ,rank() over (partition by 班级
order by 成绩 desc) as ranking
from 班级表
rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：
1）每个班级内：按班级分组partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。
create table tb_score3(
id int(11) not null auto_increment,
userid varchar(20) not null,
class varchar(20),
score double,
primary key(id))
engine=innodb default charset=utf8;
tab3:
INSERT INTO tb_score3(userid,class,score) VALUES ('001','A',86),('002','A',95),('003','B',89),('004','A',83),('005','B',86),('006','C',92),('007','C',86),('008','A',88);
网友评论