SQL

- SQL 연습(설치없이)

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

SQL Tutorial

# 이어 붙이기
SELECT Country || City FROM Customers;

# 공백
SELECT Country || ' ' || City FROM Customers;

###
# MySQL은 || 생략 가능

# BETWEEN
# 30과 50포함
SELECT * FROM Customers WHERE CustomerID BETWEEN 30 AND 50;

# IN
SELECT * FROM Customers WHERE CustomerID IN (10, 20,30);

# LIKE
# %:모든, _:한 글자
SELECT * FROM Customers WHERE CustomerID LIKE '1_';

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

SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM

# TOP 
SELECT TOP 10 FROM Customers;

# LIMIT
SELECT * FROM Customers LIMIT 10;

 

함수

SELECT CustomerID, CustomerName,
	LOWER(CustomerName) AS 소문자,
    UPPER(CustomerName) AS 대문자
FROM Customers;

# 잘라오기
SELECT SUBSTR('Hello world', 1, 4);
# Hel
SELECT SUBSTR('Hello world', -3, 4);
# rld
SELECT SUBSTR('Hello world', -5);
# world

SELECT LENGTH('Hello world');
#11

# 값의 개수
SELECT COUNT('Hello world');
# 1

SELECT REPLACE('Hello world', 'world', 'SQL');
# Hello SQL

SELECT CONCAT('Hello', 'SQL');
# Hello SQL

SELECT DATE();
# 2022-11-02
SELECT DATE() + 10;
# 2032

# 아스키코드
SELECT CHAR(65);
SELECT ASCII('A');

 

조건문

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

# 기준은 Orders
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

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


SELECT COUNT(CustomerID), Country
FROM Customers;
# 1줄 : 80과 Mexico만 나옴

# -> 나라별로 몇개씩인지 알고 싶음 / 추가 조건은 HAVING
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;