MySQL(데이터베이스 생성 및 관리)

데이터베이스 생성 / 삭제

데이터베이스 생성하기

create database `데이터베이스이름` [default charset `utf8`];

- 기존에 존재하는것과 중복 불가.
- 기본 charset는 utf8, euckr 등의 값을 지정.
- MySQL은 라틴어가 기본언어로 저장되서 charset를 하지않으면 한글깨짐.

데이터베이스 삭제

drop databaes `데이터베이스이름`;

- 존재하지않는 데이터베이스 이름을 지정한 경우는 에러 발생.
- 삭제한 데이터베이스는 복구불가능하니 신중히 할 것.

예제 1)

데이터 베이스 생성

예제 2)

데이터 베이스 삭제

테이블 생성하기(1)

create table `테이블이름`(
`컬럼이름`데이터타입 [not bull | null] [AUTO_INCREMENT][comment='컬럼설명'],
`컬럼이름`데이터타입 [not bull | null] [AUTO_INCREMENT][comment='컬럼설명'],
`컬럼이름`데이터타입 [not bull | null] [AUTO_INCREMENT][comment='컬럼설명'],
[,primary key(`기본키컬럼이름`)] )
[engine=InnoDB][default charset=`utf8`] [comment='테이블 설명'];

• create table 테이블이름( ) engine = InnoDB default charset 'utf8'
  • 괄호 안에 테이블이 포함할 컬럼의 이름과 데이터 타입쌍을 콤마로 구분하여 명시.
  • engine = InnoDB -> Oracle이 MySQL을 인수한 후에 추가한 저장형식으로 참조키나 프로시저 등의 고급기능 사용 가능.
  • defalute charset = utf8 -> 기본 문자열 저장 형식을 UTF8로 설정 (DB에서는 '-'안씀 ex) utf-8)
  • 테이블 이름이나 컬럼이름에는 역따옴표를 적용. (필수아님)
  • comment는 홑따옴표를 적용.

데이터 타입

구분	데이터 타입	설명
문자 문장형	CHAR(n)	글자수가 최대 n개인 고정 길이 문자열 저장 -> 한글, 영문 구분 없이 한 글자로 취급함. Ex) char(10)이라 지정된 컬럼의 경우 -> “mysql”이라 저장하면 모자란 5글자는 공백처리되어 “mysql ”이라 저장된다. -> “mysql database”라 저장하면 초과되는 4글자는 저장되지 않는다.
	VARCHAR(n)	글자수가 최대 n개인 가변 길이 문자열 저장
	TEXT	최대 65535 길이의 문자열을 저장한다. (ex: 게시판의 본문 등)
숫자형	INT	정수 형태의 데이터를 저장한다. (-2147483648 ~ 2144483647)
	FLOAT(m,n)	실수 형태의 데이터를 저장한다. M은 전체 자리수(소수점도 자리수에 포함됨), n은 소수점 이하의 자리수.
날짜형	DATE	날짜 형식의 데이터를 저장한다. (YYYY-MM-DD)
	DATETIME	날짜+시간 형식의 데이터를 저장한다. (YYYY-MM-DD HH:MI:SS)
기타	BLOB	최대 65535 길이의 바이너리를 저장한다.
	ENUM(‘A’,’B’,’C’, … ‘Z‘)	저장 가능한 값을 A, B, C … Z로 제한한다.

테이블 생성하기 (2)

- 데이터 타입을 결정한 후에는 Null 데이터의 허용 여부를 명시

• Null : Null 데이터 허용 -> 미필수 입력 항목에 사용.
• not Null : Null 데이터 허용 X -> 필수 입력 항목에 사용.

- 기본키 지정방법

1) 컬럼을 나열할때 Primary Key 명시.

create table`test`(
`column1`int not null primarykey,
`column2` varchar(10) not null )
engine=InnoDB default charset=`utf8`;

2) 컬럼의 목록을 모두 나열한 후 Primary Key 명시.

create table`test`(
`column1`int not null,
`column2` varchar(10) not null,
primarykey (`column1`) )
engine=InnoDB default charset=`utf8`;

테이블 생성하기(3)

- 자동증가 일련번호의 설정
\ - 각 데이터 행을 고유하게 구분하기 위해 설정하는 Primary Key가 int 형인 경우, AUTO_INCREMENT 옵션을 추가하면 데이터 insert 시에 자동으로 1씩 증가된 값 저장.

create table`test`(
`column1`int not null primary key auto_increment,
`column2` varchar(10) not null )
engine=InnoDB default charset=`utf8`;

