SU Library

SQL 쿼리문 기본 정리 본문

SQL

SQL 쿼리문 기본 정리

S U 2023. 5. 25. 17:00

SQL 쿼리문 기본 정리

컬럼의 종류

데이터 유형 설명
Varchar Character varying의 약자로 가변 길이의 문자열 정보임 sql의 경우 최대 8000바이트 저장 가능
NUMERIC 정수, 실수 등 숫자 정볼르 가지고 있음 표기법의 예로 정수부분이 6자리 소수점이 2자리인것을 표현하면 NUMERIC(8,2)로 표기하면 된다.
DATE 날짜와 시간정보 관리함

제약 조건의 종류

  • PRIMARY KEY(기본키) : 기본키 중복안되고, NULL입력 안됩니다.
  • UNIQUE KEY(고유키) : NULL있어도 됨, 다만 중복만 안됩니다.
  • NOT NULL : 무조건 비우면 안됨 'DEFAULT'설정이 되어있으면, 새로운 로 추가시 아무값입력안하면 디폴트 세팅이 들어갑니다.
  • CHECK : TRUE FALSE로 평가 할수 있는 논리식 지정합니다.
  • FOREIGN KEY(외래키) : 다른 테이블과의 관계를 정의하기위해 기본키를 다른 테이블의 왜리키로 복사하는 경우의 복사된 기본키를 지칭함.

DML

  • INSERT (syntax) - INSERT INTO 테이블명 (컬럼리스트) VALUES(값1,값2,....) : 테이블에 넣을 컬럼을 정의해주고 그 컬럼을 채울 값들을 입력해준후 새로운 행을 추가함.
  • UPDATE (syntax)- UPDATE TABLE tablename SET 컬럼명 = 새로운 값 : 테이블에 컬럼 명 제거
  • DELETE (syntax)- DELETE [FROM] tablename : 테이블의 정보가 필요 없게 되었을 경우 데이터 삭제를 수행함 where 절로 어디 조질지 위치 지정가능. 암것도없이 그냥 저대로 쓰면 테이블 전체 데이터가 삭제댐
  • SELECT (syntax)- SELECT [ALL/DISTINCT] 보고싶은 컬럼명1,2,3... FROM 테이블명; : 테이블에서 보고싶은 컬럼명을 보여줌 ALL-디폴트로 중복데이터도 다보여줌, DISTINCT - 중복데이터는 제거해서 보여줌 *(와일드카드) 사용시 전체 보여줌, 좌측정렬: 문자 및 날짜 데이터, 우측정렬: 숫자 데이터
  • ALIAS (syntax) 컬럼명 as 별명, 컬럼명2 as 별명2,... : 조회된 데이터에 별명을 부과해서 컬럼 레이블을 변경 가능함. ALIAS는 컬럼명 바로 뒤에 오고, 특수문자 공백 대소문자구분이 필요할 경우 사용됨. 별명에 공백이 들어갈경우 "",'',[]를 써야댐 "선수 이름" 등 이런식으로 지정해줘야합니다.

산술 연산자

+,-,/,* 우리가 아는 그 연산자 예시: select 몸무게+ 키 as "몸무게 더하기 키" from 선수들로 하면 각 로우의 몸무게 + 키 값을 더한것을 앨리어스로 해서 계산한 값을 보여줌

합성 연산자

문자와 문자를 연결하는 합성 연산자를 사용하면 별도의 프로그램 도움 없이도 sql 문장가지고만 유용한 리포트를 출력 가능하다.

  • 문자와 문자를 연결하는 경우 2개의 수직 바(||)에 의해 이루어진다.(오라클)
  • 문자와 문자를 연결하는 경우 '+'에 의해 이루어진다.(sql server)
  • 두 벤더 모두 공통적으로 CONCAT(str1,str2)를 이용해서 연결 가능하다.
  • 컬럼과 문자 또는 다른 컬럼과 연결 시킬수도 있음.
  • 문자 표현식의 결과에 의해 새로운 칼럼을 생성한다.

Where절

where : 보통 select * from 테이블 where 조건식 으로 select 문에 끼어서 조건을 부여해서 찿고자하는 조건을 부과해서 사용자가 보여주고 싶은 정보만 선택적으로 보여주게 한다.

조건식의 내용

  • 칼럼(Column)명
  • 비교 연산자
  • 문자, 숫자, 표현식
  • 비교 칼럼명(JOIN 사용시)
구분 연산자 연산자의 의미
비교 연산자 = 같다
>,>= 크다,크거나 같다
<,<= 작다,작거나 같다
SQL 연산자 BETWEEN a AND b a와 b의 값 사이에 있으면 된다. (a,b 포함)
IN(list) 리스트에 있는 값 중에서 어느 하나라도 일치하면 된다.
Like '문자열' 문자열과 형태가 일치하면 된다. (%: 문자 여러개,_ :문자 하나사용)
IS NULL NULL 값인 경우
부정 비교 연산자 <>,!=,^= 같지 않다
NOT 칼럼명 = ~와 같지 않다.
NOT 칼럼명 > ~ 보다 크지 않다.
부정 SQL 연산자 NOT BETWEEN a AND b a와 b의 값 사이에 있으면 안된다. (a,b 포함)
NOT IN(list) 리스트에 있는 값 중에서 어느 하나라도 일치하면 안된다.
IS NOT NULL NULL 값이 아닌 경우

