[새싹X코딩온]웹 풀스택 6주차(8/23)회고록|데이터베이스, MYSQL, SQL문법

1. 데이터베이스 개요

1) database VS file system

지금까지는 우리가 input에 입력을 해도 f5를 누르면 value가 사라졌었죠..

이제는 데이터 베이스를 연결해서 사라지지 않도록 해볼겁니다!

 

우선 데이터 베이스가 뭔지부터 알아야 겠죠?

데이터베이스란? 데이터를 저장하는 구조/자료의 모음을말합니다.

 

우리는 자료를 저장할 떄 주로 microsoft exel에 표 형태로 데이터릉 저장했었죠?

 

그런 웹사이트에서는 파일을 통해서 데이터를저장하지 않고 왜 데이터베이스를 사용할까요?

바로 파일 시스템의 단점 때문입니다. 

파일시스템

•단점1. 데이터 중복이 생길 수 있다.

•단점2. 데이터 공유에 있어서 제한이 존재한다.

•단점3. 구현과 유지보수에 시간이 오래 걸린다.

이러한 단점이 있습니다. 

예를 들어서, 고객 구매정보가 담긴파일이 있고, 고객의 정보가 담긴 파일이 존재한다고 했을 때, 고객이 개명을 했다고 해 봅시다! 그럼 고객의 정보파일에서 그 고객의 이름은 바뀌겠지만 고객 구매 정보에서 고객의 이름은 바뀌지 않아서 본질적으로 한 사람이 두 사람인 것 처럼 표에서는 보이게 되겠죠? 이렇게 파일 간에 동기화가 되지 않아서 불편함이 있을 수 있습니다! 

그래서!!!

우리는 이제부터 이런 데이터들에 대해서 파일시스템 대신에 데이터베이스를 사용해서 데이터를 관리할 겁니다!

 

2) DBMS :Data Base Management System

데이터 베이스에 접근하고 이를 관리하기 위해 존재합니다. 즉, 사용자와 데이터베이스를 연결시켜주는 소프트웨어로, 데이터베이스 사용자가 데이터베으스를 생성, 공유 관리할수 있도록 하는 역할을 합니다!

그 중에서도 관계가 추가 된것을 RDBMS라고 하는데요! 

RDBMS: "Relational" + DBMS 

그 중에서도 우리는 MySQL이라는 것을 써보도록 할거에요!  MySQL의 특징은 운영체제마다 달라지는 것이 별로 없고, 다야한 운영체제를 지원하며, 무엇보다 무료라고 합니다!

3) DataBase 용어

<데이터 베이스 구조>

-열(column, Attribute, 속성)

-행(Record, Tuple, 튜플)

-테이블(Table, Relation)

 

-키(key): 데이터 베이스에서 튜플(행)을 찾거나 정렬할 때 구분하고 정렬의 기준이 되는 속성입니다(이 테이블에서는 학번, 이름, 학과명, 성별이 key가 될 수 있겠죠?)

 

-기본키(PK, PRIMARY KEY):메인 키로 한 테이블에서 특정 튜플(행을 유일하게 구별할 수 있는 속성)

 -> 기본키의 특성은 NULL값, 중복값이 불가능하다는 것 입니다!

 ⚠️기본 키 선정시 고려사항

• 테이블 내 튜플을 식별할 수 있는 고유한 값을 가져야 함(중복X)
• NULL값 허용 안됨(식별해야하기 때문)
• 키 값의 변동이 일어나지 않아야 함(전화번호 같은 건 기본키가 될 수 없다!)
• 최대한 적은 수의 속성을 가진 것이어야 함(예를 들어서 두개의 속성으로 하나의 행을 추출할 수 있고, 하나의 속성으로도 하나의 행을 추출할 수 있다면 하나의 행으로 추출하는것이 더 효율적이다~)
• 향후 키를 사용하는데 문제 발생 소지가 없어야 함(예를 들어서 주민번호를 주키로 사용하게되면 추후에 개인정보관련해서 문제가 발생할 수 있기 때문에 주키로 사용할 수 없습니다!)

-외래키(FK, Foreign Key): 어떤 테이블의 기본키를 참조하는 속성

 -> 외래키가 있기 때문에 테이블간 관계를 맺을 수 있습니다! 

⭐외래키 특징

