DB - 한 페이지로 정리하는 SQL (Table 관련)

주석

-- 한줄 주석

/*
  문장 주석
*/

 

SELECT (조회)

/* 기본 */
SELECT column1, column2, ... /* DISTINCT, COUNT, AVG, SUM, MIN, MAX */
FROM table_name
WHERE condition /* =, <>, !=, >, <, >=, <=, BETWEEN, LIKE, IN, NOT, IS NULL, IS NOT NULL */ /* 연결자: AND, OR */
ORDER BY column1, column2, ... ASC|DESC;

/* GROUP BY, HAVING */
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

/* EXISTS */
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

/* ANY */
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

/* ALL */
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

 

 

INSERT (추가)

/* INSERT */
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

/* SELECT INTO */
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

/* INSERT INTO SELECT */
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

 

UPDATE (수정)

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

 

 

DELETE (삭제)

DELETE FROM table_name WHERE condition;

 

 

JOIN

/* INNER JOIN */
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

/* LEFT JOIN */
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

/* RIGHT JOIN */
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

/* FULL JOIN */
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

/* SELF JOIN */
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

 

UNION

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

 

 

CASE

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

/* 예제1 */
SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

/* 예제2 */
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

 

Procedures

/* 생성 */
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

/* 실행 */
EXEC procedure_name;

/* 예제 */
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers City = "London", PostalCode = "WA1 1DP";

 

DB - 한 페이지로 정리하는 SQL (Database 관련)