MySQL知识汇总（三）

最新推荐文章于 2023-04-12 21:25:07 发布
最新推荐文章于 2023-04-12 21:25:07 发布
阅读量891 收藏 7
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_46690290/article/details/106817184
版权

目录

DQL（数据查询语言）

在这里插入图片描述

链接: 点击跳转到MySQL知识汇总（二）.

分组查询

1、概念

分组函数作用于一组数据，并对一组数据返回一个值。

2、分组函数类型

分组函数案例

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
Sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：

1、sum、avg一般用于处理数值型max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段

1.简单的使用:

SELECT SUM(salary) FROM t_mysql_employees;
SELECT AVG(salary) FROM t_mysql_employees;
SELECT MIN(salary) FROM t_mysql_employees;
SELECT MAX(salary) FROM t_mysql_employees;
SELECT COUNT(salary) FROM t_mysql_employees;

2.语法：

SELECT SUM(salary) 和,AVG(salary)
 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;


SELECT SUM(salary) 和,ROUND(AVG(salary),2) 
平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM t_mysql_employees;

2、参数支持哪些类型

SELECT SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;

SELECT SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;

SELECT MAX(last_name),MIN(last_name) FROM t_mysql_employees;

SELECT MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;

SELECT COUNT(commission_pct) FROM t_mysql_employees;

SELECT COUNT(last_name) FROM t_mysql_employees;

3、是否忽略null

SELECT SUM(commission_pct) ,
	AVG(commission_pct),
	SUM(commission_pct)
/35,SUM(commission_pct)/107 
FROM t_mysql_employees;

SELECT MAX(commission_pct) ,
MIN(commission_pct) 
FROM t_mysql_employees;

SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT commission_pct FROM t_mysql_employees;

4、和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;

5.count函数的详细介绍

SELECT COUNT(salary) FROM t_mysql_employees;

SELECT COUNT(*) FROM t_mysql_employees;

SELECT COUNT(1) FROM t_mysql_employees;

效率：

MYISAM存储引擎下 ，COUNT()的效率高
INNODB存储引擎下，
COUNT()和COUNT(1)的效率差不多，
比COUNT(字段)要高一些


6.和分组函数一同查询的字段有限制,employee_id是最小的那个

SELECT AVG(salary),employee_id  FROM t_mysql_employees;

分组函数案列：

1.查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary) 最大值,MIN(salary)
 最小值,AVG(salary) 平均值,SUM(salary) 
 和
FROM t_mysql_employees;

2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）

SELECT MAX(hiredate) 最大,MIN(hiredate)
 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM t_mysql_employees;

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM t_mysql_employees;

SELECT DATEDIFF('1995-2-7','1995-2-6');

3.查询部门编号为90的员工个数

SELECT COUNT(*) FROM t_mysql_employees WHERE department_id = 90;

3、Group by子句

4、非法使用组函数

5、过滤分组Having 子句


分组查询案例：

语法：

select 查询列表 from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：

1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选

	针对的表			位置		连接的关键字

分组前筛选	原始表				group by前	where

分组后筛选 group by后的结果集 group by后 having


引入：查询每个部门的员工个数

SELECT COUNT(*) FROM t_mysql_employees WHERE department_id=90;

1.简单的分组

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;

二、可以实现分组前的筛选

案例：
查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM t_mysql_employees
WHERE email LIKE '%a%'
GROUP BY department_id;

案例：
查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

3、分组后筛选

案例：
查询哪个部门的员工个数>5

①查询每个部门的员工个数

SELECT COUNT(*),department_id
FROM t_mysql_employees
GROUP BY department_id;

② 筛选刚才①结果

SELECT COUNT(*),department_id
FROM t_mysql_employees

GROUP BY department_id

HAVING COUNT(*)>5;

案例：
每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

案例：
领导编号>102的每个领导手下的最低工资大于5000的领导编号和
最低工资manager_id>102

SELECT manager_id,MIN(salary)
FROM t_mysql_employees
GROUP BY manager_id
HAVING MIN(salary)>5000;

