AI 助理
备案控制台
开发者社区 数据库 文章 正文

《卸甲笔记》-单行函数对比之一

2016-06-21 1698
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 1验证UPPER()、LOWER()函数 Oracle SQL> select UPPER('SongYuejie'),LOWER('VASTDATA') from dual; UPPER('SONGYUEJIE') LOWER('VASTDATA' -----------------

1验证UPPER()、LOWER()函数
Oracle

SQL> select UPPER('SongYuejie'),LOWER('VASTDATA') from dual;

UPPER('SONGYUEJIE')  LOWER('VASTDATA'
-------------------- ----------------
SONGYUEJIE         vastdata

PPAS

scott=# select UPPER('SongYuejie'),LOWER('VASTDATA') from dual;
   upper    |  lower   
------------+----------
 SONGYUEJIE | vastdata
(1 row)

2查询出雇员姓名是SMITH的完整信息，但是由于失误没有考虑到数据的大小写问题，此时可以使用UPPER()函数将全部内容变为大写
Oracle

SQL> select * from emp where ename=UPPER('smith');

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

PPAS

scott=# select * from emp where ename=UPPER('smith');
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
(1 row)

3查询所有雇员的姓名，要求将每个雇员的姓名以首字母大写的形式出现
Oracle

SQL> select ename 原始姓名,INITCAP(ename) 姓名开头首字母大写 from emp; 

原始姓名         姓名开头首字母大写
-------------------- --------------------
SMITH             Smith
ALLEN             Allen
WARD             Ward
JONES             Jones
MARTIN             Martin
BLAKE             Blake
CLARK             Clark
SCOTT             Scott
KING             King
TURNER             Turner
ADAMS             Adams

原始姓名         姓名开头首字母大写
-------------------- --------------------
JAMES             James
FORD             Ford
MILLER             Miller

14 rows selected.

PPAS

scott=# select ename 原始姓名,INITCAP(ename) 姓名开头首字母大写 from emp; 
 原始姓名 | 姓名开头首字母大写 
----------+--------------------
 SMITH    | Smith
 ALLEN    | Allen
 WARD     | Ward
 JONES    | Jones
 MARTIN   | Martin
 BLAKE    | Blake
 CLARK    | Clark
 SCOTT    | Scott
 KING     | King
 TURNER   | Turner
 ADAMS    | Adams
 JAMES    | James
 FORD     | Ford
 MILLER   | Miller
(14 rows)

4查询出所有雇员的姓名，并且将雇员姓名中所有的字母“A”替换成“_”
Oracle

SQL> select ename,REPLACE(ename,'A','_') from emp;

ENAME             REPLACE(ENAME,'A','_
-------------------- --------------------
SMITH             SMITH
ALLEN             _LLEN
WARD             W_RD
JONES             JONES
MARTIN             M_RTIN
BLAKE             BL_KE
CLARK             CL_RK
SCOTT             SCOTT
KING             KING
TURNER             TURNER
ADAMS             _D_MS

ENAME             REPLACE(ENAME,'A','_
-------------------- --------------------
JAMES             J_MES
FORD             FORD
MILLER             MILLER

14 rows selected.

PPAS

scott=# select ename,REPLACE(ename,'A','_') from emp;
 ename  | replace 
--------+---------
 SMITH  | SMITH
 ALLEN  | _LLEN
 WARD   | W_RD
 JONES  | JONES
 MARTIN | M_RTIN
 BLAKE  | BL_KE
 CLARK  | CL_RK
 SCOTT  | SCOTT
 KING   | KING
 TURNER | TURNER
 ADAMS  | _D_MS
 JAMES  | J_MES
 FORD   | FORD
 MILLER | MILLER
(14 rows)

5查询书姓名长度是5的所有雇员的信息
Oracle

SQL>  select * from emp where length(ename)=5;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


8 rows selected.

PPAS

scott=# select * from emp where length(ename)=5;
 empno | ename |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+-------+----------+------+--------------------+---------+--------+--------
  7369 | SMITH | CLERK    | 7902 | 17-DEC-80 00:00:00 |  800.00 |        |     20
  7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7566 | JONES | MANAGER  | 7839 | 02-APR-81 00:00:00 | 2975.00 |        |     20
  7698 | BLAKE | MANAGER  | 7839 | 01-MAY-81 00:00:00 | 2850.00 |        |     30
  7782 | CLARK | MANAGER  | 7839 | 09-JUN-81 00:00:00 | 2450.00 |        |     10
  7788 | SCOTT | ANALYST  | 7566 | 19-APR-87 00:00:00 | 3000.00 |        |     20
  7876 | ADAMS | CLERK    | 7788 | 23-MAY-87 00:00:00 | 1100.00 |        |     20
  7900 | JAMES | CLERK    | 7698 | 03-DEC-81 00:00:00 |  950.00 |        |     30
(8 rows)

6查询姓名前3个字母是JAM的雇员信息
Oracle

SQL>  select * from emp where SUBSTR(ename,0,3)='JAM';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

PPAS

scott=# select * from emp where SUBSTR(ename,0,3)='JAM';
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7900 | JAMES | CLERK | 7698 | 03-DEC-81 00:00:00 | 950.00 |      |     30
(1 row)

7查询所有10部门雇员姓名，但是不显示每个雇员姓名的前三个字母
Oracle

SQL> select ename 原姓名,SUBSTR(ename,3) 截取之后的姓名 from emp where deptno=10;

原姓名
--------------------
截取之后的姓名
----------------------------------------------------------------
CLARK
ARK

KING
NG

MILLER
LLER

PPAS

scott=# select ename 原姓名,SUBSTR(ename,3) 截取之后的姓名 from emp where deptno=10;
 原姓名 | 截取之后的姓名 
--------+----------------
 CLARK  | ARK
 KING   | NG
 MILLER | LLER
(3 rows)

8要求显示每个雇员姓名及其姓名的后3个字母
Oracle

SQL> select ename,SUBSTR(ename,LENGTH(ename)-2) from emp;

ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
SMITH
ITH

ALLEN
LEN

WARD
ARD


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
JONES
NES

MARTIN
TIN

BLAKE
AKE


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
CLARK
ARK

SCOTT
OTT

KING
ING


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
TURNER
NER

ADAMS
AMS

JAMES
MES


ENAME
--------------------
SUBSTR(ENAME,LENGTH(ENAME)-2)
--------------------------------------------------------------------------------
FORD
ORD

MILLER
LER


14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,LENGTH(ename)-2) from emp;
 ename  | substr 
--------+--------
 SMITH  | ITH
 ALLEN  | LEN
 WARD   | ARD
 JONES  | NES
 MARTIN | TIN
 BLAKE  | AKE
 CLARK  | ARK
 SCOTT  | OTT
 KING   | ING
 TURNER | NER
 ADAMS  | AMS
 JAMES  | MES
 FORD   | ORD
 MILLER | LER
(14 rows)

9在SUBSTR()函数中设置负数截取（-3表示从倒数第三个开始截取）
Oracle

SQL> select ename,SUBSTR(ename,-3) from emp;

ENAME             SUBSTR(ENAME,-3)
-------------------- ------------------------
SMITH             ITH
ALLEN             LEN
WARD             ARD
JONES             NES
MARTIN             TIN
BLAKE             AKE
CLARK             ARK
SCOTT             OTT
KING             ING
TURNER             NER
ADAMS             AMS

ENAME             SUBSTR(ENAME,-3)
-------------------- ------------------------
JAMES             MES
FORD             ORD
MILLER             LER

14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,-3) from emp;
 ename  | substr 
--------+--------
 SMITH  | ITH
 ALLEN  | LEN
 WARD   | ARD
 JONES  | NES
 MARTIN | TIN
 BLAKE  | AKE
 CLARK  | ARK
 SCOTT  | OTT
 KING   | ING
 TURNER | NER
 ADAMS  | AMS
 JAMES  | MES
 FORD   | ORD
 MILLER | LER
(14 rows)

10下标从0开始（由于Oracle数据库设计的灵活性，所有其下标是从0或1开始都是一样的）
Oracle

SQL> select ename,SUBSTR(ename,0,3) from emp;

ENAME             SUBSTR(ENAME,0,3)
-------------------- ------------------------
SMITH             SMI
ALLEN             ALL
WARD             WAR
JONES             JON
MARTIN             MAR
BLAKE             BLA
CLARK             CLA
SCOTT             SCO
KING             KIN
TURNER             TUR
ADAMS             ADA

ENAME             SUBSTR(ENAME,0,3)
-------------------- ------------------------
JAMES             JAM
FORD             FOR
MILLER             MIL

14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,0,3) from emp;
 ename  | substr 
--------+--------
 SMITH  | SMI
 ALLEN  | ALL
 WARD   | WAR
 JONES  | JON
 MARTIN | MAR
 BLAKE  | BLA
 CLARK  | CLA
 SCOTT  | SCO
 KING   | KIN
 TURNER | TUR
 ADAMS  | ADA
 JAMES  | JAM
 FORD   | FOR
 MILLER | MIL
(14 rows)

11下标从1开始
Oracle

SQL> select ename,SUBSTR(ename,1,3) from emp;

ENAME             SUBSTR(ENAME,1,3)
-------------------- ------------------------
SMITH             SMI
ALLEN             ALL
WARD             WAR
JONES             JON
MARTIN             MAR
BLAKE             BLA
CLARK             CLA
SCOTT             SCO
KING             KIN
TURNER             TUR
ADAMS             ADA

ENAME             SUBSTR(ENAME,1,3)
-------------------- ------------------------
JAMES             JAM
FORD             FOR
MILLER             MIL

14 rows selected.

PPAS

scott=# select ename,SUBSTR(ename,1,3) from emp;
 ename  | substr 
--------+--------
 SMITH  | SMI
 ALLEN  | ALL
 WARD   | WAR
 JONES  | JON
 MARTIN | MAR
 BLAKE  | BLA
 CLARK  | CLA
 SCOTT  | SCO
 KING   | KIN
 TURNER | TUR
 ADAMS  | ADA
 JAMES  | JAM
 FORD   | FOR
 MILLER | MIL
(14 rows)

12返回指定字符的ASCII码
Oracle

SQL> select ASCII('L') from dual;

ASCII('L')
----------
    76

PPAS

scott=# select ASCII('L') from dual;
 ascii 
-------
    76
(1 row)

13验证CHR()函数，将ASCII码变回字符
Oracle

SQL> select CHR(100) from dual;

CH
--
d

PPAS

scott=# select CHR(100) from dual;
 chr 
-----
 d
(1 row)

14去掉字符串左边空格函数---LTRIM()
Oracle

SQL> select '     SongYuejie Vastdata     ' 原始字符串,LTRIM('     SongYuejie Vastdata     ') 去掉左空格 from dual ;

原始字符串
----------------------------------------------------------
去掉左空格
------------------------------------------------
     SongYuejie Vastdata
SongYuejie Vastdata

PPAS

scott=# select '     SongYuejie Vastdata     ' 原始字符串,LTRIM('     SongYuejie Vastdata     ') 去掉左空格 from dual ;
          原始字符串           |        去掉左空格        
-------------------------------+--------------------------
      SongYuejie Vastdata      | SongYuejie Vastdata     
(1 row)

15去掉字符串右边空格函数---RTRIM()
Oralce

SQL> select '     SongYuejie Vastdata     ' 原始字符串,RTRIM('     SongYuejie Vastdata     ') 去掉右空格 from dual ;

原始字符串
----------------------------------------------------------
去掉右空格
------------------------------------------------
     SongYuejie Vastdata
     SongYuejie Vastdata

PPAS

scott=# select '     SongYuejie Vastdata     ' 原始字符串,RTRIM('     SongYuejie Vastdata     ') 去掉右空格 from dual ;  
          原始字符串           |        去掉右空格        
-------------------------------+--------------------------
      SongYuejie Vastdata      |      SongYuejie Vastdata
(1 row)

16去掉左右两边空格函数—TRIM()
Oracle

SQL> select '     SongYuejie Vastdata     ' 原始字符串,TRIM('     SongYuejie Vastdata     ') 去掉右空格 from dual ; 

原始字符串
----------------------------------------------------------
去掉右空格
--------------------------------------
     SongYuejie Vastdata
SongYuejie Vastdata

PPAS

scott=# select '     SongYuejie Vastdata     ' 原始字符串,TRIM('     SongYuejie Vastdata     ') 去掉右空格 from dual ; 
          原始字符串           |     去掉右空格      
-------------------------------+---------------------
         SongYuejie Vastdata      | SongYuejie Vastdata
(1 row)

17字符串左、右填充函数—LPAD()、RPAD()
Oracle

SQL> select LPAD('Vastdata',10,'*') LPAD函数使用,RPAD('Vastdata',10,'*') RPAD函数使用,
  2  LPAD(RPAD('Vastdata',10,'*'),16,'*') 组合使用 from dual;

LPAD函数使用         RPAD函数使用      组合使用
-------------------- -------------------- --------------------------------
**Vastdata         Vastdata**       ******Vastdata**

PPAS

scott=#  select LPAD('Vastdata',10,'*') LPAD函数使用,RPAD('Vastdata',10,'*') RPAD函数使用, 
scott-# LPAD(RPAD('Vastdata',10,'*'),16,'*') 组合使用 from dual;
 lpad函数使用 | rpad函数使用 |     组合使用     
--------------+--------------+------------------
 **Vastdata   | Vastdata**   | ******Vastdata**
(1 row)

18字符串查找函数---INSTR()
Oracle

SQL> select INSTR('SongYuejie Vastdata','Vastdata')查找得到,        
  2  INSTR('SongYuejie Vastdata','SongYuejie')查找得到,
  3  INSTR('SongYuejie Vastdata','VASTDATA')查找不到
  4  from dual;

  查找得到   查找得到    查找不到
---------- ---------- ----------
    12        1           0

PPAS

scott=# select INSTR('SongYuejie Vastdata','Vastdata')查找得到,                 
scott-# INSTR('SongYuejie Vastdata','SongYuejie')查找得到,
scott-# INSTR('SongYuejie Vastdata','VASTDATA')查找不到
scott-# from dual;
 查找得到 | 查找得到 | 查找不到 
----------+----------+----------
       12 |        1 |        0
(1 row)

数值函数
19验证ROUND()函数的使用
Oracle

SQL> select ROUND(789.652) 不保留小数,ROUND(789.652,2) 保留两位小数,ROUND(789.652,-1) 处理整数进位 from dual;

不保留小数 保留两位小数 处理整数进位
---------- ------------ ------------
       790     789.65      790

PPAS

scott=# select ROUND(789.652) 不保留小数,ROUND(789.652,2) 保留两位小数,ROUND(789.652,-1) 处理整数进位 from dual;
 不保留小数 | 保留两位小数 | 处理整数进位 
------------+--------------+--------------
        790 |       789.65 |          790
(1 row)

20列出每个雇员的一些基本信息和日工资情况
Oracle

SQL> select empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金 from emp;

     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7369 SMITH        CLERK           17-DEC-80           800
     26.67

      7499 ALLEN        SALESMAN       20-FEB-81          1600
     53.33

      7521 WARD         SALESMAN       22-FEB-81          1250
     41.67


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7566 JONES        MANAGER        02-APR-81          2975
     99.17

      7654 MARTIN        SALESMAN       28-SEP-81          1250
     41.67

      7698 BLAKE        MANAGER        01-MAY-81          2850
    95


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7782 CLARK        MANAGER        09-JUN-81          2450
     81.67

      7788 SCOTT        ANALYST        19-APR-87          3000
       100

      7839 KING         PRESIDENT       17-NOV-81          5000
    166.67


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7844 TURNER        SALESMAN       08-SEP-81          1500
    50

      7876 ADAMS        CLERK           23-MAY-87          1100
     36.67

      7900 JAMES        CLERK           03-DEC-81           950
     31.67


     EMPNO ENAME        JOB           HIREDATE           SAL
---------- -------------------- ------------------ ------------ ----------
    日薪金
----------
      7902 FORD         ANALYST        03-DEC-81          3000
       100

      7934 MILLER        CLERK           23-JAN-82          1300
     43.33


14 rows selected.

PPAS

scott=# select empno,ename,job,hiredate,sal,ROUND(sal/30,2) 日薪金 from emp;
 empno | ename  |    job    |      hiredate      |   sal   | 日薪金 
-------+--------+-----------+--------------------+---------+--------
  7369 | SMITH  | CLERK     | 17-DEC-80 00:00:00 |  800.00 |  26.67
  7499 | ALLEN  | SALESMAN  | 20-FEB-81 00:00:00 | 1600.00 |  53.33
  7521 | WARD   | SALESMAN  | 22-FEB-81 00:00:00 | 1250.00 |  41.67
  7566 | JONES  | MANAGER   | 02-APR-81 00:00:00 | 2975.00 |  99.17
  7654 | MARTIN | SALESMAN  | 28-SEP-81 00:00:00 | 1250.00 |  41.67
  7698 | BLAKE  | MANAGER   | 01-MAY-81 00:00:00 | 2850.00 |  95.00
  7782 | CLARK  | MANAGER   | 09-JUN-81 00:00:00 | 2450.00 |  81.67
  7788 | SCOTT  | ANALYST   | 19-APR-87 00:00:00 | 3000.00 | 100.00
  7839 | KING   | PRESIDENT | 17-NOV-81 00:00:00 | 5000.00 | 166.67
  7844 | TURNER | SALESMAN  | 08-SEP-81 00:00:00 | 1500.00 |  50.00
  7876 | ADAMS  | CLERK     | 23-MAY-87 00:00:00 | 1100.00 |  36.67
  7900 | JAMES  | CLERK     | 03-DEC-81 00:00:00 |  950.00 |  31.67
  7902 | FORD   | ANALYST   | 03-DEC-81 00:00:00 | 3000.00 | 100.00
  7934 | MILLER | CLERK     | 23-JAN-82 00:00:00 | 1300.00 |  43.33
(14 rows)

本连载博客主要探讨Oracle与PPAS(PostgreSQL)数据库的差异，以帮助更多读者了解如何实现数据库迁移！

文章标签：
关系型数据库
Oracle
SQL
数据库
宋跃杰
目录
相关文章
炒茄子O
摄影测量学：课后练习总结2
摄影测量学：课后练习总结2
炒茄子O
235 0
摄影测量学：课后练习总结2
jk75rxa4uokyc
|
6月前
|
机器学习/深度学习
PTA-打印九九口诀表
该程序生成1到N的下三角九九乘法表。输入一个1到9的正整数N，输出1*1到N*N的乘法表达式，等号右侧数字左对齐且占4位。示例输入4，输出1*1=1至4*4=16的口诀表。代码通过输入n值，使用两层循环结构实现乘法规则的打印。
jk75rxa4uokyc
99 0
2019ab
|
6月前
|
关系型数据库 MySQL
Mysql基础第四天,Mysql数据类型
Mysql基础第四天,Mysql数据类型
2019ab
34 0
Mysql基础第四天,Mysql数据类型
孙玉洁-47170
|
6月前
|
SQL OLAP HIVE
HQL的中级题目---炸裂函数、窗口函数
HQL的中级题目---炸裂函数、窗口函数
孙玉洁-47170
143 2
Hong_zh0
|
6月前
|
SQL 前端开发 Java
《我好想摆烂》（1）之SQL基础语法
《我好想摆烂》（1）之SQL基础语法
Hong_zh0
38 0
三哥一起来编程
LeetCode-630 课程表Ⅲ
这里有 n 门不同的在线课程，按从 1 到 n 编号。给你一个数组 courses ，其中 courses[i] = [durationi, lastDayi] 表示第 i 门课将会 持续 上 durationi 天课，并且必须在不晚于 lastDayi 的时候完成。 你的学期从第 1 天开始。且不能同时修读两门及两门以上的课程。 返回你最多可以修读的课程数目。
三哥一起来编程
74 0
小余007
|
存储 编译器 C语言
第六周:数据类型
坚持自律是计算机中最强的天赋之一，加油！
小余007
104 0
游客gsgxy6yg6ydlm
VLOOKUP函数都不会，如何在职场快人一步？
VLOOKUP函数都不会，如何在职场快人一步？
游客gsgxy6yg6ydlm
151 1
VLOOKUP函数都不会，如何在职场快人一步？
喵叔哟
|
存储 自然语言处理 关系型数据库
从大厂不允许使用 SELECT * 说开去
从大厂不允许使用 SELECT * 说开去
喵叔哟
145 0
技术小胖子
AGG第二十八课 rasterizer的填充规则
技术小胖子
1202 0