第 2 章： 单表查询
use TSQLFundamentals2008; select * from Sales.orders; select empid, year(orderdate) as orderyear, Count(*) as numorders from Sales.Orders where custid='71' group by empid, year(orderdate) having count(*) >1 order by empid, orderyear; select empid, year(orderdate) as orderyear, Count(distinct custid) as custnum from Sales.Orders group by empid, year(orderdate) order by empid, orderyear; select * from HR.Employees; select distinct country as cty, * from HR.Employees order by country; select distinct country from HR.Employees as e order by country; select count(*) from Sales.Orders select top(861) * from Sales.Orders select top(5) orderid, orderdate, custid, empid from Sales.Orders order by orderdate desc select top(5) orderid, orderdate, custid, empid from Sales.Orders order by orderdate desc, orderid desc select top(5) with ties orderid, orderdate, custid, empid from Sales.Orders order by orderdate desc --2.1.8 OVER 子句 select orderid, custid, val from Sales.OrderValues; select orderid, custid, val, sum(val) over() as totalvalue, sum(val) over(partition by custid) as custtotalvalue from Sales.OrderValues; select orderid, custid, val, 100. * val /sum(val) over() as totalvaluePer, 100. * val /sum(val) over(partition by custid) as custtotalvaluePer from Sales.OrderValues; select OV.orderid, OV.custid, ov.val, ROW_NUMBER() over(order by val, OV.orderid) as rownum, Rank() over(order by val) as rank, Dense_rank() over(order by val) as dense_rank, NTile(10) over(order by val) as ntile from Sales.OrderValues as OV order by val select OV.orderid, OV.custid, ov.val, --ROW_NUMBER() over(order by val, OV.orderid) as rownum, --Rank() over(order by val) as rank, --Dense_rank() over(order by val) as dense_rank, NTile(10) over(order by val) as ntile from Sales.OrderValues as OV order by val ----------------------------------------------------- select ov.val, ROW_NUMBER() over(order by val) as rownum from sales.OrderValues as ov select distinct ov.val, ROW_NUMBER() over(order by val) as rownum from sales.OrderValues as ov select val from sales.OrderValues as ov group by val select ov.val, ROW_NUMBER() over(order by val) as rownum from sales.OrderValues as ov group by val ----------------------------------------------------- ----------------------------------------------------- --2.3 Case子句 ----------------------------------------------------- select P.productid, p.productname, categoryid, Case categoryid when 1 then 'Beverages' when 2 then 'Condimets' when 3 then 'Confe' when 4 then 'Beverages' when 5 then 'sdf' when 6 then 'asd' when 7 then 'ghh' else '不知道' end as Categoryname from Production.Products as P select orderid, custid,val, case when val<1000.00 then '< 100' when val between 1000.00 and 3000.00 then '1000-3000' when val > 3000.00 then '> 3000' else '不知道' end as valueCategory from sales.OrderValues; --2.4 select * from Sales.Customers where region=N'WA' select * from Sales.Customers where region <> N'WA' select * from Sales.Customers where region <>N'WA' or region IS NULL -------------------------------------- --2.5 --2.6.2 select * from sys.fn_helpcollations() select Len('abcde') select Len(N'abcde') select dataLength('abcde') select dataLength(N'abcde') select Len('abcde ') select dataLength(N'abcde ') ---2.7.5 select GETDATE() as "GetDate", CURRENT_TIMESTAMP as "CURRENT_TIMESTAMP", SYSDATETIME() as "SYSDATETIME", SYSUTCDATETIME() as "SYSUTCDATETIME", SYSDATETIMEOFFSET() as "SYSDATETIMEOFFSET" select Cast(CURRENT_TIMESTAMP as date) as "date", Cast(CURRENT_TIMESTAMP as time) as "time" select CURRENT_TIMESTAMP as "CURRENT_TIMESTAMP", Cast(CURRENT_TIMESTAMP as date) as "date1", Cast(CURRENT_TIMESTAMP as char(100)) as "date2" select CONVERT(Char(8),CURRENT_TIMESTAMP, 112), Cast(CONVERT(Char(8),CURRENT_TIMESTAMP, 112) as datetime) select CONVERT(Char(12),CURRENT_TIMESTAMP, 114), Cast(CONVERT(Char(12),CURRENT_TIMESTAMP, 114) as datetime), Cast(CONVERT(Char(12),CURRENT_TIMESTAMP, 114) as datetime2) select DATEADD(year, 1, '20160526'), DATEADD(year, 1, CURRENT_TIMESTAMP) select datediff(DAY,'20080212', '20090212') select CURRENT_TIMESTAMP, Datediff(day,'20160501', CURRENT_TIMESTAMP), DATEadd(day, Datediff(day,'20160501', CURRENT_TIMESTAMP), '20160501') select CURRENT_TIMESTAMP, Datediff(MONTH,'20160501', CURRENT_TIMESTAMP), DATEadd(MONTH, Datediff(MONTH,'20160501', CURRENT_TIMESTAMP), '20160501') select CURRENT_TIMESTAMP, Datediff(MONTH,'20151231', CURRENT_TIMESTAMP), DATEadd(MONTH, Datediff(MONTH,'20151231', CURRENT_TIMESTAMP), '20151231') select CURRENT_TIMESTAMP, Datediff(YEAR,'20150101', CURRENT_TIMESTAMP), DATEadd(YEAR, Datediff(YEAR,'20150101', CURRENT_TIMESTAMP), '20150101') select CURRENT_TIMESTAMP, Datediff(YEAR,'19991231', CURRENT_TIMESTAMP), DATEadd(YEAR, Datediff(YEAR,'19991231', CURRENT_TIMESTAMP), '19991231') select year(current_timestamp) select year('20150527') select datepart(month,'20160212') select datepart(month,CURRENT_TIMESTAMP) select datename(month,CURRENT_TIMESTAMP) select datename(month,'20170202') select * from sys.tables; select SCHEMA_NAME(schema_id),name as tablename from sys.tables; select * from sys.columns select TYPE_NAME(system_type_id), * from sys.columns where object_id = OBJECT_ID(N'Sales.Orders') --2.6 exec sys.sp_help @objname=N'Sales.Orders' exec sys.sp_columns @table_name=N'Orders', @table_owner=N'Sales' --2.10 --2.10.1 select orderid, orderdate, custid, empid from Sales.Orders as o where o.orderdate > '20070531' and o.orderdate < '20070701' --2.10.2 select orderid, orderdate, custid, empid from Sales.Orders as o where o.orderdate= dateadd(month,datediff(month,'19991231',o.orderdate),'19991231') --select DateDiff(MONTH,'19991231',current_timestamp); --select Dateadd(MONTH,DateDiff(MONTH,'19991231','20160602'),'19991231'); select Dateadd(MONTH,DateDiff(MONTH,'19991231','20160102'),'19991231'); select Dateadd(MONTH,DateDiff(MONTH,'19990101','20160502'),'19990101'); select Dateadd(MONTH,DateDiff(MONTH,'19990101','20160602'),'19990101'); select Dateadd(MONTH,DateDiff(MONTH,'19991231','20160502'),'19991231'); select Dateadd(MONTH,DateDiff(MONTH,'19991231','20160605'),'19991231'); select Dateadd(MONTH,DateDiff(MONTH,'19991231','20160205'),'19991231'); --2.10.3 select empid, firstname, lastname from hr.Employees as e where e.lastname like N'%a%a' ----2.10.4 select * from Sales.OrderDetails select orderid, Sum(od.unitprice * od.qty) as totalValue from Sales.OrderDetails as od group by od.orderid having Sum(od.unitprice * od.qty) > 10000 order by totalValue Desc ----2.10.5 select * from Sales.Orders select top(3) o.shipcountry,AVG(o.freight) as avgfreight from Sales.Orders as o where o.orderdate >= '20070101' and o.orderdate < '20080101' group by o.shipcountry order by AVG(o.freight) desc ----2.10.6 select custid, orderdate, orderid, ROW_NUMBER() over(partition by custid order by orderdate,orderid) as rownum from Sales.Orders ----2.10.7 select * from HR.Employees select empid, firstname, lastname, titleofcourtesy, case titleofcourtesy when 'Ms.' then 'Female' when 'Mrs.' then 'Female' when 'Mr' then 'Male' else 'Unknown' end as gender from HR.Employees select empid, firstname, lastname, titleofcourtesy, case titleofcourtesy when 'Ms.' then 'Female' when 'Mrs.' then 'Female' when 'Mr' then 'Male' else 'Unknown' end as gender from HR.Employees select empid, firstname, lastname, titleofcourtesy, case when titleofcourtesy ='Ms.' then 'Female' when titleofcourtesy ='Mrs.' then 'Female' when titleofcourtesy ='Mr' then 'Male' else 'Unknown' end as gender from HR.Employees select empid, firstname, lastname, titleofcourtesy, case when titleofcourtesy Like'M%s.' then 'Female' when titleofcourtesy Like'Mr' then 'Male' else 'Unknown' end as gender from HR.Employees select * from Sales.Customers select custid, region from Sales.Customers order by case when region is Null then 1 else 0 end ,region select custid, region from Sales.Customers order by case when region is Null then 4 else 3 end ,region
第 3 章 ：联接查询
Use TSQLFundamentals2008; --3.1.1 select * from Sales.Customers as C select * from HR.Employees as E select * from Sales.Customers as C cross join HR.Employees as E --3.1.2 select * from Sales.Customers, HR.Employees; --3.1.3 select E1.empid, E2.empid from HR.Employees as E1 cross join HR.Employees as E2 select E1.empid, E2.empid from HR.Employees as E1 cross join HR.Employees as E2 order by E1.empid select E1.empid, E2.empid from HR.Employees as E1 cross join HR.Employees as E2 order by E1.empid,E2.empid --3.1.4 use tempdb; if OBJECT_ID('dbo.Digits') is not null drop table dbo.Digits create table dbo.Digits( digit int not null primary key ) insert into dbo.Digits(digit) values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9) select D1.digit, D2.digit from dbo.Digits as D1 cross join dbo.Digits as D2 select D1.digit, D2.digit, D3.digit from dbo.Digits as D1 cross join dbo.Digits as D2 cross join dbo.Digits as D3 select D3.digit * 100 + D2.digit * 10 + D1.digit + 1 as n from dbo.Digits as D1 cross join dbo.Digits as D2 cross join dbo.Digits as D3 order by n select D3.digit * 100 + D2.digit * 10 + D1.digit + 1 as n from dbo.Digits as D1 ,dbo.Digits as D2 ,dbo.Digits as D3 order by n --3.2.1 select * from dbo.Digits as D1 join dbo.Digits as D2 on D1.digit = D2.digit --3.2.2 select * from dbo.Digits as D1 ,dbo.Digits as D2 where D1.digit = D2.digit use TSQLFundamentals2008 --3.3.3 select C.custid, O.orderid from Sales.Customers as C join Sales.Orders as O on c.custid=O.custid select o.orderid, od.productid from Sales.Orders as O JOIN Sales.OrderDetails AS OD on O.orderid=OD.orderid select C.custid, O.orderid from Sales.Customers as C join Sales.Orders as O on c.custid=O.custid select C.custid,o.orderid, od.productid from Sales.Customers as C JOIN Sales.Orders as O On c.custid= O.custid JOIN Sales.OrderDetails AS OD on O.orderid=OD.orderid --3.4 --3.4.1 select q.custid, o.orderid from Sales.Customers q join Sales.Orders o on q.custid= o.custid select q.custid, o.orderid from Sales.Customers q left join Sales.Orders o on q.custid= o.custid select q.custid, o.orderid from Sales.Customers q left join Sales.Orders o on q.custid= o.custid where o.orderid is null -- select C.custid ,COUNT(*) from Sales.Customers C left join Sales.Orders as O on c.custid=O.custid group by C.custid select C.custid ,COUNT(O.orderid) from Sales.Customers C left join Sales.Orders as O on c.custid=O.custid group by C.custid --3.6.1 set nocount on; use TSQLFundamentals2008; if OBJECT_ID('dbo.NUMS', 'U') is not null drop table dbo.NUMS; create table dbo.NUMS ( n int not null primary key ); declare @i as int =1; begin tran while @i <= 100000 begin insert into dbo.NUMS(n) values(@i); set @i = @i + 1; end commit tran set nocount off; --3.6.2 select E.empid,e.firstname, e.lastname, n.n from HR.Employees as E join dbo.NUMS N on N.n < 5 select E.empid,e.firstname, e.lastname, n.n from HR.Employees as E cross join dbo.NUMS N where N.n < 5 order by n.n, E.empid select e.empid, DATEADD(day, n- 1,'20090612') as dt from HR.Employees as E Cross Join NUMS as D where d.n <= datediff(day,'20090612','20090616') + 1 order by e.empid --3.6.2 select C.custid,COUNT(O.orderid),Sum(OD.qty) from Sales.Customers as C left join Sales.Orders as O on C.custid = O.custid left join Sales.OrderDetails as OD on O.orderid = OD.orderid where C.country ='USA' group by C.custid select C.custid,COUNT(distinct O.orderid),Sum(OD.qty) from Sales.Customers as C left join Sales.Orders as O on C.custid = O.custid left join Sales.OrderDetails as OD on O.orderid = OD.orderid where C.country =N'USA' group by C.custid --3.6.3 select C.custid, C.companyname, O.orderid, O.orderdate from Sales.Customers as C Left Join Sales.Orders as O on C.custid=O.custid --3.6.4 select C.custid, C.companyname, O.orderid, O.orderdate from Sales.Customers as C Left Join Sales.Orders as O on C.custid=O.custid where O.orderid Is NUll --3.6.5 select C.custid, C.companyname, O.orderid, O.orderdate from Sales.Customers as C Join Sales.Orders as O on C.custid=O.custid where orderdate ='20070212' --3.6.6 select C.custid, C.companyname, O.orderid, O.orderdate from Sales.Customers as C Left Join Sales.Orders as O on C.custid=O.custid where orderdate ='20070212' or O.orderdate is NUll select C.custid, C.companyname, O.orderid, O.orderdate from Sales.Customers as C Left Join Sales.Orders as O on C.custid=O.custid and orderdate ='20070212' --3.6.7 select C.custid, C.companyname, O.orderid, O.orderdate, Case when O.orderid Is Not null then 'Yes' else 'NO' End as HasOrderOn20170212 from Sales.Customers as C Left Join Sales.Orders as O on C.custid=O.custid and orderdate ='20070212' order by C.custid; select distinct C.custid, C.companyname, O.orderid, O.orderdate, Case when O.orderid Is Not null then 'Yes' else 'NO' End as HasOrderOn20170212 from Sales.Customers as C Left Join Sales.Orders as O on C.custid=O.custid and orderdate ='20070212' order by C.custid;