FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- (๋น์ ์ฐจ์ ) ๋ฐ์ดํฐ ์กฐ์์ด (DML) โ SELECT, INSERT, UPDATE, DELETE
- ๋ฐ์ดํฐ ์ ์์ด (DDL) โ CREATE, ALTER, DROP, RENAME
- ๋ฐ์ดํฐ ์ ์ด์ด (DCL) โ GRANT, REVOKE
- ํธ๋์ญ์ ์ ์ด์ด (TCL) โ COMMIT, ROLLBACK
- ์๋ฌธ์๋ก ์์ํด์ผํจ
- ์ค๋ณต๋๋ฉด ์๋จ
- ์์ฝ์ ์ฌ์ฉ๋ถ๊ฐ
- A-Z, a-z, 0-9, _ ,#, $ ์ ๋ฌธ์๋ง ํ์ฉํ๋ค.
- ๋์ ํญ์ ;๋ก ๋๋จ
abc_123 (0) / _abc (x) / 123abc (x)
CREATE TABLE member(
id VARCHAR2(20),
password VARCHAR2(20),
name VARCHAR2(20)
)
CREATE TABLE member(
id VARCHAR2(20) NOT NULL,
password VARCHAR2(20) NOT NULL,
name VARCHAR2(20)
)
CREATE TABLE member(
id VARCHAR2(20) NOT NULL,
password VARCHAR2(20) DEFAULT '1234',
name VARCHAR2(20) DEFAULT 'NICKNAME' NOT NULL
)
DEFAULT
์ต์
์ ์๋ก์ด ๋ ์ฝ๋๊ฐ ํ
์ด๋ธ์ ์ฝ์
๋ ๋ ํด๋น ์ปฌ๋ผ์ ๊ฐ์ ์ ๊ณตํ์ง ์์ ๊ฒฝ์ฐ ์ฌ์ฉ๋๋ ๊ธฐ๋ณธ ๊ฐ์ ์ค์ ํฉ๋๋ค.
CREATE TABLE member(
id VARCHAR2(20) UNIQUE,
password VARCHAR2(20) NOT NULL,
name VARCHAR2(20)
)
CREATE TABLE member(
id VARCHAR2(20),
password VARCHAR2(20) NOT NULL,
name VARCHAR2(20),
UNIQUE(id)
)
UNIQUE
์ต์
์ ํ
์ด๋ธ์ ์ปฌ๋ผ์ ์ค๋ณต๋ ๊ฐ์ ํ์ฉํ์ง ์๋๋ก ์ค์ ํฉ๋๋ค. ์ฆ, ํด๋น ์ปฌ๋ผ์ ๋ชจ๋ ๊ฐ์ ์ ์ผํด์ผ ํฉ๋๋ค.
CREATE TABLE member(
id VARCHAR2(20) UNIQUE,
password VARCHAR2(20) NOT NULL,
name VARCHAR2(20)
age NUMBER CHECK(AGE โฅ 18)
gender VARCHAR2(10) CHECK(gender IN ('๋จ', '์ฌ'))
)
CHECK
์ต์
์ ํน์ ์กฐ๊ฑด์ด ๋ง์กฑ๋์ด์ผ๋ง ํ
์ด๋ธ์ ๋ฐ์ดํฐ๊ฐ ์ฝ์
๋๊ฑฐ๋ ์
๋ฐ์ดํธ๋ ์ ์๋๋ก ์ค์ ํฉ๋๋ค. ์ฆ, ์ง์ ํ ์กฐ๊ฑด์ ๋ง์กฑํ์ง ์๋ ๋ฐ์ดํฐ๋ ํ
์ด๋ธ์ ์ถ๊ฐ๋์ง ์์ต๋๋ค.
// ๋ฐ์ดํฐ ํ์
๊ณผ ๊ฐ์ด ์ค์ ํ๊ธฐ
CREATE TABLE member(
id VARCHAR2(20) PRIMARY KEY,
password VARCHAR2(20),
name VARCHAR2(20)
)
// ๋ฐ์ดํฐ ํ์
๊ณผ ๋ฐ๋ก ์ค์ ํ๊ธฐ
CREATE TABLE member(
id VARCHAR2(20),
password VARCHAR2(20),
name VARCHAR2(20)
PRIMARY KEY(id)
)
PRIMARY KEY ์ค์
CREATE TABLE member(
id VARCHAR2(20) CONSTRAINT pk_id PRIMARY KEY,
password VARCHAR2(20),
name VARCHAR2(20)
)
PRIMARY KEY๋ฅผ ์ง์ ํ๋ฉฐ ์ ์ฝ ์กฐ๊ฑด์ ์ด๋ฆ ๋ฌ๊ธฐ
์ ์ฝ ์กฐ๊ฑด์ ์ถ๊ฐํ ๋๋ CONSTRAINT
๋ก ์ด๋ฆ์ ์ง์ ํด์ฃผ๋ ๊ฒ์ด ์ผ๋ฐ์ ์ผ๋ก ์ข์ ๊ดํ์ด๋ค.
CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
์ฌ๋ฌ ์ปฌ๋ผ์ ์กฐํฉํ์ฌ ๋ณตํฉ ๊ธฐ๋ณธ ํค(Composite Primary Key) ์ค์ ํ๊ธฐ
CREATE TABLE member(
id VARCHAR2(20) PRIMARY KEY,
password VARCHAR2(20),
name VARCHAR2(20)
)
// ๋ฐ์ดํฐ ํ์
๊ณผ ๊ฐ์ด ์ค์ ํ๊ธฐ
CREATE TABLE member_score(
id VARCHAR2(20) REFERENCES member(id),
score NUMBER NOT NULL,
)
// ๋ฐ์ดํฐ ํ์
๊ณผ ๋ฐ๋ก ์ค์ ํ๊ธฐ
CREATE TABLE member_score(
id VARCHAR2(20) REFERENCES member(id),
score NUMBER NOT NULL,
FOREGIN KEY (id) REFERENCES member(id)
)
member_score์์ member์ ์๋ id๋ฅผ ์ฝ์
ํ๋ ค ํ๋ค๋ฉด DEPENDENT
์ค๋ฅ ๋ฐ์ํ๋ค.
์ธ๋ ํค ๊ฐ์ ์ฐธ์กฐ ๋ฌด๊ฒฐ์ฑ ์ ์ฝ์ ๋ฐ์ ์ ์๋ค.
CREATE TABLE member(
id VARCHAR2(20) PRIMARY KEY,
password VARCHAR2(20),
name VARCHAR2(20)
)
CREATE TABLE member_score(
id VARCHAR2(20) PRIMARY KEY,
score NUMBER NOT NULL,
// ๋ถ๋ชจ ๊ฐ ์ญ์ ์ ์ฐธ์กฐํ๋ ์์์ด ์๋ค๋ฉด ์ญ์ ๋ถ๊ฐ (๊ธฐ๋ณธ ์ต์
)
FOREIGN KEY(id) REFERENCES member(id) ON DELETE RESTRICT
// ๋ถ๋ชจ ํค๋ฅผ ์ญ์ ์ ์์ ํค๋ฅผ ๊ฐ์ง ํ๋ ํจ๊ป ์ญ์
FOREIGN KEY(id) REFERENCES member(id) ON DELETE CASCADE
// ๋ถ๋ชจ ํค๋ฅผ ์ญ์ ์ ์์ ํค๋ฅผ NULL๋ก ๋ณ๊ฒฝํ๋ค (๋ค๋ฅธ ํ์ ๊ทธ๋๋ก).
FOREIGN KEY(id) REFERENCES member(id) ON DELETE SET NULL
)
CREATE TABLE member(
id VARCHAR2(20),
password VARCHAR2(20),
name VARCHAR2(20)
)
COMMENT ON COLUMN member.id IS '๋ฉค๋ฒ ์์ด๋'
COMMENT ON COLUMN member.password IS '๋ฉค๋ฒ ๋น๋ฐ๋ฒํธ'
COMMENT ON COLUMN member.name IS '๋ฉค๋ฒ ๋๋ค์'
CREATE TABLE tableName (
columnName dataType(SIZE) NOT NULL,
columnName dataType(SIZE) DEFAULT,
)
CREATE INDEX indexName ON tableName (columnName)
// ์นผ๋ผ ์ด๋ฆ ๋ณ๊ฒฝ
ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName;
// ์นผ๋ผ ์ถ๊ฐ
ALTER TABLE tableName ADD columnName dataType(SIZE)
// ์ฌ์ด์ฆ ๋ณ๊ฒฝ, ๋ฐ์ดํฐ ํ์
๋ณ๊ฒฝ
ALTER TABLE tableName MODIFY columnName dataType(SIZE)
// NOT NULL ์ต์
์ถ๊ฐ
ALTER TABLE tableName MODIFY columnName VARCHAR(100) NOT NULL;
// ์ปฌ๋ผ ์ญ์
ALTER TABLE tableName DROP COLUMN columnName
ALTER
๋ช
๋ น์ด๋ ์ฌ๋ฌ๊ฐ์ column์ ๋์์ ์์ ํ ์ ์๋ค. ๋จ, ํ๋์ ํ๊ฐ์ ์์
๋ง ์ํํ ์ ์๋ค.
// unique ์ต์
์ถ๊ฐ
ALTER TABLE tableName ADD CONSTRAINT ์ ์ฝ์กฐ๊ฑด์ด๋ฆ UNIQUE (columnName);
// PKํค ์ค์ ์ถ๊ฐ
ALTER TABLE tableName ADD PRIMARY KEY (columnName);
ALTER TABLE tableName ADD CONSTRAINT ์ ์ฝ์กฐ๊ฑด์ด๋ฆ PRIMARY KEY (columnName);
// ์ธ๋ํค ์ค์ ์ถ๊ฐ
ALTER TABLE child_tableName ADD CONSTRAINT ์ ์ฝ์กฐ๊ฑด์ด๋ฆ
FOREIGN KEY (child_columnName) REFERENCES parent(parent_columnName);
// ์ธ๋ํค ์ญ์
ALTER TABLE ํ
์ด๋ธ์ด๋ฆ DROP PRIMARY KEY
// check ์ต์
์ถ๊ฐ
ALTER TABLE products ADD CONSTRAINT ์ ์ฝ์กฐ๊ฑด์ด๋ฆ CHECK (price > 0);
์์์๋ ๋งํ๋ฏ์ด ์ ์ฝ ์กฐ๊ฑด์ ์ถ๊ฐํ ๋๋ CONSTRAINT
๋ก ์ด๋ฆ์ ์ง์ ํด์ฃผ๋ ๊ฒ์ด ์ผ๋ฐ์ ์ผ๋ก ์ข์ ๊ดํ์ด๋ค.
// ํ
์ด๋ธ ์ญ์
DROP TABLE tableName
// ์นผ๋ผ๊ฐ๋ง ๋จ๊ธฐ๊ณ ํ
์ด๋ธ์ ๋ชจ๋ ๋ฐ์ดํฐ ์ญ์
TRUNCATE TABLE tableName
// ๋ฐ์ดํฐ ๋ชจ๋ ์ญ์
DELETE FROM tableName
DROP | TRUNCATE | DELETE |
---|---|---|
DDL | DDL | DML |
RollBack ๋ถ๊ฐ๋ฅ | RollBack ๋ถ๊ฐ๋ฅ | Commit ์ด์ Rollback ๊ฐ๋ฅ |
Auto Commit | Auto Commit | ์ฌ์ฉ์ Commit |
ํ ์ด๋ธ์ด ์ฌ์ฉํ๋ ๋ชจ๋ Storage ์ญ์ | ํ ์ด๋ธ์ด ์ฌ์ฉํ๋ Storage ์ค ์ต์ด ํ ์ด๋ธ ์์ฑ์ ํ ๋น๋ Storage๋ง ๋จ๊ธฐ๊ณ ์ญ์ | ์ฌ์ฉํ๋ Storage๋ ์ญ์ ๋์ง ์์ |
ํ ์ด๋ธ์ ์ ์ ์์ฒด๋ฅผ ์์ ํ ์ญ์ | ํ ์ด๋ธ์ ์ต์ด ์์ฑ๋ ์ด๊ธฐ์ํ๋ก ๋ง๋ฌ | ๋ฐ์ดํฐ๋ง ์ญ์ |
RENAME origin_table_name TO change_table_name
ํ ์ด๋ธ ์ด๋ฆ ๋ณ๊ฒฝํ๊ธฐ
// ์ฝ์
์ปฌ๋ผ์ ๋ช
์ํ์ง ์์์ ๊ฒฝ์ฐ ๋ชจ๋ ์ปฌ๋ผ์ ์ฝ์
ํด์ผํ๋ค.
INSERT INTO tableName VALUES (data, data, data...);
// ํน์ ๋ฐ์ดํฐ๋ง ์ฝ์
ํ๋ ๊ฒฝ์ฐ ์ฝ์
์ปฌ๋ผ์ ๋ช
์ํด์ผํ๋ค.
INSERT INTO tableName (columnName, columnName) VALUES (data, data);
// ํน์ column ๊ฐ ๋ณ๊ฒฝ
UPDATE tableName SET columnName = data WHERE ์กฐ๊ฑด
// ๋๊ฐ ํ๋ฒ์ ๋ณ๊ฒฝ
UPDATE tableName SET columnName1 = data1, columnName2 = data2 WHERE ์กฐ๊ฑด
WHERE ์กฐ๊ฑด์ ์ง์ ํ์ง ์์ผ๋ฉด ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋ชจ๋ ํ์ด ์ ๋ฐ์ดํธ ๋์์ด ๋๋ค.
// table์ ๋ชจ๋ ๋ฐ์ดํฐ ์ญ์
DELETE FROM tableName
// ์กฐ๊ฑด์ ๋ฌ์์ ์ญ์ ํ๊ธฐ
DELETE FROM tableName WHERE ์กฐ๊ฑด
// ์กฐ๊ฑด ์ฌ๋ฌ๊ฐ ๋ฌ์์ ์ญ์ ํ๊ธฐ
DELETE FROM tableName WHERE ์กฐ๊ฑด AND ์กฐ๊ฑด
DELETE FROM neflix WHERE video_name IN ('์๊ทธ๋', '๋์ ์์ ์จ')
// X ์๋ชป๋ ๋ฌธ๋ฒ
DELETE * FROM tableName
TRUNCATE
DDL, ROLLBACK ๋ถ๊ฐ๋ฅ, AUTO COMMIT, ํ
์ด๋ธ ์ต์ด ์์ฑ ์ ์ฉ๋๋ง ๋จ๊ธฐ๊ณ ๋ชจ๋๋ฐํ. ํ
์ด๋ธ์ ์ต์ด ์์ฑ๋ ์ด๊ธฐ์ํ๋ก ๋ง๋ฌ
DELETE
DML, ROLLBACK ๊ฐ๋ฅ(๋ก๊ทธ ๋จ์), ์ฌ์ฉ์ COMMIT, ์ฉ๋๋ฐํํ์ง ์์,๋ฐ์ดํฐ๋ง ์ญ์
// ํ
์ด๋ธ์ ์ ์ฒด ๋ฐ์ดํฐ ์กฐํ
SELECT * FROM tableName
// ํน์ ์ปฌ๋ผ๋ง ๊ฐ์ ธ์ค๊ธฐ
SELECT columnName1, columnName2 FROM tableName
// ํน์ ์ปฌ๋ผ๋ง ๊ฐ์ ธ์ค๊ธฐ
SELECT * FROM tableName WHERE ์กฐ๊ฑด์
// ์ค๋ณต ๋ฐ์ดํฐ ํ๋๋ก ๋ถ๋ฌ์ค๊ธฐ
SELECT DISTINCT columnName FROM tableName WHERE ์กฐ๊ฑด์
// ASํค์๋๋ก ๋ณ์นญ ์ค์นญํ๊ธฐ
SELECT columnName1 AS c1, columnName2 AS c2 FROM tableName
FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY
SELECT tableA.name, tableA.age, tableB.job
FROM tableA, tableB
SELECT A.name, A.age, B.job
FROM tableA A, tableB B
์ด๋ ๊ฒ ๋ค์ค ํ
์ด๋ธ์์ SELECTํ ๊ฒฝ์ฐ ํ
์ด๋ธ ์ด๋ฆ์ ๊ณ์ ์ฐ๋๊ฑด ๋ถํธํ๋ฏ๋ก alias
๋ก ์ค์ฌ์ ๋ํ๋ผ ์ ์๋ค.
// column
SELECT * FROM tableName WHERE columnName = "columnName"
// category๊ฐ movie๋ animtion์ธ ๊ฒ๋ค ์กฐํ
SELECT * FROM neflix WHERE category IN ("movie", "animation")
// category๊ฐ movie๋ animtion์ด ์๋๊ฒ๋ค ์กฐํ
SELECT * FROM neflix WHERE category NOT IN ("movie", "animation")
// AND๋ OR์ผ๋ก ์ฌ๋ฌ๊ฐ ์กฐ๊ฑด ๋น๊ต
SELECT * FROM neflix WHERE view < 70;
SELECT * FROM neflix WHERE view < 70 AND view > 50;
SELECT * FROM neflix WHERE category = "animation" AND view < 70
SELECT * FROM neflix WHERE category = "animation" OR view < 70
// BEWEEN์ผ๋ก ์ฌ์ด๊ฐ ๋น๊ต
SELECT * FROM neflix WHERE view BETWEEN 50 AND 70
// LIKE๋ฌธ์ผ๋ก ๋ฌธ์์ด ๋น๊ต
SELECT * FROM neflix WHERE name LIKE "๋ฏธ%" // "๋ฏธ"๋ก ์์ํ๋ ์ํ ๊ฐ์ ธ์ค๊ธฐ
SELECT * FROM neflix WHERE name LIKE "%๋ฏธ" // "๋ฏธ"๋ก ๋๋๋ ์ํ ๊ฐ์ ธ์ค๊ธฐ
SELECT * FROM neflix WHERE name LIKE "%๋ฏธ%" // "๋ฏธ"๋ฅผ ํฌํจํ๋ ์ํ ๊ฐ์ ธ์ค๊ธฐ
// NULL์ WHERE์ ์ฌ์ฉํ ๋๋ IS๋ฅผ ์ฌ์ฉํด์ผ ํ๋ค.
SELECT * FROM tableName WHERE columnName IS NULL
SELECT * FROM tableName WHERE columnName IS NOT NULL
Customers table
customer_id | customer_name |
---|---|
1 | a |
2 | b |
3 | c |
4 | d |
Orders table
custome_id | order_id |
---|---|
1 | 101 |
2 | 102 |
1 | 103 |
5 | 104 |
SELECT A.customer_id, A.customer_name, B.order_id
FROM Customers_table A
LEFT OUTER JOIN Orders_table B
ON A.customer_id = B.customer_id
customer_id | customer_name | order_id |
---|---|---|
1 | a | 101 |
1 | b | 103 |
2 | b | 102 |
3 | c | NULL |
4 | d | NULL |
**LEFT OUTER JOIN
**์ ์ผ์ชฝ ํ
์ด๋ธ์ ๋ชจ๋ ๋ ์ฝ๋์ ์ค๋ฅธ์ชฝ ํ
์ด๋ธ์ ์ผ์นํ๋ ๋ ์ฝ๋๋ฅผ ๊ฐ์ ธ์ค๋ฉฐ, ์ค๋ฅธ์ชฝ ํ
์ด๋ธ์ ๋ ์ฝ๋๊ฐ ์ผ์นํ์ง ์๋ ๊ฒฝ์ฐ์๋ NULL ๊ฐ์ ๊ฐ์ง ๋ ์ฝ๋๊ฐ ์์ฑ๋๋ค.
์ผ์ชฝ์ ์๋ table์ ์ ๋ณด๋ ๋ชจ๋ ๊ฐ์ ธ์์ง๋ฉฐ, ์ค๋ฅธ์ชฝ์ ์๋ table์ ON์กฐ๊ฑด์ ์ผ์นํ๋ ๊ฒ๋ง ๊ฐ์ ธ์์ง๋ค.
SELECT A.customer_id, A.customer_name, B.order_id
FROM Customers_table A
RIGHT OUTER JOIN Orders_table B
ON A.customer_id = B.customer_id
order_id | custome_id | customer_name |
---|---|---|
101 | 1 | a |
102 | 2 | b |
103 | 1 | a |
104 | 5 | NULL |
**RIGHT OUTER JOIN
**์ ์ผ์ชฝ ํ
์ด๋ธ์ ์ผ์นํ๋ ๋ ์ฝ๋์ ์ค๋ฅธ์ชฝ ํ
์ด๋ธ์ ๋ชจ๋ ๋ ์ฝ๋๋ฅผ ๊ฐ์ ธ์ค๋ฉฐ, ์ผ์ชฝ ํ
์ด๋ธ์ ๋ ์ฝ๋๊ฐ ์ผ์นํ์ง ์๋ ๊ฒฝ์ฐ์๋ NULL ๊ฐ์ ๊ฐ์ง ๋ ์ฝ๋๊ฐ ์์ฑ๋๋ค.
SELECT A.customer_id, A.customer_name, B.order_id
FROM Customers_table A
FULL OUTER JOIN Orders_table B
ON A.customer_id = B.customer_id
customer_id | customer_name | order_id |
---|---|---|
1 | a | 101 |
1 | b | 103 |
2 | b | 102 |
3 | c | NULL |
5 | NULL | 104 |
**FULL OUTER JOIN
**์ ์์ชฝ ํ
์ด๋ธ์ ๋ชจ๋ ๋ ์ฝ๋๋ฅผ ๊ฐ์ ธ์ค๋ฉฐ, ์์ชฝ ํ
์ด๋ธ ์ค ์ด๋ ํ์ชฝ์ด๋ผ๋ ์ผ์นํ์ง ์๋ ๊ฒฝ์ฐ์๋ NULL ๊ฐ์ ๊ฐ์ง ๋ ์ฝ๋๊ฐ ์์ฑ๋๋ค.
SELECT A.name, A.age, B.name
FROM tableA A, tableB B
WHERE A.name = B.name
customer_id | customer_name | order_id |
---|---|---|
1 | a | 101 |
1 | b | 103 |
2 | b | 102 |
SELECT A.name, A.age, B.name
FROM tableA A, tableB B
WHERE A.name Like B.name
์ผ๋ฐ์ ์ผ๋ก Join์ PK์ FK๊ฐ์ ์ฐ๊ด์ฑ์ ์ํด ์ฑ๋ฆฝ๋๋ค.
Equal Join์ Join์ ๊ด์ฌํ๋ ํ ์ด๋ธ ๊ฐ์ ์ปฌ๋ผ ๊ฐ๋ค์ด ์ ํํ๊ฒ ์ผ์นํ๋ ๊ฒฝ์ฐ์ ์ฌ์ฉ๋๋ค.
Equal Join์ โ=โ ์ฐ์ฐ์์ ์ํด ์ํ๋๋ฉฐ, ๊ทธ ์ด์ธ์ ๋น๊ต ์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ๋ ๊ฒฝ์ฐ์๋ ๋ชจ๋ Non Equal Join์ด๋ค. ๋๋ถ๋ถ Non Equal Join์ ์ํํ ์ ์์ง๋ง ์ค๊ณ์์ ์ด์ ๋ก ์ํ์ด ๋ถ๊ฐ๋ฅ ํ ๊ฒฝ์ฐ๋ ์๋ค.
// ์ง์์๊ด์ ์ถ๋ ฅํ๋ ์ฟผ๋ฆฌ
SELECT A.id, A.name AS '์ง์๋ช
', B.name AS '์ง์์๊ด'
FROM employee A, employee B
WHERE A.supervisor_id = B.id
SELECT *
FROM Customers
CROSS JOIN Products;
์์ SQL ๋ฌธ์ฅ์ "Customers" ํ
์ด๋ธ๊ณผ "Products" ํ
์ด๋ธ์ CROSS JOIN
ํ์ฌ ๊ฐ๋ฅํ ๋ชจ๋ ๊ณ ๊ฐ๊ณผ ์ ํ ์กฐํฉ์ ๋ฐํํฉ๋๋ค
ํ์ชฝ ํ
์ด๋ธ์ ๋ชจ๋ ํ๊ณผ ๋ค๋ฅธ ์ชฝ ํ
์ด๋ธ์ ๋ชจ๋ ํ์ ์กฐ์ธ์ํค๋ ๊ธฐ๋ฅ์
๋๋ค. ์ํธ ์กฐ์ธ ๊ฒฐ๊ณผ์ ์ ์ฒด ํ ๊ฐ์๋ ๋ ํ
์ด๋ธ์ ๊ฐ ํ์ ๊ฐ์๋ฅผ ๊ณฑํ ์๋งํผ ๋ฉ๋๋ค. ์นดํฐ์
๊ณฑ(CARTESIAN PRODUCT)
๋ผ๊ณ ๋ ํฉ๋๋ค.
// ์กฐํ์ ๊ธฐ์ค ์ค๋ฆ์ฐจ์ (ASC๊ฐ default)
SELETE * FROM neflix ORDER BY view ASC
// ์กฐํ์ ๊ธฐ์ค ๋ด๋ฆผ์ฐจ์
SELETE * FROM neflix ORDER BY view DESC
// category๋ณ๋ก ์ ๋ ฌํ ๋ค์, ์กฐํ์ ๊ธฐ์ค ์ ๋ ฌ
SELETE * FROM neflix ORDER BY category, view DESC
// category๋ ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌํ ๋ค์, ์กฐํ์๋ ์ค๋ฆ์ฐจ์ ๊ธฐ์ค ์ ๋ ฌ
SELETE * FROM neflix ORDER BY category DESC, view ASC
// GROUP BY๋ฅผ ์ฌ์ฉํ ๊ฒฝ์ฐ GROUP BY ํํ์์ด ์๋ ๊ฐ์ ORDER BY์ ๊ธฐ์ ๋ ์ ์๋ค.
SELECT ์ง์ญ, SUM(๋งค์ถ๊ธ์ก) AS ๋งค์ถ๊ธ์ก
FROM ์ง์ญ๋ณ ๋งค์ถ
GROUP BY ์ง์ญ
ORDER BY ๋
(x)
// GROUP BY๋ฅผ ์ฌ์ฉํ์ง ์์ ๊ฒฝ์ฐ๋ ์๊ด์๋ค.
SELECT ์ง์ญ, ๋งค์ถ๊ธ์ก
FROM ์ง์ญ๋ณ ๋งค์ถ
ORDER BY ๋
(o)
// ์ฒซ๋ฒ์งธ ์นผ๋ผ ๊ธฐ์ค ์ ๋ ฌ ํ, ๋๋ฒ์งธ ์นผ๋ผ ๊ธฐ์ค ์ ๋ ฌ
SELECT ์ง์ญ, ๋งค์ถ๊ธ์ก
FROM ์ง์ญ๋ณ ๋งค์ถ
ORDER BY 1, 2
// category ๋ณ๋ก ์ซ์์ธ๊ธฐ
// null์ count ์๋๋ค
SELECT category, COUNT(*) FROM neflix GROUP BY category
// category ๋ณ๋ก ์กฐํ์ ํฉ๊ณ
SELECT category, SUM(view) FROM neflix GROUP BY category
// category ๋ณ๋ก ์กฐํ์๊ฐ ๊ฐ์ฅ ๋ง์ ๊ฐ
SELECT category, MAX(view) FROM neflix GROUP BY category
// MAX๊ฐ์ด ๊ฐ์ฅ ํฐ category๋ถํฐ ์ ๋ ฌ
SELECT category, MAX(view) FROM neflix GROUP BY category ORDER BY MAX(view) DESC
// category ๋ณ๋ก ์กฐํ์๊ฐ ๊ฐ์ฅ ์์ ๊ฐ
SELECT category, MIN(view) FROM neflix GROUP BY category
// category ๋ณ๋ก ์กฐํ์ ์ต๋ ์ต์๊ฐ ํจ๊ป ์ถ๋ ฅ
SELECT category, MAX(view), MIN(view) FROM neflix GROUP BY category
// category ๋ณ๋ก ์กฐํ ์ ํ๊ท
SELECT category, AVG(view) FROM neflix GROUP BY category
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING order_count >= 10;
**Having**
์ ์ **WHERE**
์ ๊ณผ ๋น์ทํ๊ฒ ํน์ ์กฐ๊ฑด์ ๋ง์กฑํ๋ ๊ทธ๋ฃน๋ง์ ์ ํํ์ง๋ง,
**GROUP BY**
๋ก ๊ทธ๋ฃนํ๋ ๊ฒฐ๊ณผ ์ค์์ ํน์ ์กฐ๊ฑด์ ๋ง์กฑํ๋ ๊ทธ๋ฃน๋ง์ ์ ํํ ์ ์๋ค.
SELECT column1, column2, column3
CASE WHEN column1 = 1 THEN 'ONE' // 1์ธ ๊ฐ๋ค์ ๋ํด์ 'ONE'
WHEN column2 = 2 THEN 'TWO' ELSE 'THREE' // 2์ธ ๊ฐ๋ค์ ๋ํด์ 'TWO' ๋๋จธ์ง๋ 'THREE'
**CASE WHEN**
์ ์ฌ์ฉํ๋ฉด ๊ธฐ์กด์ ์๋ ์ด์ ์กฐ๊ฑด์ ๋ฐ๋ฅธ ์๋ก์ด ๊ฐ์ ๊ฐ๋ ์๋ก์ด ์ด์ด ๋ง๋ค์ด์ง๊ฒ ๋๋ค.
SELECT column1, column2, column3
CASE WHEN column1 = 1 THEN 'ONE' // 1์ธ ๊ฐ๋ค์ ๋ํด์ 'ONE' ๋๋จธ์ง๋ NULL
**ELSE**
๋ฅผ ๋นผ๊ณ ์ฐ๋ฉด ์ผ์นํ์ง ์๋ ๊ฐ๋ค์ ๋ํด์ NULL์ด ๋๋ค.
SELECT column1, column2, column3
CASE column1 WHEN 1 THEN 'ONE'
WHEN 2 THEN 'TWO' ELSE 'THREE' AS new_col
AS
๋ฅผ ์ฌ์ฉํ๋ฉด ํ์ ์ด๋ฆ์ ์ง์ ํด ์ค ์ ์๋ค.
// ํ์ฌ ๋ ์ง ๋ฐ ์๊ฐ ๊ฐ์ ธ์ค๊ธฐ
SELECT SYSDATE FROM DUAL;
// ๋
, ์๋ง ์ถ๋ ฅํ๊ณ ์๊ฐ์ ๋ฒ๋ฆฌ๊ธฐ
SELECT TRUNC(SYSDATE) FROM DUAL;
// ์ค๋ ๋ ์ง๋ก ๋ถํฐ 3๋ฌ ํ ๋ ์ง ์ถ๋ ฅ
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
// ์ค๋ ๋ ์ง๋ก ๋ถํฐ 3๋ฌ ์ ๋ ์ง ์ถ๋ ฅ
SELECT ADD_MONTHS(SYSDATE, -3) FROM DUAL;
// ์๊ฐ ๋ฌธ์์ด ํฌ๋งคํ
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24MISS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
// ์ฝ๋๋ผ์ด์ธ์์ ์ฝ๋๋ฅผ CODE๋ก ๋ณ๊ฒฝ
SELECT REPLACE('์ฝ๋๋ผ์ด์ธ', '์ฝ๋', 'CODE') FROM DUAL
// ์ธ๋ฒ์งธ ์ธ์์ ์๋ฌด๊ฒ๋ ์ ์ด์ฃผ์ง ์์ผ๋ฉด ์ฝ๋๋ผ์ด์ธ์์ ์ฝ๋๋ฅผ ์์ ๊ธฐ
SELECT REPLACE('์ฝ๋๋ผ์ด์ธ', '์ฝ๋') FROM DUAL
// ํธ๋ํฐ ๋ฒํธ์์ - ์ญ์
SELECT REPLACE('010-1234-1234', '-') FROM DUAL
// ์ค์ ํ
์ด๋ธ ์ฌ์ฉ ์
SELECT REPLACE(columnName, "A", "B") FROM tableName
// 3๋ฒ์งธ ๋ถํฐ 2๊ธ์ ์๋ผ์ ๊ฐ์ ธ์ค๊ธฐ
SELECT SUBSTR("์ฝ๋๋ผ์ด์ธ", 3, 2) FROM DUAL // ๋ผ์ด
// 3๋ฒ ์ธ์์ ์๋ฌด๊ฒ๋ ์์ฐ๋ฉด ๋๊น์ง ๊ฐ์ ธ์ด
SELECT SUBSTR("์ฝ๋๋ผ์ด์ธ", 3, 2) FROM DUAL // ๋ผ์ด์ธ
// ์์๋ฉด ๋ค์์๋ถํฐ ์๋ฆฌ์ (4๋๊น ๋ค์์๋ถํฐ 4๋ฒ์งธ ๊ธ์)
SELECT SUBSTR("์ฝ๋๋ผ์ด์ธ", -4, 2) FROM DUAL // ๋๋ผ์ด์ธ
// ์ด๋ฆ ๋ณ๊ฒฝ ๊น๋ฏผ์ฐ -> ๊น*์ฐ || ๋ ์ด์ด ๋ถํ๊ธฐ
SELECT SUBSTR(name, 1, 1) || "*" || SUBSTR(name, 3) FROM tableName
// ์ด ํธ์ง๋...
SELECT SUBSTR("์ด ํธ์ง๋ ์๊ตญ์์๋ถํฐ ์์๋์ด ํ ์์ด์ผ ๊ฐ์ ~~~", 1, 5) || "...." FROM DUAL
// ๋๋ฌธ์ ๋ณ๊ฒฝ
SELECT UPPER("abcd") FROM DUAL // ABCD
// ์๋ฌธ์ ๋ณ๊ฒฝ
SELECT LOWER("ABCD") FROM DUAL // ABCD
SELECT * FROM member WHERE ID = UPPER("collection");
// ๋ฐ์ฌ๋ฆผ
SELECT ROUND(3.16) FROM DUAL
// ๋ฐ์ฌ๋ฆผํด์ ์์์ ์๋ ์ฒซ์ฌ์๋ฆฌ ๊น์ง ๋ฐํ
SELECT ROUND(3.16, 1) FROM DUAL
// ๋ค์ ์์์ ๋ฒ๋ฆฌ๊ธฐ
SELECT TRUNC(3.16) FROM DUAL
// ์ฌ๋ฆผ
SELECT CEIL(3.16) FROM DUAL
SELECT TOP 3 columnName
FROM tableName
ORDER BY columnName DESC
columnName์ผ๋ก ์ ๋ ฌํ๊ณ ์์ 3๊ฐ ์ถ๋ ฅ
SELECT TOP 3 WITH TIES columnName
FROM tableName
ORDER BY columnName DESC
๋ง์ฝ ๊ฐ์ ๊ฐ์ด ์๋ค๋ฉด ๊ฐ์ด ์ถ๋ ฅ
SELECT TOP 50 PERCENT columnName
FROM tableName
ORDER BY columnName DESC
์์ 50ํผ์ผํธ ์ถ๋ ฅ
// ๋ชจ๋ ํ์ ์๋ฅผ ์ถ๋ ฅํ๋ฉฐ NULL ๊ฐ์ ๊ฐ์ง ํ๋ ํฌํจ
SELECT COUNT(*) AS count FROM tableName
// ํํ์์ ๊ฐ์ด NULL์ธ ๊ฒ์ ์ ์ธํ ํ์ ์๋ฅผ ์ถ๋ ฅ
SELECT COUNT(columnName) AS c1 FROM tableName;
// ํํ์์ NULL์ ์ ์ธํ ํฉ๊ณ ์ถ๋ ฅ
SELECT SUM(columnName) AS c1 FROM tableName;
// ํํ์์ NULL์ ์ ์ธํ ํ๊ท ์ถ๋ ฅ
SELECT AVG(columnName) AS c1 FROM tableName;
// ์ต๋๊ฐ ์ถ๋ ฅ
SELECT MAX(columnName) AS c1 FROM tableName;
// ์ต์๊ฐ ์ถ๋ ฅ
SELECT MIN(columnName) AS c1 FROM tableName;
// ํ์ค ํธ์ฐจ ์ถ๋ ฅ
SELECT STDDEV(columnName) AS c1 FROM tableName;
// ๋ถ์ฐ ์ถ๋ ฅ
SELECT VARIAN(columnName) AS c1 FROM tableName;
// ISNULL(A, B) โ A column์ ๊ฐ์ด NULL์ด๋ผ๋ฉด B๋ก ๋์ฒด (SQL)
SELECT column1, column2, ISNULL(column3, '๊ฐ ์์') FROM tableName
// NVL(A, B) โ A column์ ๊ฐ์ด NULL์ด๋ผ๋ฉด B๋ก ๋์ฒด (ORACLE)
SELECT column1, column2, NVL(column3, '๊ฐ ์์') FROM tableName
// NULLIF(A, B) โ A column์ ๊ฐ์ด B์ ๊ฐ์ผ๋ฉด NULL์, ๊ฐ์ง ์์ผ๋ฉด A column์ ๊ฐ์ return
SELECT column1, column2, NULLIF(column3, 1) FROM tableName
// NULL์ด ์๋ ์ต์ด์ ํํ์ ๋ํ๋, ๋ชจ๋ ๊ฒ NULL์ด๋ฉด NULL return
SELECT column1, column2, COALESCE(column3, column4, column5) IS FROM tableName
SELECT A, B, C COALESCE(A, B, C) FROM tableName;
A | B | C | COALESCE(A, B) |
---|---|---|---|
1 | 2 | NULL | 1 |
NULL | NULL | 3 | 3 |
NULL | 1 | NULL | 1 |
NULL | NULL | NULL | NULL |
Transaction
์ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋
ผ๋ฆฌ์ ์ฐ์ฐ๋จ์๋ก์ ๋ฐ์ ํ ๊ด๋ จ๋์ด ๋ถ๋ฆฌ๋ ์ ์๋ ํ ๊ฐ ์ด์์ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์กฐ์์ ๊ฐ๋ฆฌํจ๋ค.
**์์์ฑ**
- ํธ๋์ญ์ ์์ ์ ์๋ ์ฐ์ฐ๋ค์ ๋ชจ๋ ์ฑ๊ณต์ ์ผ๋ก ์คํ๋๋์ง ์ ํ ์คํ๋์ง ์์ ์ํ๋ก ๋จ์์์ด์ผ ํ๋ค.**์ผ๊ด์ฑ**
- ํธ๋์ญ์ ์ด ์คํ๋๊ธฐ ์ ์ ๋ฐ์ดํฐ๋ฒ ์ด์ค ๋ด์ฉ์ด ์๋ชป๋์ด ์์ง ์๋ค๋ฉด ํธ๋์ญ์ ์ด ์คํ๋ ์ดใ ใ ์๋ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋ด์ฉ์ ์๋ชป์ด ์์ผ๋ฉด ์๋๋ค.**๊ณ ๋ฆฝ์ฑ**
- ํธ๋์ญ์ ์ด ์คํ๋๋ ๋์ค์ ๋ค๋ฅธ ํธ๋์ญ์ ์ ์ํฅ์ ๋ฐ์ ์๋ชป๋ ๊ฒฐ๊ณผ๋ฅผ ๋ง๋ค์ด์๋ ์๋๋ค**์ง์์ฑ**
- ํธ๋์ญ์ ์ด ์ฑ๊ณต์ ์ผ๋ก ์ํ๋๋ฉด ๊ทธ ํธ๋์ญ์ ์ด ๊ฐฑ์ ํ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋ด์ฉ์ ์๊ตฌ์ ์ผ๋ก ์ ์ฅ๋๋ค.**Dirty Read**
- ์์ ๋์์ง๋ง commit์ด ๋์ง ์์ ๋ฐ์ดํฐ๋ฅผ ์ฝ๋ ๊ฒ**Non-Repeatbale Read**
- ์กฐํ / ์์ / ์กฐํ ์์ผ๋ก ์ผ์ด๋๋ฉด ๋ ์กฐํ์ ๊ฒฐ๊ณผ๋ฌผ์ด ๋ค๋ฅผ ์ ์๋ค. ( ์กฐํ๋ ๋ ์ฝ๋์ ๊ฐ์ด ๋ณ๊ฒฝ ๋จ )**Phantom Read**
- ์กฐํ / ์ฝ์ or ์ญ์ / ์กฐํ ์์ผ๋ก ์ผ์ด๋์ ๋ ์กฐํ์ ๊ฒฐ๊ณผ๋ฌผ์ด ๋ค๋ฅผ ์ ์๋ค. ( ์๋ก์ด ๊ฐ์ด ์๊ฒจ๋๊ฑฐ๋ ์์ด์ง )
ORACLE | SQL Server |
---|---|
DDL ๋ฌธ์ฅ ์ํ ํ ์๋ COMMIT | DDL ๋ฌธ์ฅ ์ํ ํ ์๋ COMMIT ํ์ง ์์ |
DDL ๋ฌธ์ฅ์ ์ํ์ ๋ด๋ถ์ ์ผ๋ก Transaction ์ข ๋ฃ | CREATE๋ Transaction์ ํฌํจ๋จ์ผ๋ก Rollback์ ์ทจ์ |
๋ฐ์ดํฐ ๊ฐ์ ๊ณต๋ฐฑ์ผ๋ก ์ ๋ ฅํ๋ฉด NULL ์ ๋ ฅ๋จ | ๋ฐ์ดํฐ ๊ฐ์ ๊ณต๋ฐฑ์ผ๋ก ์ ๋ ฅํ๋ฉด โโ ์ ๋ ฅ๋จ |
๋จ์ผํ ์ฐ๊ด ์๋ธ์ฟผ๋ฆฌ
// Jones๋ณด๋ค ๋์ ๊ธ์ฌ๋ฅผ ๋ฐ๋ ์ฌ์๋ค ์ถ๋ ฅ
SELECT *
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE name = 'JONES');
์ด๋ JONES๋ผ๋ ์ด๋ฆ์ด ์ฌ๋ฌ๊ฐ ์กด์ฌํ๋ฉด ์๋ฌ๊ฐ ๋ฐ์ํ๋ค. ๊ดํธ์์ ๊ฒฐ๊ณผ๋ ํ๋์ฌ์ผ ํ๋ค.
๋ค์คํ ์ฐ๊ด์๋ธ์ฟผ๋ฆฌ
SELECT * FROM employees A
WHERE A.department_id IN
(SELECT B.department_id FROM department B WHERE b.location_id = 1700)
์ฌ๋ฌ๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ํ์ธํ๊ณ ์ถ๋ค๋ฉด IN
์ ์ฌ์ฉํด์ฃผ๋ฉด ์๋ฌ๊ฐ ๋ฐ์ํ์ง ์๋๋ค.
WHERE EXIST
// ๋ฐ๋๋งจ๊ณผ ๋ฌดํ๋์ ๋ชจ๋ ์ฐธ์ฌํ๋ ์ฐธ์ฌ์
SELECT * FROM ๋ฐ๋๋งจ A
WHERE EXISTS (SELECT 1 FROM ๋ฌดํ๋์ B WHERE A.NAME = B.NAME)
WHERE EXIST
๋ค์์ SELECT
์ ์์ ์ปฌ๋ผ์ SELECT 1 ์ฒ๋ผ ์๋ฌด ์ฟผ๋ฆฌ๋ ์ ์ด๋ ๋๋ค. ์ค์ํ ๊ฑด ์กฐ๊ฑด์ ๋ง์ถฐ ๋ฐํ๊ฐ์ด ์๋ ์๋์ด๊ธฐ ๋๋ฌธ์ด๋ค. WHERE
์ ์ ์กฐ๊ฑด๋ง ์ ์ฌํ ํ์ธํ๋ฉด ๋๋ค.
WHERE NOT EXISTS
SELECT * FROM ๋ฐ๋๋งจ A
WHERE NOT EXISTS (SELECT 1 FROM ๋ฌดํ๋์ B WHERE A.NAME = B.NAME)
๋ฐ๋๋งจ์๋ ์ฐธ์ฌํ์ง๋ง ๋ฌดํ๋์ ์๋ ์ฐธ์ฌํ์ง ์๋ ์ฐธ์ฌ์ ( ์ฐจ์งํฉ )
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM (SELECT * FROM EMP WHERE DEPTNO = 10) E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
FROM์ ์ ์ง์ ํ ์ด๋ธ์ ๋ช ์ํ์ฌ ์ฌ์ฉํ๊ธฐ์ ๋ฐ์ดํฐ ๊ท๋ชจ๊ฐ ํฌ๊ฑฐ๋ ๋ถํ์ํ ์ด์ด ๋ง์ ๋, ์ผ๋ถ ํ๊ณผ ์ด๋ง ์ฌ์ฉํ๊ณ ์ ํ ๋ ์ฌ์ฉํ๋ค.
// customers ํ
์ด๋ธ์ ๊ฐ ๊ณ ๊ฐ์ ๋ํด ํด๋น ๊ณ ๊ฐ์ ์ฃผ๋ฌธ ์
SELECT customers.customer_name,
(
SELECT COUNT(*)
FROM orders
WHERE orders.customer_id = customers.customer_id
) AS order_count
FROM customers;
select์ ์๋ธ์ฟผ๋ฆฌ๋ join
์ผ๋ก๋ ๋์ผํ ๊ฒฐ๊ณผ ๋์ถ ๊ฐ๋ฅํ๋ค.
๋๊ฐ์ ํ ์ด๋ธ์ ํ๋๋ก ๋ง๋๋ ์ฐ์ฐ, ์ค๋ณต๋๋ ๋ฐ์ดํฐ๋ ํ๋๋ก ์ฒ๋ฆฌ
๋๊ฐ์ ํ ์ด๋ธ์ ํ๋๋ก ๋ง๋๋ ์ฐ์ฐ, ์ค๋ณต๋๋ ๋ฐ์ดํฐ์ ๋ํด ์ค๋ณต ์ ๊ฑฐ๋ฅผ ํ์ง ์์
๋๊ฐ์ ํ ์ด๋ธ์ ๋ํด ๊ฒน์น๋ ๋ถ๋ถ์ ์ถ์ถํ๋ ์ฐ์ฐ. ์ค๋ณต๋๋ ๋ฐ์ดํฐ๋ ํ๋๋ก ์ฒ๋ฆฌ
์์ ์ํ ๊ฒฐ๊ณผ์์ ๋ค์ ๊ฒฐ๊ณผ๋ฅผ ์ฐจ์งํฉ. ์ค๋ณต๋๋ ๋ฐ์ดํฐ๋ ํ๋๋ก ์ฒ๋ฆฌ
SELECT column1 A, column2 B
FROM table1
UNION ALL
SELECT column1 C, column2 D
FROM table2
๊ฒฐ๊ณผ์์ column1
์ ํํํ ๋๋ ์ฒซ๋ฒ์งธ SELECT ๋ฌธ์ฅ์ alias
์ธ A
๊ฐ ์ ์ฉ์ด ๋๋ค.
- START WITH๋ Oracle์์๋ง ์ฌ์ฉํ ์ ์๋ค.
- SQL Server์์์ ๊ณ์ธตํ ์ง์๋ฌธ์ CTE๋ฅผ ์ฌ๊ทํธ์ถํจ์ผ๋ก์จ ๊ณ์ธต ๊ตฌ์กฐ๋ฅผ ์ ๊ฐํ๋ค.
- SQL Server์์์ ๊ณ์ธตํ ์ง์๋ฌธ์ ์ต์ปค ๋ฉค๋ฒ๋ฅผ ์คํํ์ฌ ๊ธฐ๋ณธ ๊ฒฐ๊ณผ ์งํฉ์ ๋ง๋ค๊ณ ์ดํ ์ฌ๊ท ๋ฉค๋ฒ๋ฅผ ์ง์์ ์ผ๋ก ์คํํ๋ค.
- Oracle์ ๊ณ์ธตํ ์ง์๋ฌธ์์ WHERE์ ์ ๋ชจ๋ ์ ๊ฐ๋ฅผ ์งํํ ์ดํ ํํฐ ์กฐ๊ฑด์ผ๋ก์ ์กฐ๊ฑด์ ๋ง์กฑํ๋ ๋ฐ์ดํฐ๋ง์ ์ถ์ถํ๋๋ฐ ํ์ฉํ๋ค.
- PRIORํค์๋๋ SELECT์์๋ ์ฌ์ฉํ ์ ์๋ค.
C1 | C2 | C3 |
---|---|---|
1 | NULL | A |
2 | 1 | B |
3 | 1 | C |
4 | 2 | D |
SELECT * FROM TABLE
START WITH C2 IS NULL // 1ํ์ด level 1์ด ๋๋ค.
CONNECT BY PRIOR C1 = C2 // c2๊ฐ 1์ธ ๊ฒ๋ค์ด level2๊ฐ ๋๋ค.
ORDER SIBLINGS BY C3 DESC // ์์๋ค๋ผ๋ฆฌ๋ c3๊ธฐ์ค ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ์ ํ๋ค.
C1 | C2 | C3 | level |
---|---|---|---|
1 | NULL | A | 1 |
3 | 1 | C | 2 |
2 | 1 | B | 2 |
4 | 2 | D | 3 |
๋ถ์์ฝ๋ | ๋ถ์๋ช | ์์ ๋ถ์์ฝ๋ |
---|---|---|
100 | ์์์๋ถ | NULL |
110 | ํ๊ตญ์ง์ฌ | 100 |
111 | ์์ธ์ง์ | 110 |
120 | ์ผ๋ณธ์ง์ฌ | 100 |
121 | ๋์ฟ์ง์ | 120 |
122 | ์ค์ฌ์นด์ง์ | 120 |
์๋ฐฉํฅ์ ๊ฐ
SELECT * FROM TABLE
START WITH ๋ถ์์ฝ๋ = 120
CONNECT BY PRIOR ๋ถ์์ฝ๋ = ์์ ๋ถ์์ฝ๋
๋ถ๋ชจ ๋ ธ๋๋ก๋ถํฐ ์์ ๋ ธ๋ ๋ฐฉํฅ์ผ๋ก ์ ๊ฐํ๋ ๊ฒ
๋ถ์์ฝ๋ | ๋ถ์๋ช | ์์ ๋ถ์์ฝ๋ | level |
---|---|---|---|
120 | ์ผ๋ณธ์ง์ฌ | 100 | 1 |
121 | ๋์ฟ์ง์ | 120 | 2 |
122 | ์ค์ฌ์นด์ง์ | 120 | 2 |
์ญ๋ฐฉํฅ์ ๊ฐ
SELECT * FROM TABLE
START WITH ๋ถ์์ฝ๋ = 120
CONNECT BY PRIOR ์์ ๋ถ์์ฝ๋ = ๋ถ์์ฝ๋
์์ ๋ ธ๋๋ก๋ถํฐ ๋ถ๋ชจ ๋ ธ๋ ๋ฐฉํฅ์ผ๋ก ์ ๊ฐํ๋ ๊ฒ
๋ถ์์ฝ๋ | ๋ถ์๋ช | ์์ ๋ถ์์ฝ๋ | level |
---|---|---|---|
120 | ์ผ๋ณธ์ง์ฌ | 100 | 1 |
100 | ์์์๋ถ | NULL | 2 |
SELECT * FROM TABLE
START WITH C2 IS NULL // 1ํ์ด level 1์ด ๋๋ค.
CONNECT BY PRIOR C1 = C2 // c2๊ฐ 1์ธ ๊ฒ๋ค์ด level2๊ฐ ๋๋ค.
AND ์กฐ๊ฑด~~~
ORDER SIBLINGS BY C3 DESC // ์์๋ค๋ผ๋ฆฌ๋ c3๊ธฐ์ค ๋ด๋ฆผ์ฐจ์ ์ ๋ ฌ์ ํ๋ค.
START WITH
์ ํด๋นํ๋ ํ๋ค์(์ฌ๊ธฐ์ C2
) ์กฐ๊ฑด๊ณผ ์๊ด์์ด select๊ฐ ๋๋ค!! ์ฃผ์
: ํ๋ ์ด์์ ๊ธฐ๋ณธ ํ ์ด๋ธ์ด๋ ๋ค๋ฅธ ๋ทฐ๋ฅผ ์ด์ฉํ์ฌ ์์ฑ๋๋ ๊ฐ์ ํ ์ด๋ธ
-
- ๊ธฐ๋ณธ ํ ์ด๋ธ์ ๋์คํฌ์ ๊ณต๊ฐ์ด ํ ๋น๋์ด ๋ฐ์ดํฐ๋ฅผ ์ ์ฅํจ
-
- ๋ทฐ๋ ๋ฐ์ดํฐ ๋์ ๋๋ฆฌ (Data Dictionary) ํ ์ด๋ธ์ ๋ทฐ์ ๋ํ ์ ์ (SQL ๋ฌธ) ๋ง ์ ์ฅ๋์ด ๋์คํฌ ์ ์ฅ ๊ณต๊ฐ ํ ๋น์ด ์ด๋ฃจ์ด์ง์ง ์์
-
- ์ ์ฒด ๋ฐ์ดํฐ ์ค์์ ์ผ๋ถ๋ง ์ ๊ทผํ ์ ์๋๋ก ํจ
-
- ๋ทฐ์ ๋ํ ์์ ๊ฒฐ๊ณผ๋ ๋ทฐ๋ฅผ ์ ์ํ ๊ธฐ๋ณธ ํ ์ด๋ธ์ ์ ์ฉ๋จ
-
- ๋ทฐ๋ฅผ ์ ์ํ ๊ธฐ๋ณธ ํ ์ด๋ธ์์ ์ ์๋ ๋ฌด๊ฒฐ์ฑ ์ ์ฝ์กฐ๊ฑด์ ๊ทธ๋๋ก ์ ์ง๋จ
๋ทฐ์ ํ์์ฑ
-
- ์ฌ์ฉ์ ๋ง๋ค ํน์ ๊ฐ์ฒด๋ง ์กฐํํ ์ ์๋๋ก ํ ํ์๊ฐ ์์
(๋ชจ๋ ์ง์์ ๋ํ ์ ๋ณด๋ฅผ ๋ชจ๋ ์ฌ์์ด ๋ณผ ์ ์๋๋ก ํ๋ฉด ์ ๋จ)
-
- ๋ณต์กํ ์ง์๋ฌธ์ ๋จ์ํ ํ ์ ์์
-
- ๋ฐ์ดํฐ์ ์ค๋ณต์ฑ์ ์ต์ํํ ์ ์์
์) ํ๋งค๋ถ์ ์ํ ์ฌ์๋ค๋ง์ ์ฌ์ํ ์ด๋ธ์์ ์ฐพ์์ ๋ค๋ฅธ ํ ์ด๋ธ๋ก ๋ง๋ค๋ฉด ์ค๋ณต์ฑ์ด ๋ฐ์ํจ
-
์ด๋ด ๋ ๋ทฐ๊ฐ ํ์ํจ
create view ๋ทฐ์ด๋ฆย asย sql๋ฌธย (select๋ฌธ)ย ;
์)ย ์ฌ์ย ํ ์ด๋ธ์ย ๋ถ์๋ฒํธย 30์ธย ์ฌ์๋ค์ย ๋ทฐย ์์ฑํ๊ธฐ
create view emp30 as select * from employee where dno=30;
ROLL UP
ํจ์๋ ์๊ทธ๋ฃน๊ฐ์ ํฉ๊ณ๋ฅผ ๊ณ์ฐํ๋ ํจ์์ด๋ค. ROLLUP
์ ์ฌ์ฉํ๋ฉด GROUP BY
๋ก ๋ฌถ์ ๊ฐ๊ฐ์ ์๊ทธ๋ฃน ํฉ๊ณ์ ์ ์ฒด ํฉ๊ณ๋ฅผ ๋ชจ๋ ๊ตฌํ ์ ์๋ค.
SELECT ์ํID, ์, SUM(๋งค์ถ์ก) AS ๋งค์ถ์ก
FROM ์๋ณ๋งค์ถ
GROUP BY ์ํID, ์;
์ํID | ์ | ๋งค์ถ์ก |
---|---|---|
P1 | 1 | 1000 |
P1 | 2 | 2000 |
P2 | 1 | 1000 |
P2 | 1 | 3000 |
P3 | 2 | 4000 |
์ด๋ ๊ฒ GROUP BY
๋ง ์์ฑํ๋ค๋ฉด ์ํID์ ์๋ณ ํฉ๊ณ๋ง ๋ณด์ฌ์ฃผ์ง๋ง
SELECT ์ํID, ์, SUM(๋งค์ถ์ก) AS ๋งค์ถ์ก
FROM ์๋ณ๋งค์ถ
GROUP BY ROLLUP(์ํID, ์);
์ํID | ์ | ๋งค์ถ์ก |
---|---|---|
P1 | 1 | 1000 |
P1 | 2 | 2000 |
p1 | null | 3000 |
P2 | 1 | 1000 |
P2 | 1 | 3000 |
P2 | null | 4000 |
P3 | 2 | 4000 |
P3 | null | 4000 |
null | null | 11000 |
ROLL UP
์ผ๋ก ๋ฌถ์ด์ค๋ค๋ฉด ์ํID์ ์๋ณ ํฉ๊ณ, ์ํID๋ณ ํฉ๊ณ, ์ด ํฉ๊ณ ์ด๋ ๊ฒ 3๊ฐ๋ฅผ ๋ณด์ฌ์ฃผ๊ฒ ๋๋ค.
CUBE
ํจ์๋ ํญ๋ชฉ๋ค ๊ฐ์ ๋ค์ฐจ์์ ์ธ ์๊ณ๋ฅผ ๊ณ์ฐํ๋ค. ROLL UP
๊ณผ ๋ฌ๋ฆฌ GROUP BY ์ ์ ๋ช
์ํ ๋ชจ๋ ์ปฌ๋ผ์ ๋ํด ์๊ทธ๋ฃน ํฉ๊ณ๋ฅผ ๊ณ์ฐํด์ค๋ค.
SELECT ์ํID, ์, SUM(๋งค์ถ์ก) AS ๋งค์ถ์ก
FROM ์๋ณ๋งค์ถ
GROUP BY CUBE(์ํID, ์);
์ํID | ์ | ๋งค์ถ์ก |
---|---|---|
P1 | 1 | 1000 |
P1 | 2 | 2000 |
p1 | null | 3000 |
P2 | 1 | 1000 |
P2 | 1 | 3000 |
P2 | null | 4000 |
P3 | 2 | 4000 |
P3 | null | 4000 |
null | 1 | 5000 |
null | 2 | 6000 |
null | null | 11000 |
ROLL UP
์ ์ํID์ ๋ํด ํฉ๊ณ๋ง ๋ณด์ฌ์ฃผ์ง๋ง, CUBE
๋ ์๋ณ ๋งค์ถ์ก๋ ๋ณด์ฌ์ฃผ๊ฒ๋๋ค.
SELECT ์ํID, ์, SUM(๋งค์ถ์ก) AS ๋งค์ถ์ก
FROM ์๋ณ๋งค์ถ
GROUP BY GROUPING SETS(์ํID, ์);
์ํID | ์ | ๋งค์ถ์ก |
---|---|---|
p1 | null | 3000 |
P2 | null | 4000 |
P3 | null | 4000 |
null | 1 | 5000 |
null | 2 | 6000 |
GROUPING SETS
๋ ํน์ ํญ๋ชฉ์ ๋ํ ์๊ณ๋ฅผ ๊ณ์ฐํ๋ ํจ์์ด๋ค.
ROLL UP
๊ณผ CUBE
๋ GROUP BY ๊ฒฐ๊ณผ์ ์๊ทธ๋ฃน ํฉ๊ณ์ ํ ํ ํฉ๊ณ๋ฅผ ๋ณด์ฌ์ฃผ์ง๋ง GROUPING SETS
๋ ๊ฐ ์๊ทธ๋ฃน๋ณ ํฉ๊ณ๋ง ๊ฐ๋จํ๊ฒ ๋ณด์ฌ์ค๋ค.
SELECT ์ํID, ์, SUM(๋งค์ถ์ก) AS ๋งค์ถ์ก
FROM ์๋ณ๋งค์ถ
GROUP BY GROUPING SETS((์ํID), (์), (์ํID, ์), ());
// == GROUP BY CUBE(์ํID, ์);
GROUPING SETS
์์ ํน์ ์ปฌ๋ผ๋ค์ ์์ฑํ๋ฉด ํด๋น ์นผ๋ผ๋ค์ ๋ํด ํฉ๊ณ๋ค์ ์์๋ก ๋ณด์ฌ์ค๋ค.
SELECT
CASE GROUPING(์ํID) WHEN 1 THEN '๋ชจ๋ ์ํID' ELSE ์ํID END AS ์ํID,
CASE GROUPING(์) WHEN 1 THEN '๋ชจ๋ ์' ELSE ์ END AS ์,
SUM(๋งค์ถ์ก) AS ๋งค์ถ์ก
FROM ์๋ณ๋งค์ถ
GROUP BY ROLLUP(์ํID, ์);
์ํID | ์ | ๋งค์ถ์ก | GROUPING(์ํID) | GROUPING(์) |
---|---|---|---|---|
P1 | 1 | 1000 | 0 | 0 |
P1 | 2 | 2000 | 0 | 0 |
P1 | ๋ชจ๋ ์ | 3000 | 0 | 1 |
P2 | 1 | 1000 | 0 | 0 |
P2 | 1 | 3000 | 0 | 0 |
P2 | ๋ชจ๋ ์ | 4000 | 0 | 1 |
P3 | 2 | 4000 | 0 | 0 |
P3 | ๋ชจ๋ ์ | 4000 | 0 | 1 |
๋ชจ๋ ์ํ ID | ๋ชจ๋ ์ | 11000 | 1 | 1 |
GROUPING
์ ์ง์ ์ ์ผ๋ก ๊ทธ๋ฃน๋ณ ์ง๊ณ๋ฅผ ๊ตฌํ๋ ํจ์๋ ์๋์ง๋ง ์์ ์ง๊ณํจ์๋ค์ ์ง์ํ๋ ํจ์์ด๋ค.
์ง๊ณ๊ฐ ๊ณ์ฐ๋ ๊ฒฐ๊ณผ์ ๋ํด์๋ 1์ ๊ฐ์ ๊ฐ๊ณ ๊ทธ๋ ์ง ์์ ๊ฒฐ๊ณผ์ ๋ํด์๋ 0์ ๊ฐ์ ๊ฐ๋๋ค.
CASE WHEN
๋ฌธ์ ์ฌ์ฉํด์ ๋งจ ์ฒ์์ ๋จ์ ROLL UP
ํจ์๋ง ์ผ์ ๋ NULL๊ฐ์ผ๋ก ํ์๋์๋ ๊ณณ์ ๊ฐ์ ๋ฃ์ด์ฃผ์๋ค. ์ง๊ณ๊ฐ ๊ณ์ฐ๋ ๊ฒฐ๊ณผ์ ๋ํด์๋ง ๊ฐ์ ๋ฃ์ด์ฃผ๋ฉด ๋๊ธฐ ๋๋ฌธ์ GROUPING(์ปฌ๋ผ๋ช
)=1์ธ ๊ฒฝ์ฐ์๋ง '๋ชจ๋ ์ํID' ๋๋ '๋ชจ๋ ์' ๊ฐ์ ๋ถ์ฌํ๊ณ 0์ธ ๊ฒฝ์ฐ์๋ ์๋๋๋ก ์ํID์ ์์ ์จ์ฃผ์๋ค.
GROUP BY
๋ ์ง์ฝ ๊ธฐ๋ฅ์ผ๋ก ์ธํด ํ ์๊ฐ ์ค์ด๋ ๋ฐ๋ฉด, ์๋์ฐ ํจ์๋ ํ ์๊ฐ ๊ทธ๋๋ก ๋จ์์๋ค.
์๋์ฐ ํจ์์๋ ์ง์ฝ์ ๊ธฐ๋ฅ์ด ์๊ธฐ ๋๋ฌธ์ด๋ค.
์ด๋ฌํ ํน์ง์ ์ด์ฉํด์ ์ฐ๋ฆฌ๋ ํ๊ณผ ํ๊ฐ์ ๊ด๊ณ๋ฅผ ํธํ๊ฒ ๋ค๋ฃฐ ์ ์๊ฒ ๋๋ค.
PARTITION BY
๋ฅผ ๋ถํ๋ฉด ํด๋น ๋ฐ์ดํฐ ๋ณ๋ก ์์๊ฐ ์ง์ ๋๋ค. ๋ถํ์ง ์์ผ๋ฉด ์ ์ฒด ํ์ ๋ํด ์ ์ฉ๋๋ค.
RANK
SELECT JOB, ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 3
CLERK ADAMS 1300 3
CLERK JAMES 950 5
CLERK SMITH 800
SELECT JOB, ENAME, SAL,
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1300 1
CLERK JAMES 950 3
CLERK SMITH 800 4
ORDER BY๋ฅผ ํฌํจํ ์ฟผ๋ฆฌ๋ฌธ์์ ํน์ ์ปฌ๋ผ์ ์์๋ฅผ ๊ตฌํ๋ ํจ์์ด๋ค. PARTITION
๋ด์์ ์์๋ฅผ ๊ตฌํ ์๋ ์๊ณ ์ ์ฒด ๋ฐ์ดํฐ์ ๋ํ ์์๋ฅผ ๊ตฌํ ์๋ ์๋ค. ๋์ผํ ๊ฐ์ ๋ํด์๋ ๊ฐ์ ์์๋ฅผ ๋ถ์ฌํ๋ฉฐ ์ค๊ฐ ์์๋ฅผ ๋น์ด๋ค.
์๋ฅผ ๋ค์ด ๋์ผํ ๊ฐ์ด ์๋ ๊ฒฝ์ฐ ์์๋ 1,1,3,4,4,6 ์ด๋ฐ์์ธ ์
DENSE_RANK
SELECT JOB, ENAME, SAL,
DENSE_RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1300 1
CLERK JAMES 950 2
CLERK SMITH 800 3
RANK
์ ์๋๋ฒ์ ๋์ผํ์ง๋ง, ๋์ผํ ๊ฐ์ ๋ํด์๋ ๊ฐ์ ์์๋ฅผ ๋ถ์ฌํ๊ณ ์ค๊ฐ ์์๋ฅผ ๋น์ฐ์ง ์๋๋ค.
์๋ฅผ๋ค์ด ๋์ผํ ๊ฐ์ด ์๋ ๊ฒฝ์ฐ ์์๋ 1,1,2,3,3,4 ์ด๋ฐ ์
ROW_NUMBER
SELECT JOB, ENAME, SAL,
ROW_NUMBER() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 2
CLERK MILLER 1300 1
CLERK ADAMS 1300 2
CLERK JAMES 950 3
CLERK SMITH 800 4
RANK
, DENSE_RANK
๋ ๋์ผํ ๊ฐ์ ๋ํด ๋์ผ ์์๋ฅผ ๋ถ์ฌํ์ง๋ง ROW_NUMBER
์ ๋์ผํ ๊ฐ์ด์ด๋ ๊ณ ์ ํ ์์๋ฅผ ๋ถ์ฌํ๋ค.
FIRST_VALUE
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
ROWS UNBOUNDED PRECEDING) DEPT_RICH
FROM EMP ;
ํํฐ์ ๋ณ ์๋์ฐ์์ ๊ฐ์ฅ ๋จผ์ ๋์จ ๊ฐ์ ๊ตฌํ ์ ์๋ค.
LAST_VALUE
SELECT DEPTNO, ENAME, SAL,
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) DEPT_POOR
FROM EMP ;
ํํฐ์ ๋ณ ์๋์ฐ์์ ๊ฐ์ฅ ๋ง์ง๋ง์ ๋์จ ๊ฐ์ ๊ตฌํ ์ ์๋ค.
LAG
SELECT ENAME, HIREDATE, SAL
, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600
WARD 22-FEB-81 1250 1600
TURNER 08-SEP-81 1500 1250
MARTIN 28-SEP-81 1250 1500
--HIREDATE๋ฅผ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ๊ณ ๋ณธ์ธ๋ณด๋ค ์
์ฌ์ผ์๊ฐ ํ๋ ๋ ์์ ์ฌ์์ ๊ธ์ฌ๋ฅผ ์ถ๋ ฅ
SELECT ENAME, HIREDATE, SAL
, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600 0
WARD 22-FEB-81 1250 0
TURNER 08-SEP-81 1500 1600
MARTIN 28-SEP-81 1250 1250
--HIREDATE๋ฅผ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ๊ณ ๋ณธ์ธ๋ณด๋ค ์
์ฌ์ผ์๊ฐ ๋ ๊ฐ ๋ ์์ ์ฌ์์ ๊ธ์ฌ๋ฅผ ์ถ๋ ฅ
--๋ ๊ฐ ๋ ์์ ์ฌ์์ด ์์ ๊ฒฝ์ฐ 0์ ์ถ๋ ฅ
์ด์ ๋ช ๋ฒ์งธ ํ์ ๊ฐ์ ๊ฐ์ ธ์ค๋ ํจ์์ด๋ค. ์ธ์๋ฅผ ์ต๋ 3๊ฐ๊น์ง ๊ฐ์ง๋ค.
๋๋ฒ์งธ ์ธ์๋ ๋ช ๋ฒ์งธ ์์ ํ์ ๊ฐ์ ธ์ฌ์ง ๊ฒฐ์ ํ๋ ๊ฒ์ด๋ฉฐ DEFAULT๊ฐ์ 1์ด๋ค. ์ธ๋ฒ์งธ ์ธ์๋ ๊ฐ์ ธ์ฌ ํ์ด ์์ ๊ฒฝ์ฐ DEFAULT๊ฐ์ ์ง์ ํด์ฃผ๋ ๊ฒ์ผ๋ก NVL
์ด๋ ISNULL
ํจ์์ ๊ธฐ๋ฅ๊ณผ ๋์ผํ๋ค.
LEAD
SELECT ENAME, HIREDATE
, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"
FROM EMP;
ENAME HIREDATE NEXTHIRED
---------- --------- ---------
SMITH 17-DEC-80 20-FEB-81
ALLEN 20-FEB-81 22-FEB-81
WARD 22-FEB-81 02-APR-81
JONES 02-APR-81 01-MAY-81
BLAKE 01-MAY-81 09-JUN-81
CLARK 09-JUN-81
--HIREDATE๋ฅผ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ๊ณ ๋ณธ์ธ๋ณด๋ค HIREDATE๊ฐ ํ๋ ๋ ๋ค์ธ ๋ ์ง๋ฅผ ์ถ๋ ฅ
--์๋ ๊ฒฝ์ฐ NULL
์ดํ ๋ช ๋ฒ์งธ ํ์ ๊ฐ์ ๊ฐ์ ธ์ค๋ ํจ์๋ก LAG์ ๋ง์ฐฌ๊ฐ์ง๋ก ์ธ์๋ฅผ ์ต๋ 3๊ฐ๊น์ง ๊ฐ๋๋ค.
์๋์ฐ ํจ์() OVER (partition by ์ปฌ๋ผ order by ์ปฌ๋ผ RANGE BETWEEN A AND B)
์๋์ฐ ํจ์() OVER (partition by ์ปฌ๋ผ order by ์ปฌ๋ผ ROWS BETWEEN A AND B)
UNBOUNDED PRECEDING // ์ต์ข
์ถ๋ ฅ๋ ๊ฐ์ ๋งจ ์ฒ์ row ๊ฐ
CURRENT ROW // ํ์ฌ row ๊ฐ
UNBOUNDED FOLLOWING // ์ต์ข
์ถ๋ ฅ๋ ๊ฐ์ ๋งจ ๋ง์ง๋ง row ๊ฐ
// ์ง์
๋ณ ์๊ธ ๋์ ํฉ๊ณ ๊ตฌํ๊ธฐ
SELECT ENAME, JOB, SAL,
SUM(SAL) OVER (PARTITION BY JOB) AS ์๊ธ ๋์ ํฉ๊ณ
FROM EMP
// ์ง์
๋ณ ์๊ธ ๋์ ํฉ๊ณ ๊ตฌํ๊ณ , ์๊ธ ์์๋๋ก ๋ณด์ฌ์ฃผ๊ธฐ
SELECT ENAME, JOB, SAL,
SUM(SAL) OVER (PARTITION BY JOB ORDER BY SAL) AS ์๊ธ ๋์ ํฉ๊ณ
FROM EMP
MAX(์ด) OVER (PARTITION BY ๊ทธ๋ฃน์ด) -- ์์) ์ต๋๊ฐ
SUM(์ด) OVER (ORDER BY ์์์ด) -- ๋์ ํฉ
SUM(์ด) OVER (ORDER BY ์์์ด PARTITION BY ๊ทธ๋ฃน์ด) -- ๊ทธ๋ฃน๋ผ๋ฆฌ ๋์ ํฉ
SELECT MGR, ENAME, SAL,
SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP ;
RATIO_TO_REPORT
SELECT ENAME, SAL
, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME SAL R_R
---------- ---------- ----------
ALLEN 1600 .29
WARD 1250 .22
MARTIN 1250 .22
TURNER 1500 .27
--์ ์ฒด ๊ธ์ฌ์์ ๊ฐ๊ฐ์ด ์ฐจ์งํ๋ ๋น์จ ์ถ๋ ฅ
ํํฐ์ ๋ด ์ ์ฒด SUM๊ฐ์ ๋ํ ํ๋ณ ์ปฌ๋ผ ๊ฐ์ ๋ฐฑ๋ถ์จ์ ์์์ ์ผ๋ก ์ถ๋ ฅํ๋ค. ๊ฒฐ๊ณผ๊ฐ์ 0~1 ์ฌ์ด์ด๋ฉฐ ๊ฐ๋ณ ๋น์จ์ ํฉ์ ๊ตฌํ๋ฉด 1์ด๋ค.
PERCENT_RANK
SELECT DEPTNO, ENAME, SAL
, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
FROM EMP;
DEPTNO ENAME SAL P_R
---------- ---------- ---------- ----------10 KING 5000 0
10 CLARK 2450 .5
10 MILLER 1300 1
20 SCOTT 3000 0
20 FORD 3000 0
20 JONES 2975 .5
20 ADAMS 1100 .75
20 SMITH 800 1
30 BLAKE 2850 0
30 ALLEN 1600 .2
30 TURNER 1500 .4
30 MARTIN 1250 .6
30 WARD 1250 .6
30 JAMES 950 1
--DEPTNO๋ฅผ ๊ธฐ์ค์ผ๋ก ํํฐ์
๋ํด์ ์์๋ณ ๋ฐฑ๋ถ์จ ์ถ๋ ฅ
ํํฐ์ ๋ณ๋ก ๊ฐ์ฅ ๋จผ์ ย ๋์ค๋ย ๊ฐ์ย 0,ย ๊ฐ์ฅย ๋ง์ง๋ง์ย ๋์ค๋ย ๊ฐ์ย 1๋กย ํด์ย ํย ์์๋ณย ๋ฐฑ๋ถ์จย ์ถ๋ ฅํ๋ค. ๊ตฌ๊ฐ์ ๋๋์ด ๋ฐฑ๋ถ์จ๋ก ํ์ํ๋ค.
CUME_DIST
SELECT DEPTNO, ENAME, SAL
, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST
FROM EMP;
DEPTNO ENAME SAL CUME_DIST
---------- ---------- ---------- ----------10 KING 5000 .333333333
10 CLARK 2450 .666666667
10 MILLER 1300 1
20 SCOTT 3000 .4
20 FORD 3000 .4
20 JONES 2975 .6
20 ADAMS 1100 .8
20 SMITH 800 1
30 BLAKE 2850 .166666667
30 ALLEN 1600 .333333333
30 TURNER 1500 .5
30 MARTIN 1250 .833333333
30 WARD 1250 .833333333
30 JAMES 950 1
--DEPTNO๋ฅผ ๊ธฐ์ค์ผ๋ก ํํฐ์
๋ํด์ ๋์ ๋ฐฑ๋ถ์จ ์ถ๋ ฅ
ํํฐ์
๋ณย ์ ์ฒด๊ฑด์์์ย ํ์ฌย ํ๋ณด๋คย ์๊ฑฐ๋ย ๊ฐ์ย ๊ฑด์์ย ๋ํย ๋์ ๋ฐฑ๋ถ์จ
์๊ตฌํ๋ค.
NTILE
SELECT ENAME, SAL
, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE
FROM EMP ;
ENAME SAL QUAR_TILE
---------- ---------- ----------
KING 5000 1
FORD 3000 1
SCOTT 3000 1
JONES 2975 1
BLAKE 2850 2
CLARK 2450 2
ALLEN 1600 2
TURNER 1500 2
MILLER 1300 3
WARD 1250 3
MARTIN 1250 3
ADAMS 1100 4
JAMES 950 4
SMITH 800 4
--์ ์ฒด๊ฑด์๋ฅผ ARGUMENT๊ฐ(์ฌ๊ธฐ์๋ 4)์ผ๋ก N๋ฑ๋ถํ ๊ฒฐ๊ณผ ์ถ๋ ฅ--14๋ฅผ 4๋ก ๋๋๋ฉด ๋ชซ์ด 3 ๋๋จธ์ง๊ฐ 2, ๋๋จธ์ง2๋ ์์ ์กฐ๋ถํฐ ํ ๋น๋๊ธฐ ๋๋ฌธ์ 4,4,3,3์ผ๋ก ๋๋
ํํฐ์ ๋ณย ์ ์ฒดย ๊ฑด์๋ฅผย ARGUMENT๊ฐ์ผ๋กย N๋ฑ๋ถํย ๊ฒฐ๊ณผ๋ฅผ ์ถ๋ ฅํ๋ค.
DBMS
์ฌ์ฉ์๋ฅผ ์์ฑํ๋ฉด ๊ธฐ๋ณธ์ ์ผ๋ก ๋ง์ ๊ถํ์ ๋ถ์ฌํด์ผํ๋ค.
๋ง์ DBMS
์์๋ DBMS
๊ด๋ฆฌ์๊ฐ ์ฌ์ฉ์๋ณ๋ก ๊ถํ์ ๊ด๋ฆฌํด์ผ ํ๋ ๋ถ๋ด๊ณผ ๋ณต์กํจ์ ์ค์ด๊ธฐ ์ํ์ฌ ๋ค์ํ ๊ถํ์ ๊ทธ๋ฃน์ผ๋ก ๋ฌถ์ด ๊ด๋ฆฌํ ์ ์๋๋ก ์ฌ์ฉ์์ ๊ถํ ์ฌ์ด์์ ์ค๊ฐ ์ญํ ์ ์ํํ๋ ROLE
์ ์ ๊ณตํ๋ค.
// ์ฌ์ฉ์ U1, U2, U3์๊ฒ professor ํ
์ด๋ธ์ ๋ํ select ๋ฌธ์ฅ์ ์ฌ์ฉํ ์ ์๋ ๊ถํ์ ๋ถ์ฌ
Grant select on professor to U1, U2, U3;
// ์ฌ์ฉ์ U4์๊ฒ professor ํ
์ด๋ธ์ ๋ํ select ๋ฌธ์ฅ์ ์ฌ์ฉํ ์ ์๋ ๊ถํ์ ๋ถ์ฌํ๊ณ
// ๋ํ U4์๊ฒ ๋ฐ์ ๊ถํ์ ๋ค๋ฅธ ์ฌ์ฉ์์๊ฒ ๋ถ์ฌํ ์ ์๋ ๊ถํ์ ํจ๊ป ๋ถ์ฌ
Grant select on professor to U4 with grant option;
// Lee ์ฌ์ฉ์์๊ฒ department ํ
์ด๋ธ์ deptName ์์ฑ์ ์ฐธ์กฐํ๋ ์ธ๋ํค๋ฅผ ์์ฑํ๋ ๊ถํ์ ๋ถ์ฌ
Grant references (deptName) on department to Lee;
// ๋ชจ๋ ์ฌ์ฉ์์๊ฒ ๊ถํ๋ถ์ฌ
Grant select on professor to public;
// U1์๊ฒ๋ง professor ํ
์ด๋ธ์ ๋ํ select ๊ถํ์ ํ์ํ๋ผ
Revoke select on professor from U1
// U1, U2, U3๋ ๋ฌผ๋ก ์ด๊ณ U1,U2,U3๊ฐ ์ธ์ง๋ฌ๋์ ๊ถํ๊น์ง๋ ๋ชจ๋ ์ฒ ํํ์์ค
Revoke select on professor from U1, U2, U3 cascade;
// U1, U2, U3๊ฐ ์๋ฌด๊ฒ๋ ์ธ์ง๋ฌ๋์ง ์์ ๊ฒฝ์ฐ์๋ง ์๋ค ๊ถํ์ ๋ชจ๋ ์ฒ ํํ์์ค.
Revoke select on professor from U1, U2, U3 restrict;
// ๋ชจ๋ ์ฌ์ฉ์ ๊ถํ ์ฒ ํ
revoke select on professor from public
cascade
๋ง์ฝ์ ์ฌ์ฉ์ U1์ด U2์๊ฒ ํน์ ๊ถํ์ ์ฃผ์๊ณ , ๋ U2๊ฐ ๋์ผ ๊ถํ์ ์ฌ์ฉ์ U3์๊ฒ ๋ถ์ฌํ์ ๋, ์ด ๊ฒฝ์ฐ ์ฌ์ฉ์ U1์ด U2์๊ฒ ๊ถํ ์ทจ์๋ฅผ ํ๋ค๋ฉด ์ด๋ป๊ฒ ๋ ๊น?
cascade
ย ์ต์
์ ๊ถํ ์ทจ์ ์์ ์ทจ์๋๋ ๊ถํ์ผ๋ก ์ธํ์ฌ ํจ๊ป ์ทจ์๊ฐ ๋์ด์ผ ํ๋ ๊ถํ์ด ์์ผ๋ฉด ๊ทธ ๊ถํ๋ ํจ๊ป ์ทจ์ํ๋ ๊ฒ์ด๋ฉฐ, ์ฆ U1์ด U2์๊ฒ ๊ถํ์ทจ์ ๋ช
๋ น์ ๋ด๋ ธ์ ๋, ๊ฑฐ๊ธฐ์ ๋ธ๋ฆฐ ๋ U3์ ๊ถํ๋ ์ฒ ํ๋๋ค.
restrict
๋ง์ฝ์ ์ฌ์ฉ์ U1์ด U2์๊ฒ ํน์ ๊ถํ์ ์ฃผ์๋๋ฐ, U3๊ฐ U2์๊ฒ ๋์ผ๊ถํ์ ๋ถ์ฌํ์๋ค๋ฉด ์ฌ์ฉ์ U1์ดย Revoke select on professor from U2;
ย ๋ผ๋ ๊ตฌ๋ฌธ์ ์
๋ ฅํ์ ๋ U2์ select๊ถํ์ ์์ด์ง๊น?
์ด ๊ฒฝ์ฐ ์ฌ์ฉ์ U3๋ย Revoke select on professor from U2;
ย ๋ฅผ ์
๋ ฅํด์ผ U2๋ professor์๋ํ select๊ถํ์ด ์์ ํ ์์ด์ง๊ฒ ๋๋ค.
- A / 0 โ 0
- 0 / A โ ERROR
- 0 / NULL, NULL / 0, A / NULL, NULL / A โ NULL
- ๋ชจ๋ฅด๋ ๊ฐ์ ์๋ฏธํ๋ค.
- ๊ฐ์ ๋ถ์ฌ๋ฅผ ์๋ฏธํ๋ค.
NULL
๊ณผ์ ๋ชจ๋ ๋น๊ต๋Unknown
์ ๋ฐํํ๋ค.NULL
๊ณผ์ ๋ชจ๋ ์ฐ์ฐ์NULL
์ ๋ฐํํ๋ค.
- orcle์ โโ๋ก ์ ๋ ฅํ๋ฉด null๋ก ์ธ์ / sql์ โโ๋ก ์ธ์
- Oracle์์๋
NULL
์ ๊ฐ์ฅ ํฐ ๊ฐ์ผ๋ก ๊ฐ์ฃผํ๋ค. - SQL์์๋
NULL
์ ๊ฐ์ฅ ์์ ๊ฐ์ผ๋ก ๊ฐ์ฃผํ๋ค.