SQL 자주 쓰이는 문법

자주 쓰이는 문법

구분	명령어	예시
DB/테이블 생성	CREATE DATABASE/TABLE	sql\nCREATE DATABASE mydb;\nCREATE TABLE member (mno INT AUTO_INCREMENT PRIMARY KEY, mid VARCHAR(30));\n
컬럼 수정	ALTER TABLE	sql\nALTER TABLE member ADD mphone VARCHAR(20);\n
삭제	DROP	sql\nDROP TABLE member;\n
데이터 삽입	INSERT	sql\nINSERT INTO member(mid, mpw) VALUES ('hong','1234');\n
데이터 조회	SELECT	sql\nSELECT * FROM member WHERE mno=1;\n
데이터 수정	UPDATE	sql\nUPDATE member SET mpw='5678' WHERE mno=1;\n
데이터 삭제	DELETE	sql\nDELETE FROM member WHERE mno=1;\n
정렬	ORDER BY	sql\nSELECT * FROM member ORDER BY mno DESC;\n
중복 제거	DISTINCT	sql\nSELECT DISTINCT city FROM member;\n
집계	COUNT, SUM, AVG	sql\nSELECT COUNT(*), AVG(price) FROM product;\n
그룹화	GROUP BY/HAVING	sql\nSELECT city, COUNT(*) FROM member GROUP BY city HAVING COUNT(*)>10;\n
페이징	LIMIT	sql\nSELECT * FROM post ORDER BY pno DESC LIMIT 0,10;\n
조인	INNER JOIN	sql\nSELECT m.mid, p.ptitle FROM member m JOIN post p ON m.mno=p.mno;\n
조인	LEFT JOIN	sql\nSELECT m.mid, p.ptitle FROM member m LEFT JOIN post p ON m.mno=p.mno;\n
제약조건	PRIMARY/FOREIGN KEY	sql\nFOREIGN KEY (mno) REFERENCES member(mno) ON DELETE CASCADE;\n
IN/LIKE	IN, LIKE	sql\nSELECT * FROM product WHERE category IN('food','drink');\nSELECT * FROM member WHERE mid LIKE '%kim%';\n
서브쿼리	Subquery	sql\nSELECT * FROM product WHERE price > (SELECT AVG(price) FROM product);\n

DDL

• CREATE : 데이터베이스/테이블 생성
• ALTER : 테이블 구조 변경
• DROP : 삭제

DML

• INSERT : 데이터 삽입
• SELECT : 데이터 조회
• UPDATE : 데이터 수정
• DELETE : 데이터 삭제

SELECT 확장

• 조건 : WHERE / BETWEEN / IN / LIKE
• 정렬 : ORDER BY
• 집계 : COUNT, SUM, AVG, MAX, MIN
• 그룹화 : GROUP BY + HAVING
• 페이징 : LIMIT

JOIN

• INNER JOIN : 교집합
• LEFT JOIN : 왼쪽 기준, 오른쪽 없으면 NULL
• 여러 테이블 JOIN : member, post, category 함께 조회 가능

제약조건

• PRIMARY KEY : 기본
• FOREIGN KEY : 참조 무결성
• UNIQUE : 중복 불가
• NOT NULL : 빈 값 불가
• DEFAULT : 기본값 지정

기타

• 별칭 : AS
• 서브쿼리 : SELECT 안에 SELECT