Database - 1

Database

• 관계형 데이터

• 집합론

• 객체 - table, view, index

• table

  • schema(외부, 내부, 물리)

  • entity, relation

  • 행 : column, field

  • 열 : tuple, row, record

• 제약 조건(Constraint)

  • 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해 규칙을 적용하는 것

  • NOT NULL : 꼭 데이터를 입력해야 함 ex) 아이디, 이름, 생일 등등

  • UNIQUE : 데이터가 중복되는 것을 막음 ex) 아이디

  • PRIMARY KEY : 테이블에서 단 하나. 이 것을 기준으로 데이터 정렬

  • FOREIGN KEY : 테이블에서 다른 테이블의 칼럼을 참조할 때 사용(PRIMARY KEY 참조)

• utf8mb4

  • utf8의 경우 1글자당 3bytes를 사용

  • 4bytes의 이모티콘을 표현할 수 없음

  • 확장된 utf8mb4를 사용하게 됨

  • 1글자 당 4bytes 사용

• INT = 4bytes

• SMALLINT = 2bytes

• String = CHAR, VARCHAR, TEXT

• mysql의 경우, 대소문자를 구분하지 않지만 대문자를 쓰도록 한다.

• typing error가 났을 경우

  • \c를 통해 빠져나온다.

  • ‘로 시작했을 경우, ‘로 닫아주고 다시 \c로 빠져나온다.

• mysql server와 연결

  $ sudo mysql -u root

• 유저 생성

  CREATE USER [username]@[IP] INDENTIFIED BY [password];
  
  # sudo mysql -u jinwook -p 및 password 입력 후 mysql server와 연결

• 유저에게 사용자의 권한 제어 및 모든 테이블에 모든 권한 부여 및 적용

  GRANT ALL PRIVILEGES ON *.* TO [username]@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

• 생성된 데이터베이스 목록을 보여줌

  SHOW DATABASES;

• 데이터베이스 생성

  CREATE DATABASE [database_name] DEFAULT CHARACTER SET utf8mb4;

• 데이터베이스 제거

  DROP DATABASE [database_name]

• “classes” 테이블 생성

  • AUTO_INCREMENT : record의 값이 중복되지 않고 1씩 자동 증가

    CREATE TABLE classes (
    -> classID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> num_students INT NOT NULL);

• “students” 테이블 생성

  • FOREIGN KEY는 classes의 classID 참조

    CREATE TABLE students (
    -> studentID INT AUTO_INCREMNT NOT NULL PRIMARY KEY,
    -> studentName VARCHAR(20) NOT NULL,
    -> height SMALLINT DEFAULT 200,
    -> score SMALLINT NULL,
    -> birthday DATE NOT NULL,
    -> classID INT NULL,
    -> FOREIGN KEY (classID) REFERENCES classes (classID));

• “teachers” 테이블 생성

  • PRIMARY KEY를 뒤에서 지정

    CREATE TABLE teachers (
    -> teacherID INT AUTO_INCREMENT NOT NULL,
    -> subject VARCHAR(30) UNIQUE,
    -> classID INT NULL,
    -> PRIMARY KEY(teacherID),
    -> FOREIGN KEY (clastsID) REFERENCES classes (classID));

• “classes” 테이블의 num_students에 데이터 추가

  INSERT INTO classes
  -> (num_students)
  -> VALUES
  -> (4),
  -> (3),
  -> (3);

• 같은 방법으로 “students” 테이블에 데이터 추가

  INSERT INTO students
  -> (studentName, height, score, birthday, classID)
  -> VALUES
  -> ('Greg', 180, 87, '2002-3-23', 1)
  -> ('John', 175, 95, '2002-4-2', 2),
  -> ('Mark', 178, 50, '2002-5-12', 1),
  -> ('James', 170, 56, '2002-7-14', 3),
  -> ('Johanna', 165, 90, '2001-9-23', 1),
  -> ('Mary', 160, 50, '2002-1-5', 2),
  -> ('Kelly', 176, 80, '2002-9-17', 3),
  -> ('Sam', 172, 78, '2002-6-5', 1),
  -> ('Daniel', 187, 90, '2002-12-1', 2),
  -> ('Ann', 165, 46, '2002-9-18', 3);

• ALTER 명령어를 통해 “teachers” 테이블에 column 항목 추가

  ALTER TABLE teachers
  -> ADD COLUMN
  -> teacherName VARCHAR(20) NOT NULL AFTER teacherID;

