首页 > 编程学习 > Oracle SQL 核心

Oracle SQL 核心

发布时间:2022/10/2 6:25:27

5个核心的SQL语句

SELECT 、INSERT、UPDATE、DELETE 、MERGE

1.SELECT 语句

SELECT <column list>
FROM           <source object list>
FROM           <left source object> <join type>
          JOIN <right source object> ON <on predicates>
WHERE      <where predicates>
GROUP BY   <group by expression(s)>
HAVING     <having predicates>
ORDER BY   <order by list>

例1 下订单超过4次的女顾客查询语句

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select c.customer_id,count(o.order_id) as orders_ct
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id
  5  where c.gender = 'F'
  6  group by c.customer_id
  7  having count(o.order_id) > 4
  8  order by orders_ct,c.customer_id
  9  /

CUSTOMER_ID  ORDERS_CT
----------- ----------
        146          5
        147          5

2 rows selected.

oe@orclpdb1:orclcdb> 

1.1 FROM 子句

FROM子句列出了查询数据的源对象。这个子句可以包含表、视图、物化视图、分区或子分区,或者你可以建立一个子查询生成子对象。

注意在处理联结语句时是按照下面的顺序来进行的:

  • 交叉联结
  • 内联结
  • 外联结

仅包含FROM子句的查询语句

oe@orclpdb1:orclcdb> select c.customer_id cust_id,o.order_id ord_id,c.gender
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id;

   CUST_ID     ORD_ID G
---------- ---------- -
       101       2458 M
       101       2447 M
       101       2413 M
       101       2430 M
       102       2397 M
       102       2432 M
       102       2414 M
       102       2431 M
       103       2454 F
       103       2433 F
       103       2415 F
       103       2437 F
       104       2354 F
       104       2355 F
       104       2416 F
       104       2438 F
       105       2358 F
       105       2356 F
       105       2417 F
       105       2439 F
       106       2381 M
       106       2359 M
       106       2418 M
       106       2441 M
       107       2440 F
       107       2360 F
       107       2419 F
       107       2442 F
       108       2357 M
       108       2361 M
       108       2420 M
       108       2443 M
       109       2394 M
       109       2362 M
       109       2421 M
       109       2444 M
       116       2436 M
       116       2369 M
       116       2428 M
       116       2453 M
       117       2446 M
       117       2370 M
       117       2429 M
       117       2456 M
       118       2371 M
       118       2457 M
       119       2372 M
       120       2373 M
       121       2374 M
       122       2375 M
       141       2377 M
       143       2380 M
       144       2435 M
       144       2363 M
       144       2382 M
       144       2422 M
       144       2445 M
       145       2455 M
       145       2364 M
       145       2383 M
       145       2423 M
       145       2448 M
       146       2379 F
       146       2365 F
       146       2384 F
       146       2424 F
       146       2449 F
       147       2396 F
       147       2366 F
       147       2385 F
       147       2425 F
       147       2450 F
       148       2406 M
       148       2367 M
       148       2386 M
       148       2426 M
       148       2451 M
       149       2434 M
       149       2368 M
       149       2387 M
       149       2427 M
       149       2452 M
       150       2388 M
       151       2389 M
       152       2390 M
       153       2391 M
       154       2392 F
       155       2393 M
       156       2395 F
       157       2398 M
       158       2399 M
       159       2400 M
       160       2401 M
       161       2402 M
       162       2403 M
       164       2405 M
       165       2407 M
       166       2408 F
       167       2409 M
       169       2411 F
       170       2412 M
       123       2376 F
       142       2378 M
       163       2404 M
       168       2410 M

105 rows selected.

oe@orclpdb1:orclcdb> 

1.2 WHERE子句

目的是限制或者减少结果集。

1.3GROUP BY 子句

group by子句将执行完FROM和WHERE 子句后得到的经过筛选后的结果集进行聚合。查询出来的结果按照GROUP BY子句中列出的表达式进行分组,为每个分组得出一行汇总结果。

GROUP BY子句中还可以包含两个附加的运算:ROLLUP和CUBE。ROLLUP运算用来产生部分求和值,CUBE运算用来求得交互分类值。

