dbms. mysql 관련 정리

예전에 정리했던 내용입니다.

DDL


컬럼 추가
ALTER TABLE 테이블명 ADD COLUMN 컬럼명 타입
컬럼타입 변경
ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 타입
컬럼이름 변경
ALTER TABLE 테이블명 CHANGE COLUMN 원래컬럼명 변경할컬럼명 변경할컬럼타입
컬럼 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명
Primary Key 생성
ALTER TABLE 테이블명 ADD PRIMARY KEY ( 컬럼명1, 컬럼명2, ... )
Primary Key 제거
ALTER TABLE 테이블명 DROP PRIMARY KEY;
테이블명 변경
ALTER TABLE 테이블명 RENAME 새테이블명

사용자



root 계정 비밀번호 생성
mysql>set password FOR root@localhost=password( '비밀번호' );
또는
#/usr/bin/mysqladmin -u root password '비밀번호'
anonymous 유저 삭제
mysql>delete FROM mysql.user WHERE user = '';
외부에서 접근할수 있게 하는 경우
mysql>GRANT ALL PRIVILEGES ON test_db.* TO test_id@'%' IDENTIFIED BY 'test_passwd';
내부에서만 접속할수 있게 하는 경우
mysql>GRANT ALL PRIVILEGES ON test_db.* TO test_id@localhost IDENTIFIED BY 'test_passwd';
mysql 재시작
#mysqladmin -uroot -ptest_passwd reload 
데이터 백업 및 복구



mysqldump 명령을 이용한 sql 데이터 반출

기본적인 사용방법은 아래와 같다
형식
mysqldump -u사용자명 -p DB명 > 파일저장경로
예제
mysqldump -uroot -p rootdb > /home/rootdb.sql

sql 파일 실행

특정 텍스트파일에 담겨있는 복수개의 쿼리를 다음의 방법으로 실행시킬 수 있다.
mysql -u사용자명 -p DB명 < sql파일경로

csv 형식의 데이터 배치인서트

아래와 같이 행과 열을 나누는 구분자를 지정해주면, 해당 기준으로 데이터를 지정 테이블에 인서트한다, 고 한다.
테스트해보지 않았다.
예제
LOAD DATA INFILE '/home/gomdolinara/zip.csv' 
INTO TABLE zip
FIELDS terminated BY ',' 
LINES terminated BY '\n';

문자셋 설정



기본 문자셋 설정

/etc/my.cnf 파일에 default-character-set 항목을 설정한다
[client] 
default-character-set=utf8 

[mysqld] 
default-character-set=utf8 

[mysqldump] 
default-character-set=utf8 

UTF-8 문자셋을 사용하기 위한 JDBC Driver URL

jdbc:mysql://localhost:3306/dbname?useUnicode=true

현제 설정된 문자셋을 조회하는 쿼리

현제 문자셋 정보 보기 
mysql>show VARIABLES LIKE 'c%'; 
 
/** 아래와 같은 정보를 얻을 수 있다 
character_set_client : utf8 
character_set_connection : utf8 
character_set_database : utf8 
character_set_results : utf8 
character_set_server : utf8 
character_set_system : utf8 
character_sets_dir : /usr/share/mysql/charsets/ 
collation_connection : utf8_general_ci 
collation_database : utf8_general_ci 
collation_server : utf8_general_ci 
**/

컬럼별 문자셋을 지정하며 테이블 생성

mysql>create TABLE test ( test_column varchar(20) collate utf8_general_ci ); 

필드의 문자셋 변경

mysql>alter TABLE test MODIFY test_column BINARY(100); 
mysql>alter TABLE test MODIFY test_column varchar(20) collate euckr_korean_ci;
binary 타입으로 바꾸는 중간과정을 거쳐야 한다

하위버전 MySQL 데이터의 마이그레이션


쿼리 이슈



select 쿼리시 조건주기

  • if 사용
SELECT
ADate,
Sum( Amnt ) AS Total,
Sum(IF( Pay = 1, Amnt, 0 ) ) AS Pay1,
Sum(IF( Pay = 2, Amnt, 0 ) ) AS Pay2
FROM ATable
GROUP BY ADate;
  • 'Y', 'N'값을 가지는 필드 토글하기
<!-- 이런식으로.. -->
<update
 id="updateToggleSomeField"
 parameterClass="java.util.List"
 >
 update some_table
 set some_field = ( 
     case some_field 
     when 'Y' then 'N' 
     when 'N' then 'Y'
     else some_field end )
 <dynamic prepend=" where ">
 <iterate open=" seq in ( " conjunction=", " close=" ) ">
  #value[]#
 </iterate>
 </dynamic>
</update>



다른글 읽기