TigerCow.Door



안녕하세요 문범우입니다.

이번 포스팅에서는 IN, EXISTS, NOT IN, NOT EXISTS 에 대해서 보다 상세하게 알아보려고 합니다.

해당 내용은 꼭 SQL Server 뿐만 아니라 MySQL 등에서도 포괄적으로 적용되는 내용입니다.



0. 데이터 세팅


먼저 각 구문에 대해서 비교를 할 때 보다 쉽게 확인할 수 있도록 가상 데이터를 세팅해보도록 하겠습니다. 총 2개의 테이블을 생성하며 각 테이블의 이름과 데이터는 아래와 같습니다.


SELECT * FROM TB_FOOD;




SELECT * FROM TB_COLOR;




1. IN


SELECT * FROM TB_FOOD f

WHERE f.number IN (SELECT c.number FROM TB_COLOR c);


위와 같은 쿼리를 실행하면 어떤 결과가 나올까요?

먼저 결과를 살펴보면 다음과 같습니다.



이는 우리가 어느정도 예상할 수 있는 결과입니다. 하지만 실제로 IN 을 포함한 쿼리가 어떻게, 어떤 식으로 작동되는지 알아야 이후에 EXISTS 또는 NOT IN / NOT EXISTS와 헷갈리지 않습니다.


위의 쿼리에서는 제일먼저 TB_COLOR 테이블에 접근하게 됩니다.

즉, IN 뒤에 있는 괄호의 서브쿼리를 먼저 실행해서 그에 대한 요소를 가져오는 것이죠.

따라서 사실 IN뒤에 괄호안에는 서브쿼리 이외에도 직접 요소값을 적어줄 수 있습니다.


이후에는 TB_FOOD에서 하나의 레코드를 가져오며 그 레코드의 number 값이 앞에서 가져온 IN 이하의 요소들에 포함되어 있는지를 체크합니다. 그리고 IN 이하의 요소들 중 하나라도 일치한다면 그 레코드를 출력하게 되는 것이죠.


여기서 중요한 것은, 쿼리에서 TB_COLOR에 먼저 접근하여, number 값들을 가져와 리스트로 IN 이하에 뿌려주고, 그 이후에 TB_FOOD에서 하나의 레코드씩 IN 이하의 요소들과 일치하는지 비교한다는 것 입니다.



2. EXISTS


그럼 EXISTS는 어떻게 동작하는지 쿼리와 그 결과를 보도록 합시다.


SELECT * FROM TB_FOOD f

WHERE EXISTS (SELECT c.number FROM TB_COLOR c);



무언가 이상합니다. 우리가 기대했던 결과와는 달리 TB_FOOD 테이블이 그대로 출력되었습니다. 왜 이럴까요? 이는 EXISTS 구문에 대해서 정확히 알지 못하고 잘못 사용하였기 때문에 나온 결과입니다.


위의 쿼리를 기준으로 DB가 어떻게 동작하는지 한번 알아보겠습니다.

IN구문에서는 IN 이후에 나오는 소괄호 내부의 서브쿼리에 대해서 먼저 접근하였습니다. 하지만 EXISTS 구문에서는 다릅니다. 먼저 TB_FOOD에 접근하여 하나의 레코드를 가져오고 그 레코드에 대해서 EXISTS 이하의 서브쿼리를 실행하고 서브쿼리에 대한 결과가 '존재하는지'를 확인합니다.


예시를 들어 생각해보면, 제일 처음에 [ 1 / 치킨 ] 이라는 레코드를 가져왔을 것이고, 해당 레코드에 대해서 SELECT c.number FROM TB_COLOR c 쿼리를 통해 결과가 나오는지 확인합니다. 이때 서브쿼리에 대해 어떠한 결과라도 존재하기만 한다면 참이 되어서 [ 1 / 치킨 ] 레코드가 출력됩니다.

그런데 SELECT c.number FROM TB_COLOR c 쿼리는 사실 TB_FOOD의 어떠한 레코드하고도 연관이 없이 항상 결과값을 가지는 쿼리입니다. 따라서 TB_FOOD의 모든 레코드가 출력되는 것 이죠.


그럼 이를 우리가 기대하는 결과대로 출력하도록 하기 위해서는 다음과 같이 쿼리를 수정하면 됩니다.


SELECT * FROM TB_FOOD f

WHERE EXISTS (SELECT c.number FROM TB_COLOR c WHERE c.number = f.number);



이렇게 나온 결과는 사실 IN 구문과 같은 결과를 출력합니다. 하지만 내부적으로 쿼리가 동작하는 방식은 아예 다르다는 것에 주의하시길 바랍니다. 그러한 내부 로직에 따라서 성능차이도 크게 발생하기 때문입니다.



3. NOT IN


이번에는 NOT IN 구문입니다.

먼저 쿼리와 그 결과를 보고 함께 생각해보겠습니다.


SELECT * FROM TB_FOOD f

WHERE f.number NOT IN (SELECT c.number FROM TB_COLOR c);



위의 쿼리를 실행하니 위의 사진과 같이 아무런 결과도 출력되지 않았습니다. 왜 그럴까요?

우리가 처음에 알아본 IN의 방식에 대해서 알아봅시다. IN은 먼저 소괄호의 서브쿼리를 실행합니다. 그럼 SELECT c.number FROM TB_COLOR c 의 쿼리가 실행되고 그 결과로 다음의 리스트가 반환됩니다.


