【MySQL】表的基本增删查改（结合案例）

2024-06-13 20:28
文章标签 mysql 基本 结合 案例 增删 database 查改

本文主要是介绍【MySQL】表的基本增删查改（结合案例），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

  • 1.前言
  • 2.插入数据（Create）
    • 2.1案例
    • 2.2单行数据+全列插入
    • 2.3多行数据+指定列插入
    • 2.4插入否则更新
    • 2.5替换
  • 3. 读取数据(Retireve)
    • 3.1案例
    • 3.2全列查询
    • 3.3指定列查询
    • 3.4查询字段为表达式
    • 3.5为查询结果起别名
    • 3.6去重
    • 3.7where条件
      • 3.7.1案例
    • 3.8排序
    • 3.9筛选分页
  • 4. 修改数据（Update）
  • 5.删除数据（Delete）
    • 5.1delete删除
    • 5.2截断表
    • 5.3truncate和delete的区别
  • 6.将查询结果当作数据进行多行插入
  • 7.聚合函数
  • 8.分析基本查询的执行顺序
  • 9.分组查询
  • 10.SQL查询的顺序

1.前言

在前面我们学习了针对库的操作以及针对表结构的操作，下面我们来学习针对表中数据的操作。对数据操作的类型无非就是C(创建)R（读取）U（修改）D（删除）,针对以上四种类型的操作，结合案例展开讨论

2.插入数据（Create）

语法：

insert表插入列
values数据

2.1案例

现创建一张学生表，表结构如下：

CREATE TABLE students (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,sn INT NOT NULL UNIQUE COMMENT '学号',name VARCHAR(20) NOT NULL,qq VARCHAR(20)
);

在这里插入图片描述

2.2单行数据+全列插入

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2.3多行数据+指定列插入

在这里插入图片描述

2.4插入否则更新

如果我们再插入数据时遇到了主键或者唯一键冲突，我们选择性的进行同步操作，例如：
在这里插入图片描述

在这里插入图片描述
使用ON DUPLICATE KEY UPDATE可以在发生冲突之后执行update语句。

2.5替换

主键或者唯一键发生冲突，删除再插入
没有发生冲突就直接插入
在这里插入图片描述

3. 读取数据(Retireve)

读取数据其实就是查询数据，查询数据使用select语句
语法：

select {cloumn1,cloumn2..} from {table1,table2...} [where...] [order by...] [goup by...] [limit...]

3.1案例

创建一个exam_result表记录学生的考试成绩:

CREATE TABLE exam_result ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL COMMENT '同学姓名', chinese float DEFAULT 0.0 COMMENT '语文成绩', math float DEFAULT 0.0 COMMENT '数学成绩', english float DEFAULT 0.0 COMMENT '英语成绩' 
);

给出一组数据用于测试：

INSERT INTO exam_result (name, chinese, math, english) VALUES ('唐三藏', 67, 98, 56), ('孙悟空', 87, 78, 77), ('猪悟能', 88, 98, 90), ('曹孟德', 82, 84, 67), ('刘玄德', 55, 85, 45), ('孙权', 70, 73, 78), ('宋公明', 75, 65, 30);//多行全列插入

3.2全列查询

在这里插入图片描述

3.3指定列查询

假设要查看所有同学的数学成绩以及姓名
在这里插入图片描述

3.4查询字段为表达式

假设要查询所有同学的总成绩
在这里插入图片描述

3.5为查询结果起别名

使用as可以给一个表达式或者是一张表或者一个字段起别名，例如还是查询所有同学的总成绩：
在这里插入图片描述
其中as也可以省略不写

3.6去重

可以在查询字段前添加distinct表示对该字段去重，例如查询所有同学的数学成绩：
去重前：
在这里插入图片描述
去重后
在这里插入图片描述
这样就没有重复的数学成绩了。

3.7where条件

使用where可以保证查询数据的准确性。
下面给出常见的比较运算符
在这里插入图片描述
逻辑运算符
在这里插入图片描述
下面结合具体案例来使用where

3.7.1案例

  • 案例一：查询英语成绩不及格的同学及其英语成绩
    在这里插入图片描述
  • 案例二：语文成绩在 [80, 90] 分的同学及语文成绩
    在这里插入图片描述
    在这里插入图片描述
  • 案例三：数学成绩是 98 或者 78的 同学及数学成绩
    在这里插入图片描述
  • 案例四：查询姓孙的同学的成绩
    在这里插入图片描述
  • 案例五：查询名字姓孙某（两个字）同学的成绩
    在这里插入图片描述
  • 案例六：在students表中查询qq为NULL的同学的语文成绩
    在这里插入图片描述

3.8排序

语法：

