实验一 关系数据库标准语言SQL 课后习题/头歌

 第1关：创建数据库

任务要求

建立demo数据库 并显示所有数据库

 #代码开始CREATE DATABASE demo;show DATABASES; #代码结束

第2关：创建表

任务要求

设有一个demo数据库，包括S,P,J,SPJ四个关系模式： S(SNO,SNAME,STATUS,CITY) P(PNO,PNAME,COLOR,WEIGHT) J(JNO,JNAME,CITY) SPJ(SNO,PNO,JNO,QtY) 供应商表S由供应商代码(SNO)、供应商姓名(SNAME)、供应商状态(STATUS)、供应商所在城市(CITY)组成； 零件表P由零件代码(PNO)、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成； 工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成； 供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，表示某供应商 供应某种零件 给某工程项目的数量为QTY。

demo数据库已经创建好，请按下面步骤完成任务。

1. 切换到demo数据库
2. 分别创建s、p、j和spj数据表
3. 查看s、p、j和spj数据表的详细结构

#代码开始 #1. 切换到demo数据库use demo;#2. 分别创建s、p、j和spj数据表-- DROP TABLE j, p,s,spj;CREATE TABLE  s (sno CHAR(2) NULL  ,sname VARCHAR(10) ,status INT(11) NULL ,city VARCHAR(10));-- CREATE TABLE  p(pno CHAR(2)  null ,pname VARCHAR(10),color CHAR(1),weight int);-- CREATE TABLE  j(jno CHAR(2)  null ,jname VARCHAR(10),city VARCHAR(10));-- CREATE TABLE spj(sno CHAR(2) null ,pno CHAR(2),jno CHAR(2),qty INT);#3. 查看s、p、j和spj数据表的详细结构 describe s;  describe p;   describe j;    describe spj; #代码结束

第3关：插入数据

任务要求

• 分别在s、p、j和spj数据表中插入数据
• 分别显示s、p、j和spj数据表的所有数据

s、p、j和spj数据表的原始数据如下所示:

1. ('S1','精益',20,'天津')
2. ('S2','盛锡',10,'北京')
3. ('S3','东方红',30,'北京')
4. ('S4','丰泰盛',20,'天津')
5. ('S5','为民',30,'上海')
7. ('P1','螺母','红',12)
8. ('P2','螺栓','绿',17)
9. ('P3','螺丝刀','蓝',14)
10. ('P4','螺丝刀','红',14)
11. ('P5','凸轮','蓝',40)
12. ('P6','齿轮','红',30)
14. ('J1','三建','北京')
15. ('J2','一汽','长春')
16. ('J3','弹簧厂','天津')
17. ('J4','造船厂','天津')
18. ('J5','机车厂','唐山')
19. ('J6','无线电厂','常州')
20. ('J7','半导体厂','南京')
22. ('S1','P1','J1',200)
23. ('S1','P1','J3',100)
24. ('S1','P1','J4',700)
25. ('S1','P2','J2',100)
26. ('S2','P3','J1',400)
27. ('S2','P3','J2',200)
28. ('S2','P3','J4',500)
29. ('S2','P3','J5',400)
30. ('S2','P5','J1',400)
31. ('S2','P5','J2',100)
32. ('S3','P1','J1',200)
33. ('S3','P3','J1',200)
34. ('S4','P5','J1',100)
35. ('S4','P6','J3',300)
36. ('S4','P6','J4',200)
37. ('S5','P2','J4',100)
38. ('S5','P3','J1',200)
39. ('S5','P6','J2',200)
40. ('S5','P6','J4',500)

use demo;#代码开始#插入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','半导体厂','南京');#插入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);#分别查询s、p、j和spj表的所有数据select * from s;select * from p;select * from j;select * from spj; #代码结束

第4关：数据查询-基础查询

任务要求

请用sql语句完成下面题目的查询。

1. 查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。
2. 查询所有零件的名称、颜色、重量（按零件名称升序排列）.

use demo;#代码开始#1. 查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。select s.sname,s.city from s order by s.sname;#2. 查询所有零件的名称、颜色、重量（按零件名称升序排列）。select p.pname,p.color,p.weight from p order by p.pname;  #代码结束

第5关：数据查询-条件查询

任务要求

请用SQL语句完成下面题目的查询。

1. 查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序排列)。
2. 查询供应工程J1零件P1的供应商号码（按供应商号码升序排列）。
3. 查询使用供应商S1所供应零件的工程号码（按工程号码升序排列）。

注意：数据大小写要保持一致。

