spj数据库
-- 1. 创建数据库

IF DB_ID('SPJDB') IS NULL

CREATE DATABASE SPJDB;

GO

USE SPJDB;

GO

-- 2. 删除旧表（避免重复执行报错）

IF OBJECT_ID('SPJ', 'U') IS NOT NULL DROP TABLE SPJ;

IF OBJECT_ID('S', 'U') IS NOT NULL DROP TABLE S;

IF OBJECT_ID('P', 'U') IS NOT NULL DROP TABLE P;

IF OBJECT_ID('J', 'U') IS NOT NULL DROP TABLE J;

GO

-- 3. 创建表

-- S 表：供应商

CREATE TABLE S (

SNO CHAR(2) PRIMARY KEY,

SNAME NVARCHAR(20),

STATUS INT,

CITY NVARCHAR(20)

);

-- P 表：零件

CREATE TABLE P (

PNO CHAR(2) PRIMARY KEY,

PNAME NVARCHAR(20),

COLOR NVARCHAR(10),

WEIGHT INT

);

-- J 表：工程项目

CREATE TABLE J (

JNO CHAR(2) PRIMARY KEY,

JNAME NVARCHAR(50),

CITY NVARCHAR(20)

);

-- SPJ 表：供应关系

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),

FOREIGN KEY (PNO) REFERENCES P(PNO),

FOREIGN KEY (JNO) REFERENCES J(JNO)

);

-- 4. 插入数据

-- S 表数据

INSERT INTO S VALUES ('S1','精益',20,N'天津');

INSERT INTO S VALUES ('S2','盛锡',10,N'北京');

INSERT INTO S VALUES ('S3','东方红',30,N'北京');

INSERT INTO S VALUES ('S4','丰泰盛',20,N'天津');

INSERT INTO S VALUES ('S5','为民',30,N'上海');

-- P 表数据

INSERT INTO P VALUES ('P1','螺母',N'红',12);

INSERT INTO P VALUES ('P2','螺栓',N'绿',17);

INSERT INTO P VALUES ('P3','螺丝刀',N'蓝',14);

INSERT INTO P VALUES ('P4','螺丝刀',N'红',14);

INSERT INTO P VALUES ('P5','凸轮',N'蓝',40);

INSERT INTO P VALUES ('P6','齿轮',N'红',30);

-- J 表数据

INSERT INTO J VALUES ('J1','三建',N'北京');

INSERT INTO J VALUES ('J2','一汽',N'长春');

INSERT INTO J VALUES ('J3','弹簧厂',N'天津');

INSERT INTO J VALUES ('J4','造船厂',N'天津');

INSERT INTO J VALUES ('J5','机车厂',N'唐山');

INSERT INTO J VALUES ('J6','无线电厂',N'常州');

INSERT INTO J VALUES ('J7','半导体厂',N'南京');

-- SPJ 表数据

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);

GO