Oracle数据库初学者入门教程

Oracle数据库是相对于其他数据库来说比较难的一个。Oracle Database，又名Oracle RDBMS，简称Oracle。是甲骨文公司推出的一款关系数据库管理系统。Oracle数据库系统是目前世界上流行的关系数据库管理系统，拥有可移植性好、使用方便、功能强等优点，在各类大、中、小、微机环境中都适用。Oracle是一种高效率、可靠性好的、适应高吞吐量的数据库解决方案。下面我们来具体的学习一下：

首先我们来看什么是Oracle数据，他的组成都有哪些？

Oracle数据库服务器由一个数据库和至少一个数据库实例组成。数据库是一组存储数据的文件，而数据库实例则是管理数据库文件的内存结构。此外，数据库是由后台进程组成。数据库和实例是紧密相连的，所以我们一般说的Oracle数据库，通常指的就是实例和数据库。

Oracle 11G如何安装

https://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html这是一个Oracle11g的下载链接，大家可以根据自己的电脑系统下载不同的安装包。大家这里需要注意，会下载一个文件一和文件二。

如图，下载好后，选中2个压缩包，解压到一个文件夹就可以了，这点非常重要。选择setup.exe 安装就行。

数据库的创建

安装完成后我们在CMD命令窗口中执行sqlplus命令，来打开终端，此时他会提示我们输入用户名和密码，这个时候用户可以输入sys，密码则是你在安装的时候设置的密码。

接下来我们创建一个用户：

CREATE USER user1 IDENTIFIED BY 1234;

我们给user1用户来授权：

GRANT CONNECT,RESOURCE,DBA TO user1;

上面我们创建了一个用户，并且授予了登录和DBA的权限，下面我们用user1来进行登录看看：

CONNECT user1@orcl;

会提示你输入密码，登录成功。注意，user1用户仅存在于orcl数据库中，因此，必须在CONNECT命令中明确指定用户名为user1@orcl。

Oracle创建数据库有三种方式：

用oracle dbca来创建
手工创建数据库（这个是比较复杂的）
使用oracle managed Field来创建

带着问题去学习

常用的查询方面的有：

Select 演示如何查询单个表中的数据。

排序方面有：

Order By 按升序或降序对查询的结果集进行排序。

过滤方面有：

Distinct 介绍如何消除查询输出中的重复行。
Where 演示如何为查询返回结果集中的行记录指定过滤条件。
And 组合两个或两个以上的布尔表达式，如果所有表达式都为true，则返回true。
Or 组合两个或两个以上的布尔表达式，如果其中一个表达式为true，则返回true。
Fetch 演示如何使用行限制子句限制查询返回的行数。
in 演示如何使用行限制子句限制查询返回的行数。
Between 基于一系列值(区间值)过滤数据。
Like 根据特定模式执行匹配。

链接表方面有：

Inner join 演示如何从表中查询具有与其他表匹配的行记录。
Left join 介绍左连接概念，并学习如何使用它选择左表中具有，但右表中不具有的行记录。
Right join 解释右连接概念，并演示如何从右表查询具有，但左表中不具有的行记录。
Cross join –介绍如何从多个表中构建笛卡尔乘积。
Self join 演示如何将表连接到自身以查询分层数据或比较同一个表中的行记录。

分组方面有：

Group By 演示如何将行分组为子组，并为每个分组应用聚合函数。
Having 演示如何过滤分组中的行记录。

子查询方面有：

子查询 - 介绍子查询的概念以及如何使用子查询来执行高级数据选择技术。
相关子查询 - 了解相关的子查询，它是一个依赖于外部查询返回的值的子查询。
EXISTS和NOT EXISTS - 检查子查询返回的行是否存在。
ANY，SOME和ALL - 将值与列表或子查询进行比较。

设置操作符

UNION - 演示如何将两个查询的结果合并为一个结果。
INTERSECT - 演示如何实现两个独立查询的结果的交集。
MINUS - 学习如何从一个结果集中减去另一个结果(也就是求差集)。

