형화의 PHP 페이지

제 3강 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

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-9090

park@jisan.ac.kr

충주

10

정철수

55

777-9999

jung@jisan.ac.kr

광주

레코드 삽입 방법 : 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 테이블 명 ;

예제