StudyPython and CE

Database - 2

FOREIGN KEY 해제

  • 테이블 보기

    1
    SHOW TABLES;

  • 테이블 생성 쿼리 보기

    1
    2
    SHOW CREATE TABLE "table_name";
    SHOW CREATE TABLE students;

    • FOREIGN KEY 확인

      1
      CONSTRAINT students_ibfk_1 FOREIGN KEY (classID) REFERENCES classes (classID)

  • FOREIGN KEY 해제

    • 테이블 생성 쿼리에서 FOREIGN KEY 앞 값 확인하여 작성

      1
      ALTER TABLE students -> DROP FOREIGN KEY students_ibfk_1;

  • FOREIGN KEY가 해제됐는지 확인

    1
    SHOW CREATE TABLE students;

  • 테이블 인덱스 보기

    1
    SHOW INDEX FROM students;

  • FOREIGN KEY였던 값(classID) 확인

    • 인덱스 제거

      1
      DROP INDEX [index_name]; DROP INDEX classID;

    • 제거되었는지 확인

      1
      SHOW INDEX FROM students;

    • teachers table도 같은 방법으로 FOREIGN KEY 해제 및 INDEX 제거

    • 테이블 제거

      1
      DROP TABLE [table_name]; DROP TABLE classes;

    • classes의 classID를 FOREIGN KEY로 참조하고 있었기 때문에 해제한 후 제거

JOIN

  • 집합론

  • INNER JOIN과 OUTER JOIN으로 나뉨

  • OUTER JOIN은 LEFT JOIN, RIGHT JOIN, FULL JOIN으로 나뉨

  • FULL JOIN은 mySQL이나 MariaDB에서는 지원하지 않으므로, UNION을 통해 구현

  • TABLE 1과 TABLE 2가 있을 때, 두 TABLE을 가로로 연결하는 것

  • 해당 TABLE의 모든 내용 가져오기

    1
    2
    3
    SELECT * FROM "table_name";
    SELECT * FROM students;
    SELECT * FROM teachers;

INNER JOIN - 교집합

  • 첫번째, 두번째 줄에서 COLUMN 앞에 붙은 것은 해당 TABLE의 별칭

  • INNER JOIN은 DEFAULT 명령어이기 때문에 JOIN만 명시해도 INNER JOIN으로 실행

  • ON은 JOIN할 조건

  • ORDER BY는 오름차순으로 정렬

    1
    2
    3
    4
    5
    SELECT S.studentName, S.score, S.classID,
    -> T.teacherName, T.subject
    -> FROM students S INNER JOIN teachers T
    -> ON S.classID = T.classID
    -> ORDER BY S.classID

LEFT OUTER JOIN - 왼쪽 TABLE 기준 집합

  • 세번째 줄에서 JOIN 명령어를 기준으로 왼쪽에 있는 TABLE 기준으로 JOIN

  • 왼쪽 TABLE의 데이터가 모두 출력

  • 오른쪽 TABLE은 보이지 않으므로 NULL로 표시됨

    1
    2
    3
    4
    5
    SELECT S.studentName, S.score, S.classID,
    -> T.teacherName, T.subject
    -> FROM students S LEFT OUTER JOIN teachers T
    -> ON S.classID = T.classID
    -> ORDER BY S.classID;

RIGHT OUTER JOIN - 오른쪽 TABLE 기준 집합

  • 세번째 줄에서 JOIN 명령어를 기준으로 오른쪽에 있는 TABLE 기준으로 JOIN

  • 오른쪽 TABLE의 데이터가 모두 출력

  • 왼쪽 TABLE은 보이지 않으므로 NULL로 표시됨

    1
    2
    3
    4
    5
    SELECT S.studentName, S.score, S.classID,
    -> T.teacherName, T.subject
    -> FROM students S RIGHT OUTER JOIN teachers T
    -> ON S.classID = T.classID
    -> ORDER BY S.classID;

FULL JOIN - 전체 집합

  • mySQL, MariaDB에서는 지원하지 않으므로, UNION 명령어를 통해 구현

  • [LEFT OUTER JOIN] UNION [RIGHT OUTER JOIN]

    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT S.studentName, S.score, S.classID,
    -> T.teacherName, T.subject
    -> FROM students S LEFT OUTER JOIN teachers T
    -> ON S.classID = T.classID
    -> UNION
    SELECT S.studentName, S.score, S.classID,
    -> T.teacherName, T.subject
    -> FROM students S RIGHT OUTER JOIN teachers T
    -> ON S.classID = T.classID;

