实验三数据更新及视图

1.实验目的

1.加深对数据库相关性质的理解；
2.各种约束性理解；
3.学会数据库中数据的更新的方法；
4.学会视图的创建与查询。

2.实验内容

对已建好的各表输入适当的数据并练习数据的插入、删除和修改，注意检测键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束在输入数据及数据的删除、修改时的作用，结合课堂教学体会其含义。同时练习视图的建立、查询与删除等。

1. 向S表中插入一条新数据

实验过程：
用Sql语句插入数据：'S6','智造',10,'济南'

INSERT 
INTO s 
VALUES('S6','智造',10,'济南');

INSERT 
INTO s(SNO,SNAME,STATUS,CITY) 
VALUES('S6','智造',10,'济南');

实验结果：

结果分析：

两种方法，一种是INTO s ，一种是INTO s(SNO,SNAME,STATUS,CITY) ，第二种方法是插入表中指定列中，当括号里面时表的全部列名的时候，这两种方法运行结果是一样的。

2. 供应商S3向J2工程供应零件P2零件350个，将该数据插入相应的表中
实验过程：

INSERT 
INTO spj 
VALUES('S3','P2','J2',350);

INSERT 
INTO spj(SNO,PNO,JNO,QTY) 
VALUES('S3','P2','J2',350);

实验结果：

结果分析：

同第一题，两种方法，一种是INTO spj ，一种是INTO spj(SNO,PNO,JNO,QTY) ，第二种方法是插入表中指定列中，当括号里面时表的全部列名的时候，这两种方法运行结果是一样的。

3. 将J1工程项目名称改为建工集团，城市改为济南
实验过程

UPDATE j
SET JNAME='建工集团',CITY='济南'
WHERE jno='J1';

实验结果

结果分析

数据库更新---插入：update [表名]

set [修改表内的值]

where [限制条件]

4. 把全部红色零件的颜色改成蓝色
实验过程

UPDATE p
SET color='蓝'
WHERE color='红';

实验结果

结果分析

同第三题

5. 由S2供给J1的零件P3改为供给P4零件
实验过程

UPDATE spj
SET PNO='P4'
WHERE SNO='S2' AND JNO='J1'AND PNO='P3';

实验结果

结果分析

同第3题，改变表中的值

6. 删除工程项目J3的所有信息
实验过程
（1） SQL语句直接删除（将外键约束删除时由RESTRICT改为CASCADE，即实现级联删除）

DELETE 
FROM j
WHERE JNO='J3'

（2）创建表时，创建级联操作

（3）用触发器实现级联删除
创建触发器deletej_spj：

CREATE TRIGGER deletej_spj
AFTER
DELETE on spj
for each ROW
BEGIN
DELETE FROM j WHERE JNO=OLD.JNO;
END;

再运行删除语句：

 DELETE from j where JNO='J3';

删除触发器的语句是：

 drop TRIGGER deletej_spj;

（4）也可以用两个SQL语句进行删除，先删除spj表中的，再删除j表中的

DELETE from spj where JNO='J3';
DELETE from j where JNO='J3';

实验结果

运行结果正确

结果分析

起初只写了一句DELETE from j where JNO='J3';运行结果错误，原因是spj表里引用了j表中的j3。所以用了以上4种方法，都可以实现级联删除。结果经过分析，正确。

7. 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录
实验过程
（1） SQL语句直接删除（将外键约束删除时由RESTRICT改为CASCADE，即实现级联删除）

DELETE 
FROM s
WHERE SNO='S2';

创建表时，创建级联操作

用触发器实现级联删除

创建触发器deletes_spj：

CREATE TRIGGER deletes_spj
AFTER
DELETE on spj
for each ROW
BEGIN
DELETE FROM s WHERE SNO=OLD.SNO;
END;

再运行删除语句：
	   DELETE from s where SNO='S2';
删除触发器的语句是：
	   drop TRIGGER deletes_spj;

（4）也可以用两个SQL语句进行删除，先删除spj表中的，再删除j表中的

DELETE from spj where SNO='S2';
DELETE from s where SNO='S2';

实验结果

结果分析

同第6题一样，4种方法都可以实现。

