3 mysql 데이터 타입 및 필수 기능

앞에서 설명한 학사 관리 데이터 베이스를 실제 mysql 상에서 구현하고 이에 필요한 필수적인 기능들에 대해 설명한다.

먼저 mysql을 이용하여 데이터 베이스를 생성하기 위해서는 mysql에 데이터 베이스를 생성해야 한다.

하나의 mysql 에는 여러개의 데이터 베이스가 존재할 수 있으며, 각각의 데이터 베이스 이름은 사용자가 지정할 수 있다.

데이터 베이스 생성

create database haksa;

확인

show databases;

db변경

use haksa;

인사 테이블 insa 생성 및 데이터 입력

create table insa(bunho int(1) auto_increment, name char(8) not null, e_name char(4) not null, town char(6) not null, primary key(bunho));

데이터 삽입

insert into insa values('1','홍길동','Hong', '순천');

insert into insa values('2','제갈공명','Je', '여수');

insert into insa values('3','순자','Soon', '부산');

insert into insa values('4','이순신','Lee', '여수');

확인

select * from insa;

commit/rollback 작업

commit : 변경된 데이터를 데이터베이스에 적용시킨다.

rollback: 변경된 데이터를 취소 시킨다. 직전에 Commit 이 수행된 지점까지 취소시킨다.

주의 사항으로 mysql은 명령어를 실행하면 자동으로 Commit을 하게 되어 있다.

우선 AutoCommit을 하지 않도록 한다.

set autocommit = 0;

insa 테이블의 내용 변경 : 번호 4번도시를 한산도로 변경

update insa set town = '한산도' where bunho = 4;

변경된 인사테이블 질의

"select * from insa;

"

변경된 데이터 복구 작업 : rollback

rollback;

확인

select * from insa;

타운이 여수인 데이터를 '대구'로 변경

update insa set town = '대구' where town = '여수';

인사테이블 내용 데이터베이스에 저장

commit;

데이터 복구해보기

rollback

복구가 안됨

savepoint/truncate 작업

savepoint는 변경된 지점의 위치를 저장한다.

savepoint로 저장점을 저장하고 insert, delete, update 작업을 수행 후 rollback to 저장점을 수행하면 그 위치까지 다시 복구 시쿨 수 있다.

번호 2의 도시 town 을 '' 여수로 변경

update insa set town='여수' where bunho = 2;

세입이브 포인트 지정

savepoint aa;

번호 3번행 삭제 delete 작업

delete from insa where bunho = 3;

insa 테이블 savepoint aa 까지 복구

rollback to aa;

질의

select * from insa;

truncate 작업 : insa 테이블의 삭제 처리 ( 모든 행이 삭제 처리됨)

truncate table insa;

rollback

복구되지 않음~!

sql 데이터 형

sql 에서 사용되는 데이터 타입을 설명한다. 데이터베이스의 목표는 데이터를 효율적으로 관리하고 이용할 수 있도록 하는 것이므로 데이터 베이스를 이해하는 데 있어 데이터 타입의 이해는 반드시 필요하다.

1 숫자 데이터 형

정수 데이터 형 (int)

mysql 은 정수형 데이터를 저장하기 위해서 int라는 데이터 형을 제공한다.

0, 음수, 양수를 저장한다.

실수형 데이터 float

소수점을 포함하여 값을 저장할 수 있다.

2 문자 데이터 char, varchar blob

char 데이터 형

1바이트에서 255바이트까지의 고정 길이 문자열을 저장하고, 정의된 저장공간보다 입력데이터가 짧으면 나머지 공간은 공백으로 채워지고, 정의된 길이 보다 입력 데이터가 길면 길이에 맞게 잘린 데이터가 입력 된다.

그러므로 테이블 생성시 저장할 데이터의 최대 크기로 정의해야만 데이터 손실을 막을 수 있다.

varchar 데이터형

char와 비슷하지만  char 는 정의된 저장공간 보다 긴 문자열이 입력 되면 에러를 발생시키지 않고 초과되는 데이터를 잘라서 입력하지만,  varchar에서는 에러 값을 리턴한다.

최대로 정의할 수 있는 데이터 길이는 255바이트까지 저장할 수 있고, 메모 등의 다양한 길이의 데이터에 적절하고, 가변적인 길이의 문자열을 저장하기 때문에, 문자열을 저장하기 위하여 선호되는 데이터형이다.

하지만 자료구조의 원리로 볼때 char가 varchar보다 검색 속도가 훨씬 빠르다.

blob , text  데이터형

blob과 text는 65,535 이상의 거대한 텍스트 데이터를 저장할 때 사용하면 된다. 다면 blob은 검색시 대소문자를 구분하고 , text는 대소문자의 구분 없이 검색할 수 있다.

날짜 데이터형

mysql은 날짜 및 시간 데이터를 저장하기 위해서, date 데이터 형을 제공한다.

사용자들은 sysdate라는 함수를 사용해서 현재 os날짜를 조회 할 수 있다.

예) select now();

select 는 산술 계산의 결과나 날짜 등을 볼 수 있다

위의 질의 결과로 얻은 날자는 보통 우리 나라 사람들이 사용하는 년 월 일 형식으로 나타난다. 

date 데이터 형에는 세기 , 년  월, 일 시간 분 초에 대한 정보가 포함되어 있다.

바이너리

음성 , 화상이미지, 동영상과 같은 데이터를 저장하기 위해서 바이너리 데이터형으로 raw와 long raw 데이터형을 사용하고 제약점으로는 내장함수를 사용할 수 없다.

