MySQL의 사 용 방법
3.1 중요 실행 파일과 데몬
- 중요 실행 파일
mysql - SQL 쉘
- client 프로그램
- mySQL 에 접속하여 쿼리를 수행하는 프로 그램
mysqladmin - MySQL을 관리하는 프로그램
- 데이터베이스의 생성, 삭제, 권한 설정 테이블을 읽어들이는 일을 수행
mysqld - MySQL 데몬
mysqlshow - MySQL 안에 있는 데이터베이스와 테이블 과 컬럼을 표시해주는 프로그램
safe_mysqld - MySQL을 안전하게 실행시키는 스크립트 프로그램 파일
isamchk - 테이블에 문제가 발생할 경우 수정하고, 테이블을 최적화 하는 프로그램
- 데몬을 수행 시키는 방법
% safe_mysqld 데몬을 안전하게 수행 % mysqld 데몬을 수행, 디폴트 포드 : 3306 % mysqld -P 333 포트 번호를 지정하여 데몬을 수행 - 부팅시 데몬을 자동수행시키는 방법
- /etc/rc.d/rc.local 파일에 등록
- rpm으로 설치한 경우 자동 등록
3.2 권한 설정
- MySQL을 처음 설치하면 root 만이 MYSQL 을 사용가능하도록 권한이 설정됨
- 현재 상태에서 설정된 데이타베이스를 확인 :mysqlshow 명령 사용
% mysqlshow -u root +-----------+ | Databases | +-----------+ | mysql | | test | +-----------+
- " -u root " : root 가 사용 한다는 옵션
- 최초 설치시 : mtsql과 test 두개의 데이블이 존재 - mysql 데이타베이스의 테이블 확인 : mysqlshow 명령 사용
% mysqlshow -u root mysql Database: mysql +--------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +--------------+
- user 테이블이 사용자 권한에 관한 테이 블임
- user 테이블의 레코드 확인
% mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 3.22.22 Type 'help' for help. mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from user; +-----------+------+- ---------+-------------+-------------+-------------+------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delet| +-----------+------+----------+-------------+-------------+-- ---+------+ | localhost | root | | Y | Y | Y | Y | | orange | root | | Y | Y | Y | Y | | localhost | | | N | N | N | N | | orange | | | N | N | N | N | +-----------+------+----------+-------------+-------------+---+ 4 rows in set (0.01 sec) mysql>
- "*.priv" 컴럼: 사용 권한에 대한 설정, Y 이면 권한을 가짐을, N이면 권한이 없음을 의미
- "Password" 컬럼 : 암호의 설정 상태를 의미, 현제 root는 암호가 없음 - root 사용자에 대하여 암호를 설정
mysql> update user set password = password('apple1234') => 암호 설정 - > where user='root' ; Query OK, 2 rows affected (0.13 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> quit => 종료 Bye % mysqladmin -u root reload => 권한이 변경된 경우 변경된 권한을 다시 읽어 들임 % mysql -u root -p => 암호가 있는 경 우 -p 옵션을 붙여서 mysql을 수행 Enter password: XXXXXXXXXX Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 3.22.22 Type 'help' for help. mysql> select * from user; +-----------+------+------------------+-------------+--------+-- ----------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv| +-----------+------+------------------+-------------+-------+-- ----------+ | localhost | root | 32b157395f7543c0 | Y | Y | Y | | orange | root | 32b157395f7543c0 | Y | Y | Y | | localhost | | | N | N | N | | orange | | | N | N | N | +-----------+------+------------------+-------------+-------+-- ----------+ 4 rows in set (0.01 sec) mysql>
3.3 사용자 추가
- "INSERT .. INTO" 명령을 이용 해서 mysql 데이타베이스의 user 테이블에 추가
% mysql -u root -p mysql Enter password:XXXXXXXXXX Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 to server version: 3.22.22 Type 'help' for help. mysql> insert into user values('localhost', 'wiseo', ==> 사용자 wiseo에게 모든 권한 -> password('apple2000'), 'Y', 'Y', 'Y', 'Y', -> 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', -> 'Y', 'Y'); Query OK, 1 row affected (0.01 sec) mysql> select * from user; +-----------+-------+------------------+-------------+----- ----------+ | Host | User | Password | Select_priv | Insert_priv | Update_pri| +-----------+-------+------------------+-------------+-----+- ----------+ | localhost | root | 32b157395f7543c0 | Y | Y | Y | | orange | root | 32b157395f7543c0 | Y | Y | Y | | localhost | | | N | N | N | | orange | | | N | N | N | | localhost | wiseo | 6da73b5d2ab9ea69 | Y | Y | Y | +-----------+-------+------------------+-------------+---+- ----------+ 5 rows in set (0.01 sec) mysql>- 권한을 부여 하고자 한다면 해당 컬럼 의 값을 'Y'로 아니면 'N'으로 설정
- SELECT, INSERT, DELETE에 대한 권한만 주고자 한다면
mysql> insert into user values('localhost', 'wiseo', -> password('apple2000'), 'Y', 'Y', 'Y'); Query OK, 1 row affected (0.01 sec) mysql>
- "GRANT" 명령을 이용해서 mysql 데이타베이스의 user 테이블에 추가
mysql> grant all priviledges on *.* to wiseo@localhost ==> 사용자 wiseo에게 모든 권한 -> identified by 'apple2000' with grant option; Query OK, 0 rows affected (0.01 sec) mysql>
- "GRANT" 명령을 이용해서 RELOAD와 PROCESS 권한 만을 설정
mysql> grant reload,process on *.* to wiseo@localhost -> identified by 'apple2000' with grant option; Query OK, 0 rows affected (0.01 sec) mysql> - 권한을 변경한 경우 반드시 권한을 적용 하여야 함(2가지 방법)
mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
% mysqladmin -u root -p reload Enter password: XXXXXXXX
3.4 암호
- root 암호를 잊어버린 경우
- MySQL 데몬의 실행을 중지
- 권한에 대한 검사를 하지 않고 데몬을 실행 : safe_mysql -Sg &
- mysql을 사용하여 암호를 제거
- mysqladmin reload를 이용해서 변경된 권한을 적용하고, 데몬을 다시 수행 - 암호의 저장
- 암호가 설정된 경우 모든 명령을 사용하기 위해서는 -p 옵션을 붙여 야 하고, 암호를 입력하여야 함
- 암호 입력을 생략하려면 홈디렉토리에 .my.cnf 파일을 다음과 같이 만들어야 함.% cat > .my.cnf [client] password=암호 ^D % chmod 600 .my.cnf => 다른 사람이 보지 못하도록 파일 접근 권한을 설정 %
3.5 컬럼의 유형
- 컬럼(Column)
- 데이타베이스를 구성 하는 최소 단위
- 컬럼들이 모여서 테이블(Table)을 구성 - 컬럼의 유형
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)개의 문자 를 저장
3.6 데이타베이스 생성
- mysqladmin을 이용하는 방법
% mysqladmin create super Database "guestbook" created. % mysqlshow +-----------+ | Databases | +-----------+ | mysql | | guestbook | | test | +-----------+ %
- mysql을 이용하는 방법
% mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 3.22.22 Type 'help' for help. mysql> create datatbase guestbook; Query OK, 1 row affected (0.01 sec) mysql> show databases; +-----------+ | Databases | +-----------+ | mysql | | guestbook | | test | +-----------+ Query OK, 1 row affected (0.01 sec) mysql>
3.7 테이블 생성
- 테이블의 예 : 고객 테이블
테이블 명 : guest 컬럼 명 : 일련번호(no) -> 자동으로 부여되는 일련 번호, 키 이름(name) 나이(age) 전화번호(phone) 전자우편주소(email) 주소(address) - 테이블 생성 방법 : mysql 명령 모드에 서 CREATE 문을 사용
CREATE TABLE 테이블명 (컬럼의 형 식) ; - 테이블 생성 예제
% mysql mysql> use guestbook; Database changed mysql> 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)); Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; => 생성 된 테이블을 확인 +-------------------- -+ | Tables in guestbook | +---------------------+ | guest | +---------------------+ 1 row in set (0.00 sec) mysql> - PRIMARY KEY : 주 키로 설정
- NOT NULL : 반드 시 값을 가져야 함을 의미
- AUTO_INCREMENT : 레코드가 삽입될 때 마다 자동으로 값을 증가
3.8 레코드 삽입
- 삽입할 레코드 정보
no
name
age
phone
email
address
1
홍길동
20
510-0001
부산
2
김유신
22
510-1004
경주
3
강감찬
23
510-9000
대구
4
이순신
21
560-8000
여수
5
임꺽정
30
800-7000
대전
6
유관순
21
700-5555
서울
7
윤봉길
28
288-5757
인천
8
안중근
33
566-4545
수원
9
박문수
25
767-9090
충주
10
정철수
55
777-9999
광주
- 레코드 삽입 방법 : mysql 명령 모드에 서 INSERT 문을 사용
INSERT INTO 테이블명 VALUES(컬럼의 데 이터 값...) ; - 레코드 삽입 예제:
mysql> INSERT INTO guest VALUES(1,'홍길 동',20,'510-0001', -> 'hong@jisan.ac.kr','부산'); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO guest VALUES(NULL,'김유신',22,'510-1004', -> 'kim@jisan.ac.kr','경 주'); Query OK, 1 row affected (0.05 sec) (중략) mysql> select * from guest; +----+--------+------+----------+------------------+---------+ | no | name | age | phone | email | address | +----+--------+------+----------+------------------+---------+ | 1 | 홍길동 | 20 | 510-0001 | hong@jisan.ac.kr | 부산 | | 2 | 김유신 | 22 | 510-1004 | kim@jisan.ac.kr | 경주 | | 3 | 강감찬 | 23 | 510-9000 | gang@jisan.ac.kr | 대구 | | 4 | 이순신 | 21 | 560-8000 | lee@jisan.ac.kr | 여수 | | 5 | 임꺽정 | 30 | 800-7000 | lim@jisan.ac.kr | 여수 | | 6 | 유관순 | 21 | 700-5555 | you@jisan.ac.kr | 서울 | | 7 | 윤봉길 | 28 | 288-5757 | youn@jisan.ac.kr | 인천 | | 8 | 안중근 | 33 | 566-4545 | ann@jisan.ac.kr | 수원 | | 9 | 박문수 | 25 | 767-4545 | park@jisan.ac.kr | 충주 | | 10 | 정철수 | 55 | 777-9999 | jung@jisan.ac.kr | 광주 | +----+--------+------+----------+------------------+---------+ 10 rows in set (0.00 sec) mysql>
3.9 레코드 수정
- 레코드 수정 방법 : mysql 명령 모드에 서 UPDATE 문을 사용
UPDATE 테이블명 SET 컬럼명=식 ,... [ WHERE 조건 ]; - 레코드 수정 예제:
mysql> Update guest SET address='대전' where name='임꺽정'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from guest where name='임꺽정'; +----+--------+------+----------+-----------------+---------+ | no | name | age | phone | email | address | +----+--------+------+----------+-----------------+---------+ | 5 | 임꺽정 | 30 |800-7000 | lim@jisan.ac.kr | 대전 | +----+--------+------+----------+-----------------+---------+ 1 row in set (0.00 sec) mysql>
3.10 레코드 검색
- 검색 조건
- guest 테이블의 모든 레코드 검색하여 이 름과 나이 필드만 출력
- guest 테이 블의 모든 레코드 검색하여 25살 이하인 사람들의 이름만 출력
- guest 테이블의 모든 레코드를 나 이가 큰 순서로 정렬하여 이름과 나이 필드만 출력
- 전체 나이의 평균 출력
- 레코드 검색 방법 : mysql 명령 모드에 서 SELECT 문을 사용
SELECT 컬럼명 [AS 구문] ,.. FROM 테이블명 [WHERE 조건] [ORDER BY 정렬기준 컬럼 ACS|DESC ] [GROUP BY 구룹화 기준 컬럼 ];
- 레코드 검색 예제:
mysql> SELECT name,age from guest; +--------+------+ | name | age | +--------+------+ | 홍길동 | 20 | | 김유신 | 22 | | 강감찬 | 23 | | 이순신 | 21 | | 임꺽정 | 30 | | 유관순 | 21 | | 윤봉길 | 28 | | 안중근 | 33 | | 박문수 | 25 | | 정철수 | 55 | +--------+------+ 10 rows in set (0.00 sec) mysql> SELECT name,age from guest where age <= 25 ; +--------+------+ | name | age | +--------+------+ | 홍길동 | 20 | | 김유신 | 22 | | 강감찬 | 23 | | 이순신 | 21 | | 유관순 | 21 | | 박문수 | 25 | +--------+------+ 6 rows in set (0.00 sec) mysql> SELECT name,age from guest ORDER BY age DESC; +--------+------+ | name | age | +--------+------+ | 정철수 | 55 || 안중근 | 33 | | 임꺽정 | 30 | | 윤봉길 | 28 | | 박문수 | 25 | | 강감찬 | 23 | | 김유신 | 22 | | 이순신 | 21 || 유관순 | 21 | | 홍길동 | 20 | +--------+------+ 10 rows in set (0.01 sec) mysql> SELECT AVG (age) from guest ; +----------+ | AVG(age) | +----------+ | 27.8000 | +----------+ 1 row in set (0.07 sec) mysql> SELECT AVG (age) AS '평균 연령' from guest ; +-----------+ | 평균 연령 | +-----------+ | 27.8000 | +-----------+ 1 row in set (0.01 sec) mysql>
3.12 파일로 부터 레코드 입력받기와 결과를 파일 로 저장하기
3.12.1 파일에서 데이터를 테이블로 읽어 들이기
- 방법 : mysql 명령 모드에서
LOAD DATA INFILE '파일 경로명' INTO TABLE 테이블명 FIELDS TERMINATED BY '필드구분 자';
- 예제 :
% cat > guest.txt NULL,서태지,22,666- 9999,seo@jisan.ac.kr,진주 NULL,신승훈,33,999-6666,shin@jisan.ac.kr,청주 % mysql guestbook Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 to server version: 3.22.22 Type 'help' for help. mysql> LOAD DATA INFILE '/home/prof/wiseo/guest.txt' INTO TABLE guest -> FIELDS TERMINATED BY ',';=> 파일명은 반드시 절대경로명 Query OK, 2 rows affected (0.03 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 2 mysql>
3.12.2 테이블에서 데이터를 검색하여 파 일로 출력하기
- 방법: mysql 명령 모드에서
SELECT 문 INTO OUTFILE '출력파일 의 경로명';
- 예제 :
mysql> SELECT * from guest where age <= 25 -> INTO OUTFILE 'out.txt' -> FIELDS TERMINATED BY ',' ; => 상대경로명을 사용하면 기준 은 DB가 존재하는 디렉토리 mysql>
3.13 테이블의 이름 변경과 컬럼 구조 변경(ALTER 문)
3.13.1 테이블 이름 변경
- 방법: mysql 명령 모드에서
ALTER TABLE 테이블명 RENAME 새테 이블명 ;
- 예제
3.13.2 테이블 컬럼 구조 변경
- 방법: mysql 명령 모드에서
ALTER TABLE 테이블명 ADD 컬럼명 컬럼 유형; => 새로 운 컬럼을 추가 ALTER TABLE 테이블명 DROP COLUMN 컬럼명; => 컬럼을 삭제
- 예제
3.13 테이블의 삭제
- 방법: mysql 명령 모드에서
DROP TABLE 테이블 명 ;
