-- 一、创建表
-- 创建图书馆管理系统所涉及的表
-- 创建产品销售系统所涉及的表
-- 1.图书表book(bid,bname,price,qty)
DROP TABLE book cascade constraints;
DROP TABLE lib;
DROP TABLE reader;
DROP TABLE loan;
create table book
(
bid char(10),
bname char(20),
price number(8,2),
qty int);
-- 2.读者表reader(rid,rname,dept)
create table reader
(
rid char(10),
rname char(20),
dept char(30));
--3、借阅表loan(bid,rid,loanDate)
create table loan
(
bid char(10),
rid char(10),
loandate char(20)
);
--- 4.书库表lib(lid,lname,address)
create table lib
(
lid char(10),
lname char(20),
address char(100)
);
--二、修改表
-- 1、为各表创建约束
-- DBMS自动实现数据的完整性---借助于约束的作用:保证数据库中永久数据的正确性(主键唯一,不能引用不存在的对象,满足用户定义的条件)
-- 创建主键约束(为图书表、读者表、借阅表创建主键)
-- 主键:唯一标识,该字段的值必须与别的实体必须是不一样的
-- 由dbms保证数据的正确性
alter table book
add constraint bookPK primary key(bid);
alter table reader
add constraint readerPK primary key(rid);
alter table loan
add constraint loanPK primary key(bid,rid);
ALTER TABLE lib
ADD constraint libPK primary key(lid);
--外键约束(为借阅表创建外键)
ALTER TABLE book
add constraint loanFK1 foreign key(bid) references book(bid)
alter table reader
add constraint loanFK2 foreign key(rid) references reader(rid);
--自定义约束(指定图书表中价格必须为正值)
alter table book
add constraint ck_book_price check(price>0);
--三、修改字段
--增加字段( 在图书表中添加“是否热门”字段)
alter table book
add(ishot int);
ALTER TABLE book
ADD (ishot varchar(20) default '是否热门' not null);
--修改字段
alter table book
modify(ishot char(20));
-- -- 修改图书表中书名字段为变长字符串
ALTER TABLE book
MODIFY(bname varchar(20));
--删除字段(删除图书表中“是否热门”字段)
ALTER TABLE book
DROP COLUMN ishot;
--四、创建查询表
-- -- 创建所有昂贵书籍表
create table book_exp
as
select * from book
where price >50;
--五、创建视图
create view book_exp_view
as
select * from book
where price >50;
--六.删除表
-- -- 删除无关联表
DROP TABLE book;
-- -- -- 删除包含外键引用的表
DROP TABLE loan cascade constraints;
-- -- 七、对借阅情况表进行截断操作
-- -- delete * from loan
-- -- dml 日志多
-- -- ddl 日志少
-- -- truncate loan;
truncate TABLE loan;
-- -- select * from book where bid in(
-- -- select bid
-- -- from book
-- -- group by bid
-- -- having count(bid)>1);
create table book
(
bid char(10),
bname char(20),
price number(8,2),
qty int);
create table reader
(
rid char(10),
rname char(20),
dept char(30));
create table loan
(
bid char(10),
rid char(10),
loandate char(20)
);
create table lib
(
lid char(10),
lname char(20),
address char(100)
);
alter table book
add constraint bookPK primary key(bid);
alter table reader
add constraint readerPK primary key(rid);
alter table loan
add constraint loanPK primary key(bid,rid);
ALTER TABLE lib
ADD constraint libPK primary key(lid);
ALTER TABLE book
add constraint loanFK1 foreign key(bid) references book(bid)
alter table reader
add constraint loanFK2 foreign key(rid) references reader(rid);
alter table book
add constraint ck_book_price check(price>0);
ALTER TABLE book
ADD (ishot varchar(20) default '是否热门' not null);
-- -- 修改图书表中书名字段为变长字符串
ALTER TABLE book
MODIFY(bname varchar(20));
create table book_exp
as
select * from book
where price >50;
create view book_exp_view
as
select * from book
where price >50;