oracle基本查询用法入门示例

    本文实例讲述了oracle基本查询用法。分享给大家供大家参考,具体如下:

    一、基本select语句

    
    SELECT *|{[DISTINCT] column|expression [alias], ...}
    FROM table;
    

    例如:

    
    --查询所有数据
    select * from emp;
    
    --查询指定列数据
    select empno,ename,sal from emp;
    
    --算数运算符(+ - * /)
    select ename,sal,sal+30 from emp;
    
    --使用括号
    select ename,sal,12*(sal+30) from emp;
    
    --定义空值
    --(空值是无效的,未指定,未知的或不可预知的值,空值不是空格或是0)
    select ename,job,sal,comm from emp;
    
    --空值的数学运算
    --包含空值的数学表达式的值都为空值
    select ename,12*sal+comm from emp;
    
    --列的别名
    --别名使用双引号,AS可以省略
    select deptno as "no",ename as "name" from emp;
    
    --连接符,把列与列,列与字符连接在一起
    select deptno || '--' || ename from emp;
    
    --字符串
    --日期和字符只能在单引号中出现
    select 'hello ' || ename from emp;
    
    --删除重复行
    select distinct deptno from emp;
    
    --显示表结构
    desc[ribe] tablename;
    

    二、过滤和排序

    
    SELECT *|{[DISTINCT] column|expression [alias], ...}
    FROM table
    [WHERE condition(s)];
    

    例如:

    
    --查询指定条件数据
    select deptno,ename from emp where deptno=10;
    
    --字符串和日期包含在单引号中
    --字符串大小写敏感,日期格式敏感
    select ename,job,deptno from emp where ename='King';
    
    --比较运算符(= > < <= >= <> !=)
    select ename,sal from emp where sal<1500;
    
    --其他比较运算符
    --BETWEEN ... AND ... 在两个值之间包含边界
    --IN(set) 等于值列表中的一个
    --LIKE 模糊查询
    
    --IS NULL 空值
    select ename,sal,deptno from emp where deptno in(10,30);
    select ename,sal,comm from emp where comm is null;
    
    --逻辑运算(AND OR NOT)
    select ename,sal from emp where deptno=10 and sal>1500;
    

    排序

    
    ORDER BY 字段 [DESC|ASC]
    

    例如:

    
    select ename,sal from emp order by sal desc;
    --多列排序
    --先按第一列排序,如果相同,则按第二列排序,以此类推
    select * from emp order by sal desc,hiredate desc;
    

    三、单行函数

    1、字符函数

    
    --LOWER 转换小写
    --UPPER 转换大写
    --INITCAP 首字母大写
    select lower(ename) from emp;
    --CONCAT 接接字符串
    --SUBSTR 截取字符串
    --LENGTH 字符串长度
    --INSTR 查找字符串
    --LPAD 左边填充字符
    --RPAD 右边填充字符
    --TRIM([leading|trailing|both] 字符串1 from 字符串2) 
    --TRIM可以删除两边空格,也可删除其他字符
    --REPLACE 替换字符串
    select concat('aa','bb') from emp;
    select substr('abcdefg', 2, 3) from emp;
    select length('test...') from emp;
    select instr('hello world', 'w') from emp;
    select lpad(sal, '10', '0') from emp;
    select rpad(sal, '10', '*') from emp;
    select trim(' test ') from emp;
    --从尾部删除字符串*号
    select trim(trailing '*' from '**1212121**') from emp;
    --把字符串中的22替换成88
    select replace('11223344', '22', '88') from emp;
    

    2、数字函数

    
    --ROUND 四舍五入
    --TRUNC 截断
    --MOD 求余
    select round(25.533,2) from dual;
    select trunc(25.323,2) from dual;
    select mod(8, 3) from dual;
    

    3、日期

    oracle中日期型数据实际含有两个值:日期和时间。

    默认格式为:DD-MON-RR

    
    --返回系统时间
    select sysdate from dual;
    --两个日期相减,返回日期之间相差的天数
    select ename,(sysdate-hiredate) / 7 "weeks" from emp;
    --MONTHS_BETWEEN 两日期相差月数
    --ADD_MONTHS 指定日期加上若干月数
    --NEXT_DAY 指定日期的下一个日期
    --LAST_DAY 本月的最后一天
    --ROUND 日期四舍五入
    --TRUNC 日期截断
    select months_between(sysdate,hiredate) from emp;
    

    4、显式数据类型转换

    
    --TO_CHAR(date, 'format_model')
    --把日期转换成字符串
    select to_char(sysdate, 'YYYY MM DD HH:MI:SS') from dual;
    --TO_CHAR(number, 'format_model')
    select ename,sal,to_char(sal, '$99,999.00') from emp;
    --TO_NUMBER(char[,'format_model'])
    --TO_DATE(char[,'format_model'])
    

    通用函数,适用于任何数据类型,也适用于空值

    
    NVL(expr1,expr2)
    NVL2(expr1,expr2,expr3)
    NULLIF(expr1,expr2)
    COALESCE(expr1,expr2,...)
    

    5、条件表达式

    在sql语句中使用if-then-else逻辑
    case表达式,sql99语法,类似basic,比较繁锁
    decode函数,oracle自已语法,类似java,比较简洁

    
    CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_expr3 THEN return_expr3
          ELSE else_expr]
    END
    
    
    DECODE(col|expression, search1, result1
               [,search2, result2, ..., ]
               [,defautl]
    )
    
    
    select ename,sal,decode(round(sal/1000),
    1, '一倍',
    2, '二倍',
    3, '三倍',
    '不知倍数'
    ) from emp;
    

    6、函数嵌套

    单行函数可以嵌套,嵌套函数的执行是由内到外。

    四、分组函数

    分组函数作用于一组数据,并对一组数据返回一个值。
    常用组函数

    
    --AVG
    --COUNT
    --MAX
    --MIN
    --SUM
    select sum(sal) as "total" from emp;
    select max(sal) from emp;
    select count(*) from emp where deptno=10;
    select count(distinct deptno) from emp;
    

    分组数据
    通过GROUP BY可以将数据分成若干组

    
    select deptno,avg(sal) from emp group by deptno;
    

    (*注意:在select列表中所有未包含在组函数中的列都必须包含在group by中。)

    
    --多列分组
    select deptno,job,avg(sal) from emp group by deptno,job;
    

    过滤分组
    通过HAVING子句对分组进行过滤

    
    select deptno,avg(sal) from emp group by deptno having deptno in(10,20);
    

    (*注意:不能在where子句中使用组函数,having子句中可以。)
    嵌套组函数

    
    select max(avg(sal)) from emp group by deptno;
    

    五、多表查询

    oracle的连接  sql99的连接
    等值连接        cross joins
    不等值连接     natural joins
    外连接           using clause
    自连接           full or two sided outer joins

    
    SELECT table1.column, table2.column
    FROM table1,table2
    WHERE table1.column1=table2.column2;
    
    
    --等值连接
    select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno;
    
    --多连接条件和and操作符
    select d.dname,e.ename,e.sal from emp e, dept d where e.deptno=d.deptno and e.deptno=10;
    

    表的别名
    1、使用表别名可简化查询
    2、使用表名前缀可以提高执行效率
    3、如果使用了表的别名,则不能再使用表的真名。

    
    --不等值连接
    
    --查询员工部门名称和工资等级
    select d.dname, e.ename, e.sal, s.grade
    from emp e, dept d, salgrade s
    where e.deptno = d.deptno
    and e.sal >= s.losal
    and e.sal <= s.hisal;
    

    外连接语法
    外连接查询可以查询不满足连接条件的数据。
    外连接的符号是(+)

    
    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column(+) = table2.column;
    
    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column= table2.column(+) ;
    
    
    --查询部门人数
    select d.dname, d.deptno, count(e.empno)
     from emp e, dept d
     where e.deptno(+) = d.deptno
     group by d.deptno,d.dname;
    

    自连接

    
    --查询员工的上级
    select e.ename as "员工", e2.ename as "上级"
     from emp e, emp e2
     where e.empno = e2.mgr;
    

    使用sql:1999语法连接

    
    SELECT table1.column, table2.column
    FROM table1
    [CROSS JOIN table2] |
    [NATURAL JOIN table2] |
    [JOIN table2 USING (column_name)] |
    [JOIN table2 ON(table1.column_name=table2.column_name)] |
    [LEFT|RIGHT|FULL OUTER JOIN table2 ON(table1.column_name=table2.column_name)];
    

    使用cross join连接的表产生叉集,叉集和笛卡尔集是相同的。

    
    select e.ename,d.dname from emp e cross join dept d;
    

    使作natural join自然连接,会以两个表中具有相同名字的列为条件创建等值连接。

    
    select e.ename,d.dname from emp e natural join dept d;
    

    使用using创建连接,用natural join创建等值连接时,可以使用using指定等值连接中需要用到的列。

    
    select e.ename,d.dname from emp e join dept d USING (deptno);
    

    使用on创建连接,可以指定额外的连接条件。

    
    select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
    

    使用on创建多表连接

    
    select e.ename, d.dname, e2.ename
    from emp e
    join dept d
    on e.deptno = d.deptno
    join emp e2
    on e.empno = e2.mgr;
    

    内连接和外连接
    在sql:1999中,内连接只返回满足连接条件的数据。
    两个表在连接过程中除了返回满足连接条件的行以外,还返回左(右)表中不满足条件的行,这种称为左(右)外连接。
    两个表在连接过程中除了返加满足连接条件的行以外,还返回两个表中不满足条件的行,这种连接称为满外连接。

    
    --左外连接
    select e.ename, d.dname
    from emp e
    left outer join dept d
    on e.deptno = d.deptno;
    
    
    --右外连接,返回右表中不满足条件的行
    select e.ename, d.dname
    from emp e
    right outer join dept d
    on e.deptno = d.deptno;
    
    
    --满外连接
    select e.ename, d.dname
    from emp e
    full outer join dept d
    on e.deptno = d.deptno;
    

    更多关于Oracle相关内容感兴趣的读者可查看本站专题:《Oracle常用函数汇总》、《Oracle日期与时间操作技巧总结》及《php+Oracle数据库程序设计技巧总结

    希望本文所述对大家Oracle数据库程序设计有所帮助。