[SQL] cheating sheet 정리

아래 링크 내용을 정리함

www.sqltutorial.org/sql-cheat-sheet/

SQL Cheat Sheet Download PDF it in PDF or PNG Format

 

 

실제 데이터로 쿼리를 사용하는 실습은 아래 링크에서 가능

www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

SQL Tryit Editor v1.6

 

 

 

 

SQL 문법 순서 : 해석 우선순위

 

SELECT column_name(s) : 5
FROM table_name : 1
WHERE condition : 2
GROUP BY column_name(s) : 3
HAVING condition : 4
ORDER BY column_name(s) : 6

 

어떤 SQL 구문이 있을 때는 FROM 부터 해석하고 그 다음에는 WHERE 해석하고...

마지막에는 ORDER BY 를 해석한다는 의미

 

 

 

테이블 schema 확인

 

DESC [table 이름];

 

 

 

 

컬럼 쿼리하기

 

SELECT c1, c2 FROM t;

SELECT * FROM t;

 

 

 

Alias 사용하기

 

Table 과 Select 문 에 사용 가능

 

SELECT column_name AS alias_name
FROM table_name;

 

SELECT column_name(s)
FROM table_name AS alias_name;

 

 

 

 

 

where 사용하기

 

SELECT c1, c2 FROM t WHERE condition;

SELECT * FROM Customers WHERE Country='Mexico';

SELECT * FROM Customers WHERE CustomerID=1;

SELECT * FROM Customers WHERE CustomerID<5;

SELECT * FROM Customers WHERE CustomerID>5;

SELECT * FROM Customers WHERE CustomerID<=10;

SELECT * FROM Customers WHERE CustomerID>=10;

SELECT * FROM Customers WHERE CustomerID<>7;

SELECT * FROM Customers WHERE CustomerID IS NULL;

SELECT * FROM Customers WHERE CustomerID IS NOT NULL;

 

만약 날짜 date column 이 yyyy-MM-dd hh:mm:ss 이런 형태라고 하자

 

SELECT date FROM Customers WHERE DATE(date)='2021-01-01'

SELECT date FROM Customers WHERE DATE(date)='2021-01-01 13:30:12'

SELECT date FROM Customers WHERE DATE(date) BETWEEN '2021-01-01' AND '2021-01-31'

SELECT date FROM Customers WHERE DATE(date) BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:59'

 

 

 

where 에 between 사용하기 : a이상 b 이하의 값

아래 예제에서는 50 과 60을 모두 포함하여 사이의 값을 쿼리함

 

SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;

 

 

 

where 에 like 사용하기 : 패턴으로 찾기

% : 없거나, 하나 이상. 와일드카드의 * 이라고 생각하면 좋겠다.

_ : 하나. 와일드카드의 ? 라고 생각하면 좋겠다.

 

SELECT * FROM Customers WHERE City LIKE 's%';

SELECT * FROM Customers WHERE City NOT LIKE 's%';

SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';

 

 

LIKE 말고 ILIKE 도 있음

LIKE 는 대소문자를 가리지만, ILIKE 는 대소문자를 가리지 않음

예를 들어

select * from customers where city LIKE 's%'; 

는 무조건 소문자 s 로 시작하는 도시 이름만을 가져오지만

select * from customers where city ILIKE 's%';

는 대문자 S, 소문자 s 로 시작하는 모든 도시 이름을 가져옴

 

 

 

where 에 in 사용하기 : 리스트에 포함되는 값 찾기

 

SELECT * FROM Customers WHERE City IN ('Paris','London');

SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');

SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

 

 

 

 

where 에 ANY, ALL 사용하기

 

ANY, ALL 은 WHERE 혹은 HAVING 문과 함께 사용됨.

ANY, ALL 은 IN 과 다르게 비교연산자를 사용한다.

ANY 를 SOME 이라고도 한단다.

설명보다 예문을 보는 게 이해가 더 쉽다.

 

SELECT ages FROM MyTable WHERE ages = ANY(1, 4)

