Początek pracy z SQLite3 – podstawowe polecenia

SQLite jest biblioteką języka C, która implementuje silnik bazy danych SQL. Jest to system zarządzania relacyjną bazą danych (lub RDBMS). Większość baz danych SQL działa w modelu klient/serwer. Weźmy na przykład MySQL. Aby wprowadzić i odebrać dane z bazy danych MySQL, musisz wysłać żądanie do serwera MySQL, który po otrzymaniu go dostarczy Ci odpowiednią odpowiedź. W przeciwieństwie do MySQL, bazy danych SQLite obsługiwane są bezpośrednio z dysku. Nie ma potrzeby tworzenia zapytań do serwera.

Instalacja

Naszą bazę będziemy obsługiwać za pomocą interfejsu sqlite31 Command Line Interface(CLI). Będziemy pracować na platformie Linux. sqlite3 CLI dla MAC OS i Windows są takie same, ale dla zachowania spójności zaleca się zainstalowanie Homestead Improved – lekkiej maszyny wirtualnej uruchamianej w 5 minut, która utrzyma Twój komputer w czystości od dodatkowego, niepotrzebnego oprogramowania.

Aby zainstalować go na Homestead Improved (lub innym systemie operacyjnym opartym na Debianie, takim jak Ubuntu), wykonaj następujące polecenie:

sudo apt-get install sqlite3 libsqlite3-dev

To zainstaluje sqlite3. Aby zainstalować go na innych platformach, postępuj zgodnie z ich oficjalnymi instrukcjami. Po zainstalowaniu możemy rozpocząć sesję. Otwórz Terminal/wiersz poleceń i wpisz komendę sqlite3. Musisz zobaczyć coś w tym rodzaju :

Druga linia każe Ci wpisać .help w celu uzyskania instrukcji. Pojawia się znak zachęty czekający na wpisanie polecenia. Więc idź dalej i wpisz .help. Pojawi się lista komend Meta i ich opisy.

Meta Polecenia

Meta Polecenia są używane do definiowania formatu wyjściowego dla tabel, badania baz danych i innych operacji administracyjnych. Zawsze zaczynają się od kropki. Nawet .help jest komendą meta. Możesz przejrzeć całą listę. Oto kilka, które często się przydadzą:

Polecenie	Opis
.show	Wyświetla aktualne ustawienia różnych parametrów
.databases	Podaje nazwy baz danych i plików
.quit	Zakończ program sqlite3
.tables	Pokazuje bieżące tabele
.schema	Wyświetla schemat tabeli
.header	Wyświetla lub ukrywa nagłówek tabeli wyjściowej
.mode	Wybierz tryb dla tabeli wyjściowej
.dump	Zrzut bazy danych w formacie tekstowym SQL

Standardowe polecenia

Przejrzyjrzyjmy się standardowym poleceniom w sqlite3. Polecenia meta są wydawane w celu zbadania bazy danych. Standardowe polecenia SQL są wydawane, aby operować na bazie danych. Standardowe polecenia można podzielić na trzy grupy:

• Język definicji danych: Zapewnia strukturę przechowywania i metody dostępu do danych z systemu bazy danych.

  • CREATE
  • ALTER
  • DROP

• Język Manipulacji Danymi: Umożliwia użytkownikom manipulowanie (dodawanie/modyfikowanie/usuwanie) danymi.

  • INSERT
  • UPDATE
  • DELETE

• Język zapytań o dane: Umożliwia użytkownikom pobieranie żądanych danych z bazy danych.

  • SELECT

Uwaga: SQLite rozumie wiele innych standardowych poleceń, których listę można przeczytać tutaj. Ponieważ tutorial ten jest wprowadzeniem dla początkujących, zajmiemy się tylko wyżej wymienionymi komendami.

Bazy danych SQLite są międzyplatformowymi plikami przenośnymi. Mogą być przechowywane na różnych urządzeniach pamięci masowej i mogą być przenoszone pomiędzy różnymi komputerami.

Pracę z sqlite3 poznamy na przykładzie naszej bazy danych Comment Section. Prawie każda strona internetowa ma dziś sekcję komentarzy. Aby zamieścić komentarz, użytkownik musi wprowadzić następujące dane:

• Nazwisko
• Email
• Strona
• Komentarz

Z tych czterech, tylko adres URL strony jest opcjonalny. Musimy również zdefiniować kolumnę, która będzie numerować komentarze. Nazwijmy ją post_id.

Możemy zdefiniować datatypy kolumn (atrybuty) w następujący sposób:

Attribute	Datatype Required
post_id	INTEGER
name	TEXT
email	TEXT
website_url	TEXT
comment	TEXT

Możesz zobaczyć dokumentację dla różnych Datatypów i Klas Magazynowych dostarczanych w SQLite3.

SQLite3 używa Manifest Typing2. Większość innych silników baz danych, w tym MySQL, używa Static Typing.

Teraz stwórzmy bazę danych. Jeśli nadal jesteś w programie sqlite3, zamknij go, wpisując .quit w znaku zachęty sqlite. Następnie wydaj polecenie:

sqlite3 comment_section.db

To spowoduje utworzenie pliku bazy danych comment_section.db w bieżącym katalogu roboczym.

Uwaga: Jeśli po poleceniu nie następuje nazwa pliku, sqlite3 automatycznie tworzy tymczasową bazę danych. Tak właśnie się stało, gdy po raz pierwszy wpisaliśmy pojedyncze polecenie sqlite3.

CREATE TABLE

Aby przechowywać komentarze, musimy zdefiniować tabelę. Nazwijmy ją . Aby ją utworzyć, wprowadźmy następujące instrukcje:

CREATE TABLE comments ( post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL );

