• SQL堂上作业四


    等值连接

    所有值相等的,都会被进行一次连接

    SELECT employees.employee_id, employees.last_name, 
    employees.department_id, departments.department_id,
    departments.location_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
    

    输出如下

    EMPLOYEE_ID LAST_NAME                                          DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
    ----------- -------------------------------------------------- ------------- ------------- -----------
            100 King                                                          90            90        1700
            101 Kochhar                                                       90            90        1700
            102 De Haan                                                       90            90        1700
            103 Hunold                                                        60            60        1400
            104 Ernst                                                         60            60        1400
            105 Austin                                                        60            60        1400
            106 Pataballa                                                     60            60        1400
            107 Lorentz                                                       60            60        1400
            108 Greenberg                                                    100           100        1700
            109 Faviet                                                       100           100        1700
            110 Chen                                                         100           100        1700
            111 Sciarra                                                      100           100        1700
            112 Urman                                                        100           100        1700
            113 Popp                                                         100           100        1700
            114 Raphaely                                                      30            30        1700
            115 Khoo                                                          30            30        1700
            116 Baida                                                         30            30        1700
            117 Tobias                                                        30            30        1700
            118 Himuro                                                        30            30        1700
            119 Colmenares                                                    30            30        1700
            120 Weiss                                                         50            50        1500
            121 Fripp                                                         50            50        1500
            122 Kaufling                                                      50            50        1500
            123 Vollman                                                       50            50        1500
            124 Mourgos                                                       50            50        1500
            125 Nayer                                                         50            50        1500
            126 Mikkilineni                                                   50            50        1500
            127 Landry                                                        50            50        1500
            128 Markle                                                        50            50        1500
            129 Bissot                                                        50            50        1500
            130 Atkinson                                                      50            50        1500
            131 Marlow                                                        50            50        1500
            132 Olson                                                         50            50        1500
            133 Mallin                                                        50            50        1500
            134 Rogers                                                        50            50        1500
            135 Gee                                                           50            50        1500
            136 Philtanker                                                    50            50        1500
            137 Ladwig                                                        50            50        1500
            138 Stiles                                                        50            50        1500
            139 Seo                                                           50            50        1500
            140 Patel                                                         50            50        1500
            141 Rajs                                                          50            50        1500
            142 Davies                                                        50            50        1500
            143 Matos                                                         50            50        1500
            144 Vargas                                                        50            50        1500
            145 Russell                                                       80            80        2500
            146 Partners                                                      80            80        2500
            147 Errazuriz                                                     80            80        2500
            148 Cambrault                                                     80            80        2500
            149 Zlotkey                                                       80            80        2500
            150 Tucker                                                        80            80        2500
            151 Bernstein                                                     80            80        2500
            152 Hall                                                          80            80        2500
            153 Olsen                                                         80            80        2500
            154 Cambrault                                                     80            80        2500
            155 Tuvault                                                       80            80        2500
            156 King                                                          80            80        2500
            157 Sully                                                         80            80        2500
            158 McEwen                                                        80            80        2500
            159 Smith                                                         80            80        2500
            160 Doran                                                         80            80        2500
            161 Sewall                                                        80            80        2500
            162 Vishney                                                       80            80        2500
            163 Greene                                                        80            80        2500
            164 Marvins                                                       80            80        2500
            165 Lee                                                           80            80        2500
            166 Ande                                                          80            80        2500
            167 Banda                                                         80            80        2500
            168 Ozer                                                          80            80        2500
            169 Bloom                                                         80            80        2500
            170 Fox                                                           80            80        2500
            171 Smith                                                         80            80        2500
            172 Bates                                                         80            80        2500
            173 Kumar                                                         80            80        2500
            174 Abel                                                          80            80        2500
            175 Hutton                                                        80            80        2500
            176 Taylor                                                        80            80        2500
            177 Livingston                                                    80            80        2500
            179 Johnson                                                       80            80        2500
            180 Taylor                                                        50            50        1500
            181 Fleaur                                                        50            50        1500
            182 Sullivan                                                      50            50        1500
            183 Geoni                                                         50            50        1500
            184 Sarchand                                                      50            50        1500
            185 Bull                                                          50            50        1500
            186 Dellinger                                                     50            50        1500
            187 Cabrio                                                        50            50        1500
            188 Chung                                                         50            50        1500
            189 Dilly                                                         50            50        1500
            190 Gates                                                         50            50        1500
            191 Perkins                                                       50            50        1500
            192 Bell                                                          50            50        1500
            193 Everett                                                       50            50        1500
            194 McCain                                                        50            50        1500
            195 Jones                                                         50            50        1500
            196 Walsh                                                         50            50        1500
            197 Feeney                                                        50            50        1500
            198 OConnell                                                      50            50        1500
            199 Grant                                                         50            50        1500
            200 Whalen                                                        10            10        1700
            201 Hartstein                                                     20            20        1800
            202 Fay                                                           20            20        1800
            203 Mavris                                                        40            40        2400
            204 Baer                                                          70            70        2700
            205 Higgins                                                      110           110        1700
            206 Gietz                                                        110           110        1700
    
    已选择106行。
    

    表的别名

    在上文中,表名太长了,打起来太复杂了,我们可以给表命名一个别名

    SELECT e.employee_id, e.last_name, e.department_id,
    d.department_id, d.location_id
    FROM employees e , departments d
    WHERE e.department_id = d.department_id;
    

    输出与上文的输出一致

    多个条件

    我们可以在WHERE后面添加多个条件

    SELECT e.last_name, e.salary, j.grade_level
    FROM employees e, job_grades j
    WHERE e.salary 
    BETWEEN j.lowest_sal AND j.highest_sal;
    

    这段语句的意思是:输出表employees,但是会给employees中的每个人的工资打一个分

    输出如下:

    LAST_NAME                                              SALARY GR
    -------------------------------------------------- ---------- --
    Olson                                                    2100 A
    Markle                                                   2200 A
    Philtanker                                               2200 A
    Landry                                                   2400 A
    Gee                                                      2400 A
    Colmenares                                               2500 A
    Marlow                                                   2500 A
    Patel                                                    2500 A
    Vargas                                                   2500 A
    Sullivan                                                 2500 A
    Perkins                                                  2500 A
    Himuro                                                   2600 A
    Matos                                                    2600 A
    OConnell                                                 2600 A
    Grant                                                    2600 A
    Mikkilineni                                              2700 A
    Seo                                                      2700 A
    Tobias                                                   2800 A
    Atkinson                                                 2800 A
    Geoni                                                    2800 A
    Jones                                                    2800 A
    Baida                                                    2900 A
    Rogers                                                   2900 A
    Gates                                                    2900 A
    Cabrio                                                   3000 B
    Feeney                                                   3000 B
    Khoo                                                     3100 B
    Davies                                                   3100 B
    Fleaur                                                   3100 B
    Walsh                                                    3100 B
    Nayer                                                    3200 B
    Stiles                                                   3200 B
    Taylor                                                   3200 B
    McCain                                                   3200 B
    Bissot                                                   3300 B
    Mallin                                                   3300 B
    Dellinger                                                3400 B
    Rajs                                                     3500 B
    Ladwig                                                   3600 B
    Dilly                                                    3600 B
    Chung                                                    3800 B
    Everett                                                  3900 B
    Bell                                                     4000 B
    Bull                                                     4100 B
    Lorentz                                                  4200 B
    Sarchand                                                 4200 B
    Whalen                                                   4400 B
    Austin                                                   4800 B
    Pataballa                                                4800 B
    Mourgos                                                  5800 B
    Ernst                                                    6000 C
    Fay                                                      6000 C
    Kumar                                                    6100 C
    Banda                                                    6200 C
    Johnson                                                  6200 C
    Ande                                                     6400 C
    Vollman                                                  6500 C
    Mavris                                                   6500 C
    Lee                                                      6800 C
    Popp                                                     6900 C
    Tuvault                                                  7000 C
    Sewall                                                   7000 C
    Grant                                                    7000 C
    Marvins                                                  7200 C
    Bates                                                    7300 C
    Smith                                                    7400 C
    Cambrault                                                7500 C
    Doran                                                    7500 C
    Sciarra                                                  7700 C
    Urman                                                    7800 C
    Kaufling                                                 7900 C
    Weiss                                                    8000 C
    Olsen                                                    8000 C
    Smith                                                    8000 C
    Chen                                                     8200 C
    Fripp                                                    8200 C
    Gietz                                                    8300 C
    Livingston                                               8400 C
    Taylor                                                   8600 C
    Hutton                                                   8800 C
    Hunold                                                   9000 C
    Faviet                                                   9000 C
    Hall                                                     9000 C
    McEwen                                                   9000 C
    Sully                                                    9500 C
    Greene                                                   9500 C
    Bernstein                                                9500 C
    Fox                                                      9600 C
    Baer                                                    10000 D
    Bloom                                                   10000 D
    King                                                    10000 D
    Tucker                                                  10000 D
    Vishney                                                 10500 D
    Zlotkey                                                 10500 D
    Cambrault                                               11000 D
    Raphaely                                                11000 D
    Abel                                                    11000 D
    Ozer                                                    11500 D
    Higgins                                                 12000 D
    Greenberg                                               12000 D
    Errazuriz                                               12000 D
    Hartstein                                               13000 D
    Partners                                                13500 D
    Russell                                                 14000 D
    De Haan                                                 17000 E
    Kochhar                                                 17000 E
    King                                                    24000 E
    
    已选择107行。
    

    溢出的情况

    在上文的BETWEEN AND语句中,需要保证e.salary在j中一直能够找到与之对应的值域,否则整行都不会输出出来

    为了让数据尽可能地可以输出出来,我们可以用(+)来避免

    SELECT e.last_name, e.department_id, d.department_name
    FROM employees e, departments d
    WHERE e.department_id(+) = d.department_id ;
    

    输出如下

    LAST_NAME                                          DEPARTMENT_ID DEPARTMENT_NAME
    -------------------------------------------------- ------------- ------------------------------------------------------------
    King                                                          90 Executive
    Kochhar                                                       90 Executive
    De Haan                                                       90 Executive
    Hunold                                                        60 IT
    Ernst                                                         60 IT
    Austin                                                        60 IT
    Pataballa                                                     60 IT
    Lorentz                                                       60 IT
    Greenberg                                                    100 Finance
    Faviet                                                       100 Finance
    Chen                                                         100 Finance
    Sciarra                                                      100 Finance
    Urman                                                        100 Finance
    Popp                                                         100 Finance
    Raphaely                                                      30 Purchasing
    Khoo                                                          30 Purchasing
    Baida                                                         30 Purchasing
    Tobias                                                        30 Purchasing
    Himuro                                                        30 Purchasing
    Colmenares                                                    30 Purchasing
    Weiss                                                         50 Shipping
    Fripp                                                         50 Shipping
    Kaufling                                                      50 Shipping
    Vollman                                                       50 Shipping
    Mourgos                                                       50 Shipping
    Nayer                                                         50 Shipping
    Mikkilineni                                                   50 Shipping
    Landry                                                        50 Shipping
    Markle                                                        50 Shipping
    Bissot                                                        50 Shipping
    Atkinson                                                      50 Shipping
    Marlow                                                        50 Shipping
    Olson                                                         50 Shipping
    Mallin                                                        50 Shipping
    Rogers                                                        50 Shipping
    Gee                                                           50 Shipping
    Philtanker                                                    50 Shipping
    Ladwig                                                        50 Shipping
    Stiles                                                        50 Shipping
    Seo                                                           50 Shipping
    Patel                                                         50 Shipping
    Rajs                                                          50 Shipping
    Davies                                                        50 Shipping
    Matos                                                         50 Shipping
    Vargas                                                        50 Shipping
    Russell                                                       80 Sales
    Partners                                                      80 Sales
    Errazuriz                                                     80 Sales
    Cambrault                                                     80 Sales
    Zlotkey                                                       80 Sales
    Tucker                                                        80 Sales
    Bernstein                                                     80 Sales
    Hall                                                          80 Sales
    Olsen                                                         80 Sales
    Cambrault                                                     80 Sales
    Tuvault                                                       80 Sales
    King                                                          80 Sales
    Sully                                                         80 Sales
    McEwen                                                        80 Sales
    Smith                                                         80 Sales
    Doran                                                         80 Sales
    Sewall                                                        80 Sales
    Vishney                                                       80 Sales
    Greene                                                        80 Sales
    Marvins                                                       80 Sales
    Lee                                                           80 Sales
    Ande                                                          80 Sales
    Banda                                                         80 Sales
    Ozer                                                          80 Sales
    Bloom                                                         80 Sales
    Fox                                                           80 Sales
    Smith                                                         80 Sales
    Bates                                                         80 Sales
    Kumar                                                         80 Sales
    Abel                                                          80 Sales
    Hutton                                                        80 Sales
    Taylor                                                        80 Sales
    Livingston                                                    80 Sales
    Johnson                                                       80 Sales
    Taylor                                                        50 Shipping
    Fleaur                                                        50 Shipping
    Sullivan                                                      50 Shipping
    Geoni                                                         50 Shipping
    Sarchand                                                      50 Shipping
    Bull                                                          50 Shipping
    Dellinger                                                     50 Shipping
    Cabrio                                                        50 Shipping
    Chung                                                         50 Shipping
    Dilly                                                         50 Shipping
    Gates                                                         50 Shipping
    Perkins                                                       50 Shipping
    Bell                                                          50 Shipping
    Everett                                                       50 Shipping
    McCain                                                        50 Shipping
    Jones                                                         50 Shipping
    Walsh                                                         50 Shipping
    Feeney                                                        50 Shipping
    OConnell                                                      50 Shipping
    Grant                                                         50 Shipping
    Whalen                                                        10 Administration
    Hartstein                                                     20 Marketing
    Fay                                                           20 Marketing
    Mavris                                                        40 Human Resources
    Baer                                                          70 Public Relations
    Higgins                                                      110 Accounting
    Gietz                                                        110 Accounting
                                                                     NOC
                                                                     Manufacturing
                                                                     Government Sales
                                                                     IT Support
                                                                     Benefits
                                                                     Shareholder Services
                                                                     Retail Sales
                                                                     Control And Credit
                                                                     Recruiting
                                                                     Operations
                                                                     Treasury
                                                                     Payroll
                                                                     Corporate Tax
                                                                     Construction
                                                                     Contracting
                                                                     IT Helpdesk
    
    已选择122行。
    

    将表与自身链接

    SELECT worker.last_name || ' works for ' 
    || manager.last_name
    FROM employees worker, employees manager
    WHERE worker.manager_id = manager.employee_id ;
    

    输出如下:

    WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME
    --------------------------------------------------------------------------------------------------------------------------
    Hartstein works for King
    Zlotkey works for King
    Cambrault works for King
    Errazuriz works for King
    Partners works for King
    Russell works for King
    Mourgos works for King
    Vollman works for King
    Kaufling works for King
    Fripp works for King
    Weiss works for King
    Raphaely works for King
    De Haan works for King
    Kochhar works for King
    Higgins works for Kochhar
    Baer works for Kochhar
    Mavris works for Kochhar
    Whalen works for Kochhar
    Greenberg works for Kochhar
    Hunold works for De Haan
    Lorentz works for Hunold
    Pataballa works for Hunold
    Austin works for Hunold
    Ernst works for Hunold
    Popp works for Greenberg
    Urman works for Greenberg
    Sciarra works for Greenberg
    Chen works for Greenberg
    Faviet works for Greenberg
    Colmenares works for Raphaely
    Himuro works for Raphaely
    Tobias works for Raphaely
    Baida works for Raphaely
    Khoo works for Raphaely
    Geoni works for Weiss
    Sullivan works for Weiss
    Fleaur works for Weiss
    Taylor works for Weiss
    Markle works for Weiss
    Landry works for Weiss
    Mikkilineni works for Weiss
    Nayer works for Weiss
    Cabrio works for Fripp
    Dellinger works for Fripp
    Bull works for Fripp
    Sarchand works for Fripp
    Olson works for Fripp
    Marlow works for Fripp
    Atkinson works for Fripp
    Bissot works for Fripp
    Perkins works for Kaufling
    Gates works for Kaufling
    Dilly works for Kaufling
    Chung works for Kaufling
    Philtanker works for Kaufling
    Gee works for Kaufling
    Rogers works for Kaufling
    Mallin works for Kaufling
    Jones works for Vollman
    McCain works for Vollman
    Everett works for Vollman
    Bell works for Vollman
    Patel works for Vollman
    Seo works for Vollman
    Stiles works for Vollman
    Ladwig works for Vollman
    Grant works for Mourgos
    OConnell works for Mourgos
    Feeney works for Mourgos
    Walsh works for Mourgos
    Vargas works for Mourgos
    Matos works for Mourgos
    Davies works for Mourgos
    Rajs works for Mourgos
    Tuvault works for Russell
    Cambrault works for Russell
    Olsen works for Russell
    Hall works for Russell
    Bernstein works for Russell
    Tucker works for Russell
    Sewall works for Partners
    Doran works for Partners
    Smith works for Partners
    McEwen works for Partners
    Sully works for Partners
    King works for Partners
    Banda works for Errazuriz
    Ande works for Errazuriz
    Lee works for Errazuriz
    Marvins works for Errazuriz
    Greene works for Errazuriz
    Vishney works for Errazuriz
    Kumar works for Cambrault
    Bates works for Cambrault
    Smith works for Cambrault
    Fox works for Cambrault
    Bloom works for Cambrault
    Ozer works for Cambrault
    Johnson works for Zlotkey
    Grant works for Zlotkey
    Livingston works for Zlotkey
    Taylor works for Zlotkey
    Hutton works for Zlotkey
    Abel works for Zlotkey
    Fay works for Hartstein
    Gietz works for Higgins
    
    已选择106行。
    
  • 相关阅读:
    MQTT Retained消息和LWT和Keep Alive(转)
    ant design 1.x中走马灯记录
    通过Array构造指定长度的数组
    Unicode编码与中文的相互转换
    在谷歌控制台调试代码如何换行
    辅助工具
    vue中扩展函数,除了原有的事件中传的参数增加自己传的参数
    使用allparis正交法,生成测试用例
    vue 字写的tab切换两个列表,在IOS上出现列表数据错乱的问题。
    Redis中的原子操作(2)redis中使用Lua脚本保证命令原子性
  • 原文地址:https://www.cnblogs.com/alphainf/p/16793848.html
Copyright © 2020-2023  润新知