管理表实训

一、创建图书管理系统所涉及的表

1.创建表

--书库表
create table lib(
    lid varchar2(10) primary key,
    lname varchar2(255) not null,
    address varchar2(200) not null
);

--图书表
create table book(
    bid vachar2(20) primary key,
    bname varchar2(255) not null,
    lid varchar2(10) not null,
    price number(4) not null,
    qty number(5) not null,
    foreign key(lid) reference lib(lid)
);
--读者表
create table reader(
        rid varchar2(10),
        rname varchar2(255),
        dept int
);
--借阅表
create table loan(
    bid  varchar2(20) not null,   --借的什么书
    rid   varchar2(10)not null,  --哪个读者借的
    loanDate Date not null     --借的日期
    foreign key (bid) reference book(bid),
    foreign key (rid) reference reader(rid)
);

2.修改表

---2.1创建主键(初建表时已建)
---2.2创建外键(初建表时已建)
---2.3 创建用户自定义约束（指定图书表中价格必须为正值）
alter table book
add constraint c1 check(price >0);
---2.4添加新字段   在图书表中添加“是否热门”字段
alter table book
add (pishot_number(1)default 0 check (pishot in(0,1))) ;
---2.5修改字段类型  修改图书表中图书字段为变长字符串
alter table book
modify bname varchar2(20);
---2.6删除不用的字段  删除图书表中“是否热门”字段
alter table book
drop column pishot;

3.删除表

---3.1删除无关联表
----例如要删除读者表(读者表没有任何关联)
DROP TABLE reader;
---3.2删除包含外键引用的表
----例如你要删除借阅表（此表中有相关的一些约束）
----方法1：
drop table loan cascade constraints;
---在这里 CASCADE CONSTRAINTS（是一个可选项）是表示删除表中的一些约束（如主键，唯一约束，外键约束）
----方法2：
alter table book cascade constraint c1;
drop table book;

4.创建查询表

-----4.1创建昂贵书籍表
create view pexpensive as
select *
from book
where  price>(select avg(price) from book
---视图是一个虚拟的表，基于sql查询的结果集，视图本身不存储数据，它只是保存了定义视图的SQL查询，
---视图是基于基础表的数据的，所以如果基础表的数据发生变化，视图中的数据也会相应变化

5.对借阅情况进行截断操作

truncate loan
--截断操作：在Oracle中截断操作（truncate）是一种快速删除表中所有行数据的方式，截断操作会一次性快速删除所有行数据，并且不记录每一行的删除日志
--truncate属于ddl，所以ddl日志少，相对于dml，执行dml语言时，会进行每一行的数据变更记录，所有dml日志多

--6.
select * 
from book 
where bid in(
select bid
from book
group by bid
having count(bid)>1);
--此语句实现的操作是按bid分组显示出查找bid相同的书籍记录

二、创建产品销售系统所涉及的表

1.创建表

--创建产品类别表
create table ProductCategories(
    pcgid varchar2(10) primary key,
    pcgname varchar2(20)
);
--创建产品表
create table product(
    pid varchar2(10) primary key,
    pname varchar2(20) not null,
    productprice number(6,2) not null,
    pcgid varchar2(10) not null,
    foreign key (pcgid) reference ProductCategories(pcgid)
);
--创建销售表
create table sale(
    salid varchar2(10) primary key,
    pid varchar2(10) not null,
    sname varchar2(20) not null,
    customerid varchar2(10) not null,
    saleprice number(10) not null,
    saledate date not null,
    foreign key (sname) reference staff(sname),
    foreign key (pid) reference product(pid),
    foreign key (customerid) reference customer(customerid)
);
--创建销售职员表
create table staff(
    sid varchar2(10) primary key,
    sname varchar2(20) not null,
    sposition varchar2(10) not null
);
--创建客户表    
create table customer(
    customerid varchar2(10) primary key,
    cname  varchar2(20) not null,
    sid varchar2(10) not null,
    sname varchar2(20) not null,
        foreign key(sid) reference staff(sid),
    foreign key(sname) reference staff(sname)
);

2.修改表

---2.1创建主键(初建表时已建)
---2.2创建外键(初建表时已建)
---2.3 创建用户自定义约束（指定产品表中价格必须为正值）
alter table pruduct
add constraint c1 check(productprice >0);
---2.4添加新字段   在产品表中添加“是否热门”字段
alter table product
add (pishot_number(1)default 0 check (pishot in(0,1))) ;
---2.5修改字段类型  修改产品表中产品名字段为变长字符串
alter table product
modify pname varchar2(20);
---2.6删除不用的字段  删除产品表中“是否热门”字段
alter table product
drop column pishot;

3.删除表

---3.1删除无关联表
----例如要删除职员表(staff没有任何关联)
DROP TABLE staff;
---3.2删除包含外键引用的表
----例如你要删除客户表（此表中有相关的一些约束）
----方法1：
drop table customer cascade constraints;
---在这里 CASCADE CONSTRAINTS（是一个可选项）是表示删除表中的一些约束（如主键，唯一约束，外键约束）
----方法2：
alter table product cascade constraint c1;
drop table product;

4.创建查询表

-----4.1创建昂贵书籍表
create view pexpensive as
select *
from product
where  productprice>(select avg(productprice) from product)

5.对销售情况进行截断操作

truncate sale；