select 聚合函数，列（要求出现在group by的后面）
from 表
where 筛选条件
order by cloumn asc;//升序cloumn desc;//降序
  • 案例1：查询所有同学的数学成绩并按照升序显示
    在这里插入图片描述
  • 案例2：查询所有同学的总成绩并安装降序排序
    在这里插入图片描述

3.9筛选分页

语法：

-- 起始下标为 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;

比如查询所有同学的总成绩，显示分数最高的三个人
在这里插入图片描述
也可以使用offset:
在这里插入图片描述

4. 修改数据（Update）

语法：

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • 案例：将孙某同学的成绩改为60分
    在这里插入图片描述

5.删除数据（Delete）

5.1delete删除

delete可以删除表中的部分数据，也可以删除全部数据。delete并不会影响表的结构。
语法：

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

  • 案例1：删除孙悟空同学的考试成绩
    查看原数据：
    在这里插入图片描述
    删除后：
    在这里插入图片描述

  • 案例2：删除整张表的数据
    delete table table_name表示删除table_name表的数据，这里不做演示。

5.2截断表

截断truncate和delete都是对表中数据进行删除，truncate只能用来删除表中的所有数据。
语法：

TRUNCATE TABLE table_name;

5.3truncate和delete的区别

  • 作用范围：truncate只能作用于整个表，不能带条件，而delete可以删除表中部分数据且可以带条件
  • 速度和性能：truncate是批量删除，delete是逐行删除。truncate删除数据之后并不会留下日志记录，而delete会，所以truncate的速度较快。
  • 重置表的索引：除了删除数据之外，truncate还会重置表中的索引值，比如自增键（auto_incream）又变为了1。
    总结：如果希望删除表中所有数据其希望操作更快，用truncate.如果希望删除操作能回滚即恢复数据，或者只是删除部分数据，那就用delete

6.将查询结果当作数据进行多行插入

语法：

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

mysql支持将select查询到的结果作为数据对某一张表进行多行插入，比如当我们想复制一个表中的数据到另一个表里。
现给出原始表数据：

-- 创建原数据表 CREATE TABLE duplicate_table (id int, name varchar(20)); 
Query OK, 0 rows affected (0.01 sec) -- 插入测试数据 
INSERT INTO duplicate_table VALUES (100, 'aaa'), (100, 'aaa'), (200, 'bbb'), (200, 'bbb'), (200, 'bbb'), (300, 'ccc');

在这里插入图片描述

  • 案例：删除duplicate_table表中的重复数据
    思路：创建一张空表，表结构和duplicate_table保持一致，然后将duplicate_table去重查询到的数据插入到新表中，再将新表重命名为duplicate_table
    其中复制一个表的结构我们可以使用like,例如：
    在这里插入图片描述

将duplicate_table去重查询到的数据插入到新表中：
在这里插入图片描述
重命名表名
在这里插入图片描述
在这里插入图片描述

7.聚合函数

MySQL中的聚合函数主要用于对一组数据进行计算，返回单个值。常见的聚合函数包括SUM（求和）、AVG（平均值）、MAX（最大值）、MIN（最小值）、COUNT（计数）等。

在这里插入图片描述

  • 案例：统计班级有多少学生
    在这里插入图片描述
  • 案例：统计数学成绩总分
    在这里插入图片描述
  • 案例：统计数学的平均成绩
    在这里插入图片描述
  • 案例：返回数学成绩做高分
    在这里插入图片描述

8.分析基本查询的执行顺序

比如以下语句：
在这里插入图片描述

执行的顺序为：

  1. from exam_result
  2. where math>60
  3. select name,math
  4. order by math asc

9.分组查询

在select中使用group by 子句可以对指定列进行分组查询
语法：

select 聚合函数，列（要求出现在group by的后面）
from 表
where 筛选条件
group by 分组的列表
having条件查询
order by 排序
limit分页

其中having表示对分组结果进行条件筛选，执行顺序在group by之后。

下面有一张员工表EMP，字段包括员工工资sal、部门号deptno，岗位job。具体内容如下：
在这里插入图片描述

  • 案例1：显示每个部门的平均工资和最高工资
    思路：按照部门号进行分组，于是我们得到了诺干个子表，子表属于同一部门的。再聚合统计每个子表的平均工资的最高工资好平均工资。
    sql语句：
select deptno,max(sal),avg(sal) from emp group by deptno;

在这里插入图片描述

  • 案例2：显示每个部门的每种岗位的平均工资和最高工资
    思路：先按部门号分组，再按岗位分组，再聚合统计每个组的平均工资和最高工资
    sql语句：
select deptno,max(sal),avg(sal) from emp goup by deptno,joib;

在这里插入图片描述

  • 案例3：统计平均工资低于2000的部门和它的平均工资
    思路：先按部门分组，再对每个组求平均工资，最后使用having 对分组结果进行查询，即查询部门平均工资低于2000的
    sql语句：