• 다른 테이블의 기본키를 참조하는 속성
• 참조하고(외래키) 참조되는(기본키) 양쪽 테이블 도메인은 서로 같아야 함 (여기에서 도메인은 서버에서 배운것과는 다른 개념으로, '속성이 가질 수 있는 값의 집합'이라는 의미 입니다!)
• 참조한다? 다른 테이블의 기본 키를 바라본다!
• NULL값과 중복값 허용
• 자기자신의 기본키를 참조하는 외래키기능

---

2. MySQL설치

다음으로는 My SQL을 설치해 보았습니다! 

저는 Window운영체제를 사용해서 쉽게 설치했는데 MAC은 어려움이 많은 것 같더라구요... 버전을 낮춰서설치하기도 했습니다..

 아! 그리고 설치이후에는 My SQL을 로컬에서도 실행할 수 있었는데요.. 

이런식으로 할 수 있는데 저장되 안되고 이것저것 불편함이 많아서 MySQL workbench라는 걸 사용합니다!

---

3. MySQL을 사용해서 SQL배우기! 

이제 오늘 수업의 핵심이라고 볼 수 있었던 MySQL을 사용해서 데이터베이스를 만들고 그 안에 표를 만들고 표 안에 값들을 넣고 이것저것 해 보도록 하겠습니다!

아 그리고 VSCODE에서 MySQL을 사용하고 싶다면

이 확장 프로그램을 설치해서 해볼 수 있어요!

자 그럼 데이터 베이스 어떻게 생성하고 관리하는지 알아보도록 할게요! 

우선 표, 데이터베이스 이런것들을 아무런 순서 없이 만들 수는 없어요~~

데이터 베이스가 존재해야 테이블을 만들 수 있고, 테이블이 있어야 그 안에 값들을 넣을 수 있습니다! 

그래서! 데이터 베이스 구축단계는 다음과 같습니다!

 

데이터베이스를 만들어 보기 전에 SQL이 뭔지정도는 알고 있어야 겠죠? 

SQL은 구조적 쿼리 언어로 관계형 데이터 베이스에 정보를 저장하고 처리하기 위한 프로그래밍 언어입니다. 다른 프로그래밍 언어와의 가장 큰 차이점은 실행 순서가 없는 비절차적인 언어라는 점 입니다!!!

 

SQL은 기능에 따라서

• 데이터 정의어(DDL)
• 데이터 조작어(DML)
• 데이터 제어어(DCL)

이렇게 세가지로 분류해 볼 수 있습니다!

그럼 하나하나 살펴보고 코드도 작성해봐야겠죠?

1) 데이터 정의어(DDL:Data Definition Language)

-CREATE문 : 데이터베이스와 테이블을 생성하는 명령어

• 테이블 이름, 열 이름, 데이터 형식등을 지정
• 기본키 , 외래키 정의

create database sesac default character set utf8 collate utf8_general_ci;

이건 sesac이라는 데이터 베이스를 생성한 것이고

CREATE TABLE product (

id INT PRIMARY KEY NOT NULL auto_increment,

name VARCHAR(50) NOT NULL,

model_number VARCHAR(50) NOT NULL,

series VARCHAR(30) NOT NULL

);

 

이건 product라는 테이블을 생성한 것입니다. 

생성한 데이터 베이스는 

show databases;

을 통해서 확인해 볼 수 있고

생성한 테이블은

SHOW TABLES;

을 통해서 확인 할 수 있습니다.(현재 사용중인 데이터 베이스의 모든 테이블을 조회할 수 있습니다)

아 그런데 중요한건 이거 실행하기전에 꼭 사용 선언을 해 주어야 합니다!! 안그러면 목록에서 확인할 수 없어요~~

use sesac;

테이블 구조확인

DESC product;

테이블 삭제

DROP TABLE product;

truncate table product;

drop과 truncate의 차이는 drop은 테이블 존재 자체를 없애는 것이고, truncate은 테이블 구조만 남겨놓고 테이블의 모든 행을 일괄 삭제하는 거에요!

 

테이블 행 추가

ALTER TABLE product ADD new_column date;

테이블 행 수정

ALTER TABLE product modify new_column INT;

ALter table product change new_column new_column2 int;--

2) 데이터 조작어(DML)

desc customer;

