关于sql:oracle常用语法

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

select * from testdetailedcourse

查问数据：“张三选课详情”

WM_CONCAT: 用于数据合并，示例中用于连贯课程名称 name
regexp_like: 用于正则 regexp_like(原字段，正则)
replace: replace(原字段，“原字段旧内容“,“原字段新内容“)

1. 首先将 course 中的 , 替换成 |, 配合管道|| 应用拼接成正则表达式，如上述表达式 : ^1|2|3|$ 判断 id 中蕴含 1,2,3 的数据
2. 应用 regexp_like 匹配数据
3. WM_CONCAT 连贯