SQL이란 무엇인가

SQL = Structured Query Language

SQL is a set-based, declarative programming language, not an imperative programming language like C or BASIC.

 

하나하나 뜯어보면서 의미를 분석해 보았다

Structured

SQL의 원래 이름은 SEQUEL(Structured English QUEry Language) → 상표 문제로 SQL로 이름을 바꿈

문법이 영어와 같다는 사실을 강조하고 싶었던 것이라고 함

Set-based

Set-Based vs Row-Based Database Code

Set-Based Code

• Operations on Sets: Set-based code focuses on performing operations on entire sets of data rather than individual rows. This is aligned with the relational nature of databases, where data is stored in tables.
• Declarative: Set-based code is often more declarative, meaning you specify what you want to achieve without explicitly instructing how to do it. SQL is designed to work with sets of data, and set-based operations leverage this design.
• Performance: Set-based operations are generally more efficient because they allow the database engine to optimize the query execution plan. The database engine can decide the most efficient way to retrieve and process the data.

집합에 대한 연산: 집합 기반 코드는 개별 행이 아닌 전체 데이터 집합에 대해 연산을 수행하는 것에 중점을 둔다. 이는 데이터가 테이블에 저장되는 관계형 데이터베이스의 특성과 일치한다.

선언적: 집합 기반 코드는 종종 더 선언적이다. 이는 어떻게 할지를 명시적으로 지시하지 않고 무엇을 달성하고자 하는지를 지정하는 것을 의미한다. SQL은 데이터 집합을 다루도록 설계되었으며, 집합 기반 연산은 이러한 설계를 활용한다.

성능: 집합 기반 연산은 일반적으로 더 효율적이다. 데이터베이스 엔진이 쿼리 실행 계획을 최적화할 수 있도록 하기 때문이다. 데이터베이스 엔진은 데이터를 검색하고 처리하는 가장 효율적인 방법을 결정할 수 있다.

UPDATE Products
SET Price = Price * 1.1
WHERE CategoryID = 2;

Row-Based Code

