검색결과 리스트
TRUNCATE에 해당되는 글 1건
- 2015.08.26 [SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL
글
[SQLD] 2과목 SQL 기본및 활용 - 1장 2절 DDL
1. 데이터 유형
- 특정 칼럼을 정의할 때 선언한 데이터 유형은 그 칼럼이 받아들일 수 있는 자료의 유형을 규정한다.
데이터 유형 | 설명 |
CHARACTER(s) | - 고정길이 문자열 정보 (ORACLE/SQL SERVER 모두 CHAR로 표현) - s는 기본길이 1바이트, 최대 길이 ORACLE 2,000 바이트, SQL SERVER 8000 바이트 - s만큼 길이를 갖고 고정길이를 가지고 있으므로 할당된 변수 값의 길이가 s보다 작을 경우 그 차이 길이만큼 공간으로 채워진다. |
VARCHAR(s) | - CHARACTER VARYING 약자로 가변길이 문자열 정보 (오라클은 VARCHAR2로 표현, SQL SERVER는 VARCHAR로 표현) - s는 최소 길이 1바이트, 최대 길이 오라클 4,000 바이트, SQL Server 8,000바이트 - s만큼 길이를 갖지만 가변 길이로 조정되기 때문에 할당된 변수값의 바이트만 적용됨.( Limit개념) |
NUMERIC | - 정수, 실수 등 숫자정보 (오라클은 NUMBER로, SQL SERVER는 10가지 이상의 숫자 타입이 있음.) - 오라클은 처음에 전체 자리 수를 지정하고, 그 다음 소수부분의 자리수를 지정한다. ex) 정수부분 6자리, 소수점 부분 2자리 => nuber(8,2) |
DATE | - 날짜와 시각 정보 - ORACLE은 1초단위, SQL SERVER는 3.33 ms 단위 관리. |
* VARCHAR 유형은 가변 길이이므로 필요한 영역은 실제 데이터 크기뿐이다.
* CHAR에서는 문자열을 비교할 때 공백을 채워서 비교하는 방법을 사용한다.
- 공백 채우기 비교에서는 우선 짧은쪽의 끝에 공백을 추가하여 2개의 데이터가 같은 길이가 되도록 한 후 앞에서부터 비교한다.(공백만 다른 문자열은 같다고 판단함.)
* VARCHAR
- 맨 처음부터 한 문자씩 비교하고 공백도 하나의 문자로 취급하므로 끝의 공백이 다르면 다른 문자로 판단한다.
ex)
CHAR 유형: 'AA' = 'AA '
VARCHAR 유형 'AA' != 'AA '
2. CREATE TABLE
가. 테이블과 칼럼 정의
- 테이블에 존재하는 모든 데이터를 고유하게 식별할 수 있으면서 반드시 값이 존재하는 단일 칼럼이나 칼럼의 조합들(후보키) 중에 하나를 선정하여 기본칼럼으로 지정한다.
- 기본키는 단일 칼럼이 아닌 여러개의 칼럼으로 만들어질 수 있다.
- 테이블과 테이블간에 정의된 관계는 기본키와 외부키를 활용해서 설정하도록 한다.
나. CREATE TABLE
- 테이블을 생성하는 구문.
CREATE TABLE 테이블명( 칼럼명1 DATATYPE [DEFAULT 형식], 칼럼명2 DATATYPE [DEFAULT 형식] ); |
- 테이블 생성 시 주의할 규칙.
1) 테이블명은 객체를 의미할 수 있는 적절한 이름으로 사용한다. 가능한 단수형을 권고.
2) 테이블 명은 다른 테이블의 이름과 중복되지 않아야 한다.
3) 한 테이블 내에서는 칼럼명이 중복되게 지정될 수 없다.
4) 테이블 이름을 지정하고 각 칼럼들은 괄호로 묶어서 지정한다.
5) 각 칼럼들은 콤마로 구분되고, 테이블 생성명의 끝은 ;로 끝난다.
6) 칼럼에 대해서는 다른 테이블까지 고려하여 데이터베이스 내에서는 일관성 있게 사용하는 것이 좋다.
7) 칼럼 뒤에 데이터 유형은 꼭 지정되어야한다.
8) 테이블명과 칼럼명은 반드시 문자로 시작해야 하고 벤더별로 길이에 대한 한계가 있다.
9) 벤더에서 사전에 지정한 예약어는 사용할 수 없다.
10) A-Z, a-z, 0-9, _, $, # 문자만 허용된다.
ex) 테이블명이 잘못 정의된 사례
10_player
star-player
- 규칙에 맞게 선수 테이블 생성
1) ORACLE
CREATE TABLE PLAYER ( PLAYER_ID CHAR(7) NOT NULL, PLAYER_NAME VARCHAR2(20) NOT NULL, TEAM_ID CHAR(3) NOT NULL, E_PLAYER_NAME VARCHAR2(40), NICKNAME VARCHAR2(30), POSITION VARCHAR2(10), BACK_NO NUMBER(2), NATION VARCHAR2(20), BIRTH_DATE DATE, SOLAR CHAR(1), HEIGHT NUMBER(3), WHEIGHT NUMBER(3), CONSTARAINT PLAYER_PK PRIMARY KEY(PLAYER_ID), CONSTARAINT PLAYER_FK (TEAM_ID) REFERENCES TEAM(TEAM_ID) ); |
2) SQL SERVER
CREATE TABLE PLAYER ( PLAYER_ID CHAR(7) NOT NULL, PLAYER_NAME VARCHAR(20) NOT NULL, TEAM_ID CHAR(3) NOT NULL, E_PLAYER_NAME VARCHAR(40), NICKNAME VARCHAR(30), POSITION VARCHAR(10), BACK_NO TYNINT, NATION VARCHAR(20), BIRTH_DATE DATE, SOLAR CHAR(1), HEIGHT SMALLINT, WHEIGHT SMALLINT, CONSTARAINT PLAYER_PK PRIMARY KEY(PLAYER_ID), CONSTARAINT PLAYER_FK (TEAM_ID) REFERENCES TEAM(TEAM_ID) ); |
- 테이블 생성시 대소문자 구분하지 않는다.
- 기본적으로 테이블이나 칼럼명은 대문자로 만들어진다.
- DATETIME 데이터 유형에는 별도의 크기를 지정하지 않는다.
- 문자 데이터 유형은 반드시 가질 수 있는 최대 길이를 표시해야한다.
- 칼럼과 칼럼의 구분은 콤마로 하되, 마지막 칼럼은 콤마를 찍지 않는다.
- 칼럼에 대한 제약조건이 있으면 CONSTRAINT를 이용하여 추가할 수 있다.
* 제약조건은 PLAYER_NAME, TEAM_ID 컬럼의 데이터 유형 뒤에 NOT NULL을 정의한 사례와 같은 칼럼레벨 정의방식과, PLAYER_PK PRIMARY KEY, PLAYER FK FOREIGN KEY 사례처럼 테이블 마지막에 모든 제약조건을 기술하는 테이블레벨 방식이 있다.
다. 제약조건 (CONSTRAINT)
- 제약조건이란 사용자가 원하는 조건의 데이터만 유지하기 위한 즉, 데이터 무결성을 유지하기 위한 데이터베이스의 보편적 방법으로 테이블의 특정 칼럼에 설정하는 제약이다.
- 제약조건의 종류
구분 | 설명 |
PRIMARY KEY(기본키) | 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 기본키를 정의한다. 하나의 테이블에는 하나의 기본키 제약만 정의할 수 있다. 기본키 제약을 정의하면 DBMS는 자동으로 UNIQUE INDEX를 생성하며 기본키를 구성하는 칼럼에는 NULL을 입력할 수 없다. 기본키 제약 = 고유키 제약 & NOT NULL 제약이다. |
UNIQUE KEY (고유키) | 테이블에 저장된 행 데이터를 고유하게 식별하기 위한 고유키를 정의한다. 단, NULL은 고유키 제약의 대상이 아니므로, NULL 값을 가진 행이 여러 개가 있더라도 고유키 제약 위반이 되지 않는다. |
NOT NULL | NULL값의 입력을 금지한다. 이 제약을 정함으로써 해당 칼럼은 입력 필수가 된다. NOT NULL을 CHECK의 일부분으로 이해할 수 있다. |
CHECK | 입력할 수 있는 값의 범위 등을 제한한다. CHECK 제약으로는 TRUE or FALSE로 평가할 수 있는 논리식을 지정한다. |
FOREIGN KEY(외래키) | 관계형 데이터베이스에서 테이블 간의 관계를 정의하기 위해 기본키를 다른테이블의 외래키로 복사하는 경우 외래키가 생성된다. 외래키 지정 시 참조 무결성 제약 옵션을 선택할 수 있다. |
- 조건에 따른 테이블 생성
1) ORACLE
CREATE TABLE TEAM( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR2(8) NOT NULL, TEAM_NAME VARCHAR2(40) NOT NULL, E_TEAM_NAME VARCHAR2(50), ORIG_YYYY CHAR(4), STADIUM_ID CHAR(3) NOT NULL, ZIP_CODE1 CHAR(3), ZIP_CODE2 CHAR(3), ADDRESS VARCHAR2(80), DDD VARCHAR2(3), TEL VARCHAR2(10), FAX VARCHAR2(10), HOMEPAGE VARCHAR2(50), OWNER VARCHAR2(10), CONSTRAINT TEAM_ID_PK PRIMARY KEY (TEAMD_ID), CONSTRAINT TEAM_FK FROEIGN KEY(STADIUM_ID) REFERENCES(STADIUM_ID) ); |
2)SQL server
CREATE TABLE TEAM( TEAM_ID CHAR(3) NOT NULL, REGION_NAME VARCHAR(8) NOT NULL, TEAM_NAME VARCHAR(40) NOT NULL, E_TEAM_NAME VARCHAR(50), ORIG_YYYY CHAR(4), STADIUM_ID CHAR(3) NOT NULL, ZIP_CODE1 CHAR(3), ZIP_CODE2 CHAR(3), ADDRESS VARCHAR(80), DDD VARCHAR(3), TEL VARCHAR(10), FAX VARCHAR(10), HOMEPAGE VARCHAR(50), OWNER VARCHAR(10), CONSTRAINT TEAM_ID_PK PRIMARY KEY (TEAMD_ID), CONSTRAINT TEAM_FK FROEIGN KEY(STADIUM_ID) REFERENCES(STADIUM_ID) ); |
라. 생성된 테이블 구조 확인.
오라클 : DESC 테이블명; 또는 DESCRIBE 테이블명;
SQL SEVER : exec sp_help 'dbo.테이블명'
go
마. SELECT 문장을 통한 테이블 생성.
- Create Table ~ As select ~
해당 방법을 이용할 경우 칼럼별로 데이터 유형을 다시 정의하지 않아도된다.
- CATS기법 사용시 기존테이블의 제약 조건중에 Not null만 새로운 테이블에 적용되며 기본키, 고유키, 외래키, Check 등의 다른 제약조건은 없어진다.
- SQL Server에서는 Select ~ into ~ 를 활용하여 같은 결과를 얻을 수 있으며 칼럼속성에 Identity를 사용했다면 Identity 속성까지 같이 적용된다.
* 오라클
CREATE TABLE TEMP_PLAYER
AS SELECT * FROM PLAYER;
* SQL SERVER
SELECT * INTO TEAM_TEMP FROM TEAM;
3. ALTER TABLE
- 한번 생성된 테이블의 구조를 변경하기위해 사용한다.
- 칼럼을 추가/삭제하거나 제약조건을 추가/삭제 하는 작업을 진행하게된다.
가. ADD COLUMN.
ALTER TABLE 테이블명
ADD 추가할 칼럼명 데이터 타입;
* 추가된 칼럼은 마지막에 위치하게되며 위치를 지정할 수 없다.
- 오라클
ALTER TABLE PLAYER
ADD (ADDRESS VARCHAR2(80));
* 아래 명령어도됨.
ALTER TABLE PLAYER
ADD ADDRESS VARCHAR2(80);
- SQL SERVER
ALTER TABLE PLAYER
ADD ADDRESS VARCHAR(80);
나. DROP COLUMN
- 테이블에서 필요없는 칼럼을 삭제할 수 있으며 데이터가 없거나 있거나 모두 삭제 가능하다.
- 한 번에 하나의 칼럼만 삭제 가능하며 칼럼 삭제 후 최소 하나 이상의 칼럼이 데이터에 존재해야한다.
- 한 번 삭제된 칼럼은 복구가 불가능하다.
ALTER TABLE 테이블명
DROP COLUMN 컬럼명;
다. MODIFY COLUMN
- ALTER TABLE 명령어를 통해 칼럼의 데이터 유형, 디폴트 값, NOT NULL 제약조건에 대해 변경을 포함할 수 있다.
- 칼럼에 대한 데이터 정의를 변경하는 명령어
ALTER TABLE 테이블명
MODIFY ( 칼럼명1 데이터유형 [DEFAULT] [NOTNULL] ,
칼럼명2 데이터유형 .... ) ;
* 칼럼 변경 시 고려할 사항.
- 해당 칼럼의 크기는 늘릴 수 있지만 줄일 수는 없다. (기존 데이터의 훼손 방지)
- 해당 칼럼이 NULL값만 가지고 있거나 테이블에 아무 행도 없으면 칼럼의 폭을 줄일 수 있다.
- 해당 칼럼이 NULL값만 가지고 있으면 데이터 유형을 변경할 수 있다.
- 해당 칼럼의 DEFAULT 값을 바꾸면 변경작업 이후 발생하는 행 삽입에만 영향을 미친다.
- 해당 칼럼에 NULL값이 없을 경우에만 NOT NULL제약조건을 추가할 수 있다.
ex) TEAM 테이블에 ORIG_YYYY컬럼의 데이터 유형을 CHAR(4) -> VARCHAR2(8) 형식으로 변경하고 향후 입력되는 데이터의 DEFAULT 값으로 20090826 적용하고 모든 행의 ORIG_YYYY컬럼에 널이 없으므로 제약조건을 NOT NULL로 변경한다
- 오라클
ALTER TABLE TEAM
MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20150826' NOT NULL) ;
-SQL SERVER
ALTER TABLE TEAM
ALTER CLOUMN ORIG_YYYY VARCHAR(8) DEFAULT '20150826' NOT NULL;
ALTER TABLE TEAM
ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '201508226' FOR ORIG_YYYY;
- RENAME COLUMN
칼럼명을 어떤 이유로 불가피하게 변경해야 하는 경우.
ALTER TABLE 테이블명
RENAME COLUMN 변경해야 할 컬럼명 TO 새로운 칼럼명;
ex)
- 오라클
ALTER TABLE PLAYER
RENAME COLUMN PLAYER_ID TO TEMP_ID;
ALTER TABLE PLAYER
RENAME COLUMN TEMP_ID TO PLAYER_ID;
- SQL server
sp_rename 프로시져를 이용하여 변경한다.
sp_rename 변경해야 할 컬럼명, 새로운 칼럼명, 'COLUMN';
ex) sp_rename 'dbo.TEAM_TEMP.TEAMID', 'TEAM_TEMP_ID', 'COLUMN';
라. DROP CONSTRAINT
- 테이블 생성 시 부여했던 제약조건을 삭제하는 명령
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
ex)
- 오라클 / SQL server
ALTER TABLE PLAYER
DROP CONSTRAINT PLAYER_FK;
마. ADD CONSTRAINT
- 테이블 생성 이후에 필요에 의해서 제약조건을 추가할 수 있다.
ALTER TABLE 테이블
ADD CONSTRAINT 제약조건명 제약조건 (칼럼명) ;
ex)
ALTER TABLE PLAYER
ADD CONSTRAINT PLAYER_FK
FOREIGN KEY(TEAM_ID) REFERENCES TEAM(TEAM_ID);
* 참조 제약조건을 추가하면 참조 무결성 옵션에 따라 테이블의 데이터를 삭제하려 할 경우 외부에서 참조하고 있기 때문에 삭제가 불가능하게 제약할 수 있다.
=> FK를 설정함으로써 실수에 의한 테이블 삭제나 필요한 데이터의 의도하지 않은 삭제와 같은 불상사를 방지할 수있다.
4. RENAME TABLE
- RENAME 명령어를 사용하여 테이블 이름을 변경할 수 있다.
오라클: RENAME 변경전 테이블명 TO 변경후 테이블명;
SQL SERVER : sp_rename 변경전 테이블명, 변경후 테이블명;
ex)
오라클
RENAME TEAM TO TEMP_TEAM;
SQL server
sp_rename 'dbo.team','temp_team';
5. DROP TABLE
- 테이블을 잘못 만들거나 더이상 필요없을 경우 삭제하는 명령어
DROP TABLE 테이블명 [CASCADE CONSTRAINT];
* DROP 명령어를 사용하면 테이블의 데이터 및 구조를 삭제한다.
* CASCADE CONSTRAINT 옵션을 사용하면 테이블과 관계가 있었던 참조제약조건에 대해서도 삭제한다.
(SQL server에서는 CASCADE옵션이 존재하지 않으며 테이블 삭제하기 전에는 FOREIGN KEY 제약조건 또는 참조하는 테이블을 먼저 삭제해야한다.)
ex)
- 오라클
DROP TABLE TEMP_EMP;
- SQL server
DROP TABLE TEMP_EMP;
'자격증을 위한 > SQLD' 카테고리의 다른 글
[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL (0) | 2015.08.29 |
---|---|
[SQLD] 2과목 SQL 기본및 활용 - 1장 3절 DML (0) | 2015.08.26 |
[SQLD] 2과목 SQL 기본및 활용 - 1장 1절 관계형 데이터베이스 개요 (0) | 2015.08.26 |
[SQLD] 1과목 데이터 모델링의 이해 - 2장 6절:분산 데이터베이스와 성능 (0) | 2015.08.24 |
[SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능 (0) | 2015.08.24 |
RECENT COMMENT