[ DB ] 01. SQL

1. SQL

저장된 데이터(정형화)를 사용하는 언어가 SQL이다.

1. (몇개 테이블을 쓸 것인가) - 조인
2. 어떤 것을 조회할 것인가? = 무엇을 추출할 것인지
3. 어떤 조건이 있는가?

1. 초기 환경설정

• server - setwork - admin - ( listerner.ora , tnsnameora ) - 모두 영문 내용이어야함

• 오류 발생시

  • 시스템관리도구 - 서비스 - OracleTNSListener 다시시작

• scott 계정생성과 권한부여하기

• set linesize 300; -> 접속할때마다 해줘야함

2. 기본문법

1. SQL 개요

1. DDL(데이터 정의 언어)

1. CREATE : 데이터베이스나 테이블 등을 작성한다.

2. DROP : 데이터베이스나 테이블 등을 삭제한다.

3. ALTER : 데이터베이스나 테이블 등의 구성을 변경한다.

4. 제약조건

   1. NOT NULL
   2. UNIQUE : 중복 되지 않는 키
   3. PRIMARY KEY : NOT NULL + UNIQUE
      • 구분해주는 키이기때문에 중복 불가능
   4. FOREIGN KEY : PRIMARY KEY 이외의 값은 사용할 수 없다.
      • 참조하는 키
   5. CHECK : 컬럼에 입력되는 값에 제한을 두는 것

• 테이블정규화에 의해 중복되어 들어가는 값들을 따로 데이터로

• 중복되지않는 유일한 값들을 갖고는 테이블의 기본키

  참조하는 키가 foreign key

  기본키가 설정된 테이블을 parent table

  참조하는게 child table

  제약조건을 제거하는게 가장 좋은 방법이다.

2. DML(데이터 조작 언어)

직접 데이터를 조작해서 원본을 변경

1. SELECT : 테이블에서 행을 검색한다.(따로 쿼리로 빼기도 한다.)

2. INSERT : 테이블에 신규 행을 등록한다.

   • 게시물등록? 댓글작성? 회원가입? 등등 DB에 새로운 내용 저장
   • 저장을 하기 위해서 commit; 해주어야 한다

   insert (all) into <테이블명> values('', '', ....);
   commit;

3. DELETE : 테이블에서 행을 삭제한다

   1. delete member;
   2. delete from member;
   3. delete from member
       where id = 'jjang';
   4. SQL> delete
       from member
       where addr = (select addr
                     from member
                     where id='jang');

4. UPDATE : 테이블에서 데이터를 변경(갱신)

   update member
   set addr =(select addr
           from member
           where id = 'jang')
   where id = 'lee';

3. DCL(데이터 제어 언어)

DBA할 거 아니면 집중적으로 하지 않는다.

1. COMMIT : 데이터베이스 변경 내용을 확정한다.
2. ROLLBACK : 데이터베이스 변경 내용을 취소한다.
3. GRANT : 사용자에게 처리 권한을 부여한다.
4. REVOKE : 사용자 처리 권한을 제거한다.

4. Query

< SELECT>

1. 기본

2. GROUP BY , HAVING

3. 조인( JOIN )

4. 서브쿼리

   => 전체 쿼리안에 또 다른 쿼리가 포함 되있는 것

   ​ (Main Query) (Sub Query)

5. 함수

5. 서브쿼리

• SQL문 안에 삽입된 QUERY
• SELECT문에서 주로 사용하고 SELECT문안에 삽입된 SELECT문.
• 바깥쪽의 QUERY를 MAIN QUERY, 안쪽에 삽입된 QUERY를 SUB QUERY
• SUB QUERY는 괄호로 묶어 주어야 한다.
• SUB QUERY는 메인쿼리가 실행되기 전에 한 번 실행되며 그 실행결과를 메인쿼리에서 사용한다.

[서브쿼리의 종류]

1. 단일행서브쿼리 - 결과가 1행 1열인 서브쿼리

2. 다중행서브쿼리 - 서브쿼리의 실행결과가 열 하나에 행이 여러개 인 경우

   • ' = ' 연산자와 같은 비교연산자를 사용할 수 없다.

