2019-08-22 函数

-- 函数
-- 1.文本函数   CHAR_LENGTH(str)可以返回文本字符的个数
SELECT CHAR_LENGTH('abcdef')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('HHH'),UPPER('dasd') from dual

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

-- TRIM(str)截取首尾空格
SELECT  CHAR_LENGTH(' ABC '),CHAR_LENGTH(TRIM(' der ')) 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 unit)
-- date 日期类型的时间
-- expr 表达式(数字)
-- type year month day hour 

-- 当前时间之后1小时的时间
select NOW(),DATE_ADD(NOW(),INTERVAL 1 HOUR) from DUAL

-- 计算两个日期之间的天数 DATEDIFF(expr1,expr2)
-- 计算员工入职多少天
select ename,hiredate,DATEDIFF(CURDATE(),hiredate)/365 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(*),AVG(sal),SUM(sal) from emp
SELECT COUNT(ename) 总人数 from emp
SELECT COUNT(comm) from emp
-- 计算所有员工的平均月薪
SELECT AVG(sal+IFNULL(comm,0)) from emp
-- 分组与多值的列ename是不能够混合使用的一定要使用GROUP BY 进行分组
SELECT ename,AVG(sal+IFNULL(comm,0)) from emp -- 错的

-- 如何分组 SELECT...FROM....WHERE.....GROUP BY....ORDER BY
-- 查询每个部门的平均工资
SELECT deptno,AVG(sal) from emp WHERE deptno is not null GROUP BY deptno 

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

-- 分组函数条件的筛选 having 
-- 编写：SELECT...FROM....WHERE.....GROUP BY......having.....ORDER BY
-- 执行：from...WHERE....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,grade where student.sid = grade.sid AND student.sid = 1 GROUP BY sname
SELECT sname,SUM(sgrade) from student join grade on student.sid = grade.sid where student.sid = 1 GROUP BY sname
-- 查询一班学生的学号，姓名，总成绩
select s.sid,s.sname,SUM(g.sgrade) from student s,grade g where s.sid = g.sid and s.sclass ='一班' GROUP BY s.sid,s.sname
SELECT s.sid,s.sname,sum(g.sgrade) from student s join grade g on s.sid = g.sid where s.sclass = '一班' GROUP BY s.sid,s.sname          
-- 查询平均分大于85分，总分大于170分的学生的学号，姓名，班级
select s.sid,s.sname,s.sclass,SUM(g.sgrade),AVG(g.sgrade) from student s,grade g where s.sid = g.sid GROUP BY s.sid,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 * 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 > ALL(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 

1）查询张三的同班同学的信息
select sclass from student where sname = '张三'
select * from student where sclass = (select sclass from student where sname = '张三') and sname <> '张三'
2.查询总成绩比张三总成绩高的学生的学号，姓名，总成绩
select SUM(g.sgrade) from student s,grade g where s.sid = g.sid and s.sname='张三'
select s.sid,s.sname,SUM(g.sgrade) from student s,grade g where s.sid = g.sid group by s.sid,s.sname HAVING SUM(g.sgrade)>(select SUM(g.sgrade) from student s,grade g where s.sid = g.sid and s.sname='张三')
3.查询一班总成绩最高的同学的学号，姓名，总成绩
select SUM(g.sgrade) from student s,grade g where s.sid = g.sid and s.sclass = '一班'group by s.sid,s.sname
SELECT s.sid,s.sname,sum(g.sgrade),s.sclass from student s,grade g where s.sid = g.sid group by s.sid,s.sname HAVING SUM(g.sgrade)>=ALL(select SUM(g.sgrade) from student s,grade g where s.sid = g.sid and s.sclass = '一班'group by s.sid,s.sname)

4.查询总成绩比3号同学总成绩高的人的姓名
SELECT SUM(sgrade) from grade g where sid = 3
select student.sname ,sum(sgrade) from student ,grade where student.sid=grade.sid group by student.sname 
    having sum(sgrade)>(select sum(sgrade) from grade where sid=3)