coalesce

coalesce(参数列表):返回参数列表中第一个非空参数,最初一个参数通常为常量

distinct

去重

nvl

作用：判断某个值是否为空值，若不为空值则输入，若为空值，返回指定值。专

具体解释如下：

1、nvl()函数的格属式如下：NVL(expr1,expr2);
2、含意是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数原本的值。

3、例：select name,NVL(name,-1) from user；运行后，后果返回两列数值，若name为空，则返回-1，若name不为空值，则返回其本身。

round

round函数用于数据的四舍五入

1、round(x,d) ，x指要解决的数，d是指保留几位小数

这里有个值得注意的中央是，d能够是正数，这时是指定小数点右边的d位整数位为0,同时小数位均为0；

2、round(x) ,其实就是round(x,0),也就是默认d为0；

union与union all

union:去反复,排序

union all:不反复也不排序.(举荐)

intersect 与 minus

intersect 就是交加
minus 就是差集

交加就是两个后果集中都有的元素
比方 select uid from tb1
intersect
select uid from tb2
那么既存在zhitb1 又存在tb2中 雷同的UID 就会查dao进去
差集：select uid from tb1
minus
select uid from tb2
存在于tb1 但不存在与tb2中的uid 会被查出

表的复制

如果须要对表中的数据进行删除和批改,倡议通过复制表中的数据来对数据进行操作

create table 表名 as 查问语句;

  --将emp表中的数据复制到t_emp表中  create table t_emp  as  select * from emp;  --只须要表的构造  --将emp表的构造复制到t_emp表中  create table t_emp  as  select * from emp  where 1=0;/*提供一个否定条件*/  --只复制一部分数据  --将emp表中部门10的员工的数据复制到t_emp表中  create table t_emp  as  select * from emp  where deptno=10;  --将emp表中的员工姓名,工资,年薪保留到t_emp表中  create table t_emp  as  select ename,sal,sal*12 year_sal /*如果字段中呈现函数或者计算须要提供别名*/  from emp;  --统计emp表中部门的人数,将部门编码和人数保留到t_emp表中  create table t_emp(did,ecount)  as  select deptno,count(ename)  from emp  group by deptno;   留神:表的复制只会复制表中的数据,不会复制表中的束缚

伪列rowid,rownum

select rowid from dual;

rowid:是一个伪列,Oracle独有的.每一条记录的rowid 的记录是惟一的

sign比拟大小

与0进行比拟，判断是不是负数，大于0显示1 ，小于0显示-1 ，等于0显示0
select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;

如何进行SQL语句的优化

在select语句中防止应用*

缩小数据库的拜访次数

删除重复记录

尽量多应用commit

应用where替换having

多应用外部函数进步sql语句效率

多应用表的别名

应用exists替换in,应用not exists替换notin

尽量应用索引类进行查问

sql语句尽量大写.oracle会主动转换成大写

防止在索引列上进行计算

防止在索引类上应用not,oracle遇到not就应用全表扫描

能够应用>=替换>

应用in替换or

尽量应用where替换groupby

防止应用耗费资源的操作.如union

子查问注意事项

1.子查问须要定义在括号当中

2.子查问通常定义在条件判断的左边

3.在子查问中不倡议应用 order by

子查问中多行比拟符

in :等于列表中的任何一个

any:和子查问后果中的任意一个值进行比拟

all:和子查问后果中的所有值进行比拟

oracle 与 mysql的区别

(1) 对事务的提交
MySQL默认是主动提交，而Oracle默认不主动提交，须要用户手动提交，须要在写commit;指令或者点击commit按钮
(2) 分页查问
MySQL是间接在SQL语句中写"select... from ...where...limit x, y",有limit就能够实现分页;而Oracle则是须要用到伪列ROWNUM和嵌套查问
(3) 事务隔离级别

MySQL是read commited的隔离级别，而Oracle是repeatable read的隔离级别，同时二者都反对serializable串行化事务隔离级别，能够实现最高级别的

