네트워크 프로그래밍-DB(DDL, DML, DCL)

oracle DB 구성

DB 구성 명칭과 엑셀 명칭 비교

엑셀의 열 -> 컴퓨터에서는 필드 -> DB에서는 attribute

엑셀의 행 -> 컴퓨터에서는 레코드 -> DB에서는 tuple

컴퓨터는 필드가 모여 레코드가되고, 레코드가 모여 파일이 되고 파일이 모여 DB가 된다.

DB에서는 attribute가 모여 tuple이 되고 tuple이 모여 table이 되며 table이 모여 데이터베이스가 된다.

DB 데이터 형

char(8) -> 문자형, 뒤에 8은 길이를 나타낸다.

varchar2(8) -> oracle에서 나타내는 가변형 문자형, 뒤에 8은 limit을 뜻한다.

numeric(8) -> 정수형

numeric(8,2) -> 해당 방식으로 쓰면 앞에있는 8은 길이를 나타내고 뒤에있는 2는 소숫점 자리수를 나타낸다.

datetime -> 시간/날짜형

DDL

DDL은 테이블을 만들거나 삭제하거나 혹은 안에 구조를 수정 할 때 사용한다.

DDL에서 테이블을 만들 때 사용하는 명령어는 create이다.

SQL> connect netdream/netdream;

우선 특정 계정으로 로그인해주고(해당 계정에는 앞서 말한 권한부여가 되어있어야함)

SQL> create table school(

    student_number char(8) not null,

    student_name char(10),

    student_score numeric(5),

    constraint my_key primary key(student_number)

    );

테이블을 만들어준다. 해당 명령어의 의미는 school table을 만들라는 의미이다.

student _number의 데이터 형은 문자형이며 null값이 없도록 제한(not null)해주었다.

*null 값: null 값은 해당 데이터가 비어있다는 뜻이다. not null을 지정해주면 해당 attribute를 비워두고 데이터를 만들지 못한다.

student_name은 문자형, student_score는 정수형이라는 뜻이다.

여기서 마지막 constraint는 제약조건을 뜻한다.

해당 명령어는 이런 뜻이다.

my_key라는 변수에다 제약조건을 저장한다. 이때 제약조건은 기본키이며, 기본키로 사용되는 attribute는 student_number이다.

우선 기본키에 대해 알 필요가 있다. 

student_number        student_name        student_score

      201812345           홍길동                        100

후에 이런 식으로 각 attribute마다 데이터가 들어가게 된다. 그리고 이 데이터가 가지는 고유한 값을 기본키로 지정해준다.

기본키는 각 데이터를 구별하는 식별자로 사용한다. student_name이나 student_score는 데이터 값이 중복 될수도 있다.

홍길동이라는 이름이 누군가 가지고 있을 수도 있고 점수 또한 중복될 수 있다. 그렇기때문에 student_number라는 고유한 값을 기본키로 지정해주었다.

create 명령어를 사용하면 테이블 생성 뿐 아니라 복제 또한 가능하다.

SQL> create table school2 as select student_number, student_name from school;

school 테이블의 student, number와 student_name attribute를 가져와서 school2 테이블을 만들라는 뜻이다.

as select 뒤에 원하는 attribute를 써주면 복사가 가능하다.

SQL> create table school3 as select * from school;

*은 모든 것을 선택할 때 쓰인다. 따라서 school 테이블을 완전 복사하려면 *만 써주면된다.

SQL> select * from tab;

다음 명령어를 사용해서 내 계정에 어떤 table이 있는지 조회가 가능하다.

SQL> describe school;

위의 명령어는 school table의 attribute를 확인해주는 명령어이다. 

alter 명령어는 각 attribute을 추가/삭제 혹은 형 변환과 이름 변경등의 수정 작업에 사용된다.

SQL> alter table school add student_class char(10);

school table에 student_class라는 attribute를 추가하라는 뜻.

SQL> alter table school drop column student_class;

school table에 student_class attribute를 삭제하라는 뜻.

SQL> alter table school modify student_name varchar2(10);

school table에 student_name의 형을 varchar2로 변환하라는 뜻.

SQL> alter table school rename column student_number to student_id;

school table에 student_number를 student_id로 이름을 바꾸라는 뜻.

위의 명령어를 수행 후 describe 명령어로 바뀐 내용을 조회해보면 내용이 바뀐 것을 확인 가능하다.

