자주 쓰는 SQL 기본 문법을 정리해보자!
SELECT col1, col2, ....
FROM table_name;
SELECT DISTINCT col1, col2, ...
FROM table_name;
SELECT col1, col2, ...
FROM table
WHERE condition;
SELECT col1, col2, ...
FROM table
WHERE condition1 and condition2;
SELECT col1, col2, ....
FROM table
WHERE condition1 or condition2;
SELECT col1, col2, ...
FROM table
WHERE NOT condition;
SELECT col1, col2, ...
FROM table
ORDER BY col1 ASC|DESC;
SELECT col1, col2, ...
FROM table
ORDER BY FIELD (col1, order1, order2, ...);
order by field ( 컬럼명, "순서1", "순서2", .... )
임의로 정한 순서대로 보여주려면 아래와 같이 하면 된다.
select * from testbl where sid in (3,4,1) order by field (sid, 3, 4, 1);
SELECT * FROM table LIMIT 3;
SELECT * FROM table LIMIT 2, 3;
SELECT * FROM table LIMIT 0, 4;
SELECT col1, col2, ...
FROM table
WHERE col_name IS NOT NULL;
SELECT col1, col2, ...
FROM table
WHERE col_name IS NULL;
IFNULL(NAME, 'No name')
SELECT col1, col2, ...
FROM table
WHERE col_name LIKE pattern;
SELECT col
FROM table
WHERE col_name IN (val1, val2, ...);
SELECT col1
FROM table
WHERE col_name BETWEEN val1 AND val2;
SELECT col1
FROM table1 INNER JOIN table2
ON table1.col = table2.col;
SELECT col1
FROM table1 LEFT INNER JOIN table2
ON table1.col = table2.col;
SELECT col1
FROM table1 RIGHT JOIN table2
ON table1.col = table2.col;
SELECT col1
FROM table1 FULL OUTER JOIN table2 # 쉽게말해 합집합, A테이블이 가지고 있는 데이터 , B테이블이 가지고있는 데이터 모두 검색
ON table1.col = table2.col;
SELECT
A.NAME, --A테이블의 NAME조회
B.AGE --B테이블의 AGE조회
FROM EX_TABLE A
CROSS JOIN JOIN_TABLE B # 모든 경우의 수를 전부 표현해주는 방식
SELECT col FROM table1
UNION
SELECT col FROM table2;
SELECT MIN (col) FROM table;
SELECT MAX (col) FROM table;
SELECT ABS (col) FROM table;
SELECT COUNT (col) FROM table;
SELECT AVG (col) FROM table;
SELECT SUM (col) FROM table;
특정 컬럼을 그룹화 하는 GROUP BY
SELECT col1
FROM table
WHERE condition
GROUP BY col_name;
특정 컬럼을 그룹화한 결과에 조건을 거는 HAVING
SELECT col1
FROM table
WHERE condition
GROUP BY col_name
HAVING condition;
SELECT CONCAT(str1, str2, str3, ...);
SELECT ROUND|TRUNC (val, digit)
FROM table