SQLite 예제가 포함된 트리거, 뷰 및 인덱스

데일리 사용에 SQLite, 데이터베이스에 대한 몇 가지 관리 도구가 필요합니다. 또한 이를 사용하여 인덱스를 생성하여 데이터베이스를 보다 효율적으로 쿼리하거나 뷰를 생성하여 재사용성을 높일 수 있습니다.

SQLite 관측

뷰는 테이블과 매우 유사합니다. 그러나 뷰는 논리적 테이블입니다. 테이블처럼 물리적으로 저장되지 않습니다. 뷰는 select 문으로 구성됩니다.

복잡한 쿼리에 대한 뷰를 정의할 수 있으며, 쿼리를 다시 작성하지 않고도 뷰를 직접 호출하여 언제든지 이러한 쿼리를 재사용할 수 있습니다.

CREATE VIEW 문

데이터베이스에 뷰를 생성하려면 CREATE VIEW 문과 뷰 이름을 사용한 다음 그 뒤에 원하는 쿼리를 입력하면 됩니다.

예: 다음 예제에서는 "라는 이름을 가진 뷰를 생성합니다.모든학생보기” 샘플 데이터베이스에서 “튜토리얼SampleDB.db” 다음과 같습니다:

단계 1) 내 컴퓨터를 열고 다음 디렉토리로 이동합니다.C:\sqlite"를 연 다음 "sqlite3.exe":

단계 2) 데이터베이스 열기 “튜토리얼SampleDB.db다음 명령을 사용하여:

단계 3) 다음은 뷰를 생성하기 위한 sqlite3 명령의 기본 구문입니다.

CREATE VIEW AllStudentsView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

다음과 같은 명령의 출력이 없어야 합니다.

단계 4) 뷰가 생성되었는지 확인하려면 다음 명령을 실행하여 데이터베이스에서 뷰 목록을 선택하세요.

SELECT name FROM sqlite_master WHERE type = 'view';

"라는 뷰를 봐야 합니다.모든학생보기"가 반환됩니다:

단계 5) 이제 뷰가 생성되었으므로 다음과 같은 일반 테이블로 사용할 수 있습니다.

SELECT * FROM AllStudentsView;

이 명령은 "AllStudents" 뷰를 쿼리하고 다음 스크린샷에 표시된 대로 해당 뷰의 모든 행을 선택합니다.

임시 보기

임시 뷰는 그것을 만드는 데 사용된 현재 데이터베이스 연결에 대한 임시 뷰입니다. 그런 다음 데이터베이스 연결을 닫으면 모든 임시 뷰가 자동으로 삭제됩니다. 임시 뷰는 다음 명령 중 하나를 사용하여 생성됩니다.

임시 보기 생성 또는
임시 보기를 만듭니다.

임시 뷰는 당장 어떤 작업을 하고 싶지만 영구적인 뷰가 필요하지 않은 경우에 유용합니다. 따라서 임시 뷰를 만든 다음 해당 뷰를 사용하여 처리하면 됩니다. Later 데이터베이스와의 연결을 닫으면 자동으로 삭제됩니다.

예:

다음 예에서는 데이터베이스 연결을 열고 임시 뷰를 생성합니다.

그런 다음 해당 연결을 닫고 임시 보기가 여전히 존재하는지 확인합니다.

단계 1) 디렉토리에서 sqlite3.exe를 엽니다.C:\sqlite"라고 앞서 설명한 바와 같다.

단계 2) 데이터베이스에 대한 연결 열기 "튜토리얼SampleDB.db다음 명령을 실행하여:

.open TutorialsSampleDB.db

단계 3) 다음 명령을 작성하여 임시 뷰를 생성합니다.모든 학생 임시 보기"

CREATE TEMP VIEW AllStudentsTempView
AS
  SELECT 
    s.StudentId,
    s.StudentName,
    s.DateOfBirth,
    d.DepartmentName
FROM Students AS s
INNER JOIN Departments AS d ON s.DepartmentId = d.DepartmentId;

