MySQL

1. 데이터(Data)

- 숫자, 문자, 기호의 집합

- 컴퓨터 또는 디지털 장치에서 처리하고 저장할 수 있는 형태

 

2. 데이터베이스(DataBase)

- 체계적으로 구성된 데이터의 집합. 특정 목적을 위해 조직화되고 관리

- 정보를 효율적으로 저장, 검색, 업데잍, 관리할 수 있는 기술적인 도구와 구조를 제공

- 데이터베이스 관리 시스템(DataBase Management System, DBMS) : 데이터베이스를 관리할 수 있는 기술적인 소프트웨어

 

3. 관계형 데이터베이스 시스템(Relational DBMS)

- SQL(Structured Query Language) : 관계형 데이터베이스에서 데이터를 정의하고 조작하기 위한 언어

- 데이터를 테이블(표) 형식으로 관리하고, 각 테이블 간의 관계를 설정하여 데이터를 효율적으로 저장하고 검색하는 데이터베이스 관리시스템의 한 종류

 

4. SQL의 특징

- 대소문자를 구별하지 않음

- 문자열을 사용할 때 ' '(작은 따옴표)만 사용

- 주석문 -- 또는 /* */

 

5. MySQL 주기능

5.1 테이블 만들기

# 데이터베이스 확인하기
show databases ;
# 데이터베이스 생성하기
create database nodejs;

/*
	테이블(Table)
    데이터를 행(Row , Record)과 열(Column, field)로 스키마에 따라 저장할 수 잇는 구조를 말한다.
    스키마 : 데이터베이스의 구조와 제약조건에 관한 명세를 기술한 집합
    create table 테이블명 (
		필드명1 데이터타입 제약조건,
        필드명2 데이터타입 제약조건,
		...
    )
    참조 : https://wikidocs.net/226199
    
    데이터 타입
    1. 숫자형
    정수 : TINYINT(-127~127), SMALLINT, MEDIUMINT, INT(이거 사용하기), BIGINT
    실수 : FLOAT, DOUBLE(이거 사용하기), DECIMAL(고정 소수점 사용할 때)
    2. 문자형
    CHAR, VARCHAR(최대 65535byte), BINARY(글자가 아닌 데이터 저장할 때), VARBINARY, TEXT
    char(20) => 영어로 20글자 , 한글로 10글자  + 한칸에 20바이트를 자동으로 채워버림
    varchar => 넣은대로 저장해줌
    3. 날짜형
    DATE, TIME, DATETIME, TIMESTAMP,  YEAR
    
    제약조건
    데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사 규칙
    1. not null : null 값을 허용하지 않음
    2. unique : 중복값을 허용하지 않음. 그러나 null값은 허용
    3. default : null값을 삽입할 때 기본이 되는 값을 설정함
    4. primary key : 
		- null 값을 허용하지 않음
        - 중복값을 허용하지 않음
        - 테이블에 하나의 값만 허용
        - 참조키와 쌍으로 연결
        - 인덱싱을 설정
	5. foreign key(참조키) : 기본키와 쌍으로 연결
    
*/

# 데이터베이스 선택하기
use nodejs;

# 테이블 만들기
# auto_increment : 값을 주지 않아도 자동으로 1씩 늘어나는 기능 단, priamy key를 여기에 줘야함
create table member (
	idx int auto_increment primary key,
    userid varchar(20) unique not null,
    userpw varchar(20) not null,
    name varchar(20) not null,
    hp varchar(20) not null,
    email varchar(50) not null,
    gender varchar(10) not null,
    ssn1 char(6) not null,
    ssn2 char(7) not null,
    zipcode char(5),
    address1 varchar(100),
    address2 varchar(100),
    address3 varchar(100),
    regdate datetime default now(),
    point int default 1000
);

# 테이블 확인하기
desc member;

# 테이블 삭제하기
drop table member;

# 필드 삭제하기
alter table member drop point;

# 필드 추가하기
alter table member add point int default 1000 ;

# 필드 수정하기
alter table member modify column point int default 100;


