保姆级 MySQL 查询教程：覆盖所有常用语法与函数
*查询字段：
1.查询出所有商品
SELECT * FROM product;
2.查询出所有商品的pname和price字段
SELECT pname,price FROM product;
3.给查询出的字段添加别名pname->商品名称 ，price->商品价格
SELECT pname 商品名称, price 商品价格 FROM product;
SELECT pname AS 商品名称, price AS 商品价格 FROM product;
4.对查询字段进行计算
SELECT pname 名称,price 原价,price0.9 九折, price0.1 节省 FROM product;
5.cid: 产品分类
查询有哪些产品分类(DISTINCT： 对结果去重)
SELECT DISTINCT cid FROM product;
*1.条件查询
比较查询(=,!=,<,>,>=,<=)
-
查询商品名称等于"花花公子"的商品信息
SELECT * FROM product WHERE pname="花花公子"; -
查询商品名称不等于"花花公子"的商品信息
SELECT * FROM product WHERE pname!="花花公子"; -
查询商品价格大于1000的商品信息
SELECT * FROM product WHERE price > 1000;
*2.逻辑运算(AND, OR, NOT)
-
查询商品分类是c001和c003的商品信息
SELECT * FROM product WHERE cid="c001" OR cid="c003"; -
查询商品价格在[500-1000]的商品信息
SELECT * FROM product WHERE price>=500 AND price<=1000; -
查询商品名称不等于"花花公子"的商品信息
SELECT * FROM product WHERE NOT(pname="花花公子"); -
查询商品分类是c001和c003的商品且价格小于1000的信息
SELECT * FROM product WHERE (cid="c001" OR cid="c003") AND price<1000;
*3.范围查询（[NOT] IN, [NOT] BETWEEN...AND...）
-
查询商品分类是c001和c003的商品信息
SELECT * FROM product WHERE cid IN ("c001","c003");
NOT IN
SELECT * FROM product WHERE cid NOT IN ("c001","c003"); -
查询商品价格在[500-1000]的商品信息
SELECT * FROM product WHERE price BETWEEN 500 AND 1000;
NOT BETWEEN AND
SELECT * FROM product WHERE price NOT BETWEEN 500 AND 1000;
*4.文本模糊查询（LIKE）
-
查询商品名以"本草"结束的商品信息(%->匹配任意个字符)
SELECT * FROM product WHERE pname LIKE "%本草"; -
查询商品名"xx公子"的商品信息(_ -> 匹配1个字符)
SELECT * FROM product WHERE pname LIKE "__公子";
*5.限制返回数据 LIMIT M,N
*返回前3条数据
SELECT * FROM product LIMIT 3;
*返回从第2条开始，返回3条数据(索引从0开始)
SELECT * FROM product LIMIT 2,3;
*6.正则表达式 REGEXP => SHELL/Python/....
-
查询商品名以"本草"结束的商品信息（text$）
SELECT * FROM product WHERE pname REGEXP "本草$"; -
查询商品名以"花花"开头的商品信息（^text）
SELECT * FROM product WHERE pname REGEXP "^花花"; -
查询商品名包含"想"的商品信息（text）
SELECT * FROM product WHERE pname REGEXP "想";
#注：如果基本的能满足需求，尽量用基本查询方法
[0-9]: 匹配在这个范围内任意一个数据
[a-z,A-Z]
重复前面指定的字符出现的次数
?: 前面的字符重复0-1次
+: 前面的字符1次及以上
*: 前面的字符重复任意次
{n}
{m,n}
eg:
SELECT * FROM contacts WHERE phone_number REGEXP "[1][0-9]{2}-?[0-9]{3}-?[0-9]{4}$";
+----+---------+--------------+
| id | name | phone_number |
+----+---------+--------------+
| 1 | Alice | 1234567890 |
| 3 | Charlie | 123-456-7890 |
| 5 | Eve | 9876543210 |
+----+---------+--------------+
*7.查询空值NULL
SELECT * FROM product WHERE cid IS NOT NULL;
*8.排序查询（ORDER BY）
*单字段排序
(1)查询所有的商品，按价格升序输出
SELECT * FROM product ORDER BY price;
SELECT * FROM product ORDER BY price ASC;
(2)查询所有的商品，按价格降序输出
SELECT * FROM product ORDER BY price DESC;
*多字段排序
查询所有的商品，按分类升序，价格降序输出
SELECT * FROM product ORDER BY cid ASC, price DESC;
SELECT * FROM product ORDER BY cid, price DESC;
*9.聚合查询
-
COUNT(字段名)： 统计值不为NULL的行数
SELECT COUNT(pid) FROM product;
SELECT COUNT(*) FROM product; -
SUM(字段名)：求和
SELECT SUM(price) FROM product;
SELECT SUM(price*0.9) FROM product;
- AVG(字段名)：求平均
SELECT AVG(price*0.9) FROM product;
-
MAX(字段名)：最大值
SELECT MAX(price) FROM product; -
MIN(字段名)：最小值
SELECT MIN(price) FROM product;
*10.分组查询(Group By)
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
*11.统计有哪些分类
SELECT cid FROM product GROUP BY cid;
*12.统计每个分类有多少个商品
SELECT cid,COUNT(cid) FROM product GROUP BY cid;
*13.ROLLUP（在分组统计的基础上，自动生成一行 / 多行总计（汇总）数据，无需你额外编写 UNION ALL 之类的语句来拼接总计结果。）
SELECT cid,COUNT(cid) FROM product GROUP BY cid WITH ROLLUP;
+------+------------+
| cid | COUNT(cid) |
+------+------------+
| c001 | 3 |
| c002 | 5 |
| c003 | 3 |
| c004 | 1 |
| c005 | 1 |
| NULL | 13 |
+------+------------+
*二级分组
1.学生信息：姓名、性别、年龄 => 不同性别中各个年龄的人数是多少
SELECT cid,price,COUNT(cid) FROM product GROUP BY cid,price;
2.将结果按cid,price排序
SELECT cid,price,COUNT(cid) FROM product GROUP BY cid,price ORDER BY cid,price;
3.将结果按count(cid)升序排列
SELECT cid,price,COUNT(cid) FROM product GROUP BY cid,price ORDER BY count(*);
4.将结果count(cid)结果>=2的过滤出来
SELECT cid,price,COUNT(cid) AS num FROM product GROUP BY cid,price HAVING num>=2;
1.修改数据 UPDATE
UPDATE 表名 SET col1=value, col2=value.... WHERE 条件
UPDATE product SET price=price0.9;
UPDATE product SET price=price*0.9 WHERE cid="c001";
*2.删除：DELETE/TRUNCATE
DELETE FROM 表名 WHERE 条件
DELETE FROM product WHERE price<1;
*3.清空表
DELETE FROM product;
#用DELETE清空test_primarykey表中的数据
#用INSERT INTO test_primarykey (age) VALUES (1),(2)
#观察数据的变化
TRUNCATE
TRUNCATE TABLE product;
#用TRUNCATE清空test_primarykey表中的数据
#用INSERT INTO test_primarykey (age) VALUES (1),(2)
#观察数据的变化
#注：
DELETE和TRUNCATE清空表有什么区别
DELETE: 一行一行删除数据，删除后会产生二进制日志（DELETE->INSERT）
TRUNCATE: 把表删除，重新创建一个表，不会产生进制日志，不能数据恢复
二进制日志：可以用来做数据恢复
*MYSQL的函数
1.聚合函数
COUNT
SUM
MIN
MAX
AVG
2.统计长度
LENGTH: 统计字符存储消耗存储空间
CHAR_LENGTH: 统计字符长度，字符的个数
SELECT *,LENGTH(username), CHAR_LENGTH(username) FROM person;
+----------+---------------+------+------+------------------+-----------------------+
| username | email | sex | age | LENGTH(username) | CHAR_LENGTH(username) |
+----------+---------------+------+------+------------------+-----------------------+
| 李卓 | lizhuo@qq.com | 女 | 18 | 6 | 2 |
+----------+---------------+------+------+------------------+-----------------------+
3.字符串函数
*CONCAT: 字符串连接
SELECT CONCAT(username, email) info FROM person;
+---------------------+
| info |
+---------------------+
| 李卓lizhuo@qq.com |
+---------------------+
SELECT CONCAT(username, "-",email) info FROM person;
+----------------------+
| info |
+----------------------+
| 李卓-lizhuo@qq.com |
+----------------------+
*CONCAT_WS: 指定连接符（第一个参数是连接字符串）
SELECT CONCAT_WS("-",username,email,"A","B","C") info FROM person;
+----------------------------+
| info |
+----------------------------+
| 李卓-lizhuo@qq.com-A-B-C |
+----------------------------+
4.FORMAT: 数字格式化（四舍六入五取偶）=> FORMAT(字段,保留N位小数)
SELECT *, FORMAT(price, 0) FROM product;
5.UPPER 字母 => 转大写
6.LOWER 字母 => 转小写
SELECT cid,UPPER(cid),LOWER(UPPER(cid)) FROM product;
5.字符串截取
LEFT:从左截取N个字符
RIGHT:从右截取N个字符
SELECT pname, LEFT(pname,2), RIGHT(pname,2) FROM product;
6.SUBSTRING: 取子串(中间截取)
SELECT "2026-01-07", SUBSTRING("2026-01-07", 6, 2) month;
+------------+-------+
| 2026-01-07 | month |
+------------+-------+
| 2026-01-07 | 01 |
+------------+-------+
1 row in set (0.00 sec)
7.删除字符
LTRIM: 删除前导空格
RTRIM: 删除后续符
SELECT " MYSQL ", LENGTH(LTRIM(" MYSQL ")), LENGTH(RTRIM(" MYSQL "));
+------------+-----------------------------+-----------------------------+
| MYSQL | LENGTH(LTRIM(" MYSQL ")) | LENGTH(RTRIM(" MYSQL ")) |
+------------+-----------------------------+-----------------------------+
| MYSQL | 8 | 7 |
+------------+-----------------------------+-----------------------------+
1 row in set (0.00 sec)
8.TRIM 指定前导或后续符
TRIM(LEADING/TRAILING/BOTH "指定的符号" FROM "字符串")
SELECT "##MYSQL###",TRIM(LEADING "#" FROM "##MYSQL###");
+------------+-------------------------------------+
| ##MYSQL### | TRIM(LEADING "#" FROM "##MYSQL###") |
+------------+-------------------------------------+
| ##MYSQL### | MYSQL### |
+------------+-------------------------------------+
SELECT "##MYSQL###",TRIM(TRAILING "#" FROM "##MYSQL###");
+------------+--------------------------------------+
| ##MYSQL### | TRIM(TRAILING "#" FROM "##MYSQL###") |
+------------+--------------------------------------+
| ##MYSQL### | ##MYSQL |
+------------+--------------------------------------+
SELECT "##MYSQL###",TRIM(BOTH "#" FROM "##MYSQL###");
+------------+----------------------------------+
| ##MYSQL### | TRIM(BOTH "#" FROM "##MYSQL###") |
+------------+----------------------------------+
| ##MYSQL### | MYSQL |
+------------+----------------------------------+
9.REPLACE: 替换字符串
10.REPLACE(字符串，查找str, 替换str)
SELECT REPLACE("##MY#SQL###", "#", "");
+---------------------------------+
| REPLACE("##MY#SQL###", "#", "") |
+---------------------------------+
| MYSQL |
+---------------------------------+
*数字类型相关函数
1.CEIL: 向上取整
2.FLOOR: 向下取整
SELECT 3.18, CEIL(3.18), -3.18, CEIL(-3.18);
SELECT 3.18, FLOOR(3.18), -3.18, FLOOR(-3.18);
3.DIV： 整除（取整除）
SELECT 3/2, 3 DIV 2;
4.MOD: 取余数
SELECT 3%2, 3 MOD 2;
5.POWER: 冪运算
SELECT POWER(2, 10);
+--------------+
| POWER(2, 10) |
+--------------+
| 1024 |
+--------------+
6.ROUND: 四舍六入五取偶（VERSION:8.4.7 版本不同有所差异）=>不一定
SELECT price, ROUND(price,0) FROM product;
7.TRUNCATE(数字，保留N位小数): 数字截取
SELECT price, TRUNCATE(price,1) FROM product;
SELECT price, TRUNCATE(price,-1) FROM product;
+---------+--------------------+
| price | TRUNCATE(price,-1) |
+---------+--------------------+
| 4901.50 | 4900 |
| 2940.30 | 2940 |
| 4900.50 | 4900 |
| 784.08 | 780 |
| 196.02 | 190 |
| 431.24 | 430 |
*日期时间函数
1.NOW: 获取当前日期时间
SELECT NOW();
2.CURDATE: 获取当前日期
SELECT CURDATE();
3.CURTIME: 获取当前时间
SELECT CURTIME();
#日期时间计算：
vip=> 7天
1.生效时间 失效时间CURDATE+7天
SELECT CURDATE()+7;
2.DATE_ADD 支持YEAR, MONTH, DAY, WEEK, HOUR, MINUTE, SECOND
SELECT DATE_ADD(CURTIME(), INTERVAL -1 HOUR);
#计算日期差 => 结果天，日期1-日期2
1.DATEDIFF(日期1， 日期2)
SELECT DATEDIFF(NOW(), '2026-02-15 10:10:10');
#日期格式化
1."2026-01-07" => 01/07/2026------DATE_FORMAT
SELECT DATE_FORMAT("2026-01-07", "%m/%d/%Y");
#ABS => 绝对值
SELECT ABS(-1);
+---------+
| ABS(-1) |
+---------+
| 1 |
+---------+
注：MySQL常用函数的思维导图如下：
1-9 ↩︎
浙公网安备 33010602011771号