目录
1.SQL语句的执行顺序
2.SQL常用的函数
2.1聚合函数
2.2字符串函数
2.3数学函数
2.4日期函数
2.5窗口函数
2.6分析函数
2.7行转列
2.8集合操作
2.9小的知识点
2.10 PLSQL快捷键
3.总结
1.SQL语句的执行顺序,连接方式 INNER JOIN LEFT JOIN FULL JOIN
SELECT A.SID,MAX(b.name)
FROM A
INNER JOIN B
ON A.ID = B.ID
INNER JOIN C
ON
2.SQL常用的函数
2.1聚合函数 MAX MIN COUNT AVG SUM
SELECT MAX(T.SCORE) AS "最高分",
MIN(T.SCORE) AS "最低分",
AVG(T.SCORE) AS "平均分",
SUM(T.SCORE),
COUNT(1) AS ZZZ
FROM SCORE T
2.2字符串函数 NVL DECODE || CONCAT SUBSTR INSTR REPLACE
SELECT * FROM student T
LEFT JOIN score T1
ON T.STU_ID= T1.STU_ID
SELECT '张三'||'A',CONCAT('张三','A') FROM DUAL
2.3数学函数 exp ABS
SELECT EXP(1) "e的N次幂", sqrt(4) AS "开平方" FROM DUAL
2.4日期函数 to_date to_char
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),TO_DATE('2018-1-1','YYYY-MM-DD') FROM DUAL
2.5窗口函数 OVER
SELECT MONTH,
SUM(TOT_SALES) MONTH_SALES,
SUM(SUM(TOT_SALES)) OVER(ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTAL_SALES
FROM ORDERS
GROUP BY MONTH;
SELECT MONTH,
SUM(TOT_SALES) MONTH_SALES,
SUM(SUM(TOT_SALES)) OVER(ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CURRENT_TOTAL_SALES
FROM ORDERS
GROUP BY MONTH;
SELECT MONTH,
SUM(TOT_SALES) MONTH_SALES,
SUM(SUM(TOT_SALES)) OVER(ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CURRENT_TOTAL_SALES,
SUM(SUM(TOT_SALES)) OVER(ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTAL_SALES
FROM ORDERS
GROUP BY MONTH;
措行实现同比环比
SELECT MONTH,
SUM(TOT_SALES) MONTHLY_SALES,
LAG(SUM(TOT_SALES), 1) OVER(ORDER BY MONTH) PREV_MONTH_SALES
FROM ORDERS
GROUP BY MONTH
ORDER BY MONTH;
2.6分析函数 ROW_NUMBER() DENSE_RANK() RANK() NTILE(5)
SELECT T.*,
ROW_NUMBER() OVER(ORDER BY T.SCORE DESC) AS "名次1",
DENSE_RANK() OVER(ORDER BY T.SCORE DESC) AS "名次2",
RANK() OVER(ORDER BY T.SCORE DESC) AS "名次3",
NTILE(5) OVER(ORDER BY T.SCORE DESC) AS "分组"
FROM SCORE T
2.7行转列
with temp as(
select '中国' nation ,'广州' city from dual union all
select '中国' nation ,'上海' city from dual union all
select '中国' nation ,'北京' city from dual union all
select '美国' nation ,'纽约' city from dual union all
select '美国' nation ,'波士顿' city from dual union all
select '日本' nation ,'东京' city from dual
)
SELECT * FROM temp;
select nation,listagg(city,',') within GROUP (order by city)
from temp
group by nation;
2.8集合操作 UNION ALL UNION INTERSECT MINUS
with temp as(
select '中国' nation ,'广州' city from dual union all
select '中国' nation ,'上海' city from dual union all
select '中国' nation ,'北京' city from dual union all
select '美国' nation ,'纽约' city from dual union all
select '美国' nation ,'波士顿' city from dual union all
select '日本' nation ,'东京' city from dual
),
TEMP2 AS(
select '中国' nation ,'广州' city from dual union all
select '中国' nation ,'济南' city from dual union all
select '中国' nation ,'青岛' city from dual union all
select '美国' nation ,'纽约' city from dual union all
select '美国' nation ,'波士顿' city from dual union all
select '日本' nation ,'东京' city from dual
)
SELECT * FROM temp INTERSECT SELECT * FROM temp2
2.9小的知识点
BETWEEN AND
VARCHAR2(4000)
ORACLE 小数显示
GBK 2个字节 UTF8 3个字节
3总结:
1.学习是一个不断积累的过程,没有人是生下来什么都会,只要用心,大部分人都能学会
2.可以走弯路,但不能原地踏步
3.如果我们正处于瓶颈,困难,迷茫期,有三件事可以帮助我们,一就是学习 通过不断的学习
打开思路,寻求改变 二就是体育锻炼 增强体质 三是艺术,感受美好。
打造学习型的人生,把抱怨 借口 迟疑 变为目标 学习 方法 行动 进入良性循环