MySQL TIP

1. Backup & Restore
1.1 Backup
1.2 Restore
1.2.1 Bulk Load

2. image upload
3. CREATE TABLE 구문 보기
4. 한번 쿼리로 여러 개 컬럼 UPDATE 하기
5. 설치 후 계정 정리하기
6. 주민번호로 연령별 통계 구하기
select (substring(substring(now(),1,4)-concat(ceiling(substring(ResRegNum2,1,1)/2)+18,substring(ResRegNum1,1,2)),1,1)*10) as age,count(ID) from member group by age;

1. 단원은 데이터베이스 마이그레이션의 핵심 사항만을 정리한 것이다.
기초적인 문법은 생략을 한다.

1.1 Backup
1.1.1 탭으로 분리된 데이터 만들기(Export with tab separated data)
mysql> select * into outfile '/tmp/babo.txt' from info_dong;
데이터베이스에서 자료만 뽑아서 사용할 때가 많다. 특히 마이그레이션할 때는 필수적이라고도 할 수 있다. 위의 방법은 간단하게 탭으로 분리된 데이터를 만드는 구문이다.

이렇게 하면 필요한 컬럼만 뽑아서 탭으로 분리된 데이터로만 저장이 된다. Mysqldump와 같이 테이블 스키마가 포함되어 있는 스크립트를 전부 저장할 필요도 없이 데이터만 저장하여 다른 DB에서 이용할 수 있게 한다.

1.2 Restore
1.2.1 Bulk Load
구문
LOAD DATA INFILE ‘파일명’
[REPLACE | IGNORE]
INTO TALE tbl_name
[FIELDS
[TERMINATED BY ‘\t’] 또는 [TERMINATED BY ‘,’]
[ENCLOSED BY ‘”’];
]
1.2.2 Tab으로 분리된 데이터 올리기
Mysql>load data infile “c:/mysql/book1.txt” replace into table dept teminated by ‘\t’;
unique 컬럼에 중복된 열이 있을 때는 replace를 하면 덮어쓰고, ignore를 하면 무시된다.

1.2.3 comma(,)로 분리된 데이터 올리기
Mysql>load data infile “c:/mysql/book1.txt” into table dept teminated by ‘,’;

1.2.4 기타 문장부호가 들어간 데이터 올리기
“강민주”,”3”
“강병기”,”34”
“이지연”,”31”
과 같은 형식으로 되어 있다면
Mysql> load data infile “c:/mysql/book1.txt” into table dept
FIELDS teminated by ‘,’ ENCLOSED BY’”’;

1.2.5 필드인 맨 처음 라인을 제외하고 입력할 때
Mysql> load data infile “c:/mysql/book1.txt” into table dept
FIELDS teminated by ‘,’ ENCLOSED BY’”’;
IGNORE 1 LINES;
2. 그림 올리기
CREATE TABLE images (
id NOT NULL auto_increment primary key,
image blob,
description varchar(200)
);
02.jpg란 파일을 올려보자 ….
Mysql>INSERT INTO TABLE(image,description) VALUES(LOAD_FILE(‘02.jpg’), “2번”);
이 파일을 수정하려면
Mysql>UPDATE images set image=LOAD_FILE(“/tmp/picture”) where id=1;

3. CREATE TABLE 구문 보기
info_device란 테이블이 존재하면
Mysql>show create table check\G

4. 한번 쿼리로 여러 개 컬럼 UPDATE 하기
dept란 테이블이 존재한다면
Mysql>desc dept;
Field Type Null Key Default Extra
--------- ----------- ------ ------ ------- ------
dept_no int(11) PRI 0
dept_name varchar(10)
zipcode varchar(7)
address varchar(30)
local_no char(3)
tel1 int(11) 0
tel2 varchar(4)

Mysql>select * from dept;
dept_no dept_name zipcode address local_no tel1 tel2
------- --------- ------- ----------------------- -------- ------ ------
10 관리부 135-240 123-45번지 우리빌딩 3층 02 504 5185
20 전산부 135-240 123-45번지 우리빌딩 4층 02 576 0186
30 영업부 135-240 123-45번지 우리빌딩 1층 02 678 1234
40 생산부 150-040 17가 12번지 (주)우리 02 553 4980

20번의 전산부를 15번으로 수정하고 부서명, 우편번호도 수정을 해준다.

잘못된 결과를 되돌림하기 위해 트랜잭션을 걸어준다.
Mysql>start transaction;
Mysql>update dept set
dept_no =’15’,
dept_name=’전산실’,
zipcode=’145-150’
where dept_no=20;

