1.當出現字符串‘_’時的處理方法
SQL> select table_name from user_tables where table_name like 'T\_A%' escape '\';
TABLE_NAME
------------------------------
T_ADMIN_INFO
T_ADV_CLICK_INFO
T_ADV_INFO
2.第二個字符串為a的名字
SQL> select last_name from s_emp where last_name like '_a%';
LAST_NAME
-------------------------
Nagayama
Catchpole
Havel
Magee
Maduro
Patel
Markarian
Patel
Dancs
3.未尾字符串為z的名字
SQL> select last_name from s_emp where last_name like '%z';
LAST_NAME
-------------------------
Velasquez
Schwartz
4.第一個字符串為M的名字
SQL> select last_name from s_emp where last_name like 'M%';
LAST_NAME
-------------------------
Menchu
Magee
Maduro
Markarian
5.左連接詳解
(1)4條數據的部門表
SQL> select * from scott.dept;
DEPTNO DNAME????????? LOC
------ -------------- -------------
??? 10 ACCOUNTING???? NEW YORK
??? 20 RESEARCH?????? DALLAS
??? 30 SALES????????? CHICAGO
??? 40 OPERATIONS???? BOSTON
(2)14條數據的的員工表
SQL> select * from scott.emp;
EMPNO ENAME????? JOB???????? MGR HIREDATE????????? SAL????? COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
?7369 SMITH????? CLERK????? 7902 1980-12-17???? 800.00?????????????? 20
?7499 ALLEN????? SALESMAN?? 7698 1981-2-20???? 1600.00??? 300.00???? 30
?7521 WARD?????? SALESMAN?? 7698 1981-2-22???? 1250.00??? 500.00???? 30
?7566 JONES????? MANAGER??? 7839 1981-4-2????? 2975.00?????????????? 20
?7654 MARTIN???? SALESMAN?? 7698 1981-9-28???? 1250.00?? 1400.00???? 30
?7698 BLAKE????? MANAGER??? 7839 1981-5-1????? 2850.00?????????????? 30
?7782 CLARK????? MANAGER??? 7839 1981-6-9????? 2450.00?????????????? 10
?7788 SCOTT????? ANALYST??? 7566 1987-4-19???? 3000.00?????????????? 20
?7839 KING?????? PRESIDENT?????? 1981-11-17??? 5000.00?????????????? 10
?7844 TURNER???? SALESMAN?? 7698 1981-9-8????? 1500.00????? 0.00???? 30
?7876 ADAMS????? CLERK????? 7788 1987-5-23???? 1100.00?????????????? 20
?7900 JAMES????? CLERK????? 7698 1981-12-3????? 950.00?????????????? 30
?7902 FORD?????? ANALYST??? 7566 1981-12-3???? 3000.00?????????????? 20
?7934 MILLER???? CLERK????? 7782 1982-1-23???? 1300.00?????????????? 10
14 rows selected
(3)左連接查詢結果
SQL> select a.deptno,b.ename from scott.dept a,scott.emp b? where a.deptno=b.deptno(+);
DEPTNO ENAME
------ ----------
??? 20 SMITH
??? 30 ALLEN
??? 30 WARD
??? 20 JONES
??? 30 MARTIN
??? 30 BLAKE
??? 10 CLARK
??? 20 SCOTT
??? 10 KING
??? 30 TURNER
??? 20 ADAMS
??? 30 JAMES
??? 20 FORD
??? 10 MILLER
??? 40
15 rows selected
之所以出現15條記錄是因為：部門表的4條記錄和員工表相匹配后有14條記錄，但是一個部門40沒有對應的員工表，則和null匹配。所以得??? 到15條記錄。
左連接的第二個示例
SQL> select * from a;
??????????? A??????????? AA
------------- -------------
??????????? 1??????????? 22
??????????? 1??????????? 33
??????????? 1??????????? 44
SQL> select * from b;
??????????? A??????????? BB??????????? CC
------------- ------------- -------------
??????????? 1??????????? 23??????????? 33
??????????? 1??????????? 77??????????? 77
??????????? 1??????????? 88??????????? 99
SQL> select a.a,b.a from a a,b b where a.a=b.a(+);
??????????? A???????????? A
------------- -------------
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
??????????? 1???????????? 1
9 rows selected
當兩個表中為3條數據時，最多能查出9條記錄，左表a會和b表數據一一匹配，三條記錄分別匹配三次。所有為9條
3.組函數是忽略空值的
SQL> select * from a;
??????????? A??????????? AA
------------- -------------
??????????? 1??????????? 22
??????????? 2??????????? 33
??????????? 3??????????? 44
??????????? 3
SQL> select avg(aa) from a;
?? AVG(AA)
----------
??????? 33