实验三 视图和索引实验【数据库原理】

前言

以下内容源自数据库原理实验
仅供学习交流使用

推荐

数据库原理

实验三 视图和索引实验

一、 实验目的

（1）理解索引和视图的概念。
（2）掌握索引的使用方法。
（3）掌握视图的定义和使用方法。

二、实验内容

1）建立索引。对JWGL数据库的学生选课表SC建立索引，要求按照Cno升序、Grade降序建立一个名为SC_ind的索引。

USE JWGL
IF EXISTS(SELECT name FROM sysindexes WHERE name = 'SC_ind')
DROP INDEX SC. SC_ind;
GO
USE JWGL
CREATE INDEX SC_ind ON SC ( Cno, Grade DESC);

2）视图的定义和操作。
①在JWGL数据库中，完成视图的定义和视图上的查询、更新操作：
（1）建立计算机系学生的视图，并要求进行修改和插入操作时需保证该视图只能对计算机系的学生进行操作。
（2）建立选择了2号课程且成绩在80分以上的学生视图。
（3）建立一个反映学生出生年份的视图。
（4）在计算机系的视图c_student上查询年龄在18～20岁之间的学生的学号和姓名。
（5）在（4）的学生视图上查询成绩在90分以上的女学生的学号和姓名。
（6）在例（1）的视图上进行如下操作，将学号为950002的学生的姓名改为“张良”。
（7）在（1）的视图上进行如下操作，插入一个学生的信息。
（8）在（1）的视图进行如下操作，删除学号为980013的学生信息。
②在Market数据库中，在Market数据库上，试用SQL语句完成以下各项操作：
（1）请为北京客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称。
（2）针对（1）中定义的视图，完成查询，查找客户张三的所有订单信息。

三、实验环境

MySQL，SQLyog。

四、实验前准备

学习建立视图和索引的T-SQL语言

五、实验步骤

根据实验内容书写相应的T-SQL语言，完场要求。

六、实验结果

1.建立索引

#实验三 视图和索引实验   
#实验6 实验6 索引和视图

# 1）建立索引。对JWGL数据库的学生选课表SC建立索引，要求按照Cno升序、Grade降序建立一个名为SC_ind的索引。


#索引的建立

#① ALTER TABLE ... ADD ...
ALTER TABLE SC ADD INDEX SC_ind ON SC(Cno, Grade DESC);

#② CREATE INDEX ... ON ...
CREATE INDEX SC_ind ON SC(Cno, Grade DESC);

#查索引
SHOW INDEX FROM SC ;

/*
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
sc	0		PRIMARY		1		Sno		A		2		\N		\N		BTREE					YES	\N
sc	0		PRIMARY		2		Cno		A		5		\N		\N		BTREE					YES	\N
sc	1		SC_ind		1		Cno		A		3		\N		\N		BTREE					YES	\N
sc	1		SC_ind		2		Grade		D		5		\N		\N	YES	BTREE					YES	\N

*/

#索引的删除
#方式1：ALTER TABLE .... DROP INDEX ....
ALTER TABLE SC 
DROP INDEX SC_ind;

#方式2：DROP INDEX ... ON ...
DROP INDEX SC_ind ON SC;

2.视图的定义和操作
（1）在JWGL数据库中，完成视图的定义和视图上的查询、更新操作
①建立计算机系学生的视图，并要求进行修改和插入操作时需保证该视图只能对计算机系的学生进行操作。

#2）视图的定义和操作。

#① 在JWGL数据库中，完成视图的定义和视图上的查询、更新操作：


#（1）  建立计算机系学生的视图，并要求进行修改和插入操作时需保证该视图只能对计算机系的学生进行操作。
CREATE VIEW c_student(Sno,Sname,Sex,Age,Phonenumber,Sdept)
AS(SELECT * FROM student WHERE Sdept='CS');

SELECT * FROM c_student;
/*
Sno		Sname	Sex	Age	Phonenumber	Sdept
20221101	李勇	男	15	188820221101	CS
20221102	刘晨	女	19	188820221102	CS
*/

