1 --------------------------------------所有的数据基于Northwind库---------------------------
2
3 --Order by 支持多个排序
4 select * from dbo.Employees order by EmployeeID asc,TitleOfCourtesy desc,BirthDate asc;
5
6 --Title中包含es的所有数据
7 select * from dbo.Employees where Title like '%es%';
8
9 --Title中倒数第二个字符为v，最后一个字符任意
10 select * from dbo.Employees where Title like '%v_';
11
12 --集合匹配只在 MSSQLServer 上提供支持，在 MYSQL、Oracle、DB2 等数据库中不支持， 必须采用变通的手段来实现。
13 --查询LastName中以d或者s开头的数据
14 select * from dbo.Employees where LastName like '[ds]%';
15 --在其他的数据库中我们可以变通的设置下[这个就是通用的sql了]
16 select * from dbo.Employees where LastName like 'd%' or LastName like 's%'
17
18 --否定符^来对集合取反，它匹配不与字符集中任意一个字符相匹配的字符
19 --查询LastName中所有不以d或者s开头的数据
20 select * from dbo.Employees where LastName like '[^ds]%';
21 --变为通用的sql
22 select * from dbo.Employees where not(LastName like 'd%') And not(LastName like 's%');
23
24 -- 通配符过滤一个非常强大的功能，不 过 在使 用通配符过滤进行检索的时候，数据库系统 会对全表进行扫描，所以执行速度非常慢。
25 --因此不要过分使用通配符过滤，在使用其他方式 可以实现的效果的时候就应该避免使用通配符过滤
26
27
28 --空值检测
29 select * from dbo.Employees where Region is null;
30 --检测Region不为空
31 select * from dbo.Employees where Region is not null;
32
33 -- 反义运算符
34 -- “=”、“<”、“>”等运算符都是用来进行数值判断的，有的时候则会想使用这些运算符 的反义
35 --查询Extension不大于2300的数据;
36 select * from dbo.Employees where Extension !>2300;
37
38 --通用Sql的反义运算符 不等于<> 不大于<= 不小于>=
39 select * from dbo.Employees where Extension <=2300;
40 --或者使用not
41 select * from dbo.Employees where not (Extension=2300) and (Extension<2300);
42
43
44 --多值检测
45 --查询员工序列号为1,3,7的员工信息
46 select * from dbo.Employees where EmployeeID =1 or EmployeeID=3 or EmployeeID=7;
47 --简写
48 select * from dbo.Employees where EmployeeID in(1,3,7);
49
50 -- 范围值检测
51 --检索EmployeeID在3到8之间的数据
52 select * from dbo.Employees where EmployeeID>=3 and EmployeeID <=8;
53 --简写
54 select * from dbo.Employees where EmployeeID between 3 and 8;
55
56 --数据分组
57 --需要注意的是GROUP BY子句的位置，GROUP BY子句必须放到SELECT语句的之后，
58 --如果 SELECT语句有WHERE子句，则GROUP BY子句必须放到WHERE语句的之后。
59
60 select EmployeeID from dbo.Orders group by EmployeeID;
61
62 --分组函数和聚合函数联合使用
63 select EmployeeID,COUNT(*) as a from dbo.Orders group by EmployeeID;
64
65 --首先根据EmployeeID分组，再根据OrderID分组
66 select EmployeeID,OrderID from dbo.Orders group by EmployeeID,OrderID;
67
68 --分组函数，聚合函数以及排序功能混合使用
69 select EmployeeID,ShipVia,COUNT(*) OrderCount from dbo.Orders group by EmployeeID,ShipVia order by ShipVia,EmployeeID;
70
71
72 --HAVING条件过滤
73 --根据EmployeeID分组，查询分组总数大于100的ID
74 select EmployeeID,COUNT(*) as a from dbo.Orders group by EmployeeID having COUNT(*)>100;
75
76 --having过滤条件和in联合使用
77 select EmployeeID,COUNT(*) as a from dbo.Orders group by EmployeeID having COUNT(*) in(123,96,72)
78
79 --HAVING语句能够使用的语法和WHERE几乎是一样的，不过使用WHERE的时候,GROUP BY 子句要位于WHERE子句之后，
80 --而使用HAVING子句的时候，GROUP BY子句要位于HAVING子句之前
81
82
83 --ROW_NUMBER OVER(排序规则)
84 select ROW_NUMBER() over(order by orderid) id, OrderID,EmployeeID,ShipVia from Orders;
85
86
87 --查询第3行到第10行的数据[子查询]
88 --方法一：
89 select * from(
90 select ROW_NUMBER() over (order by orderId) id,OrderID,EmployeeID,ShipVia from Orders
91 )T where t.id between 3 and 10;
92 --方法二：
93 select top 8 * from dbo.Orders where OrderID not in
94 (select top 2 OrderID from Orders);
95
96
97 --DISTINCT去重复数据
98 select distinct EmployeeID from Orders;
99
100 --常量字段 也称为‘常量值’ 并没有什么实际的意义
101 select '产品订单' as 'name',* from dbo.Orders ;
102
103 --字段间计算 [可用于select,insert,update,where等条件中]
104 select OrderID,(ShipVia*Freight) as Price from dbo.Orders;
105
106 --数据处理函数
107
108 ---主流数据库系统都提供了计算字符串长度的函数，在MYSQL、Oracle、DB2中这个函数 名称为LENGTH,
109 -- 而在MSSQLServer中这个函数的名称则为LEN
110 select ProductID,ProductName,LEN(ProductName) as ProductNameLen from Products;
111
112 --主流系统都提供了取得字符串的子串的函数，在MYSQL、MSSQLServer中这个函数名称 为SUBSTRING，
113 --而在Oracle、DB2这个函数名称为SUBSTR。
114 --这个函数接受三个参数，第一个 参数为要取的主字符串，第二个参数为字串的起始位置（从1开始计数），第三个参数为字串 的长度
115 --注意在数据库中Start的index索引是从1开始的
116 select ProductID,SUBSTRING(ProductName,1,3)as 'SubProductName' from dbo.Products;
117
118 --主流系统都提供了计算正弦函数值的函数SIN和计算绝对值的函数ABS，它们都接受一个数值类型的参数
119 select ProductID,SIN(UnitPrice) SInPrice from dbo.Products;
120 select ProductID,UnitPrice,ABS(UnitPrice) AbsPrice,SIN(UnitPrice) as SInPrice from dbo.Products
121
122 --拼接字符串
123 select '产品ID为：'+CONVERT(varchar,ProductID)+'的产品名称为：'+ProductName as Description from dbo.Products
124
125 --不从实体表中取的数据 有的时候我们需要查询一些不能从任何实体表中能够取得的数据，
126 --比如将数字1作为结果集或者计算字符串“abc”的长度
127 select distinct 1 as '1' from Products;
128 --MYSQL和MSSQLServer允许使用不带FROM子句的SELECT语句来查询这些不属于任何实 体表的数据，比如下面的SQL将1作为结果集
129 select 1 as '1';
130 select LEN('abc');
131 SELECT 1,2,3,'a','b','c';
132
133 --求打印出打印5以内自然数以及它们的平方数
134 SELECT 1 as '序号',1 * 1 as '平方数'
135 UNION SELECT 2,2 * 2
136 UNION SELECT 3,3 * 3
137 UNION SELECT 4,4 * 4
138 UNION SELECT 5,5 * 5 ;
139
140 -- 分别列出所有的正式员工和所有的临时工的姓名
141 select '正式员工'
142 UNION ALL
143 select LastName from dbo.emp
144 union all
145 select '临时工'
146 union all
147 select LastName from dbo.Employees;
148
149 CREATE TABLE T_Person (FIdNumber VARCHAR(20), FName VARCHAR(20),FBirthDay DATETIME, FRegDay DATETIME,FWeight NUMERIC(10,2))
150 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67);
151 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17);
152 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33);
153 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23);
154 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68);
155 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67);
156 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28);
157 INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES ('123456789127','BillGates','1972-07-18','1995-06-19',60.32);
158
159 select * from T_Person
160
161
162 --一般用 Date 来表示日期类型
163 --一般用 Time 来表示时间类型
164 -- 在数据库中， 一般用 DateTime 来表示日期时间类型
165 --一般用 TimeStamp 来表示日期时间类型
166
167 --获取当前时间
168 select GETDATE() as '当前日期时间';
169
170 --当前日期
171 select CONVERT(varchar(50),GETDATE(),101) as '当前日期';
172
173 --当前时间
174 select CONVERT(varchar(50),GETDATE(),108) as '当前时间';
175
176
177 --取值 别名 说明
178 --year yy,yyyy 年份
179 --quarter qq,q 季度
180 --month mm,m 月份
181 --dayofyear dy,y 当年度的第几天
182 --day dd,d 日
183 --week wk,ww 当年度的第几周
184 --weekday dw,w 星期几
185 --hour hh 小时
186 --minute mi,n 分
187 --second ss,s 秒
188 --millisecond ms 毫秒
189
190 --在当前日期上加两天
191 select DATEADD(DAY,2,GETDATE());
192 select CONVERT(varchar(50),(select DATEADD(DAY,2,GETDATE())),101);
193
194 --当前时间的下一个星期
195 select DATEADD(week,1,getdate());
196
197 --当前时间的上一个月
198 select DATEADD(MONTH,-1,getdate());
199
200 --DATEDIFF()函数用于计算两个日期之间的差额
201
202 --计算月份差
203 select DATEDIFF(month,getdate(),(select dateadd(month,2,getdate()))) as '计算月份差';
204
205 --DATENAME()函数，这个函数可以返回一个日期的特定部分，并且 尽量用名称来表述这个特定部分，
206 --其参数格式如下： DATENAME(datepart,date) 其中参数date为待计算日期，
207 --date 参数也可以是日期格式的字符串；参数datepart指定 要返回的日期部分的参数
208
209 --今天是星期几
210 select DATENAME(dw,getdate());
211
212 --当前年份
213 select DATENAME(YY,GETDATE()) as '当前年份';
214
215 --获取当年度的第几天
216 select '今天是'+DATENAME(YYYY,GETDATE())+'年第: '+ DATENAME(DAYOFYEAR,GETDATE())+'天' as '当年度的第几天 ';
217
218 select GETDATE() as '当前日期',
219 DATENAME(YY,(select getdate())) as '年',
220 DATENAME(MONTH,(select getdate())) as '月',
221 DATENAME(DAY,(select GETDATE())) as '日',
222 DATENAME(HH,(select GETDATE())) as '小时',
223 DATENAME(MM,(select getdate())) as '分钟',
224 DATENAME(S,(select GETDATE())) as '秒',
225 DATENAME(MS,(select GETDATE())) as '毫秒';
226
227
228 --DATEPART()函数和DATENAME()函数完全一样，不过其实它们并不是只是名称不同的别名函数，
229 --虽然都是用来提取日期的特定部分的,不过DATEPART()函数的返回值是数字而DATENAME()函数则会将尽可能的以名称的方式做为返回值
230
231 select DATEPART(YY,(select getdate()));
232
233 --类型转换 类型转换
234 SELECT CAST('-30' AS INTEGER) as i,
235 CONVERT(DECIMAL,'3.1415726') as d,
236 CONVERT(DATETIME,'2008-08-08 08:09:10') as dt;
237
238 select RIGHT('123456789120',3) as '后三位',
239 CAST(RIGHT('123456789120',3) as integer) as '后三位转化为整数类型',
240 CAST(RIGHT('123456789120',3) as integer)+1 as '后三位加1',
241 CONVERT(integer,RIGHT('123456789120',3))/2 as '后三位除以2';
242
243 --空值验证
244 --COALESCE的简写版本ISNULL
245 select ShipRegion,ISNULL(ShipRegion,'为空') from Orders;
246
247 --case when 函数
248 --第一种用法
249 select OrderID,
250 (case ShipVia
251 when 1 then 'good'
252 when 2 then 'ok'
253 when 3 then 'no'
254 else 'Error'
255 end
256 ) as orderinfo
257 from Orders;
258 --第二种用法
259 select OrderID,
260 (
261 case when ShipPostalCode<'100 ' then 'no'
262 when ShipPostalCode <'1000' then 'number'
263 else 'ok'
264 end)as ShipPostInfo from Orders;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
