与MySQL相识的第N天

        欢迎收听，MySQL学习日记，前面我们讲到了排序查询，接下来继续我们的进阶！加油奥利给！冲冲冲鸭，让我们直接步入正题！

常见函数

一、概述

二、单行函数

1.字符函数

2、数学函数

3、日期函数

4、其他函数

5、流程控制函数

三、分组函数

1、分类

2、特点

分组查询

一、语法

二、特点

连接查询

一、含义

二、分类

按年代分类：

三、SQL92语法

1、等值连接

2、非等值连接

四、SQL99语法

1、内连接

2、外连接

3、交叉连接

常见函数

一、概述

功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select 函数名(实参列表);

调用： select 函数名（实参列表）【from 表】;
    特点：
        ①：叫什么（函数名）
        ②：干什么（函数功能）
    分类：
        1、单行函数
        如concat、length、ifnull等
        2、分组函数
    功能：做统计使用、又称为统计函数、聚合函数、

二、单行函数

1.字符函数

concat:连接
substr:截取子串
upper:变大写
lower：变小写
replace：替换
length：获取字节长度
trim:去前后空格
lpad：左填充
rpad：右填充
instr:获取子串第一次出现的索引

#一、字符函数
#1.length
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

SHOW VARIABLES LIKE '%char%';

#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');

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

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

#substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',6) out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
#案例： 姓名中的首字母大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(last_name)) FROM employees;

#5.instr 返回子串第一次出现的索引， 如果找不到返回0;
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠')AS out_put; 

#6.trim
SELECT LENGTH(TRIM('     张翠山   ')) AS out_put;

SELECT TRIM('a' FROM 'aaaaaa张aaaaa翠aaaaa山aaaaa')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;

2、数学函数

ceil:向上取整
round：四舍五入
mod:取模
floor：向下取整
truncate:截断
rand:获取随机数，返回0-1之间的小数

#二、数学函数
#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;

3、日期函数

now：返回当前日期+时间
year:返回年
month：返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime：返回当前时间
hour:小时
minute:分钟
second：秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月

#三、日期函数

#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间，不包含日期;
SELECT CURTIME();

#可以获取指定的部分，年，月，日、小时、分钟、秒
SELECT YEAR(NOW()) 年;

SELECT YEAR('1998-1-1') 年;

SELECT YEAR(hiredate) 年 FROM 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 employees WHERE commission_pct IS NOT NULL;

4、其他函数

version 当前数据库服务器的版本
database 当前打开的数据库
user当前用户
password('字符')：返回该字符的密码形式
md5('字符'):返回该字符的md5加密形式

其他函数我们作为了解，这里就不上例子哦

5、流程控制函数

①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
②case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end

③case情况2
case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end

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

SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL, '没奖金','哈哈哈有奖金') 备注 FROM 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 employees;

#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


*/

三、分组函数

1、分类

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

2、特点

①语法
select max(字段) from 表名;

②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型

③以上分组函数都忽略null
④都可以搭配distinct使用，实现去重的统计
select sum(distinct 字段) from 表;
⑤count函数
count(字段)：统计该字段非空值的个数
count(*):统计结果集的行数

下面放上实际操作的案例！

#1.简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

#2.参数支持哪些类型
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;

#3.忽略null
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;


#4.和distinct搭配   (去重)

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

#5.count函数的详细介绍

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

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

#6.和分组函数一同查询的字段有限制

# 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）；
MAX(hiredate) MIN(hiredate)

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

#3.查询部门编号为90的员工个数
SELECT COUNT(1) FROM employees WHERE department_id=90;

        看完了激情的函数，是不是觉得 函数里面的东西，感觉都很熟悉啊，不管是什么类型的函数，总有一种似曾相识的感觉！

分组查询

一、语法

select 分组函数，分组后的字段
from 表
        【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

注意：
    查询列表必须特殊，要求是分组函数和group by后出现的字段

二、特点

    1、分组查询中的筛选条件分为两类

                    使用关键字      筛选的表                    位置
分组前筛选    where            原始表                group by的前面
分组后筛选    having        分组后的结果         group by 的后面

    ①分组函数做条件肯定是放在having子句中
    ②能用分组前筛选的，就优先考虑使用分组前筛选
    
    2、group by子句支持单个字段分组，多个字段分组（多个字段之间用,逗号隔开没有顺序要求）,表达式或函数（用的比较少）
    
    3、也可以添加排序（排序放在整个分组查询的最后）

为了方便观察其用法，下面直接上代码，简单明了！

#引入： 查询每个部门的平均工资
SELECT AVG(salary) FROM employees;

#案例1: 查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#添加分组前的筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的额平均工资
SELECT AVG(salary),department_id,email FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id
ORDER BY salary;

#添加分组后的筛选条件
#案例1：查询哪个部门的员工个数>2
#①：查询每个部门的员工个数
SELECT COUNT(*),department_id 
FROM employees
GROUP BY department_id;

