数据库实验报告实验二-交互式SQL

《数据库实验报告实验二-交互式SQL》由会员分享，可在线阅读，更多相关《数据库实验报告实验二-交互式SQL（17页珍藏版）》请在装配图网上搜索。

1、数据库实验报告实验二-交互式SQL数据库系统概论课程之实验二交互式SQL实验目的熟悉数据库的交互式SQL工具。熟悉通过SQL对数据库进展操作。完成作业的上机练习。实验工具RDBMS利用RDBMS及其交互式查询工具来操作SQL语言。实验内容和要求1. 在RDBMS中建立一个数据库，进展实验所要求的各种操作，所有的SQL操作均在此建立的新库里进展。2. 根据以下要求认真填写实验报告，记录所有的实验用例。数据定义熟悉根本表的创立、修改及删除。熟悉索引的创立和删除。熟悉视图的创立和删除。数据操作完成各类查询操作单表查询，连接查询，嵌套查询，集合查询。完成各类更新操作插入数据，修改数据，删除数据。视图的

2、操作视图的定义创立和删除，查询，更新注意更新的条件。实验报告数据库系统概论实验报告题目：交互式SQL姓名王 朔日期20_/3实验环境：操作系统：dows 7； 数据库管理系统： SQL Server 2022实验内容与完成情况： 一、内容：在 SQL Server 2022中建立一个数据库，进展实验所要求的各种操作，所有的SQL操作均在此建立的新库里进展。二、完成情况：1.建立数据库，创立S、P、J、SPJ、四张表格，再给每张表添加数据代码： CREATE TABLE S(SNO CHAR(4) PRIMARY KEY,SNAME CHAR(20),STATUS SMALLINT,CITY C

3、HAR(10);CREATE TABLE P(PNO CHAR(4) PRIMARY KEY,PNAME CHAR(20),COLOR CHAR(10),WEIGHT SMALLINT);CREATE TABLE J(JNO CHAR(4) PRIMARY KEY,JNAME CHAR(20),CITY CHAR(10);CREATE TABLE SPJ(SNO CHAR(4),PNO CHAR(4),JNO CHAR(4),QTY SMALLINT,FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(PNO)REFERENCES P(PNO),FORE

4、IGN KEY(JNO)REFERENCES J(JNO);INSERT INTO S VALUES(#;S1#;,#;精益#;,20,#;天津#;); INSERT INTO S VALUES(#;S2#;,#;盛锡#;,10,#;北京#;); INSERT INTO S VALUES(#;S3#;,#;东方红#;,30,#;北京#;); INSERT INTO S VALUES(#;S4#;,#;丰泰盛#;,20,#;天津#;);INSERT INTO S VALUES(#;S5#;,#;为民#;,30,#;上海#;); INSERT INTO P VALUES(#;P1#;,#;螺母#;

5、,#;红#;,12);INSERT INTO P VALUES(#;P2#;,#;螺栓#;,#;绿#;,17);INSERT INTO P VALUES(#;P3#;,#;螺丝刀#;,#;蓝#;,14);INSERT INTO P VALUES(#;P4#;,#;螺丝刀#;,#;红#;,14);INSERT INTO P VALUES(#;P5#;,#;凸轮#;,#;蓝#;,40);INSERT INTO P VALUES(#;P6#;,#;齿轮#;,#;红#;,30);INSERT INTO J VALUES(#;J1#;,#;三键#;,#;北京#;);INSERT INTO J VALUE

6、S(#;J2#;,#;一汽#;,#;长春#;);INSERT INTO J VALUES(#;J3#;,#;弹簧厂#;,#;天津#;);INSERT INTO J VALUES(#;J4#;,#;造船厂#;,#;天津#;);INSERT INTO J VALUES(#;J5#;,#;机车厂#;,#;唐山#;);INSERT INTO J VALUES(#;J6#;,#;无线电厂#;,#;常州#;);INSERT INTO J VALUES(#;J7#;,#;半导体厂#;,#;南京#;);INSERT INTO SPJ VALUES(#;S1#;,#;P1#;,#;J1#;,20_);INSER

7、T INTO SPJ VALUES(#;S1#;,#;P1#;,#;J3#;,100);INSERT INTO SPJ VALUES(#;S1#;,#;P1#;,#;J4#;,700);INSERT INTO SPJ VALUES(#;S1#;,#;P2#;,#;J2#;,100);INSERT INTO SPJ VALUES(#;S2#;,#;P3#;,#;J1#;,400);INSERT INTO SPJ VALUES(#;S2#;,#;P3#;,#;J2#;,20_);INSERT INTO SPJ VALUES(#;S2#;,#;P3#;,#;J4#;,500);INSERT INTO

8、 SPJ VALUES(#;S2#;,#;P3#;,#;J5#;,400);INSERT INTO SPJ VALUES(#;S2#;,#;P5#;,#;J1#;,400);INSERT INTO SPJ VALUES(#;S2#;,#;P5#;,#;J2#;,100);INSERT INTO SPJ VALUES(#;S3#;,#;P1#;,#;J1#;,20_);INSERT INTO SPJ VALUES(#;S3#;,#;P3#;,#;J1#;,20_);INSERT INTO SPJ VALUES(#;S4#;,#;P5#;,#;J1#;,100);INSERT INTO SPJ V

9、ALUES(#;S4#;,#;P6#;,#;J3#;,300);INSERT INTO SPJ VALUES(#;S4#;,#;P6#;,#;J4#;,20_);INSERT INTO SPJ VALUES(#;S5#;,#;P2#;,#;J4#;,100);INSERT INTO SPJ VALUES(#;S5#;,#;P3#;,#;J1#;,20_);INSERT INTO SPJ VALUES(#;S5#;,#;P6#;,#;J2#;,20_);INSERT INTO SPJ VALUES(#;S5#;,#;P6#;,#;J4#;,500);2.完成课后习题所要求的查询：(1).a.SE

10、LECT DISTINCT SNO FROM SPJ WHERE JNO=#;J1#;SNO1SNO1S12S23S34S45S5b.SELECT DISTINCT SNO FROM SPJ WHERE JNO=#;J1#; AND PNO=#;P1#;SNO1S12SNO1S12S3c.SELECT DISTINCT SNO FROM P,SPJ WHERE JNO=#;J1#; AND SPJ.PNO=#;P1#; AND COLOR=#;红#;SNO1S1SNO1S12S3d.SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN (SELECT JN

11、O FROM S,P,SPJ WHERE S.CITY=#;天津#; AND P.COLOR=#;红#; AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)JNO1J2JNO1J22J5e.(first).供给商S1供给的零件SELECT DISTINCT PNO FROM SPJ WHERE SNO=#;S1#;PNO1PNO1P12P2(second).再求即用P1又用P2的工程号SELECT DISTINCT JNO FROM SPJ WHERE PNO=#;P1#; INTERSECT SELECT DISTINCT JNO FROM SPJ WHERE PNO=

12、#;P2#;JNO1JNO1J4(2).a.SELECT DISTINCT SNO,CITY FROM S SNOCITYSNOCITY1S1天津2S2北京3S3北京4S4天津5S5上海b.SELECT DISTINCT PNO,COLOR,WEIGHT FROM P PNOCOLORPNOCOLORWEIGHT1P1红122P2绿173P3蓝144P4红145P5蓝406P6红30c.SELECT DISTINCT JNO FROM SPJ WHERE SNO=#;S1#; JNO1J1JNO1J12J23J34J4d.SELECT DISTINCT PNO,QTY FROM SPJ WHE

13、RE JNO=#;J2#; PNOQTY1PNOQTY1P21002P320_3P51004P620_e.SELECT DISTINCT PNO FROM SPJ WHERE PNO IN(SELECT PNO FROM S,SPJ WHERE S.SNO=SPJ.SNO AND S.CITY=#;上海#;)PNO1PNO1P22P33P6f.SELECT DISTINCT JNO FROM SPJ WHERE JNO IN(SELECT JNO FROM S,SPJ WHERE S.SNO=SPJ.SNO AND S.CITY=#;上海#;)JNO1JNO1J12J23J4h.SELECT

14、DISTINCT JNO FROM SPJ WHERE JNO NOT IN(SELECT JNO FROM S,SPJ WHERE S.SNO=SPJ.SNO AND S.CITY=#;天津#;)JNO1JNO1J5i.UPDATE P SET COLOR=#;蓝#; WHERE COLOR=#;红#;SELECT _FROM PPNOPNAMEPNOPNAMECOLORWEIGHT1P1螺母蓝122P2螺栓绿173P3螺丝刀蓝144P4螺丝刀蓝145P5凸轮蓝406P6齿轮蓝30j.UPDATE SPJ SET SNO=#;ES3#; WHERE SNO=#;S5#; AND JNO=#;

15、J4#; AND PNO=#;P6#;SELECT _FROM SPJSNOPNOJNOSNOPNOJNOQTY13S4P5J110014S4P6J330015S4P6J420_16S5P2J410017S5P3J120_18S5P6J220_19S3P6J4500k.(first).先删除S表中的记录DELETE FROM S WHERE SNO=#;S2#;SELECT _FROM SSNOSNAMESNOSNAMESTATUSCITY1S1精益20天津2S3东方红30北京3S4丰泰盛20天津4S5为民30上海(second).再删除SPJ表中得记录DELETE FROMSPJ WHERE

16、 SNO=#;S2#;SELECT _FROM SPJSNOPNOSNOPNOJNOQTY1S1P1J120_2S1P1J31003S1P1J47004S1P2J21005S3P1J120_6S3P3J11007S4P5J11008S4P6J3300l.INSERT INTO SPJ VALUES(#;S2#;,#;P4#;,#;J6#;,20_);SELECT _FROM SPJ结果：SNOSNOPNOJNOQTY4S1P2J21005S2P4J620_6S3P1J120_7S3P3J120_8S4P5J11009S4P6J330010S4P6J420_11S5P2J4100出现的问题：出现一些查询不会用相应的程序语句; 屡次插入一样数据时提示错误.解决方案列出遇到的问题和解决方法，列出没有解决的问题：看了答案，有些查询得分两步；有的查询得利用嵌套查询，从而进一步掌握了嵌套查询的要义.主码值不能重复,违背了数据完好性约束条件.第 17 页 共 17 页