DQL查询语句

DQL查询语句

分组查询

1.1 分组函数作用于一组数据，并对一组数据返回一个值
1.2 分组函数案例

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
一般简单的使用代码是下面这样的：

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;


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;

这些参数支持的类型也是如下：

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

是否忽略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;

和distinct搭配：

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

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

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(字段)要高一些

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

SELECT AVG(salary),employee_id  FROM t_mysql_employees;

例子：查询部门编号为90的员工个数

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

---

多表查询

2.1 什么是多表查询

多表查询：当查询的数据并不是来源一个表时，需要使用 多表链接操作完成查询。根据

不同表中的数据之间的关系查询相关联的数据。

2.2 多表链接方式：

内连接：连接两个表，通过相等或不等判断链接列，称为内连接。在内连接中典型

的联接运算有 = 或 <> 之类的比较运算符。包括等值联接和自然联接

等值连接

非等值连接

自连接

SQL99：交叉链接(CROSS JOIN)

SQL99：内连接(INNER JOIN)

SQL99：自然链接(NATURAL JOIN)

外连接：在两个表之间的连接，返回内连接的结果，同时还返回不匹配行的左（或右）表的连接，称为左（或右）连接。返回内连接的结果， 同时还返回左和右连接，称为全连接。

左外链接

右外链接

全外链接

子查询：当一个查询是另一个查询的条件时，称之为子查询。

2.3 什么是笛卡尔乘积
笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尓积（Cartesian product），又称直

积，表示为 X * Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一

个成员。

2.4 如何避免出现笛卡尔乘积
当 一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积 (Cartesian product)，其中

所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积

会产生大量的行，其结果没有什么用。应该在 WHERE 子句中始终包含一个有效的连接条

件。

2.5 示例

select * from employees,departments;

2.6 多表 连接 语法
2.7 语法结构

使用一个连接从多个表中查询数据。

• 在 WHERE 子句中写连接条件。

• 当多个表中有相同的列名时，将表名或者表的别名作为列名的前缀。

2.8 定义连接
当数据从多表中查询时，要使用连接 (join) 条件。一个表中的行按照存在于相应列中的值被连

接到另一个表中的行。

2.9 原则
• 在写一个连接表的 SELECT 语句时，在列名前面用表名或者表别名可以使语义清楚，

并且加快数据库访问。

• 为了连接 n 个表在一起，你最少需要 n-1 个连接条件。例如，为了连接 4 个表，

最少需要 3 个连接条件。

2.1.1 等值连接
什么等值连接
等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins)。是通过等号来判断连

接条件中的数据值是否相匹配。

2.1.2 抉择矩阵 (decision matrix)
是通过行与列来分析一个查询的方式。

例如，如果你想显示同一个部门中所有名字为 Taylor 的雇员的名字和部门名称，可以

写出下面的决策矩阵：

投影列 源表 条件

last_name employees last_name = ‘Taylor’

department_name departments employees.department_id =

departments.department_id；

---

子查询

#进阶7：子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

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

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

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

*/

#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）

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

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

< >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/
#1.标量子查询★

#案例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'

);

#案例2：返回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

);

#案例3：返回公司工资最少的员工的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
);

#案例4：查询最低工资大于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.列子查询（多行子查询）★
#案例1：返回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)
);

#案例2：返回其它工种中比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’;

#或
SELECT last_name,employee_id,job_id,salary
FROM t_mysql_employees
WHERE salary<(
SELECT MAX(salary)
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

) AND job_id<>‘IT_PROG’;

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE t_mysql_salary<ALL(
SELECT DISTINCT salary
FROM t_mysql_employees
WHERE job_id = ‘IT_PROG’

) AND job_id<>‘IT_PROG’;

#或

SELECT last_name,employee_id,job_id,salary
FROM t_mysql_employees
WHERE salary<(
SELECT MIN( 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;

#案例2：查询员工号=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

SELECT * FROM t_mysql_job_grades;

#②连接①的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

SELECT ag_dep.*,g.grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

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

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

*/

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

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

#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

);

#案例2：查询没有女朋友的男神信息

#in

SELECT bo.*
FROM t_mysql_boys bo
WHERE bo.id NOT IN(
SELECT boyfriend_id
FROM t_mysql_beauty
)

#exists
SELECT bo.*
FROM t_mysql_boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM t_mysql_beauty b
WHERE bo.id=b.boyfriend_id

);

---

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;

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

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

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

size=10
page  
1	0
2  	10
3	20

*/
#案例1：查询前五条员工信息
SELECT * FROM t_mysql_employees LIMIT 0,5;
SELECT * FROM t_mysql_employees LIMIT 5;

#案例2：查询第11条——第25条
SELECT * FROM t_mysql_employees LIMIT 10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT
*
FROM
t_mysql_employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;

---

总结

今天的内容都是文字 虽然很多 但是都是精华 又是加油的一天！