190522_Day30 DB(Oracle)-SQL

용진용진 2019. 5. 22. 21:19

2019. 5. 22. 21:19

190522_Day30 DB(Oracle)

오라클 설치

https://www.oracle.com/index.html
Oracle Database 다운로드
Oracle Database Express Edition 사용
Oracle Database 11g Release 2 Express Edition for Windows 64 사용

데이터베이스 개요

데이터베이스란?
- 관리해야 하는 데이터를 모아놓은 집합체
- 데이터의 중복없이 서로 연관되어 있는 집합
- 행과 열의 형태
DBMS란, 프로그램과 DB사이의 매개체, 징검다리 (서버)
- DataBaseManagementServer
- 서버 사용 용도 뿐만 아닌 내부 저장용도로도 사용됨
데이터베이스의 목적
- 중복 최소화
- 공유
- 보안유지
- 무결성
파일관리시스템
- 검색,추가,수정,삭제 기능을 사용자가 원하는 정보를 이요할 수 있게 해주는 프로그램
단점
- 데이터의 중복성
- 데이터의 불일치
- 프로그램 데이터간의 종속성
- 제한적인 정보 제공
  - 독립적인 파일을 갖고 그 각각에 파일에 대한 정보만 제공
- 보안문제
관계 데이터 모형
- 일직선상의 데이터 나열이 아닌
- 행(row) 열(Column)을 사용하여 표시
데이터베이스 구조
- 물리적(내부)
- 논리적(개념적)
- 사용자(외부)
  - 위의 세개의 사이에는 물리적 독립성과 논리적 독립성이 보장되어야 하며 이는 자료 독립성을 보장하게 한다.

<DB관련 수업>

Java - SQL(Structured Query Language) - JDBC - ibatis(MyBatis)

데이터 저장하는 인메모리, 파일, DB중 DB를 하고 있는 것.

<DB접속>

접속 툴 : 토드, SQL Developer(오라클 제공), 이클립스, SQL*PLUS
SQL*PLUS 는 수업할 때나 사용, 이를 통해 접속한다.
cmd에서 sqlplus 계정명/패스워드 로도 가능

<오라클 계정>

SYS : 오라클 Super 사용자 계정이며 데이터베이스에서 발생하는 모든 문제들을 처리 할 수 있는 권한을 가짐
- sqlplus /nolog
- conn /as sysdbs
SYSTEM : 오라클 데이터베이스를 유지보수 및 관리할 때 사용하는 계정
- 명령프롬프트창 : sqlplus system/oracle
- SQL명령창 : conn system/oracle
SCOTT, HR : 처음 오라클을 사용하는 사용자를 위해 만들어 놓은 사용자 계정

<데이터베이스 관리자 접속을 해서 사용자 [SCOTT]을 만들고 권한 주기>

관리자 접속

C:\Users\Playdata // 윈도우즈 관련 명령어 입력 대기상태!
C:\Users\Playdata>sqlplus
Enter user-name: system
Enter password:oracle
SQL>_

사용자 계정 만들기

CREATE USER [user_name]
IDENTIFIED BY [password];

SQL> create user scott
  2  identified by tiger;

User created.

SQL은 명령어에서 대소문자 구별 안함!
생성된 scott계정의 접속 시도
- 방법1) 현재 연결된 [system]관리 계정에서 접속을 끊고 다시 [scott]유저 접속하기
- ```
exit
sqlplus scott/tiger
```
- 방법2) 계정 접속만 변경하기
- ```
conn scott/tiger
```
- 엥 그런데 에러 발생?!
- ```
ERROR:
ORA-01045: user SCOTT lacks CREATE SESSION privilege; logon denied
```
- 뭐야... 이게?
  - 접속 권한 불충분!

생성된 계정(User)에게 연결 권한 부여(관리자가)

grant connect,resource to scott;
Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> show user
USER is "SCOTT"
SQL>

<접속 계정에서의 생성된 테이블 종류 보기(검색)>

select * from tab;
---> no rows selected(검색된 테이블 정보 없음)

<예제 테이블 정보 가져오기(복사하기)>

imp scott/tiger file=c:\Yongjin\scottTable.dmp fromuser=scott touser=scott constraints=y commit=y ignore=y tables=dept,empsalgrade

주의) SQL>_프롬프트상태에서는 SQL관련 명령어만 입력 => imp는 sql명령어가 아님
- C:\Users\Playdata>_프롬프트상태의 imp입력!
- 반드시 한줄로 입력
- file뒤에 dmp파일이 실제 존재하는 곳을 명시!
- 확인) select tname from tab;
TNAME

