[MySQL ] 06. DML, DDL, DCL , 정규식

DML, DDL, DCL , 정규식

 

# DML: 데이터 조작 (SELECT, INSERT, UPDATE, DELETE)

DML(Data Manipulation Language)은 데이터베이스 테이블의 데이터를 조작하는 데 사용되는 SQL 명령어입니다. 여기서는 DML에 속하는 명령어들의 문법과 예제를 각각 설명하겠습니다.

 

 

• SELECT: 데이터를 조회하여 결과를 반환 (예: 조회된 직원 목록)
• INSERT: 새로운 데이터를 테이블에 삽입 (예: 새로운 직원 추가)
• UPDATE: 기존 데이터를 수정 (예: 특정 직원의 부서 업데이트)
• DELETE: 데이터를 삭제 (예: 특정 직원 데이터 삭제)

 

 

1. SELECT: 데이터 조회

SELECT는 테이블에서 데이터를 조회하는 명령어입니다. 데이터를 선택해서 보여주며, 다양한 조건과 정렬 등을 사용해 원하는 데이터를 추출할 수 있습니다.

 

SELECT [컬럼1, 컬럼2, ...] 
FROM 테이블명 
WHERE 조건식;

 

모든컬럼 조회 

SELECT * FROM employees;

 

특정컬럼 조회

SELECT name, age FROM employees;

 

조건에 만족하는 데이터 조회 

SELECT name, age FROM employees WHERE age > 30;

 

정렬 조회

SELECT name, age FROM employees ORDER BY age DESC;

 

 

2. INSERT: 데이터 삽입

INSERT는 테이블에 새로운 데이터를 추가할 때 사용됩니다.

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...) 
VALUES (값1, 값2, ...);

 

INSERT INTO employees (name, age, department) VALUES ('John', 28, 'Marketing');

 

INSERT INTO employees (name, department) VALUES ('Alice', 'HR');

 

 

3. UPDATE: 데이터 수정

UPDATE는 테이블의 기존 데이터를 수정하는 명령어입니다. 특정 조건을 만족하는 행을 업데이트할 수 있습니다.

UPDATE 테이블명 SET 컬럼1 = 값1, 컬럼2 = 값2, ... WHERE 조건식;

 

특정 데이터 수정 

UPDATE employees SET age = 29 WHERE name = 'John';

 

여러 컬럼값 동시 수정 

UPDATE employees SET age = 30, department = 'Sales' WHERE name = 'Alice';

 

조건 없이 모든 데이터 수정 

UPDATE employees SET department = 'HR';

 

 

4. DELETE: 데이터 삭제

DELETE는 테이블에서 특정 조건을 만족하는 데이터를 삭제하는 명령어입니다.

DELETE FROM 테이블명 WHERE 조건식;

 

특정데이터 삭제

DELETE FROM employees WHERE name = 'John';

 

모두 삭제

DELETE FROM employees;

WHERE 절이 없으면 테이블의 모든 데이터가 삭제됩니다.

 

 

 

---

 

 

 

 

# DDL: 데이터 정의 언어 (CREATE, ALTER, DROP, TRUNCATE)

DDL(Data Definition Language)은 데이터베이스 구조(스키마)를 정의하거나 수정하는 명령어를 말합니다. 테이블, 인덱스, 뷰, 스키마 등의 데이터베이스 객체를 생성, 수정, 삭제하는 데 사용됩니다. DDL 명령어를 실행하면 데이터베이스의 구조가 변경됩니다.

 

 

 

• CREATE: 새로운 데이터베이스 객체 생성 (예: 테이블, 인덱스, 데이터베이스)
• ALTER: 기존 객체를 수정 (예: 컬럼 추가, 데이터형 변경, 테이블 이름 변경)
• DROP: 객체 삭제 (예: 테이블, 데이터베이스 삭제)
• TRUNCATE: 테이블의 모든 데이터를 삭제하되 테이블 구조는 유지

 

 

1. CREATE: 데이터베이스 객체 생성

CREATE 명령어는 테이블, 데이터베이스, 인덱스, 뷰 등 새로운 데이터베이스 객체를 생성합니다.

CREATE [OBJECT] 객체명 (컬럼명1 데이터형, 컬럼명2 데이터형, ...);

 

데이터베이스 생성

