컴퓨터를 다루다

뷰(View)란 데이터베이스에 존재하는 일종의 가상 테이블을 말합니다.

이러한 뷰를 이용하여 실제 테이블처럼 행과 열을 가지고 있지만, 실제로 데이터를 저장하고 있지는 않습니다.

즉, 우리가 여러 테이블을 번거롭게 들러서 확인을 해야 할 때 혹은 SELECT 문을 겹겹이 사용해서 어려운 쿼리문으로 조회를 수행해야 할 때 이러한 번거로움을 줄여주는 것이 바로 뷰(View) 입니다.

단, 유의해야 할 점은 MySQL에서 뷰는 단지 다른 테이블이나 다른 뷰에 있는 데이터를 보여주는 역할만을 수행합니다. 이름이 그래서 뷰(View)인가 봅니다. 뷰는 마치 하나의 테이블인 것처럼 보여준다는 장점이 있습니다.

MySQL에서의 뷰의 장점은 다음과 같습니다.

1. 특정 사용자에게 테이블 전체가 아닌 필요한 필드만 보여줄 수 있음

2. 복잡한 쿼리를 단순화해서 사용할 수 있음

3. 위와 같이 사용한 쿼리를 재사용할 수 있음

단, 편리하지만 단점 또한 있습니다.

1. 한 번 정의된 뷰는 변경이 불가능함

2. 삽입, 삭제, 갱신 작업에 많은 제한 사항을 가짐

3. 뷰는 자신만의 인덱스를 가질 수 없음

위와 같은 특징을 유의하여 뷰를 생성하고 사용해보도록 합시다.

VIEW는 마치 테이블을 만들듯 생성할 수 있습니다. 따라서 CREATE 문을 사용하여 생성해보도록 합시다.

MySQL에서 VIEW를 생성하는 예제는 다음과 같습니다.

-- VIEW 생성 명령어
CREATE VIEW [view_name] AS \
SELECT [field_name_1], [field_name_2] ... \
FROM [table_name] \
WHERE [조건];

위의 명령에서는 하나의 테이블에서 필요한 필드의 내용만 가져오기 위한 명령어를 작성해보았습니다.

이는 테이블이 하나밖에 없기 때문입니다.

만약 여러분이 여러 테이블의 내용을 섞어서 보고싶다면 다음과 같은 방법을 사용해보도록 합시다.

-- 여러 테이블을 조회하는 VIEW 생성 명령어
CREATE VIEW [view_name] AS \
SELECT a.[field_name_1], b.[field_name_2] \
FROM [table_name_1] AS a, [table_name_2] AS b \
WHERE [조건];

위의 명령어는 table_name_1과 table_name_2의 두 테이블에서 각각 field_name_1과 field_name_2를 가져오는 명령어입니다.

field_name_1은 table_name_1에 있는 필드이고, field_name_2는 마찬가지로 table_name_2에 있는 필드입니다.

여기서 [조건]에 적절한 조건을 넣어줘야 합니다.

예를들면..

- table_name_1의 어떤 필드와 table_name_2의 어떤 필드의 값이 같은 것을 조회한다.

- table_name_1의 어떤 필드의 특정 값을 조회한다.

등이 있겠습니다.

실행 결과는 다음과 같이 나타나게 됩니다.

뷰 대체란 기존에 생성했던 뷰를 다시 새로운 뷰로 대체한다는 뜻입니다.

뷰는 한 번 생성하면 변경이 불가능하기 때문에 새로운 뷰로 대체하는 것으로 뷰에 설정한 필드를 대체할 수 있습니다.

-- 뷰 대체 명령어
-- 기존의 view_name 이라는 VIEW를 새로운 필드로 설정해줄 수 있음
CREATE OR REPLACE VIEW [view_name] AS \
SELECT [field_name_1], [field_name_2] AS [new_field_name] \
FROM [table_name];

만약 위와 같이 뷰를 대체하게 되면, [field_name_2]라는 이름을 가진 필드가 [new_field_name]이라는 이름으로 대체됩니다.

생성한 뷰는 일반적인 SELECT 명령어로 사용할 수 있습니다.

-- 생성된 뷰 조회(뷰 이름 : view_name)
SELECT * FROM view_name;

위와 같이 생성된 뷰를 전체 조회하게 되면 원하는 테이블의 원하는 필드 값만 볼 수 있습니다.

DCL(Data Control Language) 명령은 프로그래밍 보다는 MySQL 내부의 데이터를 제어하는 언어입니다.

제어라 하니 애매모호할 수 있겠습니다.

더 자세히 말씀드리자면! 데이터의 보안, 무결성, 회복, 병행 수행제 등을 정의하는데 사용합니다.

DCL의 명령어는 GRANT, REVOKE, COMMIT, ROLLBACK 이라는 명령어가 있습니다.

그런데 역서 COMMIT과 ROLLBACK 명령어는 TCL(Transaction Control Language)이라고 하여 트랜잭션을 제어하는 명령어라고 구분되어 표현되기도 합니다.

하지만 이 포스트에서는 모두 DCL이라고 생각하고 작성하도록 하겠습니다.

GRANT 명령어는 사용자에게 권한을 부여하기 위한 명령어입니다.

MySQL에서 사용자 권한을 주는 방법에는 INSERT 문을 이용하는 방법이 있습니다.

그러나 여기서는 GRANT의 명령어로 권한을 주는 명령어를 살펴보도록 하겠습니다.

-- 사용자 권한 부여 명령어
GRANT ALL PRIVILEGES ON [dbname.table_name] TO [user@host] IDENTIFIED BY 'my_password';


-- 예제 (호스트 : 로컬호스트)
GRANT ALL PRIVILEGES ON testDB.testTable TO myuser@localhost IDENTIFIED BY 'testPassword';

-- 예제 (호스트 : 원격 접속)
GRANT ALL PRIVILEGES ON testDB.testTable TO myuser@'%' IDENTIFIED BY 'testPassword';

-- 예제 (호스트 : 아이피)
GRANT ALL PRIVILEGES ON testDB.testTable TO myuse@192.168.0.100 IDENTIFIED BY 'testPassword';

GRANT 명령어 이후 설정한 권한을 적용해야 합니다.

-- 설정한 권한 적용 명령어
FLUSH PRIVILEGES;

위의 명령어까지 완료하면 이제 GRANT 명령어가 적용됩니다.

REVOKE 명령어는 GRANT 명령어로 적용한 권한을 해제해주는 명령어입니다.

이 명령어를 사용하면 MySQL에서 사용자 권한을 해제해줄 수 있습니다.

-- 권한 해제 명령어(INSERT, UPDATE, CREATE 권한 해제)
REVOKE insert, update, create ON [dbname.table_name] TO [user@host];

-- 권한 해제 명령어(전체 권한 해제)
REVOKE ALL ON [dbname.table_name] TO [user@host];

위와 같은 예제로 간단히 알아보았습니다.

만약 예제를 통해 해제한 권한이 잘 적용되었는지 확인해보고자 한다면 다음 명령을 사용해보면 좋습니다.

-- 권한 확인 명령어
SHOW GRANTS FOR [user@host];

COMMIT 명령어는 작업한 결과를 물리적 디스크로 저장하고, 조작 작업이 정상적으로 완료되었음을 관리자에게 알려주는 명령어입니다.

요약하자면, 작업 결과를 저장 후, 관리자에게 "잘 되었습니다" 하고 알려준다는 것입니다. 이 명령어는 INSERT, UPDATE, DELETE 등의 작업 내용에 대해 데이터가 물리 디스크로 완전히 업데이트되며, 모든 사용자가 변경한 데이터의 결과를 볼 수 있게 됩니다.

이 명령어를 사용하면 MySQL에서 작업한 내용들이 물리적 디스크로 저장/적용되고, 정상적으로 처리되었다면 이를 알려줍니다.

-- 여러분이 INSERT, UPDATE, DELETE 등의 작업을 수행하였다고 가정...

-- 이전 까지의 작업을 완전 저장하는 명령어
COMMIT;

위의 명령어를 보고 조금 맥이 빠질 수 있습니다.

실제로 MySQL과 물리디스크 사이에서 일어나는 일은 매우 복잡하지만, 명령어는 딱 한 단어 뿐입니다.

