Oracle 入门

1. 基本查询

字段(列)控制

a) 查询所有列 : SELECT * FROM 表名;

b) 查询指定列 : SELECT 列1 [,列2...列N] FROM 表名;

c) 完全重复的记录只显示一次 : SELECT DISTINCT 列1 [,列2, ... 列N] FROM 表名;

- 当查询结果中的多行记录一模一样时,只显示一行. 一般查询所有列时很少会有这种情况,

但只查询一列(或几列)时,这种可能就大了!!(不会对源表产生影响, 只是在显示的时候, 不显示)
列运算(源表没有变化, 查询不会修改数据库表记录)

a) 数量型的列可以做加, 减, 乘, 除运算: SELECT sal*1.5 FROM emp;

b) 字符串类型的列可以做连接运算: SELECT CONCAT(ename, job) FROM emp;

也可以使用连接符"||": SELECT ename ||'的工作是'|| job FROM emp;

c) 转换 NULL 值: SELECT nvl(comm,0)+100 FROM emp;

表示如果 comm 中存在 NULL 值, 那么当成 0 来运算.

d) 给列起别名: SELECT nvl(comm,0)+1000 AS 奖金 FROM emp; 其中 AS 可以省略.

别名也可以使用双引号,以便在别名中包含空格或特殊的字符并区分大小写;

e) 如果查询的数据跟任何表没有关系,可以使用 dual 表(伪表): SELECT 3+2 from dual;
条件控制

a) 条件查询
- 字符和日期需要包含在单引号中
- 字符大小写敏感,日期格式敏感
- 默认的日期格式是 DD-MON-RR
- 查询数据字典: SELECT * FROM v$nls_parameters;
b) 模糊查询
- 查询姓张, 并且姓名中包含""的员工,这时, ""需要转义

SELECT * FROM emp WHERE ename LIKE '%\_%' escape '\';
排序

a) 降序 DESC SELECT * FROM emp order by comm desc nulls last;

按照奖金降序排列,如果有空值,放在最后显示.

2. 函数

2.1 字符函数

大小写控制函数
- 转为小写: lower()
- 转为大写: upper()
- 首字母大写: initcap()
字符控制函数
- substr(a,b,c): 从a字符中,第b位开始取,取c位
- length(): 字符数
- lengthb(): 字节数
- instr(a,b): 在a中查找是否存在b字符
- lpad(a,b,c): 左填充,a表示给谁填充,b表示填充至多少位,c表示以什么字符填充;
- rpad(a,b,c): 右填充;
- trim('H' from 'Hello WorldH'): 去除前后指定的字符;
- replace('Hello World','l','*'): 用"*"替换"hello world"中的 "l"

2.2 数值函数

round(45.926,2):四舍五入,结果为: 45.93;
trunc(45.926,2):截取, 结果为: 45.92;

2.3 日期函数

Oracle 中的日期型数据实际含有两个值,日期和时间; 默认的日期格式是: DD-MON-RR;
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'): 格式化时间, 其中"sysdate"表示系统时间;
在日期上加上或减去一个数字结果仍为日期;
两个日期相减返回日期之间相差的天数
MONTH_BETWEEN: 两个日期相差的月数;
ADD_MONTHS: 向指定日期中加上若干月数;
NEXT_DAY: 指定日期的下一个日期;例如,从今天开始,查看下一个周五是几号: NEXT_DAY(sysdate,'星期五')
LAST_DAY: 本月的最后一天;
ROUND: 日期四舍五入;例如,SYSDATE = '25-JUL-95'
- ROUND(SYSDATE,'MONTH'), 结果为: 01-AUG-95
- ROUND(SYSDATE,'YEAR'), 结果为: 01-JAN-96
TRUNC: 日期截断;
- TRUNC(SYSDATE,'MONTH'), 结果为: 01-JUL-95
- TRUNC(SYSDATE,'YEAR'), 结果为: 01-JAN-95

2.4 转换函数

TO_CHAR(date,'format_model'): 将日期转换为字符
- 字符必须包含在单引号中而且大小写敏感;
- 可以包含任意的有效的日期格式;
- 日期之间用逗号隔开;
TO_CHAR(number,'format_model'): 将数字转换成字符
- 查询员工薪水: 两位小数,千位符,本地货币代码
- SELECT TO_CHAR(sal,'L9,999.99') FROM emp;
TO_NUMBER(char[, 'format_model']): 将字符转换成数字;
TO_DATE(char[ , 'format_model']): 将字符转换成日期;

2.5 通用函数

这些函数适用于任何数据类型,同时也适用于空值;
NVL(expr1,expr2);
NVL2(expr1,expr2,expr3);: 当 expr1=null 时,返回 expr3; 否则返回 expr2;
NULLIF(expr1,expr2);: 当 expr1 = expr2 时,返回 null, 否则返回 expr1;
COALESCE(expr1,expr2,...,exprn);: 从左到右,找到第一个不为 null 的值;

