Oracle(1)之虚拟机下安装与简单使用

Oracle介绍与安装

简介

Oracle 数据库系统是美国 ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器 (CLIENT/SERVER) 或 B/S 体系结构的数据库之一。比如 SliverStream 就是基于数据库的一种中间件。Oracle 数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。

体系结构

数据库：database

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

实例：instance

一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Background Processes）和内存结构（Memory Structures） 组成。一个数据库可以有 n 个实例。

数据文件：dbf

数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正的存储是在某一个或多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

表空间

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

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

用户

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

表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或多个数据文件中。

由于 Oracle 的数据库不是普通的概念，Oracle 是有用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查询。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了。

SCOTT 用户和 HR 用户：

Oracle 为了让学习者更好的进行学习，在安装成功后，也创建了初始的用户，其中 SCOTT 与 HR 就是初始的普通用户。这些用户下面都默认存在了表结构，我们重点掌握 SCOTT 用户下的所有表。如下所示：

一个数据库一般有一个 orcl 实例，也可以有多个实例。

有了数据库实例，就可以创建表空间。

Oracle 数据库是通过表空间来存储物理表的，一个数据库实例可以有 N 个表空间，一个表空间下可以有 N 张表。

要想在表空间下建表，必须先建立用户，让用户关联该表空间，接下来用户就可以在所关联的表空间下建表。

小结：

1、一个用户只能属于一个表空间。

2、同一个数据库用例中，用户名是不能重复的。

3、不同用户中可以创建同名的表。

4、表空间创建后需要创建用户后才能使用，因为任何数据库对象都需要建立在一个数据用户上。

5、连接 dba 权限的用户可以直接访问其他用户的数据库对象，否则需要相关用户授权。

6、访问其他用户的数据库对象是时需要加上对象所属用户的用户名。

Oracle10g安装

下载

32 位 XP 系统镜像下载，Oracle 10G 下载。

运行XP系统

解压上述系统镜像，如下：

双击 .vmx 后缀文件即可在 VMware 中打开：

选中，点击右边框中的‘开启此虚拟机’，此时虚拟机就会运行 xp 系统，如下：

VMware 安装使用参考

导入Oracle安装包到XP

方式一：VMware tools

如果安装了 VMware tools，则可直接将 Oracle 安装包拖入虚拟机进行导入：

方式二：共享文件夹

虚拟机关闭状态选中 XP 系统，点击‘编辑虚拟机设置’：

选中‘选项’栏位，做如下勾选：

点击‘添加’按钮：

选中 Oracle 安装包所在目录，确定，再次运行 XP，打开我的电脑：

进入网络驱动器下的 ‘Shared Folders’：

即可看到共享目录下的 Oracle 安装包，将其复制到 XP 桌面，解压：

解压工具下载，同样可以通过共享文件夹方式导入安装。

安装Oracle

进入 Oracle 解压目录：

点击 ‘setup.exe’ 启动 Oracle 安装程序：

输入数据库口令，我这里输入‘root’，点击下一步：

继续下一步：

点击安装：

点击‘口令管理’：

解除用户‘SCOTT’，‘HR’的锁定，设置‘SCOTT’的口令为‘tiger’，‘HR’的口令为‘hr’，确定。

点击退出会弹出如下页面：

进入‘C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN’目录下，编辑文件如下：

# listener.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      # HOST 值修改为当前系统 ip
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.161.130)(PORT = 1521))
    )
  )

listener.ora

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    # 修改 HOST 值为Oracle所在主机 ip
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.161.130)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

tnsnames.ora

按顺序重启下面两个服务：

至此 Oracle 服务端的安装已经完成。

使用

客户端连接Oracle

instantclient连接Oracle

点击进入官网下载 instantclient 客户端：

解压到同一目录，命令行下进入该目录，通过 sqlplus 连接：

C:\install\dev\instantclient_11_2>sqlplus scott/tiger@192.168.202.133:/orcl