修改数据

INSERT - 学习如何在表中插入一行。
INSERT INTO SELECT - 从查询结果中将数据插入到表中。
INSERT ALL - 讨论多重插入语句，将多行插入到一个或多个表中。
UPDATE - 演示如何更新表的存在的数据值。
DELETE - 演示如何从表中删除一行或多行。
MERGE - 使用单个语句逐步完成插入，更新和删除操作。

数据定义：

CREATE TABLE - 演示如何在数据库中创建新表。
IDENTITY列 - 了解如何使用IDENTITY子句来定义表的标识列。
ALTER TABLE - 演示如何改变表的结构。
ALTER TABLE ADD列 - 显示如何将一个或多个列添加到现有表
ALTER TABLE MODIFY列 - 演示如何更改表中现有列的定义。
DROP COLUMN - 了解如何使用各种语句从表中删除一列或多列。
DROP TABLE - 演示如何从数据库中删除表。
TRUNCATE TABLE - 更快，更有效地删除表中的所有数据。
RENAME TABLE - 学习如何重命名表和处理其依赖对象的过程。

数据类型：

Oracle数据类型 - 内置Oracle数据类型的概述。
NUMBER - 介绍数字数据类型，并展示如何使用它为表定义数字列。
FLOAT - 通过实例来解释Oracle中的浮点数据类型。
CHAR - 了解固定长度的字符串类型。
NCHAR - 演示如何存储固定长度的Unicode字符数据，并解释CHAR和NCHAR数据类型之间的区别
VARCHAR2 - 向您介绍可变长度字符，并向您展示如何在表中定义可变长度字符列。
NVARCHAR2 - 了解如何在数据库中存储可变长度的Unicode字符。
DATE - 讨论日期和时间数据类型，并说明如何有效地处理日期时间数据。
TIMESTAMP - 介绍如何以小数秒精度存储日期和时间。
INTERVAL - 介绍区间数据类型，主要用来存储时间段。
TIMESTAMP WITH TIME ZONE - 了解如何使用时区数据存储日期时间。

约束：

主键 - 解释主键概念，并演示如何使用主键约束来管理表的主键。
外键 - 解释介绍外键概念，并演示如何使用外键约束来强制表之间的关系。
NOT NULL约束 - 演示如何确保列不接受NULL值。
UNIQUE约束 - 讨论如何确保存储在一列或一组列中的数据在整个表内的行之间是唯一的。
CHECK约束 - 在将数据存储到表中之前添加用于检查数据的逻辑的过程。

创建表空间

创建临时表空间

CREATE TEMPORARY TABLESPACE ttf_temp 
TEMPFILE 'F:oracledata	tf_temp.dbf' 
SIZE 50m  
AUTOEXTEND on  
NEXT 50m MAXSIZE 40960m  
EXTENT MANAGEMENT LOCAL;

创建数据表空间

CREATE TEMPORARY TABLESPACE ttf_data LOGGING
DATAFILE 'F:oracledata	tf_data.dbf' 
SIZE 50m  
AUTOEXTEND on  
NEXT 50m MAXSIZE 40960m  
EXTENT MANAGEMENT LOCAL;

创建用户并指定表空间

CREATE USER USER1 IDENTIFIED BY 1234
DEFAULT TABLESPACE ttf_data
TEMPORARY TABLESPACE ttf_temp;

//给用户授予权限
GRANT CONNECT,RESOURCE,DBA to user1

创建表

创建表的一般语法格式如下：

CREATE TABLE <table_name> ( 
    <column_name_1> <data_type_1>, 
    <column_name_2> <data_type_2>, 
    <column_name_N> <data_type_N> 
); 


====================================
示例如下：

CREATE TABLE authors ( 
 id number(38), 
 name varchar2(100), 
 birth_date date, 
 gender varchar2(30) 
);

创建表并且指定主键等约束：

//创建一个学生表

CREATE TABLE STU(
  STUID NUMBER(10) PRIMARY KEY, //申明为主键
  STUNAME VARCHAR2(20) NOT NULL , //不为null
  STUSEX VARCHAR2(2) DEFAULT '男'   CHECK(STUSEX IN('男','女')) 
);

//创建一个课程表

CREATE TABLE COURSE(
   COURSEID NUMBER(10) PRIMARY KEY,
   COURSENAME VARCHAR2(20) NOT NULL,
   COURSETYPE VARCHAR2(4)
);

//创建一个学生和课程的关联表

CREATE TABLE STU_COURSE(
  ID NUMBER(10) PRIMARY KEY,
  STUID NUMBER(10) REFERENCES STU(STUID), //外键
  COURSEID NUMBER(10),
  CONSTRAINT FF_COURSEid FOREIGN KEY(COURSEID) REFERENCES COURSE(COURSEID)
  ON DELETE CASCADE //级联删除
)

添加数据--Insert

//插入的格式一般为

INSERT INTO table [(column1,column2,...)] VALUE (value1,value2,...)

INSERT INTO STU(id,name) VALUES(1,'张三');

//多表多行插入

INSERT [ALL] [condition_insert_clause]
[insert_into_clause values_clause] (subquery)

INSERT ALL
INTO stu(sid,sname) VALUES(ssid,ssname)
INTO tea(tid,tname) VALUES(ttid,ttname)
SELECT ssid,ssname,ttid,ttname,state FROM stu_tea WHERE state != 0


//有条件的INSERT

INSERT [ALL | FIRST]
WHEN condition THEN insert_into_clause values_clause
[WHEN condition THEN] [insert_into_clause values_clause]
......
[ELSE] [insert_into_clause values_clause] 
Subquery;

INSERT ALL
WHEN id > 5 THEN INTO stu(sid,sname) VALUES(ssid,ssname)
WHEN id < 5 THEN INTO tea(tid,tname) VALUES(ttid,ttname)
ELSE INTO tt(sid,tid) VALUES(ssid,ttid)
SELECT ssid,ssname,ttid,ttname FROM stu_tea;

//旋转Insert（pivoting Insert）
create table sales_source_data (
	employee_id number(6),
	week_id number(2),
	sales_mon number(8,2),
	sales_tue number(8,2),
	sales_wed number(8,2),
	sales_thur number(8,2),
	sales_fri number(8,2)
);
insert into sales_source_data values (176,6,2000,3000,4000,5000,6000);
 
create table sales_info (
	employee_id number(6),
	week number(2),
	sales number(8,2)
);

看上面的表结构,现在将要sales_source_data表中的数据转换到sales_info表中,这种情况就需要使用旋转Insert
 
示例如下:
	insert all
	into sales_info values(employee_id,week_id,sales_mon)
	into sales_info values(employee_id,week_id,sales_tue)
	into sales_info values(employee_id,week_id,sales_wed)
	into sales_info values(employee_id,week_id,sales_thur)
	into sales_info values(employee_id,week_id,sales_fri)
	select employee_id,week_id,sales_mon,sales_tue,
	sales_wed,sales_thur,sales_fri
	from sales_source_data;
 
从该例子可以看出,所谓旋转Insert是无条件 insert all 的一种特殊应用,但这种应用被oracle官方,赋予了一个pivoting insert的名称,即旋转insert

更新数据 -- Update

UPDATE 表名称 SET 列名称 = 新值 <WHERE 条件>

UPDATE stu SET sid = 1,sname = '张三' WHERE state = 0

删除数据 -- Delete

//语法如下

DELETE FROM <table/view> [WHERE <condition>]


//注意事项：
//如果有外键关联，则删除数据之前,需先删除外键关联数据

DELETE FROM stu WHERE sid = 1;

//DELETE 与 TRUNCATE 应用区别：

