SQL Server经典案例问答~！
 
1.要求在yuangong表上增加一列为录入员,并将该字段设为默认约束,值为:张红.请用SQL语句写出从创建默认约束到删除的一系列过程.
 
答：
新建yuangong表
create table yuangong
(
  姓名 varchar(20)not null
)
添加列名：录入员
alter table yuangong add 录入员 varchar(10)
新建默认值约束 DK_lry
alter table yuangong add constraint DK_lry default '张红' for 录入员
进行测试
insert into yuangong(姓名)values ('张正涛')
查看张红是否自动添加到录入员列
select * from yuangong
删除默认约束 DK_lry
alter table yuangong drop constraint DK_lry
进行测试
insert into yuangong (姓名) values ('张涛')
查看
select * from yuangong
 
2.现在你们公司在北京设置了一个专卖店，这个专场店只能针对北京五大区的用户进行销售。也就是说填写客户资料时，客户所在地只能是海淀区，朝阳区，通州区、崇文区、顺义区,并且客户邮编必须正确（全部是数字）,每一条记录都会有一个录入日期字段，取当前日期，请自拟建表实现，使用SQL语句写出实现过程
 
答：
新建表yuangong
create table yuangong
(
    姓名 Varchar(11) NOT NULL,
    地址 varchar(10) not null,
    邮编 Varchar(20) not null,
    日期 datetime DEFAULT GETDATE()
)
建立邮编的check约束
ALTER TABLE table2 add constraint CK_YB check (邮编 like '[0-9][0-9][0-9][0-9][0-9][0-9]')
进行测试
insert into table2 (姓名,邮编) values ('测试1',100009)
insert into table2 (姓名,邮编) values ('测试2',1000098)
insert into table2 (姓名,邮编) values ('测试3',中文邮编)
建立地址check约束
alter table yuangong add constraint CK_DZ check (地址='海淀' or 地址='朝阳' or 地址='通州' or 地址='崇文' or 地址='顺义')
进行测试
insert into yuangong (地址)values ('大兴')
insert into yuangong (地址)values ('海淀')
 
3.创建用于存储收货人资料的recipient表。下面是要存储到表中的收货人资料
--创建表
create table Recipient
(    OrderNo char(6) not null,
    userName  varchar(20)not null,
    Address varchar(20) null,
    City  char(15) null,
    CountryId char(3) null,
    ZipCode char(10) null,
    Phone char(15) null
)
 
考虑上面的表结构：
就以下问题参考这些表结构：
a.创建Category(种类)表。在创建表时实施下面的数据完整性规则：
 CategoryID属性应作为主健
 Category属性应为唯一，但不是主健
 Description的描述可存储NULL值
答：
create table Category
(
    CategoryID char (6) primary key not null,
    Category varchar(10) not null,
    Description varchar(20)
)
alter table Category add constraint UK_ZL unique(CategoryID)

b.创建ToyBrand(商标)表。在创建表时实施下面的数据完整性规则：
BrandID属性应作为主健
BrandName属性应为唯一，但不是主健
 
答：
create table ToyBrand
(
    ToyBrandID char (6) primary key not null,
    BrandName varchar(10) not null,
  
)
alter table ToyBrand add constraint UK_SB unique(ToyBrandID)
 
c.创建Toys表。在创建表时实施下面的数据完整性规则：
 ToysID属性应作为主健
 ToyName和ToyDescription属性不可取NULL值
 玩具的siToyQoh现有数量应在0到200之间
 玩具的最低年龄LowerAge缺省为1
 CategoryId属性的值应出现在Category表中
 
答：
create table toys
(
    ToysID char (6) primary key not null,
    ToyName varchar(10) not null,
    ToyDescription varchar(20) not null,
    SiToyQoh char(10) not null,
    constraint CK_SL check (siToyQoh>=0 and siToyQoh<=200),
    LowerAge int default 1,
    BrandID char (6),
    CategoryID char (6)
   
)
alter table toys add constraint FK_Category foreign key(CategoryID) references Category(Categoryid)
on update cascade on delete cascade
 
