软件测试 数据库 Oracle 数据库实例的开启和关闭 Oracle查询操作 连接符 ||

1 Oracle 11g的新功能（了解）

1 增强信息生命周期管理和存储管理能力
Oracel 11g具有极强的数据划分和压缩功能，可以实现更经济的信息生命周期管理和存储管理。
2 全面回忆数据变化
Oracle 11g具有全面回忆组件，可帮助管理员查询在过去某些时刻指定表格中的数据。
3 最大限度的提高信息可用性
Oracle闪回交易，可以轻松撤销错误交易以及任何相关交易;并行备份和恢复功能，可改善非常大的数据库的备份和存储性能；“热修补”功能，不必挂载数据库就可以进行数据库修改，提高系统可用性。
4 Oracle快速文件
Oracle 11g具有在数据库中存储大型对象的下一代功能，这些对象包括图像、大型文本或一些先进的数据类型。如XML、医疗成像数据和三维对象。
5 更快的XML
在Oracle 11g中，XML DB的性能获得了极大的提高，XML DB是Oracle数据库的一个组件，可以帮助客户以本机方式存储和操作XML数据（本地存储）。
6 透明的加密
Oracle 11g进一步增强了Oracle数据库的安全性。
7 嵌入式OLAP行列
Oracle 11g在数据仓库方面也进行了创新，OLAP行列下载可以在数据库中像物化图那样使用。联机分析处理OLAP，它使分析人员能够迅速、一致、交互地从各个方面观察信息，以达到深入理解数据的目的。图形化界面
8 连接汇合和查询结果高速缓冲
Oracle 11g进一步增强了Oracle在性能和拓展性方面的业界领先地位，增强了查询结果高速缓冲等性能。
9 增强了应用开发性能
Oracle 11g提供了多种开发工具共开发人员选择，它提供了简化应用开发流程。

2 数据库实例

定义
指的是Oracle数据库中的表与数据。实例=表+数据

数据库实例的开启与关闭
开启：只有数据库实例开启之后，才能访问数据里面的内容。
关闭：把数据库关闭，不再开放表和数据。

数据库->图书馆
实例->书籍
数据库实例关闭->图书馆关闭
数据库实例开启->图书馆开放

2.1 开启数据库实例

第一步，启动实例；第二步，加载数据库；第三步，打开数据库。
startup[nomount | mount | open | force]
nomount 开启实例，加载数据库，但是数据库还是一个关闭的状态
mount 启动实例，加载数据并保持数据的关闭状态
open 启动实例，加载数据库，打开数据库
force 强制终止实例并重启数据

在Oracle中，进行insert、update、delete操作表中的数据不会马上改变，需要提交事务工作（commit）才会改变。

当用scott用户名登录，密码tiger，该用户在安装Oracle时，指令管理的时候添加了3个用户。

sys 超级系统管理员
scott system 普通用户

用以上帐户登录进入Oracle，默认打开数据库实例和加载数据库。

2.2 关闭数据库实例

第一步，关闭数据库；第二步，卸载数据库；第三步，关闭Oracle。
使用shutdown语句关闭数据库，具体语法格式如下：
shutdown[normal | transactional | immediate | abort]
normal：正常方式关闭数据库
transactional：在当前的所有的活动事务被提交完毕之后，关闭数据库
immediate：尽可能短的时间内立刻关闭数据库
abort：终止的方式关闭数据库

2.3 用户切换

普通用户切换
conn 用户名/密码;

sys用户切换
conn 用户名/密码 as sysdba;

显示当前用户
show user;

输入shutdown abort，终止的方式关闭数据库，如下：

输入startup open，启动实例，加载数据库，打开数据库，如下：

数据库实例的启动与关闭，只有sys用户才有权限！

3 SQL、SQL Plus和Oracle数据类型

SQL语言三个特征
集合性；统一性；可移植性（跨平台）

SQL语言分类
数据查询语言（DQL） select语句。
数据操纵语言（DML） insert，update，delete三条语句。
数据定义语言（DDL）create，alter，drop。数据库对象（表，视图，索引）
数据控制语言（DCL） 授权grant，收回权限revoke
事务控制语言（TCL） commit提交。rollback回滚或者撤销。savepoint设置保持点。

SQL Plus工具主要用来进行数据查询和数据处理。利用SQL Plus可将SQL和Oracle专有的PL/SQL结合起来进行数据查询和处理。SQL*Plus工具具备以下功能：
定义变量，编写SQL语句。
插入、修改、删除、查询，以及执行命令和PL/SQL语句。比如，执行show parameter命令。
格式化查询结构、运算处理、保存、打印机输出等。
显示任何一个表的字段定义，并实现与用户进行交互。
完成数据库的所有管理工作。比如，维护表空间和数据表。
运行存储在数据库中的子程序或包。
以sysdba身份登录数据库实例，可以实现启动/停止。