(SELECT ages FROM MyTable WHERE ages=1 OR ages=4 와 같다)

 

MyTable 내의 ages 중, ANY 의 list 내에 값이 하나라도 만족하면 where 문은 true 가 된다.

예를 들어 MyTable 의 ages 값이 1, 2, 3, 4 이라고 할 때

MyTable 의 ages 가 1일 때 : 1이 (1,4) 에 적어도 하나가 포함되어 있으므로 true

MyTable 의 ages 가 2일 때 : 2가 (1,4) 에 적어도 하나가 포함되어있지 않으므로 false

MyTable 의 ages 가 3일 때 : 3이 (1,4) 에 적어도 하나가 포함되어있지 않으므로 false

MyTable 의 ages 가 4일 때 : 4가 (1,4) 에 적어도 하나가 포함되어 있으므로 true

따라서 select 의 결과는 1,4 가 된다.

 

SELECT ages FROM MyTabl eWHERE ages < ANY(1, 3)

(SELECT ages FROM MyTable WHERE ages<1 OR ages<3 과 같다)

 

MyTable 의 ages 가 1일 때 : 1<1 은 false 이지만 1<3 은 true 이므로 1일 때의 조건문은 true

MyTable 의 ages 가 2일 때 : 2<1 는 false 이지만 2<3 은 true 이므로 2일 때의 조건문은 true

MyTable 의 ages 가 3일 때 : 3<1 는 false 이고 3<3 도 false 이므로 3일 때의 조건문은 false

MyTable 의 ages 가 4일 때 : 4<1 는 false 이고 4<3 도 false 이므로 4일 때의 조건문은 false

따라서 select 의 결과는 1,2 가 된다.

 

SELECT ages FROM MyTable WHERE ages = ALL(1, 4)

(SELECT ages FROM MyTable WHERE ages=1 AND ages=4 와 같다)

 

MyTable 내의 ages 중, ALL 의 list 내 모든 값이 만족해야 where 문은 true 가 된다.

예를 들어 MyTable 의 ages 값이 1, 2, 3, 4 이라고 할 때

MyTable 의 ages 가 1일 때 : list 에 1이 포함되지만 4는 포함되지 않으므로 false

MyTable 의 ages 가 2일 때 : list 의 1, 4가 모두 포함되지 않으므로 false

MyTable 의 ages 가 3일 때 : list 의 1, 4가 모두 포함되지 않으므로 false

MyTable 의 ages 가 4일 때 : list 에 4가 포함되지만 1은 포함되지 않으므로 false

따라서 select 의 결과는 없다.

만약 ALL(1) 이었다면, 결과값이 1이 나올 것임.

만약 ALL(3) 이었다면, 결과값이 3이 나올 것임.

 

SELECT ages FROM MyTable WHERE ages < ALL(3, 4)

(SELECT ages FROM MyTable WHERE ages<3 AND ages<4 와 같다)

 

MyTable 의 ages 가 1일 때 : 1<3 은 true 이고 1<4 도 true 이므로 1일 때의 조건문은 true

MyTable 의 ages 가 2일 때 : 2<3 은 true 이고 2<4 도 true 이므로 2일 때의 조건문은 true

MyTable 의 ages 가 3일 때 : 3<3 은 false 이고 3<4 도 false 이므로 3일 때의 조건문은 false

MyTable 의 ages 가 4일 때 : 4<3 은 false 이고 4<4 도 false 이므로 4일 때의 조건문은 false

따라서 select 의 결과는 1,2 가 된다.

 

참고 : carami.tistory.com/18

 

위의 쿼리문처럼, ANY, ALL 에 들어가는 list 는 SELECT 문을 사용하여 만들수 도 있다.

 

SELECT ProductName
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);

 

 

 

날짜 함수

 

SELECT EXTRACT(year FROM current_timestamp())

SELECT EXTRACT(month FROM current_timestamp()) 

SELECT EXTRACT(day FROM current_timestamp())

SELECT EXTRACT(quarter FROM current_timestamp())

SELECT TO_CHAR(current_timestamp(), 'yyyy-MM-dd')

