当前位置： 首页 > news >正文

MySQL增删改查（基础）

news 来源：原创 2024/9/21 15:35:08

1、. 新增（Create）

语法：

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

例子：

-- 创建一张学生表
DROP TABLE IF EXISTS student;
CREATE TABLE student (id INT,sn INT comment '学号',name VARCHAR(20) comment '姓名',qq_mail VARCHAR(20) comment 'QQ邮箱'
);

2.1 单行数据 + 全列插入

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

2.2 多行数据 + 指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
INSERT INTO student (id, sn, name) VALUES(102, 20001, '曹孟德'),(103, 20002, '孙仲谋');

一次插入一条数据和一次插入多条数据哪个效率高？

 一次插入多条数据的效率通常高于一次插入一条数据。这是因为：

  1. 减少网络开销：执行一次多条插入操作可以减少与数据库之间的通信次数，从而降低网络延迟。

  2. 批处理优化：许多数据库系统在处理批量插入时能够进行优化，例如通过使用事务、批处理和缓存机制，这样可以提高插入速率。

  3. 减少锁竞争：一次性插入多条数据能够减少对表的锁定时间，从而降低因并发插入导致的锁竞争。

  4. 更高的吞吐量：批量插入能利用数据库的内部机制来提高数据写入的吞吐量。

 2、 查询（Retrieve）

语法：

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

例子：

-- 创建考试成绩表
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (id INT,name VARCHAR(20),chinese DECIMAL(3,1),math DECIMAL(3,1),english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES(1,'唐三藏', 67, 98, 56),(2,'孙悟空', 87.5, 78, 77),(3,'猪悟能', 88, 98.5, 90),(4,'曹孟德', 82, 84, 67),(5,'刘玄德', 55.5, 85, 45),(6,'孙权', 70, 73, 78.5),(7,'宋公明', 75, 65, 30);

2.1 全列查询

-- 通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。（索引待后面课程讲解）
SELECT * FROM exam_result;

2.2 指定列查询

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english FROM exam_result;

2.3 查询字段为表达式

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;
-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;
-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

2.4 别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称，语法：

SELECT column [AS] alias_name [...] FROM table_name;
-- 结果集中，表头的列名=别名
SELECT id, name, chinese + math + english 总分 FROM exam_result;

2.5 去重：DISTINCT

使用DISTINCT关键字对某列数据进行去重：

select distinct math from exam_result;

2.6 排序：ORDER BY

语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

-- 查询同学姓名和 qq_mail，按 qq_mail 排序显示
SELECT name, qq_mail FROM student ORDER BY qq_mail;
SELECT name, qq_mail FROM student  ORDER BY qq_mail DESC;

3. 使用表达式及别名排序

-- 查询同学及总分，由高到低
SELECT name, chinese + english + math FROM exam_result ORDER BY chinese + english + math DESC;
SELECT name, chinese + english + math  as total FROM exam_result ORDER BY total DESC;

4. 可以对多个字段进行排序，排序优先级随书写顺序

-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;

2.7 条件查询：WHERE

比较运算符

运算符说明
>, >=, <, <=大于，大于等于，小于，小于等于
=等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>等于，NULL 安全，例如 NULL NULL 的结果是 TRUE(1)
!=, <>不等于
BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, ...)如果是 option 中的任意一个，返回 TRUE(1)
IS NULL是 NULL
IS NOT NULL不是 NULL
LIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字 符

逻辑运算符：

运算符说明
AND多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT条件为 TRUE(1)，结果为 FALSE(0)

注意：

1. WHERE条件可以使用表达式，但不能使用别名。

在 MySQL 中，SQL 语句的执行顺序通常遵循以下步骤。这一顺序对于 SELECT 语句尤为重要，因为它影响到最终查询结果的返回。

MySQL 语句的执行顺序

  1. FROM：确定数据源。从指定的表中选择数据。
  2. JOIN：如果有多张表参与查询，则进行连接操作（如 INNER JOIN、LEFT JOIN 等）。
  3. WHERE：应用条件过滤，从中筛选出符合条件的记录。
  4. GROUP BY：对结果集进行分组，根据指定的列进行聚合。
  5. HAVING：对分组后的结果进行过滤，通常与聚合函数一起使用。
  6. SELECT：选择需要的列或表达式。
  7. DISTINCT：去除重复记录。
  8. ORDER BY：对最终结果集进行排序。
  9. LIMIT：限制返回的结果集数量。

2. AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

基本查询：

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
-- 观察AND 和 OR 的优先级：
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

AND与OR：

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
-- 观察AND 和 OR 的优先级：
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

范围查询:

1. BETWEEN ... AND ...

-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese 
<=90;

2. IN

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
-- 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math 
= 98 OR math = 99;

模糊查询：LIKE

-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权
-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权

NULL 的查询：IS [NOT] NULL

-- 查询 qq_mail 已知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;
-- 查询 qq_mail 未知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

2.8 分页查询：LIMIT

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

 LIMIT 的基本用法

只使用 LIMIT：限制返回的行数。

SELECT * FROM table_name
LIMIT 10;  -- 返回前 10 行