단계 4) 임시 보기 "모든 학생 임시 보기다음 명령을 실행하면 "가 생성됩니다.

SELECT name FROM sqlite_temp_master WHERE type = 'view';

단계 5) sqlite3.exe를 닫았다가 다시 엽니다.

단계 6) 데이터베이스에 대한 연결 열기 "튜토리얼SampleDB.db다음 명령을 사용하여:

.open TutorialsSampleDB.db

단계 7) 다음 명령을 실행하여 데이터베이스에 생성된 임시 뷰 목록을 가져옵니다.

SELECT name FROM sqlite_temp_master WHERE type = 'view';

이전 단계에서 데이터베이스 연결을 닫았을 때 만든 임시 뷰가 삭제되었기 때문에 출력이 표시되지 않아야 합니다. 그렇지 않으면 데이터베이스가 열린 상태에서 연결을 유지하는 한 데이터가 있는 임시 뷰를 볼 수 있습니다.

배송 시 요청 사항:

뷰에는 INSERT, DELETE 또는 UPDATE 문을 사용할 수 없으며, CREATE 뷰 예제의 5단계에 표시된 대로 "보기에서 선택" 명령만 사용할 수 있습니다.
VIEW를 삭제하려면 "DROP VIEW" 문을 사용할 수 있습니다.

DROP VIEW AllStudentsView;

뷰가 삭제되었는지 확인하려면 다음 명령을 실행하여 데이터베이스에 있는 뷰 목록을 확인하세요.

SELECT name FROM sqlite_master WHERE type = 'view';

다음과 같이 뷰가 삭제되었기 때문에 뷰가 반환되지 않습니다.

SQLite 색인

책이 있고 해당 책에 대한 키워드를 검색하고 싶은 경우. 책의 색인에서 해당 키워드를 검색하게 됩니다. 그런 다음 해당 키워드의 페이지 번호로 이동하여 해당 키워드에 대한 자세한 정보를 읽을 수 있습니다.

하지만 그 책에 색인이나 페이지 번호가 없다면, 당신은 처음부터 끝까지 책 전체를 훑어보며 찾고 있는 키워드를 찾을 것입니다. 그리고 이것은 특히 색인이 있고 키워드를 검색하는 과정이 매우 느리다면 매우 어렵습니다.

인덱스 SQLite (그리고 다른 개념에도 동일한 개념이 적용됩니다. 데이터베이스 관리 시스템 마찬가지로) 책 뒷면에 있는 색인과 동일한 방식으로 작동합니다.

특정 행을 검색할 때 SQLite 검색 기준이 포함된 테이블, SQLite 검색 기준과 일치하는 찾고 있는 행을 찾을 때까지 테이블의 모든 행을 검색합니다. 그리고 더 큰 테이블이 있으면 해당 프로세스가 매우 느려집니다.

인덱스는 데이터에 대한 검색 쿼리 속도를 높이고 테이블에서 데이터 검색을 수행하는 데 도움이 됩니다. 인덱스는 테이블 열에 정의됩니다.

인덱스를 통한 성능 향상:

인덱스는 테이블에서 데이터 검색 성능을 개선할 수 있습니다. 열에 인덱스를 만들 때, SQLite 각 필드 값이 해당 값이 속한 전체 행에 대한 포인터를 갖는 해당 인덱스에 대한 데이터 구조를 생성합니다.

그런 다음, 인덱스에 포함된 컬럼에 대해 검색 조건을 포함하는 쿼리를 실행하면, SQLite 먼저 인덱스에서 값을 찾습니다. SQLite 전체 테이블을 스캔하지는 않습니다. 그런 다음 테이블 행의 값이 가리키는 위치를 읽습니다. SQLite 해당 위치에서 행을 찾아 검색합니다.

하지만 검색하려는 열이 인덱스의 일부가 아닌 경우, SQLite 원하는 데이터를 찾기 위해 열 값에 대한 검색을 수행합니다. 인덱스가 없으면 일반적으로 프로세스 속도가 느려집니다.

