[MySQL初阶]MySQL（4）基本查询

标题：[MySQL初阶]MySQL（4）基本查询
@水墨不写bug

本文讲解MySQL数据库的表的基本查询操作（CURD操作）：CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除）
在这里，我将首先创建一个数据表，然后对这个表一边操作，一边讲解。

一. 数据表设计

首先，我们设计一个用户管理系统，以它内部的 users 表为例，表结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '名字',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    password_hash CHAR(60) NOT NULL COMMENT '密码',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建账户时间',
    last_login DATETIME COMMENT '最后一次登录时间',
    is_active BOOLEAN DEFAULT 1 COMMENT '用户是否活跃'
);

二、对数据表的操作

1. Create 操作（插入数据）

插入数据语法：

• 单行数据+全列插入：

一次插入一行数据，每次全列插入

insert into users(id,username,email,password_hash,created_at,last_login,is_active) values (1,'zhangsan','123@qq.com','60个字符',NOW(),NOW(),TRUE);

• 多行数据+指定列插入：

-- 插入多行数据，指定 username、email、password_hash、is_active 列
INSERT INTO users (username, email, password_hash, is_active)
VALUES 
    ('lisi', '000@ex.com', '$2a$10$abc...', 1),  -- 显式指定 is_active
    ('sunwukong', 'wukong@xe.com', '$2a$10$xyz...', 0);

为了方便起见，这里我们假设插入一个较小的数据表。

• 插入冲突是否更新：

insert into tb_name(name,qq) values('zhangsan','123') on duplicate key update name = 'lisi',qq = '234';

插入数据：

尝试插入一条记录，name 为 ‘zhangsan’，qq 为 ‘123’。

冲突处理：

如果插入的数据与表中已有的 主键或唯一键 冲突（例如 name 或 qq 字段有唯一约束），则执行 UPDATE 操作。

将冲突记录的 name 更新为 'lisi'，qq 更新为 '234'。

• 替换

replace into tb_name (name, qq) values ('zhangsan', '123');

插入数据：

尝试插入一条记录，name 为 ‘zhangsan’，qq 为 ‘123’。

冲突处理：

如果插入的数据与表中已有的 主键或唯一键 冲突（例如 name 或 qq 字段有唯一约束），则会 先删除冲突的行，再插入新行。

注意：REPLACE INTO 是 先删除再插入，而不是更新。

查看最近受影响的行数：

select row_count();

可以查看最近受影响的行数。

2. Retrieve 操作（读取数据）

语法：

SELECT column1, column2, ... FROM table_name [WHERE ...];

这是最基本的查询方式，在最基本的语句之间，我们可选择加上一些选项。比较丰富的选项可以如下所示：

select distinct(是否去重) */列名 
		from tb_name 
		where 查询条件 
		order by 列名称 asc/desc(排序条件) 
		limit(限定查询出来的数据的条目数);

（1）基本查询的用法：

• 全列查询：

select * from tb_name;

一般不推荐全列查询会输出整张表的所有内容，对一个数据库，数据量动辄上百万，盲目全列查询会占用网络带宽，并且导致刷屏或者机器死机，一般需要用limit指定查询数据条目数。

• 指定列查询：

select 列名称 from tb_name;

指定某一列内容输出。

• 列间关系运算

查询的时候，可以进行列间运算

select name,math+chinese+english as total from tb_name;

显示出来的是3列成绩的综合。其中as 代表重命名，每一列都可以重命名

select name 姓名,math 数学,english 英语,math + chinese + english 总分 from tb_name;

• 结果去重

select distinct math from tb_name;

如果查询结果有重复，比如有多个人得分95，只显示一次95

（2）where子句详解

MySQL 的 WHERE 子句用于在查询中筛选符合特定条件的记录。它是 SELECT、UPDATE、DELETE 等语句的核心组成部分，select帮助你选择了某一张表，而where则是进一步选择表中符合要求的数据；更直观的来说，where字句类似于if条件判断。

i. 基本语法

SELECT 列名 FROM 表名 WHERE 条件;
UPDATE 表名 SET 列=值 WHERE 条件;
DELETE FROM 表名 WHERE 条件;

ii. 常用运算符

比较运算符

• =,<=>：等于
• 注意："="NULL不安全（无法参与NULL比较），如果想要与NULL值比较，需要用NULL安全的 <=> 例如：NULL <=> NULL 结果为TRUE。

  SELECT * FROM users WHERE age = 25;
  SELECT * FROM users WHERE address <=> NULL;
  

• <> 或 !=：不等于

  SELECT * FROM products WHERE price <>（或者!=） 100;
  

• >、<、>=、<=：关系比较

  SELECT * FROM orders WHERE sum_amount > 1000;
  

• BETWEEN ...AND...：在范围内（闭区间）

  SELECT * FROM employees WHERE salary BETWEEN 300 AND 1000;
  

• IN：匹配列表中的任意值

  SELECT * FROM customers WHERE country IN ('China', 'Canada', 'Mexico');
  

逻辑运算符

• AND：同时满足多个条件

  SELECT * FROM students WHERE age >= 18 AND grade = 'A';
  