연산자의 우선 순위

  • 1 : 괄호 ()
  • 2 : NOT 연산자
  • 3 : 비교연산자, SQL 비교 연산자
  • 4 : AND
  • 5 : OR

ROWNUM,TOP

ROWNUM : select 출력시 원하는 만큼만 가져옴

한건만 가져오고 싶을 때

  • SELECT * from EMP WHERE ROWNUM = 1
  • SELECT * from EMP WHERE ROWNUM < 2
  • SELECT * from EMP WHERE ROWNUM <= 1

TOP : select 출력되는 행의 수를 제한함 (syntax) : TOP (Expression) [PERCENT][WITH TIES]

  • Expression : 반환할 행의 수를 지정하는 숫자임
  • PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환 됨을 알 수 있음
  • WITH TIES : ORDER BY절이 지정된 경우에만 사용 가능하며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력 되도록 지정 할수 있음.
  • 한건만 가져오고 싶을 때 : SELECT TOP(1) * from EMP
  • N건만 가져오고 싶을 때 : SELECT TOP(N) * from EMP

SQL 연산자 예시

  • IN (list) 연산자 : SELECT * from EMP where (JOB,DEPTNO) IN (('MANAGER',20),('CLERK',30)),SELECT * from EMP where JOB IN (('MANAGER','CLERK') AND DEPTNO IN (20,30)) -- 사원 테이블에서 JOB이 CLERK이면서 30번 부서에 속하거나 JOB이 MANAGER이면서 20번에 속하는 사람 찿아서 출력
  • BETWEEN a AND b : SELECT * from EMP WHERE HEIGHT BETWEEN 170 AND 180; : 키가 170-180 사이에 있는 직원 출력
  • NULL : SELECT * from EMP WHERE HEIGHT IS NULL (* HEIGHT= NULL 하면 대가리깨져도 안뜸) : 키가 NULL인 직원 출력
  • <> : SELECT * from EMP WHERE POSITION <> 'CIA'; : POSITIOn이 CIA가 아닌 직원 출력

단일행 함수

특징

  • SELECT,Where, order by절에 사용 가능하다.
  • 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.
  • 여러 인자를 입력해도 단 하나의 결과만 나온다.
  • 함수의 인자로 상수,변수 표현식이 사용 가능하고, 하나의 인수를 가지는 경우도 있지만, 여러개의 인수를 가질 수도 있다.
  • 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 충첩이 가능하다.

syntax : 함수명 (칼럼이나 표현식, [arg1,arg2, ...])

  • 문자형 함수 : 문자를 입력하면 문자나 숫자 값을 반환한다. ex(LOWER, UPPER, SUBSTR/SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM)
  • 숫자형 함수 : 숫자를 입력하면 문자나 숫자 값을 반환한다. ex(ABS, MOD, ROUND, TRUNC,SIGN, CHR/CHAR, CEIL, FLOOR, POWER 등 )
  • 날자형 함수 : DATA 타입의 값을 연산한다. ex(SYSDATE/GETDATE, EXTRACT/DATEPART, TO_NUMBER(TO_CHAR(d,'YYYY'|'MM'|'DD'))/ YEAR|MONTH|DAY)
  • 변환형 함수 : 문자, 숫자, 날짜형 값의 데이터를 변환한다. ex(TO_NUMBER, TO_CHAR, TO_DATE/CAST, CONVERY 등 )
  • NULL 관련 함수 : NULL을 처리하기 위한 함수 ex(NVL/ISNULL, NULLIF, COALESCE )

DDL - 데이터 데피니션(정의) 언어

  • ADD (syntax)- ALTER TABLE tablename ADD 컬럼명 데이터 유형 : 테이블에 새로운 컬럼 명 추가
  • DROP (syntax)- ALTER TABLE tablename DROP 컬럼명 : 테이블에 컬럼 명 제거
  • MODIFY (syntax)- ALTER TABLE tablename MODIFY (컬럼명 데이터유형 [DEFAULT 식][NOT NULL], 컬럼명2 데이터유형...); : 테이블에 컬럼명에 대해서 제약조건 등에 대한 변경
  • RENAME (syntax)- ALTER TABLE tablename RENAME 컬럼명 TO 새로운 컬럼명; : 테이블에 컬럼명에 대해서 새로운 컬럼명으로 변경
  • DROP CONSTRAINT (syntax)- ALTER TABLE tablename DROP CONSTRAINT 제약조건명; : 테이블에 제약조건명에 대해서 제거
  • ADD CONSTRAINT (syntax)- ALTER TABLE tablename ADD CONSTRAINT 제약조건명 제약조건(컬럼명); : 테이블에 입력한 제약조건을 가진 제약조건명에 대해서 입력한 컬럼에 대해 추가함
  • DROP TABLE(syntax)- DROP 테이블명 : 해당 테이블 제거
  • TRUNCATE TABLE (syntax)- ALTER TABLE tablename DROP 컬럼명 : 테이블에 있는 모든 행들을 제거 테이블과 컬럼들은 남겨둠

