DDL(Data Definition Language)
- 테이블의 구조와 제약 조건을 생성, 삭제하고 수정하는 DDL 명령문
DDL 대상
- 데이터를 정의하는 언어
- 데이터를 담는 그릇을 정의하는 언어
- OBJECT
1. 스키마(Schema)
- DBMS 특성과 구현 환경을 감안한 데이터 구조
- 직관적으로 하나의 데이터베이스로 이해 가능
- DBMS마다 차이
2. 도메인(Domain)
- 속성의 데이터 타입과 크기, 제약 조건 등을 지정한 정보
- 속성이 가질 수 있는 값의 범위로 이해 가능
- 예를 들어, 주소를 VARCHAR(120)로 정의
3. 테이블(Table)
- 데이터 저장 공간
4. 뷰(View)
- 하나 이상의 물리 테이블에서 유도되는 가상의 논리 테이블
5. 인덱스(Index)
- 검색을 빠르게 하기 위한 데이터 구조
DDL 조작 방법
- 오브젝트를 생성, 변경 그리고 제거하기 위해 다음과 같은 명령어 사용
1. CREATE
- 생성 명령어
- 데이터베이스 오브젝트 생성
2. ALTER
- 변경 명령어
- 데이터베이스 오브젝트 변경
3. DROP
- 삭제 명령어
- 데이터베이스 오브젝트 삭제
4. TRUNCATE
- 데이터 삭제 명령어
- 데이터베이스 오브젝트 내용 삭제
테이블 생성
- 신규 생성
CREATE TABLE 테이블이름(
열이름 데이터타입 [DEFAULT 값] [NOT NULL]
{,열이름 데이터 타입 [DEFAULT 값] [NOT NULL]}*
[PRIMARY KEY (열 리스트).]
{[FOREIGN KEY(열 리스트) REFERENCES 테이블이름 [(열이름)]
[ON DELETE 옵션]
[ON UPDATE 옵션]],}*
[CHECK (조건식) | UNIQUE(열이름) ]);
SQL> CREATE TABLE userTBL -- 회원테이블
2 ( userID CHAR(8) NOT NULL PRIMARY KEY, --사용자 아이디(PK)
3 userName nvarchar2(10) not null, --사용자 이름
4 birtYear number(4) not null, --출생 연도
5 addr nchar(2) not null, --지역(경기, 서울, 경남 식으록 2글자만 입력)
6 mobile1 CHAR(3), --휴대폰의 국번(010, 011, 016, 017, 018, 019 등)
7 mobile2 CHAR(8), --휴대폰의 나머지 전화번호(- 제외)
8 height number(3), --키
9 mDate DATE --회원 가입일
10 );
SQL> CREATE TABLE buyTBL -- 회원구매테이블
2 ( idNum number(8) NOT NULL PRIMARY KEY, --순번(pk)
3 userID char(8) not null, --사용자아이디
4 prodName nchar(6) not null, --물품 명
5 groupName nchar(4), --분류
6 price number(8) not null, -- 단가
7 amount number(3) not null, -- 수량
8 FOREIGN KEY (userID) REFERENCES userTBL(userID)
9 -- 외래 키 설정하여 buyTBL의 userID를 userTBL(userID)를 참고하여 연결
10 );- 다른 테이블 정보를 이용한 테이블 생성 (테이블 복사는 DBMS 제품마다 다를 수 있다!)
CREATE TABLE 테이블이름 AS SELECT 문;
-- 테이블의 전체 내용을 복사
SQL> CREATE TABLE buyTBL2 AS (SELECT * FROM buyTBL);
-- 테이블 중에 특정 COLUMN만 복사
SQL> CREATE TABLE buyTBL3 AS (SELECT userID, prodName FROM buyTBL);테이블 변경
ALTER를 이용하여 테이블 구조를 변경하는 문법은 다음과 같다!
- 열 추가
ALTER TABLE 테이블이름 ADD 열이름 데이터타입 [DEFAULT 값]
SQL> ALTER TABLE buyTBL3 ADD amount varchar2(3) null;- 열 데이터 타입 변경
ALTER TABLE 테이블이름 MODIFY 열이름 데이터타입 [DEFAULT 값];
SQL> ALTER TABLE buyTBL3 MODIFY amount number(3);- 열 삭제
ALTER TABLE 테이블이름 DROP COLUMN 열이름;
SQL> ALTER TABLE buyTBL3 DROP COLUMN amount;테이블 삭제, 절단, 이름 변경
DROP TABLE, TRUNCATE TABLE, RENAME TABLE 명령문을 사용하여 테이블을 삭제, 절단, 이름 변경을 할 수 있다, 테이블 및 테이블 내용을 삭제하기 위한 명령어의 사용 문법은 다음과 같다!
- 테이블 삭제
DROP TABLE 테이블이름;
SQL> DROP TABLE buyTBL;- 테이블 내용 삭제
TRUNCATE TABLE 테이블이름;
SQL> TRUNCATE TABLE buyTBL3;- 테이블 이름 변경
RENAME 이전테이블이름 TO 새로운테이블이름
SQL> RENAME buyTBL3 TO shopTBL;
ATER TABLE 이전테이블이름 RENAME TO 새로운테이블이름
SQL> ALTER TABLE shopTBL RENAME TO buyTBL3;제약 조건 적용
- PRIMARY KEY
- 테이블의 기본키를 정의
- 기본으로 NOT NULL, UNIQUE 제약이 포함
userID CHAR(8) NOT NULL PRIMARY KEY,- FOREIGN KEY
- 왜리키를 정의
- 참조 대상을 테이블이름(열이름)으로 명시
- 참조 무결성 위배 상황 발생 시 처리 방법으로 옵션 지정 가능
FOREIGN KEY (userID) REFERENCES userTBL(userID)- UNIQUE
- 테이블 내에서 열은 유일한 값을 가져야 함
- 테이블 내에서 동일한 값을 가져서는 안 되는 항목에 지정함
userID VARCHAR(10) UNIQUE NOT NULL,- NOT NULL
- 테이블 내에서 관련 열의 값은 NULL일 수 없음
- 필수 입력 항목에 대해 제약 조전으로 설정
userID VARCHAR(10) UNIQUE NOT NULL,- CHECK
- 개발자가 정의하는 제약 조건
- 상황에 따라 다양한 조건 설정 가능
CONSTRAINT emp_ch_gno CHECK(LENGTH(gno)=13),
CONSTRAINT emp_ch_sex CHECK(sex IN('남', '여'))CREATE TABLE 내부에 제약 조건을 명시하는 형태로 사용
ARTER를 통해 테이블 제약 조건 변경 가능
SQL 활용 주요 내용
SQL은 DDL, DML 및 DCL과 같은 유형의 작업을 통해 데이터베이스 안에 그릇을 만들고
그 안에 데이터를 담거나 꺼내어 사용하는 도구다
DDL 활용 (테이블을 통해 관리할 데이터를 확인)
1. 데이터를 활용
- 데이터를 정의(Data Definition)
- 데이터를 담는 그릇을 만드는 것
2. 데이터 타입 결정
- 자료를 표현할 때 필요한 공간과 형식을 규정하는 기준
- 해당 컬럼에 입력되는 데이터의 종류와 크기를 제한
- 다른 종류의 데이터나 큰 데이터가 입력되면 오류
- 숫자형, 문자형, 날짜 등 (NUMERIC, CHARACTER, VARCHAR, DATETIME 등)
사용하는 제품마다 다를 수 있다
3. 제약 조건이 무엇인지 확인
- 주문 번호를 기본키(Primary Key)로 지정
- 제약조건은 변경이나 추가가 가능하다
DDL 활용 (테이블의 구성요소에 대해 조사)
1. 테이블의 구성요소에 대해 조사
- 눈에 보이지 않는 그 어떤 것을 문제 해결의 수단이나 목적으로 하는 경우,
이를 가시화 하는것이 중요 - 테이블, 행(Row) & 열(Column)으로 구성된 가장 기본적인 데이터베이스 객체로
데이터 베이스 내에서 모든 데이터는 테이블 안에 저장 - 기본키(Primary Key), 개별의 행을 유일하게 식별하는 속성
데이터베이스는 정보의 중복을 최소화하기 위해 정보 종류를
테이블에 해당하는 엔티티 단위로(Entity) 분리 저장 - 외래키(Foreign Key), 분리된 정보 사이의 관계를 이어주는 수단!
관계형 데이터베이스, 정보를 격자 형태의 테이블로 저장하고
테이블의 관계를 이용해 정보를 활용하는 데이터 베이스
2. 테이블에서 사용할 제약조건을 선택
- 테이블 구조를 정의하면서 데이터 무결성을 유지하기 위해 제약 조건을 이용
- 필수 요소는 아니지만 데이터베이스 일관성(Consistency)을 유지하고
잘못된 데이터 입력과 수정으로부터 데이터베이스를 보호하는데 필수적인 요소이므로
반드시 정의하는 것이 좋다 - 기본키(Primary Key), 왜래키(Foreign Key), UNIQUE KEY, NOT NULL, CHECK 등
DDL 활용 (테이블을 생성하고 변경)
1. CREATE 문으로 테이블 생성
SQL> CREATE TABLE '주문테이블' (
2 '주문번호' VARCHAR(16) NOT NULL,
3 '고객번호' VARCHAR(16) NOT NULL,
4 '주문일' VARCHAR(8) NOT NULL,
5 '주문가격' DECIMAL(15,2) NOT NULL,
6 '배송도시' VARCHAR(256),
7 '배송완료일' VARCHAR(8),
8 '결제금액' VARCHAR(8),
9 '할인금액' DECIMAL(15,2) NOT NULL,
10 '적립포인트' DECIMAL(15,2) NOT NULL,
11 PRIMARY KEY('주문번호')
12 );2. SELECT를 이용하여 테이블을 생성
- 만일 생성하고자 하는 '기존테이블'과 동일한 컬럼을 가진 '신규테이블'이 있다면
다음과 같이 기존에 존재하는 테이블 정보를 이용하여 새로운 테이블을 만들 수 있다
CREATE TABLE 신규테이블 AS (SELECT * FROM 기존테이블);
SQL> CREATE TABLE '주문테이블2' AS (SELECT * FROM '주문테이블');- 생성된 테이블은 기존 테이블의 컬럼 및 테이터 유형과 길이 등을 그대로 적용
- NOT NULL의 정의는 그대로 적용
- 제약 조건은 적용되지 않음
- ALTER TABLE을 사용하여 제약 조건을 추가해야 함
- 동일한 컬럼들로 생성된 경우 '*'를 사용
- 필요한 컬럼만을 지정하여 테이블을 생성할 수 있음
3. 생성한 테이블을 확인
- 대표적인 상용 O 제품
SQL> SELECT * FROM '주문테이블';- 대표적인 비상용 M 제품
SHOW TABLES;
4. CREATE문에 사용된 제약 조건을 조사
- 6~8. 제약조건 없음
- 2~5, 9~10. NOT NULL (NULL 값 허용 안함)
- PRIMARY KEY (기본 키)
DDL 활용 (테이블 구조를 변경)
1. 테이블 구조 변경 대상을 정의
- 기존 테이블의 '배송도시'를 '배송도시코드'로 이름 변경과 정수형으로 타입 변경!
2. 컬럼 이름과 타입을 변경
- '배송도시'를 '배송도시코드'로 변경
- ALTER TABLE 테이블명 CHANGE 컬럼명 자료형
SQL> ALTER TABLE '주문테이블' CHANGE '배송도시' '배송도시코드' INT;- 열의 크기 확대는 별 제한이 없다
- 축소는 데이터 타입 변환은 모든 데이터가 NULL 값을 가지는 경우 허용
- DEFAULT 값 수정도 가능하며 수정 이후에 입력되는 값에만 적용
3. 컬럼을 추가하고 삭제
- 추가
- ALTER TABLE '테이블명' ADD '컬럼명' 자료형;
- 테이블은 가지는 열의 개수에는 제한이 있어 추가가 안될수도 있다
// 맨 뒤에 추가
ALTER TABLE '테이블명' ADD '새컬럼명' 자료형;
// 맨 앞에 추가
ALTER TABLE '테이블명' ADD '새컬럼명' 자료형 FIRST;
// 지정 컬럼 뒤 추가
ALTER TABLE '테이블명' ADD '새컬럼명' 자료형 AFTER '앞컬럼명';DDL 활용 (제약 조건 변경)
1. 제약 조건 변경 대상을 정의
- 앞서 '배송도시'를 '배송도시코드'로 변경 하면서 데이터 타입을 INT로 바꾸었다
배송도시 명칭을 알기 위해서는 '도시코드테이블'에서 코드를 통해 조회해야 한다
이를 위해 '배송도시코드'를 외래키(FOREIGN KEY)로 정의2. 제약 조건 변경
- 왜래키(FOREIGN KEY) 제약 조건 추가
SQL> ALTER TABLE '주문테이블' ADD FOREIGN KEY ('배송소디코드') REFERENCES '도시코드테이블'(code);- 방금 생선한 외래키 제약 조건을 삭제
SQL> ALTER TABLE '주문테이블' DROP FOREIGN KEY[제약조건 이름];- [제약조건이름]은 시스템이 부여한 것으로 별도의 테이블에서 내용 확인
(information_schema.table_constraints 테이블)
3. 추가적으로 제약 조건 변경 SQL문을 조사
- ALTER TABLE을 사용하여 테이블에 제약조건을 추가하거나 삭제할 수 있으나,
수정은 불가하다 - 제약 조건 추가
ALTER TABLE 테이블이름 ADD [CONSTRAINT 제약조건이름] 제약조건(열이름);- 제약 조건 삭제
ALTER TABLE 테이블이름 CONSTRAINT 제약조건이름
| 테이블이름 PRIMARY KEY
| FOREIGN KEY(열이름)
| UNIQUE(열이름)- 제약 조건 비활성화
ALTER TABLE 테이블이름 DISABLE CONSTRAINT 제약조건이름- 제약 조건 활성화
ALTER TABLE 테이블이름 ENABLE CONSTRAINT 제약조건이름- 제약 조건을 추가하려면 관련된 열의 데이터가 제약 조건에 부합해야 한다
- PRIMARY KEY : 관련 열이 NULL 값을 가져도, 동일한 값을 가져도 안된다.
- UNIQUE : 관련 열이 유일한 값 도는 NULL 값을 가져야 한다
- FOREIGN KEY : 해당 열의 값이 NULL이거나 참조하는 테이블에 있어야 한다
- 제약 조건 비활성화 : 제약 조건을 없애는 것이 아닌 사용하지 못하게 하는 것
'ALTER TABLE 테이블이름 DISABLE CONSTRAINT 제약조건이름' - 제약 조건 활성화 : 비활성화 된 제약 조건을 활성화 하는 것
'ALTER TABLE 테이블이름 ENABLE CONSTRAINT 제약조건이름'
스키마 생성
-- 스키마 생성
-- USER SQL
ALTER USER "Shop"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK ;
-- QUOTAS
ALTER USER "Shop" QUOTA 10240K ON "USERS";
-- ROLES
ALTER USER "Shop" DEFAULT ROLE "CONNECT","RESOURCE";
-- SYSTEM PRIVILEGES
-- 유저 생성(command line)
SQL> create user shop identified by 1234;
-- 권한주기(grant)
-- 세션을 만들 권한을 주겠다(접속 권한)
SQL> grant create session to shop;
-- 테이블 생성 권한
SQL> grant create table to shop;
-- system user create table
SQL> connect system
Enter password:
Connected.
SQL> create table third_tbl (
2 name1 varchar2(20 byte) not null,
3 num1 number(10) not null);
-- connect는 연결, dba 데이터베이스 권한, resource는 장소
SQL> grant connect, dba, resource to shop;
-- shop, create table Member
SQL> create table MEMBER(
2 memberID varchar(8) not null,
3 memberName nchar(5) not null,
4 memberAddress nvarchar2(20) null );
-- shop, product
SQL> create table PRODUCT (
2 productName nchar(4) not null,
3 cost number(7) not null,
4 makeDate date null,
5 company nchar(5) null,
6 amount number(3) not null);
아직까지는 코린이!
This viewpoint throws new wordle junior insight on the subject and invites further investigation and discussion. convertidor mp3