数据库的定义、建立与维护实验
- 目的
- 要求
- 实验内容
- 定义数据库
- 定义数据库基本表
- 数据输入
- 数据修改
- 数据删除
- 思考
- 附录——完整代码
目的
使用SQL语言实现数据库的创建、删除；基本表的创建、删除、更新工作
要求
- 在MySql Workbench中，利用SQL语言中CREATE、DROP命令实现数据库的创建及删除工作。
- 在MySql Workbench中，利用SQL语言中CREATE、ALTER及DROP命令进行基本表的创建、更新、删除工作，并实现基本表中各类完整性约束条件的限定。
- 完成上述工作后，在MySql Workbench中，查看是否成功创建实验所要求数据库、基本表、各类完整性约束条件及索引等内容。
实验内容
定义数据库
定义一个借阅数据库，数据库名称为“Library_01”
create database library_01;定义数据库基本表
在所定义的借阅数据库 “Library_01” 中，按要求定义三个基本表（书籍Book,借书证Card,借阅记录Borrow），并确定如下完整性约束：
- 确定各基本表的主码，主码属性不能为空；
- 如果有外码，定义各基本表外码；
- 要求检查card表中属性Type的值是否为(‘T’,‘G’,‘U’,‘F’)（T:教师，G:研究生，U:本科生，F:外单位人员）；
- Book
- Card
- Borrow
-- 切换到Library_01 数据库，必须在文件开头启用，否则服务器不知道要用哪个数据库，或者是应用了其他数据库
use Library_01;
-- 创建Book表
create table Book
(
bno char(8) not null comment'书籍编号' primary key,
category varchar(10) not null comment'书籍类别',
title varchar(40) not null comment'书名',
publisher varchar(30) comment'出版社',
pub_year year not null comment'出版年份',
author char(20) comment'作者',
price decimal(5,2) not null comment'价格'
);
-- 创建Card表
create table Card
(
cno char(7) not null comment'借书证编号' primary key,
pname char(8) not null comment'姓名',
department varchar(5) comment'所在单位',
ctype char(1) not null comment'借书证类别' check(ctype in('T', 'G', 'U', 'F'))
);
-- 创建Borrow表
create table Borrow
(
cno char(7) not null comment'借书编号',
bno char(8) not null comment'书籍编号',
borrow_date date not null comment'借出日期',
return_date date comment'归还日期',
constraint C1 primary key(cno, bno),
constraint C2 foreign key(cno) references Card(cno),
constraint C3 foreign key(bno) references Book(bno)
);数据输入
分别向Book、Card、Borrow三个基本表中输入10条数据
- Book
- Card
- Borrow
-- Book基本表中输入10条数据
insert into Book (bno, category, title, publisher, pub_year, author, price) values
('b0001', 'eng.', 'Database systems', 'Springer', 2015, 'John', 28),
('b0002', 'eng.', 'Operation systems', 'Taylor', 2017, 'Michael', 35),
('b0003', 'sci.', 'Advanced mathematics', 'MIT', 2021, 'Catherine', 15),
('b0004', 'sci.', 'Discrete mathematics', 'MIT', 2019, 'Peter', 22),
('b0005', 'eng.', 'C language', 'Springer', 2016, 'Shannon', 40),
('b0006', 'liter.', 'Harry potter', 'Elseiver', 2018, 'Lisa', 19),
('b0007', 'liter.', 'Star wars', 'Elseiver', 2017, 'Jim', 25),
('b0008', 'his.', 'Modern chinese history', 'Taylor', 2016, 'Frank', 32),
('b0009', 'his.', 'The second world war', 'Taylor', 2020, 'Mark', 32),
('b0010', 'eng.', 'JAVA language', 'MIT', 2021, 'Sally', 38);
-- Card基本表中输入10条数据
insert into Card (cno, pname, department, ctype) values
('T001', 'Li Yuan', 'auto', 'T'),
('T002', 'Wang Li', 'auto', 'T'),
('T003', 'Zhang Qi', 'cs', 'T'),
('T004', 'Zhao Gao', 'math', 'T'),
('G001', 'Wu Mi', 'auto', 'G'),
('G002', 'Qian qi', 'cs', 'G'),
('U001', 'Li Fen', 'auto', 'U'),
('U002', 'Bai La', 'art', 'U'),
('F001', 'Zhang Fa', 'pku', 'F'),
('F002', 'Li Aigu', 'stu', 'F');
-- Borrow基本表中输入10条数据
insert into Borrow (cno, bno, borrow_date, return_date) values
('T001', 'b0001', '2021/1/10', '2021/3/4'),
('T001', 'b0002', '2021/5/10', '2021/8/1'),
('T001', 'b0005', '2021/1/1', '2021/3/4'),
('T001', 'b0010', '2021/1/1', '2021/3/4'),
('T003', 'b0001', '2021/5/15', '2021/8/4'),
('T003', 'b0002', '2021/3/15', '2021/4/4'),
('T003', 'b0003', '2021/2/15', '2021/3/4'),
('T003', 'b0004', '2020/2/15', '2020/3/4'),
('G002', 'b0005', '2021/5/5', '2021/7/14'),
('G002', 'b0007', '2021/8/10', null),
('G002', 'b0010', '2021/7/10', null),
('U001', 'b0009', '2021/2/2', '2021/3/9'),
('U001', 'b0008', '2021/2/2', '2021/3/9'),
('U002', 'b0003', '2021/3/6', '2021/3/9'),
('U002', 'b0004', '2021/4/2', '2021/5/9'),
('F001', 'b0001', '2021/8/12', '2021/8/15'),
('F001', 'b0002', '2021/2/1', '2021/3/1'),
('F001', 'b0003', '2021/6/12', '2021/7/11'),
('F002', 'b0009', '2021/8/20', null);
-- 查看建成的表格
select * from Book;
select * from Card;
select * from Borrow;数据修改
- 将Book表中price属性的值全部打八折
- 将Card表中 cno=‘F001’ 的姓名改为 ‘Li Ai’ 。
-- 将Book表中price属性的值全部打8折
update Book
set price = price*0.8;
-- 将Card表中cno=’F001’的姓名改为‘Li Ai’
update Card
set pname = 'Li Ai'
where cno = 'T001';数据删除
- 删除Borrow表中的cno = ‘F002’ 的借书记录
- 删除 ‘MIT’ 出版社的书籍（超纲知识点）
-- 删除Borrow表中cno=’F002’的借书记录
delete from Borrow
where cno = 'F002';
-- 删除‘MIT’出版社的书籍
-- 一、‘人工智能’做法
select * from Book where publisher = 'MIT';
delete from Borrow where bno in ('b0003', 'b0004', 'b0010');
delete from Book where bno in ('b0003', 'b0004', 'b0010');
select * from Book;
select * from Borrow;
-- 二、用临时表的方式删除‘MIT’出版社的书籍
create temporary table MIT_bno_table as
select bno from book where publisher = 'MIT';
delete from Borrow where bno in (select * from MIT_bno_table);
delete from Book where bno in (select * from MIT_bno_table);
select * from Book;
select * from Borrow;
drop table MIT_bno_table;思考
如果在创建表时没有定义数据完整性约束，则在数据录入或删除过程中会对数据库造成什么影响？
数据完整性约束是在表和字段上强制执行的数据检验规则，为了防止不规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，主要是对空值和重复值的约束，使不符合规范的数据不能进入数据库，以保证数据存储的完整性和准确性。
所以如果在创建表时没有定义数据完整性约束，则在数据录入或删除过程中可能会出现不规范数值的录入或者出现空值和重复值。
附录——完整代码
-- 查看现有的数据库
show databases;
-- 创建数据库
create database Library_01;
-- 切换到 Library_01 数据库
use Library_01;
-- 创建表
create table Book
(
bno char(8) not null comment'书籍编号' primary key,
category varchar(10) not null comment'书籍类别',
title varchar(40) not null comment'书名',
publisher varchar(30) comment'出版社',
pub_year year not null comment'出版年份',
author char(20) comment'作者',
price decimal(5,2) not null comment'价格'
);
create table Card
(
cno char(7) not null comment'借书证编号' primary key,
pname char(8) not null comment'姓名',
department varchar(5) comment'所在单位',
ctype char(1) not null comment'借书证类别' check(ctype in('T', 'G', 'U', 'F'))
);
-- drop table Borrow;
create table Borrow
(
cno char(7) not null comment'借书编号',
bno char(8) not null comment'书籍编号',
borrow_date date not null comment'借出日期',
return_date date comment'归还日期',
constraint C1 primary key(cno, bno),
constraint C2 foreign key(cno) references Card(cno),
constraint C3 foreign key(bno) references Book(bno)
);
-- Book基本表中输入10条数据
insert into Book (bno, category, title, publisher, pub_year, author, price) values
('b0001', 'eng.', 'Database systems', 'Springer', 2015, 'John', 28),
('b0002', 'eng.', 'Operation systems', 'Taylor', 2017, 'Michael', 35),
('b0003', 'sci.', 'Advanced mathematics', 'MIT', 2021, 'Catherine', 15),
('b0004', 'sci.', 'Discrete mathematics', 'MIT', 2019, 'Peter', 22),
('b0005', 'eng.', 'C language', 'Springer', 2016, 'Shannon', 40),
('b0006', 'liter.', 'Harry potter', 'Elseiver', 2018, 'Lisa', 19),
('b0007', 'liter.', 'Star wars', 'Elseiver', 2017, 'Jim', 25),
('b0008', 'his.', 'Modern chinese history', 'Taylor', 2016, 'Frank', 32),
('b0009', 'his.', 'The second world war', 'Taylor', 2020, 'Mark', 32),
('b0010', 'eng.', 'JAVA language', 'MIT', 2021, 'Sally', 38);
-- Card基本表中输入10条数据
insert into Card (cno, pname, department, ctype) values
('T001', 'Li Yuan', 'auto', 'T'),
('T002', 'Wang Li', 'auto', 'T'),
('T003', 'Zhang Qi', 'cs', 'T'),
('T004', 'Zhao Gao', 'math', 'T'),
('G001', 'Wu Mi', 'auto', 'G'),
('G002', 'Qian qi', 'cs', 'G'),
('U001', 'Li Fen', 'auto', 'U'),
('U002', 'Bai La', 'art', 'U'),
('F001', 'Zhang Fa', 'pku', 'F'),
('F002', 'Li Aigu', 'stu', 'F');
-- Borrow基本表中输入10条数据
insert into Borrow (cno, bno, borrow_date, return_date) values
('T001', 'b0001', '2021/1/10', '2021/3/4'),
('T001', 'b0002', '2021/5/10', '2021/8/1'),
('T001', 'b0005', '2021/1/1', '2021/3/4'),
('T001', 'b0010', '2021/1/1', '2021/3/4'),
('T003', 'b0001', '2021/5/15', '2021/8/4'),
('T003', 'b0002', '2021/3/15', '2021/4/4'),
('T003', 'b0003', '2021/2/15', '2021/3/4'),
('T003', 'b0004', '2020/2/15', '2020/3/4'),
('G002', 'b0005', '2021/5/5', '2021/7/14'),
('G002', 'b0007', '2021/8/10', null),
('G002', 'b0010', '2021/7/10', null),
('U001', 'b0009', '2021/2/2', '2021/3/9'),
('U001', 'b0008', '2021/2/2', '2021/3/9'),
('U002', 'b0003', '2021/3/6', '2021/3/9'),
('U002', 'b0004', '2021/4/2', '2021/5/9'),
('F001', 'b0001', '2021/8/12', '2021/8/15'),
('F001', 'b0002', '2021/2/1', '2021/3/1'),
('F001', 'b0003', '2021/6/12', '2021/7/11'),
('F002', 'b0009', '2021/8/20', null);
select * from Book;
select * from Card;
select * from Borrow;
-- 将Book表中price属性的值全部打8折
update Book
set price = price*0.8;
-- 将Card表中cno=’F001’的姓名改为‘Li Ai’
update Card
set pname = 'Li Ai'
where cno = 'T001';
-- 删除Borrow表中cno=’F002’的借书记录
delete from Borrow
where cno = 'F002';
-- 删除‘MIT’出版社的书籍
select * from Book where publisher = 'MIT';
delete from Borrow where bno in ('b0003', 'b0004', 'b0010');
delete from Book where bno in ('b0003', 'b0004', 'b0010');
select * from Book;
select * from Borrow;
-- 用临时表的方式删除‘MIT’出版社的书籍
create temporary table MIT_bno_table as
select bno from book where publisher = 'MIT';
delete from Borrow where bno in (select * from MIT_bno_table);
delete from Book where bno in (select * from MIT_bno_table);
select * from Book;
select * from Borrow;
drop table MIT_bno_table;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
