SQL语句

创建表

CREATE TABLE DEPARTMENT(
    DepartmentName Char(35) Not Null,
    BudgeCode Char(30) Not Null,
    OfficeNumber Char(15) Not NUll,
    Phone Char(12) Not Null,
    CONSTRAINT DEPARTMENT_PK PRIMARY KEY(DepartmentName)    //设置主键
);

CREATE TABLE EMPLOYEE(
    EmployeeNumber Int Not Null Identity(1,1),
    FirstName Char(25) Not Null,
    LastName Char(25) Not Null ,
    Department Char(35) Not Null Default 'Human Resources',
    Phone Char(12) Null,
    Email VarChar(100) Not Null Unique,
    CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EmployeeNumber),
    CONSTRAINT EMP_DEPART_FK FOREIGN KEY(Department) REFERENCES DEPARTMENT(DepartmentName) ON UPDATE CASCADE//设置外键，并设置参照完整性约束，级联更新
);

CREATE TABLE PROJECT(
    ProjectID Int Not Null Identity(1000,100),
    ProjectName Char(50) Not Null,
    Department Char(35) Not Null,
    MaxHours Numeric(8,2) Not Null Default 100,
    StartDate DateTime Null,
    EndDate DateTime Null,
    CONSTRAINT PROJECT_PK PRIMARY KEY (ProjectID),
    CONSTRAINT PROJ_DEPART_FK FOREIGN KEY(Department) REFERENCES DEPARTMENT(DepartmentName) ON UPDATE CASCADE
);

CREATE TABLE ASSIGNMENT(
    ProjectID Int Not Null,
    EmployeeNumber Int Not Null,
    HoursWorked Numeric(6,2) Null,
    CONSTRAINT ASSIGNMENT_PK PRIMARY KEY (ProjectID,EmployeeNumber),
    CONSTRAINT ASSIGN_PROJ_FK FOREIGN KEY (ProjectID) REFERENCES PROJECT(ProjectID) ON UPDATE NO ACTION ON DELETE CASCADE,
    CONSTRAINT ASSIGN_EMP_FK FOREIGN KEY(EmployeeNumber) REFERENCES EMPLOYEE(EmployeeNumber) ON UPDATE NO ACTION ON DELETE NO ACTION
);

插入数据

插入所有列的值，

INSERT INTO DEPARTMENT VALUES('Administration','BC-100-10','BLDG01-300','3602858100');

插入指定列值

INSERT INTO PROJECT
    (ProjectName,Department,MaxHours,StartDate,EndDate)
    Values('2010 Q4 Protfolio Analysis','Finance',140.00,'05-OCT-10');

注意

• 注意如果有代理键，不需要指定值。
• 为所有Not Null列提供值
• 注意单引号的使用