Oracle SQL 核心
2023-09-14 09:13:28 时间
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> 相关文章
- oracle SQL Develop导出数据库中的表格数据到excel
- 在 linux 上运行 oracle sql脚本
- 《oracle每日一练》免安装Oracle客户端使用PL/SQL
- 《oracle每天一练》Oracle冷备份与数据恢复
- ORACLE关于锁表查询的部分SQL
- Oracle中查询某字段不为空或者为空的SQL语句怎么写
- [SQL] sql server中如何查看执行效率不高的语句
- oracle 、mysql、 sql server使用记录
- PostgreSQL Oracle 兼容性之 - COMPOSE , UNISTR , DECOMPOSE
- Oracle查询用户所有表
- Oracle性能优化学习笔记之选择最有效率的表名顺序
- Sql Server中sql语句自动换行
- [转] Oracle数据库维护常用SQL语句集合
- Oracle与Sql Server复制表结构及数据
- 在64位机器上使用plSQL连接Oracle的问题(SQL*Net not properly installed)
- C# Oracle、Sql Server连接（增、删、改、查）
- Oracle与Sql Server复制表结构及数据
- Atitit.mysql oracle with as模式临时表模式 CTE 语句的使用,减少子查询的结构性 mssql sql server..
- one command 一键收集 oracle 巡检信息（包括dbhc，awr reports）
- oracle 11g 自己主动调整
- PostgreSQL的学习心得和知识总结（一百二十一）|词法级自上而下完美实现Oracle数据库PL/SQL过程语言的 for in list 的实现方案
- 说明Oracle数据库逻辑备份和物理备份的方式。
- ORACLE冷备份及恢复
- Oracle数据库之表空间（tablespace）---面试再也不害怕系列之一
- Linux下的Oracle的数据库备份与恢复（emp和imp命令）
- Oracle 管理多租户环境之CDB管理
- 《收获，不止Oracle》读书笔记一
- Oracle数据库误truncate table的数据恢复案例