x7 MySQL 约束Constraint:案例练习

创建图书管理系统数据库 booksys, 并创建以下4张表:

表1：图书信息表 bookinfo

图书号/bid	类别号/cid	书名/name	作者/author	价格/price	出版社/press	出版/pubdata	库存/store
20150201	3	java编程	张三	79.8	机械工业出版社	2007/4	5
20150202	4	php开发	艾美美	95	机械工业出版社	2009/4	2

案例分析：

图书号/bid: 不能为空,唯一标识数据信息, 选择 [主键 primary key];
类别号/cid: 根据图书类型表确定类别号,选择[外键 foreign key];
书名/name: 书名唯一，选择 [唯一 unique];
价格/price; 库存/store: 不能为空,选择 [非空 not null];
作者/author; 出版社/press; 出版/pubdata: 可优化数据库,选择[默认 default]

参考代码：

create table bookinfo(
	book_id int primary key,
	cid int,
	name varchar(30) unique not null,
	author varchar(20) not null,
	press varchar(20),
	pubdata date not null,
	price float(5,2) not null,
	store int not null,
	foreign key(cid) references bookclass(class_id)
);

表2：图书类别表 bookclass

类别号/class_id	类别名/name	父类别/parent_id
1	计算机	0
2	医学	0
3	编程语言	1
4	数据库	1
5	外科	2
6	肉科	2

案例分析：

类别号/class_id: 选择[主键 primary key]; 自增约束 auto_increment;
类别名/name: 选择[唯一 unique];
父类别/parent_id: 选择 not null 非空

参考代码：

create table bookclass(
	class_id int primary key auto_increment,
	class_name varchar(20) unique not null,
	parent_id int not null
);

表3：读者信息表 readerinfo

证件号/card_id	姓名/name	性别/sex	年龄/age	电话/tel	余额/balance
360781201910013333	张三	男	33	13833333333	300
360781201911254444	李四	男	44	13944444444	400
360781201911265555	王五	男	25	13655555555	500
360781201911277777	郝美	女	17	18977777777	700
360781201911288888	钱朵朵	女	18	18888888888	800

案例分析：

证件号/card_id: 选择[主键 primary key];
电话/tel: 选择[唯一 unique];
姓名/name; 年龄/age: 选择 [非空 not null];
余额/balance: 选择 [非空 not null]; 不能少于200元,还可设置[默认 default]
性别/sex: 根据男多或女多,选择[默认 default]

参考代码：

create table readerinfo(
	card_id char(18) primary key,
	name varchar(10) not null,
	sex enum('男', '女') default '女',
	age tinyint,
	tel char(11) unique,
	balance decimal(5,2) not null
);

表4：借阅信息表 borrowinfo

图书号/cid	证件号/card_id	借出日期/borrow_data	归还日期/return_data	归还状态/status
20150201	360781201910013333	2017/5/5	2017/6/5	是
20150202	360781201911254444	2018/8/18	2017/9/18	是

案例分析：

图书号/cid: 若为单独主键,则书只能被1个人借阅,不可行;
证件号/card_id: 若为单独主键,则每人只能借1次书,也不可行;
所以可以考虑 图书号/cid +证件号/card_id的复合主键约束;
借出日期/borrow_data; 归还日期/return_data; 归还状态/status: 选择 [非空 not null];

参考代码：

create table borrowinfo(
	bid int,
	card_id varchar(18),
	borrow_data date not null,
	return_data date not null,
	status enum('未还','已还'),
	primary key(bid,card_id),
	foreign key(bid) references bookinfo(book_id)
);