数据库：头歌实验一关系数据库标准语言SQL

一、创建数据库

本关任务：

创建数据库
任务要求：

创建demo数据库
并显示全部数据库

1. #代码开始
2. CREATE DATABASE demo;
3. show databases;
4. #代码结束

复制代码

二、创建表

本关任务：

创建数据表

任务要求

设有一个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数据库已经创建好，请按下面步骤完成任务。
切换到demo数据库
分别创建s、p、j和spj数据表
查看s、p、j和spj数据表的具体结构
注意：表名同一用小写。
数据表结构如下：

1. #代码开始
3. #1. 切换到demo数据库
4. use demo;
6. #2. 分别创建s、p、j和spj数据表
7. create table s(
8.         sno char(2),/*定长*/
9.         sname VARCHAR(10),
10.         status int,
11.         city VARCHAR(10)
12. );
14. CREATE TABLE p(
15.         pno char(2),
16.         pname VARCHAR(10),
17.         color char(1),
18.         weight int
19. );
21. create table j(
22.         jno char(2),
23.         jname varchar(10),
24.         city varchar(10)
25. );
27. create table spj(
28. sno char(2),
29. pno char(2),
30. jno char(2),
31. qty int
32. );
35. #3. 查看s、p、j和spj数据表的详细结构
36. describe s;
37. describe p;
38. describe j;
39. describe spj;
41. #代码结束

复制代码

 三、插入数据

本关任务：

在s、p、j和spj数据表中插入数据。
任务要求

分别在s、p、j和spj数据表中插入数据
分别显示s、p、j和spj数据表的全部数据
s、p、j和spj数据表的原始数据如下所示:
('S1','精益',20,'天津')
('S2','盛锡',10,'北京')
('S3','东方红',30,'北京')
('S4','丰泰盛',20,'天津')
('S5','为民',30,'上海')
('P1','螺母','红',12)
('P2','螺栓','绿',17)
('P3','螺丝刀','蓝',14)
('P4','螺丝刀','红',14)
('P5','凸轮','蓝',40)
('P6','齿轮','红',30)
('J1','三建','北京')
('J2','一汽','长春')
('J3','弹簧厂','天津')
('J4','造船厂','天津')
('J5','机车厂','唐山')
('J6','无线电厂','常州')
('J7','半导体厂','南京')
('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)

1. use demo;
3. #代码开始
4. #插入s表数据
5. insert into s values('S1','精益',20,'天津');
6. insert into s values('S2','盛锡',10,'北京');
7. insert into s values('S3','东方红',30,'北京');
8. insert into s values('S4','丰泰盛',20,'天津');
9. insert into s values('S5','为民',30,'上海');
13. #插入p表数据
14. insert into p values('P1','螺母','红',12),
15. ('P2','螺栓','绿',17),
16. ('P3','螺丝刀','蓝',14),
17. ('P4','螺丝刀','红',14),
18. ('P5','凸轮','蓝',40),
19. ('P6','齿轮','红',30);
23. #插入j表数据
24. insert into j values('J1','三建','北京'),
25. ('J2','一汽','长春'),
26. ('J3','弹簧厂','天津'),
27. ('J4','造船厂','天津'),
28. ('J5','机车厂','唐山'),
29. ('J6','无线电厂','常州'),
30. ('J7','半导体厂','南京');
34. #插入spj表数据
35. insert into spj values('S1','P1','J1',200),
36. ('S1','P1','J3',100),
37. ('S1','P1','J4',700),
38. ('S1','P2','J2',100),
39. ('S2','P3','J1',400),
40. ('S2','P3','J2',200),
41. ('S2','P3','J4',500),
42. ('S2','P3','J5',400),
43. ('S2','P5','J1',400),
44. ('S2','P5','J2',100),
45. ('S3','P1','J1',200),
46. ('S3','P3','J1',200),
47. ('S4','P5','J1',100),
48. ('S4','P6','J3',300),
49. ('S4','P6','J4',200),
50. ('S5','P2','J4',100),
51. ('S5','P3','J1',200),
52. ('S5','P6','J2',200),
53. ('S5','P6','J4',500);
57. #分别查询s、p、j和spj表的所有数据
58. select * from s;
59. select * from p;
60. select * from j;
61. select * from spj;
66. #代码结束

复制代码

四、数据查询-基础查询

任务要求

请用SQL语句完成下面题目的查询。
查询全部供应商的姓名和所在城市（按供应商姓名升序分列）。
查询全部零件的名称、颜色、重量（按零件名称升序分列）。
注意：数据大小写要保持一致。

1. use demo;
3. #代码开始
5. #1. 查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。
6. SELECT sname,city FROM s ORDER BY sname;
10. #2. 查询所有零件的名称、颜色、重量（按零件名称升序排列）。
11. SELECT pname,color,weight FROM p ORDER BY pname;
14. #代码结束

复制代码

五、数据查询-条件查询

本关任务：

按题目要求完成数据表的查询。

模糊查询
模糊查询使用like关键字，可以使用通配符进行占位:
_ : 代表单个恣意字符
% : 代表恣意个数字符
任务要求

请用SQL语句完成下面题目的查询。
查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序分列)。
查询供应工程J1零件P1的供应商号码（按供应商号码升序分列）。
查询使用供应商S1所供应零件的工程号码（按工程号码升序分列）。
注意：数据大小写要保持一致。

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