记住一点:GROUP BY 子句并不确保排序结果数据。如果你需要结果按照特定的顺序排序,则必须指定ORDER BY 子句。

oe@orclpdb1:orclcdb> select c.customer_id,count(o.order_id) as orders_ct
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id
  5  where c.gender = 'F'
  6  group by c.customer_id;

CUSTOMER_ID  ORDERS_CT
----------- ----------
        107          4
        166          1
        105          4
        104          4
        103          4
        156          1
        169          1
        146          5
        147          5
        154          1
        123          1

11 rows selected.

oe@orclpdb1:orclcdb> 

1.4 HAVING 子句

HAVING 子句将分组汇总后的查询结果限定为只有该子句中的条件为真的数据行。

1.5 SELECT 列表

展现SELECT 列表各种可能情况的查询实例


hr@orclpdb1:orclcdb> conn oe/oe@ORCLPDB1
Connected.
oe@orclpdb1:orclcdb> select c.customer_id,c.cust_first_name||' '||c.cust_last_name,
  2  (select e.last_name from hr.employees e where e.employee_id = c.account_mgr_id) acct_mgr
  3  from oe.customers c;

CUSTOMER_ID C.CUST_FIRST_NAME||''||C.CUST_LAST_NAME   ACCT_MGR
----------- ----------------------------------------- -------------------------
        188 Charlotte Buckley                         Zlotkey
        189 Gena Harris                               Zlotkey
        190 Gena Curtis                               Zlotkey
        191 Maureen Sanders                           Zlotkey
        192 Sean Stockwell                            Zlotkey
        193 Harry dean Kinski                         Zlotkey
        194 Kathleen Garcia                           Zlotkey
        195 Sean Olin                                 Zlotkey
        196 Gerard Dench                              Zlotkey
        197 Gerard Altman                             Zlotkey
        198 Maureen de Funes                          Zlotkey
        199 Clint Chapman                             Zlotkey
        200 Clint Gielgud                             Zlotkey
        201 Eric Prashant                             Zlotkey
        202 Ingrid Welles                             Zlotkey
        203 Ingrid Rampling                           Zlotkey
        204 Cliff Puri                                Zlotkey
        205 Emily Pollack                             Zlotkey
        206 Fritz Hackman                             Zlotkey
        207 Cybill Laughton                           Zlotkey
        208 Cyndi Griem                               Zlotkey
        209 Cyndi Collins                             Zlotkey
        210 Cybill Clapton                            Zlotkey
        212 Luchino Falk                              Zlotkey
        213 Luchino Bradford                          Zlotkey
        214 Robin Danson                              Zlotkey
        215 Orson Perkins                             Zlotkey
        217 Bryan Huston                              Zlotkey
        218 Bryan Dvrrie                              Zlotkey
        219 Ajay Sen                                  Zlotkey
        221 Carol Bradford                            Zlotkey
        222 Cary Stockwell                            Zlotkey
        223 Cary Olin                                 Zlotkey
        224 Clara Krige                               Zlotkey
        226 Ajay Andrews                              Zlotkey
        227 Kathy Prashant                            Zlotkey
        228 Graham Neeson                             Zlotkey
        229 Ian Chapman                               Zlotkey
        231 Danny Rourke                              Russell
        232 Donald Hunter                             Russell
        277 Don Siegel                                Zlotkey
        278 Gvtz Bradford                             Zlotkey
        279 Holly Kurosawa                            Zlotkey
        280 Rob MacLaine                              Zlotkey
        281 Don Barkin                                Zlotkey
        282 Kurt Danson                               Russell
        283 Kurt Heard                                Russell
        308 Glenda Dunaway                            Cambrault
        309 Glenda Bates                              Cambrault
        323 Goetz Falk                                Cambrault
        326 Hal Olin                                  Errazuriz
        327 Hannah Kanth                              Errazuriz
        328 Hannah Field                              Errazuriz
        333 Margret Powell                            Errazuriz
        334 Harry Mean Taylor                         Errazuriz
        335 Margrit Garner                            Errazuriz
        337 Maria Warden                              Errazuriz
        339 Marilou Landis                            Errazuriz
        361 Marilou Chapman                           Errazuriz
        363 Kathy Lambert                             Errazuriz
        360 Helmut Capshaw                            Cambrault
        342 Marlon Laughton                           Errazuriz
        343 Keir Chandar                              Errazuriz
        345 Keir Weaver                               Errazuriz
        346 Marlon Clapton                            Errazuriz
        347 Kelly Quinlan                             Errazuriz
        349 Ken Glenn                                 Errazuriz
        350 Ken Chopra                                Errazuriz
        351 Ken Wenders                               Errazuriz
        380 Meryl Holden                              Cambrault
        447 Richard Coppola                           Errazuriz
        448 Richard Winters                           Errazuriz
        450 Rick Lyon                                 Errazuriz
        451 Ridley Hackman                            Errazuriz
        452 Ridley Coyote                             Errazuriz
        454 Rob Russell                               Cambrault
        458 Robert de Niro                            Cambrault
        466 Rodolfo Hershey                           Cambrault
        467 Rodolfo Dench                             Cambrault
        468 Rodolfo Altman                            Cambrault
        233 Graham Spielberg                          Russell
        234 Dan Roberts                               Russell
        235 Edward Oates                              Russell
        236 Edward Julius                             Russell
        237 Farrah Quinlan                            Russell
        238 Farrah Lange                              Russell
        239 Hal Stockwell                             Russell
        240 Malcolm Kanth                             Russell
        241 Malcolm Broderick                         Russell
        242 Mary Lemmon                               Russell
        243 Mary Collins                              Russell
        244 Matt Gueney                               Russell
        245 Max von Sydow                             Russell
        246 Max Schell                                Russell
        247 Cynda Whitcraft                           Russell
        248 Donald Minnelli                           Russell
        249 Hannah Broderick                          Russell
        250 Dan Williams                              Russell
        251 Raul Wilder                               Russell
        252 Shah Rukh Field                           Russell
        253 Sally Bogart                              Russell
        254 Bruce Bates                               Russell
        256 Ben de Niro                               Russell
        257 Emmet Walken                              Russell
        258 Ellen Palin                               Russell
        259 Denholm von Sydow                         Russell
        261 Emmet Garcia                              Zlotkey
        262 Fred Reynolds                             Zlotkey
        263 Fred Lithgow                              Zlotkey
        265 Irene Laughton                            Zlotkey
        266 Prem Cardinale                            Zlotkey
        267 Prem Walken                               Russell
        268 Kyle Schneider                            Russell
        270 Meg Derek                                 Russell
        271 Shelley Peckinpah                         Russell
        272 Prem Garcia                               Russell
        274 Bob McCarthy                              Zlotkey
        275 Dom McQueen                               Zlotkey
        276 Dom Hoskins                               Zlotkey
        101 Constantin Welles                         Russell
        102 Harrison Pacino                           Russell
        103 Manisha Taylor                            Russell
        104 Harrison Sutherland                       Russell
        105 Matthias MacGraw                          Russell
        106 Matthias Hannah                           Russell
        107 Matthias Cruise                           Russell
        108 Meenakshi Mason                           Russell
        109 Christian Cage                            Russell
        110 Charlie Sutherland                        Russell
        111 Charlie Pacino                            Russell
        112 Guillaume Jackson                         Russell
        113 Daniel Costner                            Russell
        114 Dianne Derek                              Russell
        115 Geraldine Schneider                       Russell
        116 Geraldine Martin                          Russell
        117 Guillaume Edwards                         Russell
        118 Maurice Mahoney                           Russell
        119 Maurice Hasan                             Russell
        120 Diane Higgins                             Russell
        121 Dianne Sen                                Russell
        122 Maurice Daltrey                           Russell
        124 Diane Mason                               Russell
        125 Dianne Andrews                            Russell
        126 Charles Field                             Russell
        128 Isabella Reed                             Russell
        129 Louis Jackson                             Russell
        130 Louis Edwards                             Russell
        131 Doris Dutt                                Russell
        133 Kristin Malden                            Russell
        134 Sissy Puri                                Russell
        135 Doris Bel Geddes                          Russell
        136 Sissy Warden                              Russell
        138 Mani Fonda                                Russell
        139 Placido Kubrick                           Russell
        140 Claudia Kurosawa                          Russell
        141 Maximilian Henner                         Russell
        143 Sachin Neeson                             Russell
        144 Sivaji Landis                             Russell
        145 Mammutti Pacino                           Russell
        146 Elia Fawcett                              Russell
        147 Ishwarya Roberts                          Russell
        148 Gustav Steenburgen                        Russell
        149 Markus Rampling                           Russell
        150 Goldie Slater                             Russell
        151 Divine Aykroyd                            Russell
        152 Dieter Matthau                            Russell
        153 Divine Sheen                              Russell
        154 Frederic Grodin                           Russell
        155 Frederico Romero                          Russell
        156 Goldie Montand                            Russell
        157 Sidney Capshaw                            Russell
        158 Frederico Lyon                            Russell
        159 Eddie Boyer                               Russell
        160 Eddie Stern                               Russell
        161 Ernest Weaver                             Russell
        162 Ernest George                             Russell
        164 Charlotte Kazan                           Russell
        165 Charlotte Fonda                           Russell
        166 Dheeraj Alexander                         Russell
        167 Gerard Hershey                            Russell
        169 Dheeraj Davis                             Russell
        170 Harry Dean Fonda                          Russell
        171 Hema Powell                               Russell
        172 Harry Mean Peckinpah                      Russell
        174 Blake Seignier                            Zlotkey
        175 Claude Powell                             Zlotkey
        176 Faye Glenn                                Zlotkey
        177 Gerhard Seignier                          Zlotkey
        179 Harry dean Forrest                        Zlotkey
        180 Harry dean Cage                           Zlotkey
        181 Lauren Hershey                            Zlotkey
        183 Lauren Altman                             Zlotkey
        184 Mary Beth Roberts                         Zlotkey
        185 Matthew Wright                            Zlotkey
        186 Meena Alexander                           Zlotkey
        767 Klaus Maria Russell                       Errazuriz
        771 Kris de Niro                              Errazuriz
        826 Alain Barkin                              Errazuriz
        830 Albert Dutt                               Errazuriz
        850 Amanda Finney                             Errazuriz
        905 Billy Hershey                             Cambrault
        911 Bo Dickinson                              Cambrault
        919 Brooke Michalkow                          Cambrault
        924 Bruno Montand                             Cambrault
        930 Buster Jackson                            Cambrault
        980 Daniel Loren                              Cambrault
        473 Rolf Ashby                                Cambrault
        474 Romy Sharif                               Cambrault
        475 Romy McCarthy                             Cambrault
        476 Rosanne Hopkins                           Cambrault
        477 Rosanne Douglas                           Cambrault
        478 Rosanne Baldwin                           Cambrault
        479 Roxanne Shepherd                          Cambrault
        480 Roxanne Michalkow                         Cambrault
        481 Roy Hulce                                 Cambrault
        482 Roy Dunaway                               Cambrault
        483 Roy Bates                                 Cambrault
        487 Rufus Dvrrie                              Cambrault
        488 Rufus Belushi                             Cambrault
        492 Sally Edwards                             Cambrault
        496 Scott Jordan                              Cambrault
        605 Shammi Pacino                             Cambrault
        606 Sharmila Kazan                            Cambrault
        609 Shelley Taylor                            Cambrault
        615 Shyam Plummer                             Cambrault
        621 Silk Kurosawa                             Cambrault
        712 M. Emmet Stockwell                        Errazuriz
        713 M. Emmet Olin                             Errazuriz
        717 Mammutti Sutherland                       Errazuriz
        719 Mani Kazan                                Errazuriz
        721 Mani Buckley                              Errazuriz
        729 Margaux Krige                             Errazuriz
        731 Margaux Capshaw                           Errazuriz
        754 Kevin Goodman                             Errazuriz
        756 Kevin Wilder                              Errazuriz
        757 Kiefer Reynolds                           Errazuriz
        766 Klaus Young                               Errazuriz
        768 Klaus Maria MacLaine                      Errazuriz
        769 Kris Harris                               Errazuriz
        770 Kris Curtis                               Errazuriz
        772 Kristin Savage                            Errazuriz
        782 Laurence Seignier                         Zlotkey
        825 Alain Dreyfuss                            Errazuriz
        827 Alain Siegel                              Errazuriz
        828 Alan Minnelli                             Errazuriz
        829 Alan Hunter                               Errazuriz
        831 Albert Bel Geddes                         Errazuriz
        832 Albert Spacek                             Errazuriz
        833 Alec Moranis                              Errazuriz
        834 Alec Idle                                 Errazuriz
        835 Alexander Eastwood                        Errazuriz
        836 Alexander Berenger                        Errazuriz
        837 Alexander Stanton                         Errazuriz
        838 Alfred Nicholson                          Errazuriz
        839 Alfred Johnson                            Errazuriz
        840 Ali Elliott                               Errazuriz
        841 Ali Boyer                                 Errazuriz
        842 Ali Stern                                 Errazuriz
        843 Alice Oates                               Errazuriz
        844 Alice Julius                              Errazuriz
        845 Ally Fawcett                              Errazuriz
        846 Ally Brando                               Errazuriz
        847 Ally Streep                               Errazuriz
        848 Alonso Olmos                              Errazuriz
        849 Alonso Kaurusmdki                         Errazuriz
        851 Amanda Brown                              Errazuriz
        852 Amanda Tanner                             Errazuriz
        853 Amrish Palin                              Errazuriz
        906 Billy Dench                               Cambrault
        909 Blake Mastroianni                         Cambrault
        912 Bo Ashby                                  Cambrault
        913 Bob Sharif                                Cambrault
        916 Brian Douglas                             Cambrault
        917 Brian Baldwin                             Cambrault
        920 Bruce Hulce                               Cambrault
        921 Bruce Dunaway                             Cambrault
        923 Bruno Slater                              Cambrault
        927 Bryan Belushi                             Cambrault
        928 Burt Spielberg                            Cambrault
        929 Burt Neeson                               Cambrault
        931 Buster Edwards                            Cambrault
        932 Buster Bogart                             Cambrault
        934 C. Thomas Nolte                           Cambrault
        981 Daniel Gueney                             Cambrault
        123 Elizabeth Brown                           Russell
        127 Charles Broderick                         Russell
        132 Doris Spacek                              Russell
        137 Elia Brando                               Russell
        142 Sachin Spielberg                          Russell
        163 Ernest Chandar                            Russell
        168 Hema Voight                               Russell
        173 Kathleen Walken                           Russell
        178 Grace Belushi                             Zlotkey
        182 Lauren Dench                              Zlotkey
        187 Grace Dvrrie                              Zlotkey
        211 Luchino Jordan                            Zlotkey
        216 Orson Koirala                             Zlotkey
        220 Carol Jordan                              Zlotkey
        225 Clara Ganesan                             Zlotkey
        230 Danny Wright                              Zlotkey
        255 Brooke Shepherd                           Russell
        260 Ellen Khan                                Zlotkey
        264 George Adjani                             Zlotkey
        269 Kyle Martin                               Russell
        273 Bo Hitchcock                              Russell
        341 Keir George                               Errazuriz
        344 Marlon Godard                             Cambrault
        348 Kelly Lange                               Errazuriz
        352 Kenneth Redford                           Errazuriz
        378 Meg Sen                                   Zlotkey
        449 Rick Romero                               Errazuriz
        453 Ridley Young                              Cambrault
        463 Robin Adjani                              Cambrault
        470 Roger Mastroianni                         Cambrault
        607 Sharmila Fonda                            Cambrault
        627 Sivaji Gielgud                            Cambrault
        715 Malcolm Field                             Errazuriz
        727 Margaret Ustinov                          Errazuriz
        755 Kevin Cleveland                           Errazuriz

