MySQL 사용방법에 대해서

📍 DataBase 객체에 대해서 

MySQL 데이터베이스는 여러 종류의 객체를 포함하고 있습니다. 이러한 객체들은 데이터를 저장, 관리하고 데이터베이스 시스템을 효과적으로 활용할 수 있도록 해줍니다. 주요 데이터베이스 객체에 대한 간단한 설명은 다음과 같습니다

 

1. 테이블 (Table):

설명: 데이터를 행과 열의 형태로 저장하는 가장 기본적인 객체입니다. 테이블은 2차원 형태로 필드(열)와 레코드(행)로 구성되며, 각 필드는 특정한 데이터 유형과 제약 조건을 가질 수 있습니다.

예시 SQL:

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

 

2. 인덱스 (Index):

설명: 테이블의 검색 성능을 향상하기 위해 사용되는 객체로, 특정 열(또는 열의 조합)에 대한 정렬된 데이터 구조입니다. 인덱스는 데이터를 빠르게 찾을 수 있도록 도와줍니다.

예시 SQL:

CREATE INDEX idx_username ON users(username);

 

3. 뷰 (View):

설명: 하나 이상의 테이블에서 선택적인 열 및 행의 부분 집합을 나타내는 가상의 테이블입니다. 뷰를 사용하여 복잡한 쿼리를 간소화하거나 특정 사용자에게만 필요한 데이터에 접근할 수 있습니다.

예시 SQL:

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

 

4. 저장 프로시저 (Stored Procedure):

설명: 미리 정의된 SQL 코드 블록으로, 일련의 쿼리 및 로직을 포함하고 데이터베이스에서 실행될 수 있습니다. 코드의 재사용성과 보안을 강화하는 데 사용됩니다.

예시 SQL:

DELIMITER //
CREATE PROCEDURE GetUserInfo(IN user_id INT)
BEGIN
  SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

 

5. 트리거 (Trigger):

설명: 특정 이벤트(INSERT, UPDATE, DELETE 등)가 발생할 때 자동으로 실행되는 작업입니다. 주로 데이터 일관성 유지나 로깅과 관련된 작업에 사용됩니다.

예시 SQL:

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

 

6. 인코딩 (Collation):

설명: 문자열 데이터의 정렬 순서와 비교 동작을 정의하는 규칙입니다. 데이터베이스 및 테이블 수준에서 설정할 수 있으며, 서로 다른 언어 및 정렬 순서에 대한 지원을 제공합니다.

예시 SQL:

CREATE TABLE multilingual_data (
  id INT,
  name VARCHAR(50) COLLATE utf8_general_ci
);

 

이러한 객체들을 조합하여 MySQL 데이터베이스는 다양한 요구사항에 대응할 수 있는 유연하고 강력한 데이터 관리 시스템을 제공합니다.

 

7. 이름 규칙 

• 식별자의 길이 제한
  대부분의 시스템에서는 객체의 이름에 길이 제한이 있습니다. 예를 들어, MySQL에서는 객체 이름의 최대 길이가 64자로 제한됩니다.
• 영문자와 숫자 사용
  대부분의 데이터베이스 시스템 및 프로그래밍 언어에서는 영문자와 숫자를 혼합하여 사용할 수 있습니다. 다만, 객체 이름은 숫자로 시작하지 않아야 합니다. 한글을 사용할 경우 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다. 
• 특수문자 및 공백 제한
  일반적으로 객체 이름에는 특수 문자나 공백이 포함되지 않아야 합니다. 대신 밑줄(_)이나 언더스코어를 사용하는 경우가 많습니다.
• 대소문자 구분
  몇몇 데이터베이스 시스템은 객체 이름을 대소문자 구분하여 다르게 취급합니다. 따라서 "Table1"과 "table1"은 서로 다른 객체로 간주됩니다.
• 예약어 회피
  객체 이름으로 예약어를 피해야 합니다. 예약어는 데이터베이스나 프로그래밍 언어에서 특별한 의미를 가지는 단어로, 일반적으로 식별자로 사용되어서는 안됩니다.