( 1, 2, 3, 4, 5, 6, NULL )


즉, 초기의 쿼리는 다음과 같은 쿼리인 것입니다.


SELECT * FROM TB_FOOD f

WHERE f.number NOT IN ( 1, 2, 3, 4, 5, 6, NULL );


그럼 이제 TB_FOOD에서 하나의 레코드씩 가져올 것이고 IN이 아니라 NOT IN 구문이기 때문에 소괄호의 요소들과 일치하지 않아야 결과로 반환됩니다.


TB_FOOD의 레코드들 중에서 [ 7 / 사탕 ] 레코드를 예로 들어서 생각해보면 해당 레코드의 number 값인 7이 NOT IN 이하의 소괄호에 있는지 확인하면 됩니다. 분명히 7이라는 요소는 존재하지 않습니다. 따라서 우리의 생각대로 라면 해당 레코드는 결과로 출력되어야 하는데 위에서 본 것 처럼 출력되지 않았습니다. 왜 일까요?


이는 DB 에서 해당 요소가 NOT IN 이하의 소괄호의 요소들에 대한 포함여부를 어떻게 판단하는지를 알면 쉽게 이해할수 있습니다. 사실 위의 쿼리는 아래의 쿼리와 같이 동작함으로써 NOT IN 이하의 소괄호에 대한 포함 여부를 판단하게 됩니다.


SELECT * FROM TB_FOOD f

WHERE f.number != 1

AND f.number != 2

AND f.number != 3

AND f.number != 4

AND f.number != 5

AND f.number != 6

AND f.number != NULL;

즉, NOT IN 구문은 TB_FOOD에서 가져온 레코드의 number 값이 소괄호의 모든 요소들과 일치하지 않는지를 체크하는 것입니다. 그런데 위에서는 number 값이 NULL과 연산을 진행하게 되는데, 이때 NULL과의 비교연산은 항상 UNKNOWN 값을 반환하게 됩니다. 따라서 WHERE 절 이하가 TRUE가 아니므로 해당 레코드가 출력되지 않게 되는 것이죠.


이렇게 NOT IN이 어떻게 동작하는지를 알고보니 결국 TB_COLOR에 존재하는 NULL 때문에 우리가 기대하던 결과가 나오지 않음을 알 수 있었습니다. 그럼 우리가 기대한 결과가 나오게 하려면 다음과 같이 쿼리를 수정하면 됩니다.


SELECT * FROM TB_FOOD f

WHERE f.number NOT IN (SELECT c.number FROM TB_COLOR c WHERE c.number IS NOT NULL);


 



4. NOT EXISTS


그럼 마지막으로 NOT EXISTS에 대해서 알아보도록 하겠습니다.

오히려 NOT EXISTS는 위에서 EXISTS에 대해서 이해했다면 크게 어려운 점이 없습니다. 하지만 그 결과가 NOT IN과 약간 다르죠. 쿼리와 결과를 먼저 보도록 하겠습니다.


SELECT * FROM TB_FOOD f

WHERE NOT EXISTS (SELECT c.number FROM TB_COLOR c WHERE c.number = f.number);



위에서 NOT IN을 사용했을 때에는 number 값이 NULL인 레코드는 출력되지 않았습니다. 그 이유를 다시 생각해보자면, IN 구문은 요소간에 비교 연산으로 레코드가 출력되는데 NULL 값에 대한 비교연산은 항상 UNKNOWN을 반환하기 때문이었습니다. 

하지만 앞에서 알아볼 때 EXISTS 구문은 다르게 동작했습니다. 위 쿼리를 기준으로 한다면 먼저 TB_FOOD에서 레코드를 가져오고 해당 레코드의 number를 NOT EXISTS 이하의 서브쿼리에 전달하여 해당 서브쿼리에서 값이 존재하는지를 확인합니다. EXISTS 구문이었다면 값이 존재할 때 해당 레코드를 출력하지만, NOT EXISTS 구문이기에 해당 서브쿼리의 값이 존재하지 않으면 해당 레코드를 출력합니다.


여기서 NULL이 출력하는 과정을 한번 더 자세하게 알아보자면, [ NULL / 타코 ] 레코드를 예로 들었을 떄, number = NULL 입니다. 따라서 NOT EXISTS 이하의 쿼리를 확인해보면 다음과 같을 것 입니다.


SELECT c.number FROM TB_COLOR c WHERE c.number = NULL;


이때 우리가 NOT IN에서 알아본 것과 같이 NULL에 대한 비교연산은 항상 UNKNOWN 값을 반환하므로 해당 쿼리의 결과가 존재하지 않게 되고, 이에 따라서 [ NULL / 타코 ] 레코드가 출력되는 것 입니다.



이렇게 IN, EXISTS, NOT IN, NOT EXISTS 에 대해서 알아보았습니다.

저 또한 많이 헷갈리던 개념들아었는데 다시 한번 정리하고 나니 확실히 개념이 잡히는 것 같습니다. IN과 EXISTS는 성능에 대해서도 많은 이슈를 가져오는 구문들이니 그 차이를 확실히 짚고 넘어가는 것을 추천드립니다.


