1.函數(shù)

1.1 單行函數(shù)

數(shù)學(xué)函數(shù)

# 絕對值
abs()
# 向上取整,最接近并且大于等于該值的整數(shù)值
ceiling()/ceil()
# 向下取整,最接近并且小于等于該值的整數(shù)值
floor()
# 取模運(yùn)算(取余)
mode(m,n)
# 求PI值
PI()
# 求冪運(yùn)算
pow(m,n)
# 隨機(jī)數(shù)
rand()  
# 四舍五入
round(m,n)
# 截取m小數(shù)點(diǎn)后n位
truncate(m,n)

字符函數(shù)

# 獲取str的ascii碼值
ascii(str)
# 將字符串轉(zhuǎn)換為小寫
lower(字段|表達(dá)式)
# 將字符串轉(zhuǎn)換為大寫
upper(字段|表達(dá)式)
# 拼接字符串
concat(str1,str2…)
# 獲取字符串長度
length(字段|表達(dá)式)
# 截取字符串,pos開始位置兔甘，從1開始；len表示長度
substr(str,pos,len)
# 在str中搜索old鳞滨，使用new代替
replace(str,old,new)
# str長度不夠len洞焙，使用s左側(cè)填充
lpad(str,len,s)
# str長度不夠len,使用s右側(cè)填充
rpad(str,len,s)
# 去除左右兩側(cè)的空格
trim()

日期函數(shù)

# 獲取當(dāng)前日期時(shí)間
NOW()/SYSDATE()/CURRENT_TIMESTAMP()
# 獲取當(dāng)前系統(tǒng)日期
CURRENT_DATE()/CURDATE()
# 獲取當(dāng)前系統(tǒng)時(shí)間
CURRENT_TIME()/CURTIME()
# 獲取天數(shù)
DAY(date)
# 獲取月份
MONTH(date)
# 獲取年份
YEAR(date)
# 返回一年中的周數(shù)
week(date)
# 返回一周中的第幾天(0-6)
weekday(date)
# 日期計(jì)算
DATE_ADD(date,INTERVAL expr unit)

1.2 聚合函數(shù)

# 平均值
avg()
# 獲取總數(shù)
sum()
# 獲取最大值
max()
# 獲取最小值
min()
# 統(tǒng)計(jì)數(shù)目
count()
統(tǒng)計(jì)記錄數(shù)或者統(tǒng)計(jì)某個(gè)非空字段的個(gè)數(shù)

1.3 分組函數(shù)

GROUP BY
    用于將信息劃分為更小的組
    每一組行返回針對該組的單個(gè)結(jié)果
HAVING子句
    用于指定 GROUP BY 子句檢索行的條件
#查詢平均工資大于2000的部門編號
select deptno,avg(sal) avg from emp group by deptno having avg>=2000

1.4 加密函數(shù)

select md5('root');
select sha('root');
select password('root');

1.5 流程函數(shù)

如果expr1是真, 返回expr2, 否則返回expr3
IF(expr1,expr2,expr3)   

如果expr1不是NULL,返回expr1,否則返回expr2
IFNULL(expr1,expr2) 

如果value是真, 返回result1,否則返回default
CASE WHEN [value1] THEN[result1]… ELSE[default] END 

如果expr等于value1, 返回result1,否則返回default
CASE [expr] WHEN [value1] THEN[result1]… ELSE[default] END

2.高級查詢

2.1 連接查詢

笛卡爾積

多張表中記錄組合所有可能有序?qū)?/p>

內(nèi)連接

多張表均匹配的記錄連接并展示在結(jié)果集

無主從表之分,與連接順序無關(guān)

# 查詢所有雇員及其部門信息
select * from emp,dept where emp.deptno = dept.deptno;
select * from emp inner join dept on emp.deptno = dept.deptno;
select * from emp inner join dept using(deptno); #字段名稱必須一致,會(huì)自動(dòng)去除重復(fù)列

自然連接

自然連接都是等值連接

表中字段名稱相等的字段進(jìn)行連接，會(huì)自動(dòng)去重重復(fù)列
```
select * from emp natural join dept;
```
外連接

以驅(qū)動(dòng)表為依據(jù),依次在匹配表中進(jìn)行記錄匹配,如果匹配則連接并展示在結(jié)果集中,否則以null填充

