实训目的:
掌握创建表、修改表、删除表的方法
掌握创建主键约束、外键约束、用户自定义约束的方法
掌握创建查询表的方法及使用截断表的方法
理解约束在数据库中的作用
实训内容:
一、创建表
创建图书馆管理系统所涉及的表
图书表book(bid,bname,price,qty)
CREATE TABLE book(
bid INT,
bname CHAR,
price NUMBER(8,2),
qty NUMBER(8)
);
书库表lib(lid,lname,address)
CREATE TABLE lib(
lid INT,
lname VARCHAR2(30),
address VARCHAR2(30)
);
读者表reader(rid,rname,dept)
CREATE TABLE reader(
rid INT,
rname VARCHAR2(30),
dept VARCHAR2(30)
);
借阅表loan(bid,rid,loanDate)
CREATE TABLE loan(
bid INT,
rid INT,
loanDate DATE
);
二、修改表
1、为各表创建约束
DBMS自动实现数据的完整性---借助于约束的作用:保证数据库中永久数据的正确性(主键唯一,不能引用不存在的对象,满足用户定义的条件)
创建主键约束(为图书表、读者表、借阅表创建主键)
主键:唯一标识,该字段的值必须与别的实体必须是不一样的
由dbms保证数据的正确性
prompt 1.创建主键约束(为图书表、读者表、借阅表创建主键)
prompt 创建主键约束
--图书表
ALTER TABLE book
add constraint book_bid_pk primary key(bid);
--读者表
ALTER TABLE reader
add constraint reader_rid_pk primary key(rid);
--借阅表
ALTER TABLE loan
add constraint loan_bid_pk primary key(bid);
创建外键约束(为借阅表创建外键)
prompt 创建外键约束(为借阅表创建外键)
ALTER TABLE loan
add constraint loan_order_fk foreign key(rid) references reader(rid);
创建用户自定义约束(指定图书表中价格必须为正值)
prompt 创建用户自定义约束(指定图书表中价格必须为正值)
ALTER TABLE book
add constraint book_price_check check(price >0 );
2、对各表进行字段调整操作
添加新字段
在图书表中添加“是否热门”字段
prompt 在图书表中添加“是否热门”字段
ALTER TABLE book
ADD(hot varchar2 (10));
修改字段类型
修改图书表中书名字段为变长字符串
ALTER TABLE book
modify(bname varchar2(50));
删除不用的字段
删除图书表中“是否热门”字段
ALTER TABLE book
drop column hot;
三、删除表
删除无关联表
prompt 删除无关联表
drop table lib;
删除包含外键引用的表
ALTER TABLE loan
drop constraint loan_order_fk;
drop table reader;
四、创建查询表
创建所有昂贵书籍表
prompt 创建所有昂贵书籍表
CREATE TABLE expensiveBooks AS
SELECT * FROM book where price >1000;
五、对借阅情况表进行截断操作
delete * from loan
dml 日志多
ddl 日志少
truncate loan;
select * from book where bid in(
select bid
from book
group by bid
having count(bid)>1);
prompt 五、对借阅情况表进行截断操作
TRUNCATE TABLE loan;
SELECT *FROM loan;
总代码:
prompt 一、创建表
prompt 图书表book(bid,bname,price,qty)
CREATE TABLE book(
bid INT,
bname CHAR,
price NUMBER(8,2),
qty NUMBER(8)
);
prompt 书库表lib(lid,lname,address)
CREATE TABLE lib(
lid INT,
lname VARCHAR2(30),
address VARCHAR2(30)
);
prompt 读者表reader(rid,rname,dept)
CREATE TABLE reader(
rid INT,
rname VARCHAR2(30),
dept VARCHAR2(30)
);
prompt 借阅表loan(bid,rid,loanDate)
CREATE TABLE loan(
bid INT,
rid INT,
loanDate DATE
);
prompt 二、修改表
prompt 1.创建主键约束(为图书表、读者表、借阅表创建主键)
prompt 创建主键约束
--图书表
ALTER TABLE book
add constraint book_bid_pk primary key(bid);
--读者表
ALTER TABLE reader
add constraint reader_rid_pk primary key(rid);
--借阅表
ALTER TABLE loan
add constraint loan_bid_pk primary key(bid);
prompt 创建外键约束(为借阅表创建外键)
ALTER TABLE loan
add constraint loan_order_fk foreign key(rid) references reader(rid);
prompt 创建用户自定义约束(指定图书表中价格必须为正值)
ALTER TABLE book
add constraint book_price_check check(price >0 );
prompt 2.对各表进行字段调整操作
prompt 在图书表中添加“是否热门”字段
ALTER TABLE book
ADD(hot varchar2 (10));
prompt 修改图书表中书名字段为变长字符串
ALTER TABLE book
modify(bname varchar2(50));
prompt 删除图书表中“是否热门”字段
ALTER TABLE book
drop column hot;
prompt 三、删除表
prompt 删除无关联表
drop table lib;
prompt 删除包含外键引用的表
ALTER TABLE loan
drop constraint loan_order_fk;
drop table reader;
prompt 四、创建查询表
prompt 创建所有昂贵书籍表
CREATE TABLE expensiveBooks AS
SELECT * FROM book where price >1000;
prompt 五、对借阅情况表进行截断操作
TRUNCATE TABLE loan;
SELECT *FROM loan;