[SQL] SQL 정리

SQL?

Structured Query Language

데이터베이스 접근, 작동을 용이하게 함.

# SELECT

- 데이타베이스로 부터 데이터를 select

 

SELECT column1, column2 FROM table_name;

 

- DISTINCT : 하나의 distinct, different 값만 리턴.

 

SELECT DISTINCT column1, column2, ...
FROM table_name;

 

SELECT COUNT(DISTINCT Country) FROM Customers;

 

 

# WHERE- 특정 조건에 충족하는 record만 filter

 

SELECT column1, column2, ...
FROM table_name
WHERE condition;

 

 

# Operator

- AND, OR, NOT

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition1 AND condition2 OR condition3 ...;

 

# Operator

- AND, OR, NOT

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition1 AND condition2 OR condition3 ...;

 

# ORDER BY

- 오름차순, 내림차순으로 정렬

- default : 오름차순

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

 

 

# ORDER BY

- 오름차순, 내림차순으로 정렬

- default : 오름차순

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

 

# INSERT INTO

- 테이블에 새로운 record 삽입

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

 

# NULL

- no value

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

 

 

# UPDATE

- 존재하는 table의 record 수정

 

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

 

# DELETE

- 존재하는 table의 record 삭제

 

DELETE FROM table_name WHERE condition;

 

# SELECT TOP

- 반환하는 record의 수를 지정

 

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

 

 

# MIN(), MAX()

- MIN () : 선택된 column 값들 중 가장 작은 값 리턴

- MAX () : 선택된 column 값들 중 가장 큰 값 리턴

SELECT MIN(column_name)
FROM table_name
WHERE condition;

 

# COUNT(), AVG(), SUM()

- COUNT() : 특정 criterion에 일치하는 row의 개수 리턴

- AVG() : numeric column의 평균 값 리턴

- SUM() : numeric column의 전체 합 리턴

SELECT COUNT(column_name), AVG(column_name), SUM(column_name)
FROM table_name
WHERE condition;

 

# LIKE

- column에서 특정 패턴에 대해 search하기 위해 WHERE clause에 사용

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

 

 

# Wildcard Characters

- string에서 character들을 substitute

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

=> "ber"로 시작하는 City의 모든 customer들을 select

 

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

=> "es"를 포함하는 City의 모든 customer들을 select

 

* 이 외에도 여러 characters를 사용하는 방법들이 있다.

 

 

 

 

# IN

-  WHERE Clause내에 여러 값들을 지정하게 함.

 

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

 

 

 

# BETWEEN

- 주어진 range내에 값들을 select

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 

# Aliases

- table, column에게 temporary한 이름을 부여함

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

 

 

# JOIN

- 여러개의 table로 부터 row들을 combine

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

 

 

# INNER JOIN

- 두 table내에 같은 값을 가진 records를 select

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

 

 

# LEFT JOIN

- 왼쪽 table로 부터의 모든 records를 반환

 

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

 

# RIGHT JOIN

- 오른쪽 table로 부터의 모든 records를 반환

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

 

 

# FULL OUTER JOIN

- 왼쪽, 오른쪽 table records 모두 match하는 records 리턴

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

 

# Self Join

- regular join이지만, table은 그 자체로 join 됨.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

 

 

# UNION

- SELECT 구문의 result-set을 결합

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

 

 

 

# GROUP BY

-  summary rows에 같은 값을 갖는 row들을 그룹핑

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

 

 

# HAVING

- WHERE이 결합된 function에 사용되지 않으므로, HAVING이 SQL에 더해짐.

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

 

 

# EXISTS

- subquery내에 해당 record가 존재하는지 test

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

 

# ANY, ALL Operator

- ANY : subquery 값들 중 어느 것이 조건 만족한다면 TRUE

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

 

- ALL : subquery 값들이 모두 조건을 만족해야 TRUE

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

 

 

# SELECT INTO

- 새로운 table의 모든 column들을 copy

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

 

 

# INSERT INTO

-  한 table로 부터 data를 copy하여 다른 table에 insert

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

 

 

# CASE

- 조건을 통과하고, 첫 조건이 충족되면 값 return 하는 형식.

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

 

# NULL Functions

- IFNULL() : 표현이 null이면 alternative 값을 리턴

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

- COALESCE() 

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;

- ISNULL() : 표현이 null이면 alternative 값을 리턴하게 만듦

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;

- IsNull() : 표현이 null이면 TRUE, 그렇지 않으면 FALSE 리턴

SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;

 

 

# Comment

'--'

--Select all:
SELECT * FROM Customers;