319 rows selected.

oe@orclpdb1:orclcdb> 

1.6 ORDER BY子句

ORDER BY 子句用来将查询最终返回的结果集排序。

示例查询的最终输出

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> select c.customer_id,count(o.order_id) as orders_ct
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id
  5  where c.gender = 'F'
  6  group by c.customer_id
  7  having count(o.order_id) > 4
  8  order by orders_ct,c.customer_id
  9  /

CUSTOMER_ID  ORDERS_CT
----------- ----------
        146          5
        147          5

2 rows selected.

oe@orclpdb1:orclcdb> 

2.INSERT语句

2.1 单表插入

单表插入

oe@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> insert into hr.jobs(job_id,job_title,min_salary, max_salary)
  2  values ('IT_PM', 'Project Manager', 5000, 11000);

1 row created.

scott@orclpdb1:orclcdb> insert into scott.bonus(ename,job,sal)
  2  select ename,job,sal * .10
  3  from scott.emp;

14 rows created.

scott@orclpdb1:orclcdb> 
scott@orclpdb1:orclcdb> rollback;

Rollback complete.

scott@orclpdb1:orclcdb>

2.2多表插入

oe@orclpdb1:orclcdb> create table small_customers as select customer_id,sum(order_total) sum_orders from oe.orders where 1=0 group by customer_id;

