黑马程序员——常用的数据库操作

------- Windows Phone 7手机开发、.Net培训、期待与您交流！ ------- 

if exists(
select * from sys.databases where name=N'Test2'
)
begin
--删除数据库
drop database [Test2]
print 'drop database [Test2]'
--分离数据库
USE [master]
ALTER DATABASE [Test2] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'Test2', @skipchecks = 'false'
end


--附加数据库
CREATE DATABASE [Test2] ON 
( FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test2.mdf' ),
( FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test2_log.ldf' )
FOR ATTACH
--创建数据库
CREATE DATABASE [Test2]
 ON  PRIMARY 
( NAME = N'Test2', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test2.mdf' , SIZE = 4160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Test2_log', FILENAME = N'F:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test2_log.ldf' , SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
--创建表
if exists(
select * from sys.tables where name=N'Student'
)
begin
drop table Student
print 'drop table Student'
end
create table Student
(
id int identity(1,1) primary key,
studentNum varchar(20),
studentName nvarchar(20)
)


--给表增加列
if not exists(select * from sys.columns where object_id = OBJECT_ID(N'Student') and name = N'Age')
alter table Student add  Age int
--修改一列
if exists(select * from sys.columns where object_id = OBJECT_ID(N'Student') and name = N'Age')
alter table Student alter Column Age bigint
--删除一列
if exists(select * from sys.columns where object_id = OBJECT_ID(N'Student') and name = N'Age')
alter table Student drop Column Age 


--删除存储过程
if exists(select * from sys.procedures where name=N'usp_InitStudentInfo')
begin
drop proc usp_InitStudentInfo
print 'drop proc usp_InitStudentInfo'
end
go
--创建存储过程
create proc usp_InitStudentInfo
@number varchar(20),
@name nvarchar(20),
@outNumber int out
as
insert into Student(studentNum,studentName)
values(@number,@name)
select @outNumber = studentNum from Student order by id desc
go




--修改存储过程
alter proc usp_InitStudentInfo
@number varchar(20),
@name nvarchar(20),
@outNumber varchar(20) out
as
insert into Student(studentNum,studentName)
values(@number,@name)
select top 1 @outNumber = studentNum from Student order by id desc
go


--使用存储过程
declare @studentNumber varchar(20)
exec usp_InitStudentInfo B090160226,N'张三', @studentNumber out
select @studentNumber


--删除触发器
if exists(select * from sys.triggers where name=N'TG_Student_Up')
begin
drop trigger TG_Student_Up
print'drop trigger TG_Student_Up'
end


--为Student表创建历史表，并创建触发器
if exists(
select * from sys.tables where name=N'StudentHist'
)
begin
drop table StudentHist
print 'drop table StudentHist'
end
create table StudentHist
(
HistID int identity(1,1) primary key,
id int,
studentNum varchar(20),
studentName nvarchar(20),
Loggon datetime
)
go


create trigger TG_Student_Up on Student
for update
as
insert into StudentHist (id,studentNum,studentName,Loggon)
select id,studentNum,studentName,GETDATE() from deleted


create table #tempdee(id int)
--判断临时表是否存在
if exists(select * from tempdb.sys.objects where object_id = OBJECT_ID(N'tempdb..#tempdee'))