sql数据库练习题(涵盖大部分知识点)

# 建立表
create database library;
use library;
create table booktype(
  typeid int primary key,
  typename varchar(20)
 
);
 
create table readerType(
  # 如果这个字段需要进行链接外键，那么这个也没有要求主键约束，那么我们只能弄一个唯一约束
  retypeid int unique not null,
  readerName varchar(20) not null,
  borrowquantity int not null,
  borrowDay int
 
);
 
create table book(
  bookid char(10) PRIMARY key,
  bookName varchar(20) not null,
  typeid int,
  bookauthor varchar(20),
  bookPublisher varchar(20),
  bookPrice double,
  borrowsum int,
  FOREIGN key (typeid) REFERENCES booktype(typeid)
 
);
 
create table bookstorage(
  bookbarcode char(20) PRIMARY key,
  bookId char(10) not null,
  bookInTime datetime,
  bookStatus varchar(4),
  FOREIGN key (bookId) REFERENCES book(bookId)
 
 
);
 
 
 
create table reader(
  readerId char(10) PRIMARY key,
  readerName varchar(20) not null,
  readerPass varchar(20) not null,
  retypeId int not null,
  readerDate dateTime,
  readerStatus varchar(4),
  FOREIGN key (reTypeId) REFERENCES readerType(reTypeId)
 
);
 
create table bookBorrow(
  borrowId char(10) PRIMARY key,
  bookBarCode char(20) not null,
  readerId char(10) not null,
  borrowTime datetime,
  returnTime datetime,
  borrowStatus varchar(4),
  FOREIGN key (bookBarCode) REFERENCES bookStorage(bookBarCode),
  Foreign key (readerId) REFERENCES reader(readerId)
 
);
 
 
 
# 插入数据
 
insert into booktype VALUES
(1, "自然科学"),
(2, "数学"),
(3, "计算机"),
(4, "建筑水利"),
(5, "旅游地理"),
(6, "励志/自我实现"),
(7, "工业技术"),
(8, "基础医学"),
(9, "室内设计"),
(10, "人文景观");
 
insert into book values
('TP39/1712','Java程序设计',3,'陈永红','机械工业出版社',35.5,30),
('013452','离散数学',2,'张小新','机械工业出版社',45.5,10),
('TP/3452','JSP程序设计案例',3,'刘城清','电子工业出版社',42.8,8),
('TH/2345','机械设计手册',7,'黄明凡','人民邮电出版社',40,10),
('R/345677','中医的故事',8,'李奇德','国防工业出版社',20.0,5);
 
insert into bookstorage values
('132782','TP39/1712','2009-08-10 00:00:00','在馆'),
('132789','TP39/1712','2009-08-10 00:00:00','借出'),
('145234','013452','2008-12-06 00:00:00','借出'),
('145321','TP/3452','2007-11-04 00:00:00','借出'),
('156833','TH/2345','2009-12-04 00:00:00','借出'),
('345214','R/345677','2008-11-03 00:00:00','在馆');
 
insert into readertype values
(1,'学生',10,30),
(2,'教师',20,60),
(3,'管理员',15,30),
(4,'职工',15,20);
 
insert into reader values
('0016','苏小东',123456,1,'1999-09-09 00:00:00','有效'),
('0017','张明',123456,1,'2010-09-10 00:00:00','有效'),
('0018','梁君红',123456,1,'2010-09-10 00:00:00','有效'),
('0021','赵清远',123456,2,'2010-07-01 00:00:00','有效'),
('0034','李瑞清',123456,3,'2009-08-03 00:00:00','有效'),
('0042','张明月',123456,4,'1997-04-23 00:00:00','有效');
 
insert into bookborrow values
('001328','132789','0017','2011-01-24 00:00:00','2011-02-28 00:00:00','已还'),
('001356','145234','0018','2011-02-12 00:00:00','2011-02-27 00:00:00','已还'),
('001432','132782','0016','2011-03-04 00:00:00','2011-04-05 00:00:00','已还'),
('001435','145321','0021','2011-08-09 00:00:00','2011-09-02 00:00:00','已还'),
('001578','156833','0034','2011-10-01 00:00:00','2011-11-01 00:00:00','未还'),
('001679','345214','0042','2011-02-21 00:00:00','2011-03-05 00:00:00','未还');
 
 
 

-- 1.查询全体学生的学号, 姓名和年龄
select sid , sname , age  from student ;
 
-- 2.查询选修了课程的学生号
select distinct sid from scourse ;
 
-- 3.查询选修课程号c3的学号和成绩
select sid , score from scourse where cid ='c3';
 
-- 4.查询成绩高于85分的学生的学号，课程号和成绩
select  sid , cid, score from scourse where score > '85';
 
-- 5.查询没有选修C1也没有选修C2的学生学号，课程号和成绩
select sid , cid, score from scourse where cid != 'c1' and cid != 'c2';
 
-- 6.查询工资在1500~2000之间的教师的教师号，姓名和职称
select tname , tid , title from teacher where salary between '1500' and '2000';
 
-- 7.查询选修C1或C2的学生的学号，课程号和成绩
select score , b1.sid , cid from student as b1 LEFT JOIN scourse as b2 on b1.sid = b2.sid where cid = 'c1' or cid = 'c2';
 
-- 8.查询所有姓张的教师的教师号和姓名
select tid ,tname from teacher where tname like '张%';
 
-- 9.查询姓名中第2个汉字是力的教师号和姓名
select tid , tname from teacher where tname like '_力_';
 
-- 10.查询所有没有成绩的学生的学号和相应的课程号
#查询空值数据只能用is判断;
select sid , cid from scourse where score is null;
 
-- 11.查询选修C1的学生学号和成绩，并按照成绩降序排列
select sid , score from scourse  where cid = 'c1' ORDER BY score desc;
 
-- 12.查询选修C2,或者C3,或者C4,或者C5课程的学号，课程号和成绩, 查询结果按学号升序排列，学号相同，再按成绩降序排列
select sid , cid , score from scourse where cid != 'c1' order by sid asc , score desc ;
 
-- 13.查询选修C1的学生学号和成绩，并显示成绩前三的学生
select sid , score from scourse where cid = 'c1' order by score limit 3;
 
-- 14.查询计算机系学生的总数
select count(sid) as people from student where dept  = '计算机系' group by dept;
 
-- 15.查询每位学生的学号及其选修课的门数
select sid , count(cid) as '选修课门数' from scourse group by sid;
 
-- 16.在分组查询中使用having条件查询，平均成绩大于85的学生学号及平均成绩
select sid , avg(score) as '平均成绩'  from scourse  GROUP BY sid HAVING avg(score) > '85';
 
-- 17.查询选课在两门以上且各门课均及格的学生的学号及其总成绩, 查询结果按总成绩降序列出
select sum(score) , sid from scourse where score >= 60 group by sid HAVING count(cid) >= 2 ORDER BY sum(score) desc;
 
-- 18.查询所有选课学生的学号，姓名，选课名称及成绩
select b1.sid , b1.sname , b3.cname , b2.score  from student as b1 LEFT JOIN scourse as b2 on b1. sid = b2.sid LEFT JOIN courseinfo as b3 on b2.cid = b3.cid ;
 
-- 19.查询选修C1课程且成绩在60以上的所有学生的学号, 姓名和分数
select * from scourse as b1 LEFT JOIN student as b2 on b1.sid = b2.sid HAVING cid = 'c1' and score > '60';
 
-- 20.查询与李明教师职称相同的教师号, 姓名
select tid , tname from teacher where title = (select title from teacher where tname = '李明');