管理表 DDL
实训目的:
掌握创建表、修改表、删除表的方法
掌握创建主键约束、外键约束、用户自定义约束的方法
掌握创建查询表的方法及使用截断表的方法
理解约束在数据库中的作用
实训内容:
一、创建表
1.创建图书馆管理系统所涉及的表
图书表book(bid,bname,price,qty)
书库表lib(lid,lname,address)
读者表reader(rid,rname,dept)
借阅表loan(bid,rid,loanDate)
-- 图书表book
create table book(
bid int ,
bname char,
price number(8,2),
qty number(8) -- 数量
);
-- 书库表lib
create table lib(
lid int,
lname varchar2(30),
address varchar2(30)
) ;
-- 读者表reader
create table reader(
rid int,
rname varchar2(30),
dept varchar2(30)
);
-- 借阅表loan
create table loan(
bid int,
rid int,
loanDate date
);
2.创建产品销售系统所涉及的表
-- 产品表product
create table product(
pid int, -- 产品ID
pname varchar2(50), -- 产品名称
price number(8,2), -- 价格
stock number(8) -- 库存数量
);
-- 供应商表supplier
create table supplier(
sid int, -- 供应商ID
sname varchar2(50), -- 供应商名称
address varchar2(100), -- 地址
contact varchar2(30) -- 联系方式
);
-- 客户表customer
create table customer(
cid int, -- 客户ID
cname varchar2(50), -- 客户名称
address varchar2(100), -- 地址
phone varchar2(20) -- 联系电话
);
-- 销售表sale
create table sale(
saleid int, -- 销售记录ID
pid int, -- 产品ID
cid int, -- 客户ID
quantity number(8), -- 销售数量
saleDate date, -- 销售日期
totalSale number(8,2) -- 销售总额
);
-- 订单表order
create table order(
orderid int, -- 订单ID
pid int, -- 产品ID
cid int, -- 客户ID
orderDate date, -- 下单日期
status varchar2(20) -- 订单状态
);
二、修改表
1、为各表创建约束
DBMS自动实现数据的完整性---借助于约束的作用:保证数据库中永久数据的正确性(主键唯一,不能引用不存在的对象,满足用户定义的条件)
1.创建主键约束(为图书表、读者表、借阅表创建主键)
主键:唯一标识,该字段的值必须与别的实体必须是不一样的
由dbms保证数据的正确性
-- 图书表
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);
2.创建外键约束(为借阅表创建外键)
-- 借阅表
alter table loan
add constraint loan_order_fk foreign key (rid) references reader(rid);
3.创建用户自定义约束(指定图书表中价格必须为正值)
alter table book
add constraint book_price_check check (price > 0);
2、对各表进行字段调整操作
1.添加新字段
在图书表中添加“是否热门”字段
alter table book
add (hot varchar2(10));
select * from book;
2.修改字段类型
修改图书表中书名字段为变长字符串
alter table book
modify (bname varchar2(50));
3.删除不用的字段
删除图书表中“是否热门”字段
alter table book
drop column hot;
三、删除表
1.删除无关联表
-- 删除无关联表(书库表)
drop table lib;
2.删除包含外键引用的表
alter table loan
drop constraint loan_order_fk;
drop table reader;
四、创建查询表
创建所有昂贵书籍表
create table expensive_books as
select * from book WHERE price > 100;
五、对借阅情况表进行截断操作
delete * from loan
dml 日志多
ddl 日志少
truncate loan;
select * from book where bid in(
select bid
from book
group by bid
having count(bid)>1);