• in : 다중에서 쓰는 = 이다.
  • < any : 서브쿼리 결과의 최대값보다 작은 값을 구하는 경우 사용
• ) any : 서브쿼리 결과의 최솟값보다 큰 값을 구하는 경우
  • < all : 서브쿼리 결과의 최솟값보다 작은 값을 구하는 경우
• ) all : 서크붜리 결과의 최대값보다 큰 값을 구하는 경우

   select ename, sal
   from emp
   where sal > any(select sal
               from emp
               where deptno =10);
<<<<<<< HEAD

>>>>>>> d7b64bb14e6cf0a250fc1284aec1675b9cd4777a

3. 다중컬럼 서브쿼리 - 두개 이상의 컬럼과 다중행을 반환하는 서브쿼리

   * 메인쿼리 비교 컬럼의 갯수, 종류가 서브쿼리의 반환 결과와 동일

   ```bash
   where (컬럼1, 컬럼2) in (select 컬럼1, 컬럼2
                           ...)

4. 상관형 서브쿼리(상호연관 서브쿼리) - 메인쿼리의 값이 서브쿼리에서 사용되는 경우

   • 메인쿼리 한 row에 대해 서브쿼리가 한 번씩 실행된다.
   • 메인쿼리의 값이 어떤 값이냐에 따라 서브쿼리의 결과가 달라진다.

   " 소속부서의 급여 평균보다 급여가 많은 사원들의 정보를 출력 "
   select ename, deptno, sal
   from emp outer
   where sal > (select avg(sal)
       from emp e
       where e.deptno = outer.deptno)

   [실행 흐름]

   1. 메인쿼리에서 비교할 값을 가져온다
   2. 메인쿼리에서 받은 값을 이용해서 서브쿼리가 실행된다.
   3. 서브쿼리의 실행결과로 메인쿼리가 실행된다.
   4. 메인쿼리의 레코드수만큼 반복된다.

5. from 절에서 사용하는 서브쿼리(inline view)

   • from절에 서브쿼리를 추가해서 사용
   • 서브쿼리 결과를 가상 테이블로 사용하겠다는 의미
   • from절에 추가되는 서브쿼리는 alias를 정의해야 한다.
   • from절에 추가되는 서브쿼리 내부의 컬럼은 실제 컬럼처럼 메인쿼리에서 사용해야 하므로 컬럼도 컬럼명이 존재하거나 alias를 정의해야 한다.

   select 컬럼명1,....
   from ( select 컬럼
           from 테이블명
           where 테이블명
           group by ....) alias

   select deptcode, countdata
   from (select deptno as deptcode, count(empno) as countdata
       from emp
       group by deptno) mytable; // 반드시 이름을 정의해줘야 한다

   " 소속부서의 급여 평균보다 급여가 많은 사원들의 정보를 출력 " + "조인과 from절에 추가하는 서브쿼리를 이용"
   select e.ename, e.deptno, e.sal, d.avgsal
   from emp e, (select deptno, avg(sal) avgsal
               from emp
               group by deptno) d
   where e.deptno = d.deptno
           and e.sal > d.avgsal;

6. 뷰

• (selec문이 저장 되있는) 실행결과로 보여지는 가상테이블

CREATE VIEW 뷰명 alias
AS
SELECT문

6. 기본적인 작성 규칙

• SQL 문 마지막에 세미콜론(;)을 붙인다.
• 대문자, 소문자 구분이 없다.
• 상수(리터럴) 작성은 작은따옴표(')를 이용한다.
• 단어는 공백 문자나 줄바꿈 문자로 구분한다.
• 1) from --> 2) where --> 3) group by --> 4) having --> 5) select --> 6)order by순으로 인식된다.

2. 테이블 작성

1. 데이터베이스 작성
   • 테이블을 저장할 데이터베이스

CREATE DATABASE <데이터베이스명>

2. 테이블 작성

CREATE TABLE <테이블명>
( 
<열명1> <데이터형> <이 열의 제약>,
<열명2> <데이터형> <이 열의 제약>,
<열명3> <데이터형> <이 열의 제약>,
            .
            .
            .
<이 테이블의 제약1>,<이 테이블의 제약2>,...);

3. 데이터형 지정
   • INTEGER형(정수형)
   • CHAR형(고정 문자열)
   • VARCHAR형(가변 문자열)
4. 제약 설정

3. 테이블 삭제와 변경

1. 3. SELECT문의 기본

1. 기본 SELECT문

SELECT <열명1>, <컬럼명2>, ...
    FROM <테이블명>;

• sql은 대소문자 구분하지 않는다.
• ; 은 sql문의 종료를 의미한다.
• ; 을 입력하기 전에 여러줄로 sql문을 작성할 수 있다.
• *는 모든 컬럼을 조회하겠다는 의미
• 컬럼에 null을 저장할 수 있다.
• null은 ()이나 space만 입력해 놓은 값과 다른 값
  • oracle에서의 null은 아무 값도 없는 것을 의미
  • -> 사용할 수 없고 정의될 수 없음.
• 컬럼명 대신 alias를 정의해서 사용할 수 있다.

select 컬럼명 alias명 / 컬럼명 as alias명 / 컬럼명 "alias명"
                                        => alias명에 공백이 있는 경우 사용

• 여러 컬럼을 합쳐서 하나의 컬럼으로 조회할 수 있다. (string의 concat과 비슷한 기능)
  • || 연산자 이용
• 오라클의 문자열, 날짜 데이터는 ' ' 로 표현
• 연산의 결과로 컬럼을 생성할 수 있다.
  • ( +, -, *, / ...함수...) 단, null이 포함되어 있는 컬럼은 연산할 수 없다.
• 중복이 있는 경우 중복을 제거하기 위해 select문에 distinct를 추가할 수 있다.

2. SELECT절에 조건 추가하기

SELECT[distinct] 컬럼명, 컬럼명,...[alias명]  ( [ ]는 줘도 되고 안줘도 되는 것 )
    FROM 테이블명
    WHERE 조건;

• 검색 결과를 제한(조건에 만족하는 데이터만 조회하겠다는 의미)

• where절은 반드시 from절 다음에 정의

• where절은 조건식이 true가 되도록 정의

• where절에 사용할 수 있는 비교연산자

• ( >, >=, <, <=, =(같다), <>(같지않다), !=(같지않다) )

• where 절에서 조건과 함께 비교하는 값을 추가해야 하는 경우 문자,날짜는 작은따옴표(')로 묶어주어야 한다.

• SQL은 대소문자를 구분하지 않는다. 값을 비교하는 경우 정확하게 대소문자까지 일치해야 한다.

• 두 개 이상의 조건이 있는 경우 사용할 수 있는 연산자

  • and연산자 : 모든 조건이 일치

  • or연산자 : 모든 조건 중 한개만 일치(조건이 모두 다른 컬럼인 경우)

  • between A and B : and연산과 동일 (같은 컬럼에서 조건을 비교하는 경우)

  • in연산자 : or연산자의 의미와 동일 (같은 컬럼에서 값을 여러 개 비교해야 하는 경우)

    => 컬렴명 in (비교값1, 비교값2, ...)

  • not연산자 : 부정

• null 값에 대한 비교

  • is null : null인 데이터를 조회
  • is not null : null이 아닌 데이터를 조회

3. SQL-PLUS에서 사용할 수 있는 명령어

• 컬럼명 like 값 % ( 값으로만 시작하면 글자수 상관 x)

• 컬럼명 like % 값 ( 값으로 끝나는 글자)

• 컬럼명 like % 값 % (값이 있는건 다 조회)

• 컬럼명 like _A _ _ : 두번째에 A가 들어간 거

• like 연산자 : 대표문자와 함게 사용
  => 조건 비교를 위해 입력한 값이 문자열에 포함되어 있는 것을 찾는 경우

   => % : 모든 문자열
    _ ; 한 자리 문자를 의미

• cl scr : 전체화면 클리어

• conn : 접속

4. 데이터 정렬

SELECT[distinct] 컬럼명1, 컬럼명2, ...[alias명]
    FROM 테이블명
    WHERE 조건
    ORDER BY 컬럼명 정렬기준

• 정렬기준
  • asc : 오름차순 정렬
  • desc : 내림차순 정렬
  • 정렬기준을 생략하면 기본은 오름차순
  • 정렬기준을 여러개 설정할 수 있다.( , 를 이용해서)

4. 함수

1. 단일행 함수

select, where, order by절에 모두 사용할 수 있다.

테이블의 모든 행에 각각 함수가 적용된다.

(1) 문자함수

• lower (문자열 or 컬럼명) : 값을 소문자로 변환

• upper (문자열 or 컬럼명) : 값을 대문자로 변환

• initcap (문자열 or 컬럼명) : 전달된 값의 첫글자만 대문자로 변환

• substr (문자열 or 컬럼명, 시작위치, 문자열의 갯수) :

  • oracle은 index가 1부터 시작한다고 생각한다.

  select substr('oracle', 2, 2) from dual;
  
  > ra

• length (문자열 or 컬럼명) : 문자열의 길이를 반환

• instr (문자열 or 컬럼명, **찾을 문자, 찾을 위치,** n번째 문자) :

  • 특정 컬럼이나 문자열에서 문자 위치를 찾을 때 사용하는 함수.(indexof)
  • 찾을 위치나 n번째 문자에 대한 매개변수는 생략이 가능
  • 찾을 위치를 -1로 정의하면 문자열의 오른쪽 끝에서 문자를 찾는다.(lastindexof)

  select instr('oracle oracle oracle', 'a', 5, 2) 
  from dual;
  
  > 17
  => 해당 문자열의 5번 위치부터 a를 찾아 위치를 반환.
  => 단, 2번째 a위치를 반환

• concat (문자열 or 컬럼명, 문자열 or 컴럼명) : || 연산자와 동일 => 문자열 연결

• LPAD (문자열 or 컬럼명, 출력할 문자열의길이, 출력할 문자열(채움문자))

  • 전체 출력할 문자열의 길이에 문자열을 출력한 후 남는 공간에 정의한 문자를 채워 출력해주는 함수(왼쪽)

• RPAD(문자열 or 컬럼명, 출력할 문자열의 길이, 출력할 문자열(채움문자))

  • 전체 출력한 문자열의 길이에 문자열을 출력한 후 남는공간에 정의한 문자를 채워 출력해주는 함수(오른)

  select rapd('oracle', 10, '*') from dual;
  
  > oracle****

• ltrim (문자열 or 컬럼명,** 문자)

  • 컬럼에서 매개변수로 정의한 문자를 왼쪽에서 찾아 모두 제거
  • 연속된 문자만 제거

• rtrim(문자열 or 컬럼명, 문자)

  • 컬럼에서 매개변수로 정의한 문자를 오른쪽에서 찾아 모두 제거
  • 연속된 문자만 제거

  select rtrim('oracleaaaaaaaaaaa', 'a') from dual;
  
  > oracle

(2) 숫자 함수

• round(숫자, 반올림한 위치) : 반올림

select round(125.8888, -1) from dual;
> 130

select round(125,8888, 1) from dual;
>125.9

• floor (숫자, 반올림한 위치): 버림
• trunc (숫자, 반올림한 위치): 버림
• ceil (숫자, 반올림한 위치): 올림

(3) 변환함수

• 데이터의 타입을 변환하기 위한 함수

• 또 다른 함수의 매개변수로 사용하게될 경우 타입이 컬럼의 타입과 일치해야 하므로

• cast (값 or 컬럼명 as 타입)

  • 타입 : date(날짜) / integer (숫자)

• to_char (변환할데이터, 표시할format)

  • 숫자를 문자로 변환 : 세 자리마다 ,를 출력하고 싶은 경우
    • 숫자의 한자리를 표시 => 9, 0
    • 콤마 => ,
    • 소수점 => .
    • 통화기호 => , $, L(지역의 통화기호를 출력)
  • 날짜를 문자로 변환 : 년 원 일 각각의 데이터를 추출하고 싶은 경우
    • 년도 => YYYY(2019)
    • 월 => MM(12)
    • 일 => DD(19)

  select ename, sal, to_char(sal, 'L9.999') from emp;
  >￦2,975
  select ename, hiredate, to_char(hiredate, 'YYYY')
  >1996

(4) 날짜함수

• sysdate : 오늘 날짜
  • 연산이 가능하다.

(5) null처리함수

• nvl (컬럼, null인 경우 처리할 식이나 값) : null인 경우 대신할 식이나 값을 명시
• nvl2 (컬럼, 표현값1, 표현값2)
  • 표현값 1 ; null이 아닌 경우
  • 표현값 2 ; null인 경우

(6) *****언어 변환

alter session set nls_launguage = 'american';
=> oracle에서 사용하는 언어를 바꿀 수가 있다.

2. 그룹행 함수

GROUP BY를 적용한 후에 사용할 함수

sum(총합), avg(평균), max(최대값), min(최솟값), count(카운트) 등

• 그룹함수는 그룹으로 묶인 데이터에 적용되므로 where절에 그룹함수를 사용할 수 없다.
  • where절은 한행 한행 검사하기 때문.

5. 그룹화

SELECT 컬럼...
FROM 테이블...
WHERE 조건
GROUP BY 그룹화할 컬럼명(함수를 포함한 식도 가능)
HAVING 그룹바이한 결과에 적용할 조건
ORDER BY 정렬할 컬럼명

• 테이블에 저장된 레코드를 그룹화하여 분류하고 싶은 경우 사용

• 내가 갖고 있는 데이터들을 다 표시할 것이 아니라, 내가 보고싶은 컬럼만 볼 것이다!

• select절에는 group by절에 명시한 컬럼명과 그룹함수만 사용할 수 있다.

• 데이터가 복잡한 경우 group by절에 두 개 이상의 컬럼을 명시할 수 있다.

• group by하기 전에 적용해야 하는 조건은 where 절에 정의

• group by한 결과에 조건을 적용해야 하는 경우 having절을 이용

  • 조건에 그룹함수를 써야 하면 having에 추가!

• 앞에 명시한 게 대그룹 - 뒤에 있는 게 소그룹.

  => 먼저 대그룹으로 묶고 난 후 그 후에 다시 소분류로 분류하겠다!

• 1) from --> 2) where --> 3) group by --> 4) having --> 5) select --> 6)order by순으로 인식된다.

6. 조인(JOIN)

• PK와 FK관계인 테이블에서만 조인이 가능하다.
• 어떤 테이블을쓸지 명시 - 조인조건
• 테이블과 테이블의 연관성을 명시.
• 2개 이상의 테이블에서 어떤 값을 뽑아서 하나의 결과를 만들고 싶을 때.
• 정규화된 테이블이나 혹은 일반적으로 작성된 여러 테이블의 컬럼을 이용해서 데이터를 조회하는 것을 조인.
• 조인은 관계형 데이터베이스(RDBMS)에서 반드시 알아야 하는 개념
• 기본키와 외래키의 관계를 이용해서 테이블을 조인
  • 외래키를 가지고 기본키 테이블에서 값을 비교하여 작업이 진행된다.
• 조인을 하는 경우 반드시 where절에 조인조건을 정의해야 한다.
• 테이블을 여러개 사용하는 경우 모든 테이블들의 조인조건을 정의해야 하며 select절에서 사용하지 않고 조건으로만 사용한다고 하더라도 조인조건은 정의해야 한다.

[ 조인 작성 방법 ]

1. from절에 조회하고 싶은 데이터가 저장된 테이블들을 모두 명시

2. 조인을 하는 경우 컬럼이 어떤 테이블의 컬럼인지 명확하게 정의하기 위해 "테이블명.컬럼명"으로 액세스를 한다.

3. from절에 테이블명을 정의하면서 alias를 함께 추가하여 alias를 통해 액세스하도록 한다.

   SELECT alias1.컬럼명 , alias2.컬럼명....
   FROM 테이블1 alias1, 테이블2 alias2

4. where절에는 반드시 조인조건을 추가하며 조인조건에는 두 테이블의 값을 비교하기 위해 정의하는 것이므로 외래키(FK)와 기본키(PK)를 정의한다.

   • 외래키테이블(child 테이블)에 정의된 컬럼값을 기본키테이블(parent 테이블)에서 비교하여 정확하게 일치하는 경우 값을 가져온다.

     (테이블이 두개 - 조인조건 한 개 , 테이블이 세 개 - 조인조건 두 개, 테이블이 네 개 - 조인조건 세 개)

• 여러 테이블에서 조인을 하기 위해 기본키의 기준을 적절히 정의해줘야 한다!!

[ 조인의 종류 ]

(1) equi join -내부조인

• 두 테이블에서 정확하게 일치하는 컬럼에 대한 데이터만

(2) outer join -외부조인

• 두 개 이상의 테이블에 조인을 적용했을 때 join조건을 만족하지 않아도 데이터를 조회하고 싶은 경우 사용
• 조인조건에 (+)를 추가한다.
• 조인조건을 만족하지 않아도 한 쪽 테이블의 모든 데이터를 출력하고 싶을 때 사용하는 조인방식으로 정보가 부족한 테이블의 컬럼에 (+)를 추가한다.
  • (+)기호는 한쪽에만 사용한다
  • 데이터가 확장되어야 하는 곳에 추가한다.

SELECT 테이블alias.컬럼명..
FROM 테이블 alias
WHERE 테이블alias.컬렴명 = 테이블alias.컬렴명(+)
WHERE 테이블alias.컬렴명(+) = 테이블alias.컬렴명

(3) self join

• 두 개 이상의 테이블에서 조인하지 않고 같은 테이블의 컬럼을 이용해서 조인.

  (하나의 가상 테이블 생성)
  <<<<<<< HEAD

=======

7. 시퀀스(SEQUENCE)

1. 시퀀스 개념 이해와 시퀀스 생성

- 오라클에서는 행을 구분하기 위해서 기본 키를 두고 있습니다. 기본 키는 중복된 값을 가질 수 있으므로 항상 유일한 값을 가져야 합니다.

- 기본 키가 유일한 값을 갖도록 사용가가 직접 값을 생성해내려면 부담이 클 것입니다.

- 시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이므로 시퀀스를 기본 키로 사용하게 되면 사용자의 부담을 줄일 수 있습니다.

2. 뷰의 기본 테이블

다음은 시퀀스를 생성하기 위한 기본 형식입니다.

CREATE SEQUENCE sequence_name

​ [START WITH n] ①

​ [INCREMENT BY n] ②

​ [{MAXVALUE n | NOMAXVALUE}] ③

​ [{MINVALUE n | NOMINVALUE}] ④

​ [{CYCLE | NOCYCLE}] ⑤

​ [{CACHE n | NOCACHE}] ⑥

① START WITH

시퀀스 번호의 시작값을 지정할 때 사용됩니다. 만일 1부터 시작되는 시퀀스를 생성하려면 START WITH 1이라고 기술하면 됩니다.

② INCREMENT BY

연속적인 시퀀스 번호의 증가치를 지정할 때 사용됩니다. 만일 1씩 증가하는 시퀀스를 생성하려면 INCREMENT BY 1이라고 기술하면 됩니다.

③ MAXVALUE n | NOMAXVALUE

MAXVALUE 은 시퀀스가 가질 수 있는 최대값을 지정합니다. 만일 NOMAXVALUE를 지정하게 되면 ASCENDING 순서일 경우에는 1027승이고 DESCENDING 순서일 경우에는 -1로 설정됩니다.

④ MINVALUE n | NOMINVALUE

MINVALUE 은 시퀀스가 가질수 있는 최소값을 지정합니다. 만일 NOMINVALUE을 지정하게 되면 ASCENDING 순서일 경우에는 1이고 DESCENDING 순서일 경우에는 1026승으로 설정됩니다.

⑤ CYCLE | NOCYCLE

CYCLE 은 지정된 시퀀스 값이 최대값까지 증가가 완료되게 되면 다시 START WITH 옵션에 지정한 시작 값에서 다시 시퀀스를 시작하도록 합니다. NOCYCLE은 증가가 완료되게 되면 에러를 유발시킵니다.

⑥ CACHE n | NOCACHE

CACHE 은 메모리상의 시퀀스 값을 관리하도록 하는 것인데 기본 값은 20입니다. NOCACHE는 원칙적으로 메모리 상에서 시퀀스를 관리하지 않습니다.