▶ 기본 명령어 및 쿼리
$ mysql -u 사용자아이디 -p DB명 MySQL 접속
$ mysql -u root -p mysql
mysql> update user set password=password('비밀번호') where user='root';
mysql> flush privileges; (또는 $ mysqladmin -u root reload) 		root 비밀번호 변경
mysql> create database DB명; DB 생성
mysql> show databases; DB 목록
mysql> use DB명; 사용할 DB 선택
mysql> drop database DB명; DB 삭제
mysql> use mysql;
mysql> insert into user (host,user,password) values ('localhost', '아이디',
mysql> password('비밀번호'));
mysql> insert into db values ('localhost','DB명','아이디','y','y','y','y','y','y','y','y',
mysql> 'y','y');
mysql> flush privileges;
$ mysqladmin reload
DB 사용자 부여
mysql> show variables; 서버의 variables(설정사항)출력
mysql> show variables like 'have_inno%'; 조건에 맞는 variables만 출력
mysql> show tables; 현재 DB의 테이블 목록
mysql> show tables from DB명; 지정된 DB명이 소유한 테이블 목록
mysql> show tables like 'mem%'; 조건에 맞는 테이블 목록만 출력
mysql> show index from 테이블명; 인덱스 보기
mysql> create table 테이블명(
       -> num int(20) not null auto_increment,
       -> title varchar(255) not null,
       -> content text not null,
       -> wdate datetime not null,
       -> PRIMARY KEY (num)
       -> ); 		테이블 생성

primary key : 주 키로 설정
not null : 반드시 값을 가져야 함
auto_increment : 레코드가 입력될 때 마다 자동으로 값을 증가
mysql> show columns from 테이블명;
mysql>(desc 테이블명, describe 테이블명, explain 테이블명)		 테이블 구조
mysql> rename table 테이블1 to 테이블2;
mysql>(alter table 테이블1 rename to 테이블2)		 테이블명 변경
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; 한번에 여러 테이블명 변경
mysql> alter table 테이블명 type=innodb; 테이블 type변경
mysql> drop table 테이블명; 테이블 삭제
mysql> delete from 테이블명; 테이블 전체내용 삭제
mysql> show create table 테이블명; 해당 테이블 생성 SQL문 출력
mysql> rename table db1명.테이블명 to db2명.테이블명; 테이블을 다른 DB로 이동
mysql> create table 테이블2 as select * from 테이블1; 테이블1과 동일한 테이블2 생성(데이터 포함)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; (또는 where 0;) 테이블1과 동일한 테이블2 생성(데이터 미포함)
mysql> insert into 테이블2 select * from 테이블1; 테이블1의 데이터를 테이블2에 insert
mysql> show table status; 현재 DB의 테이블들 상태 (row수, table type, row길이...)
mysql> show table status from db명; 지정된 DB의 테이블들 상태 (row수, table type, row길이...)
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type)
mysql> alter table 테이블명 add 컬럼명 데이터타입; 컬럼 추가
mysql> alter table 테이블명 del 컬럼명; 컬럼 삭제
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입; 컬럼명 변경
mysql> alter table 테이블명 modify 컬럼명 컬럼타입; 지정된 컬럼의 타입 변경
mysql> lock tables 테이블명 read;
mysql> unlock tables;
mysql> lock tables 테이블명 write;
mysql> unlock tables; 		트랜잭션(LOCK)

read lock - 다른 창이나 외부에서 select만 가능
write lock - 다른 창이나 외부에서 모든 작업 불가
▶ 데이터 타입
• int : 정수형
• tynyint : 부호 있는 정수 -128 ~ 127, 부호 없는 정수 0 ~255, 1 Byte
• smallint : 부호 있는 정수 -32768 ~ 32767, 부호 없는 정수 0 ~65535, 2 Byte
• mediumint : 부호 있는 정수 -8388608 ~ 8388607, 부호 없는 정수 0 ~16777215, 3 Byte
• int/integer : 부호 있는 정수 -2147483648 ~ 2147483647, 부호 없는 정수 0 ~4294967295, 4 Byte
• bigint : 부호 있는 정수 -9223372036854775808 ~ 9223372036854775807, 부호 없는 정수 0 ~18446744073709551615, 8 Byte
• float : 단일 정밀도를 가진 부동 소수점, -3.402823466E+38 ~3.402823466E+38
• double : 2 배 정밀도를 가진 부동 소수점, -1.79769313486231517E+308 ~ 1.79769313486231517E+308

