νλ²νΈ
μνμ€ κ°μ²΄ μκ΄X
νμ¬ ν μ΄λΈμ νλ²νΈλ₯Ό κ°μ Έμ€λ μν
ν μ΄λΈμ μ μ₯λ κ°μ΄ μλλΌ, select μ€ν μ λμ μΌλ‘ κ³μ°λμ΄ λ§λ€μ΄μ§λ€.(μ€μ)
fromμ μ΄ μ€νλ λ κ° λ μ½λμ rownumμ ν λΉνλ€.(λ§€μ° μ€μ)
whereμ μ΄ μ€νλ λ μν©μ λ°λΌ rownumμ΄ μ¬κ³μ°λλ€.(λ§€μ° μ€μ) > fromμ μμ λ§λ€μ΄μ§ rownumμ whereμ μ΄ μ€νλ λ λ³κ²½λ μ μλ€.
rownumμ΄ 1μ΄ ν¬ν¨λκ³ , μμ΄λ ννκ° μλλ©΄ μ¬μ©ν μ μλ€.
λ΄κ° μνλ μμλλ‘ μ λ ¬ ν > rownumμ ν λΉνλ λ°©λ² > μλΈμΏΌλ¦¬ μ¬μ©(μ€μ)
νμ΄μ§ > λλ μ 보기 > ν λ²μ 20λͺ μ© λ³΄κΈ° + μ΄λ¦μμΌλ‘ μ λ ¬(곡μμ²λΌ μ¨)
SELECT * FROM tbladdressbook; -- 2,000 건
-- 1.
SELECT * FROM tbladdressbook ORDER BY name ASC;
-- 2. μ΄ λμ rownumμ΄ νμνλ€.
SELECT a.*, rownum FROM (
SELECT *
FROM tbladdressbook
ORDER BY name ASC) a;
-- 3. rownumμ 쑰건 μ¬μ© > ν λ² λ μλΈμΏΌλ¦¬
SELECT * FROM (SELECT a.*, rownum FROM (
SELECT *
FROM tbladdressbook
ORDER BY name ASC) a);
κ°μ₯ λ°κΉ₯μͺ½ μλΈμΏΌλ¦¬λ λ·°λ‘ λ§λ€μ΄μ μ¬μ©ν μ μλ€.
μ°λ¦¬κ° νλ νλ > μκ°μμΌλ‘ κΈ°μ΅(μ€μ)
λ‘κ·ΈμΈ μ§ν(μ μ) > νΈλμμ
μ΄ μμλ¨
νΈλμμ
> λͺ¨λ λͺ
λ Ήμ΄ (X) > INSERT, UPDATE, DELETE λͺ
λ Ήμ΄λ§ νΈλμμ
μ ν¬ν¨λλ€.
INSERT, UPDATE, DELETE μμ
> μ€λΌν΄ μ μ©(x), μμ λ©λͺ¨λ¦¬ μ μ©(o)
prior: λΆλͺ¨ λ μ½λ μ°Έμ‘° > μ§μ μμ¬
connect_by_root: μ΅μμ λ μ½λ μ°Έμ‘° > νμ¬μ₯
connect_by_isleaf: λ§λ¨ λ
Έλ
κ³μΈ΅ν 쿼리μμμ μ λ ¬ - order siblings by
join νκΈ°
μμ ν¨μ + order by
μμ ν¨μ + partition by + order by > μμ ν¨μ + group by > κ·Έλ£Ήλ³ μμ ꡬνκΈ°
/*
[WITH <Sub Query>]
SELECT column_list
FROM table_name
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC|DESC]]
from μ μλΈμΏΌλ¦¬ > μ΄λ¦ μλ ν
μ΄λΈ > 1νμ© ν
μ΄λΈ
from ν
μ΄λΈ > μ΄λ¦ μλ ν
μ΄λΈ > Nνμ© ν
μ΄λΈ
with μ
- μΈλΌμΈλ·°(from μ μλΈμΏΌλ¦¬)μ μ΄λ¦μ λΆμ΄λ κΈ°μ
with μμν
μ΄λΈλͺ
as (μλΈ μΏΌλ¦¬)
select λ¬Έ
*/
SELECT * FROM (SELECT name, buseo, jikwi FROM tblinsa WHERE city = 'μμΈ');
WITH seoul AS (SELECT name, buseo, jikwi FROM tblinsa WHERE city = 'μμΈ')
SELECT * FROM seoul; -- 미리 μλ‘ μ¬λ €μ μ΄λ¦μ μ§μ΄λ κ². κ°λ
μ± ν₯μ
null value
nullμ μΉννλ ν¨μ
SELECT
name, nvl2(population, 'μΈκ΅¬μO', 'μΈκ΅¬μX') -- κ°μ΄ μμΌλ©΄ μΈκ΅¬μO, nullμ΄λ©΄ μΈκ΅¬μX
FROM tblcountry;