-- member에 데이터 넣기
insert into member (userid,userpw,name,hp,email,gender,ssn1,ssn2,zipcode,address1,address2,address3) values
('apple','1111','김사과','010-1111-1111','apple@apple.com','여자','001011','4068518','06774','서울 서초구 강남대로 27','AI 센터','(양재동)'); 
insert into member (userid,userpw,name,hp,email,gender,ssn1,ssn2,zipcode,address1,address2,address3) values
('banana','2222','반하나','010-2222-2222','banana@banana.com','여자','950101','4068518','12345','서울 서초구 방배동27','1111','(방배동)'); 
insert into member (userid,userpw,name,hp,email,gender,ssn1,ssn2,zipcode,address1,address2,address3) values
('orange','3333','오렌지','010-3333-3333','orange@orange.com','남자','910202','4068518','06774','서울 서초구 역삼동 27','2222','(역삼동)'); 
insert into member (userid,userpw,name,hp,email,gender,ssn1,ssn2,zipcode,address1,address2,address3) values
('melon','4444','김메론','010-4444-4444','melon@melon.com','남자','881011','4068518','06774','서울 서초구 논현동27','3333','(논현동)');
insert into member (userid,userpw,name,hp,email,gender,ssn1,ssn2,zipcode,address1,address2,address3) values
('cherry','5555','이체리','010-5555-5555','cherry@cherry.com','여자','991011','4068518','06774','서울 동작구 사당동 27','4444','(사당동)');  

 select * from member;
 
 -- 모든 유저 중에서 성별이 여성인 유저만 point를 100을 추가
 update member set point=point+100 where gender='여자';

 

 

5.2 CRUD

/*
	CRUD(Create Read Update Delete)
    
    데이터 삽입(insert)
    1. insert into 테이블명 values (값1, 값2, 값3 ...)
    - 테이블의 컬럼수와 값입력의 개수가 같아야 한다.
    2. insert into 테이블명 (필드명1, 필드명2 ...) values (값1, 값2, ...)
    - 입력 필드와 입력 값의 순서와 개수가 같아야 한다.
*/
use nodejs;
create table words(
	eng varchar(50) primary key,
    kor varchar(50) not null,
    lev int default 1,
    regdate datetime default now()
);

desc words;

insert into words values('apple','사과',1,now());   #  apple 중복 삽입 에러남  --> why? eng는 primary key라서 중복X, 
insert into words values('orange','오렌지',3,now());
insert into words values('banana','바나나',null,null);
insert into words values ('melon','메론',null,null);  -- null이 삽입됨
insert into words (eng,kor) values ('cherry','체리');  -- 기본값이 적용됨

select * from words;

/*
	데이터 삭제(delete)
    1. delete from 테이블명
    2. delete from 테이블명 where 조건식
*/
-- primary key를 선택해서 삭제 
delete from words where eng='melon';
delete from words;

-- safe 모드 해제
set sql_safe_updates = 0;   -- 일시적인 방법
/*
	데이터 수정(update)
    1. update 테이블명 set 필드명1=값1, 필드명2=값2 ...
    2. update 테이블명 set 필드명1=값1, 필드명2=값2 ... where 조건식
    
*/ 
select * from words;
 update words set lev=2;
 update words set lev=1 where eng='apple';

 /*
	데이터 검색(select)
 */
 select 100;
 select 100+50;
 select 100 + 50 as 덧셈;
 select 100 + 50 as '덧셈 연산';
 select '';
 select null;
 select 100+'';
 select 100+null;	# null과의 연산은 무조건 null이다.
 
 select * from words;
 select eng,kor,lev,regdate from words;
 select eng from words;
 select kor,lev,eng from words;
 
 /*
	MySQL의 연산자
    1. 산술 : +, -, *, /, mod, div(몫)
    2. 비교 : =, <, >, <=, >=, <>
    3. 대입 : =
    4. 논리 : and, or, not, xor
    5. 기타
		is : 양쪽의 피연산자가 모두 같으면 true, 아니면 false
        between A and B : A보다는 크거나 같고, B보다는 작거나 같으면 true, 아니면 false -> 범위연산자
			예) between 1 and 10 , 1 <= 변수 <= 10
		in : 매개변수로 전달된 리스트에 값이 존재하면 true 아니면 false
        like : 패턴으로 문자열을 검색해서 값이 존재하면 true 아니면 false
 */
 
# 아이디가 'apple'인 유저의 아이디, 이름, 성별을 출력
select userid, name, gender from member where userid='apple';

# 포인트가 200 이상인 유저의 아이디, 이름, 포인트를 출력
select userid, name, point from member where point >=200;
select userid, name, point from member where point between 200 and 1000;
select userid, name, point from member where point >= 200 and point <= 1000;

# 로그인 쿼리
select userid from member where userid='apple' and userpw='1111'; -- 로그인 성공
select userid from member where userid='apple' and userpw='1112'; -- 로그인 실패

# 아이디가 apple, orange, melon인 유저의 모든 컬럼을 출력
select * from member where userid = 'apple'or userid='orange' or userid='melon';
select * from member where userid in ('apple','orange','melon');