GROUP BY

  • 특정 COLUMN을 그룹화

  • 그룹한 결과에 조건을 거는 HAVING = HAVING은 GROUP BY 명령어 다음으로 선언

평균값(AVG)로 그룹화 하기

  • 이렇게 할 경우, TABLE 내부에 classID 값이 NULL인 결과도 출력됨

    1
    2
    3
    SELECT classID, AVG(score)
    -> FROM students
    -> GROUP BY classID;

  • WHERE 조건으로 classID가 NULL이 아닌 값만 출력하게 함

    1
    2
    3
    4
    SELECT classID, AVG(score)
    -> FROM students
    -> WHERE classID IS NOT NULL
    -> GROUP BY classID;

평균값이 65 초과만 출력하기

  • 이렇게 할 경우, TABLE 내부에 classID 값이 NULL인 결과도 출력됨

    1
    2
    3
    4
    SELECT classID, AVG(score) AS average
    -> FROM students
    -> GROUP BY classID
    -> HAVING average > 65;

  • WHERE 조건으로 classID가 NULL이 아닌 값만 출력하게 함

    1
    2
    3
    4
    5
    SELECT classID, AVG(score) AS average
    -> FROM students
    -> WHERE classID IS NOT NULL
    -> GROUP BY classID
    -> HAVING average > 65;

CHAR vs VARCHAR

  • CHAR는 고정 길이 문자열 정보

  • VARCHAR는 가변 길이 문자열 정보

  • 저장 측면에서 보면 VARCHAR가 장점

  • 문자열 비교 측면

    • CHAR가 고정 길이 문자열을 사용

      • 주민등록번호와 같이 길이가 일정한 데이터는 CHAR를 사용하는 것이 좋음

    • 이름, 주소 등의 길이가 변할 수 있는 값은 VARCHAR를 사용

INT

  • TINYINT : 1byte

  • SMALLINT : 2bytes

  • INT : 4bytes

  • BIGINT : 8bytes

