trigger
특정 이벤트가 발생했을 때 자동으로 실행되는 일련의 SQL 문들의 집합
이 이벤트는 INSERT, UPDATE, DELETE 등의 데이터 조작 문이 실행될 때 발생
사용이유
- 데이터의 무결성을 위해 사용
- 해당 테이블의 데이터가 삭제될때, 연관 테이블도 삭제해야한다던지 등의 경우에 사용
- 업무의 규칙을 보장
- 업무 처리 자동화
주요 이벤트
- BEFORE INSERT: 새로운 레코드가 삽입되기 전에 실행.
- AFTER INSERT: 새로운 레코드가 삽입된 후에 실행.
- BEFORE UPDATE: 레코드가 업데이트되기 전에 실행.
- AFTER UPDATE: 레코드가 업데이트된 후에 실행.
- BEFORE DELETE: 레코드가 삭제되기 전에 실행.
- AFTER DELETE: 레코드가 삭제된 후에 실행.
쿼리문
CREATE TRIGGER [트리거의 이름]
[BEFORE 또는 AFTER] [INSERT 또는 UPDATE 또는 DELETE] ON [대상 테이블]
FOR EACH ROW
BEGIN
-- 트리거에서 수행할 작업을 정의합니다.
END;[트리거의 이름]: 생성할 트리거의 이름을 지정[BEFORE 또는 AFTER]: 트리거가 실행될 시점을 지정.
BEFORE는 해당 이벤트가 발생하기 전에,
AFTER는 해당 이벤트가 발생한 후에 트리거가 실행[INSERT 또는 UPDATE 또는 DELETE]: 트리거가 연결될 이벤트를 지정.
INSERT, UPDATE, DELETE 중 하나를 선택.[대상 테이블]: 트리거가 연결될 대상 테이블을 지정.FOR EACH ROW: 각 레코드에 대해 트리거가 실행되어야 함을 의미.BEGIN ... END: 트리거에서 수행할 작업을 정의.
- DELETE trigger 예시코드 -
--1. delete 이벤트가 들어갈 빈 테이블 만들기
DROP TABLE if EXISTS deletedMember;
CREATE TABLE deletedMember
AS SELECT mem_id, mem_name, addr FROM member WHERE 1 = 0;
ALTER TABLE deletedMember ADD DATE DATETIME; -- 필요컬럼 추가
ALTER TABLE deletedMember ADD userName VARCHAR(15); - 필요컬럼 추가
SELECT * FROM deletedMember;
--2--delete 트리거 생성
DROP TRIGGER if EXISTS trig_del;
delimiter $$
CREATE TRIGGER trig_del
AFTER DELETE
ON member
FOR EACH ROW
BEGIN
INSERT INTO deletedMember
VALUES( old.mem_id ,old.mem_name, old.addr,
CURDATE(), CURRENT_USER());
END $$
delimiter ;
--3 트리거 test
DELETE FROM member WHERE mem_id='APN';
SELECT * FROM deletedMember; -- 삭제했던 멤버 등록
SELECT * FROM member; -- 삭제멤버 안보임-UPDATE trigger 예시코드 -
-- 1. update 이벤트가 들어갈 빈 테이블 만들기
DROP TABLE if EXISTS updateTest;
CREATE TABLE UPDATETest
(
mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
mem_number INT NOT NULL,
addr CHAR(2) NOT NULL,
modType CHAR(2),
modDate DATE,
modUser VARCHAR(30)
);
--2. update 트리거 생성
DROP TRIGGER if EXISTS trg_memberUpdate;
delimiter $$
CREATE TRIGGER trg_memberUpdate
AFTER update
ON member
FOR EACH row
BEGIN
INSERT INTO UPDATETest VALUES( OLD.mem_id, OLD.mem_name,
OLD.mem_number,OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$
delimiter ;
-- 3. test
SELECT * FROM updateTest; -- 처음은 비어있음
UPDATE member SET addr='영국' WHERE mem_id='apn';
SELECT * FROM updateTest; -- update내역 들어감
DELETE FROM member WHERE mem_number >= 7;
SELECT * FROM updateTest; -- 작동 안되었음을 확인. 업데이트가 아니기때문에index
특정 열(칼럼)에 대한 검색 속도를 향상시키기 위해 사용되는 데이터 구조
인덱스를 사용하면 데이터베이스 엔진은 테이블을 순차적으로 검색하는 대신 인덱스를 사용하여 빠르게 원하는 데이터를 찾을 수 있다.
인덱스의 종류
- 기본 키(Primary Key): 기본 키는 각 레코드를 식별하는 데 사용되는 열에 대한 인덱스. 기본 키는 해당 테이블에서 유일해야 하며 NULL 값을 허용하지 않음.
- 고유 인덱스(Unique Index): 고유 인덱스는 인덱스가 가리키는 열의 값이 모두 고유해야 하는 경우 사용. 즉, 중복된 값이 허용되지 않음.
- 외래 키 인덱스(Foreign Key Index): 외래 키 인덱스는 다른 테이블의 기본 키와 연결된 테이블의 열에 대한 인덱스.
- 일반 인덱스(Index): 일반 인덱스는 고유하지 않은 열에 대한 인덱스로, 중복된 값이 허용.
쿼리문 예시
ALTER TABLE cluster
ADD CONSTRAINT
PRIMARY KEY (mem_id);
-- pk/fk 제약조건이 들어가 있으면 클러스트형 인덱스(메인 인덱스)가 된것임
-- 인덱스가 걸려서 가나다 순으로 자동정렬됨.
ALTER TABLE second
ADD CONSTRAINT
UNIQUE (mem_id);
-- uniue 제약조건을 걸어주면 보조인덱스 적용한것, 겉으로는 변화가 없음.
SHOW INDEX FROM member; -- 인덱스 확인
CREATE INDEX idx_member_addr
ON member (addr);
-- 강제적으로 다른 필드에다가 인덱스 걸어주기. pk로 하면 자동으로 인덱스 걸림
-- 제약조건으로 건 인덱스가 우선순위다.
-- 인덱스를 걸어놓는다는건 결국 메모리를 잡아먹는것이기에 남발하면 좋지 않음.
DROP INDEX idx_column_name ON your_table; -- 인덱스 삭제
ANALYZE TABLE member; -- 지금까지 만든 인덱스를 모두 적용
제약조건으로 인덱스가 걸려있는데 제약조건을 뺴면 보조인덱스 기준으로 정렬기본 키(Primary Key) 와 index 차이
기본 키 (Primary Key)
- 역할: 기본 키는 테이블에서 각 레코드(행)를 식별하는 데 사용되는 열(또는 열의 조합). 즉, 각 레코드는 기본 키 값을 가져야 하며 중복이 허용되지 않습니다.
- 고유성: 기본 키 값은 각 레코드마다 고유해야 합니다.
- NULL 값: 기본 키는 NULL 값을 가질 수 없습니다.
- 테이블 당 하나: 테이블 당 하나의 기본 키만 가질 수 있습니다.
- 자동 증가(auto-increment): 일반적으로 정수형 기본 키는 자동으로 1씩 증가하는 방식으로 사용
인덱스 (Index)
- 역할: 인덱스는 데이터베이스에서 데이터를 빠르게 조회하기 위한 구조로, 특정 열(또는 열의 조합)에 대한 검색 성능을 향상시킵니다.
- 고유성: 인덱스는 중복된 값을 가질 수 있습니다.
- N- ULL 값: 인덱스는 NULL 값을 가질 수 있습니다.
- 여 러 개 가능: 테이블당 여러 개의 인덱스를 가질 수 있습니다.
- 수동으로 생성: 인덱스는 수동으로 생성하며, 특정 열에 대한 인덱스를 생성하는 것 외에도 여러 열의 조합으로 복합 인덱스를 만들 수 있습니다.
Profile
쿼리의 성능 측정 정보를 보여주는 명령어. 이 명령어를 사용하여 쿼리의 실행 시간 및 각 단계에서 소요된 시간 등을 확인
SELECT @@profiling;
-- 값이 0이면 밑에 명령어를 써서 1로 바뀌어야 profile을 볼수 있다.
SET profiling=1;
-- profile 보여주는 명령어 디폴트는 꺼져있음.
SHOW PROFILE;
현재까지 실행된 모든 쿼리의 성능 측정 정보를 보여줍니다.
SHOW PROFILE FOR QUERY n;
특정 쿼리 번호(n)에 대한 성능 측정 정보를 보여줍니다.
SHOW PROFILE CPU FOR QUERY n;
특정 쿼리 번호(n)에 대한 CPU 사용량 정보를 보여줍니다.
SHOW PROFILE MEMORY FOR QUERY n;
특정 쿼리 번호(n)에 대한 메모리 사용량 정보를 보여줍니다.
SHOW PROFILE BLOCK IO FOR QUERY n;
특정 쿼리 번호(n)에 대한 블록 I/O 정보를 보여줍니다.
sequence
일련의 숫자를 생성하는 객체로, 주로 고유한 식별자를 생성하는 데 사용 AUTO_INCREMENT 와 같은기능
SELECT NEXT VALUE FOR s;
이 쿼리는 시퀀스 s에서 다음 값을 가져옵니다.
실제로는 해당 값을 삽입하거나 업데이트에 사용하지 않고도 다음 값을 가져오는 방법
SELECT NEXTVAL(s);
이 쿼리는 시퀀스 s에서 다음 값을 가져오는 또 다른 문법
SELECT PREVIOUS VALUE FOR s;
이 쿼리는 시퀀스 s에서 이전 값을 가져옵니다.
SELECT LASTVAL(s);
이 쿼리는 시퀀스 s에서 생성된 마지막 값을 가져옵니다.
LASTVAL은 현재 세션에서 생성된 가장 최근의 값을 가져오는 데 사용
INSERT INTO member VALUES(s.nexval,'aa','하히후',....);
이 INSERT 문은 member 테이블에 새 레코드를 삽입합니다.
첫 번째 열은 시퀀스 s에서 다음 값으로 채워짐(auto_increment됨)
create sequence s1;
1부터 시작하여 1씩 증가하는 s1이라는 새로운 시퀀스를 생성
create table t1 (a int primary key default(next value for s1), b int);
a라는 기본 키 열을 가진 t1 테이블을 생성.
a 열은 정수이며 기본값이 시퀀스 s1에서 다음 값으로 설정됩니다.
insert into t1 (b) values (1),(2);
t1 테이블에 두 개의 레코드를 삽입.
a 열은 시퀀스 s1에서 다음 값으로 채워지며, b 열은 각각 1과 2로 설정.
ALTER SEQUENCE s RESTART 50;
시퀀스 s를 값 50으로 다시 시작
SELECT NEXTVAL(s);
시퀀스 s가 다시 시작된 후 다음 값을 가져옵니다.
SELECT SETVAL(s, 100);
시퀀스 s의 현재 값을 100으로 설정.
DROP SEQUENCE s;
데이터베이스에서 시퀀스 s를 삭제.pivot
특정 열의 값을 행으로, 특정 행의 값을 열로 변환하는 것을 의미
CREATE TABLE pivotTest
( uName CHAR(3),
season CHAR(2),
amount INT );
SELECT * FROM pivotTest;
INSERT INTO pivotTest VALUES
('김범수' , '겨울', 10) , ('윤종신' , '여름', 15) ,
('김범수' , '가을', 25) , ('김범수' , '봄', 3) ,
('김범수' , '봄', 37) , ('윤종신' , '겨울', 40),
('김범수' , '여름', 14) ,('김범수' , '겨울', 22) ,
('윤종신' , '여름', 64) ;
SELECT uName,
SUM(IF(season='봄', amount, 0)) AS '봄',
SUM(IF(season='여름', amount, 0)) AS '여름',
SUM(IF(season='가을', amount, 0)) AS '가을',
SUM(IF(season='겨울', amount, 0)) AS '겨울',
SUM(amount) AS '합계' FROM pivotTest GROUP BY uName ;피벗전
피벗적용
etc
SELECT CAST('2020-10-19 12:35:29.123' AS DATE) AS 'DATE' ;
SELECT CAST('2020-10-19 12:35:29.123' AS TIME) AS 'TIME' ;
SELECT CAST('2020-10-19 12:35:29.123' AS DATETIME) AS 'DATETIME' ;
SELECT CAST('2022$12$12' AS DATE);
SELECT CAST('2022/12/12' AS DATE);
SELECT CAST('2022%12%12' AS DATE);
SELECT CAST('2022@12@12' AS DATE);
SELECT * FROM buy;
SELECT AVG(amount) AS '평균 구매 개수' FROM buy ;
SELECT CAST(AVG(amount) AS SIGNED INTEGER) AS '평균 구매 개수' FROM buy ;
-- 또는
SELECT CONVERT(AVG(amount) , SIGNED INTEGER) AS '평균 구매 개수' FROM buy ;
SELECT num, CONCAT(CAST(price AS CHAR(10)), 'X', CAST(amount AS CHAR(4)) ,'=' ) AS '단가X수량',
price*amount AS '구매액'
FROM buy ;
SELECT ADDDATE('2022-01-01', INTERVAL 31 DAY), ADDDATE('2022-01-01', INTERVAL 1 MONTH);
SELECT SUBDATE('2022-01-01', INTERVAL 31 DAY), SUBDATE('2022-01-01', INTERVAL 1 MONTH);
SELECT ADDTIME('2022-01-01 23:59:59', '1:1:1'), ADDTIME('15:00:00', '2:10:10');
SELECT SUBTIME('2022-01-01 23:59:59', '1:1:1'), SUBTIME('15:00:00', '2:10:10');
SELECT YEAR(CURDATE()), MONTH(CURRENT_DATE()), DAYOFMONTH(CURRENT_DATE);
SELECT HOUR(CURTIME()), MINUTE(CURRENT_TIME()), SECOND(CURRENT_TIME), MICROSECOND(CURRENT_TIME);
SELECT DATE(NOW()), TIME(NOW());
SELECT DATEDIFF('2022-01-01', NOW()), TIMEDIFF('23:23:59', '12:11:10');
SELECT DAYOFWEEK(CURDATE()), MONTHNAME(CURDATE()), DAYOFYEAR(CURDATE());
SELECT LAST_DAY('2022-02-01');
SELECT MAKEDATE(2022, 32);
SELECT MAKETIME(12, 11, 10);
SELECT SLEEP(5);
SELECT '5초후에 이게 보여요';
-- https://mariadb.com/kb/en/server-system-variables
SHOW VARIABLES;
SHOW variables LIKE 'max%';
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES;
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES;
-- 로그인과 동시에 디비 시스템에 있는 값 확인하기
mysql -u root -p'계정 비밀번호' -e "SHOW VARIABLES;"
mysql -u root -p'계정 비밀번호' -e "SHOW VARIABLES LIKE 'max_connect%';"
/*
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
[GLOBAL 변수와 SESSION 변수]
영향을 미치는 범위
GLOBAL 변수 : MySQL 서버에서 단 하나의 값을 가지며, 시스템에 영향을 미치는 변수입니다.
SESSION 변수 : 각 세션에만 적용되는 값으로 커넥션 단위로 값을 변경할 수 있습니다.
GLOBAL, SESSION 둘 다 해당하는 변수도 존재합니다.
[Static변수와 Dynamic변수]
값을 변경할 때 서버를 재시작해야 하는지 여부에 따른 분류
Static 변수 : 서버를 재시작해야지만 값이 변경됩니다.
Dynamic 변수 : 서버를 재시작하지 않고도 값을 변경할 수 있습니다.
*/
SELECT * INTO OUTFILE 'C:/SQL/userTBL.txt' FROM userTBL; -- 역슬러쉬로 되어있는 경로를 슬러쉬로 바꿔야 함.
SELECT * INTO OUTFILE 'C:/dell/member.txt' FROM member; -- 파일로 복사
CREATE TABLE c_emp2222
AS
SELECT * FROM emp WHERE 1=0; -- 테이블 구조만 복사 할때
DESC c_emp2222;
CREATE TABLE memberTBL LIKE member; -- 테이블 구조랑 제약, 인덱스 복사 할때
새로운 시작. 그리고 도약
0개의 댓글
