TIL - day12

정상화·2023년 3월 8일
0

TIL

목록 보기
8/46
post-thumbnail

DB

데이터베이스는 테이블들을 모아놓은 것
MySql은 데이터베이스가 아닌 DBMS이다.

DDL(Database Definition Language)

데이터베이스를 정의할 때 사용
CREATE, DROP, ALTER 등

DML(Database Manipulation Language)

데이터베이스를 조작할 때 사용
SELECT, INSERT, UPDATE, DELETE 등

CRUD

  • Create
  • Read
  • Update
  • Delete

위의 4가지 작업을 이르는 말

데이터베이스 생성&조회 예시

# 전체 데이터베이스 리스팅
show databases;

# `mysql` 데이터 베이스 선택
use mysql;

# 테이블 리스팅
show tables;

# 특정 테이블의 구조
desc `user`;

# `test` 데이터 베이스 선택
use test;

# 테이블 리스팅
show tables;

# 기존에 a1 데이터베이스가 존재 한다면 삭제
drop database if exists a1;

# 새 데이터베이스(`a1`) 생성
create database a1;

# 데이터베이스(`a1`) 선택
use a1;

# 데이터베이스 추가 되었는지 확인
show databases;

# 테이블 확인
show tables;

# 게시물 테이블 article(title, body)을 만듭니다.
# VARCHAR(100) => 문자 100개 저장가능
# text => 문자 많이 저장가능
create table `article`(
	`title` varchar(100),
	`body` text
);

# 잘 추가되었는지 확인, 리스팅과 구조까지 확인
show tables;
desc article;

# 데이터 하나 추가(title = 제목, body = 내용)
insert into article
set title = 'Title',
body = 'Content';

# 데이터 조회(title 만)
select title
from article;

# 데이터 조회(title, body)
select title, body
from article;

# 데이터 조회(body, title)
select body, title
from article;

# 데이터 조회(*)
select *
from article;

# 데이터 또 하나 추가(title = 제목, body = 내용)
insert into article
set title = 'Title',
body = "Content";

# 데이터 조회(*, 어떤게 2번 게시물인지 알 수 없음)
select *
from article;

# 테이블 구조 수정(id 칼럼 추가, first)
alter table article
add column id int first;

# 데이터 조회(*, id 칼럼의 값은 NULL)
select *
from article;

# 기존 데이터에 id값 추가(id = 1, id IS NULL)
update article
set id = 1
where id is null;

# 데이터 조회(*, 둘다 수정되어 버림..)
select *
from article;

# 기존 데이터 중 1개만 id를 2로 변경(LIMIT 1)
update article
set id = 2
limit 1;

# 데이터 조회(*)
select *
from article;

# 데이터 1개 추가(id = 3, title = 제목3, body = 내용3)
insert into article
set id = 3,
title = 'Title3',
body = 'Content3';

# 데이터 조회(*)
select *
from article;

# 2번 게시물, 데이터 삭제 => DELETE
delete from article
where id = 2;

# 데이터 조회(*)
select *
from article;

# 날짜 칼럼 추가 => regDate DATETIME
alter table article
add column regDate datetime
after id;

# 테이블 구조 확인
desc article;

# 데이터 조회(*, 날짜 정보가 비어있음)
select *
from article;

# 1번 게시물의 비어있는 날짜정보 채움(regDate = 2018-08-10 15:00:00)
update article
set regDate = '2018-08-10 15:00:00'
where id = 1;

# 데이터 조회(*, 이제 3번 게시물의 날짜 정보만 넣으면 됩니다.)
select *
from article;

# NOW() 함수 실행해보기
select now();

# 3번 게시물의 비어있는 날짜정보 채움(NOW())
update article
set regDate = now()
where id = 3;

# 데이터 조회(*)
select *
from article;

PK와 Where 절

# 기존에 a2 데이터베이스가 존재 한다면 삭제
drop database if exists a2;

