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