02.MySQL 다루기

MySQL의 기본적인 사용법에 대해 알아본다.

 

알아볼 항목

1. MySQL 시작하기
2. MySQL 끝내기
3. 데이터베이스 관련 명령
4. 테이블 관련 명령
5. 테이블에 데이터 만들기, 읽기, 수정하기, 삭제하기
6. sql명령어를 모아놓은 파일 실행하기
7. csv파일 로드하기
8. 한글처리 관련

 

 

 

1. MySQL 시작하기

MySQL을 시작하려면, cmd창에서 mysql.exe를 실행시키면 된다. mysql.exe는 MySQL설치 폴더의 bin폴더 밑에 위치하기에(나의 경우는 c:\mysql5.1\bin), cmd창에서 그 폴더로 이동해서 mysql을 실행하거나, 그 폴더가 path에 지정되어 있다면(설치할 때, path가 자동 지정되도록 체크했으면 OK), 아무 폴더에서나 실행 가능 하겠다.

실행 명령은 mysql -u root -p

 

 

위처럼 mysql명령어가 동작하려면 미리 sql서비스가 시작되어야 한다.

MySQL이 정상적으로 설치되었다면, 컴퓨터가 부팅될 때마다 자동으로 mysql서비스가 시작된다.

mysql 서비스가 시작되어 있는지 확인은 아래 그림 처럼, services.msc를 실행하고, 여러 서비스들중에서 MYSQL서비스를 찾아서 더블클릭 후, 시작되고 있는지를 확인하면 된다.

 

 

 

 

---

2. MySQL 끝내기

MySQL을 끝내는 명령어는 quit (대소문자 구분없다)

 

mysql>quit

 

혹은 \q를 입력해도 마찬가지로 mysql을 벗어날 수 있다. 또는 EXIT를 쳐도 된다.

mysql>\q

 

 

---

 

3. Database관련 명령

데이터베이스관련 4개의 명령어에 대해서 알아본다.

 

• SHOW DATABASES
• CREATE DATABASE [DB 이름]
• DROP DATABASE [DB 이름]
• USE [DB이름]

 

 

MySQL안에 여러 Database를 만들 수 있고, 그 Database안에 여러 Table을 만들 수 있다. 테이블은 여러 Column과 Row를 갖는 데이터를 가질수 있고.

Database > Table > Data

 

현재 MySQL에 어떤 Database들이 존재하는지는 SHOW DATABASES명령어를 통해서 알 수 있다. (명령문의 끝에는 ;를 붙인다)

 

 

위 그림에서 보면, show databases명령에 의해 2개의 데이터베이스가 존재함을 알 수 있다. (이 2개는 MySQL을 설치하면, 기본으로 존재하는 Database들이다.)

 

이제 test라는 Database를 만들어 보자.

Database의 생성: CREATE DATABASE [DB이름]

 

mysql>CREATE DATABASE test;

 

SHOW DATABASES를 해보면 test라는 Database가 새로 생겼음을 알 수 있을 것이다.

 

 

참조로, 데이터베이스를 없애는 명령은 DROP DATABASE이다.

Database의 삭제: DROP DATABASE [DB 이름]

 

데이터베이스를 사용하려면, 어떤 데이터베이스를 사용할 지를 먼저 지정해야 한다.

Database의 사용지정: USE [DB 이름]

 

위에서 만든 test 데이터를 사용하려면 다음과 같은 명령을 치면 된다.

mysql>use test;

이제 test 데이터베이스안에 테이블들을 만들고 그 테이블에 데이터를 넣고 읽는 등의 작업을 해보겠다.

 

 

---

 

4. 테이블관련 작업

테이블 관련 다음 명령어들을 알아본다.

SHOW TABLES

CREATE TABLES [TABLE 이름] ([칼럼이름] [칼럼형태], ...)

DESC [TABLE 이름]

RENAME TABLE [TABLE 이름] TO [새로운 TABLE 이름]

DROP TABLE [TABLE 이름]

 

현재 선택된 Database내에 어떤 테이블이 있는지 확인하는 명령어는 SHOW TABLES이다.

test 데이터베이스를 선택하고 SHOW TABLES하면, 현재는 아무 테이블도 없음으로 보일것이다.

그렇다면 MySQL을 설치하면 기본으로 존재하는 mysql이란 데이터베이스를 선택하고 어떤 테이블이 있는지 보자.

 

 

MYSQL이란 데이터베이스에는 23개의 테이블이 이미 존재함을 알 수 있다.

 

