ORACLE数据库学习整理

一、select语句

1. 日期和数值型字段可以进行加减乘除

2. 关于NULL NULL表示不可用，未赋值，不知道，不适用，所以它既不是0，也不是空格。NULL和数值进行四则运算，结果是NULL。

3. 字符串连接符：“

   ”

4. 去除重复行：“distinct”

二、条件和限制

1. 比较操作符

   比较操作符 意义
   =          等于
   >          大于
   >=         大于等于
   <          小于
   <=         小于等于
   <>         不等于
   BETWEEN ..AND..  在两个值之间
   IN(set)     在一个集合范围内
   LIKE       匹配一个字符串样子，可以使用%通配符
   

2. 使用like进行模糊匹配 可使用% 或者_ 作为通配符：% 代表 0个或者多个字符；而 _ 代表一个单个字符。

   如果要是查找有%的记录，使用关键字escape 举例： select * from t_char where a like '%\%%' escape '\';

3. 逻辑操作符

| 逻辑操作符 | 意义 | | —– | —————– | | AND | 所有条件都满足，返回TRUE | | OR | 只要有一个条件满足，返回TRUE | | NOT | 如果条件是FALSE,返回TRUE |

1. 排序：“order by”
   • asc 升序
   • desc 降序

三、单行函数

####1.大小写转换函数 | 函数 | 结果 | | ——————— | ———- | | LOWER(‘SQL Course’) | sql course | | UPPER(‘SQL Course’) | SQL COURSE | | INITCAP(‘SQL course’) | Sql Course |

注意：Oracle数据库中的数据是大小写敏感的

####2.字符串操作函数

####3.数字操作函数

####4.日期操作函数

• 日期运算操作

  SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS, sysdate+1 as
  tomorrow , hire_date + 8/24
  FROM employees
  WHERE department_id = 90;
  

####5. Oracle数据类型的隐私转换规则

注意：对于表达式比较操作仅可以：

SELECT last_name, TO_CHAR(hire_date, 'fmDD "of" Month YYYY') AS
HIREDATE
FROM employees;

####6. 日期到字符串的转换：TO_CHAR(date, 'format_model');

####7. 数字到字符串的转换TO_CHAR(number, 'format_model');

SELECT TO_CHAR(salary, 'L99,999.00') SALARY FROM employees
WHERE last_name = 'Ernst';

####8. 字符串到数字和日期的转换

TO_NUMBER(char[, 'format_model']);
TO_DATE(char[, 'format_model']);

####9. 其它单行函数

| 函数 | 用途 | | ———————————– | —————————————- | | NVL (expr1, expr2) | 如果expr1为空，这返回expr2 | | NVL2 (expr1, expr2, expr3) | 如果expr1为空，这返回expr3（第2个结果）否则返回expr2 | | NULLIF (expr1, expr2) | 如果expr1和expr2相等，则返回空 | | COALESCE (expr1, expr2, …, exprn) | 如果expr1不为空，则返回expr1,结束；否则计算expr2,直到找到一个不为NULL的值或者如果全部为NULL，也只能返回NULL |

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

####10. 条件表达式 CASE语句：

SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END "REVISED_SALARY"
FROM employees;

DECODE语句：

SELECT last_name, job_id, salary,
 DECODE(job_id, 'IT_PROG', 1.10*salary,
 'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
 salary) REVISED_SALARY
FROM employees;

四、多表关联查询

不同的数据库厂商对链接类型有不同的定义，但国际上有个凌驾于各厂商的工业标准定义（SQL 1999), 我们先来看Oracle定义的链接类型： 1、等于链接 2、不等链接 3、外连接（可细分为左外连接、右外连接） 4、自链接

####1.等于连接 语法：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

举例：

SELECT employees.employee_id, employees.last_name,
employees.department_id,
departments.department_id,departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

####2. 不等于连接 “不等链接” 语法： 使用不等链接符，包括> , < , !=, between

语法：

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 > table2.column2;

举例：

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

####3.外连接 外连接包括左外连接和右外连接

举例：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

记忆方式：

哪边没加好，就是那种外连接。比如：加号在右面，就叫左外连接，此时右面对应的空数据也会查询出来

####4.自连接 “自链接” ：其实是一种概念，某个table和自己本身链接 ，比如：table1给另一个“自己”起别名为table2

举例：

SELECT worker.last_name || ' works for ' || manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

五、 分组计算函数和“GROUP BY”字句

• 常用的分组计算函数包括：

• count()的用法

注意：

SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ，其他字段必须出现在Group By子句中，否则不合法。 不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。 分组计算函数可以嵌套使用

##六、子查询

语法：

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
 FROM table);

注意点：

• 单行比较必须对应单行子查询（返回单一结果值的查询）； 比如= ， >
• 多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN , > ANY, > ALL 等

七、DML语句

