2011. 10. 9.

mysql 내용 모음(정리 않됨)

예전에 여기저기에서 퍼워서 모와놓은건데 3.x대 버전 방식이라 볼수 있습니다. 요즘 최신버젼에서는 필요없는 것두 있으니
알아서 잘 골라 쓰시기 바랍니다. 기본적인 골자는 같다고 볼수 있죠.



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이라는 것이 있습니다..

mysql max정리
c:\mysql\bin\net stop mysql

c:\mysql\bin\mysqld-nt -remove

c:\mysql\bin\mysqld-max-nt -install

c:\mysql\bin\net start mysql

c:\mysql\bin\mysql -u root -p test



create table bank(
account varchar(30) not null,
money int unsigned not null default 0,
primary key(account)
) type=innodb;

insert into bank values('111-1111-111', 10000);
insert into bank values('222-2222-222', 5000);

begin;
update bank set money=money-3000
where account='111-1111-111';



update bank set money=money+3000
where account='222-2222-222';


select * from bank; 했을때는 당연히 7000 원, 8000 원으로 보임.
rollback;
select * from bank;

롤백된 데이터 확인됨.. 
commit; 하면 실제 데이터 반영됨..

트랜잭션은 berkeley-db 에서도 지원됨.


포린키는 아래와 같이..(이건 innodb 만 되는듯..)
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL ) TYPE=INNODB;
이노디비를 mysql 에서 상당히 밀어주는듯..  참고로 BLOB 데이터두 잘 됩니다.


mysql 권한 부여

#mysql -u root -p
mysql>GRANT ALL PRIVILEGESE ON 데이타베이스명.테이블명 TO '아이디'@'호스트'

->IDENTIFIED BY '패스워드' ;


※ DB 생성

mysql> create database test_db;

※ 사용자 생성

1. 외부에서 접근할수 있게 하는 경우

mysql> GRANT ALL PRIVILEGES ON test_db.* TO test_id@'%'              <======== %는 모든 호스트를 의미함

-> IDENTIFIED BY 'test_passwd';
mysql>flush privileges;

2. 내부에서 접속할수 있게 하는 경우

mysql> GRANT ALL PRIVILEGES ON test_db.* TO test_id@localhost      <======== *는 모든 테이블을 의미함
-> IDENTIFIED BY 'test_passwd';                                                      <======== localhost는 내부
mysql>flush privileges;

3. 권한 적용
권한을 변경한 경우 반드시 권한을 적용하여야 함(2가지 방법)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

% mysqladmin -u root -p reload
Enter password: XXXXXXXX


GRANT ALL PRIVILEGES on book.* TO book@localhost
IDENTIFIED by 'book123' with grant option;
GRANT ALL PRIVILEGES on storages.* TO storages@localhost
IDENTIFIED by 'storage123' with grant option;
flush privileges;





MySQL ver. 5.0.21 윈도우즈 셋업 파일용.



$Mysql_home/bin/mysql -u root -p

enter the password : *******

Mysql>



# 데이터베이스 생성



Mysql> use mysql;

Mysql> create database databeseName;



# 사용자 계정 추가

Mysql> Grant ALL ON <DATABASE 명 혹은 테이블명> TO <사용자명>@<호스트명> IDENTIFIED BY '비밀번호';



# 데이터베이스 권한 설정

Mysql> insert into db values('%','디비이름','UserName',

       ->'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');



--> desc db; 하여 컬럼의 갯수 확인 후 'Y'옵션 지정 함...ㅡㅡㅋ



Mysql> flush privileges;

댓글 없음:

댓글 쓰기