1、对于删除整个表的所有数据时，delete并不会释放表所占用的空间
2、如果用户确定是 删除 整表的所有数据，那么使用 truncate table 速度更快

//删除所有学生信息，使用DELETE
DELETE FROM stu

//删除所有部门信息使用 TRUNCATE
TRUNCATE TABLE stu

查询语句 -- Select

//语法如下

SELECT column_1,column_2 FROM table_name;


//查询单个列的数据
SELECT sid FROM stu

//查询多个列
SELECT sid,sname FROM stu

//查询所有列的数据
SELECT * FROM stu

//分组查询
SELECT id,name,age FROM stu GROUP BY age

//排序查询,按照id降序排序
SELECT id,name,age FROM stu ORDER BY id DESC

//唯一查询  语法格式如下
SELECT DISTINCT column_1,column_2 FROM table_name

//多个条件查询 AND
SELECT id,name,age,state FROM stu WHERE age = 1 AND state = 0

//多个条件查询 OR
SELECT id,name,age,state FROM stu WHERE age = 1 OR age = 2

链接查询

//内链接  inner join
SELECT * FROM stu INNER JOIN course ON cid = ccid ORDER BY ccid DESC

//使用USING 
//1.查询必须是等值连接。
//2.等值连接中的列必须具有相同的名称和数据类型。
SELECT * FROM stu INNER JOIN course USING(cid) ORDER BY cid DESC

----------------------------------------------------------------------------

//左链接
SELECT * FROM stu LEFT JOIN course ON cid == ccid ORDER BY cid DESC

//使用USING ,这里的c1和c2，在stu表和course表中都必须要相同类型的相同字段
SELECT * FROM stu LEFT JOIN course USING(c1,c2) ORDER BY cid

----------------------------------------------------------------------------

//右链接
SELECT * FROM stu RIGHT JOIN course ON cid = ccid ORDER BY cid DESC

//使用USING
SELECT * FROM stu RIGHT JOIN course USING(c1,c2) ORDER BY cid DESC

----------------------------------------------------------------------------

//笛卡尔积 CROSS JOIN
SELECT * FROM stu CROSS JOIN course

----------------------------------------------------------------------------

//自身链接
SELECT m.id ,c.name FROM stu AS m,LEFT JOIN stu AS c ON c.cid = m.pid

Oracle 常用分页

//1、通过MINUS分页
SELECT * FROM STU WHERE ROWNUM < 3 MINUS SELECT * FROM STU WHERE ROWNUM < 2


//2、通过ROWNUM分页
SELECT * FROM STU WHERE ROWNUM < 10

//查询前10条
SELECT * FROM (SELECT * FROM STU) WHERE ROWNUM <= 10

//3、通过BETWEEN分页 (查询1到10)
SELECT * FROM STU WHERE ROWNUM BETWEEN 1 AND 10

SELECT * FROM (SELECT a.*,ROWNUM RN FROM STU) WHERE RN <=10

Oracle 创建视图

视图的优点有如下：

对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
用户通过简单的查询可以从复杂查询中得到结果。
维护数据的独立性，试图可从多个表检索数据。
对于相同的数据可产生不同的视图。

视图分为简单视图和复杂视图：

简单视图只从单表里获取数据，复杂视图从多表；
简单视图不包含函数和数据组，复杂视图包含；
简单视图可以实现DML操作，复杂视图不可以。

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias[, alias]...)]

AS subquery
     [WITH CHECK OPTION [CONSTRAINT constraint]]
     [WITH READ ONLY]


//创建视图
CREATE OR REPLACE VIEW SV AS SELECT sid,sname FROM STU WITH READ ONLY

//查询视图
SELECT * FROM SV;

//查询视图定义
SELECT SV,text FROM SV;


语法解析：