• char(m) : 고정길이 문자열을 표현하는 유형, M = 1 ~255
• varchar(m) : 가변길이 문자열을 표현하는 유형, M = 1 ~ 255
• text : 최대 65535개의 문자를 가변적으로 입력
• tinyblob (tinytext) : 이진/문자 데이타, 최대크기 255 (2^8 - 1) characters. blob는 binary large object의 약자
• blob (text) : 이진/문자 데이타, 최대크기 65535 (2^16 - 1) characters.
• mediumblob (mediumtext) : 이진/문자 데이타, 최대크기 16777215 (2^24 - 1) characters.
• longblob(longtext) : 이진/문자 데이타, 최대크기 4294967295 (2^32 - 1) characters.

• date : 날짜를 표현하는 유형, 1000-01-01 ~ 9999-12-31
• datetime : 날짜와 시간을 표현하는 유형, 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• timestamp : 1970-01-01 00:00:00 부터 2037년 까지 표현, 4 Byte
• time : 시간을 표현하는 유형, -839:59:59 ~ 838:59:59
• year : 년도를 표현하는 유형, 1901 년 ~ 2155년
▶ 숫자 관련 함수
• ABS(숫자) : 절대값 출력.
   select abs(123);

• CEILING(숫자) : 값보다 큰 정수 중 가장 작은 수.
   -양수일 경우는 소숫점 자리에서 무조건 반올림(4.0과 같은 소숫점 자리 0 값은 제외)
   -음수일 경우는 소숫점 자리를 무조건 버림
   select ceiling(4.0);
   select ceiling(4.1);
   select ceiling(4.9);

• FLOOR(숫자) : 값보다 작은 정수 중 가장 큰 수[실수를 무조건 버림(음수일 경우는 제외)].
   -음수일 경우는 [.0/.00/.000/...] 을 제외하고 무조건 소숫점을 버리고 반내림(?)
   select floor(4.0);
   select floor(4.1);
   select floor(4.9);
   select floor(-4.6789);

• ROUND(숫자,자릿수) : 숫자를 소수점 이하 자릿수에서 반올림.(자릿수는 양수,0,음수를 갖을 수 있다. 생략하면 디폴트 0 이 된다.)
   -자릿수를 생략하면 소숫점이 5 이상일 때 반올림/자릿수를 지정하면 지정한 자리수에서 반올림
   select round(4.5); 결과값은 4 / 자릿수를 생략하면 소숫점이 5 이상일 때 반올림을 하며 소숫점 첫째자리까지는 6 이상이어야 반올림
   select round(4.55);
   select round(-4.5);결과값은 -5 / 음수일 경우는 자릿수를 생략하면 소숫점이 5 이상일 때 반내림.(소숫점 첫째자리 역시 5 이상이면 반내림)
   select round(4.556);결과값은 5
   select round(4.556,0);결과값은 5
   select round(4.556,1);결과값은 4.6
   select round(4.556,2);결과값은 4.56
   select round(45.556,-1);결과값은 50
   select round(455.556,-2);결과값은 500

• TRUNCATE(숫자,자릿수) : 숫자를 소수점 이하 자릿수에서 버림.
   =>만일 자릿수를 소숫점 이전으로 정하면 소숫점이하는 버리고 나머지 값은 0 값으로 처리
      예) truncate(9999,-3) -> 9000
   =>또는 자릿수를 소숫점이하로 정하며, 해당숫자가 자릿수보다 소숫점이 모자랄경우 0 값으로 대치
      예) truncate(999,3) -> 999.000
   -반드시 자릿수를 명시해주어야 한다
   -음수일 경우는 해당자릿수에서 소숫점을 버리면서 무조건 반올림
   =>(자릿수 숫자에서 이후 숫자가 0 일 경우는 제외 / 예)-4.0,0/-400,-2/-4.1230,4)
   =>음수 역시 자릿수를 소숫점이하로 정하며, 해당숫자가 자릿수보다 소숫점이 모자랄경우 0 값으로 대치
   =>또한 자릿수를 소숫점 이전으로 정하면 소숫점이하는 버리고 나머지 값은 역시 0 값으로 처리

