03.SQL

jeong_hyeon·2022년 3월 15일
1

DB

목록 보기
3/3

DML (Data Manipulation Language) - 문자열 조작을 위한

  • insert
    • 테이블에 데이터 삽입
    
    1. 몇개의 컬럼에만 데이터를 삽입하는경우
    INSERT INTO 테이블명(컬럼리스트) VALUES(컬럼에 넣을 값 리스트);
    ex)
    INSERT INTO student(age, grade, report_card) VALUES(18, 2, 'A');
    
    2. 모든 칼럼에 데이터를 넣을경우
    INSERT INTO 테이블명 values(테이블에 맞는 데이터 양식);
    ex)
    INSERT INTO student(18, 2, Null,'A');
  • 데이터 유형이 문자일경우 ‘(single quotation)으로 입력할 값을 입력
    • 데이터 유형이 숫자 일 경우 그냥 입력
    • Primary Key 나 Not null 로 지정되어있는 칼럼은 null 이 허용 되지 않는다.
    • 두번째 유형은 칼럼의 순서대로 빠짐없이 데이터 가 입력되어야 한다.
  • update
    • 테이블 내부 데이터 내용 수정
    
    ```markdown
    UPDATE 테이블 SET 칼럼 = '값' WHERE 조건;
    ex)
    UPDATE student SET age = 19;
    UPDATE student SET report_card = 'A+';
    ```
  • delete

    • 데이터 삭제 FROM문구는 생략이 가능한 키워드 이며 뒤에 WHERE 절을 사용하지 않는다면 테이블의 전체 데이터가 삭제된다.

      DELETE [FROM] 테이블 WHERE 조건;
ex)
DELETE FROM student;
  • select
    • 조회
    • ALL : Default 옵션이므로 별도로 표기하지 않아도 된다. 중복된 데이터가 있더라도 모두 출력한다.
    • DISTINCT : 중복된 데이터가 있는 경우 1건으로 처리해서 출력한다.
    1. 모든 컬럼 조회 : SELECT * FROM 테이블명;
    2. 필요한 컬럼 조회 : SELECT 컬럼명,컬럼명.. FROM 테이블명;
    3. 중복된 데이터가 있는경우 1건으로 처리해서 출력 
     : SELECT DISTANCT 컬럼명 FROM 해당칼럼이 있는 테이블명;
  • ALIAS 부여 하기 조회된 결과에 일종의 별명을 부여해서 칼럼 레이블을 변경할수 있다. 칼럼명의 바로 뒤에 위치 칼럼명과 ALAS 사이에 AS or as 키워드 사용 이중 인용부호는 Alias 가 공백 특수문자를 포함 하는 경우와 대소문자 구분이 필요할 경우 사용 칼럼 별명을 적용할때 별명 중간에 공백이 들어가는 경우 (ex: 곰 돌 이) 쌍따옴표를 사용 SQL Server의 경우 쌍따옴표 따옴표 [] 과 같이 세가지 방식으로 별명을 부여할수있다.
    SELECT PLAYER_NAME AS 선수명, POSITION  AS 포지션, HEIGHT AS 키,
									 WEIGHT AS 몸무게 FROM PLAYER;
  • 산술연산자와 합성연산자
    • 산술연산자
      • 산술연산자는 NUMBERdhk DATE 자료형에 대해 적용되며 수학의 4칙연산과 동일 그리고 우선순위를 위한 괄호적용이 가능하다.
      • 일반적으로 산술연산을 사용하거나 특정함수를 적용하게 되면 칼럼의 LABEL이 길어지고 기존칼럼에 대해 새로운 의미를 부여한것이므로 적절한 별명을 새로 부여하는 것이 좋다.

    예제)
    SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게"
  • 합성(CONCATENATION) 연산자 문자와 문자를 연결하는 합성 연산자를 사용하면 별도의 프로그램 도움 없이도 SQL 문장만으로도 유용한 결과를 출력할 수 있다.
    • 문자와 문자를 연결하는 경우 2개의 수직 바 (| |)에 의해 이루어짐 (Oracle)
    • 문자와 문자를 연결하는 경우 + 표시에 의해 이루어짐 (SQL Server)
    • 두 벤더 모두 공통적으로 CONCAT (string1, string2) 함수를 사용할 수 있음
    • 칼럼과 문자 또는 다른 칼럼과 연결시킴
    • 문자 표현식의 결과에 의해 새로운 칼럼을 생성

