<목차> |
※ 자료출처: 데이터 산업 진흥원(K-DATA)
SQL 문장의 종류
| 명령어의 종류 | 명령어 | 설명 |
| 데이터 조작어(DML: Data Manipulation Language) |
SELECT | 데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어 (RETRIEVE) |
| INSERT UPDATE DELETE |
- 데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어 - 데이터를 테이블에 새로운 행에 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어 |
|
| 데이터 정의어(DDL: Data Definition Language) |
CREATE ALTER DROP RENAME |
- 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어 - 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어 |
| 데이터 제어어(DCL: Data Control Language) |
GRANT REVOKE |
데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어 |
| 트랜잭션 제어어(TCL: Transactuon Control Language) |
COMMIT ROLLBACK |
논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어 |
2. DDL (Data Definition Language)
A. 데이터 유형
■ 자주 사용하는 데이터 유형
| 데이터 유형 | 설명 |
| CHARACTER(s) | - 고정 길이 문자열 정보 (Oracle, SQL Server) - s는 기본 길이 1바이트, 최대 길이 Oracle 2,000 바이트, SQL Server 8,000 바이트 - s만큼 최대 길이를 갖고 고정 길이를 가지고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우에는 그 차이 길이만큼 공간으로 채워짐. |
| VARCHAR(s) | - CHARACTER VARYING의 약자 - 가변 길이 문자열 정보(Oracle - VARCHAR2 / SQL Server - VARCHAR로 표현) - s는 최소 길이 1바이트, 최대 길이 Oracle 4,000 바이트, SQL Server 8,000 바이트 - s만큼의 최대 길이를 갖지만 가변 길이로 조정이 되기 때문에 할당된 변수값의 바이트만 적용 (Limit 개념) |
| NUMERIC | - 정수, 실수 등 숫자 정보(Oracle - NUMBER / SQL Server - 10 가지 이상의 숫자 타입) - Oracle : 처음에 전체 자리 수를 지정하고, 그 다음 소수 부분의 자리 수 지정 (예. 정수 부분이 6자리이고 소수점 부분이 2자리인 경우 - 'NUMBER(8,2)') |
| DATETIME | - 날짜와 시각 정보 (Oracle - DATE / SQL Server - DATETIME) - Oracle은 1초 단위, SQL Server 는 3.33ms(millisecond) 단위 관리 |
※ 문자열 유형의 경우, CHAR 유형과 VARCHAR 유형 중 중요한 것은 저장 영역과 문자열의 비교 방법.
VARCHAR: 가변 길이. 필요한 영역은 실제 데이터 크기 뿐이므로, 길이가 다양한 칼럼과, 정의된 길이와 실제 데이터 길이에 차이가 있는 칼럼에 적합. (저장 측면에서도 CHAR 유형보다 작은 영역에 저장 가능)
※ 비교 방법의 차이.
CHAR: 문자열을 비교할 때 공백(BLACK)을 채워서 비교하는 방법 사용. (우선 짧은 쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 하고, 앞에서부터 한 문자씩 비교 ▶ 끝의 공백만 다른 문자열은 같다고 판단)
VARCHAR: 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급. ( ▶끝의 공백이 다르면 다른 문자로 판단)
■ VARCHAR vs CHAR 유형
주민등록번호나 사번처럼 자료들이 고정된 길이의 문자열을 가지지 않는다면 데이터 유형은 VARCHAR 유형을 적용하는 것이 바람직.
VARCHAR, NUMERIC 유형에서 정으한 길이나 자릿수의 의미는 해당 데이터 유형이 가질 수 있는 최대한의 한계값을 정의하는 것. (문자열에 대한 최대 길이와 NUBER 칼럼의 정밀도를 지정하는 것은 테이블 설계 시 반드시 고려해야 할 중요 요소)
B. CREATE TABLE
■ 테이블과 칼럼 정의
- 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본키 칼럼으로 지정.
- 기본키는 단일 칼럼이 아닌 여러 개의 칼럼으로도 생성 가능.
- 테이블과 테이블 간에 정의된 관계는 기본키(PK)와 외부키(FK)를 활용해서 설정.
■ CREATE TABLE
- 테이블을 생성하는 구문 형식
| CREATE TABLE 테이블이름 ( 칼럼명1 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식], 칼럼명3 DATATYPE [DEFAULT 형식] ); |
- 테이블 생성 시 주의할 점
|
* 한 테이블 안에서 칼럼 이름은 달라야 하지만, 다른 테이블의 칼럼 이름과는 같을 수 있음.
<잘못된 사례>
| 10_PLAYER | 반드시 문자로 시작되어야 함 |
| T-PLAYER | 특수 문자 '-' 비허용 |
[예제]
다음 조건의 형태로 선수 테이블을 생성
| 테이블명: PLAYER 테이블 설명: K-리그 선수들의 정보를 가지고 있는 테이블 칼럼명: PLAYER_ID (선수ID) 문자 고정 자릿수 7자리, PLAYER_NAME (선수명) 문자 가변 자릿수 20자리, TEAM_ID (팀ID) 문자 고정 자릿수 3자리, E_PLAYER_NAME (영문선수명) 문자 가변 자릿수 40자리, NICKNAME (선수별명) 문자 가변 자릿수 30자리, JOIN_YYYY( 입단년도) 문자 고정 자릿수 4자리, POSITION (포지션) 문자 가변 자릿수 10자리, BACK_NO (등번호) 숫자 2자리 NATION (국적) 문자 가변 자릿수 20자리, BIRTH_DAT (생년월일) 날짜, SOLAR (양/음) 문자 고정 자릿수 1자리, HEIGHT (신장) 숫자 3자리, WEIGHT (체중) 숫자 3자리, 제약조건: 기본키 (PK) - PLAYER_ID (제약조건명은 PLAYER_ID_PK) 값이 반드시 존재 (NOT NULL) - PLAYER_NAME, TEAM_ID |
※ 주의사항
- 테이블 생성 시 대/소문자 구분 필요 없음. (기본 대문자 옵션)
- DATETIME 데이터 유형에는 별도의 크기 미지정
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이 표시
- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마 생략
- 칼럼에 대한 제약조건이 있으면 CONSTRAINT 를 이용하여 추가 가능
※ 제약조건은 PLAYER_NAME, TEAM_ID 칼럼의 데이터 유형 뒤에 NOT NULL을 정의한 사례 (칼럼 LEVEL 정의) /
PLAYER_PK, PLAYER_FK 같은 테이블 생성 마지막에 모든 제약 조건을 기술 (테이블 LEVEL 정의) 두 가지 방식.
▶ 하나의 SQL 문장 내에서 두 가지 방식은 혼용해서 사용 가능하며, 같은 기능
■ 제약조건
- 제약조건(CONSTRAINT): 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터의 무결성을 유지하기 위한 데이터베이스의 보편적인 방법.
- 특정 칼럼에 설정하는 제약
- 테이블을 생성할 때 제약조건을 반드시 기술할 필요는 없음.
- 테이블 생성 후 ALTER TABLE을 이용해서 추가, 수정하는 경우 데이터가 이미 입력된 경우라면 처리 과정이 쉽지 않으므로 초기에 충분한 검토 필요.
- 제약조건의 종류
| 구분 | 설명 |
| PRIMARY KEY (PK, 기본키) |
- 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키 정의 - 하나의 테이블에 하나의 기본키 제약만 정의 - 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE 인덱스를 생성하며, 기본키를 구성하는 칼럼에는 NULL 입력 불가 - 기본키 제약 = 고유키 제약 + NOT NULL 제약 |
| UNIQUE KEY (고유키) |
- 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키 정의 - NULL은 고유키 제약의 대상이 아님. (NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반 되지 않음.) |
| NOT NULL | - NULL 값의 입력 금지. - DEFAULT 상태에서는 모든 칼럼에서 NULL을 허가하고 있지만, 이 제약을 지정함으로써 해당 칼럼은 입력 필수가 됨. - NOT NULL은 CHECK의 일부분 |
| CHECK | - 입력할 수 있는 값의 범위 등을 제한 - TRUE or FALSE로 평가할 수 있는 논리식 지정 |
| FOREIGN KEY (FK, 외래키) |
- 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른 테이블의 외래키로 복수하는 경우 외래키 생성 - 외래키 지정 시 참조 무결성 제약 옵션 선택 가능. |
※ 참초 무결성 제약 조건
|
- DEPARTMENT 릴레이션(R1)의 기본 키인 DEPTNO를 EMPLOYEE 릴레이션(R2)에서 DNO 라는 외래 키로 참조.
- DNO에서 가질 수 있는 값.
a. DNO는 R1의 기본 키 값과 같아야 함. (1, 2, 3, 4 중 한 값)
b. DNO가 R2의 기본 키가 아니기 때문에, NULL 값 가질 수 없음. (R2의 기본키는 EMPNO)
▶ DNO가 1, 2, 3, 4, NULL 중 한 값을 가지면 참조 무결성 제약조건을 만족
※ NULL의 의미
NULL은 공백이나 숫자0과는 전혀 다른 값이며, 조건에 맞는 데이터가 없을 때의 공집합과도 다름.
'아직 정의되지 않은 미지의 값' 또는 '현재 데이터를 입력하지 못하는 경우'
더 자세한 내용은
데이터 모델과 SQL - Null 속성의 이해
SQLD 1과목 데이터 모델과 SQL 정규화 관계와 조인의 이해 모델이 표현하는 트랜잭션의 이해 Null 속성의 이해 본질식별자 vs 인조식별자 4. Null 속성의 이해 A. Null 정의 - 값이 존재하지 않는 것으로
puppy-foot-it.tistory.com
※ DEFAULT의 의미
|
■ 생성된 테이블 구조 확인
- Oracle: "DESCRIBE 테이블명;" 또는 "DESC 테이블명;"
- SQL Server: "sp_help 'dbo.테이블명"
■ SELECT 문장을 통한 테이블 생성 사례
- SELECT 문장을 활용해서 테이블을 생성할 수 있는 방법 (CTAS: CREATE TABLE ~ AS ~ SELECT ~)
- 기존 테이블을 이용한 CTAS 방법을 이용하면 칼럼별로 데이터 유형을 다시 재정의 하지 않아도 됨.
- 다만, 사용 시 기존 테이블의 제약조건 중에 NOT NULL만 새로운 복제 테이블에 적용이 되고, 기본키, 고유키, 외래키, CHECK 등 다른 제약 조건은 없어짐.
- 제약 조건을 추가하기 위해서는 ALTER TABLE 기능 사용 필요.
- SQL Server 에서는 SELECT ~ INTO ~를 활용하여 복사 가능. (칼럼 속성에 IDENTITY 를 사용했다면 IDENTITY 속성까지 같이 적용 됨.)
[예제]
선수(PLAYER) 테이블과 같은 내용으로 TEAM_TEMP 라는 복사 테이블 생성
| <ORACLE> CREATE TABLE TEAM_TEMP AS SELECT * FROM TEAM; 테이블이 생성되었다. |
| <SQL Server> SELECT * INTO TEAM_TEMP FROM TEAM; (1개 행이 영향을 받음) |
| [실행 결과] SQL Sever exec sp_help 'dbo.TEAM_TEMP' go |
C. ALTER TABLE
- 칼럼을 추가/삭제하거나 제약조건을 추가/삭제
■ ADD COLUMN (칼럼 추가)
- 새롭게 추가된 칼럼은 테이블의 마지막 칼럼이 되며 칼럼의 위치 지정 불가
| ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형; |
[예제]
PLAYER 테이블에 ADDRESS(가변문자 자릿수 80자리) 칼럼 추가
| <Oracle> ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80)); 테이블이 변경되었다. |
| <SQL Server> ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80)); 명령이 완료되었다. |
■ DROP COLUMN (칼럼 삭제)
- 테이블에서 필요 없는 칼럼 삭제 (데이터 유무 관계 없이 모두 삭제 가능)
- 한 번에 하나의 칼럼만 삭제 가능
- 칼럼 삭제 후 최소 하나 이상의 칼럼이 테이블에 존재해야 함.
- 한 번 삭제된 칼럼은 복구 불가.
| ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명; |
[예제]
앞에서 PLAYER 테이블에서 새롭게 추가한 ADDRESS 칼럼 삭제
| ALTER TABLE PLAYER DROP COLUMN ADDRESS; |
■ MODIFY COLUMN (칼럼 수정)
- 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대한 변경
| <Oracle> ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 ...); |
| <SQL Server> ALTER TABLE 테이블명 ALTER (칼럼명 1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 ...); |
- 칼럼 변경 시 고려 사항
|
[예제]
TEAM 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4) → VARCHAR2(8)으로 변경하고, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고, 모든 행의 ORIG_YYYY 칼럼에 NULL이 없으므로 제약조건을 NULL → NOT NULL로 변경
| <Oracle> ALTER TABLE TEAM_TEMP MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL); 테이블이 변경되었다. |
| <SQL Server> ALTER TABLE TEAM_TEMP ALTER COLUMN ORIG_YYYY VARCHAR(8) NOT NULL; 명령이 완료되었다. ALTER TABLE TEAM_TEMP ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY; 명령이 완료되었다. |
※ RENAME COLUMN (칼럼명 변경)
- 칼럼명을 변경해야 하는 경우에 사용
| ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명; |
※ RENAME COLUMN 으로 칼럼명이 변경되면, 해당 칼럼과 관계된 제약조건에 대해서도 자동으로 변경.
(Oracle 등 일부 DBMS에서만 지원)
※ SQL Server 에서는 sp_rename 저장 프로시저를 이용하여 칼럼 이름 변경
| sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN'; 주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있음. |
■ DROP CONSTRAINT
- 테이블 생성 시 부여했던 제약조건을 삭제하는 명령어
| ALTER TABLE 테이블명 DROP CONSTRATIN 제약조건명; |
[예제]
PLAYER 테이블의 외래키 제약조건을 삭제
| ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK; 테이블이 변경 되었다. |
■ ADD CONSTRAINT
- 테이블 생성 이후 제약조건을 추가할 경우 사용하는 명령어
| ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명); |
[예제]
PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건 추가
(제약조건명 - PLAYER_FK / PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID 참조하는 조건)
| ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID); 테이블이 변경되었다. |
[예제]
PLAYER 테이블이 참조하는 TEAM 테이블 제거
| <Oracle> DROP TABLE TEAM; ERROR: 외래 키에 의해 참조되는 고유/기본 키가 테이블에 있다. * 테이블은 삭제되지 않음. |
| <SQL Server> DROP TABLE TEAM; ERROR: 엔터티 'TEAM'은 FOREIGN KEY 제약 조건을 참조하므로 삭제할 수 없음. * 테이블은 삭제되지 않음. |
▶ 외부키(FK)를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사 방지하는 효과
D. RENAME TABLE
- RENAME 명령어를 사용하여 테이블의 이름 변경 가능
| RENAME 변경 전 테이블명 TO 변경 후 테이블명; |
- SQL Server 에서는 sp_rename 을 이용하여 테이블 이름 변경
| sp_rename 변경 전 테이블명, 변경 후 테이블명; |
[예제]
RENAME 문장을 이용하여 TEAM 테이블명을 다른 이름으로 변경하고, 다시 TEAM 테이블로 변경
| <Oracle> RENAME TEAM TO TEAM_BACKUP; 테이블 이름이 변경되었다. RENAME TEAM_BACKUP TO RENAME; 테이블 이름이 변경되었다. |
| <SQL Server> sp_rename 'dbo.TEAM', 'TEAM_BACKUP'; 주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다. sp_rename 'dbo.TEAM_BACKUP', 'TEAM'; 주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다. |
E. DROP TABLE
- 테이블을 잘못 만들었거나 테이블이 더 이상 필요 없을 경우 해당 테이블을 삭제하는 명령어
- DROP 명령어를 사용하면 테이블의 모든 데이터 및 구조를 삭제
- CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 것을 의미
| DROP TABLE 테이블명 [CASCADE CONSTRAINT]; |
※ SQL Server 에서는 CASCADE 옵션이 존재하지 않으며 테이블을 삭제하기 전에 참조하는 FOREIGN KEY 제약 조건 또는 참조하는 테이블을 먼저 삭제해야 함.
[예제]
PLAYER 테이블 삭제
| <Oracle> DROP TABLE PLAYER; 테이블이 삭제되었다. DESC PLAYER; ERROR: 설명할 객체를 찾을 수 없다. |
| <SQL Server> DROP TABLE PLAYER 명령이 완료되었다. exec sp_help 'dbo.PLAYER'; 메시지 15009, 수준 16 상태 1, 프로시저 sp_help, 줄 66 데이터베이스 'northwind'에 엔터티 'dbo.PLAYER' 이(가) 없거나 이 작업에 적합하지 않다. |
F. TRUNCATE TABLE
- 테이블 자체가 삭제되는 것이 아니고, 해당 테이블에 들어있던 모든 행들이 제거되고 저장 공간을 재사용 가능하도록 해제.
| TRUNCATE TABLE PLAYER; |
■ DROP vs TRUNCATE vs DELETE
|
이전글
다음글
'자격증 > SQLD' 카테고리의 다른 글
| SQLD - 기출문제 챕터별 예제 (feat.노랭이 등.) (0) | 2024.02.03 |
|---|---|
| SQLD 2과목 관리구문 - DCL (0) | 2024.02.02 |
| SQLD 2과목 관리구문 - TCL (0) | 2024.01.31 |
| SQLD 2과목 관리구문 - DML (0) | 2024.01.31 |
| SQLD 2과목 SQL 활용 - 정규 표현식 (0) | 2024.01.30 |