use demo;#代码开始#1. 查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序排列)。SELECT distinct spj.sno FROM spj  WHERE jno='J1'  ; #2. 查询供应工程J1零件P1的供应商号码（按供应商号码升序排列）。SELECT  spj.sno FROM spj WHERE spj.jno='J1' AND spj.pno ='P1';#3. 查询使用供应商S1所供应零件的工程号码（按工程号码升序排列）。SELECT spj.jno FROM spj WHERE spj.sno="S1" order by spj.jno;#代码结束

第6关：数据查询-连接查询

任务要求

请用SQL语句完成下面题目的查询。

1. 查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列）。
2. 查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。
3. 查询使用上海产的零件的工程名称（按工程名称升序排列,去除重复数据）。

use demo;#代码开始#1. 查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列）。select  p.pname,spj.qty from p,spj where p.pno=spj.pno and spj.jno='J2' order by p.pname;#2. 查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。SELECT spj.pno FROM spj WHERE spj.sno IN(SELECT s.`SNO` FROM s WHERE s.city='上海') GROUP BY spj.pno;#3. 查询使用上海产的零件的工程名称（按工程名称升序排列,去除重复数据）。SELECT j.jname FROM j WHERE j.jno IN(SELECT spj.jno FROM spj WHERE spj.sno IN(SELECT s.sno FROM s WHERE s.city='上海')) order by j.jname#代码结束

 第7关：数据查询-子查询

任务要求

请用SQL语句完成下面题目的查询。

1. 查询供应工程J1零件为红色的供应商号码（按供应商号码升序排列）。
2. 查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序排列）。

注意：数据大小写要保持一致。

use demo;#代码开始#1. 查询供应工程J1零件为红色的供应商号码（按供应商号码升序排列）。SELECT spj.sno FROM spj WHERE spj.jno='J1' and spj.pno in (SELECT p.pno from p where p.color='红');#2. 查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序排列）。#找到天津生产的零件SELECT DISTINCT jno FROM spj WHERE jno NOT IN (SELECT DISTINCT jno FROM spj NATURAL JOIN p NATURAL JOIN s WHERE city='天津' AND color='红') order by jno asc;#代码结束

第8关：修改数据

任务要求

请用SQL语句完成下面题目。

1. 把全部红色零件的颜色改成蓝色。
2. 由S5供给J4的零件P6改为由S3供应。
3. 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
4. 请将(S2，J6，P4，200)插入供应情况关系表。

数据修改完后请和原始数据进行比对以查看修改结果。

use demo;#代码开始#1. 把全部红色零件的颜色改成蓝色。UPDATE p SET p.color='蓝' WHERE p.color='红';#2. 由S5供给J4的零件P6改为由S3供应。UPDATE spj SET spj.sno='S3'  WHERE spj.sno='S5' AND spj.pno='P6' and spj.jno='J4';#3. 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。DELETE FROM s WHERE s.sno='S2';DELETE FROM spj WHERE spj.sno='S2';#4. 请将(S2，J6，P4，200)插入供应情况关系表。INSERT INTO spj VALUE('S2','P4','J6',200);#代码结束#评测使用，不要删除下面代码select * from s;select * from p;select * from spj;

 第9关：创建视图

任务要求

1. 请为三建工程项目建立一个供应情况的视图(v_spj)，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
2. 查看视图(v_spj)信息。

针对视图(v_spj)完成下列查询： (1) 查询三建工程项目使用的各种零件代码及其总数量(total) 提示：利用聚合函数sum()和分组查询实现，总数量起别名total。 (2) 查询供应商S1的供应情况

use demo;#代码开始#1. 请为“三建”工程项目建立一个供应情况的视图(v_spj)，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。CREATE VIEW v_spj(sno,pno,qty) AS SELECT sno,pno,qty FROM spj WHERE jno=(SELECT jno FROM j WHERE jname='三建');#2. 查看视图(v_spj)信息。desc v_spj;#针对视图(v_spj)完成下列查询：#(1) 查询“三建”工程项目使用的各种零件代码及其总数量(total)#提示：利用聚合函数sum()和分组查询实现，总数量起别名total。SELECT pno,SUM(qty) total  FROM v_spj   GROUP BY pno;#(2) 查询供应商S1的供应情况。sELECT sno,pno,qty FROM v_spj WHERE sno='S1';#代码结束

第10关：定义索引

任务要求

1. 在spj表定义索引IDX_SPJ，包括（sno,pno,jno）。
2. 查看spj表索引定义。

use demo;#代码开始# 1.在spj表定义索引IDX_SPJ，包括（sno,pno,jno）。ALTER TABLE spj ADD INDEX IDX_SPJ(sno,pno,jno);# 2. 查看spj表索引定义。SHOW INDEX FROM spj;#代码结束

来源地址：https://blog.csdn.net/m0_59054762/article/details/129778420