d.修改Toys表。实施下面的数据完整性规则：
BrandID属性的值应出现在ToyBrand表中
 
答：
alter table toys add constraint FK_ToyBrand foreign key(BrandID) references toyBrand(toybrandid)
on update cascade on delete cascade
 
4. 现有两个表学生表和学生借书记录表
结构如下：
  Create Table Student   --学生表
(            
       StudentID int primary key，      --学号
       studentname varchar(10)       -- 学生姓名
)
     Create Table BorrowRecord   --学生借书记录表
(             
       BorrowRecord  int identity(1,1),      --流水号 
       StudentID     int ,                   --学号   
       BorrowDate    datetime,               --借出时间
       ReturnDAte    Datetime,               --归还时间 
)

要求使用触发器实现以下功能
A．    如果更改了学生的学号,希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
B．    如果该学生已经毕业，希望删除他的学号的同时，也删除它的借书记录。
可以使用外键约束或是触发器来实现
 
答：
外键约束
alter table BorrowRecord add constraint FK_studentID foreign key(studentID) references Student(studentID)
on update cascade（级联更新-针对题目A）
on delete cascade（级联删除-针对题目B）
测试：
student表中写入两名学生
insert into student (studentID,studentName)values ('1','张三')
insert into student (studentID,studentName)values ('2','李四')
BorrowRecord表中写入这两名学生的借书时间
insert into BorrowRecord (studentID,BorrowDate,ReturnDate )values ('1','2006-05-01','2006-08-02')
insert into BorrowRecord (studentID,BorrowDate,ReturnDate )values ('2','2007-04-01','2007-06-02')
更改张三的学号为10
update student set studentid=10 where studentName='张三'
查看结果
select * from student
select * from BorrowRecord
删除学员张三
delete from student where studentname ='张三'
查看结果
select * from student
select * from BorrowRecord
 
5. 要求建立2个表：
cust_test                    和                    order_test
cust_test: CustomerID char(5) PK                   order_test: CustomerID char(5)--对应关系
           Custcity        所在城市                            Orderid    PK     --订单序号
           Custname        产品名称                            OrderNames        --订购的产品名称
           CStatus    int  库存状态                            OStatus    int    --状态
           Cstorage   int  库存量                              Orders     int    --定购
           Cdate      date 日期                                Odate      date   --日期
题目1：
在cust_test表中建立删除触发器，实现上述2表的级联删除。
从库存表中删除一个产品，相应的订单也会删除
create table cust_test
(
     customerID char(5) primary key,
     custcity   varchar(20),
     custname   varchar(5),
     cstatus    int,
     cstorage   int,
     cdate       datetime
)
create table order_test
(
     customerID char(5) ,
     orderid    char(5) primary key,
     ordername  varchar(20),
     ostatus    int,
     ostorage   int,
     odate      datetime
)
create trigger del_CP
on cust_test for delete
as
begin
if exists (select * from deleted)
  begin
    delete from order_test where CustomerID in (select CustomerID from deleted )
  end
end
 

题目2：
在order_test表上建立一个插入触发器，在添加一个订单时，减少cust_test表的相应货物的记录的库存量。
create trigger in_DD
on order_test for insert
as
begin
if exists (select orders from inserted)
  begin
     update cust_test set Cstorage= Cstorage-inserted.orders where CustomerID=inserted.crustomerid
  end
end
题目3：
在order_test表上建立一个更新触发器，规定订单日期(Odate)不能修改。
答：
Create trigger odate
on order_test for update
as
begin
if update(odate)
begin
  rollback
  print '抱歉，订单日期无法修改，请与管理员联系'
 end
end
题目4：
要求订购的物品一定要在仓库中有的，并且数量足够。
 
答：
Create trigger TR_SL
on order_test for insert
as
begin
if exists (select * from inserted)
begin
    if 'Orders' > 'Cstorage'
      begin
      Rollback
      end
 end
end