oracle学习笔记
以前学数据库的时候用的是微软的MS SQL Server,到了这边，公司用是Oracle数据库，所以这几天直在看oracle数据库方面的知识，还没有完全看完，把看到的先做个笔记，供大家参考，希望能帮助也在学Oracle的同学，后面还会不断的更新！
字符类
char 定长 最大2000个字符。
例子：username char(10) ‘小韩’前四个字符放‘小韩’，后添6个空格补全 如‘小韩’
varchar2(20) 变长 最大4000个字符。
例子：username varchar2（10） ‘小韩’ oracle分配四个字符。这样可以节省空间
clob (character large object) 字符型大对象 最大4G
数字型
number 范围 -10的38次方 到 10的38次方
可以表示整数，也可以表示小数
例子：number(5,2) 表示一位小数有5位有效数，2位小数 （范围：-999.99到999.99 ）
number(5) 表示一个5位整数 范围99999到-99999
日期类型
date 包含年月日和时分秒 oracle默认格式 1-1月-1999
timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。
blob 二进制数据 可以存放图片/声音 4G 一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。
建表
--学生表
create table student ( ---表名
xh number(4), --学号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
);
增
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女');
改
UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001';
删
DELETE FROM student;
查
DESC emp; --查看表结构
SELECT DISTINCT deptno, job FROM emp; --取消重复的行
SELECT * FROM student WHERE birthday IS null; --查看记录birthday为空的记录
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;
1 SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; --如果comm为null，则默认等于0 2 SELECT ename "姓名", sal*12 AS "年收入" FROM emp; --使用列名 3 4 SELECT ename || ' is a ' || job FROM emp; --\\表示连接字符串 5 SELECT ename,sal FROM emp WHERE ename like 'S%'; --%表示0到多个字符 6 SELECT ename,sal FROM emp WHERE ename like '__O%'; --_表示任意单个字符 7 8 SELECT * FROM emp WHERE empno in (7844, 7839,123,456); --使用in 9 10 SELECT * FROM emp ORDER by sal; --升序默认是asc 11 SELECT * FROM emp ORDER by sal desc; --降序默认是desc 12 13 -----数据分组 max，min， avg， sum， count -------------------------------------------- 14 SELECT MAX(sal),min(sal) FROM emp ; 15 SELECT AVG(sal) FROM emp; 16 SELECT COUNT(*) FROM emp; 17 18 -----group by 和 having子句------------------------------------------------------------- 19 SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; --group by用于对查询的结果分组统计 20 21 SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(sal) < 2000; --having子句用于限制分组显示结果 22 23 24 -----多表查询------------------------------------------------------------------------------------- 25 SELECT e.ename, e.sal, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; --where后面的语句是防止笛卡尔效应 26 27 -----子查询------------------------------------------- 28 29 -----单行子查询是指只返回一行数据的子查询语句-------------------------------------------- 30 SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH'); 31 32 -----多行子查询指返回多行数据的子查询-------------------------------------------- 33 SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); --注意：不能用job=..，因为等号=是一对一的 34 35 SELECT ename, sal, deptno FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30); --all的用法 36 SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30); --any的用法 37 38 -----多列子查询是指查询返回多个列数据的子查询语句-------------------------------------------- 39 SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH'); --查询和SMITH部门和工作一样的职位 40 41 -----把查询结果看做是一张子表----------------------------------------------------------------- 42 SELECT e.ename, e.deptno, e.sal, ds.mysal FROM emp e, (SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno) ds WHERE e.deptno = ds.deptno AND e.sal > ds.mysal; 43 44 45 -----使用子查询插入数据 ------------------------------------------------------------------------------ 46 47 --emp表中10号部门的数据导入到新表中--------------------------------------------------------------------------- 48 insert into kkk (myId, myName, myDept) select empno, ename, deptno from emp where deptno = 10; 49 50 --员工SCOTT的岗位、工资、补助与SMITH员工一样--------------------------------------------------------------------------- 51 update emp set(job, sal, comm)=(select job, sal, comm from emp where ename='SMITH') where ename='SCOTT'; 52 53 ------oracle的函数-------------------------------------------------- 54 --lower(char)：将字符串转化为小写的格式。 55 --upper(char)：将字符串转化为大写的格式。 56 --length(char)：返回字符串的长度。 57 --substr(char,m,n)：取字符串的子串n代表取n个的意思，不是代表取到第n个 58 --replace(char1,search_string,replace_string) 59 --instr(char1,char2,[,n[,m]])取子串在字符串的位置 60 61 select lower(ename) from emp; --问题：将所有员工的名字按小写的方式显示 62 select upper(ename) from emp; --问题：将所有员工的名字按大写的方式显示。 63 select * from emp where length(ename)=5; --问题：显示正好为5个字符的员工的姓名。 64 select substr(ename,1,3) from emp; --问题：显示所有员工姓名的前三个字符。 65 select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;--问题：以首字母大写,后面小写的方式显示所有员工的姓名。 66 select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;--问题：以首字母小写,后面大写的方式显示所有员工的姓名。 67 select replace(ename,'A', '我是老虎') from emp; --问题：显示所有员工的姓名，用“我是老虎”替换所有“A” 68 69 ----数学函数--- 70 /* 71 数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包括cos，cosh，exp，ln, log，sin，sinh，sqrt，tan，tanh，acos，asin，atan，round，我们讲最常用的： 72 round(n,[m]) 该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。 73 trunc(n,[m]) 该函数用于截取数字。如果省掉m，就截去小数部分，如果m是正数就截取到小数点的m位后，如果m是负数，则截取到小数点的前m位。 74 mod(m,n) 75 floor(n) 返回小于或是等于n的最大整数 76 ceil(n) 返回大于或是等于n的最小整数 77 */ 78 select trunc(sal/30), ename from emp;--问题：显示在一个月为30天的情况下，所有员工的日薪金，忽略余数。 79 or 80 select floor(sal/30), ename from emp; 81 82 83 ----日期函数-------- 84 /* 85 日期函数用于处理date类型的数据。 86 默认情况下日期格式是dd-mon-yy 即12-7月-78 87 (1)sysdate: 该函数返回系统时间 88 (2)add_months(d,n) 89 (3)last_day(d)：返回指定日期所在月份的最后一天 90 */ 91 92 select * from emp where sysdate>=add_months(hiredate,8); --问题：查找已经入职8个月多的员工 93 94 select ename, hiredate from emp where sysdate>=add_months(hiredate,12*10); --问题：显示满10年服务年限的员工的姓名和受雇日期。 95 96 select floor(sysdate-hiredate) "入职天数",ename from emp; --问题：对于每个员工，显示其加入公司的天数。 97 or 98 select trunc(sysdate-hiredate) "入职天数",ename from emp; 99 100 select hiredate,ename from emp where last_day(hiredate)-2=hiredate; --问题：找出各月倒数第3天受雇的所有员工。 101 102 ----转换函数 ---------- 103 --to_char 104 select ename, to_char(hiredate, 'yyyy-mm-dd hh24:mi:ss') from emp; 105 /* 106 yy：两位数字的年份 2004-->04 107 yyyy：四位数字的年份 2004年 108 mm：两位数字的月份 8月-->08 109 dd：两位数字的天 30号-->30 110 hh24： 8点-->20 111 hh12：8点-->08 112 mi、ss-->显示分钟\秒 113 114 9：显示数字，并忽略前面0 115 0：显示数字，如位数不足，则用0补齐 116 .：在指定位置显示小数点 117 ,：在指定位置显示逗号 118 $：在数字前加美元 119 L：在数字前面加本地货币符号 120 C：在数字前面加国际货币符号 121 G：在指定位置显示组分隔符、 122 D：在指定位置显示小数点符号(.) 123 124 */
