SQL 쿼리 모음

SELECT 조회

SELECT *(전체) FROM 테이블명 WHERE 조건

 

UPDATE 수정

UPDATE 테이블명 SET 열 = 값 WHERE 조건

 

DELETE 삭제

DELETE FROM 테이블명 WHERE 조건

 

인덱스 생성 및 삭제

CREATE INDEX 인덱스명 ON 인덱스명(컬럼)

 

DROP INDEX 인덱스명

 

ORDER BY

SELECT * FROM 테이블명 ORDER BY 정렬할 열 ASC -순정렬

SELECT * FROM 테이블명 ORDER BY 정렬할 열 DESC - 역정렬

SELECT * FROM 테이블명 ORDER BY 정렬할 열1 ASC, 정렬할 열2 DESC - 열1은 오름차순, 열2는 내림차순

 

JOIN

두 개 이상의 테이블을 하나의 조건을 걸어 해당 조건을 가진 값들을 하나의 ROW로 조회하는 것

 - INNER JOIN : 조건이 일치하는 값이 두 테이블에 모두 존재할 때 조회

 ex) SELECT * FROM EMP A INNER JOIN DEPT B ON A.DEPTNO = B.DEPTNO;

조회 결과값

7369 SMITH CLERK 7902 80/12/17 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 81/11/17 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 81/12/03 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 10 ACCOUNTING NEW YORK

---

SELECT A.* FROM EMP A INNER JOIN DEPT B ON A.DEPTNO = B.DEPTNO;

조회 결과값

	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	SMITH	CLERK	7902	80/12/17	800	(null)	20
2	7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
3	7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
4	7566	JONES	MANAGER	7839	81/04/02	2975	(null)	20
5	7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
6	7698	BLAKE	MANAGER	7839	81/05/01	2850	(null)	30
7	7782	CLARK	MANAGER	7839	81/06/09	2450	(null)	10
8	7839	KING	PRESIDENT	(null)	81/11/17	5000	(null)	10
9	7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
10	7900	JAMES	CLERK	7698	81/12/03	950	(null)	30
11	7902	FORD	ANALYST	7566	81/12/03	3000	(null)	20
12	7934	MILLER	CLERK	7782	82/01/23	1300	(null)	10

---

SELECT B.* FROM EMP A INNER JOIN DEPT B ON A.DEPTNO = B.DEPTNO;

조회 결과값

	DEPTNO	DNAME	LOC
1	20	RESEARCH	DALLAS
2	30	SALES	CHICAGO
3	30	SALES	CHICAGO
4	20	RESEARCH	DALLAS
5	30	SALES	CHICAGO
6	30	SALES	CHICAGO
7	10	ACCOUNTING	NEW YORK
8	10	ACCOUNTING	NEW YORK
9	30	SALES	CHICAGO
10	30	SALES	CHICAGO
11	20	RESEARCH	DALLAS
12	10	ACCOUNTING	NEW YORK

A.* 전체 조회를 했을 때와 B.*전체 조회를 했을때 해당 테이블 컬럼값을 기준으로 하여 결과값이 다르게 나옴

---

OUTER JOIN :조건 방향희 테이블은 NULL 값을 포함하여 조회

SELECT A.* FROM EMP A LEFT OUTER JOIN DEPT B ON A.DEPTNO = B.DEPTNO;

	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	SMITH	CLERK	7902	80/12/17	800	(null)	20
2	7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
3	7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
4	7566	JONES	MANAGER	7839	81/04/02	2975	(null)	20
5	7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
6	7698	BLAKE	MANAGER	7839	81/05/01	2850	(null)	30
7	7782	CLARK	MANAGER	7839	81/06/09	2450	(null)	10
8	7839	KING	PRESIDENT	(null)	81/11/17	5000	(null)	10
9	7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
10	7900	JAMES	CLERK	7698	81/12/03	950	(null)	30
11	7902	FORD	ANALYST	7566	81/12/03	3000	(null)	20
12	7934	MILLER	CLERK	7782	82/01/23	1300	(null)	10

 

이후 추가 수정예정