Nomad Writer

alter table "테이블명" add column "GEOM" geometry;

update "테이블명" set "GEOM" = st_makepoint("x 좌표(경도)", "y 좌표(위도)"); 

- Mysql에는 Oracle과 달리 rownum의 기능이 없다.

- 따라서 rownum을 설정하여 활용해보자.

​

예제)

set @no:=0;

​

select d.rownum, d.name, d.dateTime

from (select @no:=@no+1 as rownum, name, animal_id, dateTime

from animal_ins

order by dateTime desc) d

where rownum = 1;

1) 그룹화(group by) / name 열로 group by 
select name from sample group by name;
select name, count(name), sum(quantity) from sample group by name;
※ group by는 집계함수와 함께 사용해야 유의미
※ 내부처리 순서 : where → group by → having → select → order by

2) 집계한 결과 정렬하기 : name 열로 그룹화해 합계를 구하고 내림차순으로 정렬
select name, count(name), sum(quantity)
from sample 
group by name 
order by sum(quantity) desc;

1) 집계함수 
 - count / sum / avg/ min / max

2) sample의 행의 개수 구하기
select count(*) from sample;
select count(*) from sample where name = 'B';

3) distinct로 중복 제거
select distinct name from sample;
select count(all name), count(distinct name) from sample;

4) sum으로 합계 구하기
select sum(quantity) from smaple

5) avg로 평균값 구하기
select avg(quantity), sum(quantity)/count(quantity) from sample;

6) min, max로 최솟값, 최댓값 구하기
select min(quantity), max(quantity), min(name), max(name) from sample;

[meal database를 활용하여, 내부에 Table, column, primary key, foreign key 설정하기]

​

USE MEAL;

​

Create Table Consumer

(

CONS_NUMBER INT(11) NOT NULL,

CONS_ID VARCHAR(50) NOT NULL,

CONS_NAME VARCHAR(50) NULL,

CONS_PW VARCHAR(50) NULL,

CONS_ADDRESS VARCHAR(50) NULL,

CONS_MOBILE VARCHAR(50) NULL

);

​

Create table follow

(

Follow_number INT(11) not NULL,

PROD_ID VARCHAR(50) NOT NULL,

CONS_ID VARCHAR(50) NOT NULL

);

​

Create table menu

(

MEMU_NUMBER INT(11) NOT NULL,

MENU_NAME VARCHAR(50) NOT NULL,

PROD_ID VARCHAR(50) NOT NULL,

CONS_ID VARCHAR(50) NOT NULL,

orders_name VARCHAR(50) NOT NULL,

MENU_INTRO VARCHAR(50) NULL,

MENU_STARTING_DAY VARCHAR(50) not NULL,

MENU_ENDING_DAY VARCHAR(50) NULL,

MENU_PHOTO VARCHAR(50) NULL,

MENU_UCC VARCHAR(50) NULL,

MENU_AVAILABLE_QUAN INT(11) NULL,

MENU_ORIGIN_PLACE VARCHAR(50) NULL

);

​

Create table orders

(

ORDERs_NUMBER INT(11) NOT NULL,

orders_name VARCHAR(50) NOT NULL,

PROD_ID VARCHAR(50) NOT NULL,

CONS_ID VARCHAR(50) NOT NULL,

MENU_NAME VARCHAR(50) NOT NULL,

MENU_STARTING_DAY VARCHAR(50) not NULL,

ORDERs_REG_DATE DATE NULL,

ORDERs_REG_TIME TIME NULL,

ORDERs_QUAN INT(11) NULL

);

​

Create table producer

(

PROD_NUMBER INT(11) NOT NULL,

PROD_ID VARCHAR(50) NOT NULL,

PROD_NAME VARCHAR(50) NULL,

PROD_PW VARCHAR(50) NULL,

PROD_ADDRESS VARCHAR(50) NULL,

PROD_STORENAME VARCHAR(50) NULL,

PROD_MOBILE VARCHAR(50) NULL,

PROD_INTRO VARCHAR(50) NULL,

PROD_AVAILABLE_PLACE VARCHAR(50) NULL,

PROD_PHOTO VARCHAR(50) NULL,

PROD_REGISTRATION VARCHAR(50) NULL,

PROD_STARS INT(11) NULL,

PROD_SALES INT(11) NULL

);

​

Create table Review

(

REVIEW_NUMBER INT(11) NOT NULL,

REVIEW_TITLE VARCHAR(50) NOT NULL,

PROD_NUMBER INT(11) NOT NULL,

PROD_ID VARCHAR(50) NOT NULL,

CONS_NUMBER INT(11) NOT NULL,

CONS_ID VARCHAR(50) NOT NULL,

MENU_NAME VARCHAR(50) NOT NULL,

MENU_STARTING_DAY VARCHAR(50) not NULL,

REVIEW_SOURCE VARCHAR(50) NULL,

REVIEW_PHOTO VARCHAR(50) NULL,

REVIEW_STARS INT(11) NULL,

REVIEW_REPLY VARCHAR(50) NULL

);

​

​

ALTER TABLE Consumer ADD PRIMARY KEY (CONS_ID);

ALTER TABLE FOLLOW ADD PRIMARY KEY (FOLLOW_NUMBER);