Table created.

oe@orclpdb1:orclcdb> select * from small_customers;

no rows selected
oe@orclpdb1:orclcdb> desc small_customers;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 CUSTOMER_ID                                           NOT NULL NUMBER(6)
 SUM_ORDERS                                                     NUMBER

oe@orclpdb1:orclcdb> create table medium_customers as select customer_id,sum(order_total) sum_orders from oe.orders where 1=0 group by customer_id;

Table created.

oe@orclpdb1:orclcdb> create table large_customers as select customer_id,sum(order_total) sum_orders from oe.orders where 1=0 group by customer_id;

Table created.

oe@orclpdb1:orclcdb> 

oe@orclpdb1:orclcdb> 
oe@orclpdb1:orclcdb> insert all
  2  when sum_orders < 10000 then
  3  into small_customers
  4  when sum_orders >= 10000 and sum_orders < 100000 then
  5  into medium_customers
  6  else
  7  into large_customers
  8  select customer_id,sum(order_total) sum_orders
  9  from oe.orders
 10  group by customer_id;

47 rows created.

oe@orclpdb1:orclcdb> select * from small_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
        120        416
        121       4797
        152     7616.8
        157     7110.3
        160      969.2
        161        600
        162        220
        163        510
        164       1233
        165       2519
        166        309
        167         48

