MariaDB 2일차 강의 / 현대오토에버 모빌리티 SW 스쿨 / 클라우드반

 

Oracle: User >Database

Mysql, MariaDB, MongoDB: Database>User

 

1. GROUPING

1) Group by 절

• Select 열 이름이나 연산식을 나열하거나 *
• from 테이블 이름이나 select 구문
• [where 조건절]
• [group by 그룹화 할 열 이름이나 연산식을 나열]
• [order by 정렬할 열 이름이나 연산식을 나열]

📍 tStaff테이블에서 각 depart 별로 salary의 평균을 조회

-- 각 depart 별로 salary의 평균을 조회
select depart, AVG(salary)
from tStaff
group by depart;

 

📍  두 개 이상의 컬럼이나 연산식으로 그룹화하는 것도 가능

- depart와 gender로 그룹화해서 데이터의 개수를 조회

-- 두 개 이상의 컬럼이나 연산식으로 그룹화 가능
select depart, gender, count(*)
from tStaff
group by depart, gender;

=> group by 이 작성되면 select 절에는 group by 절에 사용된 컬럼이나 연산식 그리고 집계함수만 나와야 함
oracle에서는 이 규칙을 어기면 에러 발생

 

📍  tStaff 테이블에서 depart 별로 그룹화해서 depart와 name 그리고 salary의 합계를 조회

-- tStaff 테이블에서 depart 별로 그룹화해서 depart와 name 그리고 salary의 합계를 조회
select depart, name, sum(salary)
from tStaff
group by depart;

-- name은 제일 위에거가 나오기 때문에 연관성 없는 데이터, 그룹화를 잘 하는게 중요함
-- group by depart, name;

 

📍  group by 절을 사용했는데 그룹화한 컬럼이나 연산식을 조회하지 않는 것도 바람직하지 않다.

-- group by 절을 사용했는데 그룹화한 컬럼이나 연산식을 조회하지 않는 것도 바람직하지 않다.
select sum(salary)
from tStaff
group by depart;
-- 의미없는 값

 

=> Group by를 이용하면 DISTINCT와 유사한 결과를 만들 수 있습니다.

📍  tStaff 테이블에서 depart의 종류를 조회

-- Group by를 이용하면 DISTINCT와 유사한 결과를 만들 수 있습니다.
select distinct depart
from tStaff;
-- ↓
select depart
from tStaff
group by depart;

 

2) HAVING 절

• group by 이후에 적용되는 조건절
• 기본 형식
  • Select 열 이름이나 연산식을 나열하거나 *
  • from 테이블 이름이나 select 구문
  • [where 조건절]
  • [group by 그룹화 할 열 이름이나 연산식을 나열]
  • [having 그룹화 한 이후의 조건]
  • [order by 정렬할 열 이름이나 연산식을 나열]

📍  buytble 테이블에서 총 구매액(price*amount)이 1000 보다 큰 userID와  총 구매액을 조회

-- buytble 테이블에서 총 구매액(price*amount)이 1000 보다 큰 userID와  총 구매액을 조회
-- 에러 이유: 집계 함수는 group by 이후에 사용할 수 있다.
select userID, sum(price * amount)
from buytbl 
where sum(price*amount)>1000;
group by userID;

-- 정상 동작
select userID, sum(price * amount)
from buytbl 
group by userID
having sum(price*amount)>1000;

 

📍  tStaff 테이블에서 depart 별로 그룹화 한 후 salary의 평균이 340 이상인 데이터의 depart와 salary의 평균의 오름차순을 구하시오.

-- tStaff 테이블에서 depart 별로 그룹화 한 후 salary의 평균이 340 이상인 데이터의 depart와 salary의 평균의 오름차순을 구하시오.
select depart, avg(salary) as "평균 급여"
from tStaff
group by depart
having avg(salary)>=340
order by "평균 급여" ASC;

 

📍  tStaff 테이블에서 depart가 인사과와 영업부인 데이터의 depart와 salary의 최대값을 조회

-- tStaff 테이블에서 depart가 인사과와 영업부인 데이터의 depart와 salary의 최대값을 조회
-- 조건을 where에 작성
select depart, MAX(salary)
from tStaff
where depart in('인사과', '영업부')
group by depart;

