灵魂居住的地方
幼苗
共回答了12个问题采纳率:91.7% 举报
我记得我以前做过这个题目,我把创建表的脚本给你吧,其它的你自己想着做吧,
create database SPJ;
use SPJ;
create table S(SNO varchar(10) primary key,
x05x05x05x05SNAME varchar(200) NOT NULL,
x05x05x05x05STATUS int NOT NULL,
x05x05x05x05CITY varchar(20) NOT NULL);
create table P(PNO varchar(10) primary key,
x05x05x05x05PNAME varchar(200) NOT NULL,
x05x05x05x05COLOR varchar(20) NOT NULL,
x05x05x05x05WEIGHT int check (WEIGHT > 0) NOT NULL);
create table J(JNO varchar(10) primary key,
x05x05x05x05JNAME varchar(200) NOT NULL,
x05x05x05x05CITY varchar(20) NOT NULL);
create table SPJ(SNO varchar(10) NOT NULL,
x05x05x05x05PNO varchar(10) NOT NULL,
x05x05x05x05JNO varchar(10) NOT NULL,
x05x05x05x05QTY int NOT NULL check(QTY >= 0),
x05x05x05x05FOREIGN KEY (SNO) REFERENCES S(SNO),
x05x05x05x05FOREIGN KEY (PNO) REFERENCES P(PNO),
x05x05x05x05FOREIGN KEY (JNO) REFERENCES J(JNO)
x05x05x05x05);
INSERT INTO S(SNO,SNAME,STATUS,CITY)
x05x05x05SELECT 'S1','精益',20,'天津'
x05x05x05UNION ALL
x05x05x05SELECT 'S2','盛锡',10,'北京'
x05x05x05UNION ALL
x05x05x05SELECT 'S3','东方红',30,'北京'
x05x05x05UNION ALL
x05x05x05SELECT 'S4','丰泰盛',20,'天津'
x05x05x05UNION ALL
x05x05x05SELECT 'S5','为民',30,'上海';
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)
x05x05x05SELECT 'P1','螺母','红',12
x05x05x05UNION ALL
x05x05x05SELECT 'P2','螺栓','绿',17
x05x05x05UNION ALL
x05x05x05SELECT 'P3','螺丝刀','蓝',14
x05x05x05UNION ALL
x05x05x05SELECT 'P4','螺丝刀','红',14
x05x05x05UNION ALL
x05x05x05SELECT 'P5','凸轮','蓝',40
x05x05x05UNION ALL
x05x05x05SELECT 'P6','齿轮','红',30;
INSERT INTO J(JNO,JNAME,CITY)
x05x05x05SELECT 'J1','三建','北京'
x05x05x05UNION ALL
x05x05x05SELECT 'J2','一汽','长春'
x05x05x05UNION ALL
x05x05x05SELECT 'J3','弹簧厂','天津'
x05x05x05UNION ALL
x05x05x05SELECT 'J4','造船厂','天津'
x05x05x05UNION ALL
x05x05x05SELECT 'J5','机车厂','唐山'
x05x05x05UNION ALL
x05x05x05SELECT 'J6','无线电厂','常州'
x05x05x05UNION ALL
x05x05x05SELECT 'J7','半导体厂','南京';
INSERT INTO SPJ(SNO,PNO,JNO,QTY)
x05x05x05SELECT 'S1','P1','J1',200
x05x05x05UNION ALL
x05x05x05SELECT 'S1','P1','J3',100
x05x05x05UNION ALL
x05x05x05SELECT 'S1','P1','J4',700
x05x05x05UNION ALL
x05x05x05SELECT 'S1','P2','J2',100
x05x05x05UNION ALL
x05x05x05SELECT 'S2','P3','J1',400
x05x05x05UNION ALL
x05x05x05SELECT 'S2','P3','J2',200
x05x05x05UNION ALL
x05x05x05SELECT 'S2','P3','J4',500
x05x05x05UNION ALL
x05x05x05SELECT 'S2','P3','J5',400
x05x05x05UNION ALL
x05x05x05SELECT 'S2','P5','J1',400
x05x05x05UNION ALL
x05x05x05SELECT 'S2','P5','J2',100
x05x05x05UNION ALL
x05x05x05SELECT 'S3','P1','J1',200
x05x05x05UNION ALL
x05x05x05SELECT 'S3','P3','J1',200
x05x05x05UNION ALL
x05x05x05SELECT 'S4','P5','J1',100
x05x05x05UNION ALL
x05x05x05SELECT 'S4','P6','J3',300
x05x05x05UNION ALL
x05x05x05SELECT 'S4','P6','J4',200
x05x05x05UNION ALL
x05x05x05SELECT 'S5','P2','J4',100
x05x05x05UNION ALL
x05x05x05SELECT 'S5','P3','J1',200
x05x05x05UNION ALL
x05x05x05SELECT 'S5','P6','J2',200
x05x05x05UNION ALL
x05x05x05SELECT 'S5','P6','J4',500;
1年前
8