색인이 없는 책에서 특정 단어를 검색해야 한다고 상상해 보세요. 책 전체를 첫 페이지부터 마지막 페이지까지 스캔하여 해당 단어를 찾습니다. 그러나 그 책에 색인이 있다면 먼저 그 책에 있는 단어를 찾아볼 것입니다. 해당 페이지 번호를 확인한 다음 해당 페이지로 이동하세요. 책 전체를 처음부터 끝까지 스캔하는 것보다 훨씬 빠릅니다.

SQLite 인덱스 생성

컬럼에 인덱스를 생성하려면 CREATE INDEX 명령을 사용해야 합니다. 그리고 다음과 같이 정의해야 합니다.

CREATE INDEX 명령 다음에 인덱스 이름을 지정해야 합니다.
인덱스 이름 뒤에 키워드 “ON”을 입력하고, 인덱스가 생성될 테이블 이름을 적어야 합니다.
그런 다음 인덱스에 사용되는 열 이름 목록입니다.
인덱스 데이터를 정렬하는 데 사용되는 정렬 순서를 지정하려면 열 이름 뒤에 다음 키워드 중 하나인 "ASC" 또는 "DESC"를 사용할 수 있습니다.

예:

다음 예에서는 인덱스를 생성합니다. “학생 이름 색인” 학생 테이블의 “학생들” 데이터베이스는 다음과 같습니다:

단계 1) "폴더로 이동합니다.C:\sqlite"라고 앞서 설명한 바와 같다.

단계 2) sqlite3.exe를 엽니다.

단계 3) 데이터베이스 열기 “튜토리얼SampleDB.db다음 명령을 사용하여:

.open TutorialsSampleDB.db

단계 4) 새 인덱스 생성 “학생 이름 색인다음 명령을 사용하여:

CREATE INDEX StudentNameIndex ON Students(StudentName);

이에 대한 출력은 표시되지 않습니다.

단계 5) 인덱스가 생성되었는지 확인하려면 다음 쿼리를 실행하면 Students 테이블에 생성된 인덱스 목록이 표시됩니다.

PRAGMA index_list(Students);

방금 생성한 인덱스가 반환된 것을 볼 수 있습니다.

배송 시 요청 사항:

인덱스는 열뿐만 아니라 표현식을 기반으로 생성할 수도 있습니다. 이 같은:

CREATE INDEX OrderTotalIndex ON OrderItems(OrderId, Quantity*Price);

"OrderTotalIndex"는 OrderId 열과 Quantity 열 값 및 Price 열 값의 곱을 기반으로 합니다. 따라서 "OrderId" 및 "Quantity*Price"에 대한 모든 쿼리는 쿼리에서 인덱스를 사용하므로 효율적입니다.

CREATE INDEX 문에서 WHERE 절을 지정한 경우 인덱스는 부분 인덱스가 됩니다. 이 경우 WHERE 절의 조건과 일치하는 행에 대한 항목만 인덱스에 있습니다. 예를 들어, 다음 인덱스에서:
```
CREATE INDEX OrderTotalIndexForLargeQuantities ON OrderItems(OrderId, Quantity*Price)
WHERE Quantity > 10000;
```
( 위의 예에서는 WHERE 절이 지정되어 인덱스가 부분 인덱스가 됩니다. 이 경우 수량 값이 10000보다 큰 주문에만 인덱스가 적용됩니다. 참고로 이 인덱스를 부분 인덱스라고 합니다. WHERE 절에 사용된 표현식이 아니라 인덱스를 사용하기 때문입니다. 단, 일반 인덱스와 함께 표현식을 사용할 수 있습니다.)
CREATE INDEX 대신 CREATE UNIQUE INDEX 문을 사용하면 열에 대한 중복 항목을 방지할 수 있으므로 인덱싱된 열의 모든 값이 고유해집니다.
인덱스를 삭제하려면 DROP INDEX 명령 뒤에 삭제할 인덱스 이름을 사용하세요.

