SQL-spj库创建脚本

爱被打了一巴掌 2023-07-18 06:53 6阅读 0赞

# spj库创建脚本 #

CREATE TABLE j (
      jno char(3) NOT NULL,  -- 工程项目号
      jname varchar(10) DEFAULT NULL,
      city varchar(10) DEFAULT NULL,
      PRIMARY KEY (jno)
    );
    CREATE TABLE p (
      pno char(3) NOT NULL,  -- 零件号
      pname varchar(10) DEFAULT NULL,
      color char(2) DEFAULT NULL,
      weight int DEFAULT NULL,
      PRIMARY KEY (pno)
    );
    CREATE TABLE s (
      sno char(3) NOT NULL,  -- 供应商号
      sname varchar(10) DEFAULT NULL,
      status char(2) DEFAULT NULL,
      city varchar(10) DEFAULT NULL,
      PRIMARY KEY (sno)
    );
    CREATE TABLE spj (
      sno char(3) NOT NULL,
      pno char(3) NOT NULL,
      jno char(3) NOT NULL,
      qty int DEFAULT NULL,
      PRIMARY KEY (sno,pno,jno),
      CONSTRAINT fk_jno FOREIGN KEY (jno) REFERENCES j (jno),
      CONSTRAINT fk_pno FOREIGN KEY (pno) REFERENCES p (pno),
      CONSTRAINT fk_sno FOREIGN KEY (sno) REFERENCES s (sno)
    );
    
    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 j values('j8','电子元件厂','南京');
    
    insert into spj values('s1','p1','j1',200);
    insert into spj values('s1','p1','j2',700);
    insert into spj values('s1','p1','j4',700);
    insert into spj values('s1','p2','j4',100);
    insert into spj values('s1','p2','j5',400);
    insert into spj values('s1','p3','j2',200);
    insert into spj values('s1','p3','j4',100);
    insert into spj values('s2','p3','j5',50);
    insert into spj values('s2','p3','j6',400);
    insert into spj values('s3','p1','j1',100);
    insert into spj values('s3','p3','j1',200);
    insert into spj values('s4','p1','j1',200);
    insert into spj values('s4','p2','j1',100);
    insert into spj values('s4','p3','j1',300);
    insert into spj values('s4','p5','j4',200);
    insert into spj values('s4','p6','j2',100);
    insert into spj values('s5','p2','j2',154);
    insert into spj values('s5','p3','j1',20);
    insert into spj values('s5','p4','j2',50);
    insert into spj values('s5','p6','j3',125);
    insert into spj values('s5','p6','j4',52);

> **原创不易，请勿转载**（本不富裕的访问量雪上加霜 ）  
> 博主首页：[https://blog.csdn.net/qq\_45034708][https_blog.csdn.net_qq_45034708]  
> *如果文章对你有帮助，记得关注点赞收藏❤*

[https_blog.csdn.net_qq_45034708]: https://blog.csdn.net/qq_45034708