분류 sql

SQL 연산자 자습서 – 비트, 비교, 산술 및 논리 연산자 쿼리 예

컨텐츠 정보

  • 조회 497 (작성일 )

본문

핵심에서 인터넷과 모든 애플리케이션은 데이터 일뿐입니다. 


https://www.freecodecamp.org/news/sql-operators-tutorial/ 


모든 이메일, 트윗, 셀카, 은행 거래 등은 데이터베이스 어딘가에 있는 데이터 일뿐입니다.


해당 데이터가 유용하려면 검색 할 수 있어야 합니다. 그러나 데이터를 검색하는 것만으로는 충분하지 않습니다. 데이터는 유용하고 우리 상황과 관련이 있어야 합니다.


데이터베이스 수준에서는 SQL 쿼리를 작성하여 데이터베이스에서 특정 정보를 요청합니다. 이 SQL 쿼리는 수신 할 데이터와 수신 할 형식을 지정합니다.


이 기사에서는 SQL 쿼리를 필터링 하는 가장 일반적인 방법을 모두 살펴 보겠습니다.


우리가 다룰 내용은 다음과 같습니다.


  • Setting up your database
  • Creating users
  • Inserting users
  • Filtering data with WHERE
  • Logical operators (AND / OR / NOT)
  • Comparison operators (<, >, <=, >=)
  • Arithmetic operators (+, -, *, /, %)
  • Existence operators (IN / NOT IN)
  • Partial matching using LIKE
  • Dealing with missing data (NULL)
  • Using IS NULL and IS NOT NULL
  • Comparison operators with dates and times
  • Existence using EXISTS / NOT EXISTS
  • Bitwise operators
  • Conclusion


Setting up your database 


데이터를 필터링 하려면 먼저 데이터가 있어야 합니다.


이 예에서는 PostgreSQL을 사용하지만 여기에 표시된 쿼리와 개념은 다른 최신 데이터베이스 시스템 (예 : MySQL, SQL Server 등)으로 쉽게 변환됩니다.


PostgreSQL 데이터베이스를 사용하려면 대화형 PostgreSQL 명령 줄 프로그램 인 psql을 사용할 수 있습니다. 작업을 즐기는 다른 데이터베이스 클라이언트가 있는 경우에도 괜찮습니다!


시작하려면 데이터베이스를 생성 해 보겠습니다. PostgreSQL이 이미 설치된 상태에서 터미널에서 psql 명령 createdb <database-name>을 실행하여 새 데이터베이스를 생성 할 수 있습니다. 나는 내 fcc라고 불렀다.


$ createdb fcc

다음으로 psql 명령을 사용하여 대화형 콘솔을 시작하고 \ c <database-name>을 사용하여 방금 만든 데이터베이스에 연결하겠습니다.


$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

사용자 생성 


이제 데이터베이스가 있으므로 가상 시스템에서 잠재적 인 사용자를 모델링 할 데이터베이스 테이블을 만들어 보겠습니다.


이 테이블을 users라고 부르고 이 테이블의 각 행은 사용자 중 한 명을 나타냅니다.


이 사용자 테이블에는 이름, 이메일, 연령 등 사용자를 설명 할 것으로 예상되는 열이 있습니다.

psql 세션 내에서 users 테이블을 생성 해 보겠습니다.


CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT NOT NULL,
  age INTEGER NOT NULL
);

출력은 테이블 생성이 성공했음을 의미하는 CREATE TABLE을 보여줍니다.


참고 :이 예제에서는 읽기 쉽도록 psql 출력을 정리 했으므로 여기에 표시된 출력이 터미널에서 본 것과 정확히 일치하지 않더라도 걱정하지 마십시오. 


users 테이블의 내용을 살펴 보겠습니다.


SELECT * FROM users;

 id | first_name | last_name | email | age
----+------------+-----------+-------+-----
(0 rows)

테이블에 데이터를 삽입하지 않았으므로 빈 테이블 구조 만 볼 수 있습니다.


SQL 쿼리에 익숙하지 않은 경우 방금 실행 한 SELECT * FROM users는 작성할 수 있는 가장 간단한 쿼리 중 하나입니다.