• POW(X,Y) 또는 POWER(X,Y) : X의 Y승
   -소숫점이 있는 경우도 실행, 단 음수는 양수로 승처리
   select pow(-2.5,2);
   select pow(1.5,2);

• MOD (분자, 분모) : 분자를 분모로 나눈 나머지를 구한다.(연산자 %와 같음)
   select mod(12,5);    => 2
   select 12%5;           => 2

• GREATEST(숫자1,숫자2,숫자3...) : 주어진 수 중 제일 큰 수 리턴.
   select greatest(100,101,90);

• LEAST(숫자1,숫자2,숫자3...) : 주어진 수 중 제일 작은 수 리턴.
   select least(100,101,90);

• INTERVAL(a,b,c,d.....) : a(숫자)의 위치 반환
   -두 번째 이후는 오름차순 정렬이 되어야 함
   예) INTERVAL(5,2,4,6,8) => 2
        5는 4와 6사이에 존재, 4~6사이의 위치가 앞에서 2번째
   select interval(4,1,2,3,5,6);123~56 사이에 들어가므로 출력값은 앞에서부터 0,1,2,3 이므로 3 이 된다.
▶ 문자 관련 함수
• ASCII(문자) : 문자의 아스키 코드값 리턴.
   SELECT ASCII('문자');
   select ascii('A');

• CONCAT('문자열1','문자열2','문자열3'...) : 문자열들을 이어준다.
   select concat('ASP,','PHP,','SQL',' WEB STUDY');

• INSERT('문자열','시작위치','길이','새로운문자열') : 문자열의 시작위치부터 길이만큼 새로운 문자열로 대치
   '시작위치' 와 '길이'는 문자열이 아니므로 작은따옴표로 굳이 묶어주지 않아도 된다.
   select insert('MySql web study','7','3','offline');
   select insert('MySql web study',7,3,'offline');

• REPLACE('문자열','기존문자열','바뀔문자열') : 문자열 중 기존문자열을 바뀔 문자열로 바꾼다.
   select replace('MySql web study','web','offline');

• INSTR('문자열','찾는문자열') : 문자열 중 찾는 문자열의 위치값을 출력
   -값이 존재하지 않으면 0값 리턴
   select instr('MySql web study','s');
   select instr('MySql web study','S');

• LEFT('문자열',개수) : 문자열 중 왼쪽에서 개수만큼을 추출.
   select left('MySql web study',5);
   select left('MySql web study','5');

• RIGHT('문자열',개수) : 문자열 중 오른쪽에서 개수만큼을 추출.
   select right('MySql web study',5);
   select right('MySql web study','5');

• MID('문자열',시작위치,개수) : 문자열 중 시작위치부터 개수만큼 출력
   select mid('MySql web study',7,3);
   select mid('MySql web study','7','3');

• SUBSTRING('문자열',시작위치,개수) : 문자열 중 시작위치부터 개수만큼 출력
   select substring('Mysql web study',11,5);
   select substring('Mysql web study','11','5');

• LTRIM('문자열') : 문자열 중 왼쪽의 공백을 없앤다.
   select ltrim('          web study');

• RTRIM('문자열') : 문자열 중 오른쪽의 공백을 없앤다.
   select rtrim('web study          ');

• TRIM('문자열') : 양쪽 모두의 공백을 없앤다.
   select trim('     web study      ');

• LCASE('문자열') 또는 LOWER('문자열') : 소문자로 바꾼다.
   select lcase('MYSQL');
   select lower('MySQL');

• UCASE('문자열') 또는 UPPER('문자열') : 대문자로 바꾼다.
   select ucase('mySql');
   select upper('mysql');

