[Database]MySql 명령어 정리

처음 사용해보는 MySql 명령어 정리입니다.

블러그 깔끔히 정리 하셔서 가져왔습니다. 하단 출처 기재합니다.

 

 

1. 새로운 계정 만들고 관리하기(DB 사용권한 주기)

* mysql 접속 : mysql -u계정ID -p비밀번호 데이터베이스명(DB명 생략가능)

* mysql 종료 : quit \q

i) 데이터 베이스명 만들기
create database DB명;

ii) user 테이블에 계정, 비밀번호 등록
insert into user(host, user, password) values('호스트명'. '계정', password('계정비밀번호'));

iii) db테이블에 db사용권한 등록
insert into db valuse('호스트명', 'DB명', '계정', 'Y 12개'...); 모든권한 줄 필요 없으면 N 적절 사용

iv) user, db 테이블 변경 내용 적용
flush privileges;

===> 자세한 내용은 아래의 내용 참고하기

== 중요한 테이블 user, db 테이블 ==

* 계정등록
ex1) desc user; (시스템에 존재하는 계정을 등록하는 테이블임, 계정, 비밀번호 등등 볼때)
-> select host, user, password from user; 로 레크드 보기(뭐있나 ^^)
그리고 계정, 비밀번호 등록해 보기
-> insert into user(host, user, password) values('localhost'. 'php5', password('1234'));
참고 : password('1234') : 비밀번호를 암호화 하여 관리자를 포함한 어느 누구도 비밀번호를 알아내지 못하도록 하기 위함/나중에 select로 확인해 보면 446ㅁ1210000c85644 뭐 이런식으로 암호와 되있을 것임 ^^

* 사용권한 등록(권한부여)
ex2) desc db;
-> insert into db valuse('localhost', 'php5_db', 'php5', 'Y','Y', 10개Y...); 모든권한 부여할려면 Y 12개
php5 계정에서 php5_db 사용할 수 있도록 권한 부여 ==> DB 테이블에 레코드 추가하면 OK~!

* 시스템 적용 (user, db 테이블의 변경된 내용 적용)
flush privileges;
or) mysql 빠져나온 상태에서는 c:\mysql\bin> mysqladmin reload
둘중 한가지로 시스템에 적용해주세요!!

== 참고
update 문을 이용한 비빌번호 변경
ex) 관리자계정(root) 비밀번호 변경해 보기 ^^
i) user mysql; (사용자 계정관리 DB)
ii) update user(테이블) set password = password('1234') where user='root';
iii) select host, user, password form user;
iv) flush privileges;
==> 반드시 db, user 테이블 업데이트 했을시 반드시 mysql 시스템에 적용!
나와서 mysql -uroot -p1234로 접속해 보기 변경 되었는지~

2. 데이터베이스 관련 명령어
i) 데이터베이스 접속
mysql -u계정ID -p비밀번호 데이터베이스명(생략가능)

ex) mysql -uphp5 -p1234 php5_db
만약 DB명을 생략하게 되면 나중에 use 데이터베이스명; 으로^^(ex use php5_db)

ii) 데이터베이스 만들기(생성) - 반드시 root 관리자 계정 접속
create database DB명;
ex) create database php5_db;
* DB목록 확인 : show databases;

iii) 데이터베이스 삭제
drop database DB명;
ex) drop database php5_db;

3.테이블 관련 명령어

* 테이블 생성
create table 테이블명(
필드명 타입,
필드명 타입,
PRIMARY KEY(필드명)
);

ex)
create table friend
num int NOT NULL,
name char(10),
address char(80),
tel char(20),
PRIMARY KEY(num)
);

Tip>
파일로 만들어 명령어 수행하기
i) 위의 명령어를 파일.sql로 만들기(mysql>bin에 저장)
ex) friend.sql

ii) mysql 실행시 < 파일명입력

ex) c:\mysql\bin> mysql -uphp5 -p1234 php5_db < friend.sql
확인)
c:\mysql\bin> mysql -uphp5 -p1234 php5_db
show tables;
desc friend;

* 테이블 목록보기
show tables;

* 테이블 구조보기
desc 테이블명;

* 테이블의 필드 변경하기
alter table 테이블명 add 새로운필드명 타입 [first 또는 after 필드명];

ex)
alter table friend add age int;
desc friend;

alter table friend add email char(30) after address;

* 필드 삭제
alter table 테이블명 drop 삭제할필드명1, 삭제할필드명2;

ex)alter table friend drop email;

* 필드 수정(필드이름 변경)
alter table 테이블명 change 이전필드명 새로운필드명 타입;

ex)
alter table friend change tel phone int;
desc friend;

* 필드의 타입수정
alter table 테이블명 modify 기존필드명 새로운 타입;

ex) alter table friend modify name int;

* 테이블 이름 변경하기
alter table 이전테이블명 rename 새테이블명;

