MySQL 数据库 day-03

三、常见函数

函数概念：将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节
　　　2、提高代码的重用性
调用：select 函数名(实参列表)[from 表]；
特点： 1、函数名
　　　2、函数功能
分类：　 1、单行函数
　　　　①字符函数
　　　　②数学函数
　　　　③日期函数
　　　　④其他函数
　　　　⑤流程控制函数
　　　　　如 concat、length、ifnull 等
　　　　2、分组函数
　　　　　功能：做统计使用，又称为统计函数、聚合函数、组函数

1、字符函数

① length:获取参数值的字节个数

SELECT LENGTH('john');           #4
SELECT LENGTH('张三丰hahaha');   #15  汉字占3个字节

② concat:拼接字符串

SELECT	CONCAT(last_name,'_',first_name) 姓名 FROM employees;

③ upper、lower:将字母变大写/小写

SELECT UPPER('jogn');
SELECT LOWER('JmOw');	

案例：将姓名变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM employees;	

#④ substr、substring：截取字符
注意：索引都是从1开始

#截取从指定索引处开始后面的所有字符
SELECT SUBSTR('它是MySQL呀',4) out_put;
# 截取从指定索引处指定字符长度的字符
SELECT SUBSTR('它是MySQL呀',3,5) AS out_put;

案例：姓名中首字符大写，其他字符小写，然后用_拼接

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

⑤ instr:返回子串第一次出现的索引，如果找不到则返回0

SELECT INSTR('java和mysql','mysql') AS out_put    #6

⑥ trim:移除字符串两侧的空白字符(默认)或其他预定义字符

SELECT LENGTH('   myqsql   ')			  #12
SELECT LENGTH(TRIM('   mysql   ')) AS out_put;    #5
SELECT TRIM('a' FROM('aaaaaMYSaaalaaa')) AS out_put;  #MYSaaal

⑦ lpad:用指定的字符实现左填充指定长度 最终长度为指定长度 超过指定长度从右边截取
rpad:用指定的字符实现右填充指定长度 最终长度为指定长度 超过指定长度从右边截取

SELECT LPAD('MYsql',10,'*') AS out_put;
SELECT LPAD('MYsql',3,'*') AS out_put;
SELECT RPAD('MYsql',3,'*') AS out_put;

⑧ repalce:将指定字符替换为指定字符

SELECT REPLACE('我爱java','java','mysel') out_put;

2、数学函数

① round:四舍五入

SELECT ROUND(1.65);   #2
SELECT ROUND(1.49);   #1
SELECT ROUND(-2.59);  #-3
SELECT ROUND(1.5978,3); #1.598

② ceil:向上取整,返回不小于该参数的最小整数

SELECT CEIL(1.0002);  #2
SELECT CEIL(-3.58);   #-3

③ floor:向下取整,返回不大于该参数的最小整数

SELECT FLOOR(9.56);   #10
SELECT FLOOR(-9.90);   #-10

④** truncate:截断,保留指定小数**

SELECT TRUNCATE(1.6999,1);   #1.6

⑤ mod取余

MOD(a,b); #结果：a-a/b*b
	  #相当于%
SELECT MOD(10,3);   #1
SELECT MOD(10,-3);  #1
SELECT MOD(-10,-3); #-1
SELECT MOD(-10,3);  #-1 

3、日期函数

now:返回当前系统日期+时间

SELECT NOW();

curdate:返回当前系统日期,不返回时间

SELECT CURDATE();

curtime:返回当前系统时间,不返回日期

SELECT CURTIME();

可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;              #2021
SELECT MONTH('1998-10-1') 月;       #10
SELECT MONTHNAME('1997-10-5') 月;   #OCtober
SELECT YEAR(hiredate) 年 FROM employees;

str_to_date:将字符串通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;  #1998-03-02

#查询入职日期为 1992-4-3 的员工信息

SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');

#date_format 将日期转换成字符

#查询有奖金的员工名和入职日期（**月/**日 **年）

SELECT  last_name,
	DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 日期
FROM	employees
WHERE   commission_pct IS NOT NULL;

4、其他函数

SELECT VERSION();   #当前mysql版本
SELECT DATABASE();  #查看当前库
SELECT USER();	    #当前用户

5、流程控制函数

① if函数：if else 的效果

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

