SQL이란 무엇입니까? 데이터 분석의 언어

오늘날 Structured Query Language는 관계형 데이터베이스에서 데이터를 조작하고 쿼리하는 표준 수단이지만 제품간에 독점적 인 확장이 있습니다. SQL의 용이성과 편재성 덕분에 많은 "NoSQL"또는 Hadoop과 같은 비 관계형 데이터 저장소의 작성자가 SQL의 하위 집합을 채택하거나 고유 한 SQL과 유사한 쿼리 언어를 만들게되었습니다.

그러나 SQL이 항상 관계형 데이터베이스의 "범용"언어는 아니 었습니다. 처음 (1980 년경)부터 SQL은 그것에 대해 특정 공격을가했습니다. 저를 포함한 당시 많은 연구자와 개발자는 SQL의 오버 헤드가 프로덕션 데이터베이스에서 SQL이 실용적이지 못하도록 할 것이라고 생각했습니다.

분명히 우리는 틀 렸습니다. 그러나 많은 사람들은 SQL의 모든 용이성과 접근성 때문에 런타임 성능에 대한 가격이 너무 높은 경우가 많다고 여전히 믿고 있습니다.

SQL 기록

SQL이 존재하기 전에 데이터베이스는 긴밀한 탐색 프로그래밍 인터페이스를 가지고 있었고 일반적으로 CODASYL 데이터 모델이라는 네트워크 스키마를 중심으로 설계되었습니다. CODASYL (Committee on Data Systems Languages)은 COBOL 프로그래밍 언어 (1959 년 시작) 및 데이터베이스 언어 확장 (10 년 후 시작)을 담당하는 컨소시엄이었습니다.

CODASYL 데이터베이스에 대해 프로그래밍 할 때 일대 다 관계를 표현하는 세트를 통해 레코드를 탐색했습니다. 이전 계층 적 데이터베이스에서는 레코드가 한 세트에만 속할 수 있습니다. 네트워크 데이터베이스를 사용하면 레코드가 여러 세트에 속할 수 있습니다.

당신이 먼저 찾을 것입니다 학생들이 CS (101)에 등록 목록 싶어 말 "CS 101"Courses의 소유자 또는 부모로서 것으로 설정 이름으로 세트 Enrollees세트 제 1 부재 (발견 ffm의) EnrolleesA는 세트, Student기록 및 목록 그것. 그런 다음 루프로 이동합니다. 다음 멤버 ( fnm)를 찾아 나열합니다. 때 fnm실패, 당신은 루프를 종료합니다.

데이터베이스 프로그래머에게는 많은 작업처럼 보이지만 실행 시간에는 매우 효율적이었습니다. University of California at Berkeley 및 Ingres의 Michael Stonebraker와 같은 전문가들은 IDMS와 같은 CODASYL 데이터베이스에서 이러한 종류의 쿼리를 수행하는 데 SQL을 사용하는 관계형 데이터베이스에서 동일한 쿼리를 수행 할 때 CPU 시간이 약 절반, 메모리가 절반도되지 않는다고 지적했습니다. .

비교를 위해 CS 101의 모든 학생을 반환하는 동등한 SQL 쿼리는 다음과 같습니다. 

코스, 등록자, 학생 WHERE course.name에서 student.name 선택

이 구문은 아래에서 설명 하겠지만 관계형 내부 조인 (실제로 둘 중 두 개)을 의미하며 조인에 사용되는 필드와 같은 몇 가지 중요한 세부 정보는 생략합니다.

관계형 데이터베이스 및 SQL

실행 속도와 메모리 사용에서 두 가지 개선 요소를 포기하는 이유는 무엇입니까? 두 가지 큰 이유는 개발 용이성과 이식성입니다. 1980 년에는 성능과 메모리 요구 사항에 비해 어느 쪽도 중요하지 않다고 생각했지만 컴퓨터 하드웨어가 개선되고 저렴 해지면서 사람들은 실행 속도와 메모리에 대한 관심을 멈추고 개발 비용에 대해 더 걱정했습니다.

