[MySQL] 기본 사용법 및 예제

MySql에 대한 기본 사용방법 및 명령어에 대해 알아본다.

- 목차 -

1. Mysql 기본 사용법
2. 데이터베이스 생성 및 삭제
3. 새로 생성한 데이터베이스 등록하기
4.  Mysql 사용자등록
5. 테이블 생성 및 삭제
6. 테이블속성 및 구조변경법
7. 데이터 입력하기 (INSERT)
8. 데이터 검색 (SELECT)
9. 데이터 수정하기(UPDATE)
10. 데이터 삭제하기(DELETE)
11. 새로운 사용자 등록
12. 일반사용자 권한설정(보안사항)
13 .Mysql root 암호 변경하기
14. root 패스워드를 잊어버렸을 때는 어떻게?
15. 특정 테이블의 구조보기
16. 현재 상태 보기(STATUS)



1. Mysql 기본 사용법


● Mysql 데이터베이스 접속하기

# mysql -u 사용자명 -p dbname

설치 직후에는 root 사용자에 비밀번호가 없으므로 다음과 같이 접속하여 MySQL을 관리할 수 있다.

# mysql -u root mysql


● mysql 관리자 root 패스워드 설정하기

MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.
다음 세가지 방법으로 비밀번호를 변경 할 수 있다.

 ▶ mysqladmin이용
# mysqladmin -u root password 새비밀번호

 ▶ update문 이용
# mysql -u root mysql

mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
mysql> FLUSH PRIVILEGES;

mysql> update user set password = password('shcm0816') where user = ' root';

일단 root 비밀번호가 설정된 상태에서는 
mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고 
기존 비밀번호를 입력해야만 한다.

 ▶ Set Password 이용
SET PASSWORD FOR root=password('새비밀번호');


 ▶ 빠져나간후 데이터베이스를 재시작
# mysqladmin -u root reload

 ▶ 패스워드 입력
# mysql -u root -p mysql



● 사용자 추가/삭제

mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';

username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. 
username은 dbname이라는 데이타베이스에 대해 모든 권한을 가지고 있다.
username 사용자는 로컬 호스트에서만 접속할 수 있다. 

 ▶ 다른 호스트에서 접속하려면
mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';

위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.

 ※ 추가
'%'를 호스트네임으로 지정해도 모든 호스트에서 접속할 수 없었다. 
각 호스트별로 다 지정해야 했다.

 ▶ 불필요한 사용자 삭제는
mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;





2. 데이터베이스 생성 및 삭제

● 데이터베이스 생성

① 첫번째 방법
# mysql -u root -p mysqlll
    msyql> create database TESTDB;
    msyql> show databases;

② 두번째 방법
# ./mysqladmin -u root -p create testdb2
Enter password:
# ./mysql -u root -p mysql
Enter passwor

현재 존재하는 데이터베이스 목록을 보여준다.
cd /
msyql> show databases;
msyql> exit
Bye
#

특정 데이타베이스를 사용하겠다고 선언한다.
mysql> USE dbname;


● 데이타 베이스 삭제

# ./mysqladmin -u root -p drop testdb2
Enter password:

-- mysql> DROP DATABASE [IF EXISTS] dbname;
mysql> drop database testdb2

IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.

# ./mysql -u root -p mysql
msyql> show databases;
msyql> desc user =>동일한 결과



3. 새로 생성한 데이터베이스 등록하기

데이터베이스를 새로 생성한 후에는 mysql 이라는 관리용 데이터베이스에 등록한다.
mysql 이라는 데이터베이스의 db라는 테이블에 TESTDB를 등록한 것

