MySql

[문법]

1. SELECT
select [COLUMN_NAMES] from [TABLE_NAME] where [COLUMN_NAME]=[SEARCH_VALUE] order by [COLUMN_NAME] asc/desc;

2. INSERT
insert into [TABLE_NAME] VALUES(DATA_1, DATA_2, DATA_3...);
insert into [TABLE_NAME] (COLUMN_1, COLUMN_2, COLUMN_3) VALUES (DATA_1, DATA_2, DATA_3);

3. DELETE
delete from [TABLE_NAME] where [COLUMN_NAME]=[SEARCH_VALUE];

4. UPDATE
update [TABLE_NAME] set [COLUMN_NAME]=[UPDATE_VALUE] where [COLUMN_NAME]=[SEARCH_VALUE];
update [TABLE_NAME] set [COLUMN_NAME_1]=[UPDATE_VALUE_1], [COLUMN_NAME_1]=[UPDATE_VALUE_2] where [COLUMN_NAME]=[SEARCH_VALUE];

5. CREATE
CREATE TABLE [TABLE_NAME] ( [COLUMN_NAME] [TYPE] [OPTION],[COLUMN_NAME] [TYPE] [OPTION] );
ex)
CREATE TABLE TEST_TABLE ( TEST1 INT NOT NULL COMMENT '테스트코멘트1',  TEST2 VARCHAR COMMENT '테스트코멘트2', PRIMARY KEY(TEST1));

6. SHOW INDEX
show index from [TABLE_NAME];

7. INSERT & UPDATE
insert into [TABLE_NAME] (COLUMN_1, COLUMN_2, COLUMN_3) VALUES(DATA_1, DATA_2, DATA_3) on duplicate key update COLUMN_1=DATA_1, COLUMN_2=DATA_2, COLUMN_3=DATA_3;

[컬럼 조작]

1. 추가

alter table [TABLE_NAME] add [COLUMN_NAME] [COLUMN_TYPE] [COLUMN_POSITION] default [VALUE];

2. 변경

alter table [TABLE_NAME] change [BASE_COLUMN_NAME] [MODIFY_COLUMN_NAME] [COLUMN_TYPE];
ALTER TABLE [TABLE_NAME] CHANGE [BASE_COLUMN_NAME] [MODIFY_COLUMN_NAME] [COLUMN_TYPE] CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;


3. 타입변경

alter table [TABLE_NAME] modify [COLUMN_NAME] [MODIFY_COLUMN_TYPE];

4. 삭제

alter table [TABLE_NAME] drop [COLUMN_NAME];



[특정 시간 내의 자료]

select COLUMN_NAME from TABLE_NAME where COLUMN_NAME between '2013-02-01 00:00:00.0' and '2013-02-28 00:00:00.0' group by substring(COLUMN_NAME , 1, 10);



[DB 용량]

SELECT table_name, SUM(data_length + index_length) / 1024 / 1024 "Size(MB)"

FROM information_schema.TABLES

WHERE table_schema ='가져올 데이타베이스 이름'

GROUP BY table_name ;


[TABLE 용량]


select

concat(table_schema,'.',table_name),

concat(round(table_rows/1000000,2),'M') rows,

concat(round(data_length/(1024*1024*1024),2),'G') DATA,

concat(round(index_length/(1024*1024*1024),2),'G') idx,

concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,

round(index_length/data_length,2) idxfrac

from information_schema.tables

where table_name = 'TABLE_NAME' ;


[테이블 조회 결과 파일 저장]
select * from [DATABASENAME.TABLENAME] into outfile 'file output path' fields terminated by ',' enclosed by '"' lines terminated by '\n';

[외부 데이터 입력]

load data infile '파일경로' into table TABLE_NAME fields terminated by '|';

--> terminated by '|'; 옵션 추가 시 | 를 기준으로 데이터를 잘라서 입력한다.


[코멘트 달기]

alter table TABLE_NAME COMMENT '코멘트';

alter table TABLE_NAME MODIFY COLUMN_NAME varchar(6) comment '코멘트';


[테이블 복사]

create table DATABASENAME.TABLENAME(대상) select * from DATABASENAME.TABLENAME(원본);


[테이블 값 복사]

insert into DATABASENAME.TABLENAME(대상) select * from DATABASENAME.TABLENAME(원본);

** 중복 데이터 무시 -> insert ignore into ...


[DB 백업]

mysqldump -u 'USERID' -p 'DATABASENAME' > 'SAVEFILENAME(DUMPFILE)'


[DB 복원]

mysql -u 'USERID' -p 'PASSWD' --default-character-set='CHARSET' 'DATABASENAME' < 'LOADFILENAME(DUMPFILE)'


[TABLE 백업]

mysqldump -u 'USERID' -p "DATABASE'.'TABLENAME' > 'SAVEFILENAME(DUMPFILE)'


[DB 생성]

create database 'DATABASENAME';


[MYSQL DB 접근 권한 - GRANT 구문(USER ADD/DEL]( PWD만 문자열이라서 " 필요)

1. 권한 추가
grant all on 'DATABASENAME'.* to 'USERID'@'HOSTIP' identified by "PASSWD";(pwd는 문자열)
flush privileges;

2. 권한 제거
revoke all on *.* from user;
flush privileges;

3. 권한 확인
show grants for 'USERID'@'localhost';
(USERID = 접속계정, localhost = 접속IP)

[DB 백업 복구 관련 명령어]

test:db명 table1:테이블명


1. 백업하기

./mysqldump -u root -p test table1 > table1.sql


2. 복구하기

./mysqldump -u root -p test < table1.sql

[DB, Table character set 확인 방법]
1. Database 확인

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "[DBNAME]";

2.Table 확인

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "[DBNAME]" AND T.table_name = "[TABLENAME]";




3. Column 확인

SELECT character_set_name FROM information_schema.`COLUMNS` C WHERE table_schema = "[DBNAME]" AND table_name = "[TABLENAME]" AND column_name = "[COLUMNNAME]";

4. Show 명령어

SHOW FULL COLUMNS FROM [TABLENAME];

5. 생성문 조회

SHOW CREATE DATABASE [DBNAME];

SHOW CREATE TABLE [TABLENAME];