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

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

建立資料庫library

create database library character set utf8;
use library;
複製程式碼

建立資料表

圖書類別表(booktype)

序號 屬性名稱 含義 資料型別 是否為空 備註
1 typeid 類別編號 int not null 主鍵
2 typename 類別名稱 varchar(20) null 
create table booktype(
	typeid int not null primary key,
	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 
create table book(
	bookid char(10) not null PRIMARY key,
	bookname VARCHAR(20) not null,
	typeid INT,
	bookauthor VARCHAR(20),
	bookpublisher VARCHAR(50),
	bookprice DOUBLE,
	borrowsum int,
	FOREIGN key(typeid) REFERENCES booktype(typeid)
);
複製程式碼

圖書儲存資訊表(bookstorage)

序號 屬性名稱 含義 資料型別 是否為空 備註
1 bookbarcode 圖書條碼 char(20) not null 主鍵
2 bookid 圖書編號 char(10) not null 外來鍵
3 bookintime 圖書入館時間 datetime null
4 bookstatus 圖書狀態 varchar(4) null 
create table bookstorage(
	bookbarcode char(20) not null PRIMARY key,
	bookid char(10) not null,
	bookintime datetime,
	bookstatus VARCHAR(4),
	FOREIGN key(bookid) REFERENCES book(bookid)
);
複製程式碼

讀者類別表(readertype)

序號 屬性名稱 含義 資料型別 是否為空 備註
1 retypeid 類別編號 int not null
2 typename 類別名稱 varchar(20) not null
3 borrowquantity 可借數量 int not null
4 borrowday 可借天數 int null 
create table readertype(
	retypeid int not null primary key,
	typename VARCHAR(20) not null,
	borrowquantity int not null,
	borrowday int
);
複製程式碼

讀者資訊表(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 
create table reader(
	readerid char(10) not null PRIMARY key,
	readername VARCHAR(20) not null,
	readerpass VARCHAR(20) not null,
	retypeid int,
	readerdate datetime,
	readerstatus VARCHAR(4),
	FOREIGN key(retypeid) REFERENCES readertype(retypeid)
);
複製程式碼

圖書借閱表(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 
create table bookborrow(
	borrowid char(10) not null 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)
);
複製程式碼

為建立的表插入如下資料

圖書類別表(booktype)

typeid typename
1 自然科學
2 數學
3 計算機
4 建築水利
5 旅遊地理
6 勵志/自我實現
7 工業技術
8 基礎醫學
9 室內設計
10 人文景觀 
insert into booktype values
	(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 
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);
複製程式碼

圖書儲存資訊表(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 在館 
insert into bookstorage values
	('132782','TP39/1712','2009-8-10','在館'),
	('132789','TP39/1712','2009-8-10','借出'),
	('145234','013452','2008-12-6','借出'),
	('145321','TP/3452','2007-11-4','借出'),
	('156833','TH/2345','2009-12-4','借出'),
	('345214','R/345677','2008-11-3','在館');
複製程式碼

讀者類別表(readertype)

retypeid typename borrowquantity borrowday
1 學生 10 30
2 教師 20 60
3 管理員 15 30
4 職工 15 20 
insert into readertype values
	(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 有效 
insert into reader values
	('0016','蘇小東','123456',1,'1999-9-9','有效'),
	('0017','張明','123456',1,'2010-9-10','有效'),
	('0018','樑君紅','123456',1,'2010-9-10','有效'),
	('0021','趙清遠','123456',2,'2010-7-1','有效'),
	('0034','李瑞清','123456',3,'2009-8-3','有效'),
	('0042','張明月','123456',4,'1997-4-23','有效');
複製程式碼

圖書借閱表(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 未還 
insert into bookborrow values
	('001432','132782','0016','2011-3-4','2011-4-5','已還'),
	('001328','132789','0017','2011-1-24','2011-2-28','已還'),
	('001356','145234','0018','2011-2-12','2011-2-27','已還'),
	('001435','145321','0021','2011-8-9','2011-9-2','已還'),
	('001578','156833','0034','2011-10-1','2011-11-1','未還'),
	('001679','345214','0042','2011-2-21','2011-3-5','未還');
複製程式碼

建立資料庫stucourse

沒有表結構, 根據資料, 自己決定使用哪種資料型別

create database stucourse;
use stucourse;
複製程式碼

學生表(student)

sid sname sex age dept
1001 宋江 男 25 計算機系
3002 張明 男 23 生物系
1003 李小鵬 男 26 計算機系
1004 鄭冬 女 25 計算機系
4005 李曉紅 女 27 工商管理
5006 趙紫月 女 24 外語系 
create table student(
	sid char(10) not null primary key,
	sname varchar(20) not null,
	sex char(1) not null,
	age tinyint(2) not null,
	dept varchar(20) not null
);
複製程式碼
insert into student values('1001','宋江','男',25,'計算機系');
insert into student values('3002','張明','男',23,'生物系');
insert into student values('1003','李小鵬','男',26,'計算機系');
insert into student values('1004','鄭東','女',25,'計算機系');
insert into student values('4005','李小紅','女',27,'工商管理');
insert into student values('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 
create table teacher(
	tid char(10) not null primary key,
	tname varchar(20) not null,
	title char(2) not null,
	salary int(11) not null,
	dept varchar(20) not null,
	cid char(2) not null,
	FOREIGN key(cid) REFERENCES courseinfo(cid)
);
複製程式碼
insert into teacher values('3102','李明','初級',2500,'計算機系','C1');
insert into teacher values('3108','黃小明','初級',4000,'生物系','C3');
insert into teacher values('4105','張小紅','中級',3500,'工商管理','C2');
insert into teacher values('5102','宋力月','高階',1500,'物理系','C4');
insert into teacher values('3106','趙明陽','初級',3500,'地理系','C2');
insert into teacher values('7108','張麗','高階',3500,'生物系','C3');
insert into teacher values('9103','王彬','高階',3500,'計算機系','C1');
insert into teacher values('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 計算機系 
create table courseinfo(
	cid char(2) not null PRIMARY key,
	cname varchar(20) not null,
	cbook char(5) not null,
	ctest date not null,
	dept varchar(20)
);
複製程式碼
insert into courseinfo values('C1','計算機基礎','b1231','2009-4-6','計算機系');
insert into courseinfo values('C2','工商管理基礎','b1232','2009-7-16','工商管理');
insert into courseinfo values('C3','生物科學','b1233','2010-3-6','生物系');
insert into courseinfo values('C4','大學物理','b1234','2009-4-26','物理系');
insert into courseinfo values('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 
create table scourse(
	sid char(4) not null,
	score int,
	cid char(2) not null,
	tid char(10)
);
複製程式碼
insert into scourse values('1001',87,'C1','3102');
insert into scourse values('1001',77,'C2','4105');
insert into scourse values('1001',63,'C3','3108');
insert into scourse values('1001',56,'C4','5102');
insert into scourse values('3002',78,'C3','3108');
insert into scourse values('3002',78,'C4','5102');
insert into scourse values('1003',89,'C1','9103');
insert into scourse values('1004',56,'C2','3106');
insert into scourse values('4005',87,'C4','5102');
insert into scourse values('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 
create table bookinfo(
	bid char(5) not null PRIMARY key,
	bname varchar(50) not null,
	bpublish varchar(20) not null,
	bprice double not null,
	quantity int
);
複製程式碼
insert into bookinfo values('b1231', 'Image Processing', '人民大學出版社', 34.56, 8);
insert into bookinfo values('b1232', 'Signal Processing', '清華大學出版社', 51.75, 10);
insert into bookinfo values('b1233', 'Digital Signal Processing', '郵電出版社', 48.5, 11);
insert into bookinfo values('b1234', 'The Logic Circuit', '北大出版社', 49.2, 40);
insert into bookinfo values('b1235', 'SQL Techniques', '郵電出版社', 65.4, 20);
複製程式碼

快速跳轉

相關文章