12 rows selected.

oe@orclpdb1:orclcdb> select * from medium_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
        102    69211.4
        103    20591.4
        105    61376.5
        106    36199.5
        116      32307
        119    16447.2
        123    11006.2
        141    38017.8
        142    25691.3
        143    27132.6
        145    71717.9
        146    88462.6
        151      17620
        153    48070.6
        154      26632
        155    23431.9
        156      68501
        158    25270.3
        159    69286.4
        168      45175
        169    15760.5
        170      66816

22 rows selected.

oe@orclpdb1:orclcdb> select * from large_customers;

CUSTOMER_ID SUM_ORDERS
----------- ----------
        101   190395.1
        104   146605.5
        107   155613.2
        108   213399.7
        109   265255.6
        117   157808.7
        118   100991.8
        122   103834.4
        144   160284.6
        147   371278.2
        148   185700.5
        149   403119.7
        150   282694.3

13 rows selected.

oe@orclpdb1:orclcdb> 

3.UPDATE 语句

UPDATE \SET \WHERE

UPDATE子句用来指定要更新的表

SET子句用来指明哪些列改变了以及调整的值

WHERE 子句用来按条件筛选需要更新的行.WHERE子句可选的

hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> -- create duplicate employees tables
hr@orclpdb1:orclcdb> create table employees1 as select * from employees;