혹시 내용이 잘못되었거나 더 궁금하신 점이 있다면 언제든지 말씀해주시면 감사하겠습니다.

블로그 이미지

Tigercow.Door

Web Programming / Back-end / Database / AI / Algorithm / DeepLearning / etc


안녕하세요. 문범우입니다.

오늘은 MySQL에서 변수를 이용하는 방법에 대해 알아보도록 하겠습니다.

이해를 돕기 위해 예제를 가지고 진행하는 방식으로 해볼게요.

예제에서는 delimiter에 대한 사용이 나옵니다.

delimiter에 대해서 잘 모르신다면 지난 포스팅을 통해 확인하시길 바랍니다.


1. 예제 환경 구성


오늘은 아래와 같은 테이블에서 시작합니다.



테이블을 만들기 위한 코드는 아래와 같습니다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE person_1(
_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32NOT NULL,
job VARCHAR(64),
height INT NOT NULL,
english INT NOT NULL
);
 
INSERT INTO person_1 (name,job,height,english)
VALUES ('김철수',NULL,173,84),
('최고야','student',189,99),
('박사장','ceo',162,76),
('이세상',NULL,159,12),
('김영희',NULL,182,35),
('오감자','snack',139,3),
('수박바','student',155,65);
cs


위의 테이블을 가지고 진행을 해보도록 하겠습니다.

오늘의 목표는, 각 사람이 가진 영어점수를 바탕으로 순위를 매기는 것 입니다.

단순히 출력할때 순위가 보이도록 하는 것이 아니라, 새로운 속성을 추가하여 각각에 대한 순위를 저장하도록 하겠습니다.


2. MySQL 사용자 정의 변수


MySQL 에서는 사용자 정의 변수를 이용할 수 있습니다.

또한 당연히, 선언한 변수를 쿼리문에서 사용할 수 있습니다.

만약 var라는 이름의 변수를 선언하고 싶다면 아래와 같이 쿼리를 입력하면 됩니다.


1
SET @var = 1;
cs


위의 쿼리와 같이 SET @(변수명) 을 통해 변수를 선언합니다.

그리고 변수를 사용할때는 @(변수명) 을 통해서 변수를 사용하면 됩니다.


3. 순위 속성을 추가하고 순위 매기기


그럼 위에서 말한 것과 같이 새로운 '순위' 속성을 추가하고 각각에 대한 영어점수의 순위를 매겨보도록 하겠습니다.

먼저 rank 속성을 추가합니다.


쿼리문은 아래와 같습니다.


1
ALTER TABLE person_1 add column rank INT;
cs


그리고 이제 지난 포스팅에서 학습한 delimiter와 사용자 정의 변수를 이용해보도록 하겠습니다.

먼저 전체 쿼리문은 아래와 같습니다.


1
2
3
4
5
6
7
8
9
10
11
SET @rank = 1;
 
DELIMITER //
CREATE TRIGGER person_update
AFTER UPDATE ON person_1
FOR EACH ROW BEGIN
SET @rank=@rank+1;
END //
DELIMITER ;
 
UPDATE person_1 SET rank=@rank ORDER BY english DESC;
cs


1번 라인을 보시면 rank 라는 변수를 1로 선언과 동시에 초기화 하였습니다.


그리고 이어서 DELIMITER를 사용하여 트리거를 만듭니다.

트리거는 person_1 테이블이 update될 때마다 작동되며 6번 라인을 통해 각 행마다 적용되도록 설정하였습니다.

트리거 내용으로는, rank 변수의 값을 1을 증가 시키는 내용입니다.

이렇게 트리거를 만들고 다시 DELIMITER를 수정합니다.


그리고 11번 라인에서와 같이 person_1테이블의 새로만든 속성, rank 속성에 rank변수 값을 입력합니다.

이때 테이블은 english 점수의 내림차순으로 출력된 상태입니다.


이렇게 쿼리를 입력하고 테이블을 확인하면 아래와 같습니다.



어떻게 해서 이런 결과가 나올까요?

다시 쿼리문을 확인해보면, 트리거는 각 행마다 진행이 됩니다.

즉 테이블이 영어점수의 내림차순으로 출력된 상태라면 제일 첫번째 행은 가장 높은 점수를 가진사람일 것입니다.

그럼 UPDATE 문에 의해서 그 사람의 rank는 1로 입력이 되고 이후 rank의 값은 트리거에 의해 1이 증가합니다.

그리고 이어서 두번째 사람의 rank에는 변수의 값이 1이 증가되었기 때문에 2가 저장되고,

그 다음사람은 3이 저장되는 방식입니다.




사용자 정의 변수에 대해서 간략하게 알아보고 예제를 통해 사용해보았습니다.

이후 사용자 정의 변수에 대해서는 사용이 될 때마다 추가적인 설명을 붙이도록 하겠습니다.

블로그 이미지

Tigercow.Door

Web Programming / Back-end / Database / AI / Algorithm / DeepLearning / etc

안녕하세요.

이번 포스팅에서는 MySQL의 명령어가 아닌 개념적인 내용과 해당 개념의 사용법을 알아보려합니다.

오늘 소개해드릴 MySQL의 개념은 Trigger(트리거)와 Delimiter(델리미터) 입니다.

예제가 함께 포함되어 있으니 따라하면서 하시면 이해하시는데 보다 수월하길 것 입니다.


  1. TRIGGER(트리거)