SELECT 키워드는 반환 할 열 (*는 "모든 열"을 의미)을 지정하고 FROM 키워드는 선택할 테이블 (이 경우 사용자)을 지정합니다.


따라서 SELECT * FROM users는 실제로 users 테이블의 모든 행과 모든 열을 반환한다는 것을 의미합니다.


users 테이블에서 특정 열을 반환하려는 경우 SELECT *를 반환하려는 열 (예 : SELECT id, name FROM users)으로 바꿀 수 있습니다.


사용자 삽입 


빈 테이블은 그다지 흥미롭지 않으므로 테이블에 데이터를 삽입하여 쿼리를 연습 해 보겠습니다.


INSERT INTO users(first_name, last_name, email, age) VALUES
('John', 'Smith', 'johnsmith@gmail.com', 25),
('Jane', 'Doe', 'janedoe@Gmail.com', 28),
('Xavier', 'Wills', 'xavier@wills.io', 35),
('Bev', 'Scott', 'bev@bevscott.com', 16),
('Bree', 'Jensen', 'bjensen@corp.net', 42),
('John', 'Jacobs', 'jjacobs@corp.net', 56),
('Rick', 'Fuller', 'fullman@hotmail.com', 16);

psql 세션에서 해당 insert 문을 실행하면 INSERT 0 7 출력이 표시됩니다. 이는 7 개의 새 행을 테이블에 성공적으로 삽입했음을 의미합니다.


SELECT * FROM users 쿼리를 다시 실행하면 이제 해당 데이터를 볼 수 있습니다.


SELECT * FROM users;

id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Jacobs    | jjacobs@corp.net    |  56
  2 | Rick       | Fuller    | fullman@hotmail.com |  16
  3 | Bree       | Jensen    | bjensen@corp.net    |  42
  4 | Bev        | Scott     | bev@bevscott.com    |  16
  5 | Xavier     | Wills     | xavier@wills.io     |  35
  6 | Jane       | Doe       | janedoe@Gmail.com   |  28
  7 | John       | Smith     | johnsmith@gmail.com |  25
(7 rows)

WHERE로 데이터 필터링 


지금까지 테이블에서 모든 행을 반환했습니다. 이것은 쿼리의 기본 동작입니다. 보다 선택적인 행 집합을 반환하려면 WHERE 절을 사용하여 행을 필터링 해야 합니다.


WHERE 절을 사용하여 행을 필터링 하는 방법에는 여러 가지가 있습니다. 우리가 사용할 수 있는 가장 간단한 연산자는 같음 연산자 =입니다.


이름이 "John"인 사용자를 찾고 싶다고 가정 해 보겠습니다.

SELECT *
FROM users
WHERE first_name = 'John';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Jacobs    | jjacobs@corp.net    |  56
  7 | John       | Smith     | johnsmith@gmail.com |  25
(2 rows)

여기에서 쿼리에 키워드 WHERE를 추가 한 다음 같음 문 : first_name = 'John'.


우리의 데이터베이스는 먼저 FROM 키워드를 조사하여 가져올 데이터를 결정합니다. 따라서 데이터베이스는 이 쿼리를 읽고 FROM 사용자를 확인한 다음 디스크에서 사용자 테이블의 모든 행을 가져옵니다.


모든 행이 users 테이블에서 검색되면 각 행에 대해 WHERE 절을 실행하고 first_name 열 값이 "John"과 같은 행만 반환합니다.


데이터에는 이름과 일치하는 행이 두 개 있습니다.


시스템에서 특정 "John"을 찾으려면 id 열과 같이 고유 한 열을 기반으로 쿼리 할 수 ​​있습니다.


구체적으로 "John Jacobs"행을 찾으려면 그의 ID로 쿼리 할 수 ​​있습니다.


SELECT *
FROM users
WHERE id = 1;

 id | first_name | last_name |      email       | age
----+------------+-----------+------------------+-----
  1 | John       | Jacobs    | jjacobs@corp.net |  56
(1 row)

여기서는 하나의 레코드 만 id = 1의 조건과 일치하므로 한 행만 반환 되었습니다.


Logical operators (AND / OR / NOT) 


평등 연산자 이상으로 필터링 할 수 있습니다. 또한 대부분의 프로그래밍 언어에서 발견되는 부울 논리 연산자를 사용할 수 있습니다 : and, or, not.


