개발기술/데이터베이스

SQL문 정리

bsh6226 2024. 7. 9. 20:00

SQL문

SQL : DBMS와 대화하기위한 특수목적의 언어로 데이터 그 자체 혹은 데이터를 관리하는 계정, 인스턴스를 관리한다

SQL은 1. 데이터정의어 (초기화) 2. 데이터 조작어 (활용) 3. 데이터 제어(권한부여)어로 이루어져있다.

 

SQL문 Keyword 정리

  • -- or # : comment out
  • "" : quotation for identifier
  • '' :  quotation for string literal in sql
  • % : like 연산자로 모든 단어를 지칭한다. (a% : a로 시작하는 모든 단어, %a% : a를 포함하는 모든단어)
  • _ : like 연산자로 한글자를 지칭한다 (a_ : a로 시작하는 모든 두글자)
    • LIKE IN , NOT LIKE 등으로 응용가능

1. 데이터 정의어(DDL: Data Definition Language) :

데이터베이스와 그 안의 테이블, 인덱스 구조, 사용자 등을 구축 및 관리(CREATE, DROP, ALTER, TRUNCATE) 주로 초기셋업시에 사용

 

 

 

데이터베이스 테이블 생성

  • 데이터는 관계형 데이터베이스의 기본 단위인 테이블 형태로 저장됨. 테이블은 특정한 주제와 목적으로 만들어진 일종의 집합임. 테이블(관계)는 컬럼(=필드,속성)과 로우(=레코드,튜플)로 이루어짐.
CREATE TABLE [테이블명] 
(
    필드1 데이터타입(크기),
    필드2 데이터타입(크기),
    ...,
    PRIMARY KEY (기본키_칼럼),
    FOREIGN KEY (외래키_칼럼) REFERENCES 다른테이블명(참조칼럼)
    );

 

CREATE TABLE MEMBER (
                        name VARCHAR(50),    -- 이름, 최대 50자
                        id VARCHAR(50),      -- 아이디, 최대 50자
                        password VARCHAR(50), -- 비밀번호, 최대 50자
                        PRIMARY KEY (id)     -- id 필드를 기본 키로 설정
);
  • 데이터의 크기 설정은 너무 과하면 용량과 성능상의 문제가 발생하기때문에, 적절한 크기를 설정할 필요가 있음. 주민등록번호의 경우 varchar(13)으로 고정적으로 설정가능.
  • 삭제 : drop table tablename

Column Constraint

컬럼 제약 조건은 테이블의 데이터 무결성을 보장하기 위해 사용됩니다. 각 제약 조건은 특정 컬럼에 적용되며, 테이블 간 데이터 연결이나 유일성을 보장할 수 있습니다.

  • Primary Key  : 데이터 쌍인 레코드(튜플)를 서로 구분하도록 하나의 칼럼을 중복불가키인 구분자로 설정하여 레코드들을 구분함. 
  • Foreign Key : 다른 테이블의 PK로, 테이블 간 데이터 연결고리가 되는 칼럼
  • UNIQUE : 해당 칼럼의 값들은 유니크한 값만 갖도록 제약을 설정함.
CREATE TABLE EmployeeRecords (
        ID int NOT NULL,
        FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
Department varchar(255),
PRIMARY KEY (ID),
UNIQUE (FirstName, LastName)  -- Composite unique constraint
);

 

ALTER TABLE EmployeeRecords
ADD UNIQUE (FirstName, LastName);

 

 Column Attribute

 

컬럼 속성은 테이블의 컬럼에 대해 추가적인 설정을 부여할 수 있습니다.

  • AUTO_INCREMENT : 레코드 생성시 PK값이
  • NOT NULL : 값으로 Null값을 허용하지 않음.
  • CHECK : all values in a column satisfy a specific condition
  • DEFAULT : sets a default value for a column if no value is specified

 Index

CREATE INDEX [인덱스명] ON [테이블명] ([컬럼명]);
CREATE SPATIAL INDEX idx_location ON heritage_sites (location);

DROP INDEX [인덱스명] ON [테이블명];
DROP INDEX idx_name ON EmployeeRecords;

 