CREATE DATABASE my_database;

 

테이블 생성

CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  department VARCHAR(100)
);

 

인덱스 생성

CREATE INDEX idx_name ON employees (name);

 

 

 

 

2. ALTER: 데이터베이스 객체 수정

ALTER 명령어는 기존 데이터베이스 객체의 구조를 수정할 때 사용됩니다. 주로 테이블의 컬럼을 추가, 삭제, 변경하거나 테이블 이름을 변경하는 데 사용됩니다.

 

ALTER [OBJECT] 객체명 변경사항;

 

 

컬럼 이름 변경

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

 

컬럼 데이터형 수정 

ALTER TABLE employees MODIFY COLUMN employee_age VARCHAR(3);

 

테이블 이름변경 

ALTER TABLE employees RENAME TO staff;

 

 

3. DROP: 데이터베이스 객체 삭제

DROP 명령어는 데이터베이스 객체를 완전히 삭제합니다. 삭제된 객체는 복구할 수 없으므로 주의해야 합니다.

 

DROP [OBJECT] 객체명;

 

데이터베이스 삭제

DROP DATABASE my_database;

 

테이블 삭제

DROP TABLE employees;

 

인덱스 삭제 

DROP INDEX idx_name ON employees;

 

 

4. TRUNCATE: 테이블 데이터 초기화

TRUNCATE 명령어는 테이블 내의 모든 데이터를 삭제하지만, 테이블의 구조는 유지됩니다. DELETE와 달리, 트랜잭션 로그에 기록되지 않고 속도가 더 빠릅니다.

 

TRUNCATE TABLE 테이블명;

 

테이블의 모든 데이터 삭제 

TRUNCATE TABLE employees;

 

 

 

---

 

 

 

# DCL: 데이터 제어 언어 (GRANT, REVOKE)

DCL(Data Control Language)은 데이터베이스 시스템에서 사용자의 권한을 관리하고, 데이터 접근 권한을 제어하는 명령어입니다. DCL 명령어는 주로 사용자에게 권한을 부여하거나 취소하는 데 사용됩니다. DCL 명령어의 두 가지 주요 명령은 GRANT와 REVOKE입니다.

 

 

• GRANT: 사용자에게 특정 데이터베이스 또는 테이블에 대한 권한을 부여합니다.
• REVOKE: 사용자에게 부여된 권한을 취소합니다.

 

 

1. GRANT: 권한 부여

GRANT 명령어는 특정 사용자에게 데이터베이스의 특정 작업을 수행할 수 있는 권한을 부여합니다.

GRANT 권한 ON 데이터베이스.테이블 TO '사용자'@'호스트';

 

 

특정 테이블에 대해 SELECT 권한 부여

GRANT SELECT ON my_database.employees TO 'john'@'localhost';

 

모든 테이블에 대해 SELECT와 INSERT 권한 부여

GRANT SELECT, INSERT ON my_database.* TO 'alice'@'localhost';

 

특정 사용자가 모든 테이블에서 모든 권한을 사용할 수 있도록 부여

GRANT ALL PRIVILEGES ON my_database.* TO 'admin'@'localhost';

 

 

• SELECT: 데이터를 조회할 수 있는 권한
• INSERT: 데이터를 삽입할 수 있는 권한
• UPDATE: 데이터를 수정할 수 있는 권한
• DELETE: 데이터를 삭제할 수 있는 권한
• ALL PRIVILEGES: 모든 권한 부여
• EXECUTE: 저장된 프로시저나 함수를 실행할 수 있는 권한

 

2. REVOKE: 권한 회수

REVOKE 명령어는 사용자에게 부여된 권한을 취소하거나 회수합니다. 이미 부여된 특정 권한을 회수할 수 있습니다.

 

REVOKE 권한 ON 데이터베이스.테이블 FROM '사용자'@'호스트';

 

 

특정 테이블에 대한 SELECT 권한 회수

REVOKE INSERT ON my_database.* FROM 'alice'@'localhost';

 

사용자의 INSERT 권한을 모든 테이블에서 회수

REVOKE INSERT ON my_database.* FROM 'alice'@'localhost';

 

모든 권한 회수

REVOKE ALL PRIVILEGES ON my_database.* FROM 'admin'@'localhost';

 

 

# 정규식 (REGEXP) 

 

