saii 잡다한 블로그

DML(Data Maniplulation Language)는 데이터 조작어 즉 실제 코드에서 많이 다루는 부분이다.

어차피 문법은 다른 DB나 개발 언어에서 비슷하다. 그러니 일단 스탠다드한 형태만 알아두고 사용하고, 만약 에러가 난다면 그때 한번씩 해당 언어랑 DB를 검색해보면 된다.

우선 명령어 종류다.

명령어의 종류는 SELECT, INSERT, UPDATE, DELETE가 있다.

위에 명령어는 다양하게 사용할 수 있다. 내가 생각나는대로 쓰는 것보다 계속 다양한 쿼리를 쓸 때 업데이트 하는 게 좋을듯하다.

그리고 쿼리문은 줄바꿈을 해도 상관 없다. 그리고 대소문자도 상관 없다. 어쨋든 DB는 내장함수를 대소문자, 줄바꿈 상관 없이 구분한다. 물론 컬럼이나 테이블은 아니다. 그리고 한 쿼리문당 세미콜론으로 구분한다. 그래서 끝날 때엔 세미콜론을 붙여줘야한다. 

DB마다 내장함수들도 다양하니 한번 검색해보길 바란다.

Select 명령어

Select 명령어는 생성되어 있는 테이블에서 데이터를 가져오는 명령어다.

자연스럽게 조건에 맞는 데이터라는 말이 따라 붙을 것이다. 

물론 조건문에 중첩 또한 가능하다. 다중 If 문 또는 다중 For 문 처럼 말이다. 그러니 좀 더 유연하게 사용하길 바란다.기본 형태는 아래와 같다.

Select 추출할 컬럼  From 해당 테이블  Where 조건문

예를 하나 들어보겠다.

SELECT * FROM table WHERE id = 'user';

table 테이블에서 id가 user의 컬럼 데이터를 모두 가져온다는 뜻이다.

Select문은 나의 경우는 조건문에서 많이 갈린다. 예를 들어 AND 나 OR 그리고 다중 Select 문을 사용하여 데이터를 추출한다.

Insert 명령어

Insert는 명령어는 이미 생성된 테이블에 데이터를 삽입하는 명령어다.

이 명령어는 해당 테이블에 필요한 컬럼의 데이터만 넣을 것인지 아니면 해당 테이블의 모든 컬럼 데이터를 넣을 것인지 쿼리문에서 구별할 수 있다. 모든 컬럼 데이터를 삽입할 경우 컬럼의 순서에 맞게 모든 데이터를 입력해야한다. 특정 컬럼의 데이터만 넣을 시에는 Into 부분에서 컬럼을 같이 써줘야한다.

INSERT INTO 테이블(컬럼1, 컬럼2, ... )
        VALUES (컬럼1 데이터, 컬럼2 데이터, … )

INSERT INTO 테이블
        VALUES (컬럼1 데이터, 컬럼2 데이터, … )

첫번 째 Insert문은 바로 특정 컬럼의 데이터만 넣고 싶을 때이다.

두번 째 Insert문은 해당 테이블에 모든 컬럼에 데이터를 삽입하고 싶을 때 쓰는 경우다.

아래 예시를 한번 보자.

INSERT INTO table (id, name) VALUES ('user', '홍길동');

Update 명령어

Update 명령어는 이미 들어가 있는 데이터를 수정하는 명령어다.

조건식을 통해 구분하여 데이터를 수정한다.

* 이 부분에서는 조건문을 사용하지 않으면 그 테이블에 해당 컬럼이 모두 바뀌니 주의하자.

UPDATE  테이블명
SET     컬럼1 = 컬럼1의 데이터, 컬럼2 = 컬럼2의 데이터, ...
WHERE   조건식

예를 한번 들어보겠다. 아래는 name이 홍길동인 사람의 id를 모두 400으로 수정하는 쿼리다.

UPDATE  table
SET     id = '200'
WHERE   name = '홍길동';