读一致性。每个session提交后其余session能力看到提交的更改。Oracle通过在undo表空间中结构多版本数据块来实现读一致性，每个session
查问时，如果对应的数据块发生变化，Oracle会在undo表空间中为这个session结构它查问时的旧的数据块
MySQL没有相似Oracle的结构多版本数据块的机制，只反对read commited的隔离级别。一个session读取数据时，其余session不能更改数据，但
能够在表最初插入数据。session更新数据时，要加上排它锁，其余session无法访问数据
(4) 对事务的反对
MySQL在innodb存储引擎的行级锁的状况下才可反对事务，而Oracle则齐全反对事务
(5) 保留数据的持久性
MySQL是在数据库更新或者重启，则会失落数据，Oracle把提交的sql操作线写入了在线联机日志文件中，放弃到了磁盘上，能够随时复原
(6) 并发性
MySQL以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁工夫过长，会让其余session无奈更新此表中的数据。
尽管InnoDB引擎的表能够用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有应用索引，那么依然应用表级锁。
Oracle应用行级锁，对资源锁定的粒度要小很多，只是锁定sql须要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以Oracle对并
发性的反对要好很多。
(7) 逻辑备份
MySQL逻辑备份时要锁定数据，能力保障备份的数据是统一的，影响业务失常的dml应用,Oracle逻辑备份时不锁定数据，且备份的数据是统一
(8) 复制
MySQL:复制服务器配置简略，但主库出问题时，丛库有可能失落肯定的数据。且须要手工切换丛库到主库。
Oracle:既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库呈现问题是，能够主动切换备库到主库，但配置管理较简单。
(9) 性能诊断
MySQL的诊断调优办法较少，次要有慢查问日志。
Oracle有各种成熟的性能诊断调优工具，能实现很多主动剖析、诊断性能。比方awr、addm、sqltrace、tkproof等
(10)权限与平安
MySQL的用户与主机无关，感觉没有什么意义，另外更容易被仿冒主机及ip有可乘之机。
Oracle的权限与平安概念比拟传统，中规中矩。
(11)分区表和分区索引
MySQL的分区表还不太成熟稳固。
Oracle的分区表和分区索引性能很成熟，能够进步用户拜访db的体验。
(12)管理工具
MySQL管理工具较少，在linux下的管理工具的装置有时要装置额定的包（phpmyadmin， etc)，有肯定复杂性。
Oracle有多种成熟的命令行、图形界面、web管理工具，还有很多第三方的管理工具，治理极其不便高效。
(13)最重要的区别
MySQL是轻量型数据库，并且收费，没有服务复原数据。
Oracle是分量型数据库，免费，Oracle公司对Oracle数据库有任何服务。

IT小黑

分组排序函数的用法

  • row_number() over()
先分组后排序

row_number() over(partition by col1 order by col2)示意依据col1分组，在分组外部依据col2排序，而此函数计算的值就示意每组外部排序后的程序编号(组内间断的惟一的)。

与rownum的区别在于：应用rownum进行排序的时候是先对后果集退出伪劣rownum而后再进行排序，而此函数在蕴含排序从句后是先排序再计算行号码。row_number()和rownum差不多，性能更强一点(能够在各个分组内从1开始排序)。

  • rank() over()
rank()是跳跃排序，有两个第二名时接下来就是第四名(同样是在各个分组内)
  • dense_rank() over()
dense_rank()也是间断排序，有两个第二名时依然跟着第三名。相比之下row_number是没有反复值的。

我用到的是第一个row_number() over()函数，要应用这个函数分组排序后获取最大值的oracle语句：

select a,b from(select a,b,row_number()over(partition by a order by b desc)rownumber from tablename ) where rownumber =1

  • 获取一组中最大值
SELECT * FROM (            SELECT last_comment,                    row_number() over(partition BY                     employeeid,roadline,stationname                     ORDER BY logindate DESC) rn                FROM reocrd) t         WHERE t.rn <=1

这段的意思是,将reocrd表依据员工工号( employeeid),线路(,roadline),站点名称(stationname)分组后,取登录日期(logindate) 最大的那一行的last_comment的值.

CONCAT(str1,str2,…)

返回后果为连贯参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

||

在oracle数据库中"||"起到连贯的作用

SELECT 11 || 22 FROM DUAL;

WM_CONCAT regexp_like replace 查问数据

创立表
--选课表create table testcourse(    id varchar2(20) primary key,    name varchar2(30),    course varchar2(30))--课程表CREATE TABLE testDetailedcourse (    id varchar2(20) primary key,    name varchar2(30));
插入数据
select * from testcourse
IDNAMECOURSE
1张三1,2,3
select * from testdetailedcourse
IDNAME
1语文
2英语
3数学
4物理
查问数据：“张三选课详情”

WM_CONCAT: 用于数据合并，示例中用于连贯课程名称name
regexp_like: 用于正则 regexp_like(原字段，正则)
replace: replace(原字段，“原字段旧内容“,“原字段新内容“)
  1. 首先将course中的,替换成|,配合管道||应用拼接成正则表达式，如上述表达式 : ^1|2|3|$ 判断id中蕴含1,2,3的数据
  2. 应用regexp_like匹配数据
  3. WM_CONCAT连贯