많은 프로그래밍 언어에서 and and 또는  && 및 ||로 표시됩니다. SQL에서는 단순히 AND 및 OR입니다.


ID로 쿼리하는 대신 "John Smith"라는 사람의 레코드를 찾아 보겠습니다. 이를 위해 WHERE 절에서 AND를 사용하여 이름과 성 조건을 모두 찾을 수 있습니다.


SELECT *
FROM users
WHERE first_name = 'John'
  AND last_name = 'Smith';
  
 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  7 | John       | Smith     | johnsmith@gmail.com |  25
(1 row)

이름이 "John"이거나 성이 "Doe"인 사람을 찾으려면 :


SELECT *
FROM users
WHERE first_name = 'John'
  OR last_name = 'Doe';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Jacobs    | jjacobs@corp.net    |  56
  6 | Jane       | Doe       | janedoe@Gmail.com   |  28
  7 | John       | Smith     | johnsmith@gmail.com |  25
(3 rows)

여기서 우리의 결과에는 Johns와 Jane Doe가 모두 포함되었습니다.


이러한 AND 및 OR 조건은 함께 연결할 수도 있습니다. 정확히 "John Smith"라는 이름의 사람이나 "Doe"라는 성이 있는 사람을 찾고 싶다고 가정 해 보겠습니다.


SELECT *
FROM users
WHERE
(
  first_name = 'John'
  AND last_name = 'Smith'
)
OR last_name = 'Doe';

 id | first_name  | last_name |        email        | age
----+------------+-----------+---------------------+-----
  6 | Jane       | Doe       | janedoe@Gmail.com   |  28
  7 | John       | Smith     | johnsmith@gmail.com |  25
(2 rows)

이 조건을 반전하고 이름이 "John Smith"가 아니고 성이 "Doe"가 아닌 사용자를 찾으려면 NOT 연산자를 추가 할 수 있습니다.


SELECT *
FROM users
WHERE NOT
(
  (
    first_name = 'John'
    AND last_name = 'Smith'
  )
  OR last_name = 'Doe'
);
 
 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  4 | Bev        | Scott     | bev@bevscott.com    |  16
  5 | Bree       | Jensen    | bjensen@corp.net    |  42
  6 | John       | Jacobs    | jjacobs@corp.net    |  56
  7 | Rick       | Fuller    | fullman@hotmail.com |  16
  3 | Xavier     | Wills     | xavier@wills.io     |  35
(5 rows)

참고 : 모든 사람은 쿼리 형식을 지정하는 방식에 대해 자신 만의 고유 한 스타일을 가지고 있습니다.


Comparison operators (<, >, <=, >=) 


다른 프로그래밍 언어와 마찬가지로 SQL도 비교 연산자 인 <,>, <=,> =입니다.


사용자의 연령 열에 대해 이러한 연산자를 사용하여 연습 해 봅시다.


18 세 이상의 사용자를 찾고 싶다고 가정 해 보겠습니다.

SELECT * FROM users WHERE age >= 18;

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Jacobs    | jjacobs@corp.net    |  56
  3 | Bree       | Jensen    | bjensen@corp.net    |  42
  5 | Xavier     | Wills     | xavier@wills.io     |  35
  6 | Jane       | Doe       | janedoe@Gmail.com   |  28
  7 | John       | Smith     | johnsmith@gmail.com |  25
(5 rows)

25 세 이상 35 세 이하의 사용자는 어떻습니까?


SELECT * FROM users WHERE age > 25 AND age <= 35;

 id | first_name | last_name |       email       | age
----+------------+-----------+-------------------+-----
  5 | Xavier     | Wills     | xavier@wills.io   |  35
  6 | Jane       | Doe       | janedoe@Gmail.com |  28
(2 rows)

Arithmetic operators (+, -, *, /, %) 


데이터에 대해 수학적 계산을 수행 할 수도 있습니다.


사용자 테이블에는 연령 열이 있습니다. 각 사람의 연령의 절반을 찾으려면 어떻게 해야 합니까?


SELECT
  *,
  age / 2 AS half_of_their_age
FROM users;

 id | first_name | last_name |        email        | age | half_of_their_age
