詹姆士的筆記本

UNION

example：2007年1月份，銷售前三名的員工

SELECT TOP(3)
 e.empid , e.lastname , SUM(od.qty * od.unitprice) AS total
FROM HR.Employee e
INNER JOIN Sales.Orders o
 ON e.empid = o.empid
INNER JOIN Sales.OrderDetails od
 ON o.orderid = od.orderid
WHERE YEAR(o.orderdate) = 2007 AND MONTH(o.orderdate) = 1
GROUP BY e.empid ,e.lastname
ORDER BY total DESC;


--
SELECT * FROM 
(
 一月份銷售前三名
)o
UNION ALL 
SELECT * FROM 
(
 一月份銷售前三名
)o

Apply

程式for迴圈的概念

example：2007年1月份每位員工的最後三份訂單(依orderid)

分析：不知如何做，先找出某位empid = 1的訂單

SELECT e.empid,e.lastname ,o.orderid,o.orderdate
FROM HR.Employee e
CROSS APPLY
(
 SELECT TOP(3)
  orderid,orderdate
 FROM Sales.Orders
 WHERE YEAR(orderdate) =2007 AND MONTH(orderdate) =1
  AND empid = e.empid
 ORDER BY orderid DESC
)o

example：每一位客戶銷售前三名的商品

分析：先找其中一位客戶銷售前三名的商品

