1、安装:将数据库安在虚拟机上,在本机上访问,以模拟远程访问服务器。
RAC:Real Application Cluster(RAC特指Oracle的集群)
从客户端远程连接虚拟器上的数据库: sqlplus scott/admin@192.168.56.101:1521/orcl
其中192.168.56.101是虚拟机的默认地址,1521是Oracle的端口号,orcl是Oracle自带的数据库。
如果scott账户锁定,则需要的操作:首先进入用管理员权限登录,解锁scott账户,设置scott密码。
<span style="font-family:Courier New;font-size:14px;"> sqlplus / as sysdba
alter user scott account unclock;
alter user scott identified by tiger;
</span>
如果在cmd界面上输入sqlplus scott/admin无效,那么需要安装instantclient(客户机),里面有sqlplus执行命令。
2、JDBC连接oracle(两种方式):
**提供驱动,URL为jdbc:oracle:thin:@localhost:1521:oracle 图形化工具SQL Developer
**jdbc:oracle:oci:@localhost:1521:oracle PL/SQL Developer就是用的这种连接方式(必须提供客户机)
3、RDBMS:关系型数据库管理系统,就是采用了关系模型来组织数据的数据库,何为关系模型,其实指的就是二维表格模型,而一
个RDBMS就是由二维表及其之间的联系组织成的一个数据组织。
对数据库和实例的理解:
数据库可以理解为物理概念,指的是硬盘上的文件,如orcl数据库我们可以在硬盘中找到这个文件夹,而操作这个文件夹下的 文件需要将这些文件读入到内存,把内存中的镜像就称之为实例(逻辑概念),其实实例就是数据库在内存中的镜像。
数据库集群的特点:
** Load Balance 负载均衡
** Fail Over 失败迁移RAC:Real Application Cluster(RAC特指Oracle的集群)
4、Oracle体系结构图:
5、Oracle常用命令
清屏:host cls; 当前用户:show user; 录屏:spool d:/a.txt 显示表结构:desc 表名;
显示行宽:show linesize; 设置行宽:set linesize 150;
设置列宽:col ename for a8(这一列是字符串格式的,ename列名,for即format,a代表字符串,8代表8个字符)
11、多表查询
层次查询:
![](https://img-blog.csdn.net/20150110161139000?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSmF2YV9Ob3Rl/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
层次结构图:
![](https://img-blog.csdn.net/20150110161316891?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSmF2YV9Ob3Rl/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center)
col sal for 9999(这一列是数字)
c(change)命令:SQL> c /form/from将form改成from; ed(edit)命令:作用同c命令
a(depend)命令,在语句后追加内容
6、其他基本操作:
**连接符|| ,作用同concat(str1,str2)函数,连接两个字符串
**like 模糊查询 select * from emp where ename like '%\_%' escape '\'
(escape关键字的作用,将特殊字符转义为本来的字符意义)
**order by 排序 默认升序,降序desc
order by 后可以 + 列名、表达式、别名、序号
<span style="font-family:Courier New;font-size:14px;"> select empno,ename,sal,sal*12 from emp order by sal*12 desc;
select empno,ename,sal,sal*12 年薪 from emp order by 年薪 desc;
select empno,ename,sal,sal*12 from emp order by 4 desc;
</span>
order by 作用于后面所有的列,desc只作用于离它最近的列。
<span style="font-family:Courier New;font-size:14px;"> select * from emp order by deptno ,sal desc---deptno升序,只有sal降序</span>
7、单行函数
select lower('Hello World') 转小写,upper('Hello World') 转大写, initcap('hello world') 首字母 大写from dual;
select substr('Hello World',3) 子串 from dual; --- substr(a,b)
从a中,第b位开始取 注意:索引从1开始算
select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;
select instr('Hello World','ll') 位置 from dual;---instr(a,b) 在a中,查找b
select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;---lpad 左填充 rpad右填充
select trim('H' from 'Hello WorldH') from dual;---trim 去掉前后指定的字符(这一点同java不同)
select replace('Hello World','l','*') from dual;---replace 替换
select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,round(45.926,-1) 四,round(45.926,-2) 五 fromdual; ---round 四舍五入 结果分别为:45.93、45.9、46、50、0
select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,trunc(45.926,-1)
四,trunc(45.926,-2) 五 from dual;---trunc 舍弃 结果分别为:45.92、45.9、45、40、0
8、通用函数
select sysdate from dual;---系统时间 显示格式为:11-11月-14
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--改变格式 2014-11-1114:27:05
可以+N或-N或者—日期,但是日期+日期是错误的。
select ename,hiredate,(sysdate-hiredate)/30 一, months_between(sysdate,hiredate) 二 from emp;--查询员工入职的月数,months_between函数比前者更准确。
select add_months(sysdate,78) "78个月后" from dual; --N个月之后
select last_day(sysdate) from dual; --当前月的最后一天
select next_day(sysdate,'星期一') from dual; --下一个日期
应用:如每个星期一备份数据:方式有分布式数据库、快照
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是" day') from dual;
select to_char(sal,'L9,999.99') 薪水 from emp; --查询员工薪水: 两位小数 千位符 本地货币代码
关于转换:把character转成number--to_number;把character转成date--to_date;把date或者number转成 character--to_char
nvl(a,b) 当a=null时候,返回b
nvl2(a,b,c) 当a=null时候,返回c;否则返回b select sal*12+nvl2(comm,comm,0) 年收入
nullif(a,b) 当a=b时候,返回null;否则返回a
coalesce 从左到右找到第一个不为null的值
select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;
9、条件表达式:(重要)--Oracle中的DECODE函数
使用两种方式:
** CASE表达式:SQL99的语法,较繁琐。 ** DECODE函数:Oracle自带的语法。
以涨工资为例,总裁涨1000 经理涨800 其他涨400
1、CASE表达式
<span style="font-family:Courier New;font-size:14px;"> select ename,job,sal 涨前,
case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后
from emp;</span>
2、DECODE函数
<span style="font-family:Courier New;font-size:14px;"> select ename,job,sal 涨前,
decode(job, 'PRESIDENT',sal+1000,
'MANAGER',sal+800,
sal+400) 涨后
from emp;</span>
10、多行函数(又称为分组函数,组函数)---定义:作用于一组数据,并对一组数据返回一个值。
sum()、count()、avg()、max()、min()
分组数据:group by
△注意:select a,b,c,sum(x) from table group by a,b,c; 所有包含于select列表中,而未包含于组函数 中的列必须包含于group by的子句中。但可以这样写select a,b,c,sum(x) from table group by a,b,c,d,e;
使用having过滤分组。(使用了组函数用having才有意义)
【where和having的区别:where后面不能使用组函数】
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;(√)
select deptno,avg(sal) from emp where avg(sal)>2000;(√) group by deptno;(×)--where后使用了组 函数
group by的增强
按照部门统计各部门不同工种的工资情况,要求按如下格式输出: | 从左图中可以看出,数据是以下三条语句的组合。 select deptno,job,sum(sal) from emp group by deptno,job; + select deptno,sum(sal) from emp group by deptno; + select sum(sal) from emp; 以上三句用group by增强来表示 = select deptno,job,sum(sal) from emp group by rollup(deptno,job); 抽象: group by rollup(a,b) = group by a,b + group by a + group by null break on deptno skip 2;不同部门之间隔两行 |
等值连接:select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;
不等值连接:select e.empno,e.empme,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
外连接:有主从之分,其中主表所有记录都会显示,无论是否满足关联关系,从表只显示满足条件的记录,主表不满足条件的, 以null补位。
外连接:有主从之分,其中主表所有记录都会显示,无论是否满足关联关系,从表只显示满足条件的记录,主表不满足条件的, 以null补位。
Oracle中的左外连接和右外连接跟MySQL稍有不同,以 where e.deptno=d.deptno 为例。
写法: 左外:where e.deptno=d.deptno(+);右外:where e.deptno(+)=d.deptno;
select d.deptno 部门号,d.dname 部门名称,count(e.empno) 人数 from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno,d.dname; | 部门号 部门名称 人数 ---------- -------------- ---------- 10 ACCOUNTING 3 40 OPERATIONS 0 20 RESEARCH 5 30 SALES 6 |
自连接:通过表的别名,把同一张表视为多张表。
自连接不适合操作数据量过大的表。于是有了层次查询
查询员工信息:员工姓名 老板姓名 select e.ename 员工姓名,m.ename 老板姓名 where e.mgr = m.empno; |
<span style="font-family:Courier New;font-size:14px;"> select level,empno,ename,mgr
from emp
connect by prior empno=mgr//上一层的员工是下一层的老板
start with mgr is null //从mgr为null的开始
order by 1;</span>
层次结构图: