(데이터베이스)테이블 생성,삭제(Data Type) / 데이터 삽입, 수정, 삭제

목차

SQL 실행 / SQL 작성시 참고 내용

SQL 편집기 실행 / SQL 문 실행 방법

• 데이터베이스명 우클릭 - SQL 편집기 - SQL 편집기 클릭

  • 

  • 참고 : 만약 localhost 우클릭 - SQL 편집기 - SQL 편집기 클릭 을 하게 된다면 "from 테이블명" 이 아닌 "from 데이터베이스명.테이블명" 으로 지정해주어야합니다.
• SQL 작성 후 - 좌측에 실행 버튼을 통해 실행 가능

  • 

• 여러 줄로 작성된 SQL 스크립트에서 특정 부분만 실행하기 (해당 부분을 드래그 하고 SQL 스크립트 실행이 아닌 SQL문 실행 버튼 클릭)

  • 

SQL 작성 시 참고 내용

• 빨간색으로 작성된 내용은 SQL 문법

  • 

• SQL 문법들 대문자로 자동완성되게 하기
  • 파일 - 설정 - 편집기 - SQL 편집기 - SQL 포맷 설정 - 우측 상단에 Global settings 클릭
  • Keyword case를 Upper로 설정

  • 

  • 사실 소문자나 대문자 아무거로 작성해도 상관없습니다.
• 엔터를 통해 하나의 SQL문을 여러 줄로 나누어서 작성할 수 있습니다. 단, SQL문이 끝나면 " ; "를 반드시 붙여주어야합니다.

  • 

데이터베이스와 테이블

• 데이터베이스는 폴더 / 테이블을 파일이라고 생각 
  • 엑셀파일들도 폴더 안에 보관하는 것처럼 테이블을 데이터베이스 안에 보관하는 것입니다.

데이터베이스

DBeaver를 통한 데이터베이스 생성

• Databases 우클릭 - Create New Database
  • 
• 데이터베이스 이름 지정 후 확인 클릭
  • 
• 참고 : MySQL Workbench에서 하고 싶은 경우
  • 
  • MySQL Workbench에서 database를 schema라고 부릅니다.

SQL을 통한 데이터베이스 생성 / 삭제

• SQL을 통한 데이터베이스 생성 / 삭제
  • 데이터베이스 생성

    • CREATE DATABASE 데이터베이스명

  • 데이터베이스 삭제

    • DROP DATABASE 데이터베이스명

      • 삭제하면 복구가 안되니 잘 살펴보기

테이블

DBeaver를 통한 테이블/ 컬럼 생성 (제약조건)

• Tables 우클릭 - Create New Table
  • 
• 테이블명 작성
  • 
  • 테이블명은 작성 시 보통 2 단어 이상이 안전합니다. (name 같은 거를 테이블명으로 지정할 경우 기존에 name이라는 이름은 다른 곳에서 사용되고 있기 때문에 테이블명으로 지정하게 되면 오류가 발생합니다. 따라서 _ 를 통한 두 단어를 구분하여 작성하는 경우가 많습니다.) 
• Table에 컬럼 정보 지정
  • 예시
    • 
    • column이 4개 존재
  • Columns에서 우클릭 - Create New Column 클릭 (위 예시에서 4개의 column 이 필요하므로 4번 반복해서 진행)
    • 
    • 
  • Data type 지정 필요 (해당 칼럼에 들어갈 데이터 타입 지정)
    • 
  • Save 클릭 (또는 Ctrl + S) - Persist 클릭
    • 
• DBeaver에서 CONSTRAINT 주기

  • 

  • Columns에서 NOT NULL, DEFAULT 값, Auto Increment 지정가능
  • Constraints에서 PRIMARY KEY, UNIQUE, CHECK 지정가능
  • Foreign Keys에서 FOREIGN KEY 지정 가능
    • Create New Foreign Key 클릭
    • Reference table 메뉴에서 현재 테이블이 어떤 다른 테이블의 primary key를 참조할건지 선택하기 
    • Column메뉴에선 foregine key 제약 조건 달아줄 program 테이블의 컬럼 선택하면 되고, Ref Column에선 참조당할 teacher 테이블의 컬럼을 선택하기
    • On Delete 옵션과 One Update 옵션 선택하기(원하는 값으로) 
      • 
    • 저장버튼 클릭
    • program 테이블에 가면 foreign key 컬럼에 화살표가 생기는데 눌러보면 연결된 다른 테이블과 행을 보여줍니다. (나중에 강사id가 1인 사람이 누군지 궁금하면 클릭만 해보면 됩니다.)
      • 

