T-SQL 執行個體

--

USE AdventureWorks

GO

SELECT * FROM sys.types

use AdventureWorks

go

select * from sys.schemas where principal_id = 1

SELECT t.name  FROM sys.tables t ,sys.schemas m WHERE t.schema_id = m.schema_id and m.name = 'HumanResources'

USE AdventureWorks;

DECLARE @dateTemp DATETIME;

SET @dateTemp = GETDATE();

EXEC [AdventureWorks].[dbo].[uspGetBillOfMaterials] 811, @dateTemp;

EXEC dbo.uspGetEmployeeManagers 1;

EXEC dbo.uspGetManagerEmployees 185;

SELECT CustomerID AS Id,

('CN' + dbo.ufnLeadingZeros(CustomerID)) AS NewAccountNumber

FROM Sales.Customer

ORDER BY CustomerID;

SELECT ContactID, FirstName, LastName, JobTitle, ContactType

FROM dbo.ufnGetContactInformation(100);

SELECT ProductID AS N'産品編号', ListPrice AS N'标價',

    dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS N'批發價',

    StartDate AS N'開始日期',EndDate AS N'結束日期'

FROM Production.ProductListPriceHistory

WHERE ProductID = 707

ORDER BY ProductID, StartDate;

SELECT C.*

FROM Person.Contact AS C

    JOIN Sales.Individual AS I

        ON C.ContactID = I.ContactID

    JOIN Sales.Customer AS Cu

        ON I.CustomerID = Cu.CustomerID

WHERE Cu.CustomerType = 'I'  AND LastName='Edwards'

ORDER BY LastName, FirstName ;

SELECT Name,C.AccountNumber

FROM Sales.Store AS S

    JOIN Sales.Customer AS C

        ON S.CustomerID = C.CustomerID

WHERE C.CustomerType = N'S'

ORDER BY Name ;

SELECT S.Name AS N'商店', C.LastName AS '人名', CT.Name AS N'職位'

FROM (Person.Contact AS C

    JOIN Sales.StoreContact AS SC ON C.ContactID = SC.ContactID

    JOIN Person.ContactType AS CT ON

        CT.ContactTypeID = SC.ContactTypeID

    JOIN Sales.Store AS S ON S.CustomerID = SC.CustomerID)

WHERE (S.Name='Catalog Store')

ORDER BY S.Name ;

SELECT S.CustomerID, S.Name AS Store, A.City, SP.Name AS State, CR.Name

    AS CountryRegion

    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID

    JOIN Person.Address AS A ON A.AddressID = CA.AddressID

    JOIN Person.StateProvince SP ON

        SP.StateProvinceID = A.StateProvinceID

    JOIN Person.CountryRegion CR ON

        CR.CountryRegionCode = SP.CountryRegionCode

WHERE SP.Name = 'Texas' and CR.Name='United States'

ORDER BY S.CustomerID ;

SELECT Name, SalesOrderNumber, OrderDate, TotalDue

    JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID

WHERE Name = 'A Bike Store'

ORDER BY Name, OrderDate ;

SELECT PC.Name AS N'産品分類', PSC.Name AS N'子類型', PM.Name AS N'型号', P.Name AS N'産品名'

FROM Production.Product AS P

    FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID

    FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID

JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID

WHERE PC.Name = 'Bikes'

ORDER BY PC.Name, PSC.Name ;

SELECT PM.ProductModelID, PM.Name AS [Product Model], Description, PL.CultureID, CL.Name AS Language

FROM Production.ProductModel AS PM

    JOIN Production.ProductModelProductDescriptionCulture AS PL

        ON PM.ProductModelID = PL.ProductModelID

    JOIN Production.Culture AS CL ON CL.CultureID = PL.CultureID

    JOIN Production.ProductDescription AS PD

        ON PD.ProductDescriptionID = PL.ProductDescriptionID

WHERE CL.Name = 'Chinese'

ORDER BY PM.ProductModelID ;

