附录 any some all 区别
> 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
----------- ---------- ---------- -------------- ------------
1 John Brown 01-1月 -65 800-555-1211
2 Cynthia Green 05-2月 -68 800-555-1212
3 Steve White 16-3月 -71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-5月 -70
SQL> select * from customers where customer_id > any(2, 4);(大于最小的2)
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
3 Steve White 16-3月 -71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-5月 -70
SQL> select * from customers where customer_id > some(2, 4);(大于最小的2)
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
3 Steve White 16-3月 -71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-5月 -70
SQL> select * from customers where customer_id > all(2, 4); (大于最大的4)
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
5 Doreen Blue 20-5月 -70
SQL> select * from customers where customer_id > all(select customer_id from customers where customer_id between 2 and 4);
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
5 Doreen Blue 20-5月 -70
SQL> select customer_id from customers where customer_id between 2 and 4;
CUSTOMER_ID
-----------
2
3
4
SQL> select * from customers where customer_id > all(select customer_id from customers where customer_id in (2, 4));
(先执行空号中的 在执行all)
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- -------------- ------------
5 Doreen 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(select customer_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(select customer_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
4 Gail 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) from customers;
SQL> select LOWER(first_name), UPPER(last_name), LOWER(*) from customers; (不可在lower中有*)
SQL> select LOWER(first_name), UPPER(last_name) from customers WHERE UPPER(LAST_NAME) LIKE 'B%';(like后面的匹配可以带%)
字符函数:
1, LOWER函数和UPPER函数
作用:将字符串转换成为小(大)写字母
示例:select firstname,lastname from customers where LOWER(lastname) = 'nelson';
eg:
SQL> select first_name, last_name from customers where last_name = 'Brown';
FIRST_NAME LAST_NAME
---------- ----------
John Brown
SQL> select first_name, last_name from customers where last_name = 'brown';
未选定行
SQL> select first_name, last_name from customers where lower(last_name) = 'brown';
FIRST_NAME LAST_NAME
---------- ----------
John Brown
2、INSTR函数
作用:在字符串中查找指定的字符串,返回找到的位置(下标从1开始)。如果没找到,则返回0.
示例:SELECT INSTR('my name is zhangsan.','zh') FROM DUAL;
eg:
SQL> select instr(first_name, 'xy') from customers where last_name = 'Brown';
(也可以在索引关键字中的列中搜索)
SQL> select instr(first_name, 'hn') from customers;
INSTR(FIRST_NAME,'HN')
----------------------
3
0
0
0
0
3、INITCAP函数
作用:将字符串中每个单词转换为首字母大写,其余字符小写。
示例: select initcap(firstname),initcap(lastname) from ustomers;
eg:
SQL> select initcap(title) from employees;
INITCAP(TITLE)
--------------------
Ceo
Sales Manager (多个单词的情况下 每个单词首写字母大写 但是对中文没有效果
)
Salesperson
Salesperson
4、ASCII函数和CHR函数
作用:字符和ASCII码相互转换
示例:SELECT ASCII('A'),CHR(65) FROM DUAL;
eg:
ASCII('A')
---------- (两个字符 )
97
SQL> select ascii('a'), chr(97) from dual;
ASCII('A') C
---------- - (一个字符) (区分大小写!)
97 a
5,LENGTH函数
作用:返回字符串的长度
示例:SELECT LENGTH('my name is zhangsan.') FROM DUAL;
6,LPAD函数和RPAD函数
格式:LPAD(x,width[,pad_string])
作用:给x左边补充pad_string,直至width长度。
示例:SELECT c.customer_id,LPAD(c.first_name,20,'*') FROM customers 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,如果没有第二个参数则截去空格。
示例:SELECT LTRIM(' ZHANG SAN. ') FROM DUAL;
SQL> select name from products;
NAME
------------------------------
Modern Science
Chemistry
Supernova
Tank War
Z Files
2412: The Return
Space Force 9
From Another Planet
Classical Music
Pop 3
Creative Yell
1
eg:
SQL> select * from test;
ID NAME
---------- --------------------
1 cissst
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)) from employees;
COUNT(NVL2(MANAGER_ID,NULL,123))
--------------------------------
1
SQL> select count (nvl2(manager_id, ' ', 123)) from employees;
COUNT(NVL2(MANAGER_ID,'',123))
------------------------------
4
> ANY(sub-qurey)
< ANY(sub-query)
> ALL(sub-query)
< ALL(sub-query)
=ANY
=any(2,4) =in(2,4)
all 是查询还可以是子查询
如:
select name from edit 其中name前省略了all.
name前可以加ALL|DISTINCT
eg:
SQL> select * from customers
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where customer_id > any(2, 4);(大于最小的2)
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where customer_id > some(2, 4);(大于最小的2)
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where customer_id > all(2, 4); (大于最大的4)
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
SQL> select * from customers where customer_id > all(select customer_id from customers where customer_id between 2 and 4);
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
SQL> select customer_id from customers where customer_id between 2 and 4;
CUSTOMER_ID
-----------
SQL> select * from customers where customer_id > all(select customer_id from customers where customer_id in (2, 4));
(先执行空号中的 在执行all)
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
SQL> select customer_id from customers where customer_id in (2, 4);
CUSTOMER_ID
-----------
SQL> select * from customers where customer_id = all(select customer_id from customers where customer_id in (2, 4));
未选定行
SQL> select customer_id from customers where customer_id in (2, 4);
CUSTOMER_ID
-----------
SQL> select * from customers where customer_id = any(select customer_id from customers where customer_id in (2, 4));
CUSTOMER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- -------------- ------------
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) from customers;
SQL> select LOWER(first_name), UPPER(last_name), LOWER(*) from customers; (不可在lower中有*)
SQL> select LOWER(first_name), UPPER(last_name) from customers WHERE UPPER(LAST_NAME) LIKE 'B%';(like后面的匹配可以带%)
字符函数:
1, LOWER函数和UPPER函数
eg:
SQL> select first_name, last_name from customers where last_name = 'Brown';
FIRST_NAME LAST_NAME
---------- ----------
John
SQL> select first_name, last_name from customers where last_name = 'brown';
未选定行
SQL> select first_name, last_name from customers where lower(last_name) = 'brown';
FIRST_NAME LAST_NAME
---------- ----------
John
2、INSTR函数
eg:
SQL> select instr(first_name, 'xy') from customers where last_name = 'Brown';
(也可以在索引关键字中的列中搜索)
SQL> select instr(first_name, 'hn') from customers;
INSTR(FIRST_NAME,'HN')
----------------------
3、INITCAP函数
eg:
SQL> select initcap(title) from employees;
INITCAP(TITLE)
--------------------
Ceo
Sales Manager
)
Salesperson
Salesperson
4、ASCII函数和CHR函数
eg:
ASCII('A')
----------
SQL> select ascii('a'), chr(97) from dual;
ASCII('A') C
---------- -
5,LENGTH函数
6,LPAD函数和RPAD函数
SQL> select lpad(ename, 20, '_') from emp;(够20)
LPAD(ENAME,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,'*')
---------------------------------------- ----------------------------------------
***************James
*****************Ron
****************Fred
***************Susan
7,LTRIM函数、RTRIM函数和TRIM函数
SQL> select name from products;
NAME
------------------------------
Modern Science
Chemistry
Supernova
Tank War
Z Files
2412: The Return
Space Force 9
From Another Planet
Classical Music
Pop 3
Creative Yell
1
eg:
SQL> select * from test;
---------- --------------------
SQL> select ltrim(name, 'C') from test;
LTRIM(NAME,'C')
--------------------
cissst
ccissst
或者:
SQL> select rtrim(ltrim(name, 'c'), 't') from test;
RTRIM(LTRIM(NAME,'C'
--------------------
isss
isss
8,SUBSTR函数
空值处理函数:
1,NVL函数
eg:
SQL> select nvl(manager_id, 8888) from employees;
NVL(MANAGER_ID,8888)
--------------------
2,NVL2函数
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)
-------------------------
COUNT(NVL2(MANAGER_ID,NULL,123))
--------------------------------
SQL> select count (nvl2(manager_id, ' ', 123)) from employees;
COUNT(NVL2(MANAGER_ID,'',123))
------------------------------