--
--
USE AdventureWorks
GO
SELECT * FROM sys.types
GO
--

--
use AdventureWorks
go
select * from sys.schemas where principal_id = 1
go
 
 

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

--
USE AdventureWorks;
GO
DECLARE @dateTemp DATETIME;
SET @dateTemp = GETDATE();
EXEC [AdventureWorks].[dbo].[uspGetBillOfMaterials] 811, @dateTemp;
GO
--

--
USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 1;
GO
--

--
USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 185;
GO
--

--
USE AdventureWorks;
GO
SELECT CustomerID AS Id,
('CN' + dbo.ufnLeadingZeros(CustomerID)) AS NewAccountNumber
FROM Sales.Customer
ORDER BY CustomerID;
GO
--

--
USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(100);
GO
--

--
USE AdventureWorks;
GO
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;
GO
 
 
--

--
USE AdventureWorks;
GO
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 ;
GO
--

--
USE AdventureWorks;
GO
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 ;
GO
--

--
USE AdventureWorks;
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 ;
GO
--

--
USE AdventureWorks;
GO
SELECT S.CustomerID, S.Name AS Store, A.City, SP.Name AS State, CR.Name
    AS CountryRegion
FROM Sales.Store AS S
    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 ;
GO
--

--
USE AdventureWorks;
GO
SELECT Name, SalesOrderNumber, OrderDate, TotalDue
FROM Sales.Store AS S
    JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID
WHERE Name = 'A Bike Store'
ORDER BY Name, OrderDate ;
GO
--

--
USE AdventureWorks;
GO
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 ;
GO
--

--
USE AdventureWorks;
GO
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 ;
GO
--

--
USE AdventureWorks;
GO
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;
GO
--

--
USE AdventureWorks;
GO
SELECT P.ProductNumber as N'产品号',P.Name as N'产品名', V.Name as N'供应商名'
FROM Production.Product AS P
    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 ;
GO
--
--
USE AdventureWorks;
GO
SELECT P.Name AS N'产品', L.Name AS N'库存位置',
    SUM(PI.Quantity)AS N'存量'
FROM Production.Product AS P
    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
ORDER BY P.Name ;
GO
--

--
USE AdventureWorks;
GO
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 ;
GO
 
--

--
USE AdventureWorks
GO
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
 )
)
--

--
USE AdventureWorks
GO
IF OBJECT_ID ('dbo.table_test_6_1', 'U') IS NOT NULL    --1
   DROP TABLE table_test_6_1
GO
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')
GO
--

--
--1.查找并删除已经存在的数据表
IF OBJECT_ID ('dbo.table_test_6_2', 'U') IS NOT NULL
   DROP TABLE table_test_6_2
GO
--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')
GO
--3.查看table_test_6_2数据表，将看到4条数据
SELECT * FROM table_test_6_2
GO
--4.删除中断的号
DELETE FROM table_test_6_2 WHERE id_num = 3
--5.查看table_test_6_2数据表，将看到3条数据，标识列记录分别为1，2，4
SELECT * FROM table_test_6_2
GO
--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
--

--
USE AdventureWorks
IF OBJECT_ID ('dbo.table_test_6_3', 'U') IS NOT NULL
   DROP TABLE table_test_6_3
GO
CREATE TABLE table_test_6_3
(
 id_num int IDENTITY(1,1), 
 fname varchar (20)  NOT NULL,  --1.设置为非空列
 minit char(1),
 lname varchar(30)
)
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表
USE AdventureWorks;
GO
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') --定义默认值
GO
--2.定义
/* For illustration only, use DEFAULT definitions instead.*/
CREATE DEFAULT abc_const AS 'abc';
GO
sp_bindefault abc_const, 'table_test_6_4.char1';
GO
--3.插入数据
INSERT INTO table_test_6_4(keycol) VALUES (1);
GO
--4.查看数据
SELECT * FROM table_test_6_4;
GO
--

--
USE master;
GO
--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',
    FILENAME =
       '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',
    FILENAME =
       'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\data\Table_test_6_5.ldf',
    SIZE=1MB, MAXSIZE=10MB, FILEGROWTH=1MB);
GO
--2.修改文件组为默认
ALTER DATABASE Table_test_6_5
  MODIFY FILEGROUP Table_test_6_5_FG1 DEFAULT;
GO
--3.在用户定义的文件组中创建数据表
USE Table_test_6_5;
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON Table_test_6_5_FG1;--4指定文件组
GO
 
--

--
--创建数据表
USE AdventureWorks;
GO
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');
GO
--

--
USE AdventureWorks
GO
--在CREATE TABLE过程中设计字段的排序规则
CREATE TABLE Table_test_6_7
  (col1   int PRIMARY KEY,
  col2    varchar(10)  COLLATE  French_CI_AS NOT NULL
  )
GO