Table created.

hr@orclpdb1:orclcdb> create table employees2 as select * from employees;

Table created.

hr@orclpdb1:orclcdb> -- add a primary key
hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> alter table employees2 add constraint emp2_emp_id_pk primary key (employee_id);

Table altered.

hr@orclpdb1:orclcdb> -- retrieve list of employees in department 90
hr@orclpdb1:orclcdb> select employee_id, last_name,salary
  2  from employees1 where department_id = 90;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000
        101 Kochhar                        17000
        102 De Haan                        17000

3 rows selected.

hr@orclpdb1:orclcdb> -- Exp1: Update a single column value using an expression
hr@orclpdb1:orclcdb> update employee2
  2  

hr@orclpdb1:orclcdb> update employees2
  2  set salary = salary * 1.10 -- increase salary by 10%
  3  where department_id = 90;

3 rows updated.

hr@orclpdb1:orclcdb> commit;

Commit complete.

hr@orclpdb1:orclcdb> select employee_id,last_name,salary
  2  from employees2 where department_id = 90;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           26400
        101 Kochhar                        18700
        102 De Haan                        18700

3 rows selected.

hr@orclpdb1:orclcdb> -- Exp2 : Update a single column value using a subquery
hr@orclpdb1:orclcdb> update employees1
  2  set salary = (select employees2.salary
  3                  from employees2
  4                where employees2.employee_id = employees1.employee_id
  5                  and employees1.salary != employees2.salary)
  6  where department_id = 90;

