1 create database cookbbook 2 on primary( 3 name='cookbook_mdf', 4 filename='F:\我的数据库\cookbook\cookbook.mdf', 5 size=5mb, 6 maxsize=50mb, 7 filegrowth=2mb 8 9 ) 10 log on 11 (name='cookbok_ldf', 12 filename='F:\我的数据库\cookbook\cookbook.ldf', 13 size=5mb, 14 maxsize=50mb, 15 filegrowth=2mb 16 ) 17 18 create database school 19 on primary 20 (name='school_mdf', 21 filename='F:\我的数据库\school\school.mdf', 22 size=2mb, 23 maxsize=5mb, 24 filegrowth=1mb 25 ) 26 log on 27 (name='school_ldf', 28 filename='F:\我的数据库\school\school.ldf', 29 size=2mb, 30 maxsize=5mb, 31 filegrowth=1mb 32 33 ) 34 ----------------------------------------------------------------------------- 35 36 37 38 create database orderdb 39 create table employee 40 ( 41 employeeno varchar(8) primary key, 42 employeename varchar(10), 43 sex varchar(2), 44 birthday datetime, 45 address varchar(50), 46 telephone varchar(20), 47 hiredate datetime, 48 department varchar(30), 49 headship varchar(10), 50 salary numeric(8,2) 51 ) 52 --------------------------- 53 create table customer 54 ( 55 customerno varchar(9) primary key, 56 customername varchar(40), 57 telephone varchar(20), 58 address varchar(40), 59 zip varchar(6) 60 ) 61 --------------------------- 62 create table product 63 ( 64 productno varchar(9) primary key, 65 productname varchar(40), 66 productclass varchar(20), 67 productprice numeric(7,2), 68 ) 69 --------------------------- 70 create table ordermaster 71 ( 72 orderno varchar(12) primary key, 73 customerno varchar(9), 74 salerno varchar(8), 75 orderdate datetime, 76 ordersum numeric(9,2), 77 invoiceno char(10), 78 foreign key (customerno) references customer(customerno) 79 ) 80 ----------------------------- 81 create table orderdetail 82 ( 83 orderno varchar(12), 84 productno varchar(9), 85 quantity int, 86 price numeric(7,2), 87 primary key(orderno,productno), 88 foreign key (orderno) references ordermaster(orderno), 89 foreign key (productno) references product(productno) 90 ) 91 -------------------------------------------------------- 92 93 bulk insert employee from 'F:\数据库\orderdb数据库创建\employee.txt' 94 with 95 ( 96 fieldterminator = ',', 97 rowterminator='\n' 98 ) 99 100 bulk insert customer from 'F:\数据库\orderdb数据库创建\customer.txt' 101 with 102 ( 103 fieldterminator = ',', 104 rowterminator='\n' 105 ) 106 107 bulk insert product from 'F:\数据库\orderdb数据库创建\product.txt' 108 with 109 ( 110 fieldterminator = ',', 111 rowterminator='\n' 112 ) 113 114 bulk insert ordermaster from 'F:\数据库\orderdb数据库创建\ordermaster.txt' 115 with 116 ( 117 fieldterminator = ',', 118 rowterminator='\n' 119 ) 120 121 bulk insert orderdetail from 'F:\数据库\orderdb数据库创建\orderdetail.txt' 122 with 123 ( 124 fieldterminator = ',', 125 rowterminator='\n' 126 ) 127 128 129 130 131 132 133 134 135 136 137 138 select *from employee 139 select * from ordermaster 140 select * from orderdetail 141 142 143 144 create view employee_ordermaster 145 as 146 select salerno ,employeename,ordermaster.orderno,orderdetail.productno,price 147 from employee left outer join ordermaster 148 on (employee.employeeno=ordermaster.salerno) 149 ,orderdetail 150 where ordermaster.orderno=orderdetail.orderno 151 152 select *from employee_ordermaster 153 154 update employee_ordermaster 155 set price=600 156 where orderno='200801090001' and productno='p2005001' 157 158 insert into 159 employee_ordermaster 160 values('e2000','林东豪','asdasdasd','asdsadas',234234) 161 162 drop view employee_ordermaster 163 --第四章 简单查询 164 --（1）查询所有业务部门的员工姓名，职称，薪水 165 select department,employeename,headship,salary 166 from employee 167 order by department,headship 168 --（2）查询名字中含有“有限”的客户姓名和所在地 169 select customername,address 170 from customer 171 where customername like '%有限%' 172 173 --（3）查询姓“张”并且姓名的最后一个字为“娟”的员工 174 select employeename 175 from employee 176 where employeename like '张%娟' 177 178 --（4）查询住址中含有上海或南昌的女员工，并显示其姓名， 179 -- 所属部门，职称，住址，其中性别用“男”和“女”显示。 180 select employeename,(case sex when 'F' then '女' when 'M' then '男'end) sex,department,headship,address 181 from employee 182 where address='上海市' or address='南昌市' and sex='M' 183 order by address 184 185 --（5）查询订单金额高于8000的所有客户编号 186 update ordermaster 187 set ordersum=(select sum(quantity* price ) 188 from orderdetail 189 where ordermaster.orderno=orderdetail.orderno) 190 select * from ordermaster 191 192 select customerno,sum(ordersum) sumorder 193 from orderdetail,ordermaster 194 group by customerno 195 having sum(ordersum)>8000 196 197 --（6）选取编号界于C2005001~C2005003的客户编号，客户名称，客户地址 198 select *from customer 199 select customerno,customername,address 200 from customer 201 where customerno between 'c2005001' and 'c2005003' 202 203 204 --（7）找出同一天进入公司服务的员工 205 select distinct a.employeename,a.hiredate 206 from employee a,employee b 207 where a.hiredate=b.hiredate and a.employeename!=b.employeename 208 order by a.hiredate 209 210 211 212 update orderdetail 213 set price=100 214 where orderno='200801090001' 215 select * from orderdetail 216 select * from ordermaster 217 select *from product 218 219 update orderdetail 220 set productno='p2007002' 221 where orderno='200801090001' and quantity=2 222 223 --（8）在订单主表中查询订单金额大于“E2005002”业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 224 225 select * 226 from ordermaster om,orderdetail od 227 where om.orderno=od.orderno and om.orderno in 228 (select orderno 229 from orderdetail 230 where price >all 231 (select price 232 from orderdetail ,ordermaster 233 where orderdetail.orderno=ordermaster.orderno 234 and salerno='E2005002' 235 and orderdate='2008-01-09' 236 ) 237 ) 238 239 240 241 --（9）查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。 242 243 select distinct customerno,ol.orderno,ol.price 244 from orderdetail ol ,ordermaster ot 245 where ol.orderno=ot.orderno and 246 ol.orderno in 247 (select orderno 248 from orderdetail od,product pt 249 where od.productno=pt.productno and pt.productname='52倍速光驱' 250 251 intersect 252 253 select orderno 254 from orderdetail od,product pt 255 where od.productno=pt.productno and pt.productname='17寸显示器') 256 257 258 --（10）查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。 259 select employeename,sex,department,headship 260 from employee 261 where department= 262 (select department 263 from employee 264 where employeename='陈诗杰' 265 )and employeename!='陈诗杰' 266 267 select e1.employeename,e1.sex,e1.department,e1.headship 268 from employee e1,employee e2 269 where e1.department=e2.department and e2.employeename='陈诗杰' 270 271 --（11）查询单价高于400元的商品编号、商品名称、订货数量和订货单价。 272 select pt.productno,pt.productname,sum(od.quantity) quantity,pt.productprice 273 from orderdetail od,product pt 274 where od.productno=pt.productno and pt.productprice>400 275 group by pt.productno,productname,productprice 276 277 278 --（12）分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。 279 280 select pt.productno,pt.productname,od.quantity ,pt.productprice 281 from orderdetail od left outer join product pt on ( od.productno=pt.productno ) 282 where pt.productprice>400 283 284 select pt.productno,pt.productname,od.quantity,pt.productprice 285 from orderdetail od right outer join product pt on ( od.productno=pt.productno ) 286 where pt.productprice>400 287 288 select pt.productno,pt.productname,od.quantity ,pt.productprice 289 from orderdetail od full outer join product pt on ( od.productno=pt.productno ) 290 where pt.productprice>400 291 292 293 ---（13）查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期， 294 --其中性别使用“男”和“女”表示，日期使用yyyy-mm-dd格式显示。 295 296 update ordermaster 297 set ordersum=temp.total 298 from ordermaster om,(select orderno,SUM(quantity*price) total from orderdetail group by orderno) temp 299 where om.orderno=temp.orderno 300 301 select om.orderno,om.orderno,om.salerno,em.employeename,(case sex when 'F' then '男' when 'M' then '女' end)'性别',pt.productno,od.quantity,pt.productprice,od.price,om.ordersum,CONVERT(varchar(100),om.orderdate,23)'orderdate' 302 from employee em,ordermaster om, orderdetail od,product pt 303 where em.employeeno=om.salerno and om.orderno=od.orderno and od.productno=pt.productno 304 select*from employee 305 select* from orderdetail 306 select* from customer 307 select* from ordermaster 308 select* from product 309 310 insert into customer 311 values('c2005004','207工商协会','022-324234','福州市','21233') 312 313 --（14）查找在2008年3月中有销售记录的客户编号、名称和订单总额。 314 select temp.customerno, customer.customername,temp.ordersum 315 from 316 (select customerno,sum(ordersum ) ordersum 317 from ordermaster 318 where orderdate between '2008-03-01' and '2008-03-31' 319 group by customerno) temp, customer 320 where temp.customerno=customer.customerno 321 --（15）使用左外连接查找每个客户的客户编号、名称、订单日期、订货金额， 322 --其中订货日期不要显示时间，日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出。 323 324 select cu.customerno,cu.customername,convert(varchar(100),om.orderdate,23) orderdate,om.ordersum 325 from (select customerno,customername 326 from customer) cu 327 left outer join 328 (select customerno,ordersum,orderdate 329 from ordermaster 330 ) om 331 on(om.customerno=cu.customerno) 332 order by cu.customerno,om.ordersum desc 333 334 --（16）查找32M DRAM的销售情况，要求显示相应的销售员的姓名，性别，销售日期、销售数量和金额，其中性别用“男”，“女”表示。 335 select oe.employeename,oe.sex,oe.orderdate,op.quantity,op.price 336 from (select orderno,quantity,price 337 from orderdetail,product 338 where product.productno=orderdetail.productno and productname='32M DRAM') op, 339 340 (select orderno,employeename,(case sex when 'F' then '女' when 'M' then '男' end) sex, orderdate 341 from ordermaster,employee 342 where ordermaster.salerno=employee.employeeno 343 ) oe 344 where op.orderno=oe.orderno 345 346 --（17）查找公司男业务员所接且订单金额超过2000元的订单号及订单金额。 347 select em.employeename,sum(od.price) sumprice 348 from(select orderno,employeeno,employeename 349 from employee,ordermaster 350 where sex='M' and employeeno=salerno) em, 351 (select orderno,price 352 from orderdetail 353 ) od 354 where em.orderno=od.orderno 355 group by em.employeename 356 having sum(od.price)>2000 357 358 --（18）查找来自上海市的客户的姓名，电话，订单号及订单金额。 359 select customername,telephone,orderno,sum(md.price)sumprice 360 from (select customerno,customername,telephone 361 from customer 362 where address='上海市' 363 ) cu, 364 (select om.customerno,om.orderno, od.price 365 from ordermaster om,orderdetail od 366 where om.orderno=od.orderno 367 ) md 368 where cu.customerno=md.customerno 369 group by customername,telephone,orderno 370 order by customername 371 -------------------------------------------------------------------------------------------- 372 -------------------------------------------------------------------------------------------- 373 374 --第五章 实验 复杂查询 375 select *from ordermaster 376 update ordermaster 377 set ordersum=(select sum(quantity*price) 378 from orderdetail od 379 where ordermaster.orderno=od.orderno 380 ) 381 update ordermaster 382 set ordersum=temp.total 383 from ordermaster om, 384 (select sum(quantity*price)total,orderno 385 from orderdetail 386 group by orderno) temp 387 where om.orderno=temp.orderno 388 --（1）用子查询查询员工“张小娟”所做的订单信息。 389 --1)第一种 390 select '张小娟',om.orderno, om.orderdate,om.ordersum,om.customerno 391 from ordermaster om,employee em 392 where em.employeeno=om.salerno and em.employeename='张小娟' 393 394 --2）第二种 395 396 select '张小娟',om.orderno,om.orderdate,om.ordersum,om.customerno 397 from ordermaster om 398 where salerno=(select employeeno 399 from employee 400 where employeename='张小娟') 401 402 403 404 --（2）查询没有订购商品的且在北京地区的客户编号，客户名称和邮政编码，并按邮政编码降序排序。 405 select customerno,customername,zip 406 from customer cu 407 where address='北京市' and not exists (select * 408 from ordermaster om 409 where cu.customerno=om.customerno) 410 411 412 --（3）查询订购了“32M DRAM”商品的订单编号，订货数量和订货单价。 413 select orderno,quantity,price 414 from orderdetail 415 where orderno in(select orderno 416 from ordermaster 417 where productno=(select productno from product where productname='32M DRAM') 418 ) 419 420 --（4）查询与员工编号“E2008005”在同一个部门的员工编号，姓名，性别，所属部门。 421 select employeeno,employeename ,(case sex when 'F' then '男' when 'M' then '女' end) sex,department 422 from employee 423 where department=(select department from employee where employeeno='E2008005') 424 425 --（5）查询既订购了P2005001商品，又订购了P2007002商品的客户编号，订单编号和订单金额 426 select * from orderdetail 427 select * from ordermaster 428 update orderdetail 429 set productno='p2007002' 430 where orderno='200801090001' and productno='p2005003' 431 432 select om.customerno,o1.orderno,o1.price 433 from orderdetail as o1,ordermaster as om 434 where o1.productno='P2005001' 435 and o1.orderno in 436 (select orderno 437 from orderdetail o2 438 where o2.productno='p2007002') 439 and o1.orderno=om.orderno 440 441 442 --（6）查询没有订购“52倍速光驱”或“17寸显示器”的客户编号，客户名称。 443 444 select cu.customerno,cu.customername 445 from ordermaster om ,customer cu 446 where orderno in 447 (select orderno 448 from orderdetail 449 where productno not in 450 (select productno 451 from product 452 where productname='52倍速光驱' or productname='17寸显示器') 453 ) and om.customerno=cu.customerno 454 455 --（7）查询订单金额最高的订单编号，客户姓名，销售员名称和相应的订单金额。 456 select om.orderno,cu.customername,em.employeename,om.ordersum 457 from ( 458 select distinct orderno, ordersum,salerno ,customerno 459 from ordermaster 460 where ordersum =(select max(ordersum) from ordermaster) 461 ) om, 462 employee em,customer cu 463 where om.customerno=cu.customerno and om.salerno=em.employeeno 464 465 --（8）查询订购了“52倍速光驱”商品的订购数量，订购平均价和订购总金额。 466 467 select '52倍速光驱',sum(quantity) quantity, avg(price) , sum(price) 468 from orderdetail 469 where productno=(select productno from product where productname='52倍速光驱') 470 471 472 --（9）查询订购了“52倍速光驱”商品且订货数量界于2~4之间的订单编号，订货数量和订货金额。 473 select orderno,quantity,price 474 from orderdetail 475 where productno=(select productno from product where productname='52倍速光驱') 476 and quantity between 2 and 4 477 478 --（10）在订单主表中查询每个业务员的订单数量 479 select salerno,count(orderno) ordercount 480 from ordermaster 481 group by salerno 482 483 --（11）统计在业务科工作且在1973年或1967年出生的员工人数和平均工资。 484 select * from employee 485 select employeeno,salary 486 from employee 487 where department='业务科' and year(birthday)=1973 or year(birthday)=1967 488 489 --（12）在订单明细表中统计每种商品的销售数量和金额，并按销售金额的升序排序输出。 490 select * from orderdetail 491 select productname,sum(quantity) quantity , sum(price) price 492 from (select productname,productno 493 from product) pt,orderdetail 494 where orderdetail.productno=pt.productno 495 group by productname 496 order by price 497 498 --（13）统计客户号为“C2005001”的客户的订单数，订货总额和平均订货金额 499 select * from ordermaster 500 501 --第一种 由于orderno有主键约束，ordersum不会存在重复计算问题 502 select 'c2005001' ,count(orderno),sum(ordersum),avg(ordersum) 503 from ordermaster 504 where customerno='c2005001' 505 506 507 --第二种 如果ordeno没有主键约束，ordersum可能存在重复计算问题 508 509 select count(temp.orderno),sum(temp.ordersum) 510 from (select orderno,sum(ordersum) ordersum 511 from ordermaster 512 where customerno='c2005001' 513 group by orderno) temp 514 --（14）统计每个客户的订单数，订货总额和平均订货金额。 515 select customername,temp.countorder,temp.sos ordersum,temp.aos averageOrdersum 516 from (select customerno ,count(orderno) countorder,sum(ordersum) sos,avg(ordersum) aos 517 from ordermaster 518 group by customerno) temp,customer 519 where customer.customerno=temp.customerno 520 521 522 523 --（15）查询订单中至少包含3种（含3种）以上商品的订单编号及订购次数，且订购的商品数量在3件（含3件）以上。 524 select * from orderdetail 525 select *from ordermaster 526 527 select orderno,count(distinct orderdate) 528 from ordermaster 529 where orderno in(select orderno 530 from orderdetail 531 group by orderno 532 having count(productno)>=3 and sum(quantity)>=3 533 ) 534 group by orderno 535 536 537 538 539 540 --（16）查找订购了“32M DRAM”的商品的客户编号，客户名称，订货总数量和订货总金额。 541 542 select cu.customerno,cu.customername,sum(temp.quantity) sumquantity,sum(temp.price) sumprice 543 from customer cu,ordermaster , 544 (select orderno ,quantity,price 545 from orderdetail 546 where productno in 547 (select productno 548 from product 549 where productname='32M DRAM') 550 ) temp 551 where temp.orderno=ordermaster.orderno and ordermaster.customerno=cu.customerno 552 group by cu.customerno,customername 553 554 555 556 --（17）查询每个客户订购的商品编号，商品所属类别，商品数量及订货金额，结果显示客户名称，商品所属类别，商品数量及订货金额，并按客户编号升序和按订货金额的降序排序输出。 557 --第一种：表连接 558 select cu.customername ,pt.productname ,pt.productclass ,od.quantity,od.price 559 from customer cu,product pt,orderdetail od,ordermaster om 560 where cu.customerno=om.customerno 561 and om.orderno=od.orderno 562 and od.productno=pt.productno 563 564 --第一种同一公司订购的同一商品没有整合 565 select cu.customerno,cu.customername,pt.productname,pt.productclass,od.quantity,od.price 566 from (select productno,productname 567 from product) pt,orderdetail od, ordermaster om,customer cu 568 where pt.productno=od.productno and od.orderno=om.orderno and om.customerno=cu.customerno 569 order by cu.customerno ,od.price 570 --第二种同一公司订购的同一商品进行了整合 571 select cu.customerno,cu.customername,pt.productname,pt.productclass,sum(od.quantity)sumquantity ,sum(od.price) sumprice 572 from (select productno,productname,productclass 573 from product) pt,orderdetail od, ordermaster om,customer cu 574 where pt.productno=od.productno and od.orderno=om.orderno and om.customerno=cu.customerno 575 group by cu.customerno,cu.customername ,pt.productname,pt.productclass 576 order by cu.customerno ,sumprice 577 578 579 --（18）按商品类别查询每类商品的订货平均单价在280元（含280元）以上的订货总数量，订货平均单价和订货总金额。 580 select pt.productclass ,SUM(quantity) sumquantity,AVG(price) avgprice,sum(price) sumprice 581 from orderdetail od, product pt 582 where od.productno=pt.productno 583 group by pt.productclass 584 585 586 587 --（19）查找至少有2次销售的业务员名称和销售日期。 588 589 select em.employeename, om.orderdate 590 from ordermaster om, employee em 591 where em.employeeno in 592 (select salerno 593 from ordermaster 594 group by salerno 595 having COUNT(salerno)>=2)and om.salerno=em.employeeno 596 order by employeename 597 598 --（20）查询销售金额最大的客户名称和总货款额 599 update ordermaster 600 set ordersum=(select SUM(quantity*price) 601 from orderdetail 602 where ordermaster.orderno=orderdetail.orderno 603 604 select cu.customername,sumprice 605 from (select om.customerno,sum(om.ordersum) sumprice 606 from ordermaster om 607 group by customerno 608 )temp,customer cu 609 where sumprice>=all(select sum(om.ordersum) sumprice 610 from ordermaster om 611 group by customerno) 612 and cu.customerno=temp.customerno 613 614 --（21）查找销售总额小于5000元的销售员编号，姓名和销售额 615 select em.employeeno,em.employeename,om.sumorder 616 from (select salerno,sum(ordersum) sumorder 617 from ordermaster 618 group by salerno) om,employee em 619 where om.sumorder<5000 and om.salerno=em.employeeno 620 621 --（22）查找至少订购了3种商品的客户编号，客户名称，商品编号，商品名称，数量和金额。 622 623 624 select cu.customerno,cu.customername,pt.productno,pt.productname,od.quantity,od.price 625 from customer cu, ordermaster om,orderdetail od,product pt 626 where cu.customerno=om.customerno and om.orderno=od.orderno and od.productno=pt.productno 627 and cu.customerno in(select om.customerno 628 from ordermaster om,orderdetail od 629 where om.orderno=od.orderno 630 group by om.customerno 631 having count(distinct od.productno)>=3) 632 order by cu.customerno 633 634 635 --（23）查找同时订购了商品为“P2007002”和商品编号为“P2007001”的商品的客户编号，客户姓名， 636 --商品编号，商品名称和销售数量，按客户编号排序输出。 637 638 select cu.customerno,cu.customername,pt.productno,pt.productname, sum(od.quantity) quantity 639 from customer cu,ordermaster om,product pt, 640 (select orderno,productno,quantity 641 from orderdetail 642 where productno='p2007002' and orderno 643 in(select orderno 644 from orderdetail 645 where productno='p2007001' ) 646 ) od 647 where cu.customerno=om.customerno and om.orderno =od.orderno and od.productno=od.productno 648 group by cu.customerno,cu.customername,pt.productno,pt.productname 649 order by customerno 650 651 --（24）计算每一商品每月的销售金额总和，并将结果首先按销售月份然后按订货金额降序排序输出。 652 select pt.productname,dm.sumprice,dm.orderdate 653 from product pt, 654 (select productno,sum(price) sumprice,om.orderdate 655 from orderdetail od,ordermaster om 656 where od.orderno=od.orderno 657 group by productno,om.orderdate) dm 658 where pt.productno=dm.productno 659 order by dm.orderdate ,dm.sumprice desc 660 --（25）查询订购了“键盘”商品的客户姓名，订货数量和订货日期 661 select cu.customername,op.quantity,om.orderdate 662 from (select orderno,quantity 663 from orderdetail 664 where productno in 665 (select productno from product where productname='键盘')) op, 666 customer cu,ordermaster om 667 where cu.customerno=om.customerno and om.orderno=op.orderno 668 669 --（26）查询每月订购“键盘”商品的客户名称。 670 select month(om.orderdate) monthorder,cu.customername 671 from ordermaster om,customer cu, 672 (select od.orderno,od.productno 673 from orderdetail od, product pt 674 where od.productno=pt.productno and pt.productname='键盘') dp 675 where dp.orderno=om.orderno and cu.customerno=om.customerno 676 group by month(om.orderdate),cu.customername 677 order by monthorder 678 --（27）查询至少销售了5种商品的销售员编号，姓名，商品名称，数量及相应的单价，并按销售员编号排序输出。 679 select om.salerno,em.employeename,pt.productname,sum(od.quantity)quantity,pt.productprice 680 from employee em,product pt ,orderdetail od,ordermaster om 681 where em.employeeno=om.salerno and om.orderno=od.orderno and od.productno=pt.productno 682 and om.salerno in(select om.salerno 683 from ordermaster om,orderdetail od 684 where om.orderno=od.orderno 685 group by om.salerno 686 having count(distinct productno)>=5) 687 group by om.salerno,em.employeename,pt.productprice,pt.productname 688 order by salerno 689 --（28）查询没有订购商品的客户编号和客户名称。 690 select customerno ,customername 691 from customer 692 where customerno not in(select customerno from ordermaster) 693 --（29）查询至少包含了“世界技术开发公司”所订购的商品的客户编号，客户名称，商品编号，商品名称，数量和金额。 694 select cu1.customerno,cu1.customername,od1.productno,pt1.productname,od1.quantity,od1.price 695 from customer cu1,ordermaster om1,orderdetail od1,product pt1 696 where cu1.customerno=om1.customerno and om1.orderno=od1.orderno and od1.productno=pt1.productno 697 and not exists 698 (select * 699 from customer cu2,ordermaster om2,orderdetail od2 700 where cu2.customerno=om2.customerno and om2.orderno=od2.orderno and 701 cu2.customername='世界技术开发公司' 702 and not exists 703 ( select * 704 from ordermaster om3,orderdetail od3 705 where om3.orderno=od3.orderno 706 and od3.productno=od2.productno 707 and om3.customerno=cu1.customerno 708 ) 709 710 ) 711 order by cu1.customerno 712 ------------------------------------------------------------------------------------------------- 713 ------------------------------------------------------------------------------------------------- 714 715 --第六章 数据库的安全性 716 --（1）分别创建登陆账号和用户账号john，mary（注意服务器角色的设置） 717 sp_addlogin 'john','123','orderdb' 718 sp_addlogin 'mary','123','orderdb' 719 sp_adduser 'john' 720 sp_adduser 'mary' 721 --（2）将员工表的所有权限给全部用户 722 723 grant select,update,delete 724 on employee 725 to john,mary 726 --（3）创建角色r1，r2，将订单明细表所有列的select权限，price列的update权限给r1。 727 sp_addrole 'r1' 728 sp_addrole 'r2' 729 grant select,update(price) 730 on orderdetail 731 to r1,r2 732 --（4）收回全部用户对员工表的所有权限。 733 revoke select 734 on employee 735 from john,mary 736 --（5）将john，mary两个用户赋予r1角色。 737 sp_addrolemember 'r1','john' 738 --（6）收回john对订单明细表所有列的select权限。 739 revoke select 740 on orderdetail 741 from john 742 --（7）在当前数据库中删除角色r2。 743 sp_droprole 'r2' 744 ------------------------------------------------------------------------------- 745 ------------------------------------------------------------------------------- 746 --第七章 数据库的完整性 747 --（1）重建orderDB数据库中的表，分别为每张表建立主键，外键。 748 --（2）各表的用户定义的完整性如下： 749 create database oredrDB 750 --员工表：员工编号，姓名、性别、所属部门、职称、薪水设为not null； 751 --员工编号构成：年流水号，共8位，第一位为E，如E2008001，年份取雇佣日期的年份； 752 --性别：f表示女，m表示男。 753 --创建员工表 754 create table Employee 755 ( employeeNo char(8) primary key, 756 employeeName varchar(20), 757 constraint E_NO check(employeeNo like 'E[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 758 Sex varchar(10) check(sex='f' or sex='m'), 759 Department varchar(20), 760 Title varchar(10), 761 Salary numeric(10,2) not null , 762 Hiredate datetime, 763 constraint E_Hire_NO check(substring(employeeNo,2,4)=year(Hiredate)) 764 ) 765 --添加员工记录 766 insert 767 into employee(employeename,sex,department,Title,salary,hiredate) 768 select '王帅','m','销售部','主管',1200,getdate() union all 769 select '赵少帅','m','娱乐部','部长',200000,getdate() 770 --添加员工流水号触发器 771 create trigger T_E_employeeNo 772 on employee 773 instead of insert 774 as 775 declare @head varchar,@datebody varchar(10),@maxid varchar(10) 776 set @head='E' 777 set @datebody=year(getdate()) 778 select @maxid=right(max(employeeNo),3) from employee 779 if @maxid is null 780 set @maxid='000' 781 declare @temp int 782 set @temp=cast(@maxid as int) 783 select * into #temp from inserted 784 update #temp set @temp=@temp+1, 785 employeeno=@head+@datebody+right(('00'+cast(@temp as varchar)),3) 786 insert into employee select *from #temp 787 788 789 790 791 792 --商品表：商品编号、商品名称、商品类别、建立日期设为not null； 793 --商品编号构成：年流水号，共9位，第一位为P，如P20080001，年份取建立日期的年份 794 --创建商品表 795 create table Product 796 ( productNo char(9) primary key, 797 constraint P_No check(productNo like 'P[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 798 productName varchar(20), 799 productType varchar(20), 800 productDate date not null, 801 constraint P_Date_No check(substring(productNo,2,4)=year(productDate)) 802 ) 803 --添加商品记录 804 insert into product(productname,productdate,producttype) 805 select '内存',getdate(),'硬件' union all 806 select '显卡',getdate(),'硬件' 807 --添加商品流水号触发器 808 create trigger T_P_productNo 809 on product 810 instead of insert 811 as 812 declare @head varchar,@datebody varchar(10),@maxid varchar(10) 813 set @head='P' 814 set @datebody=year(getdate()) 815 select @maxid=right(max(productNo),4) from product 816 if @maxid is null 817 set @maxid='0000' 818 declare @temp int 819 set @temp=cast(@maxid as int) 820 select * into #temp from inserted 821 update #temp set @temp=@temp+1, 822 productno=@head+@datebody+right(('000'+cast(@temp as varchar)),4) 823 insert into product select *from #temp 824 825 --客户表：客户编号、电话属性为not null； 826 客户号构成：年流水号，共9位，第一位为C，如C20080001，年份取建立日期的年份 827 --创建客户表 828 create table Customer 829 ( 830 customerNo char(9) primary key, 831 constraint C_No check(customerNo like 'C[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 832 customerName varchar(20) , 833 Telephone varchar(20) not null, 834 Sex varchar(5) check(sex='f' or sex='m'), 835 constraint C_Date_No check(substring(customerNo,2,4)=year(getDate())) 836 ) 837 --添加客户记录 838 insert into customer(customername,telephone,sex) 839 select '上海工商','1778922990','f' union all 840 select '香港电子','1778923990','m' 841 --添加客户流水号触发器 842 create trigger T_C_customerNo 843 on customer 844 instead of insert 845 as 846 declare @head varchar,@datebody varchar(10),@maxid varchar(10) 847 set @head='C' 848 set @datebody=year(getdate()) 849 select @maxid=right(max(customerno),4) from customer 850 if @maxid is null 851 set @maxid='0000' 852 declare @temp int 853 set @temp=cast(@maxid as int) 854 select * into #temp from inserted 855 update #temp set @temp=@temp+1, 856 customerno=@head+@datebody+right(('000'+cast(@temp as varchar)),4) 857 insert into customer select *from #temp 858 --订单主表：订单编号的构成：年月日流水号，共12位，如200708090001； 859 订单编号、客户编号、员工编号、发票号码设为not null；业务员必须是员工； 860 订货日期和出货日期的默认值设为系统当前日期；订单金额默认值为0；发票号码建立unique约束。 861 --创建订单主表 862 create table orderMaster 863 ( orderNo char(12) primary key, 864 constraint OM_No check(orderNo like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), 865 customerNo char(9) foreign key references Customer(customerNo), 866 salesmanNo char(8) foreign key references employee(employeeNo), 867 invoiceNo varchar(10) unique, 868 orderDate DateTime default(getDate()), 869 shipment datetime default(getDate()), 870 orderPrice numeric default(0) 871 ) 872 873 --添加记录到订单主表 874 insert into ordermaster(customerno,salesmanno,invoiceno,orderdate,shipment) 875 select 'C20180001','E2018001','1231230',getdate(),getdate() union all 876 select 'C20180002','E2018002','1231231',getdate(),getdate() 877 select *from ordermaster 878 879 --创建订单主表订单流水号触发器 880 881 create trigger T_OM_orderNo 882 on orderMaster 883 instead of insert 884 as 885 declare @datebody varchar(10),@maxid varchar(10), 886 @year char(4),@month char(2),@day char(2) 887 set @year=year(getdate()) set @month=datename(month,getdate()) set @day=datename(day,getdate()) 888 set @datebody=@year+@month+@day --replace(convert(char(10),getdate(),120),'-','') 889 select @maxid=right(max(orderno),4) from ordermaster 890 if @maxid is null 891 set @maxid='0000' 892 declare @temp int 893 set @temp=cast(@maxid as int) 894 select * into #temp from inserted 895 update #temp set @temp=@temp+1, 896 orderno=@datebody+right(('000'+cast(@temp as varchar)),4) 897 insert into ordermaster select *from #temp 898 899 --订单明细表：订单编号、商品编号、数量、单价设为not null。 900 --创建订单明细表 901 create table orderDetail 902 ( 903 orderNo char(12) foreign key references orderMaster(orderNo), 904 productNo char(9) foreign key references Product(ProductNo), 905 quantity numeric(10), 906 unitPrice numeric(10) not null, 907 primary key(orderNo,productNo) 908 ) 909 --第八章 游标、存储过程与触发器 910 --（1）利用游标查找所有女业务员的基本情况 911 declare find_female cursor static 912 for 913 select * 914 from employee 915 where sex='F' 916 917 open find_female 918 919 fetch next from find_female 920 while @@fetch_status=0 921 begin 922 fetch next from find_female 923 end 924 925 close find_female 926 927 deallocate find_female 928 929 930 931 --（2）创建一游标，逐行显示表customer的记录，要求按 932 --‘客户编号’+‘-------’+‘客户名称’+‘-------’+‘客户地址’+‘-------------------’+‘客户电话’+‘ 933 ----------’+‘客户邮编’+‘--------’格式输出， 934 --并且用while结构来测试游标的函数@@Fetch_Status的返回值。 935 936 937 declare dis_customer cursor static 938 for 939 select * 940 from customer 941 942 open dis_customer 943 944 declare @no varchar(9),@name varchar(40),@address varchar(40),@telephone varchar(20), 945 @zip varchar(6) 946 947 fetch first from dis_customer into @no,@name,@telephone,@address,@zip 948 print '客户编号:'+@no+'客户名称:'+@name+'客户地址:'+@address+'客户电话:'+@telephone+'客户邮编:'+@zip 949 while @@fetch_status=0 950 begin 951 fetch next from dis_customer into @no,@name,@telephone,@address,@zip 952 print '客户编号:'+@no+'客户名称:'+@name+'客户地址:'+@address+'客户电话:'+@telephone+'客户邮编:'+@zip 953 end 954 close dis_customer 955 deallocate dis_customer 956 957 958 959 --（3）利用游标修改orderMaster表中的Ordersum的值 960 declare up_om cursor scroll 961 for 962 select *from ordermaster 963 964 open up_om 965 fetch first from up_om 966 while @@fetch_status=0 967 begin 968 update ordermaster 969 set ordersum=0 970 where current of up_om 971 fetch next from up_om 972 end 973 974 close up_om 975 deallocate up_om 976 --（4）利用游标显示出orderMaster表中每一个订单所对应的明细数据信息。 977 declare dis_om cursor scroll 978 for 979 select orderno from ordermaster 980 open dis_om 981 982 declare @orderno varchar(12) 983 while 1=1 984 begin 985 fetch next from dis_om into @orderno 986 if @@fetch_status=0 987 begin 988 declare dis_od cursor scroll 989 for 990 select * from orderdetail 991 where orderno=@orderno 992 open dis_od 993 fetch first from dis_od 994 while @@fetch_status=0 995 begin 996 fetch next from dis_od 997 end 998 close dis_od 999 deallocate dis_od 1000 end 1001 else 1002 break 1003 end 1004 1005 close dis_om 1006 deallocate dis_om 1007 --（5）利用存储过程，给Employee表添加一条业务部门员工的信息。 1008 go 1009 create procedure pr_insertem 1010 @emno varchar(20), 1011 @emname varchar(20) 1012 as 1013 begin 1014 insert into employee(employeeno,employeename) values(@emno,@emname) 1015 end 1016 go 1017 execute pr_insertem 'E2018005','吴起' 1018 go 1019 drop proc pr_insertem 1020 --（6）利用存储过程输出所有客户姓名、客户订购金额及其相应业务员的姓名 1021 go 1022 create proc pr_outinfo 1023 as 1024 begin 1025 select cu.customername ,om.ordersum,em.employeename 1026 from customer cu,ordermaster om, employee em 1027 where cu.customerno=om.customerno and om.salerno=em.employeeno 1028 end 1029 execute pr_outinfo 1030 drop proc pr_outinfo 1031 --（7）利用存储过程查找某员工的员工编号、订单编号、销售金额。 1032 go 1033 create proc pr_selemployee 1034 as 1035 begin 1036 select em.employeeno,om.orderno,om.ordersum 1037 from employee em,ordermaster om 1038 where em.employeeno=om.salerno 1039 order by em.employeeno 1040 end 1041 go 1042 execute pr_selemployee 1043 go 1044 drop proc pr_selemployee 1045 --（8）利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额 1046 go 1047 create proc pr_selemployee 1048 as 1049 begin 1050 select em.employeeno,om.orderno,om.ordersum 1051 from employee em,ordermaster om 1052 where em.employeeno=om.salerno and em.employeename like '李%' and em.headship='职员' 1053 order by em.employeeno 1054 end 1055 execute pr_selemployee 1056 drop proc pr_selemployee 1057 1058 --（9）请使用游标和循环语句编写一个存储过程proSearchCustomer， 1059 --根据客户编号，查询该客户的名称、地址以及所有与该客户有关的销售记录，销售记录按商品分组输出。 1060 create proc proSearchCustomer 1061 @cuname varchar(20) 1062 as 1063 begin 1064 declare customers cursor scroll 1065 for 1066 select cu.customername,cu.address,om.orderno,om.customerno,om.salerno,om.orderdate,om.ordersum,om.invoiceno 1067 from customer cu,ordermaster om 1068 where cu.customername=@cuname and cu.customerno=om.customerno 1069 open customers 1070 fetch next from customers 1071 while @@FETCH_STATUS=0 1072 begin 1073 fetch next from customers 1074 end 1075 close customers 1076 deallocate customers 1077 end 1078 execute proSearchCustomer '统一股份有限公司' 1079 1080 drop proc proSearchCustomer 1081 1082 --（10）设置一个触发器，该触发器仅允许dbo用户可以删除Employee表内数据，否则出错。 1083 create trigger deleteemployee 1084 on employee 1085 for delete 1086 as 1087 begin 1088 if user='dbo' 1089 begin 1090 commit 1091 print'删除成功' 1092 end 1093 else 1094 begin 1095 rollback 1096 print'无权限修改employeeb表' 1097 end 1098 end 1099 --（11）在OrderMaster表中创建触发器， 1100 --插入数据时要先检查Employee表中是否存在和Ordermaster表同样值的业务员编号，如果不存在则不允许插入。 1101 create trigger ins_om 1102 on ordermaster 1103 for insert 1104 as 1105 if exists( 1106 select salerno from inserted 1107 where salerno 1108 in (select employeeno from employee) 1109 ) 1110 begin 1111 commit 1112 print('employeeno存在！') 1113 end 1114 else 1115 begin 1116 rollback 1117 print('employeeno不存在！') 1118 end 1119 --（12）级联更新：当更新customer表中的customerNo列的值时， 1120 --同时更新OrderMaster表中的customerNo列的值，并且一次只能更新一行。 1121 1122 1123 1124 --??????????????????????????????????????????????????尚存问题 主外键约束阻碍触发器运行 1125 create trigger up_cusno 1126 on customer 1127 for update 1128 as 1129 if(update(customerno)) 1130 begin 1131 update ordermaster 1132 set customerno=(select ins.customerno from inserted ins) 1133 where customerno=(select del.customerno from deleted del) 1134 end 1135 1136 --（13）对product表写一个UPDATE触发器。 1137 1138 1139 --??????????????????????????????????????????????????尚存问题 主外键约束阻碍触发器运行 1140 create trigger up_pro 1141 on product 1142 for update 1143 as 1144 if(update(productno)) 1145 begin 1146 update orderdetail 1147 set productno=(select ins.productno from inserted ins) 1148 where productno=(select del.productno from deleted del) 1149 end 1150
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