• 의미 있는 이름 사용
  객체의 이름은 해당 객체의 역할이나 내용을 명확하게 나타낼 수 있도록 지정하는 것이 좋습니다. 가독성을 높이기 위해 의미 있는 이름을 사용하는 것이 좋습니다.

📍 Schema란 ?

데이터베이스에서 "스키마(Schema)"는 데이터베이스에서 데이터의 구조를 정의하고 조직화하는 방법을 나타냅니다. 스키마는 테이블, 필드, 관계 등과 같은 데이터베이스 객체들의 구조 및 정의를 포함합니다.

1. 테이블 정의:

각 테이블의 이름과 해당 테이블이 가지는 열(필드)들의 이름과 데이터 유형을 정의합니다.

2. 테이블 간의 관계:

테이블 간의 관계를 정의하고, 이러한 관계가 어떻게 유지되는지를 명시합니다. 예를 들어, 외래 키(Foreign Key)를 사용하여 두 테이블을 연결할 수 있습니다.

3. 데이터 제약 조건:

각 테이블의 열에 대한 제약 조건을 정의합니다. 예를 들어, 특정 열에 대한 고유(unique) 제약 조건이나 NULL 값 허용 여부 등이 있습니다.

4. 인덱스 및 기타 객체:

데이터 검색 및 조작을 최적화하기 위해 인덱스와 같은 추가적인 객체들을 정의할 수 있습니다.

 

스키마는 데이터베이스의 구조를 정의하고 데이터베이스에 저장되는 정보의 레이아웃과 형식을 결정합니다. 스키마의 정의는 데이터베이스를 사용하는 애플리케이션 및 사용자가 데이터에 접근하고 이해하는 데 중요한 역할을 합니다. 데이터베이스 시스템에 따라 스키마는 물리적인 스키마와 논리적인 스키마로 나뉠 수 있습니다. 물리적인 스키마는 데이터의 실제 저장 방식에 관련되어 있고, 논리적인 스키마는 데이터의 논리적인 구조와 관계에 관련되어 있습니다. 

스키마는 데이터베이스 내의 객체(table, index, view)를 담는 컨테이너라고 정의할 수 있습니다. 테이블과 스키마는 무엇인가를 담는 그릇 역할을 한다는 점에서 비슷하며, 테이블 안에 열을 정의할 수 있고 스키마 안에 테이블을 정의할 수 있습니다. 

 

# 터미널에서 mysql -u root -p 명령어를 입력해 접속한 뒤
SHOW DATABASES; 

# 위의 명령어를 입력하면 MySQL 소프트웨어를 설치하면 기본적으로 생성되는 데이터베이스가 존재하는 것을 확인할 수 있습니다. 
# 아래 명령어를 입력해 mysql을 사용하겠다고 mysql 서버에 알려준 후
USE mysql;

# 아래 명령어를 통해 스키마 내에 테이블 객체 목록을 출력해볼 수 있습니다. 
SHOW TABLES;

# mysql에서는 CREATE DATABASE; 라는 DDL이라는 SQL 명령으로 작성한 데이터베이스가 스키마가 됩니다.

 

1. MySQL 기초 사용 방법 

1️⃣ SQL 테이블 생성 & 수정 & 삭제 

 

📌 DDL( Data Definition Language)이란? 

DDL( Data Definition Language )은 "데이터 정의 언어"의 약어로, 데이터베이스의 구조를 정의하고 관리하기 위한 SQL 명령어 집합을 나타냅니다. DDL 명령어는 데이터베이스, 테이블, 인덱스 등과 같은 데이터베이스 객체의 생성, 수정, 삭제를 다룹니다. 주로 데이터베이스 관리자나 데이터베이스 설계자가 사용하며, 다음과 같은 주요 DDL 명령어들이 있습니다.  

 

SQL 테이블 생성하기 전에 스키마를 먼저 생성해야 합니다. 