2.6 条件表达式

在 SQL 语句中使用 IF-THEN-ELSE 逻辑;
CASE 表达式: SQL 99 的语法,类似 Basic, 比较繁琐;
DECODE 函数: Oracle 自己的语法,类似 Java, 比较简洁;

// CASE 表达式
    // 需求: 涨工资, 如果是总裁, 涨1000; 经理涨800; 其他涨400
    SELECT empno,ename,job,sal 涨前,
           CASE job WHEN 'PRESIDENT' THEN sal+1000
                    WHEN 'MANAGER' THEN sal+800
                    ELSE SAL+400
            END 涨后
    FROM emp;

// DECODE 表达式
    SELECT empno,ename,job,sal 涨前,
           DECODE(job,'PRESIDENT',sal+1000,
                       'MANAGER',sal+800,
                                 sal+400) 涨后
    FROM emp;

3. 组函数

概念
- 分组函数作用于一组数据,并对一组数据返回一个值;
常用的组函数
- 组函数会忽略空值;
- AVG
- COUNT
- MAX
- MIN
- SUM
分组数据
- GROUP BY: 将表中的数据分成若干组;
- 在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中;
  SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;
- 包含在 GROUP BY 子句中的列不必包含在 SELECT 列表中;

4. 多表查询

连接类型
- 等值连接
- 不等值连接
- 左外连接: WHERE e.deptno=d.deptno(+)
- 右外连接: WHERE e.deptno(+)=d.deptno
- 自连接: 通过表的别名,将同一张表视为多张表;
层次查询
- 自连接不适合操作大表,因为笛卡尔积较大,例如有一亿条记录的大表,笛卡尔积的数量相当大;
- 可以使用层次查询(单表查询)

5. 子查询

5.1 概念

子查询: 在一个查询的内部还包括另一个查询.
可以在主查询的 where select having from 后面使用子查询;
单行子查询,返回一行;
多行子查询,返回多行; 多行比较操作符:
- IN: 等于列表中的任何一个;
- ANY: 和子查询返回的任意一个值比较;
- ALL: 和子查询返回的所有值比较;

6. 分页

rownum
- rownum 永远按照默认的顺序生成;
- rownum 只能使用 "< 或 <=", 不能使用"> 或 >=";
- rownum 永远从 1 开始;
oracle 分页

    SELECT *
    FROM   (SELECT rownum r, el.*
            FROM (SELECT * FROM emp order by sal) el
            WHERE rownum <=8
            )
    WHERE r >= 5;

临时表
- 特点: 当事务或会话结束时,表中数据自动删除;
- 手动创建临时表: CREATE GLOBAL TEMPORARY TABLE 表名;
- 自动创建临时表: 在使用 order by时

// 基于事务的临时表, 在事务提交时,表自动删除
    CREATE GLOBAL TEMPORARY table 表名
    (列名 列类型, 列名2 列类型2)
    on COMMIT delete rows;

// 基于会话的临时表, 在 exit 时,表自动删除
    CREATE GLOBAL TEMPORARY table 表名
    (列名 列类型, 列名2 列类型2)
    on COMMIT preserve rows;

6. 集合运算

并集: UNION / UNION ALL
交集: INTERSECT
差集: MINUS
参与集合运算的各个集合必须列数相同,且类型一致;
采用第一个集合作为最后查询出来的表头;
ORDER BY 永远在最后;
尽量不要使用集合运算,因为参与运算的集合数量越多,效率越低.

// 查询 10 和 20 号部门的员工
    SELECT * FROM emp WHERE deptno=10
    UNION
    SELECT * FROM emp WHERE deptno=20;

// 备注: 如果 UNION 和 UNION ALL 相同的情况下, 尽量使用 UNION ALL
//       因为 UNION = UNION ALL + DISTINCT

7. 插入数据

7.1 地址符 &

INSERT INTO emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);
在 SQL 语句中使用 "&" 变量指定列值.

7.2 从其他表中拷贝数据

少量数据
- 一次性将 emp 中所有10号部门的员工插入到 emp10 中
- INSERT INTO emp10 SELECT * FROM emp WHERE deptno=10;
插入海量数据
- 数据泵(PLSQL 程序: dbms_datapump);
- SQL*Loader 工具;
- 外部表;
如何去掉碎片
- 第一种方式: ALTER table 表名 move;
- 第二种方式: 数据的导出(exp 或 expdp)和导入(imp 或 impdp)

8. 数据库事务

8.1 Oracle 中事务的标志