NOT NULL dbamy o to, aby dana wartość nie pozostała pusta w rekordzie. PRIMARY KEY3 i AUTOINCREMENT4 opracowują atrybut post_id.

Aby sprawdzić, czy tabela została utworzona, wydaj polecenie meta .tables. Spowoduje to wyświetlenie nazwy tabeli .

Uwaga: Aby uzyskać schemat tabeli wpisz .schema comments

Teraz, gdy tabela została utworzona, musimy wprowadzić dane.

INSERT ROWS

Załóżmy, że użytkownik wprowadził komentarz z danymi:

Name : Shivam MamgainEmail : [email protected] : shivammg.blogspot.comComment : Great tutorial for beginners. 

Aby go wstawić, używamy polecenia INSERT.

INSERT INTO comments ( name, email, website_url, comment )VALUES ( 'Shivam Mamgain', '[email protected]','shivammg.blogspot.com', 'Great tutorial for beginners.' );

Nie podałem wartości dla post_id, mimo że jest on zdefiniowany jako NOT NULL. Rzecz w tym, że nie jest ona potrzebna, ponieważ jest to pole AUTOINCREMENT.

Wprowadź jeszcze kilka wierszy do tabeli, aby poćwiczyć.

SELECT

Uczyliśmy się wstawiać dane do tabeli. Aby pobrać dane używamy polecenia SELECT.

SELECT post_id, name, email, website_url, commentFROM comments;

Powyższe polecenie można również zapisać jako:

SELECT *FROM comments;

W ten sposób zostaną pobrane wszystkie wiersze z tabeli. Wynikowa tabela może wyglądać jak obfuscated bez nagłówków i odpowiedniej separacji między kolumnami. Aby temu zaradzić, musimy zmienić niektóre parametry.

Wprowadź .show

Aby wyświetlić atrybuty kolumn, wprowadź .headers ON.
Aby wyświetlić wiersze w stylu kolumny, wpisz .mode column.
Wprowadź ponownie polecenie SELECT.

Uwaga: Wybierz tryb wyświetlania, który Ci odpowiada. Wpisz .help i wyszukaj .mode. Wyświetlą Ci się różne wartości, jakie może mieć .mode.

UPDATE

Załóżmy, że e-mail dla „Shivam Mamgain” został zmieniony na „[email protected]” . Musimy zaktualizować ten wiersz. Wydaj następujące polecenie:

UPDATE commentsSET email = '[email protected]'WHERE name = 'Shivam Mamgain';

To zmieni atrybut email dla nazwy 'Shivam Mamgain' na '[email protected]'.

Uwaga: Atrybut name nie jest unikalny, więc powyższe polecenie może mieć wpływ na więcej niż jeden wiersz. Dla każdej nazwy = 'Shivam Mamgain' email zostanie ustawiony na '[email protected]'. Aby zaktualizować konkretny wiersz, użyj atrybutu post_id. Jest on zdefiniowany jako PRIMARY KEY i zawsze będzie unikalny.

DELETE

Aby usunąć wiersz(y) z tabeli możemy użyć warunku WHERE, tak samo jak w UPDATE.

Przypuśćmy, że musimy usunąć wiersz z post_id 9. Możemy wpisać komendę:

DELETE FROM commentsWHERE post_id = 9;

Potrzebujemy usunąć wszystkie komentarze, które są napisane przez 'Barta Simpsona' i 'Homera Simpsona'. Możemy wykonać następujące zapytanie:

DELETE FROM commentsWHERE name = 'Bart Simpson' OR name = 'Homer Simpson';

ALTER

Nowe kolumny mogą być dodawane do tabeli za pomocą polecenia ALTER. W sieci, większość ludzi niekoniecznie używa swojego prawdziwego imienia i nazwiska. Zazwyczaj pracują pod pseudonimem, nazwą użytkownika. Musimy dodać kolumnę username do naszej tabeli. Poniższe zapytanie wykona to zadanie:

ALTER TABLE commentsADD COLUMN username TEXT;

To utworzy kolumnę username w i ustawi jej datatype na TEXT. Wartość username dla już wstawionych wierszy zostanie ustawiona na NULL.

Polecenie ALTER służy również do zmiany nazwy tabel. Zmieńmy tabelę na Coms.

ALTER TABLE commentsRENAME TO Coms;

DROP

Usuwanie tabeli oznacza usunięcie całej tabeli. Tabelę Coms można usunąć za pomocą następującego zapytania:

DROP TABLE Coms;

Wniosek

SQLite3 oferuje wiele zalet w porównaniu z innymi relacyjnymi bazami danych. Niektóre z jego charakterystycznych cech można przeczytać tutaj. Większość frameworków PHP oraz wiele innych frameworków internetowych w tym Django, Ruby on Rails i web2py posiada SQLite3 jako domyślną bazę danych. Lekkość SQLite3 sprawia, że jest on preferowany do lokalnego przechowywania danych w przeglądarkach internetowych. Jest również wykorzystywana jako pamięć kliencka dla wielu systemów operacyjnych, w tym Android i Windows Phone 8. Jest to jeden z najszerzej stosowanych silników baz danych.

W tutorialu zajmowaliśmy się systemem baz danych SQLite. Istnieją aplikacje GUI, które pozwalają zrobić to samo, bez konieczności uczenia się komend. Dwie takie aplikacje to DB Browser for SQLite oraz SQLiteStudio. Możesz również ćwiczyć SQL online w SQL Fiddle.

Ten przewodnik zawiera podstawy, które pozwolą Ci rozpocząć pracę z SQLite3. Mam nadzieję, że zachęci Cię do dalszych kroków. Możesz łatwo zintegrować go z PHP za pomocą wspólnej klasy PDO.