DataBase 테이블,CRUD

KDT_4기 데이터 분석  1007(2)

2024.10.07 수업 내용 복습일기 두번째

 

SQL은 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계한 특수 목적의 프로그래밍 언어이다.

RDBMS를 공부하기전 기본적인 테이블 만들기부터 시작해보려 한다.

1. MySQL시작하기

 

먼저 MySQL Workbench를 처음 실행하면 아래와 같은 모습이다 아래 사진에 있는 Local instance MySQL80으로 접속한다

설정한 비밀번호를 입력해주면

아래 화면이 뜬다

제목을 바꾸고 저장하기 위해 File에서 Save Script As누르고 저장할 파일 위치에 저장한다

 

Day2로 저장하였다 

위와 같이 저장하면 아래처럼 Day2라고 바뀌고 저장된다.

 

2. MySQL Workbench 구조

1. 현재 사용중인 데이터베이스 및 테이블들의 구조를 파악하는 스키마탭
2. 현재 내가 선택한 데이터베이스 및 테이블의 정보
3. 쿼리 구문 작성하는 탭
4. 쿼리 실행 후 결과창
5. 성공 혹은 에러가 뜨는 콘솔창

 

기본적으로 있는 데이터베이스를 확인해 보았다 줄 끝에는 ;을 사용하여 문장을 끝내주어야한다

실행은 Ctrl + Enter이다.

 

데이터베이스를 먼저 만들어 보겠다 코드를 실행하면 아래처럼 데이터베이스가 만들어 졌다는 메세지와 함께 초록색 체크 표시가 뜬다  빨간색 x표시가 뜬다면 에러가 난것이니 코드를 다시 한번 확인해 보길 바란다.

 

 

위에 코드를 실행하여 데이터베이스를 만들었다면 다시 위에 코드 show database 줄을 누르고 Ctrl + Enter을 통해 아래 사진처럼 확인 할 수 있다

아래 두 버튼 중 Schemas를 눌러 확인 할 수 있다

 

 

3. SQL개념

MySQL은 다음과 같은 기초 개념으로 구성된다.

 

- 테이블(Table) : 데이터를 기록하는 곳

- 스키마(Schema) : 테이블들을 모아 놓은 것 즉 데이터베이스를 뜻 함

- 데이터베이스(Database) : 테이블들을 모아 놓은 것

- 데이터베이스 서버(Database server) : 스키마들을 모아 놓은 곳

- 관계형 데이터베이스(Relation Database) : 키(key)와 값(value)들의 간단한 관계를 테이블화 시킨 데이터베이스

- SQL(Structured Query Language) : 관계형 데이터베이스 관리 시스템의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어

- 쿼리(Query) : 데이터베이스에 정보를 요청하는 것

참조 : 생활코딩 'MySQL의 구조' https://opentutorials.org/course/3161/19533

MySQL의 구조 - 생활코딩

4. SQL데이터 타입과 제약 조건

✔ 데이터타입

1. 숫자형
TINYINT,SMALLINT, MEDIUMINT, ✔INT, BIGINT(21억 넘을 때에 사용): 정수
FLOAT, ✔DOUBLE, DECIMAL(가변.고정 소수점 수를 저장함): 실수

2. 문자형
CHAR, ✔VARCHAR(일반문자)(최대 65535byte), [BINARY(문자가 아닌 이진데이터 ex영상 사운드등...), VARBINARY], TEXT(65535byte 초과시 사용)
이미지,음악,영상등 이진 파일은 데이터베이스에 넣지 않음(용량이 너무 크기때문)

3. 날짜형
DATE, TIME, ✔DATETIME, TIMESTAMP(시간의 흐름 + - 할때 사용), YEAR

 

✔ 제약조건

데이터의 무결성을 지키기 위해 데이터를 입력받을 때 실행되는 검사 규칙
1. not null: null값을 허용하지 않음

2. unique: 중복값을 허용하지 않음. null값을 허용

3. default: null값을 삽입할 때 기본이 되는 값을 설정함

4. primary key:
    null값을 허용하지 않음. 
    중복값을 허용하지 않음. 
    인덱싱을 설정.(인덱싱을 하기 떄문에 검색 속도가 높아짐)
    테이블에 단 하나만 설정가능.
    참조키와 쌍으로 연결.
    
5. foreign key: 기본키와 쌍으로 연결

 

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

1. MySQL 8 소개

 

4. SQL  CRUD

CRUD 란 Create, Read, Update, Delete의 약자로써 일반적인 데이터베이스에서 많이 수행하는 네 가지 작업 테이블 생성, 데이터 삽입,  데이터 변경, 데이터 삭제를 뜻한다. MySQL WorkBench를 통해 알아보자