-- 조건을 having에 작성
select depart, MAX(salary)
from tStaff
group by depart
having depart in('인사과', '영업부');

-- 2번으로 하면 필요없는 총무부도 찾아서 메모리 낭비, 1번으로 하면 총무부를 안 찾아서 메모리에 좋다.

 

 

3) Window 함수

• 행과 행 사이의 관계를 정의하기 위해서 제공되는 함수
• OVER 절이 들어간 함수
• 순위 함수
  • RANK, NTILE, DENSE_RANK, ROW_NUMBER, PERSENT_RANK
• 기본 형식
  순위함수이름() OVER([PARTITION BY 파티션목록] ORDER BY 정렬조건)
• PARTITION BY 파티션목록: 그룹화
• ORDER BY: 순위를 매길 데이터

📍  usertbl 테이블의 나이가 많은 순(birthyear가 태어난 해)으로 순위를 매겨서 조회

-- usertbl 테이블의 나이가 많은 순(birthyear가 태어난 해)으로 순위를 매겨서 조회
-- ROW_NUMBER는 일련번호 형태로 순위를 제공
select name, birthyear, ROW_NUMBER() OVER(order by birthyear asc)
from usertbl;

--  RANK는 동일한 값의 경우 동일한 순위를 주고 순위를 건너뜀
select name, birthyear, RANK() OVER(order by birthyear asc)
from usertbl;

-- DENSE_RANK는 동일한 값의 경우 동일한 순위를 주고 순위를 건너뛰지 않음
select name, birthyear, DENSE_RANK() OVER(order by birthyear asc)
from usertbl;

 

📍  usertbl 테이블의 addr 별로 그룹화 해서 나이가 많은 순(birthyear가 태어난 해)으로 순위를 매겨서 조회

-- usertbl 테이블의 addr 별로 그룹화 해서 나이가 많은 순(birthyear가 태어난 해)으로 순위를 매겨서 조회
select name, addr, birthyear, ROW_NUMBER() OVER(partition by addr order by birthyear asc)
from usertbl;

 

📍  N등분하기: Ntile 함수를 이용하는데 등분할 값을 매개변수로 설정

select name, birthyear, NTILE(3) OVER(order by birthyear asc)
from usertbl;

 

• 분석 함수
  • CUST_DIST(): 누적 합
  • LEAD(): 다음 행
  • FIRST_VALUE(): 첫번째 행
  • LAG(): 이전 행
  • LAST_VALUE(): 마지막 행
  • PERCENT_RANK()

📍  다음 행과 차이: usertbl에서 다음 행과의 birthyear 차이

-- 다음 행과 차이: usertbl에서 다음 행과의 birthyear 차이
select name, birthyear, birthyear - (LEAD(birthyear, 1) over(order by birthyear desc))
from usertbl;

-- 사용자들의 출생년도 순위 위치를 누적 분포 비율로 계산
select name, birthyear, CUME_DIST() over(order by birthyear desc)
from usertbl;

 

• WITH ROLLUP: group by와 같이 사용해서 그룹 합계와 총 합계를 만들어주는 기능

📍  order_d 테이블에서 goodscd 별로 그룹화해서 qty의 합계를 조회

-- order_d 테이블에서 goodscd 별로 그룹화해서 qty의 합계를 조회
select goodscd, sum(qty)
from order_d
group by goodscd with rollup;

 

• 피벗 기능: 한 열에 포함된 여러 값을 출력하고 이를 여러 열로 변환해서 테이블 반환식을 만들어 내는 것

📍  uName과 season으로 피봇 테이블을 생성

-- uName과 season으로 피봇 테이블을 생성
select uName, SUM(amount) as '합계'
from pivotTest
group by uName;

select uName, SUM(if(season='봄', amount, 0)) as '봄', 
SUM(if(season='여름', amount, 0)) as '여름',
SUM(if(season='가을', amount, 0)) as '가을',
SUM(if(season='겨울', amount, 0)) as '겨울',
SUM(amount) as '합계'
from pivotTest
group by uName;

 

2. 관계형 데이터 베이스

