培训机构的Oracle学习笔记2

1、日期转换 -连接查询 -广义笛卡尔积 -内连接，等值连接 -自然连接 -外连接 -自连接 -子查询1、select to_date(02-09-49,dd-mm-yy) from dual;2、select to_date(02-09-59,dd-mm-rr) from dual;3、如果有佣金，则将佣金提升 10%，如果没有佣金，则使用薪水的2%作为佣金select empno,ename,sal,comm,nvl2(comm,comm+comm*0.1,sal*0.02) 佣金 from emp;4、如果有佣金，则佣金不变，如果没有佣金，则为每个员工设置100 佣金select empno

2、,ename,sal,comm,nvl2(comm,comm,100) 佣金 from emp;5、如果佣金为 0，则去掉佣金，即将佣金设置为空select empno,ename,nullif(comm,0) from emp;6、根据不同的工作，增加不同的薪水select empno,ename,job,sal,case job when SALESMAN then sal+sal*0.1 when CLERK then sal+sal*0.2 end 提升薪水 from emp;连接查询7、广义笛卡尔积，没有连接谓词，得到的结果数量为N 张表的记录数乘积，为其他连接提供了理论基础，对实际

3、应用没有意义select * from student,scores;8、内连接，等值连接，在广义笛卡尔积的基础上做条件赛选select * from student,scores where student.sid=scores.stuid ；9、自然连接，是等值连接的特殊情况，目的是为去除重复项，采用的方法是指定显示的字 段项select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;10、外连接select * from emp right outer join dept on dept.dept

4、no=emp.deptno;dept left outer join emp dept.deptno=emp.deptno(+);dept right outer join empdept.deptno(+)=emp.deptno;emp left outer join dept emp.deptno=dept.deptno(+);emp right outer join dept emp.deptno(+)=dept.deptno;11、自连接，表自身连接，使用表的两个副本连接 查询所有员工和他的上司的信息。注意连接谓词 select e2.*,e1.* from emp e1,emp e2

5、 where e2.mgr=e1.empno; 查询所有比 scott 的薪水低的员工。注意连接谓词select * from emp e1,emp e2 where e2.ename=SCOTT and e2.sale1.sal;查询 ford 的下属。注意连接谓词select * from emp e1,emp e2 where e1.mgr=e2.empno and e2.ename=FORD;复合条件连接查询，注意连接谓词查询 ACCOUNTING 部门中员工薪水大于 2000SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND

6、 DEPT.DNAME=ACCOUNTING AND EMP.SAL2000;子查询一个 select from where 称为一个查询块，将一个查询块的结果作为另一个查询块的条件查询所有比 scott 薪水低的员工select * from emp where emp.sal ALL(select sal from emp where deptno in (select deptno from dept where dname=SALES);查询所有学生的数学成绩 select ,score from scor,student where scid in(sele

7、ct sc.id from sc where sc.code in (select course.code from course where ame=数学)单行函数 months_between add_between next_day last_day round lower upper initcap concat substr instr lpad trim replace round trunc mod to_char可作用于每一行 为每一行返回一个值1、求所有员工入职的月份数select months_between(sysdate,hiredate) from emp;2、求所有

8、员工入职 3 个月的日期select add_between(hiredate,3) from emp;3、select next_day(1-1 月-1985,星期三) from dual;4、select last_day( 1-2 月 -1984) from dual;5、round 默认以天为舍入模式select sysdate,round(sysdate) from dual; 舍入日select sysdate,round(sysdate,year) from dual; 舍入年select sysdate,round(sysdate,day) from dual; 舍入周sele

9、ct sysdate,round(sysdate,month) from dual; 舍入月6、TRUNC 截断，只舍不入用日期做算数运算7、求每一个员工工作年数select months_between(sysdate,hiredate)/12 from emp;7、求每一个员工工作周select (sysdate-hiredate)/7 from emp;字符函数8、大写转换小写select lower(ename) from emp;select lower(CDEF) FROM dual;9、小写转大写select upper(aaabbc) from dual;9、 INITCAP

10、将单词的首写字母转换成大写select INITCAP(adcd mmni) from dual;10、字符串连接 concat 同|select concat(你好,ename) from emp;11、substr 截取字符串select substr(helloworld,2,5) from dual;select substr(ename,1,3) from emp12、instr 求得给定字符串在原字符串中的位置select instr(helloworld,or) from dual;13、 lpad 在字符串左边使用指定的符号如*补充剩余符号select lpad(nihao,2

11、0,*) from dual;14、trim 截取首尾字符串select trim(h from hellowhorlh) from dual;15、replaceselect replace(hellwerd,er,or) from dual;16、数字函数 round trunc 四舍五入，取模 modselect round(23.335,2) from dual; 23.34select trunc(23.335,2) from dual; 23.33select mod(10,3) from dual; 1select round(55.335,-2) from dual;17、字符

12、转换函数： to_char 将日期转换成字符串select to_char(sysdate) from dual;select to_char(sysdate,yyyy-mm-dd hh24:mi:ss) from dual;select to_char(123432,999,999,999) from dual;cycle建议此项不要设置 )创建删除序列 同义词 作用：生成唯一值，一般用于主键自增，如果用于主键自增，则不需要定义 创建create sequence s_estart with 1 从 1 开始increment by 1 每条用一次该序列的 nextval 自增 1maxva

13、lue 1000( 如果用于主键，建议此项不要设置 )cache 10 缓存值cycle 是否循环， 当达到最大值则从初始值开始重新自增(如果用于主键，删除drop sequence s_e;同义词 创建私有同义词 创建公共同义词首先必须保证系统管理员将 create public synonym 权限授予该用户 grant create public synonym to scott create public synonym sep for emp; 公有同义词可以和表名相同，但在当前模式下优先使用表名create public synonym emp for emp;删除私有的同义词dr

14、op synonym s;删除公有的同义词 ,当前模式必须具备删除公有同义词的权限，grant drop public synonym to ty;drop public synonym s;pl/sql 变量赋值 变量名 := 值 select 值 into 变量名 if else end if 嵌套 接受输入框的值作为 条件查询pl/sql 变量赋值的方法之一：变量名:= 值declarei number:=10;j int:=20;userName varchar2(10):= 张三 ;userSex char(2)=女；f boolean:=TRUE;d date；begind:=sy

15、sdate；userName:=王五;dbms_output.put_line(i)；dbms_output.put_line(j);dbms_output.put_line(userName); dbms_output.put_line(userSex);if f thendbms_output.put_line(d);end if;end;pl/sql 变量赋值的方法之二： select 值 into 变量名 declarei number;beginselect sal into i from emp where empno=7369; dbms_output.put_line(i);e

16、nd;求园面积declarei constant number:=3.14;r number:=23;a number;begin-a:=r*r*i;- 表示单行注释相当于 java 中的 /select r*r*i into a from dual; dbms_output.put_line( 园的面积是 :|a); end;用 pl/sql 程序块输出 smith 的详细信息 DECLAREENO VARCHAR2(10);ENA VARCHAR2(10);BEGINSELECT EMPNO ,ENAME INTO ENO,ENA FROM EMP WHERE EMPNO=7369;DBM

17、S_OUTPUT.put_line(编号为：|ENO的员工姓名是：|ENA);END;字段类型DECLAREENO emp.empno%type;ENA emp.ename%type;jo emp.job%type;sa emp.sal%type;com m%type;dno emp.deptno%type;hidate emp.hiredate%type;BEGINSELECTEMPNO,ENAME,job,sal,comm,deptno,hiredateINTOENO,ENA,jo,sa,com,dno,hidate FROM EMP WHERE EMPNO=7369;DBMS_OUTPU

18、T.put_line(编号为：|ENO的员工姓名是：|ENA|jo|com|dno|hidate);END;接受一个值作为条件查询，输入框复制的变量如果是字符型或日期型，在用,如果不是，则不用 declarere emp%rowtype;EN EMP.ENAME%TYPE;beginEN:=& 员工姓名 ;select * into re from emp where ename=EN; dbms_output.put_line(re.empno|re.ename|re.job|re.mgr|re.sal|re.deptno|m); end;IF ELSE END IF 嵌套declarei number;er emp%rowtype;en em