MySQL 기본적인 명령어들 정리

MySQL를 하다가 안하다가 하니까 계속 까먹어서 기본적인 명령어들은 정리해둬야 할 것 같다.

0. MySQL 접속

일단 명령어를 사용할려면 MySQL로 들어가야 한다.

컴퓨터에 MySQL이 설치되어 있지 않다면 웹 서버 구축의 MySQL를 참조하자.

https://lektion-von-erfolglosigkeit.tistory.com/10

 

명령프롬포트를 관리자 권한으로 실행(Win + s -> 명령 프롬포트 검색 -> 관리자 권한으로 실행 클릭)

mysql -u root를 입력한다.

(-u뒤는 username, 따로 설정하지 않았다면 root다)

(만약 password를 설정 했다면 root 뒤 -p를 붙이고 엔터를 누른 뒤 비밀번호까지 입력해야 한다.)

이렇게 나오면 성공이다. 이제 명령어를 사용할 수 있게 되었다.

(exit;를 치면 나간다.)

(대부분의 명령어를 대문자로 쓰는 이유는 사용자가 입력한 이름과 구분하기 위해서다. 소문자로 써도 상관없다.)

 

1. DATABASE 관련

MySQL의 존재목적이자 제일 처음 해야하는 데이터베이스다.

1.1 데이터베이스 생성

일단 데이터베이스를 만들어야지 뭔가 다른 걸 시작 할 수 있다.

CREATE DATABASE 데이터베이스명;

데이터베이스명을 이름으로하는 DB를 만든다.

 

후술에서는 데이터베이스명는 test로 기재한다.

 

1.2 데이터베이스 불러오기

데이터베이스를 만들긴 했는데 이게 진짜 있는지 확인을 해봐야 하지 않겠는가

SHOW DATABASES;

현재 존재하는 데이터베이스를 모두 불러온다.

 

 

1.3 데이터베이스 삭제하기

데이터베이스를 잘못 만들었거나 삭제해야 할 때가 있다.

(삭제하면 복구는 못하니 enter는 신중히 눌러야한다.)

 

DROP DATABASE test;

위 명령어를 입력하면 test라는 이름을 가진 데이터베이스는 삭제된다.

 

1.4 데이터베이스 이름변경

아쉽게도 MySQL 5.1.23부터 rename 쿼리가 삭제되었다.

그래서 새로운 데이터베이스를 만들고 데이터베이스 안의 테이블을 옮기고 기존 데이터베이스를 삭제해야 한다.

1. CREATE DATABASE 새데이터베이스;

2. RENAME TABLE 기존데이터베이스.테이블이름 to 새데이터베이스.테이블이름;

(테이블이 많으면 여러 번 해야한다.)

3. DROP DATABASE 기존데이터베이스;

 

위와 같이 최소 3번의 명령어를 사용해야 한다.

처음에 만들 때 잘 만들자.

 

1.5 데이터베이스 선택하기

이제 데이터베이스를 만들고 확인하고 삭제했으니 본격적인 작업을 위해 데이터베이스를 선택하자.

선택된 데이터베이스에서 표을 만들거나 삭제, 값을 넣거나 삭제할 수 있다.

 

USE test

위 명령어로 데이터베이스를 선택할 수 있다.

(이상하게 ;(세미콜론) 없이도 된다.)

 

2. table(표) 관련

MySQL의 본기능, 여러가지 값들이 저장될 표(공간)를 만든다. (table은 schema(스키마)라고도 부른다.)

데이터베이스를 선택하고 실행해야 한다.

2.1 테이블 생성하기

일단 만들어보자.

CREATE TABLE table_name (
칼럼명1 data_type,
칼럼명2 data_type
);

여기서 칼럼은 표의 세로줄에 해당한다.

 

예를 들어 학생들의 정보를 저장하는 데이터베이스를 만들어보자. (뒤의 글에서도 예제로써 사용한다.)

CREATE TABLE student(

id INT(11) NOT NULL AUTO_INCREMENT,

name CHAR(4) NOT NULL ,

gender ENUM('남자','여자') NOT NULL ,

address VARCHAR(50) NOT NULL ,

birthday DATETIME NOT NULL ,

PRIMARY KEY (id)

);

-NOT NULL은 비어있으면 안되는 칼럼을 말한다.

-AUTO_INCREMENT는 보통 중복되면 안되는 id(식별번호)등에 적용된다.

-PRIMARY KEY는 괄호 안의 것을 기본 키(유일 값)로 설정한다. 보통 AUTO_INCREMENT와 같이 사용된다.

(위 세 가지는 많이 쓰이는 제약조건들이다.)

 

