Oracle数据库笔记——小白需要知道的基本内容

• 基本概念

  • 数据库

    Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

  • 实例

    一个Oracle实例（OracleInstance）有一系列的后台进程（Backguound Processes)和内存结构（MemoryStructures)组成。一个数据库可以有n个实例。

  • 用户

    用户是在实例下建立的。不同实例可以建相同名字的用户。

  • 表空间

    表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为system表空间)。

    每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。

  • SQL——结构化查询语言

    • DML(数据库操作语言)

    • DDL(数据库定义语言)

    • DCL(数据库控制语言)

• SQL基本操作

  • 简单查询

    • 基本查询【select 目标字段 from 表名】

    • 别名【select 列名1 别名1，列名2 别名2，... from 表名;】

    • 去重【select distinct 目标字段 from 表名;】 使用distinct可以消除重复的行，如果查询多列的必须保证多列都重复才能去掉重复

    • 字符串连接查询【select '前置描述' || 字段名 || '描述' from 表名 】 字符串的连接使用‘||’

  • 限定查询

    • 【select 列名 from 表名 where 条件；】 在查询绝大多数都会有条件的限制

    • 空字段和非空字段的限制

      非空：【列名 IS NOT NULL】

      为空：【列名 IS NULL】

    • 指定查询范围【IN / NOT IN / BETWEEN】

    • 关键字匹配

      • LIKE语句

        “ _ ” 可以匹配一个长度的内容

        “%” 可以匹配任意长度的内容

  • 对结果集排序

    • ORDER BY

      【SELECT 列名 FROM 表名 {WEHRE 查询条件} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC】

      ORDER BY 列名默认的排序规则是升序排列，可以不指定ASC，如果按着降序排列必须指定DESC

    注意ORDER BY语句要放在sql的最后执行。

  • 单行函数

    • 字符函数

      • 小写转换大写【upper('word_name')】

      • 大写转换小写【lower('word_name')】

      • 首字符大写【initcap('word_name')】

      • 字符串连接【concat('word_name1','word_name2')】

      • 字符串截取【substr('word_name',num1,num2)】 （源字符串，参数开始索引，参数结束索引）

      • 字符串长度【length('word_name')】

      • 字符串替换【replace('word_name','字母1','字母2')】 （源字符串，参数被替换字符串，替换字符串）

    • 数值函数

      • 四舍五入【ROUND()】四舍五入取整，自己制定保留位数

      • 取整【TRUNC()】默认全部去掉小数，可以指定保留位数

      • 取余【MOD()】

    • 日期函数

      • 获取两个时间段中的月数【MONTH_BETWEEN()】

      • 获得几个月后的日期【ADD_MONTH()】

      • 指定给出下次某日期数【NEXT_DATE()】

    • 转换函数

      • 字符串转换函数

        • 【TO_CHAR()】可以使用fm去掉前导零

          时间格式

          年：y, 年是四位使用yyyy

          月：m, 月是两位使用mm

          日：d, 日是两位使用dd

          时：HH是12进制，HH24进制

          分：mi

          秒：ss

      • 数值转换函数【TO_NUMBER()】可以把字符串转换成数值

      • 日期转换函数【TO_DATE()】可以把字符串的数据转换成日期类型

    • 通用函数

      • 空值处理【nvl()】

      • Decode函数

        该函数类似if....else

        语法：

        【DECODE(col/expression,[search1,result1],[search2, result2]....[default])】

        Col/expression:列名或表达式

        Search1，search2...:用于比较的条件

        Result1, result2...:返回值

        如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值

  • 多表函数

    • 多表连接基本查询

      SELECT {DISTINCT} 列名 FROM 表名1 别名1，表名2 别名2 {WHERE 限制条件 ORDER BY 排序字段 ASC|DESC...}； 交叉连接（CROSS JOIN）会出现笛卡尔积现象

      关联查询

      可以给每一个查询后的表起一个别名

      自然连接（NATURAL JOIN）会自动的分析管理条件进行连接，去掉笛卡尔积

      USING子句，直接管理操作

      ON子句，自己编写连接条件

    • 外连接【右连接 RIGHT JOIN】/【左连接 LEFT JOIN】

    • 分组函数

      • 统计记录数【count()】

        不建议使用count(*)，可以使用一个具体的列以免影响性能。

      • 最小/大值查询【min()/max()】

      • 查询平均值【avg()】

      • 求和函数【sum()】

    • 分组统计

      使用GROUP BY来分组

      【SELECT 列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段,分组字段1，…} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC】

      注意：

      1. 如果使用分组函数，SQL只可以把GOURP BY分组条件字段和分组函数查询出来，不能有其他字段。

      1. 如果使用分组函数，不使用GROUP BY 只可以查询出来分组函数的值

  • 子查询

    • 在一个查询的内部还包括另一个查询，则此查询称为子查询。

      所有的子查询必须在“（）”中编写

    • 子查询在操作中有三类：

      单行单列列子查询：返回的结果是一列的一个内容

      多行单列子查询：返回多个行一个列的内容

      多行子查询：返回多条记录

    • exist/not exist

      exists （sql 返回结果集为真）

      not exists (sql 不返回结果集为真）

    • union/union all

      Union：对两个结果集进行并集操作，不包括重复行。

      Union All：对两个结果集进行并集操作，包括重复行。

  • 数据库操作

    • insert

      【INSERT INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...);】

      注意：如果不输入列名必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null

    • update

      • 全部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....

      • 局部修改：UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；

    • delete

      DELETE FROM 表名 WHERE 删除条件; 在删除语句中如果不指定删除条件的话就会删除所有的数据

  • 事务：保证数据的完整性，要么同时成功，要么同时失败

  • 表的管理

    • 建表

      【create table 表名 (字段1 数据类型 [default默认值],字段2 数据类型 [default默认值],...字段n 数据类型 [default默认值]);】

    • 表删除【DROP TABLE 表名】

    • 修改表（使用alter可以修改表）

      添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)

      修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)

    • 截断表【语法：TRUNCATE TABLE 表名】

      表中使用delete语句删除数据，则可以使用rollback来回滚，

      想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法

  • 约束

    • 主键约束

      主键约束主要在ID上使用，默认不为空，建表时可以指定，主键不可重复不可为空，可自定义主键约束的名字

    • 非空约束

      使指定字段不可为空

    • 唯一约束

      表中一个字段的内容是唯一的，名字可以自定义

    • 检查约束

      约束字段值的合法范围

    • 外键约束

      外键是两张表的约束，保证关联数据的完整性。外键一定是主表的主键，删表时一定先删字表再删主表，如果直接删主表会出现由于约束存在无法删除的问题

  • 视图

    相当于封装了一条复杂查询的语句

    CREATE VIEW 视图名称 AS 子查询

    CREATE OR REPLACE VIEW 视图名称 AS 子查询

  • 索引

    索引是用于加速数据存取的数据对象

    • 单例索引

      • 基于单个列所建立的索引

    • 复合索引

      • 复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同

    • 使用原则

      在大表上建立索引才有意义

      在where子句后面或者是连接条件上建立索引

      索引的层次不要超过4层