select avg(sal) avg_sal from emp group by deptno having avg(sal)<2000;

在这里插入图片描述

10.SQL查询的顺序

from > where > group by > having> select > distinct > order by > limit

这篇关于【MySQL】表的基本增删查改（结合案例）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1058342。

相关文章

mapstruct中的@Mapper注解的基本用法

mapstruct中的@Mapper注解的基本用法

《mapstruct中的@Mapper注解的基本用法》在MapStruct中,@Mapper注解是核心注解之一,用于标记一个接口或抽象类为MapStruct的映射器（Mapper）,本文给大家介绍ma... 目录1. 基本用法2. 常用属性3. 高级用法4. 注意事项5. 总结6. 编译异常处理在MapSt
阅读更多...
canal实现mysql数据同步的详细过程

canal实现mysql数据同步的详细过程

《canal实现mysql数据同步的详细过程》：本文主要介绍canal实现mysql数据同步的详细过程,本文通过实例图文相结合给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的... 目录1、canal下载2、mysql同步用户创建和授权3、canal admin安装和启动4、canal
阅读更多...
SQL中JOIN操作的条件使用总结与实践

SQL中JOIN操作的条件使用总结与实践

《SQL中JOIN操作的条件使用总结与实践》在SQL查询中,JOIN操作是多表关联的核心工具,本文将从原理,场景和最佳实践三个方面总结JOIN条件的使用规则,希望可以帮助开发者精准控制查询逻辑... 目录一、ON与WHERE的本质区别二、场景化条件使用规则三、最佳实践建议1.优先使用ON条件2.WHERE用
阅读更多...
MySQL存储过程之循环遍历查询的结果集详解

MySQL存储过程之循环遍历查询的结果集详解

《MySQL存储过程之循环遍历查询的结果集详解》：本文主要介绍MySQL存储过程之循环遍历查询的结果集,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言1. 表结构2. 存储过程3. 关于存储过程的SQL补充总结前言近来碰到这样一个问题：在生产上导入的数据发现
阅读更多...
MySQL 衍生表(Derived Tables)的使用

MySQL 衍生表(Derived Tables)的使用

《MySQL衍生表(DerivedTables)的使用》本文主要介绍了MySQL衍生表(DerivedTables)的使用,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学... 目录一、衍生表简介1.1 衍生表基本用法1.2 自定义列名1.3 衍生表的局限在SQL的查询语句select
阅读更多...
MySQL 横向衍生表(Lateral Derived Tables)的实现

MySQL 横向衍生表(Lateral Derived Tables)的实现

《MySQL横向衍生表(LateralDerivedTables)的实现》横向衍生表适用于在需要通过子查询获取中间结果集的场景,相对于普通衍生表,横向衍生表可以引用在其之前出现过的表名,本文就来... 目录一、横向衍生表用法示例1.1 用法示例1.2 使用建议前面我们介绍过mysql中的衍生表（From子句
阅读更多...
MyBatis ResultMap 的基本用法示例详解

MyBatis ResultMap 的基本用法示例详解

《MyBatisResultMap的基本用法示例详解》在MyBatis中,resultMap用于定义数据库查询结果到Java对象属性的映射关系,本文给大家介绍MyBatisResultMap的基本... 目录MyBATis 中的 resultMap1. resultMap 的基本语法2. 简单的 resul
阅读更多...
六个案例搞懂mysql间隙锁

六个案例搞懂mysql间隙锁

《六个案例搞懂mysql间隙锁》MySQL中的间隙是指索引中两个索引键之间的空间,间隙锁用于防止范围查询期间的幻读,本文主要介绍了六个案例搞懂mysql间隙锁,具有一定的参考价值,感兴趣的可以了解一下... 目录概念解释间隙锁详解间隙锁触发条件间隙锁加锁规则案例演示案例一：唯一索引等值锁定存在的数据案例二：
阅读更多...
MySQL JSON 查询中的对象与数组技巧及查询示例

MySQL JSON 查询中的对象与数组技巧及查询示例

《MySQLJSON查询中的对象与数组技巧及查询示例》MySQL中JSON对象和JSON数组查询的详细介绍及带有WHERE条件的查询示例,本文给大家介绍的非常详细,mysqljson查询示例相关知... 目录jsON 对象查询1. JSON_CONTAINS2. JSON_EXTRACT3. JSON_TA
阅读更多...
MySQL 设置AUTO_INCREMENT 无效的问题解决

MySQL 设置AUTO_INCREMENT 无效的问题解决

《MySQL设置AUTO_INCREMENT无效的问题解决》本文主要介绍了MySQL设置AUTO_INCREMENT无效的问题解决,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参... 目录快速设置mysql的auto_increment参数一、修改 AUTO_INCREMENT 的值。
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2