[MySQL] 명령어 정리2

출처 : http://blog.naver.com/myrilke/150019611211

이 메뉴얼은 http://www.mysqlkorea.co.kr/sub.html?mcode=develop&scode=01&lang=k 의 한글메뉴얼 중 겨우 3장을 정리한 내용입니다. 버전은 5.0 기준이므로 이보다 낮은 버전의 MySQL에서는 몇 가지 기능이 실행되지 않습니다. 좀 더 자세히 SQL문에 대해 알고 싶으시다면 한글 메뉴얼의 13장을 보세요. 레퍼런스 북처럼 되어 있습니다. 

참고하세요.

 

mysql 실행시

-h: MYSQL서버의 IP또는 호스트명

-u: 유저명

-p: 패스워드

-D: 데이터베이스명

ex) mysql -h localhost -u root -p

// localhost에 root라는 유저명의 유저가 접속, -p를 쓰면 패스워드를 묻는 절차가 실행됨

// 귀찮다면 만약 패스워드가 myrilke라면 -pmyrilke로 쓰면 바로 접속 가능

// 만약 떨어져 있을 시에는 myrilke라는 데이터베이스로 오판함

 

1.mysql에는 쿼리를 넣을 때 대소문자 구분을 하지 않습니다. 쿼리 입력시 제일 마지막에는 ;(세미콜론)을 입력하는 것도 잊지 말아야 합니다.

 

2.입력을 하다 이 명령어를 실행시키고 싶지 않으면 \c로 취소하면 '삐'하는 경고음없이 취소할 수 있습니다. 가끔 \c로 못 마칠 때가 있는데 이는 문장구조가 여전히 대기 상태이기 때문입니다. 그럴 때엔 필요로 하는 문자(아래 Prompt형태에서 1)을 제외한 모든 경우)을 입력 후 \c를 입력해야 합니다. ex) '\c, "\c...

 

