数据库mysql实战
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建图书管理系统的数据库 create database book; --使用数据库 use book; --创建读者信息表 create table reader( card_id char(18), name varchar(10), sex enum('男','女'), age tinyint, tel char(11), balance decimal(7,3) ); --查看数据库表列表 show tables from book; --查看数据表的基本结构 show columns from reader; desc reader; --查看表的详细结构(可以用来显示数据表的创建语句) show create table reader;
对table进行的操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--修改表添加列 alter table reader add email varchar(30); --查看表的基本结构 desc reader; --修改表,在指定位置添加列 alter table reader add email2 varchar(30) after tel; --修改列名 alter table reader change email2 email_bak varchar(30); --修改列的数据类型 alter table reader modify email varchar(25); --修改列的排列位置 alter table reader modify balance decimal(7,3) after email; --删除列 alter table reader drop email_bak; --修改表名 alter table reader rename to readerinfo;
创建表时候添加非空约束及修改删除非空约束
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建表时添加非空约束 create table bookinfo( book_id int, book_name varchar(20) not null ); --删除非空约束 alter table bookinfo modify book_name varchar(20); --通过修改表添加非空约束 alter table bookinfo modify book_name varchar(20) not null;
创建表时添加主键约束
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建表时添加主键约束 create table bookinfo( book_id int primary key, book_name varchar(20) not null ); create table bookinfo( book_id int, book_name varchar(20) not null, constraint pk_id primary key(book_id) ); --删除主键约束 ALTER TABLE bookinfo DROP PRIMARY KEY; --通过修改表的方式添加主键约束 ALTER TABLE bookinfo ADD PRIMARY KEY(book_id);
unique唯一性约束
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建表时添加唯一约束 CREATE TABLE bookinfo( book_id INT PRIMARY KEY, book_name VARCHAR(20) NOT NULL UNIQUE ); --删除唯一约束 ALTER TABLE book_info DROP KEY uk_bname; --通过修改表的方式添加唯一约束 ALTER TABLE bookinfo ADD UNIQUE(book_name);
默认约束,初始化
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建表时添加默认约束 CREATE TABLE bookinfo( book_id INT PRIMARY KEY, press VARCHAR(20) DEFAULT '机械工业出版社' ); --删除默认约束 ALTER TABLE bookinfo ALTER COLUMN press DROP DEFAULT; --通过修改表的方式添加默认约束 ALTER TABLE bookinfo ALTER COLUMN press SET DEFAULT '机械工业出版社’;
外键约束;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--创建表时添加外键约束 --图书类别表(父表) CREATE TABLE bookcategory( category_id INT PRIMARY KEY, category VARCHAR(20), parent_id INT ); --图书信息表(子表) CREATE TABLE bookinfo( book_id INT PRIMARY KEY, book_category_id INT, CONSTRAINT fk_cid FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id) ); --删除外键约束 ALTER TABLE bookinfo DROP FOREIGN KEY fk_cid; --通过修改表的方式添加外键约束 ALTER TABLE bookinfo ADD FOREIGN KEY(book_category_id) REFERENCES bookcategory(category_id);
图书管理系统
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 打开图书管理的数据库 use book; -- 查看库下都有哪些表 show tables; -- 删除库中原有的表 drop table bookinfo; drop table bookcategory, readerinfo; -- 图书类别表 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) ); -- 读者信息表 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_date date not null, return_date date not null, status char(11) not null, primary key(book_id,card_id) );
向表中插入数据
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 为表的所有列插入数据 insert into bookcategory(category_id,category,parent_id)values(1,'计算机',0); insert into bookcategory values(2,'医学',0); -- 为表的指定列插入数据 insert into readerinfo(card_id,name,tel)values('210210199901011111','张飞','13566661111'); -- 同时插入多条记录 insert into bookcategory(category_id,category,parent_id)values(3,'编程语言',1),(4,'数据库',1),(5,'儿科学',2); -- 将查询结果插入的表中 insert into bookcategory select * from test where id>5;
创建表时添加自增列
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 创建表时添加自增列 create table bookcategory_tmp( category_id int primary key auto_increment, category varchar(20) not null unique, parent_id int not null )auto_increment=5; -- 测试自增列 insert into bookcategory_tmp(category,parent_id)values('医学',0);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- 创建表时添加自增列 create table bookcategory_tmp( category_id int primary key auto_increment, category varchar(20) not null unique, parent_id int not null )auto_increment=5; -- 测试自增列 insert into bookcategory_tmp(category,parent_id)values('医学',0); -- 去掉自增列 alter table bookcategory_tmp modify category_id int; -- 添加自增列 alter table bookcategory_tmp modify category_id int auto_increment; -- 修改自增列的起始值 alter table bookcategory_tmp auto_increment = 15; insert into bookcategory_tmp(category,parent_id)values('文学',0); -- 删除图书信息表的外键 alter table bookinfo drop foreign key fk_bcid; -- 为图书类别表添加自动编号的功能 alter table bookcategory modify category_id int auto_increment; -- 恢复关联 alter table bookinfo add constraint fk_bcid foreign key(book_category_id)references bookcategory(category_id);
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*图书类别表*/ insert into bookcategory(category,parent_id)values('计算机',0),('医学',0),('编程语言',1),('数据库',1),('儿科学',2); /*图书信息表*/ insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store) values (20150201,3, 'Java编程思想', '(美)埃克尔', 79.8, default, '2007-04-01', 5), (20150202,4, 'PHP和MySQL Web开发', 'Luke Welling等',95, default, '2009-04-01', 2), (20150301,3, 'Spring源码深度解析', '郝佳',69, '人民邮电出版社', '2013-09-01', 3), (20160801,5, '中医儿科学', '汪受传', 136, '人民卫生出版社', '2011-04-01', 1), (20170401,5, '小儿推拿秘笈', '李德修', 24.5, '人民卫生出版社', '2011-04-01',4); /*读者信息表*/ insert into readerinfo(card_id,name,sex,age,tel,balance) values (210210199901011111,'张飞','女',18,'13566661111',300), (210210199901012222,'李月','女',19,'13566662222',200), (210210199901013333,'王鹏','男',20,'13566663333',300), (210210199901014444,'刘鑫','男',21,'13566664444',400), (210210199901015555,'杨磊','男',22,'13566665555',500); /* 身份证号为210210199901011111的读者,2017-11-29借走了图书编号为20150201的这本书, 根据业务需求向借阅信息表插入一条借阅信息,更新读者信息表中的余额和图书信息表的库存,要求如下: 1、借书的期限为1个月。 2、借书的费用为:书的价格*5% 3、图书信息表中图书编号为20150201这本书的库存-1 */ -- 向借阅信息表插入一条借阅信息 insert into borrowinfo(book_id,card_id,borrow_date,return_date,status)values(20150201,210210199901011111,'2017-11-29','2017-12-29','否'); -- 更新读者信息表中的余额 -- 查看书的价格 79.80 select price from bookinfo where book_id = 20150201; -- 更新余额 update readerinfo set balance = balance - 79.80*0.05 where card_id = '210210199901011111'; select * from readerinfo; -- 更新图书信息表的库存 update bookinfo set store = store -1 where book_id = 20150201; select * from bookinfo;
提供测试的数据;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
/*图书类别表*/ insert into bookcategory(category,parent_id)values('计算机',0),('医学',0),('编程语言',1),('数据库',1),('儿科学',2); bookcategory /*图书信息表*/ insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store) values (20150201,3, 'Java编程思想', '(美)埃克尔', 79.8, default, '2007-04-01', 5), (20150202,4, 'PHP和MySQL Web开发', 'Luke Welling等',95, default, '2009-04-01', 2), (20150301,3, 'Spring源码深度解析', '郝佳',69, '人民邮电出版社', '2013-09-01', 3), (20160801,5, '中医儿科学', '汪受传', 136, '人民卫生出版社', '2011-04-01', 1), (20170401,5, '小儿推拿秘笈', '李德修', 24.5, '人民卫生出版社', '2011-04-01',4); /*读者信息表*/ insert into readerinfo(card_id,name,sex,age,tel,balance) values (210210199901011111,'张飞','女',18,'13566661111',300), (210210199901012222,'李月','女',19,'13566662222',200), (210210199901013333,'王鹏','男',20,'13566663333',300), (210210199901014444,'刘鑫','男',21,'13566664444',400), (210210199901015555,'杨磊','男',22,'13566665555',500);
第六章内容都是对table进行处理的方法