请创建表S，P，J，SPJ，并进行相关的查询

文章目录

• • 数据库展示
  • 建表语句以及相关数据
  • 课本习题答案

数据库展示

建表语句以及相关数据

• J表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for j
-- ----------------------------
DROP TABLE IF EXISTS `j`;
CREATE TABLE `j`  (
  `JNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `JNAME` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `CITY` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of j
-- ----------------------------
INSERT INTO `j` VALUES ('J1', '三建', '北京');
INSERT INTO `j` VALUES ('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', '半导体厂', '南京');

SET FOREIGN_KEY_CHECKS = 1;

• P表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for p
-- ----------------------------
DROP TABLE IF EXISTS `p`;
CREATE TABLE `p`  (
  `PNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `PNAME` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `COLOR` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `WEIGHT` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of p
-- ----------------------------
INSERT INTO `p` VALUES ('P1', '螺母', '红', 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);

SET FOREIGN_KEY_CHECKS = 1;

• S表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for s
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s`  (
  `SNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `SNAME` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `STATUS` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `CITY` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of s
-- ----------------------------
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', '上海');

SET FOREIGN_KEY_CHECKS = 1;

• SPJ表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for spj
-- ----------------------------
DROP TABLE IF EXISTS `spj`;
CREATE TABLE `spj`  (
  `SNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `PNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `JNO` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `QTY` int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of spj
-- ----------------------------
INSERT INTO `spj` VALUES ('S1', 'P1', 'J1', 200);
INSERT 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', 200);
INSERT INTO `spj` VALUES ('S2', 'P3', 'J4', 500);
INSERT INTO `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', 200);
INSERT INTO `spj` VALUES ('S3', 'P3', 'J1', 200);
INSERT INTO `spj` VALUES ('S4', 'P5', 'J1', 100);
INSERT INTO `spj` VALUES ('S4', 'P6', 'J3', 300);
INSERT INTO `spj` VALUES ('S4', 'P6', 'J4', 200);
INSERT INTO `spj` VALUES ('S5', 'P2', 'J4', 100);
INSERT INTO `spj` VALUES ('S5', 'P3', 'J1', 200);
INSERT INTO `spj` VALUES ('S5', 'P6', 'J2', 200);
INSERT INTO `spj` VALUES ('S5', 'P6', 'J4', 500);

SET FOREIGN_KEY_CHECKS = 1;

课本习题答案

-- 第四题

-- (1) 求供应工程J1零件的供应商号码SNO。

SELECT SNO FROM SPJ
WHERE JNO = 'J1';

-- (2) 求供应工程J1零件P1的供应商号码SNO。

SELECT SNO FROM SPJ
WHERE JNO = 'J1' AND PNO = 'P1';

-- (3) 求供应工程J1零件为红色的供应商号码SNO。

SELECT SNO FROM SPJ
WHERE JNO = 'J1' AND PNO IN (
	SELECT PNO FROM P 
	WHERE COLOR = '红'
);

-- (4) 求没有使用天津供应商生产的红色零件的工程号JNO。

SELECT JNO FROM J
WHERE NOT EXISTS (
	SELECT *
	FROM SPJ, S, P
	WHERE SPJ.JNO = J.JNO AND SPJ.SNO = S.SNO
		AND SPJ.PNO = P.PNO AND S.CITY = '天津'
		AND P.COLOR = '红'
);

-- (5) 求至少用了供应商S1所供应的全部零件的工程号JNO。

SELECT DISTINCT JNO
FROM SPJ AS SPJZ
WHERE NOT EXISTS (
	SELECT *
	FROM SPJ AS SPJX
	WHERE SNO = 'S1'
	AND NOT EXISTS (
		SELECT *
		FROM SPJ AS SPJY
		WHERE SPJY.PNO = SPJX.PNO
			AND SPJY.JNO = SPJZ.JNO
	)
);


-- 第五题

-- (1) 找出所有供应商的姓名和所在城市。

SELECT SNAME, CITY FROM S;

-- (2) 找出所有零件的名称、颜色、重量。

SELECT PNAME, COLOR, WEIGHT FROM P;

-- (3) 找出使用供应商S1所供应零件的工程号码。

SELECT PNO FROM SPJ
WHERE SNO = 'S1';

-- (4) 找出工程项目J2使用的各种零件的名称及其数量。

SELECT P.PNAME, SPJ.QTY
FROM P, SPJ WHERE P.PNO = SPJ.PNO
			  AND SPJ.JNO = 'J2';

-- (5) 找出上海厂商供应的所有零件号码。

SELECT DISTINCT SPJ.PNO
FROM S, SPJ WHERE S.SNO = SPJ.SNO
			  AND S.CITY = '上海';

-- (6) 找出使用上海产的零件的工程名称。

SELECT J.JNAME
FROM SPJ, S, J
WHERE SPJ.SNO = S.SNO
	AND SPJ.JNO = J.JNO
	AND S.CITY = '上海';
	
-- (7) 找出没有使用天津产的零件的工程名称。

SELECT JNO FROM J
WHERE NOT EXISTS (
	SELECT *
	FROM SPJ
	WHERE SPJ.JNO = J.JNO
		AND SNO IN (
			SELECT SNO
			FROM S
			WHERE CITY = '天津'
		)
);

-- (8) 把全部红色零件的颜色改为蓝色。

UPDATE P SET COLOR = '蓝'
WHERE COLOR = '红';

-- (9) 由S5供给J4的零件P6改为由S3供应，请作必要的修改。

UPDATE SPJ SET SNO = 'S3'
WHERE SNO = 'S5' AND JNO = 'J4' AND PNO = 'P6';
	
-- (10) 从供应商关系中删除S2的记录，并从供应情况关系中删除相关的记录。

DELETE FROM SPJ WHERE SNO = 'S2'; -- 从SPJ中删除相关S2供应商的记录

DELETE FROM S WHERE SNO = 'S2'; -- 删除供应商S2

-- (11) 请将 (S2, J6, P4, 200) 插入供应情况关系。

INSERT INTO SPJ VALUES('S2', 'J6', 'P4', 200);