# 아이디가 a로 시작하는 유저의 모든 컬럼을 출력
select * from member where userid like 'a%';

# 아이디가 a로 끝나는 유저의 모든 컬럼을 출력
select * from member where userid like '%a';
 
# 아이디가 a로 포함하는 유저의 모든 컬럼을 출력
select * from member where userid like '%a%';

select * from words;

# words 테이블에서 lev이 null인 데이터를 출력
select * from words where lev is null;
select * from words where lev is not null;
 
# 유저 테이블에서 가입순으로 오름차순 정렬하여 모든 컬럼을 출력
select * from member order by regdate;	# 오름차순 asc , 내림차순 desc, 오름차순은 생략가능
select * from member order by regdate desc;

# 유저 테이블에서 포인트로 내림차순 정렬하고 포인트가 같다면 가입순으로 오름차순
select * from member order by point desc, regdate asc;

# 일부 row만 가져오기
# limit 가져올 row의 개수 / limit 시작 row(index), 가져올 row의 개수 
select * from member limit 3;
select * from member limit 2,2;

# 유저 테이블에서 회원을 포인트 순으로 내림차순하고 포인트가 같다면 가입순으로 오름차순 한 뒤, top3를 출력
select * from member order by point desc, regdate asc limit 3;
 
use nodejs;
select * from member;
# 그룹
# select 그룹을 맺은 컬럼 또는 집계함수 from 테이블 group by 컬럼명
# select 그룹을 맺은 컬럼 또는 집계함수 from 테이블 group by 컬럼명 having 조건
# 집계함수 : count(), sum(), avg(), min(), max()
select gender from member group by gender;
select gender,count(idx) from member group by gender;
# 집계함수에서 컬럼을 선택할 때, primary key 제약조건이 있는 컬럼을 선택하는 것을 추천합니다.
select gender, count(idx) from member group by gender having gender='여자';
# const aql = 'select gender, count(idx) from member group by gender having gender='여자'';
select gender, count(idx) as cnt from member group by gender having cnt >=3;

# 유저 테이블에서 포인트가 50이상인 유저 중에서 성별로 그룹을 나눠 각 그룹의 포인트 평균을 구하고 평균의 포인트가 100이상인 성별을 알아보자.
# 단, 성별이 남, 여 모두 출력된다면 포인트가 높은 성별을 우선으로 출력
# 순서 기억하기!!!!!!!!!!! 
select gender, avg(point) as avg from member where point >=50 group by gender having avg >=100 order by avg desc;

 

 

5.3 MySQL 함수

/*
	MySQL 문자열 함수
*/
# concat : 문자열을 연결해주는 함수
select concat('안녕하세요','MySQL') as concat;
select concat(address1,' ',address2,' ',address3) as address from member where userid='apple';

# left, right : 왼쪽 또는 오른쪽에서 길이만큼 문자열을 반환
select left('ABCDEFGHIJKLMN',5) as str;
select userid, left(userid,3) as id from member;

# substring : 문자열의 일부를 추출하여 반환 
select substring('ABCDEFGHIJKLMN',5) as str; # 시작위치(ex.5번째)부터 끝까지 추출
select substring('ABCDEFGHIJKLMN',5,3) as str; # 시작위치부터 길이만큼 추출 (5번째부터 3글자 뽑아옴)
select userid, substring(address1,1,5) as address from member;

# char_length : 문자열의 길이를 반환
select char_length('ABCDEFGHIJKLMN') as str; -- 문자열의 길이를 알 수 있음
select email, char_length(email) as len from member;

# lpad, rpad : 왼쪽 또는 오른쪽의 문자열을 해당 길이만큼 늘리고, 빈 공간을 채운 문자열을 반환
select lpad('ABCDEFG',10,'*')as lpad;
select userid, rpad(userid,20,' ') as rpad from member;

# ltrim, rtrim, trim : 왼쪽 또는 오른쪽 , 양쪽 모든 공백을 제거
select ltrim('     ABCDEF     ')as ltrim;
select rtrim('     ABCDEF     ')as rtrim;
select trim('     ABCDEF     ')as trim;

# replace : 문자열에서 특정 문자열을 변경하여 반환
select replace('ABCDFG','CD','')as rp;
select userid, replace(gender,'자','')as gender from member;

 

5.4 SubQuery