----+------------+-----------+---------------------+-----+-------------------
  1 | John       | Jacobs    | jjacobs@corp.net    |  56 |                28
  2 | Rick       | Fuller    | fullman@hotmail.com |  16 |                 8
  3 | Bree       | Jensen    | bjensen@corp.net    |  42 |                21
  4 | Bev        | Scott     | bev@bevscott.com    |  16 |                 8
  5 | Xavier     | Wills     | xavier@wills.io     |  35 |                17
  6 | Jane       | Doe       | janedoe@Gmail.com   |  28 |                14
  7 | John       | Smith     | johnsmith@gmail.com |  25 |                12
(7 rows)

여기에서 모든 테이블 열 (SELECT * 사용)을 선택하고 새 집계 계산도 선택합니다. age / 2. 또한 AS 키워드를 사용하여 별칭과 함께 이 값에 설명적인 이름 (half_of_their_age)을 지정합니다.


모듈러스 또는 나머지 연산자 (%)를 사용하여 나이가 짝수 인 사람을 찾을 수도 있습니다.


SELECT * FROM users WHERE (age % 2) = 0;

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Jacobs    | jjacobs@corp.net    |  56
  2 | Rick       | Fuller    | fullman@hotmail.com |  16
  3 | Bree       | Jensen    | bjensen@corp.net    |  42
  4 | Bev        | Scott     | bev@bevscott.com    |  16
  6 | Jane       | Doe       | janedoe@Gmail.com   |  28
(5 rows)

! = 또는 <>를 사용하여 = 조건을 "같지 않음"으로 변경하여 나이가 홀수인지 확인할 수 있습니다.


SELECT * FROM users WHERE (age % 2) <> 0;

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  5 | Xavier     | Wills     | xavier@wills.io     |  35
  7 | John       | Smith     | johnsmith@gmail.com |  25
(2 rows)

존재 연산자 (IN / NOT IN) 



값 목록에 열 값이 있는지 확인하려면 IN 또는 NOT IN을 사용할 수 있습니다.


SELECT *
FROM users
WHERE first_name IN ('John', 'Jane', 'Rick');

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28
  6 | John       | Jacobs    | jjacobs@corp.net    |  56
  7 | Rick       | Fuller    | fullman@hotmail.com |  16
(4 rows)

마찬가지로 NOT IN을 사용하여 해당 조건을 부정 할 수 있습니다.


SELECT *
FROM users
WHERE first_name NOT IN ('John', 'Jane', 'Rick');

 id | first_name | last_name |      email       | age
----+------------+-----------+------------------+-----
  3 | Xavier     | Wills     | xavier@wills.io  |  35
  4 | Bev        | Scott     | bev@bevscott.com |  16
  5 | Bree       | Jensen    | bjensen@corp.net |  42
(3 rows)

LIKE를 사용한 부분 일치 


때로는 부분 검색을 기반으로 행을 검색 할 수 있습니다.


예를 들어 Gmail 주소를 사용하여 애플리케이션에 가입 한 모든 사용자를 찾고 싶다고 가정 해 보겠습니다. LIKE 키워드를 사용하여 열에 대해 부분 일치를 수행 할 수 있습니다. %를 사용하여 일치 문자열에 와일드 카드 (또는 "모든 항목 일치")를 지정할 수도 있습니다.


gmail.com으로 끝나는 이메일을 사용하는 사용자를 찾으려면 :


SELECT *
FROM users
WHERE email LIKE '%gmail.com';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
(1 row)

% gmail.com 문자열은 "gmail.com으로 끝나는 모든 항목과 일치"를 의미합니다.


사용자 데이터를 살펴보면 실제로 gmail.com 주소를 가진 두 명의 사용자가 있음을 알 수 있습니다.


('John', 'Smith', 'johnsmith@gmail.com', 25),
('Jane', 'Doe', 'janedoe@Gmail.com', 28),

그러나 Jane의 이메일에는 이메일 주소에 대문자 'G'가 있습니다. 또는 이전 쿼리가 소문자 'g'가있는 gmail.com과 정확히 일치하기 때문에 이 레코드를 선택하지 않았습니다.


대소 문자를 구분하지 않는 일치를 수행하려면 LIKE 대신 ILIKE를 사용하면 됩니다.


