正心全栈编程-文档站

Appearance

增删改查 ​

增删改查是数据库最常用的操作

请为下面这些数据新建一个数据表，并将其写入。

txt
('秦岚,糸井重里,岛本须美', '龙猫', '2018-12-14', '9.1'),
('张国荣,张曼玉,刘德华', '阿飞正传', '2018-06-25', '8.8'),
('俞承豪,金艺芬,童孝熙', '爱·回家', '2002-04-05', '9.0'),
('雅克·贝汉,姜文,兰斯洛特·佩林', '海洋', '2011-08-12', '9.0'),
('宋在浩,李顺才,尹秀晶', '我爱你', '2011-02-17', '9.0'),
('克林特·伊斯特伍德,李·范·克里夫,埃里·瓦拉赫', '黄金三镖客', '1966-12-23(意大利)', '8.9'),
('雅克·贝汉,Philippe Labro', '迁徙的鸟', '2001-12-12', '9.1'),
('柊瑠美,周冬雨,入野自由', '千与千寻', '2019-06-21', '9.3'),
('蒂姆·罗斯,比尔·努恩,普路特·泰勒·文斯', '海上钢琴师', '2019-11-15', '9.3'),
('菲利浦·诺瓦雷,赛尔乔·卡斯特利托,蒂兹亚娜·罗达托', '天堂电影院', '2019-06-15', '9.2')

创建数据表

sql
create table maoyan
(
    id      int primary key auto_increment,
    star    varchar(50),
    name    varchar(50),
    publish varchar(50),
    score   varchar(50)
);

新增数据 ​

方式1：VALUES的方式 添加

使用这种语法一次只能向表中插入一条数据。

情况1： 为表的所有字段按默认顺序插入数据

sql
INSERT INTO 表名
VALUES (value1,value2,....);

值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

sql
insert into maoyan
values (1, '秦岚,糸井重里,岛本须美', '龙猫', '2018-12-14', '9.1');

情况2： 为表的指定字段插入数据

sql
INSERT INTO 表名(column1 [, column2, ..., columnn])
VALUES (value1[, value2, ..., valuen]);

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

在 INSERT 子句中随意列出列名，但是一旦列出，VALUES 中要插入的 ·需要与 column1,...,columnn 列一一对应。如果类型不同，将无法插入，并且 MySQL 会产生错误。

举例：

sql
insert into maoyan(id, star, name, publish, score)
values (2, '张国荣,张曼玉,刘德华', '阿飞正传', '2018-06-25', '8.8');

-- id 如果给0则是自动填充
insert into maoyan(id, star, name, publish, score)
values (0, '俞承豪,金艺芬,童孝熙', '爱·回家', '2002-04-05', '9.0');

-- 如果不想给 id,可以不给字段
insert into maoyan(star, name, publish, score)
values ('雅克·贝汉,姜文,兰斯洛特·佩林', '海洋', '2011-08-12', '9.0');

情况3： 同时插入多条记录

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

sql
INSERT INTO table_name
VALUES (value1[, value2, ..., valuen]),
       (value1[, value2, ..., valuen]), ...
       (value1[, value2, ..., valuen]);

或者

sql
INSERT INTO table_name(column1 [, column2, ..., columnn])
VALUES (value1[, value2, ..., valuen]),
       (value1[, value2, ..., valuen]), ...
       (value1[, value2, ..., valuen]);

举例：

sql
insert into maoyan(star, name, publish, score)
values ('宋在浩,李顺才,尹秀晶', '我爱你', '2011-02-17', '9.0'),
       ('克林特·伊斯特伍德,李·范·克里夫,埃里·瓦拉赫', '黄金三镖客', '1966-12-23(意大利)', '8.9'),
       ('雅克·贝汉,Philippe Labro', '迁徙的鸟', '2001-12-12', '9.1');

insert into maoyan(id, star, name, publish, score)
values (0, '柊瑠美,周冬雨,入野自由', '千与千寻', '2019-06-21', '9.3'),
       (0, '蒂姆·罗斯,比尔·努恩,普路特·泰勒·文斯', '海上钢琴师', '2019-11-15', '9.3'),
       (0, '菲利浦·诺瓦雷,赛尔乔·卡斯特利托,蒂兹亚娜·罗达托', '天堂电影院', '2019-06-15', '9.2');

使用 INSERT 同时插入多条记录时，MySQL 会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

  • Records：表明插入的记录条数。
  • Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
  • Warnings：表明有问题的数据值，例如发生数据类型转换。

提示

