数据库sql中S P J SPJ 表的创建

创建s，p，j，spj表

CREATE TABLE S(SNO VARCHAR2(10),SNAME VARCHAR2(10) NOT NULL,STATUS VARCHAR2(3),CITY VARCHAR2(20),CONSTRAINT PK_S PRIMARY KEY(SNO));CREATE TABLE P(PNO VARCHAR2(10),PNAME VARCHAR2(20) NOT NULL,COLOR VARCHAR(10),WEIGHT VARCHAR(2),CONSTRAINT PK_P PRIMARY KEY(PNO));CREATE TABLE J(JNO VARCHAR2(10),JNAME VARCHAR2(20) NOT NULL,CITY VARCHAR(20),CONSTRAINT PK_J PRIMARY KEY(JNO));CREATE TABLE SPJ(SNO VARCHAR2(10),PNO VARCHAR2(10),JNO VARCHAR2(10),QTY NUMBER(3),CONSTRAINT PK_SPJ PRIMARY KEY(SNO,PNO,JNO)，CONSTRAINT FK_SPJ_To_S FOREIGN KEY (SNO) REFERENCES S(SNO),CONSTRAINT FK_SPJ_To_P FOREIGN KEY (PNO) REFERENCES P(PNO),CONSTRAINT FK_SPJ_To_J FOREIGN KEY (JNO) REFERENCES J(JNO));

插入相关元素

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,'上海');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);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','半导体厂','南京');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);

方便接下来实验直接配置当前的表进行查询！

alter table p rename column sname to pname;select sname,city from s;select pname,color,weight from p;select PNAME,QTYfrom p,spjwhere spj.jno='J2'and p.pno=spj.pno;select pnofrom s,spjwhere city='上海' and s.sno=spj.sno;select jnamefrom s,spj,jwhere s.city='上海' and s.sno=spj.sno and j.jno=spj.jno;select jno from s,spjwhere city!='天津' and s.sno=spj.sno;-------------------------------------------------select snofrom spjwhere jno='J1';select snofrom spjwhere jno='J1' and pno='P1';select snofrom p,spjwhere color='红'and p.pno=spj.pno;///1select spj.sno snofrom s,p,spjwhere not(s.city='天津'and p.color='红')and s.sno=spj.sno and p.pno=spj.pno;select sno from spjminusselect spj.sno snofrom s,p,spjwhere s.city='天津'and p.color='红'and s.sno=spj.sno and p.pno=spj.pno;///2select snofoom spjwhere sno in(select snofrom swhere city