SELECT DATE_FORMAT(current_timestamp(), 'yyyy-MM-dd')

SELECT DATE(current_timestamp()) FROM mytable

SELECT STR_TO_DATE('19910625', '%Y-%M-%D')

 

 

 

 

 

값 정렬하기

 

SELECT c1, c2 FROM t ORDER BY c1 ASC;

SELECT c1, c2 FROM t ORDER BY c1 DESC;

 

 

 

 

앞에서부터 offset 만큼 건너 뛰고, n 개 만큼 쿼리

LIMIT 은 혼자 쓸 수 있지만, OFFSET 은 LIMIT 과 같이 써야 함

 

SELECT c1, c2 FROM t ORDER BY c1 LIMIT n OFFSET offset;

 

 

 

 

숫자 계산

 

SELECT abc(c1) FROM t; : 절대값

SELECT ceil(c1) FROM t; : 값보다 큰 정수 중 가장 작은 정수 (소수점 이하 올림)

SELECT floor(c1) FROM t; : 값보다 작은 정수 중 가장 큰 정수 (소수점 이하 버림)

SELECT pow(x, y) FROM t; : X의 Y제곱. power(X,Y) 로 쓰기도 함.

SELECT mod(분자, 분모) FROM t; : 분자를 분모로 나눈 나머지. mod는 % 로 대체 가능

SELECT gratest(c1, c2, c3...) FROM t; : 주어진 숫자 중에 가장 큰 값 반환

SELECT least (c1, c2, c3...) FROM t; : 주어진 숫자 중에 가장 작은 값 반환

SELECT round(c1) FROM t; : 반올림

SELECT round(숫자, 자릿수) FROM t; : 자릿수를 기준으로 반올림
SELECT truncate(숫자, 자릿수) FROM t; : 자릿수를 기준으로 버림 

SELECT stddev(c1) FROM t; : 표준편차

SELECT variant(c1) FROM t; : 분산

 

 

 

집계 함수(aggregate)

반드시 group by 와 함께 써야하는 것은 아님

 

SELECT count(*) FROM t; : null 값까지 함께 셈

SELECT count(id) FROM t; : null 값은 제외하고 셈

SELECT sum(c1) FROM t; : null 값을 제외한 합계

SELECT sum(c1) FROM t GROUP BY c1; : null 값을 제외한 합계

SELECT max(c1) FROM t;

SELECT min(c1) FROM t GROUP BY c1;

SELECT avg(c1) FROM t; : null 값을 제외한 평균

 

Group By 를 사용했는데 여러 값이 나오는 컬럼을 단독으로 쿼리하면

그룹의 가장 첫번째 행이 출력됨

예를 들어

SELECT * FROM Customers GROUP BY country;

하면 각 country 그룹의 첫번째 행들이 출력됨

 

 

 

 

 

Having 사용하기

Having 은 Group By 와 사용되어짐

그룹을 어떤 조건으로 필터링할 때 사용됨

 

SELECT c1, aggregate(c2) FROM t GROUP BY c1 HAVING condition;

 

아래 예제는 Country 로 그룹지었을 때 CustomerID 수가 5 이상인 행을 찾음

 

SELECT COUNT(CustomerID) FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > =5;

 

 

 

 

 

EXISTS 사용하기

 

어떤 쿼리문의 결과가 존재하면 true, 존재하지 않으면 false 를 내보내는 함수가 EXISTS 함수임.

예를 들어 "Suppliers 와 Products 의 id 가 동일하고 Price 가 20보다 적은 값이 존재한다면...."

을 조건문으로 만든 쿼리문은 다음과 같음.

 

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Products.Price < 20);

 

 

 

 

 

 

 

 

CASE 사용하기

 

if 문처럼 사용할 수 있다.

 

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

 

SELECT Id, Sex, Name,
CASE
    WHEN Age >= 50 THEN 'group D'

    WHEN Age >= 40 THEN 'group C'

    WHEN Age >= 30 THEN 'group B'
    WHEN Age >= 20 THEN 'group A'

    ELSE 'Too Young'
