Oracle入门教程

一、Oracle Database基本介绍

Oracle 数据库是甲骨文公司的一款关系数据库管理系统。到目前仍在数据库市场上占有主要份额。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。

是现在的大型数据库。

 

二、下载

Oracle下载地址：

注意，12c只支持64位，plsql得安装客户端才能使用，所以建议大家下载11g

 

最新版12c

Oracle Database 12c Release 1 (12.1.0.1.0) for Microsoft Windows (x64)

http://download.oracle.com/otn/nt/oracle12c/121010/winx64_12c_database_1of2.zip
http://download.oracle.com/otn/nt/oracle12c/121010/winx64_12c_database_2of2.zip

 

11g

适用于 Microsoft Windows（32 位）的 Oracle Database 11g 第 2 版 (11.2.0.1.0)

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_1of2.zip

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_database_2of2.zip

适用于 Microsoft Windows（32 位）的 Oracle Database 11g 第 2 版 Client (11.2.0.1.0)

http://download.oracle.com/otn/nt/oracle11g/112010/win32_11gR2_client.zip

 

三、安装过程详解（11g，win7为例）

首先将下载下来的两个压缩包解压到同一目录下

文件目录如图所示：

 

右键setup.exe，以管理员权限运行

 

出现这个界面，等待几分钟

 

开始安装：

 

 

这里都可以空着不填

 选择是

 

创建和配置数据库

 

选择桌面类（按需求）

 

 

这里选择安装路径，输入数据库名以及密码

都可以随意填。

 如果出现这样的错误，第一条是说口令也就是密码不符合规范，这个错误可以忽略，就是说你的密码太简单，可能容易被盗。后者说SID已在使用，说明你之前安装过了数据库并且也叫做这个名字，建议先完全卸载干净之后再重装数据库。

 

如果没有报无法继续安装的错，就可以继续了。

 

 

接下来出现概要，无需理会点击完成

就会开始安装了，等待进度条完成

 

进度条完成之后，开始配置

 

 

接下来点击 口令管理

 

 

 去掉Scott前的勾，在后面输入scott用户的密码，scott是数据库给我们提供的一个示例账户，可以在此进行解锁。

同样的，密码不符合规范可以忽略掉，接着就点击完成 安装完成了。

 

现在在程序列表里有一个sql plus，可以打开他进行测试

 

 密码输入是不会有显示的，但是实际上输进去了。

 
 这就连接成功了。

 

四、基本sql语句

 

 /*****************基本操作***************************/

（切换）用户连接

conn system/system;

 

查看当前用户

show user;

 

创建新用户，帐号为xiaoming，密码为xiaoming（语句后者）

create user xiaoming identified by xiaoming;

 

对用户授权连接数据库（管理员账户登录）

grant connect to xiaoming;

 

对用户授权建表查询（管理员账户登录）

grant resource to xiaoming;

 

切换到xiaoming帐号(conn 帐号/密码)

conn xiaoming/xiaoming;

 

新建一个表student

create table student(stuname varchar2(20),stunum number(8));

 

像student表中插入数据

insert into student values('xiaoming',2012);

 

insert into student(stuname)values('xiaowang');

 

修改student名字为xiaoming的人号码为2011,where是条件语句，后面接条件，前面是操作

update student set stunum = 2011 where stuname = 'xiaoming';

 

将所有的stuname都改为zhangsan

update student set stuname= 'zhangsan';

 

授予scott查看（插入/删除/更新/所有）这张表的权限

grant select(insert/delete/update/all) on student to scott;

 

收回scott的权限

revoke all on student from scott;

 

删除指定数据

delete student where stunum = 2012;

 

查询student表的所有数据，*代表所有

select * from student;

 

查询student表的所有号码

select stunum from student;

 

scott用户得到了查询student的权限，这样查询

select * from xiaoming.student;

 

删除表,包括结构和数据

drop table student;

 

删除用户

drop user xiaoming;

 

查询表中stunum为空null的数据

select * from student where stunum is null;

 

查询表中stunum不为空null的数据

select * from student where stunum is not null;

 

删除表中所有数据，可以找回

delete from student;

 

删除表中所有数据，无法找回

truncate table student;

 

/*****************单表查询***************************/