주의할 점은 조건문에 기본키가 아닌 다른 컬럼일 경우 적어도 1개 이상의 데이터가 변경될 수 있다. 

그러니깐 이름이 홍길동인 사람들의 id가 다 바뀐다.

Delete 명령어

Delete 명령어는 특정 데이터나 아니면 테이블에 있는 데이터를 삭제할 수 있다.

이 명령어도 주의해야 할 점은 조건문이 잘못될 경우 상관 없는 데이터가 삭제될 수 있다. 그리고 조건문이 없으면 모든 데이터가 삭제된다.

DELETE FROM 테이블 WHERE 조건문

예를 한번 들어보겠다. 아래는 name이 '홍길동'인 사람의 데이터를 삭제하는 쿼리다.

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

Update 문 처럼 조건문에 기본키가 아닌 다른 컬럼일 경우 적어도 1개 이상의 데이터가 삭제될 수 있다.

그러니깐 이름이 홍길동인 사람들의 데이터는 다 삭제된다.

* 저는 현재 Mysql을 사용하고 있습니다.

#mysql dml

DB 때문에 이렇게 고생을 해야한다는게 화가난다...

Error fetching schema list: Error executing 'SHOW DATABASES' Lost connection to MySQL server during query. SQL Error: 2013

이 에러는 workbench에서 실행한 쿼리에 걸리는 시간이 길어져서 정해진 제한시간 동안 쿼리가 종료되지 못해서 발생한 에러다.

나의 경우는 import하는데 특정 테이블이 커서 오래걸려서 동안 발생했다.

해결방법은 쿼리 제한 시간을 늘려주면 된다.

workbench 탭에서 edit > Preferences >SQL Editor

DBMS connection keep-alive interval : 연결 유지 시간 간격
DBMS connection read timeout interval : 읽기 시간 초과 간격
DBMS connection timeout interval 연결 시간 초과 간격 

이 3개를 늘려주면 해결됩니다.

Dump 파일을 다시 import 했는데 대충 그냥 게이지만 보고 됐겠네 하고 스키마 탭 보니깐 DB가 안생겨져있었다.

다시 import 하고 log를 보니 에러가 있었다.

ERROR 1049 (42000): Unknown database 'DB명'

참 어처구니가 없었다.

MySQL에서 DB 백업 후 복원 할 때 자주 생긴다고 한다.

당연히 내가 DBA가 아닌 이상 왜 그런지는 잘 몰랐다. 당연히 나는 import하는데 DB도 생성해주는 줄 알았기 때문이다.

에러 메세지 그대로 DB를 알 수 없다는 의미다.

create database DB;

이거 한 줄 이면 해결된다.

DB import 시 해당 DB와 동일한 DB가 생성 되어 있어야 한다고 한다.

이제야 Default Target Schema 의미를 알게된듯...?

루트 계정 접속

 mysql -u root -p

user 테이블 확인

mysql> use mysql;
mysql> select user, host from user

user 추가

mysql> create user '사용자 이름 입력하세요'@'localhost(또는 %)' identified by '비밀번호 입력';

'localhost' or '%' or 'IP'  : localhost는 해당 pc만 접속 , %는 외부의 모든 접속 가능, IP는 해당 IP만 접속 가능하다.

user 삭제

mysql> drop user '사용자명 입력'@'localhost';

해당 user의 접속 권한 또한 똑같이 입력해야 한다.

user DB 권한 부여 (Grant)

mysql> grant all privileges on *.* to '사용자'@'localhost';
mysql> grant all privileges on DB이름.* to '사용자'@'localhost';
mysql> grant all privileges on DB이름.테이블명 to '사용자'@'localhost';
mysql> grant select on DB이름.테이블명 to '사용자'@'localhost';
mysql> grant update(컬럼1, 컬럼2) on DB이름.테이블명 to '사용자'@'localhost';

