[MySQL光速入门]007 作业解答

创建数据库`library`

创建数据表

图书类别表(booktype)

序号	属性名称	含义	数据类型	是否为空	备注
1	`typeid`	类别编号	`int`	`not null`	主键
2	`typename`	类别名称	`varchar(20)`	`null`

图书信息表(book)

序号	属性名称	含义	数据类型	是否为空	备注
1	`bookid`	图书编号	`char(10)`	`not null`	主键
2	`bookname`	图书名称	`varchar(20)`	`not null`
3	`typeid`	类别编号	`int`	`null`	外键
4	`bookauthor`	图书作者	`varchar(20)`	`null`
5	`bookpublisher`	出版社	`varchar(50)`	`null`
6	`bookprice`	图书价格	`doublue`	`null`
7	`borrowsum`	借阅次数	`int`	`null`

图书存储信息表(bookstorage)

序号	属性名称	含义	数据类型	是否为空	备注
1	`bookbarcode`	图书条码	`char(20)`	`not null`	主键
2	`bookid`	图书编号	`char(10)`	`not null`	外键
3	`bookintime`	图书入馆时间	`datetime`	`null`
4	`bookstatus`	图书状态	`varchar(4)`	`null`

读者类别表(readertype)

序号	属性名称	含义	数据类型	是否为空
1	`retypeid`	类别编号	`int`	`not null`
2	`typename`	类别名称	`varchar(20)`	`not null`
3	`borrowquantity`	可借数量	`int`	`not null`
4	`borrowday`	可借天数	`int`	`null`

读者信息表(reader)

序号	属性名称	含义	数据类型	是否为空	备注
1	`readerid`	读者编号	`char(10)`	`not null`	主键
2	`readername`	读者姓名	`varchar(20)`	`not null`
3	`readerpass`	读者密码	`varchar(20)`	`not null`
4	`retypeid`	类别编号	`int`	`null`	外键
5	`readerdate`	发证日期	`datetime`	`null`
6	`readerstatus`	借书证状态	`varchar(4)`	`null`

图书借阅表(bookborrow)

序号	属性名称	含义	数据类型	是否为空	备注
1	`borrowid`	借阅号	`char(10)`	`not null`	主键
2	`bookbarcode`	图书条码	`char(20)`	`not null`	外键
3	`readerid`	读者编号	`char(10)`	`not null`	外键
4	`borrowtime`	借书日期	`datetime`	`null`
5	`returntime`	还书日期	`datetime`	`null`
6	`borrowstatus`	借阅状态	`varchar(4)`	`null`

为创建的表插入如下数据

图书类别表(booktype)

`typeid`	`typename`
1	自然科学
2	数学
3	计算机
4	建筑水利
5	旅游地理
6	励志/自我实现
7	工业技术
8	基础医学
9	室内设计
10	人文景观

图书信息表(book)

`bookid`	`bookname`	`typeid`	`bookauthor`	`bookpublisher`	`bookprice`	`borrowsum`
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

图书存储信息表(bookstorage)

`bookbarcode`	`bookid`	`bookintime`	`bookstatus`
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	在馆

读者类别表(readertype)

`retypeid`	`typename`	`borrowquantity`	`borrowday`
1	学生	10	30
2	教师	20	60
3	管理员	15	30
4	职工	15	20

读者信息表(reader)

`readerid`	`readername`	`readerpass`	`retypeid`	`readerdate`	`readerstatus`
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	有效

图书借阅表(bookborrow)

`borrowid`	`bookbarcode`	`readerid`	`borrowtime`	`returntime`	`borrowstatus`
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 查询book表的书号, 书名和借出数量

select `bookid`,`bookname`,`borrowsum` from book;

2 用别名查询book表的书号, 书名和借出数量

select `bookid` as 书号,`bookname` as 书名,`borrowsum` as 借出数量 from book;

3 查询类型是学生的所有读者的信息

select * from reader where retypeid = 1;

4 查询借出时间在2011年3月1日和2011年10月1日之间的图书

select * from bookborrow where borrowtime >= '2011-3-1' and borrowtime <= '2011-10-1';

5 查询借出时间在2011年3月1日之后并且还书时间在2011年10月1日之前的图书

select * from bookborrow where borrowtime >= '2011-3-1' and returntime <= '2011-10-1';

6 in关键字查询类型是老师或者学生的读者信息

select * from reader where retypeid in (1,2);

7 查询书名包含程序的图书信息

select * from book where bookname like '%程序%';

8 查询借出数量排名前3的图书

select * from book order by borrowsum desc limit 3;

9 按图书借出数量从高到低查询, 如果借出数量相同, 再按价格高低排序

select * from book order by borrowsum desc,bookprice desc;

10 查找图书表中从第2条记录开始的5条记录的名称和价格

select bookname,bookprice from book limit 1,5;

11 按照读者类型分组查询借出图书的数量

SELECT
	reader.retypeid,
	count( * ) 
