[ MySql ][sql] 다른 컬럼값으로 update 하기

price03 의 값을 price02값의 0.7배로 전체 바꾸고 싶을때
쿼리문

 update tblname set price03 = price02*0.7;

[ MySql ] alter table drop column

행삭제 예문

ALTER TABLE tblname DROP COLUMN colname;

[ Mysql ] 데이터 베이스 charset 확인

로긴
#>mysql -u root -p
password:

mysql> show create database eccube_db \G
*************************** 1. row ***************************
       Database: eccube_db
Create Database: CREATE DATABASE `eccube_db` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

utf8로 설정 되있는 것이다.

[ mysql ] database 데이터 베이스 charset 확인

로긴
#>mysql -u root -p
password:

#>\s

ysql  Ver 14.14 Distrib 5.1.59, for Win32 (ia32)

onnection id:          5
urrent database:
urrent user:           root@localhost
SL:                    Not in use
sing delimiter:        ;
erver version:         5.1.59-community MySQL Community Server (GPL)
rotocol version:       10
onnection:             localhost via TCP/IP
erver characterset:    utf8
b     characterset:    utf8
lient characterset:    utf8
onn.  characterset:    utf8
CP port:               3306
ptime:                 38 min 31 sec

hreads: 1  Questions: 14  Slow queries: 0  Opens: 15  Flush tables:
es: 0  Queries per second avg: 0.6

[ Mysql ] insert 기본 구문

insert 기본 구문 샘플

mysql> INSERT INTO book_list
    -> (book_id, title, author, price, comments)
    -> VALUES
    -> (1, 'book_A', 'auth_A', 1500, 'good, bad, excellence');

[ linux ][ mysql ] 데이터 베이스 생성, 갱신 시간 테이블 별로 보기

데이터 베이스 생성 시간 테이블 별로 보기

[root@DbServer ~]# mysql -u root -p"PASSWORD" -e "select CREATE_TIME,TABLE_NAME f rom information_schema.tables where TABLE_SCHEMA='DATABASE NAME'";

데이터 베이스 갱신 시간 테이블 별로 보기

[root@DbServer ~]# mysql -u root -p"PASSWORD" -e "select UPDATE_TIME,TABLE_NAME f rom information_schema.tables where TABLE_SCHEMA='DATABASE NAME'";

[ Mysql ] 전체 DB 데이터베이스 테이블 최적화

출처:http://www.lxg.de/code/shell-script-to-optimize-all-tables-in-all-databases-mysql

#!/bin/bash
 
MYSQL_LOGIN='-u root --password=YOURPASSWORD'
 
for db in $(echo "SHOW DATABASES;" | mysql $MYSQL_LOGIN | grep -v -e "Database" -e "information_schema")
do
        TABLES=$(echo "USE $db; SHOW TABLES;" | mysql $MYSQL_LOGIN |  grep -v Tables_in_)
        echo "Switching to database $db"
        for table in $TABLES
        do
                echo -n " * Optimizing table $table ... "
                echo "USE $db; OPTIMIZE TABLE $table" | mysql $MYSQL_LOGIN  >/dev/null
                echo "done."
        done
done

[ Mysql ][ innodb ] 데이터 베이스 별 ibdata

데이터 베이스별로 ibdata 생성시키기

[mysqld]
innodb_file_per_table

참고사이트
http://dev.mysql.com/doc/refman/5.0/en/innodb-multiple-tablespaces.html

[ Mysql ] 관리자 root 패스워드 리셋

만일 MySQL의 root 암호를 잊어 버려도 다음과 같이 재설정 할 수 있다.

1. MySQL 데몬 프로세스를 중지
2. - skip-grant-tables 옵션을 붙여 mysqld를 다시 시작
3. mysql-u root에서 MySQL 클라이언트 시작
4. UPDATE mysql.user SET password = PASSWORD ( 'password') WHERE user = 'root';
5. FLUSH PRIVILEGES; 실행

MySQL을 권한 무시 모드로 시작한 후 정상적인 비밀번호 업데이트와 같은 방식으로 처리하면 비밀번호를 재설정 할 수 있다.

[MySql] left join right join inner join 조인 정리

MySQL의 LEFT JOIN, RIGHT JOIN, INNER JOIN

테스트 테이블을 준비

CREATE TABLE test1 (

    my_id VARCHAR (255) PRIMARY KEY,

    data1 VARCHAR (255)

);

CREATE TABLE test2 (

    id INT PRIMARY KEY AUTO_INCREMENT,

    my_id VARCHAR (255) NOT NULL,

    data2 VARCHAR (255)

);

테이블 결과

SELECT * FROM test1;

| my_id | data1 |

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

| aaaaa | aa1 |

| bbbbb | aa2 |

| ccccc | aa3 |

| ddddd | aa3 |

SELECT * FROM test2;

| id | my_id | data2 |

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

| 1 | aaaaa | bb1 |

| 2 | aaaaa | bb2 |

| 3 | bbbbb | cc1 |

| 4 | ccccc | dd3 |

| 5 | eeeee | NULL |

1) LEFT JOIN

2) RIGHT JOIN

3) INNER JOIN

1) JOIN 왼쪽 테이블의 데이터는 기본적으로 모두 표시합니다 (WHERE 절에서 제외하지 않는 한). 그리고 거기에 해당하는 오른쪽 테이블의 데이터를 추가합니다. 오른쪽 테이블에 해당하는 데이터가 존재하지 않는 경우는 NULL 대신에 사용됩니다.

SELECT

    test1.my_id AS id_1,

    test2.my_id AS id_2,

    data1,

    data2

