Sql2008--7

--数据定义语言DDL
--CREATE DATABASE DATABASE_NAME   创建一个默认数据库的语法
--CREATE DATABASE DATABASE_NAME ON <FILESPEC> FOR{};附加一个数据库
--CREATE DATABASE DATABASE_SNAPSHOT_NAME ON (NAME=,FILENAME) AS SNAPHOT OF SOURCE_DATABASE_NAME;--创建数据库快照
--验证数据库的文件和大小

select name,size as [Size in mbs]
from sys.master_files
where name = N'school';
go
--数据库修改语句
--ALTER DATABASE DATABASE_NAME{
--|MODIFY NAME = NEW_DATABASE_NAME
--|COLLATE COLLATION_NAME
--<FILE_AND_FILEGROUP_OPTIONS>};

USE master
GO
ALTER DATABASE TestWorks
 modify Name = AdventureWorks;
 go
--删除数据库,系统数据库不能删除
--DROP DATABASE {DATABASE_NAME|DATABASE_SNAPSHOT_NAME};

--表
--CREATE TABLE 创建表语句
CREATE TABLE [dbo].[PurchaseOrderDetail]
(
 [PurchaseOrderID] [int] not null
   REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
   primary key clustered(PurchaseOrderID)
   with(IGNORE_DUP_KEY = off)
)
on [primary];
--ALTER TABLE 修改表语句
--ALTER TABLE TESTDB ADD COLUMN_B VARHAR(20) NULL 添加一个允许空值的列
--ALTER TABLE TESTDB DROP COLUMN COLUMN_B 删除一列
--DROP TABLE 表删除语句

--索引 CREATE INDEX
USE AdventureWorks
GO --首先判断是否存在该索引，存在，则先删除
IF EXISTS(SELECT NAME FROM sys.indexes WHERE NAME = N'IX_ProductVendor_VendorID')
drop index IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
go--执行创建索引语句
create index IX_ProductVendor_VendorID
 on Purchasing.ProductVendor (VendorID);
 go

