[SQL] DDL, DML

📌 개인적인 공간으로 공부를 기록하고 복습하기 위해 사용하는 블로그입니다.
정확하지 않은 정보가 있을 수 있으니 참고바랍니다 :😸
[틀린 내용은 댓글로 남겨주시면 복받으실거에요]

DBMS(Database management system)

: 데이터베이스를 관리하기 위한 소프트웨어 시스템

1. 데이터 정의: 데이터베이스 스키마를 생성, 수정, 삭제할 수 있습니다.
2. 데이터 조작: 데이터를 삽입, 수정, 삭제 및 검색할 수 있습니다.
3. 데이터 보안: 데이터 접근 권한을 관리하여 민감한 정보를 보호합니다.
4. 데이터 무결성: 데이터베이스 내의 데이터가 정확하고 일관성이 유지되도록 합니다.
5. 트랜잭션 관리: 여러 작업이 동시에 수행될 때 데이터의 일관성을 유지하고, 시스템 장애가 발생했을 때 데이터를 복구할 수 있습니다.

✔️Database에 사용하는 주요 데이터타입의 정보

데이터 타입	설명	예시
CHAR(n)	고정 길이 문자열 (n: 1~255 generally)	CHAR(10)
VARCHAR(n)	가변 길이 문자열 (n: 1~여러 시스템에서 다양)	VARCHAR(50)
DECIMAL(p, s)	정밀한 고정 소수점 숫자 (p: 정밀도, s: 소수점 자릿수)	DECIMAL(10, 2)
DATE	날짜 (연, 월, 일)	DATE
TIMESTAMP	날짜와 시간 (분 단위 또는 나노초 정밀도까지)	TIMESTAMP
BINARY LARGE OBJECT (BLOB)	큰 이진 데이터 (크기 제한은 시스템에 따라 다름)	BLOB
CHARACTER LARGE OBJECT (CLOB)	큰 텍스트 데이터 (크기 제한은 시스템에 따라 다름)	CLOB

​