FROM
	bookborrow
	JOIN reader ON reader.readerid = bookborrow.readerid 
GROUP BY
	reader.retypeid;

12 查询各个出版社的图书的平均价格

select bookpublisher,avg(bookprice) from book group by bookpublisher;

13 查询读者编号是0021的读者借书的信息,包括读者名, 图书号, 借出时间和归还时间

SELECT
	reader.readername,
	bookstorage.bookid,
	bookborrow.borrowtime,
	bookborrow.returntime 
FROM
	reader
	JOIN bookborrow ON bookborrow.readerid = reader.readerid
	JOIN bookstorage ON bookstorage.bookbarcode = bookborrow.bookbarcode 
WHERE
	reader.readerid = '0021';

14 查询所有读者的借书信息, 包括读者名, 图书名, 借出时间和归还时间

SELECT
	reader.readername,
	book.bookname,
	bookstorage.bookid,
	bookborrow.borrowtime,
	bookborrow.returntime 
FROM
	reader
	JOIN bookborrow ON bookborrow.readerid = reader.readerid
	JOIN bookstorage ON bookstorage.bookbarcode = bookborrow.bookbarcode
	JOIN book ON book.bookid = bookstorage.bookid;

15 查询借出数量大于书籍编号为TP/3452的借出数量的图书信息

select * from book where borrowsum > (select borrowsum from book where bookid = 'TP/3452');

16 查询已借了图书的读者信息

select * from reader where readerid in (select readerid from bookborrow WHERE borrowstatus = '未还');

创建数据库`stucourse`

没有表结构, 根据数据, 自己决定使用哪种数据类型

学生表(student)

`sid`	`sname`	`sex`	`age`	`dept`
1001	宋江	男	25	计算机系
3002	张明	男	23	生物系
1003	李小鹏	男	26	计算机系
1004	郑冬	女	25	计算机系
4005	李晓红	女	27	工商管理
5006	赵紫月	女	24	外语系

教师表(teacher)

`tid`	`tname`	`title`	`salary`	`dept`	`cid`
3102	李明	初级	2500	计算机系	C1
3108	黄晓明	初级	4000	生物系	C3
4105	张晓红	中级	3500	工商管理	C2
5102	宋力跃	高级	3500	物理系	C4
3106	赵明阳	初级	1500	地理系	C2
7108	张丽	高级	3500	生物系	C3
9103	王彬	高级	3500	计算机系	C1
7101	王力号	初级	1800	生物系	C1

课程表(courseinfo)

`cid`	`cname`	`cbook`	`ctest`	`dept`
`C1`	计算机基础	`b1231`	2009-4-6	计算机系
`C2`	工商管理基础	`b1232`	2009-7-16	工商管理
`C3`	生物科学	`b1233`	2010-3-6	生物系
`C4`	大学物理	`b1234`	2009-4-26	物理系
`C5`	数据库原理	`b1235`	2010-2-6	计算机系

选课表(scourse)

`sid`	`score`	`cid`	`tid`
1001	87	`C1`	3102
1001	77	`C2`	4105
1001	63	`C3`	3108
1001	56	`C4`	5102
3002	78	`C3`	3108
3002	78	`C4`	5102
1003	89	`C1`	9103
1004	56	`C2`	3106
4005	87	`C4`	5102
5006	`null`	`C1`	7101

教材表(bookinfo)

`bid`	`bname`	`bpublish`	`bprice`	`quantity`
`b1231`	`Image Processing`	人民大学出版社	34.56	8
`b1212`	`Signal Processing`	清华大学出版社	51.75	10
`b1233`	`Digital Signal Processing`	邮电出版社	48.5	11
`b1234`	`The Logic Circuit`	北大出版社	49.2	40
`b1235`	`SQL Techniques`	邮电出版社	65.4	20

查询数据

查询全体学生的学号, 姓名和年龄
```
select sid,sname,age from student;
```

查询选修了课程的学生号

select sid from student where sid in (select sid from scourse);

查询选修课程号c3的学号和成绩

select sid, score from scourse where cid = 'C3';

查询成绩高于85分的学生的学号，课程号和成绩

SELECT
	sid,
	score,
	cid 
FROM
	scourse 
WHERE
	score > 85;

查询没有选修C1也没有选修C2的学生学号，课程号和成绩

SELECT
	sid,
	score,
	cid 
FROM
	scourse 
WHERE
	cid NOT IN ( 'c1', 'c2' );

查询工资在1500~2000之间的教师的教师号，姓名和职称

SELECT
	tid,
	tname,
	title
FROM
	teacher 
WHERE
	salary BETWEEN 1500 
	AND 2000;

查询选修C1或C2的学生的学号，课程号和成绩

SELECT
	sid,
	score,
	cid 
FROM
	scourse 
WHERE
	cid IN ( 'c1', 'c2' );

查询所有姓张的教师的教师号和姓名

SELECT
	teacher.tid,
	teacher.tname 
FROM
	teacher 
WHERE
	teacher.tname LIKE '张%';