查询表结构

desc student;

 

查询当前用户所有的表

select * from tab;

 

查看表student中的数据

select * from student;

 

查看scott用户的emp表的部分数据

select empno,ename from emp;

 

去重复

select distinct ename,job from emp;

 

查询SMITH的信息

select * from emp where ename = 'SMITH';

 

查询部门编号为10和20的所有员工

select * from emp where deptno = 10 or deptno = 20;

 

查询部门标号不为30的其余员工

select * from emp where deptno != 30;

 

查询薪水在2000-2500之间的员工

select * from emp where sal>2000 and sal<2500;

 

查询名字中包括B K 的(模糊查询)  %表示可以是任意内容

select * from emp where ename like '%B%K%';

 

查询名字中第三个字母为S的人，_表示任意一个内容

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

 

查询号码中包含7369和1234的用户。

select * from rmp where empno in (7369,1234);

 

查询没有上司的人。XXX is null

select * from emp where mgr is null;

 

查询的时候使用别名显示菜单，其中as 可以省略

select empno as 编号,ename as 姓名,sal 薪水from emp where ename like '%K%';

 

查询SMITH的年薪

select empno,ename,sal*12 from emp where ename = 'SMITH';

 

查询以J开头并且    薪水大于500或者为MANEGER的用户

select empno,ename,sal from emp where ename like 'J%' and (sal>500 or JOB = 'MANAGER');

 

将所有数据按照薪水的高低排序,如果遇到了相同的，就看主键的大小来排序

从小到大

select * from emp order by sal;

select * from emp order by sal asc;

 

从大到小

select * from emp order by sal desc;

 

查询出月薪最高的和月薪最低的员工的编号，姓名，月薪

select empno,ename,sal from emp where sal = (select max(sal) from emp) or sal = (select min(sal) from emp) ;

 

计算所有员工的平均月薪

select sum(sal)/count(empno) from emp;

select avg(sal) from emp;

 

显示每个部门的平均工资和最高工资

select deptno 部门,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno order by deptno;

 

显示每个部门的每个岗位的平均工资和最低工资

select deptno 部门,job 岗位,avg(sal) 平均工资,max(sal) 最高工资,count(sal) 人数 from emp group by deptno,job order by deptno;

 

显示部门平均工资低于2000的部门的平均工资(分组之后再写条件用having，且不可使用别名)

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

 

where 写在group by 前面,oeder by 写在最后

select deptno 部门,avg(sal) 平均工资 from emp where deptno!=30  group by deptno having avg(sal)>2000 order by deptno;

 

/*****************多表查询***************************/

显示员工的名字和所在部门的名字,给表加一个别名（笛卡尔集）

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

 

只显示部门为10的

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.deptno = 10;

 

显示每个员工的工资以及对应的级别

select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

 

显示员工名字，工资，所在部门名字，并且按部门号码排序

select e.ename,d.dname,e.sal from emp e,dept d where e.deptno = d.deptno order by e.deptno;

 

查询SMITH的上司

select mgr,ename from emp where empno = (select mgr from emp where ename = 'SMITH');

自链接查询：将一张表做为两张表查询

select e1.mgr,e2.ename from emp e1, emp e2 where e1.mgr = e2.empno and e1.ename = 'SMITH';

 

查询部门10工作相同的员工信息

select e1.* from emp e1,emp e2 where e1.job = e2.job and e2.deptno = 10;

多个结果用in

select * from emp where job in (select job from emp where deptno = 10);

 

显示工资比部门30最高的人高的员工信息

select * from emp where sal>(select max(sal) from emp where deptno = 30);

select * from emp where sal>all(select sal from emp where deptno = 30);

 

伪列 ROWNUM，ROWID

select ROWNUM,ename from (select ROWNUM r,ename from emp where ROWNUM<=10) where r>5;

select * from (select ROWNUM r,ename from emp where ROWNUM<=10) where r>5;

 

对于多个表的结果合并，不保留重复

select * from emp where job = 'SALESMAN' union select * from emp where deptno  = 10;

 

对于多个表的结果合并,保留重复

select * from emp where job = 'SALESMAN' union all select * from emp where deptno  = 10;

 

取交集

select * from emp where job = 'SALESMAN' intersect select * from emp where sal>1500;

 

