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;
댓글
댓글 쓰기