sqlserver第10章 其他数据库对象

视图中包含分组函数 视图中含有GROUP BY子句 视图中含有DISTINCT关键字 视图中要修改的列包含表达式 视图中没有表的NOT NULL列。
3 Sept. 2008 Confidential
WITH CHECK OPTION
•
WITH CHECK OPTION实质是给视图加一个“CHECK”约束，该 CHECK约束的条件就是视图中的子查询的WHERE条件，以后 如果想通过该视图执行DML操作，不允许违反该CHECK约束。
•
3 Sept. 2008 Confidential
简单视图和复杂视图
简单视图：只涉及到一个表，而且SELECT子句中不包含函数 表达式列（包括单行函数和分组函数）。 • 复杂视图：涉及到一个或多个表，SELECT子句中包含函数表 达式列（单行函数或分组函数）。
•
3 Sept. 2008 Confidential
第十章 其他数据库对象
东软IT人才实训中心
3 Sept. 20082008 By Neusoft Group. All rights reserved Copyright © Neusoft Confidential
目标:
本章旨在向学员介绍其他数据库对象 的应用，通过本课的学习，学员应该 掌握如下知识： 1)视图 2)索引 3)同义词 学时：4学时 教学方法：讲授ppt ＋上机练习＋点评
3 Sept. 2008 Confidential
不能通过视图修改记录的条件
• • • •
视图中包含分组函数 视图中含有GROUP BY子句 视图中含有DISTINCT关键字 视图中要修改的列包含表达式
3 Sept. 2008 Confidential
不能通过视图添加记录的条件
• • • • •
•
下面的索引将会提高对EMP表基于 ENAME 字段的查询速度.
CREATE INDEX emp_last_name_idx CREATE INDEX emp_last_name_idx ON emp(ename) ON emp(ename)
3 Sept. 2008 Confidential
测试索引
• •
3 Sept. 2008 Confidential
创建一个索引
•
在一列或者多列上创建索引.
CREATE INDEX indexname CREATE INDEX indexname ON table (column[, column]...); ON table (column[, column]...);
•
该约束的条件为视图中WHERE条件，即“employee_id=141”， 如视图v_emp3想执行DML操作，不能把记录的employee_id字 段值改成其他编号（只能是141），如果违反了，执行出错，会 出现错误提示。
3 Sept. 2008 Confidential
删除视图
•
删除视图的语法
•
创建测试环境表。
SELECT ** INTO e1 FROM employees SELECT INTO e1 FROM employees --创建表e1 --创建表e1 INSERT INTO e1 SELECT ** FROM e1 --插入200万以上记录 INSERT INTO e1 SELECT FROM e1 --插入200万以上记录 select id=identity(int,1,1),* into e2 from e1--创建含自增序列的e2 select id=identity(int,1,1),* into e2 from e1--创建含自增序列的e2 UPDATE e2 SET employee_id=id --更新所有记录的 UPDATE e2 SET employee_id=id --更新所有记录的 employee_id，以使其数值唯一 employee_id，以使其数值唯一
3 Sept. 2008 Confidential
WITH CHECK OPTION示例
•
例10-6 WITH CHECK OPTION例子
CREATE VIEW v_emp3 CREATE VIEW v_emp3 AS AS SELECT employee_id,salary FROM emp_dml SELECT employee_id,salary FROM emp_dml WHERE employee_id=141 WHERE employee_id=141 WITH CHECK OPTION WITH CHECK OPTION
查询语句 查询语句
[WITH CHECK OPTION] [WITH CHECK OPTION]
• • •
ALTER VIEW 语句格式与CREATE VIEW格式基本相同。 过程类似于先删除原有视图，再创建同名视图。 修改视图不会改变原有的权限。
3 Sept. 2008 Confidential
修改视图示例
UPDATE v_emp1 SET salary=salary+100 UPDATE v_emp1 SET salary=salary+100
3 Sept. 2008 Confidential
不能通过视图删除记录的条件
• • •
视图中包含分组函数 视图中含有GROUP BY子句 视图中含有DISTINCT关键字
•
例10-7 查询50部门的员工的薪水的视图
ALTER VIEW salvu50 ALTER VIEW salvu50 AS AS SELECT employee_id ID_NUMBER, last_name NAME, SELECT employee_id ID_NUMBER, last_name NAME, salary SALARY salary SALARY FROM employees FROM employees WHERE department_id = 50; WHERE department_id = 50;
•
创建视图v_emp1，是个简单视图。
CREATE VIEW v_emp1 AS CREATE VIEW v_emp1 AS SELECT employee_id,salary FROM emp_dml SELECT employee_id,salary FROM emp_dml
•
通过视图进行DML操作。
•
SELECT last_name,department_name SELECT last_name,department_name FROM DEPARTMENTS a, FROM DEPARTMENTS a, (SELECT last_name,department_id FROM EMPLOYEES) b (SELECT last_name,department_id FROM EMPLOYEES) b WHERE a.department_id=b.department_id WHERE a.department_id=b.department_id
3 Sept. 2008 Confidential
视图重命名
•
视图重命名方法
sp_rename <旧视图名>,<新视图名> sp_rename <旧视图名>,<新视图名>
•
例如：
sp_rename v_emp1,v_emp1_rename sp_rename v_emp1,v_emp1_rename
3 Sept. 2008 Confidential
内联视图
内联视图（Inline View）,是一个在SQL语句内可以使用的子查 询的别名。是一个命名的SQL语句，但不是真正的数据库的视 图对象。最常见的内联视图的例子就是主查询中的FROM子句 中，包含的是一个命名的子查询。 • 例10-8 内联视图的例子
3 Sept. 2008 Confidential
创建视图示例
•
例10-3 查询50部门的员工的年薪的视图
CREATE VIEW salvu50 CREATE VIEW salvu50 AS AS SELECT employee_id ID_NUMBER, last_name NAME, SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY salary*12 ANN_SALARY FROM employees FROM employees WHERE department_id = 50; WHERE department_id = 50; sp_help salvu50 sp_help salvu50 sp_helptext salvu50 sp_helptext salvu50 SELECT ** FROM salvu50 SELECT FROM salvu50
3 Sept. 2008 Confidential
Top-N分析
•
Top-N查询主要是利用TOP子句限制返回到结果集中的行 数。 Top-N分析语法：
SELECT [[列名], TOP n [PERCENT] SELECT 列名], TOP n [PERCENT] FROM 表名 FROM 表名
Βιβλιοθήκη Baidu
•
3 Sept. 2008 Confidential
本章要点
• • • •
视图 序列 索引 同义词
3 Sept. 2008 Confidential
视图
•
视图是虚表。是一个命名的查询，用于改变基表数据的显示， 简化查询。视图的访问方式与表的访问方式相同。 视图的好处： – 可以限制对基表数据的访问，只允许用户通过视图看到表中 的一部分数据 – 可以使复杂的查询变的简单 – 提供了数据的独立性，用户并不知道数据来自于何处 – 提供了对相同数据的不同显示
DROP VIEW view DROP VIEW view
•
例如：
DROP VIEW v_emp1 DROP VIEW v_emp1
3 Sept. 2008 Confidential
修改视图
ALTER VIEW view ALTER VIEW view [WITH <视图属性>] [WITH <视图属性>] AS AS
3 Sept. 2008 Confidential
索引
•
索引是: – 方案（schema）中的一个数据库对象 – 在SQL Server数据库中用来加速对表的查询速度 – 通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O – 与表独立存放，但需要依附于表，是在表的基础上创建的 – 由 SQL Server数据库自动维护
创建视图
CREATE VIEW view CREATE VIEW view [WITH <视图属性>] [WITH <视图属性>] AS AS
查询语句 查询语句
[WITH CHECK OPTION] [WITH CHECK OPTION]
• •
在子查询中可以加入复杂的SELECT语句. 视图属性包括ENCRYPTION(文本加密)、SCHEMABINDING (视图绑定到基础表的架构)和VIEW_METADATA(指定引用视图 的元数据)。
3 Sept. 2008 Confidential
Top-N例子
•
例10-7 查询部门薪水最高的8个员工的信息。
SELECT TOP 8 ** SELECT TOP 8 FROM employees FROM employees ORDER BY salary DESC ORDER BY salary DESC
3 Sept. 2008 Confidential
通过视图执行DML操作
•
创建一个测试用表EMP_DML
SELECT employee_id,last_name,salary SELECT employee_id,last_name,salary INTO emp_dml INTO emp_dml FROM employees FROM employees WHERE department_id=50 WHERE department_id=50
3 Sept. 2008 Confidential
索引
3 Sept. 2008 Confidential
创建索引
创建索引有两种方式：自动或者手动 自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条 件时,SQL Server数据库自动创建一个对应的唯一索引. • 手动: 用户可以创建索引以加速查询，在需要创建索引的字段上 创建需要的索引。