2. 데이터 베이스(Data Base) Day 2

1. 데이터베이스 확인하기

show batabase;

 

2. 데이터베이스 만들기

create database gom;

데이터베이스가 생성되었다.(생성위치 순서가 없음)

이렇게 SCHEMA가 생긴것을 확인할 수 있다

 

3. 테이블

- 데이터를 행(ROW, 레코드) 과 열(COLUMN, 필드)로 스키마에 따라 저장 할 수 있는 구조.

스키마(Schema) : 데이터베이스의 구조와 제약 조건에 관한 명세를 기술한 집합

Create Table 테이블명(필드명1 데이터타입 제약 조건, 필드명2 데이터타입 제약조건,...) : 두꺼운_확인_표시 : 데이터 타입

참조 : https://wikidocs.net/226173

1. MySQL 8 소개

 

1) 숫자형

-TINYINT, SMALLINT, MEDIUMINY,INT,BIGINT : 정수형

-FLOAT, DOUBLE DECIMAL : 실수형

2) 문자형

-CHAR, VARCHAR(최대 65535 byte), BINARY, VARBINARY, TEXT(매우 긴 텍스트 저장, 게시판 게시글 등)

3) 날짜형

DATE, TIME, DATETIME, TIMESTAMP, YEAR : 두꺼운_확인_표시 : 제약 조건

- 데이터의 무결성을 지키기 위해 데이터를 입력받을때 실행되는 검사 규칙(무결성: 결함이 없어야 한다.)  

 

1)NOT NULL: NULL 값을 허용하지 않음

2) UNIQUE :  중복값을 허용하지 않음(NULL값은 허용)

3) DEFAULT : NULL값을 삽입할때 기본이 되는 값을 설정함

4) PRIMARY KEY(기본키) : NULL값, 중복값을 허용하지 않음, 인덱싱을 설정(테이블에 단 하나만 설정 가능, 참조키와 쌍으로 연결)

5) FORIGEN KEY : 기본키와 쌍으로 연결

*/

 

3. 데이터베이스 선택하기

사용할 데이터베이스를 입력한다.

use gom;

 

데이터베이스 테이블 생성하기

create table member( userid varchar(20) primary key, userpw varchar(20) not null, name varchar(20) not null, hp varchar(20) unique not null, email varchar(50) not null, gender varchar(10) not null, ssn1 char(6) not null, ssn2 char(7) not null, zipcode varchar(5), address1 varchar(100), address2 varchar(100), address3 varchar(100), regdate datetime default now(), point int default 100 );

 

테이블 기능들

 

# 테이블 확인하기

desc member; or select * from member;

 

 

# 테이블 삭제하기

drop table member;

저장된 테이블이 삭제된다.(삭제 시 주의 데이터 다 날라감!!)

 

# 필드 추가하기

alter table member  add mbti varchar(10);

 

필드 추가

 

# 필드 수정하기

alter table member modify column mbti varchar(20);

 

# 필드 삭제하기

alter table member drop mbti;

 

 

# CRUD(Create, Read, Update, Delete)

- 테이블 생성, 데이터 삽입, 데이터 변경, 데이터 삭제 

 

# 데이터 삽입

1) INSERT INTO 테이블명 VALUES(값1, 값2, 값3,.....)

2) INSERT INTO 테이블면 (필드명1, 필드명2, ...) VALUES (값1, 값2, 값3, ...);

 

# 연습용 테이블 만들기

create table words( eng varchar(50) primary key, kor varchar(50) not null, lev int default 1);

 

# 데이터 삽입

insert into words values('apple','사과',1);

# 데이터 삽입(INSERT)

1) INSERT INTO 테이블명 VALUES (값1, 값2, 값3...)
2) INSERT INTO 테이블명 (필드명1, 필드명2,...) VALUES (값1, 값2, 값3,...)

 

# 에러 사항들

1) 중복 데이터 테이블명 에러

Duplicate entry 'apple' for key 'words.PRIMARY'

 

2) 컬럼 개수가 일치하지 않음

Column count doesn't match value count at row 1

 

3) null을 넣을 수 없음

Column 'kor' cannot be null

 

4) null을 넣을 수 없음

Field 'kor' doesn't have a default value

 

#데이터 삽입

insert into words values('banana', '바나나', null);

insert into words(eng,kor,lev) values('orange', '오렌지', 1);

insert into words(eng,kor) values('melon', '메론');

 

insert into words (lev,eng,kor) values (2, 'avocado', '이보카도');

 

데이터 삽입 후 결과

 

#문제