1. insert语句 形式一： 不允许为空的列，必须写出来。

   INSERT INTO table [(column [, column...])]
   VALUES (value [, value...]);
   

   形式二：仅写出列名，这种形式必须显式的给出所有列的数据。

   INSERT INTO departments
   VALUES (100, 'Finance', NULL, NULL);
   

   形式三：从另一个表中copy

   INSERT INTO sales_reps(id, name, salary, commission_pct)
    SELECT employee_id, last_name, salary, commission_pct
    FROM employees
    WHERE job_id LIKE '%REP%';
   

   形式四：使用子查询作为插入目标

   INSERT INTO
    (SELECT employee_id, last_name,
    email, hire_date, job_id, salary, department_id
    FROM employees
    WHERE department_id = 50)
   VALUES (99999, 'Taylor', 'DTAYLOR', TO_DATE('07-JUN-99', 'DD-MON-RR'),
    'ST_CLERK', 5000, 50);
   

2. update语句

   UPDATE table
   SET column = value [, column = value, ...]
   [WHERE condition];
   

   注意：存在约束条件的时候，可能会更新失败

3. dalete语句

   DELETE [FROM] table
   [WHERE condition];
   

   删除所有：delete删除语句没有where条件时，意为删除所有数据，但是表依然存在，删除表实体使用drop。 注意：当存在约束时，有可能会删除失败。

4. meger比较整合语句 举例：

   MERGE INTO copy_emp c
    USING employees e
    ON (c.employee_id = e.employee_id)
   WHEN MATCHED THEN
    UPDATE SET
    c.first_name = e.first_name,
    c.last_name = e.last_name,
    ...
    c.department_id = e.department_id
   WHEN NOT MATCHED THEN
   INSERT VALUES(e.employee_id, e.first_name, e.last_name,
    e.email, e.phone_number, e.hire_date, e.job_id,
    e.salary, e.commission_pct, e.manager_id,
    e.department_id);
   

七、事务控制

隐式的事务提交或者回滚：

Commit, rollback 是显式的提交和回滚语句，还有一些隐式的提交和回滚是大家需要知道并引起注意的： 当如下事件发生是，会隐式的执行Commit动作： 1、数据定义语句被执行的时候，比如新建一张表：Create Table …

2、数据控制语句被执行的时候，比如赋权 GRANT …( 或者 DENY)

3、正常退出 iSQL*Plus 或者PLSQL DEVELOPER, 而没有显式的执行 COMMIT 或者 ROLLBACK 语句 。 当如下事件发生时，会隐式执行Rollback 动作：

九、数据库对象-表

表的命名要求和表中列的命名要求： 1、必须以字母开头 2、长度不能超过30个字符 3、只能包含 A–Z, a–z, 0–9, _, $, and # 4、不能与数据库中的已有对象重名 5、不能使用Oracle 数据库的保留字

语法：

CREATE TABLE [schema.]table
 (column datatype [DEFAULT expr][, ...]);

中间部分省略

十四、GROUP BY 增强

1. GROUP BY 中使用Rollup产生常规分组汇总行以及分组小计

   SELECT department_id, job_id, SUM(salary)
   FROM employees
   WHERE department_id < 60
   GROUP BY ROLLUP(department_id, job_id);
   

   这样会产生三维的统计数据：department_id, job_id为一个维度，department_id为一个维度，job_id为一个维度。

2. GROUP BY 中使用cube产生Rollup结果集+多维度交叉表数据来源

   SELECT department_id, job_id, SUM(salary)
   FROM employees
   WHERE department_id < 60
   GROUP BY CUBE (department_id, job_id) ;
   

   这样就会有四个维度，相当于再反向的rollup一次

3. GROUPING__函数 关键字： __grouping（column） rename 举例：

   SELECT department_id DEPTID, job_id JOB,
     SUM(salary),
     GROUPING(department_id) GRP_DEPT,--用了哪个维度，哪个维度就显示为0，否则为1
     GROUPING(job_id) GRP_JOB
   FROM employees
   WHERE department_id < 50
   GROUP BY ROLLUP(department_id, job_id);
   

4. GROUPING STES 来代替多次的union 举例：

   SELECT department_id, job_id,
    manager_id,avg(salary)
   FROM employees
   GROUP BY GROUPING SETS
   ((department_id,job_id), (job_id,manager_id));
   

   此时，就会按照(department_id,job_id)和 (job_id,manager_id)两个组合来进行分组汇总，最后再union。

十五、子查询进阶

1. 非相关子查询，即把子查询的结果当做一张表来使用 举例：

   SELECT a.last_name, a.salary,
    a.department_id, b.salavg
   FROM employees a, (SELECT department_id,
                   AVG(salary) salavg
                   FROM employees
                   GROUP BY department_id) b
   WHERE a.department_id = b.department_id
   AND a.salary > b.salavg;
   

2. 相关子查询，即子查询中参考了外部主查询的中的表 举例：

   SELECT last_name, salary, department_id
   FROM employees outer
   WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id =
                outer.department_id) ; 
   

3. 使用Exists、not exists操作

4. 使用in、not in操作

注意：Not In 里面只要有一个NULL ，就不成立了，这是很容易出错的地方； 正确的方法请在后面的子查询中加上where department_id is not null;