2单表查询

示例：

use TSQL2012;

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;

1、执行顺序

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY
  查询以SELECT子句开始，逻辑化的子句处理顺序如下：
  from Sales.Orders
  where custid=71
  group by empid,YEAR(orderdate)
  having count() >1
  select empid,YEAR(orderdate) as orderyear,COUNT() as numorders
  order by empid,orderyear;
  各子句功能：
  1.从Sales.Orders表查询行；
  2.仅筛选客户ID等于71的订单；
  3.按雇员ID和订单年度对订单分组；
  4.仅筛选出大于1一个订单的组
  5.返回每组雇员ID、订单年度和订单数量；
  6.按雇员ID和订单排序输出行。

2、查询前10行订单

select top 10
	orderid,
	orderdate,
	custid,
	empid
from Sales.Orders
order by orderdate desc;

3、查询近10%的订单

select top 10 percent
	orderid,
	orderdate,
	custid,
	empid
from Sales.Orders
order by orderdate desc;

4、开窗函数

select orderid,
	custid,
	val,
	row_number() over(partition by custid
		order by val) as rownum
from Sales.OrderValues
order by custid,val;

sql子句的逻辑处理顺序

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
  * 表达式
  * DISTINCT
• ORDER BY
  * TOP/OFFSET-FETCH

5、谓词

• IN

select
	orderid,
	empid,
	orderdate
from Sales.Orders
where orderid in (10248,10249,10250);

• BETWEEN

select
	orderid,
	empid,
	orderdate
from Sales.Orders
where orderid between 10300 and 10310;

• LIKE

select
	empid,
	firstname,
	lastname
from HR.Employees
where lastname like N'D%';

• IS NULL
• IS NOT NULL

SELECT custid,country,region,city
FROM Sales.Customers;

SELECT custid,country,region,city
FROM Sales.Customers
where region = N'WA';

SELECT custid,country,region,city
FROM Sales.Customers
where region <> N'WA';

SELECT custid,country,region,city
FROM Sales.Customers
where region is null;

SELECT custid,country,region,city
FROM Sales.Customers
where region is not null;

6、运算符

运算符优先级

1. （） (圆括号)
2. *（乘号）、/（除号）、%（取模）
3. +（正号）、-（负号）、+（加号）、+（串联）、-（减号）
4. =、>、<、>=、<=、<>、!=、!>、!<（比较运算符）
5. NOT
6. AND
7. BETWEEN、IN、LIKE、OR
8. =（赋值）
   
   

7、CASE表达式

use TSQL2012;
select productid,productname,categoryid,
	case categoryid
		when 1 then '一'
		when 2 then '二'
		when 3 then '三'
		when 4 then '四'
		when 5 then '五'
		when 6 then '六'
		when 7 then '七'
		else '大于7'
	end as categoryname
from Production.Products;

CASE表达式的缩写形式：ISNULL、COALESCE、IIF、CHOOSE。
其中ISNULL存在截断问题，只有COALESCE是标准的。

select ISNULL(null,null) as a;
select ISNULL('sqy',null) as b;
select ISNULL(null,'sqy') as c;

select COALESCE(null,null,'sqy') as a;
select COALESCE(null,'sqy',null) as b;
select COALESCE('sqy',null,null,'lqq') as c;
select COALESCE(null,null,null,'lqq') as d;

8、字符数据

1）、数据类型

• 常规：每个字符使用1个字节存储，限制除英语外仅能使用一种语言。
  CHAR、VARCHAR
  示例：‘This is a regular character string literal’
• Unicode：每个字符使用2个字节存储，可以支持多种语言。
  NCHAR、NVARCHAR
  示例：N’This is a Unicode character string literal’

CHAR/NCHAR具有固定长度；VARCHAR/NVARCHAR具有可变长度。
VARCHAR(MAX)、NVARCHAR(MAX)，最大默认字节数为8000。

1）、运算符和函数

• 字符串连接
  加号运算符（+）和CONCAT函数。

use TSQL2012;
select empid,firstname,lastname,firstname + lastname as fullname
from HR.Employees;

标准SQL里面规定了连接NULL的结果应为NULL，这是SQL Server的默认行为。例如：

select custid,country,region,city,
	country + N',' + region + N',' + city as location
from Sales.Customers;

解决一：使用COALESCE函数

--使用空字符串替换NULL
select custid,country,region,city,
	country + coalesce(N','+region,N'') + N',' + city as location
from Sales.Customers;

解决二：使用CONCAT函数：接收一个连续的输入列表并自动以空格字符串替换NULL。(SQL Server2012添加)

select custid,country,region,city,
	CONCAT(country,N','+region,N','+city) as location
from Sales.Customers;

• SUBSTRING函数

• LEFT和RIGHT函数
  语法：LEFT(string,n),RIGHT(string,n)
  
• LEN和DATALENGTH函数
  语法：LEN(string)
  注意：1)LEN函数返回的输入字符串中的字符数，但不一定是字节数。对于常规字符，每个字符需要一个存储字节，字符数和字节数是相同的。对于Unicode字符，每个字符需要两个存储字节（大多数请款是这样），因此字符数是字节数的一半。要获取字节数可以使用DATALENGHT函数。
  2)LEN和LENGTH另一个区别是，LEN会删除尾随空格但是DATALENGTH不会。
  

select N'string ' as 'Unicode字符串',
	LEN(N'string ') as 'Unicode字符',
	DATALENGTH(N'string ') as 'Unicode字节',
	'string ' as '常规字符串',
	LEN('string ') as '常规字符',
	DATALENGTH('string ') as '常规字节';

• CHAINDEX
  返回字符串再字符串中第一次出现的位置。
  
  
• PATINDEX函数
  语法：PATINDEX(partter,string)
  参数partter使用T-SQL中like谓词类似模式。
  返回模式在字符串中第一次出现的位置。
  
• REPLACE函数
  语法：REPLACE(string,substring1,substring2)
  使用string2替换string中出现的substring1。
  
  可以用于计算某个字符在字符串内出现的次数。

select empid,lastname,
	LEN(lastname)-LEN(REPLACE(lastname,'e','')) as numoccur
from HR.Employees;

• REPLICATE函数
  语法：REPLICATE(string,n)
  

-- 生成一个具有前导"0"，代表供应商ID的10位数字符串。
select supplierid,
	RIGHT(REPLICATE('0',9) + cast(supplierid as varchar(10)),10) as strsupplierid
from 
Production.Suppliers;

• STUFF函数
  允许从字符串中移除指定数量的字符，并插入一个替代的新字符串。
  语法：STUFF(sting,pos,delete_length,insertstring)

select STUFF('xzf',2,1,'abc');

• UPPER和LOWER
  UPPER：返回输入字符串的全部大写。
  LOWER：返回输入字符串的全部小写。
  
• RTRIM和LTRIM
  删除字符串尾随和前导空格

select RTRIM('   abc   '),LTRIM('   abc   '),RTRIM(LTRIM('   abc   '));