SQL单表查询
40
|
0
|
mysql相关,sql语句
|
doraqiqi

2122 字
|
21 分钟

一、单表查询的语法

SELECT 字段1, 字段2, ... 
FROM 表名
WHERE 条件
GROUP BY 字段
HAVING 筛选
ORDER BY 字段
LIMIT 限制条数;

二、关键字的执行优先级（重要）

执行顺序（优先级）：

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. DISTINCT
  7. ORDER BY
  8. LIMIT

细解释

  1. 找到表 (FROM)：
  • 从指定的表中开始查询。
SELECT 字段1, 字段2 
FROM 表名;
  1. 拿着 WHERE 指定的约束条件，去表中取出一条条记录：
  • 根据 WHERE 子句的条件筛选数据。
SELECT 字段1, 字段2 
FROM 表名 
WHERE 条件;
  1. 将取出的一条条记录进行分组 (GROUP BY)：
  • 按照指定的字段对结果进行分组。
SELECT 字段1, COUNT(字段2) 
FROM 表名 
WHERE 条件 
GROUP BY 字段1;
  1. 将分组的结果进行 HAVING 过滤：
  • 对分组后的结果进行进一步筛选。
SELECT 字段1, COUNT(字段2) 
FROM 表名 
WHERE 条件 
GROUP BY 字段1 
HAVING COUNT(字段2) > 1;
  1. 执行 SELECT：
  • 选择需要显示的字段。
SELECT 字段1, COUNT(字段2) 
FROM 表名 
WHERE 条件 
GROUP BY 字段1 
HAVING COUNT(字段2) > 1;
  1. 去重 (DISTINCT)：
  • 从结果集中去除重复的行。
SELECT DISTINCT 字段1 
FROM 表名 
WHERE 条件;
  1. 将结果按条件排序 (ORDER BY)：
  • 按照指定的字段对结果进行排序。
SELECT 字段1, 字段2 
FROM 表名 
WHERE 条件 
ORDER BY 字段1 DESC;
  1. 限制结果的显示条数 (LIMIT)：
  • 限制返回的结果数量。
SELECT 字段1, 字段2 
FROM 表名 
WHERE 条件 
ORDER BY 字段1 
LIMIT 10;

关键字执行优先级的完整示例

假设有一个 students 表，结构如下：

idnameageemailborn_yearreg_time
1egon118123123@qq.com19992023-06-12 10:00:00
2egon228123123@163.com19992023-06-12 10:00:00
3egon338123123@xx.com19992023-06-12 10:00:00
4egon448123123@aa.com19992023-06-12 10:00:00
5egon558123123@zz.com19992023-06-12 10:00:00

我们要执行以下查询：

SELECT name, COUNT(*) 
FROM students 
WHERE age > 20 
GROUP BY name 
HAVING COUNT(*) > 1 
ORDER BY name 
LIMIT 3;

关键字执行顺序解释

  1. FROM：找到表 students。
  2. WHERE：筛选出 age > 20 的记录。
  3. GROUP BY：按 name 进行分组。
  4. HAVING：对分组结果进行筛选，保留 COUNT(*) > 1 的组。
  5. SELECT：选择 name 和 COUNT(*) 作为结果字段。
  6. DISTINCT：去除重复的行（此查询未使用 DISTINCT，跳过）。
  7. ORDER BY：按 name 进行排序。
  8. LIMIT：限制返回的结果数量为 3 条。

三、简单查询

准备

假设有一个 company.employee 表，其结构如下：

列名字段名类型
员工ididint
姓名emp_namevarchar
性别sexenum
年龄ageint
入职日期hire_datedate
岗位postvarchar
职位描述post_commentvarchar
薪水salarydouble
办公室officeint
部门编号depart_idint

创建表

CREATE TABLE employee (
    id INT NOT NULL UNIQUE AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    sex ENUM('male', 'female') NOT NULL DEFAULT 'male',  -- 大部分是男的
    age INT(3) UNSIGNED NOT NULL DEFAULT 28,
    hire_date DATE NOT NULL,
    post VARCHAR(50),
    post_comment VARCHAR(100),
    salary DOUBLE(15, 2),
    office INT,  -- 一个部门一个屋子
    depart_id INT
);

查看表结构

DESC employee;

插入记录

