[MySQL光速入門]007 作業解答

貓哥的技術部落格發表於2019-03-27

建立資料庫`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光速入門]004 作業解答
2019-03-27
MySql
[MySQL光速入門]012 作業解答
2019-03-27
MySql
[MySQL光速入門]003 留點作業...
2019-04-18
MySql
[MySQL光速入門]006 留點作業...
2019-04-18
MySql
[MySQL光速入門]011 留點作業
2019-04-18
MySql
[MySQL光速入門]025 函式作業答案
2019-04-17
MySql函式
[MySQL光速入門]027 索引
2019-04-18
MySql索引
[MySQL光速入門]002 讓MySQL跑起來
2019-04-18
MySql
[MySQL光速入門]005 查詢資料
2019-04-18
MySql
[MySQL光速入門]020 事務
2019-04-18
MySql
[MySQL光速入門]021 聊聊遊標
2019-04-18
MySql
[MySQL光速入門]024 聊聊函式
2019-04-18
MySql函式
[MySQL光速入門]028 聊聊檢視
2019-04-18
MySql
[MySQL光速入門]032 完結撒花!!!
2019-04-18
MySql
[MySQL光速入門]014 試題答案
2019-04-02
MySql
[MySQL光速入門]018 流程控制
2019-04-18
MySql
[MySQL光速入門]031 許可權管理
2019-04-18
MySql
Rxjs光速入門
2018-09-17
JS
[MySQL光速入門]008 SQL強化練習
2019-04-18
MySql
[MySQL光速入門]016 圖解變數
2019-04-18
MySql圖解變數
[MySQL光速入門]023 if函式(番外)
2019-04-18
MySql函式
[MySQL光速入門]000 開篇介紹&目錄
2019-04-18
MySql
[MySQL光速入門]009 SQL強化練習答案
2019-03-27
MySql
[MySQL光速入門]010 運算子與函式
2019-04-18
MySql函式
[MySQL光速入門]015 聊聊儲存過程
2019-04-18
MySql儲存過程
[MySQL光速入門]002 建庫, 建表, 加資料
2019-04-18
MySql
[MySQL光速入門]026 觸發器 trigger!!!
2019-04-18
MySql觸發器
[MySQL光速入門]029 資料庫管理和維護
2019-04-18
MySql資料庫
[MySQL光速入門]030 使用者和資料安全
2019-04-18
MySql
[MySQL光速入門]017 儲存過程中的"異常處理"
2019-04-18
MySql儲存過程
[MySQL光速入門]022 插入資料的兩種方式(番外)
2019-04-18
MySql
Windows 8 作業系統入門
2012-08-05
Windows作業系統
Unix類作業系統入門(轉)
2007-08-16
作業系統
Dart語法超光速入門（1）：內建型別
2019-04-05
Dart型別
3分鐘短文：Laravel控制器用法光速入門
2020-10-21
Laravel
入門入門入門 MySQL命名行
2020-12-15
MySql
【mysql入門】
2018-01-01
MySql
MySQL入門
2024-04-13
MySql

[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`)