인덱스 확인 : SHOW INDEX FROM heritage_entity;

 

쿼리의 작동확인 : Explain keyword다음에 원하는 쿼리문을 사용하면 해당 쿼리가 어떻게 사용되었는지, 특히 어떤 Index를 사용하였는지 확인할 수 있다.

EXPLAIN SELECT *
FROM heritage_entity
WHERE ST_Within(location, ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'));

데이터종류

  • DB별로 데이터 타입이 다르며 mariaDB에서 주력으로 사용하는 타입은 int, double ,char(고정사이즈,사이즈명시필요), varchar(가변사이즈, 사이즈명시필요), date, timestamp, text, longtext, blob, bit이다.
  • https://mariadb.com/kb/en/numeric-data-type-overview/

 

그 외 특수 명령어

  • show tables : 선택된 db의 테이블 리스트를 보여준다
  • desc tablename : table의 데이터구조를 print한다.
  • SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '' : 테이블 구조를 확인해볼 수 있음

 

 2.데이터 조작어(DML: Data Manipulation Language)

 

 

RDBMS를 조작하는 명령문

SHOW ENGINES : 해당 RDBMS의 엔진을 확인할 수 있음

SELECT VERSION(); : 해당 RDBMS의 버전을 확인 할 수 있음

 

이는 의존성 호환성문제를 해결할때 유용하다.

 

테이블을 조작하는 명령문

 

레코드를 조작하는 명령문

INSERT, UPDATE, DELETE, SELECT. 흔히 CRUD라고 지칭하며 REST의  PUT/POST , GET, PUT/PATCH, DELETE에 대응된다.

 

  • SELECT문  : SELECT문은 특정 테이블에서 지정한 컬럼들을 조회하며, WHERE 절에서 제공된 조건에 맞는 데이터를 필터링합니다.
    • SELECT 출력 대상 컬럼명, 출력 대상 컬럼명… FROM 출력 대상 컬럼들이 있는 테이블명 WHERE 조건
    SELECT 컬럼1, 컬럼2
FROM 테이블명
WHERE 컬럼3 = '값3';
  •  INSERT문 : 지정된 컬럼들에 해당하는 새로운 데이터를 테이블에 삽입합니다.  
    • INSERT INTO 테이블명 (컬럼1, 컬럼2,…) VALUES (값1, 값2,…);
INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...);

 

  • INSERT IGNORE INTO 테이블명 (컬럼1, 컬럼2,…) VALUES (값1, 값2,…) : 레코드 입력시 인덱스기준 중복데이터를 발견하면 오류가 발생되지만 그 오류를 발생시키지 않고 해당 입력을 조용히 스킵함
  • INSERT INTO [TABLE] (COLUMN1, COLUMN2) VALUES (VALUE1, VALUE2)  ON DUPLICATE KEY UPDATE (COLUMNS) : 레코드 입력시 인덱스기준 중복데이터를 발견하면 오류가 발생되지만 그 오류를 발생시키지 않고 UPDATE 부문에 있는 COLUMNS만 값을 업데이트 함.
  • UPDATE문 :지정된 조건에 맞는 행의 데이터를 업데이트(수정)합니다.
UPDATE 테이블명
    SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE 조건절;
  • DELETE문 : WHERE 절에 지정된 조건을 만족하는 데이터를 테이블에서 삭제합니다.
