[MySQL] 각종 팁 > db

본문 바로가기

db

[MySQL] 각종 팁

페이지 정보

작성자 서방님 댓글 0건 조회 27회 작성일 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이라는 것이 있습니다.. 

댓글목록

등록된 댓글이 없습니다.

Total 447건 1 페이지
게시물 검색

회원로그인

접속자집계

오늘
215
어제
282
최대
592
전체
37,313

그누보드5
Copyright © seobangnim.com All rights reserved.
자바스크립트를 활성화 하세요![ 브라우저에서 자바스크립트를 활성화하는 방법 ]