table test 의 column이


name, score가 있다고 할 때


John / 50

Matthew / 40

Cain / 70


일 때 


select @rownum:=@rownum+1 as num, t.* from test t, (SELECT @rownum:=0) r 


로 하면 


num/name/score


1 / John / 50

2 / Matthew / 40

3 / Cain / 70


로 되고


select @rownum:=@rownum+1 as num, t.* from test t, (SELECT @rownum:=0) r  ORDER BY score DESC


로 하면


1 / Cain / 70

2 / John / 50

3 / Metthew / 40


로 나온다

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

여러가지로 검색하고 해보아도 초보입장에서 쉽게 이해가고, 되는 내용들이 없어 Mysql사이트의 다큐먼트를 참고로해서 작업해보았습니다. 


다른방법들에 비해 약간 번거롭긴하지만, 정확하고 빠른 데이타 삽입이 가능하여, 혹시 저처럼 해매시는 분들이 계실까 하여 올려봅니다. 


[상황] 


mysql에 사용할 db와 테이블을 생성하였으며, 각 테이블에는 사용할 컬럼이 정의되어있다. 그런데 이곳에 엑셀파일로 가지고 있는 데이타를 한방에 빠르게 입력하고 싶다. 


예) 데이타 베이스: testdb 

    테이블명      : member 

    member 테이블의 컬럼: name,id,password,phone,mphone,email 


    데이타 베이스의 캐릭터셋은 utf-8이다. 


컬럼데이타에 해당하는 엑셀파일 memberinfo.xls파일을 가지고있다. 


==================================================== 


상황은 다들 이해 가시죠?? 지금부터 작업을 시작해 보겠습니다. 


1. 엑셀파일에는 입력할 데이타가 테이블 컬럼 순서에 맞게 입력이 되어있어야 합니다. 

    즉, 엑셀파일의 왼쪽부터 "이름/아이디/패스워드/전화번호/핸드폰/이메일" 이런식 

    으로 데이타가 입력되어있어야 합니다. 컬럼명은 필요없습니다. 데이타만.... 


2. 이상태의 엑셀파일을 다른이름으로 저장 합니다. ==>txt파일(탭으로분리) 


3. 이부분이 중요합니다. 이부분이 생략되면 한글이 정상적으로 입력되지 않습니다. 

    생성된 텍스트 파일을 윈도우에서 열어줍니다. 

    그냥 열기만 하면됩니다.  열린상태에서 그대로 다른이름으로 저장 합니다. 

    이때, 가장아래 쪽에 보면 인코딩 방식을 선택할 수 있는 곳이있습니다. 

    그곳에서 인코딩 방식을 UTF-8로 선택하고 저장합니다. 


4. 이렇게 utf-8방식으로 저장된 텍스트 파일을 ftp를 이용해 계정에 올립니다. 


5. ssh/telnet등을 이용해 계정에 접속하고 mysql에 접속합니다. 


6. 사용할 DB를 선택하시고, 예) mysql> use testdb; 


7. 다음과 같이 입력합니다. 

  


    load data infile '파일경로' into table 테이블명 (컬럼네임1,컬럼네임2,----) 


  위의 예로 작성해 보면 

(계정은 test / 파일업로드 경로는 /home/test ,텍스트파일은 member.txt, 테이블명은 member라 가정합니다.) 


load data infile '/home/test/member.txt' into table member (name,id,password,phone,mphone,email) 

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

mysql DATETIME형의 기본값으로 현재시간 설정

mysql에서 시간을 저장하는 컬럼에 현재시간을 기본으로 설정하려면 기본값을 CURRENT_TIMESTAMP로 하면 된다. CURRENT_TIMESTAMP를 기본값으로 하면 INSERT나 UPDATE시 now()를 쓰는 것과 같은 효과를 볼 수 있다.

하지만 DATETIME형의 경우 5.6버전 이후부터 CURRENT_TIMESTAMP를 사용할 수 있다.

5.5이하 버전에서 기본값으로 현재시간을 설정하려면 DATETIME형 대신 TIMESTAMP형을 사용한다. 저장하려는 시간이 아래 기간 안에 있다면 문제없이 쓸 수 있다.

'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