Oracle数据类型
字符串类型
varchar2(长度)——>MySQL中的varchar(长度)
数值类型
number/number(长度) ——>MySQL中的int
number(总位数,小数位)——>MySQL中的decimal(总位数,小数位)
日期类型 date

Oracle用户模式
目的：方便管理每个用户所包含的数据库对象（表，视图，索引），就引入模式的概念。
模式：一个数据库所有对象的集合。模式的名字和用户的名字相同。
一个用户拥有一个模式。
例子：scott用户对应scott模式，该模式下有4个数据库对象，dept表，emp表，salgrade表，bonus表。

4 Oracle查询操作

4.1 环境设置

设置每行显示的字符总数：

set linesize 长度;

设置每页显示多少行：

set pagesize 行数;

4.2 简单查询

只包含select子句和from子句的查询就是简单查询。
格式如下：

select 字段 from 表名;

查询全部表名称，语句如下：

 select * from tab;

查看bonus表语句如下：

select * from bonus;

未选定行表示没有查询结果！！！

在Oracle有隐藏列，行标识符，rowid
查询emp表中的rowid，ename，job的内容，语句如下：

select rowid,ename,job from emp;

查看表结构语句如下：

desc 表名;

带有表达式的select子句
算术运算符：+ - * / ()

求出雇员的姓名及年薪
语句如下：

select ename,sal*12 年薪 from emp;

去重复 distinct
格式如下：

select distinct 字段 from 表名;

查询出所有的工作种类，语句如下：

select distinct job from emp;

别名使用
格式一如下：

select 字段 别名 from 表名;

格式二如下：

select 字段 as "别名" from 表名;

在Oracale别名要用双引号，在MySQL中别名用单引号，双引号、单引号和as都可以省略不写。

查询emp表的empno，ename和job，并分别起别名。
语句如下：

select empno as "员工编号",ename as "姓名",job as "职务" from emp;

4.3 条件查询

格式如下：

select 字段 from 表名 where 条件;

查询工资高于1200的人，语句如下：

select * from emp where sal>1200;

all只有与所有元素的比较值为true的情况下，才返回数据的查询行。
格式如下：

select 字段 from 表名 where 字段 比较运算符all(值1,值2,……);

把括号里面的值同时全部满足，才会输出结果。

使用all关键字过滤工资(sal)同时不等于3000,950,800的员工记录。
语句如下：

select * from emp where sal<>all(3000,950,800);

注意：字符串和日期值必须使用单引号标识。（与MySQL相同）

判断为空is null
判断不为空is not null
查询每月可以得到奖金的雇员信息，语句如下：

select * from emp where comm is not null;

分析：奖金为0，但是不为null。
查询没有奖金的雇员，语句如下：

select * from emp where comm is null;

and关键字
查询基本工资大于1500和可领取奖金的雇员的信息，语句如下：

select * from emp where sal>1500 and comm is not null;

or关键字
查询基本工资大于1500或者可领取奖金的雇员的信息，语句如下：

select * from emp where sal>1500 or comm is not null;

not关键字
取反，针对条件的结果进行取反的操作，把真的条件变为假，假的变为真。
查询出基本工资不大于1500，同时不可以领取奖金的雇员信息，语句如下：

-- 不使用not
select * from emp where sal<=1500 and comm is null;

-- not 取反
-- 不大于取反大于 不可以取反可以 and取反or
select * from emp where not(sal>1500 or comm is not null);

查询基本工资大于500，但是小于3000的全部雇员的信息，语句如下：

-- 不使用not关键字
select * from emp where sal>1500 and sal<3000;

-- 大于取反小于等于，小于取反大于等于，and取反or
select * from emp where not(sal<=1500 or sal>=3000);

between……and……
指定方位查询的过滤语句 [……,……]
查询工资在1500到3000员工信息，语句如下：

-- 包含1500和3000两个数值
select * from emp where sal between 1500 and 3000;

查询工资不在1500到3000员工信息，语句如下：

select * from emp where sal  not between 1500 and 3000;

Oracle中数据的大小写是区分的。
查询SMITH雇员的信息，语句如下：

select * from emp where ename='SMITH';

若输入smith，语句如下：

select * from emp where ename='smith';

则会没有查询结果，如下：

