2024/05/25

今日学习时长：3小时

代码行数：没统计

博客数量：1篇

今天主要开始了数据库实验二的操作。

4、在数据库 test1 中进行下列查询操作，将查询语句与结果写入实验报告。 （1）查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列。 （2）查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列。 （3）查询项目名中含有“厂”的项目情况。 （4）查询供应商名称中第二个字为“方”的供应商情况。 （5）查询所有零件中的最大、最小、平均重量。 （6）查询零件中名为“螺丝刀”的零件的种类数、平均重量。 （7）查询供应商 S1 所供应的各种零件的名称和数量。 （8）查询工程 J1 所使用的各种零件的名称和数量。 （9）查询没有使用红色螺丝刀的工程名称。 （10）查询没有供应红色螺丝刀的供应商名称。 （11）查询所用零件数量超过 500 的工程项目号。 （12）查询所用零件种类超过 3 种的工程项目名称。 （13）查询使用了全部零件的工程项目名称。 （14）查询至少供应了工程 J1 所使用的全部零件的供应商名称。 （15）查询供应情况，显示内容为供应商名称、零件名、工程名称、数量。 （16）查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量。 5、请为机车厂工程项目建立一个供应情况的视图，包括供应商代码，零件代码， 供应数量。针对该试图完成如下查询并将 SQL 语句写入实验报告。 （1）查询机车厂工程项目使用的各种零件代码及其数量。 （2）查询处供应商 S2 的供应情况

create database test1;--创建数据库


Create table s(  --供应商
Sno char(2) primary key,
sname varchar(10) not null,
status int,
city varchar(10));

create table p(--零件
pno char(2) primary key,
pname varchar(10) not null,
color varchar(6),
weight int);

create table j( --工程
jno char(2) primary key,
jname varchar(20) not null,
city varchar(10));

create table spj(
sno char(2) foreign key references s(sno),
pno char(2) foreign key references p(pno),
jno char(2) foreign key references j(jno),
qty int,
primary key(sno,pno,jno));

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,'上海');
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);
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','半导体厂','南京');
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);

--查询所有供应商情况，先按城市升序排列，城市相同按供应商名称降序排列。
select * from s order by city,sname desc;
--查询所有零件情况，先按零件名称升序排列，零件名称相同按重量降序排列
select * from p order by pname,weight desc;
--查询项目名中含有“厂”的项目情况
select * from j where jname like '%厂%';
--查询供应商名称中第二个字为“方”的供应商情况
select * from s where sname like '_方%';
--查询所有零件中的最大、最小、平均重量
select max(weight) 最大重量,min(weight) 最小重量,AVG(weight) 平均重量 from p;
--查询零件中名为“螺丝刀”的零件的种类数、平均重量
select count(pno) 零件种类数,avg(weight) 平均重量 from p where pname='螺丝刀';
--查询供应商 S1 所供应的各种零件的名称和数量
select p.pname 零件名称,p.color 颜色,sum(qty) 数量 from spj,p where spj.sno='s1' and spj.pno=p.pno group by p.color,p.pname;
--查询工程 J1 所使用的各种零件的名称和数量
select p.pname 零件名称,p.color 颜色,sum(qty) 数量 from spj,p where spj.jno='j1' and spj.pno=p.pno group by p.color,p.pname;
--查询没有使用红色螺丝刀的工程名称
select DISTINCT  jname from j where jno not in(select jno from spj,p where p.pno=spj.pno and p.pname='螺丝刀' and p.color='红');
--查询没有供应红色螺丝刀的供应商名称
select DISTINCT  sname from s where sno not in(select jno from spj,p where p.pno=spj.pno and p.pname='螺丝刀' and p.color='红');
--查询所用零件数量超过 500 的工程项目号
select jno,sum(spj.qty) 零件总数 from spj group by jno having sum(qty)>500;
--查询所用零件种类超过 3 种的工程项目名称
select j.jname,count(DISTINCT  pno) 种类数 from spj,j where spj.jno=j.jno group by spj.jno,j.jname  having count(DISTINCT pno)>3;
--查询使用了全部零件的工程项目名称
select jname from j where not exists(select pno from p where not exists (select * from spj where spj.jno=j.jno and spj.pno=p.pno));
--查询至少供应了工程 J1 所使用的全部零件的供应商名称
select sname from s where sno in(select sno,count(*)种数 from spj where jno='j1' group by sno having count(*)>(select count(DISTINCT pno) from spj where jno='j1'));
--查询供应情况，显示内容为供应商名称、零件名、工程名称、数量
select sname 供应商,pname 供应零件,jname 项目,qty 数量 from s,p,j,spj where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno;
--查询“东方红”供应商供应情况，显示供应的零件名、工程名称、数量
select sname 供应商,pname 供应零件,jname 项目,qty 数量 from s,p,j,spj where s.sno=spj.sno and p.pno=spj.pno and j.jno=spj.jno and sname='东方红';

--视图
--请为机车厂工程项目建立一个供应情况的视图，包括供应商代码，零件代码， 供应数量
go
create view jview as select spj.sno ,spj.pno,spj.qty from spj,j where spj.jno=j.jno and j.jname='机车厂';

go
--查询机车厂工程项目使用的各种零件代码及其数量
select pno ,qty from jview;
--查询处供应商 S2 的供应情况
select * from jview where sno='s2';