Oracle学习笔记

Oracle的四个主要用户1.超级管理员sys/change_on_install2.普通管理员system/manager3.普通用户scott/tiger4.大数据用户sh/sh简单查询1.查询表结构 DESC 表名2.简单查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] …FROM 表名 [别名]（1）查询时也可以使用四则运算，如：SELECT (字段*x)+y FROM 表名（2）可以使用“||”连接字段与字符串，如：select '工作与工资：' || job || sal 工作工资 from emp执行结果：3.限定查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] …FROM 表名 [别名][WHERE 条件(s)]（1）Where字句后可以增加多个条件，最常见的条件就是最基本关系运算：>、<、>=、<=、!=(<>)、BE TWEEN…AND、LIKE、IN(指定范围)、IS NULL(为空)、AND(且)、OR(或)、NOT(非)。

（2）多个条件可以使用AND与OR连接。

例如：select * from emp where sal>1300 and sal<4000（3）范围判断BE TWEEN…AND：BETWEEN 最小值 AND 最大值（大于等于最小值，小于等于最大值），BE TWEEN…AND 对数字、日期都可以使用！！！例如：select * from emp where sal between 1300 and 4000例如：select * from emp where hiredate between '01-1月-1981' and '31-12月-1981'（4）判断为空IS (NOT) NULL例如：select * from emp where comm is null例如：select * from emp where comm is not null例如：select * from emp where not comm is null（5）指定范围的判断(NOT) IN例如：select * from emp where empno in(7369,7566,7799)例如：select * from emp where empno not in(7369,7566,7799)注意：如果NOT IN 范围里出现了NULL则查询不到任何结果！！！（6）模糊查询LIKE，‘_’匹配一个字符。

‘%’匹配任意个字符。

例如：select * from emp where empno like '7%'例如：select * from emp where empno like '77__'4.数据排序SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] …FROM 表名 [别名][WHERE 条件(s)][ORDER BY [字段 [ASC | DESC]] [，字段 [ASC | DESC]] …]（1）“ORDER BY”子句是写在所有SQL语句最后的，排序时可以指定多个字段，ASC为升序是默认的，DESC是降序使用时需要指定。

单行函数1.字符函数（1）首字母大写：Initcap(char)例如：Select initcap(‘hello’) from dual结果：Hello（2）转换为大写：Upper(char)例如：Select upper(‘sun’) from dual结果：SUN（3）转换为小写：Lower(char)例如：Select lower(‘FUN’) fr om dual结果：fun（4）左剪裁：Ltrim(char,set)例如：Select ltrim( ‘xyzadams’,’xyz’) from dual结果：adams（5）右剪裁：Rtrim(char,set)例如：Select rtrim(‘xyzadams’,’ams’) from dual结果：xyzad（6）按字符翻译：Translate(char, from, to)例如：Select translate(‘jack’,’j’ ,’b’) from dual结果：back（7）字符串替换：Replace(char, searchstring,[rep string])例如：Select replace(‘jack and jue’ ,’j’,’bl’) from dual结果：black and blue（8）查找子串位置：Instr (char, m)例如：Select instr (‘worldwide’,’d’) from dual结果：5（9）取子字符串：Substr (char, m, n)例如：Select substr(‘abcdefg’,3,2) from dual结果：cdOracle数据库的字符串的下标0或1都是指字符串的第一个字母，如：Select substr(‘abcdefg’,0,2) from dual结果：abSelect substr(‘abcdefg’,1,2) from dual结果：abSubstr (char, m, n)中参数n可以不写，不写表示截取完整个字符串，如：Select substr(‘abcdefg’,2) from dual结果：cdefgSubstr (char, m)中参数m可以是负数表示从后截取字符串，如：Select substr(‘abcdefg’,-3) from dual结果：efg（10）连接字符串：Concat (expr1, expr2)例如：Select concat (‘Hello’,’ world’) from dual结果：Hello world（11）字符串长度：Length(char)例如：Select length(‘lizhiwei’) from dual结果：82.数字函数（1）四舍五入：Round(m,n)例如：Select round(100.5) from dual结果：101例如：Select round(100.256,2) from dual结果：100.26当n=-1时，表示取整数的四舍五入，得到一个个位是0的整数，如下:例如：Select round(103.256,-1) from dual结果：100例如：Select round(105.256,-1) from dual结果：110（2）截断：Trunc(m,n)例如：Select trunc(100.256,2) from dual结果：100.25（3）取余数：Mod(m,n)例如：Select mod(10,3) from dual结果：13.日期函数（1）可以使用SYSDATE取得当前日期，如：SELECT SYSDATE FROM DUAL。

