本文介绍了MySQL的高级查询技巧，包括分组查询的基本语法和特点，详细讲解了连表查询的概念、分类及各种连接方式，如非等值连接、自连接和外连接。此外，还探讨了常用的数据库函数应用和子查询的使用。最后，阐述了分页查询的语法，并强调理解这些功能对于有效数据库操作的重要性。
MySQL分组查询、连表查询、子查询，分页查询
前言
之前讲过了MySQL的一些基本知识点，今天讲一点复杂的SQL语句！
MySQL相关知识点：
MySQL安装卸载以及使用
MySQL之账号管理，四大引擎以及DDL（数据定义语言）
MySQL常见查询、表达式、排序、分组查询基础
分组查询案例
语法：
select 查询列表 from 表 【where 筛选条件】
group by 分组的字段 【order by 排序的字段】;
特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
| 针对的表 | 位置 | 连接的关键字 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | group by前 | where |
| 分组后筛选 | group by后的结果集 | group by后 | having |
配合组函数的分组：
1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id FROM t_mysql_employees
GROUP BY job_id;
2：查询每个位置的部门个数
SELECT COUNT(*),location_id FROM t_mysql_departments
GROUP BY location_id;
我们还可以实现分组前的筛选：
1：查询邮箱中包含a字符的 每个部门的 最高工资
SELECT MAX(salary),department_id
FROM t_mysql_employees
WHERE email LIKE '%a%'
GROUP BY department_id;
2：查询有奖金的每个领导手下员工的平均工资
SELECT AVG(salary),manager_id
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
分组后筛选
1：查询哪个部门的员工 个数>5
SELECT COUNT(*),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING COUNT(*)>5;
2：每个工种有奖金的员工的 最高工资 >12000 的 工种编号和最高工资
SELECT job_id,MAX(salary)
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
添加排序:
每个工种有奖金的员工的 最高工资 >6000 的 工种编号和最高工资 ,按 最高工资升序
SELECT job_id,MAX(salary) m
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;
按多个字段分组:
查询每个工种每个部门的最低工资,并按最低工资降序
SELECT MIN(salary),job_id,department_id
FROM t_mysql_employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;
连表查询
概念：
我们在很多项目中有时候需要的结果集在不同的表中，我们就需要用到连表查询。
分类
按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
连表查询这里涉及到一个名词笛卡尔集，那么为什么会涉及到笛卡尔集呢？什么是笛卡尔集呢？
为什么会涉及到笛卡尔集？
因为连表查询如其名就是连着2或2以上张表查询结果集，这个结果集会将两（多）张表合二（多）为一，并且是1条数据连接完第二张表所有数据后第二条数据又会连接完第二张表的数据，这显然不是我们一般情况下需要的结果。
什么是笛卡尔集呢？
假设a表中有4条数据，b表中有14条数据，查询出来的就是4*14条数据，这个结果集就叫笛卡尔集。
笛卡尔集产生的条件：
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。
连接
使用连接在多个表中查询数据
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀
区分重复的列名
• 在不同表中具有相同列名的列可以用表的别名加以区分。
• 如果使用了表别名，则在select语句中需要使用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越好
连接多个表
• 连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
sql92标准连接查询案例
一、:等值连接
查询女神名和对应的男神名
SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;
查询员工名和对应的部门名
SELECT last_name,department_name
FROM t_mysql_employees,departments
WHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;
可以加筛选
查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;
查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM t_mysql_departments t_mysql_d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';
可以加分组
1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM t_mysql_departments d,t_mysql_employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
可以加排序
案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM t_mysql_employees e,t_mysql_jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
可以实现三表连接
案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;
非等值连接
案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';
自连接
案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.`manager_id`=m.`employee_id`;
join链接
分类：
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
使用ON 子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
sql99语法 join案例
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
分类：
内连接（★）：inner
语法：select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;
分类： 等值 非等值 自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
外连接
左外(★):left 【outer】
右外(★)：right 【outer】
全外：full【outer】
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
交叉连接：cross
等值连接
1.查询员工名、部门名
SELECT last_name,department_name
FROM t_mysql_departments d
JOIN t_mysql_employees e
ON e.`department_id` = d.`department_id`;
2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_jobs j
ON e.`job_id`= j.`job_id`
WHERE e.`last_name` LIKE '%e%';
3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM t_mysql_departments d
INNER JOIN t_mysql_locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
#② 在①结果上筛选员工个数>3的记录，并排序
SELECT COUNT(*) 个数,department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id`
INNER JOIN t_mysql_jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;
非等值连接
查询员工的工资级别
SELECT salary,grade_level
FROM t_mysql_employees e
JOIN t_mysql_job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),grade_level
FROM t_mysql_employees e
JOIN t_mysql_job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
自连接
查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM t_mysql_employees e
JOIN t_mysql_employees m
ON e.`manager_id`= m.`employee_id`;
查询姓名中包含字符k的员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM t_mysql_employees e
JOIN t_mysql_employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';
外连接
左外连接
SELECT b.*,bo.*
FROM t_mysql_boys bo
LEFT OUTER JOIN t_mysql_beauty b
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id` IS NULL;
案例1：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM t_mysql_departments d
LEFT OUTER JOIN t_mysql_employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#右外
SELECT d.*,e.employee_id
FROM t_mysql_employees e
RIGHT OUTER JOIN t_mysql_departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
#全外
USE girls;
SELECT b.*,bo.*
FROM t_mysql_beauty b
FULL OUTER JOIN t_mysql_boys bo
ON b.`boyfriend_id` = bo.id;
#交叉连接
SELECT b.*,bo.*
FROM t_mysql_beauty b
CROSS JOIN boys bo;
常见函数
字符函数
| 作用 | 函数 | 结果 |
|---|---|---|
| 转小写 | LOWER(‘SQL Course’) | sql course |
| 转大写 | UPPER(‘SQL Course’) | SQL COURSE |
| 拼接 | CONCAT(‘Hello’, ‘World’) | HelloWorld |
| 截取 | SUBSTR(‘HelloWorld’,1,5) | Hello |
| 长度 | LENGTH(‘HelloWorld’) | 10 |
| 字符出现索引值 | INSTR(‘HelloWorld’, ‘W’) | 6 |
| 字符截取后半段 | TRIM(‘H’ FROM ‘HelloWorld’) | elloWorld |
| 字符替换 | REPLACE(‘abcd’,‘b’,‘m’) | amcd |
数字函数
| 作用 | 函数 | 结果 |
|---|---|---|
| 四舍五入 | ROUND(45.926, 2) | 45.93 |
| 截断 | TRUNC(45.926, 2) | 45.92 |
| 求余 | MOD(1600, 300) | 100 |
日期函数
| 作用 | 函数 | 结果 |
|---|---|---|
| 获取当前日期 | now() | |
| 将日期格式的字符转换成指定格式的日期 | STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’) | 1999-09-13 |
| 将日期转换成字符 | DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’) | 2018年06月06日 |
常见函数案例
概念：
类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：
1、隐藏了实现细节 2、提高代码的重用性
调用：
select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
常见函数
一、单行函数
字符函数：
length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
数学函数：
round
ceil
floor
truncate
mod
日期函数：
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
其他函数：
version
database
user
控制函数
if
case
一、字符函数案例
#1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');
#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 t_mysql_FROM employees;
#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM t_mysql_employees;
#4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put
FROM t_mysql_employees;
#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;
#6.trim
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa') AS out_put;
#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',2,'*') AS out_put;
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;
#9.replace 替换
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
二、数学函数
#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);
#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);
#truncate 截断
SELECT TRUNCATE(1.69999,1);
#mod取余
/*
mod(a,b) ： a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;
三、日期函数
#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();
#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT YEAR(hiredate) 年 FROM t_mysql_employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;
#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;
#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL;
子查询
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
子查询案例
#案例1：谁的工资比 Abel 高?
#①查询Abel的工资
SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'
#②查询员工的信息，满足 salary>①结果
SELECT *
FROM t_mysql_employees
WHERE salary>(
SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'
);
分页查询
语法：
select 查询列表
from 表
【join type join 表2 on 连接条件
where 筛选条件 group by 分组字段
having 分组后的筛选 order by 排序的字段】 limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
案例
1：查询前五条员工信息
SELECT * FROM t_mysql_employees LIMIT 0,5;
2：查询第11条——第25条
SELECT * FROM t_mysql_employees LIMIT 10,15;
总结
数据库一般就是代码语句较多，知道有这个功能就行啦，一般来说不一定全部都要记住，只要会用就好啦！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
