Question

求一份完整的SQL语句（建立数据库以及增删改查）的存储过程

要求：1、用SQL语句建立一个ShopDB2、用SQL语句对其进行增、删、改、查

Answer 1

/*----创建数据库---*/
Use master
Go
/* ---检查是否已存在ShopDB数据库:查询master数据库的系统表sysdatabases---*/
If Exists (select * from sysdataBases where name = 'ShopDB')
Drop dataBase ShopDB
Go
Create DataBase ShopDB
On
Primary
(
/*主数据文件的具体描述*/
Name = 'ShopDB',
FileName = 'E:\ShopDB_data.mdf',
Size = 10MB,
FileGrowth = 20%
)
Log On
(
/*次要数据文件的具体描述*/
Name = 'ShopDB2',
FileName = 'E:\ShopDB_log.ldf',
Size = 3MB,
MaxSize = 20MB,
FileGrowth = 10%
)
GoUse ShopDB
Go
/*------------------------创建数据库表------------------*/
If Exists (select * from sysObjects where name = 'Users')
Drop table Users
Go
Create Table Users
(
ID int Identity (1,1) not null ,
myname varchar (15) not null ,
age int
)
Go---------插入数据-----------
insert into Users values ('雨',18)
insert into Users values ('叶',21)
insert into Users values ('露',24)select * from users /*--检测是否存在:存储过程存放在系统表sysObjects中--*/
If Exists ( select * from sysobjects where name = 'proc_select')
Drop procedure proc_select
Go
/*-----创建proc_select存储过程-----*/
Create procedure proc_select
As
Select age,myname from Users where age > 18
Go/*-------调用存储过程-------*/
Exec proc_select
Go/*----------------创建proc_insert带参数的存储过程---------------*/
If Exists ( select * from sysobjects where name = 'proc_insert')
Drop procedure proc_insert
Go
Create procedure proc_insert
@userName varchar(20),
@age int
As
insert into Users values (@userName,@age)
Go /*-------调用存储过程-------*/
Exec proc_insert sky,20
Go/*----------------创建proc_update存储过程---------------*/
If Exists ( select * from sysobjects where name = 'proc_update')
Drop procedure proc_insert
Go
Create procedure proc_update
@userName varchar(20),
@age Money
As
update Users set age=@age where myname = @userName
Go /*-------调用存储过程-------*/
Exec proc_update sky,21
Go
/*----------------创建proc_delete存储过程---------------*/
If Exists ( select * from sysobjects where name = 'proc_delete')
Drop procedure proc_delete
Go
/*------创建存储过程-----*/
Create procedure proc_delete
@userName varchar(20)
As
delete from Users where myName = @userName
Go/*-------调用存储过程-------*/
Exec proc_delete sky
Go