随堂笔记,因为博主之前有过mysql的基础,所以我挑了一些些而已,其实Oracle和mysql有很多相似的地方,精一通所有

Oracle数据库

mysql有很多语法跟mysql相似,顾有些部分不做介绍

该笔记使用的数据表截图

image-20210803224526456

dual 虚表

​ 可以作为一个函数的桥梁,用来展示计算的结果。

列别名

字段名 as "别名"   -- 只能用双引号,不能用单引号
字段名 as  别名      -- 可以不用引号
如:select ename as "姓名" from tablename;
-- 可以省略as
字段名 别名
如:select ename "姓名" from tablename;

<font color='red'>除了别名这里字符串用双引号,大部分情况下字符串还是用单引号的</font>

连接操作符(||)

  • 用于连接列与列、列和字符
  • 形式上是以两个竖杠||
  • 用于创建字符表达式的结果列
-- 字符串连接字段
select '姓名:' ||  ename  from emp;

image-20210803212427096

-- 字段连接字段
select ename || hiredate  from emp;

image-20210803212700241

消除重复行

原查询

select deptno  from emp;

image-20210803214857311

上面的查询存在重复值,使用distinct

select distinct deptno from emp;

image-20210803214959888

防止字符串被转义的方法 escape

​ escape指定的字符后面紧挨着的第一个字符被看作是普通字符而非通配符,如果转义符后面的字符不是通配符,则将放弃转义符并将该转义符后面的字符作为该模式中的常规字符处理。

查询JOB以“MAN_”开头的雇员信息。

ect ename,job from emp where job like 'MAN@_%' escape '@'

单行函数

  • 单行函数语法

    • 语法:

      • 函数名[(参数1,参数2,…)]
    • 其中的参数可以是以下之一:

      • 变量
      • 列名
      • 表达式

字符函数

image-20210803224041848

字符串转小写(lower)

select lower(ename) from emp

image-20210803224439364

字符串转大写(upper)

-- emp表里面没有小写,所以这里用虚表 dual
select upper('asdfgh') from dual

image-20210803230539444

每个单词的首字母大写(initcap)

​ 函数INITCAP()是将每个单词的第一个字母大写,其它字母变为小写返回单词由空格,控制字符,标点符号等非字母符号限制.

select initcap('hEllo WORLD') from dual;

image-20210803231340876

连接两个值(concat)

等同于 ||

select concat('你好:','测试') from dual;

image-20210803232037705

截取字符串(substr)

语法

substr(column|expression,n1[,n2])

返回第一个参数中,从第n1位开始,长度为n2的子串。

  • 如果n2省略,取第n1位开始的所有字符。
  • 如果n1是负值,表示从第一个参数的后面第 abs(n1) 位开始向右取长度为 n2 的子串。
select substr('abcdefg',3) from dual;

image-20210803232432963

select substr('abcdefg',2,4) from dual;

image-20210803232917855

select substr('abcdefg',-4,2) from dual;

image-20210803233036836

字符串长度(length)

select length('qweasd') from dual;

image-20210803233143579

查找字符串子串位置(instr)

语法:

instr(s1,s2[,n1][,n2])

返回s1中,子串s2从n1开始,第n2次出现的位置。n1, n2默认值为1

select instr('123asd456','asd') from dual;

image-20210803233523126

字符串填充

左填充
lpad(s1,n1,s2)

返回s1被s2从左面填充到n1长度后的字符串

select lpad('asd',10,'*') from dual;

image-20210803234232640

右填充
rpad(s1,n1,s2)

返回s1被s2从右面填充到n1长度后的字符串。

select rpad('asd',10,'*') from dual;

image-20210803234653345

trim去除头尾字符串

​ SQL 中的 TRIM 函数是用来移除掉一个字串中的字头或字尾。最常见的用途是移除字首或字尾的空白

-- 全移除select trim(' asd ') from dual;-- 右移除select rtrim(' asd ') from dual;-- 左移除select ltrim(' asd ') from dual;

替换字符串(replace)

select replace('as123dfg123hjl','123','%%%') from dual;

image-20210804085140592

数值函数

截取小数位数

round (四舍五入)
round(23.926,2)res: 23.94
trunc(直接截取到指定位)
trunc(23.926,2)res: 23.92
moc(取模,取余)
mod(m,n)mod(16,3)res: 1

取整

floor

向下取整

floor(Number)

select floor(6.5) from dual

image-20210804193814005

ceil

向上取整

ceil(Number)

select ceil(6.5) from dual

image-20210804194049978

round

round(value,precision)

根据给定的精度(precision)输入数值。

select round(6.55,1) from dual

image-20210804194924080

时间处理函数

Oracle是以一种内部的数值形式存储日期的,即:世纪、年、月、日、小时、分、秒。

默认格式:DD-MON-RR

  • 日期类型可以加减数字,功能是在该日期上加减对应的天数。如:’10-AUG-06’+15结果是’25-AUG-06’
  • 日期类型之间可以进行减操作,功能是计算两个日期之间间隔了多少天。如:’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天
  • 如果需要对一个日期进行加减相应小时操作,可以使用n/24来实现

SYSDATE

返回系统当前日期

计算每个员工来这工作多少个周了

select ename, (sysdate-hiredate)/7 as week_count from emp;

image-20210804104253916

months_between

返回两个日期类型数据之间间隔的自然月数

查询员工入职到现在多了多少个月了

