MySQL-11.数据处理之增删改 - ToB企服应用市场:ToB评测及商务社交产业平台

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

复制代码

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

复制代码

INSERT INTO 表名(column1[,column2,...])
VALUES(value1 [,value2,...])

复制代码

INERT INTO departments(department_id,department_name)
VALUES(70,'IT');

复制代码

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

复制代码

INSERT INTO emp01(employee_id,last_name,email,hire_date,job_id)
VALUES (101,'wind','wind1215@126.com','2022-06-18','0001'),
(102,'flower','wind1232@126.com','2022-02-18','0001'),
(103,'snow','wind1223@126.com','2022-12-13','0001');

复制代码

INSERT INTO table_name
(tar_column1[,tar_column2,...tar_columnn])
SELECT
(tar_column1[,tar_column2,...tar_columnn])
FROM src_table_name
[WHERE condition];

复制代码

INSERT INTO emp02
SELECT * FROM atguigudb.`employees`;
INSERT INTO salas_reps(id,name,salary,commission_pct)
SELECT employee_id,last_name,salar,commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

复制代码

UPDATE table_name
SET column1=value1,column2=value2,....
[WHERE condition];

复制代码

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

复制代码

UPDATE emp01
SET department_id = 70;

复制代码

mysql> UPDATE employees
-> SET department_id = 55
-> WHERE department_id = 110;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))

复制代码

DELETE FROM table_name
[WHERE condition];

复制代码

DELETE FROM departments
WHERE department_name = 'Finance';

复制代码

DELETE FROM emp01;

复制代码

mysql> DELETE FROM departments
-> WHERE department_id = 60;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))

复制代码

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

复制代码

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

复制代码

mysql> SELECT * FROM tb1;
+------+------+------+------+
| id | a | b | c |
+------+------+------+------+
| NULL | 100 | 200 | 300 |
+------+------+------+------+
1 row in set (0.00 sec)

复制代码

mysql> UPDATE tb1 SET a = 500;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

复制代码

# 1、创建数据库test01_library
CREATE DATABASE test01_library CHARSET 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(100,'小灰的漫画算法','小灰',30.0,'2023','算法入门书籍',001);
SELECT * FROM books;
# 2）指定所有字段名称，插入第二记录
INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)
VALUES(101,'小王子','圣埃克斯佩里',27.0,'2023','童话书籍',002);
# 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
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE SET price = 40,note = 'drama' WHERE `name` = 'EmmaT';
# 6、删除库存为0的记录。
DELETE FROM books WHERE num = 0;
# 7、统计书名中包含a字母的书
SELECT * FROM books
WHERE `name` REGEXP 'a';
# 8、统计书名中包含a字母的书的数量和库存总量
SELECT COUNT(*),SUM(num) AS 'all_num'
FROM books
WHERE `name` REGEXP 'a';
# 9、找出“novel”类型的书，按照价格降序排列
SELECT * FROM books
WHERE note = 'novel'
ORDER BY price DESC;
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列
SELECT * FROM books
ORDER BY num DESC,note;
# 11、按照note分类统计书的数量
SELECT COUNT(*),note FROM books
GROUP BY note;
# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT SUM(num),note FROM books
GROUP BY note;
HAVING SUM(num) > 30
# 13、查询所有图书，每页显示5本，显示第二页
SELECT * FROM books LIMIT 5,5;
# 14、按照note分类统计书的库存量，显示库存量最多的
SELECT SUM(num) AS sum_num,note
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`,(CASE WHEN note = 'novel' THEN '小说'
WHEN note = 'law' THEN '法律'
WHEN note = 'medicine' THEN '医药'
WHEN note = 'cartoon' THEN '卡通'
WHEN note = 'joke' THEN '笑话'
ELSE '其他' END) AS '类型'
FROM books;
# 17、查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货
SELECT `name`,num,(CASE WHEN num > 30 THEN '滞销'
WHEN 10 > num AND num > 0 THEN '畅销'
WHEN num = 0 THEN '无货' END) AS '销售状态'
FROM books
# 18、统计每一种note的库存量，并合计总量
SELECT SUM(num) FROM books
GROUP BY note WITH ROLLUP;
# 19、统计每一种note的数量，并合计总量
SELECT 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
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;

复制代码