4.添加排序

案例：
每个工种有奖金的员工的最高工资>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 ;

5.按多个字段分组

案例：
查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM t_mysql_employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

分组查询案列：

#1.查询各job_id的员工工资的
最大值，最小值，平均值，总和，并按job_id升序

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM t_mysql_employees
GROUP BY job_id
ORDER BY job_id;

2.查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM t_mysql_employees;

3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT MIN(salary),manager_id
FROM t_mysql_employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序

SELECT department_id,COUNT(*),AVG(salary) a
FROM t_mysql_employees
GROUP BY department_id
ORDER BY a DESC;

5.选择具有各个job_id的员工人数

SELECT COUNT(*) 个数,job_id
FROM t_mysql_employees
GROUP BY job_id;

多表查询

1、笛卡尔集

笛卡尔集会在下面条件下产生

 省略连接条件
 连接条件无效
 所有表中的所有行互相连接
 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。

2、等值/连接连接

使用连接在多个表中查询数据:

在 WHERE 子句中写入连接条件。
在表中有相同列时，在列名之前加上表名前缀


区分重复的列名:

• 在不同表中具有相同列名的列可以用表的别名
加以区分。
• 如果使用了表别名，则在select语句中需要使
用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越
好

表的别名:

使用别名可以简化查询。
使用表名前缀可以提高执行效率。


连接多个表:

连接 n个表,至少需要 n-1个连接条件。
例如：连接 三个表，至少需要两个连接条件。


连接查询案例:

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：

按功能分类：
	内连接：
		等值连接
		非等值连接
		自连接
	外连接：
		左外连接
		右外连接
		全外连接
		交叉连接

1、等值连接

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选


案例：
查询员工名和对应的部门名

SELECT last_name,department_name
FROM t_mysql_employees,departments
WHERE t_mysql_employees.department_id=t_mysql_departments.department_i`;

2、为表起别名

①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定


查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

3、两个表的顺序是否可以调换
查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_jobs j,t_mysql_employees e
WHERE e.`job_id`=j.`job_id`;

4、可以加筛选

案例：
查询有奖金的员工名、部门名

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;

5.可以加分组
案例：
查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

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`;

6、可以加排序

案例：
查询每个工种的工种名和员工的个数，并且按员工个数降序

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;

7.可以实现三表连接？

案例：
查询员工名、部门名和所在的城市

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%'

2.非等值连接
案例：
查询员工的工资和工资级别

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';