• Super Key: 속성 개수에 상관없이 구별
• Candidate Key: 속성의 개수를 최소로 해서 구별
• Primary Key: 후보키 중 하나 선정
• Alternate Key: 기본키가 아닌 후보키

1) 데이터 모델

• 물리적 모델
• 개념적 모델
• 논리적 모델

기획→분석 →설계 →구현 →테스트 →수정

 

2) 관계

• 1:1
  • 양쪽 테이블의 기본 키를 다른 테이블에 외래키로 추가
• 1:N
  • 1쪽의 기본키를 N쪽의 외래키로 추가
• N:M
  • 양쪽의 기본키를 갖는 별도의 테이블을 만들어서 설정

3) 데이터 중복으로 인한 이상 현상

• 변경 이상
• 삽입 이상
• 삭제 이상

4) 함수 종속

• 완전 함수 종속: 종속자가 기본키를 구성하는 모든 속성의 집합에 종속되는 경우
• 부분 함수 종속: 기본키가 여러 속성으로 구성되어 있을 때, 종속자가 기본키를 구성하는 속성 중 일부에만 종속되는 경우
• 이행적 함수 종속: 키가 아닌 속성이 다른 키가 아닌 속성을 결정하는 경우에 발생합니다 

5) 반정규화

• 테이블 분할- 파티셔닝
  • 범위 분할: 분할 키 값이 범위 내에 있는지 여부로 구분하는데 우편번호를 분할 키로 수평 분랑하는 경우
  • 목록 분할
  • 해시 분힐
  • 합성 분할

6) 분산데이터베이스

• 분할 투명성
• 위치 투명성
• 지역사상 투명성
• 장애 투명성
• 중복 투명성
• 병행 투명성

4. DDL

CREATE [TEMPORARY] TABLE [if not exists] 테이블명(

    컬럼명1 타입 [constracint 제약조건 이름] 컬럼제약조건,

    컬럼명2 타입,

    컬럼명3 타입,

    [constrain 제약조건이름] 테이블 제약조건 )

 

=> 데이터 타입

• 숫자: BIN, INT, FLOAT, DOUBLE, AECIAML 소수자리 숫자
• 문자
  • CHAR (바이트 수 255까지꺼자)
  • VARCHAR (바이트 수 65535까지)
  •  
  • TEXT: 65535까지
  • LONGTEXT: 43억자 까지
  • BLOB: 65535
  • LONGBLOB: 43억 정도까지
• 날짜
  • DATE, DATETIME,TIMESTAPM
• BOOL
• JSON
• GEOMTRY

1) 엔진

-MyISAM: 인덱스를 지원하는 구조로 조회에는 유리하지만 변경 작업을 수행할 때 시간이 많이 ㅓㄹㄹ림

InnoDB. 조회는 불리 하지만 변경 작업을 빠르게 수행

 

=> Auto_Increment: 초기값

- 시퀀스 시작 번호 혈석

=> Character set 설정ㅣ default charset = 인코딩 방법

 

2) 테이블 생성 및 수정

-- 테이블 생성
-- contact 테이블을 생성
-- 일련번호로 이름은 num 이고 기본키
-- 이름을 저장할 것인데 이름은 거의 변경이 없는 20자
-- 주소는 자주 변경되는 ㄴ데이터로 100자 정보
-- 전화번호는ㄴ 자주 변경이 되지만 자식수는 면경이 안되는 20자 정도
-- 이메일은 자주 변경되는 데이터로 100자 정도
-- 생일은 날짜 타입으로 생성

-- 일련번호는 1부터 시작
create table contact(
	num int auto_increment primary key,
	name varchar(20),
	address char(20),
	tel varchar(200),
	email char(100) collate utf8mb4_bin,
	birtyday date)Engine=myISAM AUTO_Increment=1 default charset=utf8mb4;

 

수정

ALTER TABLE 테이블이름 ADD 컬럼이름 자료형 [first 또는 after 컬럼이름];

위치를 설정하지 않으면 가장 마지막에 만들어집니다.

📍  contact 테이블에 age라는 컬럼을 정수로 추가

-- contact 테이블에 age라는 컬럼을 정수로 추가
alter table contact add age int;

desc contact;

 

컬럼 삭제

ALTER TABLE 테이블이름 DROP 컬럼이름

 