1번 라인은 모든 DB, DCL을 제외한 권한을, 2번 라인은 특정 DB 권한 및 DCL을 제외한 권한을, 3번 라인은 특정 DB의 테이블에 대한 권한 4번 라인은 DB의 테이블에 대한 select 권한 5번 라인은 DB의 테이블에 대한 특정 컬럼 수정 권한이다.

이렇게 특정 DB의 권한을 줄 수도 있고 select, delect 등의 특정 권한만 줄 수도 있다.

all privieges는 모든 권한을 , *.*는 모든 DB의 모든 테이블을 뜻한다.

정리해보면 grant (권한) on (범위)  to (user) 이렇게 정리할 수 있겠다.

1번 라인에 with grant option 을 맨 끝에 추가하면 권한 부여(ROOT 권한)을 부여한다.

user 권한 삭제 (Revoke)

이 부분은 Grant와 구조가 같다. 응용해서 특정 조건을 삭제할 수 잇다.

mysql> revoke all on DB명* from 'user'@'localhost';

user 권한 확인

mysql> show grants for 'user'@'localhost';

AWS 에서 사용중인 EC2랑 S3, RDS를 해지할 일이 생겼다.

그래서 사용하고 있던 DB를 Dump 떠서 옮기려고 한다.

1. Date Export

왼쪽 Navigator에서 Administration 탭에 Data Export 선택

Data Export 탭이 열리면서 하단의 여러 옵션이 있습니다.

우선 백업할 DB와 Table을 선택합니다.

그 후 Objects to Export, Export Options을 설정합니다.

< Objects to Export >

- Dump Stored Procedures and Functions : 저장 프로시저, 함수 백업

- Dump Events : 이벤트 백업

- Dump Triggers : 트리거 백업

< Export Options >

- Export to Dump Project Folder : 폴더 안에 테이블 별로 백업 파일 생성

- Export to Self-Contained File : DB 별로 한 개의 파일로 백업 파일 생성

Include Create Schema는 스키마 생성에 대한 문장까지 삽입해서 내보내기한다. 만약 다른 이름의 스키마로 복원할 계획이라면 체크하지 않는다.

그리고 오른쪽 하단에 Start Export를 클릭하면 해당하는 경로에 .sql 파일이 생성된다.

2. Data Import/Restore

Export 바로 밑에 Data Import/Restore 클릭

클릭하면 Data Import/Restore 탭이 열립니다.

그 밑에 여러 옵션들이 있습니다.

< Import Option >

Dump Project Folder과 Self-Contained File가 있습니다.

Export할 때 선택한 옵션을 선택해 주시면 됩니다.

아래처럼 2개의 컬럼이 있는데 2번 idx를 삭제하고 insert를 하게되면 그 다음 번호인 3번부터 추가가 됩니다.

다시 1번부터 쓰기 위해선 초기화를 해줘야 합니다.

쿼리도 그렇게 어렵지 않습니다.

ALTER TABLE 테이블명 AUTO_INCREMENT=바꾸고 싶은 값;

이렇게 하시면 됩니다. 

charcter-set을 utf8로 변경 했는데 insert문을 실행하면 ?? 이렇게 물음표가 들어갔다.

이는 character set을 변경하지 전에 데이터베이스를 생성했었기 때문에 해당 데이터베이스의 character set은 변경되지 않아서 그런것입니다.

사실 이 부분에서 몇몇분은 데이터 덤프 빼놓고 다시 하시는 분들도 계시더라고요.

그리고 저는 mysql workbench에서 변경을 했는데도 계속 ?? 물음표가 들어갔습니다. 

DB와 모든 테이블에 대해서 character-set을 바꿔줘야 합니다. 조금 많이 번거롭습니다. 그래서 테이블이 많으면 그냥 다시 만드나 봅니다.

밑에 두개를 실행해 줍니다.

ALTER DATABASE [DB명] DEFAULT CHARACTER SET UTF8

ALTER TABLE [TABLE명] CONVERT TO CHARACTER SET UTF8;

해줬더니 잘 들어가네요