★DB 기초 SQL

MoonjuLee 2022. 5. 24. 20:40

내가 공부한 것을 기록하며 복습하기(ORACLE)

SQL(Structured Query Language) : 구조화된 질의 언어 , 데이터를 조회, 수정, 갱신, 삭제 등을 할 수 있는 명령어

SQL문 정리

DDL (Data Definition Language) : 데이터 정의어, 데이터 베이스를 정의하는 언어

(create 생성, alter 수정, drop 삭제, truncate 초기화)

DML (Data Manipulation Language) : 데이터 조작어, 정의된 데이터 베이스를 조작하는 언어

(select 조회, insert 삽입, update 수정, delete 삭제)

DCL (Data Control Language) : 데이터 제어어, 데이터의 보안, 권리 권한 부여 등을 하는 언어

(grant 수행 권한부여, revoke 수행 권한 박탈/취소)

TCL (Transaction Control Language) : 트랜잭션을 처리하기 위한 명령어이다.(commit 저장, rollback 원상복구)

SQL문 자세한 이용법

1. DDL

테이블 사용
EX)

CREATE TABLE MEMBER
(
MEM_ID NUMBER(10) NOT NULL, --순번
MEM_NAME VARCHAR2(40) NOT NULL, --성명
MEM_DATE DATE , --입사일자
);

P.K, F.K 생성
EX)

CREATE TABLE MEMBER
(
MEM_ID NUMBER(10) NOT NULL, --순번
MEM_NAME VARCHAR2(40) NOT NULL, --성명
MEM_DATE DATE , --입사일자

Constraint 인덱스키명 Primary Key (필드명1, 필드명2)

Constraint 외부키명 Foreign key (필드명2) Reference 외부테이블명(외부필드명)

);

※ 테이블 복사 (CREATE 새로만들 테이블명 AS SELECT * FROM 복사할 테이블;)

하지만 기본키와 외래키는 복사 되지 않는다.

테이블 변경

EX)

ALTER TABLE MEMBER

ADD ( MEM_MAIL VARCHAR2(60) NOT NULL) --추가

ALTER TABLE MEMBER

MODIFY (MEM_DATE NOTNULL) --수정

삭제

EX)

DROP COLUMN MEM_DATE --컬럼삭제

DROP TABLE MEMBER --테이블삭제

1. DML

데이터 삽입

EX)

INSERT INTO MEMBER (MEM_ID, MEM_NAME) <- 생략시 MEMBER에 모두 삽입

VALUES (1, '홍길동');

INSERT INTO MEMBER (MEM_ID, MEM_NAME)

VALUES (2, '신짱구');

데이터 검색

EX)

사원 성명이 신짱구인 사원의 번호를 검색

SELECT [DISTINCT] MEM_ID --DISTINCT는 중복을 피해서 검색한다.

FROM MEMBER

WHERE MEM_NAME = '신짱구' ;

GROUP BY

HAVING

ORDER BY DESC | ASC; --생략되면 ASC(오름차순), DESC(내림차순)

※ ' * ' : 아스트리크, 테이블의 모든 컬럼을 조회할 때 쓴다.

※ 데이터 검색을 위한 연산자(산술, 비교, 논리 연산자)

산술연산자 : +, -, *, /, ()
비교연산자 : =, !=, <(이상도 가능),>(이하도 가능),<>(=라는 의미)
논리연산자 : AND, OR, NOT 
연산자의 우선순위는 (), NOT, AND, OR 이다. (이 순서로 실행됨)
기타연산자 : IN(둘 이상의 표현식 검색시 사용, NOT과 활용가능)
★LIKE 연산자 : _ , %(와일드카드)를 활용한 문자형태가 같은 ROW 검색
_ : 한 문자, % : 여러 문자 -> 이 _, %를 검색하려면 ESCAPE 사용

※ 컬럼명을 프로그램 실행시 바꿔서 보는법 (별칭:ALTAS)

SELECT MEM_ID 사원코드, "사원코드", AS "사원코드"

MEM_NAME 성명, "성명", AS "성명" →이와 같은 3가지 형식으로 쓴다.

FROM MEMBER ;

결과물은 컬럼명이 MEM_ID, MEM_NAME가 아닌 사원코드, 성명으로 보여진다.

(결과 조회시에만!!!)

※ NULL값으로 삽입 또는 변경하고 정렬을 한다면 ASC일때는 마지막에

DESC일때는 맨 처음에 위치한다.

데이터 변경

EX)

UPDATE MEMBER

SET MEM_NAME = '신영만'

WHERE MEM_ID = '1';