SQLite 트리거

장점 소개 SQLite 트리거

트리거는 데이터베이스 테이블에서 특정 작업이 발생할 때 실행되는 자동 사전 정의된 작업입니다. 트리거는 테이블에서 다음 작업 중 하나가 발생할 때마다 실행되도록 정의할 수 있습니다.

테이블에 INSERT합니다.
테이블에서 행을 삭제합니다.
테이블 열 중 하나를 업데이트합니다.

SQLite FOR EACH ROW 트리거를 지원하므로 트리거에 미리 정의된 작업이 테이블에서 발생하는 작업(삽입, 삭제 또는 업데이트)에 관련된 모든 행에 대해 실행됩니다.

SQLite 트리거 생성

새로운 TRIGGER를 생성하려면 다음과 같이 CREATE TRIGGER 명령문을 사용하면 됩니다.

CREATE TRIGGER 뒤에는 트리거 이름을 지정해야 합니다.
트리거 이름 뒤에는 해당 트리거 이름이 정확히 언제 실행되어야 하는지 지정해야 합니다. 세 가지 옵션이 있습니다:
BEFORE - 트리거는 INSERT, UPDATE 또는 지정된 삭제 문 이전에 실행됩니다.
이후 – 지정된 INSERT, UPDATE 또는 삭제 문 이후에 트리거가 실행됩니다.
INSTEAD OF – 트리거를 실행한 작업을 TRIGGER에 지정된 문으로 대체합니다. INSTEAD OF 트리거는 테이블에는 적용할 수 없으며 뷰에만 적용할 수 있습니다.

그런 다음 작업 유형을 지정해야 하며, 해당 작업이 발생할 때 트리거가 실행됩니다. DELETE, INSERT 또는 UPDATE 중 하나입니다.

해당 열에서 작업이 발생하지 않는 한 트리거가 실행되지 않도록 선택적 열 이름을 선택할 수 있습니다.

그런 다음 트리거가 생성될 테이블 이름을 지정해야 합니다.

트리거 본문 내에서 트리거가 실행될 때 각 행에 대해 실행되어야 하는 문을 지정해야 합니다.

트리거는 create Trigger 명령에 지정된 문의 유형에 따라서만 활성화(실행)됩니다. 예를 들어:

BEFORE INSERT 트리거는 삽입 문 전에 활성화(실행)됩니다.
AFTER UPDATE 트리거는 업데이트 문 이후에 활성화(실행)됩니다.

트리거 내부에서 "new" 키워드를 사용하여 새로 삽입된 값을 참조할 수 있습니다. 또한, old 키워드를 사용하여 삭제되거나 업데이트된 값을 참조할 수 있습니다. 다음과 같습니다.

INSERT 트리거 내부 – 새 키워드를 사용할 수 있습니다.
UPDATE 트리거 내부 – 신규 및 기존 키워드를 사용할 수 있습니다.
DELETE 트리거 내부 – 이전 키워드를 사용할 수 있습니다.

예시

다음에서는 "에 새 학생을 삽입하기 전에 실행되는 트리거를 생성합니다.학생들”테이블.

새로 삽입된 학생을 테이블에 기록합니다.학생기록” 삽입 명령문이 발생한 현재 날짜 시간에 대한 자동 타임 스탬프가 있습니다. 다음과 같습니다.

단계 1) "디렉토리로 이동합니다.C:\sqlite”를 입력하고 sqlite3.exe를 실행합니다.

단계 2) 데이터베이스 열기 “튜토리얼SampleDB.db다음 명령을 실행하여:

.open TutorialsSampleDB.db

단계 3) 트리거 생성 "InsertIntoStudentTrigger” 다음 명령을 실행하여:

CREATE TRIGGER InsertIntoStudentTrigger 
       BEFORE INSERT ON Students
BEGIN
  INSERT INTO StudentsLog VALUES(new.StudentId, datetime(), 'Insert');