select ename,months_between(sysdate,hiredate) as "mon_count" from emp;

image-20210804130850870

add_months

返回指定日期加上相应的月数后的日期

select hiredate as "原始日期",add_months(hiredate,2) as "两个月以后" from emp;

image-20210804131913093

next_day

计算日期起下 个周X的日期

select sysdate as "now", next_day(sysdate,'星期一') as "星期一" from dual;

image-20210804134527617

last_day

计算该日期中的月份的最后一天的日期

select sysdate as "now",last_day(sysdate) as "月底日期" from dual;

image-20210804134722579

extract

单独提取时间的年,月,日

select extract(year from sysdate) from dual;

image-20210804140514193

select extract(month from sysdate) from dual;

image-20210804141048976

转换函数

to_char

用于日期型

语法:

to_char(date,['fmt'])

TO_DATE格式(以时间:2007-11-02 13:45:25为例)

Year:   yy two digits 两位年        显示值:07yyy three digits 三位年        显示值:007yyyy four digits 四位年        显示值:2007  Month:   mm  number   两位月       显示值:11mon  abbreviated 字符集表示     显示值:11月,若是英文版,显示nov   month spelled out 字符集表示     显示值:11月,若是英文版,显示november  Day:   dd  number     当月第几天    显示值:02ddd  number     当年第几天    显示值:02dy  abbreviated 当周第几天简写  显示值:星期五,若是英文版,显示friday  spelled out  当周第几天全写  显示值:星期五,若是英文版,显示friday    ddspth spelled out, ordinal twelfth       Hour:   hh  two digits 12小时进制      显示值:01   hh24 two digits 24小时进制      显示值:13      Minute:   mi  two digits 60进制        显示值:45      Second:   ss  two digits 60进制        显示值:25      其它   Q   digit     季度         显示值:4   WW  digit     当年第几周      显示值:44   W  digit     当月第几周      显示值:1
select to_char(sysdate,'yyyy/mm/dd hh24:mi;ss dy') from dual

image-20210804150301398

可以通过双引号添加字符串

select to_char(sysdate,'"今年是:"yyyy') from dual

image-20210804153233069

用于数值类型

语法:

to_char(number,'fmt')

image-20210804153750276

<font color='red'>进行数字类型到字符型转换时,格式中的宽度一定要超过实际列宽度,否则会显示为###</font>

select to_char(3000,'$99,999.99') from dual

image-20210804163044098

to_date

使用TO_DATE将一个字符串转换成日期型数据

select to_date('2018-8-29','yyyy-mm-dd') from dual

image-20210804170608676

通用函数

NVL

语法:

nvl(expr1,expr2)

如果expr1不是null,返回expr1,否则返回expr2

select nvl(null,'你好') from dual;

image-20210804174421046

select nvl('没得','你好') from dual

image-20210804174500403

可以用在计算列的值的时候,部分列存在NULL 时,使用此函数

如:nvl(NULL,0)

NVL2

语法:

NVL2(expr1,expr2,expr3)-- 如果expr1不是null,返回expr2,否则返回expr3
select nvl2(null,'不为空','为空') from dual

image-20210804191849246

select nvl2('123','不为空','为空') from dual

image-20210804191838012

NULLIF

语法:

nullif(expr1,expr2)

比较两个表达式,如果相等,返回null,否则,返回第一个表达式

COALESCE

语法:

COALESCE (表达式1,表达式2, ...表达式n)

​ 函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数,参数个数不受限制。

类似于switch的东西

CASE
select(case deptno       when 10 then '大佬'      when 20 then '小菜鸡'       else '男神'       end) as 部门,ename from emp

image-20210804192956459

decode
selectdecode( deptno,        10,'擎天柱',        20,'威震天',        30,'风火轮') as "部门" ,enamefrom emp

image-20210804193100126

分组函数

分组函数的使用

image-20210804195746069

除了COUNT(*)之外,其它所有分组函数都会忽略列中的空值,然后再进行计算。

NVL 函数可以使分组函数强制包含含有空值的记录

select avg(nvl(comm,0)) from  emp;

min 和 max

MIN和MAX函数主要是返回每组的最小值和最大值。

不限于数值类型

语法:

min([distinct|all] column|expression)max([distinct|all] column|expression)-- distinct 代表去重-- 默认是 all
select min(sal),max(sal) from emp;

image-20210804201401543

sum 和 avg

SUM和AVG函数分别返回每组的总和及平均值

SUM和AVG函数都是只能够对数值类型的列或表达式操作。

语法:

sum([distinct|all] column|expression)avg([distinct|all] column|expression)-- distinct 代表去重-- 默认是 all
select sum(sal),avg(sal) from emp

image-20210804202108811

count函数

COUNT函数的主要功能是返回满足条件的每组记录条数。

语法:

COUNT(*|{[DISTINCT|ALL] column|expression})-- distinct 代表去重-- 默认是 all

示例:

select count(*),count(empno),count(comm) from emp;

image-20210804203452167

区别

count(*) 包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL

count(1) 包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL

count(列名) 只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,<font color='red'>即某个字段值为NULL时,不统计</font>。

执行效率:

列名为主键,count(列名)会比count(1)快

列名不为主键,count(1)会比count(列名)快

如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)

如果有主键,则 count(主键)的执行效率是最优的

如果表只有一个字段,则 select count(*)最优。

最后修改:2021 年 09 月 16 日 05 : 53 PM