Mysql之DMQL

数据操作与查询语言（DMQL, Data Manipulation&Query Language）

核心功能​：对数据库中的数据进行增删改查操作。

INSERT：插入新数据

  -- 准备表结构
  CREATE TABLE users (
	id BIGINT PRIMARY KEY auto_increment,
	NAME VARCHAR (50),
	age INT (3),
    city_id bigint,
	email VARCHAR (90)
  );

  INSERT INTO users (name) VALUES ('Alice');  -- 插入用户数据
  INSERT INTO users (name) VALUES ('bobi'),('danny');  -- 插入多行用户数据
  INSERT INTO users(`name`,`age`,`email`) VALUES ('zhangsan', 18, 'zhangsan@163.com'),('lisi', 19, 'lisi@qq.com'); 

UPDATE：修改现有数据

  UPDATE users SET name = 'Bob', age = 16 WHERE id = 1;  -- 根据用户ID更新用户名称与年龄
  UPDATE users SET email = 'bob@163.com' WHERE name = 'Bob' and age = 16;  -- 根据用户名称与年龄更新用户邮箱

​DELETE：删除数据

  DELETE FROM users WHERE id = 1;  -- 删除用户ID为1的记录
  DELETE FROM users; -- 全表删除，慎用，会记录日志文件，理论可恢复，自增ID会保留，下次新增继续增加，不会释放存储空间，删得慢，性能低
  truncate table user; -- 全表截断，慎用，不可逆，自增ID恢复初始值，此表的数据与日志都清空了，立即释放存储空间，删得快，性能高‌

SELECT：用于检索数据

语法格式：

  SELECT [DISTINCT] 列名/表达式 
  FROM 表名 
  [WHERE 条件] 
  [GROUP BY 分组列] 
  [HAVING 分组条件] 
  [ORDER BY 排序列] 
  [LIMIT 行数];

• ​SELECT​：指定查询的列或计算字段，使用*表示所有列。

• ​FROM​：指定数据来源的表或视图，支持多表连接。

• ​WHERE​：过滤行级数据，支持逻辑运算符（AND/OR/NOT）和范围查询（BETWEEN、IN）。

• ​GROUP BY​：按列分组，常与聚合函数（如SUM、AVG）配合统计（如 group by age）。

• ​HAVING​：过滤分组后的结果，类似WHERE但作用于组（如 having age >= 18）。

• ​ORDER BY​：结果排序（ASC升序/DESC降序，order by age desc）。

• ​LIMIT​：限制返回行数（如分页查询：limit 0,10 头10条记录）。

  SELECT * FROM users WHERE age > 18;  -- 查询年龄大于18的成年用户记录
  SELECT `name` FROM users WHERE age > 18;  -- 查询年龄大于18的成年用户名称
  SELECT `email` FROM users WHERE name='lisi';  -- 查询lisi的邮箱
  SELECT * FROM users WHERE name age >= 18 and like 'li%';  -- 查询年龄大于18并name为li开头的用户记录，LIKE支持通配符：%（任意字符）、_（单字符）
  SELECT * FROM users WHERE age IN (18, 20, 22); -- IN操作符简化多值匹配
  SELECT * FROM users WHERE age BETWEEN 18 AND 22; -- 查询18到22岁的用户记录，BETWEEN处理范围（数值、日期）
  SELECT DISTINCT age FROM users; -- 查询表中去重后用户所有的年龄，DISTINCT 去重关键字

聚合与分组统计：

常用聚合函数​

​分组与过滤​

  -- 统计行数
  SELECT COUNT(id) FROM users;
  -- 查询用户中最大年龄
  SELECT MAX(age) FROM users WHERE age is not null;
  -- 查询用户中最小年龄
  SELECT MIN(age) FROM users WHERE age is not null;
  - 计算用户总年龄
  SELECT SUM(age) FROM users WHERE age is not null;
  -- 按城市统计平均年龄，仅显示平均年龄>18的城市
  SELECT city_id, AVG(age) AS avg_age
  FROM users
  GROUP BY city_id
  HAVING avg_age> 18;

复杂查询

多表连接（JOIN）​​

连接类型​

自连接与递归查询​

  -- 查询员工的上级经理（同一表）
  SELECT e.name, m.name AS manager 
  FROM employees e 
  LEFT JOIN employees m ON e.manager_id = m.id;

子查询（Subquery）​​

应用场景​

​WHERE子句​：嵌套条件过滤

  SELECT * FROM orders 
  WHERE customer_id IN (SELECT id FROM customers WHERE city = '北京');

​SELECT子句​：动态计算字段

  SELECT name, (SELECT AVG(score) FROM exams) AS class_avg FROM students;

​FROM子句​：作为临时表（派生表）

  SELECT dept, avg_salary 
  FROM (SELECT department AS dept, AVG(salary) AS avg_salary FROM employees GROUP BY department) t;

子查询 vs JOIN​

• ​性能​：JOIN通常更高效，尤其是大数据量时。

• ​可读性​：子查询更直观，适合简单嵌套逻辑。

​结果集操作​

UNION 与 UNION ALL​

  -- 合并查询结果（UNION去重，UNION ALL保留重复）
  SELECT name FROM employees 
  UNION ALL 
  SELECT product_name FROM products;

​分页优化​

  -- 避免LIMIT 100000,20的全表扫描
  SELECT * FROM orders 
  WHERE id > 100000 
  ORDER BY id 
  LIMIT 20;