3.自连接
案例：
查询 员工名和上级的名称

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`;

3、join链接

分类：

内连接 [inner] join on
  外连接
	左外连接 left [outer] join on
	 右外连接 right [outer] join on

使用ON 子句创建连接：

自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
子句使语句具有更高的易读性。


join案例：

二、语法

select 查询列表
from 表1 别名 【连接类型】
 join 表2 别名 
 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

特点：

①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和s的等值连接效果是一样的，都是查询多表的交集

1、等值连接
案例:
查询员工名、部门名

SELECT last_name,department_name
FROM t_mysql_departments d
 JOIN  t_mysql_employees e
ON e.`department_id` = d.`department_id`;

案例:
查询名字中包含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%';

  1. 查询部门个数>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;

案例:
查询哪个部门的员工个数>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

三.自连接

查询员工的名字、上级的名字

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%';

二、外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：

1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表 右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的


左外连接:

 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;

案例：查询哪个部门没有员工
左外接:

 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;

全外接:

 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;

常见函数

1、字符函数

作用函数结果
转小写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

2、数字函数

作用函数结果
四舍五入ROUND(45.926, 2)45.93
截断TRUNC(45.926, 2)45.92
求余MOD(1600, 300)100

3、日期函数

作用函数结果
获取当前日期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;

6.trim

SELECT LENGTH(TRIM('    张翠山    ')) 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 VERSION();
SELECT DATABASE();
SELECT USER();

五、流程控制函数
1.if函数： if else 的效果

SELECT IF(10<5,'大','小');

SELECT last_name,commission_pct,
IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM t_mysql_employees;

2.case函数的使用一：
switch case 的效果:

java中 switch(变量或表达式){
case 常量1：语句1;break; …
default:语句n;break; }

mysql中:

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;

else 要显示的值n或语句n;
end

案例：
查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM t_mysql_employees;

3.case 函数的使用二：类似于 多重if

java中：
if(条件1){
语句1；
}else if(条件2){
语句2；
}
…
else{
语句n;
}

mysql中：

case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2

else 要显示的值n或语句n
end

案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM t_mysql_employees;

4、流程控制函数

条件表达式：

在 SQL 语句中使用IF-THEN-ELSE 逻辑
使用方法: – CASE 表达式

CASE 表达式 ：

在需要使用 IF-THEN-ELSE 逻辑时:

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

子查询

子查询案例
子查询：
含义：

出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询

from后面：
	支持表子查询
where或having后面：★
	标量子查询（单行） √
	列子查询  （多行） √
	
	行子查询
	
exists后面（相关子查询）
	表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）


一.where或having后面:

1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

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'
);

案例：
返回job_id与141号员工相同，
salary比143号员工多的员工 姓名，job_id 和工资

①查询141号员工的job_id

SELECT job_id
FROM t_mysql_employees
WHERE employee_id = 141

②查询143号员工的salary

SELECT salary
FROM t_mysql_employees
WHERE employee_id = 143

③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②

SELECT last_name,job_id,salary
FROM t_mysql_employees
WHERE job_id = (
	SELECT job_id
	FROM t_mysql_employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM t_mysql_employees
	WHERE employee_id = 143
);

案例：
返回公司工资最少的员工的last_name,job_id和salary

①查询公司的 最低工资

SELECT MIN(salary)
FROM t_mysql_employees

②查询last_name,job_id和salary，要求salary=①

SELECT last_name,job_id,salary
FROM t_mysql_employees
WHERE salary=(
	SELECT MIN(salary)
	FROM t_mysql_employees
);

案例：
查询最低工资大于50号部门最低工资的部门id和其最低工资

①查询50号部门的最低工资

SELECT  MIN(salary)
FROM t_mysql_employees
WHERE department_id = 50

②查询每个部门的最低工资

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id

③ 在②基础上筛选，满足min(salary)>①

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM t_mysql_employees
	WHERE department_id = 50
);

非法使用标量子查询

SELECT MIN(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary
	FROM t_mysql_employees
	WHERE department_id = 250
);

2.列子查询（多行子查询）
案例：
返回location_id是1400或1700的部门中的所有员工姓名

①查询location_id是1400或1700的部门编号

SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id IN(1400,1700)

②查询员工姓名，要求部门号是①列表中的某一个

SELECT last_name
FROM t_mysql_employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM t_mysql_departments
	WHERE location_id IN(1400,1700)
);

案例：
返回其它工种中比job_id为‘IT_PROG’工种
任一工资低的员工的员工号、姓名、job_id 以及salary

①查询job_id为‘IT_PROG’部门任一工资

SELECT DISTINCT salary
FROM t_mysql_employees
WHERE job_id = 'IT_PROG'

②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个

SELECT last_name,employee_id,job_id,salary
FROM t_mysql_employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM t_mysql_employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

3、行子查询（结果集一行多列或多行多列）

案例：
查询员工编号最小并且工资最高的员工信息

SELECT * 
FROM t_mysql_employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM t_mysql_employees
);

①查询最小的员工编号

SELECT MIN(employee_id)
FROM t_mysql_employees

②查询最高工资

SELECT MAX(salary)
FROM t_mysql_employees

③查询员工信息

SELECT *
FROM t_mysql_employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM t_mysql_employees
)AND salary=(
	SELECT MAX(salary)
	FROM t_mysql_employees
);

二、select后面
仅仅支持标量子查询:
案例：
查询每个部门的员工个数

SELECT d.*,(
	SELECT COUNT(*)
	FROM t_mysql_employees e
	WHERE e.department_id = d.`department_id`
 ) 个数
 FROM t_mysql_departments d;

案例：
查询员工号=102的部门名

SELECT (
	SELECT department_name,e.department_id
	FROM t_mysql_departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部门名;

三、from后面

将子查询结果充当一张表，要求必须起别名

案例：
查询每个部门的平均工资的工资等级
①查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id

四、exists后面（相关子查询）

语法： exists(完整的查询语句) 结果： 1或0

SELECT EXISTS(SELECT employee_id 
FROM t_mysql_employees 
WHERE salary=300000);

案例：
查询有员工的部门名

in：

SELECT department_name
FROM t_mysql_departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM t_mysql_employees
)

exists：

SELECT department_name
FROM t_mysql_departments d
WHERE EXISTS(
	SELECT *
	FROM t_mysql_employees e
	WHERE d.`department_id`=e.`department_id`
);



综合查询案例

一、查询每个专业的学生人数

SELECT majorid,COUNT(*)
FROM student
GROUP BY majorid;

二、查询参加考试的学生中，每个学生的平均分、最高分

SELECT AVG(score),MAX(score),studentno
FROM result
GROUP BY studentno;

三、查询姓张的每个学生的最低分大于60的学号、姓名

SELECT s.studentno,s.studentname,MIN(score)
FROM student s
JOIN result r
ON s.studentno=r.studentno
WHERE s.studentname LIKE '张%'
GROUP BY s.studentno
HAVING MIN(score)>60;

四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称

SELECT m.majorname,s.studentname
FROM student s
JOIN major m
ON m.`majorid=s.majorid
WHERE DATEDIFF(borndate,'1988-1-1')>0
GROUP BY m.majorid;

