SQL基础用法(实例二)
时间：2021-07-01 10:21:17
帮助过：23人阅读
/*
2
3
4 2006年10月01日
5
6 SQL Server 数据库的高级操作
7 (1) 批处理
8 (2) 变量
9 (3) 逻辑控制
10 (4) 视图
11 (5) 函数
12 (6) 高级查询
13
14 */
15
16 (
1)批处理
17 将多条SQL语句作为一个整体去编译，生成一个执行计划，然后，执行！
18 理解批处理的关键在于"编译"，对于由多条语句组成的一个批处理，
19 如果在编译时，其中，有一条出现语法错误，将会导致编译失败！
20
21 create table t
22 (
23 a
int,
24 b
int
25 )
26
27 -- 注释
28 -- 如果多行注释中包含了批处理的标识符go
29 -- 在编译的过程中代码将会被go分割成多个部分来分批编译
30 -- 多行注释的标记将会被分隔而导致编译出错
31 -- 以下几条语句是三个非常经典的批处理
32 -- 你猜一下会添加几条记录！
33 /*
34 insert into t values (1,1)
35 go
36 */
37 insert into t
values (
2,
2)
38 go
39 /*
40 insert into t values (3,3)
41 */
42 go
43
44
45 -- 查询看添加了几条记录
46 select * from t
47
48 truncate table t
49
50 (
2)变量
51
52 -- 全局变量
53 SQL Server中全局变量由系统定义、系统维护，用户一般仅可对其进行读取！
54
55 -- 查看SQL Server版本
56 print @@version
57
58 -- 服务器名称
59 print @@servername
60
61 -- 系统错误编号
62 insert into t
values (
‘a‘,
‘a‘)
63 print @@error
64
65 insert into t
values (
‘a‘,
‘a‘)
66 if @@error = 245
67 print ‘Error‘
68
69 -- SQL Server 版本的语言信息
70 print @@LANGUAGE
71
72 -- 一周的第一天从星期几算起
73 print @@datefirst
74
75 -- CPU 执行命令所耗费时间的累加
76 print @@cpu_busy
77
78 -- 获取最近添加的标识列的值
79 create table tt
80 (
81 a
int identity(
3,
10),
82 b
int
83 )
84 insert into tt (b)
values (
1)
85 print @@identity
86 select * from tt
87
88 -- 局部变量
89 局部变量由用户定义，仅可在同一个批处理中调用和访问
90
91 declare @intAge tinyint
92 set @intAge = 12
93 print @intAge
94
95 declare @strName varchar(
12)
96 select @strName = ‘state‘
97 print @strName
98 select au_lname,
@strName from authors
99
100 (
3)逻辑控制
101
102 -- IF条件判断
103 declare @i int
104 set @i = 12
105 if (
@i > 10)
106 begin -- {
107 print ‘Dadadada!‘
108 print ‘Dadadada!‘
109 end -- }
110 else
111 begin
112 print ‘XiaoXiao!‘
113 print ‘XiaoXiao!‘
114 end
115
116 -- While循环控制
117 declare @i int;
118 set @i = 12;
119 print @i
120 --return;
121 while (
@i < 18)
122 begin
123 print @i;
124 set @i = @i + 1;
125 if @i < 17
126 continue;
127 if @i > 15
128 break;
129 end;
130 print @i
131
132 -- CASE 分支判断
133 select au_lname, state,
‘犹他州‘ from authors
where state
= ‘UT‘
134 select au_lname, state,
‘密西西比州‘ from authors
where state
= ‘MI‘
135 select au_lname, state,
‘肯塔基州‘ from authors
where state
= ‘KS‘
136
137 select au_lname, state,
138 case state
139 when ‘UT‘ then ‘犹他州‘
140 when ‘MI‘ then ‘密西西比州‘
141 when ‘KS‘ then ‘肯塔基州‘
142 when ‘CA‘ then ‘加利福利亚‘
143 else state
144 end
145 from authors
146
147
148 (
3)视图
149 -- Northwind 数据库中Employees表
150 -- 创建视图显示每个员工的编号(EmployeeID)、姓(FirstName)、名(LastName)以及上级(ReportsTo)的姓
151 use northwind
152 go
153
154 -- 注意：字段ReportsTo指代了每个员工的上级的编号
155 select EmployeeID, FirstName, LastName, ReportsTo
156 from employees
157 go
158
159 -- 寻找每个员工的上级
160 -- 子查询
161 select emp.EmployeeID, emp.FirstName, emp.LastName,
162 (
select mag.FirstName
from employees
as mag
where emp.ReportsTo
= mag.EmployeeID )
as ManagerFirstName
163 from employees
as emp
164 go
165
166
167
168
169
170 (
4.1)系统函数
171
172 -- 获取指定字符串中左起第一个字符的ASC码
173 print ascii(
‘ABCDEF‘)
174 -- 根据给定的ASC码获取相应的字符
175 print char(
65)
176 -- 获取给定字符串的长度
177 print len(
‘abcdef‘)
178 -- 大小写转换
179 print lower(
‘ABCDEF‘)
180 print upper(
‘abcdef‘)
181 -- 去空格
182 print ltrim(
‘ abcd dfd df ‘)
183 print rtrim(
‘ abcd dfd df ‘)
184 -- 求绝对值
185 print abs(
-12)
186 -- 幂
187 -- 3 的 2 次方
188 print power(
3,
2)
189 print power(
3,
3)
190 -- 随机数
191 -- 0 - 1000 之间的随机数
192 print rand()
* 1000
193 -- 获取圆周率
194 print pi()
195
196
197 -- 获取系统时间
198 print getdate()
199
200 -- 获取3天前的时间
201 print dateadd(
day,
-3 ,
getdate())
202 -- 获取3天后的时间
203 print dateadd(
day,
3 ,
getdate())
204 -- 获取3年前的时间
205 print dateadd(
year,
-3 ,
getdate())
206 -- 获取3年后的时间
207 print dateadd(
year,
3 ,
getdate())
208
209 -- 获取3月后的时间
210 print dateadd(
month,
3 ,
getdate())
211 -- 获取9小时后的时间
212 print dateadd(hour,
9 ,
getdate())
213 -- 获取9分钟后的时间
214 print dateadd(minute,
9 ,
getdate())
215
216 -- 获取指定时间之间相隔多少年
217 print datediff(
year,
‘2005-01-01‘,
‘2008-01-01‘)
218 -- 获取指定时间之间相隔多少月
219 print datediff(
month,
‘2005-01-01‘,
‘2008-01-01‘)
220 -- 获取指定时间之间相隔多少天
221 print datediff(
day,
‘2005-01-01‘,
‘2008-01-01‘)
222
223 -- 字符串合并
224 print ‘abc‘ + ‘def‘
225
226 print ‘abcder‘
227
228 print ‘abc‘ + ‘456‘
229 print ‘abc‘ + 456
230
231 -- 类型转换
232 print ‘abc‘ + convert(
varchar(
10),
456)
233
234 select title_id, type, price
from titles
235 -- 字符串连接必须保证类型一致(以下语句执行将会出错)
236 -- 类型转换
237 select title_id
+ type
+ price
from titles
238 -- 正确
239 select title_id
+ type
+ convert(
varchar(
10), price)
from titles
240
241 print ‘123‘ + convert(
varchar(
3),
123)
242 print ‘123‘ + ‘123‘
243
244 print convert(
varchar(
12),
‘2005-09-01‘,
110)
245
246 -- 获取指定时间的特定部分
247 print year(
getdate())
248 print month(
getdate())
249 print day(
getdate())
250
251 -- 获取指定时间的特定部分
252 print datepart(
year,
getdate())
253 print datepart(
month,
getdate())
254 print datepart(
day,
getdate())
255 print datepart(hh,
getdate())
256 print datepart(mi,
getdate())
257 print datepart(ss,
getdate())
258 print datepart(ms,
getdate())
259
260 -- 获取指定时间的间隔部分
261 -- 返回跨两个指定日期的日期和时间边界数
262 print datediff(
year,
‘2001-01-01‘,
‘2008-08-08‘)
263 print datediff(
month,
‘2001-01-01‘,
‘2008-08-08‘)
264 print datediff(
day,
‘2001-01-01‘,
‘2008-08-08‘)
265 print datediff(hour,
‘2001-01-01‘,
‘2008-08-08‘)
266 print datediff(mi,
‘2001-01-01‘,
‘2008-08-08‘)
267 print datediff(ss,
‘2001-01-01‘,
‘2008-08-08‘)
268
269 -- 在向指定日期加上一段时间的基础上，返回新的 datetime 值
270 print dateadd(
year,
5,
getdate())
271 print dateadd(
month,
5,
getdate())
272 print dateadd(
day,
5,
getdate())
273 print dateadd(hour,
5,
getdate())
274 print dateadd(mi,
5,
getdate())
275 print dateadd(ss,
5,
getdate())
276
277 -- 其他
278 print host_id()
279 print host_name()
280 print db_id(
‘pubs‘)
281 print db_name(
5)
282
283
284 -- 利用系统函数作为默认值约束
285 drop table ttt
286
287 create table ttt
288 (
289 stu_name
varchar(
12),
290 stu_birthday
datetime default (
getdate())
291 )
292
293 alter table ttt
294 add constraint df_ttt_stu_birthday
default (
getdate())
for stu_birthday
295
296 insert into ttt
values (
‘ANiu‘,
‘2005-04-01‘)
297 insert into ttt
values (
‘ANiu‘,
getdate())
298
299 insert into ttt
values (
‘AZhu‘,
default)
300
301 sp_help ttt
302
303 select * from ttt
304
305
306
307 （
4.2）自定义函数
308
309 select title_id
310 from titles
311 where type
= ‘business‘
312
313 select stuff(title_id,
1,
3,
‘ABB‘), type
314 from titles
315 where type
= ‘business‘
316
317 select count(title_id)
from titles
where type
= ‘business‘
318 select title_id
from titles
where type
= ‘business‘
319
320
321 select *,
count(dbo.titleauthor.title_id)
322 FROM dbo.authors
INNER JOIN
323 dbo.titleauthor
ON dbo.authors.au_id
= dbo.titleauthor.au_id
324
325 select au_id,
count(title_id)
326 from titleauthor
327 group by au_id
328
329 SELECT dbo.authors.au_id,
COUNT(dbo.titleauthor.title_id)
AS ‘作品数量‘
330 FROM dbo.authors
left outer JOIN
331 dbo.titleauthor
ON dbo.authors.au_id
= dbo.titleauthor.au_id
332 GROUP BY dbo.authors.au_id
333 order by ‘作品数量‘
334
335 -- 自定义函数的引子（通过这个子查询来引入函数的作用）
336
337 -- 子查询
338 -- 统计每个作者的作品数
339 -- 将父查询中的作者编号传入子查询
340 -- 作为查询条件利用聚合函数count统计其作品数量
341 select au_lname,
342 (
select count(title_id)
343 from titleauthor
as ta
344 where ta.au_id
= a.au_id
345 )
as TitleCount
346 from authors
as a
347 order by TitleCount
348
349
350
351
352 -- 是否可以定义一个函数
353 -- 将作者编号作为参数统计其作品数量并将其返回
354 select au_id, au_lname, dbo.GetTitleCountByAuID(au_id)
as TitleCount
355 from authors
356 order by TitleCount
357
358 -- 根据给定的作者编号获取其相应的作品数量
359 create function GetTitleCountByAuID(
@au_id varchar(
12))
360 returns int
361 begin
362 return (
select count(title_id)
363 from titleauthor
364 where au_id
= @au_id)
365 end
366
367
368 -- 利用函数来显示每个作者的作品数量
369 create proc pro_CalTitleCount
370 as
371 select au_id, au_lname, dbo.GetTitleCountByAuID(au_id)
as TitleCount
372 from authors
373 order by TitleCount
374 go
375
376 -- 执行存储过程
377 execute pro_CalTitleCount
378
379 -- vb中函数定义格式
380 function GetTitleCountByAuID(au_id
as string)
as integer
381
382 .......
383
384 GetTitleCountByAuID
= ?
385 end function
386
387 -- SALES 作品销售信息
388 select * from sales
389
390 -- 根据书籍编号查询其销售记录（其中，qty 表示销量）
391 select * from sales
where title_id
= ‘BU1032‘
392
393 -- 根据书籍编号统计其总销售量（其中，qty 表示销量）
394 select