• 부가 설명

  CHAR(10) - 10개가 고정이라서 고양이 3개가 들어가면 7개는 공백으로 채움.

  보통 VARCHAR(50) 많이 사용, 문자 50개까지 사용 가능, 길이가 가변이라서 고양이 3개 들어가면 3개의 길이만 가짐

  문자열은 길이가 일정한 경우가 많지 않기 때문에 VARCHAR 많이 사용(길이를 늘렸다가 줄이는 눈에 안 보이는 역할을 하고 있기 때문에 성능 문제가 있을 수 있음

  항상 동일한 데이터가 들어올 때는 CHAR가 더 좋음

  = 그 예로 특히 휴대폰 번호, 주민등록번호 : 해시함수 사용해서 암호화되는데 길이는 동일함.

  텍스트 제외 모든 것은 BINARY LARGE OBJECT로 텍스트는 CLOB로 저장.

  정수는 Int랑 long 대신에 bigInt (tinyInt, smallInt 있는데 거의 사용할 일 없음)

• ORM

  데이터베이스의 테이블 값을 객체처럼 사용하는 것이 ORM

  : ‘ORM(Object Relational Mapping)’은 ‘객체로 연결을 해준다’는 의미로, 어플리케이션과 데이터베이스 연결 시 SQL언어가 아닌 어플리케이션 개발언어로 데이터베이스를 접근할 수 있게 해주는 툴

  JPA - 엔터티를 객체로 사용해서 스프링에서 사용

  JPA(Java Persistence API)

  • Java 진영에서 ORM(Object-Relational Mapping) 기술 표준으로 사용하는 인터페이스 모음
  • 자바 어플리케이션에서 관계형 데이터베이스를 사용하는 방식을 정의한 인터페이스
  • 인터페이스 이기 때문에 Hibernate, OpenJPA 등이 JPA를 구현함

  현재 있는 데이터베이스에 존재하는 모든 테이블 정보를 보기 위한 명령어 : SHOW TABLES; Users테이블의 구조를 보기위한 SQL 명령어 : DSEC Users; (DSEC =DESCRIBE의 약어)

SQL (Structured Query Language)

: 관계형 데이터베이스 관리 시스템(RDBMS)에서 데이터를 정의, 조작, 제어하기 위해 사용되는 표준화된 프로그래밍 언어

고수준언어(인간이 이해하기 쉬운 언어)라 함 , ↔저수준언어(컴퓨터가 이해하기 쉬운 언어)

1. ✨✨DDL (Data Definition Language): 데이터베이스 스키마를 생성, 변경, 삭제하기 위한 명령어
   1. 데이터 베이스를 만들거나 지우거나 하는 역할
2. ✨✨DML (Data Manipulation Language): 데이터를 검색, 삽입, 수정, 삭제하기 위한 명령어
   1. 실제 데이터를 CRUD할 때 사용
3. DCL (Data Control Language): 데이터베이스 사용자의 권한을 관리하기 위한 명령어들
4. TCL (Transaction Control Language): 데이터베이스 트랜잭션을 관리하기 위한 명령어 스프링에서 할 것. transaction이 뭔지는 알아야 됨. - ACID 특징을 가짐
   1. Transaction : 여러개의 작업을 하나로 묶은 실행 유닛, 데이터베이스 관점에서는 데이터베이스의 상태를 변환시키는 기능을 수행하기 위한 하나 이상의 쿼리를 모아 놓은 하나의 작업 단위
   2.  질의(query)를 하나의 묶음 처리해서 중간에 실행이 중단됐을 경우, 처음부터 다시 실행하는 Rollback을 수행하고, 오류없이 실행을 마치면 commit을 하는 실행 단위를 의미
   3. 자세한건 여기서 >[DB] 트랜잭션(Transaction)에 대해서 알아보자. (tistory.com)

SQL의 대표 명령어

종류	설명	대표 명령어
DDL	데이터 정의 언어	CREATE, ALTER, DROP, RENAME, TRUNCATE
DML	데이터 조작 언어	SELECT, INSERT, UPDATE, DELETE
DCL	데이터 제어 언어	GRANT, REVOKE
TCL	트랜잭션 제어 언어	COMMIT, ROLLBACK, SAVEPOINT

데이터베이스 테이블의 개념

1. 행 : 개별 레코드를 나타냄, 한 행의 테이블은 모든 열에 대한 값을 포함
2. 열 : 테이블에서 특정 속성을 나타냄
3. ✨기본키: 고유한 값을 가져야 함 = 중복불가 = Null 불가 = UNIQUE
4. ✨외래키: 내가 참조하고 있는 테이블의 기본 키를 가져옴

---

SQL에서는 MUL이 외래 키, 다른곳에서 참고해서 사용할 수 있다는 뜻

DDL(Data Definition Language)

CREATE TABLE문

테이블 이름 생성 규칙

1. 유효한 문자 사용: 테이블 이름은 알파벳, 숫자, 밑줄(_)로 구성되어야 합니다. 특수 문자나 공백을 사용하지 않는 것이 좋습니다.
2. 첫 글자: 테이블 이름은 알파벳 문자로 시작해야 합니다. 숫자나 밑줄로 시작하는 이름은 피하세요.
3. 길이 제한: 일반적으로 30자 이내의 길이로 제한
4. 의미 있는 이름 사용: 축약어X
5. 단수/복수형 일관성: 테이블 이름에 단수형 또는 복수형을 사용할 때 일관성을 유지
   1. 엔터티는 단수

6. 예약어 피하기: 데이터베이스 시스템에서 사용하는 예약어를 테이블 이름으로 사용하지 않는 것이 좋습니다. 예약어를 사용하면 SQL 쿼리 작성 시 혼란을 초래할 수 있습니다.

   테이블 이름을 만들 때 위의 규칙과 권장 사항을 따르면, 데이터베이스 구조를 명확하게 이해하고 관리하기 쉬움,

   팀 내에서 일관된 명명 규칙을 유지하는 것이 중요

제약조건 (constraints)

• 속성에 제약조건 걸 수 있음.

  제약조건	설명	예시
  PRIMARY KEY	테이블의 각 행을 유일하게 식별하는 열 또는 열의 조합	PRIMARY KEY (id)
  UNIQUE	지정된 열의 모든 값이 서로 다르게 유지되어야 함을 보장	UNIQUE (email)
  FOREIGN KEY	하나의 테이블에 있는 열이 다른 테이블의 PRIMARY KEY를 참조함으로써 외래 키 관계 설정	FOREIGN KEY (department_id)
  REFERENCES
  departments(department_id)
  NOT NULL	열의 값이 절대 NULL이 아니어야 함
  절대 비어있으면 안됨.	NOT NULL

• 예시> 테이블 생성

    CREATE TABLE Users (
        user_id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE
    );
      
    -- 출판사 테이블
    CREATE TABLE Publishers (
        publisher_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT
    );
      
    -- 도서 테이블
    CREATE TABLE Books (
        book_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        publication_date DATE,
        publisher_id INT,
        FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
    );
      
    -- 저자 테이블
    CREATE TABLE Authors (
        author_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL
    );
      
    -- 대출 기록 테이블
    CREATE TABLE LoanRecords (
        record_id INT AUTO_INCREMENT PRIMARY KEY,
        book_id INT,
        user_id INT,
        loan_date DATE,
        return_date DATE,
        FOREIGN KEY (book_id) REFERENCES Books(book_id),
        FOREIGN KEY (user_id) REFERENCES Users(user_id)
    );
      
    -- 도서와 저자의 다대다 관계를 위한 테이블
    CREATE TABLE Book_Authors (
        book_id INT,
        author_id INT,
        PRIMARY KEY (book_id, author_id),
        FOREIGN KEY (book_id) REFERENCES Books(book_id),
        FOREIGN KEY (author_id) REFERENCES Authors(author_id)
    );
  

ON DELETE 옵션 : Cascading/Restrict/Set Null

On Delete 옵션을 적절하게 사용하면 데이터의 무결성(Integrity)을 보장

1. Cascading :참조키의 기본키를 지우면 참조하고 있는 다른 엔터티의 외래 키를 다 지우는 것

    -- 출판사 테이블
    CREATE TABLE Publishers (
        publisher_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT
    );
       
    -- 도서 테이블
    CREATE TABLE Books (
        book_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        publication_date DATE,
        publisher_id INT,
        FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id) **ON DELETE CASCADE**
    );
   

   1. 위 예시 코드에서 publisher_id 컬럼은 Publishers 테이블의 publisher_id 컬럼을 참조하는 외래 키(Foreign Key)
   2. ON DELETE CASCADE 옵션이 설정되어 있으므로 Publishers 테이블의 레코드가 삭제될 때 해당 publisher_id를 참조하는 Books 테이블의 레코드도 함께 삭제

