SQL基础用法(实例一)

  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