이제 다시 TEST데이터베이스를 선택하고, 이 데이터베이스안에 테이블을 하나 만들어 보자.

만들 테이블은 ID,NAME,AGE라는 3개의 칼럼항목을 가지는 Employee라는 테이블이다.

테이블의 생성: CREATE TABLES [TABLE 이름] ([칼럼이름] [칼럼형태], ...)

 

mysql>CREATE TABLE Employee
(id int AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
age int
);

 

id는 integer타입이고, 자동으로 증가하는 프라이머리 키이며,

name은 20 Bytes 문자열, age는 integer형인,

3개의 칼럼을 가지는 테이블이 생성되었다.

 

만들어진 테이블의 구조를 보려면 DESC 명령을 쓰면 된다.

 

mysql>DESC Employee;

 

 

 

테이블의 이름을 바꿔보자. Employee를 Employees로.

아래와 같이 RENAME TABLE 명령어를 사용하면 된다.

mysql>RENAME TABLE employee to employees;

(mysql에서는 명령어도, 테이블 이름도 대소문자 구분이 없다. 즉, Employee = employee)

 

테이블을 없애는 명령은 DROP TABLE이다.

앞에서 만든 Employees 테이블을 없애고, 다시 만들어 보자.

 

mysql>DROP TABLE employees;

mysql>CREATE TABLE employees
(id int AUTO_INCREMENT PRIMARY KEY,
name varchar(20),
age int
);

 

 

---

테이블에 데이터 만들기, 읽기, 수정하기, 삭제하기

테이블내 데이터의 CRUD(Create, Read, Update, Delete)에 관련된 명령어는 다음과 같다.

 

데이터 생성: INSERT INTO [테이블 명] VALUES(val1, val2, ...)

데이터 읽기: SELECT [항목] FROM [테이블 명] WHERE [조건]

데이터 수정: UPDATE [테이블 명] SET [수정할 칼럼 명]=val1 WHERE [조건]

데이터 삭제: DELETE FROM [테이블 명] WHERE [조건]

 

앞에서 만든 employees테이블에 3명에 대한 데이터를 입력해 보자.

mysql>INSERT INTO employees VALUES(null,'Kim', '21');
mysql>INSERT INTO employees VALUES(null,"Lee", 22);
mysql>INSERT INTO employees VALUES(null,'Park', 23);

employees테이블을 생성할 때 id를 AUTO_INCREMENT로 설정했기에, null을 입력하면 자동으로 id값이 할당된다.

 

테이블에 입력된 데이터를 확인하기 위해서는 select 명령어를 사용한다.

mysql>SELECT * FROM employees;

 

특정한 조건에 대해서만 보고 싶을 때는 SELECT ~ WHERE 명령어를 사용한다.

예를 들어, 22세 이상만 보고 싶을 때는 SELECT * FROM WHERE age >= 20;

 

 

데이터의 수정을 UPDATE를 사용한다.

 

id가 3인 row에 대해서, age를 25로 수정해보자.

mysql>UPDATE employees SET age=25 WHERE id=3;

 

데이터의 삭제는 DELETE FROM를 사용한다.

age가 23이하인 모든 데이터를 삭제하고자 한다면,

mysql>DELETE FROM employees WHERE age <= 23;

 

 

6. sql명령어를 모아놓은 파일 실행하기 

명령어들을 텍스트파일로 만들어 놓고, 이 파일을 MySQL에서 실행시킬 수 있다.

 

실행하는 방법은 두 가지

  방법1. MySQL을 실행하면서 파일이름을 같이 지정

           mysql -u root -p < test.sql

           (이 경우, test.sql파일이 현재 실행폴더에 있어야 함)

  방법2. MySQL이 실행된 상태에서 명령어파일 실행

          mysql>source test.sql

 

실습을 해보자.

• test라는 DB안에 employees라는 테이블이 있다고 가정(없으면, 위 쪽 내용을 참조해서 만든다.)
• 아래내용으로 test.sql이라는 텍스트파일을 만든다.

use test;
show tables;
select * from employees;

• test.sql이 들어있는 폴더에서 mysql에 들어간다. (mysql -u root -p)
• mysql>source test.sql
  

 

 

7. csv파일 로드하기