五、查询每个专业的男生人数和女生人数分别是多少

SELECT COUNT(*),sex,majorid
FROM student
GROUP BY sex,majorid;

六、查询专业和张翠山一样的学生的最低分
①查询张翠山的专业编号

SELECT majorid
FROM student
WHERE studentname = '张翠山'

②查询编号=①的所有学生编号

SELECT studentno
FROM student
WHERE majorid=(
	SELECT majorid
	FROM student
	WHERE studentname = '张翠山'
)

②查询最低分

SELECT MIN(score)
FROM result
WHERE studentno IN(
SELECT studentno
	FROM student
	WHERE majorid=(
		SELECT majorid
		FROM student
		WHERE studentname = '张翠山'
)
)

七、查询大于60分的学生的姓名、密码、专业名

SELECT studentname,loginpwd,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
JOIN result r ON s.studentno=r.studentno
WHERE r.score>60;

八、按邮箱位数分组，查询每组的学生个数

SELECT COUNT(*),LENGTH(email)
FROM student
GROUP BY LENGTH(email);

九、查询学生名、专业名、分数

SELECT studentname,score,majorname
FROM student s
JOIN major m ON s.majorid=  m.majorid
LEFT JOIN result r ON s.studentno=r.studentno

十、查询哪个专业没有学生，分别用左连接和右连接实现
左:

SELECT m.majorid,m.majorname,s.studentno
FROM major m
LEFT JOIN student s ON m.majorid = s.majorid
WHERE s.studentno IS NULL;

右:

SELECT m.majorid,m.majorname,s.studentno
FROM student s
RIGHT JOIN  major m ON m.majorid= s.majorid
WHERE s.studentno IS NULL;

十一、查询没有成绩的学生人数

SELECT COUNT(*)
FROM student s
LEFT JOIN result r ON s.studentno = r.studentno
WHERE r.id IS NULL

分页查询

分页查询案例：

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

关键字释义
select查询列表
from表
join type join表2
on连接条件
where筛选条件
group by分组字段
having分组后的筛选
order by排序的字段
limit ，offset,size分页，偏移

|centered 文本居中 | right-aligned 文本居右

offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数

特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size

select 查询列表
from 表
limit (page-1)*size,size;

size=10
page  
1	0
2  	10
3	20

案例：
查询前五条员工信息

SELECT * FROM  t_mysql_employees LIMIT 0,5;
SELECT * FROM  t_mysql_employees LIMIT 5;

案例：
查询第11条——第25条

SELECT * FROM  t_mysql_employees LIMIT 10,15;

案例：
有奖金的员工信息，并且工资较高的前10名显示出来

SELECT 
    * 
FROM
    t_mysql_employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;
h0824_
关注
  • 0
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL知识点汇总
F什么琛的博客
07-23 131
一、MySQL基础 ①和数据库相关的操作 查询数据库：show databases； 新建数据库：create database if not exists db_name character set utf8mb4; 使⽤数据库：use db_name; 删除数据库：drop database db_name; ②类型相关 时间：datetime 整数：int 布尔：boolean 字符串：varchar ⽂本：text 超⼤⽂本：longtext ⾦额： decimal ③表相关的操作 查看所
MySQL知识点总结（一）
qq_46046431的博客
06-02 164
设计索引的时候，把where子句最经常用到的放到最左边；mysql匹配的时候，从左到右，一直遇到范围标识符为止；原因：（1）存在锁（2）索引没设置索引，直接全局搜索，比较慢；设置了索引，但是sql语句索引失效了（有函数，或者有运算）；（3）mvcc日志过多（4）更新脏表信息解决：（1）设置索引（2）优化sql语句，防止索引失效（3）检查是否有锁（4）把大事务变成小事务只需要在sql语句之前加上一个解释函数，；：检索性能，以上；：没用索引就是，用了就是索引的名字；因为Mysql中，一条sql语句，默认是全表检
mysql+查询列最小值_如何根据行中的数字序列获取MySQL表列的最小值
weixin_36147027的博客
02-02 545
我有一个MySQL表(results)，包含如下所示的六列:+----+-------+-------+-------+-------+-------+| id | can_1 | can_2 | can_3 | can_4 | can_5 |+----+-------+-------+-------+-------+-------+| 1 | 1 | 0 | 2 | ...
MySQL如何取出一组数的最小值或最小值以及LEAST()、GREATEST()函数的使用注意
m0_53396342的博客
04-12 4639
MySQL如何取出一组数的最小值或最小值以及LEAST()、GREATEST()函数的使用注意
MySQL小节练习二
无
09-18 1888
四：分组函数练习 1.where子句可否使用组函数进行过滤? 不可以 2.查询公司员工工资的最大值，最小值，平均值，总和 SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees; 3.查询各job_id的员工工资的最大值，最小值，平均值，总和 SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary) FROM employees GROUP
sql指定排序规则、筛选id最大或最小记录、查询插入数据
jimoandgudu的博客
04-09 4351
一、sql指定排序规则1. oracleselect l.* from learner l where l.study_num in ('1760010025', '1660030817', '1860039508', '1660056123') order by instr('1760010025，1660030817，1860039508，1660056123', l.study_num
SQL 获取连续编号中断号的最小值
wangjunhe的专栏
02-09 2006
问题描述；返回最小的缺失数,要求一定是正数. IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE dbo.T1; GO CREATE TABLE dbo.T1 ( keycol INT NOT NULL PRIMARY KEY CHECK(keycol > 0), datacol VARCHAR(10) NOT NULL );
Mysql基础知识点汇总
09-10
本文给大家汇总介绍了mysql的23个基础的知识点，这些都是学习mysql的必备知识，小伙伴们可以参考下。
MySQL必备的常见知识点汇总整理
09-08
MySQL是世界上最流行的关系型数据库管理...理解并熟练运用这些MySQL知识点，将有助于你在实际开发和管理数据库时更加得心应手。不断实践和深入学习，可以进一步提升你的数据库技能，应对更复杂的数据库设计和管理挑战。
MySQL知识点汇总（知识点+代码）
08-21
这个压缩包文件"MySQL知识点汇总（知识点+代码）"提供了全面的MySQL学习资源，包括基础操作、数据库设计、SQL查询等多个方面。以下是对这些知识点的详细阐述： 1. **数据库创建与管理**： - `CREATE DATABASE`命令...
MySQL 索引知识汇总
09-08
MySQL索引是数据库管理系统中用于加速数据检索的关键技术。索引的存在使得数据库系统能更快地定位到所需的数据行，从而显著提升查询效率。本篇文章将深入探讨MySQL索引的使用方法，包括其类型、创建、修改和删除。 ...
mysql基础知识扫盲
09-09
本文将介绍MySQL的基础知识，包括连接MySQL服务器、SQL语句、查询、聚合操作、表连接、子查询以及联合查询。 首先，连接MySQL服务器是使用MySQL的第一步。通过CMD命令行，切换到MySQL Server的bin目录，输入`mysql ...
SQL-查询表中最大(小）值的记录
dandelionLYY的博客
02-27 4096
首先表T(id,name,value),查询的value最大（小）值所在的记录。 select max(value) from T ; # 查询的只是结构内的最大（小）值，但未将记录查询出来。 有人可能第一反应是分组嘛，group by id 。查询出id或者name。但是就掉坑里了。 group by的意思是 根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规...
SQL 难点解决：集合及行号
ertyee42的博客
11-21 159
【摘要】 SQL 虽然有集合概念，但对于集合运算、特别是有序集合运算，提供的支持却很有限，经常要采用很费解的思路才能完成，计算效率也不佳。而集算器 SPL 在方面则要直观许多，可以按自然思维习惯写出运算。这里对 SQL 和集算器 SPL 在集合运算和行号相关运算方面进行了对比，如果需要了解更多，请前往乾学院：SQL 难点解决：集合及行号! 复制摘要   1、  和集 示例 1：求重叠时间段...
数据库-数据查询
m0_51305283的博客
04-10 199
数据查询 基础语法 select[all|distinct]<目标列表达式> from <表名或者视图名> where<条件表达式> group by <列名1>[having<条件表达式>] order by <列名2>[ASC|DESC] 含有having的条件表达式必须跟在group后面 不可以跟在where后面 例如： 查询平均成绩大于等于90的学生学号和平均成绩 正确答案 select sno avg(grade) fr
MySQL 基础篇 二【DQL:数据库查询语言】
Kukeoo的博客
03-01 1365
一、基础查询 二、条件查询 三、排序查询 四、常见函数 五、分组函数 六、分组查询 七、连接查询 八、子查询 九、分页查询 十、union联合查询
单行函数，聚合函数课后练习
m0_46914845的博客
07-27 1088
单行函数，聚合函数课后练习
MySQL笔记2
weixin_55694545的博客
07-08 852
排序查询（order by asc desc升降序)；单行函数（字符函数length concat substr trim instr upper lower lpad rpad replace、数学函数round ceil floor truncate mod、日期函数now curdate curtime year month hour second str_to_date date_format、其他函数、控制函数if case）；分组函数（sum avg max min count）......
C++实现的俄罗斯方块游戏
最新发布
07-01
一个简单的俄罗斯方块游戏的C++实现，涉及基本的游戏逻辑和控制。这个示例包括了初始化、显示、移动、旋转和消除方块等基本功能。 主要文件 main.cpp：包含主函数和游戏循环。 tetris.h：包含游戏逻辑的头文件。 tetris.cpp：包含游戏逻辑的实现文件。 运行说明 确保安装SFML库，以便进行窗口绘制和用户输入处理。
Mysql面试题汇总资料
10-09
MySQL面试题汇总： MySQL面试题汇总包括了很多关于MySQL的面试题和问题，这些问题旨在测试一个人对MySQL数据库的理解和应用能力。MySQL是一个关系型数据库管理系统，它被广泛应用于Web应用程序中，具有高效、稳定和...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值