SELECT  last_name,commission_pct,IF(commission_pct IS NULL,'无奖金','有奖金')
FROM 	employees;

② case函数：
使用一：switch case的效果
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end

案例：查询员工的工资,要求
部门号=30,工资为1.1倍
40， 1.2
50 1.3
否则 1

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

使用二：类似于 多重if
语法：
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 employees;

四、分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类： sum 求和
　　　 avg 平均值
　　　 max 最大值
　　　 min 最小值
　　　count 计算个数
特点：
　　 1、sum avg 只用来处理数值型
　　　 max min count 可以用来处理所有类型
　　2、以上分组函数都忽略null值
　　3、和distinct搭配
　　4、count(*) 一般用来统计行数
　　5、和分组函数一同查询的字段要求是group by后的字段

1、简单使用

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

2、和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

3、count函数的详细介绍
　　count() 一般用来统计行数*

SELECT COUNT(*) FROM employees;    #107  统计行数
SELECT COUNT(1) FROM employees;    #107  统计1的个数 即统计行数

4、和分组函数一同查询的字段有限制

SELECT AVG(salary),employee_id FROM employees;  #报错

五、分组查询

语法：
　　select 分组函数,列(要求出现在group by的后面)
　　from 表
　　 [where 筛选条件]
　　 group by 分组的列表
　　[order by 子句]
注意:
　　查询列表必须特殊,要求是分组函数和group by后出现的字段
特点：
1、分组查询终的筛选条件分为两类
　　　　　　　数据源　　　　　　 位置 　　　　　　　　关键字
　分组前筛选　原始表 　　　　　 group by子句的前面 　　 where
　分组后筛选　分组后的结果表 　group by子句的后面 　　　having

①分组条件做条件肯定是放在having子句中
②能用分组前筛选的，就优先考虑分组前筛选

2、group by子句支持单个字段分组、多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式或函数（用的较少）

3、也可以添加排序（排序放在整个分组查询的最后）
#案例:查询每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

案例：查询每个工种的最高工资

SELECT MAX(salary),job_id
FROM	employees
GROUP BY job_id;

案例：查询每个位置上的部门个数

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

添加分组前的筛选条件
案例：查询邮箱终包含a字符的，每个部门的平均工资

SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

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

SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

添加分组后的筛选条件
案例1：查询哪个部门的员工个数>2
①查询每个部门的员工个数
②根据①的结果进行筛选，查询哪个部门的员工个数>2

SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
①查询每个工种有奖金的员工的最高工资
②根据①的结果进行筛选，查询哪个员工的最高工资>12000

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

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号以及其最低工资
①查询领导编号>102的每个领导手下的最低工资

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#①查询每个长度的员工个数
#②查询哪一组的员工个数大于5

SELECT  COUNT(*),LENGTH(last_name) len_name
FROM 	employees
GROUP BY len_name;
HAVING COUNT(*)>5;

按多个字段分组
案例：查询每个部门每个工种的员工的平均工资

SELECT AVG(salary),department_id,job_id
FROM  employees
GROUP BY department_id,job_id;

添加排序
案例：查询部门编号部位mull每个工种的员工平均工资>10000的，并按平均工资从小到大排序

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary)>10000
ORDER BY AVG(salary) DESC;

六、连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1有m行，表2有n行，结果：m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
按年代分类：
sql92标准:仅仅支持内连接
sql99标准[推荐]：支持内连接+外连接(左外+右外)+交叉连接
按功能分类：
内连接：

• 等值连接
• 非等值连接
• 自连接

外连接：

• 左外连接
• 右外连接
• 全外连接
• 交叉连接

SELECT * FROM beauty;

SELECT * FROM boys;

SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

(一)、sq192标准

1、等值连接

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

(1)、简单使用

案例1：查询女神名字和对应的男神名

SELECT NAME,boyName
FROM 	boys,beauty
WHERE 	beauty.boyfriend_id=boys.id;

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

SELECT  last_name,department_name
FROM	employees,departments
WHERE	employees.department_id=departments.`department_id`;

(2)、为表起别名

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

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

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

SELECT last_name,e.job_id,job_title   #这里只能用e.,使用employess.报错
FROM	employees AS e,jobs j
WHERE	e.job_id=j.job_id;

