第六章：数据库表记录的操作

本节目标：掌握单表数据记录插入的相关操作

插入数据：在使用数据库之前，数据库表中必须有数据，我们需要使用INSERT语句向数据库表中插入数据。

插入数据的方式：

1. 插入完成的记录；
2. 插入记录的一部分；
3. 插入多条记录；
4. 插入另一个查询的结果；

1、为表的所有列插入数据

语法格式： INSERT INTO table_name(column_list) VALUES (value_list);

2、为表的指定列插入数据

为表的指定列插入数据，就是在INSERT语句中只向部分列中插入值，而其他没有指定的列所插入的数据为表定义时的默认值。

3、同时插入多条记录

INSERT 语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号隔开，基本语法格式如下：

INSERT INTO table_name(column_list) VALUES(values_list1),(values_list2),……，(values_listn);

4、将查询结果插入到表中

insert 可以将select语句查询的结果插入到表中。

语法格式：

INSERT INTO table_name1(column_list1)SELECT(column_list2)FROM table_name2 WHERE(condition);

示例：

▶为表的所有列插入数据
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;

本节目标

掌握给表添加自动增长列的方法。

为什么设置自动编号？

设置表的属性值自动增加

语法格式：

列名 数据类型 AUTO_INCREMENT

注：AUTO_INCREMENT 约束的字段可以是任何整数类型（tinyint、smallint、int）

举例：

CREATE TABLE bookcategory(
category_id int primary key auto_increment,
category varchar(20) not null qnique,
parent_id int not null
);

自增列的初始值默认是1，每添加一条记录，自动增加1。

在建表时可用“AUTO_INCREMENT=n”选项来指定一个自增的初始值。

CREATE TABLE bookcategory_tmp(
category_id INT PRIMARY KEY AUTO_INCREMENT,
category VARCHAR(20) NOT NULL UNIQUE,
parent_id INT NOT NULL
)AUTO_INCREMENT=2;

INSERT INTO bookcategory_tmp(category,parent_id)VALUES('计算机',0);

为已有的表添加自增列

CREATE TABLE bookcategory(
category_id int primary key,
category varchar(20) not null unique,
parent_id int not null
);

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 tabel bookcategory auto_increment=x;

修改后auto_increment列起始值从x开始；

示例：

▶创建表时添加自增列
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);

▶创建表时添加自增列
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);

本节目标：掌握单表数据记录更新的操作

Mysql中使用的UPDATE语句更新表中的记录；

1. 更新特定行
2. 更新所有行

▶单表数据记录的更新

语法格式：
UPDATE table_name
SET column_name1=value1，
column_name2=value2，……，
column_namen=valuen
WHERE(condition);

添加测试数据：

/*图书类别表*/
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;

本节目标：掌握单表数据记录删除的操作

1、单表记录的删除

使用delete语句删除满足条件的记录，语法格式如下：

DELETE FROM table_name [WHERE ]

2、单表记录的删除

如果想删除表中的所有记录，还可以使用TRUNCATE TABLE 语句，TRUNCATE将直接删除原来的表，并重新创建一个表，其语法结构为：

TRUNCATE TABLE table_name

▶删除指定条件的记录
delete from readerinfo where card_id = '210210199901011111';

▶删除表中所有记录
delete from readerinfo;

/*读者信息表*/
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);

truncate table readerinfo;

案例：图书管理系统要下架所有关于儿科学的书,实现的需求如下：

1、删除图书信息表中的所有关于儿科学的书。

2、删除图书类别表中儿科学这一类别。

select category_id from bookcategory where category='儿科学';

▶ 删除图书编号为5的图书信息
delete from bookinfo where book_category_id = 5;

▶删除图书类别表中儿科学这个类别
delete from bookcategory where category = '儿科学';

本节目标：掌握单表数据记录查询的操作

MySQL从数据表中查询数据的基本语句为SELECT语句。

语法结构：

SELECT select_expr[,select_expr…] 查询表达式可以是一列或多列；
[
FROM table_references 表名称
[WHERE where_condition] 查询条件
[GROUP BY {col_name | position}[ASC|DESC],…] 对查询进行分组
[HAVING where_condition] 过滤分组条件
[ORDER BY {col_name | expr | position}[ASC|DESC],…] 对查询结果进行排序
[LIMIT{[offset,]row_count | row_count OFFSET offset}] 限制查询结果数据行的个数
]

1、查询所有的列：

在SELECT 语句中使用星号“*”通配符查询所有列

如：SELECT * FROM bookcategory;

在SELECT语句中指定所有列

如：SELECT category_id,category,parent_id FROM bookcategory;

2、查询指定列

查询单个列 如：SELECT category FROM SELECT bookcategory;

