大白糖奶兔的Blog
大白糖奶兔的Blog
数据库第三章-Practice(单表查询)

建表

理解英语单词有助于记住表的定义
- S(supplier)供应商表:(sno供应商码,sname供应商姓名,status供应商状态,city供应商所在城市)
- P(part)零件表:(pno零件代码,npname零件名,color零件颜色,weight重量)
- J(无理取闹的一个表示方式,没有英语):(jno工程项目代码,jname工程项目名,city工程项目所在城市)
- spj(综合在一起的一张表):(sno供应商代码,pno零件代码,jno工程项目代码,qty供应商sno供应某零件pno给某工程项目jno的数量qty)

创建S表

CREATE TABLE S(
sno CHAR(2) PRIMARY KEY,
sname CHAR(6),
status CHAR(2),
city   CHAR(4));

创建P表

CREATE TABLE P(
pno CHAR(2)  PRIMARY KEY,
pname CHAR(6),
color CHAR(2),
weight   int);

创建P表

CREATE TABLE J
(jno CHAR(2)  PRIMARY KEY,
jname CHAR(8),
 city CHAR(4));
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3cb0026b01963b259b23b3271a9729cc.jpg

创建SPJ表

CREATE TABLE SPJ
       (sno CHAR(2),
        pno CHAR(2),
        jno CHAR(2),
        qty int,
        PRIMARY KEY(sno,pno,jno),
        FOREIGN KEY (sno) REFERENCES S(sno),
        FOREIGN KEY (pno) REFERENCES P(pno),
        FOREIGN KEY (Jno) REFERENCES J(jno));

插入数据

向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','上海');

向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);

向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','半导体厂','南京');
COMMIT;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_59ce101ca79991ae9ea3c5c288b9725c.jpg

向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);
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_d36a0b548a5f4e9ce1c1a3d8ec0f2d6a.jpg

查询

(1)查询重量在10~20之间(包括10和20)的零件信息;

select *
from p
where weight between 10 and 20;

(2)查询不在北京、天津、南京、唐山的工程项目信息;

select *
from j
where city not in('北京','天津','南京','唐山');
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_26e1f4c51f48b429deb30db366651038.jpg

(3)查询名称中包含“船”或“车”的工程项目信息;

select *
from j
where jname like '%船%' or jname like '%车%';

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_0ecb2b37f8bc5b0d24397e9b0de0336f.jpg

不要把like写成=

(4)查询倒数第2个字为“电”的工程项目信息;

select *
from j
where trim(jname) like '%电_';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_74a09225cefc904316d0dcebe18ca95c.jpg

(5)查询供应商S5总共供应了多少种零件;

select sum(qty)
from spj
where sno='S5'
group by sno;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_9b04dd879207daa83385f6f5f317bfde.jpg

(6)查询使用了零件的工程号;

select distinct jno
from spj
where pno is not null;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_660429f753160e7d04d2fbfde2dfa227.jpg

(7)查询使用了零件的工程有几个;

select sum(qty)
from spj
where pno is not null;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_4ccae99d68876b4240e1b685e1aac175.jpg

(8)查询工程J4使用零件P6的总数量。

select sum(qty)
from spj
where jno='J4' and pno='P6';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_018c0ee02391e8d95b2afe8fbc521a88.jpg

(9)查询天津、北京共有几个工程项目。

select count(jno)
from j
where city='天津' or city = '北京';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e8a7041607742facaae84fb70c2b0315.jpg

(10)查询红色零件的最大重量;

select max(weight)
from p
where color='红';
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_7cc85371ba2c7ef8242b98c8ca8ece5c.jpg

(11)查询J表中各城市的工程项目个数,查询结果按工程项目个数降序排序。要求查询结果形式为:

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_5f63b0b3017dd24500707a4d72342d4e.jpg
select city,count(jno)
from j
group by city
order by count(jno) desc,trim(city) asc;

因为这里的长春在DBMS里识别成了(zhang)的音,所以效果不是完全一样

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_e894d0492d38976ba41452b1a5fd1957.jpg

(12)查询供应商S4、S5的零件供应情况,包括工程号、零件号、数量,查询结果按工程号升序排列,工程号相同时按零件号升序排列;

select jno,pno,qty
from spj
where sno='S4' or sno='S5'
order by jno asc,pno asc;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_3b6d41264ce6a873015931413540a0b4.jpg

(13)查询供应商S4、S5的零件供应情况,包括工程号、零件号、数量,查询结果按零件号升序排列,零件号相同时按工程号升序排列;

select jno,pno,qty
from spj
order by pno asc,jno asc;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_254b587ed25180f66ee6dfed33b36a19.jpg

(14)查询工程J1使用的各种零件号及数量,查询结果按数量降序排列;(注意:通过观察结果,看是否需要修改语句)

select pno,sum(qty)
from spj
where jno='J1'
group by pno
order by sum(qty) desc;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_866a7ea2a05c37119afaff699f7f7fad.jpg

验证数据是否正确

https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_87cfffb7099ac101bff193f2a005794c.jpg

(15)查询使用零件总数量超过1000的工程项目号;

select jno
from spj
group by jno
having sum(qty) > 1000;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_f616a49b0d1f02164389bb67be557ba1.jpg

(16)查询使用零件P3总数量超过400的工程项目号及使用数量,查询结果按工程号升序排列;

select jno,qty
from spj
where pno='P3' and qty > 400
order by jno asc;
https://yczbest.cn/wp-content/uploads/2020/08/wp_editor_md_ad8b1becc867089ac6ea86ab1b0a4918.jpg

发表评论

textsms
account_circle
email

大白糖奶兔的Blog

数据库第三章-Practice(单表查询)
建表 理解英语单词有助于记住表的定义 - S(supplier)供应商表:(sno供应商码,sname供应商姓名,status供应商状态,city供应商所在城市) - P(part)零件表:(pno零件代码,npname零件名,c…
扫描二维码继续阅读
2020-08-12
Title - Artist
0:00