一个同时插入多行记录的 INSERT 语句等同于多个单行插入的 INSERT 语句，但是多行的 INSERT 语句在处理过程中效率更高 。因为 MySQL 执行单条 INSERT 语句插入多行数据比使用多条 INSERT 语句快，所以在插入多条记录时最好选择使用单条 INSERT 语句的方式插入。

小结：

  • VALUES也可以写成VALUE，但是 VALUES 是标准写法。

  • 字符和日期型数据应包含在单引号中。

将查询结果插入到表中 ​

INSERT 还可以将 SELECT 语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条 INSERT 语句和一条 SELECT 语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

基本语法格式如下：

sql
INSERT INTO 目标表名(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT (src_column1[, src_column2, . . ., src_columnn])
FROM 源表名 [WHERE condition]
  • 在 INSERT 语句中加入子查询。
  • 不必书写 VALUES 子句。
  • 子查询中的值列表应与 INSERT 子句中的列名对应。

举例：

sql
INSERT INTO maoyan_back
SELECT *
FROM maoyan
WHERE id = 1;

更新数据 ​

  • 使用 UPDATE 语句更新数据。语法如下：
sql
UPDATE table_name
SET column1=value1,
    column2=value2, ... ,
    column=valuen
        [WHERE condition]
  • 可以一次更新多条数据。
  • 如果需要回滚数据，需要保证在 DML 前，进行设置：SET AUTOCOMMIT = FALSE;
  • 使用 WHERE 子句指定需要更新的数据。
  • 如果省略 WHERE 子句，则表中的所有数据都将被更新。

删除 ​

  • 使用 DELETE 语句从表中删除数据
sql
DELETE FROM table_name [WHERE <condition>];

table_name 指定要执行删除操作的表； [WHERE <condition>] 为可选参数，指定删除条件，如果没有 WHERE 子句，DELETE 语句将删除表中的所有记录。

  • 使用 WHERE 子句删除指定的记录。
  • 如果省略 WHERE 子句，则表中的全部数据将被删除

综合案例 ​

sql
-- 1、创建数据库 test01_library

-- 2、创建表 books，表结构如下：
字段名字段说明数据类型
id书编号INT
name书名VARCHAR(50)
authors作者VARCHAR(100)
price价格FLOAT
pubdate出版日期YEAR
note说明VARCHAR(100)
num库存INT
sql
-- 3、向 books 表中插入记录

-- 1）不指定字段名称，插入第一条记录
-- 2）指定所有字段名称，插入第二记录
-- 3）同时插入多条记录（剩下的所有记录）
idnameauthorspricepubdatenotenum
1Tal of AAADickes231995novel11
2EmmaTJane lura351993joke22
3Story of JaneJane Tim402001novel0
4Lovey DayGeorge Byron202005novel30
5Old landHonore Blade302010law0
6The BattleUpton Sara301999medicine40
7Rose HoodRichard haggard282008cartoon28
sql
-- 4、将小说类型(novel)的书的价格都增加5。

-- 5、将名称为 EmmaT 的书的价格改为 40，并将说明改为 drama。

-- 6、删除库存为 0 的记录。
sql
-- 7、统计书名中包含 a 字母的书

-- 8、统计书名中包含 a 字母的书的数量和库存总量

-- 9、找出“novel”类型的书，按照价格降序排列

-- 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照 note 升序排列

-- 11、按照 note 分类统计书的数量

-- 12、按照 note 分类统计书的库存量，显示库存量超过 30 本的

-- 13、查询所有图书，每页显示 5 本，显示第二页

-- 14、按照 note 分类统计书的库存量，显示库存量最多的

-- 15、查询书名达到 10 个字符的书，不包括里面的空格

-- 16、查询书名和类型，其中 note 值为 novel 显示小说，law 显示法律，medicine 显示医药，cartoon 显示卡通，joke 显示笑话

-- 17、查询书名、库存，其中 num 值超过 30 本的，显示滞销，大于 0 并低于 10 的，显示畅销，为 0 的显示需要无货

-- 18、统计每一种 note 的库存量，并合计总量

-- 19、统计每一种 note 的数量，并合计总量

-- 20、统计库存量前三名的图书

-- 21、找出最早出版的一本书

-- 22、找出 novel 中价格最高的一本书

-- 23、找出书名中字数最多的一本书，不含空格

答案：

sql
-- 1、创建数据库 test01_library
CREATE
DATABASE IF NOT EXISTS test01_library CHARACTER SET 'utf8';

-- 指定使用哪个数据库
USE
test01_library;

-- 2、创建表 books
CREATE TABLE books
(
    id        INT,
    name      VARCHAR(50),
    `authors` VARCHAR(100),
    price     FLOAT,
    pubdate YEAR,
    note      VARCHAR(100),
    num       INT
);

-- 3、向 books 表中插入记录
-- 1）不指定字段名称，插入第一条记录
INSERT INTO books
VALUES (1, 'Tal of AAA', 'Dickes', 23, 1995, 'novel', 11);
-- 2）指定所有字段名称，插入第二记录
INSERT INTO books (id, name, `authors`, price, pubdate, note, num)
VALUES (2, 'EmmaT', 'Jane lura', 35, 1993, 'Joke', 22);
-- 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books (id, name, `authors`, price, pubdate, note, num)
VALUES (3, 'Story of Jane', 'Jane Tim', 40, 2001, 'novel', 0),
       (4, 'Lovey Day', 'George Byron', 20, 2005, 'novel', 30),
       (5, 'Old land', 'Honore Blade', 30, 2010, 'Law', 0),
       (6, 'The Battle', 'Upton Sara', 30, 1999, 'medicine', 40),
       (7, 'Rose Hood', 'Richard haggard', 28, 2008, 'cartoon', 28);

-- 4、将小说类型(novel)的书的价格都增加5。
UPDATE books
SET price=price + 5
WHERE note = 'novel';

-- 5、将名称为 EmmaT 的书的价格改为40，并将说明改为 drama 。
UPDATE books
SET price=40,
    note='drama'
WHERE name = 'EmmaT';

-- 6、删除库存为0的记录。
DELETE
FROM books
WHERE num = 0;
sql
-- 7、统计书名中包含 a 字母的书
SELECT *
FROM books
WHERE name LIKE '%a%';

-- 8、统计书名中包含 a 字母的书的数量和库存总量
SELECT COUNT(*), SUM(num)
FROM books
WHERE name LIKE '%a%';

-- 9、找出“novel”类型的书，按照价格降序排列
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC;

-- 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照 note 升序排列
SELECT *
FROM books
ORDER BY num DESC, note ASC;

-- 11、按照 note 分类统计书的数量
SELECT note, COUNT(*)
FROM books
GROUP BY note;

-- 12、按照 note 分类统计书的库存量，显示库存量超过30本的
SELECT note, SUM(num)
FROM books
GROUP BY note
HAVING SUM(num) > 30;

-- 13、查询所有图书，每页显示5本，显示第二页
SELECT *
FROM books LIMIT 5,5;

-- 14、按照 note 分类统计书的库存量，显示库存量最多的
SELECT note, SUM(num) sum_num
FROM books
GROUP BY note
ORDER BY sum_num DESC LIMIT 0,1;

-- 15、查询书名达到10个字符的书，不包括里面的空格
SELECT *
FROM books
WHERE CHAR_LENGTH(REPLACE(name, ' ', '')) >= 10;

/*
16、查询书名和类型，
 其中 note 值为 novel显示小说， law 显示法律， medicine 显示医药， cartoon 显示卡通， joke 显示笑话
*/
SELECT name    AS "书名",
       note,
       CASE note
           WHEN 'novel' THEN '小说'
           WHEN 'law' THEN '法律'
           WHEN 'medicine' THEN '医药'
           WHEN 'cartoon' THEN '卡通'
           WHEN 'joke' THEN '笑话'
           END AS "类型"
FROM books;


-- 17、查询书名、库存，其中 num 值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT name,
       num,
       CASE
           WHEN num > 30 THEN '滞销'
           WHEN num > 0 AND num < 10 THEN '畅销'
           WHEN num = 0 THEN '无货'
           ELSE '正常'
           END AS "库存状态"
FROM books;

-- 18、统计每一种 note 的库存量，并合计总量
SELECT IFNULL(note, '合计总库存量') AS note, SUM(num)
FROM books
GROUP BY note WITH ROLLUP;

-- 19、统计每一种 note 的数量，并合计总量
SELECT IFNULL(note, '合计总数') AS note, COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;

-- 20、统计库存量前三名的图书
SELECT *
FROM books
ORDER BY num DESC LIMIT 0,3;

-- 21、找出最早出版的一本书
SELECT *
FROM books
ORDER BY pubdate ASC LIMIT 0,1;

-- 22、找出 novel 中价格最高的一本书
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC LIMIT 0,1;

-- 23、找出书名中字数最多的一本书，不含空格
SELECT *
FROM books
ORDER BY CHAR_LENGTH(REPLACE(name, ' ', '')) DESC LIMIT 0,1;