oracle7天学习经典笔记.docx
《oracle7天学习经典笔记.docx》由会员分享，可在线阅读，更多相关《oracle7天学习经典笔记.docx（39页珍藏版）》请在冰豆网上搜索。
oracle7天学习经典笔记
--************************************************
--2010.9.26oracle第2天2课
--建表语句
createtablestudent（
idnumber（4）,
namevarchar（20）,
sexchar
（2）,
birthdaydate,
salnumber（7,2）
）;
--修改表的信息
--
（1）添加字段
altertablestudentadd（scorenumber）;
--
（2）修改字段长度
altertablestudentmodify（scorenumber（3,1））;
--（3）删除一个字段
altertablestudentdropcolumnscore;
--查看表的结构（注意，此命令只有用在命令窗口才能够显示表的结构信息）
descstudent
--打开显示时间的操作开关（注意，此命令只有用在命令窗口才能够显示表的结构信息,
--此命会将后面的查询操作执行时间显示出来）
settimingon
--查询
select*fromstudent
--如果查询某个字段为空的信息使用is关键字
select*fromstudentwherebirthdayisnull
--插入（注意：
时间的插入写法）
insertintostudentvalues（1,'yoyo1','男','1-2月-1988','123.11'）
--转换时间插入的格式输入法，为中国人习惯型的输入格式
altersessionsetnls_date_format='yyyy-mm-dd'
--转换时间输入格式后的插入写法
insertintostudentvalues（1,'yoyo1','男','1988-3-2','123.11'）
--修改
update表名set字段名1=值1，字段名2=值2...where条件
--设置一个保存点
savepointa
--删除表信息
deletefromstudent
--回滚
rollback
--回滚后可以查询刚刚删除的信息
select*fromstudent
--删除
--
（1）删除表的内容，不会删除自动增长列已经增长到的某个值的记录
deletefromstudent
--
（2）删除表结构
droptablestudent
--（3）删除表的内容，速度快，删除后没有日志记录,自动增长列会从1重新开始记录
truncatetablestudent
--**********************************************
--2010.9.26oracle第2天3课
select*fromemp
select*fromdept
--查询速度的区别
--使用*的速度比指明字段名的速度慢
--查询smith的工作，薪水和部门（注意：
SMITH内容的大小写，关键字不区分大小写）
selectdeptno,job,salfromempwhereename='SMITH'
--查询职工一年的工资（sal*13）
--查询结果一样，问题（如果查询字段中有一个为null值，那么该字段跟其他字段合起来查询就为null值）
select（sal*13）+（comm*13）年工资fromemp
selectsal*13+comm*13年工资fromemp
--解决（nvl（comm,0）意思：
如果comm字段为空，那么该字段值就为0，如果不为空那么就以该字段值为准进行下一步运算）
selectsal*13+nvl（comm,0）*13年工资fromemp
--如何显示工资高于3000的员工
selectename,salfromempwheresal>3000
--如何显示工资在800到1600的员工（注意：
使用between800and1600，查询出的结果包含800，和1600的值的）
selectename,salfromempwheresalbetween800and1600
--如何查找1982.1.1后入职的员工（注意：
时间输入及使用大于符合）
selectename,hiredatefromempwherehiredate>'1-1月-1982'
--或者
altersessionsetnls_date_format='yyyy-mm-dd'
selectename,hiredatefromempwherehiredate>'1982-1-1'
--如何使用like操作符
--%表示：
任意0-多个字符，_表示：
任意单个字符，?
表示：
所有
selectename,salfromempwhereenamelike'S%'
--查询员工名字第3个字符是O的员工
selectename,salfromempwhereenamelike'__O%'--此处是2条_（下划线）
--where条件中使用in
--查询员工号为：
7369,7499,7521
selectename,empnofromempwhereempnoin（7369,7499,7521）
--查询没有上级的员工
select*fromempwheremgrisnull
--查询工资大于500或者职位为MANAGER，并且名字是以S开头的员工
select*fromempwhere（sal>500orjob='MANAGER'）andenamelike'S%'
--orderby（默认asc：
低到高，高到底：
desc）
select*fromempsalorderbysaldesc
--按照部门号升序而雇员的工资降序排列
select*fromemporderbyempnoasc,saldesc
--按照部门号升序而雇员的入职降序排列
select*fromemporderbyempnoasc,hiredatedesc
--使用列的别名排序，使用年薪排名
selectename,sal*12年薪fromemporderby年薪asc
selectename,sal*12+nvl（comm,0）*12年薪fromemporderby年薪asc
--分页查询
--复杂查询
--如何显示所有员工的最高，最低工资
selectMAX（sal）fromemp
selectMIN（sal）fromemp
--查询最高工资，最低工资员工的详细信息（注意:
使用子查询--把查询出的结果看做一个结果集，来筛选）
select*fromempwheresal=（selectmax（sal）fromemp）
--查询工资高于平均工资的员工信息
select*fromempwheresal>（selectavg（sal）fromemp）
--gropby和having子句
--如何显示每个部门的最高工资，最低工资,并按照部门编号降序排列
selectavg（sal）,max（sal）,deptnofromempgroupbydeptnoorderbydeptnodesc
--显示每个部门的每种岗位的平均工资和最低工资
selectavg（sal）,min（sal）,deptno,jobfromempgroupbydeptno,job
--显示评价工资低于2000的部门号和它的平均工资
selectavg（sal）,max（sal）,deptnofromempgroupbydeptnohavingavg（sal）>2000
--分组统计的规律
--分组函数只能出现在选择列表、having、orderby子句中
--顺序：
groupby,having,orderby
--笛卡尔集：
多表查询的条件是，至少不能少于表的个数减1
--查询员工的姓名，工资，工资级别
select*fromsalgrade
select*fromemp
select*fromdept
selecta.ename,a.sal,b.gradefromempa,salgradebwherea.salbetweenb.losalandb.hisal
--显示雇员名，工资，及所在部门名字，并按部门排序
selecta.ename,a.sal,b.dnamefromempa,deptbwherea.deptno=b.deptnoorderbyb.deptno
--自连接（单独一个表多次利用自己）
--如何显示某个员工的上级领导的名字（难点!
!
!
把一张表当2张表使用）
selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno
--子查询又称嵌套查询（一个查询语句里面出现多个select关键字，也就是一个以查询结果为条件的语句）
--如何显示与smith同一部门的所有员工
--数据库在执行sql语句时，是从左到右扫描执行，而写sql语句又是从右往左写的
--写sql语句时，如果条件是多个，那么把第一次查询出来结果最多的那个放在左边，提高查询性能。
最好从多的开始查询再到少的查询
selectename名字,deptno同一部门编号fromempwheredeptno=（selectdeptnofromempwhereename='SMITH'）
--多行子查询
--如何查询和部门10相同的雇员的名字、岗位、工资、部门号
--返回的是多行的语句，那么条件使用in关键字，而不用‘=’
select*fromempwherejobin（selectjobfromempwheredeptno=10）
--使用all关键字
--如何查询工资比部门30的所有员工的工资高的员工的姓名、工资、部门号
--方法一
selectename,sal,deptnofromempwheresal>all（selectsalfromempwheredeptno=30）
--方法二（查询快点:
原因是在第二次筛选结果集中他的筛选的范围要小店）
selectename,sal,deptnofromempwheresal>all（selectmax（sal）fromempwheredeptno=30）
--any关键字的使用
--如何显示工资比部门30号的任意一个员工工资高的员工姓名，工资，部门号
--方法一
selectename,sal,deptnofromempwheresal>any（selectsalfromempwheredeptno=30）
--方法二（查询快点:
原因是在第二次筛选结果集中他的筛选的范围要小店）
selectename,sal,deptnofromempwheresal>any（selectmax（sal）fromempwheredeptno=30）
--多列子查询（如果根据查询的结果集里面包含2列，而不是1列）
--查询与smith的部门和岗位完全相同的所有雇员
--注意：
列匹配顺序要一样
select*fromempwhere（deptno,job）=（selectdeptno,jobfromempwhereename='SMITH'）
--如何显示高于自己部门平均工资的员工的信息（--难点--）
--1.首先查询各个部门的平均工资和部门号（其中a代表平均工资的别名）
selectdeptno,avg（sal）afromempgroupbydeptno
--2.把上面的查询结果看做是一张子表,并给他取个别名（其中a1代表上面所查询的结果的表的别名）
--给表取别名不能加as，给列取别名可以加as
selectename,a2.sal,a2.deptno,a1.afromempa2,（selectdeptno,avg（sal）afromempgroupbydeptno）a1
wherea2.deptno=a1.deptnoanda2.sal>a1.aorderbydeptno
--分页查询（oracle分页一共有3种方式）
--按雇员的id号升序查询
--方法一：
rownum分页
selecta1.*,rownumrnfrom（select*fromemp）a1
--问题：
如何显示第6条到10条的记录？
使用分页显示
--1.rownum分页（select*fromemp）
--2.显示rownum[oracle分配的]行号（selecta1.*,rownumrnfrom（select*fromemp）a1）
--3.显示某一部分的信息（rownum只能用一次，不能用多次，使用多次试不会查询出数据结果的）
selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<=10
--不能写成如下这样：
selecta1.*,rownumrnfrom（select*fromemp）a1whererownum<=10andrownum>=6
--4.完成分页效果机制
select*from
（selecta1.*,rownumrnfrom
（select*fromemp）a1
whererownum<=10）
wherern>=6
--5.几个查询变化
--a.如果指定查询列，只需修改最底层的查询：
select*fromemp
selectename,salfromemp
--b.如果是排序，只需修改最底层的查询:
select*fromemp
selectename,salfromemporderbysal
--方法二：
（最快）
--根据ROWID来分
select*fromt_xiaoxiwhererowidin
（selectridfrom
（selectrownumrn,ridfrom
（selectrowidrid,cidfromt_xiaoxiorderbyciddesc）
whererownum<10000）
wherern>0080）
orderbyciddesc
--方法三：
按分析函数来分（最慢）
select*from
（selectt.*,row_number（）over
（orderbyciddesc）
rkfromt_xiaoxit）
whererk<10000andrk>9980
--查询总共有多少记录：
使用count（）函数
--用查询结果创建表（常用技巧）
createtablemyemp2（id,name,sal）asselectempno,ename,salfromemp
--查看表的结构：
descmyemp2
select*frommyemp2
--复杂查询，合并查询，使用关键字：
union,unionall,intersect,minus
--
（1）union该操作符用于取得2个结果集的并集，使用时会自动去掉结果集中的重复行
--集合查询速度比and，between等快
selectename,sal,jobfromempwheresal>2500union
selectename,sal,jobfromempwherejob='MANAGER'
--
（2）unionall会出现重复，取并集
selectename,sal,jobfromempwheresal>2500unionall
select
ename,sal,jobfromempwherejob='MANAGER'
--（3）intersect取交集
--（4）minus取差集
--创建数据库有2种方法
--
（1）使用工具；
--dbca【数据库创建配置助手】
--
（2）。
。
。
*******************************************************************
--2010年10月1日
--第3章事务与加锁
--时间转换函数to_date（'1988-12-1','yyyy-mm-dd'）
--设置回滚点：
注意（只有在事务没有提交前管用）
savepointa1;
rollbacktoa1;
--在java程序中如何使用事务
--加入事务处理（在java代码中写入）
--conn.setAutoCommit（false）;//设置不能提交点
--只读事务：
指只允许执行查询的操作，而不允许执行dml（增，删，改）操作的事务
--使用只读事务可以确保用户只能取得某时点的数据
settransactionreadonly
--1.sql函数的使用
lower（char）:
将字符串转换为小写
upper（）:
--大写
length（）:
substr（char,m,n）:
截取字符，m代表从第几的个字符开始截取，n代表取3个
--以首字母大写方式显示所有员工的信息
--第一步：
取首写字母大写
selectupper（substr（ename,1,1））fromemp
--第二步：
取后面小写部分
selectlower（substr（ename,2,length（ename）-1））fromemp
--合并：
使用||符合
selectupper（substr（ename,1,1））||lower（substr（ename,2,length（ename）-1））fromemp
--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的最大整数
ceil（n）：
返回>=n的最大整数
--表dual是用来做测试用的表
selectmod（10,3）fromdual
--显示在一个月为30天的情况所有员工的日新金，忽略余数
使用floor,trunc...
--日期函数
--默认情况：
dd-mm-yy（即12-10月-2010）
sysdate：
获取系统时间
add_months（时间或者字段,所要加的月份数）
last_day（）:
返回指定日期所在月份的最后一天
--dual表位测试表
selectsysdatefromdual--查询当前时间
--查询入职超过300个月的员工
select*fromempwheresysdate>add_months（hiredate,300）
--查询员工入职的天数
selectsysdate-hiredate"入职天数",enamefromemp
--去掉小数
selecttrunc（sysdate-hiredate）"入职天数",enamefromemp
--找出各月倒数第3天受雇的所有员工
selecthiredate,last_day（hiredate）fromemp
selecthiredate,enamefromempwherelast_day（hiredate）-2=hiredate
--转换函数（隐形转换）
to_char（要转换的时间字符,转换成什么格式）:
selectename,to_char（hiredate,'yyyy-mm-ddhh24:
mi:
ss'）fromemp
--在插入时间数据时，如果插入需要精确到分钟，秒，那么显示的时候就需要用该函数转换一下，才会显示到时、分、秒
--to_char（）用于人民币的格式转换
selectto_char（sal,'L99,999.99'）fromemp
selectto_char（sal,'$99,999.99'）fromemp
--显示1980年,12月份入职的员工
select*fromempwhereto_char（hiredate,'yyyy'）=1980
select*fromempwhereto_char（hiredate,'mm'）=12
--to_date（）函数...
--系统函数
sys_context（）：
--
（1）terminal:
当前会话客户所对应的终端的标识符
selectsys_context（'USERENV','db_name'）fromdual--查询当前使用的数据库
--
（2）:
language
selectsys_context（'USERENV','language'）fromdual
--（3）:
session_user当前用户
selectsys_context（'USERENV','session_user'）fromdual
--（4）:
current_schema当前方案名（方案名与用户名一样）用户和方案的关系
--用户和方案的关系
selectsys_context（'USERENV','current_schema'）fromdual
--数据库管理员
sys（高于后者）与system用户的区别:
（1）存储的数据的重要性不同
（2）权限不同
--sys用户必需以assysdba或者assysoper身份登录
--system用户可以以normal登录，但是就是一个普通用户
--注意：
system可以以sysdba登录，但是如果以这样登录的话，那么就是一个sys用户了，具有他的所有权限
sysdba（高于后者）与sysoper的区别：
sysoper>dba（普通用户）
前者可以打开、关闭数据库，创建和删除，后者不行，前者数据恢复可以是部分，可以是全部，而后者是全部恢复
--shutdown（关闭数据库）
--startup（启动数据库）
--管理初始化的参数
--showparameter（该行没有运行出来，有点问题）
数据库表的逻辑备份与恢复
--逻辑备份和物理备份
前者：
是将数据库的对象导出到一个磁盘里面存放（备份）
将磁盘的信息导入到数据库（恢复）--数据库在运行状态才
升级会员 