DDL (Data Definition Language ) - 문자열의 정의를 위한

데이터 베이스의 테이블에 특정 자료를 입력할때 그 자료를 받아들일 공간을 자료의 유형별로 나누는 기준

  • create

    • 테이블 생성

      규칙
      • 테이블 명은 적절한 이름을 사용하며 단수형이 좋다.
      • 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
      • 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없고, 다른 테이블 간에는 칼럼명이 중복 될 수 있다.
      • 테이블 이름을 지정하고 각 칼럼들은 괄호로 묶어 지정한다.
      • 각 칼럼들은 콤마로 구분되고, 마지막 칼럼은 콤마를 쓰지 않는다.
      • 테이블 생성문의 끝은 항상 세미콜론으로 끝난다.
      • 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
      • 칼럼 뒤에 데이터 유형은 반드시 지정되어야 한다.
      • 테이블명과 칼럼명은 숫자가 아닌 문자로 시작해야하고, 길이에 대한 한계를 가진다.
      • 벤더에서 사전에 정의한 예약어는 쓸 수 없다.
      • A-Z, a-z, 0-9, _, $, # 문자만 허용 된다.
      • 테이블 생성시 대/소문자 구분은 하지 않고, 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.
      • DATETIME 데이터 유형에는 별도로 크기를 지정하지 않는다.
      • 문자 데이터 유형은 반드시 가질 수 있는 최대길이를 지정해야 한다.
      • 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.
      CREATE TABLE 테이블명(
	칼럼명 데이터타입 조건(not null 등등),
	칼럼명 데이터타입 조건(not null 등등),
	칼럼명 데이터타입 조건(not null 등등),
);
  • 칼럼 LEVEL 정의 방식 : 칼럼의 데이터 유형 뒤에 NOT NULL과 같은 제약조건을 정의
  • 테이블 LEVEL 정의 방식 : 테이블 생성 마지막에 모든 제약조건을 기술
    • 제약 조건(CONSTRAINT) →제약 조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉 데이터 무결성을 유지하기 위한 데이터 베이스의 보편적인 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.
    • NULL의 의미
      • NULL은 아직 정의 되지않은 미지의 값 이거나 현재 데이터를 입력하지 못하는 경우
    • DEFAULT의 의미
      • DEFAULT 값을 정의 했다면 해당 칼럼에 NULL 값이 입력 되지 않고 사전에 정의된 기본 값이 자동으로 입력된다.
    • 생성된 테이블 구조 확인
      • SQL Server : “sp_help’dbo.테이블명’”
      • Oracle : “DESCRIBE 테이블명;” or “DESC 테이블명;”
  • ALTER TABLE

한번 생성된 테이블은 특별히 사용자가 구조를 변경하기전에는 생성 당시의 구조를 유지하게 된다.