复制代码

六、数据查询-连接查询

本关任务：

按题目要求完成数据表的查询。
任务要求

请用SQL语句完成下面题目的查询。
查询工程项目J2使用的各种零件的名称及其数目（按零件名称升序分列）。
查询上海厂商供应的全部零件号码（去除重复，按零件号码升序分列）。
查询使用上海产的零件的工程名称（按工程名称升序分列,去除重复数据）。
注意：数据大小写要保持一致。

1. use demo;
3. #代码开始
5. #1. 查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列）。
6. SELECT pname,qty FROM spj NATURAL JOIN p WHERE jno='J2' ORDER BY pname;
9. #2. 查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。
10. SELECT DISTINCT pno FROM p NATURAL JOIN spj NATURAL JOIN s  WHERE s.city='上海' ORDER BY pno;
13. #3. 查询使用上海产的零件的工程名称（按工程名称升序排列,去除重复数据）。
15. 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
17. #代码结束

复制代码

七、数据查询-子查询

任务要求

请用SQL语句完成下面题目的查询。
查询供应工程J1零件为红色的供应商号码（按供应商号码升序分列）。
查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序分列）。
注意：数据大小写要保持一致。

1. use demo;
3. #代码开始
5. #1. 查询供应工程J1零件为红色的供应商号码（按供应商号码升序排列）。
6. SELECT DISTINCT sno FROM spj NATURAL JOIN p WHERE jno='J1' AND color='红' ORDER BY sno;
10. #2. 查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序排列）。
11. select distinct jno from spj where jno not in(
12. select jno from spj natural join s natural join p where s.city='天津' and p.color='红'
13. ) ORDER BY jno;
17. #代码结束

复制代码

八、修改数据

任务要求

请用SQL语句完成下面题目。
把全部红色零件的颜色改成蓝色。
由S5供给J4的零件P6改为由S3供应。
从供应商关系中删除供应商号是S2的记载，并从供应情况关系中删除相应的记载。
请将(S2，J6，P4，200)插入供应情况关系表。
数据修改完后请和原始数据进行比对以查看修改效果。

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

复制代码

九、创建视图

任务要求

请为三建工程项目创建一个供应情况的视图(v_spj)，包罗供应商代码(SNO)、零件代码(PNO)、供应数目(QTY)。
查看视图(v_spj)信息。
针对视图(v_spj)完成下列查询：
(1) 查询三建工程项目使用的各种零件代码及其总数目(total)
提示：使用聚合函数sum()和分组查询实现，总数目起别名total。
(2) 查询供应商S1的供应情况。

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

复制代码

十、界说索引

任务要求

在spj表界说索引IDX_SPJ，包罗（sno,pno,jno）。
查看spj表索引界说。
 

1. use demo;
3. #代码开始
5. # 1.在spj表定义索引IDX_SPJ，包括（sno,pno,jno）。
6. CREATE INDEX IDX_SPJ ON spj(sno,pno,jno);
9. # 2. 查看spj表索引定义。
10. SHOW INDEX FROM spj;
12. #代码结束

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。