INSERT INTO employee (name, sex, age, hire_date, post, salary, office, depart_id) VALUES
('egon', 'male', 18, '2017-03-01', '老男孩驻沙河办事处外交大使', 7300.33, 401, 1),  -- 教学部
('alex', 'male', 78, '2015-03-02', 'teacher', 1000000.31, 401, 1),
('wupeiqi', 'male', 81, '2013-03-05', 'teacher', 8300, 401, 1),
('yuanhao', 'male', 73, '2014-07-01', 'teacher', 3500, 401, 1),
('liwenzhou', 'male', 28, '2012-11-01', 'teacher', 2100, 401, 1),
('jingliyang', 'female', 18, '2011-02-11', 'teacher', 9000, 401, 1),
('jinxin', 'male', 18, '1900-03-01', 'teacher', 30000, 401, 1),
('成龙', 'male', 48, '2010-11-11', 'teacher', 10000, 401, 1),
('歪歪', 'female', 48, '2015-03-11', 'sale', 3000.13, 402, 2),  -- 销售部
('丫丫', 'female', 38, '2010-11-01', 'sale', 2000.35, 402, 2),
('丁丁', 'female', 18, '2011-03-12', 'sale', 1000.37, 402, 2),
('星星', 'female', 18, '2016-05-13', 'sale', 3000.29, 402, 2),
('格格', 'female', 28, '2017-01-27', 'sale', 4000.33, 402, 2),
('张野', 'male', 28, '2016-03-11', 'operation', 10000.13, 403, 3),  -- 运营部
('程咬金', 'male', 18, '1997-03-12', 'operation', 20000, 403, 3),
('程咬银', 'female', 18, '2013-03-11', 'operation', 19000, 403, 3),
('程咬铜', 'male', 18, '2015-04-11', 'operation', 18000, 403, 3),
('程咬铁', 'female', 18, '2014-05-12', 'operation', 17000, 403, 3);

设置字符编码为 GBK（适用于 Windows 系统）

ALTER TABLE employee CONVERT TO CHARACTER SET gbk;

简单查询语句

查询所有字段

SELECT id, name, sex, age, hire_date, post, post_comment, salary, office, depart_id 
FROM employee;

或

SELECT * FROM employee;

查询特定字段

SELECT name, salary FROM employee;

避免重复 (DISTINCT)

SELECT DISTINCT post FROM employee;

通过四则运算查询

SELECT name, salary * 12 FROM employee;

或

SELECT name, salary * 12 AS annual_salary FROM employee;

或

SELECT name, salary * 12 annual_salary FROM employee;

定义显示格式（可用于数据迁移，数据导出导入）

使用 CONCAT() 函数

SELECT CONCAT('姓名: ', name, '  年薪: ', salary * 12) AS annual_salary 
FROM employee;

使用 CONCAT_WS() 函数

SELECT CONCAT_WS(':', name, salary * 12) AS annual_salary 
FROM employee;

结合 CASE 语句

SELECT
    (
        CASE
            WHEN name = 'egon' THEN name
            WHEN name = 'alex' THEN CONCAT(name, '_BIGSB')
            ELSE CONCAT(name, 'SB')
        END
    ) AS new_name
FROM employee;

四、WHERE 约束

在 WHERE 子句中可以使用多种运算符和关键字来进行条件查询：

  1. 比较运算符：>, <, >=, <=, <>, !=
  2. 范围运算符：BETWEEN ... AND ... 值在指定范围内
  3. 集合运算符：IN (...) 值在指定集合中
  4. 模式匹配：LIKE 'pattern'，其中 pattern 可以使用 % 表示任意多字符，_ 表示一个字符
  5. 逻辑运算符：AND, OR, NOT

示例查询

1. 单条件查询

SELECT name FROM employee
WHERE post = 'sale';

2. 多条件查询

SELECT name, salary FROM employee
WHERE post = 'teacher' AND salary > 10000;

3. 关键字 BETWEEN ... AND ...

SELECT name, salary FROM employee 
WHERE salary BETWEEN 10000 AND 20000;

SELECT name, salary FROM employee 
WHERE salary NOT BETWEEN 10000 AND 20000;

4. 关键字 IS NULL

SELECT name, post_comment FROM employee 
WHERE post_comment IS NULL;

SELECT name, post_comment FROM employee 
WHERE post_comment IS NOT NULL;