즉, 무어의 법칙은 관계형 데이터베이스를 선호하여 CODASYL 데이터베이스를 죽였습니다. 그렇게되면서 개발 시간의 향상은 상당했지만 SQL 이식성은 꿈이되었습니다.

관계형 모델과 SQL은 어디에서 왔습니까? EF "Ted"Codd는 IBM San Jose Research Laboratory의 컴퓨터 과학자로 1960 년대에 관계형 모델의 이론을 연구하고 1970 년에 발표했습니다. IBM은 수익을 보호하기 위해 관계형 데이터베이스를 구현하는 데 느 렸습니다. CODASYL 데이터베이스 IMS / DB. IBM이 마침내 System R 프로젝트를 시작했을 때 개발 팀 (Don Chamberlin 및 Ray Boyce)은 Codd 소속이 아니었고 Codd의 1971 년 Alpha 관계형 언어 논문을 무시하고 자체 언어 인 SEQUEL (Structured English Query Language)을 설계했습니다. IBM이 제품을 출시하기 전인 1979 년에 Larry Ellison은 해당 언어를 Oracle 데이터베이스에 통합했습니다 (IBM의 사전 출시 SEQUEL 출판물을 사양으로 사용). SEQUEL은 국제 상표 위반을 피하기 위해 곧 SQL이되었습니다.

마이클 스톤 브레이커가 말했듯이 "SQL을 꺾고있는 톰톰"은 Oracle과 IBM뿐만 아니라 고객으로부터 나온 것입니다. CODASYL 데이터베이스 디자이너와 프로그래머를 고용하거나 교육하는 것이 쉽지 않았기 때문에 SEQUEL (및 SQL)이 훨씬 더 매력적으로 보였습니다. SQL은 1980 년대 후반에 너무나 매력적이어서 많은 데이터베이스 공급 업체가 본질적으로 CODASYL 데이터베이스 위에 SQL 쿼리 프로세서를 스테이플 링했습니다. Codd는 관계형 데이터베이스를 처음부터 관계형으로 설계해야한다고 생각했던 Codd는 크게 당황했습니다.

Codd가 설계 한 순수 관계형 데이터베이스는 1 차 술어 논리와 일치하는 관계로 그룹화 된 튜플에 구축됩니다. 실제 관계형 데이터베이스에는 필드, 제약 조건 및 트리거가 포함 된 테이블이 있으며 테이블은 외래 키를 통해 관련됩니다. SQL은 반환 할 데이터를 선언하는 데 사용되며 SQL 쿼리 프로세서와 쿼리 최적화 프로그램은 SQL 선언을 데이터베이스 엔진에서 실행되는 쿼리 계획으로 변환합니다.

SQL에는 스키마 정의를위한 하위 언어 인 데이터 정의 언어 (DDL)와 데이터 수정을위한 하위 언어 인 데이터 조작 언어 (DML)가 포함되어 있습니다. 둘 다 초기 CODASYL 사양에 뿌리를두고 있습니다. SQL의 세 번째 하위 언어는 SELECT문과 관계형 조인을 통해 쿼리를 선언 합니다.

SQL  SELECT

SELECT명령문은 리턴 할 데이터, 조회 할 테이블, 따라야 할 관계 및 리턴 된 데이터에 부과 할 순서를 쿼리 최적화 프로그램에 알려줍니다. 쿼리 최적화 프로그램은 특정 데이터베이스가 인덱스 힌트를 지원하지 않는 한 무차별 대입 테이블 스캔을 방지하고 우수한 쿼리 성능을 달성하기 위해 사용할 인덱스를 스스로 파악해야합니다.

관계형 데이터베이스 디자인 기술의 일부는 인덱스의 현명한 사용에 달려 있습니다. 빈번한 쿼리에 대한 인덱스를 생략하면 읽기로드가 많은 경우 전체 데이터베이스가 느려질 수 있습니다. 인덱스가 너무 많으면 쓰기 및 업데이트로드가 많은 경우 전체 데이터베이스가 느려질 수 있습니다.