--创建非聚集索引
use AdventureWorks
go --首先判断是否存在该索引，存在，则先删除
if EXISTS (SELECT NAME FROM sys.indexes WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
drop index IX_SalesPerson_SalesQuota_SalesYTD on Sales.SalesPerson;
go --执行创建索引语句
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
 ON Sales.SalesPerson (SalesQuota,SalesYTD);
 go
 
--索引修改语句 alter index
--在表中重新生成单个索引
use AdventureWorks
go
alter index PK_Employee_EmployeeID on HumanResources.Employee
REBUILD;  --使用REUBILD选项
go

--禁用对Employee表的非聚集索引
use AdventureWorks
go
alter index IX_Employee_ManagerID
 on HumanResources.Employee DISABLE;
 GO
--索引删除语句
--DROP INDEX 用来删除一个或多个索引，其不适用于通过定义PRIMARY KEY 或 UNIQUE约束创建的索引。
--1、删除ProductVendor表上的索引IX_ProductVendor_VendorID
use AdventureWorks;
go
drop index IX_ProductVendor_VendorID
 on Purchasing.ProductVendor;
go
--2、删除单个事务中的两个索引。
use AdventureWorks;
go
drop index IX_PurchaseOrderHeader_EmployeeID on Purchasing.PurchaseOrderHeader,
     IX_VendorAddress_AddressID on Purchasing.VendorAddress;
go

--视图：就是一个虚拟的表，用CREATE VIEW 语句就可以创建视图
--CREATE VIEW 必须是查询批处理中的第一条语句
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
--视图修改语句   alter view
use AdventureWorks;
go --首先，创建一个视图
CREATE VIEW HumanResources.EmployeeHireDate
 as select c.FirstName,c.LastName,e.HireDate
 from HumanResources.Employee as e join Person.Contact as c
 on e.ContactID = c.ContactID;
go
--如果未使用alter view ,而是删除后再重新创建视图，则必须重新输入用来处理视图权限的GRANT语句和任何其他语句

ALTER VIEW HumanResources.EmployeeHireDate   --重新修改视图
as select C.FirstName,C.LastName,e.HireDate
 from HumanResources.Employee as e join Person.Contact as c
 on e.ContactID = c.ContactID
 where HireDate < CONVERT(DATETIME,'19980101',101);
GO

--视图删除语句   DROP VIEW
use AdventureWorks;
go  --首先判断Reorder视图存在，将其删除
if OBJECT_ID('dbo.Reorder','V') is not null
 drop view dbo.Reorder;
go

--存储过程:可以看做是一个保存在服务器端的文件，其由多条Transact_SQL语句组成，而且允许接收输入和输出参数，并可以运行一个或多个运算
--创建存储过程的语句 CREATE PROCEDURE
use AdventureWorks;
go --开始创建存储过程语句
create procedure uspNResults
   as select COUNT(ContactID) from Person.Contact
   select COUNT(CustomerID) from Sales.Customer;
  go
--存储过程修改语句   ALTER PROCEDURE
--1、创建存储过程
use AdventureWorks;
go --首先判断该存储过程是否已经存在，存在则删除掉
if OBJECT_ID('Purchasing.uspVendor','P') is not null
 drop procedure Purchasing.uspVendor;
go --开始创建存储过程语句

CREATE PROCEDURE Purchasing.uspVendor
 with execute as caller
 as select v.Name as Vendor,p.Name as 'Product Name',v.CreditRating as 'Credit Rating',
 v.ActiveFlag as Availability
 from Purchasing.Vendor v
 inner join Purchasing.ProductVendor pv on v.VendorID = pv.VendorID
 inner join Production.Product p ON pv.ProductID = p.ProductID
 order by v.Name asc;
go
--2、修改存储过程
ALTER PROCEDURE Purchasing.uspVendor
 @Product varchar(25)
 as select LEFT(v.Name,25) as Vendor,LEFT(p.Name,25) as 'Product name',
  'Credit rating'= case v.CreditRating --使用CASE语句
  when 1 then 'Superior'
  when 2 then 'Excellent'
  when 3 then 'Above average'
  when 4 then 'Average'
  when 5 then 'Below average'
  else 'No rating'
     end
     ,Availability = Case v.ActiveFlag   --使用CASE语句
     when 1 then 'Yes'
     else 'No'
     end
     from Purchasing.Vendor as v
     inner join Purchasing.ProductVendor as pv on v.VendorID = pv.VendorID
     inner join Production.Product as p on pv.ProductID = p.ProductID
     where p.Name like @Product
     order by v.Name asc;
go
exec Purchasing.uspVendor N'LL Crankarm';--执行该存储过程
go         

--删除存储过程 drop procedure
drop procedure dbo.uspMyPro;
go
--触发器
--1、创建DML触发器
--2、创建DLL触发器
--3、创建LOGON触发器
--1、创建包含提醒消息的DML触发器
--当视图修改Customer表的数据时，DML触发器会向客户端显示一条消息
use AdventureWorks;
go --首先判断该触发器是否已经存在，存在则删除掉
if OBJECT_ID('Sales.reminder1','R') is not null
 drop trigger Sales.reminder1;
go --开始创建触发器语句
create trigger reminder1
 on Sales.Customer
 AFTER INSERT ,UPDATE
 AS RAISERROR ('注意客户表之间的关系',16,10);
go
--2、创建具有数据库范围的DDL触发器
--创建使用DDL触发器防止从数据库中删除任何同义词
use AdventureWorks;
go --首先判断该触发器是否已经存在，存在则删除掉
if exists (select * from sys.triggers
 where parent_class = 0 and name = 'safetytrig')
drop trigger safetytrig on database;
go --开始创建触发器
create trigger safetytrig
on database
for drop_synonym
as
 raiserror ('You must disable trigger "safetytrig" to drop synonyms!',10,1 )
 rollback;
 go --开始删除触发器语句
 drop trigger safetytrig
 on database;
 go
--3、查看导致触发器触发的事件
--查询sys.triggers 和 sys.trigger_enents 目录视图，以确定是哪个transact_sql语言事件导致触发了触发器
--safetytrig。其中的safeytrig是在上一个示例中创建的。
select TE.*
FROM sys.trigger_events as TE
join sys.trigger_events as T
 on T.object_id = TE.object_id
where T.parent_class = 0
and T.name ='safetytrig'
go
--触发器修改语句 ALTER TRIGGER
USE AdventureWorks;
GO --如果存在该触发器，则将其删除
if OBJECT_ID(N'Sales.bonus_reminder',N'TR') is not null
 drop trigger Sales.bonus_reminder
go --创建触发器
create trigger Sales.bonus_reminder
 ON Sales.SalesPersonQuotaHistory
 with ENCRYPTION
 AFTER INSERT,UPDATE
 AS RAISERROR ('Notify Compensation',16,10) ;
go
--修改触发器
use AdventureWorks;
go
ALTER TRIGGER Sales.bonus_reminder
 ON Sales.SalesPersonQuotaHistory
 AFTER INSERT
 AS RAISERROR ('Notify Compensation',16,10);
go
--删除触发器语句  DROP TRIGGER
--删除DML触发器
drop trigger schema_name.trigger_name[,.....][;]
--删除DDL触发器
DROP TRIGGER TRIGGER_NAME[,.....]
 ON {DATABASE | ALL SERVER}
 [;]
--删除LOGON触发器
drop trigger trigger_name[,....] 
 ON ALL SERVER
--1、删除DML触发器employee_insupd
use AdventureWorks;
go
if OBJECT_ID('employee_insupd','TR') is not null
 drop trigger employee_insupd;
 go
--删除DDL触发器 safeytrig
use AdventureWorks;
go
if exists (select * from sys.triggers where parent_class =0 and name ='safetytrig') 
drop trigger safetytrig
 on database;
go