mysql中的其他知识点

聚合函数
mysql提供了五种聚合函数，max():最大值 min()最小值 sum()求和 avg()平均值 count()个数
例子：

-- 求老师表中最大年龄 最小年龄 年龄和  平均年龄 老师的个数
select max(age),min(age),sum(age),avg(age),count(id) from tbl_teacher;

分组查询
在sql中有个 group by 语句 将某一列相同数据 视为一组 然后进行查询 与聚合函数连用。

例子: 查询各个部门中最高薪资，最低工资，
select deptno, max(sal),min(sal),avg(sal) ,sum(sal) from emp group by deptno;
注意:如果使用group by 那么select后面只能跟聚合函数和分组的字段名
having: 表示分组后的条件。

例子: 查询各个部门中最高薪资且要求最高薪资不能少于3000的部门
select deptno, max(sal) from emp group by deptno having max(sal)>=3000;
** mysql的查询语句执行顺序**

sql语法:
      select distinct *  
       from 表名 
       where 条件
       group by 分组
       having 分组条件
       order by 排序
       limit 分页。
 
上面为他得语法结构。顺序不能乱。 

mysql执行引擎:执行得顺序。
1、from子句识别查询表的数据；

2、where子句基于指定的条件对记录进行筛选；

3、group by 子句将数据划分成多个组别，如按性别男、女分组；

4、有聚合函数时，要使用聚集函数进行数据计算；

5、Having子句筛选满足第二条件的数据；

6、执行select语句进行字段筛选

7、筛选重复数据；

8、对数据进行排序；

9、执行limit进行结果限定

验证执行顺序

-- 因为where的执行顺序高于select,再执行where时select并没有编译，where中不识别别名nl
select id,name,phone,age nl from tbl_teacher where nl>22;
-- 因为order by的执行顺序在select后，所有order by后可以跟别名
select id,name,phone,age nl from tbl_teacher order by nl;

-- where的执行顺序高于聚合函数，当where执行时 聚合函数并没有执行
select max(age) from tbl_teacher where max(age)>25 group by sex;

-- 因为having的执行顺序低于聚合函数，当having执行时聚合函数一定编译过了
select max(age) from tbl_teacher group by sex having max(age)>25;

嵌套查询
1.把一个查询的结果作为另一个查询的条件值。

例子:

查询研发部的员工信息。

[1]条件研发部---部门表

[2]查询的内容---员工表。

-- 根据上面查询的部门编号得到对应的员工信息
select * from emp where deptno=(select  deptno from dept where dname='研发部');

-- 子查询先执行--在执行外部查询。

例子:查询比SCOTT入职早的员工信息。

select * from emp where hiredate<(select HIREDATE from emp where ename='SCOTT');

例子: 查找员工表中最高薪资的员工信息。

-- 例子: 查找员工表中最高薪资的员工信息。
  -- [1]查询所有员工中最高的薪资
select max(sal) from emp;
  -- [2]根据上面查询的薪资值 作为查询的条件
  select * from emp where sal=(select max(sal) from emp);

联表查询
当我们查询的结果在n张表中，这时就需要联表查询。

查询员工信息以及对应的部门信息。
1.联表查询的种类
第一种: 内连接查询。

第二种: 外连接查询。
左外连接:
左外语法: select * from A left join B on 联表条件
右外连接:
右外语法: select * from A right join B on 联表条件
例子:

查询员工信息以及该员工对应的部门信息；

分析: 查询的字段不在一张表中，

-- 查询员工信息以及该员工对应的部门信息；如果联表查询时没有使用联表条件则出现笛卡儿积。A记录数*B记录数
select * from emp join dept on emp.DEPTNO=dept.deptno;

-- 查询所有员工以及员工对应的部门信息---没有部门的员工也查询出来
select * from emp left join dept on emp.DEPTNO=dept.DEPTNO;

-- 右链接: 把右表中所有数据查询出来，以及交集数据
select * from emp right join dept on emp.DEPTNO=dept.DEPTNO;

自联接
自联接: 自己连接自己。
例1：返回员工和所属经理的姓名。
select yg.ename 员工姓名,ld.ename 领导姓名 from emp yg join emp ld on yg.MGR=ld.empno
例2：返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
select yg.ename 员工姓名,ld.ename 领导姓名 from emp yg join emp ld on yg.MGR=ld.empno where yg.hiredate<ld.hiredate
把查询的结果当作一张表-临时表
例子：返回部门号、部门名、部门所在位置及其每个部门的员工总数。

-- #13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
  -- 根据部门编号分组查询每个部门中的人数。
select * from dept join
 (select deptno,count(empno) from emp group by deptno) t  -- 把该行查询的结果作为临时表t
on dept.deptno=t.deptno

mysql常用的函数
mysql自带的函数方法。
字符串函数
专门用于操作字符串

-- concat(_s1,s2,...):字符串拼接
select concat('你好',ename,'今天吃饭码') from emp;

-- lower() 转换为小写
select lower(ename), ename from emp;

-- LPAD()左填充
select lpad(ename,10,'A'),ename,lpad(ename,2,'B') from emp;

-- trim()去除左右空格
select trim(ename),ename from emp;

-- substring()截取字符串
select substring(ename,2,3),ename from emp;

-- char_length求字符串的长度
select char_length(ename),ename from emp;

数字函数

select ceil(15.0),floor(15.9),mod(10,3),abs(-150) from dept;
日期时间函数

-- curdate()返回日期  curtime()返回时间  now()日期和时间
select curdate(),curtime(),now() from dept;

-- year()  month() day()
select year(now()),month('2022-10-11'), day(hiredate),hiredate from emp;

-- date_add(date,INTERVAL expr type):
select date_add(hiredate,INTERVAL 3 year),HIREDATE from emp;

-- datediff(date1,date2): 表示两个日期的差
select datediff(now(),hiredate),HIREDATE from emp;

-- 查询入职天数大于30天的员工。
select * from emp where datediff(now(),hiredate)>30;

-- 根据单位计算两个日期的差
select timestampdiff(hour ,HIREDATE,now()),HIREDATE from emp

流程函数

-- IF(value,t,f): 判断value如果为true，则返回t的值，否则返回f的值
select if(COMM is null, 0, comm), emp.*
from emp;

-- IFNULL(value1,value2):如果value1为空，则返回value2的值，否则返回value1的值
select ifnull(COMM, 0), emp.*
from emp;

-- case
select ename,
       sal,
       case
           when sal >= 800 and sal < 1500 then '低薪'
           when sal >= 1500 and sal < 3000 then '中薪'
           when sal < 800 then '低保'
           else '高薪'
           end AS 薪资水平
from emp;

select ename,
       hiredate,
       case month(HIREDATE)
           when 1 then '31天'
           when 3 then '31天'
           when 5 then '31天'
           when 2 then '28天'
           else '30天'
end
from emp;

设置允许mysql远程连接
写项目期间，组员需要连接一个人的数据库。 默认mysql不允许其他人连接。---设置允许远程连接。

use mysql;
update user set host='%' where user='root';
-- 刷新配置
flush privileges ;