END;

기능 "날짜 시간()" insert 문이 발생한 시점의 현재 날짜 타임스탬프를 제공합니다. 각 트랜잭션에 자동 타임스탬프를 추가하여 삽입 트랜잭션을 기록할 수 있습니다.

명령이 성공적으로 실행되고 출력이 표시되지 않습니다.

방아쇠 "InsertIntoStudentTrigger'는 학생 테이블에 새 학생을 삽입할 때마다 실행됩니다. “ ” 키워드는 삽입될 값을 의미합니다. 예를 들어, “new.학생ID”가 삽입될 학생 ID가 됩니다.

이제 새 학생을 삽입할 때 트리거가 어떻게 작동하는지 테스트하겠습니다.

단계 4) 학생 테이블에 새로운 학생을 삽입하는 다음 명령을 작성하세요.

INSERT INTO Students VALUES(11, 'guru11', 1, '1999-10-12');

단계 5) "에서 모든 행을 선택하는 다음 명령을 작성하십시오.학생기록" 테이블:

SELECT * FROM StudentsLog;

방금 삽입한 새 학생에 대해 반환된 새 행이 표시됩니다.

이 행은 ID가 11인 새 학생을 삽입하기 전에 트리거에 의해 삽입되었습니다.

이 예에서는 '트리거를 사용했습니다. InsertIntoStudentTrigger "우리는 테이블에 삽입 트랜잭션을 기록하기 위해 만들었습니다."학생기록” 자동으로. 같은 방법으로 모든 업데이트 또는 삭제 문을 기록할 수 있습니다.

트리거로 의도하지 않은 업데이트 방지:

테이블에서 BEFORE UPDATE 트리거를 사용하면 표현식을 기반으로 하는 열의 업데이트 문을 방지할 수 있습니다.

예시

다음 예에서는 모든 업데이트 문이 Students 테이블의 "studentname" 열을 업데이트하는 것을 방지합니다.

단계 1) "디렉토리로 이동합니다.C:\sqlite”를 입력하고 sqlite3.exe를 실행합니다.

단계 2) 데이터베이스 열기 “튜토리얼SampleDB.db다음 명령을 실행하여:

.open TutorialsSampleDB.db

단계 3) 새 트리거 만들기 'PreventUpdate학생 이름" 책상 위에 "학생들” 다음 명령을 실행하여

CREATE TRIGGER preventUpdateStudentName
BEFORE UPDATE OF StudentName ON Students
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, 'You cannot update studentname');
END;

"올리다" 명령은 오류 메시지와 함께 오류를 발생시킵니다. " 학생 이름을 업데이트할 수 없습니다 ", 그러면 업데이트 문이 실행되지 않습니다.

이제 트리거가 제대로 작동하고 학생 이름 열에 대한 업데이트가 차단되는지 확인하겠습니다.

단계 4) 다음 업데이트 명령을 실행하면 학생 이름이 업데이트됩니다.잭" 장차 ~ 가 되는 "Jack1".

UPDATE Students SET StudentName = 'Jack1' WHERE StudentName = 'Jack';

트리거에 지정한 오류 메시지가 나타나야 합니다.학생 이름을 업데이트할 수 없습니다” 다음과 같습니다:

단계 5) 다음 명령을 실행하면 students 테이블에서 학생 이름 목록이 선택됩니다.

SELECT StudentName FROM Students;

학생 이름 "Jack"이 여전히 동일하고 변경되지 않는 것을 볼 수 있습니다.

요약

뷰, 인덱스, 트리거는 관리를 위한 매우 강력한 도구입니다. SQLite 데이터베이스. 테이블에서 데이터 수정 작업이 발생할 때 이를 추적할 수 있습니다. 인덱스를 생성하여 데이터베이스 데이터 검색 작업을 최적화할 수도 있습니다.

당신은 좋아할 것입니다 :

SQLite 관측

관련 기사

SQLite 색인

SQLite 트리거

요약