8. 采用SQL命令和图形界面两种方法为建工集团建立一个供应情况视图，包括供应商号（SNO）、零件号（PNO）、供应数量（QTY）。
实验过程
SQL语句：

CREATE VIEW spq
AS
SELECT SNO,PNO,QTY
FROM spj
WHERE JNO in(SELECT JNO FROM j WHERE JNAME='建工集团');

图形界面：
视图—右键—新建视图

实验结果

结果分析

SQL语句和和可视化界面都可以实现，SQL语句实现更方便，可视化界面相当于往固定创建视图的框架里填充内容。

涉及知识点：创建视图

CREATE VIEW [视图名]

SELECT [指定列名]

FROM [指定表]

WHERE [限制条件];

9. 针对以上视图，查询建工集团项目使用的各种零件代码及其数量
实验过程

SELECT PNO,SUM(QTY)
FROM spq
GROUP BY PNO;

实验结果

结果分析

可以看到，结果是正确的。

涉及知识点：查询视图

SELECT PNO,SUM(QTY)

FROM spq

GROUP BY PNO;

与表的查询非常类似，from后面加上视图的名字就可以了。

10. 采用SQL命令和图形界面两种方法为建工集团建立一个供应情况视图，包括供应商名（SName）、零件名（PName）、工程项目名（Jname）、供应数量（QTY）。
实验过程
SQL语句：

CREATE VIEW spjq
AS
SELECT SNAME,PNAME,JNAME,QTY
FROM s,p,j,spj
WHERE s.sno=spj.sno AND p.pno=spj.pno AND j.jno=spj.jno AND jname='建工集团';

实验结果

SQL语句的运行结果：

可视化界面的运行结果

结果分析

与第8题非常类似，只是在第8题的基础上需要将s,p,j,spj 4个表进行连接。SQL语句当中我用的全连接，可视化界面当中使用的是INNER JOIN。

11. 针对以上视图，查询建工集团项目使用的各种零件名称及其数量
实验过程

SELECT PNAME,SUM(QTY)
FROM spjq
GROUP BY PNAME;

实验结果

结果分析

与第9题非常类似。

设有工资表，表结构如下表所示

工资号

岗位工资

薪级工资

职补

考勤

医疗

驻济补

其它补

房租

水费

电费

公积金

所得税

建立一个视图，在视图中包括原工资数据并体现应付工资和实发工资的数额。
实验过程
先建一个工资表，我给它命名为employee,SQL语句如下：

CREATE TABLE `employee`  (
`ID` char(10) COMMENT '工资号',
 		`BASE` double COMMENT '岗位工资',
 		`GRADE` double COMMENT '薪级工资',
`ZB` double  COMMENT '职补',
 		`KQ` double  COMMENT '考勤',
  		`YL` double COMMENT '医疗',
  		`ZJB` double  COMMENT '驻济补',
  		`QTB` double  COMMENT '其它补',
  		`FZ` double COMMENT '房租',
  		`SF` double  COMMENT '水费',
  		`DF` double  COMMENT '电费',
  		`GJJ` double  COMMENT '公积金',
  		`SDS` double  COMMENT '所得税',
  		PRIMARY KEY (`ID`)
)

自己编写了5条数据：

用SQL语句创建一个视图：

CREATE VIEW `员工工资条` (`工资号`,`岗位工资`,`薪级工资`,`职补`,`考勤`,

`医疗`,`驻济补`,`其他补`,`房租`,`水费`,`电费`,

`公积金`,`所得税`,`应付工资`,`实发工资`)

CREATE VIEW `员工工资条` (`工资号`,`岗位工资`,`薪级工资`,`职补`,`考勤`,
							`医疗`,`驻济补`,`其他补`,`房租`,`水费`,`电费`,
							`公积金`,`所得税`,`应付工资`,`实发工资`) 
AS SELECT ID,BASE,GRADE,ZB,KQ,YL,ZJB,QTB,FZ,SF,DF,GJJ,SDS, 
					(BASE+GRADE+ZB+KQ+YL+ZJB+QTB), 
					((BASE+GRADE+ZB+KQ+YL+ZJB+QTB)-(FZ+SF+DF+GJJ+SDS))
FROM employee 
GROUP BY ID;

实验结果