SELECT name, post_comment FROM employee 
WHERE post_comment = '';  -- 注意：'' 是空字符串，不是 NULL
-- 将某一行的 post_comment 字段设为空字符串
UPDATE employee SET post_comment = '' WHERE id = 2;

5. 关键字 IN 集合查询

SELECT name, salary FROM employee 
WHERE salary = 3000 OR salary = 3500 OR salary = 4000 OR salary = 9000;

SELECT name, salary FROM employee 
WHERE salary IN (3000, 3500, 4000, 9000);

SELECT name, salary FROM employee 
WHERE salary NOT IN (3000, 3500, 4000, 9000);

6. 关键字 LIKE 模糊查询

-- 通配符 `%`
SELECT * FROM employee 
WHERE name LIKE 'eg%';

-- 通配符 `_`
SELECT * FROM employee 
WHERE name LIKE 'al__';

五、分组查询: GROUP BY

一、什么是分组？为什么要分组？

  1. 分组发生在 WHERE 之后：
  • 分组是基于 WHERE 之后得到的记录进行的。
  1. 分组的定义：
  • 分组是将所有记录按照某个相同字段进行归类，比如根据员工信息表的职位分组，或按性别分组等。
  1. 分组的目的：
  • 获取每个部门的最高工资
  • 获取每个部门的员工数
  • 获取男性和女性的数量
  1. 分组依据：
  • 可以按照任意字段分组，但分组完毕后，比如 GROUP BY post，只能查看 post 字段。如果想查看组内信息，需要借助聚合函数。

二、ONLY_FULL_GROUP_BY 模式

查看 MySQL 5.7 默认的 sql_mode：

SELECT @@global.sql_mode;

输出结果：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY 模式说明：

  • 在 ONLY_FULL_GROUP_BY 模式下，SELECT 目标列表中的所有列的值要么是聚合函数的结果，要么是 GROUP BY 列表中的表达式的值。

设置 sql_mode（去掉 ONLY_FULL_GROUP_BY 模式）：

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

重新查看 sql_mode：

SELECT @@global.sql_mode;

输出结果为空：

+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

在没有 ONLY_FULL_GROUP_BY 模式下的查询：

SELECT * FROM emp GROUP BY post;

输出：

+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
|  9 | 歪歪 | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
|  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  1 | egon | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)

启用 ONLY_FULL_GROUP_BY 模式：

SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY';

重启 MySQL 使设置生效：

mysql> quit

重新登录并使用数据库：

USE db1;

在启用 ONLY_FULL_GROUP_BY 模式下的查询：

SELECT * FROM emp GROUP BY post;

会报错：

ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY

正确查询示例：

SELECT post, COUNT(id) FROM emp GROUP BY post;

输出结果：

+----------------------------+-----------+
| post                       | count(id) |
+----------------------------+-----------+
| operation                  |         5 |
| sale                       |         5 |
| teacher                    |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)

三、GROUP BY 查询

单独使用 GROUP BY 关键字分组：

SELECT post FROM employee GROUP BY post;

注意：按照 post 字段分组，SELECT 查询的字段只能是 post，想要获取组内的其他相关信息，需要借助聚合函数。

GROUP BY 关键字和 GROUP_CONCAT() 函数一起使用：

SELECT post, GROUP_CONCAT(name) FROM employee GROUP BY post;

按岗位分组，并查看组内成员名：

SELECT post, GROUP_CONCAT(name) AS emp_members FROM employee GROUP BY post;

GROUP BY 与聚合函数一起使用：

SELECT post, COUNT(id) AS count FROM employee GROUP BY post;

按岗位分组，并查看每个组有多少人。

四、聚合函数

强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组。

示例：

SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id = 1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id = 3;

六、HAVING 过滤

HAVING 与 WHERE 的不同之处：

  1. 执行优先级从高到低：WHERE > GROUP BY > HAVING
  • WHERE 发生在分组 (GROUP BY) 之前，因此 WHERE 中可以使用任意字段，但不能使用聚合函数。
  • HAVING 发生在分组 (GROUP BY) 之后，因此 HAVING 中可以使用分组的字段和聚合函数。

示例

错误示范

查看 MySQL 的 sql_mode 设置

SELECT @@sql_mode;

输出结果：

+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)

使用 WHERE 进行过滤

SELECT * FROM emp WHERE salary > 100000;

输出结果：

+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