DELETE FROM 테이블명
WHERE 조건절;
  • 그외 유용한 구문
    • EXISTS (All Major Databases) : is useful for checking if a subquery returns any rows
    • LIMIT ... OFFSET (or FETCH FIRST) (All Major Databases) :  This is essential for pagination—retrieving a subset of rows, 

 

  •  키워드
    • alias(as), - 별명: 말그대로 부르기 쉽게 하기 위함, AS 키워드를 이용해서 사용(옵션사항). DB의 칼럼명을 그대로 사용하는 것이 아니라 프론트 쪽에서 보여주거나 사용하고 싶은 명칭으로 바꾸고 싶을때 column명을 as로 재정의함.
    • *(애스터리스크) - select all을 하기위한 키워드

 

 

 

 JOIN문 기본소개

  •  2개 이상의 테이블을 연결해 데이터를 출력하는 것.
  • 일반적으로 사용되는 SQL문장의 상단수가 조인을 이용 SQL문에서 다른 테이블의 데이터를 가져올때 JOIN을 사용하여 데이터를 가져온다. 이는(JOIN은 관계형 데이터베이스의 가장 큰 장점이면서 핵심 기능)
  • 일반적인 경우 행들은 PK나 FK값의 연관에 의해조인이 성립(하지만, 특별한 경우에는 PK나 FK의 관계가 없어도 논리적인 값들의 연관만으로 조인 가능)
  • JOIN은 2개의 테이블을 JOIN하지만, 여러개의 테이블이 있더라도 JOIN이 가능함(먼저 특정 2개의 테이블만 JOIN 처리하고, 그 결과 집합을 다시 나머지 테이블과 JOIN. 다만, 이때 JOIN순서는 내부적으로 DBMS 옵티마이저가 결정함.)
    • INNER JOIN : 두 테이블의 공통부문을 출력
      • SELECT table1.column, table2.column FROM table1 JOIN table2 ON table1.column1 = table2.colum1 AND table2.column2 = table2.column2 (두 테이블의 조인하는 레코드의 조건, 거를 레코드는 거르고)
    • LEFT JOIN : 왼쪽 테이블에 오른쪽 테이블과 공통되는 부문을 합쳐서 출력
      • SELECT table1.column, table2.column FROM table1 LEFT JOIN table2 ON table1.column1 = table2.colum1 AND table2.column2 = table2.column2 (table1의 레코드는 그대로 두고, table2의 레코드 중 조건에 부합하는 것만 join)
    • RIGHT JOIN : 오른쪽 테이블에 왼쪽 테이블과 공통되는 부문을 합쳐서 출력
      • SELECT table1.column, table2.column FROM table1 RIGHT JOIN table2 ON table1.column1 = table2.colum1 AND table2.column2 = table2.column2 (table2의 레코드는 그대로 두고, table1의 레코드 중 조건에 부합하는 것만 join)
    • FULL JOIN : 두 테이블의 공통 비공통 구분없이 모두 합쳐서 출력
      • SELECT table1.column, table2.column FROM table1 JOIN table2  (table2의 레코드는 그대로 두고, table1의 레코드 중 조건에 부합하는 것만 join)

 

 

 JOIN문과 테이블 관계형성

  • One-to-Many Relationship / One-to-One Relationship
    • The foreign key is placed in the "many" side (Table B) to link it to Table A.
    • Join: To retrieve the related data, you use a query like this:
SELECT a.*, b.*
FROM TableA a
JOIN TableB b ON a.id = b.a_id;

x

  • Many-to-Many Relationship (Requires a Join Table):
    • multiple records in Table A can be related to multiple records in Table B. This cannot be handled with just foreign keys in Table A or Table B alone.
    • A join table is used to represent the relationship. The join table has foreign keys to both Table A and Table B.
    • Join: To retrieve related data, you need to join the two main tables through the join table. For example: 회원의 오더 중 특정아이템의 정보를 찾으려면, 회원와 특정아이템 간의 관계를 만든 전체 Jointable을 먼저 만들고, JoinTable에서 특정회원을 찾아서 그와 연결된 아이템을 선택한다. 그리고 그 아이템 정보를 사용해서 아이템테이블을 조회한다.
SELECT a.*, b.*
FROM TableA a
JOIN JoinTable j ON a.id = j.a_id
JOIN TableB b ON j.b_id = b.id;

 

