[MySQL날짜 차이 구하기

※ 경고
높이뜬새의 모르는 사람이 없는 팁시리즈 입니다.
이 팁시리즈는 지면낭비라는 항의시에 즉각 중단됩니다.

MySQL에서 날짜간의 차이를 구하기 위해서는 TO_DAYS 함수를 사용하시면 됩니다. TO_DAY 함수는 TO_SECONDS 함수처럼 지원하는 버전대가 있는 것도 아니라서 MySQL 버전이 몇이든 상관없이 사용하실 수 있습니다.



형식) MySQL프롬프트> SELECT TO_DAYS(DATE) - TO_DAYS(DATE);


예) 2010년 2월15일과 2010년1월25일의 차이 구하기

mysql> SELECT TO_DAYS('2010-02-15') - TO_DAYS('2010-01-15' ) AS CHA;

예) 2010년의 설날연휴인 2010년 2월13일은 오늘부터 몇 일 남았나 구하기

mysql> SELECT TO_DAYS('2010-02-13') - TO_DAYS(now()) AS CHA;


※ TO_DAYS 함수는 기본 인자로 DATE 형을 취하기 때문에 아래와 같이 CAST함수를 이용해서 DATE형으로 형변환 해서 쓰실 수도 있습니다.


mysql> SELECT TO_DAYS(CAST('2010-03-01' AS DATE)) - TO_DAYS(CAST('2010-01-25' AS DATE)) AS CHA;






테이블 이름에서 근무일이 300일 미만을 출력해라

select * from테이블이름 where to_days(now())-to_days(근무시작일) < 300

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거
계층형 게시판( 댓글이 있는 )의 구조와 알고리즘에 대해서 알아보자.
설명을 위해 테이블에는 다음과 같은 컬럼들만 있다고 가정한다.

이해를 돕기 위해 수행되는 쿼리와 수행후 테이블의 row를 그려 넣었다. 

no : 글번호(Primary Key)
title : 글제목
grp : 같은 주제를 갖는 게시물의 고유번호. 
       부모글과 부모글로부터 파생된 모든 자식글은 같은 번호를 갖는다.
seq : 같은 그룹내 게시물의 순서
lvl : 같은 그룹내 계층

[MySQL 관련 명령어]
alter table board auto_increment = 1; 
-- 자동증가값 사용시 최초값 셋팅
 
set last_insert_id = 0;
-- 마지막으로 사용된 auto_increment 값 초기화

select last_insert_id() 
-- 마지막으로 사용된 auto_increment 값 반환


예제)
1) 게시판에 첫번째 글이 올라 올때
  

insert into board( title, grp, seq, lvl ) 
values('안녕하세요', (select last_insert_id()+1), 1, 0);
 no title grp seq lvl
 1 안녕하세요 1 1 1
새글(글 등록)의 경우,
no 는 AutoIncrement 로부터 받아온 값
grp는 no와 동일한 값 
seq는 1
lvl은 0 이된다.


2) 두번째, 세번째 글이 올라올때

insert into board( title, grp, seq, lvl ) 

values('날씨가 맑습니다', (select last_insert_id()+1), 1, 0);

no title grp  seqlvl 
1안녕하세요 
 2날씨가 맑습니다 
 3모임이 있습니다 


3) 첫번째 글에 댓글이 올라올때

update board

set seq = seq + 1

where grp = 1

and seq > 1;

insert into board( title, grp, seq, lvl ) 

values('반가워요', 1, 1+1, 0+1);

 no titlegrp seq lvl 
 1 안녕하세요
 2날씨가 맑습니다 
 3 모임이 있습니다3
 4반가워요 
 
댓글의 경우, ( 부모글 : '댓글이 달린 바로 상위글' 원본글일수도 있고 댓글일 수도 있음 )
grp 는 부모글의 grp 값
seq 는 부모글의 seq+1
lvl 은 부모글의 lvl+1 이 된다.

댓글을 삽입하기 이전에
한 글에 대한 댓글이 여러개인 경우 우선순위를 맞춰주기 위해서 
부모글과 동일한 그룹 번호에 있으면서 
부모글의 seq 번호보다 큰 게시물의 seq 번호를 모두 1만큼 증가시킨다