MySQL을 공부하다 보면 트리거(trigger)라는 단어를 볼 수 있습니다.

트리거가 무엇일까요?

위키백과에서의 정의를 보면 이와 같습니다.

 

데이터베이스 트리거(Database Trigger)는 테이블에 대한 이벤트에 반응해 자동으로 실행되는 작업을 의미한다. 트리거는 데이터 조작 언어(DML)의 데이터 상태의 관리를 자동화하는 데 사용된다. 트리거를 사용하여 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다. (출처: 위키백과)

 

말 그대로 트리거란, 테이블에 대한 이벤트가 발생했을 때 자동으로 실행되는 작업을 말합니다.

이때 이벤트가 발생하는 경우는 크게 DML 실행, DDL 실행, 데이터베이스 동작 실행(logon, logoff, startup, shutdown, servererror ) 로 나눌 수 있습니다.

 

트리거는 문장 트리거, 행 트리거, 2종류로 나누어집니다.

행 트리거는 각 행에 대해서 트리거가 발생하는 것이고 문장 트리거는 전체 트랜젝션 작업에 대해 1번 발생되는 트리거 입니다.

다시 말해, 문장 트리거는 트리거가 설정된 테이블에 트리거 이벤트가 발생했을 때 많은 행에 대해 변경작업이 발생하더라도 단 한 번만 트리거가 발생하는 것입니다.

행 트리거는 조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법입니다.

 

또한 트리거는 다음과 같은 속성을 갖습니다.

BEFORE or AFTER : 트리거가 실행되는 시기를 지정합니다.

INSTEAD OF : 트리거를 원래 문장 대신 수행합니다.

WHEN : 트리거를 시작하는 조건식을 지정합니다.

 

MySQL에서는 버전 5.0.2에서 표준 트리거가 지원되었습니다.

INSERT, UPDATE, DELETE 동작에 대한 트리거가 지원되는데 이때 주의하실 점은 MySQL은 각 테이블에 각 형태의 단 하나의 트리거만 허용합니다.

, INSERT BEFORE 하나, INSERT AFTER 하나, … 이런 식으로 허용합니다.

 

그렇다면 트리거는 어떻게 생성할까요?

두 개의 테이블을 이용하여 트리거를 확인해보겠습니다.

먼저 각각의 테이블은 아래와 같습니다.

 


그리고 생성한 트리거의 코드는 아래와 같습니다.


1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes'WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;
cs

 


이와 같은 트리거를 작성하고

 

INSERT INTO test (name,age,adult) VALUES (‘트리거’,1,’No’);

 

쿼리를 날린 후 다시 테이블을 확인하면 아래와 같습니다.

 

 

adultNum 테이블의 변화가 보이시나요?

트리거를 작성하고 test 테이블에 INSERT를 했을 뿐인데 adultNum 테이블의 값이 변화하였습니다.

트리거는 이처럼 특정 table에 이벤트가 발생했을 때 작동하는 코드를 말합니다.

그럼 다시 트리거 코드를 통해 자세히 살펴보겠습니다.


1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes'WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;
cs

 


1: DELIMITER는 뒤에서 다시 이야기 하겠습니다.

2: CREATE TRIGGER (트리거이름)

   (트리거이름)이라는 이름을 가진 트리거를 만듭니다.

3: AFTER INSERT ON (테이블이름)

   어떤 테이블에 언제 영향을 미칠지를 정합니다.

저는 test라는 테이블에 INSERT가 이루어진 AFTER(다음)에 트리거가 실행되도록 하였습니다.

4: FOR EACH ROW

   해당 구문을 통해 행 트리거임을 말합니다.

5: BEGIN

   트리거 구문의 시작을 말합니다.

6~8: 트리거의 내용입니다.

9: Trigger 생성을 끝냅니다.

10: DELIMITER를 다시 수정합니다. (뒤에서 자세히 설명드리겠습니다.)

 

트리거의 코드에 대해서 어느정도 이해가 가시나요?

 

트리거는 많은 테이블을 관리할 때 상당히 유용하게 쓰일 수 있습니다.

그만큼 활용방안도 많지만, 주의할 점도 많습니다.

몇 가지 주의사항으로는

A에 대한(A테이블의 변화에 대한)특정 트리거에서 A라는 테이블의 값에 대한 INSERT / UPDATE / DELETE 쿼리가 존재한다면 A라는 테이블에 INSERT / UPDATE / DELETE 의 쿼리입력은 불가합니다.

이때 만약 A라는 테이블에 대해 INSERT / UPDATE / DELETE 쿼리 수행이 필요한데 트리거를 걸고 싶다면, B라는 테이블을 해당 트리거에서 A라는 테이블에 대한 변화에 따라 A테이블의 모든 값을 복사하여 B라는 테이블에 입력하도록 하는 쿼리를 추가하면 될 것 입니다.

 

또한 트리거 동작은 함수 호출과 같이 동작하기 때문에 MySQL 함수와 같은 제약을 받으며 트리거는 트랜잭션 제어문(COMMIT, ROLLBACK, SAVEPOINT )을 사용할 수 없습니다.

 

추가적으로, 생성된 트리거를 확인하기 위한 쿼리는,

SHOW TRIGGERS;

