第三章数据库作业

2022-11-16

Word count: 2.7k | Reading time≈ 12 min

第三章数据库作业

作业要求

根据SPJ数据库，给出下列各题的命令语句，将执行的语句及结果复制到word文档中，（查询结果可复制，选中查询结果后，点击右键，在快捷菜单中选择相应选项）：

针对SPJ数据库，查询S表中供应商所在城市为天津的SNO，SNAME， CITY 。

\1. 查询工程项目所在城市有哪些？

\2. 查询SPJ表中供应数量大于200的记录的SNO，PNO，JNO，QTY，分别以供应商代码，零件代码，工程项目代码，供应数量显示

\3. 查询工程项目所在城市为北京、天津、南京的工程项目信息。

\4. 查询所有名称第一个字为“螺”的零件的信息

\5. 查询第二个字为“轮”的零件的信息

\6. 查询红色且重量大于20的零件的信息

\7. 查询红色或重量大于20的零件的信息

\8. 查询工程项目所在地不为北京和天津的工程项目信息

\9. 从SPJ表中查询工程项目代码为J1的记录信息，并按照供应数量递增排序。

\10. 统计SPJ表中使用P3零件的总数量，最小数量、最多数量、平均数量

13．统计SPJ表中使用各种零件的总数量，最小数量、最多数量、平均数量

14．统计SPJ表中各工程项目分别使用了几种零件

15．统计SPJ表中各工程项目分别使用各种零件的总数量

16．查询SPJ表中使用零件种类超过两种的工程项目。

17．查询SPJ表中各供应商分别给几个项目供应零件

18．查询SPJ表中各供应商分别供应每种零件给几个项目，及供应每种零件的总数量

19．查询SPJ表中所有供应商代码，供应商姓名，零件名，工程项目名，供应数量，且供应数量>200的项目信息

20.查询所有工程项目代码，工程项目名，所在城市，及使用的各零件代码，和数量

以下使用嵌套查询实现

21．查询S1供应商供应的工程项目代码，工程项目名，所在城市

\22. 查询使用蓝色零件的项目的工程项目代码，工程项目名，所在城市

23．查询SPJ表中使用数量超过200的零件代码，零件名，颜色，

24．查询和s1在同一个城市的供应商代码，供应商名称

\25. 查询所有使用了p1零件项目代码，名称,城市（用exists查询实现）

26．查询没有使用p1零件的项目代码，名称,城市(用not exists查询)

作业分析

是简单的数据库操作的考察，sql语句的考察，题目中的SPJ表在数的课后题里，需要自己创建。

创建SPJ数据库

创建数据库代码，用的是mysql数据库不知道sql是否和其他的数据库通用

/*
SQLyog Professional v12.09 (64 bit)
MySQL - 8.0.26 : Database - spj
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`spj` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `spj`;

/*Table structure for table `j` */

DROP TABLE IF EXISTS `j`;

