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

一、创建数据库

本关任务：

建立数据库

任务要求：

建立demo数据库

并显示所有数据库

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

二、创建表

本关任务：

建立数据表

任务要求

设有一个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. 切换到demo数据库
use demo;

#2. 分别创建s、p、j和spj数据表
create table s(
	sno char(2),/*定长*/
	sname VARCHAR(10),
	status int,
	city VARCHAR(10)
);

CREATE TABLE p(
	pno char(2),
	pname VARCHAR(10),
	color char(1),
	weight int
);

create table j(
	jno char(2),
	jname varchar(10),
	city varchar(10)
);

create table spj(
sno char(2),
pno char(2),
jno char(2),
qty int
);


#3. 查看s、p、j和spj数据表的详细结构
describe s;
describe p;
describe j;
describe spj;
 
 #代码结束

 三、插入数据

本关任务：

在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)

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),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30);



#插入j表数据
insert into j values('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京');



#插入spj表数据
insert into spj values('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);



#分别查询s、p、j和spj表的所有数据
select * from s;
select * from p;
select * from j;
select * from spj;



 
 #代码结束

四、数据查询-基础查询

任务要求

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

查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。
查询所有零件的名称、颜色、重量（按零件名称升序排列）。
注意：数据大小写要保持一致。

use demo;

#代码开始

#1. 查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。
SELECT sname,city FROM s ORDER BY sname;



#2. 查询所有零件的名称、颜色、重量（按零件名称升序排列）。
SELECT pname,color,weight FROM p ORDER BY pname;

 
 #代码结束

五、数据查询-条件查询

本关任务：

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

模糊查询
模糊查询使用like关键字，可以使用通配符进行占位:

_ : 代表单个任意字符
% : 代表任意个数字符

任务要求

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

查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序排列)。
查询供应工程J1零件P1的供应商号码（按供应商号码升序排列）。
查询使用供应商S1所供应零件的工程号码（按工程号码升序排列）。
注意：数据大小写要保持一致。

use demo;

#代码开始
#1. 查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序排列)。
SELECT DISTINCT sno FROM spj WHERE jno='J1' ORDER BY sno;

#2. 查询供应工程J1零件P1的供应商号码（按供应商号码升序排列）。
SELECT DISTINCT sno FROM spj WHERE jno='J1' AND pno='P1' ORDER BY sno;

#3. 查询使用供应商S1所供应零件的工程号码（按工程号码升序排列）。
SELECT DISTINCT jno FROM spj WHERE sno='S1' ORDER BY jno ORDER BY ;


#代码结束

六、数据查询-连接查询

本关任务：

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

任务要求

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

查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列）。
查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。
查询使用上海产的零件的工程名称（按工程名称升序排列,去除重复数据）。
注意：数据大小写要保持一致。

use demo;

#代码开始

#1. 查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列）。
SELECT pname,qty FROM spj NATURAL JOIN p WHERE jno='J2' ORDER BY pname;


#2. 查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。
SELECT DISTINCT pno FROM p NATURAL JOIN spj NATURAL JOIN s  WHERE s.city='上海' ORDER BY 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

#代码结束

七、数据查询-子查询

任务要求

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

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

use demo;

#代码开始

#1. 查询供应工程J1零件为红色的供应商号码（按供应商号码升序排列）。
SELECT DISTINCT sno FROM spj NATURAL JOIN p WHERE jno='J1' AND color='红' ORDER BY sno;



#2. 查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序排列）。
select distinct jno from spj where jno not in(
select jno from spj natural join s natural join p where s.city='天津' and p.color='红'
) ORDER BY jno;



#代码结束

八、修改数据

任务要求

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

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

use demo;

#代码开始
#1. 把全部红色零件的颜色改成蓝色。
UPDATE p SET color='蓝' WHERE color='红';


#2. 由S5供给J4的零件P6改为由S3供应。
UPDATE spj SET sno='S3' WHERE sno='S5' AND pno='P6' AND jno='J4';


#3. 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
DELETE FROM s WHERE sno='S2';
DELETE FROM spj WHERE sno='S2';


#4. 请将(S2，J6，P4，200)插入供应情况关系表。
INSERT INTO spj(sno,jno,pno,qty) VALUES ('S2','J6','P4',200);


#代码结束

#评测使用，不要删除下面代码
select * from s;
select * from p;
select * from spj;

九、创建视图

任务要求

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

use demo;

#代码开始
#1. 请为“三建”工程项目建立一个供应情况的视图(v_spj)，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
CREATE VIEW v_spj
AS SELECT sno,pno,qty FROM spj NATURAL JOIN j WHERE jname='三建';
DESCRIBE v_spj;

#2. 查看视图(v_spj)信息。



#针对视图(v_spj)完成下列查询：
#(1) 查询“三建”工程项目使用的各种零件代码及其总数量(total)
#提示：利用聚合函数sum()和分组查询实现，总数量起别名total。
SELECT pno,sum(qty) AS total
FROM v_spj
GROUP BY pno;


#(2) 查询供应商S1的供应情况。

SELECT sno,pno,qty
FROM v_spj
WHERE sno='S1';

#代码结束

十、定义索引

任务要求

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

use demo;

#代码开始

# 1.在spj表定义索引IDX_SPJ，包括（sno,pno,jno）。
CREATE INDEX IDX_SPJ ON spj(sno,pno,jno);


# 2. 查看spj表索引定义。
SHOW INDEX FROM spj;

#代码结束