文章首页 Web开发 Windows开发 编程语言 数据库技术 移动平台 系统服务 微信 设计 布布扣 其他

oracle初学者入门

时间：2019-12-04 22:38:50   收藏：0   阅读：110

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创建数据库有三种方式：

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

带着问题去学习

常用的查询方面的有：

排序方面有：

过滤方面有：

链接表方面有：

分组方面有：

子查询方面有：

设置操作符

修改数据

数据定义：

数据类型：

约束：

创建表空间

创建临时表空间  

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

创建数据表空间

CREATE TEMPORARY TABLESPACE ttf_data LOGGING
DATAFILE ‘F:\oracledata\ttf_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 创建视图

视图的优点有如下：

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

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 
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。

原文：https://www.cnblogs.com/tanxiaojun/p/11986057.html

评论（0）
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣，分享程序人生！