Database

mysql 쿼리 정리 (mariadb)

CHOMAN 2015. 6. 11. 13:35

mysql, mariadb 혼용 거의 둘다 사용 가능함

DB 접속

mysql -u DB아이디 -p 패스워드입력

mysql -u DB아이디 -p 패스워드입력 -h 아이피

초기 DB 패스워드 지정

mysqladmin -u root password 비밀번호

예전 방식이고 버전별로 초기 패스워드 정책은 약간식 다름

mysql 도구

myisam_ftdump  
MyISAM에서 사용하는 FULLTEXT 인덱스에 대한 정보 출력해주는 TOOL

mysqlaccess  
DB에 대한 계정에 접근권한을 체크해주는 TOOL

mysqldumpslow queries.log  
slow query log 를 정리해주는 tools

mysqlslap  
mysql 서버에 쿼리를 실행하여 부하를 유발할수 있는 Tool

mysqldump  
백업

mysqlcheck  
tables 점검

DB 목록 보기

show databases;

DB 생성

create database DB이름;

DB 삭제

drop database DB이름

간혹 show database 해보면 빈 DB가 그대로 있는데 이거 까지 지워줄려면 DB 데이터 디렉토리 삭제

경고 없이 바로 삭제되므로 주의해야 함

DB 선택

use DB이름;

테이블 생성 (example)

CREATE TABLE member (
seq     INT NOT NULL AUTO_INCREMENT,
name   VARCHAR(50),
nickname CHAR(50),  
PRIMARY KEY(seq)
) ENGINE=MYISAM CHARSET=euckr;

테이블 목록 출력

show tables;

먼저 use DB이름; 을 한후 이용

show tables from 디비이름

테이블 내용 (레코드) 보기

select * from 테이블이름;

select * from 디비이름.테이블이름;

조건의 맞는 레코드만 조회  
select * from 테이블이름here 필드="값";

개수만큼의 레코드만 조회  
select * from 테이블이름imit 개수;

% = * select \* from 테이블이름 where 필드 like '값%';

테이블 구조 확인

desc 테이블이름;

show full columns from 테이블이름;

show create table 테이블이름\G;

레코드 삭제

테이블에서 특정 레코드 삭제하기
delete from 테이블이름 where 필드="값";

테이블 검사

check table 테이블이름;

테이블 복구

analyze table 테이블명;
repair table 테이블명;

테이블 이름 바꾸기

alter table oldtable rename newtable;

테이블 복사하기 특정조건

INSERT INTO oldtable SELECT * FROM newtable;

테이블2에 있는 조건을 검색해서 테이블1에 INSERT 하기

INSERT 할때 컬럼수 맞아야 함

INSERT INTO 테이블1 SELECT 테이블컬럼1, 테이블컬럼2 FROM 테이블2 WHERE {조건} ORDER BY 필드이름 ASC;

테이블 복사하기 (KEY 값 무시하기?)

INSERT ignore INTO oldtable SELECT * FROM newtable;

복사할 table 이 생성된 경우는 INSERT INTO 복사할 table 을 생성하는 동시에 복사는 CREATE TABLE

테이블 복사하기 (디비간)

INSERT INTO DB.newtable SELECT * FROM DB.oldtable

테이블 복사 (스키마만 복사 데이터는 복사안함)

CREATE TABLE 만들테이블명 LIKE 원본테이블;

레코드 입력

INSERT IGNORE INTO
기존레코드 남아있음, 기존 레코드의 AUTO_INCREMENT 값은 변하지 않음

REPLACE INTO
기존 레코드가 삭제되고, 신규 레코드가 INSERT됨, 따라서 AUTO_INCREMENT의 값이 변경됨

INSERT INTO ON DUPLICATE UPDATE
INSERT IGNORE의 장점 포함함, 중복 키 오류 발생 시, 사용자가 UPDATE될 값을 지정할 수 있음

테이블 삭제하기

drop table 테이블명;

테이블에 레코드 추가

insert into 테이블이름 (필드이름1,필드이름2,필드이름3) values('값1','값2','값3');

순서만 맞으면 필드이름 생략 가능함

update (기존 레코드 변경)

UPDATE 테이블명 SET 필드명='변경할값' WHERE 필드명=해당값;

update 테이블명 set 필드명=NULL; # NULL 값으로 업데이트

정렬

주로 select 구문 뒤에 사용됨

order by asc
order by desc

아이피 주소 정렬

order by INTE_ATON(ip) asc
order by INTE_ATON(ip) desc

범위 지정

where 컬럼 BETWEEN "1" AND "14000"

AUTO_INCREMENT 변경 (초기화)

alter table 테이블명 AUTO_INCREMENT=값;

DB root 패스워드 강제 변경 (OLD)

