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