END AS Group
FROM People;

 

SELECT Id, AAA, BBB, CCC
FROM MyTable
ORDER BY
(CASE
    WHEN OrderStandard=false THEN Id
    ELSE OrderStandard
END);

 

SELECT

  (CASE

    WHEN category = 1 AND supplier = 1 THEN 'A'

    WHEN category = 2 OR supplier = 3 THEN 'B'

    ELSE 'C'

  END) as 'nothing'

FROM MyTable;

 

 

 

 

 

 

 

Join 종류는 다음과 같음

 

 

join 의 개념을 간단한 테이블을 이용하여 알아보자.

아래와 같이, aaa, bbb, ccc 라는 이름의 테이블이 있다고 하자.

각각의 테이블마다 _id 라는 column 이 있고,

aaa,bbb 는 name 이라는 column,

ccc는 col 이라는 column 이 있다(이름 대충 지음)

 

 

* INNER JOIN

 

inner join 부터 알아보자.

만약 aaa 와 bbb 를 inner join 하게 되면 위의 다이어그램에서 본 것처럼

aaa 와 bbb 가 서로 겹치는, 다시 말해 서로 값을 갖고 있는 row 들이 결과로 도출된다.

참고로 쿼리문에서 join 만 쓰게되면 inner join 으로 동작함.

 

SELECT * FROM aaa

JOIN bbb ON aaa._id = bbb._id;

 

 

마찬가지로, aaa를 ccc 와 join 하면 다음과 같은 결과가 나온다.

 

SELECT * FROM aaa

JOIN ccc ON aaa._id = ccc._id;

 

 

 

* LEFT JOIN, RIGHT JOIN

 

aaa 를 기준으로 bbb 와 LEFT JOIN 을 하면 다음과 같다.

 

SELECT * FROM aaa
LEFT JOIN bbb on aaa._id = bbb._id;

 

aaa 를 기준으로 aaa 의 모든 _id 에 해당하는 row 가 결과에 나타나게 된다.

bbb 에 없는  1,3,9 _id 부분은 어떻게 처리될까? null 로 나타남.

 

반대로 RIGHT JOIN 을 하면 다음과 같다.

 

SELECT * FROM aaa
RIGHT JOIN bbb on aaa._id = bbb._id;

 

물론 여기서는 aaa 의 4,6 부분의 name 이 null 로 나타나게 됨.

 

 

* FULL OUTER JOIN

 

양 테이블의 값을 기준으로 모든 row 를 가져와 합쳐준다.

이번에는 bbb 와 ccc 를 full outer join 해본다.

 

SELECT * FROM bbb
FULL OUTER JOIN ccc on bbb._id = ccc._id;

 

둘 다 없는 부분은 제외되고(노랗지 않은 부분)

한 쪽엔 있고 한 쪽엔 없는 부분은 null 값으로 나온다.

 

 

 

* CROSS JOIN

 

다이어그램에서는 나오지 않았지만, Cross Join 이라는 것도 있다.

Cartesian Product 라고도 하는데 이 join 은 양 테이블의 모든 row 를 하나하나 결합한다.

예를 들어 aaa 와 bbb 를 cross join한다고 하자.

aaa 의 모든 row 에 bbb 의 모든 row 를 결합한다.

 

https://www.sqltutorial.org/sql-cross-join/

 

... 아래에 더 많이 있으나 화면이 모자름

aaa 에 7개, bbb 에 6개의 row 가 있었으니

7 x 6 = 42, 총 42개의 row 가 생성된다.

 

cross join 은 양이 기하급수적으로 늘어나기때문에 성능에 안 좋은 영향을 끼치므로,

전체 테이블을 cross join 하는 우를 범하지 말아야한다.

가급적 cross join 은 피하되, 불가피하게 써야 한다면 소규모의 테이블끼리 사용하기를 권장함.

 

 

 

자기 자신과 join 할 수 있다.

즉, aaa 가 aaa 자신과 join 이 가능하다는 말.
원리는 위에서 말한 것과 똑같음.

 