위는 필드 타입의 표이다.

(딱히 외우거나 그럴 필요는 없다.)

 

2.2 테이블 조회

역시나 만들었다면 잘 만들어졌나 확인을 해야한다.

SHOW TABLES;

테이블 목록을 불러올 수 있다.

 

또한 만든 칼럼들의 정보도 조회가능하다.

DESC student;

 

2.3 테이블 삭제하기

만들었으면 삭제도 해봐야지

DROP TABLE student;

student라는 이름의 테이블을 삭제한다. (물론 복구따위는 안된다.)

 

또는 테이블의 데이터만 삭제할 수도 있다.

TRUNCATE TABLE student;

역시나 복구는 불가능하다.

 

2.4 테이블 칼럼 추가 및 삭제, 수정

그렇다면 이미 만든 테이블의 칼럼을 다뤄보자.

ALTER TABLE student ADD 칼럼이름 필드타입 제약조건...;

student 테이블에 새로운 칼럼을 추가한다.

(필드이름은 칼럼의 이름, 필드타입은 위 표를 참고하자.)

 

ALTER TABLE student DROP 필드이름;

student 테이블의 필드이름 칼럼을 삭제한다.

 

ALTER TABLE student MODIFY COLUMN 필드이름 필드타입;

student 테이블의 필드이름의 기존 필드타입을 필드타입으로 바꾼다.

 

3. 테이블의 값 관련

본격적으로 데이터베이스에 값을 저장하고 수정한다.

3.1 INSERT

insert는 오락실을 가봤다면 많이 봤을 것이다.  끼워넣다 라는 뜻으로 MySQL에서 값을 넣을 때 사용한다.

INSERT INTO student(name, gender, address, birthday) VALUES('홍길동', '남자', '거제도', NOW());

- NOW()는 현재 시각을 나타낸다.

각각 다음과 같이 대응된다.

name-'홍길동', gender-'남자', address-'거제도', birthday-NOW();

 

3.2 SELECT

여기서도 만들었으면 확인을 해봐야 하지 않겠는가

SELECT * FROM student;

- *는 all을 나타낸다고 생각하자.

student 테이블의 모든 값을 조회한다.

 

여러가지 값들중 특정한 값을 가진 것들만 조회하고 싶다면?

SELECT * FROM student WHERE address='거제도';

뒤에 WHERE 필드이름='찾고싶은값'; 을 붙여준다.

(AND나 OR를 통해 여러가지 조건을 사용할 수도 있다.)

 

또는 특정 필드만 조회할 수도 있다.

SELECT name, address FROM student;

student 테이블에서 name과 address만 가져온다. (여기서도 WHERE를 쓸 수 있다.)

 

추가명령어들

- 기본 SELECT문을 조금 바꿔서 더 정확하게 값을 찾을 수 있다.

 

1. 중복되는 값 제거

SELECT DISTINCT name FROM student;

홍길동이 두명 이상 있어도 한번만 나온다.

 

2. 선택한 값들의 정렬

SELECT * FROM student ORDER BY name;

name을 기준으로 오름차순으로 정렬한다. (뒤에 DESC를 붙이면 내림차순)

 

3.3 UPDATE

저장된 값을 수정할 수 있다.

UPDATE student SET name='홍길남', address='서울' WEHRE name='홍길동';

name이 홍길동인 값들의 name과 address를 바꾼다.

만약 WHERE을 쓰지않으면 모든 값들이 변경된다. (역시나 복구는...)

 

3.4 DELETE

저장된 값을 삭제한다. 이제 눈치가 빠르다면 대충 어떻게 생겼을지 감이 올지도 모른다.

DELETE FROM student WHERE name='홍길동';

name이 홍길동인 값들을 모두 삭제한다.

(WHERE을 쓰지 않으면 모든 값이 삭제된다. = truncate table student;)

 

4. 관계형 데이터베이스

MySQL은 사실 관계형 데이터베이스 관리 체계다.(RDMS, Relational Databases Management System)

4.1 JOIN

두 테이블의 값을 어떤 식별자를 통해 합쳐서 보여준다.

SELECT * 첫번째테이블이름 JOIN 두번째테이블이름 ON 조건(첫번째테이블.식별자 = 두번째테이블.식별자);

물론 * 대신 가져오고 싶은 필드만 입력해도 된다.

