0.体系结构(掌握|重点)

数据库体系结构
数据库体系结构

思维导图下载 提取码:tiyr

1 用户与权限(了解)

1.1 默认用户

  • 只有用合法的用户帐号才能访问Oracle数据库
  • Oracle 有几个默认的数据库用户

Oracle默认用户
1.SYS  2.SYSTEM  3.SCOTT

  • 数据库中所有数据字典表和视图都存储在 SYS 模式中。SYS用户主要用来维护系统信息和管理实例。
  • SYSTEM 是默认的系统管理员,该用户拥有Oracle管理工具使用的内部表和视图。通常通过SYSTEM用户管理数据库用户、权限和存储等
  • SCOTT用户是Oracle 数据库的一个示范帐户,在数据库安装时创建

1.2 创建新用户

  • 要连接到Oracle数据库,就需要创建一个用户帐户
  • 每个用户都有一个默认表空间和一个临时表空间
    命令格式:
CREATE USER [username]        //用户名
IDENTIFIED BY [password]      //密码
DEFAULT TABLESPACE [tablespace]     //默认表空间
TEMPORARY TABLESPACE [tablespace];  //临时表空间

1.3 授予权限

GRANT CONNECT TO [username];        //允许用户连接至数据库
GRANT RESOURCE TO [username];       //允许用户使用数据库中的存储空间
GRANT CREATE SEQUENCE TO [username];    //允许用户在当前模式中创建序列
GRANT SELECT ON [tablename] TO [username];    //允许用户查询表中的记录
GRANT UPDATE ON [tablename] TO [username];    //允许用户更新表中的记录
GRANT ALL ON TEST TO MARTIN;        //允许用户插入、删除、更新和查询
ALTER USER [username] IDENTIFIED BY [password];    //修改用户的密码
DROP USER [username] CASCADE;           //删除用户的用户模式

1.4 撤销权限

REVOKE [权限] ON [table] FROM [user]

2 数据库的启动与关闭 (掌握|难点)

2.1 启动数据库

  • 用户可以在SQLPLUS上启动和关闭数据库,另外,用户必须以SYSDBA登录才能启动和关闭数据库。
STARTUP NOMOUNT    //只打开实例,读取初始化参数据文件,分配内存,启动后台进程。
                    用户可以在该状态下创建数据库和重建控制文件。
STARTUP MOUNT    //打开实例,按照control_files参数装入控制文件。用户在该状态下
                    可以进行数据介质恢复。
STARTUP [OPEN]    //打开实例,装入控制文件,从控制文件中的数据库文件和日志文件
                    位置打开数据文件和日志文件。用户可以访问数据库对象。
STARTUP FORCE    //相当于SHUTDOWN ABORT + STARTUP OPEN

#从NOMOUNT到MOUNT状态需要执行SQL命令:
ALTER DATABASE MOUNT;
#从MOUNT状态到OPEN状态需要执行SQL命令:
ALTER DATABASE OPEN;

例:以只读方式打开数据库

SQL>STARTUP MOUNT
SQL>alter database open read only;

例:以限制方式打开数据库

SQL>STARTUP RESTRICT
SQL>ALTER SYSTEM ENABLE | DISABLE RESTRICED SESSION

2.2 关闭数据库

SHUTDOWN NORMAL    //禁止新的对话产生;等待用户全部退出后,发生CHECKPOINT,
                    同步数据库;关闭数据文件、日志文件和控制文件,关闭实例。
SHUTDOWN TRANSACTION    //禁止新的对话产生;等待用户事务结束后,终止用户对话,
                    CHECKPOINT,同步数据库;关闭数据文件、日志文件和控制文件,关闭实例。
SHUTDOWN IMMEDIATE    //禁止新的对话产生;立即终止并回滚事务,发生CHECKPOINT,
                    同步数据库;关闭数据文件、日志文件和控制文件,关闭实例。
SHUTDOWN ABORT    //直接关闭实例,下次启动时需要进行实例的恢复

3 数据库操纵(了解)

3.1 查询 SELECT

从表的一个或多个列(column_1,column_2,...,column_n)中检索数据

SELECT
  column_1, 
  column_2, 
  ...
FROM
  table_name;

查看所有列,从表中返回所有列的数据

SELECT
    *
FROM
    customers;

利用现有的表创建表

CREATE TABLE <new_table_name> AS
SELECT column_names FROM <old_table_name>;