4) 첫번째 글의 댓글에 댓글이 올라올때 
(즉, 네번째 글의 댓글이 댓글이 삽입된 후)

update board

set seq = seq + 1

where grp = 1

and seq > 2;

insert into board( title, grp, seq, lvl ) 

values('감사합니다', 1, 2+1, 1+1);

 no titlegrp seq lvl 
 1 안녕하세요
 2날씨가 맑습니다 
 3 모임이 있습니다3
 4반가워요 
 5 감사합니다



5) 첫번째 글의 두번째 댓글이 올라올때

update board

set seq = seq + 1

where grp = 1

and seq > 1;


insert into board( title, grp, seq, lvl ) 

values('감사합니다', 1, 1+1, 0+1);

 no titlegrp seq lvl 
 1 안녕하세요
 2날씨가 맑습니다 
 3 모임이 있습니다3
 4반가워요 
 5 감사합니다
 6 환영합니다


6) 여섯번째 게시물에 댓글이 올라올때

update board

set seq = seq + 1

where grp = 1

and seq > 2;


insert into board( title, grp, seq, lvl ) 

values('감사합니다', 1, 2+1, 1+1);

 no titlegrp seq lvl 
 1 안녕하세요
 2날씨가 맑습니다 
 3 모임이 있습니다3
 4반가워요 
 5 감사합니다
 6 환영합니다
 7 감사합니다

검색(select) 할 때는 grp를 내림차순으로
seq를 오름차 순으로 정렬하면 된다. 
인덱스는 기본키와 grp+seq의 복합 인덱스를 설정하면 된다.

select * from board order by grp desc, seq asc;

[검색결과]
no title     grp seq lvl
--------------------------------------------
3 모임이 있습니다 3 1 0
2 날씨가 맑습니다 2 1 0
1 안녕하세요 1 1 0
6 환영합니다 1 2 1
7 감사합니다 1 3 2
4 반가워요        1 4 1
5 감사합니다 1 5 2




아래 예는 오라클에서 테이블을 구성하고 검색해 볼 수 있는 환경을 위한 SQL 이다. 

오라클은 AutoIncrement 기능이 없기때문에 sequence 를 이용한다.

<DDL> 예제
 

drop table board;


create table board(

no int primary key,

title varchar2(45),

grp int,

seq int,

lvl int);


drop sequence board_no_seq;


create sequence board_no_seq start with 1 increment by 1;


create index board_grp_seq on board(grp desc, seq asc);


<DML> 예제


[첫번째 게시물 입력 - 새 글 입력]

insert into board

values(board_no_seq.nextval, board_no_seq.currval, 1, 0);


[두번째 게시물 입력 - 새 글 입력]

insert into board

values(board_no_seq.nextval, board_no_seq.currval, 1, 0);


[세번째 게시물 입력 - 새 글 입력]

insert into board

values(board_no_seq.nextval, board_no_seq.currval, 1, 0);


[네번째 게시물 입력 - 첫번째 게시물의 답글]

(답글이 올라오는 경우에는 답글에 부여 할 seq를 먼저 확보해준다.)

update board

set seq=seq+1

where grp=1 and seq > 1;


insert into board

values(board_no_seq.nextval, '반가워요', 1, 2, 1);


[다섯번째 게시물 입력 - 네번째 게시물의 답글]

update board

set seq=seq+1

where grp=1 and seq > 2;


insert into board

values(board_no_seq.nextval, '감사합니다', 1, 3, 2);


[여섯번째 게시물 입력 - 첫번째 게시물의 두번째 답글]

update board

set seq=seq+1

where grp=1 and seq > 1;


insert into board

values(board_no_seq.nextval, 1, 2, 1);


[일곱번째 게시물 입력 - 여섯번째 게시물의 답글]

update board

set seq=seq+1

where grp=1 and seq > 2;


insert into board

values(board_no_seq.nextval, '감사합니다', 1, 3, 2);



[게시판을 검색하는 경우, grp로 내림차순, seq로 오름차순으로 정렬하면 됩니다.]

select case when lvl=0 then no

when lvl>0 then null end no

, rpad('+', lvl, '-')||title title

from board

order by grp desc, seq;


[검색결과]

no    title 

----------------

3 모임이 있습니다 

2 날씨가 맑습니다 

