이 메뉴얼은 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하는 필드값은 일치해야 한다.