create table`test`(
`column1`int not null auto_increment,
`column2` varchar(10) not null,
primarykey (`column1`) )
engine=InnoDB default charset=`utf8`;

예제 1)

id (int, PK, 자동증가), memo(varchar(255)), reg_date(datetime)의 컬럼을 갖는 테이블 생성

테이블 삭제

drop table `테이블이름`;

- 존재하지 않는 테이블을 삭제하면 에러발생.
- 삭제된 테이블은 복구불가능 하니 주의할 것.

예제 1)

앞에서 만든 테이블 삭제

데이터베이스 관리

테이블 수정하기

테이블 구조 변경하기

alter table `테이블이블` 명령어;

명령어의 종류

명령어	설명
rename	테이블의 이름 변경
add	칼럼이나 제약조건 추가
change	칼럼 수정
drop	칼럼이나 제약조건 삭제

예제 1)

'mytable'의 이름을 'mytest'로 변경

예제 2)

datetime 형식의 edit_date 컬럼을 not null로 추가

예제 3)

add 명령어와 after 옵션을 사용하여 특정 컬럼 뒤에 새로운 컬럼 추가

예제 4)

'cloumn2' 컬럼 수정. 컬럼명 -> 'comment' / 데이터타입 -> text / null 허용.

예제 5)

'writer' 컬럼의 이름을 유지한 상태에서 데이터 타입만 변경.

예제 6)

'edit_date' 컬럼 삭제.

예제 7)

'column1' 컬럼의 기본키 속성 제거.
기본키는 auto_increment 속성이 있으면 제거가 안되기 때문에 속성을 먼저 제거한다.

예제 8)

'column1' 컬럼에 기본키 설정, auto_increment 속성 추가.
제거의 역순으로 기본키를 추가한후 auto_increment 속성을 추가하면 된다

테이블의 참조관계 이해하기

- professor 테이블에서 각 교수의 소속학과를 학과번호는 department 테이블에서 존재하는 학과에 대한 deptno값이어야함.
- 테이블A의 컬럼a에 저장 될 데이터가 반드시 테이블B에 저장되어 있는 값 중의 하나이어야 할 경우,
"테이블A는 테이블B를 참조한다" 라고 하며 컬럼 a를 테이블B에 대한 참조키(혹은 외래키 / foreign key)라고 함.

professor 테이블은  department 테이블의 deptno컬럼을 참조하고 있으며 여기서 deptno 컬럼은 참조키가 된다.

도형으로 참조 관계 표현

- 테이블A의 primary key를 테이블B의 primary key가 참조하는 경우
- 각 테이블에 고유하게 존재하는 값끼리 참조하므로 '**1 : 1'** 관계 성립

- 테이블A의 primary key를 테이블B의 일반 컬럼이 참조하는 경우
- 하나의 A를 하나이상의 B가 참조하는 '**1 : n'** 의 관계 성립.

- 예) 하나의 게시글 안에 여러개의 댓글.

참조키 설정하기

create table `테이블이름`(
`컬럼이름`데이터타입 [notnull | null][auto_increment][comment '컬럼설명'],
`컬럼이름`데이터타입 [notnull | null][auto_increment][comment '컬럼설명'],
[,primary key(`기본키컬럼이름`)]
[,foreign key(`참조키컬럼이름`) references `대상테이블명` (`대상테이블의 컬럼명`) ] )
engine=InnoDB default charset=`utf8` comment '테이블설명';

- 참조키 설정시 다음의 제약조건 생성됨.(참조 무결성 제약)

• 다른 테이블의 참조를 받고 있는 데이터는 참조하고 있는 데이터가 먼저 삭제 될 수 없음
• 다른 테이블의 데이터를 참조하는 컬럼은 해당 컬럼의 데이터 중 하나만 저장해야 한다.
• 다른 테이블의 데이터를 참조하려 할 때 없는 데이터는 참조 불가.

예제1)

학생테이블의 studno 컬럼을 참조하는 성적테이블 만들기

데이터베이스 백업 / 복구

데이터베이스 백업

MySQL의백업과 복구는 명령프롬프트에서MySQL의 실행파일이 위치하는 경로로 이동후 수행

백업하기

MySQL 접속상태가아닌 일반 명령프롬프트에서 실행해야함.

mysqldump -u계정아이디 -p 백업할DB이름 > 백업파일의경로

복구하기

백업DB를 담을 새로운 데이터베이스를 생성해 놓은 상태에서 진행.
백업과 마찬가지로 MySQL 접속상태가아닌 일반 명령프롬프트에서 실행해야함.

mysql -u계정아이디 -p 복구할DB이름 < 백업파일의경로