Skip to content

Structured Query Language

SQL(Structured Query Language, 구조화 질의어)는 관계형 데이터베이스 관리 시스템에서 자료의 검색과 관리, 데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리를 위해 고안된 컴퓨터 언어이다. SQL은 데이터베이스로부터 정보를 얻거나 갱신하기 위한 표준 대화식 프로그래밍 언어이다. 많은 수의 데이터베이스 관련 프로그램들이 SQL을 표준으로 채택하고 있다.

Categories

Functions

DDL (Data Definition Language, 데이터 정의어)

데이터베이스를 정의하는 언어이며, 데이터리를 생성, 수정, 삭제하는 등의 데이터의 전체의 골격을 결정하는 역할을 하는 언어 이다. 데이터베이스, 테이블등을 생성하는 역할을 한다.

CREATE, ALTER, DROP, TRUNCATE 등이 있다.

DML (Data Manipulation Language, 데이터 조작어)

데이터베이스 사용자가 응용 프로그램이나 질의어를 통하여 저장된 데이터를 실질적으로 처리하는데 사용하는 언어 이다. 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.

SELECT, INSERT, UPDATE, DELETE 등이 있다.

DCL (Data Control Language, 데이터 제어어)

데이터를 제어하는 언어 이다. 데이터의 보안, 무결성, 회복, 권한 등을 정의하는데 사용한다. TCL (Transaction Control Language, 트랜잭션 제어 언어)과 DCL로 구분하기도 한다.

GRANT, REVOKE, COMMIT, ROLLBACK 등이 있다.

Create 

CREATE TABLE IF NOT EXISTS {table_name} ({column_name_1} TEXT PRIMARY KEY, {column_name_2} TEXT, {column_name_3} TEXT);

Insert 

INSERT INTO {table_name}({column_name_1}, {column_name_2}, {column_name_3}) VALUES('{column_value_1}', '{column_value_2}', '{column_value_3}');

Select

간단한 Select문의 사용방법은 아래와 같다. 

SELECT {column_name_1} FROM {table_name} WHERE [조건];

컬럼의 내용이 중복되는 것을 제거하고 싶을 경우 아래와 같이 DISTINCT를 추가하면 된다.
(참고로 DISTINCT는 별개의, 뚜렷한 등의 뜻을 가지고 있다.) 

SELECT DISTINCT {column_name_1} FROM {table_name} WHERE [조건];

문자열 조건 검색 

SELECT {column_name_1} FROM {table_name} WHERE {column_name_1} LIKE "%@gmail.com";

