λΉλμ€ + μ₯λ₯΄ > μ‘°μΈ
μ‘°μΈμ erdμ μ μ΄ μ°κ²°λ κ΄κ³μ λ°λΌμ λ§λ€ μ μλ€.
select
컬λΌλ¦¬μ€νΈ
from ν
μ΄λΈA
inner join ν
μ΄λΈB
on ν
μ΄λΈA.μ»¬λΌ = ν
μ΄λΈB.컬λΌ;
select
컬λΌλ¦¬μ€νΈ
from ν
μ΄λΈA
(left|right) outer join ν
μ΄λΈB
on ν
μ΄λΈA.μ»¬λΌ = ν
μ΄λΈB.컬λΌ;
λ΄λΆμ‘°μΈ - λͺ¨λ ν μ΄λΈμ λμμ μ‘΄μ¬νλ κ°λ§ λΆλ¬μ¬ μ μλ€.
μΈλΆμ‘°μΈ - μ΄λμ‘°μΈν΄μ 9μ΄κΉμ§ κΉμλ . μ΄λμ‘°μΈμ ν¬ν¨λμ§ μμ λλ¨Έμ§λ₯Ό μΆκ°λ‘ κ°μ Έμμ λΆμ¬μ€λ€.
SELECT
b.name AS μ§μλͺ
,
b.department AS λΆμλͺ
,
a.name AS μμ¬λͺ
FROM tblself a -- μν : λΆλͺ¨ν
μ΄λΈ >
INNER JOIN tblself b -- μν : μμν
μ΄λΈ >
ON a.seq = b.super;
-- λ¨μ, μ¬μ λͺ
λ¨
SELECT
m.name,
w.name
FROM tblmen m
FULL OUTER JOIN tblwomen w
ON m.name = w.couple;
create [or replace] view λ·°μ΄λ¦
as
select λ¬Έ;
CREATE OR REPLACE VIEW vwInsa
AS
SELECT * FROM tblinsa;
SELECT * FROM vwInsa; -- tblInsa ν
μ΄λΈμ 볡μ¬λ³Έ > μ€λͺ
λ·°
SELECT * FROM (SELECT * FROM tblinsa); -- > μ΅λͺ
λ·°
μ λμ¨, union
SELECT * FROM tblmen
union
SELECT * FROM tblwomen;
-- μ»¬λΌ μ΄λ¦μ μμ€μν¨. μλ£νλ§ μ€μν¨.
SELECT name, address, salary FROM tblstaff
union
SELECT name, city, basicpay FROM tblinsa;
--union > μνμ μ§ν© > μ€λ³΅ μ κ±°
SELECT * FROM tblAAA
union
SELECT * FROM tblBBB;
-- union all > μ€λ³΅κ° νμ©
SELECT * FROM tblAAA
UNION ALL
SELECT * FROM tblBBB;
-- intersect > κ΅μ§ν©
SELECT * FROM tblAAA
intersect
SELECT * FROM tblBBB;
-- minus > μ°¨μ§ν©(A - B) > νΌμ°μ°μ μμΉ μ€μ
SELECT * FROM tblAAA
minus
SELECT * FROM tblBBB;
SELECT * FROM tblBBB
minus
SELECT * FROM tblAAA;
DDL > κ°μ²΄ μ‘°μ
DML > λ°μ΄ν° μ‘°μ
-- Case 1. μλ‘μ΄ μ»¬λΌμ μΆκ°νκΈ° > κ°κ²© μΆκ°
ALTER TABLE tblEdit
ADD (price NUMBER);
SELECT * FROM tbledit;
-- Case 2. μ»¬λΌ μμ νκΈ°
ALTER TABLE tbledit
DROP COLUMN color;
-- Case 3. μ»¬λΌ μμ νκΈ°
SELECT * FROM tblEdit;
INSERT INTO tblEdit VALUES (4, 'μ ν M2 λ§₯λΆ νλ‘ 2023');