oracle7天学习经典笔记.docx

1、oracle7天学习经典笔记-*-2010.9.26 oracle第2天2课-建表语句create table student( id number(4), name varchar(20), sex char(2), birthday date, sal number(7,2);-修改表的信息-（1）添加字段alter table student add (score number);-(2)修改字段长度alter table student modify (score number(3,1);-(3)删除一个字段alter table student drop column score;-

2、查看表的结构（注意，此命令只有用在命令窗口才能够显示表的结构信息）desc student-打开显示时间的操作开关（注意，此命令只有用在命令窗口才能够显示表的结构信息,-此命会将后面的查询操作执行时间显示出来）set timing on-查询select *from student-如果查询某个字段为空的信息使用 is关键字select *from student where birthday is null-插入（注意：时间的插入写法）insert into student values(1,yoyo1,男,1-2月-1988,123.11)-转换时间插入的格式输入法，为中国人习惯型的输入格

3、式alter session set nls_date_format=yyyy-mm-dd-转换时间输入格式后的插入写法insert into student values(1,yoyo1,男,1988-3-2,123.11)-修改update 表名 set 字段名1=值1，字段名2=值2. where 条件-设置一个保存点savepoint a-删除表信息delete from student-回滚rollback-回滚后可以查询刚刚删除的信息select *from student-删除-(1)删除表的内容，不会删除自动增长列已经增长到的某个值的记录delete from student

4、-（2）删除表结构drop table student-（3）删除表的内容，速度快，删除后没有日志记录,自动增长列会从1重新开始记录truncate table student-*-2010.9.26 oracle第2天3课select *from empselect *from dept-查询速度的区别-使用 * 的速度比指明 字段名 的速度 慢-查询smith的工作，薪水和部门（注意：SMITH 内容的大小写，关键字不区分大小写）select deptno,job,sal from emp where ename=SMITH-查询职工一年的工资（sal*13）-查询结果一样，问题(如果查询

5、字段中有一个为null值，那么该字段跟其他字段合起来查询就为null值)select (sal*13)+(comm*13) 年工资 from emp select sal*13+comm*13 年工资 from emp -解决( nvl(comm,0)意思：如果comm字段为空，那么该字段值就为0，如果不为空那么就以该字段值为准进行下一步运算 )select sal*13+nvl(comm,0)*13 年工资 from emp -如何显示工资高于3000的员工select ename,sal from emp where sal3000-如何显示工资在800到1600的员工(注意：使用betw

6、een 800 and 1600，查询出的结果包含800，和1600的值的)select ename,sal from emp where sal between 800 and 1600-如何查找1982.1.1后入职的员工(注意： 时间输入 及使用 大于 符合)select ename,hiredate from emp where hiredate1-1月-1982-或者alter session set nls_date_format=yyyy-mm-ddselect ename,hiredate from emp where hiredate1982-1-1-如何使用like操作符-

7、 %表示：任意0-多个字符，_表示：任意单个字符，?表示：所有select ename,sal from emp where ename like S%-查询员工名字第3个字符是O的员工select ename,sal from emp where ename like _O%-此处是2条_(下划线)-where条件中使用in-查询员工号为：7369,7499,7521select ename,empno from emp where empno in(7369,7499,7521)-查询没有上级的员工select *from emp where mgr is null-查询工资大于500或者

8、职位为MANAGER，并且名字是以S开头的员工select *from emp where (sal500 or job=MANAGER) and ename like S%-order by (默认asc：低到高，高到底：desc)select *from emp sal order by sal desc-按照部门号升序而雇员的工资降序排列select *from emp order by empno asc , sal desc-按照部门号升序而雇员的入职降序排列select *from emp order by empno asc , hiredate desc-使用列的别名排序，使用

9、年薪排名select ename,sal*12 年薪 from emp order by 年薪 ascselect ename,sal*12+nvl(comm,0)*12 年薪 from emp order by 年薪 asc-分页查询-复杂查询 -如何显示所有员工的最高，最低工资 select MAX(sal) from emp select MIN(sal) from emp -查询最高工资，最低工资员工的详细信息(注意:使用子查询-把查询出的结果看做一个结果集，来筛选)select *from emp where sal=(select max(sal) from emp)-查询工资高于

10、平均工资的员工信息select *from emp where sal(select avg(sal) from emp)-grop by 和 having子句-如何显示每个部门的最高工资，最低工资,并按照部门编号降序排列select avg(sal),max(sal),deptno from emp group by deptno order by deptno desc-显示每个部门的每种岗位的平均工资和最低工资select avg(sal),min(sal),deptno,job from emp group by deptno,job-显示评价工资低于2000的部门号和它的平均工资se

11、lect avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000-分组统计的规律-分组函数只能出现在选择列表、having、order by子句中-顺序：group by,having,order by-笛卡尔集：多表查询的条件是，至少不能少于表的个数减1-查询员工的姓名，工资，工资级别select *from salgradeselect *from empselect *from deptselect a.ename,a.sal,b.grade from emp a,salgrade b where a.s

12、al between b.losal and b.hisal-显示雇员名，工资，及所在部门名字，并按部门排序select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno order by b.deptno-自连接(单独一个表多次利用自己)-如何显示某个员工的上级领导的名字(难点! 把一张表当2张表使用)select a.ename,b.ename from emp a,emp b where a.mgr=b.empno -子查询又称嵌套查询（一个查询语句里面出现多个select关键字，也就是一个以查询结果为条件的语

13、句）-如何显示与smith同一部门的所有员工-数据库在执行sql语句时，是从左到右扫描执行，而写sql语句又是从右往左写的-写sql语句时，如果条件是多个，那么把第一次查询出来结果最多的那个放在左边，提高查询性能。最好从多的开始查询再到少的查询select ename 名字,deptno 同一部门编号 from emp where deptno=(select deptno from emp where ename=SMITH)-多行子查询-如何查询和部门10相同的雇员的名字、岗位、工资、部门号-返回的是多行的语句，那么条件使用in关键字，而不用 = select *from emp wher

14、e job in (select job from emp where deptno=10)-使用all关键字-如何查询工资比部门30的所有员工的工资高的员工的姓名、工资、部门号-方法一select ename,sal,deptno from emp where sal all (select sal from emp where deptno=30)-方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)select ename,sal,deptno from emp where sal all (select max(sal) from emp where deptno=30)

15、-any关键字的使用-如何显示工资比部门30号的任意一个员工工资高的员工姓名，工资，部门号-方法一select ename,sal,deptno from emp where sal any (select sal from emp where deptno=30)-方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)select ename,sal,deptno from emp where sal any (select max(sal) from emp where deptno=30)-多列子查询(如果根据查询的结果集里面包含2列，而不是1列)-查询与smith的部门和岗

