数据库实验二数据查询

42 阅读 0 评论 0 点赞

一．实验目的

１．理解数据库中数据的其他查询方法和应用；

２．学会各种查询要求的实现；

３．学会各种查询的异同及相互之间的转换方法。

二. 实验内容

1.题目要求

在实验1的基础上，练习其它查询语句的使用，包括计算列、求和、最大、最小值、各类选择条件、字符匹配、分组和排序，连接查询、嵌套查询及EXISTS查询等，体会各种查询的异同及相互之间的转换，体会各种查询的执行过程，为简单综合应用打下良好的基础。

1.查询供应商号码为S1的供应商的名称SNAME，所在城市CITY

2.查询颜色为红色的零件号码

3.查询工程所在地为天津的工程名称JNAME

4.查询供应商号和名称，分别用小写字母和大写字母表示供应商代码

5.查询零件的详细信息，用结果属性名用中文显示。

6.查询供应工程J1零件P1的供应商号SNO

7.查询供应商的名称和所在城市，并按照所在城市升序排序，同一个城市的按照供应商的名称降序排序

8.查询使用供应商S1所供应零件的工程号码

9.查询各种零件的平均重量

10.查询零件的总个数

11.查询所有以“螺”字开头的零件的零件号、零件名和颜色

12.查询各个供应商供应的零件P3总数量

13.供应工程J1红色零件的供应商号SNO

14.工程项目J2使用的各种零件的名称及数量

15.使用上海产的零件的工程的名称

16.所有项目的使用零件的情况（无论有没有使用零件），包括项目代码、项目名称、零件代码和零件数量

17.供应商S1和S3供应的相同的零件编号

18.没有使用天津产的零件的工程号码

19.没有使用天津供应商生产的红色零件的工程号

20.至少用了供应商S1所供应的全部零件的工程号Jno

2.实验结果

代码（去掉题号对应的代码注释运行即可，注意先建表插入数据）：

-- SET  sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

-- CREATE TABLE P
-- (
--   PNO char(20) PRIMARY KEY,
--   PNAME char(20),
--   COLOR char(20),
--   WEIGHT int
-- );
-- 
-- CREATE TABLE J
-- (
--   JNO char(20) PRIMARY KEY,
-- 	JNAME char(20),
-- 	CITY char(20)
-- );
-- 
-- CREATE TABLE S
-- (
--   SNO char(20) PRIMARY KEY,
--   SNAME char(20) UNIQUE,
--   SSTATUS char(20),
--   CITY char(20)
-- );
-- 
-- CREATE TABLE SPJ
-- (
--   SNO char(20),
--   PNO char(20),
--   JNO char(20),
--   QTY int,
--   PRIMARY KEY(SNO,PNO,JNO),
-- 	FOREIGN KEY(SNO)REFERENCES S(SNO),
-- 	FOREIGN KEY(PNO)REFERENCES P(PNO),
-- 	FOREIGN KEY(JNO)REFERENCES J(JNO)
-- );
-- 
-- INSERT
-- INTO S
-- VALUES('S1','精益','20','天津') ,
-- ('S2','盛锡','10','北京'),
-- ('S3','东方红','30','北京'), 
-- ('S4','丰泰盛','20','天津') ,
-- ('S5','为民','30','上海');
-- 
-- INSERT
-- INTO P
-- VALUES('P1','螺母','红',12),
-- ('P2','螺栓','绿',17),
-- ('P3','螺丝刀','蓝',14),
-- ('P4','螺丝刀','红',14),
-- ('P5','凸轮','蓝',40),
-- ('P6','齿轮','红',30);
-- 
-- INSERT
-- INTO J
-- VALUES('J1','三建','北京'),
-- ('J2','一汽','长春'),
-- ('J3','弹簧厂','天津'),
-- ('J4','造船厂','天津'),
-- ('J5','机车厂','唐山'),
-- ('J6','无线电厂','常州'),
-- ('J7','半导体厂','南京');
-- 
-- INSERT
-- INTO spj
-- VALUES('S1','P1','J1',200),
-- ('S1','P1','J3',100),
-- ('S1','P1','J4',700),
-- ('S1','P2','J2',100),
-- ('S2','P3','J1',400),
-- ('S2','P3','J2',200),
-- ('S2','P3','J4',500),
-- ('S2','P3','J5',400),
-- ('S2','P5','J1',400),
-- ('S2','P5','J2',100),
-- ('S3','P1','J1',200),
-- ('S3','P3','J1',200),
-- ('S4','P5','J1',100),
-- ('S4','P6','J3',300),
-- ('S4','P6','J4',200),
-- ('S5','P2','J4',100),
-- ('S5','P3','J1',200),
-- ('S5','P6','J2',200),
-- ('S5','P6','J4',500);