(조금 더 자세한 정보는 https://opentutorials.org/course/3161/19545를 참고하면 좋다.)

 

5. 유저관리

MySQL의 새로운 유저를 추가하거나 유저 정보를 수정한다.

먼저 root 계정으로 로그인 후 use mysql로 데이터베이스 접속

5.1 유저 조회

SELECT user, host FROM user;

user와 host를 조회한다.

 

5.2 유저 생성

CREATE USER 'test'@'localhost' identified by 'password';

localhost에서 접속가능한 password를 가진 test라는 유저 생성

password를 설정 하지 않아도 됨

원격 접속을 허용할려면 localhost를 % 또는 그 사람의 ip주소로 변경

 

5.3 유저 삭제

되도록이면 root계정을 이용하자. (모든 권한이 부여된 사용자)

delete from user where user='test';

flush privileges;

test라는 이름을 가진 유저를 삭제하고

변경사항을 반영한다.

 

5.4 유저 정보 수정

5.4.1 비밀번호 변경

alter user 'test'@'localhost' identified by 'password';

localhost에서 접속가능한 test의 비밀번호를 변경한다.

5.4.2 비밀번호 없애기

set password for test@localhost='';

test계정이 비빌번호 없이도 접속하도록 한다.

(''는 작은따옴표 두개다.)

5.4.3 유저 권한 부여

grant all privileges on *.* to 'test'@'localhost';

- test계정에 모든 권한 부여

grant all privileges on DB이름.* to 'test'@'localhost';

- test계정에 특정 DB에 모든 권한 부여

grant all privileges on DB이름.테이블명 to 'test'@'localhost';

- test계정에 특정DB의 특정 테이블에 대한 모든 권한 부여

grant select on DB이름.테이블명 to 'test'@'localhost';

- test계정에 특정DB의 특정 테이블에 대한 조회(select) 권한만 부여

grant update(칼럼1, 칼럼2) on DB이름.테이블명 to 'test'@'localhost';

- test계정에 특정DB의 특정 테이블의 특정 칼럼에 대한 수정(update) 권한만 부여

5.4.4 유저 권한 삭제, 조회

revoke all on *.* from 'test'@'localhost';

- test계정에 모든 권한 삭제

- 권한 부여와 구조가 비슷하기때문에 특정 권한만 부여하는 것도 가능

 

show grants for 'test'@'localhost';

- test계정에 부여된 권한 확인

정리

mysql -u root

- mysql 접속 명령어

(비밀번호를 설정했다면 -p도 붙여주자.)

데이터베이스 관련

CREATE DATABASE 데이터베이스명;

- 데이터베이스 만들기

 

SHOW DATABASE;

- 모든 데이터베이스 불러오기(조회)

 

DROP DATABASE 데이터베이스명;

- 데이터베이스 삭제

 

USE 데이터베이스명

- 데이터베이스 접속

 

테이블관련

CREATE TABLE 테이블명(

칼럼이름1 데이터타입 제약조건,

칼럼이름2 데이터타입 제약조건,

);

- 테이블 만들기

 

SHOW TABLES;

- 테이블 불러오기(조회)

 

DESC 테이블명;

- 테이블의 칼럼 정보 조회

 

DROP TABLE 테이블명;

- 테이블 삭제

 

TRUNCATE TABLE 테이블명;

- 테이블의 데이터만 삭제

 

ALTER TABLE 테이블명 ADD 칼럼이름 필드타입 제약조건;

- 테이블에 새로운 칼럼 추가

 

ALTER TABLE 테이블명 DROP 칼럼이름;

- 테이블의 칼럼 삭제

 

ALTER TABLE 테이블명 MODIFY COLUMN 칼럼이름 필드타입;

- 테이블의 칼럼 필드타입 수정

 

테이블 값 관련

INSERT INTO 테이블이름(필드이름1, 필드이름2...) VALUES (데이터값1, 데이터값2...);

- 테이블에 값 넣기

(NULL, AUTO_INCREMENT를 제약조건을 가진 필드는 생략 가능)

 

SELECT * FROM 테이블이름;

- 테이블에 저장된 모든 값 불러오기(조회)

(WEHRE 필드이름=데이터값 을 붙여 조건에 만족하는 값만 불러올 수도 있다.)

 

UPDATE 테이블이름 SET 칼럼이름1=데이터값1, 칼럼이름2=데이터값2...

WHERE 칼럼이름=데이터값;

- WHERE조건에 만족하는 테이블의 칼럼의 값들을 수정한다.

 

DELETE FROM 테이블이름 WHERE 칼럼이름=데이터값

- WHERE 조건에 만족하는 칼러므이 값들을 삭제한다.

 

SELECT * 첫번째테이블이름 JOIN 두번째테이블이름 ON 조건;

- 두 테이블을 조건에 맞춰 합쳐서 불러온다.

(조건은 보통 첫번째테이블.칼럼이름 = 두번째테이블=칼럼이름 으로 쓴다.)