• OR：满足任意一个条件

  SELECT * FROM products WHERE category = 'aaa' OR price < 50;
  

• NOT：否定条件

  SELECT * FROM employees WHERE NOT department = 'HR';
  

• 注意优先级：AND 优先级高于 OR，建议用括号明确逻辑：

  SELECT * FROM table 
  WHERE (condition1 OR condition2) AND condition3;
  

模糊匹配（LIKE）

• %：匹配任意多个字符（包括零个）

  SELECT * FROM books WHERE title LIKE 'The%'; -- 以 "The" 开头
  

• _：匹配单个字符

  SELECT * FROM users WHERE username LIKE 'user_'; -- 如 "user1", "userA"
  

一句话总结， % 可以代表一个或者多个字符, _ 只能代表一个字符

NULL 值比较

• IS NULL：检查空值

  SELECT * FROM orders WHERE shipped_date IS NULL;
  

• IS NOT NULL：检查非空值

  SELECT * FROM contacts WHERE phone IS NOT NULL;
  

（3）order by子句详解

MySQL 的 ORDER BY 子句用于对查询结果进行排序，可以按单列、多列或表达式排序，并支持升序（ASC）或降序（DESC）排列。它是优化数据展示和分析的重要工具。

i. 基本语法

SELECT 列名 
FROM 表名 
[WHERE 条件] 
ORDER BY 排序列1 [ASC|DESC], 排序列2 [ASC|DESC], ...;

• 位置：ORDER BY 必须位于 WHERE 子句之后，LIMIT 子句之前。
• 默认排序：如果不指定 ASC 或 DESC，默认为 ASC（升序）。

ii. 核心用法

单列排序

-- 按工资升序排列
SELECT name, salary FROM employees ORDER BY salary;

-- 按入职日期降序排列
SELECT name, hire_date FROM employees ORDER BY hire_time DESC;

多列排序

按优先级依次排序，用逗号分隔：

-- 先按部门升序，再按工资降序
SELECT name, department, salary 
FROM employees 
ORDER BY department ASC, salary DESC;

表示先按照部门排序，部门一样的再按照薪资排序。

（4）limit子句

MySQL 的 LIMIT 子句常与 ORDER BY 结合使用，用于在排序后的结果中筛选出特定范围的数据（如分页查询、获取前 N 条记录）。以下是两者的协同用法和关键细节：

i. 基本语法

SELECT 列名 
FROM 表名 
[WHERE 条件] 
ORDER BY 排序列 [ASC|DESC] 
LIMIT [偏移量,] 行数;

• 执行顺序：
  WHERE → ORDER BY → LIMIT
  （先过滤数据，再排序，最后截取结果）

• 典型场景：

  • 分页查询（如每页 10 条）
  • 获取前 N 名（如销量最高的前 5 个商品）

ii. 核心用法

(1) 获取前 N 条记录

-- 获取工资最高的前 3 名员工
SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 3;

(2) 分页查询

-- 每页 10 条，查询第 3 页（偏移量 = (页码-1)*每页行数）
SELECT * 
FROM products 
ORDER BY price ASC 
LIMIT 20, 10; -- 偏移量为 20 条，取接下来的 10 条

3. Update 操作（更新数据）

对查询到的结果进行列值更新
语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE ...
ORDER BY ... 
LIMIT ...;

实际场景：

1. 修改用户邮箱：

   UPDATE users 
   SET email = 'new@qq.com'
   WHERE id = 1; -- 明确指定条件，避免全表更新！
   

   不指明条件导致全表更新是致命的！

2. 记录用户最后登录时间：

   UPDATE users 
   SET last_login = NOW() 
   WHERE username = 'ddsm';
   

3. 批量禁用长时间未登录用户：

   UPDATE users 
   SET is_active = 0 
   WHERE last_login < '2020-01-01';
   

4. Delete 操作（删除数据）

语法：

DELETE FROM table_name WHERE ... ORDER BY ... LIMIT ...;

实际场景：用户注销账号

-- 物理删除（谨慎操作！）
DELETE FROM users WHERE id = 1;

-- 实际项目中更推荐软删除,如果误删还可以找回数据
UPDATE users SET is_active = 0 WHERE id = 1;

建议：

1. 生产环境优先使用软删除（通过 is_active 或 deleted_at 标记）。
2. 删除前检查关联数据（如用户订单需级联处理，这涉及到外键约束，在以后的讲解中会逐渐详解）。

注意：

• 没有where子句的删除，将删除整张表的数据；但是表的结构不变；此外auto_increment不会归0；
• 删除表内数据的另一种方法是

truncate tb_name；

特点是只能对整张表进行操作，不能对部分数据操作。由于mysql不对数据操作，因而比delete更快。truncate在删除的时候，并不会经过真正的事务，所以无法回滚。此外auto_increment会被重置。

三、总结

• Create：INSERT 实现数据写入，注意唯一性约束。
• Retrieve：SELECT 灵活组合DISTINCT、 WHERE、ORDER BY、LIMIT 满足查询需求。
• Update：UPDATE 配合精确条件，避免误操作。
• Delete：优先软删除，物理删除需事务+备份。

完
转载请注明出处