SQL练习----建表

最新推荐文章于 2023-06-10 17:15:54 发布

原创最新推荐文章于 2023-06-10 17:15:54 发布 · 689 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #SQL

本文展示了如何使用SQL语句创建并填充供应商(S)、零件(P)、工程项目(J)及供应情况(SPJ)四个表的具体实例。通过这些表的创建与数据插入操作，可以详细了解数据库的基本操作。

CREATE TABLE S  
        
      (SNO   CHAR(9) ,     
             
  SNAME CHAR(20) ,        
     
          STATUS    SMALLINT,
     
               CITY  CHAR(20)
); 
  
  
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,'天津');

CREATE TABLE P  
     
      (PNO   CHAR(9) ,     
             
  PNAME CHAR(20) ,        
               COLOR  CHAR(20),
     
               WEIGHT    SMALLINT
);

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

CREATE TABLE J  
        
      (JNO   CHAR(9) PRIMARY KEY,     
             
  JNAME CHAR(20) UNIQUE,        
     
          CITY  CHAR(20));

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


CREATE TABLE SPJ  
        
      ( SNO CHAR(20),
                PNO CHAR(20),
                JNO CHAR(20),
                QTY SMALLINT,
                );
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('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);

J表

P表

S表

SPJ表