• REVERSE('문자열') : 문자열을 반대로 나열한다.
   예) REVERSE('abcde') => edcba
   select reverse('lqSyM');
▶ 논리 관련 함수
• IF(논리식,참일 때 값,거짓일 때 값) : 논리식이 참이면 참일 때 값을 출력하고 논리식이 거짓이면 거짓일 때 출력한다.

• IFNULL(값1,값2) : MS-SQL 의 ISNULL 기능과 같다. 값1이 NULL 이면 값2로 대치하고 그렇지 않으면 값1을 출력
▶ 집계 관련 함수
• COUNT(필드명) : 선택한 컬럼의 전체행의 수를 반환.

• SUM(필드명) : 각 필드값의 합계를 반환.

• AVG(필드명) : 각 필드값의 평균값을 반환.

• MAX(필드명) : 필드값 중 최대값을 반환.

• MIN(필드명) : 필드값 중 최소값을 반환.
▶ 날짜 관련 함수
• NOW(), SYSDATE(), CURRENT_TIMESTAMP() : 현재 날짜와 시간 출력
   ※ 함수의 상황이 숫자인지 문자열인지에 따라
      YYYYMMDDHHMMSS 또는
      'YYYY-MM-DD HH:MM:SS' 형식으로 반환한다.
   예) select now();
         => '2001-05-07 09:10:10'
         select now() + 0;
         => 20010507091010

• CURDATE(), CURRENT_DATE() : 현재 날짜 출력
   ※ 함수의 상황이 숫자인지 문자열인지에 따라
      YYYYMMDD 또는
      'YYYY-MM-DD 형식으로 반환한다.
   예) select curdate();
         => '2001-05-07'
         select curdate() + 0;
         => 20010507

• CURTIME(), CURRENT_TIME() : 현재 시간 출력
   ※ 함수의 상황이 숫자인지 문자열인지에 따라
   HHMMSS 또는 'HH:MM:SS' 형식으로 반환한다.
   예) select curtime();
         => '09:10:10'
         select curtime() + 0;
         => 091010

• DATE_ADD(날짜,INTERVAL 기준값) : 날짜에서 기준값 만큼 더한다.
※ 기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
   예) select date_add(now(), interval 2 day);
         => 오늘보다 2일 후의 날짜와 시간 출력.
         select date_add(curdate(), interval 2 day);
         => 오늘보다 2일 후의 날짜 출력.

• DATE_SUB(날짜,INTERVAL 기준값)
   날짜에서 기준값 만큼 뺸다.
※ 기준값 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
   select date_sub(now(),interval 2 day);
   => 오늘보다 2일 전의 날짜와 시간 출력.
   select date_sub(curdate(), interval 2 day);
   => 오늘보다 2일 전의 날짜 출력.

• YEAR(날짜) : 날짜의 연도 출력.
   select year('20000101');
   select year(20000101);
   select year('2000-01-01');
   select year(now());
   select year(curdate());
   select year(date_add(now(),interval 2 year));
   select year(date_sub(curdate(),interval 2 year));

• MONTH(날짜) : 날짜의 월 출력.
   select month('20001231');
   select month(20001231);
   select month('2000-12-31');
   select month(now());
   select month(curdate());
   select month(date_add(now(),interval 2 month));
   select month(date_sub(curdate(),interval 2 month));

• MONTHNAME(날짜) : 날짜의 월을 영어로 출력.
   select monthname(20021221);
   select monthname('20000721');
   select monthname('2000-08-10');
   select monthname(now());
   select monthname(curdate());
   select monthname(date_add(now(),interval 17 month));
   select monthname(date_sub(curdate(),interval 11 month));

• DAYNAME(날짜) : 날짜의 요일일 영어로 출력.
   select dayname(20000121);
   select dayname('20010123');
   select dayname('2001-06-22');
   select dayname(now());
   select dayname(curdate());
   select dayname(date_add(now(),interval 21 day));
   select dayname(date_sub(curdate(),interval 333 day));