SELECT *
FROM users
WHERE email ILIKE '%gmail.com';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28
(2 rows)

문자열 시작 부분의 와일드 카드 기호 %는 'gmail.com'으로 끝나는 모든 항목이 반환됨을 의미합니다. bob.jones+12345@gmail.com 또는 asdflkasdflkj@gmail.com (gmail.com으로 끝나는 경우)이 될 수 있습니다.


원하는 만큼 와일드 카드 (%)를 추가 할 수도 있습니다.


예를 들어, 검색어 % j % o %는 <anything> 패턴 뒤에 j, <anything>, o, <anything>이 ​​차례로 오는 모든 이메일을 반환합니다.


SELECT * FROM users WHERE email ILIKE '%j%o%';

 id | first_name | last_name |        email        | age
----+------------+-----------+---------------------+-----
  1 | John       | Smith     | johnsmith@gmail.com |  25
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28
  5 | Bree       | Jensen    | bjensen@corp.net    |  42
  6 | John       | Jacobs    | jjacobs@corp.net    |  56
(4 rows)

누락 된 데이터 처리 (NULL) 


다음으로 누락 된 데이터가 있는 열이 있는 행을 처리하는 방법을 살펴 보겠습니다.


이를 위해 users 테이블에 first_paid_at라는 다른 열을 추가하겠습니다.


이 새 열은 TIMESTAMP (다른 언어의 datetime과 유사)가 되며 사용자가 애플리케이션 비용을 지불 한 첫 번째 날짜와 시간을 나타냅니다. 앱 사용 기념일에 멋진 카드 나 꽃을 보내고 싶나요?


DROP TABLE 사용자를 사용하여 사용자 테이블을 삭제할 수 있습니다. 다시 생성하지만 테이블의 모든 데이터도 삭제됩니다.


테이블을 삭제하고 데이터를 잃지 않고 변경하려면 ALTER TABLE을 사용할 수 있습니다.


ALTER TABLE users ADD COLUMN first_paid_at TIMESTAMP; 

이 명령은 결과 ALTER TABLE을 반환하므로 ALTER 쿼리가 성공했습니다.


지금 users 테이블을 쿼리하면 이 새 열에 데이터가 없음을 알 수 있습니다.


SELECT * FROM users;

 id | first_name | last_name |        email        | age | first_paid_at
----+------------+-----------+---------------------+-----+---------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 |
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28 |
  3 | Xavier     | Wills     | xavier@wills.io     |  35 |
  4 | Bev        | Scott     | bev@bevscott.com    |  16 |
  5 | Bree       | Jensen    | bjensen@corp.net    |  42 |
  6 | John       | Jacobs    | jjacobs@corp.net    |  56 |
  7 | Rick       | Fuller    | fullman@hotmail.com |  16 |
(7 rows)

first_paid_at 열은 비어 있고 psql 쿼리의 결과는 빈 열로 표시됩니다. 이 열은 기술적으로 비어 있지 않습니다. 여기에는 psql이 출력에 표시하지 않기로 선택한 특수 값인 NULL이 포함되어 있습니다.


NULL은 데이터베이스의 특수 값입니다. 그것은 가치의 부재 또는 부족이며, 우리가 예상 한대로 작동하지 않습니다.


이를 설명하기 위해 아래의 간단한 SELECT 문을 살펴 보겠습니다.


SELECT
  1 = 1,
  1 = 2;

 ?column? | ?column?
----------+----------
 t        | f
(1 row)

여기서 우리는 단순히 1 = 1 및 1 = 2를 선택했습니다. 예상대로 이 두 명령문의 결과는 t와 f (또는 TRUE와 FALSE)입니다. 1은 1과 같고 1은 2와 같지 않습니다.



이제 NULL로 똑같이 시도해 봅시다.


SELECT 1 = NULL;

 ?column?
----------

(1 row)

이 값이 FALSE라고 예상 할 수 있지만 반환 값은 실제로 NULL입니다.


이러한 NULL을 좀 더 잘 시각화 하기 위해 \ pset 옵션을 사용하여 psql이 NULL 값을 표시하는 방법을 설정해 보겠습니다.


fcc=# \pset null 'NULL'
Null display is "NULL".

이제 해당 쿼리를 다시 실행하면 예상되는 NULL 출력이 표시됩니다.


