图书管理/学生成绩管理 数据库的简单例子（sql语言）

使用sql建立图书管理数据库

启动数据库：

首先是使用mac终端启动数据库的命令：

与linux传统一样，输入密码的时候不会有任何提示，不要以为是键盘坏了。

drop database OJBK_2;
CREATE database OJBK_2;
USE OJBK_2;
-- 
-- drop table STUDENT;
-- 
-- drop table SC;
-- drop table  Courses;
-- CREATE TABLE CLASSTABLE(
-- Class CHAR(8) not null,
-- Tno char(8) not null,
-- primary key(Class)
-- 
-- );
-- insert into CLASSTABLE(Class,Tno)
-- VALUES('1','T1');
-- insert into CLASSTABLE(Class,Tno)
-- VALUES('1','T1');


CREATE TABLE STUDENT(
Sno CHAR(8) not null,
Sname CHAR(10) not null,
Class CHAR(8)not null,
primary key(Sno)
);
-- 创建学生表格 有学号Sno 学生姓名Sname 班级号Class-------主键是学号Sno
create table Courses(
Cno char(8) not null,
Tno char(8) not null,
primary key(Cno)
);
-- 创建课程表格 有课程号Cno 该课程老师的编号Tno-------------主键是课程号Cno
insert into Courses(Cno,Tno)
VALUES('1','Tea1');
insert into Courses(Cno,Tno)
VALUES('2','Tea1');

-- 
CREATE table SC(
Sno CHAR(8) not null,
Cno char(8) not null,
Grade char(8),
primary key(Sno,Cno),
-- foreign key (Sno) references STUDENT(Sno),
foreign key (Cno) references Courses(Cno),-- 参考外键 课程表格的Cno
foreign key (Sno) references STUDENT(Sno)-- 参考外键 课程表格的Cno
-- Details varchar(8)
);
-- drop  table SC;
INSERT INTO STUDENT(Sno,Sname,Class)
VALUES('103','dld3','1');
INSERT INTO STUDENT(Sno,Sname,Class)
VALUES('104','dld4','1');
INSERT INTO STUDENT(Sno,Sname,Class)
VALUES('105','dld5','2');
INSERT INTO STUDENT(Sno,Sname,Class)
VALUES('106','dld6','2');


insert into SC(Sno,Cno,Grade)
VALUES('104','1','90');
insert into SC(Sno,Cno,Grade)
VALUES('104','2','60');
insert into SC(Sno,Cno,Grade)
VALUES('103','1','70');
insert into SC(Sno,Cno,Grade)
VALUES('105','1','100');
insert into SC(Sno,Cno,Grade)
VALUES('106','1','25');
insert into SC(Sno,Cno,Grade)
VALUES('103','2','30');
insert into SC(Sno,Cno,Grade)
VALUES('105','2','56');
-- =====================================================================
CREATE TABLE LibraryBooK(  -- 创建图书表格
Bno CHAR(8) not null,
Bname CHAR(10) not null,
BClass CHAR(8)not null,
primary key(Bno)
);

insert into LibraryBook(Bno,Bname,BClass)-- 填充图书表格
VALUES('1','Book_1','DB');
insert into LibraryBook(Bno,Bname,BClass)
VALUES('2','Book_2','IOT');
insert into LibraryBook(Bno,Bname,BClass)
VALUES('3','Book_3','IOT');
insert into LibraryBook(Bno,Bname,BClass)
VALUES('4','Book_4','DB');
insert into LibraryBook(Bno,Bname,BClass)
VALUES('5','Book_5','DB');

CREATE TABLE LibraryUser(-- 创建借书表格
UId CHAR(8) not null,
Uno CHAR(8) not null,
Uname CHAR(10) not null,
UClass CHAR(8)not null,
primary key(UId)
);
insert into LibraryUser(UId,Uno,Uname,UClass)-- 填充借书表格
VALUES('1','1','Uname_1','C1');
insert into LibraryUser(UId,Uno,Uname,UClass)-- 填充借书表格
VALUES('2','2','Uname_2','C1');
insert into LibraryUser(UId,Uno,Uname,UClass)-- 填充借书表格
VALUES('3','3','Uname_3','C1');
insert into LibraryUser(UId,Uno,Uname,UClass)-- 填充借书表格
VALUES('4','4','Uname_4','C1');
insert into LibraryUser(UId,Uno,Uname,UClass)-- 填充借书表格
VALUES('5','5','Uname_5','C2');