※ 주의 UPDATE (변경하고자 하는 테이블) SET (변경할 데이터) WHERE (변경하기를 원하는 데이터 검색조건)

함수

문자함수( || ) : || 연산자는 둘 이상의 문자열을 연결하는 연산자

CONCAT : 두 문자열을 연결하여 반환
CHR, ASCII : 아스키 값을 문자로 문자를 아스키 값으로 반환
LOWER, UPPER, INITCAP : 해당 문자나 문자열을 소문자로 반환, 대문자로 반환, 첫 글자를 대문자로 나머지는 소문자로 반환 EX) SELECT LOWER('Hello SQL') FROM DUAL; → hello sql 반환 , 다른 것들도 사용법 동일
LPAD, RPAD(X, Y, Z) : 지정된 길이(Y)에서 X를 채우고 남은 공간을 Y로 채운다. (L은 우측에 X를 채운다, 남은 좌측은 Z를 채운다. R은 반대.) EX) SELECT LPAD ('Hello SQL' , 10, '*') FROM DUAL; → *Hello SQL 반환
LTRIM, RTRIM(X, Y) : 좌, 우측 공백 문자를 제거 EX) SELECT '<' || LTRIM(' AAA ') || '>' FROM DUAL; → 'AAA ' 출력
TRIM : 위의 LTRIM 과 RTRIM 결합 EX) SELECT '<' || TRIM(' AAA ') || '>' FROM DUAL; → 'AAA'출력
★ SUBSTR(X, Y, Z) : 문자열의 일부분 X를 선택, Y위치부터 길이 Z만큼의 문자 리턴 EX) SELECT SUBSTR('Hello SQL', 7, 3) FROM DUAL; → SQL 반환
REPLACE(X, Y, Z) : X에 포함된 Y문자를 Z값으로 치환 EX)SELECT REPLACE('Hello SQL', 'Hello', 'Goodbye') FROM DUAL; → Goodbye SQL 출력
INSTR(X, Y, M, N) : X문자열에서 Y문자가 처음 나타나는 위치 숫자 출력, M은 시작위치, N은 N번째 EX)SELECT INSTR('Hello SQL Goodbye SQL', 'SQL', 1, 2) FROM DUAL; → 19 반환
LENGTH, LENGTHB(X) : LENGTH는 문자열 X의 길이값 반환, LENGTHB는 문자열 X의 BYTE 값 반환 EX) SELECT LENGTH('Hello SQL') FROM DUAL; → 9 반환 , SELECT LENGTHB('안녕 SQL') FROM DUAL; →10 반환 , 주의) 한글은 하나에 3BYTE

숫자함수

ABS(N) : N절대값으로 변환
SIGN(N) : N을 양수(1), 음수(-1), 0(0) 으로 변환
POWER(N,M) : N의 M 제곱
SQRT(N) : N의 제곱근
GREATEST, LEAST(N,M·····) : 열거한 항목 중 가장 큰 또는 가장 작은 값 항목 반환, N의 데이터 값에 따라 M항목도 판단. EX) SELECT GREATEST(1, 2, 3) FROM DUAL; → 3반환
ROUND(N,1) : 지정된 자릿수에서 반올림 EX) SELECT ROUND(3.14, 1) FROM DUAL; → 3.1 반환
TRUNC(N,1) : ROUND와 동일 하지만 반올림이 아닌 절삭 EX) SELECT TRUNC(3.16, 1) FROM DUAL; → 3.1 반환
MOD(C, N) : C를 N으로 나눈 나머지
FLOOR/ CEIL(N) : N과 같거나 작은 수 중 가장 큰 정수/ N과 같거나 큰 수 중에 가장 작은 정수 소수점 이하의 값이 존재하면 무조건 올림하는 함수 EX) SELECT FLOOR(3.0123456) FROM DUAL; → 3 반환 , SELECT CEIL(3.0123456) FROM DUAL; → 4 반환

날짜함수

SYSDATE / SYSTIMESTAMP : 시스템에서 제공하는 현재 날짜와 시간 값/ 연, 월, 일, 시, 분, 초, 1000분의 1초
ADD_MONTH(DATE, N) : DATE에 월을 더한 날짜
NEXT_DAY(DATE, CHAR), LAST_DAY(DATE) : 해당 날짜 이후의 가장 빠른 요일의 날짜, 월의 마지막 날짜
ROUND(DATE, FORMAT('YY', 'MM'...)) : 형식 기준으로 날자를 반올림한 날짜
TRUNC(DATE, FORMAT('YY', 'MM'...)) : 형식 기준으로 날짜를 버림한 날짜
MONTH_BETWEEN(DATE1, DATE2) : 두 날짜 사이 달수 반환
★EXTRACT(FORMAT, FROM DATE) : 날짜에서 필요한 부분만 추출 EX)EXTRACT(MONTH, FROM SYSDATE) FROM DUAL;