Table을 변경함 그 중에서도 column 속성변경 :

  • ALTER TABLE bookmarkGroup MODIFY COLUMN sequenceOrder Int;
  • ALTER TABLE your_table_name DROP FOREIGN KEY constraint_name;
    • FOREIGN KEY를 설정할때, column에 대해서 constraint name이 자동적으로 셋팅됨
  • ALTER TABLE your_table_name ADD CONSTRAINT constraint_name(생략가능) FOREIGN KEY (your_column_name) REFERENCES reference_table_name(reference_column_name);
  • SELECT CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
    TABLE_SCHEMA = 'seoulwifi' AND (데이터베이스명)
    TABLE_NAME = 'bookmark' AND (테이블명)
    COLUMN_NAME = 'bookmarkGroupId' AND (컬럼명)
    CONSTRAINT_NAME != 'FOREIGN'; (contraint명)
  • ALTER TABLE your_table_name MODIFY COLUMN your_column_name BIGINT;

 

- 

 DBMS 내장 함수(단일행 함수)

벤더에서 기본적으로 제공하는 내장 함수(그외 사용자가 정의하는 사용자 정의 함수)가 존재함.

  • 단일행 함수 : 함수의 입력값이 단일행 값이 입력
  • 다중행 함수 : 함수의 입력값이 여러행 값이 입력 → 집계 함수, 그룹 함수등…
    • ORDER BY column/value (DESC) : 정렬 (내림차순)
  • 숫자형 함수 : 숫자를 입력하면 숫자 값을 반환
    • SQRT(a,b) : a root b
    • POW (a,b) : a^b
    • AVG(column) : column에 대한 List를 평균화시킴
    • ROUND(a, b) : a, b. 숫자 a를 b 만큼 올림.
  • - 변환형 함수 : 문자, 숫자, 날짜형 값의 데이터 타입을 변환
  • - NULL 관련 함수 : NULL을 처리하기 위한 함수
    • COALESCE(data, value) : null 값을 value로 환산해줌. 보통 1을 넣어서 null일때 초기값을 1로 셋팅해줌,
  • - 날짜형 함수 : DATE 타입의 값을 연산
  • - 문자형 함수 : 문자를 입력하면 문자나 숫자 값을 반환
  • - 단일행 함수 : 함수의 입력값이 단일행 값이 입력
        • 데이터 표시 포맷(날짜 → 문자열, 문자열 → 날짜)- 날짜 → 문자열 변환 : date_format- 날짜 연산 : date_add(date, interval 1 month)
        • - 현재 날짜의 월초와 월말 구하기
        • - 문자열 → 날짜 변환 : str_to_date
        • 3. 회원정보 데이터 처리– II페이징 처리- 데이터는 엄청 많음- 특정 위치부터 특정개수까지 내려줌.- Oracle의 경우 ROWNUM을 이용해서 페이징 처리 가능
          • select *select *select a.*from actor as a) t1) t2
          • where row_index > 10;
          • where row_index <= 20
          • order by a.code desc
          • , (row_number() over (order by a.code desc)) as row_index
          • from (
          • from (
          • - MSSQL의 경우 OFFSET, FETCH를 이용해서 처리(2012이후)
          • - MySQL, MariaDB의 경우 limit 를 이용하여 페이징 처리 가능
          • - 한꺼번에 모두 볼수 없기 때문에

SELECT

CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE

TABLE_SCHEMA = 'seoulwifi' AND

TABLE_NAME = 'bookmark' AND

COLUMN_NAME = 'bookmarkGroupId' AND

CONSTRAINT_NAME != 'FOREIGN';

SELECT

CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE

TABLE_SCHEMA = 'seoulwifi' AND

TABLE_NAME = 'bookmark' AND

COLUMN_NAME = 'bookmarkGroupId' AND

CONSTRAINT_NAME != 'FOREIGN';

 
      • 그외 : VIEW 테이블
        • 뷰테이블은 생성시 단순한 쿼리에 자원이 사용되는 것이 아니라 뷰테이블 생성을 위한 메타데이터 생성에도 소모가 된다. 그리고 원본 데이터 변화에따라 뷰테이블도 변화하며, 뷰테이블을 별도로 삭제하지 않으면 계속남기때문에 1회성 쿼리로는 효율적이지 않다. 
        • 때문에 뷰테이블을 원본 테이블의 연산을 통해 생성된 데이터를 자주 조회한다면 사용을 고려해볼것.