查询姓名中第2个汉字是力的教师号和姓名

SELECT
	teacher.tid,
	teacher.tname 
FROM
	teacher 
WHERE
	teacher.tname LIKE '_力%';

查询所有没有成绩的学生的学号和相应的课程号
```
SELECT
	sid,
	cid 
FROM
	scourse 
WHERE
	score IS NULL;
```

查询选修C1的学生学号和成绩，并按照成绩降序排列

SELECT
	sid,
	score 
FROM
	scourse 
WHERE
	cid = 'C1' 
ORDER BY
	score DESC;

查询选修C2,或者C3,或者C4,或者C5课程的学号，课程号和成绩, 查询结果按学号升序排列，学号相同，再按成绩降序排列
```
SELECT
	sid,
	cid,
	score 
FROM
	scourse 
WHERE
	cid IN ( 'C2', 'C3', 'C4', 'C5' ) 
ORDER BY
	sid,
	score DESC;
```

查询选修C1的学生学号和成绩，并显示成绩前三的学生

SELECT
	sid,
	score 
FROM
	scourse 
WHERE
	cid = 'C1' 
ORDER BY
	score DESC 
	LIMIT 3;

查询计算机系学生的总数

SELECT
	count( * ) 
FROM
	student 
WHERE
	dept = '计算机系';

查询每位学生的学号及其选修课的门数

SELECT
	sid,
	count( * ) 
FROM
	scourse 
GROUP BY
	sid;

在分组查询中使用having条件查询，平均成绩大于85的学生学号及平均成绩
```
SELECT
	sid,
	avg( score ) 
FROM
	scourse 
GROUP BY
	sid 
HAVING
	avg( score ) > 85;
```

查询选课在两门以上且各门课均及格的学生的学号及其总成绩, 查询结果按总成绩降序列出

SELECT
	sid,
	count( * ),
	sum( score ) 
FROM
	scourse 
WHERE
	score > 60 
	and sid not in (select sid from scourse where score < 60)
GROUP BY
	sid 
HAVING
	count( * ) > 2 
ORDER BY
	sum( score ) DESC;

查询所有选课学生的学号，姓名，选课名称及成绩

SELECT
	student.sid,
	student.sname,
	scourse.cid,
	scourse.score 
FROM
	scourse,
	student 
WHERE
	student.sid = scourse.sid;

查询选修C1课程且成绩在60以上的所有学生的学号, 姓名和分数

SELECT
	student.sid,
	student.sname,
	scourse.score,
	scourse.cid 
FROM
	student
	JOIN scourse ON scourse.sid = student.sid 
WHERE
	scourse.cid = 'C1' 
	AND scourse.score > 60;

查询与李明教师职称相同的教师号, 姓名

SELECT
	teacher.tid,
	teacher.tname,
	teacher.title 
FROM
	teacher 
WHERE
	teacher.title = ( SELECT title FROM teacher WHERE tname = '李明' ) 
	AND teacher.tname != '李明';

[MySQL光速入门]007 作业解答

创建数据库`library`

创建数据表

图书类别表(`booktype`)

图书信息表(`book`)

图书存储信息表(`bookstorage`)

读者类别表(`readertype`)

读者信息表(`reader`)

图书借阅表(`bookborrow`)

为创建的表插入如下数据

图书类别表(`booktype`)

图书信息表(`book`)

图书存储信息表(`bookstorage`)

读者类别表(`readertype`)

读者信息表(`reader`)

图书借阅表(`bookborrow`)

查询数据

创建数据库`stucourse`

学生表(`student`)

教师表(`teacher`)

课程表(`courseinfo`)

选课表(`scourse`)

教材表(`bookinfo`)

查询数据

快速跳转

[MySQL光速入门]007 作业解答

创建数据库library

创建数据表

图书类别表(booktype)

图书信息表(book)

图书存储信息表(bookstorage)

读者类别表(readertype)

读者信息表(reader)

图书借阅表(bookborrow)

为创建的表插入如下数据

图书类别表(booktype)

图书信息表(book)

图书存储信息表(bookstorage)

读者类别表(readertype)

读者信息表(reader)

图书借阅表(bookborrow)

查询数据

创建数据库stucourse

学生表(student)

教师表(teacher)

课程表(courseinfo)

选课表(scourse)

教材表(bookinfo)

查询数据

快速跳转

创建数据库`library`

图书类别表(`booktype`)

图书信息表(`book`)

图书存储信息表(`bookstorage`)

读者类别表(`readertype`)

读者信息表(`reader`)

图书借阅表(`bookborrow`)

图书类别表(`booktype`)

图书信息表(`book`)

图书存储信息表(`bookstorage`)

读者类别表(`readertype`)

读者信息表(`reader`)

图书借阅表(`bookborrow`)

创建数据库`stucourse`

学生表(`student`)

教师表(`teacher`)

课程表(`courseinfo`)

选课表(`scourse`)

教材表(`bookinfo`)