1) mysql -u root -p로 mysql 서버에 접속해 Database를 생성하는 명령어를 입력합니다. 

CREATE DATABASE NAME # NAME에는 직접 사용할 데이터베이스 이름을 설정해 변경해서 입력해주세요.
SHOW DATABASES; # 명령어 입력 후 잘 생성 되었는지 해당 명령어를 통해 확인해주세요.

 

2) 아래 명령어와 같이 만든 데이터베이스 스키마를 사용하겠다고 MySQL 서버에 알려줍니다. 

USE NAME; # NAME에는 생성하신 데이터베이스 이름으로 변경해 입력해주세요.

SHOW TABLES; # 해당 명령어로 스키마 내에 테이블 객체 목록을 출력해 볼 수 있습니다.

 

이제는 Table을 생성하는 방법을 살펴보도록 하겠습니다. 

3) 아래 명령어를 사용해 Table_name을 원하는 이름으로 입력해 주시고 열(column)에는 칼럼명과 자료형 그리고 제약조건을 지정해 줍니다. 

CREATE TABLE table_name (column1 datatype, column2 datatype);

# 예시 
CREATE TABLE users 
(
  id INT NOT NULL AUTO_INCREMENT,                        -- id 칼럼을 정수, NULL을 허락하지 않으며 값을 자동증가하게 설정합니다.
  name VARCHAR(50) NOT NULL,                             -- name 칼럼을 50 bytes의 크기를 가지는 문자열, NULL을 허락하지 않습니다.
  email VARCHAR(200) NOT NULL,
  profile_image VARCHAR(1000) NULL,    
  password VARCHAR(200) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),           -- 데이터의 이력관리를 위해서 생성 시간을 기록합니다.
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP, -- 데이터의 이력관리를 위해서 변경 시간을 기록합니다.
  PRIMARY KEY (id)                                       -- 이 테이블의 Primary Key를 id 로 설정합니다.
);                                                       -- 쿼리문은 ; 로 구분되기 때문에 엔터를 쳐서 쿼리문을 멀티라인으로 작성할 수 있습니다.

 

4) 테이블 생성한 후 내가 원하는 형태로 생성되었는지 아래 명령어를 통해 확인합니다. 

DESC users; # DESC는 Description을 축약해 놓은 명령어로 뒤에 테이블 이름을 붙이면 해당 테이블의 정보를 보여줍니다.

 

5) 외부키(FOREIGN KEY) 제약조건 설정 

테이블 간의 참조관계를 생각하여 만약 users 테이블의 Id를 posts 테이블이 user_id의 이름으로 참조할 때 설정합니다. 

이때, users 테이블(부모 테이블)에서 참조될 열은 반드시 유일성을 가져야 하므로 보통 PRIMARY KEY를 지정합니다. 

CREATE TABLE posts
(
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  content VARCHAR(2000) NULL,
  user_id INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES users (id) -- 외래키를 걸어줄 칼럼과 참조할 테이블과 칼럼을 순서대로 적어줍니다.
);

 

이제 만들어진 테이블을 변경하는 방법에 대해 알아보도록 하겠습니다. 

6) 열(column) 추가의 경우

ALTER TABLE table_name ADD column_name datatype;

# 예시 
ALTER TABLE users ADD age INT NOT NULL;

# 변경 후 확인 
DESC users;

 

7) 열(column) 삭제의 경우

ALTER TABLE table_name DROP column_name datatype;

# 예시
ALTER TABLE users DROP profile_image;

# 변경 후 확인
DESC users;

 

8) 열(column) 수정의 경우

ALTER TABLE table_name MODIFY column_name datatype;

# 예시 
ALTER TABLE posts MODIFY content TEXT NULL;

# 변경 후 확인
DESC posts;

 

9) Table 삭제의 경우 

DROP TABLE table_name;

위의 명령어의 경우 테이블 객체 안에 모든 데이터도 함께 삭제되므로 신중하게 실행해야 하는 명령임을 기억해야 합니다. 

 

