MySQL增删改查（基础）

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

比较运算符

逻辑运算符：

注意：

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字段，就可以实现删除功能，这条被删除的数据并没有实质上删掉而始终存在于数据库中