移动互联网数据库应用与开发（Oracle）重点

1.关系模型的表达方式：二维表。

2.数据库中存储的基本对象是数据。

3.数据库系统的核心是DBMS。

4.DBMS的主要功能？

1）数据库的定义功能（ DDL ，数据库三级结构、两级映象）
2）数据库的操纵功能（ DML ，过程性 DML （层次和网状）和非过程性 DML （关系型） ）
3）数据库的保护功能（恢复、并发、完整性、安全性）
4）数据库的维护功能（ DB 的载入、转换、转储等）
5）数据字典（存放三级结构定义的数据库）

5.SQL

例：查询需要使用的学生表student和班级表classes。

学生表student 班级表classes

SELECT FROM 查询 
INSERT INTO 新增
UPDATE 修改
DELETE 删除
ALTER TABLE 修改表结构

条件查询

WHERE AND 与条件  OR 或条件 () 表示条件的组合
LIKE
IS NULL
IS NOT NULL

分组条件

GROUP BY 分组条件 HAVING 对分组后的数据进行筛选

排序

ORDER BY    ASC（升序）,DESC（降序）

统计函数：SUM,AVG,COUNT,MIN,MAX
多表关联

INNER JOIN  = JOIN
LEFT JOIN   = LEFT OUTER JOIN
RIGHT JOIN  = RIGHT OUTER JOIN

例：以学生表和学校表进行关联查询。

学生表和学校表进行的关联查询

子查询：将查询语句A的查询结果作为查询语句B的查询条件使用
单行子查询：子查询的结果必定只有一个值，查询的过程中使用了统计函SUM/AVG/COUNT/MIN/MAX。
多行子查询：
①IN条件判断

SELECT fields FROM table WHERE field1 in (SELECT field2 FROM table WHERE ...);

注：field1 与 field2的字段类型相同
②ANY运算符 > < = >= <= !=
接条件判断，只要条件满足期中一个就可
field > any(子查询)：查询条件是 field 大于 子查询结果的任意一个值就行
③all 运算符
关联子查询：将主查询语句的某些字段拿到子查询内作为子查询的查询条件。
尽量避免使用，当数据量过大时会影响数据性能。

6.表的创建

数据类型：NUMBER,VARCHAR,CHAR,BLOB,DATE
数据表的约束：外键约束、主键约束、非空约束、唯一约束
索引类型：
①B树索引（默认索引，保存讲过排序过的索引列和对应的rowid值）
②位图索引：用于被创建字段的变化偏少，少于基数的1%
③反向键索引：用于被创建字段是递增的情况，让新增的数据平均分配给各分支
④基于函数的索引
序列：序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。
创建序列需要CREATE SEQUENCE系统权限。
调用NEXTVAL将生成序列中的下一个序列号，调用时要指出序列名，即用以下方式调用：序列名.NEXTVAL。
CURRVAL用于产生序列的当前值，无论调用多少次都不会产生序列的下一个值。如果序列还没有通过调用NEXTVAL产生过序列的下一个值，先引用CURRVAL没有意义。调用CURRVAL的方法同上，要指出序列名，即用以下方式调用:序列名.CURRVAL。
表和视图的差异：
①视图是已经编译好的sql语句。而表不是。
②视图没有实际的物理记录。而表有。
③表是内容，视图是窗口。
④表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改。
⑤表是内模式，视图是外模式。
⑥视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
⑦表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
⑧视图的建立和删除只影响视图本身，不影响对应的基本表。

7.PL/SQL 编程

%type
record
变量的定义
游标的使用
游标：

DECLARE
  -- 声明游标
  CURSOR cursor_emp(var_job in VARCHAR2:='MANAGER',var_deptno in int:=10)
  IS SELECT empno,ename,sal FROM emp WHERE deptno=var_deptno and job=var_job;
  -- 定义record数据类型
  type record_emp is record(
    var_empno emp.empno%type,
    var_ename emp.ename%type,
    var_sal emp.sal%type
  );
  -- 指定变量的类型
  emp_row record_emp;
BEGIN
  -- 打开|执行游标
  OPEN cursor_emp();
  -- 读取游标 不可改变
  LOOP
    -- 读取一行数据
    FETCH cursor_emp INTO emp_row;
    -- 判断是否为空
    EXIT WHEN NOT cursor_emp%found;
    -- 执行具体的语句
    dbms_output.put_line(emp_row.var_empno||'--'||emp_row.var_ename||'--'||emp_row.var_sal);
    -- 主动抛出异常
    dbms_output.put_line(32/0);
  END LOOP;
  CLOSE cursor_emp;  -- 关闭游标
EXCEPTION
  when ZERO_DIVIDE then
    dbms_output.put_line('捕获到了异常');
    CLOSE cursor_emp;  -- 关闭游标
  when others
    CLOSE cursor_emp;  -- 关闭游标
END;

8.用户模式

模式：创建一个用户，就相应的创建了 一个模式。模式是指数据库对象，是对用户所创建的数据对象的总称。
模式对象包括表、视图、索引、同义词、序列、过程和程序包等 。
授权语句：

