SQL(Structured Query Language)

SELECT Statement

syntax

SELECT column1, column2, ...
FROM table_name;

ex)

SELECT Country FROM Customers;
//Customers 테이블에서 Country만 출력

SELECT DISTINCT Country FROM Customers;
//Customers 테이블에서 Country만 출력(중복된 것은 지우고 출력)

SELECT COUNT(DISTINCT Country) FROM Customers;
//Customers 테이블의 Country값에서 중복된 것은 지운뒤에 갯수 출력

WHERE Clause

syntax

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

Text 값인 것은 ''안에 값을 적어서 비교하고 Numeric 값인 것은 그냥 적어서 비교한다.(아래 예시 있음)
ex)

SELECT * FROM Customers WHERE Country='Mexico';
// Country 값이 Mexico 인 것들의 모든 값을 출력
SELECT * FROM Customers WHERE CustomerID=1;
// CustomerID가 1인 것들의 모든 값을 출력

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. (SQL 다른 버전 중에서 != 로 쓰기도 함)
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

AND, OR and NOT Operators

Syntax

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

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

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

두개 같이 사용할 수 있음
ex)

SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';
//Customers 테이블에서 Country값이 Germany고 City값이 Berlin인 것들 출력

SELECT * FROM Customers
WHERE Country='Germany' OR City='Berlin';
//Customers 테이블에서 Country값이 Germany이거나 City값이 Berlin인 것들 출력

SELECT * FROM Customers
WHERE NOT Country='Germany';
//Customers 테이블에서 Country값이 Germany이 아닌 것들 출력

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');
Country가 Germany이고 City가 Berlin이거나 Munchen인 것 출력

SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';
Country가 Germany도 아니고 USA도 아닌 것 출력

ORDER BY Keyword

Syntax

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

SELECT * FROM Customers ORDER BY Country;
//Country를 기준으로 A~Z순으로 정렬해서 출력

SELECT * FROM Customers ORDER BY Country DESC;
//Country를 기준으로 Z~A순으로 정렬해서 출력

SELECT * FROM Customers
ORDER BY Country, CustomerName;
//Country를 기준으로  A~Z순으로 정렬하고 Country가 같은 경우 CustomerName을 기준으로  A~Z순으로 정렬해서 출력

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
//Country를 기준으로  A~Z순으로 정렬하고 Country가 같은 경우 CustomerName을 기준으로  Z~A순으로 정렬해서 출력

INTO Statement

INSERT INTO Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
//전체가 아닌 몇개만 값을 지정하고 테이블에 추가하고 싶을때 사용

INSERT INTO table_name
VALUES (value1, value2, value3, ...);
//전체 값을 지정하고 테이블에 추가하고 싶을때 사용

NULL Values

값이 없는 field(값을 넣지 않은 필드)
IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;
//column)name이 NULL값인 것 찾아서 출력

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
//column)name이 NULL값이 아닌 것 찾아서 출력

UPDATE Statement

syntax

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

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
//CustomerID가 1인 것을 ContactName는 'Alfred Schmidt', City는 'Frankfurt'으로 바꾸기

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
//Country가 Mexico인 모든 것의 ContactName을 'Juan'으로 바꾸기

UPDATE Customers
SET ContactName='Juan';
//전체의 ContactName을 Juan으로 바꾸기

DELETE Statement

syntax

DELETE FROM table_name WHERE condition;

ex

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
//CustomerName이 'Alfreds Futterkiste'인 것만 삭제

DELETE FROM Customers;
//전체 기록 삭제

SELECT TOP Clause

Syntax

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
//SQL Server/MS Access

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
//Mysql

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
//Oracle

ex

SELECT TOP 3 * FROM Customers;
SELECT * FROM Customers LIMIT 3;
SELECT * FROM Customers WHERE ROWNUM <= 3;
//테이블 중에 위에서 부터 3개만 출력(전체 값 출력)

SELECT TOP 3 * FROM Customers WHERE Country='Germany';
Country가 Germany인 것 중 위에서 부터 3개만 출력(전체 값 출력)

MIN() and MAX() Functions

MIN() Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

ex

SELECT MIN(Price) AS SmallestPrice
FROM Products;
//Products에서 가장 작은 Price 값을 SmallestPrice라는 이름으로 출력

SELECT MAX(Price) AS LargestPrice
FROM Products;
//Products에서 가장 큰 Price 값을 LargestPrice라는 이름으로 출력

COUNT(), AVG() and SUM() Functions

COUNT() function : 조건에 맞는 row의 갯수

The AVG() : numeric colum의 평균 값 출력

The SUM() : numeric colum의 총합 출력

Syntax

//COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;

//AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;

//SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;

ex

SELECT COUNT(ProductID)
FROM Products;

SELECT AVG(Price)
FROM Products;

SELECT SUM(Quantity)
FROM OrderDetails;

LIKE Operator

% - 0개,1개 아니면 그 이상 갯수의 문자 의미
_ - 문자 1개 의미

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

LIKE Operator	Description	example
WHERE CustomerName LIKE 'a%'	a로 시작하는 값 찾기	SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
WHERE CustomerName LIKE '%a'	a로 끝나는 값 찾기	SELECT * FROM Customers WHERE CustomerName LIKE '%a';
WHERE CustomerName LIKE '%or%'	어디든 or이 있는 값 찾기	SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
WHERE CustomerName LIKE '_r%'	두번째 값이 r인 값 찾기	SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
WHERE CustomerName LIKE 'a__%'	a가 적어도 뒤에서 세번째인 값을 찾기	SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';
WHERE ContactName LIKE 'a%o'	a로 시작하고 o으로 끝나는 값 찾기	SELECT * FROM Customers WHERE ContactName LIKE 'a%o';

