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 |
이후 추가 수정예정
'여러가지' 카테고리의 다른 글
@PathVariable 어노테이션 (0) | 2022.03.03 |
---|---|
객체지향이란 무엇인가? (0) | 2020.09.12 |