raw 데이터형 데이터를 255바이트 까지 수용할 수 있으나 저장공간의 제한점 때문에 많이 사용하지 않는다.

long raw 데이터형~

2진형 데이터를 2gb 까지 수용할 수 있다.

blob 데이터형

이진형 데이터를 4gb까지 수용할 수 있다.

학사관리 예제 만들기~

질의 테이블

1.성별이 남자인 각 학생의 학번, 이름, 영문이름, 학년, 주민등록번호를 영문이름 순서로 출력하라

select stu_no, stu_name, stu_ename, grade, id_num from student

where SUBSTRING(id_num,8,1) =1

order by stu_ename;

성별이 남자인 학생을 찾는 조건은 where substring(칼럼, 시작위치, 몇번째)  = 1 이며, 이 함수는 id_num 의 8번째 칼럼 1자리 만큼을 선택하기 위한 함수다.

각학생의 학번, 이름, 영문이름, 학년, 주민등록번호 를 학적 테이블로 부터 구하는 명령문으로써, 정렬은 영문 이름의 순서 order by stu_ename 로 이루어진다.

from 다음에는 질의를 원하는 테이블 이름을 지정하며 where절 뒤에는 원하는 조건을 적으면 된다.

select 는 탐색하고자 하는 열을 선택할 수 있도록 하고, order by  다음에는 출력될 데이터가 정렬되는 방법을 지정한다.

2.학년이 1학년이고 성별이 남자인 각 학생의 학번과 이름을 출력하는데, 출력 순서는 학번 내림차순이다.

select stu_no, stu_name 

from student

where substring(id_num,8,1) = 1 

and grade = 1 

order by stu_no desc;

행의 갱신과 삭제

교과목 중 운영체제의 생성년도를 2006년도로 변경하라

update subject set create_year = '2006' where sub_name = '운영체제';

조회

select * from subject;

삭제처리 작업은 테이블의 내용을 제거할 때 행 단위로 이루어진다. 

만약에 sample 이라는 테이블의 모든 행을 삭제한다면 다음과 같다

delete from sample;

앞의 예제는 sample 테이블의 모든 행을 삭제처리 하나 테이블 자체가 없어진 것은 아니다.

테이블의 명세표, 인덱스, 부여된 권한 등 환경테이블 정보 자체를 없애버릴 때는 drop 명령어를 다음과 같이 사용한다.

drop table sample;

과목명이 uml 인과목을 삭제하라

delete from subject where sub_name = 'UML';

원래대로 변경

교과목 중 운영체제의 생성년도를 2002년으로 변경해라

뷰

테이블에는 데이터를 가지고 있는 행이 실제로 저장되어 있는데, 많은양의 기억 장소를 차지 하기 때문에 많은 테이블은 많은 기억 공간이 요구된다

반면 VIEW 테이블은 사용자가 여러 테이블에 있는 데이터를 보기 편한 새로운 테이블을 만들 수 있는 기능이다.

따라서 뷰 테이블은 데이터가 실제 저장되는 테이블이 아닌 사용자에게 보여주기 위한 뷰 전용 테이블이라고 할 수 있다.

뷰 테이블은 필요에 따라 사용자가 재 정의하여 생성할 수 있으며 어떤 기억공간을 차지 하지 않는다.

따라서 뷰는 가상 테이블이라고도 한다

뷰는 실제 데이터 행을 가지고 있는 것 처럼 동작하지만 데이터 행을 가지지는 않는다.

3.학생 테이블의 학번, 이름 , 출생년도 , 나이를 출력하라 

SELECT stu_no, stu_name,  substring(id_num, 1,2) from student;

SELECT stu_no, stu_name, year(now()) - (year(now()) - substring(id_num, 1,2)) from student;

select year(now());

2번째 방법

select stu_no, stu_name, birth_year ""출생년도"", year(now()) - birth_year + 1 ""나이"" from student;

 " " 는 주석이고 now() 함수는 현재의 년, 월, 일, 시간 을 나타내는 함수 이고 year() 함수는 년도를 나타내는 함수이다.

따라서 now함수로 현재의 날짜를 추출한 다음 다시 year 함수로 년도만을 추출 한다.

나이는 현재의 년도에서 출생년도를 감산하여 1을 더한 값으로 얻을 수 있다.

예를 들면 현재의 날짜가 2007년 7월 25일 일때 1981년생의 나이를 계산하면 다음과 같다

나이 = 현재 연도 - 출생년도 + 1 = 2017 - 1981 + 1

여기서 나이를 필요로 하는 경우에 매번 계산을 별도로 처리해 주어야 하는 불편을 겪게될 것이다.

이러한 데이터는 student 테이블의 출생년도를 계산하여 ages 라는 view 테이블을 작성하여 다음부터는 간단하게 검색하여 사용할 수 있다.

학적 테이블의 학번, 이름 나이로 구성된 ages 뷰테이블을 생성하라

create view ages(stu_no, stu_name, age) as 

select stu_no, stu_name, year(now()) - birth_year +1 from student;

select * from ages;

ages 뷰의 내용은 데이터베이스에 저장되지느 않지만 select 명령문이 실행되는 순간 유도된다

따라서 뷰의 사용은 기억공간을 사용하는 부가적인 비용은 전혀 없으며, 다른 테이블에 이미 저장된 데이터만으로 구성할 수 있다.

언제 사용하나?

반복되는 명령문이나 루틴을 간단히 사용하고자 할때

테이블의 출력방법을 재구성 하고자 할때

여러 단계에서 select 명령문이 사용될때

데이터를 보호하고자 할때