또한 3개의 테이블을 모두 join 하는 것도 가능하다.

간단하게 말하면,

aaa, bbb, ccc 를 모두 join 한다고 할 때

aaa 와 bbb 를 먼저 join 하고 그 결과값과 ccc 를 join 한다(....)

간단하쥬?

 

 

 

 

SELECT CAST('5' AS INTEGER)

SELECT CAST(date AS TIMESTAMP)

SELECT CAST('12345' AS VARCHAR)

 

 

 

 

 

Union 사용하기

 

쿼리 A 와 쿼리 B 의 결과를 합칠 수 있음.

다시 말해, 쿼리 A 의 결과 테이블 아래에 쿼리 B 의 결과를 이어서 하나의 테이블로 만든다는 것임.

조건이 있는데, 너무나 당연하게도

쿼리 A의 결과와 쿼리 B 의 결과의 column 개수와 column 들의 데이터 타입 및 순서(order) 가 똑같아야 함.

 

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

 

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

 

ALL 이 없이 UNION 만 사용하면, 쿼리 A 의 결과와 쿼리 B 의 결과 중 중복된 row 는 제외하고 합쳐준다.

ALL 을 넣으면 쿼리 A의 결과와 쿼리 B 의 결과를 중복 제거 하지 않고 그대로 합쳐준다.

 

 

 

 

 

NULL 함수 사용하기

 

select 문에서 "만약 이 값이 null 값이라면, 대신 이 값으로 출력하라" 라는 내용의 함수가 있음.

MySQL 에서는 IFNULL(), COALESCE()

SQL Sever 에서는 ISNULL()

MS Access 에서는 IsNull()

Oracle 에서는 NVL()

 

SELECT FirstName, IFNULL(MiddleName, ""), Last Name
FROM People;

 

 

 

여러 arguments 중에 null 이 아닌 첫 번째 값을 반환하는 함수가 COALESCE()

예를 들어

SELECT COALESCE(1, 2, 3) : 1이 출력됨

SELECT COALESCE(NULL, 2, 3) : 2가 출력됨

SELECT COALESCE(NULL, NULL, 3) : 3이 출력됨

 

ifnull() 처럼, "만약 어떤 값이 null 이라면 이 값을 대신 출력" 하는 상황에서 사용 가능

예를 들어 

SELECT 100 - price FROM mytable; 

이런 쿼리에서 price 가 null 값이 들어온다면 결과는 null 이 됨

그래서 null 일 때는 0을 대신 사용할 수 있도록 COALESCE 를 아래처럼 사용

SELECT 100 - COALESCE(price, 0) FROM mytable;

이러면 price 가 숫자일 때 price 값 그대로 들어가고,

price 가 null 일 때 0 값이 대신 들어가게 됨.

 

 

 

 

 

from 절에서 사용되는 서브쿼리 포맷

작은 테이블을 하나 만든다고 생각하자.

 

SELECT sub.a, sub.b

FROM (

    SELECT a, b

    FROM mytable

    ) sub

WHERE sub.a > 10;

 

 

 

 

where 절, having 절에서 사용되는 서브쿼리 포맷

서브쿼리에서 조건(where)에 사용할 값들을 뽑아낸다고 생각하자.

 

SELECT *

FROM crimes

WHERE date = (SELECT MIN(date) FROM crimes)

 

위의 쿼리문에서 서브쿼리의 결과는 MIN(date) 처럼 하나만 나와야 함.

만약 MIN 이 없이 date 만 있다면 syntax error 가 나지 않을까?

 

SELECT *

FROM crimes

WHERE date IN (SELECT date FROM crimes ORDER BY date LIMIT 3)

 

위의 쿼리문에서는 서브쿼리의 결과가 여러개가 될 수 있지만,

단 하나의 정보, 즉 date만 사용 가능함.

만약 서브쿼리에서 date 뿐만 아니라 week, month 등 다양한 column 들을 추가로 select 한다면

syntax error 가 날 듯?

 