2. Restrict : 참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드를 삭제하지 않음

    -- 출판사 테이블
    CREATE TABLE Publishers (
        publisher_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT
    );
       
    -- 도서 테이블
    CREATE TABLE Books (
        book_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        publication_date DATE,
        publisher_id INT,
        FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id) **ON DELETE RESTRICT**
    );
   

3. Set Null : 참조된 레코드가 삭제될 때 해당 레코드를 참조하는 다른 레코드의 컬럼 값을 Null로 설정

    -- 출판사 테이블
    CREATE TABLE Publishers (
        publisher_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        address TEXT
    );
       
    -- 도서 테이블
    CREATE TABLE Books (
        book_id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        publication_date DATE,
        publisher_id INT NULL, -- publisher_id 칼럼이 NULL을 허용하도록 설정
        FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id) **ON DELETE SET NULL
    );**
   

---

cascade 예제 풀어보기

-- 출판사 테이블
CREATE TABLE Publishers (
    publisher_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address TEXT
);

-- 도서 테이블
	CREATE TABLE Books (
	    book_id INT AUTO_INCREMENT PRIMARY KEY,
	    title VARCHAR(255) NOT NULL,
	    publication_date DATE,
	    publisher_id INT,
	    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id) ON DELETE CASCADE
	);
	
