/**************************/
/* SQL DML */
/* AUTHER:WL */
/**************************/
USE Northwind
SELECT * FROM
(
SELECT TOP 10 PERCENT orderid , customerid
FROM dbo.Orders
ORDER BY orderid
)
AS D
-- 以上是非合理的SQL，ORDER BY 返回游标不应作为表变量
--SQL2005 FROM 子句的表操作：JOIN , APPLY ,PIVOT 和 UNPIVOT
SELECT * FROM dbo.Customers
SELECT * FROM dbo.Orders
--CROSS APPLY
--返回消费者两个最新的订单
SELECT C.customerid ,city,orderid
FROM dbo.Customers AS C
CROSS APPLY
(
SELECT TOP(2) orderid ,customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC
) AS CA
--OUTER APPLY
--返回消费者两个最新的订单，同时返回没有订单的消费者
SELECT C.customerid ,city ,orderid
FROM dbo.Customers AS C
OUTER APPLY
(SELECT TOP(2) orderid ,customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC ) AS OA
SELECT C.customerid ,city,
CASE
WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) = 2 THEN 'up_to_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_to_two_orders'
END AS category
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid
GROUP BY C.customerid ,city
-- use SQL 2005 PIVORT
SELECT city , no_orders ,up_to_two_orders,more_to_two_orders
FROM
(
SELECT C.customerid ,city,
CASE
WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) = 2 THEN 'up_to_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_to_two_orders'
END AS category
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid
GROUP BY C.customerid ,city
)
AS D PIVOT(COUNT(customerid) FOR category
IN(
[no_orders],
[up_to_two_orders],
[more_to_two_orders]
)) AS P
SELECT city,
COUNT(CASE WHEN category = 'no_orders' THEN customerid END) AS [no_orders] ,
COUNT(CASE WHEN category = 'up_to_two_orders' THEN customerid END) AS [up_to_two_orders],
COUNT(CASE WHEN category = 'more_to_two_orders' THEN customerid END) AS [more_to_two_orders]
FROM (
SELECT C.customerid ,city,
CASE
WHEN COUNT(orderid) = 0 THEN 'no_orders'
WHEN COUNT(orderid) = 2 THEN 'up_to_two_orders'
WHEN COUNT(orderid) > 2 THEN 'more_to_two_orders'
END AS category
FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid
GROUP BY C.customerid ,city
) AS D
GROUP BY city
-- use UNPIVOT
-- use OVER
SELECT orderid ,customerid,
COUNT(*) OVER(PARTITION BY customerid ) AS num_orders
FROM dbo.Orders
WHERE customerid IS NOT NULL
AND orderid % 2 = 1
SELECT orderid ,customerid FROM dbo.Orders
WHERE customerid IS NOT NULL
AND orderid % 2 = 1
ORDER BY COUNT(*) OVER(PARTITION BY customerid ) DESC
--UNION
SELECT 'O' AS letter , customerid ,orderid FROM dbo.Orders
WHERE customerid LIKE '%o%'
UNION ALL
SELECT 'S' AS letter ,customerid ,orderid FROM dbo.Orders
WHERE customerid LIKE '%s%'
ORDER BY letter ,customerid ,orderid
--EXCEPTF 返回不重复的数据
SELECT customerid FROM dbo.Customers
EXCEPT
SELECT customerid FROM dbo.Orders
-- INTERSECT 返回重复的数据
SELECT customerid FROM dbo.Customers
INTERSECT
SELECT customerid FROM dbo.Orders
USE Northwind
SELECT C.CustomerID , COUNT(O.OrderID) AS NumOrders
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID
WHERE C.city = 'london'
GROUP BY C.customerID
HAVING COUNT(O.Orderid) > 5
ORDER BY NumOrders
-- 优化事件
SELECT * FROM sys.dm_exec _query_optimizer_info
USE Northwind
SELECT * INTO #TEMPWL FROM dbo.Orders
SELECT * FROM #TEMPWL
DROP TABLE #TEMPWL
GO
SELECT * FROM dbo.Table_1wl
SELECT * FROM dbo.Table_2wl
-- 复制表到另一张表
SELECT * INTO #Table_2wl FROM dbo.Table_1wl
SELECT * FROM #Table_2wl
--将一张表中的内容批量插入到另一张表
INSERT INTO Table_2wl SELECT T1,T2 FROM dbo.Table_1wl
SELECT * FROM dbo.Table_2wl
USE Northwind
SELECT
[Order Details].OrderID,
Products.ProductName,
[Order Details].Quantity,
[Order Details].UnitPrice
FROM dbo.[Order Details]
LEFT OUTER JOIN dbo.Products
ON [Order Details].ProductID = Products.ProductID
WHERE Products.UnitPrice > 10
--SHOWPLAN_TEXT
SET NOCOUNT ON;
USE Northwind;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT ProductName ,Products.ProductID
FROM dbo.[Order Details]
JOIN dbo.Products
ON [Order Details].ProductID = Products.ProductID
WHERE Products.UnitPrice > 10;
GO
SET SHOWPLAN_TEXT OFF;
GO
--SHOWPLAN_ALL
SET NOCOUNT ON;
USE Northwind;
GO
SET SHOWPLAN_ALL ON;
GO
SELECT ProductName ,Products.ProductID
FROM dbo.[Order Details]
JOIN dbo.Products
ON [Order Details].ProductID = Products.ProductID
WHERE Products.UnitPrice > 10;
GO
SET SHOWPLAN_ALL OFF;
GO
--STATISTICS XML
SET NOCOUNT ON;
USE Northwind;
GO
SET STATISTICS XML ON;
GO
SELECT ProductName ,Products.ProductID
FROM dbo.[Order Details]
JOIN dbo.Products
ON [Order Details].ProductID = Products.ProductID
WHERE Products.UnitPrice > 10;
GO
SET STATISTICS XML OFF;
GO
--SHOWPLAN_XML
SET NOCOUNT ON;
USE Northwind;
GO
SET SHOWPLAN_XML ON;
GO
SELECT ProductName ,Products.ProductID
FROM dbo.[Order Details]
JOIN dbo.Products
ON [Order Details].ProductID = Products.ProductID
WHERE Products.UnitPrice > 10;
GO
SET SHOWPLAN_XML OFF;
GO
--sql cache
SELECT * FROM sys.syscacheobjects
SELECT top 10 sql FROM sys.syscacheobjects
declare @tb table(id int,name varchar(50),age int) --创建表变量
insert @tb select 1,'nn',14
select * from @tb
create table #t(id int,name varchar(50),years int,nums int)--创建临时表
insert #t select 1,'nn',14,15
union all select 1,'nn',14,15
insert into #t exec sp_gets --可以用于存储过程或动态SQL结合
select * from #t
drop table #t --删除临时表
--1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
--法一：
select * into b from dbo.Orders where 1<>1
select * from b
drop table b
--法二：
select top 0 * into bTest from dbo.Orders
select * from bTest
drop table bTest