SQL 구문정리

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

This statement is for MySQL Syntax.

 

보조 열 선택

Select sub columns from the table

SELECT col1, col2
FROM table_name;

 

모든 열 선택

Select all columns from the table (* means all)

SELECT * FROM table_name;

 

중복 값 가지지 않도록 열 선택

Select sub columns who have distanct values

SELECT DISTINCT col1, col2
FROM table_name;

 

조건 기반 필터링하기

Filter records based on a condition

SELECT col1, col2
FROM table_name
WHERE condition;

Operator: =, >, <, >=, <=, <>, BETWEEN, LIKE(To find a pattern), IN(Multple values)

Connect several conditions with logical operator: AND, OR, NOT

 

 

 

열 기반 정렬하기

Sort the table based on the col

SELECT col1, col2
FROM table_name
ORDER BY col1 ASC, col2 DESC;

 

테이블에 새로운 데이터(행) 추가하기

Insert new records into a table

INSERT INTO table_name (col1, col2)
VALUES (val1, val2);

 

결측치 확인하기

Check for NULL

SELECT col1
FROM table_name
WHERE col1 IS NULL; # IS NOT NULL also can be used.

 

열 내의 데이터 바꾸기

Update for modifying the existing records

UPDATE table_name
SET col1=val1, col2=val2
WHERE condition;

Under this condition, we modify the value from col1 with new value, so basically update statment is for changing the value in column.

 

조건에 맞는 데이터 지우기

Delete some records(row records)

DELETE FROM table_name WHERE condition;

 

 

 

조건에 맞는 상위 n개의 데이터 선택하기

Select top n data

SELECT col1
FROM table_name
WHERE condition
LIMIT number;

You can use more function like min, max, count, avg, and sum

 

 

특정 문자패턴과 일치하는 데이터 추출하기

Find the data which of column matches specific string pattern with regex.

SELECT col1, col2
FROM table_name
WHERE col1 LIKE pattern;

SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

 

열 이름 지어주기

Aliases for giving name to table or column

(Because this statement is just temporary, so an alias only exists for the duration of that query.)

SELECT col1 AS alias_name 
FROM table_name;