정규식(Regular Expression)은 데이터베이스의 텍스트 데이터를 패턴 매칭하여 검색할 때 사용됩니다. MySQL에서는 REGEXP(또는 RLIKE) 연산자를 사용해 텍스트를 검색할 수 있습니다. 이를 통해 더 복잡하고 유연한 패턴 기반 검색을 수행할 수 있습니다.

 

 

• REGEXP: 정규식을 사용한 패턴 매칭 검색
• NOT REGEXP: 패턴과 일치하지 않는 데이터를 검색

 

패턴	설명
.	임의의 한 문자 (줄바꿈 제외)
^	문자열의 시작 (문장의 첫 부분)
$	문자열의 끝
*	0개 이상의 반복
+	1개 이상의 반복
?	0개 또는 1개의 반복
[abc]	a, b, c 중 하나의 문자와 일치
[a-z]	소문자 a부터 z까지의 문자 중 하나와 일치
[A-Z]	대문자 A부터 Z까지의 문자 중 하나와 일치
[0-9]	숫자 0부터 9까지의 숫자 중 하나와 일치
[^abc]	a, b, c를 제외한 문자와 일치
`	`
`(abc	xyz)`
\	메타 문자를 이스케이프 (문자 그대로 검색)

 

1. 기본 패턴 매칭

REGEXP 연산자를 사용하여 특정 패턴과 일치하는 데이터를 검색합니다.

 

SELECT * FROM 테이블명 WHERE 컬럼명 REGEXP '패턴';

 

 

름이 **'a'**로 시작하는 데이터를 검색

SELECT * FROM users WHERE name REGEXP '^a';

 

이름이 **'n'**으로 끝나는 데이터를 검색

SELECT * FROM users WHERE name REGEXP 'n$';

 

a 나 e 를 포함하는 데이터를 검색

SELECT * FROM users WHERE name REGEXP '[ae]';

 

 

2. 여러 패턴 매칭

MySQL에서는 |(OR) 연산자를 사용하여 여러 패턴 중 하나와 일치하는 데이터를 검색할 수 있습니다.

 

이름이 'John' 또는 **'Jane'**인 데이터를 검색

SELECT * FROM users WHERE name REGEXP 'John|Jane';

 

3. 반복 패턴 매칭

*, +, ? 등을 사용하여 특정 문자가 반복되는 경우를 매칭할 수 있습니다.

 

이름에 한 번 이상 'a' 가 포함된 데이터를 검색

SELECT * FROM users WHERE name REGEXP 'a+';

 

이름에 'ab' 패턴이 0번 또는 1번 포함된 데이터를 검색

SELECT * FROM users WHERE name REGEXP 'ab?';

 

4. 숫자 패턴 매칭

MySQL에서는 숫자와 관련된 정규식 패턴도 사용할 수 있습니다.

 

전화번호가 숫자 3으로 시작하는 데이터를 검색

SELECT * FROM contacts WHERE phone REGEXP '^3';

 

전화번호에 숫자가 3개 연속으로 포함된 데이터를 검색

SELECT * FROM contacts WHERE phone REGEXP '[0-9]{3}';

 

 

5. NOT REGEXP

NOT REGEXP는 특정 패턴과 일치하지 않는 데이터를 검색할 때 사용됩니다.

 

이름이 **'a'**로 시작하지 않는 데이터를 검색

SELECT * FROM users WHERE name NOT REGEXP '^a';

 

 

대소문자 구분: MySQL 정규식은 기본적으로 대소문자를 구분합니다. 대소문자를 구분하지 않게 하려면, LOWER() 함수를 사용하여 소문자로 변환한 후 검색할 수 있습니다.

 

SELECT * FROM users WHERE LOWER(name) REGEXP 'john';

 

1. 특정 문자 포함: SELECT * FROM users WHERE name REGEXP '[aeiou]';
2. 숫자 포함 여부 확인: SELECT * FROM orders WHERE order_number REGEXP '[0-9]';
3. 문자열이 특정 문자로 끝남: SELECT * FROM products WHERE name REGEXP 'n$';
4. 여러 패턴 중 하나와 일치: SELECT * FROM books WHERE title REGEXP 'story|tale';
5. 패턴 일치하지 않는 데이터: SELECT * FROM users WHERE email NOT REGEXP '@gmail.com';