【2017/4/23】oracle之sql(二)
来源:互联网 发布:紫阳万里 知乎 编辑:程序博客网 时间:2024/06/03 18:16
大小写
upper所有大写 A-Zlower所有小写 a-z
initcap 开头大写,其余小写
SQL> select ename from emp where deptno=10;
ENAME
----------
CLARK
KING
MILLER
SQL> select lower(ename) from emp where deptno=10;
LOWER(ENAM
----------
clark
king
miller
SQL> select initcap(ename) from emp where deptno=10;
INITCAP(EN
----------
Clark
King
Miller
SQL>
SQL> select initcap('tiger_scott') from dual;
INITCAP('TI
-----------
Tiger_Scott
SQL> select initcap('tiger8scott') from dual;
INITCAP('TI
-----------
Tiger8scott
SQL>
字符函数concat
SQL> select concat(ename,' is work'),job from emp;CONCAT(ENAME,'ISWO JOB
------------------ ---------
SMITH is work CLERK
ALLEN is work SALESMAN
WARD is work SALESMAN
JONES is work MANAGER
MARTIN is work SALESMAN
BLAKE is work MANAGER
CLARK is work MANAGER
SCOTT is work ANALYST
KING is work PRESIDENT
TURNER is work SALESMAN
ADAMS is work CLERK
CONCAT(ENAME,'ISWO JOB
------------------ ---------
JAMES is work CLERK
FORD is work ANALYST
MILLER is work CLERK
14 rows selected.
SQL>
切割
substr(expr,m,n)#m:从哪开始+|- num,n:取多少个n>0substr('mygodssr',1,1)-->m
substr('mygodssr',-1,1)-->r
SQL> select ename from emp where substr(ename,2,1)='A';
ENAME
----------
WARD
MARTIN
JAMES
SQL>
长度
SQL> select length(ename),ename from emp;#返回字符长度LENGTH(ENAME) ENAME
------------- ----------
5 SMITH
5 ALLEN
4 WARD
5 JONES
6 MARTIN
5 BLAKE
5 CLARK
5 SCOTT
4 KING
6 TURNER
5 ADAMS
LENGTH(ENAME) ENAME
------------- ----------
5 JAMES
4 FORD
6 MILLER
14 rows selected.
SQL>
SQL> insert into emp(empno,ename) values(1,'数');
1 row created.
SQL> select ename,length(ename),lengthc(ename),lengthb(ename) from emp where empno=1;
ENAME LENGTH(ENAME) LENGTHC(ENAME) LENGTHB(ENAME)#3个字节存一个汉字--》2个字符
---------- ------------- -------------- --------------
??? 3 3 9
SQL>
查找
SQL> select instr(ename,'A'),ename from emp;INSTR(ENAME,'A') ENAME
---------------- ----------
0 SMITH 没找到
1 ALLEN 第一个
2 WARD 第二个
0 JONES
2 MARTIN
3 BLAKE 第三个
1 ADAMS 多个A只找第一个
SQL> select ename from emp where instr(substr(ename,1,2),'A')=1;
ENAME
----------
ALLEN
ADAMS
SQL>
右|左对齐
SQL> select deptno,dname,loc from dept;DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select rpad(deptno,10,' ')deptno,dname,loc from dept;
DEPTNO DNAME LOC
---------------------------------------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL> select lpad(deptno,8,' ')deptno,dname,loc from dept;
DEPTNO DNAME LOC
-------------------------------- -------------- -------------
10 ACCOUNTINGNEW YORK
20 RESEARCHDALLAS
30 SALESCHICAGO
40 OPERATIONSBOSTON
SQL>
SQL> col dname just right; #使dname列靠右
SQL> select deptno,dname,loc from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
切除
SQL> select trim('a' from 'aaabbbbaaa') from dual;TRIM
----
bbbb
SQL> select trim(trailing 'a' from 'aaabbbbaaa') from dual;
TRIM(TR
-------
aaabbbb
SQL> select trim(leading 'a' from 'aaabbbbaaa') from dual;
TRIM(LE
-------
bbbbaaa
SQL>
SQL> select ename,trim('A' from ename) from emp;
ENAME TRIM('A'FR
---------- ----------
SMITH SMITH
ALLEN LLEN
替换
replace(expr,old,new)SQL> select replace('ssr and ssy','ss','mm') from dual;
REPLACE('SS
-----------
mmr and mmy
SQL>
0 0
- 【2017/4/23】oracle之sql(二)
- Oracle入门(二)之SQL
- Oracle入门(二)之SQL
- 【2017/4/23】oracle之sql(一)
- Oracle数据库之SQL---select之二
- Oracle读书笔记-----PL/SQL编程(二)之程序流程
- ORACLE之PL/SQL简单基础语法学习(二)
- Oracle性能优化之高级SQL优化(二)
- ORACLE PL/SQL编程详解之八(二)
- Oracle之SQL优化-索引的基本原理(二)
- oracle之PLSql语言(二)sql语句的使用
- Oracle学习笔记之二----SQL
- Oracle PL/SQL语言基础之二
- ORACLE SQL性能优化之二
- ORACLE PL/SQL编程详解之二
- oracle SQL简介(二)
- 【2017/4/24】oracle之sql(三)
- SQL 之 视图(二)
- 初读大话设计模式
- 拼图游戏--九宫格
- mysql函数
- tableau临时记录知识点
- 练习1
- 【2017/4/23】oracle之sql(二)
- 过拟合
- maven:资源过滤的介绍
- RabbitMQ消息队列知识点归纳
- 用python创建桌面应用(二)
- SVN简单概述
- (ZT)clear icon cache
- 颜色矩原理及C++实现
- 集合框架-泛型接口的概述和使用