CREATE TABLE customer(

custid char(10) NOT NULL primary KEY,

custname VARCHAR(10) NOT NULL,

addr CHAR(10) NOT NULL,

phone CHAR(11),

birth date

);

-- 여기에서 sesac이라는 database를 ddl파일에 생성했더라도 ddl폴더에서 create database sesac~하고 use sesac후에 create customer하고 desc customer하면 생성된 table 확인할 수 있음

-- 외래키 제약 조건

-- 두 테이블 사이의 관계를 맺음(RDBMS)

-- 다른 테이블의 기본키를 외래키로 연결

-- 기준 테이블: 기본 키를 갖는 테이블(여기에서는 customer)

-- 참조 테이블: 외래 키가 있는 테이블(여기에서는 orders)

-- 형식: foreign key (열_이름) references 기준_테이블(열_이름)

-- on update cascade: 기준 테이블의 데이터가 변경되면 참조테이블의 데이터도 변경

-- on delete cascade: 기준 테이블의 데이터가 삭제되면 참조테이블의 데이터도 삭제

CREATE TABLE orders (

orderid INT PRIMARY KEY AUTO_INCREMENT,

custid CHAR(10) NOT NULL, -- 외래키로 사용하고 싶다

prodname char(6) NOT NULL,

price INT NOT NULL,

amount SMALLINT NOT NULL,

FOREIGN KEY (custid) REFERENCES customer(custid) ON UPDATE CASCADE ON DELETE CASCADE

-- orders의 custid 키(외래키)가 customer의 custid 키를 바라보도록 한다

);

-- 테이블 삭제 순서!

-- customer 테이블과 orders 테이블이 customercustid, 를 기준으로 "관계"를 맺음

-- customer 테이블에 존재하는 회원만 orders 테이블에 데이터를 추가할 수 있음

-- 만약에 orders 테이블이 있는데, customer 테이블을 삭제 (drop)하면?

-- orders테이블은 어떤 고객의 생일 정보를 알고싶어도 customer테이블 자체가 날라갔기 때문에 알 수 없음.

-- pk-fk 연결된 테이블은 외래키가 설정된 테이블(참조테이블)을 먼저 삭제해야한다

-- 그래서 (1)orders 테이블을 삭제하고 -> (2)customer 테이블을 삭제해야한다.

-- INSERT: 추가

-- INSERT 방법 1)

INSERT INTO customer (custid, custname, addr, phone, birth) VALUES ('lucky', '강해원','미국 뉴욕','01022223333','2022-03-05');

insert into customer (addr, phone, birth, custid, custname)

    values ('대한민국 부산', '01098765432', '2007-04-28', 'wow', '이지은');

   

-- 속성을 지정하지 않는다면, column이 정의된 순서대로 입력

insert into customer values ('happy', '최시은', '일본 오키나와', '01033331234', '1970-10-31');

-- 여러 튜플 동시 추가

insert into customer values

    ('asdf', '강세희', '대한민국 부산', '01033331235', '2004-11-11'),

    ('sdfg', '윤지성', '일본 도쿄', '01033331236', '1995-02-15'),

    ('dfgh', '이재은', '미국 뉴욕', '01033331237', '2004-06-07');

insert into customer values ('kiwi', '김키위', '대한민국 서울', '01012341234', '1990-03-17');

insert into customer values ('apple', '이사과', '대한민국 포항', '01012344321', '1992-06-17');

insert into orders values (NULL, 'kiwi', '프링글스', '3000', 5);

insert into orders values (NULL, 'apple', '프링글스', '3000', 1);

insert into orders values (NULL, 'kiwi', '홈런볼', '2000', 3);

-- UPDATE 수정

-- custid가 happy인 고객의 주소를 대한민국 서울로 변경

update customer set addr='대한민국 서울' where custid='happy';

-- DELETE: 삭제

-- cust id가 happy인 사람의 정보를 테이블에서 삭제

delete from customer where custid='happy';

-- 고객 테이블에서 'apple'고객을 삭제했을 때, 주문 테이블에서 'apple' 고객의 주문 정보가 함께 삭제되는지(on delete cascade)

delete from customer where custid='apple';

-- apple 아이디를 갖는 고객을 customer 테이블에서 삭제한 후,

-- apple 주문 정보를 orders 테이블에 추가하려고 할 때 에러 발생 => 왜?!