service mysqld or mariadb stop
mysqld_safe --skip-grant &  
mysql -u root mysql  

mysql> update user set password=password('new password') where user='root';  
mysql> flush privileges;  
mysql> quit

service mysqld or mariadb start

mariadb 10.4 user 테이블 뷰로 바뀜

ALTER USER 'root'@'localhost' IDENTIFIED BY 'PASSWORD_STRING';

10.4 이상은 update 문으로 패스워드 변경 불가함

계정

사용자 계정 및 권한 명령은 수정이 아닌 이상 delete 와 insert into 사용 권장 하지 않음

계정만 생성

create user '아이디'@'호스트아이피'  identified by '패스워드';

계정만 삭제

DROP USER 아이디@호스트;

flush privileges;

권한 테이블 리로드 grant 명령이 아닌 insert into 같은 명령으로 수정한 경우 필요함

계정 패스워드 변경

update user set password=password('비밀번호') where user='유저명';

사용자 생성 및 권한

GRANT 권한 ON DB이름.테이블이름 TO '아이디'@'호스트' IDENTIFIED BY '패스워드';

권한 설정 (테이블에 권한 주기)

grant 권한 on DB이름.테이블이름 to '아이디'@'호스트' with grant option;

with grant option : 권한 선택 가능

권한 종류

SELECT
INSERT
UPDATE
DELETE
CREATE
DROP
INDEX
ALTER
USAGE
all privileges

권한 삭제

REVOKE ALL ON 디비이름.테이블이름 TO 아이디@호스트;

특정 권한만 삭제할 수 있다.

REVOKE SELECT ON 디비이름.테이블이름 TO 아이디@호스트;

MariaDB 10.8 에서는 TO -> FROM 으로 바뀐듯 함

권한 확인

show grants for 아이디@호스트;

언어셋 확인

use DB명;
show variables like 'character_set%';

언어셋 변경

alter table 테이블이름 convert to character set utf8 collate utf8_unicode_ci;

alter database 디비명 DEFAULT CHARACTER SET utf8 collate utf8_unicode_ci;

utf8_unicode_ci : 정확한 비교 및 정렬 필요,
utf8_general_ci : 일반적으로 사용, 성능 중시, ÀÁÅåāă -> A 로 치환되어 비교 처리

_ci 는 대소문자 비교시 동일한 것으로 처리함?

데이터가 들어있는 경우 언어셋 변경

alter table 테이블명 modify 컬럼 binary(100);
alter table 테이블명 modify 컬럼 varchar(20) collate euckr_korean_ci;

바이러니로 변경후 다시 언어셋 변경 (바로 변경하는 경우 문자 손상 가능성?

테이블 엔진 변경

alter table 테이블명 engine=innodb;

collation 확인

SHOW COLLATION;

백업 및 복구

디비별로 백업  
mysqldump -u root -p 디비이름 > 파일명.sql

전체백업  
mysqldump -u root -p --all-databases > 파일명.sql

케릭터셋 옵션을 이용한 백업 (euckr, utf8)  
mysqldump -u root -p --default-character-set=euckr DB이름 > 파일명.sql

테이블만 덤프  
mysqldump -u root -p -B DB명 --tables 테이블명1 테이블명2 테이블명3 > 파일명.sql

테이블만 덤프
mysqldump -u root -p -B 디비명 --tables 테이블명 > 파일명.sql

게릭터셋 옵션을 이용한 덤프 (euckr, utf8)  
mysqldump -u root -p --default-character-set=euckr DB명 < 파일명.sql

디비별로 복구
mysql -u root -p DB이름 < 파일명.sql

전체복구  
mysql -u root -p < 파일명.sql

프로세스 및 시스템

디비 상태 조회  
show status;

디비 변수 조회  
show variables;

디비 프로세스 확인  
show processlist;

특정 조건으로 디비 프로세스 확인  
SELECT \* FROM INFORMATION\_SCHEMA.PROCESSLIST where 필드1 > 조건 and 필드1 is not null;

테이블 락 걸기

FLUSH TABLES WITH READ LOCK;

UNLOCK TABLES;

락을 건 세션이 종료되면 Lock 자동 해제됨

select 조회만 가능하며 update insert 같은 쓰기 쿼리는 대기열 상태이며 Lock 이 해제되면 일괄 처리됨

프로시져 목록 보기

show procedure status;

프로시저 내용 보기

show create procedure 프로시저명;

server id 확인 및 변경

show variables like 'server_id';

SET GLOBAL server_id =2;

인덱스 (index) 보기

SHOW INDEX from 테이블명

시간함수

현재
SELECT NOW();

1시간전
SELECT DATE_ADD(NOW(), INTERVAL -1 HOUR);

저작자표시