[TIL] MySQL 명령어 정리

1. 데이터베이스 접속

: MYSQL 클라이언트로 접속하기 위해서 터미널에 아래와 같은 명령어를 입력

$ mysql -u 사용자이름 -p

 

 

2. 데이터베이스 조회

: MYSQL 서버에 있는 데이터베이스 조회

mysql> SHOW DATABASES;

 

3. 데이터베이스 선택

: MYSQL 서버에 접속 후, 사용할 데이터베이스 선택

mysql> USE 데이터베이스이름;

4. 테이블 조회

: 선택한 데이터베이스에 있는 테이블을 조회

mysql> SHOW TABLES;

 

 

5. 데이터베이스 만드는 명령어

mysql> CREATE DATABASE 데이터베이스이름;

6. 데이터베이스 삭제하는 명령어

데이터베이스 객체(스키마)를 삭제하는 명령어

해당 데이터베이스와 데이터베이스 내부의 모든 객체(테이블, 뷰, 인덱스 등)가 완전히 삭제된다.

(데이터베이스를 잘못 만들었거나, 데이터베이스 내 모든 데이터를 지우고 싶을 때, 지워도 상관없을 때 사용한다.)

mysql> DROP DATABASE 데이터베이스이름;

 

7. 유저 만드는 명령어

- 사용자이름: 새로운 유저의 이름을 나타낸다. 유저 이름은 대소문자를 구분하지 않고, 일반적으로 모두 소문자로 작성하는 것이 관례이다.

- 호스트: 유저의 접속을 허용할 호스트를 지정한다. 보통 localhost를 사용하여 로컬 호스트에서만 접속을 허용하도록 만든다. % 를 사용하면 어떤 호스트에서든 접속을 허용할 수 있다. (BUT 특수한 경우가 아니라면 특정 호스트만 허용하도록 생성해서 안전하게 DB를 관리하도록 하자!)

- 비밀번호: 유저의 비밀번호를 설정한다.

mysql> CREATE USER '사용자이름'@'호스트' IDENTIFIED BY '비밀번호';

[참고]

모든 호스트에서 접근 가능하도록 유저를 만드는 명령어

mysql> CREATE USER '사용자이름'@'%' IDENTIFIED BY '비밀번호';

위와 같이 할 때 장단점이 있다.

- 장점

1. 클라우드 및 분산 환경 or 데이터베이스에 여러 호스트가 접근해야 하는 경우 ➡️ 추가적 권한 설정 작업이 없어짐

    : 클라우드 환경이나 분산 시스템에서는 호스트 IP가 동적으로 할당될 수 있기 때문에, 모든 호스트에서 접근 가능한 사용자를 생성해서 IP 변경에 따른 추가적인 권한 설정 작업을 없앨 수 있다.

- 단점

1. 보안 취약

    : 모든 호스트에서 접근 가능하기 때문에, DB가 외부 공격에 취약해질 수 있다.

2. 필요하지 않은 호스트에서의 엑세스가 허용될 수 있음

    : 데이터 유출 및 DB의 결함을 악화 시킨다.

3. 로그 및 모니터링 작업이 복잡해질 수 있음

    : 모든 호스트에서의 접근으로 인해, 엑세스 로그를 분석하고 이상 징후를 감지하기 어려울 수 있다.

 

 

8. 유저 조회 방법

mysql> SELECT user, host FROM mysql.user;

 

 

 

🖐 여기서 잠깐!! (권한문제)

일반 유저로 mysql server에 접속해서 다른 유저를 조회하기 위해 user table을 조회하면 아래와 같이 에러가 발생한다.

mysql> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'wanted'@'localhost' for table 'user'

이는 일반 유저(여기서는 wanted)가 'user'테이블에 대한 SELECT권한이 없기 때문에 발생한다.

이 경우, root 권한으로 mysql server에 재접속 후 GRANT 명령어로 일반 유저 (여기서는 wanted)에 권한을 부여해주면 된다.

 

 

(또 다른 경우)

여기서도 마찬가지로, 일반 유저는 DATABASE를 만들 권한이 없기 때문에, root 권한으로 재접속 후 일반 유저(여기서는 wanted)에게 'preonboarding'이라는 DATABASE의 권한을 부여해주면 된다.

 

(아래는 위의 에러 해결방법이다.)

[1] 데이터베이스 'preonboarding'에 대한 'wanted'@'localhost' 유저의 권한을 확인

mysql> SHOW GRANTS FOR 'wanted'@'localhost';

[2] 'wanted'@'localhost' 유저에게 'preonboarding' 데이터베이스에 대한 모든 권한을 부여

mysql> GRANT [권한] ON [데이터베이스].* TO '[사용자]'@'[호스트]';

mysql> GRANT ALL PRIVILEGES ON preonboarding.* TO 'wanted'@'localhost';

(➕ 추가 명령어 )

mysql> FLUSH PRIVILEGES;

나는 GRANT 명령어를 사용했기 때문에 사용하지 않았지만,

만약 INSERT, UPDATE, DELETE 명령어를 사용해서 DB에 변화를 준다면 FLUSH PRIVILEGES; 명령어를 통해 변경사항을 반영해줘야한다.

GRANT 명령어를 사용하는 경우에는 FLUSH PRIVILEGES; 를 사용하지 않아도 된다. (FLUSH PRIVILEGES 명령어는 성능에 부하를 주기 때문에 꼭 필요할 때 사용해야 한다.)

 

 

---

<데이터베이스 내에 들어간 뒤에 (사용할 데이터베이스를 선택한 뒤에) 사용하는 명령어 (DML)>

1. 데이터 조회

mysql> SELECT * FROM 테이블이름;

 

2. 데이터 삽입

mysql> INSERT INTO 테이블이름 (열1, 열2, 열3, ...) VALUES (값1, 값2, 값3, ...);

 

3. 데이터 업데이트

mysql> UPDATE 테이블이름 SET 열1=값1, 열2=값2 WHERE 조건;

 

4. 데이터 삭제

해당 조건문(열의 조건)에 맞는 데이터(행)만 삭제 하는 명령어

다른 데이터(다른 행)은 영향을 받지 않고, 테이블의 구조는 그대로 유지된다.

mysql> DELETE FROM 테이블이름 WHERE 조건;

 

5. 데이터베이스 내의 테이블 생성

Migration은 프레임워크를 사용하거나 ORM(Object-Relational-Mapping) 라이브러리를 사용하여 데이터베이스 스키마를 관리하는 프로세스 중 하나이다.

Migration을 사용하면 CREATE TABLE 명령어 없이 관계테이블들을 만들어낼 수 있다.

 

BUT 쉘 안에서 CREATE TABLE 명령어를 이용하여 테이블 생성할 수 있어야 한다!

mysql> CREATE TABLE 테이블이름 (
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...
);

 

6. 데이터베이스 내의 테이블 삭제

데이터베이스 내 특정 테이블의 구조 & 데이터 모두 삭제 하는 명령어

mysql> DROP TABLE 테이블이름;

💡 하나의 테이블에 있는 모든 데이터를 지우고 다시 시작하고 싶은 경우

: (DELETE이 아닌) DROP 명령어를 사용하고 CREATE 명령어를 사용하면 된다.

 

 

 

DELETE, TRUNCATE, DROP 명령어 차이점

https://wikidocs.net/4021