in关键字
列表搜索条件
在列表内 格式：where 列名 in(值1,值2,……)
不在列表内 格式：where 列名 not in(值1,值2,……)

查询姓名是SMITH，ALLEN，KING的雇员信息，语句如下：

select * from emp where ename in('SMITH','ALLen','KING');

查询的出雇员编号不是7369,7499,7521的雇员的具体信息，语句如下：

select * from emp where empno not in(7369,7499,7521);

模糊查询
like关键字
功能：模糊查找
% 匹配任意长度的内容
_ 匹配一个长度的内容

查询出所有雇员姓名中第二个字母包含’M’的雇员信息，语句如下：

select * from emp where ename like '_M%';

查询出不要雇员姓名中第二个字母包含’M’的雇员信息，语句如下：

select * from emp where ename not like '_M%';

like还可以进行日期的匹配操作
查询雇佣日期包含81的雇员信息，语句如下：

select * from emp where hiredate like '%81%'

排序查询
order by
asc 升序 默认情况下
desc 降序
要求按照工资由低到高排序，语句如下：

select * from emp order by sal;

检索emp表中所有的数据，并按照部门编号升序，员工编号降序来排序,语句如下：

select * from emp order by deptno asc,empno desc;

分组查询（与MySQL略微不同）
group by
格式：

select 字段,聚合函数 from 表名 group by 字段 having 聚合函数当条件;

select子句后的字段必须与group by字段一致！

在emp表中，通过分组的方式计算每个部门的平均工资，语句如下：

select deptno,avg(sal) 平均工资 from emp group by deptno;

在分组过程中，如果有聚合函数当条件，可以放在having之后。
在emp表中，先通过分组的方式计算每个部门的平均工资，然后通过having过滤掉平均工资大于2000的记录信息，语句如下：

select deptno,avg(sal) 平均工资 from emp group by deptno having avg(sal)<=2000;

过滤平均工资大于2000的意思就是留下平均工资小于等于2000的信息。

完整的查询结构：

select 字段 from 表名 where 条件 group by 字段 having 分组条件 order by 字段;

4.4 多表查询

4.4.1 内连接

内连接的两种表连接的方式和MySQL的写法要求是一样的。
内连接的查询特征：只有两个表中的数据相互匹配到的情况下，才会有查询结果。（交集，公共部分）
格式如下：

-- where
select 表1.字段,表2.字段
from 表1,表2,…… where 表1.g共有字段=表2.共有字段 and 表2.共有字段=表3.共有字段 and ……

-- on
select 表1.字段,表2.字段
from 表1 join 表2
on 表1.g共有字段=表2.共有字段 and 表2.共有字段=表3.共有字段

emp(empno,ename,sal,deptno)
dept(dname,……)
在Scott模式下，通过deptno列来关联emp和dept表，并检索这两个表中相关字段的信息，语句如下：

-- scott用户
show user;

-- where
select empno,ename,dname from emp a,dept b where a.deptno=b.deptno;

-- on
select empno,ename,dname from emp a jon dept b on a.deptno=b.deptno; 

4.4.2 外连接

特点：至少返回一个表的所有行。
险些的是左表，后写的是右表。
左外连接。left [outer] join 返回表的是左表的所有行。
右外连接。right [outer] join 返回表的是右表的所有行。
完全连接。full [outer] join 返回两个表的所有行。（MySQL中没有的！）

实现emp表和dept表的外部连接，语句如下：

-- 左外连接
select empno,ename,dname from emp left join dept on emp.deptno=dept.deptno;

左外连接，左表都能匹配到右表的值，故没有null值，结果如下：

-- 右外连接
select empno,ename,dname from emp right join dept on emp.deptno=dept.deptno; 

右外连接，结果如下：

-- 完全连接
select empno,ename,dname from emp full join dept on emp.deptno=dept.deptno; 

完全连接，结果如下：

4.4.3 自然连接

natural join
功能：自动匹配两个表中具有相同名称的列
由于自然连接强制要求表之间具有相同的列名称，所以在设计表的时候会出现不可预知的错误，所以在实际中很少用到。
在emp表中查询工资大于2000的记录，并实现emp表和dept表的自然连接，语句如下：

select empno,ename,dname 
from emp natural join dept where sal>2000;

5 连接符 ||

要求查询出雇员的编号，姓名，工作，按照以下格式显示∶
编号是:7369的雇员,姓名是:SMITH,工作是:CLERK
emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
语句如下：

select '编号是:' || empno || '的雇员,姓名是:' || 'ename' || ',工作是:' || job
from emp;