SQL*Plus: Release 11.2.0.4. Production on Wed Feb  :: 

Copyright (c) , , Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1. - Production
With the Partitioning, OLAP and Data Mining options

SQL>

plsqldeveloper连接Oracle

1、点击下载安装包。

2、安装完成后打开安装目录，新建 config 文件夹，放入如下配置文件：

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    # 修改 HOST 值为当前系统 ip
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.161.130)(PORT = ))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

tnsnames.ora

3、将该目录配入环境变量：

4、打开软件，做如下配置：

5、重启客户端，以 SCOTT 用户登录：

6、显示如下界面即登录成功：

Navicat连接Oracle

1、点击下载Navicat。

2、安装完成后打开，作如下配置：

3、重启Navicat，新建Oracle连接：

基本查询

SQL简介

结构化查询语言（Structured Query Language）简称 SQL，结构化查询语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存取方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。

• DML（数据库操作语言）：其语句包括动词 INSERT、UPDATE 和 DELETE。它们分别用于添加、修改和删除表中的行。
• DDL（数据库定义语言）：其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREATE TABLE 和 DROP TABLE）；为表加入索引等。DDL 包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
• DCL（数据库控制语言）：它的通过 GRANT 或 REVOKE 语句获得许可，确定单个用户和用户组队数据库对象的访问。某些 RDBMS 可用 GRANT 或 REVOKE 控制对表单个列的访问。
• DQL（数据库查询语言）：它的语句基本结构是由 SELECT 子句、FROM 子句、WHERE 子句组成的查询块。

SELECT语句的语法格式和示例

SELECT *|列名 FROM 表名

1、查询语法

SELECT 列名 别名,列名 别名,... FROM 表名

2、别名用法

SELECT DISTINCT *|列名 FROM 表名

3、去重

--查询每个雇员的年薪
SELECT ENAME 员工名称,SAL*12 年薪 FROM EMP

4、四则运算

空值

• 空值是无效的，未指定的，未知的或不可预知的值。
• 空值不是空格或者 0。

注意：

• 包含 NULL 的表达式结果都为 NULL，如：

  SELECT ENAME,12*NULL FROM EMP

  

  例：

• 空值永远不等于空值，如：

  SELECT * FROM EMP WHERE NULL=NULL -- WHERE 结果为假，所以查不到数据

  

  例：

连接符

字符串的连接使用‘||’。

SELECT ('编号是：' || EMPNO || '的员工，姓名是' || ENAME || '，工作是：' || JOB) 连接符使用 FROM EMP

例：

条件查询和排序

--查询能得到奖金的员工
SELECT * FROM EMP WHERE COMM IS NOT NULL

--查询未得到奖金的员工
SELECT * FROM EMP WHERE COMM IS NULL

1、非空和空的限制

--查询工资大于 1500 并且 有奖金的员工
SELECT * FROM EMP WHERE SAL>1500 AND COMM IS NOT NULL

--查询工资大于 1500 或者 有奖金的员工
SELECT * FROM EMP WHERE SAL>1500 OR COMM IS NOT NULL

--查询工资不大于 1500 并且 没有奖金的员工
SELECT * FROM EMP WHERE NOT(SAL>1500) AND COMM IS NOT NULL

2、且（AND）、或（OR）和非（NOT）条件

--例：查询工资在1500-3000的员工
SELECT * FROM EMP WHERE SAL>=1500 AND SAL<=3000
    --也可以通过 BETWEEN AND 实现
SELECT * FROM EMP WHERE SAL BETWEEN 1500 AND 3000

--BETWEEN AND 不仅可以用在数值之间，也可以用在日期之间
--例：查询1981-1-1到1981-12-31号入职的员工
SELECT * FROM EMP WHERE HIREDATE BETWEEN to_date('1/1/1981 00:00:00', 'dd/MM/yyyy hh24:mi:ss') AND to_date('31/12/1981 00:00:00', 'dd/MM/yyyy hh24:mi:ss')

