Sql2005应用

来源：互联网发布：网络歌手格子兮照片编辑：程序博客网时间：2024/05/03 18:59

–判断存储过程是否存在，sysobjects对象中查询，条件是p（produce），和存储过程的名字
IF EXISTS (SELECT * FROM sysobjects WHERE type = ‘P’ AND name =‘uspReadEmpMgrs’)–如果存在就删除 BEGIN –带单引号 DROP Procedure ‘uspReadEmpMgrs’ ENDGOCREATE Procedure ‘uspReadEmpMgrs’ASBEGIN DECLARE @ThisEmp int –变量类型，放在变量名字的后面 DECLARE EmpCursor CURSOR FOR –设置游标，查查资料 SELECT EmployeeID FROM AdventureWorks.HumanResources.Employee OPEN EmpCursor WHILE @@FETCH_STATUS = 0 –看看还有没有其他类似的系统变量 BEGIN FETCH NEXT FROM EmpCursor INTO @ThisEmp PRINT ‘EmployeeID:’ + RTRIM(CAST(@ThisEmp AS VARCHAR(10))) EXEC uspGetEmployeeManagers @ThisEmp END CLOSE EmpCursor DEALLOCATE EmpCursor END;

–默认设置的情况下创建数据库
CREATE DATABASE MyNewDatabase

–指定新创建的数据库：名字，文件名，大小，最大容量，增加速度
CREATE DATABASE MyNewDatabaseON PRIMARY (Name =’MyDB_Data’, FileName= ‘C:/DBData/MyDB_Data.Mdf’, Size=100MB, MaxSize=200MB, FILEGROWTH=10%)

–设置日志文件
LOG ON (Name = ‘MyDB_Log’, FileName= ‘C:/DBData/MyDB_Log.Ldf’, Size=30MB, MaxSize=50MB, FILEGROWTH=10%)CREATE DATABASE MyDBSnapshotON (NAME = MyDatabase_data, FILENAME = ‘C:/temp/MyDatabase_data.ss’)

-数据库快照
AS SNAPSHOT OF MyNewDatabase

–创建表
CREATE TABLE Sales.Warehouse (HouseID INT PRIMARY KEY, HouseName Char(50))

–创建临时表
CREATE TABLE #tempWarehouse (HouseCode Char(5) PRIMARY KEY, HouseID INT)

–创建程序集
CREATE ASSEMBLY EmailAddress FROM ‘C:/temp/EmailAddress.dll’ —如果程序集不存在，或者再加入的时候系统会检查程序集的正确性吗？？？CREATE TYPE EMAILADDRESS

–程序集的名称.[ 命名空间.类名 ]
EXTERNAL NAME EmailAddress.[EmailNameSpace.EmailClass]
—可以查查索引的类型
–非群集索引，当Create Index语句中没有指定关键字时默认使用nonclustered模式

–索引中包含的列，我们可能经常会发现要查询表中的非键列，
–SQL Server 2005允许在非群集索引中包含非键列

–群集索引，SQL Server只允许你对每个表创建一个群集索引

–UNIQUE索引，
–索引视图
–XML索引

———————–索引的名字 ——————————在该列上进行索引
CREATE INDEX IdxTerritoryID ON Sales.SalesPerson (TerritoryID)

———————–修饰索引的
CREATE NONCLUSTERED INDEX IdxTerritoryID_Date
    ON Sales.SalesPerson (TerritoryID)
    INCLUDE (ModifiedDate)
CREATE CLUSTERED INDEX IdxPersonTerr
    ON Sales.SalesPerson (SalesPersonID, TerritoryID)      —–在两列上进行索引

–你可以查询数据类型为xml的列内容中的xml部分，分为：主索引和副索引
–主索引覆盖列中的所有元素
–副索引覆盖到路径、值和属性
CREATE PRIMARY XML INDEX IdxXmlData ON Sales.SalesPerson(xml_Data)
————————-OrderQty为列名，或者别名数据类型，
–下面的语句限制了，指定了该列的默认值
CREATE DEFAULT OrderQty AS ‘100′

——————————————–OrderQty为列名，或者别名数据类型
–指定该列所能接受的值
CREATE RULE OrderQty
AS
@list IN (’100′, ‘250′, ‘500′)

———————-创建试图
–保存关于视图的几个系统表
–sys.view
–sys.columns
–sys.sql_modules
–sys.sql_dependencies
CREATE VIEW StorePersonnel
AS SELECT SalesPersonID, Name FROM AdventureWorks.Sales.Store
WHERE SalesPersonID > 250