4-1. 테이블 만들기

먼저 use kdt를 통해 위에서 만들어둔 데이터 베이스를 사용하고 

use kdt;

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),
regdata datetime default now(),#가입한 시간 자동 저장
point int default 100
);

각 각 데이터 타입과 제약 조건을 정해 테이블을 만들어 주었다

4-2. 테이블 수정,삭제

#테이블 확인하기
desc member;

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

#테이블에 필드 추가하기
alter table member add mbti varchar(10);

#필드 수정하기
alter table member modify column mbti varchar(20);

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

위에서 만든 테이블은 각각 문장으로 테이블을 확인하고 필드를 추가하거나 수정, 삭제가 가능하다

테이블을 삭제할 수도 있다

 

4-3. 테이블에 데이터 삽입하기

이제 필드에 데이터를 삽입해보자 위에서 만든 테이블은 필드가 많아 필드가 3개인 테이블을 만들고 필드에 데이터를 넣어보겠다

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

데이터 삽입(insert) 형식
    1. insert into 테이블명 values (값1, 값2, 값3...)
    2. insert into 테이블명 (필드명1, 필드명2,..) values (값1, 값2, 값3...)

insert into words values ('apple','사과',1);
select*from words;#컬럼 검색하기
#insert into words values ('apple','사과',1); # Duplicate entry 중복 데이터 에러가 남 앞에서 primary key사용해서

# insert into words values ('banana','바나나'); # Column count doesn`t match 컬럼 개수가 일치하지 않음

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

#insert into words values ('orange',null,null); #not null제약 때문에 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','아보카도');

주석 처리 한 문장은 위에서 테이블을 만들 때에 사용한 제약조건을 따르지 않았을 때에 오류가 나고 데이터 값이 들어가지 않은 문장들이다

 

select*from words;

 

위에서 만든 데이터들은 select문장을 통해서 확인 할 수 있다

 

이제 문제를 하나 풀어보자

문제 1.

member 테이블에 위 삽입된 5개의 단어로 5명의 유저를 삽입해 보자

desc member;
# 먼저 멤버 테이블을 확인한다

insert into member (userid, userpw,name,hp,email,gender,ssn1,ssn2) values ('apple','1234','김사과','01011111111','apple@naver.com','여자',000325,1111222);
insert into member (userid, userpw,name,hp,email,gender,ssn1,ssn2) values ('banana','1111','반하나','01011111112','apple@naver.com','여자',000326,1111223);
insert into member (userid, userpw,name,hp,email,gender,ssn1,ssn2) values ('orange','2222','어노잉','01011111113','apple@naver.com','남자',000327,1111224);
insert into member (userid, userpw,name,hp,email,gender,ssn1,ssn2) values ('melon','3333','김메롱','01011111114','apple@naver.com','남자',000328,1111225);
insert into member (userid, userpw,name,hp,email,gender,ssn1,ssn2) values ('avocado','3333','어노잉','01011111115','apple@naver.com','남자',000329,1111226);
#테이블을 만들때 각 제약 조건을 참조하여 insert문을 사용하여 멤버 테이블에 데이터를 삽입한다.
#특히 primary값과 not null, unipue 제약 조건등을 주의해서 데이터를 삽입한다

select*from member;
# 멤버 테이블의 데이터를 모두 선택하여 검색한다

 

4-4. 테이블 데이터 수정하기

다음으로는 데이터 수정에 대해 알아보자

