SQLite - szybki przewodnik
Ten rozdział pomoże ci zrozumieć, czym jest SQLite, czym różni się od SQL, dlaczego jest potrzebny oraz w jaki sposób obsługuje bazę danych aplikacji.
SQLite to biblioteka oprogramowania, która implementuje samodzielny, bezserwerowy silnik transakcyjnej bazy danych SQL o zerowej konfiguracji. SQLite jest jednym z najszybciej rozwijających się silników baz danych, ale jest to wzrost pod względem popularności, a nie z jego rozmiarem. Kod źródłowy SQLite jest w domenie publicznej.
Co to jest SQLite?
SQLite jest biblioteką w procesie, która implementuje niezależny, bezserwerowy silnik transakcyjnej bazy danych SQL o zerowej konfiguracji. Jest to baza danych skonfigurowana na zero, co oznacza, że podobnie jak inne bazy danych nie musisz jej konfigurować w swoim systemie.
Silnik SQLite nie jest samodzielnym procesem, podobnie jak inne bazy danych, można go łączyć statycznie lub dynamicznie, zgodnie z wymaganiami, z aplikacją. SQLite uzyskuje bezpośredni dostęp do swoich plików pamięci.
Dlaczego SQLite?
SQLite nie wymaga oddzielnego procesu serwera ani systemu do działania (bezserwerowe).
SQLite jest dostarczany z zerową konfiguracją, co oznacza, że nie jest wymagana konfiguracja ani administracja.
Cała baza danych SQLite jest przechowywana w jednym pliku dyskowym obejmującym wiele platform.
SQLite jest bardzo mały i lekki, mniej niż 400 KB w pełni skonfigurowany lub mniej niż 250 KB z pominiętymi opcjonalnymi funkcjami.
SQLite jest samowystarczalny, co oznacza brak zależności zewnętrznych.
Transakcje SQLite są w pełni zgodne z ACID, umożliwiając bezpieczny dostęp z wielu procesów lub wątków.
SQLite obsługuje większość funkcji języka zapytań znajdujących się w standardzie SQL92 (SQL2).
SQLite jest napisany w ANSI-C i zapewnia prosty i łatwy w użyciu interfejs API.
SQLite jest dostępny na UNIX (Linux, Mac OS-X, Android, iOS) i Windows (Win32, WinCE, WinRT).
SQLite Krótka historia
2000 - D. Richard Hipp zaprojektował SQLite tak, aby administracja nie była wymagana do obsługi programu.
2000 - W sierpniu wydano SQLite 1.0 z GNU Database Manager.
2011 - Hipp ogłosił dodanie interfejsu UNQl do SQLite DB i opracowanie UNQLite (bazy danych zorientowanej na dokumenty).
Ograniczenia SQLite
Istnieje kilka nieobsługiwanych funkcji SQL92 w SQLite, które są wymienione w poniższej tabeli.
| Sr.No. | Funkcja i opis |
|---|---|
| 1 | RIGHT OUTER JOIN Zaimplementowano tylko LEFT OUTER JOIN. |
| 2 | FULL OUTER JOIN Zaimplementowano tylko LEFT OUTER JOIN. |
| 3 | ALTER TABLE Obsługiwane są warianty RENAME TABLE i ADD COLUMN polecenia ALTER TABLE. DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT nie są obsługiwane. |
| 4 | Trigger support Wyzwalacze FOR EACH ROW są obsługiwane, ale nie wyzwalacze FOR EACH STATEMENT. |
| 5 | VIEWs Widoki w SQLite są tylko do odczytu. W widoku nie można wykonywać instrukcji DELETE, INSERT ani UPDATE. |
| 6 | GRANT and REVOKE Jedyne uprawnienia dostępu, które można zastosować, to zwykłe uprawnienia dostępu do plików w podstawowym systemie operacyjnym. |
Polecenia SQLite
Standardowe polecenia SQLite do interakcji z relacyjnymi bazami danych są podobne do SQL. Są to CREATE, SELECT, INSERT, UPDATE, DELETE i DROP. Polecenia te można podzielić na grupy ze względu na ich charakter operacyjny -
DDL - język definicji danych
| Sr.No. | Polecenie i opis |
|---|---|
| 1 | CREATE Tworzy nową tabelę, widok tabeli lub inny obiekt w bazie danych. |
| 2 | ALTER Modyfikuje istniejący obiekt bazy danych, taki jak tabela. |
| 3 | DROP Usuwa całą tabelę, widok tabeli lub inny obiekt w bazie danych. |
DML - język manipulacji danymi
| Sr.No. | Polecenie i opis |
|---|---|
| 1 | INSERT Tworzy rekord |
| 2 | UPDATE Modyfikuje rekordy |
| 3 | DELETE Usuwa rekordy |
DQL - język zapytań danych
| Sr.No. | Polecenie i opis |
|---|---|
| 1 | SELECT Pobiera określone rekordy z co najmniej jednej tabeli |
SQLite słynie z doskonałej funkcji zerowej konfiguracji, co oznacza, że nie jest wymagana żadna skomplikowana konfiguracja ani administracja. Ten rozdział poprowadzi Cię przez proces konfiguracji SQLite w systemach Windows, Linux i Mac OS X.
Zainstaluj SQLite w systemie Windows
Step 1- Przejdź do strony pobierania SQLite i pobierz prekompilowane pliki binarne z sekcji Windows.
Step 2 - Pobierz pliki sqlite-shell-win32 - *. Zip i sqlite-dll-win32 - *. Zip zip.
Step 3 - Utwórz folder C: \> sqlite i rozpakuj powyżej dwa spakowane pliki w tym folderze, co da ci pliki sqlite3.def, sqlite3.dll i sqlite3.exe.
Step 4 - Dodaj C: \> sqlite do zmiennej środowiskowej PATH, a na koniec przejdź do wiersza poleceń i wydaj polecenie sqlite3, które powinno wyświetlić następujący wynik.
C:\>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>Zainstaluj SQLite w systemie Linux
Obecnie prawie wszystkie wersje systemu operacyjnego Linux są dostarczane z oprogramowaniem SQLite. Po prostu wydajesz następujące polecenie, aby sprawdzić, czy masz już zainstalowany program SQLite na swoim komputerze.
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>Jeśli nie widzisz powyższego wyniku, oznacza to, że nie masz zainstalowanego oprogramowania SQLite na komputerze z systemem Linux. Poniżej znajdują się następujące kroki, aby zainstalować SQLite -
Step 1- Przejdź do strony pobierania SQLite i pobierz sqlite-autoconf - *. Tar.gz z sekcji kodu źródłowego.
Step 2 - Uruchom następujące polecenie -
$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502 $./configure --prefix=/usr/local
$make $make installPowyższe polecenie zakończy się instalacją oprogramowania SQLite na komputerze z systemem Linux. Które możesz zweryfikować, jak wyjaśniono powyżej.
Zainstaluj SQLite w systemie Mac OS X
Chociaż najnowsza wersja systemu Mac OS X jest wstępnie zainstalowana z oprogramowaniem SQLite, ale jeśli nie masz dostępnej instalacji, wykonaj następujące czynności -
Step 1- Przejdź do strony pobierania SQLite i pobierz sqlite-autoconf - *. Tar.gz z sekcji kodu źródłowego.
Step 2 - Uruchom następujące polecenie -
$tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502
$./configure --prefix=/usr/local $make
$make installPowyższa procedura zakończy się instalacją oprogramowania SQLite na komputerze z systemem Mac OS X. Które możesz zweryfikować, wydając następujące polecenie -
$sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>Wreszcie masz wiersz poleceń SQLite, w którym możesz wydawać polecenia SQLite do swoich ćwiczeń.
Ten rozdział przeprowadzi Cię przez proste i przydatne polecenia używane przez programistów SQLite. Te polecenia nazywane są poleceniami z kropkami SQLite, a wyjątek od tych poleceń jest taki, że nie powinny być zakończone średnikiem (;).
Zacznijmy od wpisania prostego sqlite3 polecenie w wierszu polecenia, które zapewni ci wiersz polecenia SQLite, w którym będziesz wydawać różne polecenia SQLite.
$sqlite3
SQLite version 3.3.6
Enter ".help" for instructions
sqlite>Aby wyświetlić listę dostępnych poleceń kropkowych, możesz w dowolnym momencie wpisać „.help”. Na przykład -
sqlite>.helpPowyższe polecenie wyświetli listę różnych ważnych poleceń kropek SQLite, które są wymienione w poniższej tabeli.
| Sr.No. | Polecenie i opis |
|---|---|
| 1 | .backup ?DB? FILE Utwórz kopię zapasową bazy danych (domyślnie „główna”) do PLIKU |
| 2 | .bail ON|OFF Zatrzymaj się po trafieniu błędu. Domyślnie WYŁ |
| 3 | .databases Lista nazw i plików dołączonych baz danych |
| 4 | .dump ?TABLE? Zrzuć bazę danych w formacie tekstowym SQL. Jeśli określono TABLE, zrzuca tylko tabele pasujące do LIKE wzorca TABLE |
| 5 | .echo ON|OFF Włącz lub wyłącz echo poleceń |
| 6 | .exit Wyjdź z zachęty SQLite |
| 7 | .explain ON|OFF Włącz lub wyłącz tryb wyjścia odpowiedni dla EXPLAIN. Bez argumentów włącza EXPLAIN |
| 8 | .header(s) ON|OFF Włącz lub wyłącz wyświetlanie nagłówków |
| 9 | .help Pokaż tę wiadomość |
| 10 | .import FILE TABLE Importuj dane z PLIKU do TABELI |
| 11 | .indices ?TABLE? Pokaż nazwy wszystkich indeksów. Jeśli określono TABLE, pokaż tylko indeksy dla tabel pasujących do LIKE wzorca TABLE |
| 12 | .load FILE ?ENTRY? Załaduj bibliotekę rozszerzeń |
| 13 | .log FILE|off Włącz lub wyłącz logowanie. FILE może być stderr / stdout |
| 14 | .mode MODE Ustaw tryb wyjścia, w którym TRYB jest jednym z -
|
| 15 | .nullvalue STRING Drukuj STRING zamiast wartości NULL |
| 16 | .output FILENAME Wyślij dane wyjściowe do FILENAME |
| 17 | .output stdout Wyślij dane wyjściowe na ekran |
| 18 | .print STRING... Drukuj dosłowny STRING |
| 19 | .prompt MAIN CONTINUE Zastąp standardowe monity |
| 20 | .quit Wyjdź z zachęty SQLite |
| 21 | .read FILENAME Wykonaj SQL w FILENAME |
| 22 | .schema ?TABLE? Pokaż instrukcje CREATE. Jeśli określono TABLE, pokaż tylko tabele pasujące do LIKE wzorca TABLE |
| 23 | .separator STRING Zmień separator używany przez tryb wyjściowy i .import |
| 24 | .show Pokaż aktualne wartości różnych ustawień |
| 25 | .stats ON|OFF Włącz lub wyłącz statystyki |
| 26 | .tables ?PATTERN? Lista nazw tabel pasujących do wzorca LIKE |
| 27 | .timeout MS Spróbuj otworzyć zablokowane tabele na milisekundy MS |
| 28 | .width NUM NUM Ustaw szerokości kolumn dla trybu „kolumna” |
| 29 | .timer ON|OFF Włączanie lub wyłączanie pomiaru timera procesora |
Spróbujmy .show polecenie, aby wyświetlić domyślne ustawienie wiersza polecenia programu SQLite.
sqlite>.show
echo: off
explain: off
headers: off
mode: column
nullvalue: ""
output: stdout
separator: "|"
width:
sqlite>Upewnij się, że nie ma spacji między znakiem zachęty sqlite> a poleceniem kropka, w przeciwnym razie nie będzie działać.
Formatowanie danych wyjściowych
Aby sformatować dane wyjściowe, możesz użyć następującej sekwencji poleceń dot.
sqlite>.header on
sqlite>.mode column
sqlite>.timer on
sqlite>Powyższe ustawienie da wynik w następującym formacie.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
CPU Time: user 0.000000 sys 0.000000Tabela sqlite_master
Tabela główna zawiera kluczowe informacje o tabelach bazy danych i nazywa się sqlite_master. Możesz zobaczyć jego schemat w następujący sposób -
sqlite>.schema sqlite_masterSpowoduje to następujący wynik.
CREATE TABLE sqlite_master (
type text,
name text,
tbl_name text,
rootpage integer,
sql text
);Po SQLite następuje unikalny zestaw reguł i wskazówek o nazwie Składnia. W tym rozdziale wymieniono całą podstawową składnię SQLite.
Rozróżnianie wielkości liter
Należy zauważyć, że SQLite jest case insensitive, czyli klauzule GLOB i glob mają to samo znaczenie w instrukcjach SQLite.
Komentarze
Komentarze SQLite to dodatkowe uwagi, które możesz dodać do swojego kodu SQLite, aby zwiększyć jego czytelność i mogą pojawić się w dowolnym miejscu; mogą wystąpić spacje, w tym wewnątrz wyrażeń i w środku innych instrukcji SQL, ale nie można ich zagnieżdżać.
Komentarze SQL zaczynają się od dwóch kolejnych znaków „-” (ASCII 0x2d) i rozciągają się do następnego znaku nowej linii (ASCII 0x0a) włącznie lub do końca wprowadzania, w zależności od tego, co nastąpi wcześniej.
Możesz także używać komentarzy w stylu C, które zaczynają się od „/ *” i rozciągają się do następnej pary znaków „* /” włącznie lub do końca wprowadzania, w zależności od tego, co nastąpi wcześniej. Komentarze w stylu C mogą obejmować wiele linii.
sqlite> .help -- This is a single line commentInstrukcje SQLite
Wszystkie instrukcje SQLite zaczynają się od dowolnego ze słów kluczowych, takich jak SELECT, INSERT, UPDATE, DELETE, ALTER, DROP itd., A wszystkie instrukcje kończą się średnikiem (;).
Instrukcja SQLite ANALYZE
ANALYZE;
or
ANALYZE database_name;
or
ANALYZE database_name.table_name;Klauzula AND / OR SQLite
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION-1 {AND|OR} CONDITION-2;Instrukcja SQLite ALTER TABLE
ALTER TABLE table_name ADD COLUMN column_def...;Instrukcja SQLite ALTER TABLE (zmiana nazwy)
ALTER TABLE table_name RENAME TO new_table_name;Instrukcja SQLite ATTACH DATABASE
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';Instrukcja SQLite BEGIN TRANSACTION
BEGIN;
or
BEGIN EXCLUSIVE TRANSACTION;Klauzula SQLite BETWEEN
SELECT column1, column2....columnN
FROM table_name
WHERE column_name BETWEEN val-1 AND val-2;Instrukcja COMMIT programu SQLite
COMMIT;Instrukcja SQLite CREATE INDEX
CREATE INDEX index_name
ON table_name ( column_name COLLATE NOCASE );Instrukcja SQLite CREATE UNIQUE INDEX
CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN);Instrukcja SQLite CREATE TABLE
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);Instrukcja SQLite CREATE TRIGGER
CREATE TRIGGER database_name.trigger_name
BEFORE INSERT ON table_name FOR EACH ROW
BEGIN
stmt1;
stmt2;
....
END;Instrukcja SQLite CREATE VIEW
CREATE VIEW database_name.view_name AS
SELECT statement....;Instrukcja SQLite CREATE VIRTUAL TABLE
CREATE VIRTUAL TABLE database_name.table_name USING weblog( access.log );
or
CREATE VIRTUAL TABLE database_name.table_name USING fts3( );Instrukcja SQLite COMMIT TRANSACTION
COMMIT;Klauzula COUNT programu SQLite
SELECT COUNT(column_name)
FROM table_name
WHERE CONDITION;Instrukcja SQLite DELETE
DELETE FROM table_name
WHERE {CONDITION};Instrukcja SQLite DETACH DATABASE
DETACH DATABASE 'Alias-Name';Klauzula SQLite DISTINCT
SELECT DISTINCT column1, column2....columnN
FROM table_name;Instrukcja SQLite DROP INDEX
DROP INDEX database_name.index_name;Instrukcja SQLite DROP TABLE
DROP TABLE database_name.table_name;Instrukcja SQLite DROP VIEW
DROP INDEX database_name.view_name;Instrukcja SQLite DROP TRIGGER
DROP INDEX database_name.trigger_name;Klauzula SQLite EXISTS
SELECT column1, column2....columnN
FROM table_name
WHERE column_name EXISTS (SELECT * FROM table_name );Instrukcja SQLite EXPLAIN
EXPLAIN INSERT statement...;
or
EXPLAIN QUERY PLAN SELECT statement...;Klauzula GLOB SQLite
SELECT column1, column2....columnN
FROM table_name
WHERE column_name GLOB { PATTERN };Klauzula GROUP BY programu SQLite
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name;Klauzula SQLite HAVING
SELECT SUM(column_name)
FROM table_name
WHERE CONDITION
GROUP BY column_name
HAVING (arithematic function condition);Instrukcja SQLite INSERT INTO
INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN);Klauzula SQLite IN
SELECT column1, column2....columnN
FROM table_name
WHERE column_name IN (val-1, val-2,...val-N);SQLite Like Clause
SELECT column1, column2....columnN
FROM table_name
WHERE column_name LIKE { PATTERN };SQLite NOT IN klauzula
SELECT column1, column2....columnN
FROM table_name
WHERE column_name NOT IN (val-1, val-2,...val-N);Klauzula ORDER BY programu SQLite
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION
ORDER BY column_name {ASC|DESC};Instrukcja SQLite PRAGMA
PRAGMA pragma_name;
For example:
PRAGMA page_size;
PRAGMA cache_size = 1024;
PRAGMA table_info(table_name);Instrukcja SQLite RELEASE SAVEPOINT
RELEASE savepoint_name;Instrukcja SQLite REINDEX
REINDEX collation_name;
REINDEX database_name.index_name;
REINDEX database_name.table_name;Instrukcja SQLite ROLLBACK
ROLLBACK;
or
ROLLBACK TO SAVEPOINT savepoint_name;Instrukcja SQLite SAVEPOINT
SAVEPOINT savepoint_name;Instrukcja SQLite SELECT
SELECT column1, column2....columnN
FROM table_name;Instrukcja SQLite UPDATE
UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE CONDITION ];Instrukcja SQLite VACUUM
VACUUM;Klauzula SQLite WHERE
SELECT column1, column2....columnN
FROM table_name
WHERE CONDITION;Typ danych SQLite to atrybut, który określa typ danych dowolnego obiektu. Każda kolumna, zmienna i wyrażenie ma powiązany typ danych w SQLite.
Tych typów danych można użyć podczas tworzenia tabel. SQLite używa bardziej ogólnego dynamicznego systemu typów. W SQLite typ danych wartości jest powiązany z samą wartością, a nie z jej kontenerem.
Klasy pamięci SQLite
Każda wartość przechowywana w bazie danych SQLite ma jedną z następujących klas pamięci -
| Sr.No. | Klasa pamięci i opis |
|---|---|
| 1 | NULL Wartość jest wartością NULL. |
| 2 | INTEGER Wartość jest liczbą całkowitą ze znakiem, przechowywaną w 1, 2, 3, 4, 6 lub 8 bajtach, w zależności od wielkości wartości. |
| 3 | REAL Wartość jest wartością zmiennoprzecinkową, przechowywaną jako 8-bajtowa liczba zmiennoprzecinkowa IEEE. |
| 4 | TEXT Wartość to ciąg tekstowy zapisany przy użyciu kodowania bazy danych (UTF-8, UTF-16BE lub UTF-16LE) |
| 5 | BLOB Wartość to zbiór danych przechowywanych dokładnie tak, jak zostały wprowadzone. |
Klasa pamięci SQLite jest nieco bardziej ogólna niż typ danych. Na przykład klasa pamięci INTEGER zawiera 6 różnych typów danych typu integer o różnych długościach.
Typ powinowactwa SQLite
SQLite obsługuje koncepcję type affinityna kolumnach. Każda kolumna może nadal przechowywać dane dowolnego typu, ale preferowaną klasą pamięci dla kolumny jest jejaffinity. Każda kolumna tabeli w bazie danych SQLite3 ma przypisane jedno z następujących podobieństw typu -
| Sr.No. | Podobieństwo i opis |
|---|---|
| 1 | TEXT Ta kolumna przechowuje wszystkie dane przy użyciu klas pamięci NULL, TEXT lub BLOB. |
| 2 | NUMERIC Ta kolumna może zawierać wartości wykorzystujące wszystkie pięć klas pamięci. |
| 3 | INTEGER Zachowuje się tak samo, jak kolumna z powinowactwem NUMERIC, z wyjątkiem wyrażenia CAST. |
| 4 | REAL Zachowuje się jak kolumna z powinowactwem NUMERIC, z wyjątkiem tego, że wymusza wartości całkowite w reprezentacji zmiennoprzecinkowej. |
| 5 | NONE Kolumna z powinowactwem NONE nie preferuje jednej klasy pamięci od innej i nie podejmuje się próby przekształcenia danych z jednej klasy do drugiej. |
Nazwy koligacji i typów SQLite
Poniższa tabela zawiera listę różnych nazw typów danych, których można używać podczas tworzenia tabel SQLite3 z odpowiednim zastosowanym powinowactwem.
| Typ danych | Powinowactwo |
|---|---|
|
LICZBA CAŁKOWITA |
|
TEKST |
|
ŻADEN |
|
REAL |
|
NUMERYCZNE |
Typ danych logicznych
SQLite nie ma oddzielnej klasy pamięci typu Boolean. Zamiast tego wartości logiczne są przechowywane jako liczby całkowite 0 (fałsz) i 1 (prawda).
Typ danych daty i godziny
SQLite nie ma oddzielnej klasy pamięci do przechowywania dat i / lub godzin, ale SQLite może przechowywać daty i godziny jako wartości TEXT, REAL lub INTEGER.
| Sr.No. | Klasa pamięci i format daty |
|---|---|
| 1 | TEXT Data w formacie takim jak „RRRR-MM-DD GG: MM: SS.SSS” |
| 2 | REAL Liczba dni od południa w Greenwich 24 listopada 4714 rpne |
| 3 | INTEGER Liczba sekund od 1970-01-01 00:00:00 UTC |
Możesz wybrać przechowywanie dat i godzin w dowolnym z tych formatów i swobodnie konwertować między formatami za pomocą wbudowanych funkcji daty i czasu.
W SQLite sqlite3polecenie służy do tworzenia nowej bazy danych SQLite. Nie musisz mieć żadnych specjalnych uprawnień do tworzenia bazy danych.
Składnia
Poniżej przedstawiono podstawową składnię polecenia sqlite3 do tworzenia bazy danych: -
$sqlite3 DatabaseName.dbZawsze nazwa bazy danych powinna być unikalna w systemie RDBMS.
Przykład
Jeśli chcesz utworzyć nową bazę danych <testDB.db>, instrukcja SQLITE3 wyglądałaby następująco -
$sqlite3 testDB.db
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>Powyższe polecenie utworzy plik testDB.dbw bieżącym katalogu. Ten plik będzie używany jako baza danych przez silnik SQLite. Jeśli zauważyłeś podczas tworzenia bazy danych, polecenie sqlite3 zapewni pliksqlite> monit po pomyślnym utworzeniu pliku bazy danych.
Po utworzeniu bazy danych możesz ją zweryfikować na liście baz danych za pomocą następującego oprogramowania SQLite .databases Komenda.
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.dbBędziesz używać SQLite .quit polecenie wyjścia z zachęty sqlite w następujący sposób -
sqlite>.quit
$Polecenie .dump
Możesz użyć .dump dot polecenie, aby wyeksportować pełną bazę danych do pliku tekstowego za pomocą następującego polecenia SQLite w wierszu polecenia.
$sqlite3 testDB.db .dump > testDB.sqlPowyższe polecenie skonwertuje całą zawartość testDB.db bazy danych do instrukcji SQLite i zrzuć ją do pliku tekstowego ASCII testDB.sql. Możesz przeprowadzić przywracanie z wygenerowanego testDB.sql w prosty sposób w następujący sposób -
$sqlite3 testDB.db < testDB.sqlW tej chwili twoja baza danych jest pusta, więc możesz wypróbować powyższe dwie procedury, gdy masz kilka tabel i danych w swojej bazie danych. Na razie przejdźmy do następnego rozdziału.
Rozważ przypadek, gdy masz dostępnych wiele baz danych i chcesz używać jednej z nich naraz. SQLiteATTACH DATABASE instrukcja służy do wybierania określonej bazy danych, a po tym poleceniu wszystkie instrukcje SQLite będą wykonywane pod dołączoną bazą danych.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji SQLite ATTACH DATABASE.
ATTACH DATABASE 'DatabaseName' As 'Alias-Name';Powyższe polecenie spowoduje również utworzenie bazy danych w przypadku, gdy baza danych nie została jeszcze utworzona, w przeciwnym razie po prostu dołączy nazwę pliku bazy danych z logiczną nazwą bazy danych „Alias-Name”.
Przykład
Jeśli chcesz dołączyć istniejącą bazę danych testDB.db, to instrukcja ATTACH DATABASE wyglądałaby następująco -
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';Użyj SQLite .database polecenie wyświetlenia dołączonej bazy danych.
sqlite> .database
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.dbNazwy baz danych main i tempsą zarezerwowane dla podstawowej bazy danych i bazy danych do przechowywania tabel tymczasowych i innych tymczasowych obiektów danych. Obie te nazwy baz danych istnieją dla każdego połączenia z bazą danych i nie powinny być używane do załączania, w przeciwnym razie zostanie wyświetlony następujący komunikat ostrzegawczy.
sqlite> ATTACH DATABASE 'testDB.db' as 'TEMP';
Error: database TEMP is already in use
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database TEMP is already in useSQLite DETACH DATABASEInstrukcja służy do odłączania nazwanej bazy danych i odłączania jej od połączenia z bazą danych, które zostało wcześniej dołączone za pomocą instrukcji ATTACH. Jeśli ten sam plik bazy danych został dołączony z wieloma aliasami, polecenie DETACH rozłączy tylko podaną nazwę, a reszta załącznika będzie nadal działać. Nie możesz odłączyćmain lub temp bazy danych.
Jeśli baza danych znajduje się w pamięci lub jest tymczasową bazą danych, zostanie zniszczona, a zawartość zostanie utracona.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji SQLite DETACH DATABASE „Alias-Name”.
DETACH DATABASE 'Alias-Name';Tutaj „Alias-Name” jest tym samym aliasem, którego użyłeś podczas dołączania bazy danych za pomocą instrukcji ATTACH.
Przykład
Załóżmy, że masz bazę danych, którą utworzyłeś w poprzednim rozdziale i dołączyłeś do niej „test” i „currentDB”, jak widać za pomocą .database Komenda.
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.db
3 currentDB /home/sqlite/testDB.dbSpróbujmy odłączyć „currentDB” od testDB.db za pomocą następującego polecenia.
sqlite> DETACH DATABASE 'currentDB';Teraz, jeśli sprawdzisz aktualny załącznik, zobaczysz, że testDB.db jest nadal połączony z 'test' i 'main'.
sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/sqlite/testDB.db
2 test /home/sqlite/testDB.dbSQLite CREATE TABLEinstrukcja służy do tworzenia nowej tabeli w dowolnej z podanych baz danych. Tworzenie podstawowej tabeli obejmuje nazwanie tabeli i zdefiniowanie jej kolumn oraz typu danych każdej kolumny.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji CREATE TABLE.
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
.....
columnN datatype
);CREATE TABLE jest słowem kluczowym nakazującym systemowi bazy danych utworzenie nowej tabeli. Unikalna nazwa lub identyfikator tabeli następuje po instrukcji CREATE TABLE. Opcjonalnie można określić nazwę_bazy_danych wraz z nazwą_tabeli .
Przykład
Poniżej znajduje się przykład tworzenia tabeli COMPANY z identyfikatorem jako kluczem podstawowym i wartością NOT NULL, które pokazują, że te pola nie mogą mieć wartości NULL podczas tworzenia rekordów w tej tabeli.
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Stwórzmy jeszcze jedną tabelę, której użyjemy w naszych ćwiczeniach w kolejnych rozdziałach.
sqlite> CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);Możesz sprawdzić, czy Twoja tabela została pomyślnie utworzona za pomocą polecenia SQLite .tables polecenie, które zostanie użyte do wyświetlenia wszystkich tabel w dołączonej bazie danych.
sqlite>.tables
COMPANY DEPARTMENTTutaj możesz zobaczyć tabelę COMPANY dwukrotnie, ponieważ pokazuje ona tabelę COMPANY dla głównej bazy danych i tabelę test.COMPANY dla aliasu „test” utworzonego dla twojego testDB.db. Możesz uzyskać pełne informacje o tabeli, korzystając z następującego oprogramowania SQLite.schema Komenda.
sqlite>.schema COMPANY
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);SQLite DROP TABLE Instrukcja służy do usuwania definicji tabeli i wszystkich powiązanych danych, indeksów, wyzwalaczy, ograniczeń i specyfikacji uprawnień dla tej tabeli.
Musisz zachować ostrożność podczas korzystania z tego polecenia, ponieważ po usunięciu tabeli wszystkie informacje dostępne w tabeli również zostaną utracone na zawsze.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji DROP TABLE. Opcjonalnie możesz określić nazwę bazy danych wraz z nazwą tabeli w następujący sposób -
DROP TABLE database_name.table_name;Przykład
Najpierw zweryfikujmy tabelę COMPANY, a następnie usuniemy ją z bazy danych.
sqlite>.tables
COMPANY test.COMPANYOznacza to, że tabela COMPANY jest dostępna w bazie danych, więc upuśćmy ją w następujący sposób -
sqlite>DROP TABLE COMPANY;
sqlite>Teraz, jeśli spróbujesz polecenia .TABLES, nie znajdziesz już tabeli COMPANY.
sqlite>.tables
sqlite>Nie pokazuje nic, co oznacza, że tabela z bazy danych została pomyślnie usunięta.
SQLite INSERT INTO Instrukcja służy do dodawania nowych wierszy danych do tabeli w bazie danych.
Składnia
Poniżej przedstawiono dwie podstawowe składnie instrukcji INSERT INTO.
INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);Tutaj kolumna1, kolumna2, ... kolumnaN to nazwy kolumn w tabeli, do których chcesz wstawić dane.
W przypadku dodawania wartości do wszystkich kolumn tabeli może nie być konieczne określanie nazw kolumn w zapytaniu SQLite. Upewnij się jednak, że kolejność wartości jest taka sama, jak kolejność kolumn w tabeli. Składnia SQLite INSERT INTO wyglądałaby następująco -
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);Przykład
Rozważ, że utworzyłeś już tabelę COMPANY w swoim testDB.db w następujący sposób -
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Teraz poniższe instrukcje utworzą sześć rekordów w tabeli COMPANY.
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );Możesz utworzyć rekord w tabeli COMPANY, używając drugiej składni w następujący sposób -
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );Wszystkie powyższe stwierdzenia utworzyłyby następujące rekordy w tabeli COMPANY. W następnym rozdziale dowiesz się, jak wyświetlić wszystkie te rekordy z tabeli.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Wypełnij jedną tabelę, używając innej tabeli
Możesz wypełnić dane w tabeli za pomocą instrukcji select w innej tabeli, pod warunkiem, że inna tabela zawiera zestaw pól, które są wymagane do wypełnienia pierwszej tabeli. Oto składnia -
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];Na razie możesz pominąć powyższe stwierdzenie. Najpierw poznajmy klauzule SELECT i WHERE, które zostaną omówione w kolejnych rozdziałach.
SQLite SELECTinstrukcja służy do pobierania danych z tabeli bazy danych SQLite, która zwraca dane w postaci tabeli wynikowej. Te tabele wyników są również nazywaneresult sets.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji SELECT języka SQLite.
SELECT column1, column2, columnN FROM table_name;Tutaj kolumna1, kolumna2 ... to pola tabeli, których wartości chcesz pobrać. Jeśli chcesz pobrać wszystkie pola dostępne w polu, możesz użyć następującej składni -
SELECT * FROM table_name;Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład pobierania i wyświetlania wszystkich tych rekordów za pomocą instrukcji SELECT. Tutaj pierwsze trzy polecenia zostały użyte do ustawienia prawidłowo sformatowanego wyjścia.
sqlite>.header on
sqlite>.mode column
sqlite> SELECT * FROM COMPANY;Na koniec otrzymasz następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Jeśli chcesz pobrać tylko wybrane pola tabeli COMPANY, użyj następującego zapytania -
sqlite> SELECT ID, NAME, SALARY FROM COMPANY;Powyższe zapytanie da następujący wynik.
ID NAME SALARY
---------- ---------- ----------
1 Paul 20000.0
2 Allen 15000.0
3 Teddy 20000.0
4 Mark 65000.0
5 David 85000.0
6 Kim 45000.0
7 James 10000.0Ustawianie szerokości kolumny wyjściowej
Czasami napotkasz problem związany z obciętym wyjściem w przypadku .mode columnco dzieje się z powodu domyślnej szerokości wyświetlanej kolumny. Co możesz zrobić, możesz ustawić szerokość wyświetlanej kolumny za pomocą.width num, num.... polecenie w następujący sposób -
sqlite>.width 10, 20, 10
sqlite>SELECT * FROM COMPANY;Powyższe .width polecenie ustawia szerokość pierwszej kolumny na 10, szerokość drugiej kolumny na 20, a szerokość trzeciej kolumny na 10. Na koniec powyższa instrukcja SELECT da następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Informacje o schemacie
Jak wszystkie dot commands są dostępne w wierszu polecenia SQLite, dlatego podczas programowania w SQLite użyjesz następującej instrukcji SELECT z sqlite_master table, aby wyświetlić listę wszystkich tabel utworzonych w bazie danych.
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';Zakładając, że masz tylko tabelę COMPANY w swoim testDB.db, da to następujący wynik.
tbl_name
----------
COMPANYMożesz wymienić pełne informacje o tabeli FIRMA w następujący sposób -
sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';Zakładając, że masz tylko tabelę COMPANY w swoim testDB.db, da to następujący wynik.
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
)Co to jest operator w SQLite?
Operator jest słowem zastrzeżonym lub znakiem używanym głównie w klauzuli WHERE instrukcji SQLite do wykonywania operacji, takich jak porównania i operacje arytmetyczne.
Operatory służą do określania warunków w instrukcji SQLite i służą jako spójniki dla wielu warunków w instrukcji.
- Operatory arytmetyczne
- Operatory porównania
- Operatory logiczne
- Operatory bitowe
Operatory arytmetyczne SQLite
Przyjmij zmienną a posiada 10 i zmienną b zawiera 20, wówczas operatory arytmetyczne SQLite zostaną użyte w następujący sposób -
Pokaż przykłady
| Operator | Opis | Przykład |
|---|---|---|
| + (Dodawanie) | Dodaje wartości po obu stronach operatora | a + b da 30 |
| - (odejmowanie) | Odejmuje operand prawej ręki od operandu lewej ręki | a - b da -10 |
| * (Mnożenie) | Mnoży wartości po obu stronach operatora | a * b da 200 |
| / (Podział) | Dzieli operand lewej ręki przez operand prawej ręki | b / a da 2 |
| % (Moduł) | Dzieli operand po lewej stronie przez operand po prawej stronie i zwraca resztę | b% a da 0 |
Operatory porównania SQLite
Przyjmij zmienną a posiada 10 i zmienną b zawiera 20, to operatory porównania SQLite zostaną użyte w następujący sposób
Pokaż przykłady
| Operator | Opis | Przykład |
|---|---|---|
| == | Sprawdza, czy wartości dwóch operandów są równe, czy nie, jeśli tak, warunek staje się prawdziwy. | (a == b) nie jest prawdą. |
| = | Sprawdza, czy wartości dwóch operandów są równe, czy nie, jeśli tak, warunek staje się prawdziwy. | (a = b) nie jest prawdą. |
| ! = | Sprawdza, czy wartości dwóch operandów są równe, czy nie, jeśli wartości nie są równe, wtedy warunek staje się prawdziwy. | (a! = b) jest prawdą. |
| <> | Sprawdza, czy wartości dwóch operandów są równe, czy nie, jeśli wartości nie są równe, wtedy warunek staje się prawdziwy. | (a <> b) jest prawdą. |
| > | Sprawdza, czy wartości lewego operandu są większe niż wartość prawego operandu, jeśli tak, warunek staje się prawdziwy. | (a> b) nie jest prawdą. |
| < | Sprawdza, czy wartości lewego operandu są mniejsze niż wartość prawego operandu. Jeśli tak, warunek staje się prawdziwy. | (a <b) jest prawdą. |
| > = | Sprawdza, czy wartość lewego operandu jest większa lub równa wartości prawego operandu. Jeśli tak, warunek staje się prawdziwy. | (a> = b) nie jest prawdą. |
| <= | Sprawdza, czy wartość lewego operandu jest mniejsza lub równa wartości prawego operandu. Jeśli tak, warunek staje się prawdziwy. | (a <= b) jest prawdą. |
| ! < | Sprawdza, czy wartość lewego operandu nie jest mniejsza niż wartość prawego operandu, jeśli tak, warunek staje się prawdziwy. | (a! <b) jest fałszem. |
| !> | Sprawdza, czy wartość lewego operandu nie jest większa niż wartość prawego operandu, jeśli tak, warunek staje się prawdziwy. | (a!> b) jest prawdą. |
Operatory logiczne SQLite
Oto lista wszystkich operatorów logicznych dostępnych w SQLite.
Pokaż przykłady
| Sr.No. | Operator i opis |
|---|---|
| 1 | AND Operator AND dopuszcza istnienie wielu warunków w klauzuli WHERE instrukcji SQL. |
| 2 | BETWEEN Operator BETWEEN służy do wyszukiwania wartości mieszczących się w zbiorze wartości, dla których określono wartość minimalną i maksymalną. |
| 3 | EXISTS Operator EXISTS służy do wyszukiwania obecności wiersza w określonej tabeli, który spełnia określone kryteria. |
| 4 | IN Operator IN służy do porównywania wartości z listą wartości literałów, które zostały określone. |
| 5 | NOT IN Negacja operatora IN, który służy do porównywania wartości z listą wartości literałów, które zostały określone. |
| 6 | LIKE Operator LIKE służy do porównywania wartości z podobnymi wartościami za pomocą operatorów symboli wieloznacznych. |
| 7 | GLOB Operator GLOB służy do porównywania wartości z podobnymi wartościami za pomocą operatorów symboli wieloznacznych. Ponadto GLOB rozróżnia wielkość liter, w przeciwieństwie do LIKE. |
| 8 | NOT Operator NOT odwraca znaczenie operatora logicznego, z którym jest używany. Na przykład. NIE ISTNIEJE, NIE MA POMIĘDZY, NIE MA W itd.This is negate operator. |
| 9 | OR Operator OR służy do łączenia wielu warunków w klauzuli WHERE instrukcji SQL. |
| 10 | IS NULL Operator NULL służy do porównywania wartości z wartością NULL. |
| 11 | IS Operator IS działa jak = |
| 12 | IS NOT Operator IS działa jak! = |
| 13 | || Dodaje dwa różne ciągi i tworzy nowy. |
| 14 | UNIQUE Operator UNIQUE przeszukuje każdy wiersz określonej tabeli pod kątem unikalności (bez duplikatów). |
Operatory bitowe SQLite
Operator bitowy działa na bitach i wykonuje operacje bit po bicie. Poniżej znajduje się tabela prawdy dla& i |.
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
Załóżmy, że A= 60; iB = 13, to w formacie binarnym będą wyglądać następująco -
A = 0011 1100
B = 0000 1101
-----------------
A&B = 0000 1100
A | B = 0011 1101
~ A = 1100 0011
W poniższej tabeli wymieniono operatory bitowe obsługiwane przez język SQLite. Przyjmij zmiennąA mieści 60 i zmienną B posiada 13, a następnie -
Pokaż przykłady
| Operator | Opis | Przykład |
|---|---|---|
| & | Operator binarny AND kopiuje trochę do wyniku, jeśli istnieje w obu operandach. | (A i B) dadzą 12, czyli 0000 1100 |
| | | Operator binarny OR kopiuje bit, jeśli istnieje w którymkolwiek z operandów. | (A | B) da 61, czyli 0011 1101 |
| ~ | Operator dopełniacza binarnego jest jednoargumentowy i powoduje „odwracanie” bitów. | (~ A) da -61, czyli 1100 0011 w postaci uzupełnienia do 2 ze względu na liczbę binarną ze znakiem |
| << | Binarny operator przesunięcia w lewo. Wartość lewych operandów jest przesuwana w lewo o liczbę bitów określoną przez prawy operand. | << 2 da 240, czyli 1111 0000 |
| >> | Binarny operator przesunięcia w prawo. Wartość lewego operandu jest przesuwana w prawo o liczbę bitów określoną przez prawy operand. | >> 2 da 15, czyli 0000 1111 |
Wyrażenie to kombinacja jednej lub więcej wartości, operatorów i funkcji SQL, których wynikiem jest wartość.
Wyrażenia SQL są podobne do formuł i są napisane w języku zapytań. Możesz również użyć do wysłania zapytania do bazy danych o określony zestaw danych.
Składnia
Rozważmy podstawową składnię instrukcji SELECT w następujący sposób -
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];Poniżej przedstawiono różne typy wyrażeń SQLite.
SQLite - Wyrażenia logiczne
SQLite Boolean Expressions pobierają dane na podstawie dopasowania pojedynczej wartości. Poniżej znajduje się składnia -
SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajdują się proste przykłady pokazujące użycie wyrażeń logicznych SQLite -
sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 James 24 Houston 10000.0SQLite - wyrażenie numeryczne
Te wyrażenia służą do wykonywania wszelkich operacji matematycznych w dowolnym zapytaniu. Poniżej znajduje się składnia -
SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;Tutaj numerical_expression jest używane do wyrażenia matematycznego lub dowolnej formuły. Poniżej znajduje się prosty przykład pokazujący użycie wyrażeń numerycznych SQLite.
sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21Istnieje kilka wbudowanych funkcji, takich jak avg(), sum(), count(), itp., aby wykonać to, co jest znane jako aggregate data calculations do tabeli lub określonej kolumny tabeli.
sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS = 7SQLite - wyrażenia dat
Date Expressions zwraca bieżące systemowe wartości daty i godziny. Te wyrażenia są używane w różnych operacjach na danych.
sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35SQLite WHERE Klauzula służy do określenia warunku podczas pobierania danych z jednej tabeli lub wielu tabel.
Jeśli podany warunek jest spełniony, oznacza prawdę, to zwraca określoną wartość z tabeli. Będziesz musiał użyć klauzuli WHERE do filtrowania rekordów i pobierania tylko niezbędnych rekordów.
Klauzula WHERE jest używana nie tylko w instrukcji SELECT, ale jest również używana w instrukcjach UPDATE, DELETE itp., Które zostaną omówione w kolejnych rozdziałach.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji SELECT języka SQLite z klauzulą WHERE.
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]Przykład
Możesz określić warunek za pomocą porównania lub operatorów logicznych, takich jak>, <, =, LIKE, NOT itp. Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Oto proste przykłady pokazujące użycie operatorów logicznych SQLite. Następująca instrukcja SELECT wymienia wszystkie rekordy, w których WIEK jest większy lub równy 25AND wynagrodzenie jest większe lub równe 65000,00.
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których WIEK jest większy lub równy 25 OR wynagrodzenie jest większe lub równe 65000,00.
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których AGE nie jest równe NULL, co oznacza wszystkie rekordy, ponieważ żaden z rekordów nie ma wartości AGE równej NULL.
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których NAZWA zaczyna się od „Ki”, nie ma znaczenia, co następuje po „Ki”.
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których NAZWA zaczyna się od „Ki”, nie ma znaczenia, co następuje po „Ki”.
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których wartość WIEK wynosi 25 lub 27.
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Poniższa instrukcja SELECT wymienia wszystkie rekordy, w których WIEK nie wynosi ani 25 ani 27.
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których wartość WIEK mieści się między 25 a 27 rokiem.
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Następująca instrukcja SELECT wykorzystuje zapytanie podrzędne SQL, w którym zapytanie podrzędne znajduje wszystkie rekordy z polem AGE mającym SALARY> 65000 i późniejszą klauzulę WHERE wraz z operatorem EXISTS w celu wyświetlenia wszystkich rekordów, w których istnieje AGE z zapytania zewnętrznego w wyniku zwróconym przez zapytanie podrzędne -
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24Następująca instrukcja SELECT wykorzystuje zapytanie podrzędne SQL, w którym zapytanie podrzędne znajduje wszystkie rekordy z polem AGE mającym SALARY> 65000 i późniejszą klauzulę WHERE wraz z operatorem> w celu wyświetlenia wszystkich rekordów, w których AGE z zapytania zewnętrznego jest większy niż wiek w wyniku zwróconym przez zapytanie podrzędne.
sqlite> SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0SQLite AND & ORoperatory są używane do kompilowania wielu warunków w celu zawężenia wybranych danych w instrukcji SQLite. Te dwa operatory są nazywaneconjunctive operators.
Operatory te umożliwiają dokonywanie wielu porównań z różnymi operatorami w tej samej instrukcji SQLite.
Operator AND
Plik ANDOperator zezwala na istnienie wielu warunków w klauzuli WHERE instrukcji SQLite. Używając operatora AND, pełny warunek zostanie przyjęty jako prawdziwy, gdy wszystkie warunki są spełnione. Na przykład [warunek1] AND [warunek2] będzie prawdziwy tylko wtedy, gdy zarówno warunek1, jak i warunek2 są prawdziwe.
Składnia
Poniżej znajduje się podstawowa składnia operatora AND z klauzulą WHERE.
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];Możesz łączyć Nliczba warunków korzystających z operatora AND. Aby instrukcja SQLite mogła wykonać akcję, niezależnie od tego, czy jest to transakcja, czy zapytanie, wszystkie warunki oddzielone znakiem AND muszą mieć wartość TRUE.
Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których WIEK jest większy lub równy 25 AND wynagrodzenie jest większe lub równe 65000,00.
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Operator OR
Operator OR jest również używany do łączenia wielu warunków w klauzuli WHERE instrukcji SQLite. Podczas korzystania z operatora OR, pełny warunek zostanie przyjęty jako prawdziwy, jeśli przynajmniej jeden z warunków jest spełniony. Na przykład [warunek1] LUB [warunek2] będzie prawdziwy, jeśli spełniony jest warunek1 lub warunek2.
Składnia
Poniżej znajduje się podstawowa składnia operatora OR z klauzulą WHERE.
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]Możesz łączyć Nliczba warunków korzystających z operatora OR. Aby instrukcja SQLite mogła wykonać akcję, niezależnie od tego, czy jest to transakcja, czy zapytanie, tylko JEDEN z warunków oddzielonych znakiem LUB musi być PRAWDA.
Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Następująca instrukcja SELECT wymienia wszystkie rekordy, w których WIEK jest większy lub równy 25 OR wynagrodzenie jest większe lub równe 65000,00.
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0SQLite UPDATEZapytanie służy do modyfikowania istniejących rekordów w tabeli. Możesz użyć klauzuli WHERE z zapytaniem UPDATE, aby zaktualizować wybrane wiersze, w przeciwnym razie wszystkie wiersze zostaną zaktualizowane.
Składnia
Poniżej znajduje się podstawowa składnia zapytania UPDATE z klauzulą WHERE.
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];Możesz łączyć N liczba warunków korzystających z operatorów AND lub OR.
Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, który zaktualizuje ADRES dla klienta o identyfikatorze 6.
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;Teraz tabela COMPANY będzie miała następujące rekordy.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 Texas 45000.0
7 James 24 Houston 10000.0Jeśli chcesz zmodyfikować wszystkie wartości kolumn ADDRESS i SALARY w tabeli COMPANY, nie musisz używać klauzuli WHERE, a zapytanie UPDATE będzie wyglądało następująco -
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;Teraz tabela COMPANY będzie miała następujące rekordy -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 Texas 20000.0
2 Allen 25 Texas 20000.0
3 Teddy 23 Texas 20000.0
4 Mark 25 Texas 20000.0
5 David 27 Texas 20000.0
6 Kim 22 Texas 20000.0
7 James 24 Texas 20000.0SQLite DELETEZapytanie służy do usuwania istniejących rekordów z tabeli. Możesz użyć klauzuli WHERE z zapytaniem DELETE, aby usunąć wybrane wiersze, w przeciwnym razie wszystkie rekordy zostaną usunięte.
Składnia
Poniżej znajduje się podstawowa składnia zapytania DELETE z klauzulą WHERE.
DELETE FROM table_name
WHERE [condition];Możesz łączyć N liczba warunków korzystających z operatorów AND lub OR.
Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, który spowoduje USUNIĘCIE klienta o identyfikatorze 7.
sqlite> DELETE FROM COMPANY WHERE ID = 7;Teraz tabela COMPANY będzie miała następujące rekordy.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0Jeśli chcesz usunąć wszystkie rekordy z tabeli COMPANY, nie musisz używać klauzuli WHERE z zapytaniem DELETE, które będzie wyglądało następująco -
sqlite> DELETE FROM COMPANY;Teraz tabela COMPANY nie ma żadnego rekordu, ponieważ wszystkie rekordy zostały usunięte przez instrukcję DELETE.
SQLite LIKEoperator służy do dopasowywania wartości tekstowych do wzorca przy użyciu symboli wieloznacznych. Jeśli wyrażenie wyszukiwania można dopasować do wyrażenia wzorcowego, operator LIKE zwróci wartość true, czyli 1. Istnieją dwa symbole wieloznaczne używane w połączeniu z operatorem LIKE -
- Znak procentu (%)
- Podkreślenie (_)
Znak procentu oznacza zero, jedną lub wiele liczb lub znaków. Podkreślenie reprezentuje pojedynczą liczbę lub znak. Symbole te mogą być używane w kombinacjach.
Składnia
Poniżej znajduje się podstawowa składnia% i _.
SELECT FROM table_name
WHERE column LIKE 'XXXX%'
or
SELECT FROM table_name
WHERE column LIKE '%XXXX%'
or
SELECT FROM table_name
WHERE column LIKE 'XXXX_'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX'
or
SELECT FROM table_name
WHERE column LIKE '_XXXX_'Możesz łączyć Nliczba warunków korzystających z operatorów AND lub OR. Tutaj XXXX może być dowolną wartością liczbową lub ciągiem znaków.
Przykład
Poniższa tabela zawiera szereg przykładów pokazujących WHERE część mającą różne klauzule LIKE z operatorami „%” i „_”.
| Sr.No. | Oświadczenie i opis |
|---|---|
| 1 | WHERE SALARY LIKE '200%' Znajduje wartości zaczynające się od 200 |
| 2 | WHERE SALARY LIKE '%200%' Znajduje wartości, które mają 200 na dowolnej pozycji |
| 3 | WHERE SALARY LIKE '_00%' Znajduje wszystkie wartości, które mają 00 na drugiej i trzeciej pozycji |
| 4 | WHERE SALARY LIKE '2_%_%' Znajduje wszystkie wartości, które zaczynają się od 2 i mają co najmniej 3 znaki |
| 5 | WHERE SALARY LIKE '%2' Znajduje wszystkie wartości kończące się na 2 |
| 6 | WHERE SALARY LIKE '_2%3' Znajduje wszystkie wartości, które mają 2 na drugiej pozycji i kończą się 3 |
| 7 | WHERE SALARY LIKE '2___3' Znajduje wszystkie wartości w pięciocyfrowej liczbie rozpoczynającej się od 2 i kończącej na 3 |
Weźmy prawdziwy przykład, rozważmy tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, który wyświetli wszystkie rekordy z tabeli COMPANY, w której WIEK zaczyna się od 2.
sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, w którym zostaną wyświetlone wszystkie rekordy z tabeli COMPANY, w której ADRES będzie zawierał łącznik (-) w tekście.
sqlite> SELECT * FROM COMPANY WHERE ADDRESS LIKE '%-%';Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0SQLite GLOBoperator jest używany do dopasowywania tylko wartości tekstowych do wzorca przy użyciu symboli wieloznacznych. Jeśli wyrażenie wyszukiwania można dopasować do wyrażenia wzorcowego, operator GLOB zwróci wartość true, czyli 1. W przeciwieństwie do operatora LIKE, GLOB rozróżnia wielkość liter i jest zgodny ze składnią systemu UNIX w celu określenia następujących symboli wieloznacznych.
- Znak gwiazdki (*)
- Znak zapytania (?)
Znak gwiazdki (*) oznacza zero lub wiele liczb lub znaków. Znak zapytania (?) Reprezentuje pojedynczą liczbę lub znak.
Składnia
Poniżej znajduje się podstawowa składnia * i ?.
SELECT FROM table_name
WHERE column GLOB 'XXXX*'
or
SELECT FROM table_name
WHERE column GLOB '*XXXX*'
or
SELECT FROM table_name
WHERE column GLOB 'XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX'
or
SELECT FROM table_name
WHERE column GLOB '?XXXX?'
or
SELECT FROM table_name
WHERE column GLOB '????'Możesz łączyć Nliczba warunków korzystających z operatorów AND lub OR. Tutaj XXXX może być dowolną wartością liczbową lub ciągiem znaków.
Przykład
Poniższa tabela zawiera szereg przykładów pokazujących, gdzie część ma inną klauzulę LIKE z „*” i „?” operatorzy.
| Sr.No. | Oświadczenie i opis |
|---|---|
| 1 | WHERE SALARY GLOB '200*' Znajduje wartości zaczynające się od 200 |
| 2 | WHERE SALARY GLOB '*200*' Znajduje wartości, które mają 200 na dowolnej pozycji |
| 3 | WHERE SALARY GLOB '?00*' Znajduje wszystkie wartości, które mają 00 na drugiej i trzeciej pozycji |
| 4 | WHERE SALARY GLOB '2??' Znajduje wszystkie wartości, które zaczynają się od 2 i mają co najmniej 3 znaki |
| 5 | WHERE SALARY GLOB '*2' Znajduje wszystkie wartości kończące się na 2 |
| 6 | WHERE SALARY GLOB '?2*3' Znajduje wszystkie wartości, które mają 2 na drugiej pozycji i kończą się 3 |
| 7 | WHERE SALARY GLOB '2???3' Znajduje wszystkie wartości w liczbie pięciocyfrowej zaczynającej się od 2 i kończącej na 3 |
Weźmy prawdziwy przykład, rozważmy tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, w którym zostaną wyświetlone wszystkie rekordy z tabeli COMPANY, w której WIEK zaczyna się od 2.
sqlite> SELECT * FROM COMPANY WHERE AGE GLOB '2*';Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, w którym zostaną wyświetlone wszystkie rekordy z tabeli FIRMA, w której ADRES będzie zawierał łącznik (-) w tekście -
sqlite> SELECT * FROM COMPANY WHERE ADDRESS GLOB '*-*';Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0SQLite LIMIT klauzula służy do ograniczenia ilości danych zwracanych przez instrukcję SELECT.
Składnia
Poniżej przedstawiono podstawową składnię instrukcji SELECT z klauzulą LIMIT.
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]Poniżej przedstawiono składnię klauzuli LIMIT, gdy jest ona używana razem z klauzulą OFFSET.
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]Silnik SQLite zwróci wiersze zaczynające się od następnego wiersza do danego PRZESUNIĘCIA, jak pokazano poniżej w ostatnim przykładzie.
Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, który ogranicza wiersz w tabeli w zależności od liczby wierszy, które chcesz pobrać z tabeli.
sqlite> SELECT * FROM COMPANY LIMIT 6;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0Jednak w niektórych sytuacjach może być konieczne pobranie zestawu rekordów z określonego przesunięcia. Poniżej przedstawiono przykład, który odbiera 3 rejestrów wychodząc z 3 -ciej pozycji.
sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0SQLite ORDER BY Klauzula służy do sortowania danych w kolejności rosnącej lub malejącej, na podstawie co najmniej jednej kolumny.
Składnia
Poniżej znajduje się podstawowa składnia klauzuli ORDER BY.
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];W klauzuli ORDER BY można użyć więcej niż jednej kolumny. Upewnij się, że kolumna, której używasz do sortowania, powinna być dostępna na liście kolumn.
Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład, który posortuje wynik w porządku malejącym według PŁATNOŚCI.
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Poniżej znajduje się przykład, który posortuje wynik w porządku malejącym według NAZWY i WYNAGRODZENIA.
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
5 David 27 Texas 85000.0
7 James 24 Houston 10000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0Poniżej znajduje się przykład, który posortuje wynik w porządku malejącym według NAZWY.
sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
3 Teddy 23 Norway 20000.0
1 Paul 32 California 20000.0
4 Mark 25 Rich-Mond 65000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
5 David 27 Texas 85000.0
2 Allen 25 Texas 15000.0SQLite GROUP BY Klauzula jest używana we współpracy z instrukcją SELECT w celu uporządkowania identycznych danych w grupy.
Klauzula GROUP BY następuje po klauzuli WHERE w instrukcji SELECT i poprzedza klauzulę ORDER BY.
Składnia
Poniżej znajduje się podstawowa składnia klauzuli GROUP BY. Klauzula GROUP BY musi następować po warunkach w klauzuli WHERE i musi poprzedzać klauzulę ORDER BY, jeśli jest używana.
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnNW klauzuli GROUP BY można użyć więcej niż jednej kolumny. Upewnij się, że kolumna, której używasz do grupowania, powinna być dostępna na liście kolumn.
Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Jeśli chcesz poznać całkowitą wysokość wynagrodzenia każdego klienta, zapytanie GROUP BY będzie wyglądało następująco -
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;To da następujący wynik -
NAME SUM(SALARY)
---------- -----------
Allen 15000.0
David 85000.0
James 10000.0
Kim 45000.0
Mark 65000.0
Paul 20000.0
Teddy 20000.0Teraz stwórzmy jeszcze trzy rekordy w tabeli COMPANY, używając następujących instrukcji INSERT.
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );Teraz nasza tabela zawiera następujące rekordy ze zduplikowanymi nazwami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0Ponownie użyjmy tej samej instrukcji, aby pogrupować wszystkie rekordy przy użyciu kolumny NAZWA w następujący sposób -
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;Spowoduje to następujący wynik.
NAME SUM(SALARY)
---------- -----------
Allen 15000
David 85000
James 20000
Kim 45000
Mark 65000
Paul 40000
Teddy 20000Użyjmy klauzuli ORDER BY wraz z klauzulą GROUP BY w następujący sposób -
sqlite> SELECT NAME, SUM(SALARY)
FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;Spowoduje to następujący wynik.
NAME SUM(SALARY)
---------- -----------
Teddy 20000
Paul 40000
Mark 65000
Kim 45000
James 20000
David 85000
Allen 15000Klauzula HAVING umożliwia określenie warunków, które filtrują wyniki grup, które pojawiają się w wynikach końcowych.
Klauzula WHERE umieszcza warunki w wybranych kolumnach, podczas gdy klauzula HAVING umieszcza warunki na grupach utworzonych klauzulą GROUP BY.
Składnia
Poniżej znajduje się pozycja klauzuli HAVING w zapytaniu SELECT.
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BYKlauzula HAVING musi występować po klauzuli GROUP BY w zapytaniu i musi również poprzedzać klauzulę ORDER BY, jeśli jest używana. Poniżej znajduje się składnia instrukcji SELECT, w tym klauzula HAVING.
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0Poniżej znajduje się przykład, w którym zostanie wyświetlony rekord, dla którego liczba nazwisk jest mniejsza niż 2.
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000
5 David 27 Texas 85000
6 Kim 22 South-Hall 45000
4 Mark 25 Rich-Mond 65000
3 Teddy 23 Norway 20000Poniżej znajduje się przykład, w którym zostanie wyświetlony rekord, dla którego liczba nazw jest większa niż 2.
sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
10 James 45 Texas 5000SQLite DISTINCT słowo kluczowe jest używane w połączeniu z instrukcją SELECT, aby wyeliminować wszystkie zduplikowane rekordy i pobrać tylko unikatowe rekordy.
Może wystąpić sytuacja, w której masz wiele zduplikowanych rekordów w tabeli. Podczas pobierania takich rekordów bardziej sensowne jest pobieranie tylko unikalnych rekordów, zamiast pobierania zduplikowanych rekordów.
Składnia
Poniżej przedstawiono podstawową składnię słowa kluczowego DISTINCT w celu wyeliminowania zduplikowanych rekordów.
SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
8 Paul 24 Houston 20000.0
9 James 44 Norway 5000.0
10 James 45 Texas 5000.0Najpierw zobaczmy, jak poniższe zapytanie SELECT zwraca zduplikowane rekordy wynagrodzenia.
sqlite> SELECT name FROM COMPANY;Spowoduje to następujący wynik.
NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
James
JamesTeraz użyjmy DISTINCT słowo kluczowe z powyższym zapytaniem SELECT i zobacz wynik.
sqlite> SELECT DISTINCT name FROM COMPANY;Daje to następujący wynik, przy braku zduplikowanych wpisów.
NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
JamesSQLite PRAGMApolecenie to specjalne polecenie używane do kontrolowania różnych zmiennych środowiskowych i flag stanu w środowisku SQLite. Wartość PRAGMA można odczytać, a także ustawić w zależności od wymagań.
Składnia
Aby zapytać o bieżącą wartość PRAGMA, wystarczy podać nazwę pragmy.
PRAGMA pragma_name;Aby ustawić nową wartość dla PRAGMA, użyj następującej składni.
PRAGMA pragma_name = value;Ustawionym trybem może być nazwa lub odpowiednik liczby całkowitej, ale zwracana wartość zawsze będzie liczbą całkowitą.
auto_vacuum Pragma
Plik auto_vacuumpragma pobiera lub ustawia tryb automatycznego odkurzania. Poniżej znajduje się prosta składnia.
PRAGMA [database.]auto_vacuum;
PRAGMA [database.]auto_vacuum = mode;Gdzie mode może być dowolną z następujących -
| Sr.No. | Wartość i opis firmy Pragma |
|---|---|
| 1 | 0 or NONE Automatyczne odkurzanie jest wyłączone. Jest to tryb domyślny, co oznacza, że rozmiar pliku bazy danych nigdy się nie zmniejszy, chyba że zostanie ręcznie odkurzany za pomocą polecenia VACUUM. |
| 2 | 1 or FULL Automatyczne odkurzanie jest włączone i w pełni automatyczne, co umożliwia kurczenie się pliku bazy danych w miarę usuwania danych z bazy danych. |
| 3 | 2 or INCREMENTAL Automatyczne odkurzanie jest włączone, ale należy je aktywować ręcznie. W tym trybie dane referencyjne są zachowane, ale wolne strony są po prostu umieszczane na liście bezpłatnych. Te strony można odzyskać za pomocą rozszerzeniaincremental_vacuum pragma w dowolnym momencie. |
cache_size Pragma
Plik cache_sizepragma może pobrać lub tymczasowo ustawić maksymalny rozmiar pamięci podręcznej stron w pamięci. Poniżej znajduje się prosta składnia.
PRAGMA [database.]cache_size;
PRAGMA [database.]cache_size = pages;Plik pageswartość reprezentuje liczbę stron w pamięci podręcznej. Wbudowana pamięć podręczna stron ma domyślny rozmiar 2000 stron i minimalny rozmiar 10 stron.
case_sensitive_like Pragma
Plik case_sensitive_likepragma steruje rozróżnianiem wielkości liter wbudowanego wyrażenia LIKE. Domyślnie ta pragma jest fałszywa, co oznacza, że wbudowany operator LIKE ignoruje wielkość liter. Poniżej znajduje się prosta składnia.
PRAGMA case_sensitive_like = [true|false];Nie ma możliwości sprawdzenia aktualnego stanu tej pragmy.
count_changes Pragma
count_changespragma pobiera lub ustawia wartość zwracaną przez instrukcje manipulacji danymi, takie jak INSERT, UPDATE i DELETE. Poniżej znajduje się prosta składnia.
PRAGMA count_changes;
PRAGMA count_changes = [true|false];Domyślnie ta pragma jest fałszywa i te instrukcje nic nie zwracają. Jeśli ma wartość true, każda ze wspomnianych instrukcji zwróci jednokolumnową, jednowierszową tabelę składającą się z jednej wartości całkowitej wskazującej wiersze, na które operacja ma wpływ.
database_list Pragma
Plik database_listpragma zostanie użyta do wylistowania wszystkich dołączonych baz danych. Poniżej znajduje się prosta składnia.
PRAGMA database_list;Ta pragma zwróci tabelę z trzema kolumnami z jednym wierszem na otwartą lub dołączoną bazę danych, podającą numer kolejny bazy danych, jej nazwę i skojarzony plik.
kodowanie Pragma
Plik encodingpragma kontroluje sposób kodowania i przechowywania łańcuchów w pliku bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA encoding;
PRAGMA encoding = format;Wartość formatu może wynosić UTF-8, UTF-16lelub UTF-16be.
freelist_count Pragma
Plik freelist_countpragma zwraca pojedynczą liczbę całkowitą wskazującą, ile stron bazy danych jest obecnie oznaczonych jako wolne i dostępne. Poniżej znajduje się prosta składnia.
PRAGMA [database.]freelist_count;Wartość formatu może wynosić UTF-8, UTF-16lelub UTF-16be.
index_info Pragma
Plik index_infopragma zwraca informacje o indeksie bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA [database.]index_info( index_name );Zestaw wyników będzie zawierał po jednym wierszu dla każdej kolumny zawartej w indeksie, podając sekwencję kolumn, indeks kolumn z zawartością tabeli i nazwę kolumny.
index_list Pragma
index_listpragma zawiera listę wszystkich indeksów skojarzonych z tabelą. Poniżej znajduje się prosta składnia.
PRAGMA [database.]index_list( table_name );Zestaw wyników będzie zawierał jeden wiersz dla każdego indeksu, podając sekwencję indeksu, nazwę indeksu i flagę wskazującą, czy indeks jest unikalny, czy nie.
Journal_mode Pragma
Plik journal_modepragma pobiera lub ustawia tryb dziennika, który kontroluje sposób przechowywania i przetwarzania pliku dziennika. Poniżej znajduje się prosta składnia.
PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;Istnieje pięć obsługiwanych trybów dziennika, które przedstawiono w poniższej tabeli.
| Sr.No. | Wartość i opis firmy Pragma |
|---|---|
| 1 | DELETE To jest tryb domyślny. W tym miejscu po zakończeniu transakcji plik dziennika jest usuwany. |
| 2 | TRUNCATE Plik dziennika jest obcinany do długości zero bajtów. |
| 3 | PERSIST Plik dziennika pozostaje na miejscu, ale nagłówek jest nadpisywany, aby wskazać, że dziennik nie jest już ważny. |
| 4 | MEMORY Rekord dziennika jest przechowywany w pamięci, a nie na dysku. |
| 5 | OFF Nie jest prowadzony żaden zapis dziennika. |
max_page_count Pragma
Plik max_page_countpragma pobiera lub ustawia maksymalną dozwoloną liczbę stron dla bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA [database.]max_page_count;
PRAGMA [database.]max_page_count = max_page;Wartość domyślna to 1 073 741 823, co odpowiada jednej gigabajtowej stronie, co oznacza, że jeśli domyślny rozmiar strony wynosi 1 KB, bazy danych mogą urosnąć do jednego terabajta.
page_count Pragma
Plik page_countpragma zwraca bieżącą liczbę stron w bazie danych. Poniżej znajduje się prosta składnia -
PRAGMA [database.]page_count;Rozmiar pliku bazy danych powinien wynosić liczba_strony * rozmiar_strony.
page_size Pragma
Plik page_sizepragma pobiera lub ustawia rozmiar stron bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA [database.]page_size;
PRAGMA [database.]page_size = bytes;Domyślnie dozwolone rozmiary to 512, 1024, 2048, 4096, 8192, 16384 i 32768 bajtów. Jedynym sposobem zmiany rozmiaru strony w istniejącej bazie danych jest ustawienie rozmiaru strony, a następnie natychmiastowe ODKURZANIE bazy danych.
parser_trace Pragma
Plik parser_tracepragma kontroluje drukowanie stanu debugowania podczas analizowania poleceń SQL. Poniżej znajduje się prosta składnia.
PRAGMA parser_trace = [true|false];Domyślnie jest ustawiony na false, ale po włączeniu przez ustawienie go na true, parser SQL wydrukuje swój stan podczas analizowania poleceń SQL.
recursive_triggers Pragma
Plik recursive_triggerspragma pobiera lub ustawia funkcję rekurencyjnego wyzwalacza. Jeśli rekurencyjne wyzwalacze nie są włączone, akcja wyzwalacza nie uruchomi innego wyzwalacza. Poniżej znajduje się prosta składnia.
PRAGMA recursive_triggers;
PRAGMA recursive_triggers = [true|false];schema_version Pragma
Plik schema_versionpragma pobiera lub ustawia wartość wersji schematu, która jest przechowywana w nagłówku bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA [database.]schema_version;
PRAGMA [database.]schema_version = number;Jest to 32-bitowa liczba całkowita ze znakiem, która śledzi zmiany schematu. Za każdym razem, gdy wykonywane jest polecenie zmieniające schemat (na przykład CREATE ... lub DROP ...), wartość ta jest zwiększana.
secure_delete Pragma
Plik secure_deletepragma służy do kontrolowania sposobu usuwania treści z bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA secure_delete;
PRAGMA secure_delete = [true|false];
PRAGMA database.secure_delete;
PRAGMA database.secure_delete = [true|false];Domyślna wartość flagi bezpiecznego usuwania jest zwykle wyłączona, ale można to zmienić za pomocą opcji kompilacji SQLITE_SECURE_DELETE.
sql_trace Pragma
Plik sql_tracepragma służy do zrzucania wyników śledzenia SQL na ekran. Poniżej znajduje się prosta składnia.
PRAGMA sql_trace;
PRAGMA sql_trace = [true|false];SQLite musi być skompilowany z dyrektywą SQLITE_DEBUG, aby uwzględnić tę pragmę.
synchroniczna Pragma
Plik synchronouspragma pobiera lub ustawia bieżący tryb synchronizacji dysku, który kontroluje, jak agresywnie SQLite będzie zapisywał dane do fizycznej pamięci masowej. Poniżej znajduje się prosta składnia.
PRAGMA [database.]synchronous;
PRAGMA [database.]synchronous = mode;SQLite obsługuje następujące tryby synchronizacji wymienione w tabeli.
| Sr.No. | Wartość i opis firmy Pragma |
|---|---|
| 1 | 0 or OFF Brak synchronizacji |
| 2 | 1 or NORMAL Synchronizuj po każdej sekwencji krytycznych operacji na dysku |
| 3 | 2 or FULL Synchronizuj po każdej operacji dysku krytycznego |
temp_store Pragma
Plik temp_storepragma pobiera lub ustawia tryb przechowywania używany przez tymczasowe pliki bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA temp_store;
PRAGMA temp_store = mode;SQLite obsługuje następujące tryby przechowywania.
| Sr.No. | Wartość i opis firmy Pragma |
|---|---|
| 1 | 0 or DEFAULT Użyj wartości domyślnej czasu kompilacji. Zwykle PLIK. |
| 2 | 1 or FILE Użyj magazynu opartego na plikach. |
| 3 | 2 or MEMORY Użyj pamięci masowej. |
temp_store_directory Pragma
Plik temp_store_directorypragma pobiera lub ustawia lokalizację używaną dla tymczasowych plików bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA temp_store_directory;
PRAGMA temp_store_directory = 'directory_path';wersja_użytkownika Pragma
Plik user_versionpragma pobiera lub ustawia wartość wersji zdefiniowaną przez użytkownika, która jest przechowywana w nagłówku bazy danych. Poniżej znajduje się prosta składnia.
PRAGMA [database.]user_version;
PRAGMA [database.]user_version = number;Jest to 32-bitowa liczba całkowita ze znakiem, którą może ustawić programista w celu śledzenia wersji.
writable_schema Pragma
Plik writable_schemapragma dostaje lub ustawia możliwość modyfikowania tabel systemowych. Poniżej znajduje się prosta składnia.
PRAGMA writable_schema;
PRAGMA writable_schema = [true|false];Jeśli ta pragma jest ustawiona, można tworzyć i modyfikować tabele zaczynające się od sqlite_, w tym tabela sqlite_master. Zachowaj ostrożność podczas korzystania z pragmy, ponieważ może to doprowadzić do całkowitego uszkodzenia bazy danych.
Ograniczenia to reguły wymuszane na kolumnach danych w tabeli. Służą one do ograniczenia typu danych, które mogą zostać umieszczone w tabeli. Zapewnia to dokładność i wiarygodność danych w bazie danych.
Ograniczenia mogą dotyczyć poziomu kolumny lub tabeli. Ograniczenia na poziomie kolumny są stosowane tylko do jednej kolumny, podczas gdy ograniczenia na poziomie tabeli są stosowane do całej tabeli.
Poniżej znajdują się powszechnie używane ograniczenia dostępne w SQLite.
NOT NULL Constraint - Zapewnia, że kolumna nie może mieć wartości NULL.
DEFAULT Constraint - Dostarcza domyślną wartość dla kolumny, gdy żadna nie jest określona.
UNIQUE Constraint - Zapewnia, że wszystkie wartości w kolumnie są różne.
PRIMARY Key - Unikalnie identyfikuje każdy wiersz / rekord w tabeli bazy danych.
CHECK Constraint - Zapewnia, że wszystkie wartości w kolumnie spełniają określone warunki.
NOT NULL Constraint
Domyślnie kolumna może zawierać wartości NULL. Jeśli nie chcesz, aby kolumna miała wartość NULL, musisz zdefiniować takie ograniczenie w tej kolumnie, określając, że NULL nie jest teraz dozwolona dla tej kolumny.
NULL to nie to samo, co brak danych, reprezentuje raczej nieznane dane.
Przykład
Na przykład następująca instrukcja SQLite tworzy nową tabelę o nazwie COMPANY i dodaje pięć kolumn, z których trzy, ID i NAME oraz AGE, określają, że należy nie akceptować wartości NULL.
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);DEFAULT Constraint
Ograniczenie DEFAULT zapewnia wartość domyślną dla kolumny, gdy instrukcja INSERT INTO nie zawiera określonej wartości.
Przykład
Na przykład następująca instrukcja SQLite tworzy nową tabelę o nazwie COMPANY i dodaje pięć kolumn. Tutaj kolumna SALARY jest domyślnie ustawiona na 5000,00, więc w przypadku, gdy instrukcja INSERT INTO nie podaje wartości dla tej kolumny, to domyślnie ta kolumna będzie ustawiona na 5000,00.
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);UNIQUE Constraint
Ograniczenie UNIQUE zapobiega sytuacji, w której dwa rekordy mają identyczne wartości w określonej kolumnie. Na przykład w tabeli FIRMA możesz chcieć, aby dwie lub więcej osób nie miało identycznego wieku.
Przykład
Na przykład następująca instrukcja SQLite tworzy nową tabelę o nazwie COMPANY i dodaje pięć kolumn. Tutaj kolumna AGE jest ustawiona na UNIQUE, więc nie możesz mieć dwóch rekordów z tym samym wiekiem -
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);PRIMARY KEY Constraint
Ograniczenie PRIMARY KEY jednoznacznie identyfikuje każdy rekord w tabeli bazy danych. W tabeli może być więcej kolumn typu UNIQUE, ale tylko jeden klucz podstawowy. Klucze podstawowe są ważne podczas projektowania tabel bazy danych. Klucze podstawowe to unikalne identyfikatory.
Używamy ich do odwoływania się do wierszy tabeli. Podczas tworzenia relacji między tabelami klucze podstawowe stają się kluczami obcymi w innych tabelach. Ze względu na „wieloletni nadzór nad kodowaniem” klucze podstawowe mogą mieć wartość NULL w SQLite. Nie dotyczy to innych baz danych.
Klucz podstawowy to pole w tabeli, które jednoznacznie identyfikuje każdy wiersz / rekord w tabeli bazy danych. Klucze podstawowe muszą zawierać unikalne wartości. Kolumna klucza podstawowego nie może mieć wartości NULL.
Tabela może mieć tylko jeden klucz podstawowy, który może składać się z jednego lub wielu pól. Gdy wiele pól jest używanych jako klucz podstawowy, nazywa się jecomposite key.
Jeśli tabela ma klucz podstawowy zdefiniowany w jakimkolwiek polu (polach), nie można mieć dwóch rekordów o tej samej wartości w tych polach.
Przykład
Widzieliście już różne przykłady powyżej, w których utworzyliśmy tabelę COMPANY z identyfikatorem jako kluczem podstawowym.
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);SPRAWDŹ ograniczenie
CHECK Constraint umożliwia warunkowi sprawdzenie wartości wprowadzanej do rekordu. Jeśli warunek zostanie oceniony jako fałszywy, rekord narusza ograniczenie i nie jest wprowadzany do tabeli.
Przykład
Na przykład następujący SQLite tworzy nową tabelę o nazwie COMPANY i dodaje pięć kolumn. Tutaj dodajemy kolumnę CHECK with SALARY, abyś nie mógł mieć żadnej SALARY Zero.
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);Porzucanie ograniczenia
SQLite obsługuje ograniczony podzbiór ALTER TABLE. Polecenie ALTER TABLE w SQLite umożliwia użytkownikowi zmianę nazwy tabeli lub dodanie nowej kolumny do istniejącej tabeli. Nie można zmienić nazwy kolumny, usunąć kolumny ani dodać lub usunąć ograniczeń z tabeli.
SQLite JoinsKlauzula służy do łączenia rekordów z dwóch lub więcej tabel w bazie danych. JOIN to sposób łączenia pól z dwóch tabel przy użyciu wspólnych wartości.
SQL definiuje trzy główne typy sprzężeń -
- CROSS JOIN
- WEWNĘTRZNE DOŁĄCZENIE
- DOŁĄCZ ZEWNĘTRZNY
Zanim przejdziemy dalej, rozważmy dwie tabele FIRMA i DZIAŁ. Widzieliśmy już instrukcje INSERT wypełniające tabelę COMPANY. Przyjmijmy więc listę rekordów dostępnych w tabeli FIRMA -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Kolejna tabela to DEPARTMENT z następującą definicją -
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);Oto lista instrukcji INSERT do wypełnienia tabeli DEPARTMENT -
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );Na koniec mamy następującą listę rekordów dostępnych w tabeli DEPARTMENT -
ID DEPT EMP_ID
---------- ---------- ----------
1 IT Billing 1
2 Engineering 2
3 Finance 7CROSS JOIN
CROSS JOIN dopasowuje każdy wiersz pierwszej tabeli do każdego wiersza drugiej tabeli. Jeśli tabele wejściowe mają odpowiednio wiersz x i y, wynikowa tabela będzie miała wiersz x * y. Ponieważ połączenia CROSS JOIN mogą generować bardzo duże tabele, należy uważać, aby ich używać tylko wtedy, gdy jest to właściwe.
Poniżej znajduje się składnia CROSS JOIN -
SELECT ... FROM table1 CROSS JOIN table2 ...Na podstawie powyższych tabel możesz napisać CROSS JOIN w następujący sposób -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;Powyższe zapytanie da następujący wynik -
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Paul Engineering
7 Paul Finance
1 Allen IT Billing
2 Allen Engineering
7 Allen Finance
1 Teddy IT Billing
2 Teddy Engineering
7 Teddy Finance
1 Mark IT Billing
2 Mark Engineering
7 Mark Finance
1 David IT Billing
2 David Engineering
7 David Finance
1 Kim IT Billing
2 Kim Engineering
7 Kim Finance
1 James IT Billing
2 James Engineering
7 James FinanceWEWNĘTRZNE DOŁĄCZENIE
INNER JOIN tworzy nową tabelę wynikową, łącząc wartości kolumn z dwóch tabel (tabela1 i tabela2) na podstawie predykatu łączenia. Zapytanie porównuje każdy wiersz tabeli tabela1 z każdym wierszem tabeli tabela2, aby znaleźć wszystkie pary wierszy, które spełniają predykat łączenia. Gdy predykat łączenia jest spełniony, wartości kolumn dla każdej dopasowanej pary wierszy A i B są łączone w wiersz wynikowy.
SPRZĘŻENIE WEWNĘTRZNE jest najpopularniejszym i domyślnym typem sprzężenia. Opcjonalnie można użyć słowa kluczowego INNER.
Poniżej znajduje się składnia INNER JOIN -
SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...Aby uniknąć nadmiarowości i skrócić frazę, warunki INNER JOIN można zadeklarować z rozszerzeniem USINGwyrażenie. To wyrażenie określa listę zawierającą jedną lub więcej kolumn.
SELECT ... FROM table1 JOIN table2 USING ( column1 ,... ) ...NATURALNE POŁĄCZENIE jest podobne do JOIN...USING, tylko że automatycznie sprawdza równość między wartościami każdej kolumny, która istnieje w obu tabelach -
SELECT ... FROM table1 NATURAL JOIN table2...Na podstawie powyższych tabel możesz napisać WEWNĘTRZNE DOŁĄCZENIE w następujący sposób -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;Powyższe zapytanie da następujący wynik -
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
7 James FinanceDOŁĄCZ ZEWNĘTRZNY
OUTER JOIN jest rozszerzeniem INNER JOIN. Chociaż standard SQL definiuje trzy typy POŁĄCZEŃ ZEWNĘTRZNYCH: LEFT, RIGHT i FULL, SQLite obsługuje tylkoLEFT OUTER JOIN.
OUTER JOIN mają warunek identyczny z INNER JOIN, wyrażony za pomocą słowa kluczowego ON, USING lub NATURAL. Wstępna tabela wyników jest obliczana w ten sam sposób. Po obliczeniu podstawowego JOIN, OUTER JOIN pobierze wszystkie niepołączone wiersze z jednej lub obu tabel, wypełnia je wartościami NULL i dołącza je do wynikowej tabeli.
Poniżej znajduje się składnia LEFT OUTER JOIN -
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...Aby uniknąć nadmiarowości i skrócić frazę, warunki OUTER JOIN można zadeklarować za pomocą wyrażenia USING. To wyrażenie określa listę zawierającą jedną lub więcej kolumn.
SELECT ... FROM table1 LEFT OUTER JOIN table2 USING ( column1 ,... ) ...Na podstawie powyższych tabel możesz napisać sprzężenie wewnętrzne w następujący sposób -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;Powyższe zapytanie da następujący wynik -
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineering
Teddy
Mark
David
Kim
7 James FinanceSQLite UNION klauzula / operator służy do łączenia wyników dwóch lub więcej instrukcji SELECT bez zwracania zduplikowanych wierszy.
Aby użyć UNION, każdy SELECT musi mieć tę samą liczbę wybranych kolumn, taką samą liczbę wyrażeń kolumnowych, ten sam typ danych i mieć je w tej samej kolejności, ale nie muszą mieć tej samej długości.
Składnia
Poniżej znajduje się podstawowa składnia UNION.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]Tutaj podanym warunkiem może być dowolne wyrażenie oparte na twoim wymaganiu.
Przykład
Rozważ następujące dwie tabele, (a) tabela COMPANY w następujący sposób -
sqlite> select * from COMPANY;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0(b) Kolejna tabela to DEPARTMENT w następujący sposób -
ID DEPT EMP_ID
---------- -------------------- ----------
1 IT Billing 1
2 Engineering 2
3 Finance 7
4 Engineering 3
5 Finance 4
6 Engineering 5
7 Finance 6Teraz połączmy te dwie tabele za pomocą instrukcji SELECT wraz z klauzulą UNION w następujący sposób -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;Spowoduje to następujący wynik.
EMP_ID NAME DEPT
---------- -------------------- ----------
1 Paul IT Billing
2 Allen Engineering
3 Teddy Engineering
4 Mark Finance
5 David Engineering
6 Kim Finance
7 James FinanceKlauzula UNION ALL
Operator UNION ALL służy do łączenia wyników dwóch instrukcji SELECT, w tym zduplikowanych wierszy.
Te same zasady, które mają zastosowanie do UNION, dotyczą również operatora UNION ALL.
Składnia
Poniżej znajduje się podstawowa składnia UNION ALL.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION ALL
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]Tutaj podanym warunkiem może być dowolne wyrażenie oparte na twoim wymaganiu.
Przykład
Teraz połączmy wspomniane powyżej dwie tabele w naszej instrukcji SELECT w następujący sposób -
sqlite> SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID
UNION ALL
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;Spowoduje to następujący wynik.
EMP_ID NAME DEPT
---------- -------------------- ----------
1 Paul IT Billing
2 Allen Engineering
3 Teddy Engineering
4 Mark Finance
5 David Engineering
6 Kim Finance
7 James Finance
1 Paul IT Billing
2 Allen Engineering
3 Teddy Engineering
4 Mark Finance
5 David Engineering
6 Kim Finance
7 James FinanceSQLite NULLto termin używany do reprezentowania brakującej wartości. Wartość NULL w tabeli to wartość w polu, które wydaje się puste.
Pole z wartością NULL to pole bez wartości. Bardzo ważne jest, aby zrozumieć, że wartość NULL różni się od wartości zerowej lub pola zawierającego spacje.
Składnia
Poniżej znajduje się podstawowa składnia using NULL podczas tworzenia tabeli.
SQLite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Tutaj, NOT NULLoznacza, że kolumna powinna zawsze akceptować jawną wartość danego typu danych. Istnieją dwie kolumny, w których nie użyliśmy wartości NOT NULL, co oznacza, że te kolumny mogą mieć wartość NULL.
Pole o wartości NULL to takie, które zostało puste podczas tworzenia rekordu.
Przykład
Wartość NULL może powodować problemy podczas wybierania danych, ponieważ podczas porównywania nieznanej wartości z jakąkolwiek inną wartością wynik jest zawsze nieznany i nie jest uwzględniany w wynikach końcowych. Rozważ poniższą tabelę FIRMA z następującymi zapisami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Użyjmy instrukcji UPDATE, aby ustawić kilka wartości dopuszczających wartość null jako NULL w następujący sposób -
sqlite> UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);Teraz tabela COMPANY będzie miała następujące rekordy.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22
7 James 24Następnie zobaczmy użycie IS NOT NULL operator, aby wyświetlić wszystkie rekordy, w których SALARY nie ma wartości NULL.
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NOT NULL;Powyższa instrukcja SQLite da następujący wynik -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Poniżej przedstawiono użycie IS NULL operator, który wyświetli listę wszystkich rekordów, w których SALARY ma wartość NULL.
sqlite> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM COMPANY
WHERE SALARY IS NULL;Powyższa instrukcja SQLite da następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22
7 James 24Możesz tymczasowo zmienić nazwę tabeli lub kolumny, nadając inną nazwę, znaną jako ALIAS. Użycie aliasów tabel oznacza zmianę nazwy tabeli w określonej instrukcji SQLite. Zmiana nazwy jest zmianą tymczasową, a rzeczywista nazwa tabeli nie zmienia się w bazie danych.
Aliasy kolumn służą do zmiany nazw kolumn tabeli na potrzeby konkretnego zapytania SQLite.
Składnia
Poniżej znajduje się podstawowa składnia table Alias.
SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];Poniżej znajduje się podstawowa składnia column Alias.
SELECT column_name AS alias_name
FROM table_name
WHERE [condition];Przykład
Rozważ dwie poniższe tabele: (a) Tabela COMPANY przedstawia się następująco -
sqlite> select * from COMPANY;
ID NAME AGE ADDRESS SALARY
---------- -------------------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0(b) Kolejna tabela to DEPARTMENT w następujący sposób -
ID DEPT EMP_ID
---------- -------------------- ----------
1 IT Billing 1
2 Engineering 2
3 Finance 7
4 Engineering 3
5 Finance 4
6 Engineering 5
7 Finance 6Teraz, poniżej jest użycie TABLE ALIAS gdzie używamy C i D jako aliasów odpowiednio dla tabel COMPANY i DEPARTMENT -
sqlite> SELECT C.ID, C.NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;Powyższa instrukcja SQLite da następujący wynik -
ID NAME AGE DEPT
---------- ---------- ---------- ----------
1 Paul 32 IT Billing
2 Allen 25 Engineering
3 Teddy 23 Engineering
4 Mark 25 Finance
5 David 27 Engineering
6 Kim 22 Finance
7 James 24 FinanceRozważ przykład użycia COLUMN ALIAS gdzie COMPANY_ID to alias kolumny ID, a COMPANY_NAME to alias kolumny nazwa.
sqlite> SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
FROM COMPANY AS C, DEPARTMENT AS D
WHERE C.ID = D.EMP_ID;Powyższa instrukcja SQLite da następujący wynik -
COMPANY_ID COMPANY_NAME AGE DEPT
---------- ------------ ---------- ----------
1 Paul 32 IT Billing
2 Allen 25 Engineering
3 Teddy 23 Engineering
4 Mark 25 Finance
5 David 27 Engineering
6 Kim 22 Finance
7 James 24 FinanceSQLite Triggersto funkcje wywołania zwrotnego bazy danych, które są automatycznie wykonywane / wywoływane po wystąpieniu określonego zdarzenia bazy danych. Poniżej znajdują się ważne punkty dotyczące wyzwalaczy SQLite -
Wyzwalacz SQLite może być określony tak, aby był uruchamiany za każdym razem, gdy wystąpi DELETE, INSERT lub UPDATE określonej tabeli bazy danych, lub gdy nastąpi UPDATE w jednej lub większej liczbie określonych kolumn tabeli.
Obecnie SQLite obsługuje tylko wyzwalacze FOR EACH ROW, a nie wyzwalacze FOR EACH STATEMENT. Dlatego jawne określenie DLA KAŻDEGO WIERSZA jest opcjonalne.
Zarówno klauzula WHEN, jak i akcje wyzwalające mogą uzyskać dostęp do elementów wiersza, który jest wstawiany, usuwany lub aktualizowany za pomocą odwołań do formularza NEW.column-name i OLD.column-name, gdzie nazwa-kolumny to nazwa kolumny z tabeli, z którą powiązany jest wyzwalacz.
Jeśli podano klauzulę WHEN, określone instrukcje SQL są wykonywane tylko dla wierszy, dla których klauzula WHEN ma wartość true. Jeśli nie podano klauzuli WHEN, instrukcje SQL są wykonywane dla wszystkich wierszy.
Słowo kluczowe BEFORE lub AFTER określa, kiedy zostaną wykonane akcje wyzwalające względem wstawienia, modyfikacji lub usunięcia skojarzonego wiersza.
Wyzwalacze są automatycznie usuwane, gdy usuwana jest tabela, z którą są skojarzone.
Tabela, która ma zostać zmodyfikowana, musi istnieć w tej samej bazie danych co tabela lub widok, do którego jest dołączony wyzwalacz i należy użyć tylko tablename nie database.tablename.
W programie wyzwalającym można użyć specjalnej funkcji SQL RAISE (), aby zgłosić wyjątek.
Składnia
Poniżej znajduje się podstawowa składnia tworzenia pliku trigger.
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;Tutaj, event_namemoże być operacją bazy danych INSERT, DELETE i UPDATE na wspomnianej tabelitable_name. Opcjonalnie możesz określić DLA KAŻDEGO WIERSZU po nazwie tabeli.
Poniżej znajduje się składnia tworzenia wyzwalacza dla operacji UPDATE na jednej lub większej liczbie określonych kolumn tabeli.
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;Przykład
Rozważmy przypadek, w którym chcemy zachować próbę audytową dla każdego rekordu wstawianego do tabeli COMPANY, którą tworzymy na nowo w następujący sposób (Usuń tabelę COMPANY, jeśli już ją masz).
sqlite> CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Aby zachować próbę audytu, utworzymy nową tabelę o nazwie AUDIT, w której będą wstawiane komunikaty dziennika, gdy tylko pojawi się wpis w tabeli COMPANY dla nowego rekordu.
sqlite> CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);Tutaj ID to ID rekordu AUDIT, a EMP_ID to ID, które będzie pochodziło z tabeli COMPANY, a DATE zachowa znacznik czasu, kiedy rekord zostanie utworzony w tabeli COMPANY. Teraz utwórzmy wyzwalacz w tabeli COMPANY w następujący sposób -
sqlite> CREATE TRIGGER audit_log AFTER INSERT
ON COMPANY
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END;Teraz przystąpimy do właściwej pracy, zacznijmy wstawiać rekord do tabeli COMPANY, co powinno skutkować utworzeniem rekordu dziennika audytu w tabeli AUDIT. Utwórz jeden rekord w tabeli COMPANY w następujący sposób -
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );Spowoduje to utworzenie jednego rekordu w tabeli COMPANY, która wygląda następująco -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0W tym samym czasie w tabeli AUDIT zostanie utworzony jeden rekord. Ten rekord jest wynikiem wyzwalacza, który utworzyliśmy na podstawie operacji INSERT w tabeli COMPANY. Podobnie możesz utworzyć wyzwalacze dla operacji UPDATE i DELETE w oparciu o własne wymagania.
EMP_ID ENTRY_DATE
---------- -------------------
1 2013-04-05 06:26:00Wyzwalacze aukcji
Możesz wyświetlić listę wszystkich wyzwalaczy z sqlite_master tabela w następujący sposób -
sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger';Powyższa instrukcja SQLite wyświetli tylko jeden wpis w następujący sposób -
name
----------
audit_logJeśli chcesz wyświetlić listę wyzwalaczy w określonej tabeli, użyj klauzuli AND z nazwą tabeli w następujący sposób -
sqlite> SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';Powyższa instrukcja SQLite wyświetli również tylko jeden wpis w następujący sposób -
name
----------
audit_logUpuszczanie wyzwalaczy
Poniżej znajduje się polecenie DROP, którego można użyć do usunięcia istniejącego wyzwalacza.
sqlite> DROP TRIGGER trigger_name;Indeksy to specjalne tabele wyszukiwania, których mechanizm wyszukiwania bazy danych może używać do przyspieszania pobierania danych. Mówiąc najprościej, plikindexjest wskaźnikiem do danych w tabeli. Indeks w bazie danych jest bardzo podobny do indeksu na końcu książki.
Na przykład, jeśli chcesz odnieść się do wszystkich stron w książce, które omawiają określony temat, najpierw odwołujesz się do indeksu, który zawiera listę wszystkich tematów alfabetycznie, a następnie odsyła się do jednego lub kilku określonych numerów stron.
Indeks pomaga przyspieszyć zapytania SELECT i klauzule WHERE, ale spowalnia wprowadzanie danych dzięki instrukcjom UPDATE i INSERT. Indeksy można tworzyć lub usuwać bez wpływu na dane.
Tworzenie indeksu obejmuje instrukcję CREATE INDEX, która umożliwia nazwanie indeksu, określenie tabeli i kolumny do indeksowania oraz wskazanie, czy indeks jest w porządku rosnącym, czy malejącym.
Indeksy mogą być również unikatowe, podobnie jak w przypadku ograniczenia UNIQUE, ponieważ indeks zapobiega zduplikowanym wpisom w kolumnie lub kombinacji kolumn, w których znajduje się indeks.
Polecenie CREATE INDEX
Poniżej znajduje się podstawowa składnia CREATE INDEX.
CREATE INDEX index_name ON table_name;Indeksy jednokolumnowe
Indeks jednokolumnowy to taki, który jest tworzony na podstawie tylko jednej kolumny tabeli. Podstawowa składnia jest następująca -
CREATE INDEX index_name
ON table_name (column_name);Unikalne indeksy
Unikalne indeksy służą nie tylko wydajności, ale także integralności danych. Unikalny indeks nie pozwala na wstawienie do tabeli żadnych zduplikowanych wartości. Podstawowa składnia jest następująca -
CREATE UNIQUE INDEX index_name
on table_name (column_name);Indeksy złożone
Indeks złożony to indeks dwóch lub więcej kolumn tabeli. Podstawowa składnia jest następująca -
CREATE INDEX index_name
on table_name (column1, column2);Niezależnie od tego, czy chcesz utworzyć indeks jednokolumnowy, czy indeks złożony, weź pod uwagę kolumny, których możesz często używać w klauzuli WHERE zapytania jako warunki filtru.
W przypadku użycia tylko jednej kolumny należy wybrać indeks jednokolumnowy. Jeśli w klauzuli WHERE są często używane dwie lub więcej kolumn jako filtry, najlepszym wyborem będzie indeks złożony.
Niejawne indeksy
Niejawne indeksy to indeksy, które są automatycznie tworzone przez serwer bazy danych podczas tworzenia obiektu. Indeksy są tworzone automatycznie dla ograniczeń klucza podstawowego i ograniczeń unikalności.
Example
Poniżej znajduje się przykład, w którym utworzymy indeks w tabeli FIRMA dla kolumny wynagrodzenia -
sqlite> CREATE INDEX salary_index ON COMPANY (salary);Teraz wypiszmy wszystkie indeksy dostępne w tabeli COMPANY za pomocą .indices polecenie w następujący sposób -
sqlite> .indices COMPANYTo da następujący wynik, gdzie sqlite_autoindex_COMPANY_1 jest niejawnym indeksem, który został utworzony podczas tworzenia samej tabeli.
salary_index
sqlite_autoindex_COMPANY_1Możesz wyświetlić listę wszystkich baz danych indeksów w następujący sposób -
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';Polecenie DROP INDEX
Indeks można usunąć za pomocą SQLite DROPKomenda. Należy zachować ostrożność podczas usuwania indeksu, ponieważ wydajność może zostać spowolniona lub poprawiona.
Oto podstawowa składnia jest następująca -
DROP INDEX index_name;Możesz użyć poniższej instrukcji, aby usunąć wcześniej utworzony indeks.
sqlite> DROP INDEX salary_index;Kiedy należy unikać indeksów?
Chociaż indeksy mają na celu zwiększenie wydajności bazy danych, czasami należy ich unikać. Poniższe wytyczne wskazują, kiedy należy ponownie rozważyć użycie indeksu.
Indeksów nie należy używać w -
- Małe stoliki.
- Tabele, które mają częste, duże operacje aktualizacji lub wstawiania.
- Kolumny zawierające dużą liczbę wartości NULL.
- Kolumny, którymi często się manipuluje.
Klauzula „INDEXED BY nazwa-indeksu” określa, że w celu wyszukania wartości w poprzedniej tabeli należy użyć wskazanego indeksu.
Jeśli nazwa-indeksu nie istnieje lub nie można jej użyć w zapytaniu, przygotowanie instrukcji SQLite kończy się niepowodzeniem.
Klauzula „NOT INDEXED” określa, że żaden indeks nie będzie używany podczas uzyskiwania dostępu do poprzedniej tabeli, w tym niejawnych indeksów utworzonych przez ograniczenia UNIQUE i PRIMARY KEY.
Jednak INTEGER PRIMARY KEY nadal może być używany do wyszukiwania wpisów, nawet jeśli określono „NOT INDEXED”.
Składnia
Poniżej przedstawiono składnię klauzuli INDEXED BY i można jej używać z instrukcją DELETE, UPDATE lub SELECT.
SELECT|DELETE|UPDATE column1, column2...
INDEXED BY (index_name)
table_name
WHERE (CONDITION);Przykład
Rozważ tabelę COMPANY Stworzymy indeks i użyjemy go do wykonania operacji INDEXED BY.
sqlite> CREATE INDEX salary_index ON COMPANY(salary);
sqlite>Teraz wybierając dane z tabeli COMPANY możesz użyć klauzuli INDEXED BY w następujący sposób -
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
7 James 24 Houston 10000.0
2 Allen 25 Texas 15000.0
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0SQLite ALTER TABLEpolecenie modyfikuje istniejącą tabelę bez wykonywania pełnego zrzutu i ponownego ładowania danych. Możesz zmienić nazwę tabeli za pomocą instrukcji ALTER TABLE, a dodatkowe kolumny można dodać do istniejącej tabeli za pomocą instrukcji ALTER TABLE.
Nie ma innej operacji obsługiwanej przez polecenie ALTER TABLE w SQLite poza zmianą nazwy tabeli i dodaniem kolumny w istniejącej tabeli.
Składnia
Poniżej znajduje się podstawowa składnia ALTER TABLE aby ZMIENIĆ NAZWĘ istniejącej tabeli.
ALTER TABLE database_name.table_name RENAME TO new_table_name;Poniżej znajduje się podstawowa składnia ALTER TABLE aby dodać nową kolumnę w istniejącej tabeli.
ALTER TABLE database_name.table_name ADD COLUMN column_def...;Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Teraz spróbujmy zmienić nazwę tej tabeli za pomocą instrukcji ALTER TABLE w następujący sposób -
sqlite> ALTER TABLE COMPANY RENAME TO OLD_COMPANY;Powyższa instrukcja SQLite zmieni nazwę tabeli COMPANY na OLD_COMPANY. Teraz spróbujmy dodać nową kolumnę w tabeli OLD_COMPANY w następujący sposób -
sqlite> ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1);Tabela COMPANY została zmieniona, a po niej będzie wynik instrukcji SELECT.
ID NAME AGE ADDRESS SALARY SEX
---------- ---------- ---------- ---------- ---------- ---
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Należy zauważyć, że nowo dodana kolumna jest wypełniona wartościami NULL.
Niestety nie mamy polecenia TRUNCATE TABLE w SQLite, ale możesz użyć SQLite DELETE polecenie usunięcia wszystkich danych z istniejącej tabeli, chociaż zaleca się użycie polecenia DROP TABLE w celu usunięcia całej tabeli i ponownego utworzenia jej.
Składnia
Poniżej znajduje się podstawowa składnia polecenia DELETE.
sqlite> DELETE FROM table_name;Poniżej znajduje się podstawowa składnia DROP TABLE.
sqlite> DROP TABLE table_name;Jeśli używasz polecenia DELETE TABLE, aby usunąć wszystkie rekordy, zaleca się użycie VACUUM polecenie wyczyszczenia nieużywanej przestrzeni.
Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład obcięcia powyższej tabeli -
SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;Teraz tabela COMPANY jest całkowicie obcięta i nic nie będzie wynikiem instrukcji SELECT.
Widok to nic innego jak instrukcja SQLite, która jest przechowywana w bazie danych z powiązaną nazwą. W rzeczywistości jest to kompozycja tabeli w postaci predefiniowanego zapytania SQLite.
Widok może zawierać wszystkie wiersze tabeli lub wybrane wiersze z jednej lub wielu tabel. Widok można utworzyć z jednej lub wielu tabel, w zależności od zapisanego zapytania SQLite w celu utworzenia widoku.
Widoki, które są rodzajem wirtualnych tabel, pozwalają użytkownikom -
Strukturyzuj dane w sposób, który użytkownicy lub klasy użytkowników uznają za naturalny lub intuicyjny.
Ogranicz dostęp do danych w taki sposób, aby użytkownik mógł zobaczyć tylko ograniczone dane zamiast pełnej tabeli.
Podsumuj dane z różnych tabel, które można wykorzystać do generowania raportów.
Widoki SQLite są tylko do odczytu i dlatego możesz nie być w stanie wykonać instrukcji DELETE, INSERT lub UPDATE w widoku. Możesz jednak utworzyć wyzwalacz w widoku, który jest uruchamiany przy próbie USUNIĘCIA, WSTAWIENIA lub AKTUALIZACJI widoku i zrobić to, czego potrzebujesz w treści wyzwalacza.
Tworzenie widoków
Widoki SQLite są tworzone przy użyciu CREATE VIEWkomunikat. Widoki SQLite można tworzyć z pojedynczej tabeli, wielu tabel lub innego widoku.
Poniżej przedstawiono podstawową składnię CREATE VIEW.
CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];Możesz dołączyć wiele tabel do instrukcji SELECT w podobny sposób, jak używasz ich w zwykłym zapytaniu SQL SELECT. Jeśli obecne jest opcjonalne słowo kluczowe TEMP lub TEMPORARY, widok zostanie utworzony w tymczasowej bazie danych.
Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Poniżej znajduje się przykład tworzenia widoku z tabeli COMPANY. Ten widok będzie miał tylko kilka kolumn z tabeli COMPANY.
sqlite> CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM COMPANY;Możesz teraz wykonywać zapytania COMPANY_VIEW w podobny sposób, jak kwerendy dotyczące rzeczywistej tabeli. Oto przykład -
sqlite> SELECT * FROM COMPANY_VIEW;Spowoduje to następujący wynik.
ID NAME AGE
---------- ---------- ----------
1 Paul 32
2 Allen 25
3 Teddy 23
4 Mark 25
5 David 27
6 Kim 22
7 James 24Upuszczanie widoków
Aby usunąć widok, po prostu użyj instrukcji DROP VIEW z rozszerzeniem view_name. Podstawowa składnia DROP VIEW jest następująca -
sqlite> DROP VIEW view_name;Poniższe polecenie usunie widok COMPANY_VIEW, który utworzyliśmy w ostatniej sekcji.
sqlite> DROP VIEW COMPANY_VIEW;Transakcja to jednostka pracy wykonywana na bazie danych. Transakcje to jednostki lub sekwencje pracy wykonane w logicznej kolejności, czy to ręcznie przez użytkownika, czy automatycznie przez jakiś program bazodanowy.
Transakcja to propagacja jednej lub więcej zmian w bazie danych. Na przykład, jeśli tworzysz, aktualizujesz lub usuwasz rekord z tabeli, to wykonujesz transakcję na tabeli. Kontrolowanie transakcji jest ważne, aby zapewnić integralność danych i obsługiwać błędy bazy danych.
W praktyce wiele zapytań SQLite połączysz w grupę i wykonasz wszystkie razem w ramach transakcji.
Właściwości transakcji
Transakcje mają następujące cztery standardowe właściwości, zwykle określane skrótem ACID.
Atomicity- Zapewnia pomyślne zakończenie wszystkich operacji w jednostce pracy; w przeciwnym razie transakcja jest przerywana w momencie niepowodzenia, a poprzednie operacje są przywracane do poprzedniego stanu.
Consistency - Zapewnia, że baza danych prawidłowo zmienia stany po pomyślnym zatwierdzeniu transakcji.
Isolation - Umożliwia niezależne i przejrzyste transakcje transakcji.
Durability - Zapewnia, że wynik lub skutek zatwierdzonej transakcji będzie się utrzymywał w przypadku awarii systemu.
Kontrola transakcji
Poniżej znajdują się polecenia służące do sterowania transakcjami:
BEGIN TRANSACTION - Aby rozpocząć transakcję.
COMMIT - Aby zapisać zmiany, możesz użyć alternatywnie END TRANSACTION Komenda.
ROLLBACK - Aby cofnąć zmiany.
Transakcyjne polecenia sterujące są używane tylko z poleceniami DML INSERT, UPDATE i DELETE. Nie można ich używać podczas tworzenia tabel lub usuwania ich, ponieważ te operacje są automatycznie zatwierdzane w bazie danych.
Polecenie BEGIN TRANSACTION
Transakcje można rozpocząć za pomocą polecenia BEGIN TRANSACTION lub po prostu polecenia BEGIN. Takie transakcje zwykle trwają do momentu napotkania następnej komendy COMMIT lub ROLLBACK. Jednak transakcja zostanie również ROLLBACK, jeśli baza danych zostanie zamknięta lub jeśli wystąpi błąd. Poniżej znajduje się prosta składnia do rozpoczęcia transakcji.
BEGIN;
or
BEGIN TRANSACTION;COMMIT Command
Polecenie COMMIT jest poleceniem transakcyjnym używanym do zapisywania zmian wywołanych przez transakcję w bazie danych.
Polecenie COMMIT zapisuje wszystkie transakcje w bazie danych od ostatniego polecenia COMMIT lub ROLLBACK.
Poniżej znajduje się składnia polecenia COMMIT.
COMMIT;
or
END TRANSACTION;Polecenie ROLLBACK
Polecenie ROLLBACK jest poleceniem transakcyjnym używanym do cofania transakcji, które nie zostały jeszcze zapisane w bazie danych.
Komendy ROLLBACK można używać tylko do cofania transakcji od czasu wydania ostatniej komendy COMMIT lub ROLLBACK.
Poniżej przedstawiono składnię polecenia ROLLBACK.
ROLLBACK;Example
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Teraz zacznijmy transakcję i usuńmy rekordy z tabeli o wieku = 25. Następnie użyj polecenia ROLLBACK, aby cofnąć wszystkie zmiany.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> ROLLBACK;Teraz, jeśli sprawdzisz tabelę COMPANY, nadal zawiera ona następujące rekordy -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Zacznijmy kolejną transakcję i usuńmy rekordy z tabeli mające wiek = 25 i na koniec używamy polecenia COMMIT, aby zatwierdzić wszystkie zmiany.
sqlite> BEGIN;
sqlite> DELETE FROM COMPANY WHERE AGE = 25;
sqlite> COMMIT;Jeśli teraz sprawdzisz, tabela FIRMA nadal zawiera następujące rekordy -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Zapytanie podzapytanie lub zapytanie wewnętrzne lub zapytanie zagnieżdżone to zapytanie zawarte w innym zapytaniu SQLite i osadzone w klauzuli WHERE.
Podzapytanie służy do zwracania danych, które będą używane w zapytaniu głównym jako warunek dalszego ograniczenia danych do pobrania.
Podzapytań można używać z instrukcjami SELECT, INSERT, UPDATE i DELETE wraz z operatorami, takimi jak =, <,>,> =, <=, IN, BETWEEN itd.
Istnieje kilka zasad, których muszą przestrzegać podzapytania -
Podzapytania muszą być zawarte w nawiasach.
Podzapytanie może mieć tylko jedną kolumnę w klauzuli SELECT, chyba że w głównym zapytaniu dla podzapytania znajduje się wiele kolumn w celu porównania wybranych kolumn.
ORDER BY nie może być użyte w podzapytaniu, chociaż główne zapytanie może używać ORDER BY. Funkcji GROUP BY można użyć do wykonania tej samej funkcji, co ORDER BY w podzapytaniu.
Podkwerendy, które zwracają więcej niż jeden wiersz, mogą być używane tylko z wieloma operatorami wartości, takimi jak operator IN.
Operator BETWEEN nie może być używany z podzapytaniem; jednakże BETWEEN może być użyte w podzapytaniu.
Podzapytania z instrukcją SELECT
Podkwerendy są najczęściej używane z instrukcją SELECT. Podstawowa składnia jest następująca -
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])Przykład
Weź pod uwagę tabelę COMPANY z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Teraz sprawdźmy następujące zapytanie podrzędne za pomocą instrukcji SELECT.
sqlite> SELECT *
FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY
WHERE SALARY > 45000) ;Spowoduje to następujący wynik.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0Podzapytania z instrukcją INSERT
Podzapytań można również używać z instrukcjami INSERT. Instrukcja INSERT wykorzystuje dane zwrócone z podzapytania do wstawienia do innej tabeli. Wybrane dane w podzapytaniu można modyfikować za pomocą dowolnej funkcji znakowej, daty lub liczby.
Oto podstawowa składnia jest następująca -
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]Przykład
Rozważmy tabelę COMPANY_BKP o podobnej strukturze jak tabela COMPANY i można ją utworzyć przy użyciu tej samej tabeli CREATE TABLE, używając COMPANY_BKP jako nazwy tabeli. Aby skopiować całą tabelę COMPANY do COMPANY_BKP, poniżej przedstawiono składnię -
sqlite> INSERT INTO COMPANY_BKP
SELECT * FROM COMPANY
WHERE ID IN (SELECT ID
FROM COMPANY) ;Podzapytania z instrukcją UPDATE
Podzapytanie może być używane w połączeniu z instrukcją UPDATE. Za pomocą podzapytania z instrukcją UPDATE można zaktualizować jedną lub wiele kolumn w tabeli.
Oto podstawowa składnia jest następująca -
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]Przykład
Zakładając, że mamy dostępną tabelę COMPANY_BKP, która jest kopią zapasową tabeli COMPANY.
Poniższy przykład aktualizuje PŁACĘ o 0,50 razy w tabeli FIRMA dla wszystkich klientów, których WIEK jest większy lub równy 27.
sqlite> UPDATE COMPANY
SET SALARY = SALARY * 0.50
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE >= 27 );Wpłynęłoby to na dwa wiersze i ostatecznie tabela COMPANY miałaby następujące rekordy -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 10000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 42500.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Podzapytania z instrukcją DELETE
Podzapytanie może być używane w połączeniu z instrukcją DELETE, tak jak w przypadku innych instrukcji wymienionych powyżej.
Oto podstawowa składnia jest następująca -
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]Przykład
Zakładając, że mamy dostępną tabelę COMPANY_BKP, która jest kopią zapasową tabeli COMPANY.
Poniższy przykład usuwa rekordy z tabeli COMPANY dla wszystkich klientów, których WIEK jest większy lub równy 27.
sqlite> DELETE FROM COMPANY
WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
WHERE AGE > 27 );Wpłynie to na dwa wiersze i ostatecznie tabela COMPANY będzie miała następujące rekordy -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 42500.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0SQLite AUTOINCREMENTto słowo kluczowe używane do automatycznego zwiększania wartości pola w tabeli. Możemy automatycznie zwiększać wartość pola za pomocąAUTOINCREMENT słowo kluczowe podczas tworzenia tabeli z określoną nazwą kolumny do automatycznego zwiększania.
Słowo kluczowe AUTOINCREMENT może być używany tylko z polem INTEGER.
Składnia
Podstawowe użycie AUTOINCREMENT słowo kluczowe jest następujące -
CREATE TABLE table_name(
column1 INTEGER AUTOINCREMENT,
column2 datatype,
column3 datatype,
.....
columnN datatype,
);Przykład
Rozważmy utworzenie tabeli COMPANY w następujący sposób -
sqlite> CREATE TABLE COMPANY(
ID INTEGER PRIMARY KEY AUTOINCREMENT,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);Teraz wstaw następujące rekordy do tabeli COMPANY -
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );Spowoduje to wstawienie 7 krotek do tabeli COMPANY i COMPANY będą miały następujące rekordy -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Jeśli wprowadzisz dane wejściowe użytkownika za pośrednictwem strony internetowej i wstawisz je do bazy danych SQLite, istnieje szansa, że pozostawiłeś się szeroko otwarty na problem bezpieczeństwa znany jako wstrzyknięcie SQL. W tym rozdziale dowiesz się, jak temu zapobiec i jak zabezpieczyć swoje skrypty i instrukcje SQLite.
Wstrzyknięcie zwykle ma miejsce, gdy poprosisz użytkownika o wprowadzenie danych, na przykład jego imię i nazwisko, a zamiast nazwy podają ci instrukcję SQLite, którą nieświadomie uruchomisz w swojej bazie danych.
Nigdy nie ufaj danym dostarczonym przez użytkownika, przetwarzaj te dane dopiero po weryfikacji; z reguły odbywa się to poprzez dopasowywanie wzorców. W poniższym przykładzie nazwa użytkownika jest ograniczona do znaków alfanumerycznych i podkreślenia oraz do długości od 8 do 20 znaków - zmodyfikuj te reguły w razie potrzeby.
if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){ $db = new SQLiteDatabase('filename');
$result = @$db->query("SELECT * FROM users WHERE username = $matches[0]");
} else {
echo "username not accepted";
}Aby zademonstrować problem, rozważ ten fragment -
$name = "Qadir'; DELETE FROM users;";
@$db->query("SELECT * FROM users WHERE username = '{$name}'");Wywołanie funkcji ma na celu pobranie rekordu z tabeli użytkowników, w którym kolumna nazwy odpowiada nazwie określonej przez użytkownika. W normalnych okolicznościach,$namebędzie zawierać tylko znaki alfanumeryczne i być może spacje, takie jak ciąg ilia. Jednak w tym przypadku, poprzez dołączenie całkowicie nowego zapytania do $ name, wywołanie bazy danych zamienia się w katastrofę: wstrzyknięte zapytanie DELETE usuwa wszystkie rekordy od użytkowników.
Istnieją interfejsy baz danych, które nie pozwalają na zestawianie zapytań ani wykonywanie wielu zapytań w jednym wywołaniu funkcji. Jeśli spróbujesz zestawić zapytania, wywołanie kończy się niepowodzeniem, ale SQLite i PostgreSQL z radością wykonują zapytania skumulowane, wykonując wszystkie zapytania dostarczone w jednym ciągu i tworząc poważny problem z bezpieczeństwem.
Zapobieganie iniekcji SQL
Możesz inteligentnie obsługiwać wszystkie znaki ucieczki w językach skryptowych, takich jak PERL i PHP. Funkcję zapewnia język programowania PHPstring sqlite_escape_string() aby uniknąć znaków wejściowych, które są specjalne dla SQLite.
if (get_magic_quotes_gpc()) {
$name = sqlite_escape_string($name);
}
$result = @$db->query("SELECT * FROM users WHERE username = '{$name}'");Chociaż kodowanie zapewnia bezpieczne wstawianie danych, będzie renderować proste porównania tekstu i LIKE klauzule w zapytaniach nie nadają się do użytku w kolumnach zawierających dane binarne.
Note - addslashes()NIE należy używać do cytowania swoich ciągów w zapytaniach SQLite; będzie to prowadzić do dziwnych rezultatów podczas pobierania danych.
Instrukcja SQLite może być poprzedzona słowem kluczowym „EXPLAIN” lub wyrażeniem „EXPLAIN QUERY PLAN” używanym do opisu szczegółów tabeli.
Każda modyfikacja powoduje, że instrukcja SQLite zachowuje się jak zapytanie i zwraca informacje o działaniu instrukcji SQLite, gdyby zostało pominięte słowo kluczowe lub fraza EXPLAIN.
Dane wyjściowe z EXPLAIN i EXPLAIN QUERY PLAN są przeznaczone wyłącznie do interaktywnej analizy i rozwiązywania problemów.
Szczegóły formatu wyjściowego mogą ulec zmianie z jednej wersji SQLite do następnej.
Aplikacje nie powinny używać WYJAŚNIENIA ani WYJAŚNIENIA PLANU ZAPYTANIA, ponieważ ich dokładne zachowanie jest zmienne i tylko częściowo udokumentowane.
Składnia
składnia dla EXPLAIN wygląda następująco -
EXPLAIN [SQLite Query]składnia dla EXPLAIN QUERY PLAN wygląda następująco -
EXPLAIN QUERY PLAN [SQLite Query]Przykład
Rozważ tabelę COMPANY z następującymi rekordami -
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Teraz sprawdźmy następujące zapytanie podrzędne za pomocą instrukcji SELECT -
sqlite> EXPLAIN SELECT * FROM COMPANY WHERE Salary >= 20000;Spowoduje to następujący wynik.
addr opcode p1 p2 p3
---------- ---------- ---------- ---------- ----------
0 Goto 0 19
1 Integer 0 0
2 OpenRead 0 8
3 SetNumColu 0 5
4 Rewind 0 17
5 Column 0 4
6 RealAffini 0 0
7 Integer 20000 0
8 Lt 357 16 collseq(BI
9 Rowid 0 0
10 Column 0 1
11 Column 0 2
12 Column 0 3
13 Column 0 4
14 RealAffini 0 0
15 Callback 5 0
16 Next 0 5
17 Close 0 0
18 Halt 0 0
19 Transactio 0 0
20 VerifyCook 0 38
21 Goto 0 1
22 Noop 0 0Teraz sprawdźmy, co następuje Explain Query Plan z instrukcją SELECT -
SQLite> EXPLAIN QUERY PLAN SELECT * FROM COMPANY WHERE Salary >= 20000;
order from detail
---------- ---------- -------------
0 0 TABLE COMPANYKomenda VACUUM czyści główną bazę danych poprzez skopiowanie jej zawartości do tymczasowego pliku bazy danych i ponowne załadowanie oryginalnego pliku bazy danych z kopii. Eliminuje to wolne strony, wyrównuje dane tabeli tak, aby były ciągłe, a ponadto czyści strukturę pliku bazy danych.
Komenda VACUUM może zmienić ROWID wpisów w tabelach, które nie mają jawnego INTEGER PRIMARY KEY. Polecenie VACUUM działa tylko w głównej bazie danych. Nie jest możliwe VACUUM dołączonego pliku bazy danych.
Polecenie VACUUM zakończy się niepowodzeniem, jeśli istnieje aktywna transakcja. Polecenie VACUUM nie działa w przypadku baz danych w pamięci. Ponieważ komenda VACUUM odbudowuje plik bazy danych od podstaw, VACUUM może być również używany do modyfikowania wielu parametrów konfiguracyjnych specyficznych dla bazy danych.
ODKURZANIE ręczne
Poniżej znajduje się prosta składnia do wydania polecenia VACUUM dla całej bazy danych z wiersza poleceń -
$sqlite3 database_name "VACUUM;"Możesz uruchomić VACUUM z wiersza poleceń SQLite, a także:
sqlite> VACUUM;Możesz również uruchomić VACUUM na określonym stole w następujący sposób -
sqlite> VACUUM table_name;Auto-VACCUM
SQLite Auto-VACUUM nie działa tak samo jak VACUUM, a jedynie przenosi wolne strony na koniec bazy danych, zmniejszając w ten sposób rozmiar bazy danych. W ten sposób może znacznie pofragmentować bazę danych, podczas gdy VACUUM zapewnia defragmentację. W związku z tym Auto-VACUUM sprawia, że baza danych jest niewielka.
Możesz włączyć / wyłączyć automatyczne odkurzanie SQLite za pomocą następujących pragm uruchomionych w wierszu polecenia SQLite -
sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuumMożesz uruchomić następujące polecenie z wiersza polecenia, aby sprawdzić ustawienie automatycznego odkurzania -
$sqlite3 database_name "PRAGMA auto_vacuum;"SQLite obsługuje pięć funkcji daty i czasu w następujący sposób -
| Sr.No. | Funkcjonować | Przykład |
|---|---|---|
| 1 | data (czas, modyfikatory ...) | Zwraca datę w następującym formacie: RRRR-MM-DD |
| 2 | czas (czas, modyfikatory ...) | Zwraca czas jako HH: MM: SS |
| 3 | datetime (timestring, modyfikatory ...) | Zwraca RRRR-MM-DD GG: MM: SS |
| 4 | julianday (timestring, modyfikatory ...) | Zwraca liczbę dni od południa w Greenwich 24 listopada 4714 rpne |
| 5 | strftime (timestring, modyfikatory ...) | Zwraca datę sformatowaną zgodnie z ciągiem formatu określonym jako pierwszy argument sformatowany zgodnie z elementami formatującymi opisanymi poniżej. |
Wszystkie powyższe pięć funkcji daty i czasu przyjmują jako argument ciąg czasu. Po ciągu czasu następuje zero lub więcej modyfikatorów. Funkcja strftime () również przyjmuje jako pierwszy argument ciąg formatu. Poniższa sekcja zawiera szczegółowe informacje na temat różnych typów ciągów czasowych i modyfikatorów.
Ciągi czasu
Ciąg czasu może mieć dowolny z następujących formatów -
| Sr.No. | Ciąg czasu | Przykład |
|---|---|---|
| 1 | RRRR-MM-DD | 2010-12-30 |
| 2 | RRRR-MM-DD GG: MM | 2010-12-30 12:10 |
| 3 | RRRR-MM-DD GG: MM: SS.SSS | 2010-12-30 12: 10: 04.100 |
| 4 | MM-DD-RRRR GG: MM | 30-12-2010 12:10 |
| 5 | HH: MM | 12:10 |
| 6 | RRRR-MM-DDTHH: MM | 2010-12-30 12:10 |
| 7 | HH: MM: SS | 12:10:01 |
| 8 | RRRRMMDD GGMMSS | 20101230 121001 |
| 9 | teraz | 2013-05-07 |
Możesz użyć litery „T” jako dosłownego znaku oddzielającego datę i godzinę.
Modyfikatory
Po ciągu czasu może następować zero lub więcej modyfikatorów, które zmienią datę i / lub godzinę zwróconą przez dowolną z pięciu powyższych funkcji. Modyfikatory są stosowane od lewej do prawej.
W SQLite dostępne są następujące modyfikatory -
- Dni NNN
- NNN godzin
- NNN minut
- NNN.NNNN sekund
- NNN miesięcy
- NNN lat
- początek miesiąca
- początek roku
- początek dnia
- dzień tygodnia N
- unixepoch
- localtime
- utc
Formatters
SQLite zapewnia bardzo przydatną funkcję strftime()aby sformatować dowolną datę i godzinę. Możesz użyć następujących podstawień, aby sformatować datę i godzinę.
| Podstawienie | Opis |
|---|---|
| %re | Dzień miesiąca, 01-31 |
| %fa | Ułamki sekund, SS.SSS |
| % H | Godzina, 00-23 |
| %jot | Dzień roku, 001-366 |
| %JOT | Numer dnia juliańskiego, DDDD.DDDD |
| % m | Miesiąc 00-12 |
| % M | Minuta, 00-59 |
| % s | Sekundy od 1970-01-01 |
| % S | Sekundy, 00-59 |
| % w | Dzień tygodnia, 0-6 (0 to niedziela) |
| % W | Tydzień roku, 01-53 |
| % Y | Rok RRRR |
| %% | symbol% |
Przykłady
Wypróbujmy teraz różne przykłady przy użyciu zachęty SQLite. Następujące polecenie oblicza aktualną datę.
sqlite> SELECT date('now');
2013-05-07Następujące polecenie oblicza ostatni dzień bieżącego miesiąca.
sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31Następujące polecenie oblicza datę i godzinę dla danego znacznika czasu UNIX 1092941466.
sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06Następujące polecenie oblicza datę i godzinę dla danego znacznika czasu UNIX 1092941466 i kompensuje lokalną strefę czasową.
sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06Następujące polecenie oblicza bieżący znacznik czasu UNIX.
sqlite> SELECT strftime('%s','now');
1393348134Następujące polecenie oblicza liczbę dni od podpisania Deklaracji Niepodległości Stanów Zjednoczonych.
sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023Następujące polecenie oblicza liczbę sekund od określonego momentu w 2004 roku.
sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572Następujące polecenie oblicza datę pierwszego wtorku października dla bieżącego roku.
sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01Następujące polecenie oblicza czas od epoki UNIX w sekundach (jak strftime ('% s', 'now'), z wyjątkiem części ułamkowej).
sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598Aby przekonwertować między wartościami czasu UTC i czasu lokalnego podczas formatowania daty, użyj modyfikatorów utc lub czasu lokalnego w następujący sposób -
sqlite> SELECT time('12:00', 'localtime');
05:00:00sqlite> SELECT time('12:00', 'utc');
19:00:00SQLite ma wiele wbudowanych funkcji do przetwarzania danych liczbowych lub łańcuchowych. Poniżej znajduje się lista kilku przydatnych wbudowanych funkcji SQLite. We wszystkich rozróżniana jest wielkość liter, co oznacza, że można używać tych funkcji z małymi lub dużymi literami lub w postaci mieszanej. Więcej informacji można znaleźć w oficjalnej dokumentacji oprogramowania SQLite.
| Sr.No. | Opis funkcji |
|---|---|
| 1 | SQLite COUNT Function Funkcja agregująca SQLite COUNT służy do liczenia wierszy w tabeli bazy danych. |
| 2 | SQLite MAX Function Funkcja agregująca SQLite MAX pozwala nam wybrać najwyższą (maksymalną) wartość dla określonej kolumny. |
| 3 | SQLite MIN Function Funkcja agregująca SQLite MIN pozwala nam wybrać najniższą (minimalną) wartość dla określonej kolumny. |
| 4 | SQLite AVG Function Funkcja agregująca SQLite AVG wybiera średnią wartość dla określonej kolumny tabeli. |
| 5 | SQLite SUM Function Funkcja agregująca SQLite SUM umożliwia wybranie sumy dla kolumny numerycznej. |
| 6 | SQLite RANDOM Function Funkcja SQLite RANDOM zwraca pseudolosową liczbę całkowitą z przedziału od -9223372036854775808 do +9223372036854775807. |
| 7 | SQLite ABS Function Funkcja SQLite ABS zwraca wartość bezwzględną argumentu liczbowego. |
| 8 | SQLite UPPER Function Funkcja SQLite UPPER konwertuje ciąg znaków na wielkie litery. |
| 9 | SQLite LOWER Function Funkcja SQLite LOWER konwertuje ciąg znaków na małe litery. |
| 10 | SQLite LENGTH Function Funkcja SQLite LENGTH zwraca długość łańcucha. |
| 11 | SQLite sqlite_version Function Funkcja SQLite sqlite_version zwraca wersję biblioteki SQLite. |
Zanim zaczniemy podawać przykłady wyżej wymienionych funkcji, rozważ tabelę FIRMA z następującymi rekordami.
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0Funkcja SQLite COUNT
Funkcja agregująca SQLite COUNT służy do liczenia wierszy w tabeli bazy danych. Oto przykład -
sqlite> SELECT count(*) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
count(*)
----------
7Funkcja SQLite MAX
Funkcja agregująca SQLite MAX pozwala nam wybrać najwyższą (maksymalną) wartość dla określonej kolumny. Oto przykład -
sqlite> SELECT max(salary) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
max(salary)
-----------
85000.0Funkcja SQLite MIN
Funkcja agregująca SQLite MIN pozwala nam wybrać najniższą (minimalną) wartość dla określonej kolumny. Oto przykład -
sqlite> SELECT min(salary) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
min(salary)
-----------
10000.0Funkcja SQLite AVG
Funkcja agregująca SQLite AVG wybiera średnią wartość dla określonej kolumny tabeli. Oto przykład -
sqlite> SELECT avg(salary) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
avg(salary)
----------------
37142.8571428572Funkcja SQLite SUM
Funkcja agregująca SQLite SUM umożliwia wybranie sumy dla kolumny numerycznej. Oto przykład -
sqlite> SELECT sum(salary) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
sum(salary)
-----------
260000.0Funkcja SQLite RANDOM
Funkcja SQLite RANDOM zwraca pseudolosową liczbę całkowitą z przedziału od -9223372036854775808 do +9223372036854775807. Oto przykład -
sqlite> SELECT random() AS Random;Powyższa instrukcja SQLite SQL da następujący wynik.
Random
-------------------
5876796417670984050Funkcja SQLite ABS
Funkcja SQLite ABS zwraca wartość bezwzględną argumentu liczbowego. Oto przykład -
sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");Powyższa instrukcja SQLite SQL da następujący wynik.
abs(5) abs(-15) abs(NULL) abs(0) abs("ABC")
---------- ---------- ---------- ---------- ----------
5 15 0 0.0Funkcja SQLite UPPER
Funkcja SQLite UPPER konwertuje ciąg znaków na wielkie litery. Oto przykład -
sqlite> SELECT upper(name) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMESFunkcja SQLite LOWER
Funkcja SQLite LOWER konwertuje ciąg znaków na małe litery. Oto przykład -
sqlite> SELECT lower(name) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
lower(name)
-----------
paul
allen
teddy
mark
david
kim
jamesFunkcja SQLite LENGTH
Funkcja SQLite LENGTH zwraca długość łańcucha. Oto przykład -
sqlite> SELECT name, length(name) FROM COMPANY;Powyższa instrukcja SQLite SQL da następujący wynik.
NAME length(name)
---------- ------------
Paul 4
Allen 5
Teddy 5
Mark 4
David 5
Kim 3
James 5Funkcja SQLite sqlite_version
Funkcja SQLite sqlite_version zwraca wersję biblioteki SQLite. Oto przykład -
sqlite> SELECT sqlite_version() AS 'SQLite Version';Powyższa instrukcja SQLite SQL da następujący wynik.
SQLite Version
--------------
3.6.20W tym rozdziale nauczysz się, jak używać SQLite w programach C / C ++.
Instalacja
Zanim zaczniesz używać SQLite w naszych programach C / C ++, upewnij się, że masz skonfigurowaną bibliotekę SQLite na komputerze. Możesz zapoznać się z rozdziałem Instalacja SQLite, aby zrozumieć proces instalacji.
Interfejsy API C / C ++
Poniżej znajdują się ważne procedury interfejsu C / C ++ SQLite, które mogą wystarczyć do pracy z bazą danych SQLite z poziomu programu C / C ++. Jeśli szukasz bardziej wyrafinowanej aplikacji, możesz zajrzeć do oficjalnej dokumentacji SQLite.
| Sr.No. | API i opis |
|---|---|
| 1 | sqlite3_open(const char *filename, sqlite3 **ppDb) Ta procedura otwiera połączenie z plikiem bazy danych SQLite i zwraca obiekt połączenia z bazą danych, który ma być używany przez inne procedury SQLite. Jeśli argument filename ma wartość NULL lub ': memory:', sqlite3_open () utworzy bazę danych w pamięci w pamięci RAM, która będzie trwała tylko przez czas trwania sesji. Jeśli nazwa pliku nie jest równa NULL, sqlite3_open () próbuje otworzyć plik bazy danych, używając jego wartości. Jeśli nie istnieje żaden plik o tej nazwie, sqlite3_open () otworzy nowy plik bazy danych o tej nazwie. |
| 2 | sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg) Ta procedura zapewnia szybki i łatwy sposób wykonywania poleceń SQL udostępnianych przez argument sql, który może składać się z więcej niż jednego polecenia SQL. Tutaj pierwszy argument sqlite3 to otwarty obiekt bazy danych, sqlite_callback to wywołanie zwrotne, dla którego dane są pierwszym argumentem, a errmsg zostanie zwrócony w celu przechwycenia każdego błędu wywołanego przez procedurę. Funkcja SQLite3_exec () analizuje i wykonuje każde polecenie podane w sql argument, dopóki nie osiągnie końca ciągu lub napotka błąd. |
| 3 | sqlite3_close(sqlite3*) Ta procedura zamyka połączenie z bazą danych, które zostało wcześniej otwarte przez wywołanie funkcji sqlite3_open (). Wszystkie przygotowane oświadczenia związane z połączeniem należy sfinalizować przed zamknięciem połączenia. Jeśli pozostaną jakiekolwiek zapytania, które nie zostały sfinalizowane, sqlite3_close () zwróci SQLITE_BUSY z komunikatem o błędzie Nie można zamknąć z powodu niezakończonych instrukcji. |
Połącz się z bazą danych
Poniższy segment kodu C pokazuje, jak połączyć się z istniejącą bazą danych. Jeśli baza danych nie istnieje, zostanie utworzona i na koniec zostanie zwrócony obiekt bazy danych.
#include <stdio.h>
#include <sqlite3.h>
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
sqlite3_close(db);
}Teraz skompilujmy i uruchommy powyższy program, aby utworzyć naszą bazę danych test.dbw bieżącym katalogu. Możesz zmienić ścieżkę zgodnie z wymaganiami.
$gcc test.c -l sqlite3
$./a.out
Opened database successfullyJeśli zamierzasz używać kodu źródłowego C ++, możesz skompilować swój kod w następujący sposób -
$g++ test.c -l sqlite3Tutaj łączymy nasz program z biblioteką sqlite3, aby zapewnić wymagane funkcje programowi C. Spowoduje to utworzenie pliku bazy danych test.db w twoim katalogu i uzyskasz następujący wynik.
-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out
-rw-r--r--. 1 root root 323 May 8 02:05 test.c
-rw-r--r--. 1 root root 0 May 8 02:06 test.dbUtwórz tabelę
Poniższy segment kodu C zostanie użyty do stworzenia tabeli we wcześniej utworzonej bazie danych -
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
int i;
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stdout, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "CREATE TABLE COMPANY(" \
"ID INT PRIMARY KEY NOT NULL," \
"NAME TEXT NOT NULL," \
"AGE INT NOT NULL," \
"ADDRESS CHAR(50)," \
"SALARY REAL );";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Table created successfully\n");
}
sqlite3_close(db);
return 0;
}Kiedy powyższy program zostanie skompilowany i wykonany, utworzy tabelę COMPANY w twoim test.db, a ostateczna lista pliku będzie wyglądać następująco -
-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out
-rw-r--r--. 1 root root 1207 May 8 02:31 test.c
-rw-r--r--. 1 root root 3072 May 8 02:31 test.dbOperacja INSERT
Poniższy segment kodu C pokazuje, jak można tworzyć rekordy w tabeli COMPANY utworzonej w powyższym przykładzie -
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
int i;
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " \
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); " \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
"INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
if( rc != SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Records created successfully\n");
}
sqlite3_close(db);
return 0;
}Gdy powyższy program zostanie skompilowany i wykonany, utworzy podane rekordy w tabeli COMPANY i wyświetli następujące dwie linie -
Opened database successfully
Records created successfullyWybierz operację
Zanim przejdziemy do aktualnego przykładu pobierania rekordów, przyjrzyjmy się szczegółom funkcji wywołania zwrotnego, której używamy w naszych przykładach. To wywołanie zwrotne umożliwia uzyskanie wyników z instrukcji SELECT. Ma następującą deklarację -
typedef int (*sqlite3_callback)(
void*, /* Data provided in the 4th argument of sqlite3_exec() */
int, /* The number of columns in row */
char**, /* An array of strings representing fields in the row */
char** /* An array of strings representing column names */
);Jeśli powyższe wywołanie zwrotne zostanie podane w procedurze sqlite_exec () jako trzeci argument, SQLite wywoła tę funkcję zwrotną dla każdego rekordu przetwarzanego w każdej instrukcji SELECT wykonywanej w argumencie SQL.
Poniższy segment kodu C pokazuje, jak można pobrać i wyświetlić rekordy z tabeli COMPANY utworzonej w powyższym przykładzie -
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create SQL statement */
sql = "SELECT * from COMPANY";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}Gdy powyższy program zostanie skompilowany i wykonany, da następujący wynik.
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyOperacja UPDATE
Poniższy segment kodu C pokazuje, jak możemy użyć instrukcji UPDATE do aktualizacji dowolnego rekordu, a następnie pobrać i wyświetlić zaktualizowane rekordy z tabeli COMPANY.
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName){
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create merged SQL statement */
sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; " \
"SELECT * from COMPANY";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}Gdy powyższy program zostanie skompilowany i wykonany, da następujący wynik.
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyOperacja USUŃ
Poniższy segment kodu C pokazuje, jak można użyć instrukcji DELETE, aby usunąć dowolny rekord, a następnie pobrać i wyświetlić pozostałe rekordy z tabeli COMPANY.
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *data, int argc, char **argv, char **azColName) {
int i;
fprintf(stderr, "%s: ", (const char*)data);
for(i = 0; i<argc; i++) {
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char* argv[]) {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
char *sql;
const char* data = "Callback function called";
/* Open database */
rc = sqlite3_open("test.db", &db);
if( rc ) {
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
return(0);
} else {
fprintf(stderr, "Opened database successfully\n");
}
/* Create merged SQL statement */
sql = "DELETE from COMPANY where ID=2; " \
"SELECT * from COMPANY";
/* Execute SQL statement */
rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
} else {
fprintf(stdout, "Operation done successfully\n");
}
sqlite3_close(db);
return 0;
}Gdy powyższy program zostanie skompilowany i wykonany, da następujący wynik.
Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyW tym rozdziale dowiesz się, jak używać SQLite w programach Java.
Instalacja
Zanim zaczniesz używać SQLite w naszych programach Java, upewnij się, że masz zainstalowany sterownik SQLite JDBC i Javę na komputerze. Możesz sprawdzić samouczek Java dotyczący instalacji Java na swoim komputerze. Teraz sprawdźmy, jak skonfigurować sterownik SQLite JDBC.
Pobierz najnowszą wersję sqlite-jdbc- (VERSION) .jar z repozytorium sqlite-jdbc .
Dodaj pobrany plik jar sqlite-jdbc- (WERSJA) .jar do ścieżki klasy lub możesz użyć go razem z opcją -classpath, jak wyjaśniono w poniższych przykładach.
W poniższej sekcji założono, że masz niewielką wiedzę na temat pojęć Java JDBC. Jeśli tego nie zrobisz, sugerujemy spędzenie pół godziny z samouczkiem JDBC, aby oswoić się z koncepcjami wyjaśnionymi poniżej.
Połącz się z bazą danych
Poniższe programy Java pokazują, jak połączyć się z istniejącą bazą danych. Jeśli baza danych nie istnieje, zostanie utworzona i na koniec zostanie zwrócony obiekt bazy danych.
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Opened database successfully");
}
}Teraz skompilujmy i uruchommy powyższy program, aby utworzyć naszą bazę danych test.dbw bieżącym katalogu. Możesz zmienić ścieżkę zgodnie z wymaganiami. Zakładamy, że aktualna wersja sterownika JDBC sqlite-jdbc-3.7.2.jar jest dostępna w bieżącej ścieżce.
$javac SQLiteJDBC.java $java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfullyJeśli zamierzasz używać komputera z systemem Windows, możesz skompilować i uruchomić kod w następujący sposób -
$javac SQLiteJDBC.java $java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfullyUtwórz tabelę
Poniższy program w języku Java zostanie wykorzystany do stworzenia tabeli we wcześniej utworzonej bazie danych.
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "CREATE TABLE COMPANY " +
"(ID INT PRIMARY KEY NOT NULL," +
" NAME TEXT NOT NULL, " +
" AGE INT NOT NULL, " +
" ADDRESS CHAR(50), " +
" SALARY REAL)";
stmt.executeUpdate(sql);
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Table created successfully");
}
}Kiedy powyższy program zostanie skompilowany i wykonany, utworzy tabelę COMPANY w twoim test.db a ostateczna lista pliku będzie wyglądać następująco -
-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar
-rw-r--r--. 1 root root 1506 May 8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root 832 May 8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root 3072 May 8 05:43 test.dbOperacja INSERT
Poniższy program Java pokazuje, jak tworzyć rekordy w tabeli COMPANY utworzonej w powyższym przykładzie.
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (1, 'Paul', 32, 'California', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
stmt.executeUpdate(sql);
sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
"VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
stmt.executeUpdate(sql);
stmt.close();
c.commit();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Records created successfully");
}
}Kiedy powyższy program zostanie skompilowany i uruchomiony, utworzy podane rekordy w tabeli COMPANY i wyświetli następujące dwie linie -
Opened database successfully
Records created successfullyWybierz operację
Poniższy program w języku Java pokazuje, jak pobrać i wyświetlić rekordy z tabeli COMPANY utworzonej w powyższym przykładzie.
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}Gdy powyższy program zostanie skompilowany i wykonany, da następujący wynik.
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyOperacja UPDATE
Poniższy kod Java pokazuje, jak użyć instrukcji UPDATE do zaktualizowania dowolnego rekordu, a następnie pobrać i wyświetlić zaktualizowane rekordy z tabeli COMPANY.
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}Gdy powyższy program zostanie skompilowany i wykonany, da następujący wynik.
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyOperacja USUŃ
Poniższy kod Java pokazuje, jak użyć instrukcji DELETE, aby usunąć dowolny rekord, a następnie pobrać i wyświetlić pozostałe rekordy z naszej tabeli COMPANY.
import java.sql.*;
public class SQLiteJDBC {
public static void main( String args[] ) {
Connection c = null;
Statement stmt = null;
try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");
stmt = c.createStatement();
String sql = "DELETE from COMPANY where ID=2;";
stmt.executeUpdate(sql);
c.commit();
ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
while ( rs.next() ) {
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String address = rs.getString("address");
float salary = rs.getFloat("salary");
System.out.println( "ID = " + id );
System.out.println( "NAME = " + name );
System.out.println( "AGE = " + age );
System.out.println( "ADDRESS = " + address );
System.out.println( "SALARY = " + salary );
System.out.println();
}
rs.close();
stmt.close();
c.close();
} catch ( Exception e ) {
System.err.println( e.getClass().getName() + ": " + e.getMessage() );
System.exit(0);
}
System.out.println("Operation done successfully");
}
}Gdy powyższy program zostanie skompilowany i wykonany, da następujący wynik.
Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyW tym rozdziale dowiesz się, jak używać SQLite w programach PHP.
Instalacja
Rozszerzenie SQLite3 jest domyślnie włączone od PHP 5.3.0. Można go wyłączyć za pomocą--without-sqlite3 w czasie kompilacji.
Użytkownicy systemu Windows muszą włączyć php_sqlite3.dll, aby używać tego rozszerzenia. Ta biblioteka DLL jest dołączona do dystrybucji PHP w systemie Windows od wersji PHP 5.3.0.
Aby uzyskać szczegółowe instrukcje instalacji, zapoznaj się z naszym samouczkiem PHP i jego oficjalną stroną internetową.
Interfejsy API interfejsu PHP
Poniżej znajdują się ważne procedury PHP, które mogą wystarczyć do pracy z bazą danych SQLite w programie PHP. Jeśli szukasz bardziej wyrafinowanej aplikacji, możesz zajrzeć do oficjalnej dokumentacji PHP.
| Sr.No. | API i opis |
|---|---|
| 1 | public void SQLite3::open ( filename, flags, encryption_key ) Otwiera bazę danych SQLite 3. Jeśli kompilacja zawiera szyfrowanie, spróbuje użyć klucza. Jeśli nazwa pliku jest podana jako':memory:', SQLite3 :: open () utworzy bazę danych w pamięci RAM, która będzie działać tylko na czas trwania sesji. Jeśli nazwa pliku jest rzeczywistą nazwą pliku urządzenia, SQLite3 :: open () próbuje otworzyć plik bazy danych przy użyciu jego wartości. Jeśli nie istnieje żaden plik o tej nazwie, zostanie utworzony nowy plik bazy danych o tej nazwie. Opcjonalne flagi używane do określenia, jak otworzyć bazę danych SQLite. Domyślnie open używa SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE. |
| 2 | public bool SQLite3::exec ( string $query ) Ta procedura zapewnia szybki i łatwy sposób wykonywania poleceń SQL udostępnianych przez argument sql, który może składać się z więcej niż jednego polecenia SQL. Ta procedura służy do wykonywania zapytań bez wyników względem danej bazy danych. |
| 3 | public SQLite3Result SQLite3::query ( string $query ) Ta procedura wykonuje zapytanie SQL, zwracając plik SQLite3Result obiekt, jeśli zapytanie zwróci wyniki. |
| 4 | public int SQLite3::lastErrorCode ( void ) Ta procedura zwraca numeryczny kod wyniku ostatniego nieudanego żądania SQLite. |
| 5 | public string SQLite3::lastErrorMsg ( void ) Ta procedura zwraca tekst w języku angielskim opisujący ostatnie nieudane żądanie SQLite. |
| 6 | public int SQLite3::changes ( void ) Ta procedura zwraca liczbę wierszy bazy danych, które zostały zaktualizowane, wstawione lub usunięte przez ostatnią instrukcję SQL. |
| 7 | public bool SQLite3::close ( void ) Ta procedura zamyka połączenie z bazą danych wcześniej otwarte przez wywołanie SQLite3 :: open (). |
| 8 | public string SQLite3::escapeString ( string $value ) Ta procedura zwraca łańcuch, który został prawidłowo zmieniony w celu bezpiecznego włączenia do instrukcji SQL. |
Połącz się z bazą danych
Poniższy kod PHP pokazuje, jak połączyć się z istniejącą bazą danych. Jeśli baza danych nie istnieje, to zostanie utworzona i na koniec zostanie zwrócony obiekt bazy danych.
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB(); if(!$db) {
echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
?>Teraz uruchommy powyższy program, aby utworzyć naszą bazę danych test.dbw bieżącym katalogu. Możesz zmienić ścieżkę zgodnie z wymaganiami. Jeśli baza danych zostanie pomyślnie utworzona, wyświetli się następujący komunikat -
Open database successfullyUtwórz tabelę
Poniższy program PHP posłuży do stworzenia tabeli we wcześniej utworzonej bazie danych.
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB(); if(!$db) {
echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF
CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);
EOF;
$ret = $db->exec($sql); if(!$ret){
echo $db->lastErrorMsg(); } else { echo "Table created successfully\n"; } $db->close();
?>Po wykonaniu powyższego programu utworzy on tabelę COMPANY w pliku test.db i wyświetli następujące komunikaty -
Opened database successfully
Table created successfullyOperacja INSERT
Poniższy program PHP pokazuje, jak tworzyć rekordy w tabeli COMPANY utworzonej w powyższym przykładzie.
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db'); } } $db = new MyDB();
if(!$db){ echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); EOF; $ret = $db->exec($sql);
if(!$ret) { echo $db->lastErrorMsg();
} else {
echo "Records created successfully\n";
}
$db->close();
?>Po wykonaniu powyższego programu utworzy on podane rekordy w tabeli COMPANY i wyświetli następujące dwie linie.
Opened database successfully
Records created successfullyWybierz operację
Poniższy program PHP pokazuje, jak pobrać i wyświetlić rekordy z tabeli COMPANY utworzonej w powyższym przykładzie -
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db');
}
}
$db = new MyDB(); if(!$db) {
echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>Wykonanie powyższego programu da następujący wynik.
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullyOperacja UPDATE
Poniższy kod PHP pokazuje, jak za pomocą instrukcji UPDATE zaktualizować dowolny rekord, a następnie pobrać i wyświetlić zaktualizowane rekordy z tabeli COMPANY.
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db'); } } $db = new MyDB();
if(!$db) { echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF UPDATE COMPANY set SALARY = 25000.00 where ID=1; EOF; $ret = $db->exec($sql);
if(!$ret) { echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record updated successfully\n"; } $sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>Wykonanie powyższego programu da następujący wynik.
Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullyOperacja USUŃ
Poniższy kod PHP pokazuje, jak użyć instrukcji DELETE, aby usunąć dowolny rekord, a następnie pobrać i wyświetlić pozostałe rekordy z tabeli COMPANY.
<?php
class MyDB extends SQLite3 {
function __construct() {
$this->open('test.db'); } } $db = new MyDB();
if(!$db) { echo $db->lastErrorMsg();
} else {
echo "Opened database successfully\n";
}
$sql =<<<EOF DELETE from COMPANY where ID = 2; EOF; $ret = $db->exec($sql);
if(!$ret){ echo $db->lastErrorMsg();
} else {
echo $db->changes(), " Record deleted successfully\n"; } $sql =<<<EOF
SELECT * from COMPANY;
EOF;
$ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close();
?>Wykonanie powyższego programu da następujący wynik.
Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullyW tym rozdziale nauczysz się, jak używać SQLite w programach Perla.
Instalacja
SQLite3 można zintegrować z Perlem za pomocą modułu Perl DBI, który jest modułem dostępu do bazy danych dla języka programowania Perl. Definiuje zestaw metod, zmiennych i konwencji, które zapewniają standardowy interfejs bazy danych.
Poniżej przedstawiono proste kroki instalacji modułu DBI na komputerze z systemem Linux / UNIX -
$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz $ tar xvfz DBI-1.625.tar.gz
$ cd DBI-1.625 $ perl Makefile.PL
$ make $ make installJeśli chcesz zainstalować sterownik SQLite dla DBI, możesz go zainstalować w następujący sposób -
$ wget http://search.cpan.org/CPAN/authors/id/M/MS/MSERGEANT/DBD-SQLite-1.11.tar.gz $ tar xvfz DBD-SQLite-1.11.tar.gz
$ cd DBD-SQLite-1.11 $ perl Makefile.PL
$ make $ make installInterfejsy API interfejsu DBI
Poniżej znajdują się ważne procedury DBI, które mogą wystarczyć do pracy z bazą danych SQLite z poziomu programu Perl. Jeśli szukasz bardziej wyrafinowanej aplikacji, możesz zajrzeć do oficjalnej dokumentacji Perl DBI.
| Sr.No. | API i opis |
|---|---|
| 1 | DBI->connect($data_source, "", "", \%attr) Ustanawia połączenie bazy danych lub sesję z żądanym źródłem danych $ data_source. Zwraca obiekt uchwytu bazy danych, jeśli połączenie się powiedzie. Źródło danych ma postać - DBI:SQLite:dbname = 'test.db'gdzie SQLite to nazwa sterownika SQLite, a test.db to nazwa pliku bazy danych SQLite. Jeśli nazwa pliku jest podana jako':memory:', utworzy bazę danych w pamięci RAM, która będzie działać tylko na czas trwania sesji. Jeśli nazwa pliku jest rzeczywistą nazwą pliku urządzenia, wówczas próbuje otworzyć plik bazy danych przy użyciu jego wartości. Jeśli nie istnieje żaden plik o tej nazwie, zostanie utworzony nowy plik bazy danych o tej nazwie. Drugi i trzeci parametr zachowujesz jako puste ciągi, a ostatnim parametrem jest przekazanie różnych atrybutów, jak pokazano w poniższym przykładzie. |
| 2 | $dbh->do($sql) Ta procedura przygotowuje i wykonuje pojedynczą instrukcję SQL. Zwraca liczbę wierszy, na które ma to wpływ lub wartość undef w przypadku błędu. Wartość zwracana -1 oznacza, że liczba wierszy nie jest znana, nie ma zastosowania lub jest niedostępna. Tutaj $ dbh jest uchwytem zwracanym przez wywołanie DBI-> connect (). |
| 3 | $dbh->prepare($sql) Ta procedura przygotowuje instrukcję do późniejszego wykonania przez silnik bazy danych i zwraca odwołanie do obiektu uchwytu instrukcji. |
| 4 | $sth->execute() Ta procedura wykonuje wszelkie operacje niezbędne do wykonania przygotowanej instrukcji. W przypadku wystąpienia błędu zwracana jest wartość undef. Pomyślne wykonanie zawsze zwraca wartość true, niezależnie od liczby wierszy, na które ma to wpływ. Tutaj,$sth is a statement handle returned by $dbh-> przygotuj wywołanie ($ sql). |
| 5 | $sth->fetchrow_array() Ta procedura pobiera następny wiersz danych i zwraca go jako listę zawierającą wartości pól. Pola puste są zwracane na liście jako wartości undef. |
| 6 | $DBI::err Jest to równoważne $ h-> err, gdzie $h is any of the handle types like $dbh, $sth, or $drh. Zwraca to kod błędu natywnego silnika bazy danych z ostatniej wywołanej metody sterownika. |
| 7 | $DBI::errstr Jest to równoważne $ h-> errstr, gdzie $h is any of the handle types like $dbh, $sth, or $drh. Zwraca to komunikat o błędzie natywnego silnika bazy danych z ostatniej wywołanej metody DBI. |
| 8 | $dbh->disconnect() Ta procedura zamyka połączenie z bazą danych wcześniej otwarte przez wywołanie DBI-> connect (). |
Połącz się z bazą danych
Poniższy kod Perla pokazuje, jak połączyć się z istniejącą bazą danych. Jeśli baza danych nie istnieje, zostanie utworzona i na koniec zostanie zwrócony obiekt bazy danych.
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr;
print "Opened database successfully\n";Teraz uruchommy powyższy program, aby utworzyć naszą bazę danych test.db w bieżącym katalogu. Możesz zmienić ścieżkę zgodnie z wymaganiami. Zachowaj powyższy kod w pliku sqlite.pl i wykonaj go jak pokazano poniżej. Jeśli baza danych zostanie pomyślnie utworzona, wyświetli się następujący komunikat -
$ chmod +x sqlite.pl
$ ./sqlite.pl
Open database successfullyUtwórz tabelę
Poniższy program w Perlu służy do tworzenia tabeli we wcześniej utworzonej bazie danych.
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL););
my $rv = $dbh->do($stmt); if($rv < 0) {
print $DBI::errstr; } else { print "Table created successfully\n"; } $dbh->disconnect();Kiedy powyższy program zostanie wykonany, utworzy on tabelę COMPANY w twoim test.db i wyświetli następujące komunikaty -
Opened database successfully
Table created successfullyNOTE - Jeśli zobaczysz następujący błąd w którejkolwiek operacji -
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 398W takim przypadku otwórz dbdimp.c file dostępne w instalacji DBD-SQLite i dowiedz się sqlite3_prepare() funkcji i zmień jej trzeci argument na -1 zamiast 0. Na koniec zainstaluj DBD :: SQLite używając make i robić make install aby rozwiązać problem.
Operacja INSERT
Poniższy program w Perlu pokazuje, jak tworzyć rekordy w tabeli COMPANY utworzonej w powyższym przykładzie.
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite"; my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database"; my $userid = "";
my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;
$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;
print "Records created successfully\n";
$dbh->disconnect();Po wykonaniu powyższego programu utworzy podane rekordy w tabeli COMPANY i wyświetli następujące dwie linie -
Opened database successfully
Records created successfullyWybierz operację
Poniższy program w Perlu pokazuje, jak pobrać i wyświetlić rekordy z tabeli COMPANY utworzonej w powyższym przykładzie.
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();Wykonanie powyższego programu da następujący wynik.
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullyOperacja UPDATE
Poniższy kod Perla pokazuje, jak wykonać instrukcję UPDATE, aby zaktualizować dowolny rekord, a następnie pobrać i wyświetlić zaktualizowane rekordy z tabeli COMPANY.
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite"; my $database = "test.db";
my $dsn = "DBI:$driver:dbname=$database"; my $userid = "";
my $password = ""; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ) {
print $DBI::errstr; } else { print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr; if($rv < 0) {
print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) {
print "ID = ". $row[0] . "\n"; print "NAME = ". $row[1] ."\n";
print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();Wykonanie powyższego programu da następujący wynik.
Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullyOperacja USUŃ
Poniższy kod Perla pokazuje, jak użyć instrukcji DELETE, aby usunąć dowolny rekord, a następnie pobrać i wyświetlić pozostałe rekordy z tabeli COMPANY -
#!/usr/bin/perl
use DBI;
use strict;
my $driver = "SQLite";
my $database = "test.db"; my $dsn = "DBI:$driver:dbname=$database";
my $userid = ""; my $password = "";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(DELETE from COMPANY where ID = 2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) { print $DBI::errstr;
} else {
print "Total number of rows deleted : $rv\n"; } $stmt = qq(SELECT id, name, address, salary from COMPANY;);
my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
print "SALARY = ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();Wykonanie powyższego programu da następujący wynik.
Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000
Operation done successfullyW tym rozdziale dowiesz się, jak używać SQLite w programach Pythona.
Instalacja
SQLite3 można zintegrować z Pythonem za pomocą modułu sqlite3, którego autorem jest Gerhard Haring. Zapewnia interfejs SQL zgodny ze specyfikacją DB-API 2.0 opisaną w PEP 249. Nie musisz instalować tego modułu oddzielnie, ponieważ jest on dostarczany domyślnie wraz z wersją Python 2.5.x.
Aby użyć modułu sqlite3, musisz najpierw utworzyć obiekt połączenia, który reprezentuje bazę danych, a następnie opcjonalnie możesz utworzyć obiekt kursora, który pomoże ci w wykonywaniu wszystkich instrukcji SQL.
Interfejsy API modułu Python sqlite3
Poniżej znajdują się ważne procedury modułu sqlite3, które mogą wystarczyć do pracy z bazą danych SQLite z poziomu programu w Pythonie. Jeśli szukasz bardziej wyrafinowanej aplikacji, możesz zajrzeć do oficjalnej dokumentacji modułu Python sqlite3.
| Sr.No. | API i opis |
|---|---|
| 1 | sqlite3.connect(database [,timeout ,other optional arguments]) Ten interfejs API otwiera połączenie z plikiem bazy danych SQLite. Możesz użyć ": memory:" do otwarcia połączenia z bazą danych, która znajduje się w pamięci RAM zamiast na dysku. Jeśli baza danych zostanie otwarta pomyślnie, zwraca obiekt połączenia. Gdy dostęp do bazy danych jest uzyskiwany przez wiele połączeń, a jeden z procesów modyfikuje bazę danych, baza danych SQLite jest blokowana do momentu zatwierdzenia transakcji. Parametr timeout określa, jak długo połączenie powinno czekać, aż blokada zniknie do momentu zgłoszenia wyjątku. Wartość domyślna parametru limitu czasu to 5,0 (pięć sekund). Jeśli podana nazwa bazy danych nie istnieje, to wywołanie utworzy bazę danych. Możesz również określić nazwę pliku wraz z wymaganą ścieżką, jeśli chcesz utworzyć bazę danych w dowolnym miejscu poza bieżącym katalogiem. |
| 2 | connection.cursor([cursorClass]) Ta procedura tworzy plik cursorktóry będzie używany przez cały czas programowania bazy danych w Pythonie. Ta metoda akceptuje pojedynczy opcjonalny parametr cursorClass. Jeśli jest podana, musi to być niestandardowa klasa kursora, która rozszerza sqlite3.Cursor. |
| 3 | cursor.execute(sql [, optional parameters]) Ta procedura wykonuje instrukcję SQL. Instrukcja SQL może być sparametryzowana (tj. Symbole zastępcze zamiast literałów SQL). Moduł sqlite3 obsługuje dwa rodzaje symboli zastępczych: znaki zapytania i nazwane symbole zastępcze (nazwany styl). For example - kursor.execute ("wstaw do wartości osób (?,?)", (Kto, wiek)) |
| 4 | connection.execute(sql [, optional parameters]) Ta procedura jest skrótem do powyższej metody execute udostępnianej przez obiekt kursora i tworzy obiekt pośredniego kursora przez wywołanie metody kursora, a następnie wywołuje metodę wykonywania kursora z podanymi parametrami. |
| 5 | cursor.executemany(sql, seq_of_parameters) Ta procedura wykonuje polecenie SQL na wszystkich sekwencjach parametrów lub odwzorowaniach znalezionych w sekwencji sql. |
| 6 | connection.executemany(sql[, parameters]) Ta procedura jest skrótem, który tworzy obiekt kursora pośredniego przez wywołanie metody kursora, a następnie wywołuje metodę kursor.s executemany z podanymi parametrami. |
| 7 | cursor.executescript(sql_script) Ta procedura wykonuje jednocześnie wiele instrukcji SQL podanych w postaci skryptu. Najpierw wydaje instrukcję COMMIT, a następnie wykonuje skrypt SQL, który otrzymuje jako parametr. Wszystkie instrukcje SQL należy oddzielać średnikiem (;). |
| 8 | connection.executescript(sql_script) Ta procedura jest skrótem, który tworzy pośredni obiekt kursora przez wywołanie metody kursora, a następnie wywołuje metodę executescript kursora z podanymi parametrami. |
| 9 | connection.total_changes() Ta procedura zwraca całkowitą liczbę wierszy bazy danych, które zostały zmodyfikowane, wstawione lub usunięte od czasu otwarcia połączenia z bazą danych. |
| 10 | connection.commit() Ta metoda zatwierdza bieżącą transakcję. Jeśli nie wywołasz tej metody, wszystko, co zrobiłeś od ostatniego wywołania funkcji commit (), nie jest widoczne z innych połączeń z bazą danych. |
| 11 | connection.rollback() Ta metoda cofa wszelkie zmiany w bazie danych od ostatniego wywołania funkcji commit (). |
| 12 | connection.close() Ta metoda zamyka połączenie z bazą danych. Zwróć uwagę, że nie wywołuje to automatycznie funkcji commit (). Jeśli po prostu zamkniesz połączenie z bazą danych bez uprzedniego wywołania funkcji commit (), zmiany zostaną utracone! |
| 13 | cursor.fetchone() Ta metoda pobiera następny wiersz zestawu wyników zapytania, zwracając pojedynczą sekwencję lub None, gdy nie ma więcej danych. |
| 14 | cursor.fetchmany([size = cursor.arraysize]) Ta procedura pobiera następny zestaw wierszy wyniku zapytania, zwracając listę. Pusta lista jest zwracana, gdy nie ma więcej dostępnych wierszy. Metoda próbuje pobrać tyle wierszy, ile wskazuje parametr size. |
| 15 | cursor.fetchall() Ta procedura pobiera wszystkie (pozostałe) wiersze wyniku zapytania, zwracając listę. W przypadku braku dostępnych wierszy zwracana jest pusta lista. |
Połącz się z bazą danych
Poniższy kod Pythona pokazuje, jak połączyć się z istniejącą bazą danych. Jeśli baza danych nie istnieje, zostanie utworzona i na koniec zostanie zwrócony obiekt bazy danych.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";Tutaj możesz także podać nazwę bazy danych jako nazwę specjalną :memory:aby stworzyć bazę danych w pamięci RAM. Teraz uruchommy powyższy program, aby utworzyć naszą bazę danychtest.dbw bieżącym katalogu. Możesz zmienić ścieżkę zgodnie z wymaganiami. Zachowaj powyższy kod w pliku sqlite.py i wykonaj go, jak pokazano poniżej. Jeśli baza danych zostanie pomyślnie utworzona, wyświetli następujący komunikat.
$chmod +x sqlite.py $./sqlite.py
Open database successfullyUtwórz tabelę
Poniższy program w Pythonie zostanie użyty do stworzenia tabeli we wcześniej utworzonej bazie danych.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute('''CREATE TABLE COMPANY
(ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL);''')
print "Table created successfully";
conn.close()Po wykonaniu powyższego programu utworzy on tabelę COMPANY w pliku test.db i wyświetli następujące komunikaty -
Opened database successfully
Table created successfullyOperacja INSERT
Poniższy program w Pythonie pokazuje, jak tworzyć rekordy w tabeli COMPANY utworzonej w powyższym przykładzie.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (1, 'Paul', 32, 'California', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");
conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");
conn.commit()
print "Records created successfully";
conn.close()Po wykonaniu powyższego programu utworzy podane rekordy w tabeli COMPANY i wyświetli następujące dwie linie -
Opened database successfully
Records created successfullyWybierz operację
Poniższy program w Pythonie pokazuje, jak pobrać i wyświetlić rekordy z tabeli COMPANY utworzonej w powyższym przykładzie.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()Wykonanie powyższego programu da następujący wynik.
Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyOperacja UPDATE
Poniższy kod Pythona pokazuje, jak użyć instrukcji UPDATE do zaktualizowania dowolnego rekordu, a następnie pobrać i wyświetlić zaktualizowane rekordy z tabeli COMPANY.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()Wykonanie powyższego programu da następujący wynik.
Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0
ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfullyOperacja USUŃ
Poniższy kod w Pythonie pokazuje, jak użyć instrukcji DELETE, aby usunąć dowolny rekord, a następnie pobrać i wyświetlić pozostałe rekordy z tabeli COMPANY.
#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Opened database successfully";
conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
print "ID = ", row[0]
print "NAME = ", row[1]
print "ADDRESS = ", row[2]
print "SALARY = ", row[3], "\n"
print "Operation done successfully";
conn.close()Wykonanie powyższego programu da następujący wynik.
Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0
ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0
ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0
Operation done successfully