데이터베이스 모델링 및 sql문

mysql workbanch에서 파일 메뉴에 new model을 선택하면 모델링할 수 있는 화면이 나옵니다.

 

해당 데이터베이스의 이름을 바꿔줄 수 있고 테이블을 생성하고 컬럼을 추가할 수 있습니다.(테이블 추가할 때는 왼쪽에 테이블 아이콘을 클릭하고 본 화면에 클릭하면 테이블이 생깁니다.

 

테이블에 컬럼을 추가했으면 왼쪽 메뉴바에서 1:1 혹은 1:n으로 테이블끼리 관계성을 갖도록 할 수 있습니다.

 

그 다음 Database 메뉴에서 Forward Engineer를 선택하고 설정 선택 후 INDEX부분에 mysql과 workbanch의 버전이 다르기 때문에 visible을 지워줍니다.

 

그 후 스키마를 확인해보면 모델링한 데이터베이스가 생성된 것을 확인할 수 있습니다. 테이블을 확인하고 싶으면 옆에 i를 클릭하고 columns 탭에서 확인할 수 있습니다.

 

반대로 데이터베이스를 다이어그램으로 보고싶으면 Database 메뉴에서 Reverse Engineer를 통해 확인할 수 있습니다.

---

select from을 통해 데이터를 출력할 수 있습니다.

select 뒤에 어떤 컬럼을 출력할지, from 뒤에 어떤 테이블에서 출력할지를 써줍니다.

ex) select code, name, continent, population, gnp from country;

 

where 문을 추가하여 원하는 데이터를 출력할 수 있습니다.(비교, 논리, IN, LIKE)

ex) select code, name, population from country where population >= 100000000;

ex) select code, name, population from country where population >= 80000000 and population <= 100000000;

ex) select code, name, population from country where population between 80000000 and 100000000; (위의 예시랑 같음)

ex) select code, name, continent from country where continent="Asia" or continent="Africa";

ex) select code, name, continent from country where continent in ("Asia", "Africa"); (위 예시랑 같음)

ex) select code, name, continent from country where continent not in ("Asia", "Africa"); (위 예시의 반대)

ex) select code, name, governmentform from country where governmentform like "%Republic%";

 

order by 문을 통해 데이터를 정렬할 수 있습니다.

ex) select code, name, population from country order by population asc; (오름차순으로 정렬 - asc는 생략이 가능)

ex) select code, name, population from country order by population desc; (내림차순으로 정렬)

ex) city에서 국가코드를 오름차순 정렬, 인수수를 내림차순 정렬select name, countrycode, population from city order by countrycode asc, population desc; (앞쪽이 1차 정렬, 뒷부분은 2차 정렬)

ex) select code, continent, population from country where population > 50000000 and continent = "Asia" order by population desc;

 

limit 문을 통해서 데이터의 출력 수를 제한할 수 있습니다.

ex) select countrycode, name, population from city order by population desc limit 3; (데이터 3개만 출력)

ex) select countrycode, name, population from city order by population desc limit 2, 3; (2개 스킵하고, 그 후로 3개 출력)

ex) select code, name, continent, gnp, lifeexpectancy from country where lifeexpectancy >= 70 and continent in ("Asia", "Europe") order by GNP desc limit 3, 5;

ex) select code, name, surfacearea, population, population / surfacearea as pps from country where (population / surfacearea) >= 100; (연산자를 쓸 수도 있음)

 

group by 문으로 특정 컬럼을 기준으로 동일한 데이터를 합쳐서 출력하는 방법 / 기준 데이터가 아닌 컬럼은 결합 함수를 이용해서 출력합니다. (결합 함수: count, max, min, avg, sum...)

ex) select continent, count(name) from country group by continent;

ex) select continent, sum(population) as population from country group by continent order by population desc;

ex) select continent, sum(population) as population from country group by continent having population >= 500000000 order by population desc; (having 문으로 결과 목록에서 자신이 원하는 데이터를 가져올 수 있음)

ex) select continent, avg(population), avg(GNP), (avg(GNP) / avg(population)) as gpa from country group by continent having gpa >= 0.01 order by gpa desc;

 

DDL은 데이터 정의어로 데이터베이스, 테이블 CRUD를 담당

그 중에 create는 생성

create database test; (test 데이터베이스를 만듦)

use test; (데이터베이스 선택)

select database(); (선택한 데이터베이스 확인)

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

 

alter는 수정을 할 수 있습니다.

ex) show variables like "character_set_database";

ex) alter database test character set = utf8;

 

alter에서 add는 컬럼을 추가할 수 있습니다.

ex) alter table user2 add tmp text;

 

alter에서 modify는 컬럼을 수정할 때 사용합니다.

ex) alter table user2 modify tmp int;;

 

alter에서 drop은 컬럼을 삭제할 때 사용합니다.

ex) alter table user2 drop tmp;

 

그냥 drop은 데이터베이스, 테이블을 삭제할 때 사용합니다.

ex) drop database tmp;

ex) drop table tmp;

 

insert는 특정 테이블에 row 데이터를 넣을 때 사용합니다.

ex) insert into user1(user_id, name, email, age, rdate) values (1, "peter", "p@gmail.com", 23, "2017-03-23"), (2, "guemyong", "g@gmail.com", 24, "2020-11-14");

ex) insert into tmp select name, code, population from country order by population desc limit 3; (검색 결과를 테이블에 추가, 컬럼은 맞춰야 함)

 

update는 테이블의 row 데이터를 수정할 수 있습니다.

ex) update user1 set age=20, email="fds@gmail.com" where name="peter" limit 1; (limit을 쓰는 이유는 workbench에서 제한을 걸어두었기 때문)

 

delete, truncate는 테이블의 특정 데이터를 지울 수 있습니다. (truncate는 DDL입니다. 안의 내용만 지우고 스키마는 그대로 둡니다. delete는 DML이기 때문에 트랜젝션을 탑니다.)

ex) delete from user1 where rdate > "2017-01-01" limit 1;

ex) truncate user1; (테이블은 남겨두고 데이터만 삭제)

---

foreign key

데이터의 무결성을 지킬 수 있습니다. 들어가면 안되는 데이터를 잡아줍니다.(데이터가 안들어가지게 막습니다.)

unique나 primary 제약조건이 있어야 설정 가능합니다.

 

ex)

alter table money
add constraint fk_user
foreign key (user_id)
references user (user_id);

 

ex)

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

 

만약 foreign key로 연결이 돼 있는데 삭제하고 싶으면 다음 명령어를 사용합니다.

on delete / on update

ex)

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
);

cascade : 참조되는 테이블에서 삭제하거나 수정하면, 참조하는 테이블에서도 삭제하거나 수정합니다.