이며 특정 트리거를 제거할 때는

DROP TRIGGERS (트리거 이름);

이라는 쿼리를 입력하면 됩니다.

 

이처럼 트리거에 대한 내용은 더 많으니 직접 쿼리를 통해 실행해보고 오류가 발생시에 해당 오류에 대한 답을 스스로 찾아보는 것이 중요합니다.

 


 

  2. DELIMITER(델리미터)

위의 트리거 내용에서 DELIMITER 라는 쿼리가 등장하였습니다.

해당 쿼리는 아래와 같이 쓰입니다.

DELIMITER $$ 또는 DELIMITER ; DELIMITER (아무거나!)

DELIMITER가 무엇일까?

DELIMITER에 대해 간단히 말하면, 구문의 끝을 설정해주는 녀석이라고 생각하면 됩니다.

보통 procedure, trigger를 신규로 생성하거나 수행될 때 delimiter를 사용합니다.

이때 trigger(트리거)라는 것은 위에서 말한바와 같이 특정 동작이 이루어질 때 자동으로 실행되는 함수 같은 친구를 말합니다.

DELIMITER를 확인하기 위해 위에서 쓰인 코드를 다시 확인해보겠습니다.

 

1
2
3
4
5
6
7
8
9
10
DELIMITER $$
CREATE TRIGGER insertTrigger
AFTER INSERT ON test
FOR EACH ROW
BEGIN
UPDATE adultNum SET adult =(SELECT count(_id) FROM test WHERE adult = 'Yes'WHERE table_name='test';
UPDATE adultNum SET not_adult =(SELECT count(_id) FROM test WHERE (adult = 'No') AND (age > 5)) WHERE table_name='test';
UPDATE adultNum SET baby =(SELECT count(_id) FROM test WHERE (adult = 'NO') AND (age <= 5)) WHERE table_name='test';
END $$
DELIMITER ;
cs

 

 

1번 라인을 보시면 DELIMITER 가 사용되었습니다.

1번 라인에서 DELIMITER $$ 으로 쿼리를 입력하였으므로 이제 procedure trigger의 종료시점은 $$로 설정되었습니다. 따라서 트리거의 내용인 BEGIN~END 까지 쿼리내에 ; 를 입력하여도 쿼리가 끝나지 않습니다. 그리고 마지막에서 $$ 를 입력함으로써 쿼리가 끝남을 알려줍니다.

그럼 다시 해야 될 일은 무엇일까요?

, 바로 10번 라인의 DELIMITER ; 입니다.

우리는 트리거를 작성하기 편하도록 컴퓨터에게 잠시동안 쿼리의 끝은 $$ !’ 라고 알려준 것입니다.

이제 트리거 작성이 끝났으니, 트리거가 실행될 때 입력한 쿼리가 잘 작동할 수 있도록 다시 컴퓨터에게 쿼리의 끝은 ; !’ 라고 말해줘야겠죠?

DELIMITER ; 를 하지 않는다면 우리가 해당 트리거를 실행시켜도 트리거 내부에 있는 쿼리는 입력만 될 뿐, 끝나지 않아서 제대로 실행되지 않을 것 입니다.

DELIMITER 뒤의 $$ 는 자신이 원하는 문자로 수정이 가능하오니 자신이 편한 문자로 사용하시면 됩니다.


이렇게 해서 이번에는 트리거(trigger)와 델리미터(delimiter)에 대해서 알아보았습니다.

내용에 대한 피드백이나 문의사항은 언제든지 댓글 및 이메일(doorbw@outlook.com)으로 말씀해주세요 :)


블로그 이미지

Tigercow.Door

Web Programming / Back-end / Database / AI / Algorithm / DeepLearning / etc


안녕하세요. 문범우입니다.

이번에는 지난 포스팅에 이어서 MySQL의 'SELECT 조건에 따른 데이터 검색'에 대한 명령어를 알아보겠습니다.




SELECT (속성1, 속성2, … ) FROM (테이블명) WHERE (조건식);

(조건식)에 의해 해당하는 행을 선택하고 (속성1, 속성2, … )에 의해 열을 선택합니다.

 

SELECT * FROM (테이블명);

WHERE 조건문이 없으므로 모든 행을 선택하고, *(: 모든)을 통해 모든 열을 선택합니다.

(테이블명)의 이름을 가진 테이블을 확인하는 명령어입니다.

 

SELECT * FROM (테이블명) WHERE (조건1) AND (조건2);

(테이블명)의 테이블에서 (조건1) (조건2)를 모두 만족하는 행의 모든 열을 선택합니다.

 

SELECT * FROM (테이블명) WHERE (속성1) BETWEEN (1) AND (2);

(테이블명)의 테이블에서 (속성1)의 값이 (1)(2) 사이인 행의 모든 열을 선택합니다.

 

          - 참고

           아래 두 명령어를 비교합니다.

           SELECT * FROM (테이블명) WHERE age > 19 AND age < 30;

           SELECT * FROM (테이블명) WHERE age BETWEEN 19 AND 30;

           두 명령어를 통한 결과 값은 같지만 성능은 BETWEEN을 사용한 명령어가 더 좋습니다.

            

           단순히 AND만 사용한 첫번째 쿼리는

           전체 데이터에서 age19보다 큰 값과 30보다 작은 데이터를 각각 구해 이들의 교집합 값을 구하는 연산이며

           BETWEEN을 사용한 두번째 쿼리는

           age라는 속성에서 19라는 값부터 시작하여 30이라는 값보다 작은 값을 찾는 연산이기 때문입니다.

 

