题目:
1、创建数据库:创建名为“books_db”的数据库。
2、建立基本表
在MySQL命令行客户端中,使用SQL语句在“books_db”数据库中建立以下四个表。表结构如下:(注意:在创建表之前,需使用use命令打开“books_db”数据库)
(1)图书表,表名:book
(2)读者表, 表名:reader
(3)借书表, 表名:borrow_book
(4)还书表,表名:return_book
建表时,注意如下约束:
①图书表:书号为主码
②读者表:编号为主码;
③借书表:(书号, 读者编号)为主码;
书号为外码,参照表、列是图书表的书号列;
读者编号为外码,参照表、列是读者表的编号列。
④还书表:(书号, 读者编号)为主码;
书号为外码,参照表、列是图书表的书号列;
读者编号为外码,参照表、列是读者表的编号列。
3、修改基本表(使用SQL语句)
(1)给读者表增加一个“备注”字段,类型为varchar(150);
(2)将“备注”字段的类型修改为varchar(200);
(3)删除“备注”字段;
(4)将图书表中的“库存”字段类型改为smallint。
实验答案:
总体实现代码(一个要求一个答案)
1.进入
mysql -uroot -p;
create database books_db DEFAULT CHARACTER SET utf8;
show databases;
2.进入books_db数据库且创建四个表
use books_db;
SELECT DATABASE();
(1)创建图书表
CREATE TABLE book
(book_number CHAR(10) NOT NULL PRIMARY KEY,
category VARCHAR(12),
book_name VARCHAR(30) NOT NULL,
publisher CHAR(30) NOT NULL,
author CHAR(10),
price NUMERIC(5,2),
book_total INT NOT NULL,
inventory INT NOT NULL
);
desc book;
(2)创建读者表
CREATE TABLE reader
(reader_number CHAR(10) NOT NULL PRIMARY KEY,
name VARCHAR(8) NOT NULL,
department CHAR(20),
gender CHAR(2) NOT NULL,
telephone CHAR(15)
);
desc reader;
(3)创建借书表
CREATE TABLE borrow_book
(reader_number CHAR(10) NOT NULL,
book_number CHAR(10) NOT NULL,
borrow_time DATETIME NOT NULL,
PRIMARY KEY ( reader_number, book_number ),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(reader_number) REFERENCES reader(reader_number)
);
desc borrow_book;
(4)创建还书表
CREATE TABLE return_book
(reader_number CHAR(10) NOT NULL,
book_number CHAR(10) NOT NULL,
return_time DATETIME NOT NULL,
PRIMARY KEY ( reader_number, book_number ),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(reader_number) REFERENCES reader(reader_number)
);
desc return_book;
看整体建的表
show tables;
(1)给读者表增加一个“备注”字段,类型为varchar(150);
ALTER TABLE reader ADD extra VARCHAR(150);
desc reader;
(2)将“备注”字段的类型修改为varchar(200);
ALTER TABLE reader MODIFY extra VARCHAR(200);
desc reader;
(3)删除“备注”字段;
ALTER TABLE reader DROP extra ;
desc reader;
(4)将图书表中的“库存”字段类型改为smallint。
ALTER TABLE book MODIFY inventory SMALLINT;
desc book;
分布代码:每一句SQL以及对应效果
//进入
mysql -uroot -p;
//让中文输入不会乱码
create database books_db DEFAULT CHARACTER SET utf8;
//进bools_bd数据库
use books_db;
//查看当前数据库是什么
SELECT DATABASE();
//建立book表
CREATE TABLE book
(book_number CHAR(10) NOT NULL PRIMARY KEY,
category VARCHAR(12),
book_name VARCHAR(30) NOT NULL,
publisher CHAR(30) NOT NULL,
author CHAR(10),
price NUMERIC(5,2),
book_total INT NOT NULL,
inventory INT NOT NULL
);
//查看book表
desc book;
//建reader表
CREATE TABLE reader
(reader_number CHAR(10) NOT NULL PRIMARY KEY,
name VARCHAR(8) NOT NULL,
department CHAR(20),
gender CHAR(2) NOT NULL,
telephone CHAR(15)
);
//查看reader表
desc reader;
//建borrow_book表
CREATE TABLE borrow_book
(reader_number CHAR(10) NOT NULL,
book_number CHAR(10) NOT NULL,
borrow_time DATETIME NOT NULL,
PRIMARY KEY ( reader_number, book_number ),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(reader_number) REFERENCES reader(reader_number)
);
desc borrow_book;//查borrow_book表
//建return_book表
CREATE TABLE return_book
(reader_number CHAR(10) NOT NULL,
book_number CHAR(10) NOT NULL,
return_time DATETIME NOT NULL,
PRIMARY KEY ( reader_number, book_number ),
FOREIGN KEY(book_number) REFERENCES book(book_number),
FOREIGN KEY(reader_number) REFERENCES reader(reader_number)
);
//查return_book表
desc return_book;
//查看全部的数据库有哪些
show databases;
//查看当前数据库下表有哪些
show tables;
//给读者表增加一个“备注”字段,类型为varchar(150);
ALTER TABLE reader ADD extra VARCHAR(150);
//查看reader表
desc reader;
//将“备注”字段的类型修改为varchar(200);
ALTER TABLE reader MODIFY extra VARCHAR(200);
//查看reader表
desc reader;
//删除“备注”字段;
ALTER TABLE reader DROP extra ;
//查看reader表
desc reader;
//将图书表中的“库存”字段类型改为smallint。
ALTER TABLE book MODIFY inventory SMALLINT;
//查看book表
desc book;
遇到的困难及解决办法:
(1)创建book表时不小心给category加了UNIQUE属性。解决办法:删除整个book(drop TABLE book ;)表后在创建新的book表。
(2) Borrow表中主码是多个属性一起,如何设置主码?解决办法:将PRIMARY KEY放到所有元组之后,使用括号一起定义。