oracle学习摘要2
多列子查询:
select * from emp where (deptno,job)=(select deptno,job from emp where ename=’smith’);注意:列的顺序要对应。
查询高于自己部门平均工资的人:
1->查出各个部门的平均工资和部门编号:
select deptno, avg(sal) mysal from emp group by deptno;
2->将上面的查询看作是一张 子表:(内嵌视图)必须要指定别名给表取别名的时候不能加as
select a1.ename,a1.sal ,a1.deptno, a2.mysal from emp a1, (select deptno, avg(sal) mysal from emp group by deptno) a2 where a1.deptno=a2.deptno;
Oracle的分页有三种方式:
1.rownum分页
select a1.*, rownum rn from (select * from emp) a1;
结果如下: RN为分配的编号
显示rownum( Oracle分配的)
select a1.*, rownum rn from (select * from emp) a1 where rown <=10;不能用and,rownum只能用一次;
结果如下:
select * from (select a1.*, rownum rn from (select * from emp) a1 where rown <=10) where rn>=6;
结果如下:
行迁移:insert into kkk (myid, myname, mydept ) select empno, ename, deptno from emp where deptno = 10;
使用update语句更新数据时,既可以使用表达式或者数值直接修改数据,也可以使用子查询修改数据: update emp set job= ( select job from emp where ename=’SMITH’ ), sal=(select sal from emp where ename= ‘SMITH’) where ename=’jack’;
update emp set ( job, sal ) =( select job sal from emp where ename= ‘SMITH’) where ename=’jack’;
函数:
--------------字符函数--------------
lower(char):将字符串转化为小写格式
upper(char):将字符串转化为大写格式
Length(char):返回字符串的长度
substr(char, m, n ):取字符串的字串
select lower(ename) from emp; 将所有员工的姓名按小写的方式显示
select upper(ename) from emp; 将所有员工的姓名按大写的方式显示
select ename from emp where length(ename)=5; 显示正好为5个字符的员工的姓名
select substr( ename, 1, 3 ) from emp; 取三个,从第2位开始取,个数不够那么就依然是能取多少个就取多少个。
select upper( substr(ename,1,1)) || lower( substr(ename,2,length(ename) -1)) from emp; 显示第一个字母大写后面字母小写。
replace( char1, search_string,replace_string):替换
select replace(ename , ‘A’, ‘哈哈哈’) from emp; 将名字中的A替换为‘哈哈哈’;
------------------数学函数----------------------
round( n ,[m] ):用于执行四舍五入,如果省掉m,则四舍五入到整数;如果m是正数,则四舍五入到小数点的m位后。如果m是负数则四舍五入的到小数点的m位前
trunc( n ,[m] ):用于截取数字,如果省掉m,就截取小数部分,如果m是正数就截取到小数点的m位后,如果m是负数,则截取到小数点的前m位
mod( m ,n ):取模
floor( n ):返回小于或是等于n的最大整数
ceil( n ):返回大于或是等于n的最小整数
abs( n ):返回数字n的绝对值
acos( n ):返回数字的反余旋值
asin( n ):返回数字的正旋值
atan( n ):返回数字的反正切
exp( n ):返回e的n次幂
log( m, n ):返回对数值
power( m,n ):返回m的n次幂
在oracle的默认情况下日期是dd-mon-yy ->12-7月-78
sysdate:该函数返回系统时间
add_months(d,n):加月份
Select * from emp where sysdate > add_(hirdate,8 );查找已经入职8个月多的员工
Last_day(d):返回指定日期所在月份的最后一天
select trunc (sysdate-hirdate ) ‘入职天数’, ename from emp;显示加入公司的天数;
select * from emp where hirdate=last_day(hirdate)-2;找出各月倒数第三天受雇的员工;
转换函数to_char( )
select ename, to_char( hirdate, ‘YYYY-mm-dd hh24:mi:ss) from emp;
--------sys_context系统函数-------
select sys_context (‘userenv’,’db_name’) from dual;显示当前数据库的名字;
select sys_context (‘userenv’,’’language’) from dual;显示当前数据库的使用的哪种语言;