SELECT * FROM (테이블명) WHERE (조건1) OR (조건2);

(테이블명)의 테이블에서 (조건1) (조건2) 중 어떤 하나라도 만족하는 행의 모든 열을 선택합니다.

 

SELECT * FROM (테이블명) WHERE (속성1) IN (조건1, 조건2, … );

(테이블명)의 테이블에서 (속성1)의 값이 (조건1)이거나 (조건2) 인 행의 모든 열을 선택합니다.

 

          - 참고

           IN을 사용한 구문 또한 BETWEEN을 사용한 구문과 같이 OR보다 성능이 좋습니다.

           아래 두 쿼리를 비교합니다.

 SELECT * FROM (테이블명) WHERE age = 19 OR age = 20 OR age = 21;

           SELECT * FROM (테이블명) WHERE age IN (19,20,21);

           두 쿼리 모두 같은 결과를 출력하지만 IN을 사용한 두번째 쿼리가 성능이 좋습니다.

 

          - 참고

          AND 연산이 OR 연산보다 우선순위가 높습니다.

          , age19 또는 20인 열과 birth1998 또는 1999인 행의 모든 열을 선택하려면

          SELECT * FROM (테이블명) WHERE age = 19 OR age = 20 AND birth = 1998 OR birth = 1999;

가 아닌

SELECT * FROM (테이블명) WHERE (age = 19 OR age = 20)AND(birth = 1998 OR birth = 1999);

를 입력해야 합니다.

 

SELECT * FROM (테이블명) WHERE NOT (조건1);

(테이블명)의 테이블에서 (조건1)을 만족하지 않는 행의 모든 열을 선택합니다.


SELECT * FROM (테이블명) WHERE (속성1) LIKE ‘A_’;

(테이블명)의 테이블에서 (속성1) ‘A+1글자값을 가진 행의 모든 열을 선택합니다.

 

SELECT * FROM (테이블명) WHERE (속성1) LIKE ‘A__’;

(테이블명)의 테이블에서 (속성1) ‘A+2글자값을 가진 행의 모든 열을 선택합니다.


          - 참고

          위의 _ 관련 쿼리의 예제입니다.

          


SELECT * FROM (테이블명) WHERE (속성1) LIKE ‘A%’;

(테이블명)의 테이블에서 (속성1) ‘A’로 시작하는 값을 가진 행의 모든 열을 선택합니다.


SELECT * FROM (테이블명) WHERE (속성1) LIKE ‘%A’;

(테이블명)의 테이블에서 (속성1) ‘A’로 끝나는 값을 가진 행의 모든 열을 선택합니다.

 

SELECT * FROM (테이블명) WHERE (속성1) LIKE ‘%A%’;

(테이블명)의 테이블에서 (속성1) ‘A’를 포함하는 값을 가진 행의 모든 열을 선택합니다.

 

          - 참고

             위의 % 관련 쿼리의 예제입니다.

 


SELECT * FROM (테이블명) ORDER BY (속성1) (순서);

(테이블명)이란 테이블에서 (속성1)(순서)에 따라 모든 열을 출력합니다.

이따 (순서)에는 DESC(내림차순) 또는 ASC(오름차순)을 입력하며 생략했을시의 default 값은 ASC(오름차순) 입니다.

 

SELECT (속성1) AS ‘별명1’, (속성2) AS ‘별명2’ FROM (테이블명);

(테이블명)이란 테이블에서 (속성1)은 별명1이란 이름으로, (속성2)는 별명2라는 이름으로 두개의 열을 출력한다.

 

SELECT DISTINCT (속성1), (속성2), … FROM (테이블명);

(테이블명)이란 테이블에서 (속성1), (속성2), … 를 선택하는데 이때 중복되는 값은 제외합니다.

 

          - 참고

             DISTINCT 관련 쿼리를 예제를 통해 확인합니다. 두 명령어에 따른 출력 값의 차이를 확인하세요.


             기본테이블

 


DISTINCT 쿼리에 따른 차이


 


이렇게 해서 MySQL에서 SELECT 조건에 따른 데이터 검색에 관한 명령어를 알아보았습니다.

다음 포스팅에서부턴 특정 예제를 기준으로 하여 명령어를 알아보도록 하겠습니다.

피드백이나 문의사항은 댓글 및 이메일(doorbw@outlook.com)을 이용해주세요 :)

 

 

블로그 이미지

Tigercow.Door

Web Programming / Back-end / Database / AI / Algorithm / DeepLearning / etc


안녕하세요.

데이터베이스를 학교에서 공부중에 있는데 MySQL을 사용하여 수업을 하고 있습니다.

따라서 MySQL에서 사용되는 명령어들을 정리할겸 포스팅을 진행하려 합니다.

각각의 명령어들을 최대한 순서(?)에 맞춰서 의미들을 설명하려하는데

부족한 점이 있거나 추가적인 명령어들이 있다면 덧글로 피드백 해주세요 :)


이번 포스팅에서 공부해볼 내용은 'Database 및 Table 생성 / 확인 / 제거 / 수정' 에 대한 명령어 입니다.