📍  contact 테이블의 age 컬럼을 삭제

-- 컬럼 삭제
alter table contact drop age;

 

컬럼 변경

-- 기존 테이블의 자료형이나 크기또는 이름을 변경

ALTER TABLE 테이블이름 CHANGE 이전컬럼이름 새로운컬럼이름 자료형;

 

-- 기존 컬럼의 자료형 변경, NOT NULL 이나 NULL 허용도 이 방식으로 변경

  -- NOT NULL, NULL 도 크기가 바뀜. 

ALTER TABLE 테이블이름 MODIFY 컬럼이름 자료형

 

📍  contact table의 tel이라는 컬럼을 phone이라는 이름의 컬럼으로 변경

-- contact table의 tel이라는 컬럼을 phone이라는 이름의 컬럼으로 변경
ALTER TABLE contact CHANGE tel phone INT;

 

컬럼의 순서 조정

ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼이름 자료형 AFTER 컬럼이름;

ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼이름 자료형 FIRST;

 

테이블 이름 수정

ALTER TABLE 이전테이블이름 RENAME 새로운테이블이름;

 

테이블 삭제

기본 형식

DROP TABLE 테이블이름;

 

3) 테이블의 모든 데이터 삭제: 구조는 남음

=>기본 형식

TRUNCATE TABLE 테이블이름;

 

4) 테이블을 압축

=>용량은 줄어들지만 작업 시간은 길어짐

CREATE TABLE 다음에 ROW_FORMAT=COMPRESSED 추가

 

5) 주석 설정

COMMENT ON TABLE 테이블 이름 IS '주석';

 

6) 무결성 제약조건

Intergrity(무결성)

• 데이터의 정확성과 일관성을 유지하고 보증하는 것
• 적용 범위
  • Entity Integrity(개체 무결성): PRIMARY KEY의 값은 NULL이거나 중복될 수 없다.
  • Referential Integrity(참조 무결성): FOREIGN KEY의 값은 참조할 수 있는 값이거나 NULL이어야 한다.
  • Domain Integrity(도메인 무결성): 컬럼에 저장되는 값은 원자값이어야 하고 타입 지정, NULL 여부, 체크, 기본값등의 규칙을 지켜야 한다.

 

NOT NULL

• 필수 입력
• 컬럼의 크기와 연관이 있으므로 컬럼을 만들 때 설정하고 수정을 할 때도 제약조건 수정이 아니라 컬럼의 자료형 변경(MODIFY)으로 한다.
• 테스트
  
  

CREATE TABLE tNullable(
	name CHAR(10) NOT NULL,
    age INT
);

INSERT INTO tNullable(name,age) VALUES('아담', 55);
INSERT INTO tNullable(name) VALUES('이브');

-- name은 NOT NULL이므로 생략할 수 없음
INSERT INTO tNullable(age) VALUES(14);

 

CHECK

• 값의 종류나 범위를 설정하는 제약조건

CHECK (컬럼 이름 가질 수 있는 값의 종류나 범위 설정)

 

-- tCheckTest 테이블을 생성
-- gender 3글자까지이고 남과 여 둘 중 하나만 가져야 함
-- origin은 3글자이고 동서남북 중 하나만 가져야 함
-- grade는 정수이고 1부터 5 사이의 숫자
-- name은 10자이고 박으로 시작

CREATE TABLE tCheckTest(
    gender CHAR(3) CHECK(gender = '남' OR gender = '여'),
    origin CHAR(3) CHECK(origin = '동' OR origin = '서' OR origin = '남' OR origin = '북'),
    grade INT CHECK(grade >=1 AND grade <=5),
    name CHAR(10) CHECK(name like '박%')
);

 

UNIQUE

• 필드의 중복 값을 배제
• NULL을 허용
• 2개 이상의 컬럼에도 적용 가능하지만 2개 이상의 컬럼에 적용할 때는 테이블 제약조건으로 설정해야 한다.

-- area에 중복 값이 없어야 하고 popu도 중복값이 없어야 한다.
CREATE TABLE tUniqueTest(
     name CHAR(10),
     area INT UNIQUE,
     popu INT UNIQUE
);