SELECT 1 = NULL;

 ?column?
----------
 NULL
(1 row)

그래서 1은 NULL과 같지 않습니다. NULL = NULL은 어떻습니까?


SELECT NULL = NULL;

 ?column?
----------
 NULL
(1 row)

이상하게도 NULL은 NULL과 같지 않습니다.


NULL을 알 수 없는 값으로 생각하면 도움이 됩니다. 알 수 없는 값이 1과 같습니까? 글쎄, 우리는 모른다 – 그것은 알려지지 않았다. 알 수 없는 값이 알 수 없는 값과 같습니까? 다시 한 번 알 수 없습니다. 이런 식으로 NULL이 좀 더 의미가 있습니다.


IS NULL 및 IS NOT NULL 사용 


NULL과 함께 같음 연산자를 사용할 수 없지만 이를 위해 특별히 설계된 두 개의 연산자 인 IS NULL과 IS NOT NULL을 사용할 수 있습니다.


SELECT
  NULL IS NULL,
  NULL IS NOT NULL;

 ?column? | ?column?
----------+----------
 t        | f
(1 row)

이러한 값은 예상대로 나옵니다. NULL IS NULL은 true이고 NULL IS NOT NULL은 false입니다.


다 괜찮고 이상하지만 어떻게 사용합니까?


먼저 first_paid_at 열에서 데이터를 가져 오겠습니다.


UPDATE users SET first_paid_at = NOW() WHERE id = 1;
UPDATE 1

UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 month') WHERE id = 2;
UPDATE 1

UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 year') WHERE id = 3;
UPDATE 1

위의 UPDATE 문에서 사용자 ID 1은 현재 시간 (NOW ())으로, 사용자 ID 2는 1 개월 전, 사용자 ID 3은 1 년 전의 세 가지 다른 사용자 first_paid_at 열을 설정했습니다.


먼저, 우리에게 돈을 지불 한 사용자와 그렇지 않은 사용자를 찾아 보겠습니다.


SELECT *
FROM users
WHERE first_paid_at IS NULL;

 id | first_name | last_name |        email        | age | first_paid_at
----+------------+-----------+---------------------+-----+---------------
  4 | Bev        | Scott     | bev@bevscott.com    |  16 | NULL
  5 | Bree       | Jensen    | bjensen@corp.net    |  42 | NULL
  6 | John       | Jacobs    | jjacobs@corp.net    |  56 | NULL
  7 | Rick       | Fuller    | fullman@hotmail.com |  16 | NULL
(4 rows)

SELECT *
FROM users
WHERE first_paid_at IS NOT NULL;

 id | first_name | last_name |        email        | age |       first_paid_at
----+------------+-----------+---------------------+-----+----------------------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 | 2020-08-11 20:49:17.230517
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28 | 2020-07-11 20:49:17.233124
  3 | Xavier     | Wills     | xavier@wills.io     |  35 | 2019-08-11 20:49:17.23488
(3 rows)

날짜 및 시간이 있는 비교 연산자 


이제 일부 데이터가 있으므로 이 새로운 TIMESTAMP 필드에 대해 동일한 비교 연산자를 사용하겠습니다.


지난주에 처음으로 우리에게 돈을 지불 한 사용자를 찾아 보자. 이를 위해 현재 시간 NOW ()를 취하고 INTERVAL 키워드를 사용하여 1 주일을 뺄 수 있습니다.


SELECT *
FROM users
WHERE first_paid_at > (NOW() - INTERVAL '1 week');

 id | first_name | last_name |        email        | age |       first_paid_at
----+------------+-----------+---------------------+-----+----------------------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 | 2020-08-11 20:49:17.230517
(1 row)

3 개월 전과 같이 다른 간격을 사용할 수도 있습니다.


SELECT *
FROM users
WHERE first_paid_at < (NOW() - INTERVAL '3 months');

 id | first_name | last_name |      email      | age |       first_paid_at
----+------------+-----------+-----------------+-----+---------------------------
  3 | Xavier     | Wills     | xavier@wills.io |  35 | 2019-08-11 20:49:17.23488
(1 row)

1 ~ 6 개월 전에 처음 결제 한 사용자를 찾아 보겠습니다.


