【第二阶段 day14】索引 视图

1.索引

1.1 概述

为了提高查询的效率,索引会单独生成一张索引表,需要合理的使用索引

1.1.1分类:

• 单值索引:一个索引只包含一个列
• 唯一索引:一个索引只包含一个列,但是值必须唯一,允许有控制
• 复合索引:一个索引包含多个列

使用:创建索引(经常安装指定字段查询)+使用索引

1.1.2索引的优缺点

• 优点:提高查询效率
• 缺点:索引需要一张单独的表,占用空间

1.2 查看

show index from 表名;

SHOW INDEX FROM em;

1.3 创建索引

1.3.1 单值索引:

create index 索引名 on 表名(字段);

#创建索引:单值索引
create index ename_index on em(ename);

1.3.2 唯一索引:

create unique index 索引名 on 表名(字段);

#创建唯一索引
CREATE UNIQUE INDEX sal_index ON em(sal);

1.3.3 复合索引

create index 索引名 on 表名(字段1,字段2…)

#创建复合索引:一个字段包含多个字段
CREATE INDEX f_index ON em(job,mgr);
SHOW INDEX FROM em;
#遵循最左特性,只要包含最左边的元素,就会生效
EXPLAIN
SELECT * FROM em WHERE job="副总";#有效
EXPLAIN
SELECT * FROM em WHERE job="副总" AND mgr>300;#有效
EXPLAIN
SELECT * FROM em WHERE mgr>300;#失效

1.4 使用索引

• 如果查询时,查询的列有索引,那么就会使用索引,但是我们感受不到
• 我们在写时,正常使用就可以

SELECT ename FROM em WHERE ename=“jack”;

#查看SQL的执行计划/性能(看看有没有使用索引)
EXPLAIN
SELECT ename FROM em WHERE ename="jack";

1.5删除索引

alter table 表名 drop index 索引名

#删除索引
alter table em drop index f_index;

2.视图

缓存了SQL语句的执行结果,当做表来使用
和索引一样,都是对数据库优化的有效方案

• 优点:相同的SQL需求不必再写SQL了,直接查视图
  视图可以被共享,视图屏蔽了真实业务表的复杂性
• 缺点:视图一旦创建成功,就不可以再被优化

2.1创建视图

create view 视图名 as 查询的SQL语句

CREATE VIEW vi_sal AS
SELECT * FROM em WHERE sal>3000;

2.2使用视图

select 字段 from 视图名

SELECT * FROM vi_sal;

3.多表联查

3.1笛卡尔积/直积

把多张表联合起来查询,禁止三张以及三张以上的表进行查询

语法: select * from 表1,表2;

#笛卡尔积,通过逗号连接表名
SELECT * FROM courses,scores;

#描述两个表的关系,添加过滤条件
SELECT * FROM courses,scores WHERE courses.cno=scores.cno;

3.2连接查询

小表驱动大表

• 内连接:
  inner join :取两张表的交集
• 外连接:
  right join 取右表的所有和左表满足条件的
  left join:取左表的所有和右表满足条件的

将笛卡尔积查询中的:
1.连接两个表的逗号改为join
2.第一个条件由where改为on
3.第二个条件由and改为where

练习1:查询岗位是总监所在的部门信息

SELECT de.* FROM de JOIN em 
ON de.id=em.deptno 
WHERE ename="jack";

练习2:查询岗位是总监所在的部门信息,连接查询

SELECT de.* FROM de JOIN em ON de.id=em.deptno WHERE job="总监";

练习3:查询员工名叫jack的部门信息

SELECT de.* FROM de JOIN em 
ON de.id=em.deptno 
WHERE ename="jack";

3.3子查询/嵌套查询

把上次的查询结果作为这一次的条件
练习1:查询学员李军的总得分

SELECT SUM(degree) FROM scores WHERE sno=(
SELECT sno FROM students WHERE sname="李军");

练习2:查询陈冰能讲解的课程名称

SELECT cname FROM courses WHERE tno=(
SELECT tno FROM teachers WHERE tname="陈冰");

4.作业

#练习1:查询部门编号是1的员工姓名
select ename from em where deptno=1;

#练习2:查询员工姓名叫jack的部门信息
select de.* from de,em where id=deptno and ename="jack";
SELECT de.* FROM de join em on id=deptno where ename="jack";
select de.* from de where id=(select deptno from em where ename="jack");

#练习3:查询岗位是总监所在的部门信息
select de.* from de,em where id=deptno and job="总监";
SELECT de.* FROM de join em on id=deptno where job="总监";
select de.* from de where id=(select deptno from em where job="总监");

#练习4:列出java开发部 部门下的所有员工的信息
select em.* from em,de where deptno=id and name="java开发部";
SELECT em.* FROM em join de on deptno=id where NAME="java开发部";
select em.* from em where deptno=(select id from de where name="java开发部");

#练习5:查询部门地址在北京和广州的员工信息
#in形式的子查询:因为子查询中会查询多个结果
select em.* from em,de where deptno=id and loc IN('北京','广东');
SELECT em.* FROM em join de on deptno=id where loc IN('北京','广东');
select em.* from em where deptno in (SELECT id FROM de WHERE loc IN('北京','广东'));