위 시간에 벗어나는 시간대를 저장해야 하고 INSERT시 현재값을 저장하려면 아래 방법 중 1개를 선택한다.
1. mysql 5.6버전 이상으로 업그레이드하고 DATETIME형을 사용, DEFAULT값을 CURRENT_TIMESTAMP로 한다.
2. 5.5버전 이하에서는 DATETIME형으로 선언하고, INSERT시 now()를 사용해서 시간을 수동으로 설정한다.


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

MySQL이 설치된 폴더의 'BIN' 로 이동하세요.

파일의 형태로 백업이 됩니다.

 

1. 백업 

MySQLDump -u사용자 -p암호 DB이름 > DB백업.sql

ex) mysqldump -uroot -p1234 test_db > test_db.sql -- ('test_db' 전체 내용을 백업)

 

2. 테이블 백업

MySQLDump -u사용자 -p암호 DB이름 TB이름 > TB백업.sql

ex) mysqldump -uroot -p1234 test_db test_table > test_table.sq-- ('test_db' 의 'test_table' 내용을 백업)

 

3. 데이터 백업

MySQLDump -u사용자 -p암호 DB이름 TB이름 -w "조건" > 데이터백업.sql

ex) mysqldump -uroot -p1234 test_db test_table -w "name='홍길동' > test_table.sql 

 -- ('test_db' 의 'test_table' 에서 'name' 이 홍길동인 데이터만 백업) 

 

4. 복원 (데이터 등록)

MySQL -u사용자 -p암호 DB이름 < DB백업.sql -- DB가 존재 하지 않는 경우 미리 생성 후 진행 하여야 함.

MySQL -u사용자 -p암호 DB이름 TB이름 < TB백업.sql

MySQL -u사용자 -p암호 DB이름 TB이름 < 데이터백업.sql

 

주의) 복원 명령 시 기존의 테이블은 제거 된 후 재생성 하여 복원 됩니다. (기존 데이터는 삭제됨) 

 

TIP) 파일명 자동 생성 명령을 위한 명령문 

ex) mysqldump -uroot -p1234 test_db > test_db_%date%.sql -- ('test_db_2013-04-02.sql' 형태로 생성됨.)   

아래와 같이 "backup.bat" batch 파일을 만들면 내가 원하는 형태의 날짜및시간을 표현 할 수 있다. 

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

@echo off  

SET now=%date:~0,4% -- 2013-04-02 (0번째 부터 4개)
SET now=%now%%date:~5,2% -- 2013-04-02 (5번째 부터 2개)
SET now=%now%%date:~8,2%
 -- 2013-04-02
  

SET now=%now%_%time:~0,2% -- 14:10:10.10
SET now=%now%%time:~3,2% -- 14:10:10.10
SET now=%now%%time:~6,2% -- 14:10:10.10

mysqldump -uroot -p1234 %1 > %1_%now%.sql  -- %1 : 배치 파일 실행 시 포함 한 DB명

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

backup.bat test_db < 샐행  -- ('test_db_20130402_141010.sql' 형태로 생성됨.)   


출처 : http://blog.naver.com/kilsu1024/110165190855


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

mysql -u[아이디] -p [데이터베이스명] < [SQL파일경로]


아이디 : root
데이터베이스명 : test
SQL파일경로 : c:\test.sql

> mysql -uroot -p test < c:\test.sql
Enter password : 

위와 같은 화면이 나왔으면 정상적으로 명령어가 작동하는것 입니다. 패스워드를 입력하면 MYSQL에 Import가 이뤄집니다.


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

출처 : http://kwonnam.pe.kr/wiki/database/mysql/basic


MySQL의 기본 명령어와 SQL문 정리. 내가 RDBMS에 완전 문외한이기 때문에 기본 SQL쿼리까지...
Redhat Linux 7.3, MySQL 3.23.58 에서 테스트함.
설치는 Redhat 기본 제공 RPM으로 했다.

데이터베이스 접속

$ mysql -u 사용자명 -p dbname


설치 직후에는 root 사용자에 비밀번호가 없으므로 다음과 같이 접속하여 MySQL을 관리할 수 있다.

$ mysql -u root mysql



