2019.04.09 TIL

(TIL은 스스로 이해한 것을 바탕으로 정리한 것으로 오류가 있을 수 있습니다)

# 질문에 답하기

  1. 데이터 베이스

mysql은 관계형 데이터 베이스이다.
관계형 데이터 베이스 시스템들은 SQL 문법을 사용한다.

  • nosql : 빅데이터와 같이 큰 데이터가 늘어나면서 관계형 데이터가 한계가 있다고 생각

DATABASE SERVER

server이기 때문에 앞으로 들어나지 않는다.
database server밑에서 여러개의 database가 있을 수 있고
database 밑에서는 여러개의 table이 있다.
그 안에 DATABASE가 있다.

database

  1. oracle이 1위 회사이다.
  2. 중소기업은 mysql —> php와 너무 잘 맞다. APM(apach, php, mysql)
  3. MariaDB
    django
  4. postgreSQL —> open source

database는 table이라는 것을 카테고라이징 한 것이다.

  • table은 entity 혹은 relation이라고 부른다.
  • 열은 column 혹은 field라고 부른다.
  • 행은 row 혹은 tuple이라고 부른다.(파이썬의 자료형 tuple과 동일하다)
  • 또한 이 row 한 줄을 recode라고 한다.
  • 레코드는 구체적인 데이터를 이야기한다.

기본적으로 행과 열을 통해서 데이터베이스에 접근한다.

DATABASE CLIENT

mysql-client
phpmyadmin
mysql query browser
navicat

  • 참고사항
  • utf-8 mb4 (unicode utf-8)의 의미
    • a,b,c —> 1byte
    • 한글 —> 3byte
    • 어차피 3바이트까지 하면 되는데 굳이 4바이트까지 써야하나?
    • 따라서 자체적으로 3바이트로 만들었는데
    • 이모티콘이 나오면서 4바이트로 바꿈(utf-8 mb4)

시작하기

students

  • student ID - int, primary key(테이블에서 오직 하나)
  • height
  • score
  • birthday
  • class ID - int, NULL

teacher

  • teacher - int, primary key
  • subject - varchar(가변)/ not null
  • class id - int, NULL

server start

mysql.server start
mysql -u root

  • mysql -u root ===> connection이 연결 TCP 연결(socket으로 연결이 둟린 것이다.)
  • 이것을 session이라고 한다. TCP 연결을 했다.
  • client는 여려명일 수 있다. john, greg 등
  • 쓰레드의 가장 큰 문제점 : 레이스 컨디션
    • 여러명이 접속하게 되면 table이 shared resource가 된다. (공유자원)
    • updata문을 통해서 balance 값을 5000원에서 3000원으로 바꾸면서 끊김
    • 무결점을 보장해줘야한다.
    • 그렇기 때문에 session을 연 다음에 update를 하는 순간 그 쓰레드에 대해서만 복사해서 준다.
    • 트랜젝션을 업데이트를 한 다음에 commit을 통해 트랜젝션을 끝내고 데이터 베이스를 바꿈.
    • commit전에는 절대 데이터 베이스는 바뀌지 않는다. 무결성
    • 트랜젝션과 세션의 차이를 잘 알아야 한다.
    • Do it! 오라클로 배우는 데이터베이스 입문 - 이지훈 - Google 도서

유저확인

SELECT USER, HOST FROM USERS;

유저 생성 및 비밀번호 설정

CREATE USER “byeonguk”@“localhost” IDENTIFIED BY "1234";

권한 주기

GRANT ALL PRIVILEGES ON *.* TO "byeonguk"@"localhost" with grant option;

권한 적용

FLUSH privileges

권한 확인

SELECT USER, HOST, SUPER_PRIV FROM MySQL.USER;

데이터 삭제 혹은 테이블 삭제

DROP DATABASE mydb;
DROP TABLE 테이블명

테이블에 대한 정보 확인

SHOW tables

  • 테이블 리스트 확인
    SHOW CREATE table "테이블 명"
  • 해당 테이블에 대한 정보
    DESC "테이블 명";
  • 해당 테이블 스키마 열람
  • 스키마란 : 테이블에 적재될 데이터의 구조와 형식을 정의 하는 것
    SELECT * FROM "테이블명"
  • 우리가 찾던 그 정보확인

테이블 생성