Q) member 테이블에 위 테이블에 삽입된 5개의 단어(김사과,바나나,오렌지,이메론,안가도)로 5명의 유저를 삽입

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values ('apple', '1111', '김사과', '010-1111-1111', 'apple@apple.com', '여자', '000000', '0000000'); insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values ('banana', '2222', '바나나', '010-2222-2222', 'banana@banana.com', '여자', '111111', '1111111'); insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values ('orange', '3333', '오렌지', '010-3333-3333', 'orange@orange.com', '남자', '222222', '2222222'); insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values ('melon', '4444', '이메론', '010-4444-4444', 'melon@melon.com', '남자', '333333', '3333333'); insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values ('avocado', '5555', '안가도', '010-5555-5555', 'avocado@avocado.com', '남자', '444444', '4444444');

 

# 데이터 수정하기(UPDATE)

1) UPDATE 테이블명 SET 필드명1 = 값1, 필드명2 = 값2 ...

2) UPDATE 테이블명 SET 필드명1 = 값1, 필드명2 = 값2 ...WHWRE 조건

 

# 데이터 수정하기

update words set lev = 1;

 

# 일시적인 SAFE 모드 해제

SET sql_safe_updates = 0;

 

# SAFE 모드 설정 한 이유

보통 update나 delete와 같이 기존의 자료를 변경하거나 삭제하는 위험한 연산을 막아놓기 위함이다.

SAFE 모드가 설정 되어 있으면 DB는 Read Only 상태가 되어 select 또는 insert 모드만 사용 할 수 있다.

update문이나 delete문을 실행하고 다시

SET sql_safe_updates = 1; 

를 실행하면 다시 safe 모드가 활성화 된다. 

 

# 영구 적인 SAFE 모드 해제

edit -> preference -> safe update 체크 해제 후 -> Workbench 재시작

 

# 데이터 수정하기

update words set kor = '오륀지' where eng = 'orange';

변경 전

변경 후

 

# member 테이블의 모든 유저에게 50 포인트를 더해주기

select 문 실행 후 테이블 컬럼을 직접 수정할 수도 있다. 수정 후 apply 을 적용한다. 또는 update member set point = point + 50;

# member 테이블의 아이디가 'apple'인 유저의

우편 번호를 '12345' 주소1을 '서울시 서초구' 주소2를 양재동 으로 수정

update member set zipcode = '12345', address1 = '서울시 서초구', address2 = '양재동' where userid = 'apple';

 

# 데이터 삭제하기(DELETE)

1) DELETE FROM 테이블명

2) DELETE FROM 테이블명 WHERE 조건

 

# 데이터 삭제하기

delete from words where eng = 'orange';

delete from words;

 

 

# 검색하기(SELECT)

1) SELECT 필드명 1, 필드명 2,.. FROM 테이블

2) SELECT 필드명 1, 필드명 2... FROM 테이블 WHERE 조건

3) SELECT 필드명 1, 필드명 2,.. FROM 테이블 [WHERE 조건]  ORDER BY 필드명 [ASC, DESC]

4) SELECT 필드명 1, 필드명 2,...FROM 테이블 [WHERE 조건]  [ORDER BY 필드명 [ASC, DESC]]

                                                                                                      LIMIT [숫자], 숫자

5) SELECT 필드명 1, 필드명 2,...FROM 테이블 [WHERE 조건] [GRUOP BY 필드명]

                                                                                                     [HAVING 조건]

                                                                                                    [ORDER BY 필드명 [ASC, DESC]]

                                                                                                    LIMIT [숫자], 숫자

 

#SELECT문 사용하기

1)

select 100;

2)

select 100 + 50;

3)

select 100 + 50 as 덧셈;

 

4)

select 100 + 50 as '덧셈 연산';

 

5)

select null;

 

6)

select ' '; (빈 데이터가 있음)

 

7)

select 100 + null; (null과는 연산할 수 없음)

 

8)

select 100 + ' ';

 

# SELECT문 예제

1)

select eng from words;(과목명이 eng인 데이터 검색)

 

2)

select eng,kor from words;

(과목명이 eng,kor인 데이터 검색)

 

# 연산자

1) 산술 연산자 : +, -, *, /, mod(나머지), div(몫)

2) 비교 연산자 : =, < , >, >= , <=, <>

3) 대입 연산자 : =

4) 논리 연산자 : and, or, nor, xor

5) 기타 연산자 : 

       * is : 양쪽의 연산자가 모두 같으면 true, 아니면 false

       * between A and B : A보다는 크거나 같고, b 보다 작거나 같으면 true, 아니면 false

       * in : 매개변수로 전달된 리스트에 값이 존재하면 true, 아니면 false

       * like : 패턴으로 문자열을 검색하여 값이 존재하면 true, 아니면 false

 

# 아이디가 'apple' 인 유저의 아이디, 이름, 성별을 출력

select userid, name, gender from member                             where userid = 'apple'; (대입 연산자)

 

# 성별이 '남자' 인 유저를 모두 출력(단, 컬럼도 모두 출력)