INSERT INTO Publishers (name, address) VALUES ('Alpha Publishing', '123 Alpha St, New York, NY');
INSERT INTO Publishers (name, address) VALUES ('Beta Books', '234 Beta Rd, San Francisco, CA');
INSERT INTO Publishers (name, address) VALUES ('Gamma Press', '345 Gamma Blvd, Chicago, IL');
INSERT INTO Publishers (name, address) VALUES ('Delta Media', '456 Delta Ave, Los Angeles, CA');
INSERT INTO Publishers (name, address) VALUES ('Epsilon Publications', '567 Epsilon Way, Houston, TX');
INSERT INTO Publishers (name, address) VALUES ('Zeta Publishing House', '678 Zeta St, Phoenix, AZ');
INSERT INTO Publishers (name, address) VALUES ('Eta Publishing Group', '789 Eta Rd, Philadelphia, PA');
INSERT INTO Publishers (name, address) VALUES ('Theta Books', '890 Theta Blvd, San Antonio, TX');
INSERT INTO Publishers (name, address) VALUES ('Iota Press', '901 Iota Ave, San Diego, CA');
INSERT INTO Publishers (name, address) VALUES ('Kappa Media', '101 Kappa Way, Dallas, TX');

INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Introduction to AI', '2022-01-15', 1);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Advanced SQL', '2022-02-20', 2);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Learning Python', '2023-03-25', 3);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Web Development', '2021-04-30', 4);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Cloud Computing Basics', '2023-05-05', 5);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Data Structures', '2021-06-10', 6);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Operating Systems', '2022-07-15', 7);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Network Security', '2021-08-20', 8);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Mobile App Development', '2022-09-25', 9);
INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Machine Learning for Beginners', '2023-10-30', 10);

• 쿼리문은 순서가 무조건 중요 !!!
• 위부터 실행하는데 테이블을 먼저 만들고 데이터를 넣어야 하며
• Book이 publisher를 참조하고 있으므로 book을 가장 먼저 만들어야 함.
• casacade는 출판사 데이터에서 10번을 지울 경우 book이 참조하고 있는 10번의 데이터도 지워짐.

• 10번 클릭, 마우스오른쪽- Edit - 로우 삭제를 눌러 10번을 삭제 후 아래 Save 버튼 눌러준다. (그냥 Delete하고 SAVE 눌러도 됨)

• 새로고침 누르면 books와 publishers 모두 10번이 삭제되어 있음

ALTER문: ALTER TABLE/ALTER INDEX

• 기존의 데이터베이스 객체를 수정하는데 사용
• 데이터베이스의 구조를 유지하면서 업데이트 및 수정 가능
• ALTER 문을 사용하여 테이블, 인덱스, 시퀀스, 뷰 등의 데이터베이스 객체를 변경가능
• ALTER 문의 주요 사용 사례
  • 테이블에 열 추가, 수정, 삭제하기
  • 제약 조건 추가, 수정, 삭제하기
  • 인덱스 상태 변경하기 (활성화 또는 비활성화)
  • 시퀀스의 시작값, 증가값, 최대값 등 변경하기
  • 뷰 정의 변경하기
    1. ALTER TABLE - 기존 테이블 수정 : 열 추가 , 열 삭제, 열 데이터 타입 변경, 제약조건을 추가/삭제/수정 등

    -- 열 추가
    ALTER TABLE Users ADD phone_number VARCHAR(20) NOT NULL;
      
    -- 열 삭제
    ALTER TABLE Books DROP COLUMN publication_date;
      
    -- 데이터 타입 변경
    ALTER TABLE Authors
    MODIFY name TEXT NOT NULL;
      
    -- 제약 조건 추가
    ALTER TABLE Users
    **ADD CONSTRAINT** unique_username UNIQUE (username);
    //제약조건(unique) 추가 
      
    ALTER TABLE Users **DROP CONSTRAINT** unique_username;
    //제약조건(unique) 삭제
      
    -- 제약 조건 삭제
    ALTER TABLE Books
    DROP FOREIGN KEY fk_publisher_id;
  