3.Prompt 형태
1) mysql> 새로운 명령어를 위한 준비
2) -> 다중 라인 명령어의 다음 라인 대기 상태
3) '> '로 시작된 문장의 완결을 위한 다음 문장 대기
4) "> "로 시작된 문장의 완결을 위한 다음 문장 대기
5) `> `로 시작된 식별자의 완결을 위한 다음 문장 대기
6)/*> /*로 시작된 주석문의 완결을 위한 다음 문장 대기

 

4. 단순 Query입력
1) select version(), current_date; (버전정보및 오늘 날짜 확인)
2) select sin(pi()/4), (4+1)*5; (파이를 4로 나눈 값의 sin값과 (4+1)*5값이 출력)

 

5. 데이터베이스 생성 및 사용
1) show databases;

//현재 서버의 데이터 베이스들 출력

 

2) use test

//Database changed라는 출력과 함께 test라는 데이터 베이스에 접속,

//이 때에는 세미콜론 사용을 안 한다.

//또한 단일라인에서만 사용가능하므로 한 줄에 다 입력해야 함.

3) grant all on test.* to 'myrilke'@'localhost';

//myrilke라는 이름의 등록된 사용자가 localhost라는 호스트 이름으로

//test라는 데이터베이스에 사용자 권한을 요청

4) create database test;

//test라는 데이터베이스 생성

 

5) show tables;

//현 데이터베이스 내의 테이블들을 출력

 

6) create table pet(name varchar(20), owner varchar(20), 
species varchar(20), sex char(1), birth date, death date);

//name, owner, species, sex, birth, death의 정보를 가질 table을 생성,

//가독성을 높이기 위해 2줄에 걸쳐 타입

7) describe pet;

//pet이라는 테이블이 가지는 정보에 대해 검사, 이 때 필드 이름, 데이터 타입,

//NULL의 여부, Key의 여부, DEFAULT의 여부, 기타 에 관한 정보가 출력된다

 

8) load data local infile
'C:/mysql/pet.txt' into table pet;

//C:\mysql\에 있는 pet.txt 내에 있는 정보를 pet이란 테이블에 불러와서 입력한다.

//텍스트 파일로 데이터 입력시 각각의 필드는 tab이나 띄어쓰기로 구분하도록 디폴트

//텍스트 파일 입력 중 누락된 값들은 NULL값으로 자동처리된다.

//주의할 점은 mysql에서 위치입력시 Dos처럼 \를 쓰는 것이 아니라 /를 사용한다

9) load data local infile
'C:/mysql/pet.txt' into table pet
lines terminated by '\r\n';

//\r\n을 라인 터미네이터로 사용한다

 

10) insert into pet
values('Puffball','Diane','hamster','f',1993-03-30',NULL);

//pet이란 테이블에 값을 입력, 이 때는 NULL을 써줘서 누락된 값을 표시한다.

 

11) select * from pet;

//pet테이블에 있는 모든 정보 출력

 

12) delete from pet;

//pet테이블에 있는 모든 정보 삭제, 이 명령어는 텍스트 파일의 자료 중 잘못된 값이 많을 때

//텍스트 파일의 정보 수정 후 다시 입력하려고 할 때 필요하다. 만약 정보 수정 후 바로 load

//data를 해주게 되면 정보가 중복입력이 되기 때문이다.

 

13) update pet set birth = '1989-08-31' where name = 'Bowser';

//pet 테이블에서 name이 Bowser인 줄의 birth를 1989-08-31로 수정한다,

//update A set B=C where D set E의 형태로 작성하면

//A테이블의 D필드의 값이 E인 줄에서 B필드의 값을 C로 수정한다.

14) select *from pet where name = 'Bowser';

//pet 테이블에서 name이 Bowser인 줄을 모두 출력한다.

 

15) select *from pet where birth >= '1998-1-1';

//pet 테이블에서 birth가 1998-1-1 이후인 줄을 출력한다.

//만약 비교연산자 사용시 문자를 사용하면 ASCII코드 값을 비교한다.

//예를 들어 select *from pet where name >='S';를 타입하면

//'T~Z'까지의 문자로 시작되는 이름을 가진 줄들이 출력된다.

16) select *from pet where species = 'snake' or species = 'bird';

//pet 테이블에서 species가 snake이거나 species가 bird인 줄을 출력한다.

//or 뿐만 아니라 and도 사용된다.

//예를 들어 select *from pet where species ='cat' and owner = 'Gwen';

//을 타입하면 pet 테이블의 species가 cat이고 owner가 Gwen인 줄이 출력된다.

 

17) select *from pet where (species='cat' and sex='m')
or (species='dog' and sex='f');

//pet 테이블에서 species가 cat이고 sex가 m인 줄이거나 species가 dog이고 sex가 f인 줄은

//모두 출력한다.

//괄호를 쓰는 까닭은 and와 or 중 우선순위가 and가 더 높기 때문에

//혼용할 수 있으므로 방지차원에 쓴다.

 

18) select name, birth from pet;

//pet테이블에서 name과 birth필드값들을 모두 출력한다.

//이는 특정 행을 선택하여 출력하는 것과 같다.

 

19) select distinct owner from pet;

//pet테이블에서 같은 이름의 owner는 한 번만 출력하도록 owner필드값들을 모두 출력한다. 

 

20) select name, species, birth from pet
where species='dog' or species='cat';

//pet테이블에서 species가 dog이거나 cat인 줄의 name, species,birth를 출력한다.

 

21) select name, birth,

(year(curdate())-year(birth) - (right(curdate(),5)<right(birth,5))

as age

from pet;

 

//pet 테이블에서 name과 birth 그리고 age를 출력한다.

//age는 오늘 날짜(curdate()는 오늘 날짜를 불러오는 MySQL지원 메소드) 중 연도에서

//생일의 연도를 뺀 다음에

//right(curdate(),5)는 curdate() 중 오른쪽 5자리 (몇월-몇일)를 추려내서 birth의 월일과

//비교했을 때 작으면 1, 크거나 같으면 0이란 값을 출력하게 된다.

//(비교연산자 식이 true면 1, false면 0을 리턴한다)

//그래서 나온 값을 빼주면 나이가 된다.(조금만 생각해보시면 이 식이 이해가 갈껍니다.;;)

 

22) select * from pet where name like 'b%';

// pet테이블에서 name이 b로 시작되는 줄을 찾는다.

// '_'을 사용하면 단일문자 매칭

// '%'를 사용하면 문자의 부정 숫자를 매칭

// SQL 패턴 사용시 논리연산자 사용불가

// 대신 like 또는 not like를 사용할 수 있다.

 

23) select * from pet where name like '%fy';

// pet테이블에서 name이 fy로 끝나는 줄을 찾는다.

 

24) select * from pet where name like '%w%';

// pet테이블에서 name이 이름 내에 w를 포함하고 있는 줄을 찾는다.

 

25) select * from pet where name like '_____';

// pet테이블에서 5개의 문자로 name이 되어있는 줄을 찾는다.

// _의 갯수만큼의 문자검색을 할 수가 있다.

 

26) select * from pet where name  regexp '^b';

// pet테이블에서 name이 b로 시작하는 줄을 찾는다.

// regexp는 테스트가 되는 값이 있는 모든 곳에서 매칭

// like는 전체값에서만 패턴매치를 진행

 

27) select * from pet where name regexp 'fy$';

// pet테이블에서 name이 fy로 끝나는 줄을 찾는다.

 

28) select * from pet where naem regexp 'w';

// pet테이블에서 name이 중간에 w를 가지고 있는 줄을 찾는다.

 

29) select * from pet where regexp '^.....$';

// pet테이블에서 name이 5문자인 줄을 찾는다.

// regexp에서는 .을 단일문자로 취급한다.

 

30) select * from pet where name regexp '^.{5}$';

// pet테이블에서 name이 5문자인 줄을 찾는다.

// {n}은 repeat - n - times 연산자이다.

 

31) select count(*) from pet;

// pet테이블에 몇 개의 줄이 있는지 찾는다.

 

32) select owner, count(*) from pet group by owner;

// pet테이블에서 owner의 이름에 따른 줄이 몇 개가 있는지 그룹화한 결과를 출력한다.

// group by n은 n이란 속성에 따라 그룹화한다.

// 만약 select 후 n만 사용하고 뒤에 group by n을 붙이지 않으면 에러가 발생한다.

// ex) select owner, count(*) from pet; <- ERROR 1140

 

33) select species, sex, count(*) from pet group by species, sex;

// pet테이블에서 species와 sex의 조합에 따른 줄이 몇 개가 있는지 그룹화한 결과를 출력한다.

 

34) select species, sex, count(*) from pet

where species = 'dog' or species = 'cat'

group by species, sex;

// pet 테이블에서 species 이름이 dog 나 cat인 줄들을 species와 sex의 조합으로 그룹화하여 결과를 출력한다.

 

35) select species, sex, count(*) from pet

where sex is not null

group by species, sex;

// pet테이블에서 sex가 null이 아닌 줄들을 species와 sex의 조합으로 그룹화하여 결과를 출력한다.

 

36) select pet.name,

(year(date)-year(birth)) as age,

remark from pet, event

where pet.name = event.name and event.type='litter';

// pet테이블의 name과

// 오늘 날짜(date) 중 년도에서 birth의 년도를 뺀 것을 age로 한 것과

// remark를

// pet과 event 테이블에서

// pet테이블의 name이 event테이블의 name과 같고

// event테이블의 type이 litter인 줄을 출력한다.

// 만약 name이나 type처럼 두 테이블에 동시에 있는 field같은 경우

// (테이블명).(필드명)을 사용한다.

// 사용 안 할시 ambigugous 에러 발생

// ex) pet.name, event.type

 

37) select p1.name, p1.sex, p2.name, p2.sex, p1.species

from pet as p1, pet as p2

where p1.species=p2.species and p1.sex='f' and p2.sex = 'm';

// pet테이블 과 pet테이블, 즉 같은 2개의 테이블에서

// pet테이블에서 species값은 같지만

// sex에서 값이 'f'와 'm'으로 다른

// 줄의 name, sex를 각각 출력하고 species도 출력한다.

// 이 때 두 species는 같으므로 한 테이블의 species만 출력

 

38) select name,birth from pet order by birth;

// pet테이블에서 birth를 기준으로 오름차순으로 정렬된 name과 birth 출력

 

39) select name,birth from pet order by binary name;

// pet테이블에서 name을 기준으로 대소문자 구별하여 오름차순으로 정렬된 name과 birth 출력

// binary를 사용하면 ASCII코드 값의 크기에 따라 정렬

 

40) select name, birth from pet order by birth desc;

// pet 테이블에서 birth를 기준으로 내림차순으로 정렬된 name과 birth를 출력한다

// 마지막에 desc입력시 내림차순 정렬

// desc는 바로 앞에 있는 birth에만 영향을 미친다.

// 하나의 컬럼 이상의 컬럼에 영향을 주고 싶을 땐 계속 써넣어줘야 한다.

 

41) select name, species, birth from pet

order by species,birth desc;

// pet테이블에서 species와 birth 내림차순으로 기준을 한 name,species,birth를 출력한다.

// 먼저 species기준으로 정렬후 birth 내림차순을 기준으로 삼아 정렬한다.

 

42) show index from pet;

// pet테이블에 있는 인덱스들을 보여준다.

 

43) !!이 구문은 윈도우 프롬프트 창에서 쓰는 명령어다!!

C:\> mysql -e "source c:/mysql/src.txt"

// mysql을 사용하여 c:\mysql\src.txt란 스크립트를 실행시킨다

// 만약 엑세스 문제가 발생한다면

// mysql -h localhost -u root -pmyrilke -e "source c:/mysql/src.txt"

// localhost에서 사용자 이름이 root이고 비밀번호가 myrilke인 계정으로 mysql에 접속하여

// c:\mysql\src.txt란 스크립트를 실행시킨다

 

44) source c:\mysql\src.txt;

// c:\mysql\src.txt란 스크립트를 실행시킨다.

// 이 것은 mysql프롬프트 에서 쓰는 것으로 기능은 위와 똑같다

 

45) \. c:\mysql\src.txt

// c:\mysql\src.txt란 스크립트를 실행시킨다

// 끝에 ;(세미콜론)을 붙이지 않는 점에 주의

 

46) select max(article) as article from shop;

// shop 테이블에서 article중에서 가장 큰 값을 가진 것을 article이라는 이름으로 출력

// select max(A) as B from C;

// C테이블에서 A중에 가장 큰 값을 가진 것을 B라는 이름 하에 출력

 

47) select article, dealer, price

from shop

where price=(select max(price) from shop);

// shop 테이블에서 price가 가장 큰 값인 줄의 price를 선택하여 그 줄에 있는 article, dealer, price를 출력

 

48) select article, dealer, price

from shop

order by price desc

limit 1;

// shop테이블에서 price를 내림차순으로 정렬하여 그 중 위에서 1개의 줄만 article,dealer,price를 출력

// limit에 들어가는 값은 출력해낼 줄의 숫자

// 47번 예제보다 좀 더 유동성있고 사용하기 편함

 

49) select article, max(price) as price

from shop

group by article;

// shop테이블에서 article, price 중에 가장 큰 값만 price로 하여 article로 그룹화한 결과를 출력

 

50) select article, dealer, price

from shop s1

where price=(select max(s2.price)

from shop s2

where s1.article = s2.article);

//shop테이블에서 price가 가장 높은 줄의 article,dealer,price를 출력한다.

 

51) select @min_price:=min(price),@max_price:=max(price) from shop;

select * from shop where price=@min_price or price=@max_price;

// shop테이블에서 min_price라는 변수를 선언하고 그 값은 price중 가장 작은 값

// 또한 shop테이블에서 max_price라는 변수를 선언하고 그 값은 price중 가장 큰 값

// shop테이블에서 price가 min_price값을 가지거나 max_price값을 가진 줄 모두를 출력

 

52) select name, sex

from pet where name='Claws'

union

select name,sex

from pet where sex='f';

// pet테이블에서 name이 Claws인 줄의 name, sex를 출력하고

// pet테이블에서 sex가 f인 줄의 name,sex를 출력한다.

// union은 두 개의 쿼리문 결과를 붙여서 보여준다.

// 따라서 select하는 필드값은 일치해야 한다.