雇员

use master

/*----------------------------建库------------------------*/

if exists(select * from sysdatabases where name='ClassNorthwind')

  drop database ClassNorthwind

go

create database ClassNorthwind

on primary

(

 name='ClassNorthwind_data',

 filename='F:/project/ClassNorthwind_data.mdf',

 size=25mb,

 maxsize=100mb,

 filegrowth=1mb

)

log on

(

 name='ClassNorthwind_log',

 filename='F:/project/ClassNorthwind_data.ndf',

 size=15mb,

 filegrowth=1mb,

 maxsize=30mb

)

--使用sp_helpdb存储过程浏览有关数据库ClassNorthwind的信息

exec sp_helpdb ClassNorthwind

 

go

--编写和执行将ClassNorthwind事务日志文件的最大文件长度增加到MB的语句

alter database ClassNorthwind

modify file

(

 name='ClassNorthwind_log',

 maxsize=50mb

)

 

--编写和执行向数据库ClassNorthwind添加一个事务日志文件

alter database classNorthwind

add log file

(

name='ClassNorthwind3',

filename='F:/project/ClassNorthwind3_data.ndf',

size=20MB,

filegrowth=1MB,

maxsize=40MB

)

 

use ClassNorthwind

/*----------雇员表---------*/

if exists(select * from sysobjects where name='Employees')

   drop table Employees

go

--创建雇员表Employees

create table Employees

(

 EmployeeID int identity(1,1) not null,

 Ename varchar(10) not null,

 Sex char(2) not null,

 Birthdate Datetime null,

 Address varchar(50) null,

 Phone Char(13) null,

 Remark text

)

 

/*----------雇员工资表---------*/

if exists(select * from sysobjects where name='Wage')

   drop table Wage

go

--雇员工资表wage

create table Wage

(

 EmployeeID int not null,

 Wname varchar(10) not null,

 Wage money not null,

 Putdate Datetime not null

)

 

/*----------工资税表---------*/

if exists(select * from sysobjects where name='Tax')

   drop table Tax

go

--工资税表tax

create table Tax

(

 EmployeeID int not null,

 Tname varchar(10) not null,

 Tax money not null,

 Paydate datetime not null

)

 

--  向雇员表Employees中添加列Department varchar(20) NULL

alter table Employees add  Department varchar(20)

--  修改雇员表Employees中的列Address varchar(60)

alter table Employees alter column Address varchar(60)

--  删除雇员表Employees中的列Remark

alter table Employees drop column Remark

--  用存储过程sp_help查看三个表的信息

exec sp_help Employees

exec sp_help wage

exec sp_help tax

 

 

--实践：实现SQL Server数据库的完整

--（）  创建雇员表Employees的主关键字，列：EmployeeID

alter table Employees add constraint PK_Employee primary key(EmployeeID)

 

--（）  创建雇员工资表wage的外部关键字，列：EmployeeID，参考Employees的主键

 alter table wage add constraint FK_EmployeeID foreign key(EmployeeID)references Employees(EmployeeID)

 

--（）  创建工资税表tax的外部关键字，列：EmployeeID，参考Employees的主键

alter table tax add constraint FK_tax_EmployeeID foreign key(EmployeeID)references Employees(EmployeeID)

 

--（）  向雇员表Employees中添加关于以下列的DEFAULT约束：name：unknown，sex：男，Address：Salt Lake，Phone：[phone]

alter table Employees add constraint DF_name default('unknown')for Ename,

constraint DF_Sex default('男')for Sex,

constraint DF_Address default('Salt Lake')for Address ,

constraint DF_Phone default('[phone]')for Phone

go

 

--（）  向雇员表Employees中添加BirthDate列的CHECK约束：在BirthDate列中的值必须早于今天的日期

alter table Employees add constraint CK_Birthday check(Birthday<getdate())

 

--（）  定义雇员电话号码Phone的规则：(***)********，其中*是~9之间的数字字符