1 안녕하세요  

+환영합니다  

+-감사합니다  

+반가워요 

+-감사합니다 


출처 : http://hdm6337.tistory.com/entry/%EA%B3%84%EC%B8%B5%ED%98%95-%EA%B2%8C%EC%8B%9C%ED%8C%90-%EC%95%8C%EA%B3%A0%EB%A6%AC%EC%A6%98-%EB%8C%93%EA%B8%80-%EC%95%8C%EA%B3%A0%EB%A6%AC%EC%A6%98

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거

Limit 연산자를 이용하면 쿼리 결과 개수를 제한할 수 있다.

1
select * from 테이블명 Limit 개수(정수);



예) cookie_sales 테이블에서 first_name별로 그룹을 만들어 sales컬럼의 합계가 가장 높은 순으로 
     출력하되, 2개까지만 출력한다.

1
2
3
4
select first_name, sum(sales) from cookie_sales
group by first_name
order by sum(sales) desc
Limit 2;

결과

 first_namesales 
 Britney107.91 
 Paris98.23 





Limit의 조금 다른 사용법

아래처럼 두 개의 정수가 들어간다면 완전히 다른 의미가 된다. 

Limit 0, 4 
                  0은 시작할 레코드의 번호이다. (SQL은 0이 처음)
                  4는 반환할 결과의 수이다. 


예를 들어 음악 챠트에서 20위에서 30위까지 보고 싶다고 하면 
Limit 19,10 으로 하면 된다~



출처: http://warmz.tistory.com/259

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거


DBConnectionMgr.java


객체로 생성후 사용


DBConnectionMgr pool = new DBConnection();

conn = pool.connection(); 으로 연결


다 쓴후 pool.close(conn); 이런식으로 닫음 

끝!

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거

0. MySQL 콘솔 접속

MySQL에서 사용자를 관리하려면 root@localhost 아이디로 접속해야 합니다.

$ mysql -uroot -p
Enter password: 비밀번호입력
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16465
Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1. MySQL 전체 사용자 목록 보기

MySQL에 설치된 전체 사용자를 보려면 명령어로 직접 확인할 수는 없고 mysql 데이테베이스를 선택해 user 테이블의 레코드를 봐야 합니다.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT User, Host from user;
+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
|                  | localhost |
| debian-sys-maint | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

MySQL에서 사용자란 “아이디@호스트”의 결합입니다.

여기서 호스트는 MySQL 서버에 접속하는 컴퓨터를 의미합니다. root@localhost 사용자와 root@127.0.0.1 사용자는 서로 다릅니다. localhost와 127.0.0.1 주소는 네트워크상 같은 의미이지만 MySQL은 서로 다른 호스트에서 접속하는 것으로 간주합니다. 이 점은 MySQL의 사용자를 이해하는데 있어 상당히 중요합니다.

Host는 위와 같이 IP 주소이거나 도메인 이름일 수 있습니다. 아니면 % 같은 기호로 모든 호스트를 나타낼 수도 있으며 이 경우 MySQL 서버에 접속할 수 있는 컴퓨터에 제한을 두지 않는다는 의미입니다. 이는 보안상 추천하지 않습니다.

2. 사용자 만들기

test@localhost라는 사용자가 “test” 비밀번호로 접속할 수 있도록 만듭니다.

mysql> CREATE USER test@localhost IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

3. 사용자에게 데이터베이스 권한 부여

test@localhost 사용자에게 test 데이터베이스 안의 모든 테이블(test.*)에 관한 모든 권한을 부여합니다. 특정 데이터베이스 안의 모든 테이블을 나타내려면 * 문자를 사용합니다.

mysql> GRANT ALL PRIVILEGES ON test.* TO test@localhost;
Query OK, 0 rows affected (0.00 sec)

4. 사용자에 부여된 권한 확인

test@localhost 사용자의 모든 권한을 확인합니다.

mysql> SHOW GRANTS FOR test@localhost;
+---------------------------------------------------------------------------------+
| Grants for test@localhost                                                       |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE1908' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost'                          |
+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

5. 사용자에게서 권한 박탈

test@localhost 사용자에게서 test 데이터베이스 안의 모든 테이블에 대해서 모든 권한을 박탈합니다.

