实验5索引和视图

目的要求：
（1）掌握索引的使用方法
（2）熟悉视图的概念和作用
（3）掌握视图的创建方法
（4）掌握如何查询和修改视图
（5）掌握实体完整性、参照完整性以及自定义完整性的定义和使用
实验准备：
（1）了解索引的作用与分类
（2）掌握索引的创建方法
（3）了解视图的概念
（4）掌握创建视图的方法
（5）了解对视图的操作
实验内容：
1.创建索引
（1）在Employees表的Name列和Address列上建立复合索引。（4分）

CREATE INDEX name_address_idx
on zhangyk_employees(name,address)

（2）对Departments表上的DepartmentName列建立唯一性索引。（4分）

ALTER TABLE zhangyk_departments ADD
UNIQUE INDEX departmentName_idx(departmentName)

（3）向Employees表中的出生日期列添加一个唯一性索引，姓名列和性别列上添加一个复合索引。（4分）

ALTER TABLE zhangyk_employees
add UNIQUE INDEX brithday_idx(brithday),
ADD INDEX name_sex_idx(name,sex)

（4）假设Departments表中没有主键，使用ALTER TABLE语句将DepartmentID列设为主键。（4分）

ALTER TABLE zhangyk_departments
add PRIMARY key(departmentID)

（5）使用CREATE INDEX语句能创建主键吗？添加主键和添加普通索引有什么区别？（4分）
不能
添加主键会自动创建主键索引，普通索引需要自己手动去添加和指定，表中效率最高的索引就是主键索引

（6）创建与Departments表相同结构的表Departments1，将DepartmentName设为主键，DepartmentsID上建立一个索引。（4分）

CREATE TABLE zhangyk_departments1(
departmentID char(3),
departmentName char(20),
note text,
PRIMARY KEY(departmentName),
INDEX departmentID_idx(departmentID)
);

2.删除索引
（1）使用DROP INDEX语句删除表Employees上的索引depart_ind。（4分）

DROP INDEX depart_ind on zhangyk_employees;

（2）使用ALTER TABLE语句删除Departments上的主键和索引Dep_ind。（4分）

ALTER TABLE zhangyk_departments
DROP PRIMARY KEY,
DROP INDEX departmentName_idx;

3.创建视图
（1）创建YGGL数据库上的视图DS_VIEW，视图包含Departments表的全部列。（4分）

CREATE OR REPLACE VIEW DS_VIEW as 
SELECT * from zhangyk_departments

（2）创建YGGL数据库上的视图Employees_view，视图包含员工号码、姓名和实际收入。（4分）

CREATE OR REPLACE VIEW 
Employees_view(employmeeID,name,realIncome) as
select zhangyk_employees.Employees,name,income-outcome
from zhangyk_employees,zhangyk_salary
where zhangyk_employees.employees=zhangyk_salary.employmeeID

4.查询视图
（1）从视图DS_VIEW中查询出部门号为3的部门名称。（4分）

SELECT departmentName 
FROM DS_VIEW WHERE departmentID='3'

（2）从视图Employees_view查询出姓名为“王林”的员工的实际收入。（4分）

SELECT realIncome 实际收入
from Employees_view WHERE name='李丽'

5.更新视图
（1）向视图DS_VIEW中插入一行数据：6，广告部，广告业务。（4分）

INSERT INTO DS_VIEW VALUES('6','广告部','广告业务')

（2）执行完该命令使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。（4分）

SELECT * FROM DS_VIEW
 

SELECT * FROM zhangyk_departments

（3）尝试向视图Employees_view中插入一行数据，看看会发生什么情况。（4分）

INSERT INTO Employees_view VALUES('123','郭德纲','小学','1986-03-28','1','8','北京东路 100-2','833211321','1')

（4）修改视图DS_VIEW，将部门号为5的部门名称修改为“生产车间”。（4分）

UPDATE DS_VIEW 
SET departmentName='生产车间'
WHERE departmentID='5

（5）执行完该命令使用SELECT语句分别查看视图DS_VIEW和基本表Departments中发生的变化。（4分）

SELECT * from  DS_VIEW
 
SELECT * from zhangyk_departments

（6）修改视图Employees_view视图中号码为000001的雇员的姓名为“王浩”。（4分）

UPDATE Employees_view
SET name='王浩'
WHERE employmeeID='000001'

（7）删除该视图DS_VIEW中部门号为“1”的数据。（4分）

DELETE FROM DS_VIEW
WHERE departmentID='1'

6.删除视图
删除视图DS_VIEW。（4分）

DROP VIEW  DS_VIEW
  1. 数据完整性
    （1）创建一个表Employees3，只含EmployeeID、Name、Sex和Education列。将Name设为主键，作为列Name的完整性约束。EmployeeID为替代键，作为表的完整性约束。（5分）
CREATE TABLE zhangyk_employees3(
employmeeID char(6) not null,
name VARCHAR(10) PRIMARY KEY,
sex char(1),
education char(4),
UNIQUE(employmeeID)
);

（2）创建一个表Salary1，要求所有Salary表上出现的EmployeeID都要出现在Salary1表中，利用完整性约束实现，要求当删除或修改Salary表上的EmployeeID列时，Salary1表中的EmployeeID值也会随之变化。（5分）

CREATE TABLE zhangyk_salary1(
employmeeID char(6) not null PRIMARY KEY,
Income FLOAT(8) not null,
Outcome FLOAT(8) not null,
FOREIGN key(employmeeID)
REFERENCES zhangyk_salary(employmeeID)
on UPDATE CASCADE
on DELETE CASCADE
)

（3）创建完Salary1表后，初始化该表的数据与Salary表相同。删除Salary表中一行数据，再查看Salary1表的内容，看看会发生什么情况。（5分）

delete from zhangyk_salary where employmeeID=000001

只有删除或者更新Salary表中的EmployeeID时，表Salary1中的EmployeeID才发生改变，删除或更新其他数据时，表Sa1ary1中 的数据不发生变化。