# 새 데이터베이스(`a2`) 생성
create database a2;

# 새 데이터베이스(`a2`) 선택
use a2;

# article 테이블 생성(id, regDate, title, body)
create table article(
	id int,
	regDate datetime,
	title varchar(100),
	body text
);

# article 테이블 조회(*)
select *
from article;

# article 테이블에 data insert (regDate = NOW(), title = '제목', body = '내용')
insert into article
set regDate = NOW(), title = '제목', body = '내용';

# article 테이블에 data insert (regDate = NOW(), title = '제목', body = '내용')
insert into article
set regDate = NOW(), title = '제목', body = '내용';

# article 테이블 조회(*)
## id가 NULL인 데이터 생성이 가능하네?
select *
from article;

# id 데이터는 꼭 필수 이기 때문에 NULL을 허용하지 않게 바꾼다.(alter table, not null)
## 기존의 NULL값 때문에 경고가 뜬다.
## 기존의 NULL값이 0으로 바뀐다.
update article
set id = 0;

alter table article
modify column id int not null;

# article 테이블 조회(*)
select *
from article;

# 생각해 보니 모든 행(row)의 id 값은 유니크 해야한다.(ADD PRIMARY KEY(id))
## 오류가 난다. 왜냐하면 기존의 데이터 중에서 중복되는게 있기 때문에
alter table article
add primary key(id);

# id가 0인 것 중에서 1개를 id 1로 바꾼다.
update article
set id = 1
where id = 0
limit 1;

# article 테이블 조회(*)
select *
from article;

# id가 0인것을 id 2로 바꾼다.
update article
set id = 2
where id = 0;

# 생각해 보니 모든 행(row)의 id 값은 유니크 해야한다.(ADD PRIMARY KEY(id))
## 이제 적용이 잘 된다.
alter table article
add primary key(id);

# id 칼럼에 auto_increment 를 건다.
## auto_increment 를 걸기전에 해당 칼럼은 무조건 key 여야 한다.
alter table article 
modify column id int not null AUTO_INCREMENT;

# article 테이블 구조확인(desc)
desc article;

# 나머지 칼럼 모두에도 not null을 적용해주세요.
alter table article
modify column regDate date not null;

alter table article
modify column title varchar(100) not null;

alter table article
modify column body text not null;

# id 칼럼에 UNSIGNED 속성을 추가하세요.
alter table article
modify column id int unsigned not null auto_increment;

# 작성자(writer) 칼럼을 title 칼럼 다음에 추가해주세요.
alter table article
add column writer varchar(100) not null;

after title;

# 작성자(writer) 칼럼의 이름을 nickname 으로 변경해주세요.(ALTER TABLE article CHANGE oldName newName TYPE 조건)
alter table article
change writer nickname varchar(100) not null
after title;

# nickname 칼럼의 위치를 body 밑으로 보내주세요.(MODIFY COLUMN nickname)
alter table article
modify column nickname varchar(100) not null
after body;

# hit 조회수 칼럼 추가 한 후 삭제해주세요.
alter table article
add column hit int unsigned not null
after nickname;

alter table article
drop column hit;

# hit 조회수 칼럼을 다시 추가
alter table article
add column hit int unsigned not null
after nickname;

# 기존의 비어있는 닉네임 채워넣기(무명)
update article
set nickname = 'anonymous'
where nickname = '';

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목3', body = '내용3', nickname = '홍길순', hit = 10)
insert into article
set regDate = NOW(), title = '제목3', body = '내용3', nickname = '홍길순', hit = 10;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목4', body = '내용4', nickname = '홍길동', hit = 55)
insert into article
set regDate = NOW(), title = '제목4', body = '내용4', nickname = '홍길동', hit = 55;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목5', body = '내용5', nickname = '홍길동', hit = 10)
insert into article
set regDate = NOW(), title = '제목5', body = '내용5', nickname = '홍길동', hit = 10;