처음 테이블 구조를 그대로 유지 하는것이 최선이지만 업무적 요구나 시스템 운영상 테이블을 사용하는 도중 변경해야 하는일이 발생할수 있는데 이때 ALTER 명령어를 쓰게 된다.

  • ADD COLUMN

    • ALTER TABLE 테이블명
    • ADD 추가할 칼럼명 데이터 유형;

  • DROP COLUMN

    • ALTER TABLE 테이블명
    • DROP COLUMN 삭제할 칼럼명;

  • MODIFY COLUMN

    • ALTER TABLE 테이블명
    • MODIFY(칼럼명1 데이터유형 [DEFAULT식][NOT NULL], 칼럼명2 데이터유형...)
      • 기존의 데이터가 훼손 될수 있기 때문에 해당 칼럼의 크기를 늘릴수는 있지만 줄이지는 못한다.
      • 해당 칼럼이 NULL값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일수 있다.
      • 해당 칼럼이 NULL 값만 가지고 있으면 데이터 유형을 변경할수 있다.
      • 해당 칼럼이 DEFAULT 값을 바꾸면 변경 작업 이후 발생하는 행 삽입에만 영향을 미친다
      • 해당 칼럼이 NULL 값이 없을 경우에만 NOT NULL 제약 조건을 추가할수있다.

  • RENAME COLUMN

    • ALTER TABLE 테이블명
    • RENAME COLUMN 변경해야할 컬럼명 TO 새로운 컬럼명 ;

  • DROP CONSTRAINT

    • ALTER TABLE 테이블명
    • DROP CONSTRAINT 제약조건명;

  • ADD CONSTRAINT

    • ALTER TABLE 테이블명
    • ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

  • DROP TABLE

    • DROP TABLE 테이블명 [CASCADE CONSTRAINT];
    • DROP 명령어 를 사용하면 테이블의 모든 데이터 및 구조를 삭제한다.
    • CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약 조건에 대해서도 삭제 한다는 것을 의미
    • SQL Server에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전에 참조하는 FOREIGN KEY 제약조건 또는 참조하는 테이블을 먼저 삭제 해야한다.

  • TRUNCATE TABLE

    • TRUNCATE TABLE은 테이블 자체가 삭제되는것이 아니고 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제한다.
    • TRUNCATE TABLE 테이블명;
    • TRUNCATE 는 DROP와 달리 테이블의 구조는 유지한채 데이터만 전부 삭제가 가능하다.

  • RENAME

    • RENAME 명령어를 이용하여 테이블 이름을 변경할수 있다.
    • RENAME 변경전 테이블명 TO 변경후 테이블명;
    • SQL Server에서는 sq_rename을 이용하여 테이블 명을 변경할수있다.
    • sq_rename 변경전 테이블명,변경후 테이블명

DCL (Data Control Language) - 데이터 제어를 위한언어

  • grant
  • revoke

TCL (Transaction Control Language) →DCL에서 트랜잭션을 제어하는 명령만 따로 분류 한것

트랜잭션 : 데이터베이스의 논리적 연산단위

밀접히 관련되어 분리될수없는 한개이상의 데이터베이스 조작

분할할수없는 최소단위

트랜잭션의 특성

잠금(LOCKING)

트랜잭션이 수행하는동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한 하는 기법

TCL

  • commit
    • 데이터 변경 작업 시 올바르게 반영된 데이터를 데이터 베이스에 반영시키는것
    • COMMI
    UPDATE INTO PLAYER
    
    SET HEIGHT = 100;
    
    480개의 행이 수정되었다.
    
    COMMIT;
    
    커밋이 완료되었다.

COMMIT 명령어는 이처럼 INSERT 문장, UPDATE 문장, DELETE 문장을 사용한 후에 이런 변경 작업이 완료되었음을 데이터베이스에 알려주기 위해 사용

COMMIT 이후 데이터의 상태

- 데이터에 대한 변경 사항이 데이터베이스에 반영
- 이전 데이터는 영원히 잃음
- 모든 사용자는 결과를 볼 수 있음
- 관련된 행에 대한 잠금이 풀리고, 다른 사용자들이 행을 조작할 수 있음
  • ROLLBACK
    • 트랜잭션 시작 이전의 상태로 되돌리는것
    • 테이블 내 입력한 데이터나 수정한 데이터 삭제한 데이터에 대해 COMMIT이전에는 변경사항을 취소할수있는데 이때 데이터 베이스에서는 롤백(ROLLBACK)기능을 사용
    • ROLLBACK;
    UPDATE PLAYER SET HEIGHT = 100;
    
    480개의 행이 수정되었다.
    ROLLBACK;
    
    롤백이 완료되었다.
    
    => 이 경우 480개의 행이(키 들이) 100으로 수정되었던 결과를 다시 되돌리기 위해
     롤백하여 처음의 상태로 돌아오도록 만드는 것