ALTER TABLE MENU ADD PRIMARY KEY (MENU_NAME, MENU_STARTING_DAY);

ALTER TABLE ORDERS ADD PRIMARY KEY (ORDERS_name);

ALTER TABLE PRODUCER ADD PRIMARY KEY (PROD_ID);

ALTER TABLE REVIEW ADD PRIMARY KEY (REVIEW_NUMBER, REVIEW_TITLE);

ALTER TABLE MENU ADD CONSTRAINT FK_MENU_CONSUMER FOREIGN KEY (CONS_ID) REFERENCES CONSUMER (CONS_ID);

ALTER TABLE MENU ADD CONSTRAINT FK_MENU_PRODUCER FOREIGN KEY (PROD_ID) REFERENCES PRODUCER (PROD_ID);

ALTER TABLE MENU ADD CONSTRAINT FK_MENU_ORDERs FOREIGN KEY (ORDERs_name) REFERENCES ORDERs (ORDERs_name);

ALTER TABLE REVIEW ADD CONSTRAINT FK_REVIEW_CONSUMER FOREIGN KEY (CONS_ID) REFERENCES CONSUMER (CONS_ID);

ALTER TABLE REVIEW ADD CONSTRAINT FK_REVIEW_PRODUCER FOREIGN KEY (PROD_ID) REFERENCES PRODUCER (PROD_ID);

ALTER TABLE REVIEW ADD CONSTRAINT FK_REVIEW_MENU FOREIGN KEY (MENU_NAME, MENU_STARTING_DAY) REFERENCES MENU (MENU_NAME, MENU_STARTING_DAY);

ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_CONSUMER FOREIGN KEY (CONS_ID) REFERENCES CONSUMER (CONS_ID);

ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_PRODUCER FOREIGN KEY (PROD_ID) REFERENCES PRODUCER (PROD_ID);

ALTER TABLE ORDERS ADD CONSTRAINT FK_ORDERS_MENU FOREIGN KEY (MENU_NAME, MENU_STARTING_DAY) REFERENCES MENU (MENU_NAME, MENU_STARTING_DAY);

ALTER TABLE FOLLOW ADD CONSTRAINT FK_FOLLOW_CONSUMER FOREIGN KEY (CONS_ID) REFERENCES CONSUMER (CONS_ID);

ALTER TABLE FOLLOW ADD CONSTRAINT FK_FOLLOW_PRODUCER FOREIGN KEY (PROD_ID) REFERENCES PRODUCER (PROD_ID);

만약 spatialdb란 DB를 활용하여 작업한다면

​

1. use spatiadb; // spatialdb db 사용해서 [spatialdata] 테이블 및 세부 [column] setting

​create table spatialdata 

(geo_id int(11) not null auto_increment,

geo_name varchar(50) null,

geo_location geometry not null,

primary KEY(geo_id),

spatial INDEX geo_location (geo_location));

​

2. geo_location column에 해당하는 위치좌표 입력

SET @g = ST_GEOMFROMTEXT('POINT(127.031330 37.515611)');

​

insert into spatialdata(geo_name, geo_location)

values ('hak-dong', @g);

​

3. 위경도별로 geo_location 좌표 받아오기

SELECT geo_id, geo_name,

ST_X(geo_location) AS longitude,

ST_Y(geo_location) AS Latitude

FROM spatialdb.spatialdata

​

1) insert 삽입하기
insert into 테이블명 values (값1, 값2, 값3);
insert into 테이블명(열1, 열2 ...) values (값1, 값2 ...);

2) delete 삭제하기
delete from 테이블명 where 조건식;

3) update 데이터 갱신하기
update 테이블명 set 열1 = 값1, 열2 = 값2, ... where 조건식;
- update 명령에서는 where 조건에 일치하는 '모든 행'이 갱신된다.
- null 값으로 갱신하기
update 테이블명 set 열1 = null;

- 컬럼 추가 : ALTER TABLE (테이블명) ADD COLUMN (컬럼명) (데이터타입) (제약조건);

  예시) alter table make_table add column make_columns character varying Default;

- 컬럼명 변경 : ALTER TABLE (테이블명) RENAME COLUMN (현재컬럼명) TO (새로운컬럼명);

- 컬럼 데이터 타입 변경: ALTER TABLE (테이블명) ALTER COLUMN (컬럼명) TYPE (데이터타입);

- Default 값 추가 / 제거 : 

  1) 추가 SQL : ALTER TABLE (테이블명) ALTER COLUMN (컬럼명) SET DEFAULT (데이터);

  2) 제거 SQL : ALTER TABLE (테이블명) ALTER COLUMN (컬럼명) DROP DEFAULT

- NOT NULL 추가 / 제거 :

  1) 추가 SQL : ALTER TABLE (테이블명) ALTER COLUMN (컬럼명) SET NOT NULL;

  2) 제거 SQL : ALTER TABLE (테이블명) ALTER COLUMN (컬럼명) DROP NOT NULL;

- 컬럼 삭제 : ALTER TABLE (테이블명) DROP COLUMN (컬럼명);

참고자료 : https://www.postgresql.org/docs/9.5/reference.html