|
|
# task1
|
|
|
|
|
|
CREATE DATABASE dg_mysql;
|
|
|
|
|
|
SHOW DATABASES;
|
|
|
|
|
|
USE dg_mysql;
|
|
|
|
|
|
##分别创建s、p、j和spj数据表
|
|
|
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
|
|
|
);
|
|
|
|
|
|
|
|
|
##查看s、p、j和spj数据表的详细结构
|
|
|
DESCRIBE s;
|
|
|
DESCRIBE p;
|
|
|
DESCRIBE j;
|
|
|
DESCRIBE spj;
|
|
|
|
|
|
##插入s表数据
|
|
|
INSERT INTO s VALUES
|
|
|
('S1','精益',20,'天津'),
|
|
|
('S2','盛锡',10,'北京'),
|
|
|
('S3','东方红',30,'北京'),
|
|
|
('S4','丰泰盛',20,'天津'),
|
|
|
('S5','为民',30,'上海');
|
|
|
|
|
|
##插入p表数据
|
|
|
INSERT INTO p VALUES
|
|
|
('P1','螺母','红',12),
|
|
|
('P2','螺栓','绿',17),
|
|
|
('P3','螺丝刀','蓝',14),
|
|
|
('P4','螺丝刀','红',14),
|
|
|
('P5','凸轮','蓝',40),
|
|
|
('P6','齿轮','红',30);
|
|
|
|
|
|
##插入j表数据
|
|
|
INSERT INTO j VALUES
|
|
|
('J1','三建','北京'),
|
|
|
('J2','一汽','长春'),
|
|
|
('J3','弹簧厂','天津'),
|
|
|
('J4','造船厂','天津'),
|
|
|
('J5','机车厂','唐山'),
|
|
|
('J6','无线电厂','常州'),
|
|
|
('J7','半导体厂','南京');
|
|
|
|
|
|
##-插入spj表数据
|
|
|
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);
|
|
|
|
|
|
##分别查询s、p、j和spj表的所有数据
|
|
|
SELECT* FROM s;
|
|
|
SELECT* FROM p;
|
|
|
SELECT* FROM j;
|
|
|
SELECT* FROM spj;
|
|
|
|
|
|
##1. 查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。
|
|
|
|
|
|
SELECT sname,city FROM s ORDER BY sname ASC;
|
|
|
|
|
|
##2. 查询所有零件的名称、颜色、重量（按零件名称升序排列）。
|
|
|
SELECT pname,color,weight FROM p ORDER BY pname ASC;
|
|
|
|
|
|
##1. 查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序排列)。
|
|
|
SELECT DISTINCT sno FROM j NATURAL JOIN spj WHERE jno='J1' ORDER BY sno ASC;
|
|
|
SELECT DISTINCT sno FROM spj WHERE jno='J1' ORDER BY sno ASC;
|
|
|
|
|
|
##2. 查询供应工程J1零件P1的供应商号码（按供应商号码升序排列）。
|
|
|
SELECT sno FROM spj WHERE jno='J1'AND pno='P1' ORDER BY sno ASC;
|
|
|
|
|
|
##3. 查询使用供应商S1所供应零件的工程号码（按工程号码升序排列）。
|
|
|
SELECT jno FROM spj WHERE sno='S1' ORDER BY jno ASC;
|
|
|
|
|
|
##1. 查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列）。
|
|
|
SELECT pname,qty FROM spj NATURAL JOIN p WHERE jno='J2' ORDER BY pname ASC;
|
|
|
|
|
|
##2. 查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。
|
|
|
SELECT DISTINCT pno FROM s NATURAL JOIN spj WHERE city='上海' ORDER BY pno ASC;
|
|
|
|
|
|
##3. 查询使用上海产的零件的工程名称（按工程名称升序排列,去除重复数据）。
|
|
|
SELECT DISTINCT jname FROM j INNER JOIN spj ON j.`jno`=spj.`jno` INNER JOIN s ON spj.`sno`=s.`sno` WHERE s.city='上海' ORDER BY jname ASC;
|
|
|
|
|
|
##1. 查询供应工程J1零件为红色的供应商号码（按供应商号码升序排列）。
|
|
|
SELECT sno FROM p NATURAL JOIN spj WHERE jno='J1'AND color='红' ORDER BY sno ASC;
|
|
|
|
|
|
##2. 查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序排列）。
|
|
|
SELECT DISTINCT jno FROM j
|
|
|
WHERE jno NOT IN(
|
|
|
SELECT jno FROM s NATURAL JOIN spj NATURAL JOIN p WHERE s.`city`='天津'AND color='红') ORDER BY jno ASC;
|
|
|
|
|
|
##子查询
|
|
|
SELECT DISTINCT jno FROM j
|
|
|
WHERE jno NOT IN(
|
|
|
SELECT jno FROM spj
|
|
|
WHERE sno IN (SELECT sno FROM s WHERE city='天津') AND pno IN(SELECT pno FROM p WHERE color='红'));
|
|
|
|
|
|
##not exists
|
|
|
SELECT DISTINCT jno FROM j
|
|
|
WHERE NOT EXISTS(
|
|
|
SELECT jno FROM spj NATURAL JOIN p NATURAL JOIN s WHERE city='天津'AND color='红'AND j.`jno`=jno) ORDER BY jno ASC;
|
|
|
|
|
|
##1. 把全部红色零件的颜色改成蓝色。
|
|
|
UPDATE p
|
|
|
SET color='蓝'
|
|
|
WHERE color='红';
|
|
|
|
|
|
##2. 由S5供给J4的零件P6改为由S3供应。
|
|
|
UPDATE spj
|
|
|
SET sno='S3'
|
|
|
WHERE sno='S5'AND jno='J4'AND pno='P6';
|
|
|
|
|
|
##3. 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
|
|
|
DELETE FROM s
|
|
|
WHERE sno='S2';
|
|
|
DELETE FROM spj
|
|
|
WHERE sno='s2';
|
|
|
|
|
|
##4. 请将(S2，J6，P4，200)插入供应情况关系表。
|
|
|
INSERT INTO spj(sno,jno,pno,qty)VALUES('S2','J6','P4',200);
|
|
|
|
|
|
##1. 请为“三建”工程项目建立一个供应情况的视图(v_spj)，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
|
|
|
CREATE VIEW v_spj AS
|
|
|
SELECT sno,pno,qty FROM spj
|
|
|
WHERE jno IN(
|
|
|
SELECT jno FROM j
|
|
|
WHERE jname='三建'
|
|
|
);
|
|
|
|
|
|
##(1) 查询“三建”工程项目使用的各种零件代码及其总数量(total)
|
|
|
SELECT pno,SUM(qty) AS total FROM v_spj GROUP BY pno;
|
|
|
|
|
|
##(2) 查询供应商S1的供应情况。
|
|
|
SELECT* FROM v_spj
|
|
|
WHERE sno='S1';
|
|
|
|
|
|
## 1.在spj表定义索引IDX_SPJ，包括（sno,pno,jno）。
|
|
|
CREATE INDEX IDX_SPJ ON spj(sno,pno,jno);
|
|
|
|
|
|
## 2. 查看spj表索引定义。
|
|
|
SHOW INDEX FROM spj;
|
|
|
SELECT* FROM s;
|
|
|
SELECT* FROM p;
|
|
|
SELECT* FROM j;
|
|
|
SELECT* FROM spj; |
