-- 函數(shù)
-- 1.文本函數(shù)
-- CHAR_LENGTH(str)可以返回文本字符個數(shù)
select CHAR_LENGTH('abcdefgh') from dual
select ename,CHAR_LENGTH(ename) from emp
-- CONCAT(str1,str2,...) 可以將參數(shù)合并成一個字符串
select ename,job,CONCAT(ename,'職位是',job) '某人的職位是' from emp
-- 轉(zhuǎn)換大小寫 LOWER(str)轉(zhuǎn)小寫 UPPER(str)轉(zhuǎn)大寫
select LOWER('HHHH'),UPPER('abcdef') from dual
-- SUBSTR(str,pos,len) str就是要揭取的原字符串 POS 從第幾個字符開始截取
-- len截取的長度(從1開始)
select ename,SUBSTR(ename,2,3) from emp
-- TRIM(str)截取首尾空格
select CHAR_LENGTH(' abc '),CHAR_LENGTH(TRIM(' def ')) from dual
-- MOD(N,M) 取余
select MOD(5,3) from dual
-- 時間函數(shù)
-- CURDATE() 獲取當(dāng)前日期 年月日
-- CURTIME() 獲取當(dāng)前時間 小時分秒
-- NOW() 獲取當(dāng)前的日期和時間
select CURDATE(),CURTIME(),NOW() from dual
-- 追加時間
-- DATE_ADD(date,INTERVAL expr unit)
-- date：當(dāng)前時間（日期類型的時間） INTERVAL expr
-- INTERVAL expr：表達(dá)式（數(shù)字）
-- type year month day hour
-- 當(dāng)前時間后一小時
select now(),DATE_ADD(NOW(),INTERVAL 1 hour) from dual
-- 計算兩個日期間的天數(shù) DATEDIFF(expr1,expr2)
-- 計算員工入職的天數(shù)
select ename,hiredate,DATEDIFF(CURDATE(),hiredate)/365 from emp
-- 計算日期所在月份的最后一天 LAST_DAY(date)
select ename,hiredate,LAST_DAY(hiredate) from emp
-- 分組函數(shù)（重要的）：將多行數(shù)據(jù)統(tǒng)計為最后一個結(jié)果
-- MAX(expr)求最大值
-- MIN(expr)求最小值
-- COUNT(expr)求表中總記錄數(shù)（總?cè)藬?shù)）COUNT(*)表中所有的記錄
-- AVG([DISTINCT] expr)求平均值
-- SUM(expr)求總和
select * from emp
select MAX(sal),MIN(sal),COUNT(*),AVG(sal),SUM(sal) from emp
select COUNT(ename) 總?cè)藬?shù) from emp
select COUNT(comm) from emp
-- 計算所有員工的平均月薪
select AVG(sal+IFNULL(comm,0)) from emp
-- 分組函數(shù)與多值的列不能混合使用，否則一定要使用GROUP BY進(jìn)行分組
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
-- group by 后面可以按多列進(jìn)行分組
select deptno,job,AVG(sal) from emp group by deptno,job
-- 分組函數(shù)條件的篩選 having
-- 編寫語法：select....FROM....WHERE.....group BY.....HAVING....order by
-- 執(zhí)行語句：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號同學(xué)的姓名和總成績（兩種方法）
select sname,SUM(sgrade) from student,grade where student.sid=grade.sid and student.sid=1 group by sname
-- 查詢一班學(xué)生的學(xué)號。姓名，總成績
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 inner join grade g on s.sid=g.sid and s.sclass='一班' group by s.sid,s.sname
-- 查詢平均分大于85，總分大于170的學(xué)生學(xué)號，姓名，班級
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
-- 子查詢（嵌套查詢）:
-- 在查詢的邏輯中，引入另一個查詢作為條件或數(shù)據(jù)的來源
-- 查詢與TOM在同一個部門工作的員工信息
-- 1.查詢TOM所在的部門編號（子查詢）
-- 2.查詢與TOM在同一個部門工作的其他員工信息（主查詢：會利用子查詢的結(jié)果）
select * from emp
select deptno from emp where ename='TOM' （子查詢）
select * from emp where deptno=(select deptno from emp where ename='TOM') and ename<>'TOM'
-- 子查詢出現(xiàn)的位置
-- 1.可以放在from后面，可以將子查詢當(dāng)成一張臨時表來處理
select t.* from (select * from emp) t(在子查詢中寫多列)
-- 2.可以出現(xiàn)在where后面
-- 3.可以出現(xiàn)在having后面
-- 通常都是寫在where和from后面
-- 子查詢分為：單行子查詢和多行子查詢
-- 單行子查詢：單行子查詢查詢到的結(jié)果為一行一列的數(shù)據(jù)(簡單的數(shù)據(jù))
-- 可以使用單行比較運(yùn)算符（> < >= <= !=）
-- 查詢比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'
-- 多行子查詢：子查詢返回的結(jié)果是多行一列的數(shù)據(jù)
-- 多行子查詢使用多行比較運(yùn)算符（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)
-- 查詢不是經(jīng)理的員工的信息(注意 新或 not in 數(shù)據(jù)不能有null值 相當(dāng)于執(zhí)行了=null的操作，查詢不到結(jié)果)
select job from emp where job<>'MANAGER' and job is not null
select * from emp where job in (select job from emp where job<>'MANAGER' and job is not null)
-- 多行比較運(yùn)算符（ANY任何一個 ALL所有的）
-- < ANY 比最大的小 >ANY 比最小值大 =ANY相當(dāng)于in
-- < 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 后也可以寫子查詢（表）
-- 查詢部門編號，部門名成，部門所在地址，部門人數(shù)
-- 1.
select * from dept
select * from emp
select e.deptno,t.dname,t.loc,COUNT(e.ename) '部門人數(shù)' from (select * from dept) t,emp e where e.deptno=t.deptno group by e.deptno
select e.deptno,t.dname,t.loc,COUNT(e.ename) '部門人數(shù)' from (select * from dept) t left join emp e on e.deptno=t.deptno group by e.deptno
-- 1.查詢每個部門的編號及人數(shù)（顯示結(jié)果為多行多列的數(shù)據(jù)，所以將子查詢但做一張表來處理）
select deptno,COUNT(ename) 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）查詢張三的同班同學(xué)的信息
select * from student
select * from grade
select sclass from student where sname='張三'
select * from student where sclass=(select sclass from student where sname='張三')
-- 2.查詢總成績比張三總成績高的學(xué)生的學(xué)號，姓名，總成績
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.查詢一班總成績最高的同學(xué)的學(xué)號，姓名，總成績
select sum(g.sgrade) from student s,grade g where s.sid=g.sid and s.sclass='一班' group by g.sid
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)>=all(select sum(g.sgrade) from student s,grade g where s.sid=g.sid and s.sclass='一班' group by g.sid)
-- 4.查詢總成績比3號同學(xué)總成績高的人的姓名
select sum(g.sgrade) from student s,grade g where s.sid=g.sid and s.sid=3
select s.sname from student s,grade g where s.sid=g.sid group by s.sname having sum(g.sgrade)>
(select sum(g.sgrade) from student s,grade g where s.sid=g.sid and s.sid=3)
