关系数据库标准语言SQL

简介：这是关系数据库标准语言SQL实验的练习，写的不好，供大家参考学习交流，谢谢大家。

-- 创建数据库

CREATE DATABASE `数据库名称` CHARACTER SET utf8 COLLATE utf8_general_ci;

/* 设置编码格式为utf8mb4*/

-- 创建S表
CREATE TABLE S(
SNO CHAR(10) ,
SNAME CHAR(50) UNIQUE,
STATUS INT,
CITY CHAR(20));

-- 添加数据到S表
INSERT INTO S(SNO,SNAME,STATUS,CITY)
VALUES('S1','精益',20,'天津'),
('S2','盛锡',10,'北京'),
('S3','东方红',30,'北京'),
('S4','丰泰盛',20,'天津'),
('S5','为民',30,'上海')
-- 查询S表
SELECT * FROM S
-- 创建P表
CREATE TABLE P(
PNO CHAR(10) PRIMARY KEY,
PNAME CHAR(50) ,
COLOR CHAR(20),
WEIGHT INT)
-- 添加数据到P表
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)
VALUES('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30)
-- 查询P表
SELECT * FROM P
-- 创建J表
CREATE TABLE J(
JNO CHAR(10) PRIMARY KEY,
JNAME CHAR(50) UNIQUE ,
CITY CHAR(20))
-- 添加数据到J表
INSERT INTO J(JNO,JNAME,CITY)
VALUES('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京')
-- 查询J表
SELECT * FROM J
-- 创建SPJ表
CREATE TABLE SPJ(
SNO CHAR(10),
PNO CHAR(10),
JNO CHAR(10),
QTY INT)

-- 添加数据到SPJ表
INSERT INTO SPJ(SNO,PNO,JNO,QTY)
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)
-- 查询 spj
SELECT * FROM SPJ

-- (1)找出所有供应商的姓名和所在城市。
SELECT SNAME,CITY FROM S;

-- (2)求供应工程J1零件P1的供应商号码SNO。
SELECT *FROM SPJ WHERE JNO='J1' AND PNO='P1';

-- (3)求供应工程J1零件为红色的供应商号码SNO。

SELECT DISTINCT SNO FROM SPJ INNER JOIN P ON SPJ.PNO=P.PNO WHERE JNO='J1' AND COLOR='红'

-- (4)找出工程项目J2使用的各种零件的名称及其数量。

SELECT PNAME,QTY FROM spj INNER JOIN p ON spj.PNO=p.PNO WHERE JNO='j2';

-- (5)找出使用上海产的零件的工程名称。
SELECT JNAME FROM S INNER JOIN SPJ ON S.SNO=SPJ.SNO INNER JOIN J ON SPJ.JNO=J.JNO WHERE S.CITY='上海';

-- (6)求没有使用天津供应商生产的红色零件的工程号JNO。

SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN(
SELECT JNO FROM SPJ INNER JOIN S ON S.SNO=SPJ.SNO  INNER JOIN P ON P.PNO=SPJ.PNO WHERE CITY='天津' AND COLOR='红');  

-- (7)把全部红色零件的颜色改成蓝色。
UPDATE P SET COLOR='蓝' WHERE COLOR='红';

-- (8)由S5供给J4的零件P6改为由S3供应。
UPDATE SPJ SET SNO='S3' WHERE PNO='P6' AND JNO='J4' AND SNO='S5';

-- (9)从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。
DELETE FROM S WHERE SNO='S2';
DELETE FROM SPJ WHERE SNO='S2';

--     (10)请将(S2，J6，P4，200)插入供应情况关系。
INSERT INTO SPJ(SNO,JNO,PNO,QTY)
VALUES('S2','J6','P4',200)

-- 请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)

CREATE VIEW  valentines  AS SELECT SNO,PNO,QTY FROM SPJ INNER JOIN J ON SPJ.JNO=J.JNO AND J.JNAME='三建';

-- 找出三建工程项目使用的各种零件代码及其数量。
SELECT PNO,SUM(QTY)AS QTY FROM valentines GROUP BY PNO;

-- 找出供应商S1的供应情况。
SELECT * FROM   valentines   WHERE SNO='S1';

-- 在表SPJ定义索引IDX_SPJ，包括（SNO,PNO,JNO）。比较定义索引前后求供应工程J1零件P1的供应商号码SNO的区别
CREATE INDEX   IDX_SPJ ON SPJ(SNO,PNO,JNO);

SELECT *FROM SPJ WHERE JNO='J1' AND PNO='P1';

创建索引前后区别：创建索引后查询的效率提高了，时间也减少了，我们在查询大量数据的时候，使用索引的话会很大程度加快编程的进程。