管理表实训

管理表        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);