06-23(수) Oracle, SQL Developer

※ 개발 공부 정리글이며 해당 직군에서 종사하며 계속 공부하고 수시로 정리중입니다. 

---

정말 아무것도 모르는 생초짜에서 공부 시작했다.

 

■ Oracle, SQL Developer 각각 설치

Oracle  = Database 종류 중 1가지

SQL Developer = Oracle을 관리하기 위한 프로그램 툴 같은 것

 

-압축파일2개

압축파일 'OracleXE112_Win64' 오라클 중에서도 11g XE 버전 설치

압축파일 'sqldeveloper-20.2.0.175.1842-x64' <=jdk가 포함된 버전?

 

① 압축풀기

② OracleXE112_Win64 -  setup.exe 파일 더블클릭

③ password : oracle / oracle 로 설정하고 계속 Next

 

■ Oracle 정상 설치 확인 경로

ctrl+shift+esc -서비스    ->    Oracle     ->     ~ Listener와 Oracle Service가 있으면 OK

 

■ Run SQL Comman Line  검색해서 실행(cmd같은 프로그램. cmd에서 해도 무방)

SQL> CONNECT sys/oracle AS sysdba  --입력엔터
Connected.                         --나오면 서비스 접속 성공
SQL>

Run SQL Comman Line에서도 DML, CML, DDL할 수 있지만 좀더 편리하게 사용할 수 있는 SQL developer로 사용

■ Oracle SQL Developer sys 계정 생성
-Oracle SQL Developer 실행-> 접속에 +    -> 

Name : sys

사용자 이름 : sys      (* oracle DB는 대소문자를 구분함)       롤: (관리자라서) SYSDBA

비밀번호     : oracle  (* oracle DB는 대소문자를 구분함)

SID : xe   

->   테스트   ->   '상태:성공' 뜨면 '▣비밀번호 저장' 체크 후 '저장'   ->   좌측에 sys 생성 완료

■ sys 계정으로 다른 계정 찾고 생성
-학생계정인 scott 계정 생성-> sys 우클릭 (재)접속->

AlTER USER scott IDENTIFIED BY tiger;
-- 바꾼다 / scott이란 사용자계정을 / 인증되도록 / tiger라는 비밀번호로
-- 마지막 세미콜론 ; 는 문장마침표와 같은 쿼리문 종료마침표. 없으면 ctrl+enter로 실행불가

->오류라고 뜸(해당 계정은 지금 없으니까 오류 발생이 정상) -> 다시 sys계정 접속

CREATE USER scott IDENTIFIED BY tiger;
-- 생성한다 / scott이란 사용자계정을 / 인증되도록 / tiger라는 비밀번호로

-> "User SCOTT이(가) 생성되었습니다." 라고 뜸

■ scott 계정 생성, sys가 권한부여
접속에 + ->

Name        : scott

사용자 이름 : scott           롤 : (관리자가 아니고 일반이라) 기본값

비밀번호    : tiger

SID : XE

->  테스트  ->

-> 아까와 달리 계정은 있어도 sys관리자가 scott에게 접근권한을 안줬기 때문에 '실패' 뜨는게 정상
-> sys 우클릭 (재)접속 ->

GRANT CONNECT, RESOURCE TO scott;

-> "Grant을(를) 성공했습니다." 반환

-> 위 다시 하면 테스트 성공 뜨고 '비밀번호저장' 체크 후 저장해서 좌측 scott 생성완료

*그래서  sys로 접속해서 계정 생성 후에 바로 ALTER, CREATE, GRANT 필요한 권한부여까지 다 해두면 편리함

*나중에 mybatis 할때 쓸,  tester 계정도 미리 만들어두기

CREATE USER tester IDENTIFIED BY tester;
GRANT CONNECT, RESOURCE TO tester;

■ scott/tiger 계정과 tester/tester 계정에 TABLE 생성&값 INSERT(더보기 열기)

--DEPT TABLE 생성
DROP TABLE DEPT;
CREATE TABLE DEPT
(
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);

--DEPT Record INSERT
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES	(40,'OPERATIONS','BOSTON');

--EMP TABLE 생성
DROP TABLE EMP;
CREATE TABLE EMP
(
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);

--EMP Record INSERT
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

--BONUS TABLE 생성
DROP TABLE BONUS;
CREATE TABLE BONUS
(
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER,
    COMM NUMBER
);

--SALGRADE TABLE 생성
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
(
    GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER
);

--SALGRADE Record INSERT
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

COMMIT;

-> ctrl+a

-> ctrl+enter

->'오류' 뜨며 '행이 삽입되었습니다'  뜨면 OK

-> scott 계정 누르고 새로고침

-> DEP,EMP,SAL,BONUS 테이블 4개 생성확인

 