JOIN(M:N)

  • TABLE 생성 - subjects, student_subject

    • PRIMARY KEY를 선언하지 않음

    • UNIQUE와 NOT NULL이 선언될 경우, PROMARY KEY로 선언됨

      1
      2
      3
      4
      5
      6
      7
      8
      CREATE TABLE subjects(
      -> subjectName CHAR(20) UNIQUE NOT NULL
      -> roomNum TINYINT NOT NULL);

      CREATE TABLE student_subject (
      -> regID SMALLINT AUTO_INCREMENT PRIMARY KEY,
      -> studentName VARCHAR(20) NOT NULL,
      -> subjectName VARCHAR(20) NOT NULL);

  • 테이블 구조 보기

    1
    2
    DESC subjects;
    DESC student_subject;

  • TABLE 데이터 추가

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    INSERT INTO subjects
    -> (subjectName, roomNum)
    -> VALUES
    -> ('math', 101), ('literature', 105),
    -> ('science', 107), ('english', 110), ('ethics', 111);

    INSERT INTO student_subject
    -> (studentName, subjectName)
    -> VALUES
    -> ('Greg', 'english'),
    -> (('Greg', 'ethics'), ('John', 'english'),
    -> (('John', 'literature'), ('Mark', 'english'),
    -> (('Mark', 'literature'),('Mark', 'math'),
    -> (('James', 'science'), ('Johanna', 'english'),
    -> (('Johanna', 'math'), ('Kelly', 'ethics'),
    -> (('Sam', 'english'), ('Daniel', 'math'),
    -> (('Daniel', 'science'),('Daniel', 'ethics'),
    -> (('Ann', 'math'), ('Kreizig', 'math'),
    -> (('Elizabeth', 'literature'),('Elizabeth', 'ethics'),
    -> (('Emilly', 'science'), ('Emilly', 'english'),
    -> (('Lily', 'math');

  • 해당 TABLE의 모든 내용 가져오기(확인)

    1
    2
    SELECT * FROM subjects;
    SELECT * FROM student_subject;

  • students(TABLE 1), student_subject(TABLE 2), subjects(TABLE 3) - JOIN

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    SELECT ST.studentName, ST.score,
    -> SB.subjectName, SB.roomNum
    -> FROM students ST INNER JOIN student_subject SS
    -> ON ST.studentName = SS.studentName
    -> INNER JOIN subject SB
    -> ON SS.subjectName = SB.subjectName
    -> ORDER BY ST.studentName;

    SELECT SB.subjectName, SB.roomNum,
    -> ST.studentName, ST.score
    -> FROM students ST INNER JOIN student_subject SS
    -> ON ST.studentName = SS.studentName
    -> INNER JOIN subjects SB
    -> ON SS.subjectName = SB.subjectName
    -> ORDER BY SB.subjectName;

VIEW

  • view는 select 문일 뿐이지, table이 새로 만들어지는 것은 아니다.

  • 가독성 - 복잡한 쿼리를 단순화해서 사용 가능

  • 보안 - 데이터 접근 제어 가능. 뷰를 통해 접근하기 때문에 뷰에 나타나지 않는 데이터를 보호

  • VIEW 생성

    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE VIEW view_st_sb_join
    -> AS
    -> SELECT ST.studentName, ST.score,
    -> SB.subjectName, SB.roomNum
    -> FROM students ST INNER JOIN student_subject SS
    -> ON ST.studentName=SS.studentName
    -> INNER JOIN subjects SB
    -> ON SS.subjectName=SB.subjectName
    -> ORDER BY ST.studentName;

  • 생성된 VIEW TABLE 확인

    1
    SHOW TABLES;

  • 테이블 내용 확인

    1
    SELECT * FROM view_st_sb_join;

  • VIEW TABLE도 WHERE을 통해 조건에 맞는 데이터 표시 가능

    1
    2
    SELECT * FROM view_st_sb_join
    -> WHERE score BETWEEN 50 AND 70;

  • VIEW TABLE 제거

    1
    DROP VIEW view_st_sb_join;

  • 제거되었는지 확인

    1
    SHOW TABLES;

INDEX

  • 빠른 데이터 검색을 위함(Search)

  • 검색 속도가 빠르지만, 데이터의 수정 및 삽입이 많아지면 성능이 현저하게 떨어짐(page 분할)

  • Clustered Index와 Secondary Index로 나뉨

    • Clustered Index

      • 한 테이블에 하나씩만 가질 수 있음

      • 데이터 자체를 clustered index에 맞춰서 정렬

      • PRIMARY KEY = clustered index

      • PRIMARY KEY가 없는 경우, NOT NULL 옵션의 UNIQUE 인덱스가 PRIMARY KEY가 됨

    • Secondary Index

      • 원하는 만큼 가질 수 있음

      • B-Tree라는 자료구조를 만들어 실제 데이터가 저장되어 있는 테이블에서 참조

      • Where을 자주 쓰는 상황일 때

  • CREATE INDEX 명령어는 Secondary Index만 만들 수 있다.

  • Clustered Index의 수정은 ALTER TABLE 명령어를 사용해야 한다.

  • “AUTO_INCREMENT”가 있는 PRIMARY KEY인 경우, DROP할 수 없다.

    • MODIFY 명령어로 다시 Field 속성을 변경하여 DROP해야 한다.

  • 인덱스 생성

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    CREATE TABLE idx1 (
    -> id INT PRIMARY KEY,
    -> data1 INT,
    -> data2 INT);

    CREATE TABLE idx2 (
    -> id INT PRIMARY KEY,
    -> data1 INT UNIQUE,
    -> data2 INT);

    CREATE TABLE idx3 (
    -> a INT UNIQUE NOT NULL,
    -> b INT UNIQUE,
    -> c INT);

  • 인덱스 확인

    1
    2
    3
    SHOW INDEX FROM idx1;
    SHOW INDEX FROM idx2;
    SHOW INDEX FROM idx3;

  • 테이블 목록 확인

    1
    SHOW TABLES;

  • 인덱스 제거

    1
    DROP TABLE idx1, idx2, idx3;

  • PRIMARY KEY 해제 및 등록

    1
    2
    ALTER TABLE students
    -> DROP PRIMARY KEY;

  • 만약 PROMARY KEY가 등록된 COLUMN이 AUTO_INCREMENT 옵션을 가지고 있는 경우

    • MODIFY를 통해 Field 속성을 변경해주어야 한다.

      1
      2
      ALTER TABLE students
      -> MODIFY COLUMN studentID INT NOT NULL;

  • 변경된 field 속성 확인

    1
    DESC students;

  • 인덱스 확인

    1
    SHOW INDEX FROM students;

  • primary key 등록

    1
    2
    3
    ALTER TABLE students
    -> ADD CONSTRAINT
    -> PRIMARY KEY(studentName);

  • CONSTARAINT의 이름 지정도 가능

    1
    2
    3
    ALTER TABLE students
    -> ADD CONSTRAINT pk_st_studentName
    -> PRIMARY KEY(studentName);

  • 테이블 정보 확인

    1
    SHOW CREATE TABLE students;

  • 인덱스 정보 확인

    1
    SHOW INDEX FROM students;
Share