DML - 데이터 매니풀레이션 언어 과의 차이점 

DML은 생성된 스키마 내에서 데이터를 조작하는 언어에 비해 DDL은 생성된 데이터 베이스 스키마를 정의하는 일련의 언어임을 명심하자.

DCL - 데이터 컨트롤 언어

  • GRANT (syntax) -   GRANT 사용자등급[resource,delete on,select,insert 등 db에 어떠한 조작을 하거나 조회를 하는 명령어 ] TO [IDENFIED BY 암호 -  (public: 모든 사용자,혹은 사용자 이름: 김하늘 등등)] : 다른 사용자에게 어떤 권한을 부여하는 명령어임 
  • REVOKE(syntax) - REVOKE 사용자등급[resource,delete on,select,insert 등 db에 어떠한 조작을 하거나 조회를 하는 명령어 ] FROM 사용자ID리스트  : 사용자 ID 리스트에 있는 사용자들에 대해 사용자등급(권한)을 다시 회수 하는 명령어임

TCL : 트랜잭션 컨트롤 언어

트랜잭션의 정의 : 트랜잭션은 데이터베이스의 논리적 연산 단위로 밀접히 관련되어 분리될 수 없는 한개 이상의 데이터베이스 조작을 의미함. 즉 하나의 트랜잭션에는 하나이상의 sql문이 포함된다.

트랜잭션은 데이터베이스의 논리적 연산 단위로 밀접히 관련되어 분리될 수 없는 한개 이상의 데이터베이스 조작을 의미함. 즉 하나의 트랜잭션에는 하나이상의 sql문이 포함된다.

트랜잭션의 특성

  • 원자성(atomicity) : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상태로 남아있어야 한다.
  • 일관성(consistency) : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않으면, 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
  • 고립성(isolation) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
  • 지속성(durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장된다.
  • COMMIT : 올바르게 반영된 데이터를 데이터베이스에 반영 시킴.
  • ROLLBACK : 트랜잭션 시작 이전의 상태로 돌림
  • SAVEPOINT : 저장점 기능을 함.

COMMIT

입력한 자료나 수정한 자료에 대해서 혹은 삭제한 것에 대해서 전혀 문제가 없다고 판단할 시에, 해당 명령어를 사용하여 트랜잭션을 완료할 수 있음.

INSERT,UPDATE, DELETE 등 DML 명령어 후에 변경 작업이 완료 되었음을 디비에 알려주기 위해 사용한다.

특징

  • 단지 메모리 버퍼에만 영향을 받았기 때문에 데이터 변경 이전의 상태로 복구 가능함.
  • 현재 사용자는 select 문장으로 결과 확인이 가능하다.
  • 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
  • 변경 된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경 할 수 없다.

SQL 서버에서 트린잭션은 다음의 3가지 방식으로 이루어짐

  • AUTO COMMIT : sql 서버의 기본 방식이며, DML, DDL을 수행할 때마다, DBMS가 트랜잭션을 컨트롤하는 방식이다. 명령어가 성공적으로 수행되면, 자동으로 COMMIT을 수행하고 오류가 발생하면 ROLLBACK을 수행한다.
  • 암시적 트랜잭션 : 오라클에서 사용하는 방법이며, DBMS로 처리하고 트랜잭션 끝에는 사용자가 COMMIT, ROLLBACK을 직접 타이핑해서 처리하는 방법이다.
  • 명시적 트랜잭션 : 트랜잭션과 끝을 모두 사용자가 지정하는 방식임. BEGIN TRANSACTION으로 트랜잭션을 시작하고 COMMIT TRANSACTION 혹은 ROLLBACK TRANSACTION으로 트랜잭션을 종료한다. ROLLBACK만다면 최초의 BEGIN시점까지 날아감.

ROLLBACK

데이터 변경사항이 취소되어, 변경전으로 돌아감

  • 데이터에 대한 변경 사항은 취소된다.
  • 이전 데이터가 재 저장된다.
  • 관련된 행에 대해 잠금이 풀리고, 다른 사용자들이 행을 조작 할 수 있게 된다.

COMMIT과 ROLLBACK으로 얻을수 있는 효과

  • 데이터 무결성 보장
  • 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
  • 논리적으로 연관된 작업을 그룹핑하여 처리 가능

SAVEPOINT

저장점을 정의하여 롤백할때 해당 시점으로 돌아가는게 가능하도록 함

SAVEPOINT (syntax) - SAVEPINT svpt1 : 현재 시점을 svpt1으로 저장함

이렇게 저장된 세이브 포인트는 롤백등으로 ROLLBACK TO svpt1으로 사용되어 질 수 있다.

sql server의 경우 SAVE TRANSACTION svtp1,ROLLBACK TRANSACTION svpt1 으로 가능

  • 데이터에 대한 변경 사항은 취소된다.
  • 이전 데이터가 재 저장된다.
  • 관련된 행에 대해 잠금이 풀리고, 다른 사용자들이 행을 조작 할 수 있게 된다.
Comments