SQL数据库基础练习分享
use master
if exists(select *from sysdatabases where name='Company1')--判断数据库是否存在
   drop database Company1
go
create database Company1
/*on primary
(
name=Company1_data,--主文件逻辑名称
filename='E:\sqlnsa\SQLmy\Company1.mdf',--物理文件名
size=10mb,
maxsize=unlimited,
filegrowth=15%--15%的速度增长
)



log on
(
name=Company1_log,--日志文件
filename='E:\sqlnsa\SQLmy\Company1.ldf',--逻辑文件名
size=1mb,
maxsize=10,
filegrowth=2%
)*/
/********************创建部门数据表******************/
go
use Company1
go
create table 部门
(
部门编号 int  primary key,/*主键*/
部门名称 char(10),
)
go



/**********************创建职工数据表***********************/
create table 职工
(
职工编号 int primary key,/*主键约束*/
/*phonenum 职工编号,
constratint chk_职工编号 check
(phonenum line'[1-9]')*/
姓名 char(10),
性别 char (2)
constraint chk_性别 check(性别 in ('男','女')),
出生日期 smalldatetime
constraint chk_出生日期 check(出生日期<getdate()),
党员 char(2)
constraint chk_党员 check(党员 in('是','否')),


入职时间 smalldatetime
constraint chk_入职时间 check(入职时间<getdate()),
籍贯 char(10),


所在部门 int
constraint con_no
foreign key(所在部门)
references 部门(部门编号),



)
go
/******************工资表*******************/
create table 工资
(
职工编号 int ,
constraint con_gz/*工资*/
foreign key(职工编号)
references 职工(职工编号),


月份 int  check(月份>=1 and 月份<=12),
基本工资 money check(基本工资>0),
津贴 money check(津贴>=0),
补助 money check(补助>=0),
奖金 money check(奖金>=0),
扣除 money check(扣除>=0),
税收 money check(税收>=0),



)
go
/*插入记录，针对部门表*/
insert into 部门
(部门编号,部门名称)
values('1','财务处')
insert into 部门
values ('2','人事处')
insert into 部门
values('3','市场部')


/*插入记录，针对职工表*/
insert into 职工
(职工编号,姓名,性别,出生日期,党员,入职时间,籍贯,所在部门)
values('1','孙华','男','1952-1-3','是','1970-10-10','天津市','1')
insert into 职工
values('2','陈明','男','1945-5-8','否','1965-01-01','北京市','2')
insert into 职工
values('3','程西','女','1980-6-10','否','2002-07-10','北京市','1')
insert into 职工
values('4','苏天气','女','1965-3-10','是','1987-07-10','天津市','2')
insert into 职工
values('5','刘少文','男','1942-1-11','否','1960-08-10','','1')
insert into 职工
values('6','刘新','男','1952-10-08','否','1970-7-01','上海市','1')
insert into 职工
values('7','余辉','女','1980-12-4','否','2001-07-10','武汉市','3')
insert into 职工
values('8','王晓艳','女','1980-12-11','是','2001-07-10','河北省','2')
insert into 职工
values('9','李玉','女','1964-2-12','是','1989-07-15','天津市','1')
insert into 职工
values('10','周涛','男','1963-2-10','否','1983-07-20','河北省','3')
insert into 职工
values('11','欧阳少兵','男','1965-4-19','否','1984-07-12','北京市','2')
insert into 职工
values('12','张旗','男','1980-11-10','否','1999-09-18','天津市','3')
insert into 职工
values('13','李涵','女','1980-5-10','否','2002-02-14','天津市','3')
insert into 职工
values('14','魏君','女','1977-3-23','是','1993-07-10','','3')
insert into 职工
values('15','李艺','男','1979-9-18','否','2001-07-20','上海市','3')


/*插入记录，针对工资*/
insert into 工资
(职工编号,月份,基本工资,津贴,补助,奖金,扣除,税收)
values('1','8','2800','1200','180','200','0','220')
insert into 工资
values('2','8','2600','1000','200','300','0','180')
insert into 工资
values('3','8','1880','880','200','260','50','120')
insert into 工资
values('4','8','2200','980','180','230','80','110')
insert into 工资
values('5','8','2300','880','210','0','0','125')
insert into 工资
values('6','8','2100','600','220','180','50','110')
insert into 工资
values('7','8','2200','800','180','200','120','120')