SELECT V.Name AS N'供應商名', A.AddressLine1 as N'位址', A.City as N'城市', SP.Name AS N'州'

FROM Purchasing.Vendor AS V

    JOIN Purchasing.VendorAddress AS VA ON VA.VendorID = V.VendorID

    JOIN Person.Address AS A on A.AddressID = VA.AddressID

    JOIN Person.StateProvince AS SP on SP.StateProvinceID =         A.StateProvinceID

    JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode

WHERE SP.Name = 'California'

GROUP BY V.VendorID, V.Name, A.AddressLine1, A.City, SP.Name, CR.Name

ORDER BY V.VendorID;

SELECT P.ProductNumber as N'産品号',P.Name as N'産品名', V.Name as N'供應商名'

    JOIN Purchasing.ProductVendor AS PV ON P.ProductID = PV.ProductID

    JOIN Purchasing.Vendor AS V ON V.VendorID = PV.VendorID

WHERE V.Name ='Green Lake Bike Company'

ORDER BY P.Name ;

SELECT P.Name AS N'産品', L.Name AS N'庫存位置',

    SUM(PI.Quantity)AS N'存量'

    JOIN Production.ProductInventory AS PI ON P.ProductID = PI.ProductID

    JOIN Production.Location AS L ON PI.LocationID = L.LocationID

WHERE P.Name = 'Road-650 Black, 60'

GROUP BY P.Name, L.Name

SELECT WorkOrderID As N'工作号',

P.Name AS N'産品名',

OrderQty AS N'數量',

DueDate AS N'日期'

FROM Production.WorkOrder W

    JOIN Production.Product P ON W.ProductID = P.ProductID

WHERE P.ProductSubcategoryID IN (1, 2, 3)

AND P.Name = 'Road-550-W Yellow, 44'

ORDER BY P.Name, DueDate ;

CREATE TABLE Employees(

 EmployeeID  int  NOT  NULL,

 Name  nvarchar(50)   NOT NULL,

 Title  nvarchar(30),

 BirthDate  datetime,

 HireDate  datetime,

 Address  nvarchar(60),

 City  nvarchar(15),

 Region  nvarchar(15),

 PostalCode  nvarchar(10),

 Country  nvarchar(15),

 HomePhone  nvarchar(24),

 Photo  p_w_picpath,

 Memo  nvarchar(50),

 CONSTRAINT PK_Employees PRIMARY KEY CLUSTERED

 (

 EmployeeID ASC

 )

)

IF OBJECT_ID ('dbo.table_test_6_1', 'U') IS NOT NULL    --1

   DROP TABLE table_test_6_1

CREATE TABLE table_test_6_1