CREATE TABLE `j` (
  `JNO` varchar(4) DEFAULT NULL COMMENT '项目代码',
  `JNAME` varchar(10) DEFAULT NULL COMMENT '工程项目名',
  `CITY` varchar(10) DEFAULT NULL COMMENT '工程项目所在城市'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `j` */

insert  into `j`(`JNO`,`JNAME`,`CITY`) values ('J1','三建','北京'),('J2','一汽','长春'),('J3','弹簧厂','天津'),('J4','造船厂','天津'),('J5','机车厂','唐山'),('J6','无线电厂','常州'),('J7','半导体厂','南京');

/*Table structure for table `p` */

DROP TABLE IF EXISTS `p`;

CREATE TABLE `p` (
  `PNO` varchar(4) DEFAULT NULL COMMENT '零件代码',
  `PNAME` varchar(10) DEFAULT NULL COMMENT '零件名',
  `COLOR` varchar(10) DEFAULT NULL COMMENT '颜色',
  `WEIGHT` int DEFAULT NULL COMMENT '重量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `p` */

insert  into `p`(`PNO`,`PNAME`,`COLOR`,`WEIGHT`) values ('P1','螺母','红',12),('P2','螺栓','绿',17),('P3','螺丝刀','蓝',14),('P4','螺丝刀','红',14),('P5','凸轮','蓝',40),('P6','齿轮','红',30);

/*Table structure for table `s` */

DROP TABLE IF EXISTS `s`;

CREATE TABLE `s` (
  `SNO` varchar(4) DEFAULT NULL COMMENT '供应商代码',
  `SNAME` varchar(10) DEFAULT NULL COMMENT '供应商姓名',
  `STATUS` int DEFAULT NULL COMMENT '供应商状态',
  `CITY` varchar(3) DEFAULT NULL COMMENT '城市'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `s` */

insert  into `s`(`SNO`,`SNAME`,`STATUS`,`CITY`) values ('S1','精益',20,'天津'),('S2','盛锡',10,'北京'),('S3','东方红',30,'北京'),('S4','丰泰盛',20,'天津'),('S5','为民',30,'上海');

/*Table structure for table `spj` */

DROP TABLE IF EXISTS `spj`;

CREATE TABLE `spj` (
  `SNO` varchar(4) DEFAULT NULL COMMENT '供应商代码',
  `PNO` varchar(4) DEFAULT NULL COMMENT '零件代码',
  `JNO` varchar(4) DEFAULT NULL COMMENT '工程项目代码',
  `QTY` int DEFAULT NULL COMMENT '供应数量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `spj` */

insert  into `spj`(`SNO`,`PNO`,`JNO`,`QTY`) 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','J5',400),('S2','P5','J2',100),('S2','P3','J4',500),('S2','P5','J1',400),('S3','P3','J1',200),('S3','P1','J1',200),('S4','P6','J3',300),('S4','P6','J4',200),('S4','P5','J1',100),('S5','P3','J1',200),('S5','P6','J2',200),('S5','P6','J4',500),('S5','P2','J4',100);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

操作

1.针对SPJ数据库，查询S表中供应商所在城市为天津的SNO，SNAME， CITY

1
2
3

SELECT SNO,SNAME,CITY
FROM S
WHERE CITY = '天津';

2.查询工程项目所在城市有哪些

1 2	SELECT CITY FROM j;

3. 查询SPJ表中供应数量大于200的记录的SNO，PNO，JNO，QTY，分别以供应商代码，零件代码，工程项目代码，供应数量显示

1
2
3

SELECT SNO AS `供应商代码`,PNO AS `零件代码`,JNO AS `工程项目代码`,QTY AS `供应数量显示`
FROM spj
WHERE QTY > 200;

4.查询工程项目所在城市为北京、天津、南京的工程项目信息

1
2
3

SELECT * 
FROM j
WHERE CITY = '北京' OR CITY = '天津' OR CITY = '南京';

5.查询所有名称第一个字为“螺”的零件的信息

1
2
3

SELECT *
FROM p
WHERE PNAME LIKE '螺%';

6.查询第二个字为“轮”的零件的信息

1
2
3

SELECT *
FROM p
WHERE PNAME LIKE '_轮%';

7. 查询红色且重量大于20的零件的信息

1
2
3

SELECT *
FROM p
WHERE COLOR = '红' AND WEIGHT > 20;

8. 查询红色或重量大于20的零件的信息

1
2
3

SELECT *
FROM p
WHERE COLOR = '红' OR WEIGHT > 20;

9.查询工程项目所在地不为北京和天津的工程项目信息

1
2
3

SELECT *
FROM j
WHERE CITY <> '北京' AND CITY <> '天津';

10.从SPJ表中查询工程项目代码为J1的记录信息，并按照供应数量递增排序。

SELECT * 
FROM spj
WHERE JNO = 'J1'
ORDER BY QTY ASC;

11. 统计SPJ表中使用P3零件的总数量，最小数量、最多数量、平均数量

1
2
3

SELECT SUM(QTY) AS `总数量`,MIN(QTY) AS `最小数量`,MAX(QTY) AS `最大数量`,AVG(QTY) AS `平均数量`
FROM spj
WHERE PNO = 'P3';

12.统计SPJ表中使用各种零件的总数量，最小数量、最多数量、平均数量

1
2
3

SELECT spj.SNO AS `供应商代码`,SUM(QTY) AS `总数量`,MIN(QTY) AS `最小数量`,MAX(QTY) AS `最大数量`,AVG(QTY) AS `平均数量`
FROM spj
GROUP BY spj.SNO

13.统计SPJ表中各工程项目分别使用了几种零件

1
2
3

SELECT spj.`JNO`,COUNT(PNO)
FROM spj
GROUP BY JNO;

14.统计SPJ表中各工程项目分别使用各种零件的总数量

SELECT spj.`JNO`,spj.`PNO`,SUM(QTY)
FROM spj
GROUP BY JNO,PNO
ORDER BY JNO;

15.查询SPJ表中使用零件种类超过两种的工程项目

SELECT spj.`JNO`,COUNT(PNO)
FROM spj
GROUP BY JNO 
HAVING COUNT(PNO) > 2;

16.查询SPJ表中各供应商分别给几个项目供应零件

1
2
3

SELECT SNO ,COUNT(JNO)
FROM spj
GROUP BY SNO;

17.查询SPJ表中各供应商分别供应每种零件给几个项目，及供应每种零件的总数量

1
2
3

SELECT SNO ,JNO,SUM(QTY)
FROM spj
GROUP BY SNO,JNO;

18.查询SPJ表中所有供应商代码，供应商姓名，零件名，工程项目名，供应数量，且供应数量>200的项目信息

SELECT spj.`SNO` AS `供应商代码`,s.`SNAME` AS `供应商名`
,p.`PNAME` AS `零件名`,j.`JNAME` AS `项目名`
,spj.`QTY` AS 供应数量
FROM spj,s,p,j
WHERE spj.`SNO` = s.`SNO` AND spj.`JNO` = j.`JNO`
AND spj.`PNO` = p.`PNO` AND QTY > 200;

19. 查询所有工程项目代码，工程项目名，所在城市，及使用的各零件代码，和数量

SELECT 
spj.`JNO` AS `工程项目代码`
,j.`JNAME` AS `工程项目名`
,j.`CITY` AS `所在城市`
,spj.`PNO` AS `零件代码`
,spj.`QTY` AS `数量`
FROM spj,j
WHERE spj.`JNO` = j.`JNO`;

==以下使用嵌套查询实现==

20.查询S1供应商供应的工程项目代码，工程项目名，所在城市

SELECT j.`JNO`,j.`JNAME`,j.`CITY`
FROM j
WHERE j.`JNO` IN (
	SELECT spj.`JNO`
	FROM spj
	WHERE spj.`SNO` = 'S1'	
	);

21.查询使用蓝色零件的项目的工程项目代码，工程项目名，所在城市

SELECT j.`JNO`,j.`JNAME`,j.`CITY`
FROM j
WHERE j.`JNO` IN(
	SELECT j.`JNO`
	FROM spj
	WHERE spj.`PNO` IN(
		SELECT p.`PNO`
		FROM p
		WHERE p.`COLOR` = '蓝'
		
		)
	);

22.查询SPJ表中使用数量超过200的零件代码，零件名，颜色

SELECT p.`PNO`,p.`PNAME`,p.`COLOR`
FROM p
WHERE p.`PNO` IN(
	SELECT spj.`PNO`
	FROM spj
	WHERE spj.`QTY` > 200
	
);

23.查询和s1在同一个城市的供应商代码，供应商名称

SELECT X.`SNO`,X.`SNAME`
FROM s X
WHERE X.`CITY` IN (
	SELECT Y.`city`
	FROM s Y
	WHERE Y.`SNO` = 'S1'
);

24.查询所有使用了p1零件项目代码，名称,城市（用exists查询实现）

SELECT j.`JNO`,j.`JNAME`,j.`CITY`
FROM j
WHERE EXISTS
(
	SELECT spj.`JNO`
	FROM spj
	WHERE j.`JNO` = spj.`JNO` AND spj.`PNO` = 'p1'
);

25.查询没有使用p1零件的项目代码，名称,城市(用not exists查询)

SELECT j.`JNO`,j.`JNAME`,j.`CITY`
FROM j
WHERE NOT EXISTS
(
	SELECT spj.`JNO`
	FROM spj
	WHERE j.`JNO` = spj.`JNO` AND spj.`PNO` = 'p1'
);

Donate

Copyright： Copyright is owned by the author. For commercial reprints, please contact the author for authorization. For non-commercial reprints, please indicate the source.