-- area와 popu를 합친 값이 중복이 없어야 한다.
CREATE TABLE tUniqueTest(
     name CHAR(10),
     area INT,
     popu INT,
     CONSTRAINT UK_area_popu UNIQUE(area, popu)
);

 

PRIMARY KEY

• 기본키 설정
• 테이블에 하나만 설정 가능
• 2개 이상의 컬럼으로 구성하는 경우 테이블 제약 조건으로 설정
• NOT NULL 이고 UNIQUE

CREATE TABLE tPKTest1(
     name CHAR(10),
     area INT,
     popu INT,
     CONSTRAINT PK_tPKTest PRIMARY KEY (name)
);

CREATE TABLE tPKTest2(
     name CHAR(10) PRIMARY KEY,
     area INT,
     popu INT
);

 

 참조 무결성 - FOREIGN KEY

• 실습을 위한 테이블 생성(직원 테이블과 직원이 수행한 프로젝트에 대한 테이블 생성)

CREATE TABLE tEmployee(
	name CHAR(10) PRIMARY KEY,
    salary INT NOT NULL,
    addr VARCHAR(30) NOT NULL
);

CREATE TABLE tProject(
	projectID INT PRIMARY KEY,
    name CHAR(10),
    project VARCHAR(30) NOT NULL,
    cost INT
);

-- tEmployee 테이블에 샘플 데이터 삽입
INSERT INTO tEmployee(name, salary, addr) VALUES('아이린', 500, '대구');
INSERT INTO tEmployee(name, salary, addr) VALUES('배수지', 700, '광주');
INSERT INTO tEmployee(name, salary, addr) VALUES('이지은', 600, '서울');

-- tProject 테이블에 데이터를 삽입
INSERT INTO tProject(projectID, name, project, cost) VALUES(1, '배수지', '광주콘서트', 5000);

-- 카리나는 tEmployee 테이블에 존재하지 않는데도 삽입이 된다.
INSERT INTO tProject(projectID, name, project, cost) VALUES(2, '카리나', '서울콘서트', 3000);

• 실습을 위해 테이블 삭제

DROP TABLE tEmployee;
DROP TABLE tProject;

 

• 테이블 foreign key 추가해서 다시 생성 후 카리나 데이터가 삽입 되는지 확인

-- foreign 넣어서 생성
CREATE TABLE tEmployee(
	name CHAR(10) PRIMARY KEY,
    salary INT NOT NULL,
    addr VARCHAR(30) NOT null
);

-- name이라는 컬럼이 tEmployee 테이블의 name을 참조하도록 설정
CREATE TABLE tProject(
	projectID INT PRIMARY KEY,
    name CHAR(10),
    project VARCHAR(30) NOT NULL,
    cost int,
    constraint FK_EMP foreign key(name) references tEmployee(name)
);

-- tEmployee 테이블에 샘플 데이터 삽입
INSERT INTO tEmployee(name, salary, addr) VALUES('아이린', 500, '대구');
INSERT INTO tEmployee(name, salary, addr) VALUES('배수지', 700, '광주');
INSERT INTO tEmployee(name, salary, addr) VALUES('이지은', 600, '서울');

-- tProject 테이블에 데이터를 삽입
INSERT INTO tProject(projectID, name, project, cost) VALUES(1, '배수지', '광주콘서트', 5000);

-- 카리나는 tEmployee에 존재하지 않아서 에러: 참조키의 값은 NULL이거나 참고할 수 있는 값 만을 가져야 한다.
INSERT INTO tProject(projectID, name, project, cost) VALUES(2, '카리나', '서울콘서트', 3000);

 

• 참조 되는 값은 삭제 안되는 걸 증명

-- 성공
DELETE FROM tEmployee WHERE name = '아이린';

-- 실패: 프로젝트 1에서 참조당하고 있기 때문이다.
DELETE FROM tEmployee WHERE name = '배수지';

 

• 외래키로 참조 당하는 테이블은 삭제가 불가능

DROP TABLE tEmployee;

-- tProject create 코드에서 
-- constraint FK_EMP foreign key(name) references tEmployee(name)
-- 이렇게 참조하고 있으므로 삭제 불가함

 

배수지 삭제하기 위해 CASCADE 설정하기