CREATE TABLE students(
-> student ID INT AUTO_INCREMENT, 
-> studentName VARCHAR(20)  #학생의 이름이 20바이트까지 가능
-> height SMALLINT DEFAULT 200,
-> score, SMALLINT NULL,
-> birtyday DATE NOT NULL,
-> classID INT NULL,
-> PRIMARY KEY(studentID),                    # 오직 한개만 있다
-> FOREIGN KEY(classID), REFERENCES classes(classID)
  • classID라는 외래키 참조하는데 classes 열에서 classID를 참조한다.
  • 잘못되었을 때 \c를 하면 빠져나올 수 있다.
CREATE TABLE teachers (
->teacherID INT AUTO_INCREMENT NOT NULL,
->subject VARCHAR(30) UNIQUE,
->classID INT NULL,
->PRIMARY KEY(teacherID),
->CONSTRAINT fk_classID
->FOREIGN KEY (clastsID) REFERENCES classes (classID)
);

데이터 삽입

INSERT INTO students 
-> (studentName, height, scores, birthday, classID)
-> VALUES
-> (‘GREG’, 180, 87, ‘2002-3-23’, 1)

칼럼(열)더하기

DESC teacher;        #직관적으로 현 상황을 볼 수 있음

ALTER TABLE teachers
-> ADD COLUMN teacherName ;

SHOW CREATE TABLE teacher;          #해당 테이블에 대한 정보 제공(create할 때의 정보)

백업하기

mysqldump -u root -p —databases 
mydb > mydb1.sql
#mydb1.sql이 생김

카피하기

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

SELECT * FROM student_cp  #잘 복사되었는지 확인

데이터 업데이트하기

UPDATA student_cp
-> SET score = 100
-> WHERE studentName = ‘Mary’;             #Mary를 해주지 않으면 전부 다 100으로

메리 점수만 확인하기

SELECT studentName, score FROM student_cp
->WHERE studentName like ‘Mary’;
# WHERE studentName like "Ma%"; 
# %는 어떤 글자든지 다 소화시켜준다.

데이터 베이스에 조건문 넣기 (50점 이상 70점 이하 점수 보기)

SELECT studentName, score
-> FROM student_cp
-> WHERE score > 50 and score < 70

특정 데이터 지우기

Delete from student_cp
-> WHERE studentName like “Mary’;

SELECT * FROM student_cp (확인)

foreinkey 없애기

foreinkey를 없애려면
자동으로 인덱스를 잡기 때문에

폴인키 제약조건을 없앤 다음에 쇼 인덱스를 해준 다음에 잡혀있는 인덱스를 없애준다.

1)
ALTER TABLE teachers
-> DROP FOREIGN KEY teachers;

2)
DROP INDEX classID
-> ON teachers;

SHOW CREATE TABLE teachers      #확인

JOIN

innerouter

join은 테이블을 가로로 연결할 때 쓴다.

  • inner join
  • outer join
    • left outer join
    • right outer join
    • full outer join
      • mysql, mariadb는 full outer을 지원하지 않는다.
      • 필요할 때 union을 통해 구현한다.

MySQL full outer join : 네이버 블로그

INNER JOIN

ex) join 예시
student name / score / class id / teacher name / subject
==> class id로 묶는다.( class id에 대한 교집합)

NULL을 어떻게 처리할 것인가?

  • inner은 빠진다.

if select studentname, classid, teacher 이렇게 하면 classid가 누구것인지 알수가 없다.

S.를 붙여 student 표현 T.를 붙여 teacher 을 표현

  • 적용 방법
    • SELECT S.studentsName,S.score, S.classID, T.teacherName, T.subject
    • FROM students S INNER JOIN teachers T (student가 left teacher가 right)
    • ON S.classID = T.classID
    • ORDER BY S.classID

LEFT OUTER JOIN ( 왼쪽에 위치한 집합은 다 나온다.)

SELECT S.studentName, S.score, S.classID,
    -> T.teacherName, T.subject
    -> FROM students S LEFT OUTER JOIN teachers T
    -> ON S.classID = T.classID

RIGHT OUTER JOIN (오른쪽에 위치한 집합은 다 나온다.)

SELECT S.studentName, S.score, S.classID
    -> T.teacherName, T.subject
    -> FROM students S RIGHT OUTER JOIN teachers T
    -> ON S.classID = T.classID;

FULL OUTER JOIN

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

  • 전체 평균
    • SELECT AVG(score) FROM students;
  • 반별로 평균을 알고 싶다.
    • SELECT classID, AVG(score)
    • -> FROM students
    • -> GROUP BY classID
  • 만약에 반 배정 안 받는 애들은 빼고 싶다.
    • SELECT classID, AVG(score)
    • -> FROM students
    • -> WHERE classID IS NOT NULL
    • -> GROUP BY classID;
  • 평균 65점 이상인 반을 본다.
    • SELECT classID, AVG(score)
    • -> FROM students
    • -> WHERE classID IS NOT NULL
    • -> GROUP BY classID
    • -> HAVING AVG(scores) > 65;