Mysql>select * from dept;
dept_no dept_name zipcode address local_no tel1 tel2
------- --------- ------- ----------------------- -------- ------ ------
10 관리부 135-240 123-45번지 우리빌딩 3층 02 504 5185
15 전산부 145-150 123-45번지 우리빌딩 4층 02 576 0186
30 영업부 135-240 123-45번지 우리빌딩 1층 02 678 1234
40 생산부 150-040 17가 12번지 (주)우리 02 553 4980

이상이 없으면 commit; 하고 이상이 있으면 rollback;을 해준다.

5. 설치 후 계정 정리하기
Mysql>use Mysql;
Mysql>show tables;
area
columns_priv
db
func
host
tables_priv
user

계정 정보는 user 안에 들어가 있다.

Database changed
Mysql>update user set password=password(‘pppp’) where user=’root’;
Mysql>delete from user where user=’ ‘;
Mysql>delete from user where user=’‘;
Mysql>flush privileges;


6. 업그레이드
Mysql은 비교적 잦은 판올림이 되어 종종 업데이터 해주어야 하는 경우가 있다. 우선
ⓐ mysql/data 디렉토리를 다른 곳에 통째로 복사해 둔다.
ⓑ 실행 창에서 –-remove 옵션을 사용해 서버를 삭제한다. 검은 화면이 보였다가 사라진다.


ⓒ [제어판]-[프로그램 추가/삭제]에서 “MySQL server”를 선택하고 제거한다.


ⓓ 윈도우를 재부팅한다.
설치 디렉토리인 c:\mysql 디렉토리를 삭제하고 c:\WINNT\my.ini 도 삭제를 한다음 재부팅한다.


재설치
1. Mysql 설치
2. 설치 후 작업
3. 환경설정
아래의 winmysqladmin.exe를 실행한다.

my.ini Setup에서 language= 부분을 수정하고 왼쪽의 Save Medification을 클릭한다.
이 작업을 마치면 C:\Windows(또는 WINNT) my.ini가 생성된다.(LINUX에서는 my.conf) 또한 서비스에 가서 확인해 보면 Mysql이 시작됨으로 설정되어 있음을 알 수 있다.


패스워드를 가지지 않으면 권한이 제한되므로 이제는 계정에 접속을 해서 패스워드를 변경해 줄 일만 남았다.

4. 계정변경
c:\Inetpub\mysql\bin>mysql -uroot –p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.0-alpha-max-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql # 데이터베이스 선택
Database changed
mysql> UPDATE user SET password=PASSWORD('alswn')
-> WHERE user='root';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges; # 환경갱신
Query OK, 0 rows affected (0.00 sec)
mysql> quit # 재접속을 위한 빠져나가기
Bye
c:\Inetpub\mysql\bin>mysql -uroot –p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.0-alpha-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use mysql
Database changed
mysql> show tables;

6 rows in set (0.00 sec)

mysql> SELECT host,user,password
-> FROM user
-> WHERE user='root';
+-----------+------+---------------+
| host . | user | password |
+-----------+------+----------------+
| localhost . | root | 4d6c7dd77f752921 |
| % ...| root | 4d6c7dd77f752921 |
+-----------+------+----------------+
2 rows in set (0.01 sec)

mysql>


6. 주민번호로 연령별 통계 구하기
SELECT (substring(substring(now(),1,4)-concat(ceiling(substring(ResRegNum2,1,1)/2) + 18,substring(ResRegNum1,1,2)),1,1)*10) AS age,count(ID)
FROM member
GROUP BY age;

ResRegNum1 주민번호 첫 6자리
ResRegNum2 주민번호 두 7자리

ceiling(substring(ResRegNum2,1,1)/2)+18
이 부분에서 먼저 주민번호 7자리 중에서 첫번째 1자리를 가져 옵니다. 이게 1900년에 태어났는지 그 이후에 태어 낫는지 알수 있는 키가 되기 때문이죠.그리고 그걸 2로 나눈 몫을 무조건 올려주면 정수가 나오는데 이거 에다가 18 을 더해 주면 몇년도에 태어 났는지 알수가 있게 되는겁니다..한번 자기 주민번호 가지고 계산 해보시길...
substring(ResRegNum1,1,2)
이걸로 이제 나머지 년도를 구해서 concat로 더해 주면 정말 그 사람이 태어난 년도가 나오게 되겠죠...여기까진 태어난 년도 계산이고...
substring(now(),1,4)
이건 지금 년도 얻어오고 아까 그년도를 뺀다음에 다시 앞의 글자 하나 가져 와서 10을 곱하면 비로소 연령대가 나오게 되는겁니다