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 students103 (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_no117 118 ALTER TABLE students119 ADD CONSTRAINT pk_students_no PRIMARY KEY (no)120 121 ALTER TABLE students122 ADD CONSTRAINT uq_students_name UNIQUE (name)123 124 ALTER TABLE students125 ADD CONSTRAINT ck_students_birthday CHECK (datediff(year,[birthday],getdate()) >= 18)126 127 ALTER TABLE students128 ADD CONSTRAINT df_students_sex default (‘女‘) for sex129 130 ALTER TABLE students131 ADD CONSTRAINT ck_students_sex CHECK ([sex] = ‘男‘ or [sex] = ‘女‘)132 133 ALTER TABLE students134 ADD CONSTRAINT ck_students_phone CHECK ([phone] is null or len([phone]) = 11)135 136 相对应的对约束进行删除，则是通过DROP CONSTRAINT子句来完成：137 ALTER TABLE students138 DROP CONSTRAINT pk_students_no139 140 ALTER TABLE students141 DROP CONSTRAINT uq_students_name142 143 注意：144 约束只有添加与删除操作，没有修改操作！145 146 147 注意：148 其中，age(年龄)采用了“计算列”的表示方法！149 “计算列”的定义：150 在表中某个字段的值源于某一表达式的值（某一函数的运算结果或是其他字段的运算结果）！151 比如：152 某学生的成绩表结构如下：153 数学154 语文155 体育156 总分157 158 创建命令如下：159 create table scores160 (161 math      numeric(3, 1),162 chinese  numeric(3, 1),163 sport     numeric(3, 1),164 total      as math + Chinese + sport165 )166 167 insert into scores values (80, 69, 95)168 169 total 部分的值会自动计算，为 244170 171 -- 创建临时表172 -- 临时表将会存储在TempDB的临时数据库中173 -- 当用户断开连接后，就会自动删除174 -- 语法：在表名前加上#175 create table #tt176 (177 a int,178 b int179 )180 181 insert into #tt values (1,1)182 insert into #tt values (2,2)183 184 select * from #tt185 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 price269 270 (注意：默认数据排序采用升序：由低到高或是有小到大，可以通过设定关键字来调整)271 272 -- 根据价格对书籍表记录排序(降序)273 select * from titles order by price desc274 275 -- 根据价格对书籍表记录排序(升序)276 select * from titles order by price asc277 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 desc286 287 select * from titles288 select * from jobs289 -- 290 291 -- 统计书籍表中每种类型的书籍总量292 select type as ‘书籍类型‘, count(title_id) ‘书籍数量‘ 293 from titles group by type294 295 select type, title_id from titles order by type296 297 -- 统计书籍表中每种类型的书籍的预付款(advance)的总和298 select type as ‘书籍类型‘, sum(advance) ‘预付款总和‘ 299 from titles group by type300 301 -- 列出所有书籍类型302 select type as ‘书籍类型‘ from titles303 select distinct type as ‘书籍类型‘ from titles304 select distinct type as ‘书籍类型‘, title_id from titles305 306 -- 列出所有作者所在州307 select distinct state as ‘州‘ from authors308 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 authors316 select * from titles317 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_id320     join titles as t on ta.title_id = t.title_id321 where a.au_lname = ‘Green‘ and a.au_fname = ‘Marjorie‘322 323 324 -- 根据给定的出版社名称列出其所有的出版书籍名称325 select * from publishers326 327 select p.pub_name as ‘出版社‘, t.title as ‘书籍名称‘328 from publishers as p join titles as t on p.pub_id = t.pub_id329 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_id333 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_id349 350 351 -- 在销售表中统计每本书的总订购量352 select title_id as ‘书籍编号‘, sum(qty) as ‘总定购量‘  from sales group by title_id353 354 -- 在销售表中统计每个商店的总订购量355 select stor_id as ‘商店编号‘, sum(qty) as ‘总定购量‘  from sales group by stor_id356 357 -- 在销售表中查询每个商店定购了那几本书以及每本书相应的订购量358 select stor_id as ‘商店编号‘, title_id as ‘书籍编号‘, sum(qty) as ‘总定购量‘  from sales group by stor_id, title_id order by stor_id359 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_id365 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_id370 group by p.pub_name371 372 -- 统计每个作者的版权税总和373 select au_id as ‘作者编号‘, sum(royaltyper)  from titleauthor group by au_id374 375 376 377 select * from authors378 select * from titles379 select * from publishers380 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 titles395 396 select title_id, type, price 397 from titles 398 order by price desc399 400 401 select top 4 title_id, type, price 402 from titles 403 order by price desc404 405 406 -- 子查询407 -- 找寻最贵的书籍408 -- 先通过聚合函数获取最高价格409 -- 然后，将其作为查询条件，找出相应的书籍410 select title_id, type, price from titles411 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 titleauthor421 where au_id = 422     (select au_id from authors where au_lname = ‘Green‘)423 424 select * from titles425 where title_id in 426     (427     select title_id from titleauthor428         where au_id = 429         (select au_id from authors where au_lname = ‘Green‘)430     )431 432 433 434 -- 打印输出435 -- Print436 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‘ + 456473 -- 类型转换474 print ‘abc‘ + convert(varchar(10), 456)475 476 print ‘123‘ + ‘456‘477 print ‘123‘ + 456478 print 123 + ‘456‘479 480 481 -- 利用系统函数作为默认值约束482 drop table ttt483 484 create table ttt485 (486 stu_name    varchar(12),487 stu_birthday    datetime default (getdate())488 )489 490 alter table ttt491 add constraint df_ttt_stu_birthday default  (getdate()) for stu_birthday492 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 ttt499 500 select * from ttt501 502 -- 计算列503 create table scores504 (505 chinese int,506 english int,507 total int508 )509 510 insert into scores values (80, 90, 170)511 512 select * from scores513 514 drop table scores515 516 create table scores517 (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