1.oracle单行函数
单行函数:对单个行进行运算,并且对每个行返回一个结果。主要介绍字符,数字,日期和转换函数
多行函数:能够操纵成组的行,每个行组给出一个结果,这些函数也被称为组函数
---dual表
dual表用的所有者为sys用户,并且可以被所有用户访问,用来返回常数值或者不来自用户数据表的表达式
SQL> select 1+2 sums from dual;
---字符函数
upper()/lower()
SQL> select upper(lower(ename)) from emp;
SQL> select upper('yangry') names from dual;
initcap(c):将字符串中单词的第一个字母大写,其他小写
SQL> select initcap('yANGRY') names from dual;
concat(c1,c2):连接字符串,等价于||
select concat(concat(last_name,' '),first_name ) emp_name from employees;
select last_name || ' ' || first_name emp_name from employees;
substr(c1,n1[,n2])截取指定长度的字符串
c1表示字符串
n1为从那个位置开始截取
n2为截取长度
select substr('nconverge',3) substing from dual;
select substr('nconverge',3,4) substring from dual;
length(c):返回指定字符串的长度
select length(phone_number) len_phone from employees;
select * from employees where length(phone_number)>=18;
replace(c1,c2[,c3]):将c1字符串中的c2替换为c3,如果c3为null,则从c1中删除所有c2
select replace(first_name,'teven','lack') from employees where first_name='Steven';
select replace(first_name,'teven') from employees where first_name='Steven';
instr(c1,c2[,n1[,n2]]):返回c2在c1中的位置,c1为原字符串,c2为要查找的字符串,n1为查询起始位置,n2为第几个匹配项
select instr('corporate floor','or') instr_str from dual;
select instr('corporate floor','or',3,2) instr_str from dual;
14
lpad(c1,n[,c2]):返回指定长度等于n的字符串 (rpad(c1,n[,c2]))
select lpad('what is this',5) lpad_str from dual;
select lpad('what is this',5),lpad('what is this',25),lpad('what is this',25,'-') lpad_str from dual;
n>c1 and c2 is null,以空格从左向右补充字符长度至n并返回
n>c1 and c2 is not null,以指定字符c2从左向右补充字符长度至n并返回
trim([[leading|trailing|both] trim_character from ] trim_source)
leading:出除头部的trim_character字符
trailing:出除尾部的trim_character字符
both:出除头尾部的trim_character字符
trim(trim_source):没有指定任何参数,出处trim_source头尾空格
select trim(' jone come from henan ') trim_str from dual;
select trim(leading 'j' from 'jone come from henan') lead_str,
trim(trailing 'n' from 'jone come from henan') trail_str,
trim(both 'n' from 'nike come from henan') both_str
from dual;
---数字函数
round(n1[,n2]):四舍五入函数,n2缺省为0
trunc():截断函数
mod():求余函数
select round(23.56),round(23.56,1),
round(23.56,-1),round(25.56,-1) from dual;
trunc(n1[,n2]) n2缺省值为0
select trunc(23.56),trunc(23.56,1),trunc(23.56,-1) from dual;
mod(n1,n2)
select mod(8,3) from dual;
---时间函数
select sysdate from dual;
select current_date from dual;
add_months(date,integer):用于返回date之后的interger个月所对应的日期时间
select add_months(hire_date,-1),add_months(hire_date,1) netx_month,hire_date ,last_name
from employees
where last_name ='Baer';
last_day(date):返回月份的最后一天
next_day(date,n):n为下周的某一天
select last_day(sysdate),sysdate from dual;
select next_day(sysdate,3) from dual;
yyyy-mm-dd hh24:mi:ss am(上午)/pm(下午)
---转换函数
to_char(date,fmt)用于将日期值转换为字符串,fmt用于指定日期格式
select to_char(hire_date,'yyyy-mm-dd hh24:mi:ss'),last_name from employees;
select last_name,hire_date from employees
where to_char(hire_date,'yyyy')=2005;
to_date(char,fmt)用于将符合日期格式的字符串转换为date类型的值
select last_name ,hire_date from employees
where hire_date > to_date('2005-09-21','yyyy-mm-dd');
---通用函数
---mysql ifnull()
nvl(expr1,expr2):如果expr1为null,则返回expr2,否则返回expr1
select last_name,nvl(to_char(commission_pct),'ccc') pct
from employees where last_name like 'B%';
nvl2(expr1,expr2,expr3):如果expr1为null,返回expr3,如果expr1不为null,则返回expr2
select last_name,salary,nvl(commission_pct,0) pct,nvl2(commission_pct,salary + salary*commission_pct,salary) sum_salary
from employees
where last_name like 'B%';
simple case:
case expr when comparision_expr then return_expr end
select last_name,job_id,salary,
case job_id when 'IT_PROG' then salary * 1.5
when 'ST_CLERK' then salary * 1.4
when 'ST_MAN' then salary * 1.1
else salary
end rasie_salary
from employees where job_id in ('IT_PROG','ST_CLERK','ST_MAN','AC_ACCOUNT') ;
search case:
case when condition then return_expr end
select last_name,salary,
case when salary>4000 then salary
else 4000
end avg_salary
from employees ;
decode(expr,s1,r1,s2,r2...,default_value):expr作为初始参数,s1作为比对值,相同则返回r1
select last_name,job_id,salary,
decode(job_id,'IT_PROG',salary * 1.5,'ST_CLERK',
salary * 1.4,'ST_MAN',salary * 1.1, salary) avg_salary
from employees
where job_id in ('IT_PROG','ST_CLERK','ST_MAN','AC_ACCOUNT');
2.多表查询
---查询员工对应的部门名称
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a
join departments b on a.department_id = b.department_id;
连接类型:等值连接,外连接(左外连接,右外连接),自连接
left join
right join
---left join:将employees表中不满足连接条件的大的行数据全部查询出来
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a
left join departments b on a.department_id = b.department_id;
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a ,departments b where a.department_id = b.department_id(+);
---right join:将departments表中不满足连接条件的大的行数据全部查询出来
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a
right join departments b on a.department_id = b.department_id;
select a.last_name ||' ' ||a.first_name emp_name,b.department_name
from employees a , departments b
where a.department_id(+) = b.department_id;
---self join:连接
select a.last_name || ' ' || a.first_name emp_name,
b.last_name || ' ' || b.first_name manager_name
from employees a
join employees b on a.manager_id = b.employee_id;
---合并查询
select1
union|union all|minus|intersect
select2
union|union all|minus|intersect
select3
union用于获取两个结果集的并集。当使用该操作符时,会自动出掉结果集中的重复行
#union all
select ename,sal,job from emp where sal>2500
union all
select ename,sal,job from emp where job='MANAGER';
#union
select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp where job='MANAGER';
minus用于获取两个结果集的差集,当使用该操作符时,只会显示在第一个结果集中存在,在第二个结果集中不存在的数据。
#minus
select ename,sal,job from emp where sal>2500
minus
select ename,sal,job from emp where job='MANAGER';
intersect用于获取两个结果集的交集,当使用该操作符时,只会显示同时存在于两个结果集中的数据。
#intersect
select ename,sal,job from emp where sal>2500
intersect
select ename,sal,job from emp where job='MANAGER';
---多表查询,不小于3张表
查询员工对应的部门名和城市名
select a.last_name || ' ' || a.first_name emp_name,
b.department_name,
c.city
from EMPLOYEES a
join DEPARTMENTS b on a.department_id=b.department_id
join locations c on c.location_id = b.location_id;
select a.last_name || ' ' || a.first_name emp_name,
b.department_name,
c.city
from EMPLOYEES a, DEPARTMENTS b ,locations c
where a.department_id=b.department_id and c.location_id = b.location_id;
组函数/子查询/sqlplus