'IT관련/SQL'에 해당되는 글 6건

데이터베이스를 이용해 데이터를 관리하기 위해서는 우선 테이블을 생성해야한다.

여기서 잠깐 ! !

DDL이란 무엇인가?

DDL(Data Definition Language)라고 해서 데이터를 정의하는 목적으로 생성,추가,변경 등을 하는 언어이다. 이에 해당하는 종류로는 CREATE, DROP, ALTER, TRUNCATE, RENAME, COMMENT 등이 있다.

1) CREATE : 데이터베이스 객체를 생성합니다.

그럼 우선적으로 테이블생성에 대해 알아보겠다.
테이블 생성하는 쿼리문으로는 아래와 같다.

CREATE TABLE 테이블명  (컬럼명 자료형)

 

여기서 컬럼명은 사용자가 지정가능하지만 자료형은 그곳에 데이터를 저장하는 자료에 따라 달라진다.

자료형으로는 여러가지가 있는데 다음과 같다.

자료형	설명
CAHR(N)	주어진 크기만큼 고정 길이의 문자 저장  (1Byte ~ 2000Byte)
VARCHAR2(N)	주어진 크기만큼 가변 길이의 문자 저장  (1Byte ~ 4000Byte)
NVARCHAR2(N)	국가별 국가 집합에 따른 크기의 문자 또는 바이트의 가변 길이 문자(1Byte ~ 4000Byte)
NUMBER(p, s)	정밀도와 스케일로 표현되는 숫자
DATE	날짜 형식을 저장
ROWID	테이블내 행의 고유 주소를 가지는 64진수 문자  해당 6Byte 또는 10Byte
BLOB	대용량의 Binary Data를 저장, 최대 4GB
CLOB	대용량의 Text Data를 저장, 최대 4GB
BFILE	대용량의 Binary Data를 파일로 저장, 최대 4GB
TIMESTAMP(n)	DATE 형의 확장된 형태
INTERVAL YEAR TO MONTH	년과 월을 이용하여 기간을 저장
INTERVAL DAY TO SECOND	일, 시, 분, 초를 이용하여 기간 저장  두 날짜 값의 정확한 차이를 표현하는데 유용

2) DROP : 데이터베이스 객체를 삭제합니다.

DROP TABLE 테이블명

 

3) ALTER : 기존에 존재하는 데이터베이스 객체를 다시 정의하는 역할을 합니다.

3-1)컬럼추가 : 새로운 컬럼을 추가하여 값을 지정할 수 있다.

ALTER TABLE 테이블명ADD(컬럼명 자료형)

 

3-2)컬럼변경 : 컬럼의 타입, 크기, 기본 값을 변경할 수 있다.

ALTER TABLE 테이블명MODIFY 컬럼명 자료형

 

3-3)컬럼제거 : 특정 컬럼과 컬럼의 데이터를 제거 할수 있다

ALTER TABLE 테이블명DROP COLUMN 컬럼명

4) RENAME : 데이터베이스의 컬럼명을 변경합니다.

RENAME 현재테이블명 TO 변경할테이블명

 

5) TRUNCATE : 테이블을 최초 생성된 초기상태로 만들며, ROLLBACK이 불가능합니다.

TRUNCATE TABLE 테이블명

SQL을 처음 설치하면 제공되는 계정은 SYSTEM계정과 Scott계정이 있습니다.

SYSTEM계정은 말그대로 관리자용 계정입니다. SQL서버자체를 관리할수있는 계정이며 관리자권한을 가지고 있습니다. Scott계정은 소스에도 오픈소스가 있듯이 모든 사용자들이 접근할수 있도록 만들어놓은 계정입니다. 설치시 계정의 비밀번호를 설정해둬야하는건 물론이죠.
여기서 관리자가 하는 일과 사용자가 할수 있는 일에 대해 간략히 설명하자면,

 

 

이렇듯 관리자와 사용자는 하는 일이 따로 정해져있습니다. 지금은 간략하게 테이블스페이스 생성, 사용자계정만드는 방법과 사용자권한 설정 및 시스템권한 설정에 대한 간단한 설명만하도록 하겠습니다.

우선 사용자계정을 만들기전에 테이블스페이스를 만든후 사용자 계정이 그 공간을 참고하도록 해야하는데, 시스템에 접근하는 명령어를 사용하기 위해서는 SYSTEM계정으로 접속하여야합니다. 현재 로그인한계정이 SYSTEM계정이라면 다음과 같은 명령어를 사용할수 잇습니다. 우선 테이블스페이스생성하는 명령어로는

