数据库——SQL练习

Posted on | In | 次阅读
Words count in article: 1.2k 字 | Reading time ≈ 5 min

计网的实验差不多整理完毕了，今天开始陆续整理一下数据库的实验吧，大三上最喜欢的课程就是数据库，所以我觉得作业的完成质量还是可以恭维的。

关系模式

关系模式使用的是课程给出的图书管理系统，涉及到六个关系模式，包括两个实体和四个联系。
先省略，以后有时间补齐。

SQL：数据查询

在bookmis数据库中完成下列SQL查询：
1、查找借了会员“李红 ”所借的全部图书的会员姓名
2、查找没有借科普类图书的会员姓名
3、查找借了2本及以上图书的银卡会员的姓名
4、将计算机类图书的入库数加1
5、用insert命令向图书类别表中添加记录（‘ca07’ , ‘儿童’）
6、用delete命令从图书类别表中删除类别‘儿童’
7、把金卡读者的读者名和性别存入另一个已知基本表vip_readers (Reader_name,sex)中
8、建立一个计算机类图书的视图，显示书编号、书名和出版社
9、建立一个分组视图，显示每种级别会员的数量
10、在（9）建立的分组视图中查询金卡会员的数量
11、自己编写并实现一个能成功执行的视图更新任务

查询方案

本次作业基于MAC OS X系统下的mysql workbench软件完成

Q. 查找借了会员“李红 ”所借的全部图书的会员姓名
A. 代码：基本思想为逻辑蕴含的转化:

1
2
3
4
5
6
7
8
9
10
11
select reader_name from readers
where reader_id in
        (select distinct reader_id from borrow ba
         where not exists
                (select * from borrow bb
                    where reader_id=(select reader_id from readers where reader_name='李红') AND
                    not exists
                        (select * from borrow bc
                            where bc.reader_id=ba.reader_id AND bc.book_id=bb.book_id)) 
                            AND 
                            reader_id<>(select reader_id from readers where reader_name='李红'));

Q. 查找没有借科普类图书的会员姓名
A. 代码:

1
2
3
4
5
6
select reader_name from readers
where reader_id in
    (select reader_id from borrow,books,b_category
        where borrow.book_id=books.book_id AND 
                   books.category_id=b_category.category_id AND
                   b_category.category<>'科普');

Q. 查找借了2本及以上图书的银卡会员的姓名
A.

1
2
3
4
5
6
select reader_name from readers
where level='银卡' AND reader_id in(
        select reader_id
        from borrow
        group by reader_id
        having count(reader_id)>=2);

Q. 将计算机类图书的入库数加1
A.

1
2
3
4
set sql_safe_updates=0; #mysql中必须降低安全等级才能够批量操作
update books
set Quantity_in=Quantity_in+1
where Quantity_in>=0;

Q. 用insert命令向图书类别表中添加记录（‘ca07’ , ‘儿童’）
A.

1
2
3
insert #插入指令
into b_category(category_id,category)
values('ca07','儿童');

Q. 用delete命令从图书类别表中删除类别‘儿童’
A.

1
2
3
delete #删除指令
from b_category
where(category='儿童');

Q. 把金卡读者的读者名和性别存入另一个已知基本表vip_readers (Reader_name,sex)中
A.

1
2
3
4
5
6
7
8
create table vip_readers
    (Reader_name varchar(50),
    sex varchar(2)
    ); #创建表
insert into vip_readers(Reader_name,sex) #插入数据
select readers.reader_name,readers.sex
from readers
where level='金卡';

Q. 建立一个计算机类图书的视图，显示书编号、书名和出版社
A.

1
2
3
4
5
create view cs_book(book_id,book_name,publishing) #创建视图
as
select book_id,book_name,publishing
from books,b_category
where books.category_id=b_category.category_id AND b_category.category='计算机';

Q. 建立一个分组视图，显示每种级别会员的数量
A.

1
2
3
4
5
create view vip_count(level,number)
as
select readers.level,count(level)
from readers
group by readers.level; #分组

Q. 在建立的分组视图中查询金卡会员的数量
A.

1
2
3
select level,number
from vip_count
where level='金卡';

Q. 自己编写并实现一个能成功执行的视图更新任务
A.

1
2
3
update cs_book
set book_name='abc'
where book_id='b001';

SQL：数据定义

1、新建数据库Student1
2、在Student1数据库中新建表S1、C1和 SC1 ,指明主外键（结构与S,C,SC相同）
3、在表S1中新增一列addr CHAR（20)
4、删除表S1中的列addr
5、在表SC1的列sno上建立升序索引
6、删除表SC1

定义方案

Q. 新建数据库Student1
A.

1
create schema student1

Q. 在Student1数据库中新建表S1、C1和 SC1 ,指明主外键（结构与S,C,SC相同）
A.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table S1(
    sno char(4) primary key not null,#主键
    sname char(8) not null,
    age int,
    sex char(1)
); #建表S1
create table C1(
    cno char(4) primary key not null,#主键
    cname char(8) not null,
    tname char(10)
); #建表C1
create table SC1(
    sno char(4) not null,
    cno char(4) not null,
    grade int,
    primary key(sno,cno),#指定主键
    foreign key(sno) references S1(sno),#指定外键
    foreign key(cno) references C1(cno),#指定外键
    check (grade is null or grade between 0 and 100)
); #建表SC1

Q. 在表S1中新增一列addr CHAR（20)
A.

1
2
alter table S1 
add addr CHAR(20);

Q. 删除表S1中的列addr
A.

1
2
alter table S1 
drop addr;

Q. 在表SC1的列sno上建立升序索引
A.

1
2
create index sno1 
on SC1(sno asc);

Q. 删除表SC1
A.

1
drop table SC1;

小结

SQL的数据定义和数据查询语句都是基础的语法，也是在后续编程中最常用到的语句，这里的基础一定要打牢，做项目和课程设计是也就是建立在这些基础语法之上的。

小手一抖⬇️