DEPT
EMP
SALGRADE
<각 테이블의 구조(컬럼명, 자료형) 검색>
DESC[RIBE] 테이블명 - 테이블을 구성하는 컬럼(속성)과 자료형을 표현
describe emp

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO (사원번호)                           NOT NULL      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 dept - (부서테이블 - 3개의 속성으로 구성)

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO (부서번호)                                   NOT NULL NUMBER(2)
 DNAME  (부서명)                                     VARCHAR2(14)
 LOC    (부서위치)                                   VARCHAR2(13)

SQL> desc salgrade - (급여등급테이블 - 3개의 속성으로 구성)

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GRADE                                              NUMBER
 LOSAL                                              NUMBER
 HISAL                                              NUMBER

Structured Query Language ( 구조적인 질의어 )

종류 ) 암기사항
1. ★데이터 정의어(DDL) : Data Definition Language
  - 데이터베이스 관리자나 응용프로그래머가 데이터베이스의 논리적인 구조를 정의하기 위한 언어
  - 객체( 예 : Table, View( 가상테이블 ), Sequence, Trigger )에 대한 정의
  - 명령어 : CREATE(생성), DROP(삭제), ALTER, RENAME, TRUNCATE(포크레인 처럼 데이터를 제거해주는)
2. ★데이터 조작어(DML) : Data Manipulation Language
  - 데이터베이스에 저장된 데이터를 조작(추가, 수정, 삭제) 하기 위해 사용하는 언어
  - 명령어 : INSERT, UPDATE, DELETE
3. 데이터 제어어(DCL) : Data Control Language
  - 데이터베이스에 대한 접근 권한 부여등의 데이터베이스 시스템의 관리를 위한 목적으로 사용되는 언어
  - 명령어 : GRANT( 권한 부여 ), REVOKE( 권한 회수 ),
4. ★데이터 질의어(DQL) : Data Query Language
  - 검색, 조회 명령어
  - 명령어 : SELECT
5. 트랜잭션 제어어(TCL) : Transaction Control Language
  - 논리적인 작업단위의 묶음
  - 다수개의 DML에 대한 묶음.
  - ATM에 비유함, 했던 행동들을 취소할 수 있는, 혹은 확정지을 수 있는 명령
  - 명령어 : COMMIT, ROLLBACK, SAVEPOINT( ROLLBACK과 연관, 저장점 )

<테이블에 저장된 데이터에 대한 검색(조회)>

SELECT명령문 사용

형식)

SELECT (조회하고자 하는)컬럼명
FROM (컬럼이 속한)테이블명;
---
SELECT 컬럼명1, 컬럼명2
FROM 테이블명;
---
부서테이블 조회(모든 속성)
SELECT deptno, dname, loc
FROM dept;

SQL> select deptno, dname, loc
  2  from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

부서테이블 조회(부서명, 부서위치)

select DNAME, loc;
=> ORA-00923: FROM keyword not found where expected 에
---
select DNAME, loc
from dept;

DNAME                        LOC
---------------------------- --------------------------
ACCOUNTING                   NEW YORK
RESEARCH                     DALLAS
SALES                        CHICAGO
OPERATIONS                   BOSTON

<테이블내의 데이터를 검색>
형식)

SELECT 컬럼명1, 컬럼명2 (또는 출력할 데이터)
FROM (컬럼 속한) 테이블명

문제

문제1) 급여등급, 최소급여, 최대급여 조회
SQL> select grade, losal, hisal
  2  from salgrade
  3  ;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

문제2) 부서명과 부서위치 출력
SQL> select dname,loc
  2  from dept;

DNAME                        LOC
---------------------------- --------------------------
ACCOUNTING                   NEW YORK
RESEARCH                     DALLAS
SALES                        CHICAGO
OPERATIONS                   BOSTON

문제3) 사원번호, 사원명,입사일,급여를 조회
SQL> select empno,ename,hiredate, sal
  2  from EMP;

     EMPNO ENAME                HIREDATE        SAL
---------- -------------------- -------- ----------
      7369 SMITH                80/12/17        800
      7499 ALLEN                81/02/20       1600
      7521 WARD                 81/02/22       1250
      7566 JONES                81/04/02       2975
      7654 MARTIN               81/09/28       1250
      7698 BLAKE                81/05/01       2850
      7782 CLARK                81/06/09       2450
      7788 SCOTT                87/04/19       3000
      7839 KING                 81/11/17       5000
      7844 TURNER               81/09/08       1500
      7876 ADAMS                87/05/23       1100

     EMPNO ENAME                HIREDATE        SAL
---------- -------------------- -------- ----------
      7900 JAMES                81/12/03        950
      7902 FORD                 81/12/03       3000
      7934 MILLER               82/01/23       1300