(3)、可以加筛选
案例1：查询有奖金的员工名和部门名

SELECT last_name,department_name,commission_pct
FROM	employees e,departments d
WHERE	e.department_id=d.department_id
AND	e.commission_pct IS NOT NULL;

案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM	departments d,locations l
WHERE	d.location_id=l.location_id
AND	city LIKE '_o%';

(4)、加分组

案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM	departments d,locations l
WHERE	d.location_id=l.location_id
GROUP BY city;

案例2：查询有奖金的每个部门的部门名和领导编号和该部门的最低工资

SELECT department_name,e.manager_id,MIN(salary)
FROM	departments d,employees e
WHERE	d.department_id=e.department_id
AND	commission_pct	IS NOT NULL
GROUP BY   department_name,d.manager_id;

(5)、加排序

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

SELECT 	job_title,COUNT(*)
FROM 	employees e,jobs j
WHERE	e.job_id=j.job_id
GROUP BY    job_title
ORDER BY    COUNT(*) DESC;

(6)、实现三表查询

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

SELECT last_name department_id,city
FROM	employees e,departments d,location l
WHERE	e.department_id=d.department_id
AND	d.location_id=l.location_id

2、非等值连接

案例1：查询员工的工资和工资级别

SELECT * FROM job_grades;

SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;

3、自连接

案例：查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM	employees e,employees m
WHERE	e.`manager_id`=m.`employee_id`;

(二)、sql99语法

语法：
　　select 查询列表
　　 from 表1 别名 [连接类型]
　　 join 表2 别名 on 连接条件
　　 [where 筛选条件]
　　 [group by 分组]
　　 [order by 排序列表]

特点：连接条件(on)和筛选条件(where)相分离
<92语法放在where后>

内连接(★):　inner
外连接：
　　　　　左外(★):left [outer]
　　　　　右外(★):right [outer]
　　　　　全外:full [outer]
交叉连接:　crose

1、内连接

分类：　
　　　　 等值
　　　　 非等值
　　　　 自连接

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

(1)、等值连接

案例1：查询哪个部门个数>3的部门名和部门个数 并按个数降序

SELECT 		department_name,COUNT(*)
FROM		employees e
INNER JOIN	departments d
ON	e.department_id=d.department_id
GROUP BY  department_name
HAVING	COUNT(*)>3
ORDER BY COUNT(*) DESC;

案例2：查询员工名、部门名、工种名、并按部门名降序(添加三表连接)

SELECT last_name,department_name,job_title
FROM	employees e
INNER JOIN departments d ON e.department_id=d.department_id
INNER JOIN jobs j ON e.job_id=j.job_id
ORDER BY    department_name DESC; 

(2)、非等值连接

案例1：查询员工的工资级别

SELECT salary,grade_level
FROM	employees e
INNER JOIN	job_grades g
ON	e.salary BETWEEN g.lowest_sal AND g.highest_sal;

案例2：查询工资级别个数>20的个数, 并且按工资级别降序

SELECT  COUNT(*),grade_level
FROM	employees e
INNER JOIN	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;

(3)、自连接

案例：查询姓名中包含字符k的员工的名字、上级的名字

SELECT e.last_name,m.last_name
FROM	employees e
JOIN	employees m
ON	e.manager_id=m.employee_id
WHERE  e.last_name LIKE '%k%';

2、外连接

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

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

案例：查询没有男朋友的女神名

#查询的是主要是女神 所以beauty表为主表
USE girls;
#左外连接
SELECT 	b.name
FROM	beauty b
LEFT OUTER JOIN boys bo
ON	b.boyfriend_id=bo.id
WHERE  bo.id IS NULL;

案例1：查询那个部门没有员工

SELECT d.*,employee_id
FROM	departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE	e.employee_id IS NULL;

3、交叉连接

即笛卡尔乘积

SELECT b.*,bo.*
FROM	beauty b
CROSS JOIN boys bo;

(三)、总结：连接查询

1、sql92 与 sql99 比较

功能：sql99支持的功能较多
可能性：sql99实现连接条件和筛选条件的分离,可读性较高
推荐使用sql99标准

2、图形解决 内连接、外连接以及交叉连接

(1)、左外连接

(2)、右外连接

(3)、内连接

即 取交集

(4)、

(5)、

(6)、交叉连接

(7)