数据库基础小练习

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

• 需求说明

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

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

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

#答案:
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 成绩表的数据结构

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 学生表的数据结构

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

#表一：学生表
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表结构

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

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

表2-16 读者信息表reader

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

表2-17 图书借阅表borrow

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

表2-18 罚款记录表penalty

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 课程表

表3-3 学生表

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;