(

 id_num int IDENTITY(1,1),                              --2

 fname varchar (20),

 minit char(1),

 lname varchar(30)

INSERT table_test_6_1  (fname, minit, lname) VALUES  ('Karin', 'F', 'Josephs')         --3

INSERT table_test_6_1  (fname, minit, lname) VALUES  ('Pirkko', 'O', 'Koskitalo')

--1.查找并删除已經存在的資料表

IF OBJECT_ID ('dbo.table_test_6_2', 'U') IS NOT NULL

   DROP TABLE table_test_6_2

--2.建立資料表，并插入資料

CREATE TABLE table_test_6_2 (id_num int IDENTITY(1,1), company_name sysname)

INSERT table_test_6_2(company_name) VALUES ('Test 1')

INSERT table_test_6_2(company_name) VALUES ('Test 2')

INSERT table_test_6_2(company_name) VALUES ('Test 3')

INSERT table_test_6_2(company_name) VALUES ('Test 4')

--3.檢視table_test_6_2資料表，将看到4條資料

SELECT * FROM table_test_6_2

--4.删除中斷的号

DELETE FROM table_test_6_2 WHERE id_num = 3

--5.檢視table_test_6_2資料表，将看到3條資料，辨別列記錄分别為1，2，4

--6.查找最小辨別号,結果為3

-- SET IDENTITY_INSERT ON and use in table_test_6_2 table.

SET IDENTITY_INSERT table_test_6_2 ON

DECLARE @minidentval smallint

DECLARE @nextidentval smallint

SELECT @minidentval = MIN($IDENTITY) FROM table_test_6_2

 IF @minidentval = IDENT_SEED('table_test_6_2')

    SELECT @nextidentval =    --7.指派語句

    MIN($IDENTITY) + IDENT_INCR('table_test_6_2') FROM table_test_6_2 t1

       WHERE  ($IDENTITY  BETWEEN  IDENT_SEED('table_test_6_2') AND 32766)

       AND   ( NOT EXISTS (SELECT * FROM table_test_6_2 t2 WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('table_test_6_2')

                         ))

 ELSE

      SELECT @nextidentval = IDENT_SEED('table_test_6_2')

PRINT  @nextidentval --輸入查找到的值

SET IDENTITY_INSERT table_test_6_2 OFF

IF OBJECT_ID ('dbo.table_test_6_3', 'U') IS NOT NULL

   DROP TABLE table_test_6_3

CREATE TABLE table_test_6_3

 id_num int IDENTITY(1,1), 

 fname varchar (20)  NOT NULL,  --1.設定為非空列

INSERT table_test_6_3  (fname, minit, lname) VALUES  ('K', 'F', 'Jose') 

--2.以下不能執行成功

INSERT table_test_6_3  (minit, lname) VALUES  ('F', 'Jose')   

--1.建立table_test_6_4表

CREATE TABLE table_test_6_4

   (keycol      smallint,

   process_id   smallint DEFAULT @@SPID,   --定義預設值

   date_ins   datetime DEFAULT getdate(),   --定義預設值

   mathcol      smallint DEFAULT 10 * 2,   --定義預設值

   char1      char(3),

   char2      char(3) DEFAULT 'xyz') --定義預設值

--2.定義

/* For illustration only, use DEFAULT definitions instead.*/

CREATE DEFAULT abc_const AS 'abc';

sp_bindefault abc_const, 'table_test_6_4.char1';

--3.插入資料

INSERT INTO table_test_6_4(keycol) VALUES (1);

--4.檢視資料

SELECT * FROM table_test_6_4;

USE master;

--1.建立資料庫

CREATE DATABASE Table_test_6_5

ON PRIMARY

  ( NAME='Table_test_6_5_Primary',

    FILENAME=

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5_Prm.mdf',

    SIZE=4MB, MAXSIZE=10MB, FILEGROWTH=1MB),

FILEGROUP Table_test_6_5_FG1  --次要資料檔案

  ( NAME = 'Table_test_6_5_FG1_Dat1',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5_FG1_1.ndf',

    SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB),

  ( NAME = 'Table_test_6_5_FG1_Dat2',

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5_FG1_2.ndf',

    SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB)

LOG ON

  ( NAME='Table_test_6_5_log',

       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5.ldf',

    SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB);

--2.修改檔案組為預設

ALTER DATABASE Table_test_6_5

  MODIFY FILEGROUP Table_test_6_5_FG1 DEFAULT;

--3.在使用者定義的檔案組中建立資料表

USE Table_test_6_5;

CREATE TABLE MyTable

  ( cola int PRIMARY KEY,

    colb char(8) )

ON Table_test_6_5_FG1;--4指定檔案組

--建立資料表

CREATE TABLE Table_test_6_6

   id       int      PRIMARY KEY,

   cname    char(50),

   address  char(50),

   memo     char(50),

   CONSTRAINT id CHECK (id BETWEEN 0 and 10000 )

--2.執行插入資料驗證限制

INSERT INTO Table_test_6_6 values(100000,'NAME','ADDRESS','MEMO');

--在CREATE TABLE過程中設計字段的排序規則

CREATE TABLE Table_test_6_7

  (col1   int PRIMARY KEY,

  col2    varchar(10)  COLLATE  French_CI_AS NOT NULL

  )