-- 기준 테이블에 custid = 'apple'인 회원은 존재하지 않음!

-- orders 테이블에서 apple 회원의 데이터는 추가될 수 없음!

-- SELECT: 조회

SELECT * FROM customer;

SELECT * FROM orders;

TRUNCATE TABLE customer;

TRUNCATE TABLE orders;

INSERT INTO customer VALUES('bunny', '강해린', '대한민국 서울', '01012341234', '2000-02-23');

INSERT INTO customer VALUES('hello', '이지민', '대한민국 포항', '01022221234', '1999-08-08');

INSERT INTO customer VALUES('kiwi', '최지수', '미국 뉴욕', '01050005000', '1990-12-25');

INSERT INTO customer VALUES('imminji01', '강민지', '영국 런던', '01060001000', '1995-01-11');

INSERT INTO customer VALUES('lalala', '홍수지', '미국 로스앤젤레스', '01010109090', '2007-05-16');

INSERT INTO customer VALUES('jjjeee', '홍은정', '대한민국 서울', '01099991111', '2004-08-17');

INSERT INTO customer VALUES('wow123', '이민혁', '일본 삿포로', '01011223344', '1994-05-31');

INSERT INTO customer VALUES('minjipark', '박민지', '프랑스 파리', '01088776655', '1998-04-08');

INSERT INTO customer VALUES('jy9987', '강지연', '일본 삿포로', '01012312323', '1996-09-01');

INSERT INTO orders VALUES(NULL, 'jy9987', '프링글스', 3500, 2);

INSERT INTO orders VALUES(NULL, 'kiwi', '새우깡', 1200, 1);

INSERT INTO orders VALUES(NULL, 'hello', '홈런볼', 4200, 2);

INSERT INTO orders VALUES(NULL, 'minjipark', '맛동산', 2400, 1);

INSERT INTO orders VALUES(NULL, 'bunny', '오감자', 1500, 4);

INSERT INTO orders VALUES(NULL, 'jjjeee', '양파링', 2000, 1);

INSERT INTO orders VALUES(NULL, 'hello', '자갈치', 1300, 2);

INSERT INTO orders VALUES(NULL, 'jjjeee', '감자깡', 1200, 4);

INSERT INTO orders VALUES(NULL, 'bunny', '죠리퐁', 1500, 3);

INSERT INTO orders VALUES(NULL, 'kiwi', '꼬깔콘', 1700, 2);

INSERT INTO orders VALUES(NULL, 'hello', '버터링', 4000, 2);

INSERT INTO orders VALUES(NULL, 'minjipark', '칙촉', 4000, 1);

INSERT INTO orders VALUES(NULL, 'wow123', '콘초', 1700, 3);

INSERT INTO orders VALUES(NULL, 'imminji01', '꼬북칩', 2000, 2);

INSERT INTO orders VALUES(NULL, 'bunny', '썬칩', 1800, 5);

INSERT INTO orders VALUES(NULL, 'kiwi', '고구마깡', 1300, 3);

INSERT INTO orders VALUES(NULL, 'jy9987', '오징어집', 1700, 5);

INSERT INTO orders VALUES(NULL, 'jjjeee', '바나나킥', 2000, 4);

INSERT INTO orders VALUES(NULL, 'imminji01', '초코파이', 5000, 2);

-- 모든 고객의 아이디를 검색

select custid from customer;

-- 모든 고객의 아이디 생년월일을 검색

select custid, birth from customer;

-- 모든 고객의 생년월일 ,아이디 검색(순서 있음)

select birth, custid from customer;

-- 모든 고객의 아이디, 이름, 주소, 전화번호, 생년월일을 검색

select custid, custname, addr, phone, birth from customer;

-- ==와일드카드(*) 사용

select * from customer;

-- 고객 테이블에 있는 모든 주소 검색

select addr from customer;

-- 주소를 가져오되 중복값을 제외하고 싶다면? DISTINCT

-- DISTINCT: 중복제거

select distinct addr from customer;

오늘은 여기까지 배워보았구요!!

금요일에는 DCL과 다른 것들을 또 배우겠죠?? 밀리다가 중간에 임시저장으로 남겨놓은 내용이 많은데 이번 주말에는 꼭 꼭 다 복습하고 올려볼 수 있도록 하겠습니다...

금요일도 화이팅...!