MySql——sql语句

感觉好久没写忘了，翻出来之前做的数据库作业，就当复习了

#DDL创建各种东西
create table SPJ(
	SNO char(2) NOT NULL ,
    PNO char(2) NOT NULL,
    JNO char(2) NOT NULL,
    QTY INT NOT NULL,
    CONSTRAINT FK_SPJ_S FOREIGN key(SNO) REFERENCES S(SNO),
    CONSTRAINT FK_SPJ_P FOREIGN KEY(PNO) REFERENCES P(PNO) ,
    CONSTRAINT FK_SPJ_J FOREIGN KEY(JNO) REFERENCES J(JNO)
);

#视图的建立
CREATE VIEW MY_VIEW1 AS
    SELECT DISTINCT
        SNO
    FROM
        SPJ
            INNER JOIN
        S ON SPJ.SNO = S.SNO
            INNER JOIN
        P ON P.PNO = SPJ.PNO;
        
CREATE VIEW MY_VIEW2 AS SELECT SNO FROM SPJ WHERE QTY = 1000;        
#视图的查看
SELECT * FROM MY_VIEW1;

#增，删，改

INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S1', 'P1', 'J1', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S1', 'P1', 'J3', '100');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S1', 'P1', 'J4', '700');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S1', 'P2', 'J2', '100');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S2', 'P3', 'J1', '400');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S2', 'P3', 'J2', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S2', 'P3', 'J4', '500');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S2', 'P3', 'J5', '400');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S2', 'P5', 'J1', '400');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S2', 'P5', 'J2', '100');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S3', 'P1', 'J1', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S3', 'P3', 'J1', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S4', 'P5', 'J1', '100');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S4', 'P6', 'J3', '300');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S4', 'P6', 'J4', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S5', 'P2', 'J4', '100');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S5', 'P3', 'J1', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S5', 'P6', 'J2', '200');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S5', 'P6', 'J4', '500');
INSERT INTO `factory`.`spj` (`SNO`, `PNO`, `JNO`, `QTY`) VALUES ('S5', 'P6', 'J4', '500');

set SQL_SAFE_UPDATES = 0;
insert into spj values('S5','P2','J2',1000);
update SPJ SET SNO = 'S1' WHERE QTY = 1000;
SELECT * FROM MY_VIEW2;
DELETE FROM SPJ WHERE QTY = 1000;

#查询操作
SELECT distinct SNO from SPJ where JNO = 'J1';
SELECT SNO FROM SPJ WHERE JNO ='J1' AND PNO ='P1';
SELECT SNO FROM SPJ inner JOIN P ON SPJ.PNO = P.PNO WHERE COLOR ='红' AND JNO ='J1';
select  SPJ.jno,p.color,s.city FROM SPJ INNER JOIN S ON S.SNO = SPJ.SNO INNER JOIN P ON P.PNO = SPJ.PNO;
select  SPJ.JNO ,p.color,s.city FROM SPJ INNER JOIN S ON S.SNO = SPJ.SNO INNER JOIN P ON P.PNO = SPJ.PNO where color = '红' and city = '天津';
select DISTINCT jno from spj where jno not in(select jno from spj,p,s where s.city ='天津' and color ='红' and s.sno =spj.sno and p.pno =spj.pno);
SELECT distinct PNO FROM SPJ inner JOIN S ON SPJ.SNO = S.SNO WHERE CITY = '上海';
SELECT distinct jno FROM SPJ WHERE SNO = 's1';
SELECT distinct P.PNAME,sum(qty) FROM P JOIN SPJ ON P.PNO = SPJ. PNO WHERE JNO = 'J2' group by spj.PNO;
select distinct jno from spj where spj.jno not in (select distinct jno from s inner join spj on s. sno = spj.sno where city = '天津');
select SNAME,CITY FROM S ;
select PNAME,COLOR,WEIGHT FROM P;

#存储过程
delimiter $;
CREATE procedure getS(IN SN varchar(4))
BEGIN
	SELECT * FROM S WHERE SNO = SN;
END $;
#测试
CALL GETS('S1')$;
delimiter ;

#不太懂题目意思，这里在S表创建一个level列，在更新SPJ.QTY后，以新的值作为相应供应商的level等级
alter table s add column level int;

delimiter $;
create trigger Update_SPJ_QTY
AFTER UPDATE ON SPJ
for each row
begin
 UPDATE S SET `level`=(select  sum(qty) from SPJ where sno = (select SNO from SPJ where QTY = new.QTY))/10 WHERE S.sno = (select SNO from SPJ where QTY = new.QTY);
 END $;
 delimiter ;
 #测试
UPDATE SPJ SET QTY = 5000 WHERE QTY = 1000;
select * from S;

#第三章5.9
update spj set SNO = 'S3' WHERE SNO ='S5' AND JNO = 'J4' AND PNO='P6';