————————-同名对象——————————————————-
–同名对象是可以为您的对象创建的别名，
–同名对象可以帮助简化远程对象，或者另一个数据库或者模式中的对象的命名
–同名对象允许你调换底层的对象，而不会影响引用这些对象的代码
CREATE SYNONYM RetailLocation FOR AdventureWorks.Sales.Store
CREATE PROCEDURE Sales.usp_GetSalesPerson
AS
–as后面是存储过程的主程序
SELECT SalesPersonID, Name FROM Sales.Store
CREATE PROCEDURE Sales.usp_GetOneStore
(@InID int,
@OutName nvarchar(50) OUTPUT)
AS
Set @OutName =
    (SELECT Name
     FROM Sales.Store
     WHERE CustomerID = @inID)
CREATE ASSEMBLY usp_GetSalesPerson
    FROM ‘C:/temp/usp_GetSalesPerson.dll’
CREATE PROCEDURE usp_GetSalesPerson
    EXTERNAL NAME usp_GetSalesPerson.
      [usp_GetSalesPerson.StoredProcedures].usp_GetSalesPerson
–创建函数
–创建函数时，需要：1、指定函数的名称。2、指定Returns子句
——-自定义函数，根据返回值来区分的话属于标量函数。返回的是普通类型
CREATE FUNCTION ufnGetHouseName
    ( @House int )
    RETURNS char(50)            ———返回值—-函数和存储过程的区别
    AS
–as后面是主函数的内容
    BEGIN
    RETURN
    (SELECT HouseName FROM Sales.Warehouse WHERE HouseID > @House)
    END

——–自定义函数，根据返回值来区分的话属于表值函数，返回一个Table类型
CREATE FUNCTION Sales.fn_PersonPerStore (@PersonID int)
RETURNS TABLE —-是Returns，不是Return
AS
–as后面是主函数的内容
RETURN
(SELECT * FROM Sales.Store WHERE SalesPersonID = @PersonID)
CREATE ASSEMBLY ufn_GetDataAsString
FROM ‘C:/temp/ufn_GetDataAsString.dll’

CREATE FUNCTION ufn_GetDateAsString()
RETURNS nvarchar(256)
EXTERNAL NAME
ufn_GetDateAsString.UserDefinedFunctions.ufn_GetDateAsString
———-传统的触发器，Insert，Alter，Drop Table 的时候进行触发的
———-Insert，alter，drop table
CREATE TRIGGER NoTableUpdate
ON DATABASE FOR DROP_TABLE, ALTER_TABLE ——FOR后面有几种情况？？？？？？？？？？？？
AS
PRINT ‘DROP TABLE and ALTER TABLE statements are not allowed’
ROLLBACK

—————————————————————————————————————————
–安全性问题
–1、创建登陆
–创建登陆，可以让用户进行入服务器，对于访问服务器中的数据库，需要创建User
–4种登陆：SQL Server登陆；Windows登陆；证书映射登陆；非对称密钥映射登陆
CREATE LOGIN TecaGuest WITH PASSWORD = ‘iMsoiLwR4E’ MUST_CHANGE ——登陆后必须要进行密码的修改

—————————–凭证与登陆相关，使用Windows用户身份Teca01Guest，创建一个名为AlternateGuest的凭证
CREATE CREDENTIAL AlternateGuest WITH IDENTITY = ‘Teca01Guest’
—————————–使用上面的凭证创建一个登陆
CREATE LOGIN Teca02Guest WITH PASSWORD = ‘MBSim1tl’,
CREDENTIAL = AlternateGuest

–User对象用于让用户访问到服务器上的数据库，create user可以将新的数据库用户映射到登陆，你可以限制其映射
CREATE USER TecaRestrictedUser WITHOUT LOGIN —不允许映射任何登陆，不允许连接到其它数据库

–角色的创建
CREATE ROLE Payroll

–模式的创建
CREATE SCHEMA MonthSales

—————————-主密钥
–每个数据库都有一个主密钥
–它是数据库中所有密钥，证书和数据的根加密对象，

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘l1y47l%9dwvyb2ayup9#$Nn’

—————————-非对称密钥，算法是RAS，位数有512，1024，2048
CREATE ASYMMETRIC KEY AsymKeySales WITH ALGORITHM = RSA_2048

—————————-证书的创建

CREATE CERTIFICATE TecaCert09
WITH SUBJECT = ‘TCert08 certificate in master database’,
EXPIRY_DATE = ‘01/31/2008′

CREATE LOGIN TCert08 FROM CERTIFICATE TecaCert08;

