创建图书管理系统数据表
use book;
show tables;
drop table bookinfo;
drop table bookcategory;
drop table reader;
show tables;
create table bookcategory(
category_id int primary key,
category varchar(20) not null unique,
parent_id int not null
);
create table bookinfo(
book_id int primary key,
book_category_id int,
book_name varchar(20) not null unique,
author varchar(20) not null,
price float(5,2) not null,
press varchar(20) default '机械工业出版社',
pubdate date not null,
store int not null,
constraint fk_bcid foreign key(book_category_id)
references bookcategory(category_id)
);
desc bookinfo;
create table readerinfo(
card_id char(18) primary key,
name varchar(20) not null,
sex enum('男','女','保密') default '保密',
age tinyint,
tel char(11) not null,
balance decimal(7,3) default 200
);
create table borrowinfo(
book_id int,
card_id char(18),
borrow_data date not null,
return_data date not null,
status char(1) not null,
primary key(book_id,card_id)
);
show tables;
5 数据表记录的操作
5.1 单表数据的插入
insert into
bookcategory(category_id,category,parent_id)
values(1,'计算机',0);
insert into
readerinfo(card_id,name,tel)
values('210210199901011111','张飞','13777777777');
insert into
bookcategory(category_id,category,parent_id)
values(3,'编程语言',1),(4,'数据库',1),(5,'儿科学',2);
CREATE TABLE test(id INT,NAME VARCHAR(10),pid INT);
SHOW tables;
INSERT INTO
test(id,NAME,pid)
VALUES(5,'网页设计',1),(6,'口腔医学',2),
(7,'眼科学',2),(8,'骨科医学',2),(9,'临床医学',2);
insert into bookcategory
select * from test
where id>5;
5.2 设置自动编号
create table bookcategory_tmp(
category_id int primary key auto_increment,
category varchar(20) not null unique,
parent_id int not null
);
insert into
bookcategory_tmp(category,parent_id)
values('计算机',0)
create table bookcategory_tmp1(
category_id int primary key auto_increment,
category varchar(20) not null unique,
parent_id int not null
)auto_increment=5;
alter table bookcategory
modify category_id int auto_increment;
create table bookcategory(
category_id int primary key auto_increment,
category varchar(20) not null unique,
parent_id int not null
);
alter table bookcategory
auto_increment = 3;
alter table bookcategory
modify category_id int;
alter table bookinfo
drop foreign key fk_bcid;
ALTER TABLE bookinfo
ADD CONSTRAINT fk_bcid
FOREIGN KEY(book_category_id)REFERENCES bookcategory(category_id);