课后习题 SPJ

SQL数据库表结构与数据示例

最新推荐文章于 2023-09-10 23:32:28 发布

weixin_34235457

最新推荐文章于 2023-09-10 23:32:28 发布

阅读量360

点赞数 1

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/wc1903036673/p/3401648.html

CREATE TABLE S
 ( 

SNO char(2) primary key,                  

SNAME varchar(20),                  

STATUS char(4),                  

CITY char(10)

); 

create table P ( 

PNO char(2) primary key,                  

PNAME varchar(20),                  

COLOR char(4),                  

WEIGHT int

); 

create table J (

JNO char(2) primary key,                  

JNAME varchar(20),                  

CITY char(10)

);

create table SPJ ( 

SNO char(2),                    

PNO char(2),                    

JNO char(2),                    

QTY int,                     

primary key(SNO,PNO,JNO),                     

foreign key(SNO) references S(SNO) on delete cascade,                    

foreign key(PNO) references P(PNO) on delete cascade,                    

foreign key(JNO) references J(JNO) on delete cascade                    

); 



 

顺便连数据也一起给你好了

 

insert into S values('S1','供应商一','良好','郑州'); 

insert into S values('S2','供应商二','良好','天津'); 

insert into S values('S3','供应商三','良好','上海'); 

insert into S values('S4','供应商四','优秀','郑州'); 

  

insert into P values('P1','管材','红色',20); 

insert into P values('P2','管线','绿色',30); 

insert into P values('P3','螺帽','红色',10); 

insert into P values('P4','螺帽','黄色',10);  

  

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 SPJ values('S1','P1','J1',100); 

insert into SPJ values('S1','P2','J1',100); 

insert into SPJ values('S2','P1','J1',100); 

insert into SPJ values('S2','P2','J1',100); 

insert into SPJ values('S1','P1','J3',100); 

insert into SPJ values('S1','P2','J3',100); 

insert into SPJ values('S2','P1','J3',100); 

insert into SPJ values('S2','P2','J3',100); 

insert into SPJ values('S3','P1','J2',100); 

insert into SPJ values('S3','P2','J2',100); 

insert into SPJ values('S4','P3','J2',100);

转载于:https://www.cnblogs.com/wc1903036673/p/3401648.html