mysql> REVOKE ALL PRIVILEGES ON test.* FROM test@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR test@localhost;
+---------------------------------------------------------------------------------+
| Grants for test@localhost                                                       |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE1908' |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GRANT ALL PRIVILEGES 명령어를 주기 이전으로 test@localhost 사용자는 아직 로그인이 가능합니다.

만약 위의 USAGE 권한까지 박탈하려면 다음과 같이 명령합니다.

mysql> REVOKE USAGE ON *.* FROM test@localhost;
Query OK, 0 rows affected (0.00 sec)

USAGE 권한이 없으면 로그인이 안 됩니다.

다시 USAGE 권한을 주고 싶다면 아래와 같이 명령해야 합니다.

mysql> GRANT USAGE ON *.* TO test@localhost IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

6. 사용자 삭제

test@localhost 사용자를 삭제합니다.

mysql> DROP USER test@localhost;
Query OK, 0 rows affected (0.00 sec)

부가적으로 해당 사용자에게 부여된 권한(GRANTS)까지 모두 삭제됩니다. 별도로 권한을 박탈하는 과정을 거칠 필요가 없습니다.

이상 MySQL 데이터베이스에서 사용자 관리하는 방법을 살펴봤습니다. 사용자 생성, 삭제 그리고 권한 부여, 박탈 이렇게 생각할 수 있습니다.

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거

MySQL 테이블에 데이터를 넣는 방법 중 csv 파일을 직접 넣는 방법이 있다.


csv 파일과 DB 테이블 구조가 일치하면 아래와 같이 입력하면 된다.



1
2
LOAD DATA LOCAL INFILE "filePath"
INTO TABLE dbName.tableName FIELDS TERMINATED BY ",";



만약 파일의 크기가 크고 테이블에 index가 걸려 있으면 시간이 굉장히 오래 걸릴 수 있다.

그럴 경우에는 아래와 같이 index를 먼저 해제해 주고 파일을 올린 다음 다시 index를 걸면 상당히 빠르게 테이블에 데이터를 넣을 수 있다.


1
2
3
4
ALTER TABLE dbName.tableName DISABLE KEYS;
LOAD DATA local INFILE "filePath"
INTO TABLE dbName.tableName FIELDS TERMINATED BY ",";
ALTER TABLE dbName.tableName ENABLE KEYS;


대략 2GB 정도 되는 파일도 10분 정도로 import가 완료됐다.



만약 csv 파일 구조와 DB 테이블 구조가 다른 경우는 아래 샘플 sql 문 참조해서 import 한다.



1
2
3
4
5
6
LOAD DATA LOCAL INFILE 'file_name'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1,column2,column3, ...);

LOAD DATA LOCAL INFILE "c:\\zipcode2014.cvs"

INTO TABLE onepageportfolio.adress FIELDS TERMINATED BY "," optionally enclosed by '"' lines terminated by

 '\n';

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거

보통 Mysql DB 덤프를 뜰때 OS 가 리눅스인 경우가 많았다리눅스에서는

shell 로 들어가서 아래와 같은 명령어로 dump 를 떠서 백업하고 복구를

하면 된다.

 

특정 데이타베이스 하나만을 백업

백업형식 : myslqdump -u DB계정명 -p 백업대상데이터베이스명 > 저장할파일명

복구형식 : mysql -u DB계정명 -p 복구할테이터베이스명 < 저장할파일명

 

# ./mysqldump -u root -p mysql > mysql.sql      //mysql 디비 백업 예

# ./mysql -u root -p mysql < ./mysql.sql           //mysql 디비 복구 예

 

하지만 OS  ms 계열일때 어떻게 할까? MySql 을 설치하게 되면

MySQL Command Line Client 라는게 있는데 여기에서 명령어를

실행하면 되지 않을까아래 그림에서 보는것과 같이 문법 에러가 난다.



mysqldump 명령어를 실행하기 위해서는 mysql 이 설치 되어있는 곳에 bin 폴더로 간다

그중 mysqldump.exe 가 있는데 cmd 창에서 이것을 이용해 명령어를 실행하면 된다.

문법은 위에서 제시한 것과 동일하다.

문법에 맞게 계정명과 DB명을 입력하고 출력될 파일명을 적은후 Enter 를 치면

