C# SQL数据库操作语句

/*创建数据库*/ use master if DB_ID (N'zhjData_Data') is not null drop database zhjData_Data -- Get the SQL Server data path use master DECLARE @data_path nvarchar(256); --SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1) -- FROM master.sys.master_files -- WHERE database_id = 1 AND file_id = 1); select @data_path='D:\\zhjData\' --数据库路径 --自动创建数据库 EXECUTE ('CREATE DATABASE zhjData_Data ON ( NAME = zhjData_Data, FILENAME = '''+ @data_path + 'zhjData_Data.mdf'' ) LOG ON ( NAME = zhjData_Log, FILENAME = '''+ @data_path + 'zhjData_Log.ldf'')' ); GO return -- execute the CREATE DATABASE statement EXECUTE ('CREATE DATABASE zhjData_Data ON ( NAME = zhjData_Data, FILENAME = '''+ @data_path + 'zhjData_Data.mdf'', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = zhjData_Log, FILENAME = '''+ @data_path + 'zhjData_Log.ldf'', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )' ); GO /*End 创建数据库*/ /*附加数据库*/ USE master; --1，先分离开数据库,如果需要 GO EXEC sp_detach_db @dbname = N'zhjData_Data'; GO --2，将分离的文件拷到新的目录上或者在原来的目录上引用 USE master; --3， GO CREATE DATABASE MyAdventureWorks ON (FILENAME = ''+ @data_path + 'AdventureWorks_Data.mdf'), (FILENAME = ''+ @data_path + 'AdventureWorks_Log.ldf'), (FILENAME = ''+ @data_path +'MyAdventureWorks') --将新文件复制到这个目录[可隐藏] FOR ATTACH; GO /*End 附加数据库*/ /*备份数据*/ --备份整个数据库到指定目录,如D:\\zhjData\ 为简单备份 USE master; BACKUP DATABASE zhjData_Data TO DISK = ''+ @data_path + 'zhjData_Data.bak' WITH FORMAT GO --备份数据库和日志,完整备份 --01先将数据库设置为全备份模式 USE master; GO ALTER DATABASE zhjData_Data SET RECOVERY FULL; GO --02将数据库和日志备份到相应的目录下面 USE master GO EXEC sp_addumpdevice 'disk', 'AdvWorksData', 'Z:\SQLServerBackups\AdvWorksData.bak'; GO EXEC sp_addumpdevice 'disk', 'AdvWorksLog', 'X:\SQLServerBackups\AdvWorksLog.bak'; GO --03将数据文件恢复到相应的文件里 Back up the full AdventureWorks2008R2 database.-- Back up the AdventureWorks2008R2 log. BACKUP DATABASE AdventureWorks2008R2 TO AdvWorksData; GO BACKUP LOG AdventureWorks2008R2 TO AdvWorksLog; GO --备份差异数据库到备份文件，数据库备份要先有完整备份,调用第一个方法 示例 USE master; BACKUP DATABASE NewPage花样年华 TO DISK = 'D:\zhjData_Data.bak' WITH FORMAT GO USE master; BACKUP DATABASE NewPage花样年华 TO DISK = 'D:\zhjData_Data_AB.bak' WITH DIFFERENTIAL,FORMAT GO /*End 备份数据库*/ /*恢复数据备份*/ --1恢复简单备份模式文件 USE master; RESTORE DATABASE NewPage花样年华 FROM DISK ='D:\zhjData_Data.bak' WITH RECOVERY --(or NORECOVERY ) --2恢复完整备份 USE master; RESTORE DATABASE NewPage花样年华 FROM DISK ='D:\AdvWorksData.bak' WITH NORECOVERY [,MOVE 'AdventureWorks2008R2_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.mdf', MOVE 'AdventureWorks2008R2_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\NewAdvWorks2008R2.ldf'] --将新恢复的数据可以移到某目录上，可以省略 RESTORE LOG NewPage花样年华 FROM DISK='D:\AdvWorksLog.bak' WITH Recovery --3恢复差异备份,先恢复一个完整的包，再在这个基础上恢复备份,备份操作，可以参考前面的备份实例 USE master; RESTORE DATABASE NewPage花样年华 FROM DISK ='D:\zhjData_Data.bak' WITH norecovery RESTORE DATABASE NewPage花样年华 FROM DISK ='D:\zhjData_Data_AB.bak' WITH recovery go /*End恢复数据备份*/ /*自定义定时作业说明和实际操作,希望能实现用SQL Transaction-SQL实现*/ /*http://info.codepub.com/2008/04/info-18958.html*/ /*创建数据表*/ CREATE TABLE [dbo].[PurchaseOrderDetail] ( [PurchaseOrderID] [int] NOT NULL,--REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID), [LineNumber] [smallint] NOT NULL, [ProductID] [int] NULL ,--REFERENCES Production.Product(ProductID), [UnitPrice] [money] NULL, [OrderQty] [smallint] NULL, [ReceivedQty] [float] NULL, [RejectedQty] [float] NULL, [DueDate] [datetime] NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT (newid()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT (getdate()), [LineTotal] AS (([UnitPrice]*[OrderQty])), [StockedQty] AS (([ReceivedQty]-[RejectedQty])), CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber] PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber]) --WITH (IGNORE_DUP_KEY = OFF) ) ON [PRIMARY]; ALTER TABLE PurchaseOrderDetail ADD column_b VARCHAR(20) NULL --新增列 ALTER TABLE PurchaseOrderDetail ALTER COLUMN column_b decimal(18,6) --修改列 [CONSTRAINT PurchaseOrderID UNIQUE] --可以加入其他约束条件 ALTER TABLE PurchaseOrderDetail DROP column_b --删除列 DROP TABLE PurchaseOrderDetail --删除数据表 /*End 创建数据表*/ /*创建触发器*/ USE AdventureWorks; GO IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL DROP TRIGGER Sales.reminder2; GO CREATE TRIGGER reminder2 ON Sales.Customer AFTER INSERT, UPDATE, DELETE AS RAISERROR ('Notify Customer Relations', 16, 10); GO --删除触发器 USE AdventureWorks; GO IF OBJECT_ID ('employee_insupd', 'TR') IS NOT NULL DROP TRIGGER employee_insupd; GO /*End 创建触发器*/ /*创建视图*/ USE AdventureWorks ; GO IF OBJECT_ID ('hiredate_view', 'V') IS NOT NULL DROP VIEW hiredate_view ; GO CREATE VIEW hiredate_view AS SELECT c.FirstName, c.LastName, e.EmployeeID, e.HireDate FROM HumanResources.Employee e JOIN Person.Contact c on e.ContactID = c.ContactID ; GO --删除视图 USE AdventureWorks ; GO IF OBJECT_ID ('dbo.Reorder', 'V') IS NOT NULL DROP VIEW dbo.Reorder ; GO /*End创建视图*/ /*创建存储过程*/ --1简单创建存储过程 USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetEmployees; GO CREATE PROCEDURE HumanResources.uspGetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName; GO --2此存储过程模式与所传递的参数相匹配；或者，如果未提供参数，则使用预设的默认值 USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetEmployees2; GO CREATE PROCEDURE HumanResources.uspGetEmployees2 @LastName nvarchar(50) = N'D%', @FirstName nvarchar(50) = N'%' AS SET NOCOUNT ON; SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName; GO --uspGetEmployees2 存储过程可使用多种组合执行。 EXECUTE HumanResources.uspGetEmployees2; EXECUTE HumanResources.uspGetEmployees2 N'Wi%'; EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%'; EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n'; EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen'; EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%'; --3创建带OutPut参数的存储过程 USE AdventureWorks; GO IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL DROP PROCEDURE Production.uspGetList; GO CREATE PROCEDURE Production.uspGetList @Product varchar(40) , @MaxPrice money , @ComparePrice money OUTPUT , @ListPrice money OUT AS SET NOCOUNT ON; SELECT p.[Name] AS Product, p.ListPrice AS 'List Price' FROM Production.Product AS p JOIN Production.ProductSubcategory AS s ON p.ProductSubcategoryID = s.ProductSubcategoryID