having 절은 group by 의 where 이기 때문에, 위의 설명과 동일함.

 

 

 

 

 

With 를 사용하여 View 를 만드는 방법

 

WITH myview AS (

  select id, name from accounts

)

select count(id) from myview group by id, name;

 

WITH myview [id, name]AS (

  select accountId, accountName from accounts

)

select count(id) from myview group by id, name;

 

 

 

 

 

 

 

Window 함수 사용하는 방법.

window 함수는, 집계함수의 결과를 새로운 column 으로 만들어서 넣어주는 함수 라고 보면 되겠다.

예제를 보자.

 

SELECT Id,

    Name,

    Salary,

    DepartmentId,

    MAX(Salary) OVER (PARTITION BY DepartmentId) AS MaxSalary

FROM Employee;

 

위의 쿼리를 실행하면

DepartmentId 를 기준으로 그룹을 지은 것 중에 : PARTITION BY DepartmentId

Salary 의 MAX 값을 뽑아서 : MAX(Salary)

MaxSalary 라는 이름의 Column 에 넣는다 : AS MaxSalary

 

SELECT Id,

    Name,

    kg,

    Line,

    SUM(kg) OVER (ORDER BY Line) AS CumSum

FROM Elevator;

 

위의 쿼리를 실행하면

Line을 기준으로 정렬한 후 : ORDER BY Line

kg의 합계를 구해서 : SUM(kg)

CumSum 이라는 이름의 Column 에 넣는다 : AS CumSum

보면 Line 순서대로 kg 의 누적합을 구하는 모습을 볼 수 있다.

 

 

SELECT Id,

    Name,

    kg,

    Line,

    SUM(kg) OVER (ORDER BY Line PARTITION BY Id) AS CumSum

FROM Elevator;

 

위의 쿼리를 실행하면

Id 를 기준으로 그룹을 지은 것 중에 : PARTITION BY Id

Line을 기준으로 정렬한 후 : ORDER BY Line

kg의 합계를 구해서 : SUM(kg)

CumSum 이라는 이름의 Column 에 넣는다 : AS CumSum

보면 Id 그룹들 내에서 Line 순서대로 kg 의 누적합을 구하는 모습을 볼 수 있다.

 

 

SELECT val,

    ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',

    RANK() OVER (ORDER BY val) AS 'rank',

    DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'

FROM sample;

 

위의 함수들은 window 함수에서만 사용 가능한 특수 함수들임.

세 함수 모두 순위를 정하는 함수임.

ORDER BY 뒤에 오는 값을 기준으로 순위를 정할 수 있다.

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

 

 

SELECT Id,

    RecordDate,

    Temperature,

    LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag',

    LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'

FROM Weather

 

lad 와 lead 함수는 column 의 값을 한 row 아래로 내려주거나 위로 올려준다.

RecordDate 를 정렬한 후 : ORDER BY RecordDate

그 정렬된 순서에 맞게 Temperature 를

아래로 한 row 씩 내리거나 : LAG(Temperature)

위로 한 row 씩 올려준다 : LEAD(Temperature)

아래로 세 row 씩 내리거나 : LAG(Temperature, 3)

위로 세 row 씩 올려줄 수 있다 : LEAD(Temperature, 3)

아래로 다섯 row 씩 내리되 빈칸은 0으로 채우거나 : LAG(Temperature, 5, 0)

위로 다섯 row 씩 올리되 빈칸은 0으로 채울 수 있다 : LEAD(Temperature, 5, 0)

 

추가적으로 그룹을 지어서 각 그룹당 row 이동을 하고 싶다면

LAG(Temperature) OVER (PARTITION BY RecordDate ORDER BY RecordDate) 

 

위의 예제에서는 RecordDate 가 이미 정렬되어있어서

하나씩 차례차례 내리고 올리는 것 처럼 보이지만

만약 정렬되어있지 않는다면... 정렬된 이후의 순서에 따라 갈 것임.

그럼 하나씩 차례차례가 아니라 뒤죽박죽이 되겠지?

 

 