--例：查询员工编号是 7369、7499、7521 的员工信息
    --如果使用之前的做法可以使用 OR 关键字
SELECT * FROM EMP WHERE EMPNO IN (7369,7499,7521)
    --实际上，此时制定了查询范围，那么 SQL 可以使用 IN 关键字
    --    语法：WHERE 列名 IN(值1,值2,值3,...)
    --         WHERE 列名 NOT IN(值1,值2,值3,...)

--其中的值不仅可以是数值类型，还可以是字符串
--例：查询员工姓名是 'SMITH'、'ALLEN'、'WARD' 的员工信息
SELECT * FROM EMP WHERE ENAME IN('SMITH','ALLEN','WARD')

3、范围限制

--模糊查询，即：输入一个关键字，把符合的内容全部查询出来，在 SQL 中使用 LIKE 关键字完成
--    在 LIKE 中主要使用以下两种通配符：
--    "%"：可以匹配任意长度的内容。
--    "_"：可以匹配一个长度的内容。
--例：查询出姓名中第二个字符为 'M' 的员工
SELECT * FROM EMP WHERE ENAME LIKE '_M%'

--例：查询出姓名中包含字符 'M' 的员工
SELECT * FROM EMP WHERE ENAME LIKE '%M%'

4、模糊查询

--Oracle 中不等号的用法有两种形式 "<>" 和 "!="
--查询员工编号不是 7369 的员工
SELECT * FROM EMP WHERE EMPNO <> 7369
--或
SELECT * FROm EMP WHERE EMPNO != 7369

5、不等号

--使用 ORDER BY 对结果进行排序
--例：查询员工的工资从低到高
SELECT * FROM EMP ORDER BY SAL

--如果存在多个排序字段可用 ',' 分隔
SELECT * FROM EMP ORDER BY SAL ASC,HIREDATE DESC

--注意：ORDER BY 语句要放在 SQL 的最后执行

--排序中的空值问题
--查询员工的奖金从低到高
--NULLS FIRST：将空值放在前面
--NULLS LAST：将空值放在后面
SELECT * FROM EMP ORDER BY COMM NULLS FIRST

SELECT * FROM EMP ORDER BY COMM NULLS LAST

6、结果排序

函数

• 函数可以没有参数，但必须要有返回值。
• 函数的类型可以分为单行和多行函数。

单行函数

--字符串连接
--    字符串的连接可以使用 CONCAT 函数也可以使用 '||'
SELECT CONCAT('HELLO ',' ORACLE') STR FROM DUAL
SELECT 'HELLO ' || ' ORACLE' STR FROM DUAL

--字符串截取
--    使用 SUBSTR ，第一个参数是源字符串，第二个参数是开始索引，第三个参数是截取长度，开始的索引使用 1 和 0 效果相同
SELECT SUBSTR('HELLO',0,1) STR1,"SUBSTR"('HELLO',1,1) STR2 FROM DUAL

--获取字符串长度
--    使用 LENGTH，仅有一个源字符串参数
SELECT LENGTH('HELLO') LENGTH FROM DUAL

--字符串替换
--    使用 REPLACE ，第一个参数是源字符串，第二个参数是被替换的字符串，第三个是替换字符串
SELECT REPLACE('HELLO','L','X') STR FROM DUAL

1、字符函数

--四舍五入
SELECT ROUND(45.926,2) FROM DUAL

--小数位截断
SELECT TRUNC(45.926,2) FROM DUAL

--求余
SELECT MOD(9,4) FROM DUAL

2、数值函数

例：查询员工进入公司的周数
SELECT ENAME 名字,ROUND((SYSDATE-HIREDATE)/7) 周数 FROM EMP

例：获取两个时间段中的月数
SELECT ENAME 名字,ROUND("MONTHS_BETWEEN"(SYSDATE,HIREDATE)) 月数 FROM EMP