비밀번호 변경
MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.
다음 세가지 방법으로 비밀번호를 변경 할 수 있다.


  • mysqladmin이용
    $ mysqladmin -u root password 새비밀번호

  • update문 이용
    $ mysql -u root mysql

    mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
    mysql> FLUSH PRIVILEGES;


  • Set Password 이용
    SET PASSWORD FOR root=password('새비밀번호');


일단 root 비밀번호가 설정된 상태에서는 mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고 기존 비밀번호를 입력해야만 한다.

사용자 추가/삭제

mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';


username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. username은 dbname이라는
데이타베이스에 대해 모든 권한을 가지고 있다.
username 사용자는 로칼 호스트에서만 접속할 수 있다. 다른 호스트에서 접속하려면

GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';


위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.
추가 : '%'를 호스트네임으로 지정해도 모든 호스트에서 접속할 수 없었다. 각 호스트별로 다 지정해야 했다.
아래 댓글에서 rukikuki님이 알려주셨습니다.
GRANT ALL PRIVILEGES ON testdb.* TO 'username'@'%' IDENTIFIED BY 'password' ;
처럼 'username' 이런식으로 홑따옴표를 username 에도 붙여야 하더군요.. 

http://tech.zhenhua.info/2009/01/mysql-error-104528000-access-denied-for.html 에 따르면 '%'란 localhost 를 제외한 모든 호스트를 뜻한다고 한다. 즉, localhost에 대해서는 명시적으로 따로 권한을 지정해야 한다.

불필요한 사용자 삭제는

mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;



데이터베이스 생성/보기


  • 데이터베이스를 생성하고,
    mysql> CREATE DATABASE dbname;

  • 현재 존재하는 데이터베이스 목록을 보여준다.
    mysql> SHOW DATABASES;

  • 특정 데이타베이스를 사용하겠다고 선언한다.
    mysql> USE dbname;

  • 쓸모 없으면 과감히 삭제한다.
    mysql> DROP DATABASE [IF EXISTS] dbname;

    IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.



테이블 생성/보기


  • 테이블을 생성하고,
    mysql> CREATE TABLE tablename (
    column_name1 INT,
    column_name2 VARCHAR(15),
    column_name3 INT );

  • 현재 데이타베이스의 테이블 목록을 보고
    mysql> SHOW TABLES;

  • 테이블 구조를 살펴본다.
    mysql> EXPLAIN tablesname;
    혹은
    mysql> DESCRIBE tablename;

  • 이름을 잘못 지정했으면 이름을 변경할 수도 있다.
    mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];

  • 필요 없으면 삭제한다.
    mysql> DROP TABLE [IF EXISTS] tablename;



현재 상태 보기


mysql> status

--------------
mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686)

Connection id: 26
Current database: study
Current user: study@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 min 59 sec

Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 Flush tables: 1
Open tables: 1 Queries per second avg: 0.021
--------------



INSERT

mysql> INSERT INTO tablename VALUES(값1, 값2, ...);

혹은

mysql> INSERT INTO tablename (col1, col2, ...) VALUES(값1, 값2, ...);



SELECT

mysql> SELECT col1, col2, ... FROM tablename;


컬럼명을 *로 하면 모든 컬럼 의미.

mysql> SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;


컬럼의 이름을 바꿔서 출력.

mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS '총점' FROM tablename ORDER BY '총점' ASC;


DESC는 내림차순 ASC는 오름차순.

mysql> SELECT * FROM grade WHERE korean < 90;


조건줘서 SELECT.

mysql> SELECT * FROM grade LIMIT 10;


결과중 처음부터 10개만 가져오기

mysql> SELECT * FROM grade LIMIT 100, 10;


결과중 100번째부터 10개만 가져오기. 첫번째 레코드는 0번 부터 시작한다.

UPDATE

mysql> UPDATE tablename SET col1=새값 WEHER 조건



DELETE

mysql> DELETE FROM tablename WEHRE 조건



mysql에서 쿼리 결과 세로로 보기
-E 옵션을 줘서 실행한다.

$ mysql -E -u root -p mysql



mysql에서 발생한 오류나 경고 다시 보기

mysql> show errors;
mysql> show warnings;


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

mysql 명령어 모음


