我的java天地

          oracle數據庫like和左連接用法

          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

          posted on 2009-03-25 22:30 tobyxiong 閱讀(2919) 評論(0)  編輯  收藏 所屬分類: DATABASES

          <2009年3月>
          22232425262728
          1234567
          891011121314
          15161718192021
          22232425262728
          2930311234

          導航

          統計

          常用鏈接

          留言簿(3)

          隨筆分類(144)

          隨筆檔案(157)

          相冊

          最新隨筆

          搜索

          積分與排名

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 上高县| 阜新| 安新县| 涞源县| 文成县| 家居| 昔阳县| 马公市| 高雄市| 开远市| 湖北省| 辽阳市| 雷山县| 泗水县| 伊金霍洛旗| 莱西市| 全南县| 庄浪县| 永安市| 谢通门县| 天台县| 前郭尔| 安吉县| 凤翔县| 顺平县| 新野县| 称多县| 鄂尔多斯市| 孝感市| 安顺市| 康定县| 汉阴县| 广元市| 镇巴县| 当雄县| 彭州市| 齐齐哈尔市| 改则县| 涟源市| 阿拉善盟| 永年县|