【无标题】MySQL知识点分享

一：

学生成绩管理系统数据库SchoolDB 的数据如单元4的实战演练中所示，对SchoolDB数据库完成以下查询。

代码示例：

（1）查询全体学生的姓名和年龄，要求分别用name和age表示列名。提示年龄可以根据当前日期和出生日期算出，日期取年的函数YEAR,取系统当前时间的函数为NOW。

use schooldb;

select 姓名 name,year(now())-year(出生日期) age from student;

（2） 查询成绩表，成绩列用优（>=90）良(75-90),及格(60-75)，不及格(=90 then '优'

select `学号`,`课程号`,

CASE

     when 成绩>=90 then '优'

     when 成绩>=75 and 成绩 =60 and 成绩 <75 then '及格'

     when 成绩<60 then '不及格'

END as 成绩 from score;

（3）查询学时大于等于48学生的课程名和学分。

SELECT 课程名,学分 FROM course WHERE 学时>=48;

（4）查询前置课为空的课程名和学期

SELECT 课程名,学期 FROM course WHERE 前置课 is NULL；

（5）查询姓‘王’且名字为3个字的学生记录。

SELECT * FROM members WHERE 姓名 LIKE '王__';

2.多表查询

（1）查询所有学生的学号、姓名、课程号和成绩。

SELCEt student.`学号`,student.`姓名`,score.`课程号`,score.`成绩`

FROM student 

INNER JOIN score ON student.`学号`=score.`学号`；

（2）查询会计学院全体同学的学号、姓名和班级名称

SELECT student.`学号`,student.`性别`,class.`班级名称`

FROM student 

INNER JOIN class ON student.`班级编号`=class.`班级编号`WHERE class.`院系`='会计学院';

（3）查询成绩在90分以上的学生的学号、姓名和成绩

SELECT student.`学号`,student.`性名`,score.`成绩` 

FROM student 

INNER JOIN score ON student.`学号`=score.`学号` WHERE score.`成绩` >90;

（4）使用左外连接，查询所有课程的课程号、课程名和选修了该课程的学生的学号和成绩。

SELECT course 课程号,课程,学号,成绩 from course a left outer join score b on b.课程号=a.课程号

where 学号 is not null;

没有学生选修的课程也要包括。

（5）使用子查询查找“计算机14-1班”所有学生的学号、姓名。

SELECT 学号,姓名

FROM student 

WHERE 班级编号 in (SELECT 班级编号 FROM class WHERE 班级名称='计算机14-1班');

3.分类汇总和排序

（1）按性别统计学生人数

select 性别,count(*)   学生人数 from student group by 性别 

（2）统计每个学生的选课人数、平均分、最高分。

select a.学号,a.姓名,count(*) as 选课门数,avg(a.成绩) as 平均分, max(b.成绩) 最高分

from student a 

on a .学号=b.学号

group by a.学号,a.姓名;

（3）查询平均分在80分以上的每个学生的选课门数，平均分、最高分

select a.学号,a.姓名,count(*) as 选课门数,avg(a.成绩) as 平均分, max(b.成绩) 最高分

from student a  inner score b

on a .学号=b.学号

having 平均分>80

group by a.学号,a.姓名;

（4）先按性别，再按民族统计学生人数，并按人数从小到大排序。

select 性别,民族,count(*) 学生人数 from student group by 性别,民族 order by 学生人数 asc;

二：

创建图书管理系统中的图书借阅数据库名为LibraryDB,包含读者表、读者类型表、库存表、图书表、借阅表、各表的结构如表1-5下图所示。

代码示例：

三：

表结构如下：

在TBL_BookInfo中找出页数大于100页并且小于200页的书（分别用比较运算符和BETWEEN两种方法实现）

2.在TBL_Bookinfo 表中查询所有“清华大学出版社”出版的书的书名, 作者及出版日期。

3.从TBL_Bookinfo表中检索出不是电子工业出版社和清华大学出版社出版的图书。

4.在TBL_Bookinfo显示“中国古代教育文选”和“德国近代文学史”两本书的信息。

5.在TBL_Bookinfo显示“清华大学出版社”在1980后出版图书的书名，作者，页数。

代码示例：

SELECT * FROM tbl_bookinfo WHERE PageCount>100 and PageCount<200;

SELECT * FROM tbl_bookinfo WHERE PageCount BETWEEN 100 and 200;

SELECT * FROM tbl_bookinfo WHERE Publisher='清华大学出版社';

SELECT * FROM tbl_bookinfo WHERE Publisher NOT IN('清华大学出版社','电子工业出版社');

SELECT * FROM tbl_bookinfo WHERE BookName='中国古代教育文选' OR BookName='德国近代文学史';

SELECT BookName,Author,PageCount FROM tbl_bookinfo WHERE Publisher='清华大学出版社' AND PublishDate>'1980-1-1';

四：

1.查询各出版社图书的平均页数、最大页数、最小页数。

2.从TBL_Bookinfo 表查询出各图书类别的图书总数在3册及以上的图书类别、图书总数。

3.从TBL_ User 表中统计出每班男女人数(列标题依次改为班级、性别和人数)。

4.从TBL_Bookinfo表查询出版社的图书册数最少的前三位的出版社(列标题依次改为出版社、册数)。

5.查询4月、7月各出版社出版图书的总数。

代码示例：

-- 1.查询各出版社图书的平均页数、最大页数、最小页数。

SELECT Publisher,AVG(PageCount),MAX(PageCount),MIN(PageCount) FROM tbl_bookinfo GROUP BY Publisher;

-- 2.从TBL_Bookinfo 表查询出各图书类别的图书总数在3册及以上的图书类别、图书总数。

SELECT ClassId,COUNT(*) FROM tbl_bookinfo GROUP BY ClassId HAVING COUNT(*)>=3;

-- 3.从TBL_ User 表中统计出每班男女人数(列标题依次改为班级、性别和人数)。

SELECT Class 班级,Sex 性别,COUNT(*) FROM tbl_user GROUP BY Class,Sex;

-- 4.从TBL_Bookinfo表查询出版社的图书册数最少的前三位的出版社(列标题依次改为出版社、册数)。

SELECT Publisher,COUNT(*) FROM tbl_bookinfo GROUP BY Publisher ORDER BY COUNT(*) ASC LIMIT 3;

-- 5.查询4月、7月各出版社出版图书的总数。

SELECT Publisher,COUNT(*) FROM tbl_bookinfo WHERE MONTH(PublishDate) IN(4,7) GROUP BY Publisher;

五：

1.查询出图书的索取号，书名，出版社，类别名称。

2.在TBL_Borrowinfo表中用SQL语句插入如下两条记录：

          ('G40-092.2/5', '20170310088', '2007-7-6',null,0)

          ('G633.7/202', '20170310088', '2007-7-26',null,0)

3. 查询出借阅索取号为“G40-092.2/5”图书的读者姓名，班级。

4.显示没有归还书的书名、读者姓名、读者的班级。

5.查询出所有借过书的女生的信息。

6.查询出所有2007年7月、8月被借出过的书的信息及借出时间。

7.查询出男、女生各借过多少本书。

8.查询出各出版社在1985年以后出版的图书被借出的数量，并按借出数量的降序显示查询结果。

9.查询出2008年的所有借阅信息（包括：书名、借书时间、读者名）。

10.从图书信息表及图书类型表中检索书名、作者、出版社、类别编号、类别名称，所有的类别名称均显示。(分别使用右外连接和左外连接完成)。

代码示例：

-- 1.查询出图书的索取号，书名，出版社，类别名称。

SELECT tbl_bookinfo.bookId,tbl_bookinfo.BookName,tbl_bookinfo.Publisher,tbl_bookclass.ClassName

FROM tbl_bookinfo

INNER JOIN tbl_bookclass ON tbl_bookinfo.ClassId=tbl_bookclass.ClassId

-- 2.在TBL_Borrowinfo表中用SQL语句插入如下两条记录：

          -- ('G40-092.2/5', '20170310088', '2007-7-6',null,0)

          -- ('G633.7/202', '20170310088', '2007-7-26',null,0)

INSERT INTO TBL_Borrowinfo (BookID,userID,BorrowDate,ReturnDate) VALUES('G40-092.2/5','20170310088','2007-7-6',null,0);

INSERT INTO tbl_borrowinfo (BookID,userID,BorrowDate,ReturnDate) VALUES('G633.7/202','20170310088','2007-7-26',null,0);

-- 3. 查询出借阅索取号为“G40-092.2/5”图书的读者姓名，班级。

SELECT a.UserName,a.Class

FROM tbl_user a 

INNER JOIN tbl_borrowinfo b 

ON b.BookID='G40-092.2/5';

-- 4.显示没有归还书的书名、读者姓名、读者的班级。

SELECT a.BookName,c.UserName,c.Class

FROM tbl_bookinfo a 

INNER JOIN tbl_borrowinfo b 

ON b.BookID=a.BookID

INNER JOIN tbl_user c 

ON c.UserID=c.UserID

WHERE b.IsReturned=0;

-- 5.查询出所有借过书的女生的信息。

select a.*

FROM tbl_user a INNER JOIN tbl_borrowinfo b ON b.UserID=a.UserID

WHERE a.Sex='女';

-- 6.查询出所有2007年7月、8月被借出过的书的信息及借出时间。

SELECT a.*,b.BorrowDate

FROM tbl_bookinfo a

INNER JOIN tbl_borrowinfo b

ON a.BookID=b.BookID

WHERE month (b.BorrowDate) in ('7','8');

-- 7.查询出男、女生各借过多少本书。

SELECT b.Sex,COUNT(*) 

FROM tbl_borrowinfo a 

INNER JOIN tbl_user b 

ON b.UserID=a.UserID

GROUP BY b.Sex; 

-- 8.查询出各出版社在1985年以后出版的图书被借出的数量，并按借出数量的降序显示查询结果。

SELECT a.Publisher,count(*)

FROM tbl_bookinfo a 

INNER JOIN tbl_borrowinfo b 

ON b.BookID=a.BookID

WHERE a.PublishDate>='1985-01-01' 

GROUP BY a.Publisher ORDER BY count(*) desc;

-- 9.查询出2008年的所有借阅信息（包括：书名、借书时间、读者名）。

SELECT a.BookName,b.BorrowDate,c.UserName

FROM tbl_bookinfo a 

INNER JOIN tbl_borrowinfo b 

ON b.BookID=a.BookID

JOIN tbl_user c ON c.UserID=b.UserID

WHERE YEAR(b.BorrowDate)='2008';

-- 10.从图书信息表及图书类型表中检索书名、作者、出版社、类别编号、类别名称，所有的类别名称均显示。(分别使用右外连接和左外连接完成)。

SELECT a.BookID,a.Author,a.Publisher,a.ClassId,b.ClassName

FROM tbl_bookinfo a LEFT JOIN tbl

六：

1.查询与《管理信息系统原理与实践》同一出版社的所有图书的信息。

2.显示大于平均页数的图书的书名、作者、出版日期、页数。

代码示例：

-- 1.查询与《管理信息系统原理与实践》同一出版社的所有图书的信息。

SELECT * 

FROM tbl_bookinfo 

WHERE Publisher=(SELECT BookName FROM tbl_bookinfo WHERE BookName='管理信息系统原理与实践');

-- 2.显示大于平均页数的图书的书名、作者、出版日期、页数。

SELECT BookName,Author,PublishDate,PageCount

FROM tbl_bookinfo

WHERE PageCount>(SELECT avg(PageCount) FROM tbl_bookinfo);

七：

实训练习

library00库

1.创建一个名为view_book1的视图 ，功能是从图书表中查询书名中包含有“工程数学”的所有记录的索取号，书名，作者，出版社，出版日期，类别编号。

2.创建一个名为view_book2的视图 ，功能是查询出图书的索取号，书名，出版社，类别名称。

3.创建一个借书情况视图view_borrow，用于从图书借阅表、图书信息表、读者信息表中查询所有读者的借书情况，其中包括的数据项有：读者姓名、性别、图书名称、出版社、出版日期、借书日期、还书日期。

4.修改视图view_book2，并按出版社排序。

5.用SQL语句为view_book1视图插入一条数据：

('TB11/6','高等工程数学方法','胡映电','电子工业出版社','1998-07-15','T')

代码示例：

1.创建一个名为view_book1的视图 ，功能是从图书表中查询书名中包含有“工程数学”的所有记录的索取号，书名，作者，出版社，出版日期，类别编号。

CREATE VIEW view_book1 AS

SELECT * FROM tbl_bookinfo WHERE BookName LIKE '%工程数学%';

SELECT * FROM view_book1;

2.创建一个名为view_book2的视图 ，功能是查询出图书的索取号，书名，出版社，类别名称。

CREATE VIEW view_book2 AS

SELECT a.BookID,a.BookName,a.Publisher,b.ClassName

FROM tbl_bookinfo a

INNER JOIN tbl_bookclass b 

ON a.ClassId=b.ClassId;

SELECT * FROM view_book2;

3.创建一个借书情况视图view_borrow，用于从图书借阅表、图书信息表、读者信息表中查询所有读者的借书情况，其中包括的数据项有：

读者姓名、性别、图书名称、出版社、出版日期、借书日期、还书日期。

CREATE VIEW view_borrow AS

SELECT tbl_user.UserName,tbl_user.Sex,tbl_bookinfo.BookName,tbl_bookinfo.Publisher,tbl_bookinfo.PublishDate,

tbl_borrowinfo.BorrowData,tbl_borrowinfo.ReturnData

FROM tbl_user 

INNER JOIN tbl_borrowinfo 

ON tbl_user.UserID=tbl_borrowinfo.UserID

INNER JOIN tbl_bookinfo 

ON tbl_borrowinfo.BookID=tbl_bookinfo.BookID;

4.修改视图view_book2，并按出版社排序。

SELECT * FROM view_book2 ORDER BY publisher desc;

5.用SQL语句为view_book1视图插入一条数据：

('TB11/6','高等工程数学方法','胡映电','电子工业出版社','1998-07-15','T')

INSERT INTO view_book1 VALUES ('TB11/6','','高等工程数学方法','胡映电','1998-07-15','','电子工业出版社','T');

八：

1.创建一个名为SelectUser的无参存储过程，实现查询所有读者相关信息。

2. 调用该存储过程。

3.查询该存储过程的创建过程。

4.删除该存储过程。

5.创建一个名为SelectBook的无参存储过程，实现查询所有图书相关信息;

6.调用该存储过程。

知识点：

1.创建存储过程 create procedure 存储过程（参数名 参数类型）

2.删除存储过程 drop procedure 存储过程名

3.调用存储过程 call 存储过程名

代码示例：

1.创建一个名为SelectUser的无参存储过程，实现查询所有读者相关信息。

delimiter $$

CREATE PROCEDURE SelectUser()

BEGIN

SELECT * FROM tbl_user;

END

$$

delimiter;

2. 调用该存储过程。

CALL SelectUser;

3.查询该存储过程的创建过程。

SHOW CREATE PROCEDURE SelectUser;

4.删除该存储过程。

DROP PROCEDURE SelectUser;

5.创建一个名为SelectBook的无参存储过程，实现查询所有图书相关信息;

delimiter //

CREATE PROCEDURE SelectBook()

BEGIN

SELECT * FROM tbl_bookinfo;

END

//

delimiter;

6.调用该存储过程。

CALL SelectBook;