not 사용 가능

SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';

Wildcards Characters

: 문자열에서 1개나 여러개의 문자를 구성하는 문자

Wildcard Characters in MS Access

Symbol	Description	Example
*	0개나 그 이상의 문자	bl* -> bl, black, blue, and blob
?	1개 문자	h?t -> hot, hat, and hit
[]	[]사이의 문자 중 아무거나 1개 가능	h[oa]t -> hot and hat, but not hit
!	[]안에 있는 것 말고 다른 것들 중 하나	h[!oa]t -> hit, but not hot and hat
-	문자들의 범위	c[a-b]t -> cat and cbt
#	1개의 숫자	2#5 -> 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard Characters in SQL Server

Symbol	Description	Example
%	0개나 그 이상의 문자	bl% -> bl, black, blue, and blob
_	1개 문자	h_t -> hot, hat, and hit
[]	[]사이의 문자 중 아무거나 1개 가능	h[oa]t -> hot and hat, but not hit
^	[]안에 있는 것 말고 다른 것들 중 하나	h[^oa]t -> hit, but not hot and hat
-	문자들의 범위	c[a-b]t -> cat and cbt

IN Operator

syntax

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

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

ex

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
// 'Germany', 'France', 'UK' 중 하나가 Country인 것들 찾기

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
//Suppliers 테이블에 있는 Country 중 하나가 Country인 값 찾기

BETWEEN Operator

Syntax

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

ex

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
//Price가 10과 20의 사이인 값 찾기 

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND NOT CategoryID IN (1,2,3);

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

//이런식으로 여러개 연결 가능

SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
//이 두 날짜 사이인 값 찾기

Aliases

syntax

//Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;

//Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;

ex
Alias for Columns Examples

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
//CustomerID는 ID, CustomerName는 Customer로 이름 대체해서 출력

SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
//CustomerName은Customer, ContactName은 Contact Person로 이름 대체해서 출력

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
//Address, PostalCode, City, Country 형식을 Address라는 이름으로 해서 CustomerName과 Address 두 개 보여줌

//Mysql SQL은 아래처럼
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

Alias for Tables Example

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;

//위와 같이 table 이름을 간단하게 대체해서 사용 가능
//이름 대체하지 않으면 아래처럼 됨

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;

joins

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

(INNER) JOIN : 양쪽 테이블에서 두개의 값이 matching되는 경우만 return
LEFT (OUTER) JOIN: 왼쪽테이블 전체 값과 matching되는 오른쪽 테이블 값을 return

RIGHT (OUTER) JOIN: 오른쪽 테이블 전체 값과 matching되는 왼쪽 테이블 값을 return
FULL (OUTER) JOIN: 양쪽 테이블 모든 것의 값을 return

INNER JOIN

syntax

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

//ex)
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

//join three tables
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

LEFT JOIN

syntax

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

//ex
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN

syntax

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

//ex
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

FULL OUTER JOIN

syntax

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

//ex
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Self JOIN

syntax

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

//ex
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

UNION Operator

2개 이상의 SELECT statement의 결과를 합칠때 사용
같은 숫자의 columns의 결과값이여야 한다.
같은 data type을 가져야 함.
각 select statement는 같은 방식으로 정렬되어있어야 한다.

UNION은 같은 값이 있으면 한 번만 출력
UNION은 같은 값이 있으면 둘 다 출력(두개가 같으면 두개 다 출력)

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

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

ex

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;


SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

GROUP BY Statement

syntax

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

ex

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
// Country로 묶어서 Country에 속한 CustomerID의 수를 세서 출력

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

HAVING Clause

syntax

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

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
//5명 이상의 수만 출력

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;


NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

EXISTS Operator

syntax

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

()안에 존재한다면 출력
ex

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

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

ANY and ALL Operators

ALL: 모든 값이 조건에 맞으면 true
ANY: 어떤 값이 조건에 맞으면 true

ANY Syntax

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

ex

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

ALL Syntax

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

ex

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

SELECT INTO Statement

//새로운 테이블에 모든 columns을 복사
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

//새로운 테이블에 몇개의 columns만 복사
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

ex

SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

INSERT INTO SELECT Statement

syntax

//다른 table로 한 테이블로 부터 모든 columns을 복사할때
INSERT INTO table2
SELECT * FROM table1
WHERE condition;'

//다른 table로 한 테이블에서 몇가지를 복사할때
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

ex

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

CASE Statement

syntax

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

ex

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;

NULL Functions

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

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

mySQL은 둘다 사용 가능, SQL Server는 ISNULL()만 사용 가능
-> ISNULL(): 값이 NULL이면 대안 가능한 값을 돌려줌

MS Access: ISNULL(): 값이 null이면 -1 아니면 0출력

oracle: NVL() 사용 가능

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

Stored Procedures for SQL Server

SQL 코드를 저장해두고 쓰고 싶을때 제사용 가능하게 함
Stored Procedure Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

ex

//저장
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

//실행
EXEC SelectAllCustomers;

parameter 있는 ex

parameter 1개

//저장
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

//실행
EXEC SelectAllCustomers @City = "London";

parameter 여러개

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 주석

한 줄: --로 시작
ex

--이런 식으로 적음

여러줄: /* */
ex

/*
이런 식으로
*/