使用 HAVING 进行过滤

SELECT * FROM emp HAVING salary > 100000;

报错：

ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause

正确使用方式

使用分组和聚合函数与 HAVING

SELECT post, GROUP_CONCAT(name) 
FROM emp 
GROUP BY post 
HAVING AVG(salary) > 10000;

输出结果：

+-----------+-------------------------------------------------------+
| post      | group_concat(name)                                    |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野                     |
| teacher   | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+-------------------------------------------------------+
2 rows in set (0.00 sec)

小练习

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

SELECT post, GROUP_CONCAT(name) AS emp_names, COUNT(id) AS emp_count 
FROM employee 
GROUP BY post 
HAVING COUNT(id) < 2;

输出结果：

+-----------------------------------------+--------------------+-----------+
| post                                    | emp_names          | emp_count |
+-----------------------------------------+--------------------+-----------+
| 老男孩驻沙河办事处外交大使              | egon               |         1 |
+-----------------------------------------+--------------------+-----------+

2. 查询各岗位平均薪资大于10000的岗位名、平均工资

SELECT post, AVG(salary) AS avg_salary 
FROM employee 
GROUP BY post 
HAVING AVG(salary) > 10000;

输出结果：

+-----------+---------------+
| post      | avg_salary    |
+-----------+---------------+
| operation | 16800.026000  |
| teacher   | 151842.901429 |
+-----------+---------------+

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

SELECT post, AVG(salary) AS avg_salary 
FROM employee 
GROUP BY post 
HAVING AVG(salary) > 10000 AND AVG(salary) < 20000;

输出结果：

+-----------+--------------+
| post      | avg_salary   |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+

七、查询排序：ORDER BY

排序查询是指根据某个字段的值对查询结果进行排序，可以按单列或多列进行排序。

按单列排序

SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;

按多列排序

先按照 age 排序，如果 age 相同，则按照 salary 排序：

SELECT * FROM employee
ORDER BY age, salary DESC;

小练习

  1. 查询所有员工信息，先按照 age 升序排序，如果 age 相同则按照 hire_date 降序排序
SELECT * FROM employee
ORDER BY age ASC, hire_date DESC;
  1. 查询各岗位平均薪资大于 10000 的岗位名、平均工资，结果按平均薪资升序排列
SELECT post, AVG(salary) AS avg_salary 
FROM employee 
GROUP BY post 
HAVING avg_salary > 10000 
ORDER BY avg_salary ASC;
  1. 查询各岗位平均薪资大于 10000 的岗位名、平均工资，结果按平均薪资降序排列
SELECT post, AVG(salary) AS avg_salary 
FROM employee 
GROUP BY post 
HAVING avg_salary > 10000 
ORDER BY avg_salary DESC;

八、限制查询的记录数：LIMIT

LIMIT 关键字用于限制查询结果的记录数。

示例

SELECT * FROM employee ORDER BY salary DESC 
LIMIT 3;  -- 默认初始位置为 0
SELECT * FROM employee ORDER BY salary DESC 
LIMIT 0, 5;  -- 从第 0 开始，即先查询出第一条，然后包含这一条在内往后查 5 条
SELECT * FROM employee ORDER BY salary DESC 
LIMIT 5, 5;  -- 从第 5 开始，即先查询出第 6 条，然后包含这一条在内往后查 5 条

小练习

分页显示，每页 5 条：

SELECT * FROM employee LIMIT 0, 5;
SELECT * FROM employee LIMIT 5, 5;
SELECT * FROM employee LIMIT 10, 5;

九、使用正则表达式查询

正则表达式可以用于更复杂的模式匹配。

示例

SELECT * FROM employee WHERE name REGEXP '^ale';  -- 名字以 'ale' 开头
SELECT * FROM employee WHERE name REGEXP 'on$';  -- 名字以 'on' 结尾
SELECT * FROM employee WHERE name REGEXP 'm{2}';  -- 名字包含两个连续的 'm'

对字符串匹配的方式小结：

WHERE name = 'egon';  -- 精确匹配
WHERE name LIKE 'yua%';  -- 通配符匹配
WHERE name REGEXP 'on$';  -- 正则表达式匹配

小练习

查看所有员工中名字是 jin 开头，n 或者 g 结尾的员工信息：

SELECT * FROM employee WHERE name REGEXP '^jin.*[gn]$';
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																				
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