본문 바로가기

프로그래밍/DB

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

반응형

관련 링크: 한 페이지로 정리하는 SQL (Database 관련)


주석

-- 한줄 주석
/*
문장 주석
*/


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";

관련 링크: 한 페이지로 정리하는 SQL (Database 관련)

반응형