SQLD SQL활용 3, 관리구문
6. TOP-N 쿼리
탑-N번째 데이터를 추출하는 쿼리들이다. 여러 방식으로 추출할 수 있다.
6.1 ROWNUM
오라클의 ROWNUM은 수도 컬럼이다. 사전적으로는 가짜라는 뜻을 가지고 있으며 컴퓨터 공학에서는 수도코드라는 용어로도 많이 쓰인다.
실제로는 존재하지 않는 컬럼이다.
ROWNUM은 마지막에 매기기때문에 WHERE ROWNUM = ?같이 사용하며안된다.
6.2 윈도우의 순위함수
ROW_NUMBER() / RANK() OVER()구문을 사용하여 순위를 구할 수도 있다.
8. 계층쿼리
테이블에 계층 구조를 이루는 컬럼이 존재할 경우 계층 쿼리를 이용해서 데이터를 출력할 수 있다.
이전에 셀프 조인으로 계층을 만들엇던 것으로 계층쿼리를 사용할 수 있다.
LEVEL - 현재의 DEPT를 반환한다 루트 노드는 1이된다.
SYS_CONNECT_BY_PATH(컬럼, 구분자) - 루트노드부터 현재 노드까지의 경로를 출력해주는 함수이다.
START WITH - 경로가 시작되는 루트 노들르 생성해주는 절이다.
CONNECT BY - 루트로부터 자식 노드를 생성해주는 절이다. 조건에 만족하는 데이터가 없을때까지 노드를 생성한다.
PRIOR - 바로 앞에 있는 부모 노드의 값을 반환한다.
관리구문
1. DML
DML은 Data Manipuliation Language란 의미로 DDL에서 정의한 대로 데이터를 입력하고 입력된 데이터를 수정 삭제 조회하는 명령어이다.
1.1 INSERT
테이블에 데이터를 입력하는 명령어이다.
INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES (데이터1, 데이터2, ...)
INSERT INTO 테이블명 VALUES(전체컬럼에 입력될 데이터 리스트)
첫번째 sql의 경우 명시되지 않은 커럶에는 NULL값이 입력되는데 PK나 NOT NULL제약조건이 걸림컬럼에는 NULL값이 입력될 수 없으니 주의해야한다.
두번째 SQL의 경우 전체 컬럼에 대한 데이터가 테이블의 컬럼 순서대로 빠짐없이 나열되어야하는데 순서가 뒤바뀌어 데이터 유형이 맞지 않거나 누락된 데이터가 있어 전체컬럼갯수와 맞지않을 경우 데이터베이스는 에러를 발생시킨다.
1.2 UPDATE
이미 저장된 데이터를 수정하고 싶을때 사용하는 명령어이다. 수정하고 싶은 컬럼이 많다면 SET절에 ,로 이어서 명시할 수 있다.
WHERE절이 없으면 모든 ROW가 변경되니 주의해야한다.
UPDATE 테이블명 SET 컬럼명 = 새로운데이터 (WHERE 수정할 데이터에 대한 조건)
1.3 DELETE
이미 저장된 데이터를 삭제하고 싶을때 사용하는 명령어이다.
WHERE절이 없으면 모든 ROW가 변경되니 주의해야한다.
DELETE FROM 테이블명 (WHRER 조건)
만약 WHERE절없이 정말 테이블 전체 데이터를 삭제하고자하는 경우 TRUNCATE명령어를 사용할 수도 있는데 삭제하고 자 하는 마음이 확고하다면 이것을 쓰는게 시스테 부하 측면에서 유리하다.
대신 TRNCATE는 로그를 쌓지않아 롤백하는 것이 불가능하며 DELETE는 COMMIT 전에 ROLLBACK이 가능하다.
1.4 MERGE
테이블에 새로운 데이터를 입력하거나 이미 저자오디어 있는 데이터에 대한 변경 작업을 한번에 할 수 있도록 해주는 명령어이다.
MERGE
INTO 타겟 테이블명
USING 비교테이블명
ON 조건
WHEN MATCHED THEN
UPDATE
SET 컬럼명 = 새로운데이터 [,컬럼명 = 새로운데이터]
WHEN NOT MATCED THEN
INSERT [(컬럼명,컬럼명2...)]
VALUES (데이터1, 데이터2 ...)
2. TCL
TCL은 트랜잭션을 제어하는 명령어로 COMMIT ROLLBACK SAVEPOINT가 있다.(나는 이것들을 DCL로 배웟는데 더 세분해서 구분하는 것 같다.)
TCL은 Transaction Control Language라는 의미로 트랜잭션을 제어하는 명령어이다.
트랜잭션은 쪼개질 수 없는 업무처리의 단위로 한세트인 논리적인 업무단위이다.
2.1 트랜잭션의 특징
1.원자성 - 트랜잭션으로 묶인 일련의 동작들은 모두 성공하거나 모두 실패해야한다.
2.일관성 - 트랜잭션이 완료된 후에도 데이터베이스가 가진 데이터에 일관성이 있어야한다.
3.고립성 - 하나의 트랜잭션은 고립되어 수행되어야한다. 다른 처리가 이 트랜잭션이 건드는 것에 건들수없고 이 트랜잭션이 끝날때까지 대기해야한다.
4.지속성 - 트랜잭션이 성공적으로 수행되엇을 경우 트랜잭션이 변경한 데이터가 영구적으로 저장되어야한다.
2.2 COMMIT
DML이후 변경된 데이터를 확정 반영하는 명령어이다.
COMMIT을 실행하지 않으면 메모리까지만 반영이 되는데 메모리는 휘발성이기 때문에 언제든 사라질 수 있고 다른 사용자는 변경된 값을 조회할 수 없다. COMMIT을 실행해야 최종적으로 데이터 팡리에 기록이 되고 비로소 트랜잭션이 완료되는 것이다.
UPDATE한뒤 오랜시간 동안 COMMIT이나 ROLLBACK을 하지 않으면 LOCK에 걸려 다른 사용자가 변경할 수 없는 상황이 발생할 수 있으니 주의해야한다.
2.3 ROLLBACK
DML이후 변경된 내용을 취소하는 것이다.
2.4 SAVEPOINT
ROLLBACK을 수행할때 전체 작업ㅇ르 되돌리지 않고 일부만 되돌릴수 있게 하는 기능을 가진 명령어이다. ROLLBACK뒤에 SAVEPOINT를 지정해주면 그 지점까지만 데이터가 복구된다.
3. DDL
DDL은 Data Definition Lanuage란 의미로 데이터 정의어 즉 데이터를 정의하는 sql이다.
CREATE쿠러ㅣ를 수행할때 테이블을 생성하면서 그 안에 담게 될 데이터에 대한 데이터 유형을 정해주도록 되어 있는데 이런식으로 SQL에 명시해줌으로서 데이터를 정의해주는 것이다.
데이터 유형은 크게 문자 숫자 날짜 타입으로 나뉠 수 있는데 이것은 데이터베이스가 데이터를 어떻게 저장할지를 정하는 기준이라 우리가 생각하는 기준과는 조금 다르다.
예를들어 20231029를 데이터베이스에 저장한다고 햇을때 이것은 데이터베이스 입장에서 문자가 될수도 있고 숫자가 될수도잇고 날짜가 될수도 있다.
만약 선언해놓은 유형이 아닌 다른 데이터 유형을 저장하려하면 에러를 발생시키기 때문에 주의가 필요하다.
유형을 정의하며 크기도 정의하는데 더 큰데이터를 저장하려할때도 에러가 발생하기 때문에 주의해야한다.
문자 - CHAR / VARCHAR / CLOB
숫자 - NUMBER
날짜 - DATE
여기서 CHAR타입은 미리 크기를 정해두기 때문에 같은 문자라도 크기에 따라 다른 문자열이 될 수 있으니 주의하자
3.1 CREATE
테이블을 생성하기 위한 명령어이다.
CREATE TABLE 테이블명 (
컬럼명1 데이터타입 제약조건
...
);
테이블 생성시 반드시 지켜야할 규칙은 다음과 같다.
1.테이블명은 고유해야한다.
2.한 테이블내에서 컬럼명은 고유해야한다.
3.컬럼명 뒤에 데이터 유형과 데이터 크기가 명시되어야한다.
4.컬럼에 대한 정의는 괄호안에 기술한다.
5.각 컬럼들은 , 로 구분된다.
6.테이블명과 컬럼명은 숫자로 시작될 수 없다.
7.마지막은 세미콜론으로 끝난다.
다음 규칙은 에러를 발생시키지는 않지만 지키지 않으면 매우 피곤해지는 항목들이다.
1.테이블은 각가의 정체성을 나타내는 일므을 가져야한다.
2.컬러명을 정의할때는 다른 테이블과 통일성이 있어야한다.
CREATE TABLE을 할때 제약조건도 함께 정의해줄 수 있는데 제약조건은 테이블에 저장될 데이터의 무결성 즉 데이터의 정확성과 일관성을 유지하고 데이터에 결손과 부정합이 없음을 보증하기 위해 해놓은 장치이다. 테이블 생성시 정의해야할 필수요소는 아니지만 데이터가 많이 쌓인 후 정의하려고 하면 골치 아파지므로 초기에 정의해주는 것이 바람직하다.
1.PRIMARY KEY(기본키) : 테이블에 저장된 각각의 ROW에 대한 고유성을 보장한다. 한 테이블에 하나씩만 정의할 수 있으며 NULL값이 입력될수없고 자동으로 UNIQUE인덱스로 생성된다.
2.UNIQUE KEY(고유키) : PK와 유사하게 테이블에 저장된 각각의 ROW에 대한 고유성을 보장하기 위한 제약 조건이지만 NULL값이 허용된다는 차이점이 있다.
3.NOT NULL : 해당 컬럼에 NULL 값이 입력되는 것을 허용하지 않는 제약조건이다.
4.CHECK 컬럼에 저장될 수 있는 값의 범위를 제한한다.
5.FOREIGN KEY(외래키) : 하나의 테이블이 다른테입르을 참조할때 정의한다.
완전히 새로운 테이블을 생성하는 것이 아니고 기존의 테이블을 복사하고자하면
CRATE 테이블명 AS SELECT * FROM 복사할 테이블명; 을해주면된다.
3.2 ALTER
테이블의 구조가 변경되지 않는 것이 일반적이지만 변경될 수도 있다.
1. ADD COLUMN
새로운 컬럼을 추가할때 쓰는 명령어이다. 추가된 컬럼의 위치는 늘 맨끝이 되며 별도의 위치를 지정해줄 수 없다.
ALTER TABLE 테이블명 ADD 컬럼명 데이터 유형
2. DROP COLUMN
기존에 있던 컬럼이 필요없어졋을때 삭제하는 명령어이다. 한번 삭제된 컬럼은 복구할 수 없으므로 주의해야한다.
ALTER TABLE 테이블명 DROP COLUMN 컬럼명;
3. MODIFY COLUMN
기존에 있던 컬럼을 변경하고 싶을때 쓰는 명령어이다.
데이터 유형 DEFAULT값 NOT NULL제약 조건에 대한 변경이 가능하다.
단 컬럼에 저장된 모든 데이터 크기가 줄이고자 하는 커럼의 크기보다 작을 경우에만 줄일 수 있고 컬럼에 저장된 데이터가 없는 경우에만 데이터 유형을 변경할 수 있다. DEFAULT값 변경시 이후 저장되는 데이터에만 저장되며 NULL값이 저장되어 있지 않능 ㄴ컬럼에만 NOT NULL제약 조건 추가가 가능하다.
ALTER TABLE 테이블명 MODIFY (컬럼명1, 데이터유형 [DEFAULT값] [NOT NULL])
4. RENAME COLUMN
기존에 있던 컬럼의 이름을 변경하고 싶을때 쓰는 명령어이다.
AKTER TABLE 테이블명 RENAME COLUMN 변경할 컬럼명 TO 변경할 이름;
5. ADD CONSTRAINT
제약조건을 추가하고 싶을때 쓰는 명령어이다.
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (컬럼명);
6. DROP TABLE
테이블을 삭제할때 쓰는 명령어이다. 참조하고 있는 다른테이블이 잇다면 삭제되지않는다.
CASCADE CONSTRAINT를 하면 참조 제약조건까지 삭제 할 수 있다.
DROP TALBLE 테이블명 [CASCADE CONSTRAINT]
7. TRUNCATE TABLE
테이블에 저장되어 있는 데이터를 모두 제거하는 명령어이다.
TRUNCATE TABLE 테이블명;
정리
| DML | INSERT UPDATE DELETE MERGE |
| TCL | COMMIT ROLLBACK SAVEPOINT |
| DDL | CREATE ALTER DROP TRUNCATE |
| DCL | CRATE USER DROP USER GRANK REVOKE |