起始标志:事务中第一条 DML 语句;
结束标志(提交): 显示提交(COMMIT) 或隐式提交(正常退出, exit, 或 DDL 或 DCL);
结束标志(回滚): 显示回滚(ROLLBACK) 或隐式回滚(非正常退出, 掉电, 或宕机);

8.2 隔离级别

Oracle 支持两种事务隔离级别: READ COMMITED 和 SERIALIZABLE, 默认的事务隔离级别 READ COMMITED;
Oracle 还有一个自己独有的隔离级别: READ ONLY;
MySql 支持四种事务隔离级别;

8. DDL 创建和管理表

8.1 创建表

保存20号部门的员工
CREATE table emp20 as SELECT * FROM emp WHERE deptno=20;
CREATE table emp20 as SELECT * FROM emp WHERE 1=2; "1=2"条件为假,表示只是将 emp 的表头复制到 emp20;

8.2 删除表

DROP table 表名;, 将表删除到 Oracle 回收站;
Oracle 回收站
- 查看回收站: SHOW recyclebin;
- 清空回收站: PURGE recyclebin;
- 从回收站中恢复(闪回删除): flashback table 表名 to before drop;
- 管理员没有回收站;

8.3 约束

约束的类型
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK (检查性约束)

// 检查性约束
    CREATE table test
    (
        tid number,
        tname varchar2(20),
        gender varchar2(2)
        CONSTRAINT  t_gender_ck(表示约束别名) check(gender in('男','女')),
        deptno number CONSTRAINT t_fk references dept(deptno) ON DELETE SET NULL
    );

2. 外键约束

ON DELETE CASCADE: 当删除父表时, 级联删除子表记录;
ON DELETE SET NULL: 当删除父表时, 将子表的相关依赖记录的外键值置为 null;

9 常见的数据库对象

9.1 概述

表: 基本的数据存储集合,由行和列组成;
视图: 从表中抽出的逻辑上相关的数据集合;
序列: 提供有规律的数值;
索引: 提高查询的效率;
同义词: 给对象起别名;

9.2 视图

9.2.1 概述

视图是一种虚表;
视图建立在已有表的基础上,视图赖以建立的这些表称为基表;
向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句;
视图向用户提供基表数据的另一种表现形式;

9.2.1 优点

简化复杂查询, 但视图不能提高性能;

// 创建视图
    CREATE[OR REPLACE] VIEW 视图名
    as
    SELECT e.empno,e.name,e.sal,e.sal*12 annsal, d.dname
    FROM emp e, dept d
    WHERE e.deptno = d.deptno
    WITH READ ONLY;

9.3 序列(sequence)

1. 概述

序列:可供多个用户用来产生唯一数值的数据库对象;
自动提供唯一的数值; (与 MySql 中的 auto_increment 类似)
主要用于提供主键值;
将序列值装入内存可以提高访问效率;

// 语法
    CREATE SEQUENCE 序列名
            [INCREMENT BY n] // 步长
            [START WITH n] // 起始值
            [{CACHE n | NOCACHE}] // 数组长度,默认为 20 (序列实际上是数组)

2. NEXTVAL 和 CURRVAL 伪列

NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用;
CURRVAL 中存放序列的当前值;

9.4 索引

9.4.1 概述

索引是一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中;
索引被删除或损坏,不会对表产生影响,其影响的只是查询的速度;
索引一旦建立,Oracle 管理系统会对其进行自动维护,而且由 Oracle 管理系统决定何时使用索引.
在删除一个表时,所有基于该表的索引会自动被删除;
索引通过指针加速 Oracle 服务器的查询速度;
索引通过快速定位数据的方法,减少磁盘 I/O;

9.4.2 什么时候创建索引

列中数据值分布范围很广;
列经常在 WHERE 子句或连接条件中出现;
表经常被访问而且数据量很大,访问的数据大概占数据总量的 2% 到 4%;

参考资料

posted @ 2017-10-18 20:02 小a的软件思考阅读(192) 评论(0) 编辑收藏举报

刷新页面返回顶部

思考与践行

Oracle 入门

1. 基本查询

2. 函数

2.1 字符函数

2.2 数值函数

2.3 日期函数

2.4 转换函数

2.5 通用函数

2.6 条件表达式

3. 组函数

4. 多表查询

5. 子查询

5.1 概念

6. 分页

6. 集合运算

7. 插入数据

7.1 地址符 &

7.2 从其他表中拷贝数据

8. 数据库事务

8.1 Oracle 中事务的标志

8.2 隔离级别

8. DDL 创建和管理表

8.1 创建表

8.2 删除表

8.3 约束

2. 外键约束

9 常见的数据库对象

9.1 概述

9.2 视图

9.2.1 概述

9.2.1 优点

9.3 序列(sequence)

1. 概述

2. NEXTVAL 和 CURRVAL 伪列

9.4 索引

9.4.1 概述

9.4.2 什么时候创建索引

公告