• Operations on Individual Rows: Row-based code, on the other hand, involves operations that are performed on individual rows one at a time. This style of coding is more procedural and might resemble how you would approach data manipulation in a programming language like a procedural language (e.g., C# or Java).
• Imperative: Row-based code is often more imperative, specifying the step-by-step instructions to achieve a particular result. It may involve using cursors or loops to iterate through individual rows.
• Performance Concerns: Row-based operations can be less efficient, especially when dealing with large datasets, as they might lead to more I/O operations and additional processing overhead.

개별 행에 대한 연산: 반면에 행 기반 코드는 한 번에 하나의 개별 행에 대해 연산을 수행한다. 이러한 스타일의 코딩은 더 절차적이며, C#이나 Java와 같은 절차적 언어에서 데이터 조작에 접근하는 방식과 유사할 수 있다.

명령형: 행 기반 코드는 종종 더 명령형이다. 이는 특정 결과를 달성하기 위한 단계별 지침을 명시하는 것을 의미한다. 커서나 루프를 사용하여 개별 행을 반복 처리할 수 있다.

성능 문제: 행 기반 연산은 특히 큰 데이터 집합을 다룰 때 덜 효율적일 수 있다. 이는 더 많은 I/O 작업과 추가 처리 오버헤드로 이어질 수 있기 때문이다.

DECLARE @ProductID INT, @Price DECIMAL;

DECLARE ProductCursor CURSOR FOR
SELECT ProductID, Price
FROM Products
WHERE CategoryID = 2;

OPEN ProductCursor;

FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Price = @Price * 1.1;

    UPDATE Products
    SET Price = @Price
    WHERE ProductID = @ProductID;

    FETCH NEXT FROM ProductCursor INTO @ProductID, @Price;
END;

CLOSE ProductCursor;
DEALLOCATE ProductCursor;

SQL은 Set-based operation을 하는 언어이다

Set-based operation들은 데이터베이스 엔진이 쿼리 실행 계획을 최적화 할 수 있기 때문에 일반적으로 더 효율적이다

Declarative

Imperative Programming vs Declarative Programming Imperative Programming은 무엇을 어떻게 수행할지를 구체적으로 작성한다 Declarative Programming은 원하는 결과를 얻기 위해 어떤 것이 필요한지 정의만 한다

Imperative Programming

const numbers = [1, 2, 3, 4, 5];
let oddNumbers = [];
for(let I = 0; I < numbers.length; I++) {
  if (numbers[I] % 2 !== 0) {
    oddNumbers.push(numbers[I]);
  }
}console.log(oddNumbers); // [1, 3, 5]

Imperative Programming으로 했을 때는 홀수 숫자들(원하는 결과)만 찾아내기 위해 for문을 이용하여 numbers 안의 각 원소들에 접근하여 각 원소가 홀수인지 아닌지 확인(결과를 얻기 위해 ‘어떻게’ 수행할지 정의)하여 홀수이면 oddNumbers 배열(결과를 얻기 위해 사용하는 ‘어떤’ 것) 안에 추가하고 있다.

Declarative Programming

const oddNumbers = numbers.filter(num => num % 2 !== 0);
console.log(oddNumbers); // [1, 3, 5]

Declarative Programming으로 했을 때는 Array의 filter 메소드를 사용하여 홀수 숫자들을 걸러내어(filter) 새로운 배열을 만들어 oddNumbers 변수 안에 담는다

filter 메소드가 어떻게 과정을 수행하는지는 모르지만 콜백 함수의 결과가 true인 어떤 값들만 찾아내어 새로운 배열을 반환한다

 

SQL을 보면

SELECT * FROM t1 WHERE food=“rice”;

결과집합을 얻기 위해 어떻게 명령을 수행할지가 아닌 필요한 명령어(SELECT, FROM, WHERE 등)를 사용해 명령을 수행하기 때문에 Declarative하다

Query

Query Language

쿼리 언어는 쿼리를 만들고 데이터베이스에서 정보를 검색하는 데 사용되는 특수 컴퓨터 언어이다.

사용자와 데이터베이스 간의 인터페이스 역할을 하며 사용자가 데이터베이스 관리 시스템(DBMS)에서 데이터를 관리할 수 있도록 해준다

Query

넓은 의미 : 쿼리는 데이터베이스나 데이터 리포지토리 시스템에서 데이터나 정보를 요청하는 것

일반적으로 데이터베이스가 이해할 수 있는 쿼리 언어로 작성된 특정 질문이나 명령의 형태

쿼리 언어의 유형

SQL은 관계형 데이터베이스에 가장 널리 사용되고 일반적으로 사용되는 쿼리 언어이다.

정형 데이터를 저장하고 사전 정의된 스키마를 사용하는 관계형 데이터베이스에 적합하다.

선언형 언어로, 어떻게 달성해야 하는지보다는 무엇을 달성해야 하는지 설명하지만 전통적으로 절차적 요소도 포함한다.

또한 NoSQL 또는 "Not Only SQL" 데이터베이스라고 하는 비관계형 데이터베이스용 언어도 있다.

NoSQL 데이터베이스는 키 값, 그래프, 와이드 컬럼, 문서 등의 방법을 사용하여 데이터를 저장하고 검색한다. 비정형 데이터 및 빅 데이터에 대한 동적 스키마에 사용하면 좋다.

특정 도메인 및 사용 사례를 위해 개발되는 경우가 많은 데이터베이스 쿼리 언어의 몇 가지 예는 다음과 같습니다.

• 데이터 마이닝 모델용 DMX
• MongoDB는 MongoDB의 데이터에 메서드 기반 쿼리 형식을 사용합니다.
• API용 GraphQL
• 진정한 관계형 데이터베이스 관리 시스템(TRDMS)에 대한 튜토리얼 D
• XML 데이터 소스용 XQuery
• 그래프용 Neo4j의 Cypher
• 자동화된 보안 검사 및 변형 분석을 위한 CodeQL
• OLAP 데이터베이스용 MDX
• Elasticsearch의 데이터에 액세스하기 위한 Elasticsearch Query DSL

 

SQL의 분류

DDL = Data Definition Language

CREATE, ALTER, DROP, TRUNCATE

CREATE

새로운 데이터베이스 및 테이블을 생성한다

CREATE DATABASE 데이터베이스이름;
CREATE TABLE 테이블이름
(
		필드이름1 필드타입1,
    필드이름2 필드타입2,
    ...
);

CREATE 제약조건

• AUTO_INCREMENT : 컬럼의 값이 중복되지 않게 1씩 자동으로 증가하게 해줘 고유번호를 생성
• CREATE TABLE 테이블이름 ( 필드이름 필드타입 AUTO_INCREMENT, // id bigint AUTO_INCREMENT, ... );
• NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 됨
• CREATE TABLE 테이블이름 ( 필드이름 필드타입 NOT NULL, ... );
• UNIQUE : 해당 필드는 서로 다른 값을 가져야만 함
• CREATE TABLE 테이블이름 ( 필드이름 필드타입 UNIQUE, ... );
• PRIMARY KEY : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됨데이터 중복 방지(데이터 무결성 지킴) , DBMS가 기본키로 만드는 인덱스로 빠른 검색
• CREATE TABLE 테이블이름 ( 필드이름 필드타입 PRIMARY KEY, ... );
• 고유한 키값으로 식별 가능
• FOREIGN KEY : 하나의 테이블을 다른 테이블에 의존하게 만들며 데이터의 무결성을 보장해줌
• FK 를 가지는 테이블이 참조하는 기준 테이블의 열은 반드시 PK, UNIQUE 제약조건이 설정되어 있어야 함

CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...
		FOREIGN KEY(필드이름)
    REFERENCES 테이블이름(필드이름)
);

외래 키 사용 이유 : 중복되는 데이터를 없앰, 필요한 데이터만 따로따로 보관

중복된 데이터가 삽입되는 것을 방지하는 역할

외래 키는 데이터가 새롭게 추가될 때 외래 키에 해당하는 값이 외래 키가 참조하는 테이블에 존재하는지를 확인함

• CASCADE : FOREIGN KEY 로 연관된 데이터를 삭제,변경할 수 있습니다.

ALTER

데이터베이스와 테이블의 내용을 수정

ALTER TABLE 테이블이름 ADD 필드이름 필드타입;
ALTER TABLE 테이블이름 DROP 필드이름;
ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입;

DROP

데이터베이스와 테이블을 모두 삭제

DROP DATABASE 데이터베이스이름;
DROP TABLE 테이블이름;

TRUNCATE

데이터베이스와 테이블의 내용을 삭제 , 컬럼은 남아있다

TRUNCATE DATABASE 데이터베이스이름;
TRUNCATE TABLE 테이블이름;

DML = Data Manipulation Language

INSERT, SELECT, UPDATE, DELETE

테이블에 데이터를 검색, 삽입, 수정, 삭제

INSERT

테이블에 새로운 row를 추가

INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) 
VALUES(데이터값1, 데이터값2, 데이터값3, ...);
INSERT INTO 테이블이름 
VALUES(데이터값1, 데이터값2, 데이터값3, ...);

