Liny_@NotePad

沉迷ACG中

练习:SQL基础与常用单行函数

YOYO posted @ 2009年2月13日 20:56 in 【数据库】 with tags oracle 练习 , 2555 阅读

SCOTT/TIGER

SQL基础

1、用SELECT语句表达一下数据库概论中的:笛卡尔积、自然连接、并集、交集,用EMP表和DEPT

笛卡尔积@SQL语句

SQL> select *

  2  from emp,dept;

笛卡尔积@查询结果

 

自然连接@SQL语句

SQL> select *

  2  from emp,dept

  3  where emp.deptno = dept.deptno;

自然连接@查询结果

 

并集(去掉重复值)@SQL语句

SQL> select deptno

  2  from emp

  3  union

  4  select deptno

  5  from dept;

并集(去掉重复值)@查询结果

DEPTNO                                                                                         

----------                                                                                          

        10                                                                                         

        20                                                                                         

        30                                                                                          

        40

并集(不去掉重复值)@SQL语句

SQL> select deptno

  2  from emp

  3  union all

  4  select deptno

  5  from dept;

并集(不去掉重复值)@查询结果

DEPTNO                                                                                         

----------                                                                                          

        20                                                                                         

        30                                                                                          

        30                                                                                         

        20                                                                                         

        30                                                                                          

        30                                                                                         

        10                                                                                         

        20                                                                                         

        10                                                                                         

        30                                                                                          

        20                                                                                         

 

DEPTNO                                                                                         

----------                                                                                         

        30                                                                                         

        20                                                                                          

        10                                                                                         

        10                                                                                         

        20                                                                                          

        30                                                                                         

        40                                                                                          

 

已选择18行。

 

交集@SQL语句

SQL> select deptno

  2  from emp

  3  intersect

  4  select deptno

  5  from dept;

交集@查询结果

    DEPTNO                                                                                         

----------                                                                                          

        10                                                                                         

        20                                                                                          

        30           

 

