oracle学习第一天

290112011

　　sqlplus
　　其他的图形化工具
　　=====================
　　使用的用户：
　　用户名：scott
　　密码：tiger
　　登录：
　　sqlplusscott/tiger
　　退出：
　　exit
　　查看当前用户有哪些表：
　　select*fromtab;
　　=====================
　　显示当前用户
　　SQL>showuser
　　USER为"SYS"
　　切换用户
　　SQL>connectscott/tiger
　　已连接。
　　命令可以使用缩写
　　conn[ect]user
　　执行上一条sql语句
　　/
　　设置行宽
　　setlinesize150默认为80
　　设置页面大小（一页显示的数据）
　　setpagesize100默认为14
　　显示参数的值
　　show参数名
　　如:
　　showlinesize
　　永久保存配置
　　修改glogin.sql文件。
　　注释：
　　单行注释：--
　　多行注释：/**/
　　查看表结构
　　SQL>descdept
　　====================================
　　SCOTT用户的表：
　　dept表：
　　deptno编号
　　dname
　　loc位置
　　emp表：
　　empno员工编号
　　ename
　　job
　　mgr上级主管的员工编号
　　hiredate入职时间
　　sal薪水
　　comm奖金
　　deptno所属的部门编号-->引用dept.deptno
　　====================================
　　清屏：
　　Windows中：hostcls
　　Linux中：hostclear
　　设置某列的宽度：
　　columnjobformata15或coljobfora15
　　columnSALformat9999或colsalfor9999
　　格式：
　　col[umn]列名for[mat]格式
　　对于字符串：
　　a20，20是一个数据，表示20个字符的宽度。
　　对于数字
　　9表示一位，有几个，就是多宽。
　　====================================
　　空值的处理
　　不是一个有效的值。不是0，也不是空字符串。
　　null!=null
　　isnull
　　isnotnull
　　含有null的表达式结为null
　　修改上一条SQL语句
　　ed[it]
　　注意：后面不要写分号
　　滤空函数
　　nvl(表达式,当表达式为空时使用的值)
　　selectempno,ename,sal,sal*12as年薪,nvl(comm,0),(sal*12+nvl(comm,0))总收入
　　fromemp
　　====================================
　　字符串
　　是匹分大小写的，在使用时要加引号。
　　在指定别名时，引号可以加，也可以不加。
　　当含有空格、特殊字符时，一定要加引号。
　　不加引号时，显示都为大写，加上引号后，就是按所写的显示了。
　　在使用字符串时，要使用单引号。
　　在写别名时，要使用双引号。
　　去掉重复的行
　　--作用于一列
　　SQL>selectdistinctjobfromemp;
　　--作用于多个列，所有列的值加一起重复才算重复的记录
　　SQL>selectdistinctjob,deptnofromemp;
　　如果只查询一个表达式，没有用到任何表的数据，这时也必须得写from...
　　可以写成fromdual
　　dual是一个虚表，本身就存在的，可以直接使用。
　　如：
　　select3+2
　　select'Hello'||'World'fromdual;
　　字符串连接符
　　SQL>selectename||'的薪水是'||salfromemp;
　　ENAME||'的薪水是'||SAL
　　----------------------------------------------------------
　　SMITH的薪水是800
　　ALLEN的薪水是1600
　　WARD的薪水是1250
　　JONES的薪水是2975
　　MARTIN的薪水是1250
　　BLAKE的薪水是2850
　　CLARK的薪水是2450
　　SCOTT的薪水是3000
　　KING的薪水是5000
　　TURNER的薪水是1500
　　ADAMS的薪水是1100
　　JAMES的薪水是950
　　FORD的薪水是3000
　　MILLER的薪水是1300
　　也可以使用函数concat()：
　　SQL>selectconcat('Hello','World')fromdual;
　　where(aorb)andc
　　====================================
　　LIKE
　　在使用like时，可以使用%与_，分别表示任意数量的任意字符或任意一个字符。
　　要想表达%或_本身，需要使用转义符，例：
　　SQL>select*fromempwhereenamelike'KI\%%'escape'\';
　　BETWEEN
　　包含两个边界。
　　一定是小值写到前面，大值写到后面，否则没有结果。
　　IN
　　where..in(..,..,..,...)如果含有null，没有影响。
　　例：查询所有是经理的员工
　　SQL>select*fromempwhereempnoin(selectmgrfromemp);
　　where..notin(..,..,...)如果含有null，则不返回任何结果。
　　例：查询所有不是经理的员工
　　SQL>select*fromempwhereempnonotin(selectmgrfromempwheremgrisnotnull);
　　=========================================
　　MySQL中日期类型：date,time,datetime
　　Oracle中只有Date.
　　SimpleDateFormat
　　yyyy-MM-dd
　　对日期的处理
　　1，查询指定日期后入职的员工信息
　　SQL>select*fromempwherehiredate>'31-12月-81';
　　2，修改日期的格式
　　SQL>select*fromv$nls_parameters;
　　SQL>altersessionsetnls_date_format='yyyy-mm-dd';//只对当前session有效
　　3，使用日期函数的方式：
　　SQL>select*fromempwherehiredate>to_date('1981-12-31','yyyy-MM-dd');
　　或
　　SQL>select*fromempwhereto_char(hiredate,'yyyy-MM-dd')>'1981-12-31';
　　语法：TO_CHAR(date,'format_model')
　　语法：TO_DATE(str,'format_model')
　　格式字符串不区分大小写：
　　获取当前时间：
　　SQL>selectto_char(sysdate,'YYYY-MM-DD')fromdual;
　　TO_CHAR(SY
　　----------
　　2012-03-07
　　SQL>selectto_char(sysdate,'yyyy-mm-dd')fromdual;
　　TO_CHAR(SY
　　----------
　　2012-03-07
　　SQL>selectto_char(sysdate,'yyyy-mm-ddhh:mi:ss')fromdual;
　　TO_CHAR(SYSDATE,'YY
　　-------------------
　　2012-03-0702:33:41
　　=========================================
　　排序：
　　orderby列名,...
　　可以作用在：数字、日期、字符串。
　　可以使用列名，表达式，别名，序号（表示select中的第几个列）
　　升序、降列
　　按一个列排列，按多个列排序
　　当orderby所在的列中有null，会：
　　升序时，null的在下面。
　　降序时，null的在上面。
　　我们希望，不管升序还是降序，null值的始终在下面
　　方式一：SQL>select*fromemporderbycommdescnullslast;
　　方式二：
　　selectempno,ename,job,hiredate,sal,nvl(comm,0)
　　fromemp
　　orderby6desc
　　=====================================================
　　1，组函数
　　select
　　max(sal)最高工资,
　　min(sal)最低工资,
　　avg(sal)平均工资,
　　sum(sal)所有员工的工资和,
　　count(sal)领工资的员工数量
　　fromemp;
　　2，组函数对null的处理
　　例，查询所有员工的平均奖金（有人的奖金为null）
　　selectsum(comm)/count(*)平均奖金fromemp
　　组函数会自动过滤掉null值。
　　在使用avg()时要注意处理null值：
　　selectavg(nvl(comm,0))fromemp;
　　函数可以嵌套使用
　　3，分组
　　Groupby，写在FROM后，如果有WHERE，就在WHERE后面。
　　查询的列一定要是：
　　在groupby中出现的列（在Select中不一定全写上）
　　或是使用组函数
　　按一个列分组
　　按多个列分组
　　参与分组的多个列有一个不相同就是不同的组。
　　4，分组结果过滤
　　Having，是分完组后再进行过滤，只显示符合条件的结果。
　　在Groupby与Having中都不可以使用别名。
　　与Where的区别
　　Having是是分完组后再进行过滤。
　　Where是先过滤，再进行分组操作。
　　如果可以，尽量写Where条件，不写Having。
　　Select
　　...
　　From
　　...
　　Where
　　...
　　Groupby
　　...
　　Having
　　...
　　Orderby
　　...
　　================================================
　　子查询：
　　当一步不能求解时，可以使用子查询。
　　分为：
　　单行子查询
　　多行子查询
　　可以在主查询的select,from,where,having都可以放子查询
　　不可以在主查询的groupby放子查询
　　单行操作符对应单行子查询，多行操作符对应多行子查询
　　在select中放子查询时，要求只能是单行子查询。
　　IN：
　　ANY：
　　小于某集合中的任意一个值，就是小于集合中的最大值。
　　大于某集合中的任意一个值，就是大于最小值。
　　ALL：
　　小于某集合中的所有值，就是小于最小值。
　　大于某集合中的所有值，就是大于最大值。
　　练习题一：
　　题目：找到薪水大于本部门平均薪水的员工。
　　答案：
　　select*
　　fromempe1
　　wheresal>(selectavg(sal)fromempe2wheree2.deptno=e1.deptno)
　　练习题二：
　　题目：找到员工表中工资最高的前三名的员工信息
　　答案：
　　selectrownum,empno,ename,sal
　　from(selectempno,ename,salfromemporderbysaldesc)
　　whererownumselectrownum,empno,ename,salfromemp;
　　ROWNUMEMPNOENAMESAL
　　----------------------------------------
　　17369SMITH800
　　27499ALLEN1600
　　37521WARD1250
　　47566JONES2975
　　57654MARTIN1250
　　67698BLAKE2850
　　77782CLARK2450
　　87788SCOTT3000
　　97839KING5000
　　107844TURNER1500
　　117876ADAMS1100
　　127900JAMES950
　　137902FORD3000
　　147934MILLER1300
　　select
　　rownum,empno,ename,sal
　　from(
　　selectrownumr1,empno,ename,sal
　　fromemp
　　whererownum