CS_DATABASE

DB(Database)

• DB는 상이한 시스템에서 데이터를 공용하여 사용하기 위해 필요
• 겹친 데이터를 통합하여 관리해 정보를 구조적, 효율적으로 저장
• 한마디로 데이터의 집합
• 실생활의 대부분이 정보가 저장되고 관리됨
  • 카톡 메시지
  • 교통카드 내역
  • 카페 이용 내역

DBMS( Database Management System )

• 데이터베이스를 관리하고 운영하는 소프트웨어
• 여러명의 사용자나 응용프로그램과 공유하고 동시 접근 가능해야 함
• DBMS 종류
  
  • 계층형 DBMS
  • 망형 DBMS
  • 관계형 DBMS
    • RDBMS
    • 테이블이라는 최소 단위로 구성되고, 테이블은 하나 이상의 열과 행으로 이뤄짐

SQL

• 데이터를 정해진 틀에 따라 테이블에 저장
• 장점
  • 명확하게 정의된 스키마, 데이터 무결성 보장
  • 관계는 각 데이터 중복업싱 한번만 저장
• 단점
  • 덜 유연
  • 데이터 스키마를 사전에 계획하고 알려야함 ( 수정 어려움 )
  • 관계를 맺고 있으므로 조인문이 많은 쿼리 만들어질 수 있음
  • 대체로 수직적 확장만 가능
  • MySQL, PostgreSQL, Oracle ...

NoSQL

• SQL보다 데이터를 자유롭게 다양한 형태로 저장
• 정해진 구조나, 관계 없음
• 장점
  • 스키마가 없어서 유연
  • 데이터 조정, 새로운 필드 추가 가능
  • 데이터 읽어오는 속도 빨라짐
  • 수직, 수평 확장이 가능해서 읽기, 쓰기 요청 처리 가능
• 단점
  • 유연성으로 인해 데이터 구조 결정 미루게 될 수 있음
  • 데이터 중복을 계속 업데이트
  • 여러 컬렉션에 중복되어 있기 때문에 수정시 모든 컬렉션 수행해야 함
    ( SQL은 중복 데이터가 없으므로 한번만 수행 )

DB Naming Convention

기본

• 소문자 사용
• 숫자 no
• 64자 미만
• prefix 사용 no

db 이름

• 단수형
• 가능한 한 prefix 피하기

table 이름

• 소문자 사용
• 단수형
• prefix 사용

필드 이름 (컬럼명)

• 소문자
• 최대 2단어
• 단어 거꾸로 no
• 컬럼명과 테이블 이름 동일하게 no
• 외래키 사용

---

# mysql 접속 (커맨드창)
mysql -uroot -p
> 비밀번호 입력

# 데이터베이스 확인
SHOW DATABASES;

# mysql 데이터 베이스 
USE mysql; 

# 선택된 데이터베이스 확인
SELECT DATABASE();

# 현재 유저 확인
SELECT USER();

# user 테이블 살펴보기
SELECT Host, `User` FROM USER; 

# sparta 계정 삭제 (sparta 계정 있을시)
DROP USER sparta@localhost;

# 사용자 추가
CREATE USER 'sparta'@'localhost' identified BY '12345678';

# 패스워드 정책 에러시(mysql8)
set global validate_password.policy=LOW;

# 데이터 베이스 생성
CREATE DATABASE cs_special_lecture;  

# 데이터 베이스 확인
SHOW DATABASES;

# 특정 데이터베이스의 모든 테이블에 모든 권한을 줌
grant all privileges on cs_special_lecture.* to 'sparta'@'localhost';


# 권한 반영(변경된 내용을 메모리에 반영)
FLUSH PRIVILEGES;


# 유저 변경 
# 유저 변경 (cli 환경에서 입력, dbeaver 에서는 Edit Connection 설정)
mysql -usparta -p12345678;


# 현재 유저 확인
SELECT USER();


# 현재 데터베이 확인
SELECT DATABASE();


# 데이터베이스 선택
USE cs_special_lecture;


-- 테이블 생성
-- 도시 테이블 생성
CREATE TABLE city(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(50) NOT NULL 
);


-- 학생테이블 생성
CREATE TABLE student(
    id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(50) NOT NULL,
    email varchar(50) UNIQUE,
    english_grade int,
    math_grade int,
    city_id int, 
    FOREIGN KEY (city_id)
    REFERENCES city(id) ON UPDATE CASCADE
)


-- 데이터 삽
-- 도시 데이터 삽입
INSERT INTO
    city (name)