***[scott계정_Table생성6개]TB_script.txt 테이블도 전체 복붙해서 실행(오래걸림)

 

***[tester계정_Table생성2개]Final_Script.sql  tester에 쓸 table도 미리 추가 (더보기 열기)

DROP TABLE employee PURGE;
CREATE TABLE employee
(
    empno NUMBER(4) CONSTRAINT employee_empno_pk PRIMARY KEY,
    ename VARCHAR2(10) NOT NULL,
    dname VARCHAR2(20) NOT NULL,
    loc   VARCHAR2(20) NOT NULL,
    sal   NUMBER(5) NOT NULL,
    hiredate DATE NOT NULL,
    state VARCHAR2(10) NOT NULL
);
   
INSERT INTO employee ( empno, ename, dname,loc,sal, hiredate , state ) 
VALUES ( 1000, '홍길동','영업','서울', 7000, to_char('2000/06/01'),'근무');
INSERT INTO employee ( empno, ename, dname,loc,sal, hiredate , state) 
VALUES ( 2000, '임꺽정','영업','부산', 5000, to_char('2008/02/01'),'출장');
INSERT INTO employee ( empno, ename, dname,loc,sal, hiredate , state) 
VALUES ( 3000, '이몽룡','영업','인천', 4500, to_char('2013/02/01'),'출장');
INSERT INTO employee ( empno, ename, dname,loc,sal, hiredate , state ) 
VALUES ( 4000, '성춘향','개발','서울', 3500, to_char('2014/06/01'),'휴가');
INSERT INTO employee ( empno, ename, dname,loc,sal, hiredate , state ) 
VALUES ( 4001, '김선달','개발','서울', 3500, to_char('2014/06/01'),'외출');
   
COMMIT;

 

■ 'sqldeveloper-21.4.1.349.1822-x64' 폴더는 

로컬디스크(C:)드라이브에 압축 풀어서 두는게 나음

---

■ 데이터베이스 관리시스템 = DBMS DataBase Management System
-DB(=데이터집합)을 검색조작하기 위한 언어=DB언어
ex) ANSI , ISO에서 DB표준언어로 채택된 SQL(Structured Query Language)
-우리가 공부할 때 사용하는 DBMS = Oracle SQL Developer
-관계형 데이터베이스(RDBMS)는 '2차원 테이블형태의 집합체' =>엑셀 같은데 조금 다른듯
열= cloumn컬럼 / (헤더 제외) 행=law?로우 / 컬럼+로우=테이블
ex) scott 테이블

테이블 DEPT -> 데이터-> DETNO (department number 부서번호) DNAME (부서이름) LOC (location위치)

테이블 EMP -> 데이터-> EMPNO (employee number 사번) ENAME(사원이름) MGR(관리자번호) SAL(급여) COMM(상여금)

 

■ SQL 종류
DQL(질의어) = ★SELECT란 명령문★
DML(조작어) = INSERT, UPDATE, DELETE
DDL(정의어) = CREATE, ALTER, DROP, RENAME, TRUNCATE(-삭제)
->이정도만 중심적으로 보기
-SQL문 대소문자 구별X ★다만, 나중에 컬럼이나 값 자체를 적을땐 반드시 제대로 써줘야함★
-자바와 마찬가지로 키워드가 있고, 웬만하면 대문자 사용하기
-끝날땐 반드시 ;로 끝내기
ex) scott 워크시트 -> SELECT  * FROM DEPT; *=전체
-> SELECT *(나타낼 기존컬럼 또는 함수로 보여줄 새컬럼을 순서대로 ,로 보여줘라) FROM (데이터를 추출해올 테이블) ;

 

SELECTIION: 테이블의 행(로우)를 선택하기 위해 사용
PROJECTION: 테이블의 열(컬럼)을 선택하기 위해 사용
JOIN : 공통컬럼을 이용해서 다른 테이블의 데이터를 가져오기 위해 사용

-SELECT 함수나 임의 컬럼 FROM DUAL; DUAL=임시가상테이블-SQL문 옆에 -- 달면 주석 다는 거

■ DESC 명령어(=DESCRIBE) = 명령어 안 쓰고도 테이블 열에서도 볼 수 있음

DESC 테이블;

=> 컬럼이름, 널?, 컬럼데이터유형 (숫자인지 문자인지 날짜인지)

■ DUAL, 별칭 ALIAS (AS로 쓰고 생략가능) , NULL
-별칭에서 공백문자나 특수문자 사용할 때만 " " 큰땀표를 씀, 나머지 문자,날짜데이터엔 ' ' 작땀표 써야함
★꼭 구분하기★

-NULL : 비어있는 값 => 정수값 = 0과는 다름. 아예 값 자체가 없어서, 연산비교 불가라서

결과값도 NULL (56쪽) => IS NULL, IS NOT NULL이라는 문구를 사용하면 연산비교 가능