drop, truncate 명령어는 테이블을 삭제하는 명령어 이다.

SQL> drop table school2;

school2 테이블을 아예 삭제하는 명령어.

SQL> truncate table school3;

school3 테이블의 내용을 삭제하는 명령어(attribute는 그대로 남겨둔다.)

DML

지금까지 테이블에 관한 명령어를 했다.

DML은 만든 테이블에 데이터를 삽입, 삭제, 수정할 때 사용한다.

우선 만든 테이블에 데이터를 넣을 필요가 있다.

데이터는 다음과 같은 명령어로 삽입이 가능하다.

SQL> insert into school (student_id, student_name, student_score) values('2017111', '홍길동', 330);

school table의 student_id, student_name, student_score에 데이터를 삽입해라 각각의 데이터는 2017111, 홍길동, 330과 같다.

여기서 중요한 것은 student_id와 student_name은 문자형으로 지정해줬으니 데이터에 ' 표시를 해줘야 한다는 것이다.

물론 이런식으로 커맨드라인에 입력하는 것은 너무 느리고 비효율적이다.

이럴땐 편집기를 이용해서 명령어를 적용시킬 수 있다.

1. 편집기에 위와 같은 명령어를 써준다.

2. 확장자를 .sql로 저장한다. 이때 파일 형식은 모든 파일로 지정해주어야한다. 또한 내가 저장한 경로를 기억해준다.

SQL> @c:\db\school_insert.sql

해당 경로를 입력해주면 명령어가 실행되고 DB에 데이터가 삽입이 완료된다.

SQL> delete from school where student_name='홍길순';

다음 명령어는 school 테이블에서 student_name이 홍길순인 레코드를 지우라는 명령어이다.

해당 명령어 사용 시 student_name이 홍길순인 student_id, student_name, student_score를 모두 삭제가 가능하다.

여기서 where이 잘 이해가 안 갈 수도있다. where은 쉽게 말해 조건문정도로 생각하면 된다.

특정 조건에 해당하는 아이에게 해당 명령어가 적용된다.

update 명령어는 데이터의 수정과 갱신을 할 때 사용한다.

SQL> alter table school add student_avg numeric(5);

예시를 위해 student_avg라는 attribute를 추가하자. 해당 attribute는 모든 값이 null 값, 즉 아무것도 지정되어있지않다.

여기서 student_avg의 값을 null값에서 student_score/5의 값으로 바꾸고 싶다.

SQL> update school set student_avg = student_score/5;

위의 명령어를 통해 바꿀 수 있다.

school 테이블의 student_avg의 값을 모두 student_score/5로 지정하라는 뜻이다.

앞서서 몇번 나왔듯이 특정 테이블을 조회하는 명령어가 select이다.

SQL> select student_id, student_name from school;

school table의 student_id와 student_name을 조회하겠다는 명령어이다.

SQL> select * from school;

school table의 모든 attribute를 조회하겠다는 명령어이다. 

select로 조회 시 특정 attribute 명에 별칭(alias)를 주는 것 또한 가능하다.

SQL> select student_id "학번", student_name "이름" from school;

student_id는 학번이라는 별칭으로 조회하고, student_name은 이름이라는 별칭으로 조회하라는 명령어이다.

다음과 같은 결과가 조회되는 것을 확인 가능하다.

DCL

DCL은 데이터베이스에 접근을 제어하거나 앞서 진행했던 DML을 제어하는데 사용한다.

앞서서 봤듯이 데이터베이스에 접근을 제어하기위해서 grant 명령어를 사용하였다.

DML을 제어하는 대표적인 명령어가 commit이다.

우리는 지금까지 만든 테이블에 여러가지 데이터를 삽입, 삭제, 갱신하였다.

그러나 해당 데이터가 실제로 DB에 들어가기위해서는 commit 명령어를 마지막에 꼭 입력해주어야한다.

만약 해당 명령어를 입력하지않고 command를 종료하면 우리가 작업했던 내용이 전부 사라져버린다.

SQL> commit;

만약 지금까지 했던 DML 작업이 맘에 들지 않아 다시 되돌리도 싶을 경우는 rollback 명령어를 사용하면된다.

rollback 명령어를 사용 시 지금까지 작업했던 내용을 되돌릴 수 있다.

여기서 주의할 점은  commit 명령어를 입력했을 시 rollback 명령어를 사용이 불가능하다는 것이다.

SQL> rollback;