VALUES
    ('서울'),
    ('부산'),
    ('인천'),
    ('뉴욕'),    
    ('도쿄')           

    
-- 학생 데이터 삽입 
INSERT INTO 
    student (name, email, english_grade, math_grade, city_id)
VALUES
  ("정영훈", "jyha@gmail.com"      ,  70, 70  , 1),
  ("이재관", "leejaekwan@naver.com",  70, 70  , 2),
  ("홍길동", "hjd@naver.com"       ,  80, NULL, 3),
  ("김민수", NULL                  ,  90, 90  , 3),
  ("한동주", NULL                  ,  90, 90  , 5),
  ("우태현", NULL                  ,  70, 70  , 5)
  
  
-- 데이터 출
-- 학생 데이터 모든 컬 출력
SELECT * FROM student s; 


-- email이 jyha@gmail.com 인 학생 이름, 이메일 출력
SELECT name, email  
FROM student s 
WHERE email = "jyha@gmail.com";


-- email이 jyha@gmail.com 이 아닌 학생들 이름, 이메일 출력
SELECT name, email  
FROM student s 
WHERE email != "jyha@gmail.com";


-- email이 없는 학생들 이름만 출력(1)
SELECT name 
FROM student s 
WHERE email != NULL;


-- email이 없는 학생들 이름만 출력(2)
SELECT name 
FROM student s 
WHERE email is NULL;


-- email이 네이버 사용 하는 학생 모든 컬럼 출력 (MySQL Wildcards)
SELECT *
FROM student s 
WHERE email LIKE '%@naver%';


-- email컬럼에 @ 앞 글자가 3글자인 학생 모든 컬럼 출 (MySQL Wildcards)
SELECT *
FROM student s 
WHERE email LIKE '___@%';


-- 학생테이블 데이터 삭제
-- id가 6인 학생 데이터 삭제
DELETE FROM student 
WHERE id = 6;


-- 학생 데이터 모두 삭제
DELETE FROM student 


-- 학생 데이터 삽입 
INSERT INTO 
    student (name, email, english_grade, math_grade, city_id)
VALUES
  ("정영훈", "jyha@gmail.com"      ,  70, 70  , 1),
  ("이재관", "leejaekwan@naver.com",  70, 70  , 2),
  ("홍길동", "hjd@naver.com"       ,  80, NULL, 3),
  ("김민수", NULL                  ,  90, 90  , 3),
  ("한동주", NULL                  ,  90, 90  , 5),
  ("우태현", NULL                  ,  70, 70  , 5)


-- 통계함수
-- 영어, 수학 점수 총점 출력
SELECT 
    SUM(english_grade) AS total_english_grade,
    SUM(math_grade) AS total_math_grade  
FROM student s 

-- city_id그룹별 영어, 수학 점수 총점 출력
SELECT 
    city_id,
    SUM(english_grade) AS total_english_grade,
    SUM(math_grade) AS total_math_grade  
FROM student s 
GROUP BY city_id

-- city_id그룹이 3 영어, 수학 점수 총점 출력
SELECT 
    city_id,
    SUM(english_grade) AS total_english_grade,
    SUM(math_grade) AS total_math_grade  
FROM student s 
GROUP BY city_id
HAVING city_id = 3

-- 영어, 수학 점수 평균 출력
SELECT 
    AVG(english_grade) AS english_grade_avg,
    AVG(math_grade) AS math_grade_avg,  
    SUM(math_grade) / COUNT(*) AS math_grade_avg  
FROM student s 

SELECT 390 / 5;

SELECT 390 / 6;


-- LEFT JOIN
-- 학생 테이블에서 학생이 살고 있는 도시이름 출력
-- 도시 번호는 나오나 도시 이름이 출력 X
SELECT * FROM student s;


-- LEFT JOIN 으로 student, city 테이블을 합쳐줌 조건은 student.city_id = city.id
SELECT *
FROM student s 
LEFT JOIN city c ON s.city_id = c.id;


-- DB DUMP (커맨드창에서)
mysqldump -uroot -p12345678 cs_special_lecture > cs_special_lecture.sql

-- DB RESTORE (커맨드창에서)
mysql -uroot -p12345678 test < cs_special_lecture.sql

---

ERD (Entity-Relation Diagram: 개체 관계도)

• ER 모델(개체-관계 모델, Entity-Relationship Model)
• 1:1 / 1:n / n:m

ERD표준 기호

---

정규화 연습 사이트

제약 조건