差集(emp-dept@SQL语句

SQL> select deptno

  2  from emp

  3  minus

  4  select deptno

  5  from dept;

差集(emp-dept@查询结果

未选定行

差集(dept-emp@SQL语句

SQL> select deptno

  2  from dept

  3  minus

  4  select deptno

  5  from emp;

差集(dept-emp@查询结果

    DEPTNO                                                                                          

----------                                                                                         

        40      

 

2、从EMP表中查询符合以下条件的雇员信息:薪水>3000,部门号为20

SQL语句

SQL> select *

  2  from emp

  3  where sal>3000 and deptno = 20;

查询结果

未选定行

EMP表中查询符合以下条件的雇员信息:薪水>=3000,部门号为20

SQL语句

SQL> select *

  2  from emp

  3  where sal>=3000 and deptno = 20;

查询结果

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO             

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------             

      7788 SCOTT      ANALYST         7566 19-4 -87       3000                    20             

      7902 FORD       ANALYST         7566 03-12-81       3000                    20    

 

3、找出有提成(comm)”记录的雇员

SQL语句

SQL> select *

  2  from emp

  3  where comm is not null;

查询结果

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO              

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------             

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

      7521 WARD       SALESMAN        7698 22-2 -81       1250        500         30             

      7654 MARTIN     SALESMAN        7698 28-9 -81       1250       1400         30             

      7844 TURNER     SALESMAN        7698 08-9 -81       1500          0         30  

 

4、报表:列(ID,名字,部门名字,总收入【总收入=薪水+提成,折合人民币,汇率$1=7,格式:¥12,345.00】,工龄(月)

SQL语句

SQL> select empno id, ename "Name", dname "Department",

  2  ''|| to_char( coalesce(sal+comm,sal)*7, '99,999.99') "Income",

  3  round( months_between(sysdate, hiredate) ) "WorkMonths"

  4  from emp,dept

  5  where emp.deptno = dept.deptno;

查询结果

        ID Name       Department     Income       WorkMonths                                       

---------- ---------- -------------- ------------ ----------                                        

      7369 SMITH      RESEARCH         5,600.00        338                                       

      7499 ALLEN      SALES          13,300.00        336                                       

      7521 WARD       SALES          12,250.00        336                                       

      7566 JONES      RESEARCH       20,825.00        334                                       

      7654 MARTIN     SALES          18,550.00        329                                       

      7698 BLAKE      SALES          19,950.00        333                                       

      7782 CLARK      ACCOUNTING     17,150.00        332                                       

      7788 SCOTT      RESEARCH       21,000.00        262                                        

      7839 KING       ACCOUNTING     35,000.00        327                                       

      7844 TURNER     SALES          10,500.00        329                                       

      7876 ADAMS      RESEARCH         7,700.00        261                                       

 

        ID Name       Department     Income       WorkMonths                                       

---------- ---------- -------------- ------------ ----------                                        

      7900 JAMES      SALES            6,650.00        326                                       

      7902 FORD       RESEARCH       21,000.00        326                                       

      7934 MILLER     ACCOUNTING       9,100.00        325                                       

 

已选择14行。

 

5、报表,(部门名称,员工名字,岗位名字,薪水),没有员工的部门也要列出。(提示:用外连接)

SQL语句

SQL> select dname, ename, job, sal

  2  from emp right join dept

  3  on emp.deptno = dept.deptno;

查询结果

DNAME          ENAME      JOB              SAL                                                     

-------------- ---------- --------- ----------                                                     

RESEARCH       SMITH      CLERK            800                                                      

SALES          ALLEN      SALESMAN        1600                                                     

SALES          WARD       SALESMAN        1250                                                     

RESEARCH       JONES      MANAGER         2975                                                     

SALES          MARTIN     SALESMAN        1250                                                     

SALES          BLAKE      MANAGER         2850                                                      

ACCOUNTING     CLARK      MANAGER         2450                                                     

RESEARCH       SCOTT      ANALYST         3000                                                     

ACCOUNTING     KING       PRESIDENT       5000                                                     

SALES          TURNER     SALESMAN        1500                                                     

RESEARCH       ADAMS      CLERK           1100                                                      

 

DNAME          ENAME      JOB              SAL                                                     

-------------- ---------- --------- ----------                                                     

SALES          JAMES      CLERK            950                                                     

RESEARCH       FORD       ANALYST         3000                                                     

ACCOUNTING     MILLER     CLERK           1300                                                      

OPERATIONS                                                                                         

 

已选择15行。

 

单行函数

6、用Dual虚表验证以下函数:

                     字符函数:UPPER,LOWER,INITCAP  LENGTH,INSTR,TRIM,LPAD,RPAD,REPLACE,SUBSTR

                     数字函数: ROUND

                     日期函数:MONTHS_BETWEEN,NEXT_DAY

UPPER函数@作用

将所有字符串转换为大写。

UPPER函数@SQL语句

SQL> select upper('abcaldfkASLDKFJ')

  2  from dual;

UPPER函数@查询结果

UPPER('ABCALDFK                                                                                    

---------------                                                                                    

ABCALDFKASLDKFJ

 

LOWER函数@作用

将所有字符串转换为小写。

LOWER函数@SQL语句

SQL> select lower('acbasdlkASDLKFJJ')

  2  from dual;

LOWER函数@查询结果

LOWER('ACBASDLKA                                                                                   

----------------                                                                                    

acbasdlkasdlkfjj  

 

INITCAP函数@作用

将每个单词的首字母大写,其他字母小写。

INITCAP函数@SQL语句

SQL> select initcap('hello world!')

  2  from dual;

INITCAP函数@查询结果

INITCAP('HEL                                                                                        

------------                                                                                       

Hello World!

 

LENGTH函数@作用

求出字符串的长度。

LENGTH函数@SQL语句

SQL> select length('go on~!!!')

  2  from dual;

LENGTH函数@查询结果

LENGTH('GOON~!!!')                                                                                  

------------------                                                                                 

                 9

 

INSTR函数@作用

格式 - INSTR(str1,str2,m,n)

求出str1字符串中从m位置开始第nstr2字串的起始位置,默认mn均为1

INSTR函数@SQL语句1

SQL> select instr('hello world helloworldhelloworld','world',10,2)

  2  from dual;

INSTR函数@查询结果1

NSTR('HELLOWORLDHELLOWORLDHELLOWORLD','WORLD',10,2)                                               

----------------------------------------------------                                               

                                                  28  

INSTR函数@SQL语句2

SQL> select instr('hello world helloworldhelloworld','hello',1,1)

  2  from dual;

INSTR函数@查询结果2

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO',1,1)                                                

---------------------------------------------------                                                

                                                  1      

INSTR函数@SQL语句3

SQL> select instr('hello world helloworldhelloworld','hello',-9,1)

  2  from dual;

INSTR函数@查询结果3

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO',-9,1)                                               

----------------------------------------------------                                               

                                                  23 

INSTR函数@SQL语句4

SQL> select instr('hello world helloworldhelloworld','hello',-16,2)

  2  from dual;

INSTR函数@查询结果4

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO',-16,2)                                              

-----------------------------------------------------                                              

                                                    1

INSTR函数@SQL语句5

SQL> select instr('hello world','abc',0,2)

  2  from dual;

INSTR函数@查询结果5

INSTR('HELLOWORLD','ABC',0,2)                                                                      

-----------------------------                                                                       

                            0

INSTR函数@SQL语句6

SQL> select instr('hello world','abc',-1,2)

  2  from dual;

INSTR函数@查询结果6

INSTR('HELLOWORLD','ABC',-1,2)                                                                     

------------------------------                                                                     

                             0

INSTR函数@SQL语句7

SQL> select instr('hello world helloworldhelloworld','hello')

  2  from dual;

INSTR函数@查询结果7

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO')                                              

-----------------------------------------------------                                              

                                                    1

 

TRIM函数@作用

去掉字符串前后的空格。

TRIM函数@SQL语句

SQL> select trim('   hello  world  ')

  2  from dual;

TRIM函数@查询结果

TRIM('HELLOW                                                                                       

------------                                                                                        

hello  world 

 

LPAD函数@作用

格式 – LPADstr,n,ch

显示str字符串的左边n长度部分,如果str长度不足n,则在左边填充ch字符。

LPAD函数@SQL语句1

SQL> select lpad('ha',20,'x')

  2  from dual;

LPAD函数@查询结果1

LPAD('HA',20,'X')                                                                                   

--------------------                                                                               

xxxxxxxxxxxxxxxxxxha  

LPAD函数@SQL语句2

SQL> select lpad('hahaha',3,'x')

  2  from dual;

LPAD函数@查询结果2

LPA                                                                                                 

---                                                                                                

hah

LPAD函数@SQL语句3

SQL> select lpad('ha',20)

  2  from dual;

LPAD函数@查询结果3

LPAD('HA',20)                                                                                      

--------------------                                                                               

                  ha

 

RPAD函数@作用

格式 – RPADstr,n,ch

显示str字符串的左边n长度部分,如果str长度不足n,则在右边填充ch字符。

RPAD函数@SQL语句1

SQL> select rpad(‘ha’,20,’x’)

  2  from dual;

RPAD函数@查询结果1

RPAD(‘HA’,20,’X’)                                                                           

--------------------                                                                        

haxxxxxxxxxxxxxxxxxx

RPAD函数@SQL语句2

SQL> select rpad(‘hahaha’,3,’x’)

  2  from dual;

RPAD函数@查询结果2

RPA

--- 

hah

RPAD函数@SQL语句3

SQL> select rpad(‘ha’,20)

  2  from dual;

RPAD函数@查询结果3

RPAD(‘HA’,20)                                                                                       

--------------------                                                                               

ha

 

REPLACE函数@作用

格式 – REPLACE(str1,str2,str3)

替换str1字符串中所有str2字串为str3

REPLACE函数@SQL语句

SQL> select replace('strstrstrstrabcdefgabc','str','xx')

  2  from dual;

REPLACE函数@查询结果

REPLACE('STRSTRSTR                                                                                 

------------------                                                                                  

xxxxxxxxabcdefgabc

 

SUBSTR函数@作用

格式 – SUBSTR(str,m,n)

m>0则输出str字串中左起第m位置开始向右长度为n的子串。

m<0则输出str字串中右起第|m|位置开始向右长度为n的子串。

SUBSTR函数@SQL语句1

SQL> select substr('abcdefg',3,2)

  2  from dual;

SUBSTR函数@查询结果1

SU

--

Cd

SUBSTR函数@SQL语句2

SQL> select substr('abcdefg',-3,2)

  2  from dual;

SUBSTR函数@查询结果2

SU

--

Ef

SUBSTR函数@SQL语句3

SQL> select substr('abc',3,2)

  2  from dual;

SUBSTR函数@查询结果3

S

-

C

SUBSTR函数@SQL语句4

SQL> select substr('abc',-2,3)

  2  from dual;

SUBSTR函数@查询结果4

SU                                                                                        

--                                                                                         bc

SUBSTR函数@SQL语句5

SQL> select substr('abcdefg',5,4.2)

  2  from dual;

SUBSTR函数@查询结果5

SUB                                                                                        

---

efg

SUBSTR函数@SQL语句6

SQL> select substr('abcdefg',2,3.7)

  2  from dual;

SUBSTR函数@查询结果6

SUB

---

bcd

 

ROUND函数@作用

对数字四舍五入到指定小数位,如不填则默认取整。

ROUND函数@SQL语句1

SQL> select round(123)

  2  from dual;

ROUND函数@查询结果1

ROUND(123)

----------     

       123

ROUND函数@SQL语句2

SQL> select round(123.456)

  2  from dual;

ROUND函数@查询结果2

ROUND(123.456)  

--------------

           123

ROUND函数@SQL语句3

SQL> select round(123.654)

  2  from dual;

ROUND函数@查询结果3

ROUND(123.654) 

--------------     

           124

ROUND函数@SQL语句4

SQL> select round(123.654,2)

  2  from dual;

ROUND函数@查询结果4

ROUND(123.654) 

--------------     

           123.65

 

NEXT_DAY函数@作用

求出两个日期之间相差的月份数(用日期1-日期2),结果返回浮点型。

MONTHS_BETWEEN函数@SQL语句

SQL> select months_between(

  2   to_date('2009-4-25','YYYY-MM-DD'), to_date('2009-2-12','YYYY-MM-DD'))

  3   "Months_Between 2-12 and 4-25"

  4  from dual;

MONTHS_BETWEEN函数@查询结果

Months_Between 2-12 and 4-25

----------------------------

                  2.41935484

 

NEXT_DAY函数@作用

求出所给日期之后第一个星期X的日子。

NEXT_DAY函数@SQL语句

SQL> select next_day( to_date('2009-2-12','YYYY-MM-DD'), '星期六') Saturday

  2  from dual;

NEXT_DAY函数@查询结果

SATURDAY                                                                                   

---------- 

14-2 -09

 

linyq@SPOTO 2009-2-13

 

SCOTT/TIGER

SQL基础

1、用SELECT语句表达一下数据库概论中的:笛卡尔积、自然连接、并集、交集,用EMP表和DEPT

笛卡尔积@SQL语句

SQL> select *

  2  from emp,dept;

笛卡尔积@查询结果

 

自然连接@SQL语句

SQL> select *

  2  from emp,dept

  3  where emp.deptno = dept.deptno;

自然连接@查询结果

 

并集(去掉重复值)@SQL语句

SQL> select deptno

  2  from emp

  3  union

  4  select deptno

  5  from dept;

并集(去掉重复值)@查询结果

DEPTNO                                                                                         

----------                                                                                          

        10                                                                                         

        20                                                                                         

        30                                                                                          

        40

并集(不去掉重复值)@SQL语句

SQL> select deptno

  2  from emp

  3  union all

  4  select deptno

  5  from dept;

并集(不去掉重复值)@查询结果

DEPTNO                                                                                         

----------                                                                                          

        20                                                                                         

        30                                                                                          

        30                                                                                         

        20                                                                                         

        30                                                                                          

        30                                                                                         

        10                                                                                         

        20                                                                                         

        10                                                                                         

        30                                                                                          

        20                                                                                         

 

DEPTNO                                                                                         

----------                                                                                         

        30                                                                                         

        20                                                                                          

        10                                                                                         

        10                                                                                         

        20                                                                                          

        30                                                                                         

        40                                                                                          

 

已选择18行。

 

交集@SQL语句

SQL> select deptno

  2  from emp

  3  intersect

  4  select deptno

  5  from dept;

交集@查询结果

    DEPTNO                                                                                         

----------                                                                                          

        10                                                                                         

        20                                                                                          

        30           

 

差集(emp-dept@SQL语句

SQL> select deptno

  2  from emp

  3  minus

  4  select deptno

  5  from dept;

差集(emp-dept@查询结果

未选定行

差集(dept-emp@SQL语句

SQL> select deptno

  2  from dept

  3  minus

  4  select deptno

  5  from emp;

差集(dept-emp@查询结果

    DEPTNO                                                                                          

----------                                                                                         

        40      

 

2、从EMP表中查询符合以下条件的雇员信息:薪水>3000,部门号为20

SQL语句

SQL> select *

  2  from emp

  3  where sal>3000 and deptno = 20;

查询结果

未选定行

EMP表中查询符合以下条件的雇员信息:薪水>=3000,部门号为20

SQL语句

SQL> select *

  2  from emp

  3  where sal>=3000 and deptno = 20;

查询结果

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO             

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------             

      7788 SCOTT      ANALYST         7566 19-4 -87       3000                    20             

      7902 FORD       ANALYST         7566 03-12-81       3000                    20    

 

3、找出有提成(comm)”记录的雇员

SQL语句

SQL> select *

  2  from emp

  3  where comm is not null;

查询结果

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO              

---------- ---------- --------- ---------- ---------- ---------- ---------- ----------             

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

      7521 WARD       SALESMAN        7698 22-2 -81       1250        500         30             

      7654 MARTIN     SALESMAN        7698 28-9 -81       1250       1400         30             

      7844 TURNER     SALESMAN        7698 08-9 -81       1500          0         30  

 

4、报表:列(ID,名字,部门名字,总收入【总收入=薪水+提成,折合人民币,汇率$1=7,格式:¥12,345.00】,工龄(月)

SQL语句

SQL> select empno id, ename "Name", dname "Department",

  2  ''|| to_char( coalesce(sal+comm,sal)*7, '99,999.99') "Income",

  3  round( months_between(sysdate, hiredate) ) "WorkMonths"

  4  from emp,dept

  5  where emp.deptno = dept.deptno;

查询结果

        ID Name       Department     Income       WorkMonths                                       

---------- ---------- -------------- ------------ ----------                                        

      7369 SMITH      RESEARCH         5,600.00        338                                       

      7499 ALLEN      SALES          13,300.00        336                                       

      7521 WARD       SALES          12,250.00        336                                       

      7566 JONES      RESEARCH       20,825.00        334                                       

      7654 MARTIN     SALES          18,550.00        329                                       

      7698 BLAKE      SALES          19,950.00        333                                       

      7782 CLARK      ACCOUNTING     17,150.00        332                                       

      7788 SCOTT      RESEARCH       21,000.00        262                                        

      7839 KING       ACCOUNTING     35,000.00        327                                       

      7844 TURNER     SALES          10,500.00        329                                       

      7876 ADAMS      RESEARCH         7,700.00        261                                       

 

        ID Name       Department     Income       WorkMonths                                       

---------- ---------- -------------- ------------ ----------                                        

      7900 JAMES      SALES            6,650.00        326                                       

      7902 FORD       RESEARCH       21,000.00        326                                       

      7934 MILLER     ACCOUNTING       9,100.00        325                                       

 

已选择14行。

 

5、报表,(部门名称,员工名字,岗位名字,薪水),没有员工的部门也要列出。(提示:用外连接)

SQL语句

SQL> select dname, ename, job, sal

  2  from emp right join dept

  3  on emp.deptno = dept.deptno;

查询结果

DNAME          ENAME      JOB              SAL                                                     

-------------- ---------- --------- ----------                                                     

RESEARCH       SMITH      CLERK            800                                                      

SALES          ALLEN      SALESMAN        1600                                                     

SALES          WARD       SALESMAN        1250                                                     

RESEARCH       JONES      MANAGER         2975                                                     

SALES          MARTIN     SALESMAN        1250                                                     

SALES          BLAKE      MANAGER         2850                                                      

ACCOUNTING     CLARK      MANAGER         2450                                                     

RESEARCH       SCOTT      ANALYST         3000                                                     

ACCOUNTING     KING       PRESIDENT       5000                                                     

SALES          TURNER     SALESMAN        1500                                                     

RESEARCH       ADAMS      CLERK           1100                                                      

 

DNAME          ENAME      JOB              SAL                                                     

-------------- ---------- --------- ----------                                                     

SALES          JAMES      CLERK            950                                                     

RESEARCH       FORD       ANALYST         3000                                                     

ACCOUNTING     MILLER     CLERK           1300                                                      

OPERATIONS                                                                                         

 

已选择15行。

 

单行函数

6、用Dual虚表验证以下函数:

                     字符函数:UPPER,LOWER,INITCAP  LENGTH,INSTR,TRIM,LPAD,RPAD,REPLACE,SUBSTR

                     数字函数: ROUND

                     日期函数:MONTHS_BETWEEN,NEXT_DAY

UPPER函数@作用

将所有字符串转换为大写。

UPPER函数@SQL语句

SQL> select upper('abcaldfkASLDKFJ')

  2  from dual;

UPPER函数@查询结果

UPPER('ABCALDFK                                                                                    

---------------                                                                                    

ABCALDFKASLDKFJ

 

LOWER函数@作用

将所有字符串转换为小写。

LOWER函数@SQL语句

SQL> select lower('acbasdlkASDLKFJJ')

  2  from dual;

LOWER函数@查询结果

LOWER('ACBASDLKA                                                                                   

----------------                                                                                    

acbasdlkasdlkfjj  

 

INITCAP函数@作用

将每个单词的首字母大写,其他字母小写。

INITCAP函数@SQL语句

SQL> select initcap('hello world!')

  2  from dual;

INITCAP函数@查询结果

INITCAP('HEL                                                                                        

------------                                                                                       

Hello World!

 

LENGTH函数@作用

求出字符串的长度。

LENGTH函数@SQL语句

SQL> select length('go on~!!!')

  2  from dual;

LENGTH函数@查询结果

LENGTH('GOON~!!!')                                                                                  

------------------                                                                                 

                 9

 

INSTR函数@作用

格式 - INSTR(str1,str2,m,n)

求出str1字符串中从m位置开始第nstr2字串的起始位置,默认mn均为1

INSTR函数@SQL语句1

SQL> select instr('hello world helloworldhelloworld','world',10,2)

  2  from dual;

INSTR函数@查询结果1

NSTR('HELLOWORLDHELLOWORLDHELLOWORLD','WORLD',10,2)                                               

----------------------------------------------------                                               

                                                  28  

INSTR函数@SQL语句2

SQL> select instr('hello world helloworldhelloworld','hello',1,1)

  2  from dual;

INSTR函数@查询结果2

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO',1,1)                                                

---------------------------------------------------                                                

                                                  1      

INSTR函数@SQL语句3

SQL> select instr('hello world helloworldhelloworld','hello',-9,1)

  2  from dual;

INSTR函数@查询结果3

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO',-9,1)                                               

----------------------------------------------------                                               

                                                  23 

INSTR函数@SQL语句4

SQL> select instr('hello world helloworldhelloworld','hello',-16,2)

  2  from dual;

INSTR函数@查询结果4

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO',-16,2)                                              

-----------------------------------------------------                                              

                                                    1

INSTR函数@SQL语句5

SQL> select instr('hello world','abc',0,2)

  2  from dual;

INSTR函数@查询结果5

INSTR('HELLOWORLD','ABC',0,2)                                                                      

-----------------------------                                                                       

                            0

INSTR函数@SQL语句6

SQL> select instr('hello world','abc',-1,2)

  2  from dual;

INSTR函数@查询结果6

INSTR('HELLOWORLD','ABC',-1,2)                                                                     

------------------------------                                                                     

                             0

INSTR函数@SQL语句7

SQL> select instr('hello world helloworldhelloworld','hello')

  2  from dual;

INSTR函数@查询结果7

INSTR('HELLOWORLDHELLOWORLDHELLOWORLD','HELLO')                                              

-----------------------------------------------------                                              

                                                    1

 

TRIM函数@作用

去掉字符串前后的空格。

TRIM函数@SQL语句

SQL> select trim('   hello  world  ')

  2  from dual;

TRIM函数@查询结果

TRIM('HELLOW                                                                                       

------------                                                                                        

hello  world 

 

LPAD函数@作用

格式 – LPADstr,n,ch

显示str字符串的左边n长度部分,如果str长度不足n,则在左边填充ch字符。

LPAD函数@SQL语句1

SQL> select lpad('ha',20,'x')

  2  from dual;

LPAD函数@查询结果1

LPAD('HA',20,'X')                                                                                   

--------------------                                                                               

xxxxxxxxxxxxxxxxxxha  

LPAD函数@SQL语句2

SQL> select lpad('hahaha',3,'x')

  2  from dual;

LPAD函数@查询结果2

LPA                                                                                                 

---                                                                                                

hah

LPAD函数@SQL语句3

SQL> select lpad('ha',20)

  2  from dual;

LPAD函数@查询结果3

LPAD('HA',20)                                                                                      

--------------------                                                                               

                  ha

 

RPAD函数@作用

格式 – RPADstr,n,ch

显示str字符串的左边n长度部分,如果str长度不足n,则在右边填充ch字符。

RPAD函数@SQL语句1

SQL> select rpad(‘ha’,20,’x’)

  2  from dual;

RPAD函数@查询结果1

RPAD(‘HA’,20,’X’)                                                                           

--------------------                                                                        

haxxxxxxxxxxxxxxxxxx

RPAD函数@SQL语句2

SQL> select rpad(‘hahaha’,3,’x’)

  2  from dual;

RPAD函数@查询结果2

RPA

--- 

hah

RPAD函数@SQL语句3

SQL> select rpad(‘ha’,20)

  2  from dual;

RPAD函数@查询结果3

RPAD(‘HA’,20)                                                                                       

--------------------                                                                               

ha

 

REPLACE函数@作用

格式 – REPLACE(str1,str2,str3)

替换str1字符串中所有str2字串为str3

REPLACE函数@SQL语句

SQL> select replace('strstrstrstrabcdefgabc','str','xx')

  2  from dual;

REPLACE函数@查询结果

REPLACE('STRSTRSTR                                                                                 

------------------                                                                                  

xxxxxxxxabcdefgabc

 

SUBSTR函数@作用

格式 – SUBSTR(str,m,n)

m>0则输出str字串中左起第m位置开始向右长度为n的子串。

m<0则输出str字串中右起第|m|位置开始向右长度为n的子串。

SUBSTR函数@SQL语句1

SQL> select substr('abcdefg',3,2)

  2  from dual;

SUBSTR函数@查询结果1

SU

--

Cd

SUBSTR函数@SQL语句2

SQL> select substr('abcdefg',-3,2)

  2  from dual;

SUBSTR函数@查询结果2

SU

--

Ef

SUBSTR函数@SQL语句3

SQL> select substr('abc',3,2)

  2  from dual;

SUBSTR函数@查询结果3

S

-

C

SUBSTR函数@SQL语句4

SQL> select substr('abc',-2,3)

  2  from dual;

SUBSTR函数@查询结果4

SU                                                                                        

--                                                                                         bc

SUBSTR函数@SQL语句5

SQL> select substr('abcdefg',5,4.2)

  2  from dual;

SUBSTR函数@查询结果5

SUB                                                                                        

---

efg

SUBSTR函数@SQL语句6

SQL> select substr('abcdefg',2,3.7)

  2  from dual;

SUBSTR函数@查询结果6

SUB

---

bcd

 

ROUND函数@作用

对数字四舍五入到指定小数位,如不填则默认取整。

ROUND函数@SQL语句1

SQL> select round(123)

  2  from dual;

ROUND函数@查询结果1

ROUND(123)

----------     

       123

ROUND函数@SQL语句2

SQL> select round(123.456)

  2  from dual;

ROUND函数@查询结果2

ROUND(123.456)  

--------------

           123

ROUND函数@SQL语句3

SQL> select round(123.654)

  2  from dual;

ROUND函数@查询结果3

ROUND(123.654) 

--------------     

           124

ROUND函数@SQL语句4

SQL> select round(123.654,2)

  2  from dual;

ROUND函数@查询结果4

ROUND(123.654) 

--------------     

           123.65

 

NEXT_DAY函数@作用

求出两个日期之间相差的月份数(用日期1-日期2),结果返回浮点型。

MONTHS_BETWEEN函数@SQL语句

SQL> select months_between(

  2   to_date('2009-4-25','YYYY-MM-DD'), to_date('2009-2-12','YYYY-MM-DD'))

  3   "Months_Between 2-12 and 4-25"

  4  from dual;

MONTHS_BETWEEN函数@查询结果

Months_Between 2-12 and 4-25

----------------------------

                  2.41935484

 

NEXT_DAY函数@作用

求出所给日期之后第一个星期X的日子。

NEXT_DAY函数@SQL语句

SQL> select next_day( to_date('2009-2-12','YYYY-MM-DD'), '星期六') Saturday

  2  from dual;

NEXT_DAY函数@查询结果

SATURDAY                                                                                   

---------- 

14-2 -09

 

linyq@SPOTO 2009-2-13

 

 


登录 *


loading captcha image...
(输入验证码)
or Ctrl+Enter