ex) alter table friend rename student;
show tables;
desc student;

* 테이블 삭제하기
drop table 테이블명;

ex) drop table friend;

3. 테이블 조작명령어
* 데이터 삽입하기(이미만들어진 테이블에 실제데이터 저장)
insert into 테이블명 (필드명1, 필드명2, ... ) values (필드값1, 필드값2, ....);
참고) (필드명1, 필드명2, ... ) 생략가능 ==> 모든 필드에 집어 넣겠다는 뜻

ex) insert into friend (name, name, address, tel) values (1, '배성진', '서울 동작구 노량진동', '234-8998');
insert into friend values (2, '성지연', '대전시 유성구 송강동', '987-333');

==> 삽입된 데이터 보기 : select * from friend;

* 데이터 검색하기(select)
select 필드명1, 필드명2 from 테이블명;
참고) 전체데이터 보기 select * from 테이블명;

* 조건에 맞는 데이터 검색하기(select ~ where)
select 필드명1, 필드명2 from 테이블명 where 조건식;

ex) select id, name, address, tel, sex from mem where sex='W';
select * from men where (age>=20 and age<30) or (age>=40 and age<50) and sex='M'; 20대와 40대 남성 정보

* 특정 문자열을 가진 레코드 검색하기
_ : 임이의 한문자(1byte) __: 한글한글자 2byte

select name, address, tel from men where name like '김%'; 김으로 시작하는 모든 문자열
select naem, id from mem where name like '__용%'; 가운데이름이 용인사람의 이름, 아이디 보기

* 검색된 데이터 정렬하기(order by)
select 필드명1,2 .. form 테이블명 order by 필드명; (오름차순) 1,2,3 abc
내림차순 정렬시 : order by 필드명 desc; (desc만 뒤에 더 붙여주면 OK)
참고) 이름순으로 정렬이 잘 되지 않는 경우
한글이름의 '가나다..'순이 2진 한글코드의 순서와 일치하지 않기 때문
===> mysql 프로그램 설치시 한글이 적용되도록 해당 파라미터를 옵션으로 설정해 주어야 한다.

ex) 서울에 사는 사람들 나이 많은 순대로 정렬
select age, name, address from men where adress like '서울%' order by age desc;

* 데이터 수정(update set where)
update 테이블명 set 필드명 = 필드값(변결할 값) [where 조건식];
ex) men 테이블에서 아이디가 hyun인 레코드의 전화번호를 '888-9999'로 변경
update men set tel='888-9999' where id='hyun';
확인==> select id, name, tel from men where id='hyun';

* 데이터삭제(레코드삭제)
delete from 테이블명 [where 조건식];
-- delete from 테이블명; ===> 주의 : 테이블 전체 레코드 삭제
ex) men 테이블에서 이름이 홍길동인 레코드를 삭제하려면?
delete from men where name='홍길동';
확인==> select * from men where name='홍길동';

4. 데이터베이스의 백업 및 복원

* 데이터베이스 백업(mysqldump > 사용)
c:\mysql\bin> mysqldump -u계정 -p비밀번호 데이터베이스 이름 > 백업파일명
ex) php5계정의 경우 'php5_db'데이터베이스를 'php5_db.sql' 파일에 백업
c:\mysql\bin> mysqldump -uphp5 -p1234 php5_db > php5_db.sql

* 백업파일의 복원(파일실행)(mysql < 사용)
c:\mysql\bin> mysql -u계정 -p비밀번호 db이름 < 백업파일명
ex) mysql -utest -p1234 test_db < php5_db.sql

==== 정리 ====
DB 생성 : create database db명;
DB 목록보기 : show databases;
DB 삭제 : drop database db명;

테이블 생성 : create table 테이블명(필드명1 타입1, 필드명2 타입2,PRIMARY KEY(필드명));
테이블 목록보기 : show tables;
테이블 구조보기 : desc 테이블명;
테이블 필드변경 : alter table을 사용
ex)alter table 테이블명 add 새로운필드명 타입 [first 또는 after 필드명]; (새로운 필드 삽입, 다른건 윗글참조)
테이블 삭제 : drop table 테이블명;

데이터 조작명령 : 삽입(insert) : insert into 테이블명 (필드명1, 필드명2, ... ) values (필드값1, 필드값2, ....);
삭제(delete from) : delete from 테이블명 [where 조건식];
검색(select) : select 필드명1, 필드명2 from 테이블명 where 조건식;

데이터베이스 백업 : mysqldump -u계정 -p비밀번호 데이터베이스 이름 > 백업파일명
데이터베이스 복원 : mysql -u계정 -p비밀번호 db이름 < 백업파일명
(mysql 명령어를 파일(sql)로 지정해서 일괄적 실행)

[출처] http://blog.naver.com/smsprogram|작성자 1004