②建立选择了2号课程且成绩在80分以上的学生视图。

#（2）建立选择了2号课程且成绩在80分以上的学生视图。
CREATE VIEW sc_student(Sno,Sname,Sex,Age,Phonenumber,Sdept,Cno,Grade)
AS(
	SELECT student.*,sc.`Cno`,sc.`Grade` 
	FROM student 
	INNER JOIN sc
	ON student.`Sno`=sc.`Sno`
	WHERE Cno=2 AND Grade>80
);

SELECT * FROM sc_student;
/*
Sno		Sname	Sex	Age	Phonenumber	Sdept	Cno	Grade
20221102	刘晨	女	19	188820221102	CS	2	90
20221101	李勇	男	15	188820221101	CS	2	85

*/

③建立一个反映学生出生年份的视图。

#（3）建立一个反映学生出生年份的视图。
CREATE VIEW y_student(Sno,Sname,Sex,Age,Phonenumber,Sdept,birth)
AS(
	SELECT student.*,(YEAR(CURDATE()) - Age)"birth"
	FROM student 
	
);
SELECT * FROM y_student;

/*
Sno		Sname	Sex	Age	Phonenumber	Sdept	birth
20221101	李勇	男	15	188820221101	CS	2007
20221102	刘晨	女	19	188820221102	CS	2003
20221103	王敏	女	18	188820221103	MA	2004
20221104	张立	男	19	188820221104	IS	2003

*/

④在计算机系的视图c_student上查询年龄在18～20岁之间的学生的学号和姓名。

#（4）在计算机系的视图c_student上查询年龄在18～20岁之间的学生的学号和姓名。
SELECT Sno,Sname
FROM c_student
WHERE Age>18 AND Age<20;

/*
Sno		Sname
20221102	刘晨
*/

⑤在（4）的学生视图上查询成绩在90分以上的女学生的学号和姓名。

#（5）在（4）的学生视图上查询成绩在90分以上的女学生的学号和姓名。

SELECT Sno,Sname
FROM sc_student
WHERE Sex='女' AND Grade>=90;
/*
Sno		Sname
20221102	刘晨
*/

⑥在例（1）的视图上进行如下操作，将学号为950002的学生的姓名改为“张良”。

#（6）在例（1）的视图上进行如下操作，将学号为950002的学生的姓名改为“张良”。
UPDATE c_student
SET Sname='张良'
WHERE Sno='20221101';
#student也会改变

SELECT * FROM c_student;
/*
Sno		Sname	Sex	Age	Phonenumber	Sdept
20221101	张良	男	15	188820221101	CS
20221102	刘晨	女	19	188820221102	CS
*/

⑦在（1）的视图上进行如下操作，插入一个学生的信息。

#（7）在（1）的视图上进行如下操作，插入一个学生的信息。
INSERT INTO c_student
VALUES(980013,'李勇','男',15,'188820221126','CS');


/*
Sno		Sname	Sex	Age	Phonenumber	Sdept
20221101	张良	男	15	188820221101	CS
20221102	刘晨	女	19	188820221102	CS
980013		李勇	男	15	188820221126	CS
*/

⑧在（1）的视图进行如下操作，删除学号为980013的学生信息。


#（8）在（1）的视图进行如下操作，删除学号为980013的学生信息。

DELETE FROM c_student
WHERE Sno='20221101';

#有外键约束,删除失败

（2）在Market数据库中，在Market数据库上，试用SQL语句完成以下各项操作：
①请为北京客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称。

#实验三 视图和索引实验   
#实验6 实验6 索引和视图

#2）视图的定义和操作。

#② 在Market数据库中，在Market数据库上，试用SQL语句完成以下各项操作：


#（1）请为北京客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称。
CREATE VIEW v_orders AS(
	SELECT OrderID,GoodsName,Quantity,customers.CustomerID,Cname
	FROM orders
	INNER JOIN goods
	ON orders.`GoodsID`=goods.`GoodsID`
	INNER JOIN customers
	ON orders.`CustomerID`=customers.`CustomerID`
);

