运用Sql脚本编写数据库，实现增删查改功能。

–1.创建数据库DB_Auth

Create database DB_Auth

–2.删除数据库DB_Auth

drop database DB_Auth

–3.在指定位置创建数据库DB_Auth

Create database DB_Auth
on primary(
name='DB_Auth_data',
filename = 'F:\sql数据库\数据库面试题\201717380139张三\DB_Auth_data.mdf', 
size = 5MB,     --主数据库文件的初始大小
maxsize = 100MB,--主数据库文件增长的最大值
filegrowth = 15%--主数据文件                                    
)

–4.在创建部门表Department(编号[主键自增]，名称，备注)

use DB_Auth
go
Create table Department(
DeparID int primary key identity(1,1)
)

–5.删除部门表Department

drop table Department

–6.再次创建部门表Department，并设置主键自增

use DB_Auth
Create table Department(
DeparID int primary key identity(1,1),--primary key 主键 identity 自增
Name nvarchar(50),
Remark nvarchar(100)
)

–7.修改表的字段[名称]为非空

alter table Department alter column Name nvarchar(50) not null

–8.在部门表插入，系统管理部、人力资源部、物资部、采购部

insert into Department
values('系统管理部',''),('人力资源部',''),('物资部',''),('采购部','')

–9.查询部门表信息

select * from Department

–10.修改人力资源部的备注为：人员招聘，培训，考核

update Department set Remark='人员招聘，培训，考核' where Name='人力资源部'

–11.删除采购部

delete from Department where Department.Name='采购部'

–12.创建员工表Employee，添加员工编号,姓名，性别，生日，薪资，备注，所在部门等

create table Employee(
EmpId int primary key identity(1,1),--员工编号
Name nvarchar(10) not null,--姓名
Sex nvarchar(2) not null,--性别
Bir date not null,--生日
Wages decimal(10,2) not null,--薪资
Remark nvarchar(100),--备注
DeparID int references Department(DeparID)--外键关系
)

–查部门名称和员工数

select d.Name,count(*)
from Department d,Employee e
where d.DeparID=e.DeparID
group by d.Name

–13.分别查询部门表和员工表

select d.Name,e.Name,e.Sex,e.Bir,e.Wages from
c Department d,Employee e where d.DeparID=e.DeparID 

–14.新增员工信息

insert into Employee
values('王系一','男','2000/5/4',5000,null,1)

–15.男员工大于10人的部门

select d.name
from Department d,Employee e
where d.DeparID=e.DeparID and 
(select count(EmpId) from Employee where Sex='男')>10

–16.查询在30天内过生日的员工

select Name as '姓名',(dateadd(year,datediff(year,Bir,getdate()),Bir)) 
as '出生年月' from Employee  
where  (dateadd(year,datediff(year,Bir,getdate()),Bir)) 
between getdate() and getdate()+30

–17.查询20岁以下的员工

select * from Employee e where Year(GETDATE())-Year(e.Bir)<20   --方法一
select * from Employee where DATEDIFF(YY,Bir,getdate())<20      --方法二

–18.查询薪资从高到低排名在10-20之间的员工

select  top 20 e.Wages from Employee e where e.EmpId not in
(
select top 10 d.EmpId 
from  
Employee d 
order by d.Wages desc  )
 order by e.Wages desc 

–19.创建触发器，用于验证新增员工时年龄>18岁

create trigger trigger_name
on Employee
For insert
as
Declare @age int
Select @age=DATEDIFF(YEAR,i.Bir,GETDATE()) from inserted i
if(@age<=18)
begin
raiserror('员工年龄必须大于18岁',16,8)
rollback transaction
end

–20.使用游标遍历查询工资的每一行数据，累加到总工资中

Declare @sum int=0,@wages int
--声明游标
Declare wagesCursor cursor local
for Select Wages from Employee
--打开游标
open wagesCursor
--读取数据
fetch from wagesCursor into @wages
while @@FETCH_STATUS=0
begin
set @sum+=@wages
fetch next from wagesCursor into @wages
end
--关闭游标
close wagesCursor
--删除游标
deallocate wagesCursor
--输出结果
print @sum
go

–21.创建视图查询员工信息及所在部门名称

 go
 create view view_Emp
 as
select d.Name '部门名称',e.Name '员工姓名',e.Sex '性别',e.Wages '工资',e.Bir '生日',e.Remark '备注' from
 Employee e inner join Department d on e.DeparID=d.DeparID
 --执行
 select * from view_Emp

–22.创建存储过程查询姓名为张三的员工信息

 go
 create proc proc_Emp
 as
 select * from Employee where Name='张三'
 --执行
 exec proc_Emp

–23.实现sql分页格式

select top rows * from 表 Where stuid not in( Select top
((page-1)*rows) stuid from 表 )
rows:行数 page:页数