在表中,可能会包含重复值。
关键词 DISTINCT 用于返回唯一不同的值。

SELECT DISTINCT 列名称 FROM 表名称

3.2 插入 INSERT

插入日期类型的值

  • 日期数据类型的默认格式为“DD-MON-YY”
  • 使用日期的默认格式
  • 使用TO_DATE函数转换
INSERT INTO order_master
VALUES('o001', '13-3月-20', 'V002', 'c', ‘23-3月-20');
INSERT INTO my_table (date_col)
VALUES (TO_DATE('2020-03-18', 'YYYY-MM-DD'));

插入来自其它表中的记录

INSERT INTO <table_name> [(cloumn_list)]
SELECT column_names FROM <other_table_name>;

3.3 更新 UPDATE

3.4 删除 DELATE

4 SQL 操作符(掌握|重点)

4.1 算术操作符

  • 算术操作符用于执行数值计算
  • 可以在SQL语句中使用算术表达式,算术表达式由数值数据类型的列名、数值常量和连接它们的算术操作符组成
  • 算术操作符包括加(+)、减(-)、乘(*)、除(/)
#例子
SQL> SELECT hire_date       //查询在职时间大于一年,并按雇佣事件排序
FROM employees        
WHERE SYSDATE - hire_date > 365    //SYSDATE 系统时间函数
ORDER BY hire_date;        //按照xxx排序

SQL > UPDATE employees
SET salary = salary * 1.1;    //更新salary的值为原来的1.1倍

4.2 比较操作符

  • 比较操作符用于比较两个表达式的值
  • 比较操作符包括 =、!=、<、>、<=、>=、IS [NOT] NULL、LIKE、[NOT] BETWEEN…AND、[NOT]
    IN、EXISTS和IS OF type等
SQL> SELECT *
FROM employees
WHERE salary != 2500    //不等于2500的
ORDER BY employee_id;

SQL> SELECT * FROM employees
WHERE salary
BETWEEN 2000 AND 3000    //查询salary的值在2000道3000之间的
ORDER BY employee_id;

SQL> SELECT salary
FROM employees
WHERE last_name LIKE 'R%'    //以R开头的
ORDER BY salary;

利用 not like '%' 返回的是以字母开头的记录,
利用 like '%' 返回的是以汉字开头的.

原理:asciistr()函数对非ASCII代码,会转换成二进制,且前面加 ;所以asciistr(acolumn) 如果是汉字;转换出来的会有 \

例如:

select asciistr('china') from dual;

china

4.3 逻辑操作符

  • 逻辑操作符用于组合多个比较运算的结果以生成一个或真或假的结果。
  • 逻辑操作符包括与(AND)、或(OR)和非(NOT)。
SQL> SELECT *
FROM employees       //查询满足指定工种条件而且满足指定部门条件的员工信息,并升序排序
WHERE job_id = 'PU_CLERK'
AND department_id = 30
ORDER BY employee_id; 

4.4 集合操作符

集合操作符将两个查询的结果组合成一个结果

UNION        用来求两个集合的并集,并去掉重复值
UNION ALL    用来求两个集合的并集
INTERSECT    用来求两个集合的交集,并去掉重复值
MINUS        用来求在第一个集合中存在,而在第二个集合中不存在的记录,并去掉重复值

例如:

SQL> SELECT product_id FROM inventories
MINUS
SELECT product_id FROM order_items
ORDER BY product_id;

4.5 连接操作符

连接操作符用于将多个字符串或数据值合并成一个字符串

SQL> SELECT 'Name is ' || last_name
FROM employees
ORDER BY last_name;

通过使用连接操作符可以将表中的多个列合并成逻辑上的一行列

4.6 操作符的优先级

  1. 算术操作符 --------最高优先级
  2. 连接操作符
  3. 比较操作符
  4. NOT 逻辑操作符
  5. AND 逻辑操作符
  6. OR 逻辑操作符 --------最低优先级

5 SQL 函数(掌握|难点)

  • Oracle 提供一系列用于执行特定操作的函数
  • SQL 函数带有一个或多个参数并返回一个值

5.1 单行函数

  • 单行函数对于从表中查询的每一行只返回一个值
  • 以出现在 SELECT 子句中和 WHERE 子句中

5.1.1 数值函数

  • 数值函数接受数值输入并返回数值。大多数数值函数返回精确到38位小数的数值。

数值函数包括:

  • ABS(绝对值)
  • CEIL(向上取整
  • FLOOR(向下取整)
  • RONUD(number)
  • EXP(e的n次方)
  • LN(自然对数)
  • LOG(对数)
  • MOD(求余)
  • SIGN(标志)
  • SQRT(平方根)
  • POWER(指数)
  • ACOS(arc cos)
  • ASIN、ATAN、COS、COSH(双曲余弦)
  • SIN、SINH、TAN、TANH

例如:

函数输入输出
Abs(n)Select abs(-15) from dual;15
Ceil(n)Select ceil(44.778) from dual;45
Cos(n)Select cos(180) from dual;-.5984601
Cosh(n)Select cosh(0) from dual;1
Floor(n)Select floor(100.2) from dual;100
Power(m,n)Select power(4,2) from dual;16
Mod(m,n)Select mod(10,3) from dual;1
Round(m,n)Select round(100.256,2) from dual;100.26
Trunc(m,n)Select trunc(100.256,2) from dual;100.25
Sqrt(n)Select sqrt(4) from dual;2
Sign(n)Select sign(-30) from dual;-1

5.1.2 字符函数

  • 字符函数接受字符输入并返回字符或数值

字符函数【返回字符】包括:

  • CONCAT(连接)
  • INITCAP(首字母大写)
  • LOWER(小写)
  • UPPER、LPAD(左填充)
  • LTRIM(左修剪)
  • RPAD、RTRIM、TRIM、REPLACE(替换)
  • TRANSLATE(翻译)
  • SUBSTR(子串)

字符函数【返回数值】包括:

  • ASCII
  • INSTR(子串位置)
  • LENGTH(长度)
    函数| 输入| 输出
Initcap(char)Select initcap(‘hello’) from dual;Hello
Lower(char)Select lower(‘FUN’) from dual;fun
Upper(char)Select upper(‘sun’) from dual;SUN
Ltrim(char,set)Select ltrim( ‘xyzadams’,’xyz’) from dual;adams
Rtrim(char,set)Select rtrim(‘xyzadams’,’ams’) from dual;xyzad
Translate(char, from, to)Select translate(‘jack’,’j’ ,’b’) from dual;back
Replace(char,searchstring,[rep string])Select replace(‘jack and jue’ ,’j’,’bl’) from dual;black and blue
Instr (char, m, n)Select instr (‘worldwide’,’d’) from dual;5
Substr (char, m, n)Select substr(‘abcdefg’,3,2) from dual;cd
Concat (expr1, expr2)Select concat (‘Hello’,’ world’) from dual;Hello world
Length(char)Select length(’france’)from dual;6

5.1.3 日期函数

  • 日期函数对日期值进行运算,并生成日期数据类型或数值类型的结果

日期函数包括:

  • ADD_MONTHS(加上月份数)
  • CURRENT_DATE(当前时区的当前日期)
  • EXTRACT(提取参数指定部分的值)
  • LAST_DAY(返回月的最后一天)
  • MONTHS_BETWEEN(返回两个日期之间月数)
  • NEXT_DAY(指定下一个星期几的日期)
  • ROUND(四舍五入)
函数输入输出
ADD_MONTHS(date,integer)SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 1), 'DD-MONYYYY') "Next month" FROM DUAL;15-4月 -2020
CURRENT_DATESELECT CURRENT_DATE FROM DUAL;15-3月 -20
EXTRACT(dateime)SELECT EXTRACT(MONTH FROM SYSDATE) "YEAR" FROMDUAL;3
LAST_DAY()SELECT LAST_DAY(SYSDATE) "Last" FROM DUAL;31-3月 -20
MONTHS_BETWEEN()SELECT MONTHS_BETWEEN (TO_DATE('04-01-2020','MM-DD-YYYY'),TO_DATE('03-15-2020','MM-DD-YYYY') ) "Months" FROM DUAL;.548387097
NEXT_DAY()SELECT NEXT_DAY('15-3月-20','星期五') "NEXT DAY" FROMDUAL;20-3月 -20
ROUND()SELECT ROUND (TO_DATE (‘15-3月-20','DD-MON-YY'),'YEAR')FROM DUAL;01-1月 -20

5.1.4 转换函数

  • 转换函数将值从一种数据类型转换为另一种数据类型

常用的转换函数有:

  • TO_CHAR
  • TO_DATE //修改日期格式
    Alter session set nls_date_format= ’DD-MM-YYYY’;
  • TO_NUMBER

通常情况下,一个表达式中不可以包含不同的数据类型值然而Oracle支持从一种数据类型到另一种数据类型隐式和显式转换。Oracle推荐用户使用显示转换,而不是依赖隐式或自动转换隐式转换矩阵

5.1.5 其他函数

  • 用来转换空值的函数:NVL、NVL2和NULLIF
  • DECODE函数
SELECT ename,NVL(comm,0) FROM emp;

SELECT ename, NVL2(comm,-1,1) FROM emp;

--CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
SELECT itemdesc, NULLIF(re_level,max_level)
FROM itemfile;

SQL> SELECT product_id,
 DECODE (warehouse_id, 1, 'Southlake',
 2, 'San Francisco',
3, 'New Jersey',
4, 'Seattle',
 'Non domestic') "Location"
 FROM inventories
 WHERE product_id < 1775
 ORDER BY product_id, "Location";

5.2 分组函数

  • 分组函数基于一组行来返回结果
  • 为每一组行返回一个值

分组函数:

  • AVG
  • MIN
  • MAX
  • SUM
  • COUNT

GROUP BY子句

  • 用于将信息划分为更小的组
  • 每一组行返回针对该组的单个结果

HAVING子句

  • 用于指定 GROUP BY 子句检索行的条件

例如

SELECT job, AVG(sal) FROM emp GROUP BY job;

SELECT job, MAX(sal) FROM emp
GROUP BY job
HAVING job NOT IN ('manager');

总结SELECT语句执行过程

SELECT deptno,max(sal)
FROM emp
WHERE deptno between 10 and 30
GROUP BY deptno
HAVING max(sal)<=5000
ORDER BY deptno;
  • 通过SELECT子句选择显示的列或表达式及组函数;
  • 通过FROM子句找到需要查询的目标表;
  • 通过WHERE子句进行非分组函数筛选判断;
  • 通过GROUP BY子句完成分组操作;
  • 通过HAVING子句完成组函数筛选判断;
  • 通过ORDER BY子句进行排序操作。

5.3 分析函数

分析函数根据一组行来计算聚合值

  • 用于计算完成聚集的累计排名、移动平均数等
  • 分析函数为每组记录返回多个行

分析函数

  • ROW_NUMBER  返回连续的排位,不论值是否相等
  • RANK  具有相等值的行排位相同,序数随后跳跃
  • DENSE_RANK  具有相等值的行排位相同,序号是连续的

6 控制文件

6.1控制文件介绍

  • 是数据库创建的时候自动生成的二进制文件
  • 记录了数据库的状态信息
  • 在数据库启动的Mount阶段被读取
  • 控制文件的位置在参数文件中
  • 数据库服务器可以不断的修改控制文件中的内容,其他任何用户都无法修改控制文件中的信息
  • 控制文件可以在你改变文件名或移动文件时而被更新

状态信息包括:

  • 数据库名称(一个控制文件只能属于一个数据库)
  • 数据库建立时的时间戳。
  • 数据文件的名称、位置、联机/脱机状态信息
  • 重做日志文件的名称、位置及归档信息
  • 所有表空间信息
  • 当前日志序列号
  • 最近检查点信息
  • 恢复管理器信息(RMAN)

如何查看控制文件信息:

从v$controlfile视图中查看控制文件信息

select name from v$controlfile;

还可以从v$parameter视图查看

select name, value from v$parameter
where name = ’control_files’;

如何查看控制文件所存的内容信息:

可以使用v$controlfile_record_section视图

select type, record_size,record_total,record_used
from v$controlfile_record_section;

6.2使用多个控制文件(重点)

  • 默认情况下,在数据库创建过程中至少创建一个控制文件副本。在某些操作系统上,默认设置是创建多个副本。
  • 应该在数据库创建期间创建控制文件的两个或更多副本。存储在不同的物理磁盘上。
  • 如果拷贝控制文件放在不同的目录上,就要修改参数文件INITsid.ORA中的CONTROL_FILES以便包括所有的控制文件。
  • 比如:control_files = ("……/control01.ctl", "……control02.ctl","……/control03.ctl")
  • 由于在多个磁盘上都存放有同样的控制文件,这样就降低了单个磁盘可能损坏带来的风险。
  • 数据库写入数据库初始化参数文件CONTROL_FILES中列出的所有文件名
  • 在数据库运行期间,数据库仅读取CONTROL_FILES参数中列出的第一个文件。
  • 如果在数据库运行期间任何控制文件变得不可用,则实例将无法操作,应中止。

6.3创建控制文件(重点)

创建控制文件副本的步骤:
1.关闭数据库(shutdown)
2.创建初始化参数文件initorcl.ora (create pfile from spfile;)
3.用操作系统命令拷贝控制文件到不同的目录上
4.改变初始化参数文件INITsid.ORA中的CONTROL_FILES参数
5.用initorcl这个初始化参数文件蓝本创建spfile (create spfile frompfile;)
6.重新启动数据库
7.从数据字典V$PARAMETER 中查询控制文件的信息,或者从视图V$CONTROLFILE中查询控制文件的信息

获得数据库所有数据文件和联机重做日志文件信息
可以通过下面命令获取

alter database backup controlfile to trace as ’….txt’

也可以分别获取
--查看日志文件

 select member from v$logfile;

--查看数据文件

 select name from v$datafile;

--查看控制文件

 select value from v$parameter
 where name = ’control_files’;

2.关闭数据库。尽可能采用正常模式关闭数据库,如果不行使用立即关闭模式,强制模式关闭数据库作为最后手段。
3.备份所有的数据文件和日志文件。
4.用NOMOUNT启动一个新的数据库实例。

 SQL>startup nomount;

5.用CREATE CONTROLFILE命令创建新的控制文件。
并在INITsid.ORA参数文件中指定。
语法:


例子:

CREATE CONTROLFILE
 SET DATABASE “MYORCL"
 LOGFILE
     GROUP 1 ('E:\ORACLE\ORADATA\MYORCL\REDO0101.LOG',
         'E:\ORACLE\ORADATA\MYORCL\REDO0102.LOG'),
     GROUP 2 ( 'E:\ORACLE\ORADATA\MYORCL\REDO0201.LOG',
         'E:\ORACLE\ORADATA\MYORCL\REDO0202.LOG'),
     GROUP 3 ('E:\ORACLE\ORADATA\MYORCL\REDO0301.LOG',
         'E:\ORACLE\ORADATA\MYORCL\REDO0302.LOG')
 RESETLOGS
 DATAFILE
     'E:\ORACLE\ORADATA\MYORCL\SYSTEM01.DBF' SIZE 3M,
     'E:\ORACLE\ORADATA\MYORCL\USERS01.DBF' SIZE 5M,
     'E:\ORACLE\ORADATA\MYORCL\TEMP01.DBF' SIZE 5M
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 100
 MAXINSTANCES 8
 MAXLOGHISTORY 292
 NOARCHIVELOG

6.使用ALTER DATABASE OPEN命令打开数据库。
7.关闭数据库并备份数据库。

6.4重命名和重定位控制文件(难点)

  • 重命名控制文件是指改变控制文件的名称
  • 重定位控制文件是指改变控制文件的位置,即移动控制文件
  • 重命名和重定位控制文件的过程与创建控制文件副本的过程类似,步骤如下:
  1. 正常模式关闭数据库
  2. 使用操作系统命令更改要重命名的控制文件名称,或将要重定位的控制文件复制到新的位置
  3. 更改初始化参数文件的CONTROL_FILE的值
  4. 启动数据库

6.5删除控制文件

在有多个控制文件副本的情况下可以删除控制文件,
过程如下:
1.修改初始化参数文件中的CONTROL_FILES参数,令参数中不要包含要删除的控制文件
2.关闭数据库(immediate)
3.删除不再需要的控制文件
4.重新启动数据库
5.查看控制文件

6.6备份控制文件

在下列情况下最好备份控制文件:
1.增加、删除或重命名数据文件
2.增加或删除表空间以及更改表空间的读写状态
3.增加或删除重做日志文件
使用ALTER DATABASE BACKUP CONTROLFILETO '<filename.bkp>' ;
或者alter database backup controlfile to trace ;
Oracle公司建议无论你是否改变数据库结构、或加数据文件、或重新命名文件或删除重做日志文件,都要进行备份。

6.7查询控制文件(重点)

可以从视图V$CONTROLFILE中查到控制文件的信息,status字段表示控制文件的状态,一般总是为空

SQL> select * from v$controlfile ;

可以从V$CONTROLFILE_RECORD_SECTION视图查看控制文件所记录的内容信息

SQL> desc v$controlfile ;