实训4：管理表

实训目的

1、掌握创建表、修改表、删除表的方法

2、掌握创建主键约束、外键约束、用户自定义约束的方法

3、掌握创建查询表的方法及使用截断表的方法

4、理解约束在数据库中的作用

实训内容

一、创建表

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

图书表book(bid,bname,price,qty)

CREATE TABLE book (
    bid INT,
    bname VARCHAR(20),
    price DECIMAL(10, 2), --其中10代表总共的位数，2代表小数点后的位数
    qty INT
);

书库表lib(lid,lname,address)

CREATE TABLE lib (
    lid INT,
    lname VARCHAR(50),
    address VARCHAR(100)
);

读者表reader(rid,rname,dept)

CREATE TABLE reader (
    rid INT,
    rname VARCHAR(50),
    dept VARCHAR(100)
);

借阅表loan(bid,rid,loanDate)

CREATE TABLE loan (
    bid INT,
    rid INT,
    loanDate DATE
);

二、修改表

1、为各表创建约束

DBMS自动实现数据的完整性---借助于约束

约束的作用：保证数据库中永久数据的正确性(主键唯一，不能引用不存在的对象,满足用户定义的条件）

创建主键约束（为图书表、读者表、借阅表创建主键）

主键：唯一标识，该字段的值必须与别的实体必须是不一样的

由dbms保证数据的正确性

alter table book 
add primary key(bid);

alter table reader 
add primary key(rid);

alter table loan 
add primary key(bid,rid,loanDate);

创建外键约束（为借阅表创建外键）

alter table loan 
add constraint fk_bid foreign key(bid) references book(bid);

alter table loan 
add constraint fk_rid foreign key(rid) references reader(rid);

创建用户自定义约束（指定图书表中价格必须为正值）

ALTER TABLE book
ADD CONSTRAINT positive_price CHECK (price > 0);

2、对各表进行字段调整操作

添加新字段

在图书表中添加“是否热门”字段

ALTER TABLE book
ADD hot CHAR(1) DEFAULT 'N';
 

修改字段类型

修改图书表中书名字段为变长字符串

ALTER TABLE book
MODIFY bname VARCHAR(50);

删除不用的字段

删除图书表中“是否热门”字段

ALTER TABLE book
DROP COLUMN hot;

三、删除表

删除无关联表

DROP TABLE lib;

删除包含外键引用的表

---消除外键约束
ALTER TABLE LOAN
DROP CONSTRAINT fk_bid;

ALTER TABLE LOAN
DROP CONSTRAINT fk_rid;

---删除表
DROP TABLE LOAN;

四、创建查询表

创建所有昂贵书籍表

create table expensivebook 
as
select *
from book
where price>100;

五、对借阅情况表进行截断操作

1. DELETE * FROM loan：这是一个用于删除表中所有数据的 DELETE 查询语句。在这种情况下，它将从 loan 表中删除所有记录。DELETE 操作会生成相对较多的 DML（Data Manipulation Language）日志，因为它记录了被删除的每一行数据的详细信息。

2. TRUNCATE loan：这是一个用于截断表（即删除表中所有数据并重置自增主键等）的 TRUNCATE TABLE 查询语句。与 DELETE 不同，TRUNCATE 是一个DDL（Data Definition Language）操作，它会快速地删除表中的所有行而不产生大量的日志，因为它不会逐行删除，而是直接删除整个表内容。