1 /*
2
3
4 2006年10月01日
5
6 SQL Server 数据库的基本操作
7 (1) 数据库的创建
8 (2) 数据表的创建以及相关约束的指定(含临时表)
9 (3) 数据的添/删/改
10 (4) 数据的查询
11
12 */
13
14 (0)创建数据库
15 -- 指定数据库名称
16 -- (注:如果数据库名中包含空格可以使用[]将其标示)
17 create database [Super WC]
18 -- 关于数据文件的定义
19 on
20 (
21 name = Super_WC_Data, -- 逻辑名
22 filename = 'C:\Super_WC_Data.MDF', -- 物理路径以及物理名
23 size = 2MB, -- 初始大小
24 maxsize = 4MB, -- 最大限制
25 filegrowth = 1MB -- 增长大小
26 )
27 -- 关于日志文件的定义
28 log on
29 (
30 name = Super_WC_Log,
31 filename = 'C:\Super_WC_Log.LDF',
32 size = 3MB,
33 maxsize = 7MB,
34 filegrowth = 20% -- 增长比例
35 )
36
37 -- 附加数据库
38 execute sp_attach_db '[Super WC]', 'C:\Super_WC_Data.MDF','C:\Super_WC_Log.LDF'
39 -- 分离数据库
40 execute sp_detach_db '[Super WC]'
41 -- 复制数据库
42 execute master.dbo.xp_cmdshell 'copy C:\Super_WC_Data.MDF D:\Super_WC_Data.MDF'
43 execute master.dbo.xp_cmdshell 'copy C:\Super_WC_Log.LDF D:\Super_WC_Log.LDF'
44
45
46 (1)创建数据表
47
48 创建一个数据表:学生(students)
49 结构如下:
50 字段 类型 是否允许为空 约束 备注
51 no char(4) No 主键 学号
52
53 name nvarchar(8) No 唯一 姓名
54
55 birthday datetime No 检查(至少18年前) 生日
56
57 age tinyint No 缺省(默认等于当前时间减去生日) 年龄
58
59 sex nchar(1) No 缺省(默认'女') 性别
60
61 phone char(11) Yes 检查(要么没有,要么长度等于11) 电话
62
63 address nvarchar(24)No 地址
64
65 没有特别约束的情况:
66 create table student
67 (
68 no char(4) not null,
69 name nvarchar(8) not null,
70 birthday datetime not null,
71 phone char(11) null,
72 address nvarchar(24) null
73 )
74
75 注意:没有特别约束的情况下,创建数据表可以参考“企业管理器”中“设计表”的操作格式!
76
77 包含约束的情况:
78 create table students
79 (
80 no char(4) primary key,
81 name nvarchar(8) unique,
82 birthday datetime check(datediff(year, birthday, getdate()) >= 18),
83 age as datediff(year, birthday, getdate()),
84 sex nchar(1) default('女') check(sex = '女' or sex = '男'),
85 phone char(11) check((phone is null) or (len(phone) = 11)),
86 address nvarchar(24)
87 )
88
89
90 create table scores
91 (
92 no char(4) foreign key references students(no),
93 chinese numeric(4,1) check(chinese >= 0 and chinese <= 100),
94 english numeric(4,1) check(english >= 0 and english <= 100)
95 )
96
97 以上答案只是最简单的描述形式!
98
99 比较规范的写法是
100 先用create table声明数据表的结构,
101
102 CREATE TABLE students
103 (
104 no char(4),
105 name nvarchar(8),
106 birthday datetime,
107 age as DATEDIFF(year, birthday, getdate()),
108 sex nchar(1),
109 phone char(11),
110 address nvarchar(24)
111 )
112
113 然后再ALTER TABLE ADD CONSTRAINT 分别指定每个字段的约束:
114 每个约束都有一个独特的名称,其中,命名规范采用以下格式:
115 约束类型的简写_表名_字段名
116 pk_students_no
117
118 ALTER TABLE students
119 ADD CONSTRAINT pk_students_no PRIMARY KEY (no)
120
121 ALTER TABLE students
122 ADD CONSTRAINT uq_students_name UNIQUE (name)
123
124 ALTER TABLE students
125 ADD CONSTRAINT ck_students_birthday CHECK (datediff(year,[birthday],getdate()) >= 18)
126
127 ALTER TABLE students
128 ADD CONSTRAINT df_students_sex default ('女') for sex
129
130 ALTER TABLE students
131 ADD CONSTRAINT ck_students_sex CHECK ([sex] = '男' or [sex] = '女')
132
133 ALTER TABLE students
134 ADD CONSTRAINT ck_students_phone CHECK ([phone] is null or len([phone]) = 11)
135
136 相对应的对约束进行删除,则是通过DROP CONSTRAINT子句来完成:
137 ALTER TABLE students
138 DROP CONSTRAINT pk_students_no
139
140 ALTER TABLE students
141 DROP CONSTRAINT uq_students_name
142
143 注意:
144 约束只有添加与删除操作,没有修改操作!
145
146
147 注意:
148 其中,age(年龄)采用了“计算列”的表示方法!
149 “计算列”的定义:
150 在表中某个字段的值源于某一表达式的值(某一函数的运算结果或是其他字段的运算结果)!
151 比如:
152 某学生的成绩表结构如下:
153 数学
154 语文
155 体育
156 总分
157
158 创建命令如下:
159 create table scores
160 (
161 math numeric(3, 1),
162 chinese numeric(3, 1),
163 sport numeric(3, 1),
164 total as math + Chinese + sport
165 )
166
167 insert into scores values (80, 69, 95)
168
169 total 部分的值会自动计算,为 244
170
171 -- 创建临时表
172 -- 临时表将会存储在TempDB的临时数据库中
173 -- 当用户断开连接后,就会自动删除
174 -- 语法:在表名前加上#
175 create table #tt
176 (
177 a int,
178 b int
179 )
180
181 insert into #tt values (1,1)
182 insert into #tt values (2,2)
183
184 select * from #tt
185
186
187 (2)数据操作(添加/删除/修改)
188
189 添加操作(insert)的语法格式:
190 Insert [into] 数据表 (字段) values (数据)
191
192 -- 添加记录(一般情况)
193 insert into students
194 (no,name,birthday,sex,phone,address)
195 values
196 ('0001', 'AHuang', '2000-01-01', '男', '13307331100', '株洲')
197
198 (注意: into 可以省略 )
199
200
201 -- 添加记录(如果是给所有字段添加数据,可以省略字段标示)
202 insert into students
203 values
204 ('0002', 'ANing', '2008-08-08', '女', '13307330011', '北京')
205
206
207 -- 添加记录(如果是给具有默认约束的字段添加数据,想利用默认约束,可以利用default)
208 insert into students
209 values
210 ('0002', 'ANing', '2008-08-08', default, '13307330011', '北京')
211
212
213 删除操作(delete)的语法格式:
214 Delete [from] 数据表 where 条件
215
216 -- 删除记录(删除所有)
217 delete from students
218
219 (注意: from 可以省略,即可以: delete students )
220
221 -- 删除记录(删除特定记录,可以通过 where 条件来过滤,比如:学号'0001'的学生记录)
222 delete from students where no = '0001'
223
224
225 修改操作(update)的语法格式:
226 update 数据表 set 字段 = 新值 where 条件
227
228 -- 修改记录(修改所有)
229 update students set 性别 = '女'
230
231 -- 修改记录(修改特定记录,可以通过 where 条件来过滤,比如:学号'0001'的学生记录)
232 update students set 性别 = '男' where no = '0001'
233
234
235
236 (3)数据查询
237
238 查询操作(select)的语法格式:
239 select 字段 from 数据表 where 条件 order by 字段
240
241 -- 查询记录(查询所有行与所有列,指定数据表所有字段)
242 select no, name, birthday, sex, phone, address from students
243
244
245 -- 查询记录(查询所有行与所有列,除了指定数据表所有字段,还可以通过 * 来指代所有字段)
246 select * from students
247
248 -- 查询记录(查询所有行与特定列,指定数据表特定字段)
249 select no, name, phone, address from students
250
251 -- 查询记录(给字段指定别名)
252 select no as '学号', name as '姓名', phone as '电话', address as '地址' from students
253
254 -- 查询记录(合并字段并指定别名)
255 select no as '学号', name as '姓名', address + ' ' + phone as '地址 电话' from students
256
257 (注意:合并字段的前提是字段类型必须一致,否则需要通过类型转换函数convert来实现!)
258
259 -- 类型转换函数 convert 语法
260 convert(目标类型, 数据)
261
262 convert(varchar(12), 1234)
263
264
265 (4)数据查询的高级应用(利用PUBS数据库作为演示数据)
266
267 -- 根据价格对书籍表记录排序
268 select * from titles order by price
269
270 (注意:默认数据排序采用升序:由低到高或是有小到大,可以通过设定关键字来调整)
271
272 -- 根据价格对书籍表记录排序(降序)
273 select * from titles order by price desc
274
275 -- 根据价格对书籍表记录排序(升序)
276 select * from titles order by price asc
277
278 -- 找出书籍表中最高与最低的价格
279 select max(price), min(price) from titles
280
281 -- 统计书籍表中书籍总数以及总价格和平均价格
282 select count(title_id), sum(price), avg(price) from titles
283
284 -- 找出书籍表中最贵3本书
285 select top 3 title_id, price from titles order by price desc
286
287 select * from titles
288 select * from jobs
289 --
290
291 -- 统计书籍表中每种类型的书籍总量
292 select type as '书籍类型', count(title_id) '书籍数量'
293 from titles group by type
294
295 select type, title_id from titles order by type
296
297 -- 统计书籍表中每种类型的书籍的预付款(advance)的总和
298 select type as '书籍类型', sum(advance) '预付款总和'
299 from titles group by type
300
301 -- 列出所有书籍类型
302 select type as '书籍类型' from titles
303 select distinct type as '书籍类型' from titles
304 select distinct type as '书籍类型', title_id from titles
305
306 -- 列出所有作者所在州
307 select distinct state as '州' from authors
308 select distinct state as '州', city as '城市' from authors where state = 'CA'
309 select state as '州', city as '城市' from authors where state = 'CA'
310
311 (注: distinct 列出指定字段的值同时剔出所有重复的!)
312
313
314 -- 根据给定的作者姓名列出其所著书籍名称
315 select * from authors
316 select * from titles
317
318 select a.au_lname as '名', a.au_fname as '姓', t.title as '书籍名称'
319 from authors as a join titleauthor as ta on a.au_id = ta.au_id
320 join titles as t on ta.title_id = t.title_id
321 where a.au_lname = 'Green' and a.au_fname = 'Marjorie'
322
323
324 -- 根据给定的出版社名称列出其所有的出版书籍名称
325 select * from publishers
326
327 select p.pub_name as '出版社', t.title as '书籍名称'
328 from publishers as p join titles as t on p.pub_id = t.pub_id
329 where pub_name = 'New Moon Books'
330
331 select pub_name as '出版社', title as '书籍名称'
332 from publishers as p join titles as t on p.pub_id = t.pub_id
333 where pub_name = 'New Moon Books'
334
335
336 -- 在销售表中挑选1993年度的订单
337 select ord_num as '订单编号', title_id as '书籍编号', ord_date as '订购日期'
338 from sales
339 where ord_date between '1993-01-01' and '1993-12-31'
340
341 (注意:between and 之间的值必须满足"开始点"<="结束点")
342 select ord_num as '订单编号', title_id as '书籍编号', ord_date as '订购日期'
343 from sales
344 where ord_date between '1993-12-31' and '1993-1-1'
345
346
347 -- 在销售表中统计每本书的订单数量
348 select title_id as '书籍编号', count(ord_num) as '订单总数' from sales group by title_id
349
350
351 -- 在销售表中统计每本书的总订购量
352 select title_id as '书籍编号', sum(qty) as '总定购量' from sales group by title_id
353
354 -- 在销售表中统计每个商店的总订购量
355 select stor_id as '商店编号', sum(qty) as '总定购量' from sales group by stor_id
356
357 -- 在销售表中查询每个商店定购了那几本书以及每本书相应的订购量
358 select stor_id as '商店编号', title_id as '书籍编号', sum(qty) as '总定购量' from sales group by stor_id, title_id order by stor_id
359
360
361
362 -- 查询指定出版社出版的书
363 select p.pub_name as '出版社', t.title as '书籍'
364 from publishers as p join titles as t on p.pub_id = t.pub_id
365 where pub_name = 'New Moon Books'
366
367 -- 查询指定出版社出版的书籍的总数目
368 select p.pub_name as '出版社', count(t.title) as '书籍数目'
369 from publishers as p join titles as t on p.pub_id = t.pub_id
370 group by p.pub_name
371
372 -- 统计每个作者的版权税总和
373 select au_id as '作者编号', sum(royaltyper) from titleauthor group by au_id
374
375
376
377 select * from authors
378 select * from titles
379 select * from publishers
380
381 select * from publishers where = 'New Moon Books'
382 select * from titles where pub_id = '0736'
383
384 -- 子查询
385 -- 根据出版社的名称查询其出版的书籍
386 -- 首先,根据出版社的名称在publisher中找到相应的出版社编号
387 -- 然后,在根据出版社编号在titles中找到相应的书籍信息
388 select * from titles
389 where pub_id =
390 (select pub_id from publishers
391 where pub_name = 'New Moon Books')
392
393
394 select title_id, type, price from titles
395
396 select title_id, type, price
397 from titles
398 order by price desc
399
400
401 select top 4 title_id, type, price
402 from titles
403 order by price desc
404
405
406 -- 子查询
407 -- 找寻最贵的书籍
408 -- 先通过聚合函数获取最高价格
409 -- 然后,将其作为查询条件,找出相应的书籍
410 select title_id, type, price from titles
411 where price = (select max(price) from titles)
412
413 select au_id,au_lname from authors where au_lname = 'Green'
414
415 -- 子查询
416 -- 根据作者的名查找其编写的书籍
417 -- 先通过子查询获取作者编号
418 -- 然后,将其作为查询条件,找出相应的书籍编号
419 -- 最后,在利用所得到的书籍编号来得到书籍信息
420 select au_id, title_id from titleauthor
421 where au_id =
422 (select au_id from authors where au_lname = 'Green')
423
424 select * from titles
425 where title_id in
426 (
427 select title_id from titleauthor
428 where au_id =
429 (select au_id from authors where au_lname = 'Green')
430 )
431
432
433
434 -- 打印输出
435 -- Print
436 print 'Hello world'
437
438 -- 获取系统时间
439 print getdate()
440
441 -- 获取3天前的时间
442 print dateadd(day, -3 , getdate())
443 -- 获取3天后的时间
444 print dateadd(day, 3 , getdate())
445 -- 获取3年前的时间
446 print dateadd(year, -3 , getdate())
447 -- 获取3年后的时间
448 print dateadd(year, 3 , getdate())
449
450 -- 获取3月后的时间
451 print dateadd(month, 3 , getdate())
452 -- 获取9小时后的时间
453 print dateadd(hour, 9 , getdate())
454 -- 获取9分钟后的时间
455 print dateadd(minute, 9 , getdate())
456
457 -- 获取指定时间之间相隔多少年
458 print datediff(year, '2005-01-01', '2008-01-01')
459 -- 获取指定时间之间相隔多少月
460 print datediff(month, '2005-01-01', '2008-01-01')
461 -- 获取指定时间之间相隔多少天
462 print datediff(day, '2005-01-01', '2008-01-01')
463
464 -- 获取给定字符串的长度
465 print len('abcdef')
466 -- 字符串合并
467 print 'abc' + 'def'
468
469 print 'abcder'
470
471 print 'abc' + '456'
472 print 'abc' + 456
473 -- 类型转换
474 print 'abc' + convert(varchar(10), 456)
475
476 print '123' + '456'
477 print '123' + 456
478 print 123 + '456'
479
480
481 -- 利用系统函数作为默认值约束
482 drop table ttt
483
484 create table ttt
485 (
486 stu_name varchar(12),
487 stu_birthday datetime default (getdate())
488 )
489
490 alter table ttt
491 add constraint df_ttt_stu_birthday default (getdate()) for stu_birthday
492
493 insert into ttt values ('ANiu', '2005-04-01')
494 insert into ttt values ('ANiu', getdate())
495
496 insert into ttt values ('AZhu', default)
497
498 sp_help ttt
499
500 select * from ttt
501
502 -- 计算列
503 create table scores
504 (
505 chinese int,
506 english int,
507 total int
508 )
509
510 insert into scores values (80, 90, 170)
511
512 select * from scores
513
514 drop table scores
515
516 create table scores
517 (
518 chinese int,
519 english int,
520 total as chinese + english -- 计算列
521 )
522
523 insert into scores values (80, 90)
524
525 -- 故意添加,结果出错
526 insert into scores values (80, 90, 250)
527
528 select * from scores