[MySQL] 각종 팁
페이지 정보
작성자 서방님 댓글 0건 조회 156회 작성일 13-02-18 04:20본문
1. 패스워드 변경
> UPDATE user SET password = password(''새로운암호'') WHERE user=''root'' ;
> flush privileges; 또는 $ mysqladmin -u root reload
$ mysql -u root -p
1. 가동/중지
$ mysql/bin/mysqladmin 을 실행시켜 mysql에 접속할수 있도록 합니다.
$ mysql/bin/mysql을 실행합니다.
옵션: mysql -u 사용자명 -p 비밀번호(사용할 DB명) -h 접속호스트명
mysql을 처음 설치하고 나서는 그냥 mysql만 입력하여도 됩니다.
$ mysql/bin/mysqladmin shutdown
1. 데이타베이스 만들고 사용자 부여하기
> create database orcl;
> use mysql;
user테이블에 데이타베이스를 사용할 유저명과 비밀번호를 insert 합니다.
> insert into user (host,user,password) values (''localhost'',''scott'',password(''tiger''));
db테이블에 데이타베이스를 orcl를 사용할 scott사용자를 insert 합니다.
> insert into db values (''localhost'',''orcl'',''scott'',''y'',''y'',''y'',''y'',''y'',''y'',''y'',''y'',''y'',''y'');
> flush privileges;
$ mysqladmin reload
1. 쿼리
데이타베이스 목록보기: >show databases; $mysqlshow
데이타베이스 생성하기: >create database orcl; $mysqladmin create orcl
데이타베이스 사용하기: >use orcl;
데이타베이스 자체삭제: >drop database orcl;
테이블 목록보기: >show tables; $mysqlshow orcl
테이블 생성하기: >create table test(name int,email varchar(60),comment text);
테이블 구조보기: >desc test;
테이블 이름변경: >alter table test rename test2;
테이블 자체삭제: >drop table test;
테이블 내용삭제: >delete from test;
컬럼 추가: >alter table test add email varchar(50);
컬럼 삭제: >alter table test drop email;
컬럼명,타입 변경: >alter table test change name email varchar(12);
컬럼 타입 수정: >alter table test modify email varchar(14);
select * from guest;
select num,email from guest;
select * from guest where num<5;
select * from postcode where mail like ''%@netian.com%''; //mail 칼럼에서 @netian.com이 들어간 것 모두 검색
Insert into guest(num,email) values(11,''honeyspace'');
Update guest set num=22,email=''ok'';
update guest set email=''zzz'' where num<5;
select * from guest order by num asc; //오름차순
select * from guest order by num desc; //내림차순
delete from guest where num>3 and num=3;
delete from guest where email=''aaa'';
특정 컬럼의 값만을 삭제할 수 없다. delete num from guest where email=''aaa''; (x)
Sub-select: ''SELECT * FROM table WHERE id IN(SELECT id FROM table2);''와 같은 SQL을 지원하지 않는다.
그러나 ''INSERT..SELECT...''와 ''REPLACE..SELECT...''형태의 SQL은 지원한다.
(1) 테이블 생성(컬럼사이를 탭으로 구분하지 말것)
create table guest
(
no int primary key not null auto_increment,
name char(20),
age tinyint,
phone varchar(20),
email varchar(30),
address varchar(50)
);
primary key : 주 키로 설정
not null : 반드시 값을 가져야 함을 의미
auto_increment : 레코드가 삽입될 때 마다 자동으로 값을 증가
1. 데이타 타잎
int: 정수형
varchar: 가변문자형
text: 최대 65535개의 문자를 가변적으로 입력
tinyblob (tinytext): 이진/문자 데이타, 최대크기 255 (2^8 - 1) characters.
blob (text): 이진/문자 데이타, 최대크기 65535 (2^16 - 1) characters.
mediumblob (mediumtext): 이진/문자 데이타, 최대크기 16777215 (2^24 - 1) characters.
longblob(longtext): 이진/문자 데이타, 최대크기 4294967295 (2^32 - 1) characters.
tynyint: 부호 있는 정수 -128 ~ 127, 부호 없는 정수 0 ~255, 1 Byte
smallint: 부호 있는 정수 -32768 ~ 32767, 부호 없는 정수 0 ~65535, 2 Byte
mediumint: 부호 있는 정수 -8388608 ~ 8388607, 부호 없는 정수 0 ~16777215, 3 Byte
int/integer: 부호 있는 정수 -2147483648 ~ 2147483647, 부호 없는 정수 0 ~4294967295, 4 Byte
bigint: 부호 있는 정수 -9223372036854775808 ~ 9223372036854775807, 부호 없는 정수 0 ~18446744073709551615, 8 Byte
float: 단일 정밀도를 가진 부동 소수점, -3.402823466E+38 ~3.402823466E+38
double: 2 배 정밀도를 가진 부동 소수점, -1.79769313486231517E+308 ~ 1.79769313486231517E+308
date: 날짜를 표현하는 유형, 1000-01-01 ~ 9999-12-31
datetime: 날짜와 시간을 표현하는 유형, 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp: 1970-01-01 00:00:00 부터 2037년 까지 표현, 4 Byte
time: 시간을 표현하는 유형, -839:59:59 ~ 838:59:59
year: 년도를 표현하는 유형, 1901 년 ~ 2155년
char(m): 고정길이 문자열을 표현하는 유형, M = 1 ~255
varchar(m): 가변길이 문자열을 표현하는 유형, M = 1 ~ 255
tinyblob/tinytext: 255개의 문자를 저장, blob: binary large object의 약자
blob/text: 63535개의 문자를 저장
mediumblob/mediumtext: 16777215개의 문자를 저장
longblob/longtext: 4294967295(4Giga)개의 문자를 저장
1. 계산함수
COUNT는 선택한 컬럼의 전체행의 수를 반환해 줍니다.
> select COUNT(num) from test;
> select COUNT(*) from test where name=''ccc'';
MIN과 MAX는 최고의 수와 최저의 수를 표시해 줍니다.
> select MIN(num) from test; //숫자형
> select MAX(num) from test;
> select MIN(name) from test; //문자형
> select MAX(name) from test;
AVG와 SUM은 평균과 합계를 구해서 나타내어 줍니다.(AVeraGe : 평균,SUM : 합계)
> select AVG(num) from test;
> select SUM(num) from test;
1. 이미지/바이너리 데이타
blob 란 binary large object 입니다.
blob 필드는 소팅이나 index생성은 할 수 없습니다.
CREATE TABLE gallery1
(
id int NOT NULL auto_increment,
image blob NOT NULL, # 이미지의 바이너리
title varchar(100) DEFAULT '''' NOT NULL, # 이미지 제목
width smallint(6) DEFAULT ''0'' NOT NULL, # 가로크기
height smallint(6) DEFAULT ''0'' NOT NULL, # 세로크기
filesize int , # 파일크기
detail text , # 이미지 설명
PRIMARY KEY (id)
) ;
php 예제)
(1) 이미지를 입력
- html form 태그 enctype=''multipart/form-data''
- 전송돤 화일은 서버상의 /tmp 디렉토리에 임시로 저장되며 임의이 이름이 부여됩니다.
- 전송돤 화일은 이름은 $image 에 저장되며 실제 사용자 로컬 하드에서 사용되었던 이름은
$image_name 에 저장됩니다.
- 실제로 이미지를 테이블에 저장하는 핵심적인 루틴을 다음과 같습니다.
------------------------------------------------
$size = GetImageSize($image); // tmp 디렉토리에 올라온 이미지 크기
$width = $size[0];
$height = $size[1];
$imageblob = addslashes(fread(fopen($image, ''r''), filesize($image)));
$filesize = filesize($image) ;
$que1='' INSERT INTO gallery VALUES ('''', ''$imageblob'',''$title'', ''$width'',
''$height'',''$filesize'', ''$detail'' )'' ;
$result=mysql_query($que1,$connect );
------------------------------------------------
GetImageSize() 함수는 이미지의 가로,세로 등등등 을 알려줍니다.
$imageblob = addslashes(fread(fopen($image, ''r''), filesize($image)));
이부분이 바로 화일을 읽어 그 크기만큼 DB저장 가능한 BLOB형태로 처리해 줍니다.
(2) 이미지 출력
<img src="./view.html?id=$row[id] .. 인데요
------------------------------------------------
$que1=''select * from gallery where id=$id'' ;
$result=mysql_query($que1,$connect );
$row=mysql_fetch_array($result);
Header( ''Content-type: image/jpeg'');
echo $row[image];
------------------------------------------------
1. java jdbc
- mysql jdbc드라이버 다운로드:
http://mmmysql.sourceforge.net/dist/mm.mysql-2.0.2-bin.jar
- 환경 변수:
# vi /etc/profile
export JAVA_HOME=/usr/local/jdk
export CLASSPATH=./:$CLASSPATH:$ORACLE_HOME/jdbc/lib/classes111.zip:
$JAVA_HOME/lib/classes.zip:$JAVA_HOME/lib/mm.mysql-2.0.2-bin.jar
# source /etc/profile
- 자바 코딩:
<%@ page language=''java'' import=''java.sql.*'' contentType=''text/html;charset=KSC5601'' %>
<%
Class.forName ( ''org.gjt.mm.mysql.Driver'' );
Connection conn = DriverManager.getConnection ( ''jdbc:mysql://localhost/orcl'';, ''scott'', ''tiger'' );
Statement stmt = conn.createStatement ( );
ResultSet rs = stmt.executeQuery ( ''select mail from test'' );
if ( ! rs.next ( ) )
out.println(''암것도 없다'');
else
out.println ( rs.getString ( 1 ) );
stmt.close ( );
conn.close ( );
%>
1. 백업/복구
(1) 데이타베이스 백업/복구
백업:
mytest라는 데이터베이스에 employee라는 테이블과 데이터들이 있을 경우.
# su root
# cd /usr/local/mysql/bin
형식: mysqldump -p 데이터베이스명 > ''파일명''
# ./mysqldump -p mytest > ''/tmp/mytestbackup.txt''
데이터베이스 전체가 /tmp 디렉토리 밑에 mytestbackup.txt 라는 파일로 백업이 되었습니다.
복구:
일단 mytest 데이터베이스 안에 있는 데이터들을 모두 삭제.
> DROP DATABASE mytest;
> show tables;
# cd /usr/local/mysql/bin
# ./mysql -p mytest < ''/tmp/mytestbackup.txt''
(2) 테이블 백업/복구
백업:
테이블 이름이 employee일 경우.
백업이나 복구를 하기 전에는 항상 LOCK를 걸어놓아야 한다.
> LOCK TABLES employee READ;
> SELECT * INTO OUTFILE ''/tmp/backup.sql'' FROM employee;
employee 테이블의 테이타가 /tmp 디렉토리 밑에 backup.sql 이라는 파일로 저장됩니다.
복구:
일단 employee 테이블의 모든 데이타를 지웁니다.
> delete from employee;
형식: LOAD DATA INFILE ''파일명'' INTO TABLE 테이블명;
> LOAD DATA INFILE ''/tmp/backup.sql'' INTO TABLE employee;
> select * from employee;
> UNLOCK TABLES;
1. 파일 처리
(1) 테이블에서 데이터를 검색하여 파일로 출력하기
> select * from guest where age <= 25 into outfile ''out.txt'' fields terminated by '','';
상대경로명을 사용하면 기준은 DB가 존재하는 디렉토리
(2) 파일에서 데이터를 테이블로 읽어 들이기
> load data infile ''/home/prof/wiseo/guest.txt'' into table guest fields terminated by '','';
파일명은 반드시 절대경로명
(3) sql 파일 사용법
$ mysql orcl < /usr/local/oracle/postcode.sql
mysql 사용할데이타베이스명 < sql파일절대경로
1. 트랜잭션(LOCK)
LOCK을 걸어놓으면 걸어놓는 동안은 다른 사용자가 작업을 못합니다.
LOCK에는 READ락과 WRITE락이 있습니다.
mysql> LOCK TABLES mytb READ;
이렇게 명령을 내리면 mytb라는 테이블에대해 read락이 걸린겁니다.
이렇게되면 다른 창이나 외부에서 mytb에 대해 SELECT만이 가능하게됩니다.
mysql> UNLOCK TABLES;
mysql> LOCK TABLES mytb WRITE;
이렇게하면 다른 사용자는 select도 못하고 변경도 못합니다.
mysql> UNLOCK TABLES;
오라클에서는 이러한 개념으로 COMMIT와 ROLLBACK이라는 것이 있습니다..
> UPDATE user SET password = password(''새로운암호'') WHERE user=''root'' ;
> flush privileges; 또는 $ mysqladmin -u root reload
$ mysql -u root -p
1. 가동/중지
$ mysql/bin/mysqladmin 을 실행시켜 mysql에 접속할수 있도록 합니다.
$ mysql/bin/mysql을 실행합니다.
옵션: mysql -u 사용자명 -p 비밀번호(사용할 DB명) -h 접속호스트명
mysql을 처음 설치하고 나서는 그냥 mysql만 입력하여도 됩니다.
$ mysql/bin/mysqladmin shutdown
1. 데이타베이스 만들고 사용자 부여하기
> create database orcl;
> use mysql;
user테이블에 데이타베이스를 사용할 유저명과 비밀번호를 insert 합니다.
> insert into user (host,user,password) values (''localhost'',''scott'',password(''tiger''));
db테이블에 데이타베이스를 orcl를 사용할 scott사용자를 insert 합니다.
> insert into db values (''localhost'',''orcl'',''scott'',''y'',''y'',''y'',''y'',''y'',''y'',''y'',''y'',''y'',''y'');
> flush privileges;
$ mysqladmin reload
1. 쿼리
데이타베이스 목록보기: >show databases; $mysqlshow
데이타베이스 생성하기: >create database orcl; $mysqladmin create orcl
데이타베이스 사용하기: >use orcl;
데이타베이스 자체삭제: >drop database orcl;
테이블 목록보기: >show tables; $mysqlshow orcl
테이블 생성하기: >create table test(name int,email varchar(60),comment text);
테이블 구조보기: >desc test;
테이블 이름변경: >alter table test rename test2;
테이블 자체삭제: >drop table test;
테이블 내용삭제: >delete from test;
컬럼 추가: >alter table test add email varchar(50);
컬럼 삭제: >alter table test drop email;
컬럼명,타입 변경: >alter table test change name email varchar(12);
컬럼 타입 수정: >alter table test modify email varchar(14);
select * from guest;
select num,email from guest;
select * from guest where num<5;
select * from postcode where mail like ''%@netian.com%''; //mail 칼럼에서 @netian.com이 들어간 것 모두 검색
Insert into guest(num,email) values(11,''honeyspace'');
Update guest set num=22,email=''ok'';
update guest set email=''zzz'' where num<5;
select * from guest order by num asc; //오름차순
select * from guest order by num desc; //내림차순
delete from guest where num>3 and num=3;
delete from guest where email=''aaa'';
특정 컬럼의 값만을 삭제할 수 없다. delete num from guest where email=''aaa''; (x)
Sub-select: ''SELECT * FROM table WHERE id IN(SELECT id FROM table2);''와 같은 SQL을 지원하지 않는다.
그러나 ''INSERT..SELECT...''와 ''REPLACE..SELECT...''형태의 SQL은 지원한다.
(1) 테이블 생성(컬럼사이를 탭으로 구분하지 말것)
create table guest
(
no int primary key not null auto_increment,
name char(20),
age tinyint,
phone varchar(20),
email varchar(30),
address varchar(50)
);
primary key : 주 키로 설정
not null : 반드시 값을 가져야 함을 의미
auto_increment : 레코드가 삽입될 때 마다 자동으로 값을 증가
1. 데이타 타잎
int: 정수형
varchar: 가변문자형
text: 최대 65535개의 문자를 가변적으로 입력
tinyblob (tinytext): 이진/문자 데이타, 최대크기 255 (2^8 - 1) characters.
blob (text): 이진/문자 데이타, 최대크기 65535 (2^16 - 1) characters.
mediumblob (mediumtext): 이진/문자 데이타, 최대크기 16777215 (2^24 - 1) characters.
longblob(longtext): 이진/문자 데이타, 최대크기 4294967295 (2^32 - 1) characters.
tynyint: 부호 있는 정수 -128 ~ 127, 부호 없는 정수 0 ~255, 1 Byte
smallint: 부호 있는 정수 -32768 ~ 32767, 부호 없는 정수 0 ~65535, 2 Byte
mediumint: 부호 있는 정수 -8388608 ~ 8388607, 부호 없는 정수 0 ~16777215, 3 Byte
int/integer: 부호 있는 정수 -2147483648 ~ 2147483647, 부호 없는 정수 0 ~4294967295, 4 Byte
bigint: 부호 있는 정수 -9223372036854775808 ~ 9223372036854775807, 부호 없는 정수 0 ~18446744073709551615, 8 Byte
float: 단일 정밀도를 가진 부동 소수점, -3.402823466E+38 ~3.402823466E+38
double: 2 배 정밀도를 가진 부동 소수점, -1.79769313486231517E+308 ~ 1.79769313486231517E+308
date: 날짜를 표현하는 유형, 1000-01-01 ~ 9999-12-31
datetime: 날짜와 시간을 표현하는 유형, 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp: 1970-01-01 00:00:00 부터 2037년 까지 표현, 4 Byte
time: 시간을 표현하는 유형, -839:59:59 ~ 838:59:59
year: 년도를 표현하는 유형, 1901 년 ~ 2155년
char(m): 고정길이 문자열을 표현하는 유형, M = 1 ~255
varchar(m): 가변길이 문자열을 표현하는 유형, M = 1 ~ 255
tinyblob/tinytext: 255개의 문자를 저장, blob: binary large object의 약자
blob/text: 63535개의 문자를 저장
mediumblob/mediumtext: 16777215개의 문자를 저장
longblob/longtext: 4294967295(4Giga)개의 문자를 저장
1. 계산함수
COUNT는 선택한 컬럼의 전체행의 수를 반환해 줍니다.
> select COUNT(num) from test;
> select COUNT(*) from test where name=''ccc'';
MIN과 MAX는 최고의 수와 최저의 수를 표시해 줍니다.
> select MIN(num) from test; //숫자형
> select MAX(num) from test;
> select MIN(name) from test; //문자형
> select MAX(name) from test;
AVG와 SUM은 평균과 합계를 구해서 나타내어 줍니다.(AVeraGe : 평균,SUM : 합계)
> select AVG(num) from test;
> select SUM(num) from test;
1. 이미지/바이너리 데이타
blob 란 binary large object 입니다.
blob 필드는 소팅이나 index생성은 할 수 없습니다.
CREATE TABLE gallery1
(
id int NOT NULL auto_increment,
image blob NOT NULL, # 이미지의 바이너리
title varchar(100) DEFAULT '''' NOT NULL, # 이미지 제목
width smallint(6) DEFAULT ''0'' NOT NULL, # 가로크기
height smallint(6) DEFAULT ''0'' NOT NULL, # 세로크기
filesize int , # 파일크기
detail text , # 이미지 설명
PRIMARY KEY (id)
) ;
php 예제)
(1) 이미지를 입력
- html form 태그 enctype=''multipart/form-data''
- 전송돤 화일은 서버상의 /tmp 디렉토리에 임시로 저장되며 임의이 이름이 부여됩니다.
- 전송돤 화일은 이름은 $image 에 저장되며 실제 사용자 로컬 하드에서 사용되었던 이름은
$image_name 에 저장됩니다.
- 실제로 이미지를 테이블에 저장하는 핵심적인 루틴을 다음과 같습니다.
------------------------------------------------
$size = GetImageSize($image); // tmp 디렉토리에 올라온 이미지 크기
$width = $size[0];
$height = $size[1];
$imageblob = addslashes(fread(fopen($image, ''r''), filesize($image)));
$filesize = filesize($image) ;
$que1='' INSERT INTO gallery VALUES ('''', ''$imageblob'',''$title'', ''$width'',
''$height'',''$filesize'', ''$detail'' )'' ;
$result=mysql_query($que1,$connect );
------------------------------------------------
GetImageSize() 함수는 이미지의 가로,세로 등등등 을 알려줍니다.
$imageblob = addslashes(fread(fopen($image, ''r''), filesize($image)));
이부분이 바로 화일을 읽어 그 크기만큼 DB저장 가능한 BLOB형태로 처리해 줍니다.
(2) 이미지 출력
<img src="./view.html?id=$row[id] .. 인데요
------------------------------------------------
$que1=''select * from gallery where id=$id'' ;
$result=mysql_query($que1,$connect );
$row=mysql_fetch_array($result);
Header( ''Content-type: image/jpeg'');
echo $row[image];
------------------------------------------------
1. java jdbc
- mysql jdbc드라이버 다운로드:
http://mmmysql.sourceforge.net/dist/mm.mysql-2.0.2-bin.jar
- 환경 변수:
# vi /etc/profile
export JAVA_HOME=/usr/local/jdk
export CLASSPATH=./:$CLASSPATH:$ORACLE_HOME/jdbc/lib/classes111.zip:
$JAVA_HOME/lib/classes.zip:$JAVA_HOME/lib/mm.mysql-2.0.2-bin.jar
# source /etc/profile
- 자바 코딩:
<%@ page language=''java'' import=''java.sql.*'' contentType=''text/html;charset=KSC5601'' %>
<%
Class.forName ( ''org.gjt.mm.mysql.Driver'' );
Connection conn = DriverManager.getConnection ( ''jdbc:mysql://localhost/orcl'';, ''scott'', ''tiger'' );
Statement stmt = conn.createStatement ( );
ResultSet rs = stmt.executeQuery ( ''select mail from test'' );
if ( ! rs.next ( ) )
out.println(''암것도 없다'');
else
out.println ( rs.getString ( 1 ) );
stmt.close ( );
conn.close ( );
%>
1. 백업/복구
(1) 데이타베이스 백업/복구
백업:
mytest라는 데이터베이스에 employee라는 테이블과 데이터들이 있을 경우.
# su root
# cd /usr/local/mysql/bin
형식: mysqldump -p 데이터베이스명 > ''파일명''
# ./mysqldump -p mytest > ''/tmp/mytestbackup.txt''
데이터베이스 전체가 /tmp 디렉토리 밑에 mytestbackup.txt 라는 파일로 백업이 되었습니다.
복구:
일단 mytest 데이터베이스 안에 있는 데이터들을 모두 삭제.
> DROP DATABASE mytest;
> show tables;
# cd /usr/local/mysql/bin
# ./mysql -p mytest < ''/tmp/mytestbackup.txt''
(2) 테이블 백업/복구
백업:
테이블 이름이 employee일 경우.
백업이나 복구를 하기 전에는 항상 LOCK를 걸어놓아야 한다.
> LOCK TABLES employee READ;
> SELECT * INTO OUTFILE ''/tmp/backup.sql'' FROM employee;
employee 테이블의 테이타가 /tmp 디렉토리 밑에 backup.sql 이라는 파일로 저장됩니다.
복구:
일단 employee 테이블의 모든 데이타를 지웁니다.
> delete from employee;
형식: LOAD DATA INFILE ''파일명'' INTO TABLE 테이블명;
> LOAD DATA INFILE ''/tmp/backup.sql'' INTO TABLE employee;
> select * from employee;
> UNLOCK TABLES;
1. 파일 처리
(1) 테이블에서 데이터를 검색하여 파일로 출력하기
> select * from guest where age <= 25 into outfile ''out.txt'' fields terminated by '','';
상대경로명을 사용하면 기준은 DB가 존재하는 디렉토리
(2) 파일에서 데이터를 테이블로 읽어 들이기
> load data infile ''/home/prof/wiseo/guest.txt'' into table guest fields terminated by '','';
파일명은 반드시 절대경로명
(3) sql 파일 사용법
$ mysql orcl < /usr/local/oracle/postcode.sql
mysql 사용할데이타베이스명 < sql파일절대경로
1. 트랜잭션(LOCK)
LOCK을 걸어놓으면 걸어놓는 동안은 다른 사용자가 작업을 못합니다.
LOCK에는 READ락과 WRITE락이 있습니다.
mysql> LOCK TABLES mytb READ;
이렇게 명령을 내리면 mytb라는 테이블에대해 read락이 걸린겁니다.
이렇게되면 다른 창이나 외부에서 mytb에 대해 SELECT만이 가능하게됩니다.
mysql> UNLOCK TABLES;
mysql> LOCK TABLES mytb WRITE;
이렇게하면 다른 사용자는 select도 못하고 변경도 못합니다.
mysql> UNLOCK TABLES;
오라클에서는 이러한 개념으로 COMMIT와 ROLLBACK이라는 것이 있습니다..
댓글목록
등록된 댓글이 없습니다.