得到在第一个结果集中存在但是第二个中不存在的数据

select * from emp where job = 'SALESMAN' minus select * from emp where sal>1500;

 

 

 

 

 

/*****************其余操作***************************/

建立引用

alter table ARTICLES add constraint USERID foreign key (USERID) references BLOGUSERS (USERID);

 

插入时间date

insert into articles values(123,'name','1-1月 1981','text',1234);

insert into articles values(124,'name',TO_DATE('2014-9-22','yyyy-mm-dd'),'text',1234);

 

/*****************备份还原***************************/

可以设置保存点，防止错误的操作

savepoint a1;

rollback to a1;

rollback; 还原到最初始的数据

 

提交操作：

撤销所有还原点，此时就变成了最初始数据，将无法还原到以前

退出的时候自动提交

commit;

 

/*****************实例***************************/

同义词

现有数据库对象的别名

 

定义私有同义词

create synonym e for emp;

 

定义公有同义词

create public synonym e for emp;

 

生成或者替换

create or replace syninym e for emp;

 

删除同义词

drop synonym e;

 

 

序列

用于生成唯一、连续的序号

 

授权给xiaoming，让他可以创建序列

grant create sequence ,select any sequence to xiaoming;

 

创建序列,从1开始，每次增加1，最大到20，最小是1。

create sequence createnum

start with 1

increment by 1

maxvalue 20

minvalue 1

NOCYCLE

CACHE ;

 

查询userlist的当前序列到哪个数字了

select createnum.currval from userlist;

 

利用序列插入数据

insert into userlist(usernum,username)values(createnum.nextval,"xiaoming");

 

查询当前用户的序列

select * from user_sequences;

 

更改序列,不能更改 start with 参数

alter sequence createnum maxvalue 200 cycle;

 

删除序列

drop sequence createnum;

 

视图

基于一个或多个表的虚拟表

 

创建视图

create or replace view v_emp as select empno,ename,job from emp;

 

按照视图查找

select * from v_emp;

 

删除视图

drop view v_emp;

 

索引

索引用于提高 SQL 语句执行的性能。

 

便于快速查找，主键默认就是索引。每个表可以指定一个主键，是不能重复的。

 

创建索引

create index useename_index on userlist(username);

 

创建反向索引

create index usernum_index on userlist(usernum) reverse;

 

 五、Java连接数据库

 

public class OracleConnecter {
		Connection conn;
		public static void main(String[] args) {
				OracleConnecter oc = new OracleConnecter();
				oc.test();
		}
		public void test(){
				conn = this.connectOracle();
				updateData(""update stu set nik = hello where usernum =  1234");
				readAllData("userlist1");
		}

		public boolean updateData(String... command) {
				try {
					// 取得数据库sql语句的编译和执行对象
					Statement stm = conn.createStatement();
					// 定义要执行的sql语句
					// 顺序执行command数组
					conn.setAutoCommit(false);
					// 全部执行完毕才生效，否则撤销本次操作，并返回false，告诉服务器没有更新成功
					for (int i = 0; i < command.length; i++) {
						stm.executeUpdate(command[i]);
					}
					conn.commit();
					System.out.println("ConnectOracle结束修改");
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}

		public Connection connectOracle() {
				try {	Class.forName("oracle.jdbc.driver.OracleDriver");
					Connection conn = 		DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott","tiger");
					return conn;
				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		
		private void readAllData(String descName) {
				try {
					// 取得数据库sql语句的编译和执行对象
					Statement stm = conn.createStatement();
					// 定义要执行的sql语句
					ResultSet rs = stm
							.executeQuery("select 		usernum,usernik,userpswd,username,friendlist1 from "
							+ descName + " order by usernum");
					// 再遍历rs
					while (rs.next()) {
						// 每次取得一行,用第一列进行标记
						int usernum = rs.getInt(1);
						String usernik = rs.getString(2);
						String userpswd = rs.getString(3);
						String username = rs.getString(4);
						String friendlist1 = rs.getString(5);
						System.out.println(usernum + "\t" + usernik + "\t" + userpswd + "\t" + username + "\t" + friendlist1);
					}
					System.out.println("ConnectOracle结束读取");
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
}