CREATE TABLE LibraryBorrow(-- 创建借书表格
RecordId CHAR(8) not null,
UId CHAR(8) not null,
Bno CHAR(8) not null,
Bname CHAR(10) not null,
BClass CHAR(8)not null,
Btime char(8),
Rtime char(8),
primary key(RecordId),
foreign key (Bno) references LibraryBook(Bno),
foreign key (UId) references LibraryUser(UId)
);

insert into LibraryBorrow(RecordId,UId,Bno,Bname,BClass,Btime,Rtime)-- 填充借书表格
VALUES('1','1','1','Book_2','IOT','2018',null);
insert into LibraryBorrow(RecordId,UId,Bno,Bname,BClass,Btime,Rtime)-- 填充借书表格
VALUES('2','2','2','Book_1','DB','2018','2019');
insert into LibraryBorrow(RecordId,UId,Bno,Bname,BClass,Btime,Rtime)-- 填充借书表格
VALUES('3','3','3','Book_3','IOT','2018',null);
insert into LibraryBorrow(RecordId,UId,Bno,Bname,BClass,Btime,Rtime)-- 填充借书表格
VALUES('4','4','2','Book_4','DB','2018',null);



-- 任务1：查看每本书被借阅的次数
select LibraryBooK.Bname 书名,count(*) 借阅数量
from LibraryBorrow,LibraryBooK
where LibraryBooK.Bno=LibraryBorrow.Bno
group by LibraryBorrow.Bno
order by 借阅数量 desc;

select LibraryBooK.BClass,count(LibraryBooK.Bno) 书籍数量
from LibraryBooK
group by LibraryBooK.BClass
order by 书籍数量 desc limit 1;


select LibraryUser.Uname 用户名,count(LibraryBorrow.Btime)-count(LibraryBorrow.Rtime) 未还书数量
from LibraryUser,LibraryBorrow
where LibraryUser.UId=LibraryBorrow.UId
group by LibraryUser.Uname
having LibraryUser.Uname='Uname_3'; 

select count(*) 借出数量,LibraryBorrow.Bno 
from LibraryBorrow
where LibraryBorrow.Rtime is null
group by Bno;

-- select LibraryBooK.BClass 书籍类型,count(LibraryBorrow.Btime) 借出数量
-- from LibraryBooK,LibraryBorrow
-- where LibraryBooK.Bno=LibraryBorrow.Bno
-- group by LibraryBooK.BClass
-- order by 借出数量;
-- 
-- select count(LibraryBorrow.Btime) 
-- from LibraryBorrow
-- where LibraryBorrow.Bno='1';
-- having LibraryUser.Uname='Uname_3';
-- =====++++++++++++++++++++++++

-- =====================================================================

-- select  STUDENT.Sno,STUDENT.Sname,STUDENT.Class,SC.Cno,SC.Grade
-- from STUDENT,SC
-- where SC.Cno='1' and STUDENT.Sno=SC.Sno
-- order by Grade desc;

-- select A.Sno,A.SNAME,B.details
-- from STUDENT A LEFT JOIN AWARD B ON A.Sno=B.Sno
-- WHERE A.Sno='103';

-- drop database OJBK_1;
-- select STUDENT.Sno,STUDENT.SNAME
-- from STUDENT;
-- 
-- drop  table SC;-- 
-- drop table STUDENT;
-- 
--  进行分级别查询
-- SELECT  
--  STUDENT.Class AS '班级号' ,
--  SC.Cno AS '课程号',
--   sum(CASE when Grade<60 then 1 else 0 end)   AS '不及格60-',  
--   sum(CASE when Grade>=60 and Grade<70 then 1 else 0 end)   AS '及格60-70',  
--   sum(CASE when Grade>=70 and Grade<80 then 1 else 0 end)   AS '中等70-80',  
--   sum(CASE when Grade>=80 and Grade<90 then 1 else 0 end)   AS '良好80-90',  
--   sum(CASE when Grade>=90 and Grade<=100 then 1 else 0 end)   AS '优秀90+' 
 --  
  -- STUDENT.Class
  -- Class AS 'Class'
-- from SC,STUDENT 
--  where STUDENT.Sno=SC.Sno 
-- group by STUDENT.Class ,SC.Cno;


-- select *
-- from SC
-- where Sno in 
-- (select STUDENT.Sno
-- from STUDENT
-- where STUDENT.Sno='104');
-- 
-- select SC.Grade,SC.Sno,SC.Cno,STUDENT .Class
-- from SC,STUDENT;