structured query language（非过程性的结构查询语言）
主要内容：
数据库怎么限制里面的数据；
数据库怎么排列里面的数据。
WHERE子句：
--WHERE字句起到的是对数据库查询结果集的限制作用
----[WHERE condition(s)]
------[]代表可选，意思是WHERE字句是可以没有的
------(s)也就是说条件是可以多个的
----WHERE条件的三个要素
------列名
------比较条件
--------不等于的三种形式!=、<>、^=
SQL>
select count(*) from
emp;
COUNT(*)
----------
14
SQL> select
count(*) from emp where
empno^=7788;
COUNT(*)
----------
13
------between and 是有边界的，意思是包括边界值的
------常数、值列表
--------常数的特征1、字符串和日期必须用引号括起来；2、字符串是大小写敏感
------like走索引的问题：like ‘%XX‘是不走索引的，基于索引的原理
SQL>
select * from emp where
empno = ‘7788‘;
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM
DEPTNO
---------- ---------- ---------
---------- -------------- ---------- ---------- ----------
7788 SCOTT
ANALYST 7566
09-2月
-82
3000
20
执行计划
----------------------------------------------------------
Plan
hash value: 4024650034
--------------------------------------------------------------------------------------
|
Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 1 | 38 |
1 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY
INDEX ROWID| EMP |
1 | 38 | 1
(0)| 00:00:01
|
|* 2 |
INDEX UNIQUE SCAN | EMP_PK |
1 | |
0 (0)|
00:00:01 |
--------------------------------------------------------------------------------------
Predicate
Information (identified by operation
id):
---------------------------------------------------
2 - access("EMPNO"=7788)
统计信息
----------------------------------------------------------
0 recursive
calls
0
db block gets
2 consistent gets
0 physical reads
0 redo size
772
bytes sent via SQL*Net
to client
405 bytes received via SQL*Net from
client
1
SQL*Net
roundtrips to/from
client
0
sorts (memory)
0 sorts (disk)
1 rows processed
有索引，且走索引
SQL>
select * from emp where
empno like ‘%88‘;
EMPNO ENAME
JOB MGR HIREDATE
SAL COMM
DEPTNO
---------- ---------- ---------
---------- -------------- ---------- ---------- ----------
7788 SCOTT
ANALYST 7566
09-2月
-82
3000
20
执行计划
----------------------------------------------------------
Plan
hash value: 3956160932
--------------------------------------------------------------------------
|
Id | Operation
| Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
1 | 38 | 3
(0)| 00:00:01
|
|* 1 |
TABLE ACCESS FULL|
EMP | 1 | 38 | 3
(0)| 00:00:01
|
--------------------------------------------------------------------------
Predicate
Information (identified by operation
id):
---------------------------------------------------
1 -
filter(TO_CHAR("EMPNO") LIKE ‘%88‘)
统计信息
----------------------------------------------------------
0 recursive
calls
0
db block gets
8 consistent gets
0 physical reads
0 redo size
864
bytes sent via SQL*Net
to client
416 bytes received via SQL*Net from
client
2
SQL*Net
roundtrips to/from
client
0
sorts (memory)
0 sorts (disk)
1 rows processed
不能走索引，走全表扫描
--------except 逃避符 一般用来处理 字符串里面的特色字符 例如 ‘\‘
------null的特征之一就是不能用比较等于或者不等于
SQL>
select * from emp where
comm != null;
未选定行
SQL> select
* from emp
where comm =
null;
未选定行
--逻辑运算
----AND
| AND |
TRUE |
FALSE |
NULL |
| TRUE |
TRUE |
FALSE |
NULL |
| FALSE |
FALSE |
FALSE |
FALSE |
| NULL |
NULL |
FALSE |
NULL |
----OR
| OR |
TRUE |
FALSE |
NULL |
| TRUE |
TRUE |
TRUE |
TRUE |
| FALSE |
TRUE |
FALSE |
NULL |
| NULL |
TRUE |
NULL |
NULL
|
----NLS_UPPER：转换成大写，不同语言都有用
----UPPER：不能处理特殊语言
--IN的查询转换问题：
WHERE字句,布布扣,bubuko.com
WHERE字句
原文：http://www.cnblogs.com/jiaoweixue/p/3578801.html
