SQL 函数包含多行函数和单行函数
单行函数就是输入一行输出也是一行
多行函数 也称为分组函数，将多行数据返回一个值
1. 字符函数
1. ASCII（x）用于返回字母的ASCII码
2. length（x） 获取字符个数
3. concat （x） 拼接字符串
4. lower（x） 转换小写
5. upper（x）转换大写
6. substr（x，start【，length】）
x： 需要截取的字符串
start ： 开始位置
length ： 截取的长度
7. instr （x，find_string【，start】【，occurrense】）查询find_string ，然后返回所在的位置，occirrence 第几次出现的位置
8. replace（x， search_string，replace_string）
x： 用于查找的 字符串
search_string ： 要查询的字符串
replace_String： 要替换的字符串
9. initcap（x）用于将单词首字母转换
10. rpad（x，width【， pad_String】）
11. lpad（）（同10）
2. 数字函数
1. abs（x）绝对值
2. MOD（X） 余数
3. CEIL（x） 向上取整
4. FLOOR（x） 向下取整
5. ROUND（x） 四舍五入
6. POWER（x,y） x的y次幂
7. SQRT （X） 平方根
8. SIGN（x） 检测正负值
9. COS（x）
10. ACOS（x）反余弦
3. 转换函数
1. to_char（x） ：将x转换成一个字符串，格式化字符串
2. to_number（x， 【x，format】）： 将x转换成一个数字
3. case（x as type）： 将x转换成指定type的兼容类型
4. asciiStr （）： 将任意字符串转换成数据库字符集的acsii
5. bin_to_num（x）： 将二进制数转换成十进制
6. to_date（）： 将字符串转换成时间类型
4. 正则表达式
1. regexp（想，pattern【，match_option】）用于在x中查找
5. 聚合函数
1. avg（）平均数，非null的平均值
2. count（） 获取非空值得数量
3. max（）和min（） 函数
4. sum（）所有值得和
6. 日期函数
1. to_char（）和to_date()转化时间
2. sysdate 获取系统当前时间
3. months_between（x，y）函数判断2个月分差
4. add_months（x，y）计算x+y个月后的结果
7. 其他函数
nvl
nvl2
case
子查询（嵌套查询）
单行子查询： 不向外部的SQL语句返回结构，或者只返回一行
多行子查询： 向外部的SQL语句返回多行或一行
多列子查询： 向外部SQL语句返回多列
关联子查询： 引用外部SQL语句中的一列或多列，这种子查询被称为关联查询
嵌套子查询： 位于子查询中的查询，子查询最多可以嵌套255层
子查询可放在另一个查询的where子句中.
查询部门平均工资,低于部门平均工资的最高值
1000
1500
800
select avg(sal) from emp group by deptno
显示高于自身部门平均工资的员工信息
部门编号,员工姓名,工种,工资
获取每个部门的平均工资
当员工的部门编号等于 查询出来平均工资的部门编号,使用工资和平均工资比较
where中的子查询
SQL> select empno,ename from emp where deptno = (select deptno from dept where dname = 'SALES');
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
已选择6行。
having中的子查询
SQL> select deptno,avg(sal) from emp group by deptno
2 having avg(sal) < (select max(avg(sal)) from emp group by deptno);
DEPTNO AVG(SAL)
---------- ----------
800
30 1566.66667
20 2518.75
from中的子查询
SQL> select d.deptno,ename,job,sal from emp,(select deptno,avg(sal) avgsal from emp group by deptno) d
2 where emp.deptno=d.deptno and emp.sal<d.avgsal;
DEPTNO ENAME JOB SAL
---------- ---------- --------- ----------
10 SMITH CLERK 800
30 WARD SALESMAN 1250
30 MARTIN SALESMAN 1250
30 TURNER SALESMAN 1500
20 ADAMS CLERK 1100
30 JAMES CLERK 950
10 MILLER CLERK 1300
已选择7行。
子查询常见的错误:
1. 单行子查询返回多个值
SQL> select deptno,ename from emp where deptno = (select deptno from dept);
select deptno,ename from emp where deptno = (select deptno from dept)
*
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行
2. 子查询不能包含order by语句
SQL> select * from emp where sal<(select avg(sal) from emp order by avg(sal) desc);
select * from emp where sal<(select avg(sal) from emp order by avg(sal) desc)
*
第 1 行出现错误:
ORA-00907: 缺失右括号
多行子查询:
in : 匹配子查询结构的任意一个值即可.
all : 必须符合子查询结果的所有值
any : 只要符合子查询结构的人一个值即可
CLERK
PRESIDENT
MANAGER
select ename,job deptno from emp where job in (CLERK,PRESIDENT,MANAGER);
查询部门编号30的所有员工,工资.
显示所有员工中工资大于上面工资中任意一个工资的员工信息
SQL> select ename,sal,deptno from emp where sal < any(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
JAMES 950 30
ADAMS 1100 20
WARD 1250 30
MARTIN 1250 30
MILLER 1300 10
TURNER 1500 30
ALLEN 1600 30
CLARK 2450 10
已选择9行。
SQL> select ename,sal,deptno from emp where sal < any(500,2000);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
ALLEN 1600 30
WARD 1250 30
MARTIN 1250 30
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
MILLER 1300 10
已选择8行。
SQL> select ename,sal,deptno from emp where sal < all(select sal from emp where deptno = 30);
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 10
SQL> select * from dept where deptno > all(10,20,30);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 b a
多列子查询
SQL> select deptno,ename,sal from emp where (deptno,sal)in(select deptno,min(sal) from emp group by deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 JAMES 950
20 ADAMS 1100
10 SMITH 800
关联子查询:
关联子查询对外部查询中每一行都会运行一次,这与非关联子查询是不同的.
非关联子查询只在运行外部查询之前执行一次,
SQL> select empno,deptno,ename,sal from emp outer
where sal >
(select avg(sal) from emp inner where inner.deptno = outer.deptno)
;
EMPNO DEPTNO ENAME SAL
---------- ---------- ---------- ----------
7499 30 ALLEN 1600
7566 20 JONES 2975
7698 30 BLAKE 2850
7782 10 CLARK 2450
7788 20 SCOTT 3000
7839 10 KING 5000
7902 20 FORD 3000
已选择7行。
select * from emp e,
(select deptno,avg(sal) a from emp group by deptno) d;
where e.deptno = d.deptno and e.sal<d.a
子查询 exists和not exists
select ename,job,sal,deptno from emp e
where exists(
select 1 from dept d
where loc = 'NEW YORK' and d.deptno = e.deptno
)
选课表 : 学号,课程号
学生表 : 学号,姓名
课程表 : 课程号,课程名
查询选修了java课程的学生姓名
select 姓名 from 学生表 where 学号 in(
select 学号 from 选课表 where 课程号 =
(select 课程号 from 课程表 where 课程名= 'java')
)
exists的写法
select 姓名 from 学生表
where exists
(
select * from 选课表 where 学生表.学号 = 选课表.学号 and 课程号 =(.......)
)
查询选修了所有课程的学生:
select 姓名 from 学生表
where not exists
(
select * from 课程表 where not exists(
select * from 选课表 where 学生表.学号 = 选课表.学号 and 课程表.课程号 = 选课表.课程号
)
)
嵌套子查询
SQL> select deptno,avg(sal) from emp
2 group by deptno
3 having avg(sal)>
4 (select max(avg(sal)) from emp
5 where deptno in
6 (select deptno from dept
7 where deptno > 10)
8 group by deptno);
select deptno from dept where deptno > 10
查询部门编号大于10的部门编号 20,30,40,50
select max(avg(sal)) from emp where deptno in (20,30,40,50) group by deptno
计算部门平均工资的最大值 2200
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2200
显示大于20,30,40,50部门最高平局工资的部门编号和平均工资
包含子查询的修改和删除
update where
delete where
delete from emp where deptno =(
select deptno from dept where dname = 'SALES'
)
update emp set (sal,comm) = (select sal,comm from emp where ename = 'SMITH')
where job = (select job from emp where ename = 'SMITH')
高级查询
集合操作 : 将两个或多个查询返回的结构结合起来
union,union all,intersect,minus
并集(获取的结果集合并到一个结果集中)
SQL> select * from dept where deptno > 10
2 union all
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 b a
40 OPERATIONS BOSTON
50 b a
已选择6行。
SQL> select * from dept where deptno > 10
2 union
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 b a
并集:
SQL> select * from dept where deptno > 10
2 intersect
3 select * from dept where deptno >30
4 ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 b a
差集:
SQL> select * from dept where deptno > 10
2 minus
3 select * from dept where deptno >30;
DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
case表达式
case search_expression
when expression1 then result1
when expression2 then result2
......
when expression then result
else default_result;
员工工资小于1000
1000~2000
>2000
low med high
3 8 10
SQL> select empno,ename,
2 case deptno
3 when 10 then 'A'
4 when 20 then 'B'
5 when 30 then 'C'
6 else 'not exists'
7 end
8 from emp;
EMPNO ENAME CASEDEPTNO
---------- ---------- ----------
7369 SMITH A
7499 ALLEN C
7521 WARD C
7566 JONES B
7654 MARTIN C
7698 BLAKE C
7782 CLARK A
SQL> select
2 count(case when sal<1000 then 1 else null end) low,
3 count(case when sal between 1000 and 2000 then 1 else null end) med,
4 count(case when sal>2000 then 1 else null end) high
5 from emp;
LOW MED HIGH
---------- ---------- ----------
2 6 6
一行数据展示出来所有部门的人数(列转行)
select * from
(select deptno,count(1) c from emp group by deptno) t
pivot (min(c) for deptno in(10,20,30,40,50))