다음 포스팅에서는 '조건에 따른 데이터 검색'에 대한 명령어를 알아보겠습니다.


CREATE DATABASE 데이터베이스명 default CHARACTER SET UTF8;

(데이터베이스명)이라는 데이터 베이스를 생성하고 한글을 사용할 수 있는 UTF8로 문자열을 저장

 

GRANT ALL PRIVILEGES ON (데이터베이스명).* TO (사용자)@localhost INDENTIFIED BY ‘(비밀번호)’;

GRANT는 사용자에게 데이터베이스의 사용권한을 적용하는 구문입니다.

ALL PRIVILEGES 는 데이터베이스에 대한 모든 권한을 의미합니다. (DB 삭제 또한 가능)

ON (데이터베이스명).* 은 권한 대상 데이터베이스의 테이블을 설정합니다. .* 를 통해 모든 테이블을 의미합니다.

TO (사용자)@localhost 는 사용 권한을 받는 사용자를 지정합니다. 없는 유저라면 새롭게 생성하며 localhost는 말 그대로 로컬에서만 연결 가능함을 뜻합니다.

(localhost가 아닌 외부에서 접근하려고 한다면 접근권한을 따로 설정해 주어야 합니다.)

INDENTIFIED BY ‘(비밀번호)’ 는 사용자의 비밀번호를 설정합니다.

 

SHOW DATABASES;

데이터베이스들의 목록을 보여주는 명령어

 

USE (데이터베이스명)

(데이터베이스명)이라는 이름의 데이터베이스를 사용

 

DROP DATABASE (데이터베이스명);

(데이터베이스명)이라는 이름의 데이터베이스를 제거합니다.


CREATE TABLE (테이블명)(

_id INT PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(32) NOT NULL,

job VARCHAR(64) DEFAULT ‘student’,

phone VARCHAR(12)

) ENGINE=INNODB;

(테이블명)이라는 이름을 가진 테이블을 생성합니다.

( ) 내부에 속성을 입력합니다. 각각에 대한 설명은 아래와 같습니다.

_id 란 이름의 칼럼을 추가하며, 데이터 타입은 INT 입니다. 해당 칼럼을 PRIMARY KEY(기본 키)로 설정합니다. AUTO_INCREMENT를 통해 자동으로 인덱스를 증가시킵니다.

name 이란 이름의 칼럼을 추가하며, NULL을 허락하지 않으므로 자료를 입력할 때 항상 값을 넣어줘야 합니다.

job 이란 이름의 칼럼을 추가하며, DEFAULT(아무런 값을 입력하지 않았을 때의 값) ‘student’로 설정합니다.

phone 이란 이름의 칼럼을 추가합니다.

ENGINE = INNODBMySQL의 저장구조를 설정합니다.(생략가능)

 

DESCRIBE (테이블명);

(테이블명)이라는 이라는 테이블의 구조를 확인합니다. 줄여서 DESC (테이블명); 또한 가능합니다.

 

-      참고

Field = 열 이름

Type = 해당 열의 자료형 ex) int(11) '11자리의 정수값을 저장할 수 있는 자료형'

Null = NULL 값을 허용할 것인지 아닌지를 나타내는 제약사항으로 YES로 지정하면 NULL 값을 허용

Key = 해당 열이 ''로 지정되어 있는지 나타냄.

Default = 그 열에 주어진 '기본값' , 생략했을 경우 적용되는 값

자료형

INTEGER = 정수값을 저장할 수 있는 자료형

CHAR = 문자열을 저장할 수 있는 자료형. 열의 최대 길이를 지정해야 함 => 고정 길이 문자열

VARCHAR = 문자열을 저장할 수 있는 자료형.데이터크기에 맞춰 저장공간의 크기도 변경됨 =>가변 길이 문자열

DATE = 날짜값을 저장할 수 있는 자료형. 연월일

TIME = 시간을 저장할 수 있는 자료형. 시분초

 

SHOW TABLES;

데이터베이스 안의 사용 가능한 테이블 목록을 보여주는 명령어

 

INSERT INTO (테이블명) (칼럼1, 칼럼2, … ) VALUES (칼럼1의 값, 칼럼2의 값, … );

(테이블명)이라는 테이블의 (칼럼1, 칼럼2, … ) 맞춰서 (칼럼1의 값, 칼럼2의 값, … )을 넣습니다.


SELECT (칼럼1, 칼럼2, … ) FROM (테이블명);

(테이블명) 이라는 테이블에서 (칼럼1, 칼럼2, … )의 값을 읽어옵니다.

 

SELECT * FROM (테이블명);

(테이블명) 이라는 테이블에서 (*: 모든)데이터를 읽어오는 명령어

 

-      참고

숫자로 구성된 데이터      =  수치형 데이터(오른쪽 정렬)

문자로 구성된 데이터      =  문자열형 데이터(왼쪽 정렬)

날짜와 시각을 나타내는 데이터 =  날짜시간형 데이터(왼쪽 정렬)

열은 하나의 자료형만 가질 수 있고, 수치형의 열에 문자형의 데이터를 저장할 수는 없다.

값이 없는 데이터 = NULL

 

SELECT * FROM (테이블명) ORDER BY (칼럼 명);