• DAYOFMONTH(날짜) : 날짜의 월별 일자 출력.
   select dayofmonth(20030112);
   select dayofmonth('20011231');
   select dayofmonth('2001-12-23');
   select dayofmonth(now());
   select dayofmonth(curdate());
   select dayofmonth(date_add(now(),interval 56 day));
   select dayofmonth(date_sub(curdate(),interval 33 day));

• DAYOFWEEK(날짜) : 날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6))
   select dayofweek(20011209);
   select dayofweek('20001212');
   select dayofweek('2003-03-21');
   select dayofweek(now());
   select dayofweek(curdate());
   select dayofweek(date_add(now(),interval 23 day));
   select dayofweek(date_sub(curdate(),interval 31 day));

• WEEKDAY(날짜) : 날짜의 주별 일자 출력(월요일(0),화요일(1)...일요일(6))
   select weekday(20000101);
   select weekday('20030223');
   select weekday('2002-10-26');
   select weekday(now());
   select weekday(curdate());
   select weekday(date_add(now(),interval 23 day));
   select weekday(date_sub(curdate(),interval 33 day));

• DAYOFYEAR(날짜) : 일년을 기준으로 한 날짜까지의 날 수.
   select dayofyear(20020724);
   select dayofyear('20001231');
   select dayofyear('2002-01-01');
   select dayofyear(now());
   select dayofyear(curdate());
   select dayofyear(date_add(curdate(),interval 44 year));
   select dayofyear(date_sub(now(),interval 25 month));
   select dayofyear(date_add(now(),interval 55 day));
   select dayofyear(date_sub(curdate(),interval 777 hour));
   select dayofyear(date_add(now(),interval 999999 minute));

• WEEK(날짜) : 일년 중 몇 번쨰 주.
   select week(now());
   select week(date_sub(curdate(),interval 12 month));

• FROM_DAYS(날 수)
   -00년 00월 00일부터 날 수 만큼 경과한 날의 날짜 출력.
      ※ 날 수는 366 이상을 입력 그 이하는 무조건 '0000-00-00' 으로 출력.
   -또한 9999-12-31 [from_days(3652424)] 까지의 날짜가 출력가능 하다고는 하나
      정확히 말하면 0000-03-15 [from_days(3652499)] 까지의 날짜가 출력가능함.
   -따라서 날 수는 366 이상 3652424[3652499] 이하가 되어야 한다.
   select from_days(3652424);
   select from_days('3652499');

• TO_DAYS(날짜)
   -00 년 00 월 00일 부터 날짜까지의 일자 수 출력.
   -from_days와 비교해 볼 때 정확한 날짜범위는 3652424 일 수 까지임을 알 수 있다.
   select to_days('99991231');
   select to_days('0000-03-15');
   응용 예제1) 자신이 살아 온 날수
   select to_days(now()) - to_days('본인생일자');
   select to_days(now()) - to_days('1970-10-10');
   응용 예제2) 살아 온 날수를 이용하여 자신의 나이를 만으로 구하기
   select (to_days(now())-to_days('1970-10-10'))/365;
   select floor((to_days(now())-to_days('19701010'))/365);

• DATE_FORMAT(날짜,'형식') : 날짜를 형식에 맞게 출력
구분 형식 설명
연도 %Y
%y		 Year, numeric (four digits)
Year, numeric (two digits)
월 %b
%c
%M
%m		 Abbreviated month name (Jan..Dec)
Month, numeric (0..12)
Month name (January..December)
Month, numeric (00..12)
요일 %a
%W
%w		 Abbreviated weekday name (Sun..Sat)
Weekday name (Sunday..Saturday)
Day of the week (0=Sunday..6=Saturday)
일 %D
%d
%e
%j		 Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
Day of the month, numeric (00..31)
Day of the month, numeric (0..31)
Day of year (001..366)
시 %H
%h
%I
%k
%l		 Hour (00..23)
Hour (01..12)
Hour (01..12)
Hour (0..23)
Hour (1..12)
분 %i Minutes, numeric (00..59)
초 %f
%S
%s		 Microseconds (000000..999999)
Seconds (00..59)
Seconds (00..59)
시간 %r
%T		 Time, 12-hour (hh:mm:ss followed by AM or PM)
Time, 24-hour (hh:mm:ss)
주 %U
%u
%V
%v		 Week (00..53), where Sunday is the first day of the week
Week (00..53), where Monday is the first day of the week
Week (01..53), where Sunday is the first day of the week; used with %X
Week (01..53), where Monday is the first day of the week; used with %x
기타 %p
%%		 AM or PM
A literal ‘%’ character

   예) select date_format(now(),'%Y:%M:%p');
         => 2001:May:PM