例：获取几个月后的日期
SELECT ADD_MONTHS(SYSDATE,3) as "3个月后的日期" FROM DUAL

3、日期函数

--TO_CHAR ：转换为字符
--    转换日期
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:mi:ss') 当前时间 FROM DUAL

--    转换数值
SELECT TO_CHAR(12) FROM DUAL

--TO_NUMBER ：转换为数字
SELECT TO_NUMBER('') FROM DUAL

--TO_DATE ：将字符转换为日期
SELECT TO_DATE('2019-1-2','yyyy-mm-dd') FROM DUAL

4、转换函数

--例：查询所有员工的年薪
SELECT ENAME 名字,SAL*12+COMM 年薪 FROM EMP

--    我们会发现很多员工的年薪是空的，原因是很多员工的奖金为 NULL ，null 和任何数值计算都是 null，这是我们可以使用 nvl 处理。
SELECT ENAME 名字,NVL(COMM,0) 奖金, SAL*12+NVL(COMM,0) 年薪 FROM EMP

5、空值处理

--例：根据 10 号部门的工资显示税率
--    使用 DECODE 函数
SELECT
    ENAME 名字,
    SAL 月薪,
    DECODE(
        TRUNC(SAL/2000,0),
        0,0.00,
        1,0.09,
        2,0.20,
        3,0.30,
        4,0.40,
        5,0.42,
        6,0.44,
            0.45
    ) TAX_RATE
FROM EMP
WHERE DEPTNO=10
--    使用 CASEWHEN
SELECT
    ENAME 名字,
    SAL 月薪,
    CASE
        TRUNC(SAL/2000,0)
    WHEN 0 THEN 0.00
    WHEN 1 THEN 0.09
    WHEN 2 THEN 0.20
    WHEN 3 THEN 0.30
    WHEN 4 THEN 0.40
    WHEN 5 THEN 0.42
    WHEN 6 THEN 0.44
    ELSE 0.45 END TAX_RATE
FROM EMP
WHERE DEPTNO=10

6、条件表达式

多行函数

SELECT COUNT(*) 记录数 FROM EMP

1、统计记录数

--例：查询最低月薪
SELECT MIN(SAL) 月薪 FROM EMP

2、最小值

--例：查询最高月薪
SELECT MAX(SAL) 月薪 FROM EMP

3、最大值

--例：查询员工平均月薪
SELECT AVG(SAL) 平均月薪 FROM EMP

4、平均值

--例：查询所有员工月薪总和
SELECT SUM(SAL) 月薪总和 FROM EMP

5、求和

--分组查询
--例：查询每个部门的人数
SELECT DEPTNO 部门编号,COUNT(*) 人数 FROM EMP GROUP BY DEPTNO

--过滤分组数据
--例：查询出部门平均工资大于 2000 的部门
SELECT DEPTNO 部门编号,AVG(SAL) 部门平均工资 FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2000

--WHERE 和 HAVING 的区别：
--    最大区别在于：WHERE 后面不能有组函数，如：WHERE AVG(SAL)>2000 是错误的

6、分组查询

补充

plsql中文乱码解决

1、查看服务端编码：

select userenv('language') from dual;

我实际查到的结果为： 'AMERICAN_AMERICA.ZHS16GBK'

2、执行下面语句查看客户端编码：

select * from V$NLS_PARAMETERS;

查看第一行中 PARAMETER 列中值为 NLS_LANGUAGE 的行对应的 VALUE 项是否与第一步得到的值相同。

如果不是，则需要设置环境变量，否则就会因为 plsql 客户端使用的编码和服务端编码不一致，导致插入中文乱码。

3、设置环境变量：

变量名：NLS_LANG
变量值：第一步查询到的值，我这里是 AMERICAN_AMERICA.ZHS16GBK

4、重启 plsql 客户端，插入数据正常。