10) Table의 모든 행을 삭제하지만 테이블 구조는 유지하는 경우

TRUNCATE TABLE table_name;

 

11) 데이터베이스 객체의 이름을 변경하는 경우

RENAME TABLE old_name TO new_name;

 

 

2️⃣ SQL 데이터 추가 & 수정 & 삭제

📌 DML(Data Manipulation Language) 이란?

DML(Data Manipulation Language)은 "데이터 조작 언어"의 약어로, 데이터베이스에서 데이터를 조회하거나 조작하는 데 사용되는 SQL 명령어를 의미합니다. DML은 주로 SELECT, INSERT, UPDATE, DELETE와 같은 명령어들로 이루어져 있습니다. 아래에서 DML 명령어들을 간단히 설명합니다.( 모든 RDBMS가 DML을 이용해 데이터 작업을 수행하지만, RDBMS 종류와 버전에 따라 DML 문법이 달라질 수 있어, 공식 문서에서 제공하는 메뉴얼의 SQL 문법을 참조하는 것이 가장 좋습니다.)

 

1) 데이터베이스에서 데이터를 조회하는 데 사용됩니다. 특정 테이블의 열을 선택하거나, 조건을 지정하여 원하는 데이터를 검색합니다.

SELECT column1, column2 FROM table WHERE condition;

 

2) 새로운 데이터를 테이블에 삽입하는 데 사용됩니다. 명시한 열에 대응하는 값을 지정하여 새로운 행을 추가합니다.

INSERT INTO table (column1, column2) VALUES (value1, value2);

# 예시 
INSERT INTO posts (title, content, user_id) VALUES ("책의 이름", "내용", 1);

# 입력 후 확인
SELECT * FROM posts;

 

3) 테이블의 기존 데이터를 수정하는 데 사용됩니다. 명시한 열의 값을 새로운 값으로 업데이트합니다.

UPDATE table SET column1 = value1 WHERE condition;

# 예시 
UPDATE posts SET WHERE user_id=1 AND id=1;

# 변경 후 확인
SELECT * FROM posts;

 

4) 테이블에서 데이터를 삭제하는 데 사용됩니다. 특정 조건을 만족하는 행을 삭제합니다.

DELETE FROM table WHERE condition;

# 예시 
DELETE FROM posts WHERE user_id = 1;

# 변경 후 확인 
SELECT * FROM posts

 

 

3️⃣ SQL 데이터 검색 & 조회

📌 DQL(Data Query Language) 이란? 

DQL(Data Query Language)은 "데이터 조회 언어"의 약어로, 데이터베이스에서 데이터를 조회하는 데 사용되는 SQL 명령어입니다. DQL은 주로 SELECT 문을 통해 데이터를 검색하고 필요한 정보를 가져오는 데 사용됩니다.

 

1) 데이터베이스에서 데이터를 조회하는 데 사용되며, 특정 테이블의 열을 선택하거나 연산을 수행하여 원하는 결과를 얻을 수 있습니다. 복수의 조건 표현식을 사용할 때는 AND, OR, NOT을 사용할 수 있습니다. 

SELECT column1, column2, ...
FROM table
WHERE condition;

# 예시 
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 1;

# 부서 ID가 1이고 이름이 yuna인 사람만 검색합니다.
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 1 AND first_name = "yuna"

# 부서 ID가 1 또는 2인 직원을 검색합니다.
SELECT * FROM employees
WHERE department_id = 1 OR department_id = 2;

# 부서 ID가 1, 2, 또는 3인 직원을 검색합니다.
SELECT * FROM employees
WHERE department_id IN (1, 2, 3);

# 부서 ID가 1이면서 연봉이 50000보다 크거나, 부서 ID가 2이면서 연봉이 60000보다 큰 직원을 검색합니다.
SELECT * FROM employees
WHERE (department_id = 1 AND salary > 50000) OR (department_id = 2 AND salary > 60000);

# 부서 ID가 1이 아닌 직원을 검색합니다.
SELECT * FROM employees
WHERE NOT department_id = 1;