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 ) 앗뜨거

댓글을 달아 주세요

  1. 당신을존경합니다 2014.11.26 01:06  댓글주소  수정/삭제  댓글쓰기

    정말 당신은 최고에요!
    당신 덕분에 해결했습니다
    한글이 깨지는 현상을 설명해 놓은곳이 없더군요

    당신은 진정한 블로거

    아이러브유

  2. 당신을존경합니다 2014.11.26 01:06  댓글주소  수정/삭제  댓글쓰기

    정말 당신은 최고에요!
    당신 덕분에 해결했습니다
    한글이 깨지는 현상을 설명해 놓은곳이 없더군요

    당신은 진정한 블로거

    아이러브유

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 ) 앗뜨거

댓글을 달아 주세요