SELECT * FROM v_orders;
/*
OrderID	GoodsName	Quantity	CustomerID	Cname
1	Red_Computer	100		1		李彦宏
2	Green_Computer	100		2		马云
3	Blue_Computer	100		3		马化腾
4	Red_Keyboard	100		4		黄峥
5	Green_Keyboard	100		5		天天
6	Blue_Keyboard	100		6		西西
7	Red_Mouse	100		1		李彦宏
8	Green_Mouse	100		2		马云
9	Blue_Mouse	100		3		马化腾

*/

②针对（1）中定义的视图，完成查询，查找客户张三的所有订单信息。

#（2）针对（1）中定义的视图，完成查询，查找客户张三的所有订单信息。
SELECT * FROM v_orders
WHERE Cname='马云';

/*
OrderID	GoodsName	Quantity	CustomerID	Cname
2	Green_Computer	100		2		马云
8	Green_Mouse	100		2		马云
*/

七、评价分析及心得体会

通过本次实验，我理解了索引和视图的概念，掌握了索引的使用方法，掌握视图的定义和使用方法。

八、代码附录

jwgl3.sql

#实验三 视图和索引实验   
#实验6 实验6 索引和视图

# 1）建立索引。对JWGL数据库的学生选课表SC建立索引，要求按照Cno升序、Grade降序建立一个名为SC_ind的索引。

#索引的建立

#① ALTER TABLE ... ADD ...
ALTER TABLE SC ADD INDEX SC_ind ON SC(Cno, Grade DESC);

#② CREATE INDEX ... ON ...
CREATE INDEX SC_ind ON SC(Cno, Grade DESC);

#查索引
SHOW INDEX FROM SC ;
/*
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
sc	0		PRIMARY		1		Sno		A		2		\N		\N		BTREE					YES	\N
sc	0		PRIMARY		2		Cno		A		5		\N		\N		BTREE					YES	\N
sc	1		SC_ind		1		Cno		A		3		\N		\N		BTREE					YES	\N
sc	1		SC_ind		2		Grade		D		5		\N		\N	YES	BTREE					YES	\N

*/


#索引的删除
#方式1：ALTER TABLE .... DROP INDEX ....
ALTER TABLE SC 
DROP INDEX SC_ind;

#方式2：DROP INDEX ... ON ...
DROP INDEX SC_ind ON SC;

#2）视图的定义和操作。

#① 在JWGL数据库中，完成视图的定义和视图上的查询、更新操作：

#（1）  建立计算机系学生的视图，并要求进行修改和插入操作时需保证该视图只能对计算机系的学生进行操作。
CREATE VIEW c_student(Sno,Sname,Sex,Age,Phonenumber,Sdept)
AS(SELECT * FROM student WHERE Sdept='CS');

SELECT * FROM c_student;
/*
Sno     Sname   Sex Age Phonenumber Sdept
20221101    李勇  男   15  188820221101    CS
20221102    刘晨  女   19  188820221102    CS
*/

#（2）建立选择了2号课程且成绩在80分以上的学生视图。
CREATE VIEW sc_student(Sno,Sname,Sex,Age,Phonenumber,Sdept,Cno,Grade)
AS(
    SELECT student.*,sc.`Cno`,sc.`Grade` 
    FROM student 
    INNER JOIN sc
    ON student.`Sno`=sc.`Sno`
    WHERE Cno=2 AND Grade>80
);

SELECT * FROM sc_student;
/*
Sno     Sname   Sex Age Phonenumber Sdept   Cno Grade
20221102    刘晨  女   19  188820221102    CS  2   90
20221101    李勇  男   15  188820221101    CS  2   85

*/

#（3）建立一个反映学生出生年份的视图。
CREATE VIEW y_student(Sno,Sname,Sex,Age,Phonenumber,Sdept,birth)
AS(
    SELECT student.*,(YEAR(CURDATE()) - Age)"birth"
    FROM student 
    
);
SELECT * FROM y_student;