查询多个列 如：SELECT category_id,category FROM bookcategory;

3、查询指定记录

在SELECT语句中通过WHERE子句，对数据进行过滤，语法格式为：

SELECT 列名1,列名2,…列名n
FROM 表名
WHERE 查询条件

如：SELECT book_id,book_name,price FROM bookinfo WHERE press='机械工业出版社'；

4、查询结果不重复

在SELECT语句中可以使用DISTINCT关键字指示MySQL消除重复的记录值。

SELECT DISTINCT 列名 FROM 表名；

如：SELECT DISTINCT press FROM bookinfo;

5、查询空值

在SELECT语句中使用 IS NULL 子句，可以查询某列内容为空记录。

如：SELECT * FROM readerinfo WHERE age IS NULL;

实例：

▶查询所有列
select * from bookcategory;

select category_id,category,parent_id from bookcategory;

▶查询指定列
select category from bookcategory;

select category_id,category from bookcategory;

▶查询指定条件的记录
select book_id,book_name,price from bookinfo where press='机械工业出版社';

▶查询结果不重复的记录
select distinct press from bookinfo;

▶查看空值
select * from readerinfo where age is null;

本节目标：掌握分组查询的操作

1、分组查询：是对数据按照某个或多个列进行分组。

[GROUP BY 列名][HAVING <条件表达式>]

2、创建分组：查询结果分组和GROUP BY 列名

Group by 通常和聚合函数一起使用，例如：MAX(),MIN(),SUM(),AVG(),COUNT()。

例如：

SELECT count(*) FROM readerinfo WHERE sex='男';
SELECT sex,count(*) FROM readerinfo GROUP BY sex;

3、使用having过滤分组

[HAVING<条件表达式>]
HAVING限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

例：SELECT sex FROM readerinfo GROUP BY sex HAVING count(sex)>2;

示例

▶统计读者信息表中男读者的人数
select count(*) from readerinfo where sex='男';

▶将读者信息表中的记录按性别进行分组
select sex from readerinfo group by sex;

▶将读者信息表中的记录按性别进行分组，并统计每种性别的人数
select sex,count(*) from readerinfo group by sex;

▶将读者信息表中的记录按性别进行分组，分组后人数大于的性别
select sex from readerinfo group by sex having count(sex)>2;

本节目标：掌握对查询结果进行排序的操作

1、对查询结果排序

MySQL中可以通过ORDER BY 子句对查询的结果进行排序。

ORDER BY 列名 [ASC|DESC] /*ASC升序，DESC降序*/

2、单列排序：按照指定的某个列进行排序，默认为升序
例：SELECT * FROM bookinfo ORDER BY price;

3、多列排序：按照多个列进行排序，默认升序
例：SELECT * FROM bookinfo ORDER BY price,store;

4、指定排序方向：排序分为升序和降序，默认为升序。

升序ASC；降序DESC;

例：SELECT * FROM bookinfo ORDER BY price ASC, store DESC；

示例：

▶单列排序
select * from bookinfo order by price;

▶多列排序
select * from bookinfo order by price,store;

▶指定排序方向
select * from bookinfo order by price,store desc;

本节目标：掌握通过limit语句限制查询记录的数量

1、用LIMIT限制查询结果的数量

LIMIT关键字可以返回指定位置的记录。

语法格式：LIMIT[位置偏移量,]行数

说明：第一条记录的位置偏移量为0，第二条为1，…以此类推。

2、例：显示图书信息表的前3行记录

SELECT * FROM bookinfo LIMIT 3;

显示从第三条记录开始后的2条图书信息记录。

SELECT * FROM bookinfo LIMIT 2,2;

说明：MySQL5.7中也可以使用 LIMIT 2 OFFSET 2，实现例2相同的效果。

▶前3行记录
select * from bookinfo limit 3;

▶从第3条记录开始的后2条记录
select * from bookinfo limit 2,2;

select * from bookinfo limit 2 offset 2;

insert into bookinfo(book_id,book_category_id,book_name,author,price,press,pubdate,store)
values
(20160011,4, '高性能MySQL', 'Baron Schwartz等',85.8, '电子工业出版社', '2013-04-01', 10),
(20160210,4, '数据库系统基础教程', '[美]厄尔曼等', 35.5, '机械工业出版社', '2009-08-01', 20),
(20170050,4, '数据库系统实现', '[美]加西亚·莫利纳 ', 46.6, '机械工业出版社', '2010-05-01',8);

▶将图书信息按照库存进行分组，统计每组库存下的个数，然后按库存进行降序排序，并查看结果中的前四条记录。

select store,count(*)from bookinfo group by store order by store desc limit 4;