AND를 사용하여 조건을 다시 결합 할 수 있지만 보다 작음 및 보다 큼 연산자를 사용하는 대신 BETWEEN 키워드를 사용하겠습니다.


SELECT *
FROM users
WHERE first_paid_at BETWEEN (NOW() - INTERVAL '6 month')
  AND (NOW() - INTERVAL '1 month');
  
 id | first_name | last_name |       email       | age |       first_paid_at
----+------------+-----------+-------------------+-----+----------------------------
  2 | Jane       | Doe       | janedoe@Gmail.com |  28 | 2020-07-11 20:49:17.233124
(1 row)

EXISTS / NOT EXISTS를 사용한 존재 


존재를 확인하는 또 다른 방법은 EXISTS와 NOT EXISTS를 사용하는 것입니다.


이러한 연산자는 조건의 존재 (또는 존재하지 않음)를 확인하여 행을 필터링 합니다. 이 조건은 일반적으로 다른 테이블에 대한 쿼리입니다.


이를 설정하기 위해 posts라는 새 테이블을 만들어 보겠습니다. 이 테이블에는 사용자가 시스템에서 작성할 수 있는 게시물이 보관됩니다.


CREATE TABLE posts(
  id SERIAL PRIMARY KEY,
  body TEXT NOT NULL,
  user_id INTEGER REFERENCES users NOT NULL
);

간단한 테이블입니다. 여기에는 ID, 게시물 텍스트를 저장할 필드 (본문) 및 게시물을 작성한 사용자에 대한 참조 (user_id) 만 포함됩니다.


이 새 테이블에 데이터를 삽입 해 보겠습니다.


INSERT INTO posts(body, user_id) VALUES
('Here is post 1', 1),
('Here is post 2', 1),
('Here is post 3', 2),
('Here is post 4', 3);

posts 테이블에 삽입 한 데이터에서 User ID 1에는 2 개의 게시물이 있고 User ID 2에는 1 개의 게시물이 있으며 User ID 3에는 하나의 게시물이 있습니다.


게시물이 있는 사용자를 찾기 위해 EXISTS를 사용할 수 있습니다.


EXISTS 키워드는 하위 쿼리를 사용합니다. 해당 하위 쿼리에서 반환 되는 항목이 있으면 (NULL 값만 있는 행도 포함) 데이터베이스는 결과 집합에 해당 행을 포함합니다.


EXISTS에 대한 PostgreSQL 문서에서 :


EXISTS의 인수는 임의의 SELECT 문 또는 하위 쿼리입니다. 하위 쿼리는 행을 반환하는지 여부를 확인하기 위해 평가됩니다. 하나 이상의 행을 반환하면 EXISTS의 결과는 "true"입니다. 하위 쿼리가 행을 반환하지 않으면 EXISTS의 결과는 "false"입니다. 


EXISTS는 하위 쿼리에서 행의 존재를 찾고 있습니다. 그 안에 무엇이 있는지는 중요하지 않습니다.


다음은 EXISTS를 사용하는 게시물이 있는 사용자의 예입니다.


SELECT *
FROM users
WHERE EXISTS (
  SELECT 1
  FROM posts
  WHERE posts.user_id = users.id
);

 id | first_name | last_name |        email        | age |       first_paid_at
----+------------+-----------+---------------------+-----+----------------------------
  1 | John       | Smith     | johnsmith@gmail.com |  25 | 2020-08-11 20:49:17.230517
  2 | Jane       | Doe       | janedoe@Gmail.com   |  28 | 2020-07-11 20:49:17.233124
  3 | Xavier     | Wills     | xavier@wills.io     |  35 | 2019-08-11 20:49:17.23488
(3 rows)

예상대로 User 1, 2, 3이 돌아 왔습니다. 


EXISTS 하위 쿼리는 게시물의 user_id가 users 테이블의 id 열과 일치하는 게시물 레코드를 확인합니다. 여기에서 무엇이든 반환 할 수 있기 때문에 SELECT에서 1을 반환했습니다. 데이터베이스는 실제로 어떤 것이 반환 되었는지 확인하려고 합니다.


마찬가지로 EXISTS를 NOT EXISTS로 변경하여 게시물이 없는 사용자를 찾을 수 있습니다.