/*
Sno     Sname   Sex Age Phonenumber Sdept   birth
20221101    李勇  男   15  188820221101    CS  2007
20221102    刘晨  女   19  188820221102    CS  2003
20221103    王敏  女   18  188820221103    MA  2004
20221104    张立  男   19  188820221104    IS  2003

*/

#（4）在计算机系的视图c_student上查询年龄在18～20岁之间的学生的学号和姓名。
SELECT Sno,Sname
FROM c_student
WHERE Age>18 AND Age<20;

/*
Sno     Sname
20221102    刘晨
*/


#（5）在（4）的学生视图上查询成绩在90分以上的女学生的学号和姓名。

SELECT Sno,Sname
FROM sc_student
WHERE Sex='女' AND Grade>=90;
/*
Sno     Sname
20221102    刘晨
*/

#（6）在例（1）的视图上进行如下操作，将学号为950002的学生的姓名改为“张良”。
UPDATE c_student
SET Sname='张良'
WHERE Sno='20221101';
#student也会改变

SELECT * FROM c_student;
/*
Sno     Sname   Sex Age Phonenumber Sdept
20221101    张良  男   15  188820221101    CS
20221102    刘晨  女   19  188820221102    CS
*/


#（7）在（1）的视图上进行如下操作，插入一个学生的信息。
INSERT INTO c_student
VALUES(980013,'李勇','男',15,'188820221126','CS');

/*
Sno     Sname   Sex Age Phonenumber Sdept
20221101    张良  男   15  188820221101    CS
20221102    刘晨  女   19  188820221102    CS
980013      李勇  男   15  188820221126    CS
*/

#（8）在（1）的视图进行如下操作，删除学号为980013的学生信息。

DELETE FROM c_student
WHERE Sno='20221101';

#有外键约束,删除失败

Market3.sql

#实验三 视图和索引实验   
#实验6 实验6 索引和视图

#2）视图的定义和操作。

#② 在Market数据库中，在Market数据库上，试用SQL语句完成以下各项操作：

#（1）请为北京客户建立一个订单情况的视图，包括订单编号、商品名称、订货数量、客户编号、客户名称。
CREATE VIEW v_orders AS(
    SELECT OrderID,GoodsName,Quantity,customers.CustomerID,Cname
    FROM orders
    INNER JOIN goods
    ON orders.`GoodsID`=goods.`GoodsID`
    INNER JOIN customers
    ON orders.`CustomerID`=customers.`CustomerID`
);

SELECT * FROM v_orders;
/*
OrderID GoodsName   Quantity    CustomerID  Cname
1   Red_Computer    100     1       李彦宏
2   Green_Computer  100     2       马云
3   Blue_Computer   100     3       马化腾
4   Red_Keyboard    100     4       黄峥
5   Green_Keyboard  100     5       天天
6   Blue_Keyboard   100     6       西西
7   Red_Mouse   100     1       李彦宏
8   Green_Mouse 100     2       马云
9   Blue_Mouse  100     3       马化腾

*/

#（2）针对（1）中定义的视图，完成查询，查找客户张三的所有订单信息。
SELECT * FROM v_orders
WHERE Cname='马云';

/*
OrderID GoodsName   Quantity    CustomerID  Cname
2   Green_Computer  100     2       马云
8   Green_Mouse 100     2       马云
*/

最后

这篇博客能写好的原因是：站在巨人的肩膀上

这篇博客要写好的目的是：做别人的肩膀

开源：为爱发电

学习：为我而行

# 数据库 # java # mysql
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

cover

计算机网络微课堂笔记

avatar 腾讯云开发者社区
cover

Rabbitmq在java中的使用

avatar 腾讯云开发者社区

java try catch 之后定位不到具体报错行_JAVA入门（三）上

点击蓝字｜关注我们一、异常与异常处理异常简介代码中：阻止当前方法或作用域继续实现的，称之为异常java中的所有异常类都继承Throwable类，Exception 的父类是 Throwable编码 环境 用户操作输入出现问题由java虚拟机自动抛出和自动捕获需要手动添加抛出和捕获语句文件找不到ThrowableErrorException虚拟机错误 VirtualMachineError...

avatar 腾讯云开发者社区