SELECT
- extracts data from a databaseUPDATE
- updates data in a databaseDELETE
- deletes data from a databaseINSERT INTO
- inserts new data into a databaseCREATE DATABASE
- creates a new databaseALTER DATABASE
- modifies a databaseCREATE TABLE
- creates a new tableALTER TABLE
- modifies a tableDROP TABLE
- deletes a tableCREATE INDEX
- creates an index (search key)DROP INDEX
- deletes an indexSELECT column1, column2, ...
FROM table_name;
SELECT * FROM table_name;
SELECT DISTINCT column1, column2, ...
FROM table_name;
SELECT COUNT(DISTINCT Country) FROM Customers;
SELECT column1, column2, ...
FROM table_name
WHERE condition;
SELECT column1, column2, ...
FROM table_name
WHERE column1 in ('condition1', 'condition2')
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;
SELECT * FROM Customers
WHERE NOT Country='Germany';
SELECT * FROM Customers
ORDER BY Country;
SELECT * FROM Customers
ORDER BY Country DESC;
1번 열 기준으로 정렬하되, 동일한 데이터가 있을 때는 2번 열 기준으로 정렬
SELECT * FROM Customers
ORDER BY Country, CustomerName;
1번 열 기준으로 오름차순 정렬하되 동일한 데이터가 있을 때는 2번 열 기준으로 내림차순 정렬
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
UPDATE Customers
SET ContactName='Juan';
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
DELETE FROM table_name;
SELECT * FROM Customers
LIMIT 3;
SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;
SELECT MIN(Price) AS SmallestPrice
FROM Products;
SELECT MAX(Price) AS LargestPrice
FROM Products;
SELECT COUNT(ProductID)
FROM Products;
SELECT AVG(Price)
FROM Products;
SELECT SUM(Quantity)
FROM OrderDetails;
% : 0개~여러 개의 문자를 의미
_ : 1개의 문자를 의미
WHERE CustomerName LIKE 'a%'
Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'
WHERE CustomerName LIKE '_r%'
WHERE CustomerName LIKE 'a_%'
WHERE ContactName LIKE 'a%o'
SELECT * FROM Customers
WHERE City LIKE '[acs]%';
SELECT * FROM Customers
WHERE City LIKE '[a-f]%';
SELECT * FROM Customers
WHERE City LIKE '[!acf]%';
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);
같은 도시에서 온 고객끼리 매칭
🌟 <>
연산자는 !=
를 의미
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;
각 select 의 결과 집합을 결합
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
국가별 고객 수가 5 이상인 국가만 출력
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
주문을 10개 이상 한 직원만 출력
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
Davolio 와 Fuller 가 10 개이상 주문했는지 여부를 출력
Fuller 는 20개를 주문했기 때문에 출력되지 않는다.
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
LastName | NumberOfOrders |
---|---|
Davolio | 29 |
하위 쿼리가 하나 이상의 레코드를 반환하는 경우 True 를 반환
"제품 가격이 20미만인 공급자"를 만족하는 공급자 이름을 반환
SELECT SupplierName
FROM Suppliers
WHERE EXISTS
(SELECT ProductName
FROM Products
WHERE Products.SupplierID = Suppliers.supplierID
AND Price < 20);
하위 쿼리 값 중 하나라도 조건을 충족하면 TRUE 반환
OrderDetails 테이블에서 Quantity 가 10 인 레코드를 찾으면 ProductName 을 출력
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
모든 하위 쿼리 값이 조건을 충족하면 TRUE 반환
모든 제품명 출력
SELECT ALL ProductName
FROM Products
WHERE TRUE;
OrderDatails 테이블의 모든 레코드의 Quantity 가 10인 경우 ProductName 을 출력 → 이 경우에는 Quantity 열에 다양한 값이 있기 때문에 False 를 반환
SELECT ProductName
FROM Products
WHERE ProductID = ALL
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
한 테이블의 데이터를 새 테이블로 복사
Customers의 모든 데이터를 CustomersBackup2017 테이블로 복사
SELECT * INTO CustomersBackup2017
FROM Customers;
Customers의 모든 데이터를 다른 데이터베이스에 있는 CustomersBackup2017 테이블로 복사
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
일부 열만 새 테이블로 복사
SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;
조건과 일치하는 데이터만 복사
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;
데이터 없이 스키마만 복사
SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
한 테이블의 데이터를 복사하여 다른 테이블에 삽입
공급자 테이블에서 공급자명, 도시, 국가 데이터를 복사하여 고객 테이블의 고객명, 도시, 국가로 삽입
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
공급자의 국가가 독일인 경우에만 공급자명, 도시, 국가 데이터를 복사하여 고객 테이블로 삽입
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
1번 조건과 일치하면 1번 결과 반환
2번 조건과 일치하면 2번 결과 반환
조건이 모두 일치하지 않으면 ELSE 결과 반환
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 이면 국가를 기준으로 정렬, NULL 이 아니면 도시를 기준으로 정렬
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
컬럼의 값이 NULL이면 0을 리턴하여 연산
SELECT ProductName, UnitPrice * (UnitsInStock +
IFNULL(UnitsOnOrder, 0))
FROM Products;
컬럼의 값이 NULL 이면 0을 리턴하여 연산
SELECT ProductName, UnitPrice * (UnitsInStock +
COALESCE(UnitsOnOrder, 0))
FROM Products;
select, where 절에서 사용
IF(조건, 참일경우리턴값, 거짓일경우리턴값)
SELECT IF(10>5, '크다', '작다') AS result;
SQL Statement | Syntax |
---|---|
AND / OR | SELECT column_name(s)FROM table_nameWHERE conditionAND |
ALTER TABLE | ALTER TABLE table_nameADD column_name datatype / ALTER TABLE table_nameDROP COLUMN column_name |
AS (alias) | SELECT column_name AS column_aliasFROM table_name / SELECT column_nameFROM table_name AS table_alias |
BETWEEN | SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2 |
CREATE DATABASE | CREATE DATABASE database_name |
CREATE TABLE | CREATE TABLE table_name(column_name1 data_type,column_name2 data_type,column_name3 data_type,...) |
CREATE INDEX | CREATE INDEX index_nameON table_name (column_name) / CREATE UNIQUE INDEX index_nameON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition |
DELETE | DELETE FROM table_nameWHERE some_column=some_value / DELETE FROM table_name(Note: Deletes the entire table!!) / DELETE * FROM table_name(Note: Deletes the entire table!!) |
DROP DATABASE | DROP DATABASE database_name |
DROP INDEX | DROP INDEX table_name.index_name (SQL Server)DROP INDEX index_name ON table_name (MS Access)DROP INDEX index_name (DB2/Oracle)ALTER TABLE table_nameDROP INDEX index_name (MySQL) |
DROP TABLE | DROP TABLE table_name |
EXISTS | IF EXISTS (SELECT * FROM table_name WHERE id = ?)BEGIN--do what needs to be done if existsENDELSEBEGIN--do what needs to be done if notEND |
GROUP BY | SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name |
HAVING | SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value |
IN | SELECT column_name(s)FROM table_nameWHERE column_nameIN (value1,value2,..) |
INSERT INTO | INSERT INTO table_nameVALUES (value1, value2, value3,....) / INSERT INTO table_name(column1, column2, column3,...)VALUES (value1, value2, value3,....) |
INNER JOIN | SELECT column_name(s)FROM table_name1INNER JOIN table_name2ON table_name1.column_name=table_name2.column_name |
LEFT JOIN | SELECT column_name(s)FROM table_name1LEFT JOIN table_name2ON table_name1.column_name=table_name2.column_name |
RIGHT JOIN | SELECT column_name(s)FROM table_name1RIGHT JOIN table_name2ON table_name1.column_name=table_name2.column_name |
FULL JOIN | SELECT column_name(s)FROM table_name1FULL JOIN table_name2ON table_name1.column_name=table_name2.column_name |
LIKE | SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern |
ORDER BY | SELECT column_name(s)FROM table_nameORDER BY column_name [ASC |
SELECT | SELECT column_name(s)FROM table_name |
SELECT * | SELECT *FROM table_name |
SELECT DISTINCT | SELECT DISTINCT column_name(s)FROM table_name |
SELECT INTO | SELECT *INTO new_table_name [IN externaldatabase]FROM old_table_name / SELECT column_name(s)INTO new_table_name [IN externaldatabase]FROM old_table_name |
SELECT TOP | SELECT TOP number |
TRUNCATE TABLE | TRUNCATE TABLE table_name |
UNION | SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2 |
UNION ALL | SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2 |
UPDATE | UPDATE table_nameSET column1=value, column2=value,...WHERE some_column=some_value |
WHERE | SELECT column_name(s)FROM table_nameWHERE column_name operator value |