또 다른 중요한 기술은 모든 테이블에 대해 훌륭하고 고유 한 기본 키를 선택하는 것입니다. 일반 쿼리에 대한 기본 키의 영향뿐만 아니라 다른 테이블에서 외래 키로 나타날 때 조인에서 재생되는 방식과 데이터의 참조 지역성에 미치는 영향을 고려해야합니다.

수평 샤딩이라고하는 기본 키의 값에 따라 다른 볼륨으로 분할되는 데이터베이스 테이블의 고급 사례에서는 기본 키가 샤딩에 어떤 영향을 미치는지 고려해야합니다. 힌트 : 테이블이 여러 볼륨에 균등하게 분산되기를 원하므로 날짜 스탬프 또는 연속적인 정수를 기본 키로 사용하지 않을 것입니다.

SELECT성명서에 대한 토론은 간단하게 시작할 수 있지만 금방 혼란스러워 질 수 있습니다. 중히 여기다:

* 고객으로부터 선택;

간단 하죠? 모든 필드와 Customers테이블의 모든 행을 요청 합니다. 그러나 Customers테이블에 1 억 개의 행과 100 개의 필드가 있고 필드 중 하나가 주석을위한 큰 텍스트 필드라고 가정합니다. 각 행에 평균 1KB의 데이터가 포함 된 경우 초당 10 메가 비트 네트워크 연결을 통해 모든 데이터를 풀다운하는 데 얼마나 걸립니까?

아마도 당신은 전선을 통해 보내는 양을 줄여야 할 것입니다. 중히 여기다:

고객에서 TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount를 선택합니다.

주와 도시

lastSaleDate 내림차순으로 주문;

이제 훨씬 적은 데이터를 가져 오게됩니다. 데이터베이스에 4 개의 필드 만 제공하고 클리블랜드에있는 회사 만 고려하고 가장 최근에 판매 한 100 개 회사 만 제공하도록 요청했습니다. 그러나 데이터베이스 서버에서이를 가장 효율적으로 수행하려면 Customers테이블 state+cityWHERE절에 lastSaleDate대한 인덱스 ORDER BY와 and TOP 100절 에 대한 인덱스가 필요합니다 .

그건 그렇고, TOP 100SQL Server 및 SQL Azure에는 유효하지만 MySQL 또는 Oracle에는 유효하지 않습니다. MySQL에서는 절 LIMIT 100뒤에 사용 합니다 WHERE. Oracle에서는 절의 ROWNUM일부로 바운드 on 을 사용합니다 . 안타깝게도 ANSI / ISO SQL 표준 (그리고 1986 년부터 2016 년까지 현재까지 9 개가 있음)은 지금까지만 진행되며, 각 데이터베이스는 고유 한 조항과 기능을 도입합니다.WHEREWHERE... AND ROWNUM <=100

SQL 조인 

지금까지 SELECT단일 테이블에 대한 구문을 설명했습니다 . JOIN절 을 설명하기 전에  외래 키와 테이블 간의 관계를 이해해야합니다. SQL Server 구문을 사용하여 DDL의 예제를 사용하여이를 설명하겠습니다.

이것의 짧은 버전은 매우 간단합니다. 관계에서 사용하려는 모든 테이블에는 기본 키 제약 조건이 있어야합니다. 이는 단일 필드이거나 표현식으로 정의 된 필드 조합 일 수 있습니다. 예를 들면 :

