create database company1--创建数据库

 on primary
(
 --主数据文件参数设置
 name='company1',
 filename='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\company1.mdf',
 size=5MB,
 filegrowth=1MB,
 maxsize=unlimited
),

 (
 --次数据文件参数设置
 name='company1',
 filename='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\company2.ndf',
 size=5MB,
 filegrowth=1MB,
 maxsize=unlimited
 ),
  filegroup group1
  (
 --日志文件参数设置
  name='company1_log',
 filename='C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\company2.ldf',
 size=2MB,
 filegrowth=10%,
 maxsize=unlimited
  )
  ----------------------
  --创建数据库表
  create table sectioninfo--部门信息表
  (
  sectionid int identity(1,1),
  sectionname varchar(5)
  )

  create table userinfo
  (
  userno int identity(1,1),
  username varchar(5),
  usersex varchar(1),
  userage int ,
  useraddress varchar(20),
  usersection int,
  )

  create table workinfo 
  (
  workid int identity(1,1),
  userid int ,
  worktime datetime ,
  workdescription varchar(20)
  )

  ------------------
  --修改部门信息表设置
  alter table sectioninfo
  alter column sectionname varchar(10)

  --修改员工信息表设置
  alter table userinfo
  alter column username varchar(10)

  alter table userinfo
  alter column usersex varchar(2)

  alter table userinfo
  alter column useraddress varchar(50)

  --修改员工考勤表设置
  alter table workinfo 
  alter column workdescription varchar(40)

  ----------------
  --约束部门信息表进行设置
  alter table sectioninfo
  add constraint PK_sectioninfo_sectionid primary key(sectionid)

  alter table sectioninfo
  add constraint sectionname check(sectionname is not null)

  --约束员工信息表进行设置
  alter table userinfo
  add constraint PK_userinfo_userno primary key(userno)

  alter table userinfo
  add constraint userno check(userno is not null)

  alter table userinfo 
  add constraint username check(username is not null)

  alter table userinfo
  add constraint username unique(username)

  alter table userinfo
  add constraint usersex check(usersex is not null)

  alter table userinfo
  add constraint usersex check(usersex='男' or usersex='女')

  alter table userinfo
  add constraint userage check(userage is not null)

  alter table userinfo
  add constraint userage check(userage>=0 and userage<=100)

  alter table userinfo
  add  constraint useraddress default(湖北)

  alter table userinfo
  add constraint FK_userinfo_usersection foreign key(usersection) references userinfo(usersection)
  
  --约束员工考勤表进行设置
  alter table workinfo
  add constraint PK_workinfo_workid primary key(workid)
  
  alter table workinfo
  add constraint workid check(workid is not null)
  
  alter table workinfo 
  add constraint FK_workinfo_userid  check(userid is not null)
  
  alter table workinfo
  add constraint worktime check(worktime is not null)

  alter table workinfo
  add constraint workdescription check(workdescription is not null)
  
  alter table workinfo 
  add constraint workdescription check(workdescription='迟到' or workdescription='早退' or workdescription='旷工' or workdescription='病假' or workdescription='事假')