1. ALTER INDEX - 기존 인덱스 수정

       
    -- 인덱스 비활성화
    ALTER TABLE Books
    DISABLE INDEX idx_title;
       
    -- 인덱스 활성화
    ALTER TABLE Books
    ENABLE INDEX idx_title;
       
   

DROP TABLE, TRUNCATE TABLE, RENAME

1. DROP TABLE : 테이블을 완전히 삭제하는 데 사용

    DROP TABLE Books;
   

2. RENAME : 테이블의 이름을 변경, 테이블의 구조나 데이터에 영향을 주지 않는다.

    RENAME Table Books TO Book;
   

   

   한번에 바꿀 수 있음.

3. TRUNCATE TABLE : 모든 데이터를 빠르게 삭제, 테이블의 구조, 인덱스, 제약 조건 등은 그대로 유지

    TRUNCATE TABLE Books;
   

   • 쳐보기~!

        출판사 테이블
       CREATE TABLE Publishers (
           publisher_id INT AUTO_INCREMENT PRIMARY KEY,
           name VARCHAR(255) NOT NULL,
           address TEXT
       );
             
       -- 도서 테이블
       	CREATE TABLE Books (
       	    book_id INT AUTO_INCREMENT PRIMARY KEY,
       	    title VARCHAR(255) NOT NULL,
       	    publication_date DATE,
       	    publisher_id INT,
       	    FOREIGN KEY (publisher_id) REFERENCES Publishers(publisher_id)
       	);
             	
       INSERT INTO Publishers (name, address) VALUES ('Alpha Publishing', '123 Alpha St, New York, NY');
       INSERT INTO Publishers (name, address) VALUES ('Beta Books', '234 Beta Rd, San Francisco, CA');
       INSERT INTO Publishers (name, address) VALUES ('Gamma Press', '345 Gamma Blvd, Chicago, IL');
       INSERT INTO Publishers (name, address) VALUES ('Delta Media', '456 Delta Ave, Los Angeles, CA');
       INSERT INTO Publishers (name, address) VALUES ('Epsilon Publications', '567 Epsilon Way, Houston, TX');
       INSERT INTO Publishers (name, address) VALUES ('Zeta Publishing House', '678 Zeta St, Phoenix, AZ');
       INSERT INTO Publishers (name, address) VALUES ('Eta Publishing Group', '789 Eta Rd, Philadelphia, PA');
       INSERT INTO Publishers (name, address) VALUES ('Theta Books', '890 Theta Blvd, San Antonio, TX');
       INSERT INTO Publishers (name, address) VALUES ('Iota Press', '901 Iota Ave, San Diego, CA');
       INSERT INTO Publishers (name, address) VALUES ('Kappa Media', '101 Kappa Way, Dallas, TX');
             
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Introduction to AI', '2022-01-15', 1);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Advanced SQL', '2022-02-20', 2);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Learning Python', '2023-03-25', 3);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Web Development', '2021-04-30', 4);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Cloud Computing Basics', '2023-05-05', 5);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Data Structures', '2021-06-10', 6);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Operating Systems', '2022-07-15', 7);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Network Security', '2021-08-20', 8);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Mobile App Development', '2022-09-25', 9);
       INSERT INTO Books (title, publication_date, publisher_id) VALUES ('Machine Learning for Beginners', '2023-10-30', 10);
             
       //상기 코드 모두 작성 후 따로 실행
       TRUNCATE TABLE Books;
     

