【数据库CS751】数据库的建表与插入

这一篇内容主要是说一说建表语句和插入语句，很简单，目的是建立一个用例表来进行后面其他内容的测试。

目录

一、关系图：

 二、结构图：

三、建表

1.创建 EMPLOYEE 表：

2.创建DEPARTMENT表：

3.创建DEPT_LOACATION表：

4.创建PROJECT表：

5.创建WORKS_ON表：

6.创建DEPENDENT表：

四、目标表

五、插入值

 1.EMPLOYEE表插值

 2.DEPARTMENT表插值

3.DEPT_LOCATION表插值

4.PROJECT表插值 

 5.WORKS_ON表插值

 6.DEPENDENT表插值

---

一、关系图：

 二、结构图：

三、建表

1.创建 EMPLOYEE 表：

CREATE TABLE EMPLOYEE		
( 
    Fname	VARCHAR(15)	    NOT NULL,
    Minit	CHAR,	
    Lname	VARCHAR(15)	    NOT NULL,
    Ssn	    CHAR(9)	        NOT NULL,
    Bdate	DATE,	
    Address	VARCHAR(30),	
    Sex	    CHAR,	
    Salary	DECIMAL(10,2),	
    Super_ssn	CHAR(9),	
    Dno	        INT	        NOT NULL,

    PRIMARY KEY (Ssn)
)

2.创建DEPARTMENT表：

CREATE TABLE DEPARTMENT
( 
    Dname     VARCHAR(15)  NOT NULL,
    Dnumber   INT          NOT NULL,
    Mgr_ssn   CHAR(9)      NOT NULL,
    Mgr_start_date  DATE ,

    PRIMARY KEY (Dnumber),
    UNIQUE (Dname),
    FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) 
);

3.创建DEPT_LOACATION表：

CREATE TABLE DEPT_LOCATIONS

( 
    Dnumber        INT            NOT NULL,
    Dlocation      VARCHAR(15)    NOT NULL,
    PRIMARY KEY (Dnumber, Dlocation),
    FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) 
);

4.创建PROJECT表：

CREATE TABLE PROJECT

( 
    Pname         VARCHAR(15)    NOT NULL,
    Pnumber       INT            NOT NULL,
    Plocation     VARCHAR(15),
    Dnum          INT            NOT NULL,
    PRIMARY KEY (Pnumber),
    UNIQUE (Pname),
    FOREIGN KEY (Dnum) REFERENCES DEPARTMENT(Dnumber) 
);

5.创建WORKS_ON表：

CREATE TABLE WORKS_ON

( 
    Essn            CHAR(9)        NOT NULL,
    Pno             INT            NOT NULL,
    Hours           DECIMAL(3,1)   ,
    PRIMARY KEY (Essn, Pno),
    FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn),
    FOREIGN KEY (Pno) REFERENCES PROJECT(Pnumber) 
);

6.创建DEPENDENT表：

CREATE TABLE DEPENDENT

( 
    Essn                 CHAR(9)         NOT NULL,
    Dependent_name       VARCHAR(15)     NOT NULL,
    Sex                  CHAR,
    Bdate                DATE,
    Relationship         VARCHAR(8),
    PRIMARY KEY (Essn, Dependent_name),
    FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) 
);

四、目标表

五、插入值

 1.EMPLOYEE表插值

insert into employee values('John','B','Smith','123456789','1965-01-09','731 Fondren, Houston, TX','M','30000','333445555',5);
insert into employee values('Franklin','T','Wong','333445555','1955-12-08','638 Voss, Houston, TX','M','40000','888665555',5);
insert into employee values('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring, TX','F','25000','987654321',4);
insert into employee values('Jennifer','S','Wallace','987654321','1941-06-20','291 Berry, Bellaire, TX','F','43000','888665555',4);
insert into employee values('Ramesh','K','Narayan','666884444','1962-09-15','975 Fire Oak, Humble, TX','M','38000','333445555',5);
insert into employee values('Joyce','A','English','453453453','1972-07-31','5631 Rice, Houston, TX','F','25000','333445555',5);
insert into employee values('Ahmad','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M','25000','987654321',4);
insert into employee values('James','E','Borg','888665555','1937-11-10','450 Stone, Houston, TX','M','55000',NULL,1)

 2.DEPARTMENT表插值

insert into department values('Research',5,'333445555','1988-05-22');
insert into department values('Administration',4,'987654321','1995-01-01');
insert into department values('Headquarters',1,'888665555','1981-06-19');

3.DEPT_LOCATION表插值

insert into dept_locations values(1,'Houston');
insert into dept_locations values(4,'Stafford');
insert into dept_locations values(5,'Bellaire');
insert into dept_locations values(5,'Sugarland');
insert into dept_locations values(5,'Houston');

4.PROJECT表插值 

insert into project values('ProductX',1,'Bellaire',5);
insert into project values('ProductY',2,'Sugarland',5);
insert into project values('ProductZ','3','Houston','5');
insert into project values('Reorganization',20,'Houston',1);
insert into project values('Newbenefits',30,'Stafford',4);
insert into project values('Computerization',10,'Stafford',4);

 5.WORKS_ON表插值

insert into works_on values('123456789',1,32.5);
insert into works_on values('123456789',2,7.5);
insert into works_on values('666884444',3,40.0);
insert into works_on values('453453453',1,20.0);
insert into works_on values('453453453',2,20.0);
insert into works_on values('333445555',2,10.0);
insert into works_on values('333445555',3,10.0);
insert into works_on values('333445555',10,10.0);
insert into works_on values('333445555',20,10.0);
insert into works_on values('999887777',30,30.0);
insert into works_on values('999887777',10,10.0);
insert into works_on values('987987987',10,35.0);
insert into works_on values('987987987',30,5.0);
insert into works_on values('987654321',30,20.0);
insert into works_on values('987654321',20,15.0);
insert into works_on values('888665555',20,null);

 6.DEPENDENT表插值

insert into dependent values('333445555','Alice','F','1986-04-05','Daughter');
insert into dependent values('333445555','Theodore','M','1983-10-25','Son');
insert into dependent values('333445555','Joy','F','1958-05-03','Spouse');
insert into dependent values('987654321','Abner','M','1942-02-28','Spouse');
insert into dependent values('123456789','Michael','M','1988-01-04','Son');
insert into dependent values('123456789','Alice','F','1988-12-30','Daughter');
insert into dependent values('123456789','Elizabeth','F','1967-05-05','Spouse');

那么，我们就完成了 所有目标表内容的插入。