MysqlDQL(数据查询语言2)

上一篇DQL地址： DQL(1)

分组查询

1.概念

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

2.聚合函数

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

分类：

sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

3.使用

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;

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;

分组函数

#查询员工表中的最大入职时间和最小入职时间的相差天数 （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');

分组查询

语法

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

特点:

1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
		针对的表			位置		连接的关键字
分组前筛选	原始表				group by前	where
	
分组后筛选	group by后的结果集    		group by后	having

案例

#查询邮箱中包含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;

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

#查询所有部门的编号，员工数量和工资平均值,并按平均工资降序（Desc）
SELECT department_id,COUNT(*),AVG(salary) a
FROM t_mysql_employees
GROUP BY department_id
ORDER BY a DESC;

多表查询

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

笛卡尔集

数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

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

– 省略连接条件

– 连接条件无效

– 所有表中的所有行互相连接

• 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件。

区分重复的列名:

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

连接多个表:

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

连接查询案例

所需表：

等值连接

#查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;


#查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资(is not null:不为空 )
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 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;

非等值连接

#查询员工的工资和工资级别

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

join链接

分类：

内连接 [inner] join on

– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on

ON:子句创建连接

自然连接中是以具有相同名字的列为连接条件的。

可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。

语法

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

内连接

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

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

案例：

#查询员工名、部门名

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

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


非等值链接
#查询员工的工资级别

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

外链接

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

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

左连接以左表为基表，查询左表所有数据以及关联的右表数据，如果右表没有关联数据以null补齐。
右连接以右表为基表，查询右表所有数据以及关联的左表数据，如果左表没有关联数据以null补齐。

案例：

#查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
SELECT b.id,b.name,bo.*
FROM t_mysql_beauty b
LEFT OUTER JOIN t_mysql_boys bo
ON b.`boyfriend_id` = bo.`id`
WHERE b.`id`>3;

#查询部门名为SAL或IT的员工信息

SELECT e.*,d.department_name,d.`department_id`
FROM t_mysql_departments  d
LEFT JOIN t_mysql_employees e
ON d.`department_id` = e.`department_id`
WHERE d.`department_name` IN('SAL','IT');

查询哪个部门没有员工
 #左外
 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;

常见函数

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

日期函数

作用	函数	结果
获取当前日期	now()
将日期格式的字符转换成指定格式的日期	STR_TO_DATE('9-13-1999','%m-%d-%Y')	1999-09-13
将日期转换成字符	DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)	2018年06月06日

示例

#将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM t_mysql_employees;

#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_pu

#姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM t_mysql_employees;

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL;