SELECT ename,sal,deptno
FROM emp
WHERE DEPTNO = 10;
# ASC 升序 DESC 降序 默认为升序,NULL值为最大
SELECT ename,sal,deptno, hiredate, comm
FROM emp
ORDER BY deptno DESC, sal;
#字符形式 大小写敏感
# 字符函数 :
# 大小写相关:LOWER,UPPER, INTTCAP
# 其他: TRIM, LTRIM, RTRIM, LPAD, RAD, INSTR, SUBSTR, LENGTH, CONCAT, REPLACE
#
SELECT lower('Hello'), upper('Hello'), initcap('hello')
FROM dual;
# 消除字符
SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'), rtrim('HelloHH', 'H')
FROM dual;
# 补齐字符
SELECT lpad(1000, 7, '*'), rpad(1000,7,'*')
FROM dual;
#查找字符位置
SELECT instr('hello tom','tom')
FROM dual;
#截取字符
SELECT substr('hello',3,2)
FROM dual;
#连接字符,求字符长度
SELECT concat('ton','ny'),length('tom')
FROM dual;
#替换字符
SELECT replace('hellh','h','mm')
FROM dual;
#数值函数
# ROUND 四舍五入 TRUNC 去尾 MOD 求模(求余数)
#四舍五入 小数点后保留几位,小数点前保留几位,不保留
SELECT round(123.456, 2), round(145.456, -2), round(123.456, 0), round(123.456)
FROM dual;
#不进行四舍五入,去尾
SELECT trunc(123.456,2), trunc(123.456, -2),trunc(123.456,0), trunc(123.456)
from dual;
#求模
SELECT mod(12,7)
FROM dual;
# 日期函数
# SYSDATE 获取系统当前日期
# MONTHS_BETWEEN 获取两个日期之间的相差月数
# ADD_MONTH 获取几个月后的日期
# LAST_DAY 获取本月最后一天的日期
# EXTRACT 从日期中获取某一部分值
SELECT sysdate + 2/24
FROM dual;
SELECT add_months(sysdate, 3)
FROM dual;
SELECT ename, trunc(months_between(sysdate, hiredate)) da
FROM emp;
SELECT last_day(sysdate)
FROM dual;
SELECT extract(YEAR FROM sysdate)
FROM dual;
#转换函数
# TO_CHAR 将日期或数值转换为制定格式的字符
# TO_DATE 将指定格式字符转换为日期
# TO_NUMBER 将指定格式字符转换为数值
SELECT to_char(sysdate,'YYYY-MM-DD') date1
FROM dual;
SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MI:SS') date1
FROM dual;
#年 year,yyyy,yy
#月 month,mon,mm
#日 DD
#星期几 DAY(monday),DY(mon),D(1)
#时间 HH24:MI:SS HH:MI:SS
SELECT to_char(123.45,'L9,999,999.00')
FROM dual;
#L:本地的货币符号, $:直接显示, 9:表示数值, 0:同9,强制占位
SELECT to_date('1980-01-01', 'YYYY-MM-DD')
FROM dual;
SELECT to_number('$1,222.34','$9,999.99')
FROM dual;
# 空值转换函数 要求类型相同
SELECT ename,comm,nvl(comm,0)
FROM emp;
# 为空的话用 sal 代替,不为空的话用 0 代替
SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;
# 两个表达式值相同为空
SELECT nullif('a','a')
FROM dual;
# 条件分支函数 DECODE , CASE
SELECT ename,deptno,sal,
decode(deptno,
10, sal + 100,
20, sal + 200,
30, sal + 300,
sal) salary
FROM emp;
SELECT ename, deptno, sal,
CASE deptno
WHEN 10 THEN sal + 100
WHEN 20 THEN sal + 200
WHEN 30 THEN sal + 300
ELSE deptno END salery
FROM emp;
SELECT ename,deptno,sal,
CASE
WHEN deptno = 10 THEN sal + 100
WHEN deptno = 20 THEN sal + 200
WHEN deptno = 30 THEN sal + 300
ELSE deptno END salery
FROM emp;
#聚合函数与数据分组-常用聚合函数及使用
# MAX 求最大值 可以应用在字符、数值和日期类型数据上
# MIN 求最小值 可以应用在字符、数值和日期类型数据上
# SUM 计算和 可以应用在数值类型数据上
# AVG 计算平均值 可以应用在数值类型数据上
# COUNT 统计行数 统计表达式值不为空的行数
# 求最大至于最小值
SELECT MAX(sal),min(sal)
FROM emp;
# 求行数
SELECT COUNT(comm)
FROM emp;
# 验证行数
SELECT ename,comm
FROM emp
WHERE comm IS NOT NULL
ORDER BY comm DESC;
# 数据分组
# GROUP BY : 根据指定的列进行分组统计
# HAVING : 限定显示分组后的数据
SELECT deptno, max(sal), min(sal)
from emp
group by deptno
having min(sal)>1000;
# 数据分组 要求
# 在select子句中出现的非分组列,必须出现在 GROUP BY 子句中
select deptno,job,avg(sal)
from emp
group by deptno,job;
select deptno,avg(sal)
FROM emp
group by deptno;
#多表查询
#基于同名列进行连接
select ename,deptno,dname
from emp NATURAL join dept;
select ename,deptno,dname
from emp join dept using(deptno);
# 自连接:将一张表作为两张表看待进行的连接
# FROM table t1 JOIN table t2 ON t1.col=t2.col
select * from emp;
select w.ename, n.ename
from emp w join emp n on w.mgr = n.empno
# 外连接:使用空行匹配非匹配行的连接
SELECT e.ename,d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
# 连接更多表
# FROM table1 JOIN table2 ON t1.col = t2.col
JOIN table3 ON t1.col = t3.col
...
SELECT ename,dept.deptno,dname,grade
FROM emp JOIN dept ON emp.deptno = dept.deptno
JOIN salgrade ON sal BETWEEN losal AND hisal;
# 子查询
# 单行子查询:子查询为一行的查询
# 查询 工资大于 ename 为 ADAMS 的员工信息
SELECT *
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE ename = 'ADAMS');
# 查询 工资大于平均工资的员工的信息
SELECT *
FROM emp
WHERE sal > (SELECT avg(sal) FROM emp);
# 多行子查询 : 子查询为多行的查询
# ANY ALL IN
SELECT * FROM emp
WHERE sal > ANY (SELECT AVG(sal) FROM emp GROUP BY deptno);
SELECT AVG(sal) FROM emp GROUP BY deptno
SELECT ename from emp;
select * from emp where ename in ('WARD','ALLEN','SCOTT');
# 多列子查询
# 非相关子查询
# 相关子查询 : 子查询中需要使用主查询中行的查询
# 流程:
# 1.首先从主查询中获取一行
# 2.利用主查询中获取行执行子查询
# 3.执行主查询
# 相关子查询
SELECT ename,sal,deptno
FROM emp e
WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno = e.deptno)
ORDER BY deptno DESC,sal;
# 使用EXISTS进行相关子查询:在子查询中只判断存在性的查询
# SELECT ...
FROM table1 outer
WHERE [NOT] EXISTS (SELECT ...
FROM table2
WHERE col = outer.col);
SELECT *
FROM emp e
WHERE EXISTS (SELECT 0 FROM emp WHERE mgr = e.empno);
FROM emp
WHERE DEPTNO = 10;
# ASC 升序 DESC 降序 默认为升序,NULL值为最大
SELECT ename,sal,deptno, hiredate, comm
FROM emp
ORDER BY deptno DESC, sal;
#字符形式 大小写敏感
# 字符函数 :
# 大小写相关:LOWER,UPPER, INTTCAP
# 其他: TRIM, LTRIM, RTRIM, LPAD, RAD, INSTR, SUBSTR, LENGTH, CONCAT, REPLACE
#
SELECT lower('Hello'), upper('Hello'), initcap('hello')
FROM dual;
# 消除字符
SELECT trim('H' FROM 'HelloHH'), ltrim('HelloHH', 'H'), rtrim('HelloHH', 'H')
FROM dual;
# 补齐字符
SELECT lpad(1000, 7, '*'), rpad(1000,7,'*')
FROM dual;
#查找字符位置
SELECT instr('hello tom','tom')
FROM dual;
#截取字符
SELECT substr('hello',3,2)
FROM dual;
#连接字符,求字符长度
SELECT concat('ton','ny'),length('tom')
FROM dual;
#替换字符
SELECT replace('hellh','h','mm')
FROM dual;
#数值函数
# ROUND 四舍五入 TRUNC 去尾 MOD 求模(求余数)
#四舍五入 小数点后保留几位,小数点前保留几位,不保留
SELECT round(123.456, 2), round(145.456, -2), round(123.456, 0), round(123.456)
FROM dual;
#不进行四舍五入,去尾
SELECT trunc(123.456,2), trunc(123.456, -2),trunc(123.456,0), trunc(123.456)
from dual;
#求模
SELECT mod(12,7)
FROM dual;
# 日期函数
# SYSDATE 获取系统当前日期
# MONTHS_BETWEEN 获取两个日期之间的相差月数
# ADD_MONTH 获取几个月后的日期
# LAST_DAY 获取本月最后一天的日期
# EXTRACT 从日期中获取某一部分值
SELECT sysdate + 2/24
FROM dual;
SELECT add_months(sysdate, 3)
FROM dual;
SELECT ename, trunc(months_between(sysdate, hiredate)) da
FROM emp;
SELECT last_day(sysdate)
FROM dual;
SELECT extract(YEAR FROM sysdate)
FROM dual;
#转换函数
# TO_CHAR 将日期或数值转换为制定格式的字符
# TO_DATE 将指定格式字符转换为日期
# TO_NUMBER 将指定格式字符转换为数值
SELECT to_char(sysdate,'YYYY-MM-DD') date1
FROM dual;
SELECT to_char(sysdate,'YYYY-MM-DD DAY HH24:MI:SS') date1
FROM dual;
#年 year,yyyy,yy
#月 month,mon,mm
#日 DD
#星期几 DAY(monday),DY(mon),D(1)
#时间 HH24:MI:SS HH:MI:SS
SELECT to_char(123.45,'L9,999,999.00')
FROM dual;
#L:本地的货币符号, $:直接显示, 9:表示数值, 0:同9,强制占位
SELECT to_date('1980-01-01', 'YYYY-MM-DD')
FROM dual;
SELECT to_number('$1,222.34','$9,999.99')
FROM dual;
# 空值转换函数 要求类型相同
SELECT ename,comm,nvl(comm,0)
FROM emp;
# 为空的话用 sal 代替,不为空的话用 0 代替
SELECT ename,comm,nvl2(comm,0,sal)
FROM emp;
# 两个表达式值相同为空
SELECT nullif('a','a')
FROM dual;
# 条件分支函数 DECODE , CASE
SELECT ename,deptno,sal,
decode(deptno,
10, sal + 100,
20, sal + 200,
30, sal + 300,
sal) salary
FROM emp;
SELECT ename, deptno, sal,
CASE deptno
WHEN 10 THEN sal + 100
WHEN 20 THEN sal + 200
WHEN 30 THEN sal + 300
ELSE deptno END salery
FROM emp;
SELECT ename,deptno,sal,
CASE
WHEN deptno = 10 THEN sal + 100
WHEN deptno = 20 THEN sal + 200
WHEN deptno = 30 THEN sal + 300
ELSE deptno END salery
FROM emp;
#聚合函数与数据分组-常用聚合函数及使用
# MAX 求最大值 可以应用在字符、数值和日期类型数据上
# MIN 求最小值 可以应用在字符、数值和日期类型数据上
# SUM 计算和 可以应用在数值类型数据上
# AVG 计算平均值 可以应用在数值类型数据上
# COUNT 统计行数 统计表达式值不为空的行数
# 求最大至于最小值
SELECT MAX(sal),min(sal)
FROM emp;
# 求行数
SELECT COUNT(comm)
FROM emp;
# 验证行数
SELECT ename,comm
FROM emp
WHERE comm IS NOT NULL
ORDER BY comm DESC;
# 数据分组
# GROUP BY : 根据指定的列进行分组统计
# HAVING : 限定显示分组后的数据
SELECT deptno, max(sal), min(sal)
from emp
group by deptno
having min(sal)>1000;
# 数据分组 要求
# 在select子句中出现的非分组列,必须出现在 GROUP BY 子句中
select deptno,job,avg(sal)
from emp
group by deptno,job;
select deptno,avg(sal)
FROM emp
group by deptno;
#多表查询
#基于同名列进行连接
select ename,deptno,dname
from emp NATURAL join dept;
select ename,deptno,dname
from emp join dept using(deptno);
# 自连接:将一张表作为两张表看待进行的连接
# FROM table t1 JOIN table t2 ON t1.col=t2.col
select * from emp;
select w.ename, n.ename
from emp w join emp n on w.mgr = n.empno
# 外连接:使用空行匹配非匹配行的连接
SELECT e.ename,d.deptno
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
# 连接更多表
# FROM table1 JOIN table2 ON t1.col = t2.col
JOIN table3 ON t1.col = t3.col
...
SELECT ename,dept.deptno,dname,grade
FROM emp JOIN dept ON emp.deptno = dept.deptno
JOIN salgrade ON sal BETWEEN losal AND hisal;
# 子查询
# 单行子查询:子查询为一行的查询
# 查询 工资大于 ename 为 ADAMS 的员工信息
SELECT *
FROM emp
WHERE sal > (SELECT sal
FROM emp
WHERE ename = 'ADAMS');
# 查询 工资大于平均工资的员工的信息
SELECT *
FROM emp
WHERE sal > (SELECT avg(sal) FROM emp);
# 多行子查询 : 子查询为多行的查询
# ANY ALL IN
SELECT * FROM emp
WHERE sal > ANY (SELECT AVG(sal) FROM emp GROUP BY deptno);
SELECT AVG(sal) FROM emp GROUP BY deptno
SELECT ename from emp;
select * from emp where ename in ('WARD','ALLEN','SCOTT');
# 多列子查询
# 非相关子查询
# 相关子查询 : 子查询中需要使用主查询中行的查询
# 流程:
# 1.首先从主查询中获取一行
# 2.利用主查询中获取行执行子查询
# 3.执行主查询
# 相关子查询
SELECT ename,sal,deptno
FROM emp e
WHERE sal > (SELECT avg(sal) FROM emp WHERE deptno = e.deptno)
ORDER BY deptno DESC,sal;
# 使用EXISTS进行相关子查询:在子查询中只判断存在性的查询
# SELECT ...
FROM table1 outer
WHERE [NOT] EXISTS (SELECT ...
FROM table2
WHERE col = outer.col);
SELECT *
FROM emp e
WHERE EXISTS (SELECT 0 FROM emp WHERE mgr = e.empno);