데이터 수정(update) 형식
    1. update 테이블명 set 필드명1=값1, 필드명2=값2...
    (전체 다 바뀜
    2. update 테이블명 set 필드명1=값1, 필드명2=값2... where 조건
    (조건에 따라 바뀜
    

데이터의 수정은 프로그램 내에서도 함부로 수정하지 못하게 되어있다 해서 프로그램 내 safe모드를 해제해야한다

safe모드 해제 방법은 아래와 같다

 

일시적인 safe모드 해제
* set sql_safe_update = 0;

영구적인 safe모드 해제
* Edit -> Preferences -> SQL Editor -> Safe Updates 체크 해제 -> workbench를 재시작

 

위의 과정을 수행했다면 데이터의 수정이 가능해진다.

 

이제 words테이블의 데이터를 수정해보자

desc words; # words테이블 확인
update words set lev=1; # words테이블의 모든 lev을 1로 바꿈
select*from words;# words테이블 전체 검색

update words set kor='오륀지' where eng='orange';
# eng필드가 oreage인 데이터의 kor 값을 오륀지로 수정


select*from member;words테이블 전체 검색해서 수정한 데이터 확인
update member set point = point + 50;#모든 유저에게 50 포인트를 더해주기

update member set zipcode = '12345', address1='서울시 서초구',address2='양재동' where userid='apple';
# member 테이블의 아이디가 'apple'인 유저의 우편번호를 '12345' 주소1을'서울시 서초구' 주소2를 '양재동'으로 수정

위 처럼 where조건을 통해서 다양하게 수정 가능하다

4-5. 테이블 데이터 삭제

데이터 삭제(delete) 형식
    1. delete from 테이블명
    2. delete from 테이블명 where 조건

delete from words where eng='ogrange';#words테이블의 eng필드가 orange값인 데이터 삭제
select*from words;#삭제 된 데이터 확인
delete from words where eng='ogrange';#데이터가 없어 삭제되지 않음
delete from words;#words 테이블 전체 데이터 삭제
select*from words;#전체 삭제 되었는지 확인

위 처럼 데이터를 전체 삭제할수 있으며 마찬가지로 where조건을 달아 조건에 맞는 데이터만 삭제도 가능하다

 

4-6. 테이블 데이터 검색

데이터 검색하기(select) 형식
    select
    select 필드명1, 필드명2...from 테이블
    select 필드명1, 필드명2...from 테이블 where 조건
    select 필드명1, 필드명2...from 테이블 [where 조건] [order by 필드명 [asc,desc]]
    select 필드명1, 필드명2...from 테이블 [where 조건] [order by 필드명 [asc,desc]] limit [숫자],숫자

    select 필드명1, 필드명2...from 테이블 [where 조건] [group by 필드명] [having 조건] [order by 필드명 [asc,desc]] limit [숫자],숫자

 

[ ] 는 옵션이라는 뜻이다 써도 되고 안써도 된다. asc오름차순으로 정렬, 내림차순은 desc를 쓴다.

 

select는 연산자를 통한 조건으로 검색이 가능 하기 때문에 연산자도 미리 알아보고 select를 사용해 보겠다.

 

연산자
    1.산술 연산자: +, -, *, /, mod(나머지),div(몫)
    2. 비교연산자: =, <, >, >=, <=, <>
    3. 대입 연산자: =
    4. 논리 연산자: and, or, not, xor
    5. 기타 연산자
        * is: 양쪽의 피연산자가 모두 같으면 true, 아니면 false
        * between A and B: A보다는 크거나 같고,B보다 작거나 같으면 true, 아니면false
        * in: 매개변수로 전달된 리스트에 값이 존재하면 true, 아니면 false
        * like: 패턴으로 문자열을 검색하여 값이 존재하면 true, 아니면 false

 

아래는 select를 이용한 간단한 수식 계산들이다 

SQL에서 DB에서 계산된 필드를 쓰거나 복잡한 쿼리를 작성할 때 활용 되니 참고하자.

#select를 이용한 간단한 수식 게산
select 100;
select 100 + 50;

select 100 + 50 as 덧셈;
# as로 따로 별명을 지을 수 있다
select 100 + 50 as '덧셈 연산';
#띄어쓰기를 하고 싶을 시 ''를사용한다

select null;
#데이터베이스에 NULL 값을 검색함
select '';
#빈 문자열('')을 검색함
#빈 문자열은 길이가 0인 문자열을 나타내며, 실제로는 존재하는 값입니다. 데이터베이스에서 빈 문자열은 NULL과는 다름

select 100 + null;
# null과는 연산 불가 연산해도 null이 나옴

select 100 + '';
#위의 차이로 인해 ' '는 연산이 가능하다

 

이제 member 테이블에서 select문을 활용해 데이터를 검색해보자 테이블에 삽입된 데이터는 아래와 같다

이제 위의 조건을 이용해 select문을 활용해보자

#조건을 이용한 select문 활용
select eng from words;# words테이블의 모든 eng필드 데이터 불러오기
select eng,kor from words;# words테이블의 모든 eng,kor필드 데이터 불러오기
select*from words; # *인덱싱이 되지 않고 전체 불러옴 따라서 실제 업무에서는 잘 쓰지 않음

#아이디가 'apple'인 유저의 아이디, 이름, 성별을 출력
select userid, name, gender from member where userid='apple';
#성별이 '남자'인 유저를 모두 출력 단, 컬럼도 모두 출력
select*from member where gender='남자';
#포인트가 200이상인 유저의 아이디, 이름, 포인트 출력
select userid, name, point  from member where point >= 200;

 

위의 select를 활용하여 우리가 실제 사용하는 로그인 프로세스도 알 수 있다

select userid,name from member where userid='apple' and userpw='1234';#로그인
select userid,name from member where userid='apple' and userpw='1111';#로그인 실패

 

위 처럼 where조건을 사용하여 사용자가 입력한 ID와 비밀번호를 데이터베이스 쿼리를 통해 검증한다

조건에 맞는 데이터가 반환되면 로그인 성공, 그렇지 않으면 로그인 실패로 처리하면 된다

 

연산을 이용한 select문을 활용하였다.

#포인트가 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;

#아이디가 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%';

 

이번에는 null의 연산에 대해 알아보겠다

 

NULL은 "값이 없음"을 의미하므로, 일반적인 비교 연산자를 사용하여 검사할 수 없다. 따라서 NULL 값을 확인할 때는 IS NULL 또는 IS NOT NULL 조건을 사용해야한다.

# words 테이블애서 lev null인 데이터 출력
select*from words where lev ='null'; # X 안됨
select*from words where lev = null;  # X
select*from words where lev is null; # O
select*from words where lev is not null;

 

위의 내용을 종합해 다양하게 select문을 활용해 보겠다

#유저 테이블에서 아이디로 오름차순하여 모든 컬럼을 출력
select*from member order by userid;
select*from member order by userid asc;

#유저 테이블에서 아이디로 내림차순하여 모든 컬럼을 출력
select*from member order by userid desc;

#유저 테이블에서 포인트로 내림차순하여 아이디, 이름, 포인트, 가입날짜 순으로 출력
select userid, name, point, regdata from member order by point desc;

#유저 테이블에서 포인트순으로 오름차순하고 포인트가 같다면 userid로 내림차순
insert into member (userid, userpw,name,hp,email,gender,ssn1,ssn2) values 
('cherry','6666','체리','01066666666','cherry@naver.com','여자',000329,1111226);
select * from member order by point asc, userid desc;

#유저 테이블에서 여성회원은 포인트순으로 내림차순하고 포인트가 같다면 userid로 내림차순
select * from member where gender='여자' order by point desc, userid desc;

limit(일부 로우만 출력)

limit 형식
limit 가져올 로우의 개수, limit 시작로우(인덱스), 가져올 로우의 개수

# limit(일부 로우만 출력)
# limit 가져올 로우의 개수, limit 시작로우(인덱스), 가져올 로우의 개수
select * from member;
select * from member limit 3;
select * from member limit 2,2;
# member 테이블의 회원을 포인트순으로 내림차순하고, 포인트가 같다면 userid로 오름차순 한 뒤 top3 출력
select * from member order by point desc, userid asc limit 3;

 

group (그룹)

group (그룹) 형식
select 그룹을 맺은 컬럼 또는 집계함수 from 테이블 group by 필드명 
select 그룹을 맺은 컬럼 또는 집계함수 from 테이블 group by 필드명 having 조건

 

집계함수: count(), sum (), avg(), min(), max()

• count (): 지정한 컬럼의 행 수를 반환합니다. NULL 값은 포함되지 않습니다.
• sum (): 지정한 컬럼의 합계를 반환합니다. 숫자형 데이터에 사용됩니다.
• avg (): 지정한 컬럼의 평균 값을 반환합니다. 숫자형 데이터에 사용됩니다.
• min (): 지정한 컬럼의 최소 값을 반환합니다.
• max (): 지정한 컬럼의 최대 값을 반환합니다.

#집계 함수 예시
select count(userid) from member;
select sum(point) from member;
select avg(point) from member;
select min(point) from member;
select max(point) from member;

#집계함수: count(), sum(), avg(), min(), max()
select gender from member group by gender;
select gender from member group by gender having gender='남자';

select*from member;
select count(userid) from member;#null이 없는 컴럼 primary key가 제약 조건으로 걸려 있는 컬럼을 선택하는 것을 추천
select count(zipcode) from member; #null이 있는 컬럼 잘못 count 할 수 있음
select count(userid) as cnt from member;

#종합 예제
#유저 테이블에서 성별로 그룹을 나누고 각 그룹에 인원이 몇명인지 출력
select gender,count(userid) as '인원' from member group by gender;
select gender,count(userid) as '인원' from member group by gender having gender='여자';

#유저 테이블에서 포인트가 150 이상인 유저중에서 성별로 그룹을 나눠 각 그룹의 포인트 평균을 구하고 평균의 포인트가 200 이상인 성별을 알아보자 
#단, 성별이 남,여 모두나온다면 평균 포인트가 높은 성별을 우선으로 출력
#select 필드명1, 필드명2...from 테이블 [where 조건] [group by 필드명] [having 조건] [order by 필드명 [asc,desc]] limit [숫자],숫자
select gender,avg(point) as avg from member where point >= 150 group by gender having avg >= 200 order by avg desc;