视图"/>
数据库实验三之数据更新与视图
实验三 数据更新及视图
一.实验目的
1.加深对数据库相关性质的理解;
2.各种约束性理解;
3.学会数据库中数据的更新的方法;
4.学会视图的创建与查询。
二. 实验内容
对已建好的各表输入适当的数据并练习数据的插入、删除和修改,注意检测键码约束、外码约束、非空约束、唯一约束、取值约束等各种约束在输入数据及数据的删除、修改时的作用,结合课堂教学体会其含义。同时练习视图的建立、查询与删除等。
1.向S表中插入一条新数据
2.供应商S3向J2工程供应零件P2零件350个,将该数据插入相应的表中
3.将J1工程项目名称改为建工集团,城市改为济南
4.把全部红色零件的颜色改成蓝色
5.由S2供给J1的零件P3改为供给P4零件
6. 删除工程项目J3的所有信息
7. 从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录
8.采用SQL命令和图形界面两种方法为建工集团建立一个供应情况视图,包括供应商号(SNO)、零件号(PNO)、供应数量(QTY)。
9.针对以上视图,查询建工集团项目使用的各种零件代码及其数量
10. 采用SQL命令和图形界面两种方法为建工集团建立一个供应情况视图,包括供应商名(SName)、零件名(PName)、工程项目名(Jname)、供应数量(QTY)。
11. 针对以上视图,查询建工集团项目使用的各种零件名称及其数量
12. 设有工资表,表结构如下表所示
工资号 | 岗位工资 | 薪级工资 | 职补 | 考勤 | 医疗 | 驻济补 | 其它补 | 房租 | 水费 | 电费 | 公积金 | 所得税 |
建立一个视图,在视图中包括原工资数据并体现应付工资和实发工资的数额。
三. 实验过程及代码
- 向S表中插入一条新数据
--用Sql语句插入数据:'S6','智造',10,'济南'
INSERT
INTO s
VALUES('S6','智造',10,'济南');
--或
INSERT
INTO s(SNO,SNAME,STATUS,CITY)
VALUES('S6','智造',10,'济南');
- 供应商S3向J2工程供应零件P2零件350个,将该数据插入相应的表中
INSERT
INTO spj
VALUES('S3','P2','J2',350);
--或
INSERT
INTO spj(SNO,PNO,JNO,QTY)
VALUES('S3','P2','J2',350);
- 将J1工程项目名称改为建工集团,城市改为济南
UPDATE j
SET JNAME='建工集团',CITY='济南'
WHERE jno='J1';
- 把全部红色零件的颜色改成蓝色
UPDATE p
SET color='蓝'
WHERE color='红';
- 由S2供给J1的零件P3改为供给P4零件
UPDATE spj
SET PNO='P4'
WHERE SNO='S2' AND JNO='J1'AND PNO='P3';
- 删除工程项目J3的所有信息
--(1) SQL语句直接删除(将外键约束删除时由RESTRICT改为CASCADE,即实现级联删除)
DELETE
FROM j
WHERE JNO='J3'
--(2) 创建表时,创建级联操作
-- SPJ表
CREATE TABLE SPJ (SNO CHAR (2),PNO CHAR (2),JNO CHAR (7),QTY INT,PRIMARY KEY (SNO, PNO, JNO),FOREIGN KEY (SNO) REFERENCES s (SNO)ON DELETE CASCADE,FOREIGN KEY (PNO) REFERENCES p (PNO)ON DELETE CASCADE,FOREIGN KEY (JNO) REFERENCES j (JNO)ON DELETE CASCADE
);--(3) 用触发器实现级联删除
--创建触发器deletej_spj:CREATE TRIGGER deletej_spjAFTERDELETE on spjfor each ROWBEGINDELETE 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';
- 从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录
--(1) SQL语句直接删除(将外键约束删除时由RESTRICT改为CASCADE,即实现级联删除)DELETE FROM sWHERE SNO='S2';
--(2) 创建表时,创建级联操作
--见上一题
--(3) 用触发器实现级联删除
--创建触发器deletes_spj:CREATE TRIGGER deletes_spjAFTERDELETE on spjfor each ROWBEGINDELETE 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';
- 采用SQL命令和图形界面两种方法为建工集团建立一个供应情况视图,包括供应商号(SNO)、零件号(PNO)、供应数量(QTY)。
SQL语句:
--SQL语句:CREATE VIEW spqASSELECT SNO,PNO,QTYFROM spjWHERE JNO in(SELECT JNO FROM j WHERE JNAME='建工集团');
图形界面: 视图—右键—新建视图
- 针对以上视图,查询建工集团项目使用的各种零件代码及其数量
SELECT PNO,SUM(QTY)
FROM spq
GROUP BY PNO;
- 采用SQL命令和图形界面两种方法为建工集团建立一个供应情况视图,包括供应商名(SName)、零件名(PName)、工程项目名(Jname)、供应数量(QTY)。
SQL语句:
--SQL语句:CREATE VIEW spjqASSELECT SNAME,PNAME,JNAME,QTYFROM s,p,j,spjWHERE s.sno=spj.sno AND p.pno=spj.pno AND j.jno=spj.jno AND jname='建工集团';
图形界面:
- 针对以上视图,查询建工集团项目使用的各种零件名称及其数量
SELECT PNAME,SUM(QTY)
FROM spjq
GROUP BY PNAME;
- 设有工资表,表结构如下表所示
工资号 | 岗位工资 | 薪级工资 | 职补 | 考勤 | 医疗 | 驻济补 | 其它补 | 房租 | 水费 | 电费 | 公积金 | 所得税 |
建立一个视图,在视图中包括原工资数据并体现应付工资和实发工资的数额。
先建立一个工资表,我命名为employee:
--先建一个工资表,我给它命名为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`)
)
-- COMMENT是注释的意思
我给它编写了几条数据:
用SQL语句创建一个视图:
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;
/*
这道题的难点主要是应发工资和实发工资的计算问题。
使用公式:
应发工资=岗位工资+薪级工资+职补+考勤+医疗+驻济补+其他补
实发工资=应发工资-房租-水费-电费-公积金-所得税
*/
视图:
更多推荐
数据库实验三之数据更新与视图
发布评论