4. DROP TABLE과 TRUNCATE TABLE의 차이점

   • 복구 가능성: DROP TABLE은 테이블 자체를 삭제하므로 복구할 수 없음.

     TRUNCATE TABLE은 테이블의 데이터만 삭제하므로 테이블 구조는 복구 가능

   • 영향 받는 객체: DROP TABLE은 테이블과 관련된 인덱스, 제약 조건, 트리거, 권한 등 모든 객체를 삭제합니다. TRUNCATE TABLE은 오직 데이터만 삭제하고, 다른 객체는 그대로 둡니다.
   • 속도: TRUNCATE TABLE은 테이블의 모든 데이터를 빠르게 삭제하는 데 반해, DROP TABLE은 테이블 자체를 삭제하므로 시간이 다소 걸릴 수 있습니다.
   • 트랜잭션 및 롤백: TRUNCATE TABLE은 트랜잭션을 사용하지 않으며 롤백이 불가능합니다. 반면, DROP TABLE은 트랜잭션을 사용하며 롤백이 가능합니다(단, 오라클에서는 롤백이 불가능합니다).
   • 용도: DROP TABLE은 테이블을 완전히 제거할 때 사용되며, TRUNCATE TABLE은 테이블의 데이터만 빠르게 삭제하려는 경우 사용됩니다.

DML(Data Manipulation Language):

데이터베이스 내의 데이터를 조작하는 데 사용되는 SQL 구문의 한 부분

DML의 주요구문 SELECT, INSERT, UPDATE, DELETE

SELECT

• 데이터베이스에서 데이터를 조회(검색)하는 데 사용

• 특정 테이블에서 원하는 열(column)과 행(row)을 선택하여 결과를 반환

    SELECT username, email FROM Users;
      
    --모든 저자의 이름 출력:
      
    SELECT name FROM Authors;
      
    --모든 대출 기록의 대출 날짜와 반납 예정 날짜 출력
      
    SELECT loan_date, return_date FROM LoanRecords;
  

기본 사용법과 DISTINCT, 문자열 연결 연산자 ||, 그리고 별칭(alias) 지정

1. 기본 사용법

    SELECT column_name1, column_name2, ... FROM table_name;
   

2. DISTINCT
   1. DISTINCT 키워드는 중복된 값을 제거하고 고유한 값만 조회하려는 경우

    SELECT DISTINCT column_name FROM table_name;
       
    //예를 들어 **Authors**테이블에서 고유한 값만 조회
    //SELECT DISTINCT name FROM Authors;
   

3. 문자열 연결 연산자 ||: || 연산자를 사용하여 문자열을 연결

       
    SELECT column_name1 || ' ' || column_name2 AS new_column_name FROM table_name;
       
   

4. 별칭지정(alias)

   1. SELECT 문에서 별칭(alias)을 사용하면 열 이름이나 표현식에 다른 이름을 지정가능

              
       SELECT column_name AS alias_name FROM table_name;
      

   2. Users 테이블에서 username 열을 이름이라는 별칭으로 조회하려면 다음과 같이 작성

              
       SELECT username AS 이름 FROM Users;
              
       SELECT username AS un FROM Users;
      

      

INSERT

데이터베이스에 새로운 데이터를 추가하는 데 사용

--사용자 테이블에 새로운 사용자 추가:

INSERT INTO Users (username, email)
VALUES ('alice', 'alice@example.com');

--출판사 테이블에 새로운 출판사 추가:
INSERT INTO Publishers (name, address)
VALUES ('ABC Publishing', '123 Main Street');

--도서 테이블에 새로운 도서 추가:
INSERT INTO Books (title, publication_date, publisher_id)
VALUES ('The Book Thief', '2005-11-01', 1);

--저자 테이블에 새로운 저자 추가:

INSERT INTO Authors (name)
VALUES ('Markus Zusak');