# article 테이블에 데이터 추가(regDate = NOW(), title = '제목6', body = '내용6', nickname = '임꺽정', hit = 100)
insert into article
set regDate = NOW(), title = '제목6', body = '내용6', nickname = '임꺽정', hit = 100;

# 조회수 가장 많은 게시물 3개 만 보여주세요., 힌트 : ORDER BY, LIMIT
select *
from article
order by hit desc
limit 3;

# 작성자명이 '홍길'로 시작하는 게시물만 보여주세요., 힌트 : LIKE '홍길%'
select *
from article
where nickname like '홍길%';

# 조회수가 10 이상 55 이하 인것만 보여주세요., 힌트 : WHERE 조건1 AND 조건2
select *
from article
where hit between 10 and 55;

# 작성자가 '무명'이 아니고 조회수가 50 이하인 것만 보여주세요., 힌트 : !=
select *
from article
where nickname != 'anonymous'
and hit <= 50;

# 작성자가 '무명' 이거나 조회수가 55 이상인 게시물을 보여주세요. 힌트 : OR
select *
from article
where nickname != 'anonymous'
or hit >= 55;

Join

한 테이블이 다른 테이블을 참조해야하는 상황이 있을 때 두 테이블을
조인 하여 하나로 합칠 수 있다.

# a5 데이터베이스 삭제/생성/선택
drop database a5;
create database a5;
use a5;

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
create table dept(
	id int unsigned not null auto_increment,
	primary key(id),
	regDate datetime not null,
	name char(100) not null
);

insert into dept
set regDate = now(), name = '홍보';

insert into dept
set regDate = now(), name = '기획';

select *
from dept;

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
create table emp(
	id int unsigned not null auto_increment,
	primary key(id),
	regDate datetime not null,
	name char(100) not null,
	deptName char(100) not null
);

insert into emp
set regDate = now(), name = '홍길동', deptName = '홍보';
insert into emp
set regDate = now(), name = '홍길순', deptName = '홍보';
insert into emp
set regDate = now(), name = '임꺽정', deptName = '기획';

select *
from emp;

# 홍보를 마케팅으로 변경
update dept
set name = '마케팅'
where name = '홍보';

select *
from dept;

update emp
set deptName = '마케팅'
where deptName = '홍보';

select *
from emp;

# 마케팅을 홍보로 변경
update dept
set name = '홍보'
where name = '마케팅';

select *
from dept;

update emp
set deptName = '홍보'
where deptName = '마케팅';

select *
from emp;

# 홍보를 마케팅으로 변경
update dept
set name = '마케팅'
where name = '홍보';

select *
from dept;

update emp
set deptName = '마케팅'
where deptName = '홍보';

select *
from emp;

# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
alter table emp
add column deptId int unsigned not null;

update emp
set deptId = 1
where deptName = '마케팅';

update emp
set deptId = 2
where deptName = '기획';

select *
from emp;

alter table emp
drop column deptName;

# 사장님께 드릴 인명록을 생성
select *
from emp, dept;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
select emp.*, dept.name
from emp inner join dept;

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남
select emp.*, dept.name as '부서명'
from emp inner join dept;

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
select emp.*, dept.name as '부서명'
from emp inner join dept on dept.id = deptId;

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
select emp.id as '사원번호',
date(emp.regDate) as '입사일',
emp.name as'사원명',
dept.name as '부서명'
from emp inner join dept on dept.id = deptId;

# 사장님께 드릴 인명록을 생성(v5, 테이블 AS 적용)
select e.id as '사원번호',
date(e.regDate) as '입사일',
e.name as'사원명',
d.name as '부서명'
from emp as e inner join dept as d on d.id = deptId;

+) 오라클과 다르게 MySql은 inner join의 기본동작이 카티션 프로덕트이다.
따라서 foreign key를 지정했더라도 on 절은 필수이다.

profile
정상화
백엔드 희망
이전 포스트

TIL - day11

다음 포스트

TIL - day 13

0개의 댓글