SQLD) Sql 기본

SQL

RDB에 대해 데이터의 구조를 정의, 데이터 조작, 데이터 제어 등을 할 수 있는 절차형 언어.

 

SQL의 종류

• DDL
  • 관계형 데이터베이스 구조를 정의하는 언어
  • Create, Alter, Drop, Rename
• DML
  • 테이블에서 데이터를 입력,수정, 삭제, 조회
  • insert, update, delete, select
• DCL
  • 데이터베이스 사용자에게 권한을 부여하거나 회수
  • Grant, Revoke
• TCL
  • 트랜잭션을 제어하는 명령어
  • Commit, rollback

 

트랜잭션

트랜잭션은 데이터베이스의 작업을 처리하는 단위.

트랜잭션의 특성

• 원자성
  • 연산의 전부와 일부 실행
  • 처리가 끝나지 않았을 경우 전혀 이루어지지 않는 것과 같아야 한다.
• 일관성
  • 트랜잭션의 실행 결과로 데이터베이스의 상태가 모순되지 않아야 한다.
  • 트랜잭션의 실행 후에도 일관성이 유지되어야 한다.
• 고립성
  • 트랜잭션 실행 중에 생성하는 연산의 중간결과는 다른 트랜젹선이 접근할 수 없다.
• 연속성
  • 트랜잭션이 성공적으로 실행되면 그 결과는 영구적 보장이 되어야 한다.

 

SQL문의 실행 순서

파싱 -> 실행 -> 인출

• 파싱
  • SQL 문의 문법을 확인하고 구문분석
• 실행
  • 옵티마이저가 수립한 실행 계획에 따라 SQL을 실행
• 인출
  • 데이터를 읽어서 전송한다.

 

DDL

• Create
• Alter
• Drop

 

Create

constraint

constraint를 사용해서 기본키를 지정한다.

• constraint 지정할 키 이름 primary key(주요 키를 할 컬럼 이름)

• constraint 지정할 키 이름 foreign key(외래 키를 할 컬럼 이름) references 외래키의table(외래 키를 할 컬럼 이름)

cascade

cascade를 사용하여 기본키-외래키의 참조 관계가 있을 경우 참조되는 데이터도 자동적으로 삭제할 수 있게 한다.

• constraint 지정할 키 이름 foreign key(외래 키를 할 컬럼 이름) references 외래키의table(외래 키를 할 컬럼 이름) on delete cascade
• https://daily-life-of-bsh.tistory.com/207(참고)

이에 대한 효과는 참조 무결성 준수를 지킬 수 있다.

Alter

Column

• ADD
  • ALTER TABLE tableNm ADD (columnNm datatype ..)
• MODIFY
  • ALTER TABLE tableNm MODIFY(columnNm datatype ..)
• DELETE
  • ALTER TABLE tableNm DROP COLUMN columnNm

Table

• DROP
  • DROP TABLE tableNm (cascade constraint)

View

• 가상 테이블.
• 실제 데이터 없음.
• 실행시에만 참조

특징

• 참조한 테이블이 변경되면 뷰도 변경
• 입력, 수정, 삭제 제약
• 보안성
• 변경을 원한다면 재생성해야 함
• ALTER 문을 사용하여 뷰를 변경할 수 없다.

사용

• Create View viewNm AS SELECT * FROM otherTableNm
• Drop View viewNm

 

DML

Insert

insert into tableNm(column ...) values( columnValue ...)

insert into tableNm select * from otherTableNm

최종적으로 데이터를 저장하려면 TCL문인 Commit을 실행해야 한다. (Auto Commit은 상관 x)

Nologging을 사용하여 로그 파일에 데이터의 정보를 저장하는데 Nologging 옵션은 로그파일의 기록을 최소화시켜 입력 시 성능을 향상시키는 방법이다.

Buffer Cache라는 메모리 영역을 생략하고 기록한다.

=> ALTER TABLE TABLE_NM NOLOGGING;

Update

UPDATE TABLE_NM SET 변경 조건 (WHERE ... 바꿀 조건 )

Delete

DELETE FROM TABLE_NM (WHERE ... 삭제 조건)