WHERE 와 GROUP BY, HAVING

WHERE와 ==> 그룹을 하기 전에 조건절 수행
GROUP BY
HAVING의 차이 ==> 이미 그룹바이로 그룹이 끝난 이후에 조건절 수행(반듯이 그룹을 한 것에 대해서만 조건절을 수행한다)

M : N

  • INNER JOIN을 2개 사용한다.
    ex) Student와 subject와의 관계

  • VARCHAR 와 CHAR의 차이 : 비트를 고정해줄 것인가? 아니면 유동적으로 할 것이냐?

CREATE TABLE subjects(
    -> subjectName CHAR(20) UNIQUE NOT NULL,     #unique not null은 프라이머리 key가 없으면 생성한다.
    -> roomNum TINYINT NOT NULL);
INSERT INTO subjects 
(subjectName, roomNum) 
VALUES ('math', 101), ('literature', 105), 
('science', 107), ('english', 110), ('ethics', 111);

학생 입장에서 subject name + 강의실 번호까지

sql> 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;

과목 입장에서 배열

sql> 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;

SELECT의 순서가 바뀌는 것은 크게 의미가 없고
마지막에 ORDER BY에 따라서 정렬 순서를 정해준다.

SQL

  • VIEW
  • INDEX
    • CLUSTERED INDEX
    • SECONDARY INDEX

VIEW

VIEW는 select문이다.
view를 마치 테이블처럼 사용한다.

  1. view는 select문일 뿐이지 테이블이 새로 만들어지는 것이 아니다.
  2. 가독성이 높아진다.
  3. 보안문제 해결

ex) 쇼핑몰
customer / 생년 월일 /

  • 알바생에서 customer 테이블을 그대로 보여주면 안되니깐 view(생년월일이 없는)를 하나 만들고 권한을 부여(GRANT)

CREATE VIEW

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;

WHERE 적용 가능

SELECT * FROM view_st_sb_join;

sql> SELECT * FROM view_st_sb_join 
WHERE score BETWEEN 50 AND 70;

DROP VIEW 적용 가능

DROP VIEW view_st_sb_join;

INDEX

index를 쓰는 이유 <-- 데이터 검색

Tree BST --> o(log n)
단점 보완 -> red - black - tree (균형 이진 트리) 추가 단점 보완 => B-tree

  • CLUSTERED INDEX

    • 한 테이블마다 1개씩만 존재한다.
    • 데이터 자체를 클러스트 인덱스에 맞추어서 정렬한다.
    • primary key가 클러스트 인덱스가 된다.
    • primary key를 안 잡고 데이터를 넣은 뒤 primary key를 잡으면 나중에 굉장히 정렬하는 시간이 오래 걸릴 수 있다. ==> 항상 미리 잡고 시작하자.

  • SECONDARY INDEX

    • 원하는만큼 붙일 수 있다.
    • B-트리라는 자료구조를 만들고 실제 데이터를 참조해서 만든다.
      • where 에 쓰인다. ex) where classID = 3
      • secondary index를 언제만드냐?
        • where가 자주 쓰일 때 만들어 놓으면 search를 할 때 secondary index를 활용하여 서치
        • 쓰는 이유 : 검색 속도가 엄청나게 빨라진다.
        • 단점 : 테이블에 대해 insert를 계속하고 업데이트를 계속한다고 하면 계속 B-tree도 업데이트 시켜줘야하고, 수정, 삽입 등이 많이 늘어나면 페이지 분할이 일어나서 속도가 늦어질 수 있다.
        • where을 내가 얼마나 쓸 것인지 파악하고, 내가 수정 삽입 등을 얼마나 할 것인지 고민
    • CREATE INDEX는 secondary index만 만들 수 있다.
    • cluster int를 만들기 위해서는 ALTER TABLE이라는 커맨드를 써야한다.

참고사항

내부 스키마 외부 스키마 개념 스키마
[DB기초] 스키마란 무엇인가?

저작자표시

'자료구조' 카테고리의 다른 글

자료구조 07. 원형큐(circular queue)  (0) 2019.12.29
자료구조 06. 선형구조, 비선형구조  (0) 2019.12.29
자료구조 04. linked_list  (0) 2019.12.29
자료구조 03. Stack으로 Queue구현하기  (1) 2019.12.29
자료구조 02. Stack과 queue  (0) 2019.12.29

+ Recent posts

티스토리툴바