数据库管理系统及其应用开发环境的创建使用

了解数据库应用开发环境的建立与使用；掌握SQL 语言的使用；通过实践理解关系数据模型的相关概念；掌握数据库应用开发环境的使用；掌握创建、删除数据库的方法；掌握创建基本表、查看表属性、修改属性的方法；掌握向表中添加、删除以及修改数据的方法；掌握查询分析器的使用方法；掌握查询语句在单表查询中的应用；掌握复杂查询、多表查询的方法；掌握视图的使用方法；巩固数据库的基础知识。

实验环境：

openEuler操作系统上的openGauss数据库

实验内容

1.创建数据库，创建并维护基本表的结构与数据

（1）创建数据库

以学校图书馆借书为背景，创建相关数据库，其中包含4个基本表
学生（借书证号，姓名，年级，专业，性别）
出版社（出版社名，电子邮件，地址，电话）
图书（书号，书名，剩余数量，出版社名字）
借书（借书证号，书号，借书时间，还书时间）

创建名为mydb的数据库

1
create database mydb;

切换至mydb数据库

1
\c mydb;

（2）创建4个基本表

创建学生表

1
2
3
4
5
6
7
8
create table student
(
s_id varchar(10) not null primary key unique,
s_name varchar(10) not null,
s_grade varchar(10) not null,
s_major varchar(10) not null,
s_sex varchar(2) not null
);

创建出版社表

1
2
3
4
5
6
7
create table press
(
p_name varchar(10) not null primary key,
p_email varchar(20) not null,
p_address varchar(20) not null,
p_tel varchar(15) not null unique
);

创建图书表

1
2
3
4
5
6
7
8
create table book
(
b_id varchar(10) not null primary key unique,
b_name varchar(10) not null,
b_num integer default 0,
p_name varchar(10) not null,
constraint book_press_fk foreign key(p_name) references press(p_name)
);

创建借书表

1
2
3
4
5
6
7
create table borrow_book
(
b_id varchar(10) not null,
s_id varchar(10) not null,
b_time date not null,
r_time date default null
);

（3）对表的结构进行修改操作

press 表的原属性如下：

将p_tel字段改成p_telephone,

1
alter table press rename p_tel to p_telephone;

在press表中新增字段p_postcode

1
alter table press add p_postcode varchar(10) not null;

修改后的press表属性如下：

student表原属性如下：

删除student表中的s_sex字段

1
alter table student drop s_sex;

修改后的student表属性如下：

（4）创建索引及删除索引

为student表的s_name字段创建名为index_s_name的索引

1
create index index_s_name on student(s_name);

创建index_s_name索引后的student表属性如下：

删除刚才创建的s_name_index索引

1
drop index index_s_name;

删除该索引后student表恢复

（5）向表中录入若干数据，对表中数据进行修改和删除操作

在student表中插入1行数据

1
2
insert into student(s_id,s_name,s_grade,s_major) values
(‘043858’, ‘zhangsan’, 2020 , ‘computer’);

再插入4行数据

1
2
3
4
5
insert into student values
(‘878433’ , ‘lisi’ , 2019 , ‘computer’),
(‘234453’ , ‘wangwu’ , 2019, ‘English’),
(‘0003837’ , ‘zhaoliu’ , 2022 , ‘psychology’),
(‘449763’ , ‘sunqi’ , 2020 , ‘music’);

插入5行数据后student表数据如下：

press表

1
2
3
insert into press values
('press1','1243323@163.com','fuzhouminhou','13463363763','334543'),
('press2','3467736@qq.com','fuzhougulou','14555697883','334539');

book表

1
2
3
4
5
insert into book values
('23653','book1',3,'press1'),
('34224','book2',0,'press2'),
('23474','book3',13,'press1'),
('33227','book4',1 ,'press1');

borrow_book表

1
2
3
4
insert into borrow_book values
('34224','003837','2022-10-29'),
('33227','449763','2022-10-11','2022-10-28'),
('34224','043858','2022-10-22','2022-11-2');

将student表第4行元组中s_id ‘0003837’改成 ‘003837’

1
update student set s_id = ‘003837’where s_id = ‘0003837’

删除第2行元组

1
delete from student where s_id = ‘878433’;

2.数据库查询，视图使用

（1）单表查询

查询所有学生的借书证号和姓名

1
select s_id,s_name from student;

（2）多表连接查询并排序输出

连接student和borrow并查询相关信息按照借书时间降序

1
2
3
4
select student.s_id,s_name,b_id,b_time,r_time 
from student join borrow_book
on student.s_id = borrow_book.s_id
order by b_time desc ;

（3）使用聚集函数的查询

查询图书种类数目

1
select count(*)  as total from book;

查询最新的借书日期

1
select max(b_time) as latest_date from borrow_book;

（4）分组查询

book表按照出版社名字分组查询每个出版社的图书数目

1
select p_name,count(b_id) from book group by p_name;

（5）嵌套查询

查询最新的借书记录的各项信息

1
2
3
select * from borrow_book 
where b_time in 
(select max(b_time) from borrow_book);

（6）创建并使用视图查询。

为student、book、borrow_book三个表创建视图

1
2
3
4
create view borrow_view (s_id,s_name,b_id,b_name,b_time,r_time) 
as select student.s_id,s_name,book.b_id,b_name,b_time,r_time 
from borrow_book,student,book 
where student.s_id=borrow_book.s_id and book.b_id=borrow_book.b_id;

查询该视图内容

实验总结

MySQL的sql语句在openGuass上基本上都适用，但是也有很多不同之处，比如查询表的属性用的语句是\d，另外切换数据库和修改表的列的语句也不相同（目前只发现了这些）。
实验进行中出现一个小问题让我花了很多时间，我刚开始的时候会先把sql语句打在word上，然后再复制粘贴到cloudshell上，导致插入数据时一直报错，原因时word上的单引号复制到shell上并不符合sql语句中的单引号的规范，这说明我还不够细节。
创建，删除，插入，更新等基本语句都大致掌握了，但是对于复杂查询的使用有时候逻辑上没办法理顺各级查询的意思，还需要再进行一些练习。