(테이블명) 이라는 테이블에서 (*: 모든)데이터를 읽어오는데, (칼럼 명)을 기준으로 정렬하여 읽어옵니다.

 

SELECT * FROM (테이블명) WHERE (조건);

(테이블명) 이라는 테이블에서 (*: 모든)데이터를 읽어오는데, (조건)을 만족하는 행을 읽어옵니다.

 

SELECT * FROM (테이블명) WHERE (칼럼 명) LIKE ‘2017%’;

(테이블명) 이라는 테이블에서 (*: 모든)데이터를 읽어오는데, (칼럼 명)에서 값이 2017로 시작하는 모든 값을 읽어옵니다.

 

SELECT * FROM (테이블명) WHERE (칼럼 1) <> (칼럼 2);

(테이블명) 이라는 테이블에서 (*: 모든)데이터를 읽어오는데, (칼럼 1) (칼럼 2)가 같지 않은 행만 읽어옵니다.

( <>!= 와 같은 의미 입니다.)

 

ALTER TABLE (테이블명) ~ ;

(테이블명)이라는 이름의 테이블을 대상으로 테이블의 이름을 변경(RENAME)하거나 테이블에 칼럼(속성)을 추가(ADD)하거나 테이블의 칼럼(속성)을 변경(CHANGE, MODIFY)하거나 테이블의 칼럼(속성)을 제거(DROP)하는 명령을 내릴 수 있습니다.

 

ALTER TABLE (테이블명) RENAME TO (바꿀 이름);

(테이블명)이라는 테이블의 이름을 (바꿀 이름)이라는 이름으로 바꿉니다.

 

ALTER TABLE (테이블명)

ADD COLUMN (새로운 속성) INT NOT NULL AFTER (기존의 속성_1),

ADD PRIMARY KEY (기존의 속성_2);

(테이블명)이라는 테이블에 (새로운 속성)이라는 이름의 속성을 (기존의 속성_1)의 뒤에 추가합니다.

이때 (새로운 속성) INT형 자료를 가지며 NULL을 허용하지 않습니다.(NOT NULL)

또한 (기존의 속성_2)PRIMARY KEY(기본 키)로 변경합니다.

 

ALTER TABLE (테이블명)

MODIFY COLUMN (속성 이름) (변경할 타입);

(테이블명)이라는 테이블의 (속성 이름)이라는 속성의 타입을 (변경할 타입)으로 바꿉니다.

 

ALER TABLE (테이블명)

CHANGE COLUMN (속성 이름) (변경할 속성 이름) (변경할 타입);

(테이블명)이라는 테이블의 (속성 이름)이라는 속성의 이름을 (변경할 속성 이름)으로 바꾸고 타입을 (변경할 타입)으로 바꿉니다.

 

ALTER TABLE (테이블명)

DROP COLUMN (속성 이름),

DROP PRIMARY KEY;

(테이블명)이라는 테이블에서 (속성 이름)이라는 이름의 속성을 제거합니다.

또한 PRIMARY KEY(기본 키)를 제거합니다.


UPDATE (테이블명)

SET (속성 이름) = (바꿀 값)

WHERE (조건: ex. Age > 20);

(테이블명)이라는 테이블에서 WHERE 구문의 조건에 맞는 레코드의 (속성 이름) 값을 (바꿀 값)으로 변경합니다.

- 참고

좌측의 테이블이 있다고 했을 때, 현재 adult 속성의 값을 보면 제대로 맞지 않습니다.

이를 수정하기 위해서 UPDATE 구문을 사용하겠습니다.

 

사용된 명령어는 아래와 같습니다.

UPDATE updateTest

SET adult = ‘Yes’ WHERE age > 19;

 

UPDATE updateTest

SET adult = ‘No’ WHERE age <= 19;

 

결과는 아래 사진과 같습니다.

 


DELETE FROM (테이블명) WHERE _id = 3;

(테이블명)이라는 이름의 테이블에서 _id = 3인 데이터를 삭제합니다.

WHERE 구문을 입력하지 않으면 테이블의 모든 데이터가 제거됩니다.

 

DROP TABLE (테이블명);

(테이블명)이라는 이름의 TABLE을 제거합니다. (DELETE는 데이터만 제거하는 것이고 DROP는 테이블 자체를 제거하는 것)

 

TRUNCATE TABLE (테이블명);

(테이블명)이라는 이름의 테이블의 모든 데이터를 삭제합니다.

 

           - 참고

           DELETE 명령은 데이터를 순차적으로 삭제합니다.

           TRUNCATE 명령은 테이블 자체를 삭제하고 같은 이름의 테이블을 새로 생성합니다.

           (테이블을 DROPCREATE 하는 명령어입니다.)

           따라서, 모든 데이터를 삭제할 때 DELETE 명령보다 TRUNCATE 명령이 처리속도가 빠르지만

TRUNCATE 명령은 데이터의 복구가 불가능합니다.


내용에 대한 피드백 및 궁금한 점은 댓글 및 이메일(doorbw@outlook.com) 으로 말씀해주세요 :)

다음 포스팅에서는 '조건에 따른 데이터 검색'에 대한 명령어를 알아보겠습니다.

블로그 이미지

Tigercow.Door

Web Programming / Back-end / Database / AI / Algorithm / DeepLearning / etc