FROM

    test1 LEFT JOIN test2

ON

    test1.my_id = test2.my_id;

| id_1 | id_2 | data1 | data2 |

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

| aaaaa | aaaaa | aa1 | bb1 |

| aaaaa | aaaaa | aa1 | bb2 |

| bbbbb | bbbbb | aa2 | cc1 |

| ccccc | ccccc | aa3 | dd3 |

| ddddd | NULL | aa3 | NULL |

2) 1)의역으로 JOIN 오른쪽 테이블의 데이터는 기본적으로 모든 표시됩니다. 그리고 거기에 해당하는 왼쪽 테이블의 데이터를 추가합니다. 왼쪽 테이블에 해당하는 데이터가 존재하지 않는 경우는 NULL 대신에 사용됩니다.

SELECT

    test1.my_id AS id_1,

    test2.my_id AS id_2,

    data1,

    data2

FROM

    test1 RIGHT JOIN test2

ON

    test1.my_id = test2.my_id;

| id_1 | id_2 | data1 | data2 |

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

| aaaaa | aaaaa | aa1 | bb1 |

| aaaaa | aaaaa | aa1 | bb2 |

| bbbbb | bbbbb | aa2 | cc1 |

| ccccc | ccccc | aa3 | dd3 |

| NULL | eeeee | NULL | NULL |

3) 좌우 두 테이블에서 해당 데이터가 존재하는 것 밖에 표시하지 않습니다

SELECT

    test1.my_id AS id_1,

    test2.my_id AS id_2,

    data1,

    data2

FROM

    test1 INNER JOIN test2

ON

    test1.my_id = test2.my_id;

| id_1 | id_2 | data1 | data2 |

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

| aaaaa | aaaaa | aa1 | bb1 |

| aaaaa | aaaaa | aa1 | bb2 |

| bbbbb | bbbbb | aa2 | cc1 |

| ccccc | ccccc | aa3 | dd3 |

[Mysql] insert 속도 개선

다중으로 insert쓸때에는
밑에 처럼 나열하는게 빠르다 .

insert into tablename (field1,field2) values(data1-1,data2-1),(data1-1,data2-1)....

[MySql] 데이터 무한 삽입 테스트

test.sql에 데이터 삽입 sql문 작성해 놓고
(인서트문 100개 정도 넣어 놨다)

아래를 test.sh로 저장

#!/bin/bash
mysql -u root -p"12345" dbname < test.sql;
mysql -u root -p"12345" dbname -e "select count(*) from testrun;" >> count.text;
mysql -u root -p"12345" dbname -e "show table status like 'testrun'" >> count.text;
mysql -u root -p"12345" dbname -e "select table_schema, sum(round((data_length+index_length)/1024/1024,0)) as total_size_mb from information_schema.tables where table_schema like 'dbname' and engine like 'innodb' group by table_schema;" >> count.text;
date '+%F  %r' >> count.text
echo >> count.text

1분단위로 위 파일을 실행
crontab -e에 등록
*/1 * * * * /root/test.sh

count.text에서 확인

[MySql] ERROR 1005 (HY000):Cant create table (errno: 121)

테이블 생성할 때
CONSTRAINT  testrun_ibfk_1 ~~~
 testrun_ibfk_1 라고 선언된게 어딘가에 있기때문에 발생
CONSTRAINT testrun_babo_ibfk_1 ~~~라는 식으로 변경하믄 OK

[MySql] 테이블 생성 sql 뽑기

mysql>show create table TBLNAME;

[MySql] select 결과 csv 로

예:
mysql> SELECT * INTO OUTFILE 'e:\select_result.CSV' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from testbody whe
re testname like "92---JNP%";

[MySql] 원격 접속 에러

방화벽설정이 안된경우의 에러

  java.net.NoRouteToHostException: No route to host: connect

방화벽 열어주자

MYSQL DB의 설정이 안된경우의 에러

java.sql.SQLException: null,  message from server: "Host '192.168.24.52' is not allowed to connect to this MySQL server"
MYSQL DB를 설정허자.
insert into user values('192.168.24.52', 'root',password('12345'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',' ',' ',' ',' ',0,0,0,0);

[MySql] 특정 문자열 치환

update tblname set fieldname=REPLACE(fieldname, "old", "after");
예>update testmaster set testname=REPLACE(testname,"-","_");

[Mysql] alter 컬럼 속성 수정

mysql>alter table tblname change col1 col1 set("YES","NO");
                         (테이블명)          (컬럼명)   (속성지정)

[MySql] Fedora Mysql yum 으로 설치 (InnoDB용)

설치
#>yum install mysql mysql-server

실행
#> /etc/rc.d/init.d/mysqld start

함 드가본다
#>mysql -u -p

Innodb확인 함 해보고
mysql>show variables like 'have_innodb';

자동 실행 설정
#>chkconfig mysqld on

my.cnf복사
#>cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

my.cnf 수정
#>vi /etc/my.cnf

[client]
default-character-set = utf8

[mysqld]

init_connect = SET collation_connection = utf8_general_ci
init_connect = SET NAMES utf8
default-character-set = utf8
character-set-server = utf8
collation-server = utf8_general_ci

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

[MySql] alter

테이블  컬럼 삽입
mysql> alter table tblname add colname datatype;
         ->alter table test add name varchar(32);

테이블 컬럼 삭제
mysql> alter table tblname drop colname;
         ->alter table test drop name;


테이블 컬럼명 변경
mysql> alter table tblname change column colname_old colname_new datatype;
        ->alter table test change column a b varchar(128);