ROLLBACK 명령어는 작업했던 내용을 원래의 상태로 복구하기 위한 명령입니다. 이는 INSERT, UPDATE, DELETE 와 같은 트랜잭션의 작업 내용을 취소할 수 있습니다.

단! 이 명령어를 사용하는데 있어서 주의해야할 점이 있습니다.

바로 COMMIT 명령어를 사용하기 이전의 상태만 ROLLBACK이 가능하다는 것입니다..!

COMMIT을 하게 되면 위에서 설명드렸다싶이, 물리디스크에 직접 저장하고 알리는 기능이므로, 이미 물리적으로는 이전의 상태가 저장되어 있지 않다는 의미입니다.

따라서, 이전 상태로 되돌릴 수 없습니다.

-- 여러분이 INSERT, UPDATE, DELETE 등의 작업을 수행하였다고 가정...

-- 이전 까지의 작업을 취소하는 명령어
ROLLBACK;

위의 명령어도 사실 별 게 없습니다.

COMMIT과 마찬가지로 단 하나의 단어로만 명령을 수행합니다.

MySQL 명령어는 웹 프로그래밍 할 때, 데이터베이스를 정의하는 내용은 크게 사용되지 않을 수 있습니다.

그러나 DML(Data Manipulation Language) 명령어 즉, 데이터 조작어가 매우 많이 사용됩니다.

이 언어는 PHP, Python Flask, Python Django 등과 같은 서버사이트 언어에서 많이 사용되며, 문법은 MySQL의 방법을 그대로 사용합니다.

때문에 이번에는 DML 명령어들이 대해 정리해보려고 합니다.

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

위와 같은 명령어는 조건문을 붙여서 보다 능동적이게 명령어를 활용할 수 있습니다.

SELECT 명령어는 생성되어 있는 테이블에서 원하는 값을 선택하여 가져오고 싶을 때 사용하는 명령어 입니다.

명령어를 살펴보도록 하겠습니다.

-- 기본 선택 명령어(table_name 테이블에서 모두 가져오기)
SELECT * FROM [table_name];

