-- 뷰 테이블 생성
CREATE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 조건
-- 뷰 테이블 삭제
DROP VIEW [table name]
-- 뷰 테이블 생성 및 수정
CREATE OR REPLACE VIEW 뷰이름 AS
SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 조건
LOAD DATA LOCAL INFILE '파일 경로' INTO TABLE [데이터를 넣을 테이블명] FIELDS TERMINATED BY "열구분자" LINES TERMINATED BY '행구분자';
예)
LOAD DATA LOCAL INFILE 'D:\covid.csv' INTO TABLE covid_org FIELDS TERMINATED BY "," ENCLOSED BY '\"' LINES TERMINATED BY '\r\n';
D:\covid.csv 파일을 covid_org 테이블에 insert 하는데 열 구분자는 , 이고 내용에 ,가 들어간 경우 ENCLOSE BY를 통해 내용에 ,를 예외처리해준다.
행 구분자는 \r\n이다. (내용에 , 가 없는 경우 ENCLOSED BY를 제외해도된다)
이 쿼리문으로 CSV 파일로 대량 데이터 넣을 수 있습니다.
CREAT TABLE [테이블명] ( SELECT * FROM [복사할 테이블명])
이 쿼리문으로 기존 테이블의 내용을 카피하거나 특정 필드를 뽑아내거나 JOIN등으로 한번에 넣을 수 있습니다.
테이블을 삭제하고 새로운 테이블(뷰, 프로시저 .. 등)을 만들면 되겠지만 다시 만드는 순간 다른 db user(account)와의 관계(grant 해서 권한 준 것들)가 단절된다 겉으로는 테이블을 지우고 테이블을 수정해서 다시 만들었으니까 됐다고 생각하겠지만 이는 눈에 보이지 않는 관리번호의 변화를 불러오게 되어 관계단절을 불러오게 된다. 따라서 변경을 원할 때는 CREATE OR REPLACE를 사용을 권장한다.
어떤 테이블의 CRUD가 이루어졌을 때 어떤 전처리나 후처리를 하고 싶을 때 거는 조건이다.
delimiter | -- SQL문의 끝맺음기호를 ' | ' 로 바꿈
CREATE TRIGGER [트리거이름]
[BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON [테이블이름]
FOR EACH ROW
[실행문];
|
delimiter ; -- SQL문의 끝맺음기호를 ' ; ' 로 원상복구시킴
BEFORE -- CURD 적용 전에 실행문을 적용시키겠다.
AFTER -- CRUD 적용 후에 실행문을 적용시키겠다.
참고로 테이블 전체의 변화도 읽지만 컬럼 하나의 변화도 트리거를 걸 수 있다
예) Update of [컬럼명] on [테이블명]
-- 예시)
DELIMITER |
CREATE OR REPLACE TRIGGER covidTrigger -- 트리거명 covidTrigger
AFTER UPDATE ON test -- test 테이블에 업데이트가 된 후에 적용 시키겠다.
FOR EACH ROW -- 모든 row에 적용시키겠다는 의미
'
BEGIN
UPDATE test SET modi_date = NOW() WHERE id = 1;
END |
'
DELIMITER ;
drop table map1;
drop table map2;
create table map1(
name varchar(500) primary key,
lat decimal(18,10),
logt decimal(18,10)
);
create table map2(
name varchar(500) primary key,
lat decimal(18,10),
logt decimal(18,10)
);
insert into map1 values ('map1',37.49268,126.78442);
insert into map2 values ('map2',37.49084,126.78518);
# 0.00899 lat 1m
# 0.01133 logt 1m
delete from map1 where `name` = 'map1';
delete from map2 where `name` = 'map2';
SELECT
(6371*acos(cos(radians(b.lat))*cos(radians(a.lat))*cos(radians(a.logt)
-radians(b.logt))+sin(radians(b.lat))*sin(radians(a.lat))))
AS distance
FROM map1 as a, map2 as b
참고로 구글지도의 좌표간 거리와 네이버지도의 좌표간 거리는 차이가 있습니다(네이버 지도가 약 100m 더 멀음)
한쪽 테이블에는 하나의 값만 존재하고 그 값과 대응되는 다른쪽 테이블의 값은 여러개인 관계를 말합니다.지역정보가 담긴 테이블의 이름은 기본키(PK) 한 개이고 동아리명이 담긴 테이블에는 이름(FK)가 다수이기 때문에일대 다의 관계라고 이야기합니다.
대충 조인은 이런 교집합 합집합 차집합등의 관계를 생각하면 이해하기 쉽습니다.
저 파란색 영역의 부분만 데이터를 추출한다고 생각하시면 이해하기 쉬울 것입니다.
📝INNER JOIN (교집합)
DROP TABLE IF EXISTS REGION;
CREATE TABLE REGION(
nm varchar(20) PRIMARY KEY,
region varchar(20) NOT NULL
);
INSERT INTO REGION VALUES ('강호동', '경북');
INSERT INTO REGION VALUES ('김제동', '경남');
INSERT INTO REGION VALUES ('김용만', '서울');
INSERT INTO REGION VALUES ('이휘재', '경기');
INSERT INTO REGION VALUES ('박수홍', '경남');
DROP TABLE IF EXISTS USER;
CREATE TABLE USER(
no INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nm varchar(20) NOT NULL,
club varchar(20) NOT NULL
);
INSERT INTO USER(nm, club) VALUES ('강호동', '바둑');
INSERT INTO USER(nm, club) VALUES ('강호동', '축구');
INSERT INTO USER(nm, club) VALUES ('김용만', '축구');
INSERT INTO USER(nm, club) VALUES ('이휘재', '축구');
INSERT INTO USER(nm, club) VALUES ('이휘재', '봉사');
INSERT INTO USER(nm, club) VALUES ('박수홍', '봉사');
위에 테이블을 쿼리문으로 구현했습니다.
SELECT A.no,
A.nm,
A.club,
B.region
FROM USER A
INNER JOIN REGION B
ON A.nm = B.nm;
INNER JOIN은 SELECT와 같이 쓰입니다.
SELECT 필드
FROM 테이블(벤다이어그램의 왼쪽 서클)
INNER JOIN 테이블(벤다이어그램의 오른쪽 서클)
ON FK키 = PK키 (일대다의 관계는 PK키와 FK키로 연결)
이렇게되면 테이블A에 있는 필드와 테이블B에 있는 필드를 같이 볼 수 있게 해줍니다.
Alias를 필드에 안 주면 nm의 경우 USER 또는 REGION 테이블에 둘다 존재하기 때문에 어디를 봐야할지 SQL문 해석기가 이해를 못 해서걸어줘야합니다.
Document
no
nm
club
region
1
강호동
바둑
경북
2
강호동
축구
경북
3
김용만
축구
서울
4
이휘재
축구
경기
5
이휘재
봉사
경기
6
박수홍
봉사
경남
DROP TABLE IF EXISTS REGION;
CREATE TABLE REGION(
nm varchar(20) PRIMARY KEY,
region varchar(20) NOT NULL
);
INSERT INTO REGION VALUES ('강호동', '경북');
INSERT INTO REGION VALUES ('김제동', '경남');
INSERT INTO REGION VALUES ('김용만', '서울');
INSERT INTO REGION VALUES ('이휘재', '경기');
INSERT INTO REGION VALUES ('박수홍', '경남');
DROP TABLE IF EXISTS USER;
CREATE TABLE USER(
no INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
nm varchar(20) NOT NULL,
club varchar(20) NOT NULL
);
INSERT INTO USER(nm, club) VALUES ('강호동', '바둑');
INSERT INTO USER(nm, club) VALUES ('강호동', '축구');
INSERT INTO USER(nm, club) VALUES ('김용만', '축구');
INSERT INTO USER(nm, club) VALUES ('이휘재', '축구');
INSERT INTO USER(nm, club) VALUES ('이휘재', '봉사');
INSERT INTO USER(nm, club) VALUES ('박수홍', '봉사');
DROP TABLE IF EXISTS CLUB;
CREATE TABLE CLUB(
nm varchar(20) PRIMARY KEY,
room varchar(20)
);
INSERT INTO CLUB VALUES ('수영', '101호');
INSERT INTO CLUB VALUES ('바둑', '102호');
INSERT INTO CLUB VALUES ('축구', '103호');
INSERT INTO CLUB VALUES ('봉사', '104호');
3개의 테이블을 INNERJOIN 해보겠습니다.
CLUB을 추가시켰습니다. 도식화해서 표현하면 위의 그림입니다.
SELECT A.nm
, A.region
, B.club
, C.room
FROM REGION A
INNER JOIN USER B
ON A.nm = B.nm
INNER JOIN CLUB C
ON B.club = C.nm;
2개할 때랑 큰 차이는 없습니다. INNER JOIN후에 INNER JOIN절을 또 넣어주면 됩니다.
Document
nm
region
club
room
강호동
경북
바둑
102호
강호동
경북
축구
103호
김용만
서울
축구
103호
이휘재
경기
축구
103호
이휘재
경기
봉사
104호
박수홍
경남
봉사
104호
📝LEFT JOIN
SELECT B.no,
B.club,
A.nm,
A.region
FROM REGION A
LEFT JOIN USER B
ON A.nm = B.nm;
조인 조건에 해당하지 않더라도 왼쪽 테이블의 모든 행을 출력합니다.
Document
no
nm
club
region
1
강호동
바둑
경북
2
강호동
축구
경북
3
김용만
축구
서울
4
이휘재
축구
경기
5
이휘재
봉사
경기
6
박수홍
봉사
경남
NULL
NULL
김제동
경남
📝RIGHT JOIN
SELECT A.no,
A.nm,
A.club,
B.region
FROM USER A
RIGHT JOIN REGION B
ON A.nm = B.nm;
조인 조건에 해당하지 않더라도 오른쪽 테이블의 모든 행을 출력합니다.
Document
no
nm
club
region
1
강호동
바둑
경북
2
강호동
축구
경북
3
김용만
축구
서울
4
이휘재
축구
경기
5
이휘재
봉사
경기
6
박수홍
봉사
경남
NULL
NULL
NULL
경남
📝UNION (합집합)
SELECT A.nm FROM USER A
UNION
SELECT B.nm FROM REGION B
UNION
SELECT C.nm FROM CLUB C;
UNION의 경우 각 쿼리문의 결과를 합칩니다 (합칠 쿼리문의 출력 필드가 동일해야합니다)
SELECT PERIOD_ADD(202001, 13) AS period_add, PERIOD_DIFF(202001,202002) AS period_diff;
-- PERIOD_ADD_Format: YYMM or YYYYMM
-- PERIOD_DIFF_Format: YYMM or YYYYMM