CREATE TABLESPACE -테이블스페이스명- DATAFILE '경로\테이블스페이스명.dbf' SIZE 용량(K/M); [AUTO EXTEND ON NEXT 용량(K/M) MAXSIZE 용량(K/M);]

여기서 경로는 오라클이 설치된 경로입니다. 용량은 보통 10M를 사용하며, 사용용도에 따라서 용량을 지정해줄수있습니다. 아래의 []안에 있는 내용은 추가사항이므로 사용하여도 되고 사용하지 않아도 됩니다. AUTO EXTEND ON 구문을 쓰게되면 처음 지정해준 만큼의 공간을 다 사용하게 된다면 NEXT 용량만큼을 늘려주며 MAXSIZE를 넘지않게 만들어줍니다

그리고 다음은 사용자 계정생성인데, 사용자 계정은 아래와 같이 생성합니다.

CREATE USER -계정명- IDENTIFIED BY -패스워드- DEFAULT TABLESPACE -테이블스페이스명-;

계정명과 패스워드를 지정할수 있으며, 그계정이 참고할 테이블스페이스도 지정할수 있습니다. 앞의 명령어에서 만든 테이블스페이스를 위의 테이블스페이스명을 넣는 자리에 넣어주기만하면 사용할수 있습니다. 테이블스페이스와 계정을 만들었다고해서 다끝난것이 아닙니다. 만약 본인의 계정이 Scott계정과 같이 검색 및 출력자료로서만 사용한다면, 굳이 권한은 필요없을 것이지만, SQL문을 공부하기위해서라면 UPDATE도 해야하며 뷰,테이블,인덱스 삽입/삭제 등 위해서는 권한을 부여해야합니다. 이러한 권한을 시스템 권한이라고 합니다. 권한은 대략 130개정도 되며, 자주사용하는 시스템 권한을 패키지처럼 사용할수 있는 명령어들이 있습니다.

GRANT CONNECT, RESOURCE TO -계정명-;

위의 명령어를 사용하면 거의 일반적인 권한은 설정된다고 보면됩니다.

이렇게 만들어진 계정을 이제 SQL문을 사용할수 있습니다.

제가 사용할 SQL은 iSQL * Plus라는 것인데요.

iSQL * Plus는 오라클 10g를 다운받아서 사용할수 있습니다.

현재 11g도 나와있지만 10g가 상용화되어있기에 저도 10g를 쓰고있어요.

오라클10g는 웹사이트(http://www.oracle.com/technology/software/products/database/oracle10g/index.html)에 접속하여 다운받을수 있습니다. 화면하단에서 Realease1을 찾아서 해당 OS에 맞는 버전을 설치하시기바랍니다.

오라클 10g iSQL * Plus에는 다음 3가지의 테이블이 관리자계정에 저장되어있습니다.
EMP Table, DEPT Table, SALGRADE Table 3가지의 테이블이 있습니다.

EMP Table

 

DEPT Table

 

 

SALGRADE Table

 

이 3가지 테이블은 앞에서 함수 및 쿼리사용법에 대해 쓰일 테이블예제들입니다.

SQL * Plus 편집 명령과 파일 조작 명령

버퍼에 있는 내용을 나타내기 위한 LIST 명령어

SQL> select ename, sal*12     2   from emp;

연봉을 구하기 위해선 위와 같이 입력합니다

이전에 사용한 쿼리문을 다시 한번 나타나기위해서는

SQL> list

위와 같이 입력하면

SQL> list      1   select ename, sal*12      2*  from emp SQL>

이런식으로 이전 쿼리문을 출력할수 있습니다.

명령어 버퍼의 각각의 줄은 번호가 부여되어 있으며, list 대신 줄여서 l 이라고축약표현을 써도 무방합니다.

버퍼에 저장된 쿼리문을 실행시키기위한 명령어(/, RUN)

SQL> /


list로 버퍼에 저장된 쿼리문을 참고하여 /, R[un] 명령어로 버퍼의 쿼리문에 해당하는 출력문을 실행할수있다.

결과

-> iSQL*Plus에 기존에 저장된 내용을 활용하여 출력된 테이블

list와 마찬가지로 run또한 r이라고 축약표현을 써도 무방합니다.