alter table Employees add constraint CK_Phone check(Phone like '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

 

--（）  执行存储过程sp_helpconstraint浏览上面创建的约束信息

exec sp_helpconstraint Employees

go

exec sp_helpconstraint tax

go

exec sp_helpconstraint wage

go

 

 

--（）  向雇员工资表wage和工资税表tax中添加关于以下列的DEFAULT约束：

--Putdate：昨天，Paydate：昨天

alter table wage add constraint DF_Putdate default(getdate()-1)for Putdate

alter table tax add constraint DF_Paydate default(dateadd(dd,-1,getdate()))for paydate

 

 

--实践：创建和修改SQL Server数据库的表

-- 向雇员表Employees中插入十个雇员信息

insert Employees(Ename,Sex,Birthdate,Address,phone,Department) values('one','男','1988/8/8','北京','(123)45678900','化学')

insert Employees values('two','女','1989/10/8','湖北','(123)45678901','音乐')

insert Employees values('three','男','1987/5/4','湖南','(125)45678978','美术')

insert Employees values('four','男','1990/8/12','北京','(123)45678912','英语')

insert Employees values('five','男','1985/2/4','福建','(135)75670000','数学')

insert Employees values('six','女','1984/8/8','金珠','(123)67890000','物理')

insert Employees values('seven','男','1989/12/12','岳阳','(143)45678934','美容')

insert Employees values('eight','男','1989/3/15','北京','(123)45678956','计算机')

insert Employees values('nine','女','1988/3/9','岳阳','(123)45678968','历史')

insert Employees values('ten','男','1992/4/8','天津','(123)45678945','政治')

select * from Employees

go

--  向工资税表tax中插入十个雇员相应的交税情况

insert tax(Tname,tax,Paydate) values(21,'one',55,getdate())

insert tax values(22,'two',55,getdate())

insert tax values(23,'three',25,getdate())

insert tax values(24,'four',25,getdate())

insert tax values(25,'five',25,getdate())

insert tax values(26,'six',35,getdate())

insert tax values(27,'seven',35,getdate())

insert tax values(28,'eight',45,getdate())

go

 

--雇员工资表wage中插入十个雇员的工资情况

insert wage(Wname,wage,Putdate) values(21,'one',5500,getdate())

insert wage values(22,'two',5500,getdate())

insert wage values(23,'three',2500,getdate())

insert wage values(24,'four',2500,getdate())

insert wage values(25,'five',2500,getdate())

insert wage values(26,'six',3500,getdate())

insert wage values(27,'seven',3500,getdate())

insert wage values(28,'eight',4500,getdate())

insert wage values(29,'nine',4500,getdate())

insert wage values(30,'ten',5000,getdate())

 

 

--实践：创建SQL Server数据库表的视图和索引

--  在雇员表Employees中，创建一个Ename和phone列上的合成索引

if exists(select * from sysindexes where name='IX_name_phone')

drop index Employees.IX_name_phone

go

create nonclustered index IX_name_phone on Employees(Ename ,phone)

with fillfactor=30

go

 

 

--  使用存储过程sp_helpindex Employees列出表的索引

exec sp_helpindex Employees

go

 

 

--  分别查询雇员表Employees、雇员工资表wage和工资税表tax中的行数据

select * from  Employees

select * from tax

select * from wage

 

--  创建一个视图，用于检索雇员工资、税款情况，包括下列字段：EmployeeID、Name、Sex、Wage、Putdate 、Tax、Paydate

if exists(select * from sysobjects where name='view_Employees_tax_wage')

drop view view_Employees_tax_wage

go

create view view_Employees_tax_wage

as

select Employees.EmployeeID,Ename,Sex,Wage,Putdate,Tax,Paydate from Employees left join Wage on Employees.EmployeeID=Wage.EmployeeID left join tax on Employees.EmployeeID=tax.EmployeeID

go

select * from view_Employees_tax_wage

 

--  创建一个视图，用于检索雇员工资高于元的雇员情况表

if exists(select * from sysobjects where name='view_Employees_tax')

drop view view_Employees_tax

go

create view view_Employees_tax

as

select Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department  from Employees join wage on  Employees.EmployeeID=wage.EmployeeID where wage>=1000

go

select * from view_Employees_tax

 

--  创建一个视图，用于检索雇员工资高于元的雇员情况表

if exists(select * from sysobjects where name='view_Employees_tax2')

drop view view_Employees_tax2

go

create view view_Employees_tax2

as

select Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department  from Employees join wage on  Employees.EmployeeID=wage.EmployeeID where wage>=5000

go

select * from view_Employees_tax2

go

 

 

--实践：查询SQL Server数据库表的信息

--（）  列出每个雇员的工资、税款情况一览表

select Employees.EmployeeID,Ename,Wage,Putdate,Tax,paydate  from Employees left join wage on  Employees.EmployeeID=wage.EmployeeID left join tax on Employees.EmployeeID=tax.EmployeeID

 

--（）  列出工资超过元，而未交税的雇员

select Wname from wage left join tax on tax.EmployeeID=wage.EmployeeID where tax is null and wage>=1000

 

--（）  列出只领工资而未交税的雇员表

select Wname from wage left join tax on tax.EmployeeID=wage.EmployeeID where tax is null

 

--（）  对以上四种情况各作一统计报告

declare @countID int

select @countID=count(*)from Employees

print'雇员人数：'+convert(varchar(20),@countID)

 

select @countID=count(*) from wage left join tax on tax.EmployeeID=wage.EmployeeID where tax is null and wage>=1000

print '工资超过元，而未交税的雇员人数：'+convert(varchar(20),@countID)

 

select @countID=count(*) from wage left join tax on tax.EmployeeID=wage.EmployeeID where tax is null

print '只领工资而未交税的雇员人数：'+convert(varchar(20),@countID)

 

--（）  删除三个表中的所有记录

delete from tax

truncate from wage

delete from Employees

 

 

 

 

 

 

 

 

 

 

--实践：SQL Server数据库的高级查询

--（）  分别列出在雇员表Employees中而未领工资和未交税的男、女雇员情况

select Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department from Employees left join wage on  Employees.EmployeeID=wage.EmployeeID left join  tax on tax.EmployeeID=wage.EmployeeID

 where Employees.EmployeeID not in (select EmployeeID from tax )  and Employees.EmployeeID not in (select EmployeeID from wage )

 

--（）  查询电话号码区号为(010)、工资大于的男雇员情况

select Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department from Employees join wage on Employees.EmployeeID=wage.EmployeeID where Phone like '(010)%' and wage>3000 and sex='男'

 

--（）  查询交税最多的六名雇员情况

select top 6  Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department from Employees join tax on Employees.EmployeeID=tax.EmployeeID order by tax desc

 

--（）  列出工资在前三名的，而交税并不在前三名的雇员情况

select  Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department from Employees join tax on Employees.EmployeeID=tax.EmployeeID join wage on  Employees.EmployeeID=wage.EmployeeID

where Employees.EmployeeID  in (select top 3 EmployeeID from wage order by wage desc) and Employees.EmployeeID not in (select top 3 EmployeeID from tax order by tax desc)

 

 

 

 

 

 

 

 

 

 

--实践：创建、执行、修改SQL Server数据库的存储过程

--（）  编写存储过程，向雇员表Employees、雇员工资表wage和工资税表tax中各插入两条记录

if exists (select * from sysobjects where name='proc_insert')

drop procedure proc_insert

go

create procedure proc_insert

@name varchar(10),

@sex varchar(2),

@Birthday datetime ,

@wage money ,

@tax money,

@Putdate datetime,

@Paydate datetime,

@Address varchar(20)='',

@Phone varchar(13)=default,

@Department varchar(20)=''

as

declare @EmployeeID int

insert Employees values(@name,@sex,@Birthday,@Address,@phone,@Department)

select @EmployeeID=EmployeeID from Employees where EmployeeID=@@identity

insert tax values(@EmployeeID,@name,@tax,@Paydate)

insert wage values(@EmployeeID,@name,@wage,@Putdate)

go

exec  proc_insert 'eleven','男','1984/7/3','2000','20','2008/8/8','2008/8/9'

 

select * from Employees

select * from wage

select * from tax

--（）  编写存储过程，列出工资最高的六名雇员情况

if exists (select * from sysobjects where name='proc_wage')

drop procedure proc_wage

go

create procedure proc_wage

as

select top 6  Employees.EmployeeID,Ename,Sex,Birthday,Address,Phone,Department from Employees join wage on Employees.EmployeeID=wage.EmployeeID order by wage desc

go

exec proc_wage

 

--（）  编写存储过程，查找雇员表Employees中Sex、Phone与指定值相匹配的雇员

if exists(select * from sysobjects where name='proc_select')

drop procedure proc_select

go

create procedure proc_select

@name varchar(10)

as

select Sex,Phone from Employees where Ename=@name

go

exec proc_select 'one'

 

--（）  编写存储过程，查找雇员表Employees中BirthDate在指定两个日期之间的雇员

if exists(select * from sysobjects where name='proc_date')

drop procedure proc_date

go

create procedure proc_date

@date1 datetime,

@date2 datetime

as

select Ename from Employees where Birthday between @date1 and @date2

go

exec proc_date '1988/2/2','2008/8/8'

 

--（）  编写存储过程，用于计算男、女雇员的平均工资，男、女雇员的人数，并输出

if exists(select * from sysobjects where name='proc_count')

drop procedure proc_count

go

create procedure proc_count

@wageGirlAvg money output,

@wageBoyAvg money output,

@GirlCount int output,

@BoyCount int output

as

select @wageGirlAvg=avg(wage) ,@BoyCount=count(*)from Employees join wage on Employees.EmployeeID=wage.EmployeeID where Sex='男'

select @wageBoyAvg=avg(wage) ,@GirlCount=count(*)from Employees join wage on Employees.EmployeeID=wage.EmployeeID where Sex='女'

go

declare @wageGirlAvg money,@wageBoyAvg money,@GirlCount int,@BoyCount int

exec proc_count  @wageGirlAvg output,@wageBoyAvg output,@GirlCount output,@BoyCount output

print'女雇员的平均工资:'+convert(varchar(20),@wageGirlAvg) +'   '+'男雇员的平均工资:'+convert(varchar(20),@wageBoyAvg)

print'女雇员的人数:'+convert(varchar(20),@GirlCount)+'  '+'男雇员的人数:'++convert(varchar(20),@BoyCount)

 

--（）  用存储过程sp_helptext查看上面的存储过程定义

exec sp_helptext proc_insert

go

exec sp_helptext proc_wage

go

exec sp_helptext proc_select

go

exec sp_helptext proc_date

go

exec sp_helptext proc_count

go

 

 

 

 

 

 

 

 

 

 

 

--实践：创建和使用SQL Server数据库的触发器

--（）  创建触发器，从雇员表Employees中一次删除的记录数应不超过条

if exists(select * from sysobjects where name='trig_delete2')

drop trigger trig_delete2

go

create trigger trig_delete2

on Employees

for delete

as

declare @count int

select @count=count(*)from deleted

if @count>2

begin

raiserror('错误，一次删除的记录数应不超过条',16,1)

rollback

end

go

--（）  创建触发器，当向雇员工资表wage中插入记录时，同时向工资税表tax中插入记录，工资税计算为：Wage：以下，不交税；~5000：交税工资的%,5000以上：交税工资的%

if exists (select * from sysobjects where name='trig_tax_wage')

drop trigger trig_tax_wage

go

create trigger trig_tax_wage

on wage

for insert

as

declare @wage money,@tax money,@EmployeeID int,@name varchar(10)

select @wage=wage,@EmployeeID=EmployeeID,@name=Wname from inserted

if (@wage<1000)

set @tax=null

else if(@wage>=1000 and @wage<=5000)

set @tax=@wage/100

else

set @tax=@wage/100*3

insert tax values(@EmployeeID ,@name,@tax,getdate())

go

--insert Employees(Ename,Sex,Birthday) values( 'fourteen','女','1989/5/3')

insert wage values(38,'fourteen',6000,getdate())

 

--（）  创建触发器，当修改雇员工资表wage中的记录时，同时修改工资税表tax中的记录

if exists(select * from sysobjects where name='trig_update')

drop trigger trig_update

go

create trigger trig_update

on wage

for update

as

declare @name varchar(10) ,@wage money,@EmployeeID int,@tax money

select @EmployeeID=EmployeeID from deleted

select @name=Wname, @wage =wage from inserted

if (@wage<1000)

set @tax=null

else if(@wage>=1000 and @wage<=5000)

set @tax=@wage/100

else

set @tax=@wage/100*3

update tax set Tname=@name,tax=@tax where EmployeeId=@EmployeeID

go

update wage set wage=10000 where EmployeeID=37

 

--（）  创建触发器，当删除雇员表Employees中的记录时，同时删除雇员工资表wage和工资税表tax中的有关该雇员的所有记录

if exists(select * from sysobjects where name='trigger_delete')

drop trigger trigger_delete

go

create trigger trigger_delete

on Employees

instead of delete

as

declare @EmployeeID int

select @EmployeeID=EmployeeID from  deleted

print convert(varchar(20),@EmployeeID)

delete from tax where EmployeeID=@EmployeeID

delete from wage where EmployeeID=@EmployeeID

delete from Employees where EmployeeID=@EmployeeID

go

delete from Employees where EmployeeID=38

 

select * from Employees

select * from tax

select * from wage

 

--（）  创建触发器，当修改工资税表tax中Tax列的值时，检查交税情况是否符合（）的交税比例

if exists(select * from sysobjects where name='trig_select')

drop trigger trig_select

go

create trigger trig_select

on tax

for update

as

declare @EmployeeID int ,@wage money,@tax money,@tax2 money

select @EmployeeID=EmployeeID from deleted

if (@wage<1000)

set @tax=null

else if(@wage>=1000 and @wage<=5000)

set @tax=@wage/100

else

set @tax=@wage/100*3

select @tax2=tax from tax

if @tax2<>@tax

begin

raiserror('错误，交税情况不符合交税比例')

rollback transaction

end

go