索引、视图

【实验12】

2、使用SQL语句创建、管理索引
(1) 利用create table语句在test数据库中创建数据表writers，其表结构内容如下表所示。在创建表的同时在w_id字段上添加名称为uniquex的唯一索引。

字段名 字段描述 数据类型 主键 外键 非空 唯一
id 编号 INT(10) 是 否 是 是
w_name 作者姓名 VARCHAR(20) 否 否 是 否
w_address 作者地址 VARCHAR(50) 否 否 否 否
w_age 年龄 INT 否 否 是 否
w_note 说明 TEXT 否 否 否 否

CREATE TABLE writers
(w_id INT(10) PRIMARY KEY ,
 w_name VARCHAR(20) NOT NULL,
 w_address VARCHAR(50),
 w_age INT NOT NULL,
 w_note text,
 UNIQUE INDEX uniquex(w_id)
);

(2) 使用create index语句在w_address和w_age字段上建立名称为multiidx的组合索引。

CREATE INDEX multiidx 
ON writers (w_address,w_age);

(3)使用create index语句在w_note字段上建立名称为ftidx的全文索引。

CREATE FULLTEXT INDEX ftidx 
ON writers (w_note);

(4) 删除索引。

• 利用alter table语句将全文索引ftidx删除。

ALTER TABLE writers DROP INDEX ftidx;

• 利用drop index语句将index_name索引删除。

DROP INDEX index_name ON workinfo;

3、使用SQL语句创建、管理视图
(1) 创建视图。
① 创建一个名为sc_view1的视图，从数据库gradem的sc表中查询出成绩大于90分的所有学生选修成绩的信息。

CREATE VIEW test.sc_view1
AS
	SELECT *
	FROM gradem.sc
	WHERE degree>90;

② 创建一个名为sc_view2的视图，从数据库gradem的sc表中查询出成绩小于80分的所有学生的学号、课程号、成绩等信息。

CREATE VIEW test.sc_view2
AS
	SELECT sno,cno,degree 
	FROM gradem.sc
	WHERE degree<80;

③ 创建一个名为sc_view3的视图，由数据库gradem的student、course、sc表创建一个显示“20070303”班学生选修课程(包括学生姓名、课程名称、成绩等信息)的视图。

CREATE VIEW test.sc_view3
AS
	SELECT sname,cname,degree,b.cno
	FROM gradem.student a,gradem.sc b,gradem.course c
	WHERE a.sno=b.sno AND b.cno=c.cno
	AND classno='20070101';

【20070303是空值】

④ 创建一个从视图sc_view1中查询出课程号“c01”的所有学生的视图。

CREATE VIEW c01
AS
	SELECT *
	FROM sc_view1
	WHERE cno='c01';

(2) 修改视图的定义。
修改视图sc_view1，使其从数据库gradem的sc表中查询出成绩大于90分且第3学期的所有学生选修成绩的信息。

ALTER VIEW sc_view1
AS 
	SELECT a.cno,sno,degree
	FROM gradem.sc a,gradem.teaching b
	WHERE a.cno=b.cno
	AND degree>90
	AND cterm='3' AND degree IS NOT NULL;

(3) 视图的删除。
① 将视图sc_view1删除。

DROP VIEW sc_view1;

(4) 管理视图中的数据。
① 从视图sc_view2中查询出学号为“2007030125”、课程号为“a01”的学生选修成绩的信息。

SELECT*
FROM sc_view2
WHERE sno='2007030125'
AND cno='a01';

② 将视图sc_view2中学号为“2007030122”、课程号为“c02”的成绩改为87。

UPDATE sc_view2
SET degree=87
WHERE sno='2007030122'
AND cno='c02';

③ 从视图sc_view2中将学号为“2007030123”、课程号为“a01”的学生信息删除。

DELETE 
FROM sc_view2
WHERE sno='2007030123'
AND cno='a01';