-- 函数
-- 1. 文本函数 char_length(str)可以返回文本字符的个数
select char_length('adsfdsf')from dual
select ename,char_length(ename) from emp

-- concat(str1,str2...) 将参数合并成一个字符串
select ename,job,concat(ename,'的职位是',job) from emp

-- 转换大小写 lower(str)转小写 upper(str)转大写
select lower('HHHH'),upper('dsf') from dual

-- substr(str,pos,len) str就是我们要截取的原字符串 pos从第几个字符开始截取
-- len表示截取length的长度
select ename,substr(ename,2,3) from emp

-- trim(str)截取首尾空格
select char_length(' asd '),char_length(trim('sdf')) from dual

-- mod(n,m)取余
select mod(5,3) from dual

-- 时间函数
-- curdate()获取当前日期 年 月 日
-- curtime()获取当前时间 小时 分 秒
-- now() 获取当前的日期和时间
select curdate(),curtime(),now() from dual

-- 追加时间
-- date_add(date,interval expr type)
-- date 日期类型的时间
-- expr 表达式（数字）
-- type year month day hour

-- 当前时间之后一小时的时间
select now(),date_add(now(),interval 1 hour) from dual

-- 计算两个日期之间的天数 datediff(expr1,expr2)
-- 计算员工入职多少天
select ename,hiredate,datediff(curdate(),hiredate) from emp

-- 计算日期所在的最后一天 LAST_DAY(date)
select ename,hiredate,LAST_DAY(hiredate) from emp

-- 分组函数（重要的）：将多行数据统计成一个最后的结果
-- MAX(expr)求最大值
-- MIN(expr)求最小值
-- COUNT(expr)求表中总记录数（总人数） count(*)表中所有的记录
-- AVG([DISTINCT] expr)求平均值
-- SUM(expr)求总和

select max(sal),min(sal),count(ename),AVG(sal),sum(sal) from emp

select count(ename) 总人数 from emp

select count(comm) from emp

-- 计算所有员工的平均月薪
select avg(sal+ifnull(comm,0)) from emp

-- 分组函数与多值的列是不能够混合使用的一定要用group by 进行分组
select ename,avg(sal+ifnull(comm,0)) from emp

-- 如何分组 select....FROM....WHERE......group BY.....order by
-- 查询每一个部门的平均工资
select deptno,AVG(sal) from emp GROUP BY deptno order by deptno desc

-- group by 后面可以按多列进行分组
select * from emp
select deptno,job,AVG(sal) from emp GROUP BY deptno,job order by deptno desc

-- 分组函数条件的啊筛选 having
-- 编写顺序：select....FROM....WHERE......group BY......having.....order by
-- 执行顺序：from.....WHERE..ALTER..group BY....HAVING.....SELECT....order by
-- 查询平均工资高于4000的部门的编号，平均工资
select deptno,AVG(sal) from emp group by deptno having AVG(sal)>4000

select * from student
select * from grade
select * from lession

-- 查询1号同学的姓名和总成绩(两种方法发)
select sname,sum(sgrade) 总成绩
from student s join grade g on s.sid=g.sid
where s.sid=1
group by s.sname

-- 查询一班学生的学号、姓名、总成绩
select s.sid,s.sname,sum(sgrade)
from student s join grade g on s.sid=g.sid
where s.sclass='一班'
group by s.sname

-- 查询平均分大于85，总分大于170分的学生学号，姓名，班级
select s.sid,s.sname,s.sclass
from student s join grade g on s.sid=g.sid
group by s.sname,s.sname,s.sclass
having avg(g.sgrade)>85 and sum(g.sgrade)>170

-- 子查询（嵌套查询）：
-- 在查询的逻辑当中，需要引入另一个查询作为条件或是数据的来源
-- 查询与tom在同一个部门工作的其他员工信息
-- 1.查询tom所在部门的编号(子查询)
-- 2.查询与tom在同一个部门工作的其他员工信息（主查询：会利用子查询的结果）
select * from emp
where deptno=(select deptno from emp where ename='TOM') and ename<>'TOM'

-- 子查询出现的位置（可以写在很多语句当中）
-- 1.可以出现在from的后面，可以将子查询当成一张临时表处理
select t.* from (select * from emp) t
-- 2.可以出现在where后面
-- 3.可以出现在having后面
-- 通常来说都是写在from和where后面

-- 子查询分为：单行子查询和多行子查询
-- 单行子查询：单行子查询查询到的结果为一行一咧的数据(简单的数据)
-- 可以使用单行比较运算符（< > <= >= !=）
-- 查询比tom工资高的信息
-- 1.查询出tom的工资 2.查询工资比tom高的员工的信息
select sal from emp where ename='TOM'
select * from emp
where sal>(select sal from emp where ename='TOM')

-- 2. 查询与LEE同一职位同一部门的其他员工的信息
-- 查询出LEE所在的职位
-- 查询出LEE所在的部门
select job from emp where ename='LEE'
select deptno from emp where ename='LEE'

select * from emp
where job=(select job from emp where ename='LEE')
and deptno=(select deptno from emp where ename='LEE')
and ename<>'LEE'

-- 多行子查询：子查询返回的结果是多行一列的数据
-- 多行子查询使用多行比较运算符（in）
select distinct mgr from emp where mgr is not null

select * from emp
where empno in (select distinct mgr from emp where mgr is not null)

-- 查询不是经理的员工的信息
-- (注意 in或者not in 里面的数据不能是null值 相当于执行了=null操作，查询不到结果)
select * from emp
where empno not in (select distinct mgr from emp where mgr is not null)

-- 多行比较运算符（ANY任何一个 ALL所有的）
-- <any 比最大的小 >ANY 比最小的大 =ANY 相当于 IN
-- <all 比最小的小 >all 比最大的大 没有=all 没有实际意义

-- 查询比10号部门所有人工资都要高的员工的信息
-- 1.先要查询出10号部门的工资
select sal from emp where deptno=10

select * from emp
where sal > (select MAX(sal) from emp where deptno=10)

-- from 后面也可以写子查询（表）
-- 查询部门的编号，部门的名称，部门所在的位置（loc），部门的人数

-- 1.查询每个部门的编号以及每个部门的人数(结果显示多行多列，所以将子查询当做一张表来处理)
select deptno,count(ename) empnum from emp WHERE
deptno is not null group by deptno

select d.deptno,d.dname,d.loc,ifnull(t.empnum,0)
from dept d left join (
select deptno,count(ename) empnum from emp WHERE
deptno is not null group by deptno
) t on d.deptno=t.deptno

select * from dept
select * from emp

select d.deptno,d.dname,d.loc,count(e.ename)
from dept d,emp e
where d.deptno= e.deptno
group by d.deptno,d.dname,d.loc