【MySQL】数据处理之增删改

1、插入数据 INSERT INTO

• 使用 INSERT 语句向表中插入数据。

1.1 INSERT INTO VALUES

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

INSERT INTO table_name
VALUES (value1,value2,…);

• VALUES是标准写法， 也可以写成VALUE。
• 字符和日期型数据应包含在单引号中。
• 需要为每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);

INSERT INTO dept02 
VALUES (1001, 'Coding', 1, NULL, NUll);

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

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

• 为指定字段插入数据，其他字段的值为表定义时的默认值。
• 如果没有默认值必须进行指定。
• 值的顺序必须和列出的字段一一对应。
• 指定列名的插入方式只要列名顺序和值的顺序一致即可，与表字段顺序无关。

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

INSERT INTO dept02 (department_id, manager_id)
VALUE (1002, 2);
-- VARCHAR类型如果没有默认值，必须指定

ALTER TABLE dept02 
ALTER department_name SET DEFAULT 'Others';
-- 设置默认值

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

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

• MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句效率更高。

INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'shkstart'),
(1002,'atguigu'),
(1003,'Tom');
-> Query OK, 3 rows affected (0.00 sec)
-> Records: 3 Duplicates: 0 Warnings: 0
/**
 ●　Records：表明插入的记录条数。 
 ●　Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。 
 ●　Warnings：表明有问题的数据值，例如发生数据类型转换。
*/

INSERT INTO dept02 
VALUES (1003, 'Docter', 1, 2000, Null),
(1003, 'Docter', 1, 2000, Null),
(1003, 'Docter', 1, 2000, Null);
-- 没有设置主键

INSERT INTO dept02 (department_id, manager_id)
VALUE (1004, 4),
(1005, 4),
(1006, 4);

1.2 INSERT INTO SELECT …

将查询结果插入到表中。

INSERT INTO table_name (tar_column1 [, tar_column2, …, tar_columnn])
SELECT (src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

• 在 INSERT 语句中加入子查询。
• 不必书写 VALUES 子句。
• 子查询中的值列表应与 INSERT 子句中的列名对应。

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

INSERT INTO dept02 (department_id, department_name, manager_id, location_id)
SELECT *
FROM atguigudb.departments ad
WHERE ad.department_id = 250;

2、更新数据 UPDATE SET

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

• 可以一次更新多条数据。
• 如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;
• 使用 WHERE 子句指定需要更新的数据。
• 如果省略 WHERE 子句，则表中的所有数据都将被更新。

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

UPDATE copy_emp
SET department_id = 110;
# 表中的所有数据都将被更新

UPDATE dept02 
SET department_name = 'Van'
WHERE department_id = 1002;

3、删除数据 DELETE FROM

DELETE FROM table_name [WHERE ];

• 如果没有WHERE子句，DELETE语句将删除表中的所有记录。

DELETE FROM departments
WHERE department_name = 'Finance';

DELETE FROM copy_emp;

DELETE FROM dept02 
WHERE department_id = 1003;

4、 MySQL8新特性：计算列

举例：定义数据表tb1，然后定义字段id、字段a、字段b和字段c，其中字段c为计算列，用于计算a+b的值。

CREATE TABLE tb1 (
	id INT,
	a INT,
	b INT, 
	c INT GENERATED ALWAYS AS (a + b) VIRTUAL  
);

INSERT INTO tb1 (a, b)
VALUES (100, 20);

UPDATE tb1 
SET a = 200;

SELECT * FROM tb1;
# c列会自动刷新结果为 a + b;

5、综合案例

• 指定列名的插入方式，只要列名顺序和值的顺序一致即可，与表定义的字段顺序无关。
• 字符串包含特定字母使用： LIKE ‘%a%’;
• 统计个数使用：COUNT(*);
• 统计总数使用：SUM(num);
• 排序使用：ORDER BY DESC; ORDER BY ASC;
• 分类使用：GROUP BY
• 分类后条件使用：HAVING
• 分页使用：LIMIT [开始位置] 记录条数，开始位置从0开始。
• 选择最多最少：先排序，再 LIMIT 0, 1;
• 统计字符个数：CHAR_LENGTH(str);
• 替换特定字符：REPLACE(str, ‘a’, ‘b’);
• 不含空格达到10个字符的字串：CHAR_LENGTH(REPLACE(str, ’ ', ‘’)) >= 10;
• 使用 CASE，前有逗号 , 后有 END，最后From；
• 合并总量使用：WITH ROLLUP;
• 合并总量命名：IFNULL(note, ‘合计总量’) AS note

# 1、创建数据库test01_library
CREATE DATABASE IF NOT EXISTS test01_library CHARACTER SET utf8;
SHOW DATABASES;

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

SELECT DATABASE();

SHOW TABLES FROM test01_library;

CREATE TABLE books (
	id INT,
	name VARCHAR(50),
	authors VARCHAR(50),
	price FLOAT,
	pubdate YEAR,
	note VARCHAR(100),
	num INT
);

SHOW TABLES FROM test01_library;

DESC books;

# 3、向books表中插入记录
-- id name authors price pubdate note num
-- 1 Tal of AAA Dickes 23 1995 novel 11
-- 2 EmmaT Jane lura 35 1993 joke 22
-- 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
# 1）不指定字段名称，插入第一条记录
# 2）指定所有字段名称，插入第二记录
# 3）同时插入多条记录（剩下的所有记录）
INSERT INTO books 
VALUES (1, 'Tal of AAA', 'AAA', 23, 1995, 'novel', 11);

SELECT * FROM books;

INSERT INTO books (id, name, authors, price, pubdate, note, num)
VALUES (2, 'EmmaT Jane', 'lura', 35, 1993, 'joke', 22);

INSERT INTO books (name, authors, id, price, pubdate, note, num)
VALUES ('Story of Jane', 'Jane Tim', 3, 40, 2001, 'novel', 0);
-- 指定列名的插入方式只要列名顺序和值的顺序一致即可，与表字段顺序无关

INSERT INTO books 
VALUES
(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';

SELECT * FROM books;

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

# 6、删除库存为0的记录。
DELETE FROM books 
WHERE num = 0;

# 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分类统计书的数量 --
-- 注意书的数量是count(*)，不是个数num
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本，显示第二页 --
-- LIMIT [开始位置] 记录条数 
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个字符的书，不包括里面的空格 --
# REPLACE(str,from_str,to_str)
SELECT * FROM books 
WHERE CHAR_LENGTH(REPLACE(name,' ', '')) >= 10;

# 16、查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通，joke显示笑话 --
SELECT name, note, CASE note
	WHEN 'novel' THEN 'xiaoshuo'
	WHEN 'law' THEN 'falv'
	WHEN 'medicine' THEN 'yiyao'
	WHEN 'cartoon' THEN 'katong'
  WHEN 'joke' THEN 'xiaohua'
END
FROM books;

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 num
	WHEN num > 30 THEN '滞销'
	WHEN num > 0 AND num < 10 THEN '畅销'
	ELSE '无货'
END AS '库存'
FROM books;

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

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;

SELECT IFNULL(note, '合计总数') AS note, COUNT(num) 
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;



DROP TABLE books;

SHOW TABLES FROM test01_office;