특정 테이블의 행(row)에 값을 지정하여 삽입 가능- 단일 행 삽입 및 다중 행 삽입.

1. 단일 행 삽입

    INSERT INTO table_name (column1, column2, column3, ...)
    VALUES (value1, value2, value3, ...);
       
    //**able_name**은 데이터를 삽입하려는 테이블의 이름이고, 
    //**column1, column2, column3, ...**은 값을 삽입하려는 테이블의 열(column) 
    //**value1, value2, value3, ...**은 해당 열에 삽입할 값
   

   예시

    --도서 테이블에 새로운 도서 추가:
    INSERT INTO Books (title, publication_date, publisher_id)
    VALUES ('The Book Thief', '2005-11-01', 1);
   

2. 다중 행 삽입
   1. 한 번에 여러 행을 테이블에 삽입
   2. 테이블에 대량의 데이터를 삽입할 때 효율적입니다.

    INSERT INTO Users (username, email)
    VALUES ('kimlucky', 'kimlucky@example.com'),
           ('vanilla', 'vanilla@example.com'),
           ('kimlatte', 'kimlatte@example.com');
       
    INSERT INTO Publishers (name, address)
    VALUES ('ABC Publishing', '123 Main Street'),
           ('DEF Press', '456 Elm Street');
       
    INSERT INTO Books (title, publication_date, publisher_id)
    VALUES ('The Book Thief', '2005-11-01', 1),
           ('Pride and Prejudice', '1813-01-28', 2);
       
    INSERT INTO Authors (name)
    VALUES ('Markus Zusak'),
           ('Jane Austen');
       
    INSERT INTO LoanRecords (book_id, user_id, loan_date)
    VALUES (1, 1, '2024-04-30'),
           (2, 2, '2024-04-29');
       
    INSERT INTO Book_Authors (book_id, author_id)
    VALUES (1, 1),
           (2, 2);
   

3. INSERT INTO SELECT

   1. 다른 테이블에서 선택한 데이터를 기반으로 행을 삽입하는 방법

       INSERT INTO table_name1 (column1, column2, column3, ...)
       SELECT column1, column2, column3, ...
       FROM table_name2
       WHERE condition;
      

   2. 예제

       -- 모든 사용자 이름과 이메일을 'New Users' 테이블에 복사:
              
       CREATE TABLE NewUsers (
           user_id INT AUTO_INCREMENT PRIMARY KEY,
           username VARCHAR(255) NOT NULL,
           email VARCHAR(255) NOT NULL UNIQUE
       );
              
       INSERT INTO NewUsers (username, email)
       SELECT username, email
       FROM Users;
      

      • 결과

        Users

        ​

        Newusers

        ​

   3. 주의사항

      1. NOT NULL 제약 조건이 있는 열에는 NULL 값을 삽입할 수 없습니다. 반드시 값을 제공해야 함

      2. 기본 키(primary key) 또는 고유 키(unique key) 제약 조건이 있는 열에 중복 값을 삽입하려고 하면 오류가 발생

      3. 외래 키(foreign key) 제약 조건이 있는 열에 존재하지 않는 참조 값을 삽입하려고 하면 오류가 발생

         
         

UPDATE

UPDATE문은 데이터베이스의 기존 데이터를 수정하는 데 사용

특정 테이블에서 행(row)과 열(column)을 선택하여 값을 변경

//기본구문
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

//**table_name**은 데이터를 수정하려는 테이블의 이름이고,
 **column1, column2, ...**은 값을 수정하려는 테이블의 열(column)
 **value1, value2, ...**은 해당 열에 수정할 값
 마지막으로 **condition**은 특정 행을 선택하는 조건

--특정 사용자의 이메일 변경:

UPDATE Users
SET email = 'newemail@example.com'
WHERE user_id = 1;

--특정 도서의 출판 날짜 변경:

UPDATE Books
SET publication_date = '2006-01-01'
WHERE book_id = 1;