GRANT TO
ROVEKE FROM
CREATE TABLE--只能在自己拥有得模式下建立表
CREATE ANY TABLE--可以在任何模式下建立表

权限查询的相关表：USER_TAB_PRIVS,DBA_SYS_PRIVS
创建角色

CREATE ROLE roleName INDETIFIED BY password;

删除角色

DROP ROLE roleName;

为用户赋予角色

GRANT roleName TO userName;

赋予角色权限

GRANT [权限列表] TO roleName;

回收角色权限

REVOKE [权限列表] FROM roleName;

激活角色

SET ROLL

9.日志系统

1）数据文件：数据文件当然是用来存储实际数据的啦，数据文件是存储数据的物理概念，我们还有一个更大的逻辑概念，叫做表空间，表空间跟数据文件又是一个什么样的关系呢？简单的说就是一个表空间至少包含可一个数据文件，创建数据库对象的时候指定的是使用的表空间，所以就不难理解为什么一个对象(比如一张大表)可以跨越多个数据文件了。
2）控制文件：控制文件在Oracle数据库中扮演着很重要的角色，没有控制文件(或者控制文件损坏了)，运行着的实例立即崩溃，关闭着的死活打不开，你以为你是谁，可以说，控制文件一旦损坏，数据库必然down了。Oracle数据库实例启动过程中，当启动到unmount时，此时只是在内存中为Oracle实例分配了实例空间，然后如果继续要启动到mount状态，这个时候控制文件就闪亮登场了，因为Oracle要依据控制文件找到数据文件跟重做日志文件的路径，确定找到了再进去mount状态，至于打开数据库就是确定这些文件都一致，没有问题的话就能打开了。
3）日志文件：
不对日志文件进行归档。这种模式可以大大减少数据库备份的开销，但可能回导致数据的不可恢复。
归档日志文件：这是指为避免联机日志文件重写时丢失重复数据而对联机日志文件所做的备份，当Oracle转向一个新的日志文件时，将以前的日志文件进行归档。为了防止出现历史“缺口”的情况，一个给定的日志文件在它成功归档之前是不能重新使用的。归档的日志文件，加上联机日志文件，为数据库的所有改变提供了完整的历史信息。

上述学生表和班级表的查询练习：

①从学生表当中查询所有数据

SELECT * FROM student;

②从学生表当中查询学生的姓名、学号、年龄、性别

SELECT stu_name,stu_code,stu_age,stu_gender FROM student;

③从学生表中查询年龄大于18岁的学生

SELECT * FROM student WHERE stu_age > 18;

④从学生表中查询年龄大于18岁的女学生

SELECT * FROM student WHERE stu_age > 18 AND student_gender = ‘女’;

⑤从学生表中查询年龄大于18岁的女学生并且按照年龄降序，班级升序的条件排序

SELECT * FROM student 
WHERE stu_age > 18 AND student_gender = ‘女’
ORDER BY stu_age DESC,class_id ASC;

⑥从学生表中查询年龄大于18岁的女学生或者是年龄小于18的男学生；

SELECT * FROM student
WHERE (stu_age > 18 AND student_gender = ‘女’) OR (stu_age < 18 AND student_gender = ‘男’);

⑦从学生表中查询学生最大、最小的年龄和学生总数，学生平均年龄

SELECT max(age), min(age), count(*), avg(age) FROM student;

⑧从学生表中查询每个班学生最大、最小的年龄和学生总数，学生平均年龄

SELECT class_id,max(age), min(age), count(*), avg(age) FROM student 
GROUP BY class_id;

⑨从学生表中查询每个班学生最大、最小的年龄和学生总数，学生平均年龄,筛选出班级学生总数大于5的数据结果

SELECT class_id,max(age), min(age), count(*) as total, avg(age) FROM student 
GROUP BY class_id 
HAVING total > 5;

⑩从学生表中查询每个班学生最大、最小的年龄和学生总数，学生平均年龄,并且查询结果按照班级排序。

SELECT class_id,max(age), min(age), count(*), avg(age) FROM student 
GROUP BY class_id 
ORDER BY class_id ASC;

⑪从学生表中查询班级名称和每个班学生最大、最小的年龄和学生总数，学生平均年龄,并且查询结果按照班级排序。

SELECT class_name, max(age), min(age), count(*), avg(age) FROM student 
INNER JOIN classes ON classes.class_id = student.class_id 
GROUP BY student.class_id 
ORDER BY student.class_id ASC;

⑫罗列出每个班级的学生信息，按班级排序

SELECT student.class_id, classes.class_name, student.code, student.name 
FROM student 
INNER JOIN classes ON classes.class_id = student.class_id
ORDER BY student.class_id ASC;

SELECT student.class_id, classes.class_name, student.code, student.name 
FROM classes 
LEFT JOIN student ON classes.class_id = student.class_id
ORDER BY student.class_id ASC;

⑬查询学生人数大于5个的班级信息，并且按照班级排序

SELECT class_id,class_name FROM classes 
WHERE (SELECT count(*) FROM student WHERE student.class_id = classes.class_id)>5 
ORDER BY class_id ASC;