SELECT e1.Id,

    e1.Name,

    el.kg,

    e1.Line,

    (SELECT SUM(e2.kg)

        FROM Elevator e2

        WHERE e1.Id = e2.Id

            and e1.Line >= e2.Line) AS CumSum

FROM Elevator e1

 

window 함수를 사용하지 않고도 누적합을 구할 수 있다.

SELECT 에 서브쿼리를 사용하는 것이다.

SELECT 에 서브쿼리를 사용하면, 쿼리의 결과문에 따라 테이블이 하나 나오지만

SUM, MIN 등을 사용하여 반드시 숫자값 하나를 결과로 내보내야 한다.

 

Elevator 테이블 e1과 Elevator 테이블 e2의 Id 가 같고 : WHERE e1.Id = WHERE d2.ID

Elevator 테이블 e1의 Line 값이 Elevator 테이블 e2의 Line 값보다 크거나 같을 때 : e1.Line >= e2.Line

즉, e1.Line 이 1 이면 e2.Line 은 1

e1.Line 이 2 이면 e2.Line 은 1, 2

e1.Line 이 3 이면 e2.Line 은 1, 2, 3 

위와 같은 조건을 각각 만족할 때의 테이블을 구한 후 kg 을 모두 더한다 : SUM(e2.kg)

즉, e1.Line 이 1 이면 e2.Line 은 1. SUM(e2.kg) 는 70

e1.Line 이 2 이면 e2.Line 은 1, 2. SUM(e2.kg) 는 70 + 91

e1.Line 이 3 이면 e2.Line 은 1, 2, 3. SUM(e2.kg) 는 70 + 91 + 59

 

 

 

 

 

 

정규표현식 ( regex ).

 

SELECT city

FROM station

WHERE city REGEXP '[정규표현식]'

 

예를 들어

WHERE val REGEXP '^[aeiou].*' : val 이 모음으로 시작되는 것을 조건으로 만듦

WHERE val REGEXP '^[0-9].*' : val 이 숫자로 시작되는 것을 조건으로 만듦

 

 

 

 

프로시저가 뭔지 알아야겠다.

 

 

 

 

 

 

 

UPDATE 문 사용하기

 

ContactName 이라는 column 의 값을 모조리 'Juan' 이라고 바꾸는 쿼리는 다음과 같음

 

UPDATE Customers
SET ContactName='Juan';

 

모조리 바뀌기 때문에 where 문을 넣어 조건을 걸어둠. 이를테면 id 가 3인 값만 Juan 으로 바꾸려면

 

UPDATE Customers
SET ContactName='Juan'

WHERE CustomerID = 3;

 

id 가 홀수일 때

A 라는 column 은 'Abro' 로, B 라는 column 값은 'Bonet' 으로 바꾸려면

 

UPDATE MyTable

SET A = 'Abro', B = 'Bonet'

WHERE id % 2 = 1;

 

 

"case when.. then.. end" 문을 통해 한 번에 여러 행의 값을 바꿀 수 있음.

salary 라는 테이블에서 sex 라는 이름의 열이 m 과 f 데이터를 갖고 있다고 하자.

여기서 update 문 하나만을 이용하여 sex의 m은 f로, f는 m으로 바꾸고 싶다면 다음 명령어를 이용

 

UPDATE salary 

SET sex = (

        CASE

                WHEN sex = 'f' THEN 'm'

                WHEN sex = 'm' THEN 'f'

        END

)

 

 

 

 

 

 

INSERT 문 사용하기

 

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

 

위의 쿼리문의 경우, (c1, c2,,) 와 (v1, v2,,) 의 짝이 맞다면, 순서가 바뀌어도 상관 없음

또한 원하는 column 만 insert 가능.

 

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

 

위의 쿼리문의 경우 (v1, v2,,) 의 순서가 table 의 column 순서와 맞아야 함.

 

t2의 rows 를 t1에 넣는 쿼리는 다음과 같음.

 

INSERT INTO t1(c1, c2, c3,,,) SELECT (c1, c2, c3,,,) FROM t2;

 

