[Mysql] mysql 기본명령어, 설정

**패스워드복구**

# killall mysqld ;;mysql 서비스중지

# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &

# mysql

 > use mysql

 > UPDATE user SET password=PASSWORD('passowrd') WHERE user = 'root';

 > flush privileges;

 > exit

# /etc/init.d/mysqld start

**언어설정**

utf-8 euc_kr

다국어, 영어한국어

utf8로 캐릭터셋을 설정한는게 좋다 (다국어지원 단php,apache언어셋이 euc_kr일경우 euc_kr로 설정해야한다.)

>\s mysql의 전체상태확인

>set names utf8;

**mysql 명령어**

기본명령어 

show , create , drop revoke

기본 쿼리

insert, delete, select, update

>show databases;

데이터베이스 보여줌

>create database name;

데이터베이스 생성

>drop database name;

데이터베이스 삭제

>create user 'username'@'(%,localhost,IP)' identified by 'password'; 사용자추가

>grant all privileges on *.* to 'user'@'(%,localhost,IP)' identified by 'password'; 사용자 권한주기

  컬럼   권한       DB.테이블  사용자  

>revoke all on *.* from 'user'@'(%,localhost,IP)' identified by 'password'; 권한뺏기

**사용자 보는법**

> use mysql; 사용자를 볼려면 mysql 에 들어가서

> select user, host from user; :: select(출력) 출력할 table의 필드명, from(무슨테이블들의 데이터를 조회할것인지) , where 조건절 

     필드 필드     테이블명

**테이블생성 **

>show tables;

>create table name(

  -> 필드명1 데이터타입 (defult형식),

  -> 필드명2 데이터타입 (defult형식),

  -> 필드명3 데이터타입 (defult형식)

  ->);

//데이터 타입//

MySQL 데이터 형식 (문자형)

CHAR[길이] : 0 ~ 255개의 고정길이 문자열 (정확히 그 길이 바이트)

VARCHAR[길이] : 0 ~ 65536개의 가변길이 문자열 (길이 + 1 또는 2바이트)

TINYTEXT : 0 ~ 255개의 문자로 된 문자열 (길이 + 1바이트)

TEXT : 0 ~ 약 7만개의 문자로 된 문자열 (길이 + 2바이트)

MEDIUMTEXT : 0 ~ 약 1700만개의 문자로 된 문자열 (길이 + 3바이트)

LONGTEXT : 0 ~ 약 430000만개의 문자로 된 문자열. 43억개 (길이 + 4바이트)

------------------------------------------------------------------------------------------

MySQL 데이터 형식 (숫자형)

TINYINT[길이] : -128 ~ +127 또는 0 ~ 255 (1바이트)

SMALLINT[길이] : -32768 ~ 32767 또는 0 ~ 65536 (2바이트)

MEDIUMINT[길이] : -800만 ~ +800만 또는 0 ~ 1600만 (3바이트)

INT[길이] : -21억 ~ +21억 또는 0 ~ 42억 (4바이트)

BIGINT[길이] : 무제한^^ 8바이트만큼 이라고 하면 맞음. (8바이트)

* [길이]는 zerofill을 위함이다. insert된 수의 자릿수가 적어야 효과가 나타나겠지..

* zerofill을 걸지 않았다면, [길이]는 넣지도 마라. zerofill 걸면 자동으로 unsigned가 된다.

* INT(4) ZEROFILL => 0005

FLOAT[길이, 소수] : 고정 소수점을 사용하는 작은 수 (4바이트)

DOUBLE[길이, 소수] : 고정 소수점을 사용하는 큰 수 (8바이트)

DECIMAL[길이,소수] : DOUBLE를 문자열로 저장 (길이 + 1 또는 2바이트)

* FLOAT(7,4) => -999.9999

------------------------------------------------------------------------------------------

MySQL 데이터 형식 (날짜형)

DATE : YYYY-MM-DD (3바이트)

DATETIME : YYYY-MM-DD HH:MM:SS (8바이트)

TIMESTAMP : insert나 update되면 자동으로 그 날짜와 시간이 저장됨.

TIME : HH:MM:SS (3바이트)

------------------------------------------------------------------------------------------

MySQL 데이터 형식 (열거형)

* 컬럼에 저장될 데이터가 몇가지로 정해져 있을 때(최대64개), 이를 저장하기 위한 데이터형이다.

