数据库基础小练习

1.使用SQL语句创建课程表

• 需求说明

在数据库myschool中，使用SQL语句创建课程表subject。subject表的结构如表2-10所示。

在创建表之前检查课程表subject是否已存在。若存在，则删除。

表2-10 课程表的数据结构

序号	字段名称	字段说明	数据类型	长度	属性	备注
1	subjectNo	课程编号	INT	4	非空	主键，标识列，自增1
2	subjectName	课程名称	VARCHAR	50
3	classHour	学时	INT	4
4	gradeID	年级编号	INT	4

#答案:
CREATE TABLE `subject` (
  `subjectNo` int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '课程编号',
  `subjectName` varchar(50) DEFAULT NULL COMMENT '课程名称',
  `classHour` int(4) DEFAULT NULL COMMENT '学时',
  `gradeID` int(4) DEFAULT NULL COMMENT '年级编号'
);

2.使用SQL语句创建成绩表

• 需求说明

在数据库myschool中，使用SQL语句创建成绩表result。result表的结构如表2-11所示。

表2-11 成绩表的数据结构

序号	字段名称	字段说明	数据类型	长度	属性	备注
1	studentNo	学号	INT	4	非空
2	subjectNo	课程编号	INT	4	非空
3	examDate	考试日期	DATETIME		非空	默认为当前日期
4	studentResult	考试成绩	INT	4	非空

CREATE TABLE `result`(
	`studentNo` INT(4) NOT NULL COMMENT '学号',
	`subjectNo` INT(4) NOT NULL COMMENT '课程编号',
	`examDate`	TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '考试日期',
	`studentResult` INT(4) NOT NULL COMMENT '考试成绩'
);

3.使用SQL语句创建学生表和年级表

• 需求说明

在数据库myschool中，使用SQL语句创建学生表student和年级表grade。student表的结构和grade表的结构分别如表2-12和表2-13所示。

表2-12 学生表的数据结构

序号	字段名称	字段说明	数据类型	长度	属性	备注
1	studentNo	学号	INT	4	非空	主键
2	loginPwd	密码	VARCHAR	20	非空
3	studentName	姓名	VARCHAR	50	非空
4	sex	性别	CHAR	2	非空
5	gradeID	年级编号	INT	4	无符号
6	phone	电话	VARCHAR	50
7	address	地址	VARCHAR	255
8	birthday	出生日期	DATETIME
9	email	邮件账号	VARCHAR	50
10	identityCard	身份证号	VARCHAR	18

表2-13 年级表的数据结构

序号	字段名称	字段说明	数据类型	长度	属性	备注
1	gradeID	年级编号	INT	4	非空	标识列，自增1
2	gradeName	年级名称	VARCHAR	50	非空

#表一：学生表
CREATE TABLE student(
	studentNo int(4) NOT NULL PRIMARY KEY COMMENT '学号',
	loginPwd VARCHAR(20) NOT NULL COMMENT '密码',
	studentName VARCHAR(50) NOT NULL COMMENT '姓名',
	sex CHAR(2) NOT NULL COMMENT '性别',
	gradeID INT(4) UNSIGNED COMMENT '年级编号',
	phone VARCHAR(50) COMMENT '电话',
	address VARCHAR(255) COMMENT '地址',
	birthday DATETIME COMMENT '出生日期',
	email VARCHAR(50) COMMENT '邮件账号',
	identityCard VARCHAR(18) COMMENT '身份证号'
);

#表二：年级表
CREATE TABLE grade(
	gradeID int(4) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '年级编号',
	gradeName VARCHAR(50) NOT NULL COMMENT '年级名称'
);

4.创建数据表，并实现对表的修改操作

• 需求说明

1. 在test数据库中创建person表，其结构如表2-14所示。
2. 将表名修改为tb_person。
3. 删除出生日期字段。
4. 添加出生日期字段，数据类型为DATE类型。
5. 修改序号字段名(number)为id，类型改为BIGINT类型。

表2-14 person表结构