• 단, DELETE문으로 데이터를 삭제한다고 해서 테이블의 용량이 초기화 되진 않는다.
  • 용량이란?
    • 저장공간을 할당할 때 Extent 단위로 할당을 한다.
    • 만약 Extent의 크기가 최대 Extent를 넘어서게 되면 용량 초과 오류가 발생한다.
    • 즉 최대로 저장할 수 있는 공간의 의미를 가진다.
  • 모든 데이터 삭제
    • DELETE FROM TB_NM : 용량 감소 X
    • TRUNCATE TABLE TB_NM : 용량 초기화

Select

Select * from (where 검색 조건...)

• 정렬
  • Order by : desc , asc
• Distinct
  • 값 중복 안나오게 한다.
  • SELECT DISTINCT COLUMN_NM FROM TABLE_NM
• Alias
  • 실질적 이름 as 사용될 이름

Where SQL 연산자

• LIKE '비교 문자열'
  • 비교 문자열에 %를 추가하여 모든 값을 의미하게 둘 수 있다.
  • 비교 문자열에 _를 추가하여 한 개인 단일 문자를 의미하게 할 수 있다.
• BETWEEN A AND B
• IN (list)
• IS NULL

NULL 관련 함수

• NVL
  • NULL이면 다른 값으로 바꾸는 함수
• NVL2
  • 3항 방정식이라고 보면 편함
• NULLIF
  • 두 개의 값이 같으면 NULL, 같지 않으면 첫 번째 값을 반환

GROUP 연산

테이블에서 소규모 행을 그룹화 하여 합계, 평균, 최대 값, 최소 값 등을 계산하도록 하는 연산이다.

• Having 구에 조건문을 사용
• ORDER BY를 사용하여 정렬 가능

예시)

SELECT DEPTNO, SUM(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING SUM(SAL) > 10000

집계함수

• COUNT
  • NULL 제외
• SUM
• AVG
• MAX, MIN
• STDDEV : 표준편자
• VARIAN : 분산

실행 순서

SELECT ENAME (5)

FROM EMP (1)

WHERE EMPNO = 10 (2)

GROUP BY ENAME (3)

HAVING COUNT(*) >=1 (4)

 

ORDER BY ENAME; (6)

내장형 함수

• 문자형 함수
  • ASCII
  • CHART
  • SUBSTR
  • CONCAT
  • LOWER
  • UPPER
  • LEN
  • LTRIM, RTRIM
  • TRIM
• 날짜형 함수
  • SYSDATE
  • EXTRACT
• 숫자형 함수
• 조건 함수
  • DECODE(EMP_NO, 1000, 'T', 'F')
  • CASE WHEN CONDITION THEN RESULT ELSE RESULT
  • END
  • ...
  • CASE [EXPRESSION]
• ROWNUM, ROWID
  • ROWNUM
    • 조회되는 행 수 제한(게시판 페이징에 많이 사용)
    SELECT *WHERE list BETWEEN 5 AND 10
  • FROM (SELECT ROWNUM list, ENAME FROM EMP)
  • ROWID
    • 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값.
  • WITH
    • 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문.

 

DCL

GRANT

• 데이터베이스 사용자에게 권한 부여
• CRUD 권한
• GRANT문
  • GRANT privileges ON object TO user;
    • privileges : select, insert, update, delete, references, alter, index, all
  • 예시
    • GRANT SELECT, UPDATE FROM EMP TO LIMBEST
  • 권한을 부여할 수 있는 권한
    • WITH GRANT OPTION
      • 특정 사용자에게 권한을 부여할 수 있는 권한 부여
      • 연속 권한 있을 때 REVOKE 시 모든 권한 회수
    • WITH ADMIN OPTION
      • 테이블에 대한 모든 권한 부여
      • 위처럼 모든 권한을 회수 회수 하지 않는다.
    • 사용법
      • GRANT SELECT, UPDATE FROM EMP TO LIMBEST WITH GRANT OPTION
• REVOKE
  • REVOKE privileges on Object from user;

 

TCL

• commit
  • 트랜잭션 : 데이터 변경 전후같은 일련의 처리과정
• rollback
  • 이전 commit 단계까지만 복구
• savepoint
  • 트랜잭션 작게 분할 관리하는 것
  • 사용법 : savepoint sv명
  • 롤백 : rollback to sv명 ( sv명까지의 과정만 롤백)