USE MASTER
GO
CREATE DATABASE FDC
ON
(
NAME=FDC_DATA,
FILENAME='d:\fdcdatabase\fdcdata.mdf',
SIZE=30MB,
MAXSIZE=200MB,
FILEGROWTH=10MB
)
LOG ON
(
NAME=FDC_LOG_DATA,
FILENAME='d:\fdcdatabase\fdcdata.ldf',
SIZE=30MB,
MAXSIZE=200MB,
FILEGROWTH=10%
)
USE FDC
GO
CREATE TABLE LOGIN1--登录表
(
Username varchar(10) ,--用户名
Userpwd varchar(20) not null, --用户密码
Useridenty varchar(20) not null --用户身份
foreign key (Username) references Employee(Empnum)
ON DELETE CASCADE
ON UPDATE CASCADE
)
go
select *
from LOGIN1
insert into LOGIN1 values('201101','lili','职员')
insert into LOGIN1 values('201102','wangshan','经理')
insert into LOGIN1 values('201103','lilei','经理')
insert into LOGIN1 values('201104','guanxiaoiqng','职员')
insert into LOGIN1 values('201105','xiaoling','职员')
--drop PROCEDURE LOGIN_登录验证
CREATE PROCEDURE LOGIN_登录验证
@USERNAME VARCHAR(10),@USERPWD VARCHAR(20),@Useridenty varchar(20)=NULL
AS
BEGIN
IF (@Useridenty='管理员')
BEGIN
PRINT '登陆成功!'
return
END
IF EXISTS
(
SELECT *
FROM LOGIN1
WHERE USERNAME=@Username AND USERPWD=@Userpwd
)
BEGIN
PRINT '登陆成功!'
END
ELSE
PRINT'您登录的账户不存在,请查证后重新输入!'
END
GO
EXEC LOGIN_登录验证 '201106','xiaoling','管理员'
EXEC LOGIN_登录验证 '201101','xiaoling'
EXEC LOGIN_登录验证 '201105','xiaoling','职员'
(
Empnum varchar(10) not null,--员工编号
Empname varchar(20) unique,--员工姓名
Empsex VARCHAR (4) CHECK (Empsex IN ('男','女')),
Emppwd varchar(20) not null,--员工密码
Empaddress varchar(50),--员工地址
Emptel varchar(20),--员工
Empemaile varchar(20),--员工电邮地址
Empid varchar(20) unique,--员工身份证号
Empidenty varchar(20),--员工职位
Remarks varchar(50),
primary key(Empnum)
)
insert into Employee values('201101','李丽','女','lili','北京','132********','lili','130524************','职员','')
insert into Employee values('201102','王珊','女','wangshan','北京','132********','wangshan','130524************','经理','')
insert into Employee values('201103','李磊','男','lilei','北京','132********','lilei','130524************','经理','')
insert into Employee values('201104','关少刚','男','guanxiaoiqng','北京','132********','guanxiaoqing','130524************','职员','')
insert into Employee values('201105','小玲','女','xiaoling','北京','132********','xiaoling','130524198902321504','职员','')
select *
from Employee
--存储过程
--查询员工信息
--按编号查询员工信息
CREATE PROCEDURE SELECT_Empnum @编号 VARCHAR (10)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @编号 IS NULL
BEGIN
PRINT '请输入员工编号!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empnum FROM Employee WHERE Empnum =@编
号 )
BEGIN
PRINT '该姓名的员工不存在,请查证后再查!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Employee
WHERE Empnum LIKE @编号
END
GO
EXEC SELECT_Empnum '201101'
EXEC SELECT_Empnum '201107'
--按姓名查询员工信息
CREATE PROCEDURE SELECT_Empname @姓名 VARCHAR(20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @姓名 IS NULL
BEGIN
PRINT '请输入员工姓名!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empname FROM Employee WHERE Empname =@姓名 )
BEGIN
PRINT '该姓名的员工不存在,请查证后再输入!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Employee
WHERE Empname LIKE @姓名
END
GO
EXEC SELECT_Empname '王珊'
EXEC SELECT_Empname '王磊'
--按职位查询员工信息
CREATE PROCEDURE SELECT_Empidenty @职位 VARCHAR (20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @职位 IS NULL
BEGIN
PRINT '请输入要查询的员工职位!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empidenty FROM Employee WHERE Empidenty =@职位 )
BEGIN
PRINT '该职位不存在,请查证后再输入!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Employee
WHERE Empidenty LIKE @职位
END
GO
EXEC SELECT_Empidenty '经理'
EXEC SELECT_Empidenty '职员'
-
-按身份证号查询员工信息
CREATE PROCEDURE SELECT_Empid @身份证号 VARCHAR (20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @身份证号 IS NULL
BEGIN
PRINT '请输入要查询的员工身份证号!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empid FROM Employee WHERE Empid =@身份证号 )
BEGIN
PRINT '该身份证号的员工不存在,请查证后再重新查询!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Employee
WHERE Empid LIKE @身份证号
END
GO
EXEC SELECT_Empid '130524************'
EXEC SELECT_Empid '130524************'
--按员工编号查询售房信息
CREATE PROCEDURE SELECT_Depnum5 @编号 VARCHAR (10)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @编号 IS NULL
BEGIN
PRINT '请输入员工编号后再查询!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT 员工编号 FROM Depsale WHERE 员工编号=@编号 )
BEGIN
PRINT '该员工编号的售房信息不存在,请查证后再输入!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Depsale
WHERE 员工编号 LIKE @编号
END
GO
EXEC SELECT_Depnum5 '201105'
DROP PROCEDURE SELECT_Depnum5
--统计员工工作量
SELECT 员工编号, COUNT( 员工编号) AS 工作量
FROM Depsale
GROUP BY 员工编号
--删除员工信息
-
-按照编号删除员工信息
CREATE PROCEDURE DELETE_Empnum @编号 VARCHAR(10)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @编号 IS NULL
BEGIN
PRINT '请输入员工编号后再删除!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empnum FROM Empl
oyee WHERE Empnum =@编号 )
BEGIN
PRINT '该编号的员工不存在,请查证后在输入!'
RETURN @ERRORVALURE
END
ELSE
DELETE FROM LOGIN1 WHERE Username =@编号
DELETE FROM Employee WHERE Empnum =@编号
PRINT '信息删除成功!'
END
GO
EXEC DELETE_Empnum '201102'
EXEC DELETE_Empnum '201106'
--按姓名删除员工信息
CREATE PROCEDURE DELETE_Empname @姓名 VARCHAR(20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @姓名 IS NULL
BEGIN
PRINT '请输入员工姓名后再删除!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empname FROM Employee WHERE Empname =@姓名 )
BEGIN
PRINT '该编号的员工不存在,请查证后再删除!'
RETURN @ERRORVALURE
END
ELSE
declare @编号 varchar(10)
set @编号=(select Empnum from Employee WHERE Empname =@姓名 )
DELETE FROM LOGIN1 WHERE Username =@编号
DELETE FROM Employee WHERE Empname =@姓名
PRINT '信息删除成功!'
END
GO
EXEC DELETE_Empname '李丽'
EXEC DELETE_Empname '李山'
--按身份证号删除员工信息
CREATE PROCEDURE DELETE_Empid @身份证号 VARCHAR(20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @身份证号 IS NULL
BEGIN
PRINT '请输入员工身份证号后再删除!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT Empid FROM Employee WHERE Empid =@身份证号 )
BEGIN
PRINT '该身份证号号的员工不存在,请重新删除!'
RETURN @ERRORVALURE
END
ELSE
declare @编号 varchar(10)
set @编号=(select Empnum from Employee WHERE Empid =@身份证号 )
DELETE FROM LOGIN1 WHERE Username =@编号
DELETE FROM Employee WHERE Empid =@身份证号
PRINT '信息删除成功!'
END
GO
EXEC DELETE_Empid '130524************'
EXEC DELETE_Empid '130524198902321504'
--更新信息编号
--按编号更新员工信息
CREATE PROCEDURE UPDATE_Empnum @编号1 VARCHAR (10),@编号 VARCHAR (10),@姓名 VARCHAR (20),
@性别 VARCHAR (4),@员工密码 VARCHAR (20),@住所 VARCHAR (50),@电话号码 varchar(20),
@邮箱 VARCHAR (20),@身份证号 VARCHAR(10),@职位 VARCHAR(10),@备注 varchar(50)
AS
BEGIN
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
IF NOT EXISTS
(SELECT Empnum FROM Employee WHERE Empnum =@编号1 )
BEGIN
PRINT '该编号的员工不存在,请重新更新!'
RETURN @ERRORVALURE
END
IF EXISTS
(SELECT *
FROM Employee
WHERE Empnum!=@编号1 AND Empname =@姓名 OR Empidenty =@身份证号 )
BEGIN
PRINT '姓名或身份证号取唯一值,请重新添加!'
RETURN @ERRORVALURE
END
UPDATE LOGIN1
SET USERNAME=@编号,USERPWD=@员工密码
WHERE USERNAME=@编号1
UPDATE Employee
SET Empnum=@编号,Empname =@姓名,Empsex =@性别, Emppwd=@员工密码 ,Empaddress =@住所 ,
Emptel =@电话号码 ,Empid =@身份证号 , Empemaile=@邮箱 ,Empidenty =@职位,Remarks=@备注
WHERE Empnum=@编号1
UPDATE Employee
SET Empnum=@编号 ,Empname =@姓名,Empsex =
@性别, Emppwd=@员工密码 ,Empaddress =@住所 ,
Emptel =@电话号码 ,Empid =@身份证号 , Empemaile=@邮箱 ,Empidenty =@职位,Remarks=@备注
WHERE Empnum=@编号1
PRINT '更新信息成功!'
END
GO
EXEC UPDATE_Empnum '201103','201103','Allar','女','xaoling','北京','132********','xiaoling','130524192902321504','职员',''
--按编号更新员工备注信息
CREATE PROCEDURE UPDATE_Empnum1 @编号1 VARCHAR (10),@备注 varchar(50)
AS
BEGIN
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
IF NOT EXISTS
(SELECT Remarks=@备注 FROM Employee WHERE Empnum =@编号1 )
BEGIN
PRINT '该编号的员工不存在,请重新输入!'
RETURN @ERRORVALURE
END
UPDATE Employee
SET Remarks=@备注
WHERE Empnum=@编号1
PRINT '更新信息成功!'
END
go
EXEC UPDATE_Empnum1 '201105','请假'
--添加信息
CREATE PROCEDURE INSERT_Emp @编号 VARCHAR(10),@姓名 VARCHAR(20),
@性别 VARCHAR(4),@员工密码 VARCHAR(20),@住所 VARCHAR(50),@电话号码 varchar(20),
@邮箱 VARCHAR(20),@身份证号 VARCHAR(10),@职位 VARCHAR(10),@备注 varchar(50)
AS
BEGIN
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
IF( @姓名 IS NULL OR @身份证号 IS NULL )
BEGIN
PRINT '姓名,身份证号不能为空!'
RETURN @ERRORVALURE
END
IF EXISTS
(
SELECT *
FROM Employee
WHERE Empname =@姓名 OR Empnum =@编号 OR Empid =@身份证号
)
BEGIN
PRINT '该员工姓名或者身份证号已存在!请重新添加!'
RETURN @ERRORVALURE
END
ELSE
INSERT INTO LOGIN1 VALUES(@编号,@员工密码,@职位)
INSERT INTO Employee VALUES(@编号,@姓名,@性别,@员工密码,@住所 ,@电话号码 ,@邮箱 ,@身份证号,@职位,@备注)
PRINT '添加成功!'
END
GO
EXEC INSERT_Emp '201108','李建一','男','lijianyi','北京','152********','lijianyi','130524************','职员',''
CREATE TABLE Client--客户信息表
(
客户编号 varchar(10) primary key,
客户姓名 VARCHAR(20) NOT NULL,
varchar(16),
电邮地址 varchar(20),
身份证号 varchar(26) unique,
登记日期 datetime default getdate(),
备注 varchar(50)
)
select *
from Client
INSERT INTO Client( 客户编号,客户姓名, ,电邮地址,身份证号 ,备注 ) VALUES('20110101','王刚','153********','wanggang','13054226678913',' ')
INSERT INTO Client( 客户编号,客户姓名, ,电邮地址,身份证号 ,备注 ) VALUES('20110102','Para','153********','para','13054236675913',' ')
INSERT INTO Client ( 客户编号,客户姓名, ,电邮地址,身份证号 ,备注 )VALUES('20110103','张玲','153********','zhangling','13054436678913',' ')
INSERT INTO Client( 客户编号,客户姓名, ,电邮地址,身份证号 ,备注 ) VALUES('20110104','李静','153********','lijing','13054236678913',' ')
INSERT INTO Client ( 客户编号,客户姓名, ,电邮地址,身份证号 ,备注 )VALUES(
'20110105','李善','153********','lishan','13054233478913',' ')
--按编号查询客户信息
CREATE PROCEDURE SELECT_Clinum @编号 VARCHAR (10)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @编号 IS NULL
BEGIN
PRINT '请输入客户编号后再查询!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT 客户编号 FROM Client WHERE 客户编号=@编号 )
BEGIN
PRINT '该编号的客户不存在,请查证后再输入!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Client
WHERE 客户编号 LIKE @编号
END
GO
EXEC SELECT_Clinum '20110102'
EXEC SELECT_Clinum '20110106'
--按姓名查询客户信息
CREATE PROCEDURE SELECT_Cliname @姓名 VARCHAR(20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @姓名 IS NULL
BEGIN
PRINT '请输入姓名后再查询!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT 客户姓名 FROM Client WHERE 客户姓名=@姓名 )
BEGIN
PRINT '该姓名的客户不存在,请查证后再查!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Client
WHERE 客户姓名 LIKE @姓名
END
GO
EXEC SELECT_Cliname 'Para'
--按身份证号查询客户信息
CREATE PROCEDURE SELECT_Cliid @身份证号 VARCHAR (20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @身份证号 IS NULL
BEGIN
PRINT '请输入客户身份证号后再查询!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT 身份证号 FROM Client WHERE 身份证号=@身份证号 )
BEGIN
PRINT '该身份证号的客户不存在,请重新查询!'
RETURN @ERRORVALURE
END
ELSE
SELECT *
FROM Client
WHERE 身份证号 LIKE @身份证号
END
GO
EXEC SELECT_cliid '13054233478913'
--删除客户信息
CREATE PROCEDURE DELETE_Clinum @编号 VARCHAR(10)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @编号 IS NULL
BEGIN
PRINT '请输入客户编号后再删除!'
RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT 客户编号 FROM Client WHERE 客户编号=@编号 )
BEGIN
PRINT '该编号的客户不存在,请重新删除!'
RETURN @ERRORVALURE
END
ELSE
DELETE FROM Client WHERE 客户编号 =@编号
PRINT '信息删除成功!'
END
GO
EXEC DELETE_Clinum '20110102'
--按姓名删除客户信息
CREATE PROCEDURE DELETE_Cliname @姓名 VARCHAR(20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE =0
BEGIN
IF @姓名 IS NULL
BEGIN
PRINT '请输入客户姓名后再删除!'
天津机动车摇号网站RETURN @ERRORVALURE
END
IF NOT EXISTS
(SELECT 客户姓名 FROM Client WHERE 客户姓名 =@姓名 )
BEGIN
PRINT '该编号的客户不存在,请重新删除!'
RETURN @ERRORVALURE
END
ELSE
DELETE FROM Client WHERE 客户姓名 =@姓名
PRINT '信息删除成功!'
END
GO
EXEC DELETE_Cliname '王刚'
--按身份证号删除客户信息
CREATE PROCEDURE DELETE_Cliid @身份证号 VARCHAR(20)
AS
DECLARE @ERRORVALURE INT
SET @ERRORVALURE
发布评论