SELECT *
FROM users
WHERE NOT EXISTS (
  SELECT 1
  FROM posts
  WHERE posts.user_id = users.id
);

 id | first_name | last_name |        email        | age | first_paid_at
----+------------+-----------+---------------------+-----+---------------
  4 | Bev        | Scott     | bev@bevscott.com    |  16 | NULL
  5 | Bree       | Jensen    | bjensen@corp.net    |  42 | NULL
  6 | John       | Jacobs    | jjacobs@corp.net    |  56 | NULL
  7 | Rick       | Fuller    | fullman@hotmail.com |  16 | NULL
(4 rows)

마지막으로 다음과 같이 EXISTS 또는 NOT EXISTS 대신 IN 또는 NOT IN을 사용하도록 이 쿼리를 다시 작성할 수도 있습니다.


SELECT *
FROM users
WHERE users.id IN (
  SELECT user_id
  FROM posts
);

이것은 기술적으로 작동하지만 일반적으로 다른 레코드의 존재를 테스트하는 경우 일반적으로 EXISTS를 사용하는 것이 더 성능이 좋습니다. IN 및 NOT IN 연산자는 일반적으로 이전에 했던 것처럼 정적 목록에 대해 값을 확인하는 데 더 잘 사용됩니다.


SELECT *
FROM users
WHERE first_name IN ('John', 'Jane', 'Rick');

Bitwise operators 


실제로 비트 연산자는 자주 사용되지 않지만 완성도를 위해 간단한 예를 살펴 보겠습니다.


(어떤 이유로 든) 사용자의 나이를 바이너리로 보고 그 비트를 뒤집어보고 싶다면 다양한 비트 연산자를 사용할 수 있습니다.


예를 들어 비트 "and"연산자 : &를 살펴 보겠습니다.


SELECT age::bit(8) & '11111111' FROM users;

 ?column?
----------
 00010000
 00101010
 00111000
 00010000
 00011001
 00011100
 00100011
(7 rows)

비트 계산을 수행하려면 먼저 age 열을 정수에서 이진으로 변환해야 합니다.이 예에서는 :: bit (8)을 사용하여 8 비트 이진 문자열로 변환합니다.


다음으로 다른 이진 문자열 11111111을 사용하여 이진 형식의 나이 결과를 "and"할 수 있습니다. 이진 AND는 두 비트가 모두 1 인 경우 1 만 반환하므로 이 모든 1의 문자열은 출력을 흥미롭게 유지합니다.


거의 모든 다른 비트 연산자는 동일한 형식을 사용합니다.


SELECT age::bit(8) | '11111111' FROM users;    -- bitwise OR
SELECT age::bit(8) # '11111111' FROM users;    -- bitwise XOR
SELECT age::bit(8) << '00000001' FROM users;   -- bitwise shift left
SELECT age::bit(8) >> '00000001' FROM users;   -- bitwise shift right

비트 "not"연산자 (~)는 일반 NOT 연산자와 비슷하게 단일 용어에 적용된다는 점에서 약간 다릅니다.


SELECT ~age::bit(8) FROM users;

 ?column?
----------
 11101111
 11010101
 11000111
 11101111
 11100110
 11100011
 11011100
(7 rows)

마지막으로 가장 유용한 비트 연산자 인 연결입니다.


이 연산자는 일반적으로 텍스트 문자열을 결합하는 데 사용됩니다. 예를 들어 사용자에 대한 "전체 이름"의 계산 된 속성을 빌드하려는 경우 연결을 사용할 수 있습니다.

SELECT first_name || ' ' || last_name AS name
FROM users;

     name
--------------
 Bev Scott
 Bree Jensen
 John Jacobs
 Rick Fuller
 John Smith
 Jane Doe
 Xavier Wills
(7 rows)

여기서 우리는 first_name, 공백 ( ''), last_name 속성을 연결 (또는 "결합")하여 이름 값을 만듭니다.


결론 


이것이 기본적으로 사용해야 하는 모든 쿼리 필터링 연산자에 대한 개요입니다!


여기에서 다루지 않은 연산자가 몇 개 더 있지만 이러한 연산자는 자주 사용되지 않거나 위와 똑같은 방식으로 사용되므로 문제가 발생하지 않습니다.