이게 UNION 이랑 비슷해보이긴 함.

정확한 차이는 모르겠으나, 내가 보기엔..

UNION 은 모든 rows 를 이어붙여주는 것 같고,

INSERT INTO 는 내가 선택한 column 을 갖는 rows 들을 이어붙여주는 것이 다른 것 같다.

 

 

 

 

 

DELETE 문 사용하기

 

table 내의 모든 rows 제거하는 쿼리는 다음과 같음.

 

DELETE FROM MyTable;

 

때문에 조건문을 넣어주어, 내가 제거하길 원하는 특정 rows 만 제거해야 함.

예를 들어 value 가 NULL 인 것을 제거하는 쿼리는 다음과 같음.

 

DELETE FROM MyTable 

WHERE value IS NULL;

 

 

 

 

 

 

Return the intersection of two queries

 

SELECT c1, c2 FROM t1 INTERSECT SELECT c1, c2 FROM t2;

Subtract a result set from another result set

 

SELECT c1, c2 FROM t1 MINUS SELECT c1, c2 FROM t2;

 

Managing tables

Create a new table with three columns

 

CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR NOT NULL, price INT DEFAULT 0 );

Delete the table from the database

 

DROP TABLE t ;

Add a new column to the table

 

ALTER TABLE t ADD column;

Drop column c from the table

 

ALTER TABLE t DROP COLUMN c ;

Add a constraint

 

ALTER TABLE t ADD constraint;

Drop a constraint

 

ALTER TABLE t DROP constraint;

Rename a table from t1 to t2

 

ALTER TABLE t1 RENAME TO t2;

Rename column c1 to c2

 

ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all data in a table

 

TRUNCATE TABLE t;

Using SQL constraints

Set c1 and c2 as a primary key

 

CREATE TABLE t( c1 INT, c2 INT, c3 VARCHAR, PRIMARY KEY (c1,c2) );

Set c2 column as a foreign key

 

CREATE TABLE t1( c1 INT PRIMARY KEY, c2 INT, FOREIGN KEY (c2) REFERENCES t2(c2) );

Make the values in c1 and c2 unique

 

CREATE TABLE t( c1 INT, c1 INT, UNIQUE(c2,c3) );

Ensure c1 > 0 and values in c1 >= c2

 

CREATE TABLE t( c1 INT, c2 INT, CHECK(c1> 0 AND c1 >= c2) );

Set values in c2 column not NULL

 

CREATE TABLE t( c1 INT PRIMARY KEY, c2 VARCHAR NOT NULL );

 

 

Managing Views

Create a new view that consists  of c1 and c2

 

CREATE VIEW v(c1,c2) AS SELECT c1, c2 FROM t;

Create a new view with check option

 

CREATE VIEW v(c1,c2) AS SELECT c1, c2 FROM t; WITH [CASCADED | LOCAL] CHECK OPTION;

Create a recursive view

 

CREATE RECURSIVE VIEW v AS select-statement -- anchor part UNION [ALL] select-statement; -- recursive part

Create a temporary view

 

CREATE TEMPORARY VIEW v AS SELECT c1, c2 FROM t;

Delete a view

 

DROP VIEW view_name;

Managing indexes

Create an index on c1 and c2 of the t table

 

CREATE INDEX idx_name ON t(c1,c2);

Create a unique index on c3, c4 of the t table

 

CREATE UNIQUE INDEX idx_name ON t(c3,c4)

Drop an index

 

DROP INDEX idx_name;

Managing triggers

Create or modify a trigger

 

CREATE OR MODIFY TRIGGER trigger_name WHEN EVENT ON table_name TRIGGER_TYPE EXECUTE stored_procedure;

WHEN

• BEFORE – invoke before the event occurs
• AFTER – invoke after the event occurs

EVENT

• INSERT – invoke for INSERT
• UPDATE – invoke for UPDATE
• DELETE – invoke for DELETE

TRIGGER_TYPE

• FOR EACH ROW
• FOR EACH STATEMENT

Delete a specific trigger

 

DROP TRIGGER trigger_name;