데이터베이스 생성 create database 데이터베이스명;
데이터베이스 목록보기 show databases;
데이터베이스 삭제 drop  database 데이터베이스명;
데이터베이스 선택 use 데이터베이스명;

테이블 생성 create table 테이블명(필드명, 타입, 공백 여부 등등);
테이블 보기 show tables;
테이블 구조 보기   desc 테이블명;
필드 추가 alter table 테이블명 add 새로추가할 필드명과 타입 (위치 설정[after 필드명]);
필드 삭제 alter table 테이블명 drop 삭제할 필드명;
필드명 변경 alter table 테이블명 change 기존 필드명 새로운 필드명 타입;
필드의 타입만 변경 alter table 테이블명 modify 기존필드명 새로운 타입;

테이블 이름 변경 alter table 기존 테이블명 rename 새 테이블명;
테이블 삭제 drop 테이블명;
---------------------------------------------------------------------------------
일괄실행 명령어 mysql -u root명 -p 비번 데이터베이스명 < 파일명
create table ep_tb(
sno int not null,
name varchar(10),
det char(20),
addr varchar(80),
tel char(20),
primary key(sno));

파일형식  sql로 library/bin/ 폴더에 저장
---------------------------------------------------------------------------------

단일레코드 변경
update  table name set 필드명 = 변경할 조건 where  필드명 = 기존 속성값
ex update haksa_tb set age = age - 3 where son  = 2002323;

전체 레코드 변경
update 테이블명 set 필드명 = 조건
update gk_tb set time = time - 1;

레코드 삭제
delete from table name where field name = 조건 ;

단일 레코드의 삭제 (like)
delete from haksa_tb where addr like '대전%';

특정 레코드 삭제 (where)
delete from 테이블명 where age >= 20 and age <= 30;

전체 체코드 삭제 
delete from table name;


백업 myslqdump -u -p 데이터베이스명 > 백업파일명

복원  mysql -u  -p db < 백업파일명



테이블의 모든 레코드 검색 select *from 테이블명;

load data 구문
load data infile "텍스트파일" into table 테이블명;
맥의 경우 기본 폴더 설정이 테이블까지 되어있음  mamp/db/mysql/디비명/파일.txt


중복된 레코드값 제거해서 출력
select distinct 필드명  from  테이블명;

조건 where
select  필드1 필드2 필드3 from  테이블명 where 조건;
(ex. select name, age, set, addr, sex from haksa_tb where sex = 'm';)

필드정렬
order by 필드명 sac(오름차순)-  작은 숫자부터 정렬  desc(내림차순) - 큰숫자부터 정렬
(ex. select name,age, det, addr, sex from haksa_tb where sex='M' order by age ASC;

결과값 제한 Limit
select *from 테이블명 order by 필드명 asc or desc limit 숫자;

집계함수 count

count(*) : 테이블 속에 속하는 레코드의 갯수
count(필드명) : null값이 아닌 레코드의 개수
sum(필드명) : 필드명의 값들의 합계
avg(필듬명) : 필드명의 값들의 평균
max(필드명) :  필드명의 값들의 최대값
min(필드명) : 필드명의 값들의 최소값

group by 는 필드명 기준으로 그 결과를 그룹화 한다.

select sno, sum(score) from jumsu_tb group by son;

group by 에 의해 수행된 결과에 대해 조건을 적용하고 검색 하려면 having절을 이용한다.
주의할점은 반드시 group by를 먼저 기술해야 한다.
ex . select son from jumsu_tb group by sno having count(*) >= 2;

특정 문자열 검색
_(언더스코어) 임의의 한문자 한글의 경우 2바이트이므로2개 사용 
예를 들어 준이라는 이름으로 끝나는 사람을 찾는다.  '__준'

null값을 갖는 정보 검색

select *from gk_tb where hakjum IS NULL;

null값이 정보를 제외하고 검색
select *from gk_tb where hakjum IS NOT NULL;

join검색

2개 이상의 테이블로 결과를 검색하는 과정을 조인검색이라고 한다.

select haksa_tb.name from haksa_tb, jumsu_tb where haksa_tb.sno = jumsu.sno AND jumsu_tb.codee = 1001;


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