Create DB Table View Procedure

最新推荐文章于 2022-05-22 13:23:25 发布

weixin_30437481

最新推荐文章于 2022-05-22 13:23:25 发布

阅读量63

点赞数

原文链接：http://www.cnblogs.com/ganting/p/6750371.html

版权

本文档详细介绍了使用SQL进行数据库设计的过程，包括创建数据库、表结构定义、插入数据、删除操作以及视图和存储过程的创建。通过具体实例展示了如何实现学生信息与公司工资信息的关联，并通过视图简化查询流程，利用存储过程实现特定业务逻辑的自动化。

摘要由CSDN通过智能技术生成

IF DB_ID('Wages') IS NOT NULL
DROP DATABASE Wages
GO
create database Wages

if OBJECT_ID('StudentInfo','U') IS NOT NULL
DROP TABLE StudentInfo
GO

CREATE TABLE StudentInfo(
ID int identity(1,1) NOT NULL PRIMARY KEY,
Name nvarchar(10) not null,
CompanyID int,
PostDate datetime,
Wage int,
--FOREIGN KEY (CompanyID) REFERENCES WageInfo(CompanyID)
)

if OBJECT_ID('WageInfo','U') IS NOT NULL
DROP TABLE WageInfo
GO

CREATE TABLE WageInfo(
CompanyID int identity(1,1) primary key,
CompanyName nvarchar(50) not null,
Poste nvarchar(50) not null
)

Alter table WageInfo add FOREIGN KEY (CompanyID) REFERENCES WageInfo(CompanyID)

INSERT INTO WageInfo(CompanyName,Poste)
values
('NCS','DEVELOPER'),
('WISTRON','DEVELOPER'),
('IBM1','DEVELOPER'),
('IBM2','DEVELOPER'),
('IBM3','DEVELOPER');

select * from WageInfo

INSERT INTO StudentInfo(Name,CompanyID,PostDate,Wage)
values
('Name1',1,GETDATE(),2000),
('Name2',2,GETDATE(),2500),
('Name3',3,GETDATE(),3000),
('Name4',4,GETDATE(),2000),
('Name4',NULL,NULL,NULL);

delete from StudentInfo
select * from StudentInfo

--view1

IF EXISTS(SELECT * FROM sysobjects where name='V_EMPLOYED_STUDENT' )
DROP VIEW V_EMPLOYED_STUDENT
GO

CREATE VIEW V_EMPLOYED_STUDENT
AS

select Name,Poste,CompanyName,Wage,PostDate
from StudentInfo A INNER JOIN WageInfo B
ON A.CompanyID=B.CompanyID

--view2
IF EXISTS(SELECT * FROM sysobjects where name='V_NOT_EMPLOYED_STUDENT' )
DROP VIEW V_NOT_EMPLOYED_STUDENT
GO

CREATE VIEW V_NOT_EMPLOYED_STUDENT
AS

select * from StudentInfo where CompanyID is not null

GO
--proc

IF OBJECT_ID('PROC_HALF_DEVELOPER','P') IS NOT NULL
BEGIN
DROP PROCEDURE PROC_HALF_DEVELOPER
END
GO

CREATE PROCEDURE PROC_HALF_DEVELOPER

declare @Half_CT decimal
declare @CT decimal

select @Half_CT=COUNT(*)/2 from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER'

select @CT=COUNT(*) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER' and (A.Wage<3000 or A.Wage<4000 or A.Wage<6000)

--having COUNT(*)>=@Half_CT
--select @Half_CT,@CT

while(@CT>=@Half_CT)
begin

update A
SET A.Wage=A.Wage+50
FROM StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER'

select @CT=COUNT(*) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER' and (A.Wage<3000 or A.Wage<4000 or A.Wage<6000)

end

--proc

IF OBJECT_ID('PROC_AVG_DEVELOPER','P') IS NOT NULL
BEGIN
DROP PROCEDURE PROC_AVG_DEVELOPER
END
GO

CREATE PROCEDURE PROC_AVG_DEVELOPER

declare @Wage_AVG decimal
declare @n int

select @Wage_AVG=AVG(A.Wage) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID

while(@Wage_AVG<=3500 )
begin

update A
SET A.Wage=A.Wage+50
from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER'

select @Wage_AVG=AVG(A.Wage) from StudentInfo A inner join WageInfo B on A.CompanyID=B.CompanyID
where B.Poste='DEVELOPER'

end

转载于:https://www.cnblogs.com/ganting/p/6750371.html