Orace学习了 第二天!
来源:互联网 发布:java游戏中心 编辑:程序博客网 时间:2024/04/29 07:25
第一天的 总结:
SQL> select ename ,sal*12 annual_sal from emp
2 where ename not like '_A%' and sal>800
3 order by sal desc
4 ;
二,sql函数
SQL> select lower(ename) from emp;
SQL> select upper(ename) from emp;
一下两种是一样的结果,但第一种简短
SQL> select ename from emp
2 where lower(ename) like '_a%';
SQL> select ename from emp
2 where ename like '_a%' or ename like '_A%';
从第1个字符开始写 ,共截取3个字符
SQL> select substr(ename,1,3) from emp;
SQL> select chr(65)from dual; A
SQL> select ascii('A')from dual; 65
四舍五入
SQL> select round(23.652,2) from dual; 23.65
SQL> select round(23.652) from dual; 24
SQL> select round(23.652,1)from dual; 23.7
SQL> select round(23.652,-1)from dual; 20 四舍五入到个位
SQL> select round(23.652,-10)from dual; 0 四舍五入到十位
重点要记
SQL> select to_char(sal,'$99,999.9999') from emp;
SQL> select to_char(sal,'L99,999.9999') from emp;
SQL> select to_char(sal,'$000000.0000') from emp;
这是由区别的!
SQL> select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM EMP;
SQL> select ename ,hiredate from emp
2 where hiredate >to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
SQL> select sal from emp
2 where sal>to_number('$1,232.00','$9,999.99');
该函数避免了空值运算结果出现空值的现象
SQL> select ename,sal*12 +nvl(comm,0) from emp;
单行函数:多少条记录,就会有多少条记录(结果)
多行函数:有很多条记录,给过只会一条记录(结果)
组函数:只会一条记录(结果)
SQL> select round(avg(sal),1)from emp;
SQL> select to_char(avg(sal),'9999.99') from emp;
SQL> select sum(sal) from emp;
SQL> select count(*) from emp;
SQL> select count(*) from emp where deptno= 10;
SQL> select min(sal) from emp;
SQL> select max(sal) from emp;
SQL> select count(ename) from emp;
SQL> select count(comm) from emp; count 是记录不是空值的字段
SQL> select count(distinct deptno) from emp;
group by 语句
SQL> select deptno,avg(sal) from emp
2 group by deptno;
SQL> select deptno ,job,max(sal) from emp group by deptno,job;
SQL> select ename from emp
2 where sal =
3 (select max(sal) from emp);
SQL> select deptno ,max(sal) from emp
2 group by deptno;
注解:出现在select列表中的字段如果没有出现在主函数里,那就必须要出现在group by 子句里
SQL> select avg(sal) ,deptno from emp group by
2 deptno having avg(sal)>2000;
having是对分组进行限制的!
。。。。。。。。。。。。。。。。。。。。
SQL> select * from emp
2 where sal >1000
3 group by deptno
4 having ...是对分组进行限制
5 order by 对结果进行排序!
。。。。。。。。。。。。。。。。。。。。。。。
以上是完整的select ,顺序不可以颠倒!
SQL> select avg(sal) from emp
2 where sal>1200
3 group by deptno
4 order by avg(sal) desc;
SQL> select ename ,sal from emp
2 where sal =(select max(sal) from emp);
SQL> select ename ,sal from emp
2 where sal > (select avg(sal) from emp );
SQL> select ename ,sal ,deptno from emp
2 where sal in(select max(sal) from emp group by deptno)
3 ;有问题
SQL> select ename ,sal from emp
2 join(select max(sal)max_sal,deptno from emp group by deptno) t
3 on(emp.sal = t.max_sal and emp.deptno = t.deptno);
每一个部门的平均薪水,然后出薪水的等级
SQL> select deptno,grade avg_sal from
2 ( select deptno,avg(sal) avg_sal from emp group by deptno) t
3 join salgrade s on(t.avg_sal between s.losal and s.hisal);
SQL> select e1.ename,e2.ename from emp e1,emp e2
2 where e1.mgr = e2.empno;(自连接)
SQL> select ename ,sal*12 annual_sal from emp
2 where ename not like '_A%' and sal>800
3 order by sal desc
4 ;
二,sql函数
SQL> select lower(ename) from emp;
SQL> select upper(ename) from emp;
一下两种是一样的结果,但第一种简短
SQL> select ename from emp
2 where lower(ename) like '_a%';
SQL> select ename from emp
2 where ename like '_a%' or ename like '_A%';
从第1个字符开始写 ,共截取3个字符
SQL> select substr(ename,1,3) from emp;
SQL> select chr(65)from dual; A
SQL> select ascii('A')from dual; 65
四舍五入
SQL> select round(23.652,2) from dual; 23.65
SQL> select round(23.652) from dual; 24
SQL> select round(23.652,1)from dual; 23.7
SQL> select round(23.652,-1)from dual; 20 四舍五入到个位
SQL> select round(23.652,-10)from dual; 0 四舍五入到十位
重点要记
SQL> select to_char(sal,'$99,999.9999') from emp;
SQL> select to_char(sal,'L99,999.9999') from emp;
SQL> select to_char(sal,'$000000.0000') from emp;
这是由区别的!
SQL> select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') FROM EMP;
SQL> select ename ,hiredate from emp
2 where hiredate >to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
SQL> select sal from emp
2 where sal>to_number('$1,232.00','$9,999.99');
该函数避免了空值运算结果出现空值的现象
SQL> select ename,sal*12 +nvl(comm,0) from emp;
单行函数:多少条记录,就会有多少条记录(结果)
多行函数:有很多条记录,给过只会一条记录(结果)
组函数:只会一条记录(结果)
SQL> select round(avg(sal),1)from emp;
SQL> select to_char(avg(sal),'9999.99') from emp;
SQL> select sum(sal) from emp;
SQL> select count(*) from emp;
SQL> select count(*) from emp where deptno= 10;
SQL> select min(sal) from emp;
SQL> select max(sal) from emp;
SQL> select count(ename) from emp;
SQL> select count(comm) from emp; count 是记录不是空值的字段
SQL> select count(distinct deptno) from emp;
group by 语句
SQL> select deptno,avg(sal) from emp
2 group by deptno;
SQL> select deptno ,job,max(sal) from emp group by deptno,job;
SQL> select ename from emp
2 where sal =
3 (select max(sal) from emp);
SQL> select deptno ,max(sal) from emp
2 group by deptno;
注解:出现在select列表中的字段如果没有出现在主函数里,那就必须要出现在group by 子句里
SQL> select avg(sal) ,deptno from emp group by
2 deptno having avg(sal)>2000;
having是对分组进行限制的!
。。。。。。。。。。。。。。。。。。。。
SQL> select * from emp
2 where sal >1000
3 group by deptno
4 having ...是对分组进行限制
5 order by 对结果进行排序!
。。。。。。。。。。。。。。。。。。。。。。。
以上是完整的select ,顺序不可以颠倒!
SQL> select avg(sal) from emp
2 where sal>1200
3 group by deptno
4 order by avg(sal) desc;
SQL> select ename ,sal from emp
2 where sal =(select max(sal) from emp);
SQL> select ename ,sal from emp
2 where sal > (select avg(sal) from emp );
SQL> select ename ,sal ,deptno from emp
2 where sal in(select max(sal) from emp group by deptno)
3 ;有问题
SQL> select ename ,sal from emp
2 join(select max(sal)max_sal,deptno from emp group by deptno) t
3 on(emp.sal = t.max_sal and emp.deptno = t.deptno);
每一个部门的平均薪水,然后出薪水的等级
SQL> select deptno,grade avg_sal from
2 ( select deptno,avg(sal) avg_sal from emp group by deptno) t
3 join salgrade s on(t.avg_sal between s.losal and s.hisal);
SQL> select e1.ename,e2.ename from emp e1,emp e2
2 where e1.mgr = e2.empno;(自连接)
- Orace学习了 第二天!
- 今天学习html5第二天了,该来写点东西了。html5实现画线,canvas
- 单片机第二天!开始了!
- 从上班第二天了
- orace 命令
- orace基本知识
- 学习Python第二天
- hibernate学习 第二天
- 学习CRYPTO第二天
- Winpcap学习第二天
- 学习第二天
- java学习第二天
- .NET 学习 第二天
- PHP学习第二天
- Java学习第二天
- ajax学习第二天
- 学习ode第二天
- 学习第二天python
- shell语法简介
- 内核移植相关
- Direct2D教程(十二)图层
- Spring3.0的Annotation注解
- NS2.35中自带卫星网络集中式路由学习
- Orace学习了 第二天!
- arm-linux-gcc-4.3.2 安装配置及下载地址
- Direct2D中资源的创建顺序
- 关于例子
- Direct2D中DirectWrite处理WM_SIZE消息
- 心中的骚动
- hdu3415 Max Sum of Max-K-sub-sequence
- 如何判断一段程序是由C 编译程序还是由C++编译程序编译的
- JS input验证