–对称密钥
CREATE SYMMETRIC KEY SymKeySales WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD ‘cNIu284ry$bd%JDqT’
—-FULLTEXT全文本目录
—-CREATE FULLTEXT CATALOG语句文数据库创建一个全文本目录，他被限制为120个字符
—-并且不能在主数据库，模型数据库或者tempdb数据库中创建
CREATE FULLTEXT CATALOG StoreSearch

select Name
from Sale.Store
where contains(Name,’”*cycls*”‘)

————————————————————————————————————-
–基本检索操作Select
use adventureworks
go
SELECT * FROM HumanResources.Department
SELECT DepartmentID, Name FROM HumanResources.Department
Where DepartmentID = 7

—————设置列的别名
SELECT DepartmentID As ID, Name As Title FROM HumanResources.Department
Where DepartmentID BETWEEN 5 AND 10

—————对查询的结果，在按照Name列来排序
SELECT DepartmentID, Name
FROM HumanResources.Department
ORDER By Name

—————这个不用Distinct，就是按照GroupName列的内容不同进行分组，不会出现重复
SELECT GroupName, Count(*) As Departments
FROM HumanResources.Department
GROUP BY GroupName
SELECT Distinct GroupName
FROM HumanResources.Department

—————-#name，这种命名方式是建立一个临时的表
SELECT * INTO #TempDepartment
FROM HumanResources.Department
Where GroupName LIKE ‘%Ex%’

SELECT * FROM #TempDepartment

—————–声明一个变量的时候，变量的名字前面加@，在前面，类型在后面
DECLARE @MyTop INT
SET @MyTop = 5
SELECT TOP (@MyTop) DepartmentID, Name FROM HumanResources.Department

—检索的数据存在于两个表以上的情况
—使用的方法：
—
—JOIN的种类

SELECT e.EmployeeID, c.FirstName, c.LastName, e.Title,
     d.Name AS Department
FROM HumanResources.Employee e
    INNER JOIN Person.Contact c
    ON c.ContactID = e.ContactID
    INNER JOIN HumanResources.EmployeeDepartmentHistory h
    ON e.EmployeeID = h.EmployeeID
    INNER JOIN HumanResources.Department d
    ON h.DepartmentID = d.DepartmentID
Where h.EndDate IS NOT Null
——————-使用UNION组合相关数据
——————-为了实现UNION，要组合的数据必须满足2个条件：
——————-1、列的数量和顺序必须相同。
——————-2、类型要一样

SELECT *
INTO dbo.FirstHalfDept
FROM HumanResources.Department
WHERE DepartmentID <= 8
GO

SELECT *
INTO dbo.SecondHalfDept
FROM HumanResources.Department
WHERE DepartmentID > 8
GO

SELECT *
FROM dbo.FirstHalfDept
UNION
SELECT *
FROM dbo.SecondHalfDept
ORDER BY DepartmentID;
GO

————————-join
SELECT FirstName, LastName, e.Title
FROM Person.Contact c
Join HumanResources.Employee e
On e.ContactID = c.ContactID
WHERE EmployeeID IN
(SELECT EmployeeID FROM
   HumanResources.Employee   WHERE Title = ‘Tool Designer’)
DECLARE @ThisDept INT
DECLARE DeptCursor CURSOR FOR    —-注意一下，声明游标的格式，要紧跟一个Select语句
SELECT DepartmentID from HumanResources.Department——游标所操作的行就是Select语句定义的
OPEN DeptCursor    ————–在定义游标之后，我们使用Open语句将游标打开
WHILE @@FETCH_STATUS = 0         —遍历游标
BEGIN
PRINT ‘Processing Department: ‘ + RTRIM(CAST(@ThisDept AS VARCHAR(10)))
   FETCH NEXT FROM DeptCursor INTO @ThisDept
END
CLOSE DeptCursor
DEALLOCATE DeptCursor
USE AdventureWorks
WITH EmployeeChart(EmployeeID, ManagerID, Title)
AS
(SELECT EmployeeID, ManagerID, Title
FROM HumanResources.Employee
WHERE EmployeeID = 3
UNION ALL
SELECT L2.EmployeeID, L2. ManagerID, L2.Title
FROM HumanResources.Employee AS L2
JOIN EmployeeChart
ON L2.ManagerID = EmployeeChart.EmployeeID)
SELECT * FROM EmployeeChart
SELECT VendorID, [244] AS POCount1, [231] AS POCount2, [266] AS POCount3
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [244], [231], [266] )
) AS pvt
ORDER BY VendorID