|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
|
基本常用查询--selectselect * from student;
--all 查询所有select all sex from student;
--distinct 过滤重复select distinct sex from student;
--count 统计select
count(*) from
student;
select
count(sex) from
student;
select
count(distinct
sex) from student;
--top 取前N条记录select
top 3 * from student;
--alias column name 列重命名select
id as 编号, name ‘名称‘, sex 性别 from
student;
--alias table name 表重命名select
id, name, s.id, s.name
from student s;
--column 列运算select
(age + id) col from
student;
select
s.name + ‘-‘ + c.name from classes c, student s where
s.cid = c.id;
--where 条件select
* from student where
id = 2;
select
* from student where
id > 7;
select
* from student where
id < 3;
select
* from student where
id <> 3;
select
* from student where
id >= 3;
select
* from student where
id <= 5;
select
* from student where
id !> 3;
select
* from student where
id !< 5;
--and 并且select
* from student where
id > 2 and
sex = 1;
--or 或者select
* from student where
id = 2 or sex = 1;
--between ... and ... 相当于并且select
* from student where
id between 2 and 5;
select
* from student where
id not between 2 and 5;
--like 模糊查询select
* from student where
name like
‘%a%‘;
select
* from student where
name like
‘%[a][o]%‘;
select
* from student where
name not like ‘%a%‘;
select
* from student where
name like
‘ja%‘;
select
* from student where
name not like ‘%[j,n]%‘;
select
* from student where
name like
‘%[j,n,a]%‘;
select
* from student where
name like
‘%[^ja,as,on]%‘;
select
* from student where
name like
‘%[ja_on]%‘;
--in 子查询select
* from student where
id in (1, 2);
--not in 不在其中select
* from student where
id not in
(1, 2);
--is null 是空select
* from student where
age is null;
--is not null 不为空select
* from student where
age is not null;
--order by 排序select
* from student order
by name;
select
* from student order
by name desc;
select
* from student order
by name asc;
--group by 分组按照年龄进行分组统计select
count(age), age from
student group
by
age;
按照性别进行分组统计select
count(*), sex from
student group
by
sex;
按照年龄和性别组合分组统计，并排序select
count(*), sex from
student group
by sex, age order
by
age;
按照性别分组，并且是id大于2的记录最后按照性别排序select
count(*), sex from
student where
id > 2 group
by sex order by
sex;
查询id大于2的数据，并完成运算后的结果进行分组和排序select
count(*), (sex * id) new from
student where
id > 2 group
by sex * id order
by sex * id;
--group by all 所有分组按照年龄分组，是所有的年龄select
count(*), age from
student group
by all age;
--having 分组过滤条件按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息select
count(*), age from
student group
by age having
age is not null;
按照年龄和cid组合分组，过滤条件是cid大于1的记录select
count(*), cid, sex from
student group
by cid, sex having
cid > 1;
按照年龄分组，过滤条件是分组后的记录条数大于等于2select
count(*), age from
student group
by age having
count(age) >= 2;
按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2select
count(*), cid, sex from
student group
by cid, sex having
cid > 1 and
max(cid) > 2;
? 嵌套子查询 子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。
# from
(select … table)示例
将一个table的查询结果当做一个新表进行查询
select
* from (
select
id, name from student where
sex = 1
) t where
t.id > 2;
上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句： 1、 包含常规选择列表组件的常规select查询
2、 包含一个或多个表或视图名称的常规from语句
3、 可选的where子句
4、 可选的group
by子句
5、 可选的having子句
# 示例查询班级信息，统计班级学生人生select
*, (select
count(*) from
student where
cid = classes.id) as
num
from
classes order
by
num;
# in, not
in子句查询示例
查询班级id大于小于的这些班级的学生信息select
* from student where
cid in (
select
id from classes where
id > 2 and
id < 4
); 查询不是班的学生信息select
* from student where
cid not in (
select
id from classes where
name = ‘2班‘
)in、not
in 后面的子句返回的结果必须是一列，这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id；
# exists和not
exists子句查询示例
查询存在班级id为的学生信息select
* from student where
exists (
select
* from classes where
id = student.cid and
id = 3
); 查询没有分配班级的学生信息select
* from student where
not exists (
select
* from classes where
id = student.cid
);exists和not
exists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。如：id等于student.id；
# some、any、all子句查询示例
查询班级的学生年龄大于班级的学生的年龄的信息select
* from student where
cid = 5 and
age > all
(
select
age from student where
cid = 3
); select
* from student where
cid = 5 and
age > any
(
select
age from student where
cid = 3
); select
* from student where
cid = 5 and
age > some
(
select
age from student where
cid = 3
); ? 聚合查询1、 distinct去掉重复数据
select
distinct sex from student;
select
count(sex), count(distinct
sex) from student;
2、 compute和compute by汇总查询
对年龄大于的进行汇总select
age from student
where
age > 20 order
by age compute sum(age) by
age;
对年龄大于的按照性别进行分组汇总年龄信息select
id, sex, age from
student
where
age > 20 order
by sex, age compute sum(age) by
sex;
按照年龄分组汇总select
age from student
where
age > 20 order
by age, id compute sum(age);
按照年龄分组，年龄汇总，id找最大值select
id, age from
student
where
age > 20 order
by age compute sum(age), max(id);
compute进行汇总前面是查询的结果，后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式，可以添加的信息如下： a、 可选by关键字。它是每一列计算指定的行聚合
b、 行聚合函数名称。包括sum、avg、min、max、count等
c、 要对其执行聚合函数的列
compute by适合做先分组后汇总的业务。compute by后面的列一定要是order
by中出现的列。
3、 cube汇总
cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。
select
count(*), sex from
student group
by sex with cube;
select
count(*), age, sum(age) from
student where
age is not null group by
age with cube;
cube要结合group
by语句完成分组汇总
? 排序函数 排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如：
1、 对某张表进行排序，序号需要递增不重复的
2、 对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的
3、 在某些排序的情况下，需要跳空序号，虽然是并列
基本语法排序函数 over([分组语句] 排序子句[desc][asc])
排序子句 order
by
列名, 列名
分组子句 partition by
分组列, 分组列
# row_number函数根据排序子句给出递增连续序号按照名称排序的顺序递增select
s.id, s.name, cid, c.name, row_number() over(order
by c.name) as
number
from
student s, classes c where
cid = c.id;
# rank函数函数根据排序子句给出递增的序号，但是存在并列并且跳空顺序递增select
id, name, rank() over(order
by cid) as rank from student;
跳过相同递增select
s.id, s.name, cid, c.name, rank() over(order
by c.name) as
rank
from
student s, classes c where
cid = c.id;
# dense_rank函数根据排序子句给出递增的序号，但是存在并列不跳空不跳过，直接递增select
s.id, s.name, cid, c.name, dense_rank() over(order
by c.name) as
dense
from
student s, classes c where
cid = c.id;
# partition by分组子句
可以完成对分组的数据进行增加排序，partition by可以与以上三个函数联合使用。
利用partition by按照班级名称分组，学生id排序
select
s.id, s.name, cid, c.name, row_number() over(partition by
c.name order by
s.id) as rank
from
student s, classes c where
cid = c.id;
select
s.id, s.name, cid, c.name, rank() over(partition by
c.name order by
s.id) as rank
from
student s, classes c where
cid = c.id;
select
s.id, s.name, cid, c.name, dense_rank() over(partition by
c.name order by
s.id) as rank
from
student s, classes c where
cid = c.id;
# ntile平均排序函数将要排序的数据进行平分，然后按照等分排序。ntile中的参数代表分成多少等分。select
s.id, s.name, cid, c.name,
ntile(5) over(order
by c.name) as
ntile
from
student s, classes c where
cid = c.id;
? 集合运算操作两组查询结果，进行交集、并集、减集运算1、 union和union
all进行并集运算
--union 并集、不重复select
id, name from student where
name like
‘ja%‘
unionselect
id, name from student where
id = 4;
--并集、重复select
* from student where
name like
‘ja%‘
union
all
select
* from student;
2、 intersect进行交集运算
--交集（相同部分）select
* from student where
name like
‘ja%‘
intersectselect
* from student;
3、 except进行减集运算
--减集（除相同部分）select
* from student where
name like
‘ja%‘
exceptselect
* from student where
name like
‘jas%‘;
? 公式表表达式查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性低，不利于理解。那么公式表表达式可以解决这个问题。我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是create
view语句的执行范围内进行定义。
--表达式with
statNum(id, num) as
(select
cid, count(*)
from
student
where
id > 0
group
by
cid
)select
id, num from
statNum order
by
id;
with
statNum(id, num) as
(select
cid, count(*)
from
student
where
id > 0
group
by
cid
)select
max(id), avg(num) from
statNum;
? 连接查询1、 简化连接查询--简化联接查询select
s.id, s.name, c.id, c.name
from student s, classes c where
s.cid = c.id;
2、 left
join左连接
--左连接select
s.id, s.name, c.id, c.name
from student s left
join classes c on
s.cid = c.id;
3、 right
join右连接
--右连接select
s.id, s.name, c.id, c.name
from student s right
join classes c on
s.cid = c.id;
4、 inner
join内连接
--内连接select
s.id, s.name, c.id, c.name
from student s inner
join classes c on
s.cid = c.id;
--inner可以省略select
s.id, s.name, c.id, c.name
from student s join
classes c on
s.cid = c.id;
1、 聚合函数max最大值、min最小值、count统计、avg平均值、sum求和、var求方差
selectmax(age) max_age,
min(age) min_age,
count(age) count_age,
avg(age) avg_age,
sum(age) sum_age,
var(age) var_agefrom
student;
2、 日期时间函数select
dateAdd(day, 3, getDate());--加天
select
dateAdd(year, 3, getDate());--加年
select
dateAdd(hour, 3, getDate());--加小时
--返回跨两个指定日期的日期边界数和时间边界数select
dateDiff(day, ‘2011-06-20‘, getDate());
--相差秒数select
dateDiff(second, ‘2011-06-22 11:00:00‘, getDate());
--相差小时数select
dateDiff(hour, ‘2011-06-22 10:00:00‘, getDate());
select
dateName(month, getDate());--当前月份
select
dateName(minute, getDate());--当前分钟
select
dateName(weekday, getDate());--当前星期
select
datePart(month, getDate());--当前月份
select
datePart(weekday, getDate());--当前星期
select
datePart(second, getDate());--当前秒数
select
day(getDate());--返回当前日期天数
select
day(‘2011-06-30‘);--返回当前日期天数
select
month(getDate());--返回当前日期月份
select
month(‘2011-11-10‘);
select
year(getDate());--返回当前日期年份
select
year(‘2010-11-10‘);
select
getDate();--当前系统日期
select
getUTCDate();--utc日期
|