16、位完全相同的所有雇员-注意：列匹配顺序要一样select *from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH)-如何显示高于自己部门平均工资的员工的信息(-难点 -)-1.首先查询各个部门的平均工资和部门号（其中 a 代表平均工资的 别名）select deptno,avg(sal) a from emp group by deptno-2.把上面的查询结果看做是一张 子表 ,并给他取个别名(其中 a1 代表上面所查询的结果的表的别名)-给表取别名不能加as，给列取别名可以加asselect en

17、ame,a2.sal,a2.deptno,a1.a from emp a2,(select deptno,avg(sal) a from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sala1.a order by deptno-分页查询（oracle分页一共有3种方式）-按雇员的id号升序查询-方法一：rownum分页select a1.*,rownum rn from (select *from emp) a1-问题：如何显示第6条到10条的记录？使用分页显示-1.rownum分页( select *from emp )

18、-2.显示 rownumoracle分配的 行号( select a1.*,rownum rn from (select *from emp) a1 )-3.显示某一部分的信息（rownum只能用一次，不能用多次，使用多次试不会查询出数据结果的）select a1.*,rownum rn from (select *from emp) a1 where rownum=10-不能写成如下这样：select a1.*,rownum rn from (select *from emp) a1 where rownum=6-4.完成分页效果机制select *from (select a1.*,row

19、num rn from (select *from emp) a1 where rownum=6-5.几个查询变化-a.如果指定查询列，只需修改最底层的查询：select *from empselect ename,sal from emp -b.如果是排序，只需修改最底层的查询:select *from empselect ename,sal from emp order by sal-方法二：（最快）-根据ROWID来分select *from t_xiaoxi where rowid in (select rid from (select rownum rn,rid from (sele

20、ct rowid rid,cid from t_xiaoxi order by cid desc) where rownum0080) order by cid desc-方法三：按分析函数来分（最慢）select *from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk9980-查询总共有多少记录：使用count()函数-用查询结果创建表（常用技巧）create table myemp2 (id,name,sal) as select empno,ename,sal from emp

21、-查看表的结构：desc myemp2select *from myemp2-复杂查询，合并查询，使用关键字：union ,union all,intersect,minus-(1)union 该操作符用于取得2个结果集的并集，使用时会自动去掉结果集中的重复行-集合查询速度比and，between等快select ename,sal,job from emp where sal2500 union select ename,sal,job from emp where job=MANAGER-(2)union all 会出现重复，取并集select ename,sal,job from emp

22、 where sal2500 union all select ename,sal,job from emp where job=MANAGER-(3)intersect 取交集-(4)minus 取差集-创建数据库有2种方法-(1)使用工具；-dbca【数据库创建配置助手】-(2)。*-2010年10月1日-第3章 事务与加锁-时间转换函数to_date(1988-12-1,yyyy-mm-dd)-设置回滚点：注意（只有在事务没有提交前管用）savepoint a1;rollback to a1;-在java程序中如何使用事务-加入事务处理(在java代码中写入)-conn.setAutoC

23、ommit(false);/设置不能提交点 -只读事务：指只允许执行查询的操作，而不允许执行dml（增，删，改）操作的事务-使用只读事务可以确保用户只能取得某时点的数据set transaction read only-1.sql函数的使用lower(char):将字符串转换为小写upper():-大写length(): substr(char,m,n):截取字符，m 代表从第几的个字符开始截取 ，n 代表取3个-以首字母大写方式显示所有员工的信息-第一步：取首写字母大写select upper(substr(ename,1,1) from emp-第二步：取后面小写部分select lowe

24、r(substr(ename,2,length(ename)-1) from emp-合并：使用 | 符合select upper(substr(ename,1,1) | lower(substr(ename,2,length(ename)-1) from emp-2.sql函数的使用replace(char1,search_string,replace_string)-替换字符串instr(char1,char2,n,m)-找函数的位置-3.sql（数学）函数的使用:n 代表字字符round(n,m):四舍五入trunc(n,m)：截取数字mod(m,n)：取模floor(n)：返回=n的最

25、大整数-表dual是用来做测试用的表select mod(10,3) from dual-显示在一个月为30天的情况所有员工的日新金，忽略余数使用floor,trunc.-日期函数-默认情况：dd-mm-yy(即12-10月-2010)sysdate：获取系统时间add_months(时间或者字段,所要加的月份数)last_day():返回指定日期所在月份的最后一天-dual表位测试表select sysdate from dual-查询当前时间-查询入职超过300个月的员工select *from emp where sysdateadd_months(hiredate,300)-查询员工入

26、职的天数select sysdate-hiredate 入职天数,ename from emp-去掉小数select trunc(sysdate-hiredate) 入职天数,ename from emp-找出各月倒数第3天受雇的所有员工select hiredate,last_day(hiredate) from empselect hiredate,ename from emp where last_day(hiredate)-2=hiredate-转换函数（隐形转换）to_char(要转换的时间字符,转换成什么格式):select ename,to_char(hiredate,yyyy-

27、mm-dd hh24:mi:ss) from emp-在插入时间数据时，如果插入需要精确到分钟，秒，那么显示的时候就需要用该函数转换一下，才会显示到时、分、秒-to_char()用于人民币的格式转换select to_char(sal,L99,999.99) from empselect to_char(sal,$99,999.99) from emp-显示1980年,12月份入职的员工select *from emp where to_char(hiredate,yyyy)=1980select *from emp where to_char(hiredate,mm)=12-to_date(

28、)函数.-系统函数sys_context()：-(1)terminal:当前会话客户所对应的终端的标识符select sys_context(USERENV,db_name) from dual-查询当前使用的数据库-(2):languageselect sys_context(USERENV,language) from dual-(3):session_user 当前用户select sys_context(USERENV,session_user) from dual-(4):current_schema 当前方案名（方案名与用户名一样）用户和方案的关系-用户和方案的关系select s

29、ys_context(USERENV,current_schema) from dual-数据库管理员sys(高于后者)与system用户的区别:(1)存储的数据的重要性不同(2)权限不同- sys用户必需以as sysdba或者as sysoper身份登录- system用户可以以normal登录，但是就是一个普通用户-注意：system可以以sysdba登录，但是如果以这样登录的话，那么就是一个sys用户了，具有他的所有权限sysdba(高于后者)与sysoper的区别：sysoperdba(普通用户)前者可以打开、关闭数据库，创建和删除，后者不行，前者数据恢复可以是部分，可以是全部，而后者是全部恢复-shutdown（关闭数据库）-startup（启动数据库）-管理初始化的参数-show parameter(该行没有运行出来，有点问题)数据库表的逻辑备份与恢复-逻辑备份和物理备份前者：是将数据库的对象导出到一个磁盘里面存放（备份） 将磁盘的信息导入到数据库（恢复）-数据库在运行状态才