视图
6.1.1 什么是视图
视图通过以定制的方式显示来自一个或多个表的数据
视图是一种数据库对象,用户可以像查询普通表一样查询视图
视图内其实没有存储任何数据,它只是对表的一个查询
视图的定义保存在数据字典内,创建视图所基于的表称为“基表”
6.1.2 为什么需要视图
例如经常要对emp和dept表进行连接查询,每次都要做表的连接,写同样的一串语句,同时由于工资列队数据比较敏感,对外要求不可见。对这样的问题就可以通过视图来解决。
6.1.3 视图的作用和优点
作用:
-
控制安全
-
保存查询数据
优点:
-
提供了灵活一致级别安全性。
-
隐藏了数据的复杂性
-
简化了用户的SQL指令
-
通过重命名列,从另一个角度提供数据
CREATE [OR REPLACE] VIEW 视图名 [(列名[, 列名]...)]
AS select statement
[WITH [CASCADED | LOCAL] CHECK OPTION];
说明:
(1)as select statement表示select语句;
(2)WITH [CASCADED | LOCAL] CHECK OPTION表示视图在更新时,保证在视图的权限范围之内,CASCADED为默认值,表示更新视图时要满足所有相关视图和表的条件,local表示更新视图时满足该视图本身定义的条件即可。
#创建视图, emp_view_2, 包括2号部门的所有雇员信息:
mysql8.0 [chap04]>create view emp_view_2 as select employ_id,salary from employee where dept_id=2;
mysql8.0 [chap04]>select * from emp_view_2;
+-----------+--------+
| employ_id | salary |
+-----------+--------+
| 101 | 10000 |
| 102 | 9000 |
| 104 | 5000 |
+-----------+--------+
6.1.5 视图使用规则
-
视图必须有唯一命名。
-
在mysql中视图的数量没有限制。
-
创建视图必须从管理员那里获得必要的权限。
-
视图支持嵌套,也就是说可以利用其他视图检索出来的数据创建新的视图。
-
在视图中可以使用ORDER BY,但是如果视图内已经使用该排序子句,则视图的ORDER BY将覆盖前面的ORDER BY。
-
视图不能索引,也不能关联触发器或默认值。
-
视图可以和表同时使用。
索引
索引是一种特殊的数据库结构,可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。
6.2.1 什么是索引
模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放,但不能独立存在,必须属于某个表
由数据库自动维护,表被删除时,该表上的索引自动被删除。
索引的作用类似于书的目录,几乎没有一本书没有目录,因此几乎没有一张表没有索引。
6.2.2 索引优缺点
索引的优点是可以提高检索数据的速度,这是创建索引的最主要的原因;对于有依赖关系的子表和父表之间的联合查询时,可以提高查询速度;使用分组和排序子句进行数据查询时,同样可以显著节省查询中分组和排序的时间。
索引的缺点是创建和维护索引需要耗费时间,耗费的时间随着数据量的增加而增加;索引需要占用物理空间,每一个索引要占一定的物理空间;增加、删除和修改数据时,要动态的维护索引,造成数据的维护速度降低了。
练习
学生表:Student (Sno, Sname, Ssex , Sage, Sdept)
学号,姓名,性别,年龄,所在系 Sno为主键
课程表:Course (Cno, Cname)
课程号,课程名 Cno为主键
学生选课表:SC (Sno, Cno, Score)
学号,课程号,成绩 Sno,Cno为主键1.用SQL语句创建学生表student,定义主键,姓名不能重名,性别只能输入男或女,所在系的默认值是 “计算机”。
2.修改student 表中年龄(age)字段属性,数据类型由int 改变为smallint。
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX 。
4.创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。
1.用SQL语句创建学生表Student,定义主键,姓名不能重名,性别只能输入男或女,所在系的默认值是 “计算机”。
##创建Student表并查看表结构
mysql> create table Student(
-> Sno int primary key auto_increment,
-> Sname varchar(200) not null unique,
-> Ssex varchar(20) check (Ssex='男' or Ssex='女') not null,
-> Sage int not null,
-> Sdept varchar(100) default '计算机' not null
-> );
mysql> decs Student;
2.修改student 表中年龄(age)字段属性,数据类型由int 改变为smallint。
mysql> alter table Student modify Sage smallint;
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX 。
##创建SC表并查看表结构
mysql> create table SC(
-> Sno int not null,
-> Cno varchar(200) primary key not null,
-> Score int not null
-> );
mysql> desc SC;
##建立按Sno和Cno组合的升序的主键索引
mysql> create unique index SC_INDEX on SC(Sno asc,Cno asc);
4.创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。
mysql> create view stu_info as select Student.Sname as '姓名',Student.Ssex as '性别',Course.Cname as '课程名',SC.Score as '成绩' from Student,Course,SC;