패스워드 입력창이 나타나게 되는데 DB 접속 패스워드를 입력하면 된다.



복구는 문법이 mysql 로 시작한다그러므로 bin 폴더안에 mysql.exe. 를 커맨드

창에서 실행해서 복구를 하면 된다.

만약 DB 복구가 여의치 않다면 파일을 문서유틸로 열어서 Toad 같은 DB 관리 툴에

복사한후 실행을 시키면 DB 테이블 생성과 데이타가 함께 들어가서 원상 복구가 된다.



출처: http://mainia.tistory.com/590


Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거

MySQL

 

-데이터베이스 생성

mysql > create database haksa;

 


 

++ show databases; 는 데이터베이스를 모두 보여준다.

++ ; 이 명령문의 끝에 사용되어서 명령문이 길어질 경우 엔터치고 사용할 수 있다.

 

-데이터베이스 사용

mysql > use haksa;

 


 

-테이블 만들기

 

++쿼리문 만들때, 메모장에서 작업 후에 cmd창에서 하는 것이 나중에 오타가 났을 경우 고치기 쉽다!

 

<insa 테이블  생성>

create table insa(

bunho int(1) auto_increment,

name char(8) not null,

e_name char(4) not null,

town char(6) not null,

primary key(bunho)

); // 이거 작성하는데도 오타나서 고쳤네요 ㅋㅋㅋ


결과는,                                                                                                            테이블 결과 값을 보면 아래와 같습니다.

                                            

<create table 테이블명();>                                                                                         <show tables;>

 


<desc 테이블명;> -> 테이블 구조를 보여줍니다.

 

명령어를 정리해보면

mysql > create table 테이블명(); ->테이블 만들기

mysql > show tables; -> 테이블 모두 보여주기

mysql > desc 테이블명; -> 테이블 속성 보여주기

 

-데이터 입력(insert)

 

ex>

insert into insa values('1','홍길동','Hong','순천');

insert into insa values('2','제갈공명','Je','여수');

insert into insa values('3','순자','Soon','부산');

insert into insa values('4','이순신','Lee','여수');

insert into insa values('5','니얼굴','NII','내마음');                      

 

오른쪽과 같이 적용됩니다.

 

insert 문법은

 

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

 

이렇다고 합니다. (이렇게 보니까 더 어렵게 느껴지지만 여러가지 방법이 있으니 좋은거죠 ㅎㅎ)

 

insert 한 데이터를 확인해봅시다.

 

mysql > select * from insa;

 

select 문은 데이터를 가져올때 쓰이는데, *의 의미는 '모두'

from insa 는 insa 테이블에서 가져오겠다 라는 의미입니다.     

 

 

 

 

-자료 수정(update)

mysql > update insa

->set town = '한산도'

->where bunho = 4;

                          이순신 장군님 town 값이 여수 -> 한산도가 됬습니다.

 

++ Autocommit 에 대한 부분

autocommit은 자료를 입력하고 수정하고 삭제하는 것이 실제로 데이터가 반영되는것을 설정하는 값인데,

autocommit = 1  이면 자동적으로 반영되는 것이고,

autocommit = 0  이면 메모리상에서만 반영되고 commit 하면 실제로 데이터가 저장됩니다.

데이터를 저장하고 싶지 않고 원 상태로 돌리려면 rollback 하면 데이터가 복구 됩니다.

 

화면을 보면,

autocommit=0



                            다시 접속합니다.

 


                        적용이 되지 않았습니다.

여기서 바꾸고 나서 commit 하면 적용(홍길동으로 저장됨)이 되고 rollback하면 원래 "최길동"으로 돌아갑니다.

 

 

savepoint & truncate

savepoint는 rollback 시점을 정해주는 것.

mysql > savepoint aa;

mysql > rollback to aa; -> aa 지점으로 롤백합니다.

 

truncate 는 테이블의 삭제 처리(모든 행이 삭제 처리됨)

->autocommit 상관없이 다 지워버림(실제로 적용됨)

->돌이킬수 없다.

->위험하다.

->기억에서 지우자

 

 

 

 


Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거

출처 : 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하는 필드값은 일치해야 한다.

Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거



Posted by 앗뜨거워 ( @bok3937 ) 앗뜨거