建表代码
create table books(
books_name char(30),
books_quantity int ,
books_position char(50),
books_ID char(10) primary key
);
create table reader(
reader_sex char(1) check (reader_sex in ('M','W')),
reader_ID char(10) primary key ,
reader_name char(30),
reader_unit char(40),
reader_begindate char(10) ,
reader_duedate char(10)
)
create table publisher(
publisher_name char(100),
publisher_phone char(11),
publisher_address char(100),
publisher_telegraphaddress varchar(20) primary key,
publisher_postcode char(4)
)
create table borrow(
books_ID char(10),
reader_ID char(10),
primary key (books_ID,reader_ID)
)
create table publish(
books_ID char(10),
publisher_telegraphaddress varchar(20),
foreign key books_ID references books(books_ID)
)
以下代码为插入books数据
insert into books
values('vc++技术内幕','10','自然科学类t计算机科学与技术类','t1000');
insert into books
values('数据库基础教程','20','自然科学类t计算机科学与技术类','t1010');
insert into books
values('数据结构','5','自然科学类t计算机科学与技术类','t1031');
insert into books
values('操作
系统','11','自然科学类t
计算机科学与技术类','t1037');
insert into books
values('small c++','9','自然科学类t计算机科学与技术类','t1050');
insert into books
values('C++进阶','4','自然科学类t计算机科学与技术类','t1060');
insert into books
values('马克思主义','1','社会科学类','s3000');
insert into books
values('笑死你不偿命','3','杂志类','z1000');
以下代码插入读者
insert into reader
values('M','2009023130','孙科','09游戏一班','2011-05-12','2011-05-27');
insert into reader
values('W','2007033110','陈小小','国旅一班','2011-05-11','2011-05-27');
insert into reader
values('M','2008022130','胡歌','09
软件一班','2011-04-12','2011-04-27');
insert into reader
values('M','2007010120','叶林','07路桥一班','2011-02-11','2011-02-27');
insert into reader
values('W','2008020321','李明','08工管一班','2011-03-12','2011-04-02');
以下代码插入出版社
insert into publisher
values('新华出版社','','江华长征路51号','010','0746');
insert into publisher
values('机械出版社','','洪山桥98号','020','0731');
以下代码为创建bnmyni
登陆人并授权给bnmyni
exec sp_addlogin '图书管理', '123', '12'
exec sp_grantdbaccess '图书管理','bnmyni'
GRANT CREATE TABLE TO bnmyni
GRANT select,update,delete ON books TO bnmyni
GRANT select,update,delete ON reader TO bnmyni
GRANT select,update,delete ON publisher TO bnmyni
以下代码是创建读者图示
create view reader_need as
select books_name,reader_begindate,reader_duedate
from books,reader
以下代码创建触发器
create trigger books_Updatet
on books
for update
as
update books
set books_quantity=50
where books_ID like 's3000';
以下激活触发
器代码
update books
set books_quantity=44
where books_ID like 'z1000'
以下为测试代码
delete from books
where books_ID like 's1100'
select *
from books
insert books
values('马克思主义','0','社会科学类','s3300')
create trigger books_delete
on books
for delete
as
delete from books
where books_quantity=0
create trigger books_Updatet
on books
for update
as
update books
set books_quantity=50
where books_ID like 's3300';
drop trigger books_Updatet
drop trigger books_update
以下为
演示代码:
select *
from reader
select *
from publisher
select *
from books
update books
set books_quantity=1
where books_ID like 's2200'