CREATE TABLE tEmployee(
	name CHAR(10) PRIMARY KEY,
    salary INT NOT NULL,
    addr VARCHAR(30) NOT null
);

-- name이라는 컬럼이 tEmployee 테이블의 name을 참조하도록 설정
CREATE TABLE tProject(
	projectID INT PRIMARY KEY,
    name CHAR(10),
    project VARCHAR(30) NOT NULL,
    cost int,
    constraint FK_EMP foreign key(name) references tEmployee(name) on delete cascade
);

-- tEmployee 테이블에 샘플 데이터 삽입
INSERT INTO tEmployee(name, salary, addr) VALUES('아이린', 500, '대구');
INSERT INTO tEmployee(name, salary, addr) VALUES('배수지', 700, '광주');
INSERT INTO tEmployee(name, salary, addr) VALUES('이지은', 600, '서울');

-- tProject 테이블에 데이터를 삽입
INSERT INTO tProject(projectID, name, project, cost) VALUES(1, '배수지', '광주콘서트', 5000);

DELETE FROM tEmployee WHERE name = '아이린';
-- 이제 배수지가 지워진다.
DELETE FROM tEmployee WHERE name = '배수지';

여기서 tProject를 검색해보면 배수지가 삭제되면서 프로젝트도 다 사라진 걸 볼 수 있다. SET NULL 처리하면 삭제대신 NAME만 NULL 처리된다.

select * from tProject;

SET NULL 설정한 후 다시

-- set null 설정한 후 다시

CREATE TABLE tEmployee(
	name CHAR(10) PRIMARY KEY,
    salary INT NOT NULL,
    addr VARCHAR(30) NOT null
);

-- name이라는 컬럼이 tEmployee 테이블의 name을 참조하도록 설정
CREATE TABLE tProject(
	projectID INT PRIMARY KEY,
    name CHAR(10),
    project VARCHAR(30) NOT NULL,
    cost int,
    constraint FK_EMP foreign key(name) references tEmployee(name) on delete set null
);

-- tEmployee 테이블에 샘플 데이터 삽입
INSERT INTO tEmployee(name, salary, addr) VALUES('아이린', 500, '대구');
INSERT INTO tEmployee(name, salary, addr) VALUES('배수지', 700, '광주');
INSERT INTO tEmployee(name, salary, addr) VALUES('이지은', 600, '서울');

-- tProject 테이블에 데이터를 삽입
INSERT INTO tProject(projectID, name, project, cost) VALUES(1, '배수지', '광주콘서트', 5000);

DELETE FROM tEmployee WHERE name = '아이린';
DELETE FROM tEmployee WHERE name = '배수지';

-- NAME이 NULL이 됨.
select * from tProject;

NAME에 배수지가 삭제되고 NULL이 생겼다.

 

• FOREIGN KEY를 설정할 때의 옵션
• 참조 당하는 데이터가 삭제나 수정될 때 동작하는 옵션
  • NO ACTION: 아무것도 하지 않음
  • CASCADE: 연쇄 삭제, 참조하고 데이터도 삭제
  • SET NULL: 참조하는 값에 NULL
  • SET DEFAULT: 데이터베이스 설정에 따름

ON DELETE	[NO ACTION | CASCADE | SET NULL | SET DEFAULT]
ON UPDATE	[NO ACTION | CASCADE | SET NULL | SET DEFAULT]

 

 

제약조건 수정

• 제약조건 확인

SELECT * FROM information_schema.table_constraints;

• 제약조건 수정

ALTER TABLE 테이블이름 ADD 제약조건(컬럼이름);

• 제약조건 삭제

ALTER TABLE 테이블이름 DROP CONSTRAINT 제약조건이름;

 

7) 기본값 설정

자료형 뒤에 DEFAULT 와 함께 값을 설정

삽입할 때 값을 설정하지 않으면 

 

CREATE TABLE tEmployee1(
	name CHAR(10) PRIMARY KEY,
    salary INT DEFAULT 0,
    addr VARCHAR(30) NOT NULL
);

INSERT INTO tEmployee1 values('아이린', 200, '대구');
-- 원래는 salary가 null이어야 하는데 0으로 채워짐
INSERT INTO tEmployee1(name, addr) values('배수지', '광주');

select * from tEmployee1;