sqlserver简单构建表试列

create database HR go use HR go –区域表结构 create table REGIONS( REGION_ID int not null primary key identity(1,1),–区域编号，主键，自增 REGION_NAME varchar(25) not null–区域名称 ) –国家表结构 create table COUNTRIES( COUNTRY_ID char(2) not null primary key,–国家编号，主键 COUNTRY_NAME varchar(40) not null,–国家名称 REGION_ID int not null references REGIONS(REGION_ID)–区域编号，外键 ) –位置表结构 create table LOCATIONS( LOCATION_ID int not null primary key identity(1000,100),–位置编号，主键，自增 STREET_ADDRESS varchar(40) not null,–位置名称 POSTAL_CODE varchar(12) not null,–邮编 CITY varchar(30) not null,–城市 STATE_PROVINCE varchar(20),–省或州 COUNTRY_ID char(2) not null references COUNTRIES(COUNTRY_ID)–国家编号，外键 ) –职位表的结构 create table JOBS( JOB_ID varchar(10) not null primary key,–职位编号，主键 JOB_TITLE nvarchar(20) not null,–职位名称 MIN_SALARY money not null check (len(MIN_SALARY)>1000),–职位最低薪资不少于1000元 MAX_SALARY money not null ,–职位最高薪资不少于最低薪资 ) alter table JOBS add constraint CK_JOBS_MAX_SALARY check (len(MAX_SALARY)>MIN_SALARY) –部门表的结构 create table DEPARTMENTS( DEPARTMENT_ID int not null primary key identity(10,10),–部门编号，主键，自增 DEPARTMENT_NAME nvarchar(30) not null,–部门名称 MANAGER_ID int references EMPLOYEES(EMPLOYEE_ID),–部门主管编号，外键 LOCATION_ID int not null references LOCATIONS(LOCATION_ID)–部门位置编号 ) –职员表的结构 create table EMPLOYEES( EMPLOYEE_ID int not null primary key identity(100,1),–职员编号，主键，自增 FIRST_NAME nvarchar(4) not null,–职员名称 LAST_NAME varchar(4) not null,–职员姓氏 EMAIT varchar(25) not null,–职员邮件 PHONE_NUMBER varchar(20) not null,–职员电话 HIRE_DATE datetime not null default getdate(),–职员入职时间 JOB_ID varchar(10) not null references JOBS(JOB_ID),–职员职位编号，外键 SALARY money check (len(SALARY)>0),–职员薪资，大于0 COMMISSION_PCT decimal,–职员佣金百分百 MANAGER_ID int references EMPLOYEES(EMPLOYEE_ID),–职员主管编号，外键 DEPARTMENT_ID int --职员部门编号，外键 ) alter table EMPLOYEES add constraint FK_EMPLOYEES_DEPARTMENTS_DEPARTMENT_ID foreign key(DEPARTMENT_ID) references DEPARTMENTS(DEPARTMENT_ID) –任职表的结构 create table JOB_HISTORY( EMPLOYEE_ID int not null,–职员编号，主键 START_DATE datetime not null unique ,–开始时间，主键 END_DATE datetime not null default getdate(),–结束时间 JOB_ID varchar(10) not null references JOBS(JOB_ID),–职员职位编号，外键 DEPARTMENT_ID int --职员部门编号，外键 ) alter table JOB_HISTORY add constraint PK_JOB_HISTORY_START_DATE primary key (START_DATE)

alter table JOB_HISTORY add constraint FK_JOB_HISTORY_DEPARTMENTS_DEPARTMENT_ID foreign key(DEPARTMENT_ID) references DEPARTMENTS(DEPARTMENT_ID)

–创建登陆账号 create login test with password=‘testuser’ –创建用户 use HR create user test_HR for login test –权限 use HR grant select to test_HR