实验一 关系数据库标准语言SQL
【实验目的】
在给定的关系数据库管理系统MySQL环境下，通过实验学生能够：
1、MySQL环境的认识及熟悉，了解其对标准SQL的扩充。
2、掌握MySQL环境下数据定义包括数据库、基本表、视图、索引定义。
3、掌握MySQL环境下数据操纵包括数据插入、删除、修改。
4、掌握MySQL环境下数据查询及其各种变化。
【实验性质】验证性实验
【实验学时】4H
【实验内容】
设有一个SPJ数据库，包括S,P,J,SPJ四个关系模式：
S(SNO,SNAME,STATUS,CITY)
P(PNO,PNAME,COLOR,WEIGHT)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成；
零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；
工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成；
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，表示某供应商 供应某种零件 给某工程项目的数量为QTY。
今有若干数据如下：
请根据以上内容完成下面题目，写出相应的SQL语句。(空位不够请自行续页)
1.创建表。(10分)
CREATE TABLE S( SNO CHAR(2),SNAME VARCHAR(10),STATUS INT,CITY varchar(10));CREATE TABLE P(PNO CHAR(2),PNAME VARCHAR(10),COLOR char(1), WEIGHT INT);CREATE TABLE J(JNO CHAR(2),JNAME VARCHAR(10),CITY VARCHAR(10));CREATE TABLE SPJ(SNO CHAR(2),PNO CHAR(2),JNO CHAR(2),QTY INT);
2.插入数据。(5分)
INSERTINTOsVALUES('S1','精益',20,'天津',0);INSERTINTOsVALUES('S2','盛锡',10,'北京',0);INSERTINTOsVALUES('S3','东方红',30,'北京',0);INSERTINTOsVALUES('S4','丰泰盛',20,'天津',0);INSERTINTOsVALUES('S5','为民',30,'上海',0);INSERTINTOpVALUES('P1','螺母','红',12);INSERTINTOpVALUES('P2','螺栓','绿',17);INSERTINTOpVALUES('P3','螺丝刀','蓝',14);INSERTINTOpVALUES('P4','螺丝刀','红',14);INSERTINTOpVALUES('P5','凸轮','蓝',40);INSERTINTOpVALUES('P6','齿轮','红',30);INSERTINTOjVALUES('J1','三建','北京');INSERTINTOjVALUES('J2','一汽','长春');INSERTINTOjVALUES('J3','弹簧厂','天津');INSERTINTOjVALUES('J4','造船厂','天津');INSERTINTOjVALUES('J5','机车厂','唐山');INSERTINTOjVALUES('J6','无线电厂','常州');INSERTINTOjVALUES('J7','半导体厂','南京');INSERTINTOspjVALUES('S1','P1','J1',200);INSERTINTOspjVALUES('S1','P1','J3',100);INSERTINTOspjVALUES('S1','P1','J4',700);INSERTINTOspjVALUES('S1','P2','J2',100);INSERTINTOspjVALUES('S2','P3','J1',400);INSERTINTOspjVALUES('S2','P3','J2',200);INSERTINTOspjVALUES('S2','P3','J4',500);INSERTINTOspjVALUES('S2','P3','J5',400);INSERTINTOspjVALUES('S2','P5','J1',400);INSERTINTOspjVALUES('S2','P5','J2',100);INSERTINTOspjVALUES('S3','P1','J1',200);INSERTINTOspjVALUES('S3','P3','J1',200);INSERTINTOspjVALUES('S4','P5','J1',100);INSERTINTOspjVALUES('S4','P6','J3',300);INSERTINTOspjVALUES('S4','P6','J4',200);INSERTINTOspjVALUES('S5','P2','J4',100);INSERTINTOspjVALUES('S5','P3','J1',200);INSERTINTOspjVALUES('S5','P6','J2',200);INSERTINTOspjVALUES('S5','P6','J4',500);
3.请用SQL语句完成下面题目的查询。(共50分，每题5分)
(1) 找出所有供应商的姓名和所在城市。
SELECT S.SNAME,S.CITY FROM s;
(2) 求供应工程J1零件P1的供应商号码SNO。
SELECT SPJ.SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'
(3) 求供应工程J1零件为红色的供应商号码SNO。
SELECT spj.JNO,P.COLOR,spj.SNO FROM spj INNER JOIN P ON spj.PNO=P.PNO WHERE spj.JNO='J1' AND P.COLOR='红';
(4) 找出工程项目J2使用的各种零件的名称及其数量。
SELECT spj.JNO,P.PNAME,SPJ.QTY FROM SPJ INNER JOIN P ON spj.PNO=P.PNO WHERE spj.JNO='J2';
(5) 找出使用上海产的零件的工程名称。
SELECT S.CITY,J.JNAME FROM S INNER JOIN SPJ ON SPJ.SNO=S.SNO INNER JOIN J ON spj.JNO=J.JNO WHERE S.CITY='上海';
(6) 求没有使用天津供应商生产的红色零件的工程号JNO。
SELECT S.SNO,S.CITY,P.COLOR,spj.JNO FROM S INNER JOIN spj ON spj.SNO=S.SNO INNER JOIN P ON P.PNO=spj.PNO WHERE !(S.CITY='天津') AND !(P.COLOR='红');
(7) 把全部红色零件的颜色改成蓝色。
SELECT * FROM P;UPDATE p SET COLOR='蓝' WHERE COLOR='红';SELECT * FROM P;
(8) 由S5供给J4的零件P6改为由S3供应。
UPDATE SPJ SET SNO='S3'WHERE SNO='S5' AND JNO='J4' AND PNO='P6';
(9) 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
DELETE s,spj FROM S INNER JOIN spj ON S.SNO=spj.SNO WHERE S.SNO='S2';
(10) 请将(S2，J6，P4，200)插入供应情况关系。
INSERT INTO spj(SNO,JNO,PNO,QTY) VALUES('S2','J6','P4','200');
4、请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。(10分)
针对该视图完成下列查询：
CREATE VIEW st_spj_sno_pno_qty AS SELECT SNO,PNO,QTY FROM SPJ;
(1) 找出三建工程项目使用的各种零件代码及其数量。(5分)
SELECT PNO,QTY FROM st_spj_sno_pno_qty ;
(2) 找出供应商S1的供应情况。(5分)
SELECT * FROM st_spj_sno_pno_qty WHERE SNO='S1';
5、在表SPJ定义索引IDX_SPJ，包括（SNO,PNO,JNO）。比较定义索引前后求供应工程J1零件P1的供应商号码SNO的区别。(10分)
CREATE INDEX IDX_SPJ ON SPJ(SNO,PNO,JNO);
【实验总结】
请根据实验内容谈谈你对本次实验的收获、感想，或提出你对实验内容的建议等等。(5分)