• DATABASE() : 현재의 데이터베이스 이름을 출력한다.

• PASSWORD('문자열') : 문자열을 암호화한다.

• FORMAT(숫자,소수이하자리수) : 숫자를 #,###,###.## 형식으로 출력
   -임의의 소수점자릿수를 생성한다./소숫점을 필요한 만큼 취한다.
   -소숫점을 만들어 같은 길이로 한다음 동일하게 프로그램에서 불러와서 소숫점을 버리고 필요한 곳에 출력하는 등에 응용할 수 있다.
   select format(123,5);
   select format(123.12345600123,9);
   select format(123.123,-3);
   ※ 소숫점이하자리수가 0 이나 음수값은 해당이 안됨
▶ 이미지/바이너리 데이터
   blob란 binary large object로, blob 필드는 소팅이나 index 생성은 할 수 없습니다.
CREATE TABLE gallery1 (
id int NOT NULL auto_increment,
image blob NOT NULL,
title varchar(100) DEFAULT '' NOT NULL,
width smallint(6) DEFAULT '0' NOT NULL,
height smallint(6) DEFAULT '0' NOT NULL,
filesize int,
detail text,
PRIMARY KEY (id)
);

#바이너리 데이터
#이미지명
#이미지 가로크기
#이미지 세로크기
#파일 크기
#이미지 설명

[PHP 예제]

   • 이미지 입력
   -------------------------------------------------------------------------------------------------------------
   $size = GetImageSize($image); // GetImageSize() 함수는 이미지의 가로, 세로 등을 알려줌.
   $width = $size[0];
   $height = $size[1];
   $imageblob = addslashes(fread(fopen($image, 'r'), filesize($image))); // 파일 크기만큼 저장 가능한 blob 형태로 처리
   $filesize = filesize($image) ;
   $query = 'INSERT INTO gallery VALUES ('', '$imageblob','$title', '$width', '$height','$filesize', '$detail')';
   $result = mysql_query($query,$connect);
   -------------------------------------------------------------------------------------------------------------

   • 이미지 출력 <img src=./view.html?id=$row[id]...>
   -------------------------------------------------------------------------------------------------------------
   $query = 'select * from gallery where id=$id' ;
   $result = mysql_query($query,$connect );
   $row = mysql_fetch_array($result);
   Header('Content-type: image/jpeg');
   echo $row[image];
   -------------------------------------------------------------------------------------------------------------

▶ 백업/복구
• 데이타베이스 백업/복구

   [백업]
   $ mysqldump -u root -p DB명 > /절대경로/백업할 파일이름.sql

   [복구]
   drop database DB명; // 기존 데이터베이스 삭제
   $ mysql -u root -p DB명 < /절대경로/백업할 파일이름.sql

• 테이블 백업/복구

   [백업]
   mysql> lock tables 테이블명 read; // 백업이나 복구를 하기 전에는 항상 LOCK를 걸어놓는다.
   mysql> select * into outfile '/절대경로/백업할 파일이름.sql' from 테이블명;

   [복구]
   delete from 테이블명; // 테이블 내 기존 데이터 삭제
   load data infile '/절대경로/백업한 파일이름.sql' into table 테이블명;
   select * from 테이블명;
   unlock tables;

• 파일처리

   [테이블에서 데이터를 검색하여 파일로 출력하기]
   mysql> select * from 테이블명 where 컬럼명 < 값 into outfile '/절대경로/백업한 파일이름.txt' fields terminated by ',';

   [파일에서 데이터를 테이블로 읽어 들이기]
   mysql> load data infile '/절대경로/백업한 파일이름.txt' into table 테이블명 fields terminated by ',';