3. MySQL-3

(실습) 주석

-- 해당 구문은 전체 고객 정보를 조회합니다.
SELECT * FROM Customers;

-- 해당 구문은 전체 고객 정보를 조회합니다.
SELECT * FROM Customers --WHERE CustomerID > 50;

/*
여러 줄 주석입니다.
SELECT City FROM Customers;
*/
SELECT * FROM Customers;

(실습) 조건문

SQL CASE Statement

The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

https://www.w3schools.com/sql/sql_case.asp

• Oracle의 경우에는 DECODE, CASE WHEN

• MsSQL의 경우에는 CASE WHEN

• MySQL의 경우에는 IF, CASE WHEN

• CASE WHEN 조건 THEN 참일경우_실행구문 ELSE 거짓일경우_실행구문 END

SELECT CASE WHEN 'GOOD'='JOB' THEN 'HELLO' ELSE 'WORLD' END;

SELECT CustomerID, City,
CASE
    WHEN CustomerID > 50 THEN '50번을 넘음!'
    WHEN City='London' THEN '도시가 런던!'
    ELSE '50번 이하임!'
END AS '출력 문구'
FROM Customers;

SELECT CustomerName, City, Country FROM Customers
ORDER BY 
(CASE
		WHEN City IS NULL THEN Country
		ELSE City
END);

(실습) JOIN

SQL Tryit Editor v1.6

Click "Run SQL" to execute the SQL statement above. W3Schools has created an SQL database in your browser. The menu to the right displays the database, and will reflect any changes. Feel free to experiment with any SQL statement. You can restore the database at any time.

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join

-- w3school 기본 예제입니다.
-- OrderID는 Orders라는 table에 있습니다.
-- CustomerName는 Customers라는 table에 있습니다.
-- OrderDate는 Orders라는 table에 있습니다.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
-- 기준 table은 Orders입니다.
FROM Orders
-- INNER JOIN을 할 table은 Customers입니다.
INNER JOIN Customers
-- INNER JOIN의 기준은 두 table 모두 있는 CustomerID입니다.
ON Orders.CustomerID=Customers.CustomerID;

W3Schools SQL Exercise

I completed all the SQL exercises on w3schools.com

https://www.w3schools.com/sql/exercise.asp?filename=exercise_join1

(실습) UNION

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

(실습) GROUP BY

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

(실습) HAVING

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

(실습 안함) EXIST

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

(실습 안함) ANY

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

(실습 안함) ALL

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