使用 OFFSET 和 LIMIT：指定从哪一行开始返回记录。

SELECT * FROM table_name
LIMIT 5 OFFSET 10;  -- 从第 11 行开始返回 5 行数据

或者，使用 LIMIT 后跟两个参数：

sql
SELECT * FROM table_name
LIMIT 10, 5;  -- 从第 11 行开始，返回 5 行数据（第一个数字是偏移量，第二个数字是返回的行数）

分页查询示例

假设有一个名为 employees 的表，你想要每页显示 10 条记录，并根据页码进行分页：

第一页（页码 1）：

SELECT * FROM employees
LIMIT 0, 10;  -- 返回第 1 到第 10 条记录

第二页（页码 2）：

SELECT * FROM employees
LIMIT 10, 10;  -- 返回第 11 到第 20 条记录

第三页（页码 3）

SELECT * FROM employees
LIMIT 20, 10;  -- 返回第 21 到第 30 条记录

3、修改（Update）

语法：

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]

案例：

-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT
3;
-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

4、 删除（Delete）

语法：

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

案例：

-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';
-- 删除整张表数据
-- 准备测试表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (id INT,name VARCHAR(20)
);
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
-- 删除整表数据
DELETE FROM for_delete;

delete时 如果不添加where条件会出现什么问题？

如果删除时不加条件限制，那么整张表的数据都会被删除掉（清空）

如果想要恢复 在mysql中执行每一条SQL都会被记录到日志中，把日志中记录的操作，再执行一遍基本上就可以完成恢复。

在生产环境中一般不使用 delect操作

一般在表中会添加一个delectState字段，用来表示这条记录是否删除，0表示正常(没有删除） 1表示删除

用update操作去更新deleteState字段，就可以实现删除功能，这条被删除的数据并没有实质上删掉而始终存在于数据库中

相关文章：

  • 北京网站建设多少钱？
  • 辽宁网页制作哪家好_网站建设
  • 高端品牌网站建设_汉中网站制作
  • Java入门基础17：集合框架2（可变参数、Collections、Map系列集合、集合的嵌套、Stream流）
  • 不知道msvcp140.dll丢失的解决方法有哪些？看这篇文章教你修复丢失的msvcp140.dll
  • 8月9日笔记
  • Leetcode 17.电话号码的字母组合
  • SpringBoot自动装配原理
  • 探索 Go 语言的 json 库
  • 1Panel应用推荐：KubePi开源Kubernetes管理面板
  • 【运维项目经历|040】高可用Web服务平台：LVS+Apache集群+NFS共享存储系统
  • C 循环
  • GNU/Linux - memtool使用
  • 【YOLOV8】YOLOV8模型训练train及参数详解
  • 12322222222
  • 零基础5分钟上手亚马逊云科技AWS核心云架构知识-用S3桶托管静态网页
  • 2940 找到Alice和Bob可以相遇的建筑 (944/951)超时
  • Delphi 利用LiveBindings绑定JSON数据到列表控件
  • Angular 响应式表单之下拉框
  • Apache的基本使用
  • CSS盒模型深入
  • jQuery(一)
  • magento2项目上线注意事项
  • React-redux的原理以及使用
  • Spring Cloud Alibaba迁移指南（一）：一行代码从 Hystrix 迁移到 Sentinel
  • webpack4 一点通
  • 多线程事务回滚
  • 构建二叉树进行数值数组的去重及优化
  • 扑朔迷离的属性和特性【彻底弄清】
  • 前端js -- this指向总结。
  • 如何选择开源的机器学习框架？
  • 通过获取异步加载JS文件进度实现一个canvas环形loading图
  • 为物联网而生：高性能时间序列数据库HiTSDB商业化首发！
  • 一、python与pycharm的安装
  • 源码安装memcached和php memcache扩展
  • PostgreSQL之连接数修改
  • Salesforce和SAP Netweaver里数据库表的元数据设计
  • ​configparser --- 配置文件解析器​
  • #162 (Div. 2)
  • $jQuery 重写Alert样式方法
  • (4) PIVOT 和 UPIVOT 的使用
  • （LeetCode C++）盛最多水的容器
  • （草履虫都可以看懂的）PyQt子窗口向主窗口传递参数，主窗口接收子窗口信号、参数。
  • （附源码）小程序 交通违法举报系统 毕业设计 242045
  • （论文阅读22/100）Learning a Deep Compact Image Representation for Visual Tracking
  • （篇九）MySQL常用内置函数
  • (转)Google的Objective-C编码规范
  • (转)scrum常见工具列表
  • （转）visual stdio 书签功能介绍
  • ***原理与防范
  • ./indexer: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object fil
  • .apk文件，IIS不支持下载解决
  • .NET/C# 使窗口永不激活（No Activate 永不获得焦点）
  • .NET/C# 推荐一个我设计的缓存类型（适合缓存反射等耗性能的操作，附用法）
  • .NET开发者必备的11款免费工具
  • .NET命名规范和开发约定
  • .pub是什么文件_Rust 模块和文件 - 「译」
  • @GlobalLock注解作用与原理解析