数据库实验设计

本实验是设计一个图书管理系统，并实现增删改查等操作

建立表

create table library
( book_id char(8) primary key,
book_name char(10),
borrow_date date,
borrow_student char(10),
return_date date,
is_borrowed boolean
);

插入数据

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000001','计算机组成原理','0000-00-00',null,'0000-00-00',false);

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000002','计算机网络','0000-00-00',null,'0000-00-00',false);

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000003','数据结构','0000-00-00',null,'0000-00-00',false);

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000004','计算机系统结构','0000-00-00',null,'0000-00-00',false);

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000005','深入react技术栈','0000-00-00',null,'0000-00-00',false);

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000006','typescript','0000-00-00',null,'0000-00-00',false);

看一下是否成功

select * from library;

添加唯一约束

alter table library add unique(book_id,book_name);

select * from library;

delete from library where book_id='00000001';

insert into library(book_id,book_name,borrow_date,borrow_student,return_date,is_borrowed)
values('00000001','计算机组成原理','2020-06-09','Messi','2020-08-09',true);

update library set borrow_date='2020-05-20',borrow_student='Ronaldo',return_date='2020-07-20',is_borrowed=true where book_id = '00000002';

update library set borrow_date='2020-04-20',borrow_student='Neymar',return_date='2020-06-20',is_borrowed=true where book_id = '00000003';

update library set borrow_date='2020-06-18',borrow_student='De Bruyne',return_date='2020-08-18',is_borrowed=true where book_id = '00000004';

update library set borrow_date='2020-07-07',borrow_student='Suarez',return_date='2020-09-07',is_borrowed=true where book_id = '00000005';

update library set borrow_date='2020-07-04',borrow_student='Mbappe',return_date='2020-09-04',is_borrowed=true where book_id = '00000006';

select * from library;