• 같은 방법으로 “teachers” 테이블에 데이터 추가

  INSERT INTO teachers
  -> (teacherName, subject, classID)
  -> VALUES
  -> ('yang', 'math', 1),
  -> ('park', 'literature', 2),
  -> ('lee', 'science', 3);

• 데이터베이스 백업

  mysqldump -u [username] -p --databases [database_name] > [save_filename.sql]

• 데이터베이스 복원

  • mysql -u [username]] -p로 연결

  • mariaDB [(none)]> source [save_filename.sql] 실행

• 테이블 구조 확인

  DESC [table_name]

• 테이블 컬럼 속성 변경

  ALTER TABLE students
  -> MODIFY COLUMN height SMALLINT NULL;

• 데이터 추가

  • 테이블 컬럼 값을 모두 추가하지 않아도 기본값으로 NULL 값 추가

    INSERT INTO students
    -> (studentName, height, score, birthday)
    -> VALUES ('Kreizig', 175, 75, '2002-9-19'),
    -> ('Elizabeth', 165, 80, '2003-1-17');

• 데이터베이스 복사

  • 괄호로 묶은 문법이 바로 subquery

    CREATE TABLE student_cp
    -> (SELECT * FROM students);

• 테이블 데이터 제거

  • %eiz%는 eiz가 포함되어있는 데이터를 지움

    DELETE FROM student_cp
    -> WHERE studentName like '%eiz%';
    
    또는
    
    TRUNCATE TABLE student_cp;

• 데이터의 이름이 정확하게 기억나지 않는 경우, %나 , _를 통해 검색 가능

  SELECT [column_name] FROM [table\_name] WHERE [column\_name] LIKE [특정문자열%]

• AND도 사용 가능

  INSERT INTO student_cp
  -> (SELECT * FROM students WHERE birthday
  -> BETWEEN '2002-5-1' AND '2002-9-10');

• 테이블 제거

  DROP TABLE student_cp;

DDL and DML and DCL

• DDL(Data Definition Language)

  • 데이터 정의어

  • 데이터 구조를 정의하는데 사용되는 명령어

  • CREATE, ALTER, DROP, RENAME, TRUNCATE

• DML(Data Manipulation Language)

  • 데이터 조작어

  • 데이터베이스에 들어있는 데이터를 조회하거나 변형을 가하는 명령어

  • SELECT, INSERT, UPDATE, DELETE

• DCL(Data Control Language)

  • 데이터 제어어

  • 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어

  • GRANT, REVOKE

session and transaction

session

• sudo mysql -u root 실행 = mysql server로 연결 = TCP connection = session

transaction

• mysql server로 연결되는 client가 여러 군데인 상황이라면…

  • 하나의 table에 여러 client가 접근하게 됨

  • table은 공유 자원(shard resource)가 됨

• 은행 계좌 시스템(예시)

  • client 1 계좌에서 client 2 계좌로 10000원을 송금한다고 하자.

  • client 1 계좌에서 10000원을 감산하는 처리

  • client 2 계좌에서는 10000원을 가산하는 처리

  • 그런데 client 2의 계좌에서 10000원을 가산하는 과정에서 오류가 발생한다면?

    • 가산 처리 과정은 이루어지지 않은 채, 감산 처리 과정만 일어나게 됨

  • 이러한 상황 때문에 감산과 가산의 과정을 하나의 처리로 취급한다면?

    • 가산이 실패하면 감산도 취소하도록 함

  • 이렇게 여러 단계의 처리를 하나의 처리처럼 다루는 기능을 Transaction이라 한다.

• Transaction이 서버에서 진행 중인 경우, 외부 세션에서는 아직 변경한 데이터가 보이지 않는다.

• Transaction 상태에서는 작업 중인 영역이 LOCK 상태가 된다.

  • commit을 진행할 때에는 반드시 다른 개발자들에게 알려야 한다.

• DML은 자동으로 commit이 되지 않고, DDL은 자동으로 commit이 된다.

• DML 명령어는 사용 후…

  • commit이라는 명령어를 통해 Transaction을 끝내면 데이터 상태를 최종 변경

  • commit을 하기 전에 rollback 명령어를 통해 변경 전의 데이터 상태로 돌아가고 Transaction을 종료 가능

• DDL은 Transaction의 개념이 없기 때문에 명령어를 사용하는 데 있어서 주의해야 한다.

• AUTOCOMMIT 여부를 확인

  SELECT @@AUTOCOMMIT;

• AUTOCOMMIT 해제

  SET AUTOCOMMIT = FALSE