오라클 설치, 기초 설정
SQL*Plus: Release 11.2.0.1.0 Production on 화 11월 21 18:19:27 2023
Copyright (c) 1982, 2010, Oracle. All rights reserved.
사용자명 입력: SYSTEM
비밀번호 입력: oral
다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SHOW USER
USER은 "SYSTEM"입니다
SQL> CONN SCOTT
비밀번호 입력: tiger
연결되었습니다.
SQL> conn system/orcl
연결되었습니다.
SQL> ALTER USER SCOTT / 얼터는 권한을 부여하는 작업
2 IDENTIFIED BY tiger;
사용자가 변경되었습니다.
*SCOTT 계정을 사용할 수 있도록 설정하기
SCOTT 계정 잠금 풀기
SQL> ALTER USER SCOTT >scott 계정을 변경하겠다는 의미
2 IDENTIFIED BY tiger >접속 비밀번호를 소문자 tiger로 지정한다는 의미
3 ACCOUNT UNLOCK; >계정을 사용 가능 상태(잠기지 않은 상태로) 전환한다는 의미
사용자가 변경되었습니다.
CSOTT 계정에 접속 / 유저라는 계정에 들어가는 로그인 개념
SQL> CONN SCOTT
비밀번호 tiger
연결되었습니다.
SQL> show user
USER은 "SCOTT"입니다
EMP 테이블 구성 확인
SQL> DESC EMP;
이름 널? 유형
----------------------------------------- -------- ----------------------------
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)
SQL> DESC SALGRADE;
이름 널? 유형
----------------------------------------- -------- ----------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
※SQL의 기본 뼈대, SELECT절과 FROM절
SELECT[조회할 열1 이름],[조회할 열2 이름],...[조회할 열N 이름],
FROM[조회할 테이블 이름];
EMP 테이블 전체 열 조회하기
SQL> SELECT*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
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이 선택되었습니다.
열을 쉼표로 구분하여 출력하기
SQL> SELECT EMPNO, ENAME, DEPTNO
2 FROM EMP;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
EMPNO ENAME DEPTNO
---------- ---------- ----------
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
14 개의 행이 선택되었습니다.
SQL> SELECT EMPNO, DEPTNO FROM EMP;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
EMPNO DEPTNO
---------- ----------
7900 30
7902 20
7934 10
14 개의 행이 선택되었습니다.
보기 좋게 별칭 설정하기
열에 연산식을 사용하여 출력하기
SQL> SELECT ENAME, SAL*12 , COMM FROM EMP;
ENAME SAL*12 COMM
---------- ---------- ----------
SMITH 9600
ALLEN 19200 300
WARD 15000 500
JONES 35700
MARTIN 15000 1400
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000 0
ADAMS 13200
ENAME SAL*12 COMM
---------- ---------- ----------
JAMES 11400
FORD 36000
MILLER 15600
14 개의 행이 선택되었습니다.
SQL> SELECT ENAME , SAL, SAL*12+COMM, COMM FROM EMP;
ENAME SAL SAL*12+COMM COMM
---------- ---------- ----------- ----------
SMITH 800
ALLEN 1600 19500 300
WARD 1250 15500 500
JONES 2975
MARTIN 1250 16400 1400
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500 18000 0
ADAMS 1100
ENAME SAL SAL*12+COMM COMM
---------- ---------- ----------- ----------
JAMES 950
FORD 3000
MILLER 1300
14 개의 행이 선택되었습니다.
별칭을 사용 : AS/ 띄어쓰기
SQL> SELECT ENAME, SAL *12+COMM AS ANNSAL, COMM FROM EMP;
ENAME ANNSAL COMM
---------- ---------- ----------
SMITH
ALLEN 19500 300
WARD 15500 500
JONES
MARTIN 16400 1400
BLAKE
CLARK
SCOTT
KING
TURNER 18000 0
ADAMS
ENAME ANNSAL COMM
---------- ---------- ----------
JAMES
FORD
MILLER
14 개의 행이 선택되었습니다.
DISTINCT 중복데이터 삭제
SELECT DISTINCT DEPTNO FROM EMP;
DEPTNO
----------
30
20
10
여러 개 열을 명시하여 중복 제거하기
SQL> SELECT DISTINCT JOB, DEPTNO FROM EMP;
JOB DEPTNO
--------- ----------
MANAGER 20
PRESIDENT 10
CLERK 10
SALESMAN 30
ANALYST 20
MANAGER 30
MANAGER 10
CLERK 30
CLERK 20
9 개의 행이 선택되었습니다.
※ORDER BY
SELECT
FROM
.
.
.
ORDER BY [정렬하려는 열 이름(여러 열 지정 가능)] [정렬 옵션];
*필수 요소 : 정렬하려는 열 이름을 하나 이상 지정
*선택 요소 : 정렬하는 열마다 오름 차순(ASC), 내림차순(DESC)지정
*설명 : ORDER BY절에 지정한 열은 먼저 지정한 열을 우선으로 정렬, 만약 정렬 옵션을 지정하지 않을 경우 기본값으로 오름차순(ASC)이 설정된다.
SQL> SELECT*FROM EMP ORDER BY SAL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7900 JAMES CLERK 7698 81/12/03 950
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7934 MILLER CLERK 7782 82/01/23 1300
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7782 CLARK MANAGER 7839 81/06/09 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 81/05/01 2850
30
7566 JONES MANAGER 7839 81/04/02 2975
20
7788 SCOTT ANALYST 7566 87/04/19 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
14 개의 행이 선택되었습니다.
SQL> SELECT*FROM EMP ORDER BY SAL;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800
20
7900 JAMES CLERK 7698 81/12/03 950
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7934 MILLER CLERK 7782 82/01/23 1300
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7782 CLARK MANAGER 7839 81/06/09 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7698 BLAKE MANAGER 7839 81/05/01 2850
30
7566 JONES MANAGER 7839 81/04/02 2975
20
7788 SCOTT ANALYST 7566 87/04/19 3000
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
14 개의 행이 선택되었습니다.
SQL> SELECT*FROM EMP
2 ORDER BY EMPNO ASC;
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
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7876 ADAMS CLERK 7788 87/05/23 1100
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000
20
7934 MILLER CLERK 7782 82/01/23 1300
10
14 개의 행이
다음 SQS문의 결과가 사원 번호 기준으로 내림차순이 되도록 코드를 채워 보세요.
SQL> SELECT*FROM EMP
2 ORDER BY EMPNO DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7934 MILLER CLERK 7782 82/01/23 1300
10
7902 FORD ANALYST 7566 81/12/03 3000
20
7900 JAMES CLERK 7698 81/12/03 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 87/05/23 1100
20
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7839 KING PRESIDENT 81/11/17 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7782 CLARK MANAGER 7839 81/06/09 2450
10
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
7566 JONES MANAGER 7839 81/04/02 2975
20
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7369 SMITH CLERK 7902 80/12/17 800
20
14 개의 행이 선택되었습니다.
EMP 테이블의 전체 열을 부서 번호(오름차순)와 급여(내림차순)로 정렬
SQL> SELECT*FROM EMP ORDER BY DEPTNO ASC, SAL DESC;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 81/11/17 5000
10
7782 CLARK MANAGER 7839 81/06/09 2450
10
7934 MILLER CLERK 7782 82/01/23 1300
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 87/04/19 3000
20
7902 FORD ANALYST 7566 81/12/03 3000
20
7566 JONES MANAGER 7839 81/04/02 2975
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 87/05/23 1100
20
7369 SMITH CLERK 7902 80/12/17 800
20
7698 BLAKE MANAGER 7839 81/05/01 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300
30
7844 TURNER SALESMAN 7698 81/09/08 1500 0
30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7521 WARD SALESMAN 7698 81/02/22 1250 500
30
7900 JAMES CLERK 7698 81/12/03 950
30
14 개의 행이 선택되었습니다.
select*from emp;
--사원번호가 7984인 사원정보는?
SELECT*FROM EMP
WHERE EMPNO = 7984;
SELECT EMPNO, ENAME, JOB FROM EMP
WHERE EMPNO >= 980;
*산술연산자
SELECT ENAME, SAL FROM EMP
WHERE SAL*12>30000;
SELECT * FROM EMP
WHERE SAL>2000 AND MGR >7800;
SELECT * FROM EMP
WHERE SAL>2500 AND JOB = 'ANALYST';
*등가 비교 연산자 A=B/ A!=B/ A<>B/ A^=B
SELECT*FROM EMP
WHERE SAL ! = 3000;
SELECT*FROM EMP
WHERE SAL <> 3000;
*논리 부정 연산자
SELECT*FROM EMP
WHERE NOT SAL = 3000;
*IN 연산자
OR연산자를 사용하여 여러 가 조건을 만족하는 데이터 출력하기
SELECT ENAME, JOB FROM EMP
WHERE JOB = 'CLERK';
SELECT ENAME, JOB FROM EMP
WHERE JOB = 'CLERK' OR JOB = 'SALESMAN' OR JOB= 'ANALYST';
SELECT ENAME, JOB FROM EMP
WHERE JOB IN ('CLERK', 'SALESMAN', 'ANALYST');
SELECT ENAME, JOB FROM EMP
WHERE JOB NOT IN ('CLERK', 'SALESMAN', 'ANALYST');
*IN연산자를 사용하여 다음 SQL문의 결과로 부서 번호가 10,20 번인 사원 정보만 나오도록 코드를 채워 보세요
SELECT * FROM EMP
WHERE DEPTNO IN(10,20);
--SAL 2000 크고 3000보다 작은 사원 정보
SELECT *FROM EMP
WHERE SAL >2000 AND SAL<3000;
SELECT *FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;
SELECT *FROM EMP
WHERE SAL NOT BETWEEN 2000 AND 3000;
--LIKE 연산자와 와이드 카드 : 문자열이 포함된 데이터를 조회할 때 사용
SELECT*FROM EMP
WHERE ENAME LIKE 'S%';
--T로 끝나는 사원 정보
SELECT*FROM EMP
WHERE ENAME LIKE '%T';
--두번째 알파벳이 C인 사원 정보
SELECT*FROM EMP
WHERE ENAME LIKE '_C%';
--IS NULL:값이 존재하지 않음/해당 사항 없음
SELECT*FROM EMP
WHERE COMM IS NULL;
SELECT*FROM EMP
WHERE SAL IS NOT NULL;
--집한 연산자 UNION, 칼럼의 순서가 같아야한다
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20;
--출력 열 개수와 자료형이 같을때:에러가 나지 않고 이름에 연연하지 않고 데이터 타입대로 정렬한다.
--칼럼을 다 적어주는 것이 좋다
--열의 개수가 맞지 않으면 합집합을 할 수 없다.
--숫자, 문자가 섞여있으면 합집합 할 수 없다.
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
SELECT*FROM EMP
WHERE SAL IS NOT NULL;
UNION
SELECT EMPNO, ENAME, DEPTNO,SAL
FROM EMP
WHERE DEPTNO = 20;
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
MINUS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;
SELECT* FROM EMP
WHERE ENAME LIKE '%S';
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP
WHERE DEPTNO=30 AND JOB='SALESMAN';
SELECT*FROM EMP
WHERE DEPTNO IN(20,30) AND SAL>2000;
SELECT ENAME, MGR , HIREDATE FROM EMP
WHERE ENAME='SMITH';
DESC SALGRADE;
SELECT DISTINCT DEPTNO FROM EMP;
--보기 좋게 별칭 설정하기: AS를 사용하여(띄어쓰기만도 가능) 쓰기
SELECT ENAME, SAL*12 AS SAL_YEAR FROM EMP;
--ASC 오름차순 ,DESC 내림차순/ORDER BY 지정한 열은 먼저 지정한 열을 우선으로 정렬
SELECT ENAME, EMPNO, DEPTNO FROM EMP;
SELECT ENAME, EMPNO, DEPTNO FROM EMP
WHERE DEPTNO IN(10,20)
ORDER BY DEPTNO DESC;
SELECT * FROM EMP
ORDER BY EMPNO ASC;
'2023.11.21-2024.05.31' 카테고리의 다른 글
| 테이블 (0) | 2023.11.23 |
|---|---|
| 231123 3일차(오라클) (0) | 2023.11.23 |
| 231122 복습 (1) | 2023.11.23 |
| 231122 2일차(오라클) (1) | 2023.11.23 |
| 2023.11.21 복습 (1) | 2023.11.23 |