CREATE TABLE Persons (

    PersonID int NOT NULL PRIMARY KEY,

    PersonName char (80),

    ...

관련 Persons되어야 하는 모든 테이블 에는 Persons기본 키에 해당 하는 필드가 있어야하며 관계형 무결성을 유지하려면 해당 필드에 외래 키 제약 조건이 있어야합니다. 예를 들면 :

CREATE TABLE Orders (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int FOREIGN KEY REFERENCES Persons (PersonID)

);

CONSTRAINT제약 조건의 이름을 지정할 수 있는 키워드 를 사용하는 두 명령문의 더 긴 버전 이 있습니다. 이것이 대부분의 데이터베이스 디자인 도구가 생성하는 것입니다.

기본 키는 항상 인덱싱되고 고유합니다 (필드 값은 중복 될 수 없음). 다른 필드는 선택적으로 인덱싱 할 수 있습니다. 쓰기 및 업데이트로 인한 잠재적 오버 헤드 때문에 항상 그런 것은 아니지만 외래 키 필드와 WHEREORDER BY절에 나타나는 필드에 대한 인덱스를 만드는 것이 유용 합니다.

John Doe의 모든 주문을 반환하는 쿼리를 어떻게 작성 하시겠습니까?

사람에서 PersonName, OrderID 선택

Persons.PersonID에 대한 내부 조인 주문 = Orders.PersonID

WHERE PersonName;

사실, 네 가지 종류가있다 JOIN: INNER, OUTER, LEFT,와 RIGHT. 이 INNER JOIN기본값 (단어 생략 가능 INNER)이며 두 테이블 모두에서 일치하는 값을 포함하는 행만 포함하는 것입니다. 주문 여부에 관계없이 사람을 나열하려면을 사용합니다 LEFT JOIN. 예를 들면 다음과 같습니다.

사람에서 PersonName, OrderID 선택

Persons.PersonID의 LEFT JOIN 주문 = Orders.PersonID

PersonName에 의해 주문;

두 개 이상의 테이블을 조인하거나 표현식을 사용하거나 데이터 유형을 강제하는 쿼리를 시작하면 처음에는 구문이 약간 복잡해질 수 있습니다. 다행히도 종종 스키마 다이어그램에서 쿼리 다이어그램으로 테이블과 필드를 끌어서 놓아 올바른 SQL 쿼리를 생성 할 수있는 데이터베이스 개발 도구가 있습니다.

SQL 저장 프로 시저

선언문의 선언적 특성으로 인해 SELECT원하는 곳으로 이동할 수없는 경우가 있습니다. 대부분의 데이터베이스에는 저장 프로 시저라는 기능이 있습니다. 불행히도 이것은 거의 모든 데이터베이스가 ANSI / ISO SQL 표준에 대한 독점적 인 확장을 사용하는 영역입니다.

SQL Server에서 저장 프로 시저 (또는 저장 프로 시저)의 초기 언어는 Transact-SQL (일명 T-SQL)이었습니다. Oracle에서는 PL-SQL이었습니다. 두 데이터베이스 모두 C #, Java 및 R과 같은 저장 프로 시저에 대한 추가 언어를 추가했습니다. 간단한 T-SQL 저장 프로시 저는 매개 변수가있는 SELECT문 버전 일 수도 있습니다 . 그 장점은 사용의 용이성과 효율성입니다. 저장 프로시 저는 실행될 때마다가 아니라 저장 될 때 최적화됩니다.

더 복잡한 T-SQL 저장 프로시 저는 여러 SQL 문, 입력 및 출력 매개 변수, 로컬 변수, BEGIN...END블록, IF...THEN...ELSE조건, 커서 (세트의 행 단위 처리), 표현식, 임시 테이블 및 기타 여러 호스트를 사용할 수 있습니다. 절차 적 구문. 저장 프로 시저 언어가 C #, Java 또는 R 인 경우 해당 프로 시저 언어의 함수와 구문을 사용하게됩니다. 즉, SQL의 동기가 표준화 된 선언적 쿼리를 사용하는 것이 었음에도 불구하고 실제 세계에서는 많은 데이터베이스 관련 절차 서버 프로그래밍을 볼 수 있습니다.

(커서가 가까워 지더라도) 코다 실 데이터베이스 프로그래밍의 끔찍한 옛날로 돌아가지는 않지만 SQL 문을 표준화해야하고 성능 문제는 데이터베이스 쿼리 최적화 프로그램에 맡겨야한다는 생각에서 되돌아갑니다. . 결국, 성능의 두 배는 종종 테이블에 남겨두기에는 너무 많습니다.

SQL 배우기

아래에 나열된 사이트는 SQL을 배우거나 다양한 SQL 언어의 단점을 발견하는 데 도움이 될 수 있습니다.