문제4) "안녕, SQL~!"을 화면에 출력
    =>  SELECT : 컬럼조회와 출력에 사용하는 SQL명령어.
    SQL> select '안녕, SQL~!' as hi // 만약 h 띄고 i 로 하고 싶다면 "" 큰따움표로 묶어주기
  2  from emp;

    ※오라클에서는 테이블에 상관없는 데이터를 출력하기 위해 시스템 테이블을 지원!
    =>dual테이블(dumy테이블, 가상테이블)
    => <별칭 사용하기>
    SELECT 컬럼명 [AS] 별명
    FROM 테이블명;
    //AS는 생략가능
    주의)오라클은 문자, 문자열 데이터 표현 시 ''작은 따옴표 사용!, ""큰따옴표는 컬럼명 또는 별명 표현하는 용도로 사용

문제5) 화면에 사원번호와 사원명 출력
SQL> select empno "사원번호" ,ename "사원명"
  2  from emp;

  사원번호 사원명
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

  사원번호 사원명
---------- --------------------
      7900 JAMES
      7902 FORD
      7934 MILLER

문제6) 각 사원들의 사원번호 사원명 급여 연봉
   select empno, ename, sal salary, sal*12 "연 봉"
   from  emp; 
     EMPNO ENAME                    SALARY      연 봉
---------- -------------------- ---------- ----------
      7369 SMITH                       800       9600
      7499 ALLEN                      1600      19200
      7521 WARD                       1250      15000
      7566 JONES                      2975      35700
      7654 MARTIN                     1250      15000
      7698 BLAKE                      2850      34200
      7782 CLARK                      2450      29400
      7788 SCOTT                      3000      36000
      7839 KING                       5000      60000
      7844 TURNER                     1500      18000
      7876 ADAMS                      1100      13200

     EMPNO ENAME                    SALARY      연 봉
---------- -------------------- ---------- ----------
      7900 JAMES                       950      11400
      7902 FORD                       3000      36000
      7934 MILLER                     1300      15600

문제7) 사원들의 이름과 직책을 출력하시오.
(출력 변경 => SMITH사원의 직책은 CLERK입니다.)
SQL> select ename || '사원의 직책은' || job || '입니다.' "사원 직책"
  2  from emp;

사원 직책
--------------------------------------------------------------------------------
SMITH사원의 직책은CLERK입니다.
ALLEN사원의 직책은SALESMAN입니다.
WARD사원의 직책은SALESMAN입니다.
JONES사원의 직책은MANAGER입니다.
MARTIN사원의 직책은SALESMAN입니다.
BLAKE사원의 직책은MANAGER입니다.
CLARK사원의 직책은MANAGER입니다.
SCOTT사원의 직책은ANALYST입니다.
KING사원의 직책은PRESIDENT입니다.
TURNER사원의 직책은SALESMAN입니다.
ADAMS사원의 직책은CLERK입니다.

사원 직책
--------------------------------------------------------------------------------
JAMES사원의 직책은CLERK입니다.
FORD사원의 직책은ANALYST입니다.
MILLER사원의 직책은CLERK입니다.

문제8) 사원들의 직책을 출력하되, 중복된 내용은 제거
select distinct job  
from emp;
JOB
------------------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

문제9) 10과 3의 사칙연산의 결과를 출력하는 SQL문을 작성하시오.
SQL> select 10+3 더하기, 10-3 빼기, 10*3 "곱하기", 10/3 나누기
  2  from dual;                                       0/3 나누기

    더하기       빼기     곱하기     나누기
---------- ---------- ---------- ----------
        13          7         30 3.33333333

SQL> select 10+3 "더하기", 10-3 빼기, 10*3 "곱하기", floor(10/3) floor나누기, mod(10,3) mod나누기
  2  from dual;

    더하기       빼기     곱하기 FLOOR나누기  MOD나누기
---------- ---------- ---------- ----------- ----------
        13          7         30           3          1

SQL>

<별칭 alias 사용하기>
형식)

SELECT 컬럼명 [AS] 별명
FROM 테이블명;
//AS는 생략가능

<Concatenation 연산자> //Concat이라고도 부름
- 접합연산자 : ||
- '문자열1' || '문자열2' =>> '문자열1문자열2'
- select ename || '사원의 직책은' || job || '입니다.' "사원 직책"
- from emp;
<DISTINCT 키워드> : 중복제거 기능
형식)

SELECT DISTINCT (중복이 예상되는) 컬럼명
FROM 테이블명;

'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글

190529_DAY35, <복습>, <DDL>, <DML> (0)	2019.05.29
190528_DAY ANSI JOIN 복습, 서브쿼리, DDL시작 (0)	2019.05.28
190527_DAY33 decode, case , join (0)	2019.05.27
190524_Day32 DB3 복습 및 진도 (0)	2019.05.24
190523_Day31 DB2 (0)	2019.05.23

Yong Study

190522_Day30 DB(Oracle)-SQL

190522_Day30 DB(Oracle)

TNAME

'클라우드 기반 웹 개발자 과정 공부 > DB' 카테고리의 다른 글

+ Recent posts

티스토리툴바