[SQL/MySQL] SQL로 하는 데이터 관리 기초

1. 데이터베이스 생성

   -- 이미 같은 이름을 가진 DB가 있으면 에러를 발생시키지 않고 쿼리를 실행함
   CREATE database IF NOT EXISTS course_rating;
   

2. 사용할 데이터베이스를 DBMS 에게 알리기

   USE course_rating;

3. 테이블 만들기

   CREATE TABLE `course_rating`.`student` (
     `id` INT NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(20) NULL,
     `student_number` INT NULL,
     `major` VARCHAR(15) NULL,
     `email` VARCHAR(50) NULL,
     `phone` VARCHAR(15) NULL,
     `admission_date` DATE NULL,
     PRIMARY KEY (`id`));
   );

4. 속성

   • Priamry Key와 Unique 속성의 차이

5. 컬럼 관련 쿼리 다루기

      ALTER TABLE student ADD gender CHAR(1) NULL;
       ALTER TABLE student RENAME COLUMN student_number TO registration_number;
   
       ALTER TABLE student DROP COLUMN addmission_date;
   
       UPDATE student SET major = 10 WHERE major = '컴퓨터공학과';
       UPDATE student SET major = 12 WHERE major = '멀티미디어학과';
       UPDATE student SET major = 7 WHERE major = '법학과';
   
       ALTER TABLE student MODIFY major INT;
   
       ALTER TABLE student MODIFY name VARCHAR(35) NOT NULL;
       ALTER TABLE student MODIFY registration_number INT NOT NULL;
       ALTER TABLE student MODIFY major INT NOT NULL;
   
       # INSERT INTO student (email, phone, gender)
        #   VALUES ('abc@naver.com', '010-1234-5678', 'm');
   
       ALTER TABLE student MODIFY major INT NOT NULL DEFAULT 101;
       INSERT INTO student (name, registration_number) VALUES ('구지섭', 20112405);
   
       ALTER TABLE student MODIFY registration_number INT NOT NULL UNIQUE;
       #INSERT INTO student (name, registration_number) VALUES ('최태웅', 20112405);
   
       ALTER TABLE student ADD CONSTRAINT st_rule CHECK (registration_number < 30000000);
       ALTER TABLE student DROP CONSTRAINT st_rule;
   
       ALTER TABLE student 
           ADD CONSTRAINT st_rule
           CHECK (email LIKE '%@%' AND gender IN ('m', 'f'));

6. 테이블 복사본 만드는 방법, 테이블 컬럼 구조만 복사하는 법

      CREATE TABLE shoes_for_experiment AS SELECT * FROM shoes;
       DROP TABLE shoes_for_experiment;
   
       # 테이블 구조만 복사
       CREATE TABLE order_needed_shoes LIKE shoes;
   
       INSERT INTO freshman SELECT * FROM undergraduate WHERE grade = 1; #1학년 테이블 
       INSERT INTO sophomore SELECT * FROM undergraduate WHERE grade = 2; #2학년 테이블 
       INSERT INTO junior SELECT * FROM undergraduate WHERE grade = 3; #3학년 테이블 
       INSERT INTO senior SELECT * FROM undergraduate WHERE grade = 4; #4학년 테이블
   	
       CREATE TABLE beta_review_20s LIKE beta_review;
       # 20대가 남긴 리뷰만 넣어주기
       INSERT INTO beta_review_20s SELECT * FROM beta_review WHERE age  between 20 and 29;
       # 테스트 코드
       SELECT * FROM beta_review_20s;

7. Foreign Key 설정하기

   # ON DETELE / ON UPDATE - RESTRICT, CASCADE, SET NULL 
    # 적절한 정책 고르기
    ALTER TABLE `course_rating`.`review` 
       ADD CONSTRAINT `fk_review_table`
         FOREIGN KEY (`course_id`)
         REFERENCES `course_rating`.`course` (`id`)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT;
   
     ALTER TABLE review 
     DROP FOREIGN KEY fk_review_table;
        
     ALTER TABLE review 
         ADD CONSTRAINT fk_review_table
         FOREIGN KEY (course_id)
         REFERENCES course(id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT;
   
     ALTER TABLE review 
         ADD CONSTRAINT fk_review_table
         FOREIGN KEY (course_id)
         REFERENCES course(id)
         ON DELETE SET NULL
         ON UPDATE RESTRICT;
     

8. 스키마(Schema) : 데이터베이스에 관한 모든 설계사항

   • 개념적 스키마
     • 데이터베이스에 있는 테이블들
     • 각 테이블의 컬럼 구조와 각 컬럼의 데이터 타입 및 속성들
     • 테이블 간의 관계
   • 물리적 스키마
     • DBMS 개발자가 될 거라면.. 더 공부해보는 걸 추천.

실무에서 여러분이 첫 번째로 해야할 일

1. 존재하는 데이터베이스 파악

show databases

2. 한 데이터베이스 안에 테이블(뷰도 포함)들 파악

show full tables in copang_main

3. 한 테이블의 컬럼 구조 파악

describe item

4. Foreign Key(외래키) 파악

select
i.table_schema, i.table_name, i.constraint_type, i.constraint_name,
k.referenced_table_name, k.referenced_column_name
from inforamaion_schema.table_constraint i
left join information_schema.KEY_COLUMN_USAGE k ON i.constraint_name = k.constraint_name
where i.constraint_type = 'FOREIGN KEY';

Reference

• [MySQL] tinyint(1) vs tinyint(4)
• How to calculate interval between datetime in MySQL?
• MySQL 컬럼명 변경, 컬럼 타입 변경, 컬럼 추가, 컬럼 삭제
• SQL 복잡한 데이터 검색 - 문자열 검색 (LIKE / % / _ )
• SQL 범위 검색 연산자 BETWEEN
• MySQL 테이블 생성 쿼리 확인하기
• MySQL 테이블 이름 변경 (RENAME)