-
Oracle SQL 核心
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>
-
相关阅读:
随身wifi编译Openwrt的ImmortalWrt分支
1366 - Incorrect string value: ‘\xE5\xB9\xBF\xE5\x85\xB0...‘ for column编码错误
R实现动态条件相关模型与GARCH模型结合研究中美股市动态相关性（DCC-GARCH模型）
numpy生成0和1数组方法，从已有数组生成新数组方法、生成固定范围内数组、生成随机数组，绘制指定均值和标准差正态分布图、均匀分布图绘制
团队Git规范文档（操作规范及提交规范）
C#与Java计算俩个时间的差的方法
SAP-QM-UD取消程序
C#笔记：C#程序基本内容
给大家分享一份适合练手的软件测试实战项目
mysql源码分析——InnoDB的磁盘结构
- 原文地址：https://blog.csdn.net/u011868279/article/details/127135284