ROLLBACK 후 데이터 상태

  • 데이터에 대한 변경 사항은 취소

  • 이전 데이터는 다시 재저장

  • 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있게 됨

  • savepoint

    • 저장점

    • 전체작업을 롤백하는것이 아닌 현 시점에서 저장점까지 트랜잭션의 일부만 롤백이 가능

    • SAVEPOINT SVPT1;

    • ROLLBACK TO SVPT1;

    • SQL Server에서는 다음과 같이 사용한다.

    • SAVE TRANSACTION SVTR;

    • ROLLBACK TRANSACTION SVTR;

      SAVEPOINT에 따른 ROLLBACK원리


위 그림과 같이 A로 롤백이후 B방향 즉 미래방향으로 되돌릴 수는 없다 . 특정 지점까지 롤백하면 그저장점 이후에 설정한 저장점은 무효 된다.

연산자

  • 산술 연산자 : + - / *
  • 비교 연산자 : > < >= <= = != ^= <>
  • 논리 연산자 : and, or, not
  • 비교 연산자2 : between and, in, is null, like
  • 계산 함수

    • max

    • min

    • count

    • avg

    • sum

      select 계산함수(칼럼) from 테이블; 로 사용

    함수

    문자 조작 함수
    • UPPER(str) : 대문자 변환
    • LOWER(str) : 소문자 변환
    • INITCAP(str) : 첫 글자만 대문자로 변환
    • CONCAT(str, tmp) : 두 문자값 결합
    • SUBSTR(str,a,b) : 문자 추출 a = 시작위치, b = 개수
    • LENGTH(str) : 문자열 길이 반환
    • L/RPAD(대상,총길이,채울문자열) : 대상 문자열에 채울문자열을 총길이만큼 채워서 반환LPAD('001',7,'0') ▶ 0000001
    • LTRIM, RTRIM(대상, 제거할 문자열) : 대상 문자열에서 제거할 문자열을 없앤 뒤 반환select LTRIM('00010' , '00') ▶ 010
    • REPLACE(대상문자열,바꾸고싶은대상,바꾸고싶은내용) : 대상문자열에서 바꾸고 싶은 대상을 바꾸고 싶은 내용으로 수정한 뒤 반환REPLACE('ABCDEFG','DEF','XXX') ▶ ABCXXXG

    JOIN

    INNER JOIN

    일반적인 조인, A와 B 테이블의 조인에서 A와 B 테이블 모두에 있는 레코드만 조회된다.

    SELECT A.a, A.b, B.c
FROM A
JOIN B
ON B.a = A.c;

    ON 대신 WHERE를 사용할 수 있다.

    LEFT OUTER JOIN

    A와 B 테이블의 조인 상황에서 A테이블 기준으로 B테이블에 A테이블의 칼럼과 일치하는 조건이 없으면 B테이블의 칼럼은 NULL로 조회된다.

    SELECT A.a, A.b, B.c
FROM A
LEFT OUTER JOIN B
ON B.a = A.c;

    outer를 생략할 수 있다.

    RIGHT OUTER JOIN

    A와 B 테이블의 조인 상황에서 B테이블 기준으로 A테이블에 B테이블의 칼럼과 일치하는 조건이 없으면 A테이블의 칼럼은 NULL로 조회된다.

    SELECT A.a, A.b, B.c
FROM A
RIGHT OUTER JOIN B
ON B.a = A.c;

    역시 outer를 생략할 수 있다.

    CROSS JOIN(Cartesian JOIN)

    집합의 곱 개념으로A= {a, b, c, d} , B = {1, 2, 3} 일 때A CROSS JOIN B 는(a,1), (a, 2), (a,3), (b,1), (b,2), (b,3), (c, 1), (c,2), (c,3), (d, 1), (d, 2), (d,3)의 결과가 된다.결과의 개수는 n(A) * n(B) 이다.

    SELECT A.a A.b B.c
FROM A
CROSS JOIN B;
// 위 아래 쿼리의 결과는 같다.
SELECT A.a, A.b, B.c
FROM A, B;
profile
jeong_hyeon
이전 포스트

02.RDB

0개의 댓글