SELECT 
FROM Sales.Customer c
CROSS APPLY
(
 SELECT TOP(3) productid,p.productname
 FROM Production.Products p
 INNER JOIN Sales.OrderDetails od 
  ON p.productid = od.productid
 INNER JOIN Sales.Orders o
  ON o.orderid = od.orderid
 WHERE o.custid = c.custid
 GROUP BY p.productid,p.productname
 ORDER BY SUM(od.qty * od.unitprice DESC
)

Server.Database.Schema.Object

[伺服器].[資料庫].[結構化描述].[物件]

物件：table、function、view、stored procedure

• 4-part Naming
• 3-part Naming
• 2-part Naming
• 1-part Naming

 

-- 4 part naming
SELECT * FROM SRV1.TSQL.HR.Employees;

-- 3 part naming
SELECT * FROM TSQL.HR.Employees;

-- 2 part naming
SELECT * FROM HR.Employees;--當前資料庫

-- 1 part naming
SELECT * FROM Employees; --不建議使用，dbo.Employees，dbo預設Schema

建立Schema

• GUI -->Security --> Schema右鍵 -->Name 輸入HR，Table--> F4 -->Properties 視窗-->Name：Employee，Schema：HR
• T-SQL

USE db01;
GO

CREATE SCHEMA Sales;
GO 

CREATE TABLE Sales.Customer(
 customer_id int PRIMARY KEY,
 compayname nvarchar(20)
);

SELECT * FROM Sales.Customer;

 

SELECT ... INTO(TSQL特有)

會做的事

• 複製資料來源Table的欄位定義
• 複製資料來源中的資料
• IDENTITY(自動編號)的定義
• NOT NULL、UNIQUE束制條件

不會做的事

• KEY(PK，FK)
• CHECK
• DEFAULT
• TRIGGER
• INDEX
• 統計數字

補充

• 可以有SELECT...FROM...JOIN...WHERE...GROUP BY完整SELECT語法但ORDER BY 子句不保證資料寫入順序
• INTO的資料表名稱不能存在
• SELECT的欄位必須有名稱且不重複

SELECT empid,lastname+' '+firstname as empname
INTO dbo.EmployeeName
FROM dbo.Employee;

 

INSERT INTO ... SELECT

CREATE TABLE dbo.Product
(
 product_id int PRIMARY,
 product_name nvarchar(50),
 price decimal(10,2)
);

INSERT INTO dbo.Product
SELECT productid, productname ,unitprice
FROM TSQL.Production.Products;

 

UPDATE / DELETE  

UPDATE dbo.Employee
SET employee_name = N'Nick',
 birth_day = '1985-6-6,
 salary = 28000
WHERE employee_id = 1;

--
DELETE FROM dbo.Employee
WHERE employee_id = 2;

注意：UPDATE；DELETE沒下WHERE 條件式則全部改掉、刪除

臨時備份作法

SELECT * INTO dbo.Product_back
FROM dbo.Product;

UPDATE dbo.Product
SET product_name = N'XXX' ,price = 0; --誤洗資料

UPDATE dbo.Product
SET prdouct_name = b.product_name,
 price = b.price
--SELECT
FROM dbo.Product p
INNER JOIN dbo.Product_back b
 ON p.product_id = b.product_id;

 

MERGE

UPDATE dbo.Product
SET product_name = N'XXX',price = 0
WHERE pdoduct_id <= 20;

DELETE FROM dbo.Product
WHERE  product_id <=20;

--MERGE
MERGE INTO dbo.Product AS t --target
USING dbo.Product_back AS S --src
 ON t.product_id = s.product_id
WHEN MATCHED THEN 
 UPDATE
  SET prdouct_name = s.productn_name,
   price = s.price

WHEN NOT MATCHED BY TARGET THEN 
 INSERT (product_id, product_name ,price)
 VALUES (s.product_id, s.product_name, s.price)

WHEN NOT MATCHED BY SOURCE
 DELETE; --注意這邊一定要有分號

 

IDENTITY 

建立IDENTITY

• UI：table ->column ->property ->identity specification
• TSQL

CREATE TABLE dbo.AutoNumber2
(
 id int PRIMARY KEY
        IDENTITY(1,1),
 data nvarchar(10)
);

手動輸入IDENTITY

SET IDENTITY_INSERT dbo.AutoNumber2 ON
INSERT INTO dbo.AutoNumber2(id ,data ,)
VALUES(101, N'AAA'),(102,N'BBBB'),(103,N'CCC')
SET IDENTITY_INSERT dbo.AutoNumber2 OFF;

注意事項：

• 同時只能對一個table on，才能對id進行操作
• 寫入大量資料

SELECT SCOPE_IDENTITY()

可以知道該table最後跳號

DBCC CHECKIDENT('dbo.AutoNumber2',RESEED,0)

在資料庫維護時使用，不要寫在正式運作程式中

TRUNCATE TABLE

• 不會再LDF中記錄每一筆資料
• 不能加WHERE子句
• IDENTITY 會跳到初始

INSERT INTO dbo.AutoNumber2(data)
VALUES(N'AAA'),(N'BBB'),(N'CCC')

SELECT * FROM dbo.AutoNumber2

TRUNCATE TABLE dbo.AutoNumber2
DELETE TABLE dbo.AutoNumber2

INSERT INTO dbo.AutoNumber2(data)
VALUES(N'AAA'),(N'BBB'),(N'CCC')

SEQUENCE

• UI->db01 ->programmability ->Sequence
• TSQL

CREATE SEQUENCE dbo.Seq01
AS smallint
INCREMENT BY 1 
MINVALUE -5
MAXVALUE 10
CYCLE;
GO

SELECT NEXT VALUE FOR dbo.Seq01
GO 20

-- RESET
ALTER SEQUENCE dbo.Seq01
RESTART;
GO

CREATE TABLE dbo.AutoNumber3
(
 id int IDENTITY(1,1) PRIMARY KEY,
 data nvarchar(50),
 auto_no smallint DEFAULT NEXT VALUE FOR dbo.Seq01;
)

 

IDENTITY VS. SEQUENCE

IDENTITY	SEQUENCE
是一種欄位的屬性(Column property)	是DB物件
id int IDENTITY(1,1)	auto_no small int DEFAULT NEXT FOR dbo.Seq01
一個Table 中僅能有一個IDENTITY欄位	沒有限制
不同Table 的IDENTITY不互相影響	不同的Table可以共用一個SEQUENCE，也可以各自獨立
常作為PRIMARY KEY	較不適合做為PRIMARY KEY
歸零：TRUNCATE TABEL 、DBCC CHECK(....)	ALTER SEQUENCE dbo.Seq01 RESTART;