SELECT

테이블의 row를 선택

SELECT 필드이름 FROM 테이블이름 [WHERE 조건];

UPDATE

테이블의 row의 내용을 수정

UPDATE 테이블이름 
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... 
WHERE 필드이름=데이터값;

DELETE

테이블의 row를 삭제

DELETE FROM 테이블이름 WHERE 필드이름=데이터값;

TCL = Transaction Control Language

COMMIT, ROLLBACK

COMMIT

트랜잭션 처리가 정상적으로 종료되어 트랜잭션이 수행한 변경 내용을 데이터베이스에 반영하는 연산

commit;

ROLLBACK

ROLLBACK 연산은 하나의 트랜잭션 처리가 비정상적으로 종료되어 데이터베이스의 일관성이 깨졌을 때, 트랜잭션이 행한 모든 변경 작업을 취소하고 이전 상태로 되돌리는 연산

rollback;

SAVEPOINT

현재의 트랜잭션을 작게 분할하는 명령어

Example

MySQL saves the changes done after the execution of each statement.

To save changes automatically, set the autocommit option as shown below −

SET autocommit=0;

Assume we have created a table in MySQL with name EMPLOYEES as shown below −

CREATE TABLE EMP(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT);

Let us insert 4 records in to it using INSERT statements as −

INSERT INTO EMP VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000);

Following transaction updates, the age values of all the employees in the emp table −

START TRANSACTION;

SELECT * FROM EMP;
UPDATE EMP SET AGE = AGE + 1;

SAVEPOINT samplesavepoint;

INSERT INTO EMP ('Mac', 'Mohan', 26, 'M', 2000);

ROLLBACK TO SAVEPOINT samplesavepoint;
COMMIT;

If you retrieve the contents of the table, you can see the updated values as −

SELECT * FROM EMP;

DCL = Data Control Language

GRANT, REVOKE

GRANT

사용자 또는 ROLE에 대해 권한을 부여

GRANT [객체권한명] (컬럼)
ON [객체명]
TO { 유저명 | 롤명 | PUBLC} [WITH GRANT OPTION];

//ex
GRANT SELECT ,INSERT 
ON mp
TO scott WITH GRANT OPTION;

REVOKE

사용자 또는 ROLE에 부여한 권한을 회수

REVOKE { 권한명 [, 권한명...] ALL}
ON 객체명
FROM {유저명 [, 유저명...] | 롤명(ROLE) | PUBLIC} 
[CASCADE CONSTRAINTS];

//ex
REVOKE SELECT , INSERT
ON emp
FROM scott
[CASCADE CONSTRAINTS];

참고자료

https://dallasdbas.com/set-based-vs-row-based-database-code/#:~:text=SQL is designed to work,retrieve and process the data.

https://www.elastic.co/kr/what-is/query-language