2024/6/7

所学时间：2小时

代码行数：500

博客园数：1篇

所学知识：

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 ASC, sname DESC;

SELECT * FROM p ORDER BY pname ASC, weight DESC;

SELECT * FROM j where jname LIKE '%厂%';

SELECT * FROM s WHERE SUBSTRING(sname, 2, 1) = '方';

SELECT MAX(weight) AS max_weight, MIN(weight) AS min_weight, AVG(weight) AS avg_weight FROM p;

SELECT COUNT (pno) AS num_parts, AVG(weight) AS avg_weight FROM p WHERE pname = '螺丝刀';

SELECT pname,sum(qty) as qty FROM spj,p WHERE spj.pno= p.pno and spj.sno = 's1'group by pname;

SELECT pname, sum(qty) as qty FROM spj,p WHERE spj.pno= p.pno and spj.jno= 'j1' group by pname;

SELECT jname FROM j WHERE j.jno NOT IN ( SELECT j.jno FROM p,spj,j WHERE spj.pno=p.pno and spj.jno=j.jno and p.color= '红' and p.pname='螺丝刀' );

SELECT sname FROM s WHERE s.Sno NOT IN ( SELECT s.Sno FROM p,spj,s WHERE spj.pno=p.pno and spj.sno=s.Sno and p.color= '红' and p.pname='螺丝刀' );

SELECT j.jno FROM j,spj where spj.jno=j.jno GROUP BY j.jno HAVING sum( qty) > 500;

SELECT j.jname FROM j,spj where spj.jno=j.jno GROUP BY j.jname HAVING count( pno ) > 3;

SELECT j.jname FROM j,spj,p where spj.jno=j.jno GROUP BY j.jname HAVING count( spj.jno) > count(p.pno);

SELECT s.sname FROM s,spj where spj.sno=s.Sno and spj.jno= 'j1' GROUP BY s.sname;

SELECT s.sname,p.pname,j.jname,spj.qty From s,spj,j,p where spj.jno=j.jno and spj.pno=p.pno and spj.sno=s.Sno;

SELECT p.pname,j.jname,spj.qty From s,spj,j,p where spj.jno=j.jno and spj.pno=p.pno and spj.sno=s.Sno and s.sname='东方红';