형 변환(CONVERSION) 함수

CAST(X, AS TYPE) : 명시적으로 형 변환 EX) SELECT CAST('1995/01/01' AS DATE) FROM DUAL; → 1995/01/01
TO_CHAR/ TO_NUMBER/ TO_DATE : 숫자, 문자, 날짜를 지정한 형식의 문자열 반환, 숫자형식의 문자열을 숫자로 반환, 날짜형식의 문자열을 날짜로 반환 → SELECT TO_CHAR(CAST('2008-12-25' AS DATE) , 'YYYY.MM.DD HH24:MI') FROM DUAL;
TO_CHAR : 숫자, 문자, 날짜를 지정한 형식의 문자열 반환(9: 유효한 숫자인 경우 출력, 0: 무효한 숫자인 경우 '0' 출력, L: 지역화폐기호, MI: 음수인 경우 우측에 마이너스 표시, PR: 음수인 경우 <>로 묶는다, ,(comma) .(dot): 해당 위치에 comma, dot표시) EX) SELECT TO_CHAR(1234.6 , 'L9,999,00') FROM DUAL; → ￦1234.60
TO_NUMBER : 숫자형식의 문자열을 숫자로 반환 EX) SELECT TO_NUMBER('￦1,200', 'L9,999') + 1 FROM DUAL; → 1201출력
TO_DATE : 날짜형식의 문자열을 DATE형으로 변환 EX) SELECT TO_DATE('2009-03-05', 'YYYY-MM-DD') + 3 FROM DUAL; → 2009-03-08

NULL 처리함수 : NULL은 아무 값도 없는 걸 의미한다.

(요즘 시대에는 집 전화번호가 NULL값을 허용하는 대표적인 예이다.)

특정 값을 NULL처리하는 것은 이전의 UPDATE문을 활용할 수 있다.
IS NULL, IS NOT NULL : NULL값인지 아닌지 비교 EX) WHERE절에 비교데이터 뒤 IS(NOT)NULL을 사용한다.
NVL(N,M) : N이 NULL이 아니면 N 반환, NULL이면 M 반환 EX) SELECT에 NVL을 사용한다.
NVL2(N,R,M) : N이 NULL이 아니면 R 반환, NULL이면 M 반환 EX) SELECT에 NVL2을 사용한다.
NULLIF(N,M) : N과M을 비교하여 같으면 NULL 다르면 N 반환
COALESCE(A,B,C,D,E ...) : 파라미터 중 NULL이 아닌 첫 번째 파라미터 반환

표현식

★DECODE : IF문과 비슷한 역활 EX) SELECT DECODE(9, 10, 'A', 9, 'B', 8, 'C', 'D') FROM DUAL;
CASE WHEN : ① SIMPLE CASE EXPRESSION : EX) SELECT CASE '나' WHEN '철호' THEN '아니다' WHEN '나' THEN '맞다' ELSE '모르겠다' END RESULT FROM DUAL; ② SEARCHED CASE EXPRESSION : SELECT CASE WHEN '나'='나' THEN '맞다' ELSE '아니다' END RESULT FROM DUAL;

기본 TCL : 트랜잭션이란 작업의 기본단위, 최소단위

트랜잭션의 특성
1. 원자성 : 트랜잭션에서 정의된 연산의 경우 전체 실행 또는 전체 실행 안됨의 경우만 가능하다.
2. 일관성 : 실행 전 데이터베이스에 문제가 없다면, 실행 후에도 문제가 없어야 한다.
3. 고립성 : 실행 중 타 트랜잭션에 영향으로 결과에 문제가 발생해서는 안 된다.
4. 지속성 : 성공적으로 수행되면 해당 트랜잭션으로 갱신한 데이터베이스의 내용은 지속적으로 저장된다.

트랜잭션 제어문

COMMIT : 변경사항을 확정시키고 트랜잭션을 종료
ROLLBACK : 변경사항을 취소시키고 트랜잭션을 종료
SAVEPOINT : 현재의 트랜잭션 중에서 ROLLBACK 시킬 위치를 지정

※ 주의사항 : COMMIT을 하지 않았더라도 DDL을 잘못쓰면 자동으로 COMMIT된다. EX) CREATE문을 사용하여 정상적으로 작동하지 않고 오류를 띄워도 자동 COMMIT.

공부를 하며 업데이트 할 예정입니다.