-- 모든 대출 기록의 반납 날짜를 현재 날짜로 변경:

UPDATE LoanRecords
SET return_date = CURRENT_DATE;

주의사항

1. WHERE 절을 사용하여 특정 행을 선택하지 않으면, 테이블의 모든 행이 업데이트

    **update** users **set** username=**'kimlucky'**
   

   

2. NOT NULL 제약 조건이 있는 열에 NULL 값을 설정하려고 하면 오류가 발생
3. 기본 키(primary key) 또는 고유 키(unique key) 제약 조건이 있는 열에 중복 값을 설정하려고 하면 오류가 발생
4. 외래 키(foreign key) 제약 조건이 있는 열에 존재하지 않는 참조 값을 설정하려고 하면 오류가 발생

UPDATE문을 사용할 때 다양한 연산자와 함수를 사용하여 값을 계산하거나 조작할 수 있습니다. 예를 들어, 기존 값에 숫자를 더하거나 빼거나, 문자열을 연결하거나, 날짜를 변경할 수 있습니다.

예시)

-- 특정 사용자의 나이를 1 증가시키기:

UPDATE Users
SET age = age + 1
WHERE user_id = 1;

-- 특정 도서의 출판 연도를 2023으로 변경:

UPDATE Books
SET publication_year = 2023
WHERE book_id = 1;

DELETE

• 데이터베이스에서 데이터를 삭제하는 데 사용
• 특정 테이블에서 행(row)을 선택하여 삭제 가능

--특정 사용자 삭제:

DELETE FROM Users
WHERE user_id = 1;

--사용자 테이블에서 특정 사용자 삭제하기:
DELETE FROM Users WHERE username = 'john_doe';

--도서 테이블에서 특정 도서 삭제하기:
DELETE FROM Books WHERE title = 'Sample Book';

--출판사 테이블에서 주소가 불분명한 출판사 삭제하기:
DELETE FROM Publishers WHERE address IS NULL;

null 할때 =null 아니고 iS NULL;

주의사항

1. WHERE 절을 사용하여 특정 행을 선택하지 않으면, 테이블의 모든 행이 삭제됨이 경우, TRUNCATE문을 사용하는 것이 더 효율적
2. 삭제된 데이터는 복구할 수 없으므로, DELETE문을 사용하기 전에 데이터를 백업하거나 데이터를 복구할 수 있는 방법을 고려하기

3. 외래 키(foreign key) 제약 조건으로 인해 참조 무결성이 깨질 경우, 해당 행을 삭제할 수 없음. 이 경우, 참조하는 행을 먼저 삭제하거나 외래 키 제약 조건을 수정해야 함

   • DELETE FROM 과 TRUNCATE TABLE의 차이

     차이	DELETE FROM	TRUNCATE
     작동방식	WHERE 절을 사용하지 않으면 테이블의 모든 행을 하나씩 삭제 이 경우, 각 행의 삭제에 대한 로그가 기록되고, 트리거가 발생할 수 있음.	테이블의 모든 데이터를 한 번에 삭제하며, 로그를 남기지 않고 테이블을 초기 상태로 재설정합니다. 트리거를 발생X
     성능	개별적으로 삭제하므로, 테이블이 큰 경우 실행 속도가 느릴 수 있음	한 번에 삭제하므로, 실행 속도가 빠르고 효율적
     사용사례	특정 조건(WHERE절)을 만족하는 행을 삭제할 때 사용	테스트 데이터를 삭제하거나 임시 테이블의 데이터를 초기화할 때 사용
     실무	특정 날짜 이전의 테스트 데이터를 삭제	하루에 한 번씩 임시 데이터를 삭제해야 하는 시나리오

     DELETE FROM 실무에 대한 예제 [2022-05-01 이전 데이터 삭제]

       DELETE FROM LoanRecords
       WHERE loan_date < '2022-05-01';
     

     DELETE 전

     ​

     DELETE 후

     ​

Database 카테고리 내 다른 글 보러가기