3 rows updated.

hr@orclpdb1:orclcdb> select employee_id,last_name,salary
  2  from employees1 where department_id = 90;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           26400
        101 Kochhar                        18700
        102 De Haan                        18700

3 rows selected.

hr@orclpdb1:orclcdb> rollback;

Rollback complete.

hr@orclpdb1:orclcdb> -- Exp 4 : Update a table using a select statement
hr@orclpdb1:orclcdb> -- to define the table and column values
hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> update (select e1.salary,e2.salary new_sal
  2                            from employees1 e1, employees2 e2
  3                         where e1.employee_id = e2.employee_id
  4                           and e1.department_id = 90)
  5  set salary = new_sal;

3 rows updated.

hr@orclpdb1:orclcdb> select employee_id,last_name,salary,commission_pct
  2  from employees1 where department_id = 90;

EMPLOYEE_ID LAST_NAME                     SALARY COMMISSION_PCT
----------- ------------------------- ---------- --------------
        100 King                           26400
        101 Kochhar                        18700
        102 De Haan                        18700

3 rows selected.

hr@orclpdb1:orclcdb> rollback
  2  ;

Rollback complete.
hr@orclpdb1:orclcdb> -- Exp 5: Update multiple columns using a subquery
hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> update employees1
  2  set (salary, commission_pct) = (select employees2.salary, .10 comm_pct
  3                  from employees2
  4               where employees2.employee_id = employees1.employee_id
  5                 and employees1.salary != employees2.salary)
  6  where department_id = 90;

3 rows updated.

hr@orclpdb1:orclcdb> select employee_id,last_name,salary,commission_pct
  2  from employees1 where department_id = 90;

EMPLOYEE_ID LAST_NAME                     SALARY COMMISSION_PCT
----------- ------------------------- ---------- --------------
        100 King                           26400             .1
        101 Kochhar                        18700             .1
        102 De Haan                        18700             .1

3 rows selected.

hr@orclpdb1:orclcdb> rollback;

Rollback complete.

hr@orclpdb1:orclcdb> 

4.DELETE语句

DELETE语句用来从表中移除数据行

DELETE \FROM\WHERE

hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  from employees2
  3  where department_id = 90;

EMPLOYEE_ID DEPARTMENT_ID LAST_NAME                     SALARY
----------- ------------- ------------------------- ----------
        100            90 King                           26400
        101            90 Kochhar                        18700
        102            90 De Haan                        18700

3 rows selected.

hr@orclpdb1:orclcdb> -- Exp 1: Delete rows from specified table using
hr@orclpdb1:orclcdb> -- a filter condition in the WHERE clause
hr@orclpdb1:orclcdb> delete from employees2
  2  where department_id = 90;

3 rows deleted.

hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  from employees2
  3  where department_id = 90;

no rows selected

hr@orclpdb1:orclcdb> rollback;

Rollback complete.

hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  from employees2
  3  where department_id = 90;

EMPLOYEE_ID DEPARTMENT_ID LAST_NAME                     SALARY
----------- ------------- ------------------------- ----------
        100            90 King                           26400
        101            90 Kochhar                        18700
        102            90 De Haan                        18700

3 rows selected.

hr@orclpdb1:orclcdb> -- Exp 2: Delete rows using a subquery in the FROM clause
hr@orclpdb1:orclcdb> delete from (select * from employees2 where department_id = 90);

3 rows deleted.

hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  
hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  from employees2
  3  where department_id = 90;

no rows selected

hr@orclpdb1:orclcdb> rollback;

Rollback complete.

hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  from employees2
  3  where department_id = 90;

EMPLOYEE_ID DEPARTMENT_ID LAST_NAME                     SALARY
----------- ------------- ------------------------- ----------
        100            90 King                           26400
        101            90 Kochhar                        18700
        102            90 De Haan                        18700

3 rows selected.

hr@orclpdb1:orclcdb> -- Exp 3 : Delete rows from specified table using
hr@orclpdb1:orclcdb> -- a subquery in the WHERE clause
hr@orclpdb1:orclcdb> delete from employees2
  2  where  department_id in (select department_id
  3                       from departments
  4                    where department_name = 'Executive');

3 rows deleted.

hr@orclpdb1:orclcdb> select employee_id,department_id,last_name,salary
  2  from employees2
  3  where department_id = 90;

no rows selected

hr@orclpdb1:orclcdb> rollback;

Rollback complete.

hr@orclpdb1:orclcdb> 

5.MERGE语句

MERGE语句可以按条件获取要更新或插入到表中的数据行,然后从1个或多个源头对表进行更新或向表中插入行。

MERGE语句的语法

MERGE <hint>
INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

hr@orclpdb1:orclcdb> 
hr@orclpdb1:orclcdb> create table dept60_bonuses
  2                  (employee_id   number,
  3                   bonus_amt     number);

Table created.

hr@orclpdb1:orclcdb> insert into dept60_bonuses values(103, 0);

1 row created.

hr@orclpdb1:orclcdb> insert into dept60_bonuses values(104, 100);

1 row created.

hr@orclpdb1:orclcdb> insert into dept60_bonuses values(105, 0);

1 row created.

hr@orclpdb1:orclcdb> commit;

Commit complete.

hr@orclpdb1:orclcdb> select employee_id,last_name,salary
  2  from employees
  3  where department_id = 60;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        103 Hunold                          9000
        104 Ernst                           6000
        105 Austin                          4800
        106 Pataballa                       4800
        107 Lorentz                         4200

5 rows selected.

hr@orclpdb1:orclcdb> select * from dept60_bonuses;

EMPLOYEE_ID  BONUS_AMT
----------- ----------
        103          0
        104        100
        105          0

3 rows selected.

hr@orclpdb1:orclcdb> merge into dept60_bonuses b
  2                  using (
  3                    select employee_id,salary,department_id
  4                    from employees
  5                    where department_id = 60) e
  6                  on (b.employee_id = e.employee_id)
  7                  when matched then
  8                    update set b.bonus_amt = e.salary * 0.2
  9                     where b.bonus_amt = 0
 10                   delete where (e.salary > 7500)
 11                  when not matched then
 12                    insert (b.employee_id,b.bonus_amt)
 13                    values(e.employee_id, e.salary * 0.1)
 14                    where (e.salary < 7500);

4 rows merged.

hr@orclpdb1:orclcdb> select * from dept60_bonuses;

EMPLOYEE_ID  BONUS_AMT
----------- ----------
        104        100
        105        960
        107        420
        106        480

4 rows selected.

hr@orclpdb1:orclcdb> rollback;

Rollback complete.

hr@orclpdb1:orclcdb> 
Copyright © 2010-2022 kler.cn 版权所有 |关于我们| 联系方式|豫ICP备15888888号