SQL函数（-）

来源：互联网发布：mac口红免税店价格编辑：程序博客网时间：2024/06/06 04:29

附录 any someall 区别

> ANY(sub-qurey) --- >MIN(sub-qurey)
< ANY(sub-query) --- <MAX(sub-qurey)
> ALL(sub-query) --- >MAX(sub-qurey)
< ALL(sub-query) --- <MIN(sub-qurey)
=ANY 相当于in

=any（2，4） =in（2,4）

all 是查询还可以是子查询
如：

select name from edit 其中name前省略了all.

name前可以加ALL|DISTINCT  all是所有记录.   distinct是不重复的。

eg：

SQL> select * from customers

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         1John      Brown     01-1月-65    800-555-1211
         2 Cynthia   Green     05-2月-68    800-555-1212
         3Steve     White     16-3月-71    800-555-1213
         4Gail      Black                    800-555-1214
         5Doreen    Blue      20-5月-70

SQL> select * from customers where customer_id > any(2,4);(大于最小的2)

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         3Steve     White     16-3月-71    800-555-1213
         4Gail      Black                    800-555-1214
         5Doreen    Blue      20-5月-70

SQL> select * from customers where customer_id > some(2,4);(大于最小的2)

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         3Steve     White     16-3月-71    800-555-1213
         4Gail      Black                    800-555-1214
         5Doreen    Blue      20-5月-70

SQL> select * from customers where customer_id > all(2, 4);(大于最大的4)

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         5Doreen    Blue      20-5月-70

SQL> select * from customers where customer_id > all(selectcustomer_id from customers where customer_id between 2 and4);

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         5Doreen    Blue      20-5月-70

SQL> select customer_id from customers where customer_id between2 and 4;

CUSTOMER_ID
-----------
         2
         3
         4

SQL> select * from customers where customer_id > all(selectcustomer_id from customers where customer_id in (2, 4));
（先执行空号中的在执行all）

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         5Doreen    Blue      20-5月-70

SQL> select customer_id from customers where customer_id in (2,4);

CUSTOMER_ID
-----------
         2
         4

SQL> select * from customers where customer_id = all(selectcustomer_id from customers where customer_id in (2, 4));

未选定行

SQL> select customer_id from customers where customer_id in (2,4);

CUSTOMER_ID
-----------
         2
         4

SQL> select * from customers where customer_id = any(selectcustomer_id from customers where customer_id in (2, 4));

CUSTOMER_ID FIRST_NAME LAST_NAME DOB           PHONE
----------- ---------- ---------- --------------------------
         2 Cynthia   Green     05-2月-68    800-555-1212
         4Gail      Black                    800-555-1214
eg：

SQL> select 'ABC' FROM DUAL; （'abc'为常量）

'AB
---
ABC

SQL> select LOWER('ABC') FROM DUAL;

LOW
---
abc

eg：
SQL> select LOWER(first_name), last_name from customers;
SQL> select LOWER(first_name), UPPER(last_name) fromcustomers;
SQL> select LOWER(first_name), UPPER(last_name), LOWER(*) fromcustomers; （不可在lower中有*）
SQL> select LOWER(first_name), UPPER(last_name) from customersWHERE UPPER(LAST_NAME) LIKE 'B%';（like后面的匹配可以带%）

字符函数：
1， LOWER函数和UPPER函数

   作用：将字符串转换成为小(大)写字母

   示例：selectfirstname,lastname from customers where LOWER(lastname) ='nelson';

eg：

SQL> select first_name, last_name from customers where last_name= 'Brown';

FIRST_NAMELAST_NAME
--------------------
John      Brown

SQL> select first_name, last_name from customers where last_name= 'brown';

未选定行

SQL> select first_name, last_name from customers wherelower(last_name) = 'brown';

FIRST_NAMELAST_NAME
--------------------
John      Brown

2、INSTR函数

   作用：在字符串中查找指定的字符串，返回找到的位置(下标从1开始)。如果没找到，则返回0.

   示例：SELECTINSTR('my name is zhangsan.','zh') FROM DUAL;

eg：

SQL> select instr(first_name, 'xy') from customers wherelast_name = 'Brown';

（也可以在索引关键字中的列中搜索）

SQL> select instr(first_name, 'hn') from customers;

INSTR(FIRST_NAME,'HN')
----------------------
                    3
                    0
                    0
                    0
                    0

3、INITCAP函数

   作用：将字符串中每个单词转换为首字母大写，其余字符小写。

   示例： selectinitcap(firstname),initcap(lastname) from ustomers;

eg：

SQL> select initcap(title) from employees;