-- 1.查询供应商号码为S1的供应商的名称SNAME,所在城市CITY
-- SELECT SNAME,CITY
-- FROM S
-- WHERE SNO='S1';
-- 
-- 2.查询颜色为红色的零件号码
-- SELECT PNO
-- FROM P
-- WHERE COLOR='红';
-- 
-- 3.查询工程所在地为天津的工程名称 JNAME
-- Select JNAME
-- From J
-- Where City='天津';
-- 
-- 4.查询供应商号和名称，分别用小写字母和大写字母表示供应商代码
-- Select SNO As SNO大写,lower(SNO) AS sno小写,SNO
-- From S;
-- 
-- 5.查询零件的详细信息，用结果属性名用中文显示。
-- Select PNO as 零件号码,PNAME as 零件名,COLOR as 颜色,WEIGHT as 重量
-- From P
-- 
-- 6.查询供应工程 J1 零件 P1 的供应商号SNO
-- Select SNO
-- From SPJ
-- Where JNO='J1' AND PNO='P1';
-- 
-- 7.查询供应商的名称和所在城市，并按照所在城市升序排序，同一个城市的按照供应商的名称降序排序
-- Select SNAME,City 
-- From S
-- Order by City,Sname DESC;
-- 
-- 8.查询使用供应商 S1 所供应零件的工程号码
-- Select Distinct JNO
-- From SPJ
-- Where SNO='S1';
-- 
-- 9.查询各种零件的平均重量
-- SELECT PNO,PNAME,AVG(WEIGHT) AS AVG_WEIGHT
-- FROM P
-- GROUP BY PNO;
-- 
-- 10.查询零件的总个数
-- SELECT Sum(QTY) AS Sum_QTY
-- From SPJ;
-- 
-- 11.查询所有以“螺”字开头的零件的零件号、零件名和颜色
-- SELECT PNO,PNAME,COLOR
-- FROM P
-- WHERE PNAME LIKE '螺%';
-- 
-- 12.查询各个供应商供应的零件P3总数量
-- SELECT SNO,SUM(QTY) AS SUM_QTY_P3
-- FROM SPJ
-- WHERE PNO='P3'
-- GROUP BY SNO;
-- 
-- 13.供应工程J1红色零件的供应商号SNO
-- 解法一
-- Select SNO
-- From SPJ,P
-- Where P.PNO=SPJ.PNO AND COLOR='红' AND JNO='J1';
-- 解法二
-- Select Distinct SNO
-- From SPJ
-- Where Jno='J1'and PNO In
-- (
-- Select PNO
-- From P
-- Where Color='红'
-- );
-- 
-- 14.工程项目J2使用的各种零件的名称及数量
-- SELECT PNAME,QTY
-- FROM P,SPJ
-- WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2';
-- 
-- 15.使用上海产的零件的工程的名称
-- 解法一
-- SELECT DISTINCT JNAME
-- FROM S,SPJ,J
-- WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY='上海';
-- 解法二
-- SELECT DISTINCT JNAME
-- From J,SPJ
-- Where J.JNO=SPJ.JNO AND PNO in
-- (
-- SELECT PNO 
-- From SPJ 
-- Where SNO in
-- (
-- Select SNO
-- From S
-- Where City='上海'
-- )
-- )
-- AND SNO IN
-- (
-- Select SNO
-- From S
-- Where City='上海'
-- );
-- 16.所有项目的使用零件的情况（无论有没有使用零件），包括项目代码、项目名称、零件代码和零件数量
-- Select J.JNO,J.JNAME,SPJ.PNO,QTY
-- From J LEFT OUTER JOIN SPJ ON (J.JNO=SPJ.JNO);
-- 
-- 17.供应商S1和S3供应的相同的零件编号
-- Select DISTINCT PNO
-- From SPJ
-- Where SNO='S1'AND PNO IN
-- (
-- Select PNO
-- From SPJ 
-- Where SNO='S3'
-- ); 
-- 
-- 18.没有使用天津产的零件的工程号码
-- Select JNO
-- From J
-- Where JNO NOT IN
-- (
-- Select JNO
-- From SPJ
-- Where PNO in
-- (
-- Select PNO
-- From SPJ,S
-- Where SPJ.SNO=S.SNO AND S.City='天津'
-- )
-- ); 
-- 
-- 19.没有使用天津供应商生产的红色零件的工程号
-- Select JNO
-- From J
-- Where JNO NOT IN
-- (
-- Select JNO
-- From SPJ
-- Where PNO IN
-- (
-- Select SPJ.PNO
-- From SPJ,S,P
-- Where SPJ.SNO=S.SNO AND P.PNO=SPJ.PNO AND S.CITY='天津' AND P.COLOR='红'
-- )
-- );
-- 
-- 20.至少用了供应商S1所供应的全部零件的工程号Jno
-- SELECT DISTINCT JNO
-- FROM SPJ X
-- WHERE NOT EXISTS
-- (
-- SELECT *
-- FROM SPJ Y
-- WHERE SNO='s1' AND NOT EXISTS
-- (
-- SELECT *
-- FROM SPJ Z
-- WHERE X.JNO= Z.JNO AND Y.PNO=Z.PNO
-- )
-- );

实验结果截图：
建表J、P、S、SPJ并插入数据