-- WHERE문 포함 선택 명령어(table_name 테이블에서 name이 '깜이군'인 값 가져오기
SELECT * FROM [table_name] WHERE name='깜이군'; 

SELECT * FROM [table_name] WHERE name LIKE '깜이군';

-- 애매한 것도 모두 선택하는 명령어(table_name 테이블에서 name에 '깜이'가 들어가는 것 모두 가져오기)
SELECT * FROM [table_name] WHERE name LIKE '%깜이%';

-- 선택 후 name 컬럼으로 오름차순 정렬하는 명령어(ASC 없어도 가능)
SELECT * FROM [table_name] ORDER BY name;
SELECT * FROM [table_name] ORDER BY name ASC;

-- 선택 후 name 컬럼으로 내림차순 정렬하는 명령어
SELECT * FROM [table_name] ORDER BY name DESC;

-- 선택 후 여러 컬럼으로 정렬하는 명령어(우선순위 name > email)
SELECT * FROM [table_name[ ORDER BY name, email;

위와 같이 다양한 방법으로 SELECT문을 사용할 수 있습니다.

위의 방법 이외에도 다양한 조건문을 활용하거나 MySQL 내의 함수를 활용하여 사용할 수도 있습니다.

INSERT 명령어는 생성되어 있는 테이블에 원하는 값을 삽입하는 기능을 가진 명령어 입니다.

명령어를 살펴보도록 하겠습니다.

-- Table Example
-- CREATE TABLE testTable( idx INT PRIMARY KEY AUTO_INCREMENT,
--	                       name VARCHAR(255) NOT NULL,
--                         email VARCHAR(255) NOT NULL
--                       )CHARSET=utf8;

-- 테이블에 값을 삽입함
INSERT INTO testTable( name, email ) VALUES ('kkamikoon', 'kkamikoon@test.com');

-- idx는 AUTO_INCREMENT라 딱히 설정해주지 않아도 됨

위와 같이 테이블이 있다고 가정하고, 값을 삽입해보았습니다.

설명을 작성해두었지만, idx의 경우 AUTO_INCREMENT이기 때문에 테이블 필드 이름과 데이터 값을 따로 지정해주지 않아도 됩니다.

이는 1씩 자동적으로 증가한다는 의미입니다.

UPDATE 명령어는 생성되어 있는 테이블에 삽입되어 있는 값을 수정해주는 기능을 가진 명령어입니다.

명령어를 살펴보도록 하겠습니다.

-- Table Example
-- CREATE TABLE testTable( idx INT PRIMARY KEY AUTO_INCREMENT,
--	                       name VARCHAR(255) NOT NULL,
--                         email VARCHAR(255) NOT NULL
--                       )CHARSET=utf8;

-- idx가 1인 컬럼의 name을 변경함
UPDATE testTable SET name='새로운깜이군' WHERE idx=1;

-- 전체 name을 변경함
UPDATE testTable SET name='새로운깜이군';

업데이트는 삽입되어 있는 값을 수정해주는 만큼 조건문이 필요합니다.

수정하고자 하는 값을 특정해주고 변경을 해줘야 원하는 결과를 얻을 수 있습니다.

만약 WHERE문이 없이 UPDATE를 수행한다면 모든 값이 업데이트한 값으로 바뀝니다.

DELETE 명령어는 생성되어 있는 테이블에 삽입되어 있는 값을 삭제해주는 기능을 가진 명령어입니다.

명령어를 살펴보도록 하겠습니다.

-- Table Example
-- CREATE TABLE testTable( idx INT PRIMARY KEY AUTO_INCREMENT,
--	                       name VARCHAR(255) NOT NULL,
--                         email VARCHAR(255) NOT NULL
--                       )CHARSET=utf8;

-- idx가 1인 값을 삭제함
DELETE FROM testTable WHERE idx=1;

-- testTable의 전체 값을 삭제함
DELETE FROM testTable;

DELETE 명령어도 UPDATE와 마찬가지로 값을 특정해주지 않으면 전체 값이 DELETE되는 특징이 있습니다.

원하는 곳을 적절히 삭제하기 위해서는 WHERE문으로 조건을 달아주어야 합니다.

MySQL 명령어는 웹 프로그래밍 하면서 나름 익혔지만, 생각보다 익혀지지 않아서 가끔 헷갈릴 때가 있습니다.

이번 포스트에서는 MySQL 기본 명령어에 대해 알아보도록 합니다.

명령어는 DDL 명령어인 Data Define Language 명령어를 다뤄보려고 합니다.

DDL 명령어란 데이터 정의어로써 테이블과 같은 데이터 구조를 정의하는데 사용됩니다. 또한 데이터 구조를 정의함에 있어서 발생할 수 있는 이슈로 생성, 변경, 삭제, 이름 변경의 명령어도 함께 포함하고 있습니다.

따라서 아래에서 다룰 명령어는 CREATE, DROP, ALTER, RENAME, TRUNCATE의 명령어를 다뤄보려고 합니다.

CREATE 명령어는 테이블을 생성하거나 데이터베이스를 생성하는 명령어입니다.

먼저 각각 데이터베이스 생성, 테이블 생성으로 구분하여 명령어를 살펴보도록 하겠습니다.

-- 데이터베이스 생성 명령어
CREATE DATABASE [dbname];

-- 데이터베이스 생성 및 속성 설정 명령어
CREATE DATABASE [dbname] CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

위의 명령어를 보면 되게 되게 간단합니다.

단, 데이터베이스를 생성할 때 속성을 미리 설정할 수 있으므로, 함께 설정하며 생성할 수 있습니다.

-- 테이블 생성 명령어
CREATE TABLE [tablename] (
	[column_name1] INT PRIMARY KEY AUTO_INCREMENT,
	[column_name2] VARCHAR(255) NOT NULL,
	[column_name3] DATETIME NOT NULL,
)CHARSET=utf8;

테이블 생성 명령도 되게 간단합니디.

위의 명령어에서는 나름 예제로써 NOT NULL, AUTO_INCREMENT 등의 값을 써주었습니다.

DROP 명령어는 테이블을 삭제하거나 데이터베이스를 삭제하는 명령어입니다.

먼저 각각 데이터베이스 삭제, 테이블 삭제로 구분하여 명령어를 살펴보도록 하겠습니다.

-- 데이터베이스 삭제 명령어
DROP DATABASE [dbname];

CREATE 명령어보다 간단합니다. 삭제하고자 하는 데이터베이스 명을 작성하기만 하면 삭제가 가능합니다.

단 여기서 DROP 위데 DATABASE 라는 단어를 붙여줘야 합니다. 이는 테이블이냐 데이터베이스냐를 구분하기 위해서입니다.

-- 테이블 삭제 명령어
DROP TABLE [table_name];

데이터베이스 삭제 명령과 비슷하고, DATABASE 대신 TABLE을 작성하면 됩니다.

크게 어려울 것이 없는 명령어입니다.

ALTER 명령어는 테이블의 내용을 수정할 수 있도록 하는 명령어입니다.

수정하는 명령어인 만큼 사용 방법이 꽤 다양합니다.

-- 테이블에 컬럼 추가하기
ALTER TABLE [table_name] ADD COLUMN [column_name] [column_type];

-- 테이블의 컬럼 타입 변경하기
ALTER TABLE [table_name] MODIFY COLUMN [column_name] [column_type];

-- 테이블의 컬림 이름 변경하기
ALTER TABLE [table_name] CHANGE COLUMN [old_column_name] [new_column_name] [new_column_type];

-- 테이블의 컬럼 삭제하기
ALTER TABLE [table_name] DROP COLUMN [column_name];

-- 테이블에 컬럼 인덱스 주기
ALTER TABLE [table_name] DROP INDEX [index_name];

-- 테이블에 PRIMARY KEY 만들기
ALTER TABLE [table_name] ADD PRIMARY KEY( column_name_on_this_table );

-- 테이블에 PRIMARY KEY 삭제하기
ALTER TABLE [table_name] DROP PRIMARY KEY;

-- 테이블 명 바꾸기
ALTER TABLE [old_table_name] RENAME [new_table_name]; 

위와 같이 ALTER 명령어는 매우 다양한 명령을 지원하고 있습니다.

위의 명령어 사용법을 이용하여 적절히 응용하면 보다 유연한 MySQL 사용이 가능합니다.

RENAME 명령어는 테이블의 이름을 변경하는 명령어 입니다.

데이터베이스의 이름을 변경하고자 하면, 별도의 작업을 해주셔야 합니다.(RENAME을 통해 데이터베이스 이름을 변경할 수 없습니다.)

-- 테이블 이름 변경 명령어
RENAME TABLE [old_table_name] TO [new_table_name];

-- 여러 테이블 이름 변경 명령어
RENAME TABLE [old_table_name1] TO [new_table_name1], [old_table_name2] TO [new_table_name2];

위와 같이 여러 개의 테이블 이름을 한 번에 변경하거나, 하나의 테이블 이름을 변경해줄 수 있습니다.

이 명령어는 DML의 명령어인 DELETE와 DDL 명령어인 DROP과 비슷한 형태로, 데이터를 삭제하는 명령어입니다.

그러나 이 명령어는 위의 두 명령어와는 차이가 있습니다.

DML의 DELETE 명령어의 경우 WHERE문과 같은 조건문을 붙여서 원하는 곳만 삭제할 수 있습니다.

DDL의 DROP 명령어의 경우 테이블 혹은 데이터베이스를 모두 삭제하는 명령어입니다.

TRUNCATE는 DROP 후 CREATE를 하는 특징을 가지고 있습니다. 

이러한 특징을 가진 명령어로써, 차이점은 다음과 같습니다.

데이터를 삭제하고 삭제한 공간을 재사용하기 위한 명령어입니다.

데이터가 존재하던 공간까지 모두 제거합니다.

AUTO_INCREMENT로 지정하여 값이 증가하던 컬럼이 완전 초기화 됩니다.(MySQL 5.0.13 버전 이후)

[Table_name].frm 파일이 아직 유효할 경우, 데이터나 인덱스가 손상된 경우라도 빈 테이블로 재생성이 가능합니다.

-- 테이블의 데이터 삭제 명령어
TRUNCATE TABLE [table_name];