实验2 MySQL软件操作及建库建表建数据

第1关 创建数据库

CREATE DATABASE mydata;

show databases;

第2关 创建供应商表S，并插入数据

USE mydata;

CREATE TABLE S(
   SNO CHAR(2),
   SNAME VARCHAR(5),
   STATUS INT,
   CITY CHAR(2)
);
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,'上海');

SELECT * FROM S;

第3关 创建零件表P，并插入数据

USE mydata;

CREATE TABLE P(
    PNO CHAR(2),
    PNAME VARCHAR(5),
    COLOR CHAR(1),
    WEIGHT INT

);

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

SELECT * FROM P;

第4关 创建工程项目表J，并插入数据

USE mydata;

CREATE TABLE J(
    JNO CHAR(2),
    JNAME VARCHAR(5),
    CITY CHAR(2)
);
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','半导体厂','南京');

SELECT * FROM J;

第5关 创建供应情况表SPJ，并插入数据

USE mydata;

CREATE TABLE SPJ(
    SNO CHAR(2),
    PNO CHAR(2),
    JNO CHAR(2),
    QTY INT
    );

INSERT INTO SPJ(SNO,PNO,JNO,QTY)
VALUES('S1', 'P1', 'J1', 200),
    ('S1', 'P1', 'J3', 100),
    ('S1', 'P1', 'J4', 700),
    ('S1', 'P2', 'J2', 100),
    ('S2', 'P3', 'J1', 400),
    ('S2', 'P3', 'J2', 200),
    ('S2', 'P3', 'J4', 500),
    ('S2', 'P3', 'J5', 400),
    ('S2', 'P5', 'J1', 400),
    ('S2', 'P5', 'J2', 100),
    ('S3', 'P1', 'J1', 200),
    ('S3', 'P3', 'J1', 200),
    ('S4', 'P5', 'J1', 100),
    ('S4', 'P6', 'J3', 300),  
    ('S4', 'P6', 'J4', 200),
    ('S5', 'P2', 'J4', 100),
    ('S5', 'P3', 'J1', 200),
    ('S5', 'P6', 'J2', 200),
    ('S5', 'P6', 'J4', 500);

SELECT * FROM SPJ;