/*
	서브쿼리(SubQuery)
    - 다른 쿼리 내부에 포함되어 있는 select 문을 의미
    - 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고 부름
    - 서브쿼리는 괄호()를 사용해서 표현
    - select, where, from , having 문장 등에서 사용할 수 있음
*/
use nodejs;
create table product(
	code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);
select * from product;
insert into product values ('00001','맥북에어','가벼워요~',150000,now());
insert into product values ('00002','자동차','잘가요~',10000000,now());
insert into product values ('00003','반포자이','너무 비싸요~',1000000000,now());
insert into product values ('00004','현익빌딩 3층','더 비싸요~',1000000000,now());
insert into product values ('00005','경비행기','잘날아요~',1500000000,now());

# 상품코드가 '00003'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 정보를 모두 출력해보자
select price from product where code = '00003';
select * from product where price >= 1000000000;
# 위의 코드를 서브쿼리를 사용하여 간략화
select * from product where price >= (select price from product where code = '00003');

# 상품 중 가장 비싼 상품에 대한 모든 정보 출력
select * from product where price = (select price from product order by price desc limit 1);
select * from product where price = (select max(price) from product);

# 테이블에 모든 데이터를 출력
# 단, 파생 컬럼을 만듦(가장 비싼 상품의 가격을 출력)
# code, name, price, '가장 비싼 상품의 가격'
select code, name, price, (select max(price) from product) as '가장 비싼 상품의 가격' from product;

create table orders(
	no int auto_increment primary key,
    idx int not null,
    product_code varchar(5) not null,
    product_cnt int default 0,
    regdate datetime default now(),
    foreign key(idx) references member(idx)
);

drop table orders;

insert into orders (idx,product_code, product_cnt) values (1,'00003',1);
insert into orders (idx,product_code, product_cnt) values (2,'00004',1);
insert into orders (idx,product_code, product_cnt) values (1,'00005',1);
insert into orders (idx,product_code, product_cnt) values (5,'00003',1);

select *  from orders;

# product 테이블에 기본키 추가하기
alter table product add primary key(code);
# orders 테이블에 외래키 추가하기
alter table orders add foreign key(product_code) references product(code) on update cascade on delete cascade;

select * from member;
select * from product;
select * from orders;

# idx로 그룹 묶기
select idx, count(idx) from orders group by idx;	-- 각 회원당 구입 횟수
select idx, count(idx) from orders group by idx having count(idx) >=2 ;

# 상품을 최소 2번 이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력
select userid, name, gender from member where idx in (select idx from orders group by idx having count(idx) >=2);

# 데이터 복사하기
create table orders_new(
	no int auto_increment primary key,
    idx int not null,
    product_code varchar(5) not null,
    product_cnt int default 0,
    regdate datetime default now(),
    foreign key(idx) references member(idx)
);

select * from orders;
insert into orders_new(select * from orders);
select * from orders_new;

create table orders_new_new(select * from orders);
select * from orders_new_new;

 

 

5.5 View

/*
	뷰(View)
    - 하나 이상의 테이블의 쿼리 결과를 가상의 테이블 형태로 보여주는 것
    - 실제 데이터는 저장하지 않고, 쿼리를 미리 저장해둬서 테이블처럼 사용할 수 있게 함
    - 복잡한 쿼리나 자주 사용하는 쿼리를 간단하게 사용할 수 있도록 도와주는 가상 테이블
    - 테이블의 일부 열이나 행만 사용자에게 보여줄 수 있음(민감 데이터 보호)
    - 성능이 떨어질 수 있음. 뷰를 사용할 때마다 원본 테이블에서 데이터를 조회하므로, 큰 데이터셋이나 복잡한 쿼리의 경우 성능이 저하될 수 있음
    - order by는 view에서 하면 속도 저하가 심해짐. 따라서 view를 조회할 때 별도로 사용하는 것을 추천
    - insert/update 할 때 복잡하게 적용된 뷰는 데이터를 추가하거나 수정하기 힘들 수 있음.
    - 
*/
select m.idx, m.userid, m.name, m.gender, p.mbti from member as m right join profile as p on m.idx = p.idx;

create view vw_memberinfo as select m.idx, m.userid, m.name, m.gender, p.mbti from member as m right join profile as p on m.idx = p.idx;
select * from vw_memberinfo;
select userid, mbti from vw_memberinfo;

select * from member;
create view vw_member as select idx, userid, name, hp, email, gender,point from member;
select * from vw_member;
select * from vw_member order by idx desc;

-- 뷰 수정하기
alter view vw_member as select idx, userid, name, hp, email, gender,regdate,point  from member ;
drop view vw_member;

 

5.6 사용자 계정