有主從表之分拯啦，與連接順序有關(guān)澡匪。

left/right [outer] join ... on...
```
select * from emp 
LEFT JOIN dept
on emp.deptno = dept.deptno
```

自連接

以本身為鏡像進(jìn)行連接(自身連接自身)

# 查詢員工及其領(lǐng)導(dǎo)的名稱
select e1.ename 名稱,e2.ename 領(lǐng)導(dǎo)
from emp e1,emp e2
where e1.mgr = e2.empno

2.2 子查詢

嵌套查詢，將一個(gè)查詢結(jié)果作為另一個(gè)查詢條件或組成部分的查詢

# 單行子查詢
  #查詢工資大于7788號員工的所有員工信息
  select * from emp where sal >(select sal from emp where empno =7788);
# 多行子查詢
  返回多值可以使用any或all來修飾褒链。
  =any相當(dāng)于in,<any小于最大值唁情，>any大于最小值；
  <>all相當(dāng)于not in,>all大于最大值甫匹，<all小于最小值甸鸟。
  
# 查詢超過所在部門平均工資的員工信息
select * from emp e1 where sal > 
(select avg(sal) from emp e2 where e1.deptno = e2.deptno);

#查詢薪水大于2000的部門名稱
select dname from dept d where deptno in
(select deptno from emp e where sal > 2000);
    或
select dname from dept d where EXISTS 
(select * from emp e where sal > 2000 and d.deptno=e.deptno);
# in和exists的區(qū)別
in先執(zhí)行子查詢，在執(zhí)行主查詢;exists先執(zhí)行主查詢;
exists子查詢不返回具體結(jié)果兵迅，返回true值出現(xiàn)在結(jié)果集抢韭，否則不出現(xiàn)。

2.3 聯(lián)合查詢

union: 并集恍箭，所有的內(nèi)容都查詢刻恭，重復(fù)的顯示一次
union all: 并集，所有的內(nèi)容都顯示季惯，包括重復(fù)的

select * from emp where deptno = 20
UNION ALL
select * from emp where sal <=2000

1. 列出員工表中每個(gè)部門的員工數(shù)（員工數(shù)必須大于 3）,和部門名稱.

SELECT emp.deptno,dept.dname,COUNT(*) FROM emp,dept WHERE emp.deptno=dept.deptno GROUP BY emp.deptno,dept.dname HAVING COUNT(*)>3吠各；

2. 找出工資比 jones 多的員工信息。

SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE ename='JONES');

3. 列出所有員工的姓名和其上級的姓名勉抓。

SELECT e1.ename,e2.ename FROM emp e1,emp e2
WHERE e1.mgr = e2.empno;

4. 查詢大于30號部門平均工資并且不在30號部門的員工信息和部門名稱贾漏。

SELECT emp.*,dept.dname FROM emp,dept
WHERE emp.deptno = dept.deptno
AND sal>(SELECT AVG(sal) FROM emp WHERE deptno=30)
AND emp.deptno !=30;

5. 查詢每個(gè)月工資總數(shù)最少的那個(gè)部門的部門編號，部門名稱藕筋，部門位置纵散。

SELECT dept.* FROM dept,(SELECT sum(sal),deptno FROM emp GROUP BY deptno ORDER BY sum(sal) ASC
LIMIT 1) a WHERE dept.deptno=a.deptno;

6. 查詢員工編號為7369的員工姓名和所在部門的部門名稱。

SELECT emp.ename,dept.dname FROM dept,emp
WHERE emp.empno = 7369
AND emp.deptno=dept.deptno;

7. 查詢超過其所在部門平均工資的員工信息。

SELECT * FROM emp,(SELECT deptno dn,avg(sal) avsa FROM emp GROUP BY deptno) ag
WHERE sal>ag.avsa AND emp.deptno=ag.dn;

8. 查詢大于30號部門平均工資并且不在30號部門的員工信息

SELECT emp.* FROM emp
WHERE sal>(SELECT avg(sal) FROM emp WHERE deptno=30)
AND emp.deptno!=30;

BigData-MySQL（高級-函數(shù)、查詢）

BigData-MySQL（高級-函數(shù)钞螟、查詢）