데이터베이스 개요 및 SQL 활용

*데이터베이스란?

- 저장 공간. 폴더와 같은 개념

- 테이블로 구성된 집합체

폴더 -> 파일명을 지정

데이터베이스(저장 공간) -> 테이블로 저장(엑셀 테이블)

* SQLplus 명령어 : sql에서만 사용

SQL 명령어 -> 전 세계 표준 DB 언어

대소문자 구분 안함 - 오타 조심

Q1. 현재 어떠한 계정으로 로그인해서 작업하고 있는가?

sql>show user (sql plus에서만 가능한 명령어)

산출결과

SQL> show user
USER은 "SCOTT"입니다

* 명령어

user(오라클의 환경설정 변수)

select(검색하라는 키워드)

Q2. 현재 작업한 내용/목록을 확인할 수 있는 방법

sql>select * from tab

(문장 끝에 ;를 입력하고 enter / ;가 있을 경우는 sql 명령어)

산출결과

* 2가 나올경우 명령이 끝나지 않음을 표시

*테이블명(샘플링)

emp : 회원정보 -> 사원

sql>select * from tab

형식)select 보고자하는 항목(*) from 테이블명(=동의어)

sql>select * from emp (emp 테이블에 들어가 있는 정보 목록 검색)

(1) 데이터베이스 사용자 생성 및 권한 및 설정 _ 교재 38p

관리자 계정 : sys, system

                     신입 -> 계정생성 -> 로그인(권한)주기

사용자 계정 : 일반계정(scott)

(2) SQL 명령어

- DDL문(Data Definition Language) : 데이터 정의어(설계)

   -> 테이블 생성(create~),

   -> 계정생성(ctreate user~)

   -> 수정, 삭제

- DML문(Data Manipulation Language) : 교재 SQL 활용 p.14-p.16

   -> Insert(데이터 입력)

   -> update(데이터 수정)

   -> delete(데이터 삭제)

   -> select(검색어) : DQL이라고도 함. Date Query L. 

- DCL문(Date Control Language) :권한 명령어, 데이터 조절

   -> grant(권한주기), revoke(권한회수)

*화면지우기 : clear screen (sql plus 명령어)

Q3. 계정 생성하는 방법 DDL/ DCL

관리자로 로그인 후, 계정생성. 사용자가 계정에 접속할 수 있도록 권한을 설정해주기

- 계정생성 언어

형식) create user 생성할 계정명 identified(키워드) by 암호

EX) create user test01 identified by t1234 ;

1행에 오류:

ORA-01031: 권한이 불충분합니다 -> 관리자만이 가능하다는 표시

1, 관리자 로그인

형식) connect 접속계정명/암호 -> 일반 계정만

         connect 접속계정명/암호 as sysdba -> 관리자로 로그인

- 관리자로서의 기본역할

1. 계정을 생성 - 암호 부여

2. 적절한 업무에 해당하는 권한을 부여

* 방향키 history 기능

2. 계정 생성(create user) // DDL

EX) create user test01 identified by t1234 ;

3. 권한주기(create session) // DCL

SQL> connect test01/t1234
ERROR:
ORA-01045: user TEST01 lacks CREATE SESSION privilege; logon denied
-> 로그인 권한이 없다는 에러 메세지. 계정만 생성되었다고 접속이 가능하지 않음. 

형식) grant 권한종류1,권한종류2,,, to 계정명1, 계정명2,,,,

                                                       to public -> 등록된 모든 계정에게 권한 부여(그룹 개념)

EX) grant create session, resource to test01;

       grant connect, resource to test01

                (접속)     리소스(테이블~)->오라클 객체

* create session - 로그인 해주는 접속권한 - connect와 동일(롤)

  resource - 테이블뿐만 아니라 다른 오라클 객체, 기본적으로 사용하는 권한을 부여하는 명령어

* ORA-00922 : 누락된 또는 부적합한 옵션 -> 오타

4. 권한해제(revoke)

형식) revoke 회수할 권한종류명 from 계정명 -> 관리자만이 사용가능

EX) revoke create table from test01

Q5, 테이블 생성하기(DDL)

1. 데이터 형식

1) 숫자형태로 저장 : 나이, 몸무게(실수)

                                 number(3,2)=123.25

                                 자릿수, 소수점자릿수  

                                 number(2)=24

2) 문자형태로 저장

* 영어(1byte), 한글(2byte)

- character(고정길이) : 2000byte, 6byte로 고정하면 한글 세글자 이상이 들어가지 않음

- varchar(가변길이) : 4000byte, 길이에 유동성이 있어서 주로 사용. 주소,

EX) 주소 : varchar(60) -> 한글 30자 입력 가능

- clob : 4000byte 이상일 경우

* varchar : oracle의 경우 varchar2로 표기, SQL server의 경우 varchar로 표기

3) 계좌번호, 메일주소, 주민등록번호

-> 계산이 가능하면 숫자형태, 관계가 없으면 문자형태

2. 테이블 만들기

형식) create table 저장할테이블명

          (필드명=컬럼명, 저장대상자) 자료형(문자 or 숫자) 제약조건,

            필드명2~,

            필드명3~);

EX) create table prtest

       (id number,

         name varchar2(10));

Q6. 테이블의 구조(스키마)

테이블이 제대로 만들어졌는지 확인하는 명령어

형식)describe 테이블명 = desc 테이블명

Q7. DML(insert, update, delete, select) 데이터 입력

형식) insert into 테이블명 values(23,'문자형태'~);

*values에 필드에 들어갈 내용을 차례대로 입력.

  문자열의 경우 '  '(빈문자열)안에 입력

  날짜의 경우 '2017-4-16'

EX) insert into prtest values(1,'홍길동');

* 입력한 데이터 확인하기

형식) select *(보고자하는 필드명) from 테이블명(prtest)

EX) select * from prtest

      select id from prtest <- 필드명 id 입력하면 id만 나타남                   

Q8. 데이터 수정

데이터 4만개 -> 전부 변경

형식) 1. update 수정할 테이블명 set 수정할 필드명=수정한 값, 수정할 필드명2=수정한 값2,,,

          2. update 수정할 테이블 명 set 수정할 필드명=수정한 값 where 조건식 -> 조건에 만족

          -> 주로 회원 수정할 때

          -> where 검색할 대상자(필드명) 연산자 찾을 값

          -> where 조건을 지정하지 않으면 입력된 필드 전체가 수정할 값으로 변경됨

EX) update prtest set name='임시테스트' where id=2

id값이 2번인 name 갑순이 -> 임시테스트로 변경됨

* 변경된 값 확인하기

형식) select * from prtest where id=2;

* 조인, 서브쿼리

Q9. 백업테이블 생성

형식) create table 백업테이블명

         as select * from 원본테이블명; // full backup

                                                     ~  where 조건식  // 부분 backup

EX) create table b_prtest

       as select * from prtest;

*형식 확인하기

desc b_prtest

Q10. 잘못 저장된 데이터 삭제하기

형식) delete from 삭제할 테이블명; //모든 행 삭제(테이블이 삭제되는 것은 아님)

         delete from 삭제할 테이블명 where 조건식; // 해당사항에 대한 값만 삭제

EX) delete from b_prtest // 모든 행 삭제

       delete from b_prtest where id=4; // 필드명 id로 삭제하기

       delete from b_prtest where name='이름' // 필드명 name으로 삭제하기

(3) SQL 종료하기

종료창 누르면 데이터를 잃을 수 있음

형식) exit