（2）日期的运算：日期+数字=日期（若干天后的日期）、日期-数字=日期（若干天前的日期）、日期-日期=数字（两个日期之间的天数）。

如：Select sysdate+300 from dualSelect sysdate-300 from dualSelect sysdate-hiredate from emp（3）返回指定日期当月的最后一天的日期值: Last_day(d)例如：Select last_day('23-8月-07') from dual结果：2007-8-31（4）下一个星期几的日期（1<=integer<=7）: Next_day(d,[day|integer]) 例如：Select next_day('5-5月-08','星期三') from dual结果：2008-5-7注意：integer =1对应的是星期日，则星期三对应的integer=4。

例如：Select next_day('5-5月-08',4) from dual结果：2008-5-7（5）求若干月后的日期，d是日期,n表示月数: Add_months(d,n)例如：Select add_months(sysdate,12) from dual结果：2008-8-1 19:47:57（6）返回两个日期之间的月数：Months_between(d1,d2)例如：Select months_between('06-5月-13','06-5月-08') from dual结果：604.转换函数（1）数字日期格式化：To_char( d | n [,fmt] )注意：d表示日期，n表示数值，fmt为转换的格式例如：Select to_char(sysdate,'yyyy"年"MM"月"fmDD"日" hh24:mi:ss' from dual结果：2007年08月01日 16:40:54注意：加入“fm”可以除去多余的0，“hh24”表示24小时制格式例如：Select to_char(sysdate,'yyyy"年"fmMM"月"fmDD"日" hh24:mi:ss') from dual结果：2007年8月01日 16:40:54注意：使用数字格式化是“9”表示一位数字，“L”表示当前语言环境下的货币符号！例如：Select to_char(1234567890,'999,999,999,999') from dual结果：1,234,567,890例如：Select to_char(1234567890,'L999,999,999,999') from dual结果：￥1,234,567,890（2）字符串转换为日期数据类型：To_date( char [,fmt] )例如：Select to_date('2007-8-1','yyyy-mm-dd') from dual结果：01-08月-07（3）将数椐字符串转换为 number 数据类型：To_number(char)例如：Select to_number('100')+ to_number('123') from dual结果：223注意：不使用此函数也可以完成运算！例如：Select '100'+ '123' from dual结果：2235.通用函数（1）处理NULL的函数：NVL(字段,值)注意：当某字段为NULL时，则此字段经过四则运算后的就结果也为空！使用了NVL(字段,值)后，当字段为空时Oracle就会使用用户为字段设置的值进行计算！例如：select ename , NVL(comm,10)*12 from emp（2）多值判断函数：DECODE(数值|字段,判断值1,显示值1 ,判断值2,显示值2,…)例如：Select DECODE('li','li','李','zhi','志') from dual结果：李例如：select job,decode(job,'CLERK','办事员') from emp复杂查询1.多表查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] …FROM 表名 [别名] [,表名 [别名]] [,表名 [别名]]…[WHERE 条件(s)][ORDER BY [字段 [ASC | DESC]] [,字段 [ASC | DESC]] …]注意：当不知道一个表中的数据量时，应先查询表中的数据量，若查询所有的数据，当表中的数据量很大时就很容死机！查询数据量的语句如下：SELECT count(*) FROM 表名（1）多表查询时，当多个表中有相同的字段时，要使用该字段应该在字段前加上表名，即“表.字段”。