msyql> insert into db  values('%','TESTDB','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
msyql> select host, db, user from db;



4.  Mysql 사용자등록(데이터베이스 소유자)

 ▶ mysql 을 사용할 사용자는 반드시 user 라는 테이블에 등록을 해줘야함
msyql> insert into user (host, user, password) values('localhost', 'sspark', password('shcm0816'));

 ▶ 정상적인 등록여부 확인
msyql> select host, user, password from user; 

 ▶ sspark이라는 사용자로 mysql 데이터베이스로 접속
msyql> mysql -u sspark -p
Enter password:

 ▶ 새로운 데이터베이스를 생성하거나 새로운 사용자를 생성한 후에는 
    반드시 reload를 해줘야함
# mysqladmin -u root -p reload
Enter password:




5. 테이블 생성 및 삭제

 ▶ 테이블 생성
msyql> create table testtable (
         ->uid mediumint(4) unsigned DEFAULT '0' NOT NULL auto_increment,
         -> name varchar(12) DEFAULT "" NOT NULL,
         -> email varchar (20) DEFAULT "" NOT NULL,
         -> PRIMARY KEY (uid)
         -> );
Query OK, 0 rows affected (0.00 sec)

 ▶ 현재 데이터베이스의 테이블 목록 확인
msyql>show tables;

 ▶ 필드 구조가 정의한 필드구조로 생성되었는지 확인
msyql> desc testtable;

 ▶ 테이블 삭제
msyql> drop table testtable;
mysql> show tables;



6. 테이블속성 및 구조변경법

 ▶ 테이블 구조 확인
mysql> EXPLAIN tablesname;
혹은
mysql> DESCRIBE tablename;

 ▶ 기존 테이블에 필드 추가하기
ALTER TABLE 테이블명 CHANGE[COLUMN] 필드명 필드타입

mysql> er table testtable add column homepage varchar(30);
mysql> desc testtable;

 ▶ 기존에 필드 속성 변경하기
ALTER TABLE 테이블명 CHANGE[COLUMN] 기존필드명 새필드명 필드타입

mysql> er table testtable change column homepage home varchar(50);

 ▶ 테이블 이름 변경
mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];

 ▶ 기존에 필드 삭제
ALTER TABLE 테이블명 DROP [COLUMN] 필드명

mysql> er table testtable drop column home;
mysql> desc testtable;

 ▶ 기존의 테이블삭제하기
mysql> create table sampletable
mysql> show tables;
mysql> drop table sampletable;
mysql> show tables;



7. 데이터 입력하기 (INSERT)

INSERT INTO tablename VALUES(값1, 값2, ...);