#②根据①的结果进行筛选，查询哪个部门的员工个数>2 
SELECT COUNT(*),department_id 
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
 
 #案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
 #①查询每个工种有奖金的员工的最高工资
 
 SELECT MAX(salary),job_id
 FROM employees
 WHERE commission_pct IS NOT NULL
 GROUP BY job_id;
 #②根据①查询每个工种有奖金的员工的最高工资
 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 last_name,salary 工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id; 
#②再根据① 查询其筛选后的  大于5000的工资的领导编号
SELECT last_name,salary 工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;
 
 #按表达式或函数分组
 
 #案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
 
 #①查询每个长度的员工个数
 SELECT COUNT(*),LENGTH(last_name) len_hh
 FROM employees
 GROUP BY len_hh
 #②添加筛选条件
 SELECT COUNT(*),LENGTH(last_name) len_hh
 FROM employees
 GROUP BY len_hh
 HAVING COUNT(*)>5;
 
 #按多个字段分组
 
 #案例：查询每个部门每个工种的员工的平均工资
 SELECT AVG(salary),department_id,job_id
 FROM employees
 GROUP BY department_id,job_id;
 
 #查询所有部门的编号，员工数量和平均工资，并按平均工资降序
 SELECT department_id,COUNT(*),AVG(salary) a
 FROM employees
 GROUP BY department_id
 ORDER BY a DESC;
 
 #5.选择具有各个job_id的员工人数
 SELECT job_id,COUNT(*)
 FROM employees
 WHERE job_id IS NOT NULL
 GROUP BY job_id 

连接查询

一、含义

当查询中涉及到了多个表的字段，需要使用多表连接
select 字段1，字段2
from 表1，表2,...;

笛卡尔乘积：当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件

二、分类

按年代分类：

    sql92：
        等值，非等值 ，自连接

        也支持一部分外连接（用于oracle、sqlserver，mysql不支持）
    sql99【推荐使用】：
        内连接（等值 / 非等值 / 自连接）
        外连接（左外/  右外 / 全外（mysql不支持）
        交叉连接

三、SQL92语法

1、等值连接

语法：
            select 查询列表
            from 表1 别名,表2 别名
            where 表1.key=表2.key
            【and 筛选条件】
            【group by 分组字段】
            【having 分组后的筛选】
            【order by 排序字段】

特点： ① 一般为表起别名
            ②多表的顺序可以调换
            ③n表连接至少需要n-1个连接条件
            ④等值连接的结果是多表的交集部分

2、非等值连接

语法：
            select 查询列表
            from 表1 别名,表2 别名
            where 非等值的连接条件
            【and 筛选条件】
            【group by 分组字段】
            【having 分组后的筛选】
            【order by 排序字段】
3、自连接

语法：
            select 查询列表
            from 表 别名1,表 别名2
            where 等值的连接条件
            【and 筛选条件】
            【group by 分组字段】
            【having 分组后的筛选】
            【order by 排序字段】

四、SQL99语法

1、内连接

语法：
             select 查询列表
             from 表1 别名
           【inner】 join 表2 别名 on 连接条件
             where 筛选条件
             group by 分组列表
             having 分组后的筛选
             order by 排序列表
             limit 子句;

特点：①表的顺序可以调换
           ②内连接的结果=多表的交集
           ③n表连接至少需要n-1个连接条件

分类：
        等值连接
        非等值连接
        自连接

2、外连接

语法：
        select 查询列表
        from 表1 别名
        left  |  right  |   full【outer】 join 表2 别名 on 连接条件
        where 筛选条件
        group by 分组列表
        having 分组后的筛选
        order by 排序列表
l        imit 子句;
特点：
        ①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
        ②left join 左边的就是主表，right join 右边的就是主表
            full join 两边都是主表
        ③一般用于查询除了交集部分的剩余的不匹配的行

3、交叉连接

语法：
        select 查询列表
        from 表1 别名
        cross join 表2 别名;

特点：
        类似于笛卡尔乘积

实际上手代码如下：

#进阶6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表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`;
#一、sql92标准
#1.等值连接
/*
① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-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 e.last_name,e.job_id,j.job_title 
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;

#4、可以加筛选？
#案例：查询有奖金的员工名、部门名
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%";

#5、可以加分组
#案例：查询每个城市的部门个数

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

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name;

#6、可以加排序
#案例： 查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*) 个数
FROM employees e,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 employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

#2、非等值连接

#案例1： 查询员工的工资等级

#3、自连接

#案例1：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id 领导ID,m.last_name 领导名
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;


#一、显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary)
FROM employees
WHERE salary IS NOT NULL;

#二、查询员工表的employee_id，job_id,last_name, 按department_id降序，salary升序
SELECT employee_id,job_id,last_name 
FROM employees
ORDER BY salary ASC,department_id DESC;

#三、查询员工表的job_id中包含a和e的，并且a在e的前面

SELECT job_id FROM jobs
WHERE job_id LIKE '%a%e%';

        今天的学习日记就到此结束啦，此日记也是为了自己以后，可能忘啦就回顾一下！加油加油明天依旧光芒万丈