首页 > 基础资料博客日记

MySQL-11.数据处理之增删改

2024-01-09 17:21:16基础资料围观208次

本篇文章分享MySQL-11.数据处理之增删改，对你有帮助的话记得收藏一下，看Java资料网收获更多编程知识

1.插入数据(INSERT语句)

1.1 问题引入

向departments表中，新增一条数据。

解决方式，使用insert语句向表中插入数据。

1.2 方式1：values的方式添加

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

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

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

举例

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

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

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

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

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。没有默认值的列，如果可以为null，会赋null值，但是如果未给未设定默认值，且非空的列设置值的话，会报错。Field 'empNum' doesn't have a default value

在INSERT子句中可以随意列出列名，但是一旦列出，VALUES中要插入的value1,value2....需要于column1,column2...一一对应。如果类型不同，将无法完成数据的插入，并且MySQL会报错。

举例

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

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

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

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同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

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

小结

VALUES也可以写成VALUE，但是VALUES是标准写法。
字符和日期型数据应包含在单引号中。

1.3 方式2：将查询结果插入列表中

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

基本格式

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

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

举例

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%';

2.更新数据(UPDATE语句)

2.1 问题引入

修改表中的数据。

解决方式，使用UPDATE语句更新数据。

2.2 语法格式

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

可以一次更新多条数据。
如果需要回滚数据，需要保证执行DML语句前，进行设置：SET AUTOCOMMIT = false;
使用WHERE子句指定需要更新的数据

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;

如果省略WHERE子句，则表中的所有数据都将被更新。

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`))

说明，不存在55号部门。这是存在外键约束的情况下，才出现的。如果但对于department_id没有外键约束，则不会报错。

3.删除数据(DELETE语句)

3.1 问题引入

使用DELETE语句从表中删除数据

3.2 语法格式

DELETE FROM table_name
[WHERE condition];

table_name指定要删除的表；[WHERE]为可选子句，指定删除条件，如果没有WHERE子句，DELETE语句将删除表中所有的数据。

使用WHERE子句删除指定的记录。

DELETE FROM departments
WHERE department_name = 'Finance';

如果省略WHERE子句，则表中的全部数据将被删除。

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`))

说明，如果删除的列，使用了外键，需要先从外键表中删除，才能删除本表中的记录

4.MySQL8新特性：计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

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

值。首先创建测试表tb1，语句如下：

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);

查询数据表tb1中的数据，结果如下：

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

5.综合案例

# 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;

只是为了记录自己的学习历程，且本人水平有限，不对之处，请指正。

文章来源:https://www.cnblogs.com/changming06/p/17954951
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：jacktools123@163.com进行投诉反馈，一经查实，立即删除！

标签：

上一篇：关于对Java单例模式的理解与简述
下一篇：javascript实现网页背景音乐自动播放

首页 > 基础资料博客日记

MySQL-11.数据处理之增删改

1.插入数据(INSERT语句)

1.1 问题引入

1.2 方式1：values的方式添加

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

举例

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

举例

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

举例

小结

1.3 方式2：将查询结果插入列表中

基本格式

举例

2.更新数据(UPDATE语句)

2.1 问题引入

2.2 语法格式

3.删除数据(DELETE语句)

3.1 问题引入

3.2 语法格式

4.MySQL8新特性：计算列

5.综合案例

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料 博客日记

MySQL-11.数据处理之增删改

1.插入数据(INSERT语句)

1.1 问题引入

1.2 方式1：values的方式添加

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

举例

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

举例

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

举例

小结

1.3 方式2：将查询结果插入列表中

基本格式

举例

2.更新数据(UPDATE语句)

2.1 问题引入

2.2 语法格式

3.删除数据(DELETE语句)

3.1 问题引入

3.2 语法格式

4.MySQL8新特性：计算列

5.综合案例

相关文章

最新发布

点击排行

本站推荐

标签云

首页 > 基础资料博客日记