mysql> insert into testtable (uid, name, email) 
mysql> values (', 'parksungsoo', sspark09@soback.kornet.net'" target=_blank>'sspark09@soback.kornet.net');

 ※ 주의: uid라는 필드는 auto_increment 라는 속성으로 생성했기 때문에 
이 속성을 가진 필드들은 실제로 데이터값을 주지 않아도 자동적으로 1씩 증가.

 ▶ 삽입할 필드는 생략가능
mysql> insert into testtable values(','sontaesoo','shutterbug@orgio.net');


특정 필드에만 데이터를 입력할 경우에는 
입력할 필드이름만을 나열하고 그에 해당하는 데이터를 입력한다.

mysql> insert into testtable (uid,name) values(','junwooki');

 ▶ 정상적으로 입력되었는지 확인
mysql> select * from testtable;




8. 데이터 검색 (SELECT)

SELECT col1, col2, ... FROM tablename;

 ▶ 테이블의 모든 테이터를 검색
mysql> select * from testtable;

컬럼명을 *로 하면 모든 컬럼 의미.

 ▶ uid와 name 필드만을 조회
mysql> select uid, name from testtable;

 ▶ where문을 사용하여 특정 조건에 맞는 데이터만을 검색
mysql> select * from testtable where name = "sontaesoo";

 ▶ 출력 결과 레코드의 중복제거하기(DISTINCT)
SELECT DISTINCT sex FROM testtable;

 ▶ 'soo'라는 문자가 포함된 데이터를 모두 검색 (LIKE)
SELECT name FROM testtable WHERE name LIKE '%soo%'

mysql> select name from testtable where name LIKE '%soo%';

 ▶ 특정한  한 문자로 시작하는 데이터만을 출력
SELECT name FROM testable WHERE name LIKE 'park%'

mysql> select name from testtable where name LIKE 'park%';

 ▶ 특정 필드에 데이터가 NULL 인 경우
SELECT uid, name FROM testtable WHERE age IS NULL

mysql> select uid, name from testtable where age IS NULL;

 ▶ 특정 필드의 데이터가 NULL이 아닌 경우
SELECT uid, name FROM testtable WHERE age IS NULL

mysql> select uid, name from testtable where age IS NOT NULL;

 ▶ WHERE 조건문이 여러개 일 경우 (AND/OR) 
mysql> SELECT name, uid, email FROM testtable WHERE savem > 5000 AND sex='M';

 ▶ 결과데이터를 정렬하기 (ORDER BY)
mysql> SELECT name, uid, savem FROM testtable WHERE savem >= 5000 ORDER BY savem DESC;

DESC는 내림차순 ASC는 오름차순.

 ▶ 총 개수 구하기 (COUNT())
mysql> SELECT count(*) FROM testtable WHERE sex='M';

 ▶ 평균 구하기 (AVG())
mysql> SELECT avg(savem) FROM testtable WHERE sex='M';

 ▶ 컬럼의 이름을 바꿔서 출력.
mysql> SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;
mysql> SELECT col1, korean + math + english AS '총점' FROM tablename ORDER BY '총점' ASC;

 ▶ 결과중 처음부터 10개만 가져오기
mysql> SELECT * FROM grade LIMIT 10;

 ▶ 결과중 100번째부터 10개만 가져오기.(첫번째 레코드는 0번 부터 시작한다.)
mysql> SELECT * FROM grade LIMIT 100, 10;



9. 데이터 수정하기(UPDATE)

UPDATE 테이블명 
SET 필드명=필드값 또는 산술식 {, 필드명 = 필드값 또는 산술식}
[WHERE 검색조건]

mysql> select * from testtable;

 ▶ uid 4 번의 데이터에 name 값과 email을 입력해 봄
mysql> update testtable set name = 'junyangmi' where age = 23;
mysql> update testtable set email = jym@superuser.co.kr'" target=_blank>'jym@superuser.co.kr' where age = 23;
mysql> select * from testtable;

 ▶ uid 1 인 사람 (parksungsoo)의 나이를 25살로 수정
mysql> update testtable set age= 25 where uid =1;

 ▶ uid 2인 사람의 나이를 30살로 수정
mysql> update testtable set age = 30 where uid =2;
mysql> update testtable set age = 41 where uid=3;

 ▶ 한해가 지났으므로 모든 사람의 나이를 한 살씩 증가
mysql> update testtable set age = age + 1;
mysql> select * from testtable;



10. 데이터 삭제하기(DELETE)

DELETE FROM 테이블명 [WHERE 검색조건]

mysql> select * from testtable;
mysql> delete from testtable where uid = 3;



11. 새로운 사용자 등록

 ▶ Mysql 에 root로 접속
./mysql -u root -p

 ▶ Mysql DB 선택
mysql> use mysql;
mysql> show tables;

 ▶ 새로운 DB 생성
mysql> create database xlare;

 ▶ user 테이블에 입력 
(user 라는 테이블에 새로운 사용자의 계정이름과 권한설정을 할 차례)
호스팅사용자에 대한 설정 user테이블에 현재 사용장0ㅔ 대한 권한들을 모두 'N'로 설정
mysql> insert into user valuses('localhost', 'xlare', password('123'),'Y','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N');

 ▶ user  테이블에 입력확인
mysql> select * from user where user = 'xlare' ;

 ▶ db 테이블에 입력
mysql> insert into db values('localhost', 'xlare', 'xlare', 'Y','Y','Y','Y','Y','N','N','N','N','N',);

 ▶ db 테이블에 입력 확인
mysql> select * from db where db = 'xlare';

새로운 설정을 바로 적용키위해 mysqladmin 이라는 관리자명령어로 mysql을 재시작한다.
reload를 해주지 않으면 생성했던 것들이 적용이 되지 않는다

# ./mysqladmin -u root -p reload;




12. 일반사용자 권한설정(보안사항)

#./mysql -u root -p mysql
Enter password:
mysql> show tables;
--------------------
tables in mysql
--------------------
columns_priv
db
func
host
tables_priv
user
----------------------

 ▶ columns_priv 테이블
모든 특정 DB 의 특정 User 에 대한 특정 테이블의 각 컬럼에 대한 
select, insert, update, reference 권한을 설정한다.

mysql> desc columns_priv;

 ▶ db 테이블
db라는 테이블에는 특정 DB에 대한 user들의 여러가지 권한들을 설정하는 테이블

mysql> desc db;

kebia 라는 데이터베이스의 소유자와 그 권한에 대해서 알아보고자 한다.
kebia 라는 데이터베이스의 소유자는 hyung 이라는 mysqkl 사용자이며 
이 hyung 이라는 사용자는 이 kebia 라는 데이터베이스에 대한 각종 권한을 볼수 있다.

mysql> select * from db where db = 'kebia';
| localhost | kebia | hyung | Y  |   - - - - - ...........
------------------------

 ▶ tables_priv테이블
columns_priv 테이블처럼 각 데이터베이스와 사용자 및 그 테이블에 대한 권한을 설정한다.

mysql > desc tables_priv;
---------------------

 ▶ user 테이블
- 새로운 사용자를 생성할 때 이 테이블에 등록한다.
- 특정 사용자에 대한 Mysql에 존재하는 모든 데이터베이스에 대한 권한을 설정한다.
- 특정 사용자의 어떤권한에 대한 사항은 특정 데이터베이스가 아니라 Mysql에 존재하는 모든 데이터베이스에 권한이 주어진다.
- host와 user, password 항목을 제외한 나머지 권한들은 모두 'N'으로 설정한다.
- 특정한 사용자에 대한 특정한 데이터베이스에 대한 권한을 주려면 위에서 보았던 db라는 테이블에서 그 권한설정을 하는것이 안전하다.



13 .Mysql root 암호 변경하기

 ▶ 방법 1
mysqladmin 이라는 명령어 사용
# ./mysqladmin -u root -p password 12345

 ▶ 방법 2
mysql의 set 이란 명령어로 root 암호 변경
# ./mysql -u root -p mysql
mysql> set password for root=passwd('12345678');

 ▶ 방법 3
mysql 데이터베이스로 접속하여 update문을 사용하는 방법
# ./mysql -u root -p mysql
mysql> update user set password=password('12345' where user = 'root';
mysql> flush privileges;

 ▶ flush-privileges
권한 테이블을 재시작한다. 권한 설정을 변경했을경우 반드시 재시작한다.
mysql> flush privileges;



14. root 패스워드를 잊어버렸을 때는 어떻게?

 ▶ 단계 1
실행중인 mysql 종료
# ps -ef | grep mysqld
# killall mysqld

 ▶ 단계 2
grant-table 미사용모드로 mysql시작 및 root로 접속
# .safe_mysqld --skip-grant-tables&

 ▶ 단계 3
update문으로 root사용자 패스워드 변경
mysql> update user set password=password('12345') where user = 'root';
mysql> flush privileges;

 ▶ 단계 4
실행중인 mysql 다시 종료
# ps -ef | grep mysqld
# killall mysqld

 ▶ 단계 5
일반모드로 Mysql 재시작
# ./safe_mysqld&
# ps -ef | grep mysql



15. 특정 테이블의 구조보기(show columns from 테이블)

특정테이블의 필드명 type, 설정값들을 보기 위한 것이다.

 ▶ 특정한 테이블의 칼럼(필드)구조 확인
SHOW COLUMNS FROM tablename;

mysql> show columns form user;

 ▶ 필드 구조가 정의한 필드구조로 생성되었는지 확인
DESC 테이블명

 ▶ 특정 테이블의 인덱스 보기
mysql> show index from user;

 ▶ 데이타베이스의 설정상태 보기(show status)
mysql> show status;
# ./mysqladmin -u root -p status

 ▶ 데이터베이스의 설정환경변수와 값보기(show variables)
mysql> show variables;
# ./mysqladmin -u root -p variables

 ▶ 현재 데이터베이스에 연결된 프로세스들 보기(show processlist)
# ./mysqladmin -u root -p processlist


 ▶ mysql 원격접속 권한 설정

mysql> use mysql
mysql> grant all privileges on *.* to 'oops'@'192.168.5.14'      
mysql> identified by 'passwd' with grant option;

ip대신 % 를 주면 모든 호스트허용!
oops 라는 유저가 모든 권한을 부여받을 수있다. 
단 해당 아이피의 호스트 에서만.
이와 같은 설정은 select host,user from user; 로 확인해볼수 있다.



16. 현재 상태 보기

mysql> status;

--------------
mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686)

Connection id: 26
Current database: study
Current user: study@localhost
Current pager: stdout
Using outfile: '
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 min 59 sec

Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 Flush tables: 1
Open tables: 1 Queries per second avg: 0.021
--------------




참고

네이버 지식인