OR REPLACE    ：若所创建的试图已经存在，则替换旧视图；
FORCE：不管基表是否存在ORACLE都会自动创建该视图(即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用)；
NOFORCE  ：如果基表不存在，无法创建视图，该项是默认选项(只有基表都存在ORACLE才会创建该视图)。
alias：为视图产生的列定义的别名；
subquery  ：一条完整的SELECT语句，可以在该语句中定义别名；
WITH CHECK OPTION  ：插入或修改的数据行必须满足视图定义的约束；
WITH READ ONLY       ：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

删除视图

DROP VIEW 视图名称;

Oracle 中EXISTS 和 NOT EXISTS

EXISTS （sql 返回结果集为真）
NOT EXISTS (sql 不返回结果集为真）

//如果有值就返回  EXISTS 
SELECT * FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)


//如果有值就返回  NOT EXISTS 
SELECT * FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE A.ID = B.ID)

EXISTS 和 NOT EXISTS 用的也是比较多的，效率相对来收也比较优。

IN、NOT IN的用法

// IN的基本语法如下

SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);

SELECT * FROM STU WHERE ID IN / NOT IN (SELECT ID FROM B WHERE state = 0)

//举个例子来说明 “exists” 和 “in” 的效率问题

SELECT * FROM B1 WHERE EXISTS(SELECT * FROM B2 WHERE B1.a = B2.a)
//B1数据量小而B2数据量非常大时， B1 << B2 时，查询效率高

SELECT * FROM B1 WHERE B1.a in (SELECT a FROM B2)
//B1的数据量非常大而B2数据量小时， B1 >> B2 时 查询效率高

自定义函数

//创建语法

create [or replace] function function_name
　　[(parameter_list)]
　　return datatype
　　{is/as}
　　[local_declarations]
　　begin
　　　　executable_statements;
　　[exception
　　　　exception_handlers;]
　　end;

　　说明：
　　function_name：函数名称。
　　parameter_list：函数列表，可选。
　　return 自居：指定函数的返回类型，不能指定大小。
　　local_declarations：局部变量声明，可选。
　　executable_statements：要执行的PL-SQL语句。
　　exception_handlers：异常处理，可选。
　　or repalce：是否覆盖，可选。
参数的模式有3种:(如果没有注明, 参数默认的类型为 in.)
    in: 为只读模式, 在函数中, 参数的值只能被引用, 不能被改变;
    out: 为只写模式, 只能被赋值, 不能被引用;
    in out:  可读可写.

//注意 
1.在Oracle自定义函数中, else if 的正确写法是 elsif 而不是 else if
2.使用 if 需要加 then  "if 条件 then 操作"

/////////////////////////////////////////////////////////////


例如，读入两个值，返回比较大的值
create or replace function get_max(para1 in number, para2 in number) 
return number 
as 
begin
  if para1 > para2 then
      return para1;
  else
      return para2; 
  end if;
end get_max;


//使用
select get_max(666, 333) from dual;


///////////////////////////////////////////////////////////////
CREATE or REPLACE FUNCTION useEasy(a1 in number,a2 in number) RETURN NUMBER IS
  fres NUMBER;
  BEGIN
    fres := a1 + a2;
    RETURN fres;
 END useEasy;

//调用
select useEasy(1,30) from dual;



///////////////////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION get_emp_id(usernameq varchar2) RETURN NUMBER
AS
 sid emp.id%TYPE;
 BEGIN
    SELECT id INTO sid FROM emp WHERE name = usernameq;
    RETURN sid;
END get_emp_id;


//调用
select get_emp_id('张三') from dual;


//注意 
在Oracle的存储过程和函数中，其实IS和AS是同义词，没有什么区别。
还有在自定义类型（TPYE）和包（PACKAGE）时，使用IS和AS也并没有什么区别。
但是在创建视图（VIEW）时，只能使用AS而不能使用IS。
在声明游标（CURSOR）时，只能使用IS而不能使用AS。

文档未完，持续更新中

以上部分内容来自网络，有问题可以在下面评论，技术问题可以私聊我。