/*create user '사용자이름'@'호스트' identified by '비밀번호';
    호스트: IP(로칼접속의 경우 127.0.0.1 또는 localhost)
    권한 적용
    grant 권한종류 on 데이터베이스명, 테이블명 TO '사용자이름'@'호스트';
    모든권한: ALL (위험!)
    (select, insert, update delete, create, drop, alter, index)
    변경된 관한을 적용
    flush privileges;
    사용자 존재 확인
    select user, host from mysql.user;
    */
create user 'apple'@'localhost' identified by '1111';
grant all on nodejs.* to 'apple'@'localhost';
flush privileges;
select user, host from mysql.user

 

5.7 유니온

/*
	유니온(union)
    - 합집합을 나타내는 연산자로 중복된 값을 제거함
    - 서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용이 가능
    select 컬럼1, 컬럼2, ... from 테이블1 union select 컬럼1, 컬럼2, ... from 테이블2
*/
use nodejs;
select * from words;

create table words_new(
	eng varchar(50) primary key,
    kor varchar(50) not null,
    lev int default 1,
    regdate datetime default now()
);

insert into words_new values('avocado','아보카도',2,now());
insert into words_new values('pineapple','파인애플',1,now());
insert into words_new values('peach','복숭아',2,now());
select * from words_new;

insert into words_new values('cherry','체리',2,now());

# union으로 두개의 테이블을 합침(합집합)
select eng,kor,lev from words union select eng,kor,lev from words_new;
# words_new에 words에 있는 중복데이터를 삽입 --> union할 때 자동으로 하나만 들어감.
# 만약 regdate가 들어간다면 regdate는 다르기 때문에 중복데이터로 인식하지 않음.

# 만약 중복데이터 포함해서 다 넣고싶을 때 --> union all
select eng,kor,lev from words union all select eng,kor,lev from words_new;

 

 

5.8 정규화

/*
	정규화(Normalization)
    - 데이터 중복을 줄이고, 이상 현상을 방지하기 위해 테이블 구조를 체계적으로 분해하는 과정
    - 이상 현상
		- 삽입 이상 : 어떤 정보를 추가할 수 없는 문제
		- 수정 이상 : 정보를 수정할 때 여러 곳을 고쳐야하는 문제
        - 삭제 이상 : 데이터를 삭제할 때 의도치 않은 정보도 함께 사라지는 문제
        
	제1정규형(1NF) : 모든 컬럼을 원자값을 가져야 한다.
    취미 : 게임, 등산 (X) --> 복수값을 가지면 안된다. 따라서 취미에 대한 테이블을 따로 만든다.
    
    제2정규형(2NF) : 기본키(primary key)의 "일부"에만 의존하는 컬럼을 없애야 한다. --> 기본키와 중복되는 데이터를 제거
    "학번, 이름, 과목, 교수"가 존재하는 테이블에서 "학번"과 "이름"을 중복된 데이터이다.
    
    제3정규형(3NF) : 기본키가 아닌 속성끼리라도 서로 종속되면 안됨(이행 함수 종속 제거)
    
    제4정규형(비정규형) : 실무에 따라 정규형을 모두 지킬 수 없을 때
*/
use nodejs;
select * from member;

create table profile(
	idx int not null,
	height double,
    weight double,
    mbti varchar(10),
    foreign key(idx) references member(idx)
);

select* from profile;

insert into profile values (1,160,50,'ISTJ');
insert into profile values (3,170,70,'ESTP');
insert into profile values (5,155,50,'ESFP');


/*
	조인(join)
	- 정규화에 의해 분해한 테이블의 데이터를 합치는 방법
    
	select 컬럼1, 컬럼2, ... from 테이블 1 [inner, left, right] join 테이블2 on 테이블1.컬럼 = 테이블2.컬럼
		
        inner : 두 테이블간의 교집합
        left/right : 두 테이블이 조인될 때 왼쪽(기본키) 또는 오른쪽(외래키) 기준으로 데이터를 모두 출력(차집합)
*/
# inner join
select member.idx, userid, name, gender, mbti from member inner join profile on member.idx = profile.idx;

# 알리아스 이용하기 + inner 생략 가능
select m.idx, m.userid, m.name, m.gender, p.mbti from member as m join profile as p on m.idx = p.idx;

# left / right join
select m.idx, m.userid, m.name, m.gender, p.mbti from member as m left join profile as p on m.idx = p.idx;
select m.idx, m.userid, m.name, m.gender, p.mbti from member as m right join profile as p on m.idx = p.idx;