数据库之函数

概念

  • 单行函数:针对sql语句影响的每一行都进行处理,并针对这一行返回一个结果sql影响多少行就返回多少个结果
  • 组函数 :对sql语句影响的所有行进行综合处理,最终返回一个结果无论sql语句影响多少行都只返回一个结果(组函数会忽略空值)

回顾:
1.数据库介绍
sql:
dql: select
dml: insert delete update
ddl: create drop alter
tcl: commit rollback savepoint
dcl: create user、grant、revoke

对字符串处理的函数

desc s_dept;

dual表说明

假设一张表有100w条数据,测试单行函数,会返回100w条数据,这样返回会占屏幕,很麻烦所以系统提供一个测试表,dual(单行单列的表)

select * from dual;

  图片 1

可以用它进行单行函数的测试(也可以自己再建一个单行单列的表)

2.from子句
select name from s_dept;
select id,name from s_dept;
select * from s_dept;
select id,12salary 1000 from s_emp;
select id,12
salary 1000 as "yearsal" from s_emp;
select id,first_name||''''||last_name name from s_emp;
select id,12salary(1 nvl(commission_pct,0)/100) yearsal from s_emp;
select distinct title,dept_id from s_emp;

upper(par1)  对字符串变大写

3.where子句
select id,first_name,salary from s_emp
where salary>1400;
select id,first_name from s_emp
where first_name='Ben';
select id,first_name,salary from s_emp
where salary between 1100 and 1550;
select id,first_name,dept_id from s_emp
where dept_id in(31,42,50);
select id,first_name from s_emp
where first_name like '_a%';
select table_name from user_tables
where table_name like 'S_%' escape '';
select id,first_name,manager_id from s_emp
where manager_id is null;
and or not
select id,first_name,salary from s_emp
where salary>=1100 and salary<=1550;
select id,first_name,dept_id from s_emp
where dept_id=31 or dept_id=42 or dept_id=50;
select id,first_name,commission_pct from s_emp
where commission_pct is not null;

演示:

 select first_name, upper(first_name) from s_emp where id=1;

影响了一行的情况(id为1的只有一个):

图片 2

 

select first_name, upper(first_name) from s_emp where id<1;

一行都没有影响的情况(id小于1的不存在):

图片 3

 

select first_name, upper(first_name) from s_emp where id>1;

影响多行的情况(id大于1的有24个):

图片 4

4.order by子句
select id,first_name,salary from s_emp
order by salary desc,id;

lower(par1)  对字符串变小写

select id,first_name,start_date from s_emp
order by start_date;

演示:测试lower函数:

select lower('HELLO') from dual;

图片 5

练习:
select id,first_name,salary,
12salary(1 nvl(commission_pct,0)/100) as yearsal
from s_emp
where 12salary(1 nvl(commission_pct,0)/100)>15000
order by yearsal desc;

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


演示:测试initcap函数

select initcap('one world one dream') from dual;

图片 6

1.单行函数
1.1 单行函数和组函数的概念
单行函数:针对sql语句影响的数据,每行都做处理,每行产生一个结果
select upper(first_name) from s_emp
where id<11;
组函数:针对sql语句影响的数据,每组做处理,每组产生一个结果
select count(first_name) from s_emp
where id<11;

 oncat函数(用的少,一般用||)

oncat(par1  varchar2,par2)//把类型写后面  连接字符串

1.2 dual表
desc dual;
1.3 字符串函数
upper(s): 把参数s中的英文字母转换成大写 返回
select upper('hello world!') from dual;
lower(s): 把参数s中的英文字母转换成小写 返回
select lower('hello world!') from dual;
initcap(s):把参数s中的每个单词转换成首字母大写、其余小写的形式
select initcap('hello world!') from dual;
concat(s1,s2): 字符串连接 ||

演示:测试concat函数:

select concat('hello', 'world') from dual;

图片 7

要是复杂拼接,用concat不方便。

 

select concat(concat('a','b'),concat('c','d')) from dual;

图片 8

需要函数嵌套(把被嵌套函数的返回值作为函数的参数使用)

 

select 'a' || 'b' || 'c' || 'd' con from dual;

图片 9

用|| 就更方便。

 substr(s,start[,length]):从start位置开始,截取字符串s中的length的字符
   start:开始位置  从1计数   如果写成0,按照1处理
            >0 表示从左侧开始计数
            <0 表示从右侧开始计数
   length: 截取的子字符串的长度
                缺省时,表示截取到字符串的最后
   select substr('hello world!',-6,4) from dual;
 length(s): 返回字符串的长度
   select length('hello world!') from dual;

 练习:使用两种方式 列出s_emp中first_name的后三位
  select first_name,substr(first_name,-3) from s_emp;

  select first_name,
        substr(first_name,length(first_name)-2) from s_emp;

substr函数

substr(par1,par2,par3)  截取字符串
  • par1:是要处理的数据
  • par2:  
    • 指定从什么位置进行处理。
    • 编号从1开始,可以是负数(从后向前处理)。
    • -1代表最后一个字符
  • par3:截取多长

如果非要从0编号,oracle会自动把0变为1。

select substr('hello',0,2) from dual;

图片 10

 

select substr('hello',1,2) from dual;

图片 11

 

select substr('hello',-3,2) from dual;

图片 12

 

1.4 数字函数
round(x[,y]) : 四舍五入
y: 缺省时, 0 round(4.56) = 5
>0 四舍五入到小数点后y位 round(4.56,1) = 4.6
<0 四舍五入到小数点前|y|位 round(456.78,-2) =500
trunc (x[,y]) :截取
y: 缺省时, 0 trunc(4.56) = 4
>0 截取到小数点后y位 trunc(4.56,1) = 4.5
<0 截取到小数点前|y|位 trunc(456.78,-2) =400
select trunc(456.67,-2) from dual;

演示:把s_emp表中的first_name和first_name的后三个字符显示出来

select first_name ,substr(first_name,-3,3) from s_emp;

图片 13

  floor(x) : 不大于x的最大整数
  ceil(x):不小于x的最小整数
     select floor(4.5) from dual;
     select ceil(4.5) from dual;

replace函数

replace(par1,par2,par3)  
  • par1  要处理的内容
  • par2  要被替代的内容
  • par3  要替换成的内容

    select replace('one world one dream','one','two') from dual;

图片 14

1.5 日期类型和日期函数
1.5.1 日期类型 date
默认格式:
英文: dd-MON-yy 12-OCT-17
中文: dd-n月-yy 12-10月-17
系统时间: sysdate
select sysdate from dual;

nvl函数

nvl(par1,par2)   为空时替换
  • par1  要处理的内容
  • par2  为空时要替换的内容
    date数据各部分的格式表示:
    cc                  世纪                        21

    yy                 2位数字的年           17
    yyyy             4位数字的年            2017
    year              年份的英文全拼      twenty seventeen

    mm               2位数字的月            10
    mon(MON)  月份单词的前三个字母    oct(OCT)
    month(MONTH)  月份单词的全拼    october(OCTOBER)

    dd                2位数字的天             12
    dy                星期的单词的前三个字母  thu
    day              星期单词的全拼                 thursday

    hh                12小时制的小时        02
    hh24            24小时制的小时        14

    mi                分钟                            34

    ss                  秒                               15

    2017-10-12     'yyyy-mm-dd'

 1.5.2 日期类型的算术运算  (在day上进行操作)
    1) 日期   数字
         select sysdate   80 from dual;
    2) 日期 - 数字
         select sysdate - 200 from dual;
    3) 日期1 - 日期2
         select sysdate - to_date('01-JAN-00') from dual;

 1.5.3 常用的日期函数
    add_months(d,n):在日期d上加n个月
       select add_months(sysdate,4) from dual;
    months_between(d1,d2):返回两个日期相差的月数
       select months_between(sysdate,'01-JAN-00') 
            from dual;
    next_day(d,dy):返回日期d的下一个dy(星期几)
       select next_day(sysdate,'FRIDAY') from dual;
       select next_day(sysdate,'FRI') from dual;
       select next_day(sysdate,6) from dual;
       使用1~7 对应 星期日~星期六
    last_day(d): 返回日期d所在月份的最后一天
       select last_day(sysdate) from dual;

处理数字的函数

1.6 转换函数
1.6.1 to_char
to_char(d|n[,fmt]): 把日期或数字按照给定的格式转换成字符串
1) 日期--> 字符串
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss am')
from dual;
select id,first_name,to_char(start_date,'yyyy-mm-dd')
from s_emp
order by start_date;

round函数

round(par1,par2)  四舍五入
  • par1  要处理的数据
  • par2  指定要如何处理(默认是取整)
    •    0是默认四舍五入取整
    •   正数n是保留小数点后n位
    •   负数n是直接对小数点前n位进行四舍五入取整(可能会丢失数据)
     2) 数字 ---> 字符串
        格式:
        9          小数点前代表0-9,小数点后代表1-9
        0          小数点前代表前导0,小数点后代表0-9
        .           小数点
        ,           分隔符
        $          美元符号
        L          本地货币符号
        格式字符串以 fm开头,比如:fm$099,999.00
        select to_char(1234,'fm$099,999.00') from dual;

1.6.2 to_number
      to_number(s[,fmt])
       select to_number('$001,234.00','fm$099,999.00')
                  from dual;
      -- 隐式转换
      select id,first_name,salary from s_emp
                  where id='1';

 1.6.3 to_date
      to_date(s[,fmt])  字符串  ----> 日期
      create table testdate_zsm_00(
         id number,
         start_time date
      );
      insert into testdate_zsm_00 values(1,'12-OCT-17');
      insert into testdate_zsm_00 
         values(2,to_date('2017-10-12 16:22','yyyy-mm-dd hh24:mi'));
      select id,to_char(start_time,'yyyy-mm-dd hh24:mi')
              from testdate_zsm_00;

 1.7 函数嵌套
   一个函数的返回值作为另一个函数的参数
   -- 练习: 列出每个员工的id,first_name和manager_id,
                  如果manager_id为null,显示成'BOSS'
   nvl : 参数可以是任意类型,但是两个参数的类型必须一致
   to_char

   select id,first_name,
        nvl(to_char(manager_id),'BOSS')  mid from s_emp;

演示:

四舍五入默认取整:

select round(9.58) from dual;

图片 15

 

保留小数点后两位四舍五入:

select round(9.486,2) from dual;

图片 16

 

对小数点前两位进行四舍五入取整:

select round(190.486,-2) from dual;

图片 17

2.表连接
2.1 需求:列出每个员工的id,first_name和所在部门的名称
1) 列出每个员工的id,first_name和所在部门的编号
select id,first_name,dept_id from s_emp;
2) 需要查询的数据来自于两张表
desc s_dept;
Name Null? Type

本文由美洲杯赌球发布于计算机教程,转载请注明出处:数据库之函数

TAG标签: 美洲杯赌球
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。