SQL语句

来源：互联网发布：最大子数组动态规划编辑：程序博客网时间：2024/06/06 01:22

创建表

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列提供值
注意单引号的使用

0 0