엑셀로되어 있는 데이터를 MySQL로 옮기려면, 먼저 엑셀 데이터를 콤마(,)로 구분되는 텍스트파일인 CSV형태로 바꾸고, 이 텍스트파일을 MySQL에 로드하면 된다. 물론, 엑셀 데이터구조와 MySQL의 테이블구조는 같아야 한다.

   LOAD DATA INFILE <파일이름>

   INTO TABLE <테이블 명>

   FIELDS TERMINATED BY ','

   LINES TERMINATED BY '\n'

실습을 해보자.

위에서 만들어놓은 employees테이블에, 엑셀로 만든 데이터를 한번에 올려본다.

 

엑셀로 만든 데이터는 다음과 같다고 하자.

 

 

CSV파일로 저장하면, 아래와 같은 내용의 텍스트파일이 생긴다. (employees.csv)

5,AAA,12
6,BBB,31
7,CCC,32

 

이 CSV파일을 MySQL의 employees테이블에 넣는 sql문을 만들어보자. (load_csv.sql)

use test;

 

load data infile 'c:/mysql_test/employees.csv'
into table employees
fields terminated by ','
lines terminated by '\n';

여기서 파일경로는 절대경로로 적어줬고, 폴더사이에 슬래시(/)로 구분되어 있는 것에 주의.

 

이제 MySQL에 접속해서, 이 load_csv.sql파일을 실행하고, employees에 제대로 들어갔는지 확인하자.

mysql>source load_csv.sql;

mysql>select * from employees;

 

 

 

위 그림같이 나오면 OK

 

ID가 자동으로 지정되게 하려면?

위 예에서는 ID를 5,6,7로 수동으로 부여했다. 그러나, 실제 상황에서는 ID가 자동으로 증가하면서 배정되게 하고 싶고, 그래서 employees테이블을 만들때 AUTO_INCREMENT 되도록 설정한바 있다. 그렇다면, 텍스트파일로 한번에 로드할 때 ID가 자동으로 증가하게하려면 어떻게하면될까? 답은 ID부분을  '\N'으로 처리하면된다.

위의 예에서 ID부분을 \N으로 처리하면 employees.csv파일은 다음과 같은 형태일 것이다.

\N,AAA,12
\N,BBB,31
\N,CCC,32

이렇게해서 넣으면, ID부분이 자동으로 증가하면서 들어간다.

 

헤더부분을 제외하고 넣으려면?

만약 엑셀파일에서 아래와 같이 칼럼헤더가 있고, 이를 csv로 바꿨을 때, 첫번째 줄이 헤더일때 어떻게 입력할까?

 

 

이 경우는, load data 하는 SQL명령문에서 "IGNORE 1 lines" 옵션을 추가하면 된다.

즉, SQL문을 다음과 같이해서 수행하면 된다.

use test;

 

load data infile 'c:/mysql_test/employees.csv'
into table employees
fields terminated by ','
lines terminated by '\n'

ignore 1 lines;

 

8. 한글 처리 관련

한글처리관련해서 복잡하게 생각할 것 없이 다음 두 가지만 생각하자.

• MySQL의 기본 character set은 utf-8로 하자
• Windows7 같은데서 mysql콘솔을 수행할 때는 euckr로 임시 세팅하자(set character set  euckr;)

MySQL이 지원되는 대부분의 서버는 utf-8을 쓴다. 따라서, MySQL의 기본 캐릭터는 uft-8로 하자.

설치할 때 utf-8이 디폴트이기에 utf-8로 다시 설정하지 않아도 되지만, 만약 다른 character set으로 지정되어 있는 상태에서 utf-8로 바꾸려면 다음과 같이 하면 된다.

• mysql에서 현재 설정되어 있는 character set 확인 ; status라는 명령어를 사용하면, 현재 설정된 character set을 확인할 수 있다.
  mysql>status;
• utf-8로 설정하기 위해서는 my.int파일(c:\dev\mysql5.1\my.ini)에서  'default-character-set'과 'character-set-server'의 값을 utf8로 지정한다.

default-character-set=utf8

...

character-set-server=utf8

 

윈도우즈의 cmd창에서 mysql콘솔을 띄어 사용하는 경우, mysql의 테이블 내용을 볼 때, 테이블내 한글데이터가 uft8로 되어 있고, 윈도우의 cmd창은 uft8이 아니기에, 테이블의 내용을 보려할 때 한글이 깨어져서 보인다.

이때는, 현재 cmd창을 통해서 띄원 sql 클라이언트의 character set을 euckr로 바꿔주면 된다.

(아래 그림은 charactr set을 euckr로 바꾼 후, status를 해본 화면이다. client의 character set이 euckr로 바껴있다.)

 

 

 

 

-끝-