INITCAP(TITLE)
--------------------
Ceo
SalesManager       （多个单词的情况下每个单词首写字母大写但是对中文没有效果
）
Salesperson
Salesperson

4、ASCII函数和CHR函数

   作用：字符和ASCII码相互转换

   示例：SELECTASCII('A'),CHR(65) FROM DUAL;

eg：

ASCII('A')
----------          （两个字符）
       97

SQL> select ascii('a'), chr(97) from dual;

ASCII('A')C
-----------           （一个字符）   （区分大小写！）
       97a

5，LENGTH函数

   作用：返回字符串的长度

   示例：SELECTLENGTH('my name is zhangsan.') FROM DUAL;

6，LPAD函数和RPAD函数

   格式：LPAD(x,width[,pad_string])

   作用：给x左边补充pad_string，直至width长度。

   示例：SELECTc.customer_id,LPAD(c.first_name,20,'*') FROMcustomers c；

SQL> select lpad(ename, 20, '_') from emp;（够20）

LPAD(ENAME,20,'_')                        (包括单词在内一个20长度 )
----------------------------------------
_______________SMITH
_______________ALLEN
________________WARD
_______________JONES
______________MARTIN
_______________BLAKE
_______________CLARK
_______________SCOTT
________________KING
______________TURNER
_______________ADAMS
_______________JAMES
________________FORD
______________MILLER

已选择14行。

SQL> select lpad(ename, 6, '_') from emp;

LPAD(ENAME,6
------------
_SMITH
_ALLEN
__WARD
_JONES
MARTIN
_BLAKE
_CLARK
_SCOTT
__KING
TURNER
_ADAMS
_JAMES
__FORD
MILLER

已选择14行。

SQL> select lpad(ename, 5, '_') from emp;

LPAD(ENAME
----------
SMITH            （如果包括单词在内超过给定长度那么从右边省略相应的长度字节）
ALLEN
_WARD
JONES
MARTI
BLAKE
CLARK
SCOTT
_KING
TURNE
ADAMS
JAMES
_FORD
MILLE

已选择14行。

SQL> select lpad(first_name, 20, '*'), rpad(last_name, 20, '*')from employees;

LPAD(FIRST_NAME,20,'*')                 RPAD(LAST_NAME,20,'*')
--------------------------------------------------------------------------------
***************James                    Smith***************
*****************Ron                    Johnson*************
****************Fred                    Hobbs***************
***************Susan                    Jones***************



7，LTRIM函数、RTRIM函数和TRIM函数

   格式：LTRIM(x,[,trim_string])

   作用：从x左边截去trim_string，如果没有第二个参数则截去空格。

   示例：SELECTLTRIM('    ZHANGSAN.   ') FROM DUAL;

SQL> select name from products;

NAME
------------------------------
Modern Science
Chemistry
Supernova
Tank War
ZFiles
2412: TheReturn
Space Force9
From AnotherPlanet
ClassicalMusic
Pop3
Creative Yell
1

eg：

SQL> select * from test;

       IDNAME
---------- --------------------
        1cissst
        2 ccissst

SQL> select ltrim(name, 'C') from test;

LTRIM(NAME,'C')       （左边开始C匹配的字符全部删去）
--------------------
cissst
ccissst

或者：

SQL> select rtrim(ltrim(name, 'c'), 't') from test;

RTRIM(LTRIM(NAME,'C'
--------------------
isss
isss

8，SUBSTR函数

   格式：SUBSTR(x,start[,length])

   作用：从x中start开始，截取length长度的子串。如果未指明length，则一直截取到x的末尾。



空值处理函数：

1，NVL函数

   格式：NVL(x,value)

   作用：如果x为NULL，则返回value，否则返回x。

eg:

SQL> select nvl(manager_id, 8888) from employees;

NVL(MANAGER_ID,8888)         (前后给定的数值应该类型相对应)
--------------------
               8888
                  1
                  2
                  2

2，NVL2函数

   格式：NVL2(x,value1,value2)

   作用：如果x不为NULL，则返回value1，否则返回value2。

SQL> select nvl2(manager_id, 'abc', 'xyz') from employees;

NVL   （不强调类型对应）
---
xyz
abc
abc
abc

eg：（测试空值）

SQL> select nvl2(manager_id, null, 123) from employees;

NVL2(MANAGER_ID,NULL,123)
-------------------------
                     123                                                                                                                                                                          SQL> select count (nvl2(manager_id, null, 123)) fromemployees;

COUNT(NVL2(MANAGER_ID,NULL,123))
--------------------------------
                              1

SQL> select count (nvl2(manager_id, ' ', 123)) fromemployees;

COUNT(NVL2(MANAGER_ID,'',123))
------------------------------
                            4

0 0