PostgreSQL 에서 문자열에 큰따옴표(")가 아닌작은따옴표(')를 사용해야 한다. 

select * from graph_tasks where name like 'CENTRAL::GRAPH::Company::Task';

숫자 범위 검색 

SELECT {column_name_1} FROM {table_name} WHERE {column_name_1} BETWEEN 10 AND 20;

정순/역순 정렬 및 출력 개수 

select * from a4y_state where uid=1151 order by time desc limit 1;

LIKE 비교

  • % - 문자열 길게 비교
  • _ - 문자 1개만

Update

UPDATE 문은 아래의 구문을 따른다: 

UPDATE {table_name} SET {column_name_1} = value [, {column_name_2} = value ...] [WHERE condition]

예제: 

UPDATE shboard SET C1 = 1 WHERE C2 = 'a'

DELETE 

DELETE FROM pies WHERE flavor='Lemon Meringue';

GROUP BY

SQL:GroupBy 항목 참조.

DISTINCT

HAVING

JOIN

SQL:Join 항목 참조. 테이블 컬럼을 확장하여 가로 붙이기 한다.

Subquery (서브쿼리)

다른 테이블의 값을 기준으로 한 테이블에서 데이터를 검색할 수 있도록 다른 쿼리 내부에 중첩된 쿼리

UNION

UNION 은 두개의 SELECT 결과를 합칠수 있습니다. (JOIN 은 가로 붙이기, UNION 은 세로 붙이기)

그냥 union 은 중복을 제거, union all 은 중복 포함 <- (확인 필요) 

select group_uid as domain_uid, user_uid, role_uid, 0 as category from recc_group_member
union all
select project_uid as domain_uid, user_uid, role_uid, 1 as category from recc_project_member;

복잡한 예제 

select
    project_uid,
    permission_uid,
    user_uid,
    rank
from
    (
        select
            p.uid as project_uid,
            gm.permission_uid AS permission_uid,
            gm.user_uid AS user_uid,
            0 as rank
        from
            recc_project p
            JOIN recc_group_member gm ON gm.group_uid=p.group_uid
    union all
        select
            p.uid as project_uid,
            pm.permission_uid AS permission_uid,
            pm.user_uid AS user_uid,
            1 as rank
        from
            recc_project p
            JOIN recc_project_member pm ON pm.project_uid=p.uid
    ) aa
where
    permission_uid >= 1
;

문자열 연결하기

"\r\n" 문자를 "/" 으로 변환하기는 \r = char(13), \n = char(10) 이므로: 

select replace(replace(name, char(13), ''), char(10), '/') as name
from scrap_grade;

"\r\n" 문자 두 개를 ( char(13) 와, char(10)) 하나의 문자로 만드는 방법:

MySQL

CONCAT 함수 사용. 

SELECT REPLACE(aaa, CONCAT(CHAR(13), CHAR(10)), '/') AS replaced_text
FROM your_table;

SQL Server

+ 연산자 사용. 

SELECT REPLACE(aaa, CHAR(13) + CHAR(10), '/') AS replaced_text
FROM your_table;

PostgreSQL 와 SQLite

문자열 연결 연산자 || 사용. 

SELECT REPLACE(aaa, CHR(13) || CHR(10), '/') AS replaced_text
FROM your_table;

SQL의 NULL은 이상함

SQL에서 NULL 값은 독특하게 처리됨. UNIQUE 제약 조건이 있는 열은 여러 NULL 값을 가질 수 있음.

  • 이는 각 NULL 값이 다른 NULL과 다른 독립적인 값으로 간주되기 때문임
  • SQLite, Postgres, MySQL 모두 동일하게 동작함. 
select '' = '';    -- Returns 1 (true) 빈 문자열은 같음   
select 1 = 1;      -- Returns 1 (true) 숫자는 같음   
select 1 = 0;      -- Returns 0 (false) 숫자가 다름   
select null = null; -- Returns NULL (null) 응?
    • NULL은 "알려지지 않은 값"을 나타내는 자리 표시자이기 때문에, 두 개의 미지의 값이 동일하지 않다고 간주되기 때문임
    • IS 연산자를 사용하면 NULL의 정체성을 확인할 수 있음. 예를 들어, null is null은 TRUE를 반환함.
  • 고유성에 대해
    • UNIQUE 제약 조건이 있는 열에 NULL 값이 포함된 경우, NULL 값은 서로 다른 것으로 간주되어 고유성 제약 조건을 위반하지 않음.
    • 예를 들어, ('[email protected]', NULL)과 ('[email protected]', NULL)은 서로 다른 행으로 간주됨.
  • NULL이 이렇게 처리되는 이유
    • SQLite 및 다른 SQL 호환 데이터베이스는 NULL을 다른 데이터베이스와 일관되게 처리하기 위해 이렇게 구현됨. SQL 표준 문서는 NULL이 모든 곳에서 고유해야 한다고 제안하지만, 실제로는 대부분의 SQL 엔진이 SELECT DISTINCT나 UNION에서 NULL을 고유하게 처리하지 않음.
  • 고유성을 보장하는 방법
    • 생성된 열 사용
      • NULL이 아닌 결정론적 값을 항상 가지는 열을 생성하여 문제를 완화할 수 있음. 예를 들어, COALESCE(deleted_at, '1970-01-01')를 사용하여 NULL 값을 대체할 수 있음.
      • 이 방법은 테이블에 추가 필드를 추가하여 공간을 차지할 수 있음.
    • 부분 인덱스 사용
      • deleted_at이 NULL인 경우에만 email에 대한 부분 인덱스를 생성하여 고유성을 보장할 수 있음.
      • 부분 인덱스는 테이블을 넓히지 않고 공간을 덜 차지하며, 동일한 레코드 쌍을 반복적으로 삭제할 때 오류가 발생하지 않음.
  • 업데이트
    • Oracle은 빈 문자열을 NULL로 처리함.
  • 결론
    • ORM을 사용할 때는 보이지 않지만, SQL NULL의 독특한 처리 방식은 혼란을 초래할 수 있음. SQL 표준 문서는 공개적으로 제공되지 않으며, 비용을 지불해야만 구할 수 있음.

SQL 쿼리 실행 순서 (최적화)

Online tools

AI Query | Generate SQL Queries with AI in Seconds
https://aiquery.co/
AI를 이용한 SQL 빌더
  • 간단한 영어 문장으로 쓰면 쿼리를 자동생성 해주는 도구
  • 최고의 결과를 위해서는 테이블명, 컬럼명 정도는 적고, CREATE/READ/UPDATE/DELETE 4가지중 선택
  • 대시보드를 통해서 DB 스키마 생성
  • PostgreSQL, MySQL, MariaDB, SQL Server
  • SQL to English 기능도 가능
  • 베타기간 중 무료
  • 다 좋은데.. 기존 스키마 불러오기 기능이 없네요. 이걸 위해서 대시보드로 새로 DB를 구성해야된다는게 에러인듯

See also

Favorite site