mysql 기본 명령어

/*
=======================================
기본사항
=======================================
*/


#로컬 접속 로그인
> mysql -u[계정명] -p[패스워드]

#원격 접속 로그인
> mysql -h[DB주소] -u[계정명] -p[패스워드]


# 현재 사용자
SELECT USER();


# 현재 사용 DB
SELECT DATABASE();


#현재 상태 조회
show status;


# 전체 DB 목록
show databases;


# 실행계획
EXPLAIN [실행 할 쿼리];


# 현재 실행 프로세스(쿼리) 조회
SHOW PROCESSLIST;
/*
- Id : 쿼리 아이디. PID
- User : 쿼리 실행 user
- Host : 연결 host ip
- db : database명
- Command
  (1) Query : 실행중인 쿼리
  (2) Sleep : 쿼리의 실행이 완료되고 연결만 유지되고 있는 경우
- Time : 쿼리 실행 시간 (초)
- State : 현재 쿼리가 뭘 하고있는지 나타냄
- Info : 쿼리문
*/



# 프로세스 종료
KILL 1; -- KILL [ID 번호]





/*
=======================================
테이블 컨트롤
=======================================
*/


# 테이블 목록
show tables;


# 테이블 존재 확인
SELECT COUNT(*) FROM Information_schema.tables
WHERE table_schema = '[DB명]' AND table_name = '[테이블명]';


# 테이블 생성시에 확인
create table if not exists [테이블명] (
  idx int ...
  ....
);


# 테이블 스키마 조회
desc [테이블명];
explain [테이블명];


# 테이블 엔진 타입 조회
select table_schema, table_name, engine from information_schema.tables where table_schema='[DB명]'; -- 해당 DB에 속한 모든 테이블 조
select table_schema, table_name, engine from information_schema.tables where table_name='[테이블명]'; -- 해당회 테이블만 조회


# 테이블 엔진 변경
alter table TEST_BIG engine=InnoDB;
alter table TEST_BIG engine=MyISAM;


# 테이블 이름 변경
ALTER TABLE [테이블명] RENAME [바꿀이름];
RENAME TABLE [테이블명] TO [바꿀이름];


# 컬럼 추가 (MyISAM이 더 빠름. 400만건 데이터 환경)
# MyISAM - SELECT는 lock 없이 작동하지만, INSERT, UPODATE는 lock 걸림
# InnoDB - SELECT, INSERT, UPODATE 모두 lock 걸지 않음
# (1) 마지막에 추가
ALTER TABLE [테이블명] ADD COLUMN [컬럼명] [자료형];
# (2) 지정 칼럼 뒤에 추가
ALTER TABLE [테이블명] ADD COLUMN [컬럼명] [자료형] AFTER [컬럼이름];
# (3) 제일 앞에 추가
ALTER TABLE [테이블명] ADD COLUMN [컬럼명] [자료형] FIRST;


#컬럼 삭제 (MyISAM이 더 빠름. 400만건 데이터 환경)
ALTER TABLE [테이블명] DROP [컬럼명];


# 테이블 삭제
DROP TABLE [테이블명];





/*
=======================================
인덱스
PRIMARY KEY, UNIQUE 옵션을 주면 index가 기본 설정 됨
=======================================
*/
# 인덱스 생성
CREATE INDEX [사용 할 index 명] ON [테이블명] ([컬럼명], [컬럼명] ...);
ALTER TABLE [테이블명] ADD INDEX [사용 할 index 명] ([컬럼명], [컬럼명] ...);


# 테이블 생성시 인덱스 생성
CREATE TABLE [테이블명] (
  idx varchar(5) NOT NULL,
  s_name varchar(20) NOT NULL,

  PRIMARY KEY (id),
  INDEX [사용 할 index 명] (s_name)
);


# 인덱스 조회
SHOW INDEX FROM [테이블명];


# 인덱스 삭제
ALTER TABLE [테이블명] DROP INDEX [인덱스명];




/*
=======================================
트리거
mysql은 트리거 활성화, 비활성화 옵션이 없음.
=======================================
*/


# 트리거 목록 확인
show triggers;


# 트리거 스크립트 확인
show create trigger [트리거명];


# 트리거 삭제
DROP TRIGGER [트리거명];




/*
=======================================
사용자 등록과 권한
=======================================
*/

# 사용자 확인
use mysql;
select user, host from user;


# 사용자 권한 확인
SHOW GRANTS FOR '사용자계정'@'호스트';
SHOW GRANTS FOR CURRENT_USER; -- (현재 접속된 사용자 권한 확인)


# DB 생성
create database DB명 default character set utf8;


# 사용자 생성 & 권한 부여
# 계정 생성
# localhost, % 또는 특정 ip가 호스트로 사용됨
create user '사용자계정'@'localhost' identified by '비밀번호';

# DB 권한 부여
grant all privileges on *.* to '사용자계정'@'localhost'; -- 모든 DB에 권한 부여
grant all privileges on DB명.* to '사용자계정'@'localhost'; -- 특정 DB에 권한 부여

# 계정 생성과 함께 권한 부여 (mysql8 부터 미지원)
GRANT all privileges ON DB명.* to '사용자계정'@'localhost' identified by '비밀번호' ;
-- (local접속만 허용하는 [사용자계정] 사용자에게 [DB명] 데이터베이스에 대한 select,insert,update,delete,create,drop,index,alter 권한을 추가)

# 기존 계정에든 개별 권한 추가
GRANT select,insert,update,delete,create,drop,index,alter ON DB명.* to '사용자계정'@'localhost' ;
-- local접속만 허용하는 test2 사용자에게 test 데이터베이스에 대한 select,insert,update,delete,create,drop,index,alter 권한을 추가
-- * 누적으로 추가가 됨

/*
ALL - 모든 권한 부여
ALTER - ALTER TABLE 사용 가능
CREATE - CREATE TABLE 사용 가능
CREATE TEMPORARY TABLES - CREATE TEMPORARY TABLES 사용 가능
SELECT, DELETE, UPDATE, DROP - 조회,수정,삭제 권한 가능
EXCUTE - 실행권한
FILE - SELECT.. INTO OUTFILE LOAD DATA INFILE 사용 가능
INDEX - CREATE INDEX , DROP INDEX 가능
LOCK TABLES - SELECT 권한있을 때 LOCK TABLES 가능
PROCESS - SHOW FULL PROCESSLIST 가능
SHUTDOWN - SHUTDOWN 가능
SHOW DATABASES - SHOW DATABASES 가능
RELOAD - FLUSH 가능
*/


# 계정 삭제
drop user '사용자계정'@'localhost';


-- 변경사항 적용
flush privileges;