【mysql】—— 表的增删改查

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 【mysql】—— 表的增删改查



序言

CRUD 是指对于数据存储系统的常见操作,包括 Create(创建)、Retrieve(读取)、Update(更新)和Delete(删除)。它是一种通用的数据操作模式,广泛应用于数据库和其他数据管理系统中。

下面对每个操作进行简要说明:

  1. Create(创建):用于向数据存储系统中创建新的记录或实体。这可以是插入一条新的数据记录、创建一个新的文件、或在数据库中添加一条新的记录等。
  2. Retrieve(读取):用于从数据存储系统中读取已有的记录或实体。这可以是根据特定条件查询数据库中的数据、读取文件的内容、或获取特定对象的属性值等。
  3. Update(更新):用于修改数据存储系统中已有的记录或实体。这可以是更新数据库中的信息、修改文件的内容、或更改对象的属性值等。
  4. Delete(删除):用于从数据存储系统中移除现有的记录或实体。这可以是从数据库中删除数据、删除文件、或销毁对象等。

这些操作可以进一步扩展和组合,以满足更复杂的需求。通过上述操作,可以实现对表的数据进行增加、删除、修改和查询操作。


(一)Create操作

MySQL 中的 CREATE 操作用于创建新的数据库、表、视图、索引或存储过程等对象。下面对 MySQL 中 CREATE 操作的几个常见用法进行详解:

语法:

INSERT [INTO] table_name
    [(column [, column] ...)]
    VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

案例:

  • 接下来,我简单的创建一张名为 - students 的表。具体如下:
CREATE TABLE students (
    -> id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> sn INT NOT NULL UNIQUE COMMENT '学号',
    -> name VARCHAR(20) NOT NULL,
    -> qq VARCHAR(20) unique key
    -> );
  • 具体如下图所示:


1、单行数据 + 全列插入

【说明】

  1. 首先,因为 values 左边没有括号指明插入的情况,因此这里默认的是全插入的情况;
  2. 这里在插入的时候,也可以不用指定id(当然,那时候就需要明确插入数据到那些列了),那么mysql会使用默认的值进行自增;
  3. 插入语句中列和值的顺序应与表定义的列的顺序相匹配,并且在 VALUES 关键字后面的括号中提供对应的值。如果某些列允许为空,可以使用 NULL 来表示。

2、多行数据 + 指定列插入

直接进行代码展示。具体如下:

  • 插入两条记录,value_list 数量必须和指定列数量及顺序一致

3、插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败:

遇到上述这样的情况我们可以选择性的进行同步更新操作 语法:

 

INSERT ... ON DUPLICATE KEY UPDATE
    column = value [, column = value] ...

代码样例:

  • 当我们插入数据后,后面往往会显示一行:x row affected


4、直接替换

它的作用很简单,具体如下:

  1. -- 主键 或者 唯一键 没有冲突,则直接插入;
  2. -- 主键 或者 唯一键 如果冲突,则删除后再插入

代码展示:


(二)Retrieve操作

Retrieve操作用于从数据库中检索数据。在MySQL中,常用的Retrieve操作是使用 select 语句进行查询。

语法:

SELECT
    [DISTINCT] {* | {column [, column] ...}
    [FROM table_name]
    [WHERE ...]
    [ORDER BY column [ASC | DESC], ...]
    LIMIT ...

案例:

  • 接下来,我们进行插入操作:


1、SELECT 列

1️⃣全列查询

要对 MySQL 中的表进行全列查询,可以使用 SELECT * 语句。这将返回表中的所有列和所有行的数据。

  • 接下来对上述表中进行查询操作:

【说明】

通常情况下不建议使用 * 进行全列查询:

  • 1. 查询的列越多,意味着需要传输的数据量越大;
  • 2. 可能会影响到索引的使用。(索引待后面课程讲解)

2️⃣指定列查询

 

因此,基于全列查询的缺点,最好明确指定需要的列来减少数据传输和查询时间。

  • 如果只需要查询表中的特定列,可以将 * 替换为所需列的名称,如 SELECT 列1, 列2, ... FROM 表名;
  • 这样可以提高查询效率并减少不必要的数据传输。

3️⃣查询字段为表达式

在 MySQL 中,您可以使用表达式来查询字段。表达式可以是数学运算、字符串操作、逻辑运算或函数调用等。

  • ①表达式不包含字段

  • ②表达式包含一个字段

  • ③表达式包含多个字段

4️⃣为查询结果指定别名

在 MySQL 中,您可以使用 as(可写可不写)关键字为查询结果指定别名。

语法:

SELECT column [AS] alias_name [...] FROM table_name;
  • 以下是一个示例(对上述三科成绩去别名):

  • 又或者如下这样:

5️⃣结果去重


2、WHERE 条件

在 MySQL 中,使用 WHERE 关键字可以添加条件来过滤查询结果。WHERE 子句允许您指定一个或多个条件,只返回满足这些条件的行。

比较运算符:

 

逻辑运算符:

 

案例

  • ① 英语不及格的同学及英语成绩 ( < 60 )


  • ②语文成绩在 [80, 90] 分的同学及语文成绩

其次,除了上述用 and 之外,还可以使用【between and】表达式:


  • ③ 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

除了上述方式之外,还可以使用 IN 条件:


  • 姓孙的同学 及 孙某同学

除此之外,_ 匹配严格的一个任意字符:


  • ⑤ 语文成绩好于英语成绩的同学


  • ⑥ 总分在 200 分以下的同学


  • ⑦ 语文成绩 > 80 并且不姓孙的同学


  • ⑧ 孙某同学,否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80


  • ⑨ NULL 的查询


3、结果排序

在 MySQL 中,您可以使用 ORDER BY 子句对查询结果进行排序。ORDER BY 子句允许您指定一个或多个列作为排序的依据。

语法:

-- ASC 为升序(从小到大)
-- DESC 为降序(从大到小)
-- 默认为 ASC
    SELECT ... FROM table_name [WHERE ...]
        ORDER BY column [ASC|DESC], [...];
  • 注意:没有 ORDER BY 子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序

【案例】

  • ① 同学及数学成绩,按数学成绩升序显示


  • ② 同学及 qq 号,按 qq 号排序显示


  • ③ 查询同学各门成绩,依次按 数学降序,英语升序,语文升序的方式显示


  • ④ 查询同学及总分,由高到低

  • 除了上述的方法之外,ORDER BY 子句中也可以使用列别名


  • ⑤ 查询姓孙的同学或者姓曹的同学数学成绩,结果按数学成绩由高到低显示


4、筛选分页结果

在 MySQL 中,您可以使用 LIMIT OFFSET 关键字来实现对分页结果的筛选。

  1. LIMIT 用于限制查询结果的返回行数;
  2. OFFSET 则用于指定从哪一行开始返回结果。

语法:

-- 起始下标为 0

-- 从 s 开始,筛选 n 条结果

  • SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 0 开始,筛选 n 条结果

  • SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用

  • SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议:

  • 对未知表进行查询时,最好加一条 LIMIT 1,避免因为表中数据过大,查询全表数据导致数据库卡死

按 id 进行分页,每页 3 条记录,分别显示 第 1、2、3 页


(三)Update操作

在 MySQL 中,您可以使用 UPDATE 语句来修改数据库表中的数据。UPDATE 语句用于对表中的一行或多行进行更新操作。

语法:

UPDATE table_name SET column = expr [, column = expr ...]
    [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • 对查询到的结果进行列值更新

案例:

  • ① 将孙悟空同学的数学成绩变更为 80 分


  • ② 将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分


  • ③ 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分


  • ④ 将所有同学的语文成绩更新为原来的 2 倍

  • 注意:更新全表的语句慎用!!!

(四)Delete操作

1、删除数据

语法:

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

案例:

  • ① 删除孙悟空同学的考试成绩

 


  • ② 删除整张表数据

紧接着,我们在插入数据:

 


2、截断表

在 MySQL 中,您可以使用 TRUNCATE TABLE 语句来截断(清空)数据库表中的数据。该操作会从表中删除所有的行,并释放相关的存储空间。

语法:

TRUNCATE [TABLE] table_name

注意:这个操作慎用

  • 1. 只能对整表操作,不能像 DELETE 一样针对部分数据操作;
  • 2. 实际上 MySQL 不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事物,所以无法回滚
  • 3. 会重置 AUTO_INCREMENT 项

准备测试数据:

 

 

 


(五)插入查询结果

在 MySQL 中,您可以使用 INSERT INTO ... SELECT 语句将查询结果插入到表中。这样可以方便地将一个表的查询结果作为数据,插入到另一个表中。

语法:

INSERT INTO table_name [(column [, column ...])] SELECT ...

案例:删除表中的的重复复记录,重复的数据只能有一份

  • 1、实现数据准备

思路:

  • ① 创建一张空表 no_duplicate_table,结构和 duplicate_table 一样

 

  • ② 将 duplicate_table 的去重数据插入到 no_duplicate_table

 

  • ③ 通过重命名表,实现原子的去重操作

 


(六)聚合函数

聚合函数是用于对数据进行计算和统计的函数,它们可以根据一组值返回一个单一的结果。在 MySQL 中,常见的聚合函数包括以下几个:

 

案例:

  • ① 统计班级共有多少同学

 

  • ② 统计班级收集的 qq 号有多少

 

 

  • ③ 统计本次考试的数学成绩分数个数

 


  • ④ 统计数学成绩总分

 


  • ⑤ 统计平均总分

 


  • ⑥ 返回英语最高分

 


  • ⑦ 返回 > 70 分以上的数学最低分

 


(七)group by子句的使用

GROUP BY 子句是用于对结果集进行分组的一种 SQL 语句。通过 GROUP BY 子句,您可以将查询结果按照指定的列进行分组,并对每个分组应用聚合函数进行计算。

以下是 GROUP BY 子句的使用示例:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
  • 在上述示例中,column1 是需要分组的列名,aggregate_function(column2) 是要应用的聚合函数,table_name 是表名。

例如,假设有一个名为 "orders" 的表,包含列 "customer_id"、"product_id" 和 "quantity",我们想要按照 "product_id" 对订单进行分组,并计算每个产品的总销量:

SELECT product_id, SUM(quantity)
FROM orders
GROUP BY product_id;
  • 上述示例中,使用了 SELECT product_id, SUM(quantity) FROM orders GROUP BY product_id 语句,按照 "product_id" 列对 "orders" 表进行分组,并对每个分组中的 "quantity" 列应用 SUM 聚合函数,计算每个产品的总销量。

除了单个列之外,GROUP BY 子句还可以指定多个列进行分组,例如:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

总结

以上便是关于 mysql 中常见的数据查询操作。接下来,简单的总结一下本文内容!!!

MySQL 数据的基本查询可以通过使用 SELECT 语句和其他相关子句来实现。

  • 查询所有列:
SELECT * FROM table_name;

这将返回指定表中的所有列。

  • 查询特定列:
SELECT column1, column2, ... FROM table_name;

您可以指定要查询的列名,以逗号分隔,从而只返回这些列的数据。

  • 带条件的查询:
SELECT * FROM table_name WHERE condition;

使用 WHERE 子句可以添加条件,过滤满足条件的行。例如,WHERE age > 18 将会返回年龄大于 18 岁的行。

  • 使用聚合函数:
SELECT aggregate_function(column) FROM table_name;

常见的聚合函数包括 COUNT、SUM、AVG、MAX 和 MIN。您可以对列应用聚合函数,返回计算结果。

  • 排序查询结果:
SELECT * FROM table_name ORDER BY column ASC|DESC;

使用 ORDER BY 子句可以按照指定列进行升序(ASC)或降序(DESC)排序。

  • 去重查询结果:
SELECT DISTINCT column FROM table_name;

使用 DISTINCT 关键字可以去除查询结果中的重复行,只返回唯一的值。

  • 分组查询:
SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

使用 GROUP BY 子句可以对结果进行分组,并对每个分组应用聚合函数。

到此,关于本篇数据查询的讲解便到此结束了。感谢大家的观看与支持!!!

 

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
关系型数据库 MySQL 数据库
数据安全之路:深入了解MySQL的行锁与表锁机制
数据安全之路:深入了解MySQL的行锁与表锁机制
23 1
|
4天前
|
运维 DataWorks 关系型数据库
DataWorks产品使用合集之DataWorks还有就是对于mysql中的表已经存在数据了,第一次全量后面增量同步的步骤如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
37 2
|
4天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
2天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用合集之mysql通过flink cdc同步数据,有没有办法所有表共用一个dump线程
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
7 0
|
4天前
|
SQL 关系型数据库 MySQL
MySQL 基本概念 基础用法 增删改查(特殊查询)语法 详细篇
MySQL 基本概念 基础用法 增删改查(特殊查询)语法 详细篇
|
4天前
|
SQL 存储 关系型数据库
【MySQL】DDL的表操作详解:创建&查询&修改&删除
【MySQL】DDL的表操作详解:创建&查询&修改&删除
|
4天前
|
存储 SQL 关系型数据库
mysql查询数据库表大小怎么操作
mysql查询数据库表大小怎么操作
|
4天前
|
存储 SQL 关系型数据库
MySQL表的增删改查---多表查询和联合查询
MySQL表的增删改查---多表查询和联合查询
|
4天前
|
存储 关系型数据库 MySQL
{MySQL} 数据库约束& 表的关系& 新增&&删除& 修改& 查询
{MySQL} 数据库约束& 表的关系& 新增&&删除& 修改& 查询
21 0
|
4天前
|
JavaScript 前端开发 API
node+vue3+mysql前后分离开发范式——实现对数据库表的增删改查
node+vue3+mysql前后分离开发范式——实现对数据库表的增删改查
38 1

推荐镜像

更多