DML : 데이터 조작어
SELECT : DB에 들어있는 데이터를 조회하거나 검색할 떄 사용하는 SQL문
1) 단일 SELECT
SELECT 칼럼리스트...[*]
FROM 테이블명;
Q1 ) 생성된 모든 테이블을 확인하자.
SQL> SELECT *
2 FROM TAB;
TNAME TABTYPE
------------------------------------------------------------ --------------
CLUSTERID
----------
BIN$H46tM4CZR3OdQ3f6WPSA3Q==$0 TABLE
BIN$PfWlsmmgRmSXcrAaiqbBDg==$0 TABLE
BIN$eKikftJOQg+jx59lS8wDfw==$0 TABLE
TNAME TABTYPE
------------------------------------------------------------ --------------
CLUSTERID
----------
BONUS TABLE
DEPT TABLE
EMP TABLE
TNAME TABTYPE
------------------------------------------------------------ --------------
CLUSTERID
----------
SALGRADE TABLE
Q2 ) 생성된 테이블 중 사원테이블(EMP)의 구조를 살펴보자(부서도 같음)
SQL> DESC EMP;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)# DESC : 테이블의 구조를 살펴볼 떄 사용
Q3) 사원테이블(EMP)의 모든 컬럼을 확인하자
SQL> SELECT *
2 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17
800 20
7499 ALLEN SALESMAN 7698 81/02/20
1600 300 30
7521 WARD SALESMAN 7698 81/02/22
1250 500 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
7566 JONES MANAGER 7839 81/04/02
2975 20
7654 MARTIN SALESMAN 7698 81/09/28
1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01
2850 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
7782 CLARK MANAGER 7839 81/06/09
2450 10
7839 KING PRESIDENT 81/11/17
5000 10
7844 TURNER SALESMAN 7698 81/09/08
1500 0 30
EMPNO ENAME JOB MGR HIREDATE
---------- -------------------- ------------------ ---------- --------
SAL COMM DEPTNO
---------- ---------- ----------
7900 JAMES CLERK 7698 81/12/03
950 30
7902 FORD ANALYST 7566 81/12/03
3000 20
7934 MILLER CLERK 7782 82/01/23
1300 10
12 rows selected.
Q4) 부서(DEPT) 테이블의 모든 컬럼을 확인하자.
SQL> SELECT *
2 FROM DEPT;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Q5) HELP SET을 통해 줄간격 등을 일정하게 맞춰주자
SQL> HELP SET
NUM[WIDTH] {10|n}
SQL> SELECT *
2 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
12 rows selected.
Q6) TYPE 형 확인하기
SQL> DESC EMP;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)# NOT NULL : NULL값을 가지지 않음
# VARCHAR(n) : n개 이하의 문자열
# NUMBER(int) :정수, Number(0.0) : 실수형
# DATE ; 날짜형
Q7) 테이블 구조를 보고 파악하기
SQL> SELECT *
2 FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- -------------------- ------------------ ----- -------- ----- ----- ------
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
======================================================================================
SQL> SELECT *
2 FROM DEPT;
DEPTNO DNAME LOC
------ ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON# SMITH의 사원번호 : 7369
# SMITH의 상사 : MGR ->7902 -> 'FORD'
# KING의 상사 : NULL
# FORD의 일하는 지역 : DEPNO -> 20 / 20 LOC -> 'DALLAS'
Q8) 사원테이블에서 사원의 이름, 사번을 출력해보자
SQL> SELECT ENAME, EMPNO
2 FROM EMP;
ENAME EMPNO
-------------------- -----
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566
MARTIN 7654
BLAKE 7698
CLARK 7782
ENAME EMPNO
-------------------- -----
KING 7839
TURNER 7844
JAMES 7900
FORD 7902
MILLER 7934
Q9) 사원의름과 부서번호, 부서이름을 출력하면서 테이블에 사원,부서라는 별칭을 주자
SELECT * FROM EMP,DEPT : CROSS JOIN 모든 컬럼에 대해 대응해서 나온다
SQL> SELECT 사원.ENAME,부서.DEPTNO, 부서.DNAME
2 FROM EMP 사원,DEPT 부서;
Q10) 사원테이블에서 사원의 이름, 부서번호, 봉급이라고 별칭을 주고 출력하자
SQL> SELECT ENAME AS "사원의 이름", DEPTNO AS "부서번호", SAL "봉급"
2 FROM EMP;
'IT > DataBase' 카테고리의 다른 글
| SQL - SUBQUERY (0) | 2021.05.25 |
|---|---|
| SQL - SET (0) | 2021.05.25 |
| SQL - JOIN (0) | 2021.05.24 |
| SQL - 그룹함수 (0) | 2021.05.21 |
| SQL - 날짜/문자 (0) | 2021.05.18 |