ENUM : 정해진 몇가지의 값들 중, 하나만 저장 수 있다. (1 또는 2바이트)

SET : 정해진 몇가지의 값들 중, 여러 개를 저장할 수 있다. (1,2,3,4,8 바이트)

------------------------------------------------------------------------------------------

MySQL 데이터 형식 (이진 데이터 저장용 : 파일이나 암호화된 데이터 저장)

TINYBLOB : 2^8 - 1 [256 Bytes]

BLOB : 2^16 - 1 [64KB]

MEDIUMBLOB : 2^24 - 1 [8 MB]

LONGBLOB : 2^32 - 1 [4GB]

//////

//default 형식///

not null ;;공백x

primary key  ;;필드에서 서로겹칠수없으며 인덱스를 부여 테이블전체를 통틀어서 겹칠수없는값이들어가야한다

///////////////////////////////////////

**쿼리이용**

데이터 출력 Select 

select 출력, 조건절 where을 이용해서 상세하게 볼수있다.

>select * from 테이블명 where AGE='10' ;user table에서 모든필드출력 단 나이필드가 10인 것만

>select * from 테이블명 where AGE like '%0'  ; AGE필드에 모든문자(숫자)0이 들어간 것을 출력

데이터 삽입 Insert

>insert into 테이블명 values('값', '값', '',...); ;;필드순서대로 값또한 맞춰서 순서대로 넣어주어야한다.(테이블생성시옵션에따라 빈칸으로 둘수도안될수도잇음)

>insert into 테이블명 (필드명1, 필드명2) values ('값','값'); ;;필드의 일부분만 넣고싶은경우

데이터 수정 Update

>update 테이블명 set 필드명='값' where 필드명='값2' ;;데이터수정 필드명의 값2를 값으로 수정

데이터 삭제 Delete

>delete from 테이블명 where 필드명1='값', 필드명2='값'.... ;;데이터삭제

>delete from 테이블명 where 필드명 like '%d%'  ;; 필으명에 d문자가들어간 모든것 삭제

>delete from 테이블명 

>truncate 테이블명; 데이터 전체삭제 

> show grants FOR name@localhost;  사용자의 부여된 권한을 확인

> drop user name@localhost;

>desc 테이블명; 테이블의 구조를 한눈에 볼수잇

**테이블수정**

alter table 테이블명 add 필드명 데이터타입;              => 속성 추가

alter table 테이블명 drop 필드명;                    => 속성 삭제

alter table 테이블명 change column 이전필드명 새필드명 데이터타입;     => 속성 변경

alter table 테이블명 modify column 필드명 새데이터타입;               => 속성 타입 변경

alter table 테이블명 rename 새 테이블명;                            => 테이블명 변경

>flush privileges; 변경내용저장

**테이블 체크**

테이블이손상되었는지 check table -> 손상된테이블은 복구 repair table -> 속도개선을 위해 테이블최적화 optimize table 

> use DB명;

> check table 테이블명;

> optimize table 테이블명;

 

=> MySQL 설치 홈의 bin 폴더로 이동

# ./mysqlcheck -u [DB계정] -p[패스워드] --auto-repair [DB명]           

=> 해당 DB의 모든 테이블을 체크 및 자동복구

=> MySQL 설치 홈의 bin 폴더로 이동

# ./mysqlcheck -u [DB계정] -p[패스워드] --optimize [DB명]               

=> 해당 DB의 모든 테이블을 최적화

**백업**

#mysqldump -u username -p --all-databases > name.sql  ;;모든 데이터베이스 백업

#mysqldump -u username -p --databases DB명 > name.sql ;; 특정데이터만 백업

# mysql -u username -p DB명 < name.sql ;복원

> source name.sql

**슬로우쿼리**

쿼리응답시간이 오랫동안 지연되는경우 시간과 해당쿼리를 로그에 남기도록 설정

#vim /etc/my.cnf

[mysqld]

log-slow-queries = /usr/local/mysql/data/mysql/mysql-slow.log

long_query_time = 3 :3초이상걸리는 쿼리를 로그파일에 저장

재시작

로그항목

- Time          : 쿼리요청시간

- User@Host : 쿼리를 요청한 User와 호스트

- Query_time : 실제 수행시간

- Lock_time   : 락이 걸린 시간

- Rows_sent  : 쿼리결과로 나온 열(Row)의 수

- Rows_examined : 쿼리대상의 열(Row)수

- 가장하단 : 수행된 쿼리문