Oracle基本代码学习

/*------------Orcale函数----------------*/

1.字符函数

LOWER()小写
UPPER（）大写
INITCAP（）把第一个字母大写
CONCAT（）字符串的连接（也可以用 "||"）函数进行连接

//注意在orcale之中，从0开始和从1开始是一样的
SUBSTR(列|字符串,位置,截取数量)  字符串的截取，注意位置如果为负数的话，就是倒着截取
LENGTH()  字符串的长度
REPLACE()  内容的替换

2.数值函数
·四舍五入小数位：
 ROUND()，也可以指定四舍五入的位数，也可以直接对整数给四舍五入，只要给负数（以小数点为基准）
·截断小数位：TRUNC()  都舍不入，参数和ROUND一样
·取余（取模）：MOD('数','被除数')  
 select MOD(489.655523,2) FROM DUAL  =>1.655523


3.日期函数:(SYSDATE->当前天数)
·日期-/+数字=日期
·日期 - 日期 = 数字（天数）

·求星期数：当前日期-雇佣日期 = 天数/7 = 星期数


·MONTHS_BETWEEN()  求出给定日期范围的月数
·ADD_MONTHS();  在指定日期上加上指定的约束，求出之后的日期
·NEXT_DAY()  求出下一次的给定日期数量  [NEXT_DAY(SYSDATE,'星期一')]  距离现在最近的
·LAST_DAY()  求出给定日期的最后一天日期LAST_DAY(SYSDATE)  7月31号


4.转换函数：
·TO_CHAR() 
 1.转换成字符串 TO_CHAR(hiredate,'yyyy')  取出日期之中的年
 2.日期格式转化 TO_CHAR(hiredate,'fmyyyy-mm-dd')，默认有前导0，用fm去掉前导0
 3.数字格式化：TO_CHAR(sal,'999,999')  9代表一位数 和y表示年一月的
 4.数字表示区域：$：表示美元.L:是local的缩写，和本地的语言金额显示
   eg: TO_CHAR(sal,'$999,999')
·TO_NUMBER() 转换成数字：把数字的字符串 换成 数字
·TO_DATE()  转化成日期

5.通用函数：
·NVL 把一个是null值变成指定的内容：
   (sal+NVL(comm,0))*12  如果comm为null的话，就当成0显示。
    如果不用NVL函数的话，comm为NULL，则上面的指就一定为NULL
·DECODE（）函数，重点!
 类似于，IF...ELESEIF ....ELSE 语句

SELECT DECODE(2,1,'内容是一',2,'内容是2',3,'内容是3') FROM DUAL;

DECODE(
-------
内容是2

可见：第一个参数是 数值
     往后就是 一个条件  对应 一个结果
 SELECT empno,ename,DECODE(job,'CLERK','业务员','SALESMAN','销售人员','MANAGER','经理') FROM emp;
CLERK    --> 业务员
SALESMAN --> 销售人员
MANAGER' --> 经理

sqlplus的常用命令
 ed和@指令
 连接： conn 用户名/密码 AS SYSDBA
 set linesize 长度  设置行显示数量
 set pagesize 长度  设置页显示数量

/*-------------------关联查询-----------------------*/
SELECT e.ename 雇员姓名,e.sal 工资,d.dname 部门,s.grade 工资级别 
FROM emp e,dept d,salgrade s 
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

一、关联表的左、右连接

·(+)在=左边表示右连接和下面相反

·(+)在=右边表示左连接[默认] 以左边的表为准，如果左边没有数据的话，即使右边有数据也不显示

/*----------------------组函数---------------------------*/
1.COUNT  求数量
2.MAX/MIN 求最大最值
3.SUM/AVG  求和/求平均



重点：（分组函数）
·范例：求出每个部门的雇员数量，应该按照部门分组
 SELECT deptno,COUNT(empno)
 FROM emp
 GROUP BY deptno


注意点：
SELECT deptno,COUNT(empno) FROM emp
       *
第 1 行出现错误:
ORA-00937: 不是单组分组函数
 以上代码不能执行是因为：
1.如果程序之中使用了分组函数，则有两种可以使用的情况：
 ·程序中存在了GROUP BY，并制定了分组条件，这样可以将分组条件一起查询出来。
 ·如果不使用分组的话，则只能单独的使用分组函数
2.在使用分组函数的时候，不能出现分组函数和分组条件之外的字段。

 关联表查询：
SELECT d.dname,COUNT(e.empno)
FROM emp e,dept d
WHERE e.deptno=e.deptno
GROUP BY d.dname

 显示出平均工资大于两千的部门编号和平均工资：

·分组函数只能在分组之中使用不允许在WHERE语句之中出现，那么如果现在假设要指定分组的条件，则只能通过第二种条件的指令：
 HAVING  。
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno HAVING AVG(sal)>2000;

分组的原则：

·只要一列上存在重复的内容才有可能考虑到分组

注意：

·分组函数可以嵌套使用，但是在组函数嵌套使用的时候，不能够再出现分组条件的查询组

/*----------------子查询---------------------*/
要求查询比雇员7654工资高的全部雇员信息：

SELECT * 
FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654);
子查询必须有括号。
子查询的操作分为三类：
1.单列子查询
2.单行子查询  （多个列，有可能是一条完整的记录）
3.多行子查询：返回多条记录

范例：
SELECT ename,job,sal
FROM emp
WHERE sal=(SELECT MIN(sal) FROM emp)
;

在子查询之中存在三种查询符号：
IN
ANY
ALL


·IN操作符，指定一个查询的范围：
例：求出每个部门的最低工资的雇员信息：
 ·每个部门的最低工资，返回的值肯定是多个，所以此时可以用IN指定一个操作的范围
SELECT * FROM emp WHERE sal IN (
SELECT MIN(sal) FROM emp GROUP BY deptno);



·ANY操作 
=ANY  和IN的操作符功能完全一样
SELECT * FROM emp WHERE sal=ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

>ANY  比里面的最低值要大
SELECT * FROM emp WHERE sal>ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

<ANY  比里面最大的值要小
SELECT * FROM emp WHERE sal<ANY(SELECT MIN(sal) FROM emp GROUP BY deptno);

·ALL操作  
>ALL  比最大的值要大的

<ALL  比最小的值要小的

/*-------------------数据库的更新操作------------------*/
复制表：

CREATE TABLE myemp AS SELECT * FROM emp;


·事务处理：
commit：提交事务
rollback：回滚，但是如果在commit之后就不能够rollback了

·事务处理会产生死锁，一个session如果更新了数据，但是没有commit，则其他session是无法立刻更新的，要等待对方commit之后才允许更新，否则会一直处于等待。