select * from member where gender = '남자';

 

# 포인트가 200이상인 유저의 아이디,이름, 포인트 출력

select userid, name, point from where point >= 200;

 

# 로그인 성공

select userid, name from member                         where userid = 'apple' and userpw = '1111';

 

# 로그인 실패

select userid, name from member  where userid='apple' and userpw='1234';

 

#  포인트가 200이상인 유저의 아이디, 이름, 포인트 출력

1)

select userid, name, point from member  where point >= 200;

2)

select userid, name, point from member  where point  between  200  and  1000;

3)

select userid, name, point from member  where point >= 200  and  point <= 1000;

# 아이디가 apple, orange, melon인 유저의 모든 컬럼을 출력

1)

select * from member where userid = 'apple' or userid = 'orange' or userid = 'melon';

2)

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%';

 

# words 테이블에서 lev가 null인 데이터를 출력

select * from member where lev is null;

 

# words 테이블에서 lev가 null이 아닌 데이터를 출력

select * from words where lev is not null;

 

# member 테이블에서 아이디로 오름차순하여 모든 컬럼을 출력

1)

select * from member order by userid;

2)

select * from member order by userid asc;

 

# member 테이블에서 아이디로 내림차순하여 모든 컬럼을 출력

select * from member order by userid desc;

 

# member 테이블에서 포인트로 내림차순하여 아이디, 이름, 포인트, 가입날짜 순으로 출력

select userid, name, point, regdate from member order by point desc;

 

 

# 데이터 추가 삽입

insert into member(userid, userpw, name, hp, email, gender, ssn1, ssn2) values ('cherry', '6666', '채리', '010-6666-6666', 'cherry@cherry.com', '여자', '666666', '6666666');

 

 

# member 테이블에서 포인트순으로 오름차순하고 포인트가 같다면 userid로 내림차순

select * from member order by point asc, userid desc;

 

# member 테이블에서 여성 회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 내림차순

select * from member where gender  = '여자' order by point desc, userid desc;

 

# Limit(일부 로우만 출력)

- Limit 가져올 로우의 갯수, Limit 시작로우(인덱스), 가져올 로우의 갯수 

 

1) top 3 출력

select * from member limit 3;

2) 

select * from member limit 2, 2;

 

# member 테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 오름차순 한 뒤 top 3 출력

select * from member order by point desc, userid asc limit 3;

 

# 그룹 테이블

1) SELECT 그룹을 맺은 컬럼 또는 집계함수 FROM 테이블 GROUP BY 필드명

2) SELECT 그룹을 맺은 컬럼 또는 집계함수 FROM 테이블 GROUP BY 필드명 HAVING 조건

 

# 집계합수

- COUNT(), SUM(), AVG(), MIN(), MAX()

 

# member 테이블에서 성별로 그룹을 나누고 모든 성별 데이터 출력

select gender from member group by gender;

 

# member 테이블에서 성별로 그룹을 나누고 남자 성별 데이터 출력

select gender from member  group by gender having gender='남자';

 

# memer 테이블에서 데이터 갯수

 

select count(*) from member;

 

# member 테이블에서 userid가 null이 없는 데이터 출력 

select count(userid) from member; ( null이 없는 컬럼, primary key가 제약 조건으로 걸려있는 컬럼을 선택하는것을 추천)

 

# member 테이블에서 zipcode 가 null이 있는 컬럼이 있을때 갯수

 

 

# alias를 사용하여 별명을 쓴다.

select count(userid) as cnt from member;

 

# member 테이블에서 성별로 그룹을 나누고 각 그룹에 '인원'이 몇명인지 출력

select gender, count(userid) as '인원' from member                           group by gender;

 

# member 테이블에서 성별을 여자로 나누고 그룹에 인원이 몇명인지 데이터 출력

select gender, count(userid) as '인원' from member                      group by gender            having gender = '여자';

# SELECT 필드명1, 필드명2,.. FROM 테이블

                                                 [WHERE 조건] [GROUP BY 필드명]

                                                 [HAVING 조건]

                                                [[ORDER BY 필드명 [ASC, DESC]]

                                                LIMIT [숫자] 숫자

#문제

#member 테이블에서 포인트가 150 이상인 유저중에서 성별로 그룹을 나눠 각 그룹의 포인트 평균을 구하고, 

평균의 포인트가 200이상인 성별을 알아보자. (단, 성별이 남, 녀 모두 나옴다면 포인트가 높은 성별을 우선으로 출력) 

 

#-->내가 쓴답지
select point, avg(point), gender from member where point >= 150 group by gender having avg(point) order by point desc; 

 

#정답
select gender, avg(point) as avg from member 

                                                    where point >= 150

                                                    group by gender

                                                    having avg >= 200

                                                    order by avg desc;