0%

210305_TIL(DB_2)

Posted on 2021-03-05 Edited on 2021-09-30 In DB

오늘 배운 것

DB

use fds;

# 데이터 타입
# INT
create table number1(data tinyint);
create table number2(Data tinyint unsigned);

# 현재 데이터 베이스의 테이블 확인
show tables;

# 테이블 스키마 확인
desc number1;
desc number2;

# 데이터 저장
insert into number1 value (127);
select * from number1;

insert into number2 value (128);
insert into number2 value (-1); # unsigned에서는 음수는 넣지 못 한다.
select * from number2;

# FLOAT(6자리), DOUBLE(17자리)
create table number3(data float);
insert into number3 value (12.3456789);
select * from number3;

create table number4(data double);
insert into number4 value (1234567890.123456789);
select * from number4;

# DB 생성
create database test;
use test;

# 테이블 생성
create table user1(
	user_id int,
	name varchar(20),
    email varchar(30),
    age int,
    rdate date
);

create table user2(
	user_id int primary key auto_increment,
	name varchar(20) not null,
    email varchar(30) unique,
    age int default 30,
    rdate timestamp
);

# INSERT
desc user1;
insert into user1 (user_id, name, email, age, rdate)
value (1, 'peter', 'peter@gmail.com', 23, '2018-01-23');

insert into user1 (user_id, name, email, age, rdate)
values (2, 'peter2', 'peter@gmail.com', 23, '2018-01-23'),
(3, 'peter3', 'peter@gmail.com', 23, '2018-01-23'),
(4, 'peter4', 'peter@gmail.com', 23, now());

select * from user1;

desc user2;
insert into user2 (name, email, age)
value ('peter', 'peter@gmail.com', 22);

select * from user2;

# select 결과 데이터
use world;

select countrycode, name, population from city where population >= 900* 10000;

create table city_900(
	countrycode char(3),
    name varchar(50),
    population int
);
desc city_900;

insert into city_900
select countrycode, name, population from city where population >= 900* 10000;

select * from city_900;

# update set
use test;

select * from user1;

# update 시 safe mode로 인해 limit을 안 걸면 update 되지 않는다.
#설정 SQL Editor에서 설정 가능
update user1 set age=50, email="peter@naver.com" where name = "peter" limit 3;

# DELETE

select * from user1;

delete from user1 where rdate > '2018-01-23' limit 5;

# 실행 중인 process 보기와 중단
show processlist;
kill 154;

# TRUNCATE : 스키마만 남기고 모두 삭제(DDL)
select * from user1;
truncate user1;

# DROP : 테이블 삭제
drop table user1;
show tables;

# Functions
select ceil(12.345), round(12.345, 2), truncate(12.345, 2);

# date_format
use sakila;
select date_format(payment_date, "%Y-%m") from payment;

# concat
use world;
select code, name, concat(code, "/", name) from country;

# count
select count(*) from country;
select count(distinct(continent)) from country;

# if
# 도시의 인구가 100만 이상이면 big, 100만 미면 small 출력하는 컬럼을 생성
select name, population, if(population > 100*10000, "big", "small") as scale from city;

# case
# 국가별 인구가 10억 이상(big), 1억 이상 (medium), 1억 이하는 (small)
select name, population,
	case
		when population >= 100000 * 10000 then 'big'
        when population >= 10000 * 10000 then 'medium'
        else 'small'
	end as scale
	from country order by population desc;

# GROUP BY, HAVING
# 여러개의 동일한 데이터를 가지는 지정한 컬럼의 데이터를 합쳐주는 방법
# 결합함수 : count, max, min, sum, avg ...

#city 테이블에서 국가코드별 도시의 개수 출력
select countrycode, count(countrycode) as count from city group by countrycode order by count desc;

# country 테이블에서 대륙별 총 인구수 출력 3위까지 출력
select continent, sum(population) as total_population from country group by continent order by total_population desc limit 3;

# having
# 대륙의 인구수가 5억 이상인 대륙만 출력
select continent, sum(population) as total_population from country group by continent having total_population >= 500000000;

select ifnull(continent, 'total'), ifnull(region, 'total'), count(region) from country group by continent, region with rollup;

# 변수 선언
set @data = 1;
select @data;

set @rank =0;
select @rank := @rank + 1 as ranking, countrycode, name, population from city order by population desc limit 5;

# 외례키
# 데이터의 무결성을 지키기 위한 제약조건
use test;
drop table user2;

# 아래는 무결성이 깨지는 경우
create table user(
	user_id int primary key auto_increment,
    name varchar(20),
    addr varchar(20)
);

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int
);

insert into user(name, addr) values ('po', 'seoul'), ('jo', 'pusan');
select * from user;

insert into money(income, user_id) values (5000, 1), (6000, 3);
select * from money;

#  테이블을 생성할 때 설정, 무결성을 위한 외례키
drop table money;

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int,
    foreign key (user_id) references user(user_id)
);
insert into money(income, user_id) values (5000, 1), (6000, 3); # user_id가 서로 맞지 않으면 에러 발생
insert into money(income, user_id) value (5000, 1);
select * from money;

# 테이블 생성 후 수정해서 설정
drop table money;

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int
);
alter table money add constraint fk_user foreign key (user_id) references user (user_id);
desc money;
insert into money(income, user_id) values (5000, 1), (6000, 3); # user_id가 서로 맞지 않으면 에러 발생
insert into money(income, user_id) value (5000, 1);
select * from money;

# user(1, 2), money(1, 2)
delete from user where user_id =1 limit 10; # 무결성을 위해 서로 참조하는 데이터는 삭제가 불가능

# on delete, on update 설정
# cascade : 참조되는 테이블에서 데이터를 삭제하거나 수정하면 참조하는 테이블에서도 삭제, 수정한다.
# set null : 삭제, 수정하면 참조하는 테이블에서는 null로 바뀐다.
# no action : 참조하는 테이블에서 삭제하거나 수정하면 변경되지 않는다.(무결성이 깨지기 때문에 지양)
# set default : 참조하는 테이블에서 삭제하거나 수정하면 기본값으로 설정된다.
# restrict : 삭제, 수정에 대해 불가능하게 본다. (strict mode)

# 업데이트되면 같이 업데이트, 삭제되면 null로 변경
# on update > cascade, on delete > set null 설정
drop table money;

create table money(
	money_id int primary key auto_increment,
    income int,
    user_id int,
    foreign key (user_id) references user(user_id)
    on update cascade on delete set null
);
insert into money(income, user_id) values (5000, 1), (6000, 2);
select * from money;

update user set user_id = 3 where user_id = 2 limit 1;
select * from user;

delete from user where user_id = 3 limit 1;

Nyong’s GitHub