第八章视图和索引（2课时 0919）

视图

利用查询语句构建一个虚表，可以实现与实表相似的操作。也有要叫他是保存的查询

视图使用：

1）可以抽取原表部分字段，隐藏一些字段。保证安全性

2）还可以利用视图对数据进行处理，格式可以自定义

3）可以把汇总数据存到视图里。

语法：

create view 视图名 as 子查询;

建立最简单的视图：

create view v_dept as select * from dept;

select * from v_dept;

insert into v_dept values(15,'java15','dl');

隐藏部分字段：

create view v_dept_2 as select deptno,dname from dept;

select * from v_dept_2;

视图可以对原数据表进行格式处理

create view v_emp

as

select empno,ename,sal,MONTH(hiredate)

from emp;

select * from v_emp;

把汇总数据存到视图里，方便以后查询

比如：每个部门的部门编号最低，最高工资，平均工资，总工资存在v_emp_count视图里

create view v_emp_count

AS

select deptno,max(sal),min(sal),avg(sal),sum(sal)

from emp

group by deptno;

select * from v_emp_count;

建立视图，查询员工基本信息，部门信息，工资等级

create view v_fyl

as

(select d.dname,d.loc,e.*,s.grade

from dept d,emp e,salgrade s

where e.deptno=d.deptno AND

e.sal between losal and hisal)

select * from v_fyl

•创建视图时，在子查询中使用列的别名

CREATEVIEW  salvu30

    ASSELECT  empno EMPLOYEE_NUMBER, ename NAME,

    sal SALARY

    FROM  emp

   WHERE  deptno = 30;

视图修改

#10号部门的数据存储到视图里

createor replace view v_dept_10

as

select * from dept

where deptno=10;

createor replace(没有则创建一个新的，有则修改）

视图上执行DML操作规则：

•您可以通过视图删除基表中数据，只要视图中不出现以下情况：

–Group 函数；

–GROUP BY 子句；

–DISTINCT 关键字；

•您可以通过视图修改基表中数据，只要视图中不出现以下情况：

–GROUP函数、GROUP BY子句，DISTINCT关键字；

–使用表达式定义的列；

•您可以通过视图向基表插入数据，只要视图中不出现以下情况：

–GROUP函数、GROUP BY子句，DISTINCT关键字；

–使用表达式定义的列；

–基表中未在视图中选择的其它列定义为非空并且没有默认值；

特殊强调的WITH CHECK OPTION：

WITH CHECK OPTION 建立视图时如果追加该选项，会对视图操作产生影响，这个影响就是你向视图里进行的插入操作必须满足创建视图时的where条件

比如视图创建语句如下：

create or replace view v_dept as select * from dept where deptno=10 ;

没加 WITH CHECK OPTION;限制时

insert into v_dept_10 values(13,'java13','hunan');

可以执行成功;

加上WITH CHECK OPTION后

create or replace view v_dept as select * from dept where deptno=10 WITH CHECK OPTION;

insert into v_dept_10 values(14,'java14','hunan');

那么你将来insert时，deptno=10

insert into v_dept values(60,'java60','shengyang');

删除视图

drop view 视图名;

索引index

•索引简介

–索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

索引的设计原则

•为了使索引的使用效率更高，在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。索引的设计原则如下：

–选择惟一性索引

–为经常需要排序、分组和联合操作的字段建立索引

–为常作为查询条件的字段建立索引

–限制索引的数目

–尽量使用数据量少的索引

–尽量使用前缀来索引

–删除不再使用或者很少使用的索引

索引查询的时候会二分查找 ，建立索引后会提高查询的速度，但是可能会降低添加、删除、修改的速度

•总结：索引优缺点

–索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

–索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

新建表时创建索引

前三种重点看，后两种知道

•创建普通索引

Create table index1(

Id int,

Name varchar(20),

Sex boolean,

foreign key (deptno) refenerces dept(deptno),

Index(id)

);

•创建唯一性索引

Create

table index2(

Id int unique,

Name varchar(20),

Unique index index2_id(id asc|desc)

);

创建全文索引:

只有MyISAM存储引擎支持全文索引。

Create

table index3(

Id int,

Info varchar(20),

Fulltext index index3_info(info)

)engine=myisam;

•创建单列索引

Create   table t_index1(

Id int,

Subject varchar(30),

Index index4_st(subject(10))

);

•创建多列索引

Create

table index5(

Id int,

Name varchar(20),

Sex  char(4),

Index index5_ns(name,sex)

);

•创建空间索引:表的存储引擎必须是myisam类型(很少用)

Create

table index6(

Id int,

Space geometry not null,

Spatial index index6_sp(space)

)engine=myisam;

在已经存在的表上创建索引

•CREATE  INDEX  索引名   ON  表名  (属性名  [ (长度)]  [ ASC | DESC] );

ALTER  TABLE  表名  ADD   索引名（属性名  [ (长度)]  [ ASC | DESC]）;

删除索引

•DROP  INDEX  索引名  ON  表名 ;

小结

INDEX 索引，普通的

UNIQUE 唯一索引。 不允许有重复。  MYSQL主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。

FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。

二分查找。

查询快，DML慢。

练习1略

练习2

-- 1.创建一个视图，

-- 通过该视图可以查询到工作在NEW YORK和CHICAGO的

-- 员工编号，姓名，部门编号，入职日期。

CREATE or replace VIEW v1 AS

SELECT empno,ename,deptno,hiredate

from emp NATURAL JOIN dept

WHERE loc in ('NEW YORK','CHICAGO')

SELECT * from v1

-- 2.创建一个视图，通过该视图可以查询到

-- 每个部门的部门编号，部门名称及最低工资。

CREATE or REPLACE view v5 AS

SELECT d.deptno,d.dname,min(e.sal) minsal

FROM emp e,dept d

where d.deptno=e.deptno

GROUP BY e.deptno,d.dname

SELECT * from v5

-- 3.通过如上视图，

-- 查询每个部门工资最低的员工姓名及部门名称

SELECT e.ename,e.deptno,d.dname

from  emp e,v5 d

where e.deptno=d.deptno

and sal=d.minsal

转至：↓↓↓

链接：https://www.jianshu.com/p/49204b784fb9

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 215,384评论 6赞 497
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,845评论 3赞 391
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 161,148评论 0赞 351
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,640评论 1赞 290
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,731评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,712评论 1赞 294
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,703评论 3赞 415
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,473评论 0赞 270
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,915评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,227评论 2赞 331
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,384评论 1赞 345
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,063评论 5赞 340
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,706评论 3赞 324
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,302评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,531评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,321评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,248评论 2赞 352