mysql5
实验5 索引和数据完整性
实验类型：验证型实验课时： 2 指导教师：高素春
时间：2011年 4 月 14 日课次：第 5-6 节教学周次：第 7 周实验分室： 3#101 实验台号： B03 实验员：孔庆大
实验目的：
1．掌握索引的使用方法
2．掌握数据完整性的实现方法。
实验要求：
了解索引和数据完整性的相关知识。
实验内容：
1.创建索引
2.删除索引
3.数据完整性
实验步骤：
说明：按实验步骤对数据库YGGL中的三个表进行操作，三个表结构如下（具体参看实验2）：Departments (DepartmentID,DepartmentName,Note)
Employees
(EmployeeID,Name,Sex,Birthday,Education,WorkYear,Address,PhoneNumber,DepartmentID) Salary(EmployeeID,InCome,OutCome)
要求：将实验步骤中紫色底纹部分填上正确答案。
1．创建索引
使用CREATE INDEX语句创建索引
（1）对YGGL数据库上的Employees表中的DepartmentID列建立索引。
USE YGGL;
CREATE INDEX depart_ind ON Employees(DepartmentID);
（2）在Employees表的Name列和Address列上建立复合索引。
CREATE INDEX Ad_ind ON Employees(Name,Address);
（3）在Departments表的DepartmentName列建立唯一性索引。
CREATE UNIQUE INDEX Dep_ind ON Departments (DepartmentName);
（4）使用SHOW INDEX语句查看Employees表的索引。
SHOW INDEX FROM Employees;
（5）使用SHOW INDEX语句查看Departments表的索引。
SHOW INDEX FROM Departments;
◆使用ALTER TABLE语句向表中添加索引
（1）向Employees表中的出生日期列添加一个唯一性索引，姓名列和性别列添加一个复合索引。
ALTER TABLE Employees
ADD UNIQUE INDEX date_ind(Birthday),
ADD INDEX na_ind(Name,Sex);
（2）删除表Departments的主键。
ALTER TABLE Departments
DROP PRIMARY KEY;
（3）将表Departments的DepartmentID列设为主键。
ALTER TABLE Departments
ADD PRIMARY KEY(DepartmentID);
◆在创建表时创建索引
创建与表Departments表相同结构的表Departments1，将DepartmentName列设为主键，DepartmentID列上建立一个索引
CREATE TABLE Departments1 (
DepartmentID char(3),
DepartmentName varchar(20),
Note text,
PRIMARY KEY (DepartmentName),
INDEX DID_ind(DepartmentID)
);
◆界面方式创建索引
使用界面方式在Employees表的PhoneNumber列上创建索引。
步骤如下：
登录MySQL Administrator，选中YGGL数据库中的Employees表，右击选择Edit Table,在表编辑界面中左击选中PhoneNumber列，单击Indices下方的“+”，键入索引名称INDEX_4，在Indices 框中选中INDEX_4，在Index Setting选项中选择索引类型，单击Index Columns栏右边的“+”，单击“Apply Changes”按钮，创建完成。
2．删除索引
（1）使用DROP INDEX语句删除表Employees上的索引depart_ind,。
DROP INDEX depart_ind ON Employees;
（2）使用ALTER TABLE删除表Departments的主键和索引Dep_ind。
ALTER TABLE Departments
DROP PRIMARY KEY,
DROP INDEX Dep_ind;
3．数据完整性
（1）创建一个表Employees3,只含EmployeeID,Name,Sex和Education列。将Name设为主键，EmployeeID为替代键
CREATE TABLE Employees3(
EmployeeID char(6) NOT NULL,
Name varchar(10) PRIMARY KEY,
Sex char(1),
Education char(4),
UNIQUE(EmployeeID)
);
(2) 创建一个表Salary1,要求所有Salary表上出现的EmployeeID都要出现在Salary1表中，利用完整性约束实现，要求当删除或修改Salary表上的EmployeeID列时，Salary1表中的EmployeeID值也会随之变化。
CREATE TABLE Salary1 (
EmployeeID char(6) NOT NULL PRIMARY KEY,
InCome Float NOT NULL,
OutCome Float NOT NULL,
FOREIGN KEY (EmployeeID)
REFERENCES Salary(EmployeeID)
ON UPDATE CASCADE
ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=gb2312;
(3) 将表Salary中的数据插入到表Salary1中。
Insert into salary1 select * from salary;
(4) 删除或更新表Salary中的数据时，观察表Salary1中的数据有何变化?
只有删除或更新Salary中的EmployeeID时，表Salary1中的EmployeeID才发生改变，删除或更新
其他数据时，表Salary1中的数据不发生变化。
（5）创建一个表Employees4,只考虑号码和性别列,性别只能包含男或女。
CREATE TABLE Employees4(
号码char(6) NOT NULL,
性别char(1) NOT NULL
CHECK(性别IN('男', '女'))
);
注意：CHECK完整性约束在目前的MySQL版本中只能被解析，而不能实现该功能
思考题：
1．添加主键和添加普通索引有什么区别？
答：主键一定是唯一性索引，唯一性索引并不一定就是主键。所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。
主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。一个表中可以有多个唯一性索引，但只能有一个主键主键列不允许空值，而唯一性索引列允许空值。
2．实验中遇到的问题及解决方法。
答：1.备份时mysqldump中没有空格，databases前注意有两个破折号。
2.任何队列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽
可能将操作移至等号右边。