前言
在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念….写下本博文的原因就是记载着Oracle一些以前没注意到的知识点…以后或许会有用…
实例与数据库概念
Oracle数据库服务器由两部分组成:实例:理解为对象,看不见的
数据库:理解为类,看得见的
我们在安装Oracle的时候,已经填写过自己数据库的名称了,一般实例与数据库的名称是一致的…
这里写图片描述
如果还没有安装Oracle数据库的,可以看一下我其他的博文….
那么我们自带的sqlplus黑色窗口与实例,数据库之间的关系又是什么的呢???我们可以看下图:
这里写图片描述
Oracle数据库把表、视图等都看成是对象:
这里写图片描述
Oracle中的null值
Oracle中如果存在字段是null值的话,那么在sqlplus中它是不会显示出来的….如果我们使用null值的数据与其他数据进行运算…那么最终得出的结果都是null值
因此,Oracle提供了NVL(表达式1,表达式2)函数供我们使用,如果表达式1的值为null值,那么就取表达式2的值…当然了,如果表达式1不是null,取的就是表达式1的值
还有值得注意的是:null值不能参数=号运算,null能参数number/date/varchar2类型运算
Oracle提供了 is null关键字来代替=号运算的问题
Oracle中的别名
我们知道在Mysql中如果要用别名的话,需要使用as关键字 ,后面跟着别名就行了….Oracle可以省略as关键字…
并且,一般地,我们使用别名都是用双引号""把别名括起来,Oracle也支持我们直接写别名,但是呢,如果我们不写双引号,那么我们的别名是不能有空格的
还有一点的是:Oracle的别名是不能使用单引号来括起来的,Oracle默认认为单引号是字符串类型和日期类型的。
IO输入输出SQL语句
我们可以在sqlplus中使用spool命令把SQL语句保存在硬盘中,具体的例子:spool e:/oracle-day01.sql;
使用spool off命令,保存SQL语句到硬盘文件e:/oracle-day01.sql,并创建sql文件,结束语句spool off;
当然了,我们也可以把硬盘中的SQL文件在sqlplus中执行,只要以下的命令就行了:@ e:/crm.sql;
转义字符
有的时候,我们可能会模糊查询一些数据,但是呢,在名称中又有一些特殊的字符。那么我们就要经过转义….当然了,如果按照Java的来,就十分简单了,就写一个"\"就可以了。
那在Oracle中是怎么样转义的呢??我们来看下面的例子:
查询员工姓名中含有'_'的员工,使用\转义符,让其后的字符回归本来意思【like '%\_%' escape '\'】
select * from emp where ename like '%\_%' escape '\';
如果名称是'单引号呢???那么两个单引号代表着一个引号
插入一个姓名叫''的员工
insert into emp(empno,ename) values(2222,'''''');
单行函数与多行函数
首先,我们要明确一个概念:单行函数:输入一个参数,返回一个结果
多行函数:扫描多个参数,返回一个结果….一般地,多行函数和分组函数的概念是差不多的…
Oracle提供了关于字符串函数、日期函数供我们对数据进行对应的操作,这里就不一一赘述了,我们到时候有需要的时候查文档就行了。
这里写图片描述
单引号出现的地方如下:1)字符串,例如:'hello'
2)日期型,例如:'17-12月-80'
3)to_char/to_date(日期,'YYYY-MM-DD HH24:MI:SS')
双引号出现的地方如下:1)列别名,例如:select ename "姓 名" from emp
2)to_char/to_date(日期,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
GROUP BY 细节
group by 子句的细节:1)在select子句中出现的非多行函数的所有列,【必须】出现在group by子句中
2)在group by子句中出现的所有列,【可出现可不现】在select子句中
这里写图片描述
举例子:下面这段代码是错误的!!!
select max(avg(sal)) "部门平均工资的最大值",deptno "部门编号"
from emp
group by deptno;
为啥是错误的呢???分组中我们已经有了deptno字段了,而我们select 后面跟着也就是多行函数和该字段而已,为啥就错了呢?????我们如果在分组查询的时候,使用了多行函数嵌套的话,那么我们select字段后面只能跟随着它这么一个列,而不能再多了。max(avg(sal)) 相当于又分组了一次
当然了,如果我们仅仅是求出每个部门的平均工资,也就是下面这段代码,是完全没有问题的:
select avg(sal) "部门平均工资的最大值",deptno "部门编号"
from emp
group by deptno;
这里写图片描述
多表查询、子查询
当我们一张表不能把数据查询出来的时候,就需要连接其他的表一起查询….
当我们的查询条件还没知道的时候,我们就可以使用子查询….
一般地,子查询和多表查询的功能都是差不多的….
子查询出来的数据是单行单列的时候,一般我们都是用等于、大于等于、小于等操作符去限制查询条件…
如果是单列多行的时候,我们一般都是用IN、ANY、ALL操作符去筛选条件…
如果是多行多列,我们就看成该返回查询结果是一张表【Oracle分页就是这个原理】
值得注意的是多表查询的数学基础是笛卡尔积,也就是说:如果两张实体表进行连接,那么它会构成一张笛卡尔积表…也就是说:最终就只有一张笛卡尔积表
这里写图片描述
连接
在多表查询的时候,我们由于会产生笛卡尔积,于是在笛卡尔积表中会存在很多无关的数据…为了剔除这些数据,我们将用到where字句将笛卡尔积表筛选成有用的数据表
一般地,我们有几种连接:内连接等值连接【使用=号把条件筛选出来】
非等值连接【使用between and等手段把条件筛选】
外连接
自连接
这里写图片描述
这里写图片描述
那现在问题来了,在Oracle中有的功能我们可以使用多表查询来完成,有的时候我们又可以使用子查询来完成,那么我们一般选择哪一个呢????
我们看下图来比较一下他们的优劣:
这里写图片描述
对于索引就是一个以空间换时间的概念..在数据量很大的时候,Oracle会为我们的数据创建索引,当扫描数据的时候,就可以根据索引来直接获取值….索引的算法也有几种【二叉树、稀疏索引、位图索引….等等】
这里写图片描述
综上所述:在Oracle中使用多表查询性能可能比子查询好一些
Oracle分页
在讲解JDBC的时候,我们就已经讲过Oracle与Mysql的分页问题了….详情可以看我的博文:http://blog.csdn.net/hon_3y/article/details/53790092
我们在这里还是加深一下印象:
Oracle中的分页是依靠着rownum这个伪列来实现的,由于rownum只能使用的是<=或者来进行操作】….
那么Oracle分页的思路是这样子的:先在子查询中获取前n条记录
由于返回的是多行多列,因此我们可以看做成一张表
那么将查询出来的数据放在from字句的后边
外套的查询可以通过where字句来对子查询出来的数据进行过滤
那么我们就可以查询出想要的数据了…
公式:Mysql从(currentPage-1)*lineSize开始取数据,取lineSize条数据
Oracle先获取currentPagelineSize条数据,从(currentPage-1)lineSize开始取数据
小面试题
笔试题:有【1000亿】条会员记录,如何用最高效的方式将薪水字段清零,其它字段内容不变?
第一:从emp表中删除sal字段alter table emp
drop column sal;
第二:向emp表中添加sal字段,且内容默认0alter table emp
add sal number(6) default 0;
操作表细节
进入回收站
drop table users;
查询回收站中的对象
show recyclebin;
闪回,即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to 新表名;
彻底删除users表
drop table users purge;
清空回收站
purge recyclebin;
为emp表增加image列,alter table 表名 add 列名 类型(宽度)
alter table emp
add image blob;
修改ename列的长度为20个字节,alter table 表名 modify 列名 类型(宽度)
alter table emp
modify ename varchar2(20);
删除image列,alter table 表名 drop column 列名
alter table emp
drop column image;
重名列名ename为username,alter table 表名 rename column 原列名 to 新列名
alter table emp
rename column ename to username;
将emp表重命名emps,rename 原表名 to 新表名
rename emp to emps;
number(5):最多5位数字
number(6,2):其中2表示最多显示2位小数,采用四舍五入,不足位数补0,同时要设置col … for …
其中6表示小数+整数不多于6位
其中整数位数不得多于4位,可以等于4位
varchar2(8):8表示字节
值得注意的是:修改表的时候,是不能回滚的!
Oracle中的级联操作:【on delete cascade】级联删除
【on delete set null】将外键一方设置为null如果文章有错的地方欢迎指正,大家互相交流。习惯在微信看技术文章,想要获取更多的Java资源的同学,可以关注微信公众号:Java3y