序号	字段名称	字段说明	数据类型	长度	属性	备注
1	number	序号	INT	4	自增列	主键
2	name	姓名	VARCHAR	50	非空
3	sex	性别	CHAR	2
4	birthday	出生日期	DATETIME

CREATE TABLE person(
	number int(4) AUTO_INCREMENT PRIMARY KEY COMMENT '序号',
	name VARCHAR(50) NOT NULL COMMENT '姓名',
	sex CHAR(2) COMMENT '性别',
	birthday DATETIME COMMENT '出生日期'
);
ALTER TABLE person RENAME AS tb_person;

ALTER TABLE tb_person DROP birthday;

ALTER TABLE tb_person ADD birthday DATE;

ALTER TABLE tb_person CHANGE number id BIGINT;

5.建立一个图书馆管理系统的数据库

建立一个图书馆管理系统的数据库，用来存放图书馆的相关信息，包括图书的基本信息、图书借阅的信息和读者的信息。

要求全部使用SQL语句实现。

推荐步骤如下：

1. 创建数据库Library。
2. 创建下面4个表。

图书信息表，如表2-15所示。

表2-15 图书信息表book

字段名称	数据类型	说明
bid	字符型	图书编号，主键，该列必填
bName	字符型	图书书名，该列必填
author	字符型	作者姓名
pubComp	字符型	出版社
pubDate	日期型	出版日期
bCount	整型	现存数量
price	浮点型	单价

读者信息表，如表2-16所示。

表2-16 读者信息表reader

字段名称	数据类型	说明
rId	字符型	读者编号，主键，该列必填
rName	字符型	读者姓名，该列必填
lendNum	整型	已借书数量
rAddress	字符型	联系地址

图书借阅表，如表2-17所示。

表2-17 图书借阅表borrow

字段名称	数据类型	说明
rId	字符型	读者编号，复合主键，该列必填
bId	字符型	图书编号，复合主键，该列必填
lendDate	日期型	借阅日期，复合主键，默认值为当前日期，该列必填
willDate	日期型	应归还日期
returnDate	日期型	实际归还日期

罚款记录表，如表2-18所示。

表2-18 罚款记录表penalty

字段名称	数据类型	说明
rId	字符型	读者编号，复合主键，该列必填
bId	字符型	图书编号，复合主键，该列必填
pDate	日期型	罚款日期，复合主键，该列必填
pType	整型	罚款类型，1—延期，2—损坏，3—丢失
amount	浮点型	罚款金额

CREATE DATABASE Library;
use library;

#表2-15 图书信息表book
CREATE TABLE book(
	bid VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '图书编号',
	bName VARCHAR(10) NOT NULL COMMENT '图书书名',
	author VARCHAR(10) COMMENT '作者姓名',
	pubComp VARCHAR(10) COMMENT '出版社',
	pubDate DATETIME COMMENT '出版日期',
	bCount INT(10) COMMENT '现存数量',
	price DOUBLE(10,2) COMMENT '单价'
);

#表2-16 读者信息表reader
CREATE TABLE reader(
	rId VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '读者编号',
	rName VARCHAR(10) NOT NULL COMMENT '读者姓名',
	lendNum INT(10) COMMENT '已借书数量',
	rAddress VARCHAR(10) COMMENT '联系地址'
);

#表2-17 图书借阅表borrow
CREATE TABLE borrow(
	rId VARCHAR(10) NOT NULL COMMENT '读者编号',
	bId VARCHAR(10) NOT NULL COMMENT '图书编号',
	lendDate TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '借阅日期',
	willDate DATETIME COMMENT '应归还日期',
	returnDate DATETIME COMMENT '实际归还日期',
PRIMARY KEY (rId,bId,lendDate)
);

#表2-18 罚款记录表penalty
CREATE TABLE penalty(
	rId VARCHAR(10) NOT NULL COMMENT '读者编号',
	bId VARCHAR(10) NOT NULL COMMENT '图书编号',
	pDate TIMESTAMP NOT NULL COMMENT '罚款日期',
	pType INT COMMENT '罚款类型，1—延期，2—损坏，3—丢失',
	amount DOUBLE(10,2) COMMENT '罚款金额',
	PRIMARY KEY (rId,bId,pDate)
);

6.为课程表、学生表添加数据

• 需求说明

1. 为课程添加数据，如表3-2所示。要求使用一条INSERT语句实现。
2. 为学生表添加数据，如表3-3所示。

表3-2 课程表

课程编号	课程名称	课时数	学期编号
1	LogicJava	220	1
2	HTML	160	1
3	JavaOOP	230	2

表3-3 学生表

学号	密码	学生姓名	性别	年级编号	手机号码	地址	出生日期
10000	123	郭靖	男	1	13645667783	天津市 河西区	1990-09-08
10001	123	李文才	男	1	13645667890	地址不详	1994-04-12
10002	123	李斯文	男	1	13645556793	河南洛阳	1993-07-23
10003	123	张萍	女	1	13642345112	地址不详	1995-06-10
10004	123	韩秋洁	女	1	13812344566	北京市 海淀区	1995-07-15
10005	123	张秋丽	女	1	13567893246	北京市 东城区	1994-01-17
10006	123	肖梅	女	1	13563456721	河北省 石家庄市	1991-02-17
10007	123	秦洋	男	1	13056434411	上海市 卢湾区	1992-04-18
10008	123	何晴晴	女	1	13053445221	广州市 天河区	1997-07-23
20000	123	王宝宝	男	2	15076552323	地址不详	1996-06-05
20010	123	何小华	女	2	13318877954	地址不详	1995-09-10
30011	123	陈志强	女	3	13689965430	地址不详	1994-09-27
30012	123	李露露	女	3	13685678854	地址不详	1992-09-27

INSERT INTO subject(subjectName,classHour,gradeID) VALUES('LogicJava',220,1),('HTML',160,1),('JavaOOP',230,2);

INSERT INTO student(studentNo,loginPwd,studentName,sex,gradeID,phone,address,birthday) VALUES 
(10000,'123','郭靖','男',1,'13645667783','天津市河西区','1990-09-08'),
(10001,'123','李文才','男',1,'13645667890','地址不详','1994-04-12'),
(10002,'123','李斯文','男',1,'13645556793','河南洛阳','1993-07-23'),
(10003,'123','张萍','女',1,'13642345112','地址不详','1995-06-10'),
(10004,'123','韩秋洁','女',1,'13812344566','北京市海淀区','1995-07-15'),
(10005,'123','张秋丽','女',1,'13567893246','北京市东城区','1994-01-17'),
(10006,'123','肖梅','女',1,'13563456721','河北省石家庄市','1991-02-17'),
(10007,'123','秦洋','男',1,'13056434411','上海市卢湾区','1992-04-18'),
(10008,'123','何晴晴','女',1,'13053445221','广州市天河区','1997-07-23'),
(20000,'123','王宝宝','男',2,'15076552323','地址不详','1996-06-05'),
(20010,'123','何小华','女',2,'13318877954','地址不详','1995-09-10'),
(30011,'123','陈志强','女',3,'13689965430','地址不详','1994-09-27'),
(30012,'123','李露露','女',3,'13685678854','地址不详','1992-09-27');

7.修改学生表、课程表数据

• 需求说明

1. 将学生表中学号为20000的学生的地址修改为“西直门东大街215号”，密码修改为000.
2. 将课程表中课时数大于200且学期编号为1的课程的课时减少10课时。
3. 将所有年级编号为1的学生姓名、性别、出生日期、手机号码信息保存到新表student_grade1中。

UPDATE student SET loginPwd = '000',address = '西直门东大街215号' WHERE studentNo = 20000;

UPDATE subject SET classHour = classHour-10 WHERE classHour>200 AND gradeID = 1;

CREATE TABLE student_grade1(
	studentname VARCHAR(50) NOT NULL COMMENT '姓名',
  sex CHAR(2) NOT NULL COMMENT '性别',
  birthday DATETIME DEFAULT NULL COMMENT '出生日期',
  phone VARCHAR(50) DEFAULT NULL COMMENT '电话号码'
 );

INSERT INTO student_grade1 SELECT studentname,sex,birthday,phone FROM student WHERE gradeid=1;