DBeaver을 통한 테이블 데이터 삽입 / 수정 / 삭제

• Table 데이터 추가
  • 해당 Table에서 Properties 옆에 있는 Data 클릭 - 로우 추가 클릭 
• 테이블 컬럼 수정의 경우는 해당 칸의 내용을 직접 수정하면 됩니다.
• 테이블 컬럼 삭제의 경우 로우 추가 오른쪽으로 2번째에 있는 로우 삭제버튼을 통해 가능합니다.

SQL을 통한 테이블 생성 / 삭제 (제약조건)

• 테이블 생성 시에는 해당 데이터베이스명에서 우클릭 후 테이블 생성을 클릭해야 합니다. SQL을 통한 테이블 생성 / 삭제
  • 
  • 위처럼 하지 않고 localhost 우클릭 후 SQL 편집기를 실행하는 방법 등 다른 방법으로 실행하게 된다면 테이블명을 입력할 때 앞에 "데이터베이스명.테이블명" 형식으로 매번 입력을 해주어야 합니다.
• SQL을 통한 테이블 생성 / 삭제
  • 테이블 생성
    • 간단한 테이블 생성 예시
      

      • CREATE TABLE 테이블명 ( 
            컬럼명1 데이터타입, 
            컬럼명2 데이터타입, 
            컬럼명3 데이터타입 
        )

      • 마지막 컬럼명에는 ,(콤마) 가 붙으면 안 됩니다.
    • 제약조건 추가한 테이블 생성 예시
      • [ ] 로 표시한 항목은 생략이 가능합니다. 
      • PRIMARY KEY가 1개의 속성일 때와 여러 개의 속성으로 구성된 경우 다르게 표현해 줄 수 있습니다.
      • 예시 1 (PRIMARY KEY가 1개의 속성으로 구성된 경우)
        • 이 경우에는 보통 AUTO_INCREMENT와 PRIMARY KEY를 함께 설정합니다.
          • PRIMARY KEY가 숫자가 아닌 아이디와 같은 내용일 경우 AUTO_INCREMENT를 사용하지 않습니다.
        • PRIMARY KEY로 설정하면 NOT NULL, UNIQUE 제약이 자동으로 부여됩니다.

        • CREATE TABLE 예시테이블명 (
              컬럼명1 데이터타입1 [NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY] [UNIQUE] [DEFAULT 기본값] [COMMENT '컬럼 설명'],
              컬럼명2 데이터타입2 NOT NULL AUTO_INCREMENT PRIMARY KEY,
              컬럼명3 데이터타입3 NOT NULL UNIQUE,
              컬럼명4 데이터타입4 DEFAULT 기본값4,
              컬럼명5 데이터타입5,
              컬럼명6 데이터타입6,
          
              FOREIGN KEY (컬럼명3) REFERENCES 참조할테이블명1(참조할컬럼명1) ON DELETE 옵션 ON UPDATE 옵션,
              FOREIGN KEY (컬럼명5, 컬럼명6) REFERENCES 참조할테이블명2(참조할컬럼명2, 참조할컬럼명3) ON DELETE 옵션 ON UPDATE 옵션,
          
              UNIQUE (컬럼명3, 컬럼명4),
              CONSTRAINT 특정제약조건이름 CHECK (컬럼명4 > 0)
          );

      • 예시 2 (PRIMARY KEY가 2개 이상의 속성으로 구성된 경우)
        • 이 경우에는 마지막에 PRIMARY KEY 컬럼리스트 를 통해 지정해 줍니다.

        • CREATE TABLE 예시테이블명 (
              컬럼명1 데이터타입1 [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [DEFAULT 기본값1] [COMMENT '컬럼 설명1'],
              컬럼명2 데이터타입2 [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [DEFAULT 기본값2] [COMMENT '컬럼 설명2'],
              컬럼명3 데이터타입3 [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [DEFAULT 기본값3] [COMMENT '컬럼 설명3'],
              컬럼명4 데이터타입4 [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [DEFAULT 기본값4] [COMMENT '컬럼 설명4'],
              
              PRIMARY KEY (컬럼명1, 컬럼명2),
              UNIQUE (컬럼명3, 컬럼명4),
              FOREIGN KEY (컬럼명3) REFERENCES 참조할테이블명(참조할컬럼명) [ON DELETE 옵션] [ON UPDATE 옵션],
              CONSTRAINT 특정제약조건이름 CHECK (컬럼명4 > 0)
          );

          
          
          • 컬럼명1 데이터타입1 [NOT NULL] [AUTO_INCREMENT] [UNIQUE] [DEFAULT 기본값1] [COMMENT '컬럼 설명1'],
            
            • 테이블을 구성하는 각 속성의 이름, 데이터타입, 기본적인 제약 사항 정의
            • AUTO_INCREMENT는 데이터를 넣지 않아도 자동으로 1씩 증가하는 정수값을 넣어줍니다.
          • [PRIMARY KEY (속성리스트)]
            • 기본키로 테이블에 하나만 존재 가능
            • 기본키로 최소성, 유일성을 모두 만족
            • NOT NULL, UNIQUE 제약이 자동 부여
          • [UNIQUE (속성리스트)]
            • 대체키로 테이블에 여러 개 존재 가능
            • 대체키로 최소성, 유일성을 모두 만족
            • NOT NULL 제약은 자동으로 부여되지 않습니다. 필요하다면 NOT NULL제약을 명시적으로 추가해 줄 수 있습니다.
            • UNIQUE(이름, 나이) 예시
              • 저장되는 경우

                • 

              • 저장되지 않는 경우

                • 

                • UNIQUE(이름, 나이)로 인해 이름과 나이가 모두 같으면 저장되지 않습니다.
          • [FOREIGN KEY (속성리스트) REFERENCES 참조할테이블명(속성리스트)] [ON DELETE 옵션] [ON UPDATE 옵션]
            • 외래키로 테이블에 여러 개 존재 가능
            • ON DELETE 옵션 (외래키가 참조하고 있는 테이블에서 (참조되고 있는 기본키를 가진) 투플을 삭제하려고 하는 경우 = 참조되는 테이블의 투플이 삭제될 때)
              • ON DELETE NO ACTION(기본값) : 투플을 삭제하지 못하게 한다.
              • ON DELETE CASCADE : 관련 투플을 함께 삭제한다.
              • ON DELETE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
              • ON DELETE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 기본값(해당 속성값의 DEFAULT값)으로 변경한다.
            • ON UPDATE 옵션 (외래키가 참조하고 있는 테이블에서 (참조되고 있는 기본키를 가진) 투플을 변경하려고 하는 경우 = 참조되는 테이블의 투플이 변경될 때)
              • ON UPDATE NO ACTION(기본값) : 투플을 변경하지 못하도록 한다.
              • ON UPDATE CASCADE : 관련 투플에서 외래키 값을 함께 변경한다.
              • ON UPDATE SET NULL : 관련 투플의 외래키 값을 NULL로 변경한다.
              • ON UPDATE SET DEFAULT : 관련 투플의 외래키 값을 미리 지정한 값으로 변경한다.
          • [CONSTRAINT 이름] [CHECK(조건)]
            • 데이터 무결성을 위한 제약조건으로 테이블에 여러 개 존재 가능
  • 테이블 삭제

    • DROP TABLE new_table

• 예제
  • 다음 내용을 참고하여 학생 테이블, 교사 테이블, 상담 테이블을 만들기
    • 
    • 학생 테이블

      • CREATE TABLE 학생 (
            학번 INT AUTO_INCREMENT PRIMARY KEY,
            이름 VARCHAR(100) NOT NULL,
            학년 INT NOT NULL
        );

    • 교사 테이블

      • CREATE TABLE 교사 (
            교사번호 INT AUTO_INCREMENT PRIMARY KEY,
            이름 VARCHAR(100) NOT NULL
        );

    • 상담 테이블

      • CREATE TABLE 상담 (
            상담번호 INT AUTO_INCREMENT PRIMARY KEY,
            학번 INT NOT NULL,
            담당교사 INT NOT NULL,
            상담일지 DATE NOT NULL,
            내용 TEXT NOT NULL,
            FOREIGN KEY (학번) REFERENCES 학생(학번) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (담당교사) REFERENCES 교사(교사번호) ON DELETE CASCADE ON UPDATE CASCADE
        );

SQL을 통한 테이블 컬럼 추가 / 수정 / 삭제

• 테이블 컬럼 추가
  

  • ALTER TABLE 테이블명 ADD 새로운컬럼명 데이터타입 [NOT NULL] [DEFAULT 기본값]

• 기존 테이블 컬럼 수정

  • ALTER TABLE 테이블명 MODIFY COLUMN 기존컬럼명 변경하고싶은데이터타입

    • 참고 : 이미 컬럼에 문자가 들어있는데 INT로 변경하라고 한다면 오류가 발생합니다.
• 테이블 컬럼 삭제

  • ALTER TABLE 테이블명 DROP COLUMN 기존컬럼명

SQL을 통한 테이블 데이터 삽입 / 수정 / 삭제

• 데이터 삽입/ 수정 /삭제는 매우 위험한 작업입니다. 따라서 권한관리를 잘 해줘야합니다.
• INSERT
  • INSERT 기본 

    • INSERT INTO 테이블명 (컬럼명1, 컬럼명2) VALUES (넣을값1, 넣을값2)

  • 모든 컬럼에 데이터 넣을땐 컬럼명 생략가능

    • INSERT INTO 테이블명 VALUES (넣을값1, 넣을값2, 넣을값3)

       
  • 테이블간 데이터 복사

    • INSERT INTO 기준테이블명 SELCT * FROM 복사할테이블명

       
  • 테이블 내용 복사해서 새 테이블 생성

    • CREATE TABLE 새로운테이블명 SELCT * FROM 기존테이블명

  • 테이블 내용 복사해서 임시 테이블 생성 (재접속시 사라짐)

    • CREATE TEMPORARY TABLE 새로운테이블명 SELCT * FROM 기존테이블명

• UPDATE / SET / WHERE
  • UPDATE / SET / WHERE 기본
    

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

      • WHERE 뒤에는 어떤 행을 수정할지, SET 뒤에는 어떤 컬럼을 수정할지를 지정해주기
      • 주의 ! : WHERE 빼면 모든 행이 수정됩니다. (항상 UPDATE, SET, WHERE 같이 채워놓고 시작하기)
• DELETE FROM / WHERE
  • DELETE FROM / WHERE

    • DELETE FROM 테이블명 WHERE 조건식

       
      • 특정 ROW를 아예 삭제하고 싶으면 DELETE 사용
      • 조건식에 맞는 모든 행을 삭제해줍니다.
      • 주의 ! : WHERE 빼면 모든 행이 삭제됩니다. (항상 UPDATE / DELETE FROM 할때는 WHERE 같이 쓰기!!!!)

Data type 정리

• 문자
  • 일반적으로 VARCHAR를 가장 많이 사용합니다. 조금 긴 문자 저장하고 싶을 경우는 MEDIUMTEXT 또는 LONGTEXT로 지정해 줍니다. (나머지는 잘 안 씁니다.)
    • varchar(100) : 문자를 총 100자까지 지정이 가능합니다. 라고 길이 제한을 두는 것입니다. (최대 65535자까지 가능합니다.)
  • 
• 숫자
  • 범위를 고려해서 값을 지정해 주면 됩니다. DECIMAL은 오차 없이 소수점을 지정 가능합니다.(FLOAT나 DOUBLE은 오차가 발생할 수 있습니다. 
  • INT / BIGINT / DECIMAL 자주 사용
  • 
• 날짜/시간
  • 주로 DATETIME을 사용합니다. (TIMESTAMP는 2038년까지만 저장이 가능하기 때문) 
  • 
• 나머지
  • BLOB, BOOLEAN, JSON 등
• 참고 : DBMS마다 Data type이 다르기 때문에 다른 DBMS를 사용할 때는 해당 DBMS Data type을 참고해서 지정해주어야 합니다.

예제

• id(primary key), 유저 이름, 전화번호를 저장할 테이블 하나 만들기

  • CREATE TABLE member (
    	id INT AUTO_INCREMENT PRIMARY KEY,
    	유저이름 varchar(100),
    	전화번호 varchar(100)
    )

• 방금 만든 테이블에 '나이'를 저장할 컬럼을 추가하기 (기본값은 1로 설정)

  • ALTER TABLE member ADD 나이 INT DEFAULT 1

• 방금 만든 테이블의 '나이' 컬럼의 데이터타입을 변경해보기(INT에서 BIGINT로)
  

  • ALTER TABLE MEMBER MODIFY COLUMN 나이 BIGINT

• 방금 만든 테이블 삭제하기

  • DROP TABLE member

출처

• 데이터베이스 개론(3판)
• codding apple [한 번에 끝내는 SQL & Database]