SQL创表

--创建人事系统数据库
create database rsxt;
--创建部门表
create table dept
(
dept_id char(2),
dept_name char(20),
constraint pk_dept_dept_id primary key(dept_id)
)
--创建人员花名册
create table emp
(
emp_id char(5) check(emp_id like 'jg[0-9][0-9][0-9]'),
emp_name char(20) not null,
dept_id char(2),
age int,
tel char(20),
bdate char(10) check(bdate like 'yyyy/mm/dd')
bdate datetime default getdate,
constraint pk_emp_id primary key(emp_id),
constraint fk_dept_id foreign key (fk_dept)
)
--创建考勤表
create table work
(
emp_id char(5),
dept_id char(2),
sdate datetime check(bdate like 'yyyy/mm/dd hh:mi:ss'),
edate datetime check(bdate like 'yyyy/mm/dd hh:mi:ss '),
)

--创建工资表
create table salary
(
emp_id char(5),
dept_id char(2),
base_salary numeric(6,1), --基本工资
j_salary numeric(6,1), --奖金
k_salary int,
year_month char(6) check (year_month like '[0-9][0-9][0-9][0-9][0-9][0-9]'),
give char(1) check(give between 0 and 1),
)
--部门表初始化
insert into dept values('01','市场部');
insert into dept values('02','行政部');
insert into dept values('03','学术部');
insert into dept values('04','就业部');
--人员数据初始化
insert into emp(emp_id,emp_name,dept_id) values('jg001','胡','01');
insert into emp(emp_id,emp_name,dept_id) values('jg002','温','02');
insert into emp(emp_id,emp_name,dept_id) values('jg003','李','03');
insert into emp(emp_id,emp_name,dept_id) values('jg004','张','03');
insert into emp(emp_id,emp_name,dept_id) values('jg005','A','01');
insert into emp(emp_id,emp_name,dept_id) values('jg006','B','02');
insert into emp(emp_id,emp_name,dept_id) values('jg007','C','03');
insert into emp(emp_id,emp_name,dept_id) values('jg008','D','03');
--查询表
select * from emp;
select * from work;
select * from salary;
select * from dept;