Skip to content

helper_SQLite.md

Latest commit

 

History

History
executable file
·
338 lines (255 loc) · 8.61 KB

helper_SQLite.md

File metadata and controls

executable file
·
338 lines (255 loc) · 8.61 KB

SQLite

GitHub license GitHub stars GitHub forks GitHub issues GitHub watchers

1. Overview

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.

1.1. Install SQLite 3

$ sudo apt update
$ sudo apt install sqlite3
$ sqlite3 --version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1

1.2. Installing SQLite browser

$ sudo apt install sqlitebrowser
$ sqlitebrowser -v
This is DB Browser for SQLite version 3.11.2.

2. sqlite3

Test DB: Lanka.db

$ sqlite3 Lanka.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite>

A. List database

sqlite> .databases
main: /work/codebase/Lanka.db

B. Quit

sqlite> .quit

C. Attributes

# 顯示表格欄位名稱
sqlite> .header on
# 以欄位表示
sqlite> .mode column
# 設定欄位寬度
sqlite> .width 10 20 30 40

D. SQL command

結束符號 ;

sqlite> SELECT * FROM Music;
No One You Know|Howdy|Somewhat Famous|4
Acme Band|Happy Day|Songs About Life|4
Acme Band|PartiQL Rocks|Another Album Title|4
No One You Know|Call Me Today|Somewhat Famous|4
Lanka|Lanka|Album123|4

E. Dump / Export database

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "Music" (
        "Artist"        TEXT NOT NULL,
        "SongTitle"     TEXT NOT NULL,
        "AlbumTitle"    TEXT NOT NULL,
        "Awards"        TEXT NOT NULL,
        PRIMARY KEY("Artist","SongTitle")
);
INSERT INTO Music VALUES('No One You Know','Howdy','Somewhat Famous','4');
INSERT INTO Music VALUES('Acme Band','Happy Day','Songs About Life','4');
INSERT INTO Music VALUES('Acme Band','PartiQL Rocks','Another Album Title','4'                          );
INSERT INTO Music VALUES('No One You Know','Call Me Today','Somewhat Famous','                          4');
INSERT INTO Music VALUES('Lanka','Lanka','Album123','4');
COMMIT;
sqlite>
$ sqlite3 Lanka.db .dump > Lanka.sql

F. Import database

$ sqlite3 Lanka.db < Lanka.sql

G. List table(s)

sqlite> .tables
Music

H. Describe table

sqlite> .schema Music
CREATE TABLE IF NOT EXISTS "Music" (
        "Artist"        TEXT NOT NULL,
        "SongTitle"     TEXT NOT NULL,
        "AlbumTitle"    TEXT NOT NULL,
        "Awards"        TEXT NOT NULL,
        PRIMARY KEY("Artist","SongTitle")
);

3. sqlitebrowser

$ sqlitebrowser

A. New Database

SQLite30001

SQLite30002

B. New Table

NN: Not Null 非空 PK: Primary Key 主鍵 AI: Auto Increment 自增 U: Unique 不重複

SQLite30003

C. New Record

SQLite30004

D. New Record(s)

SQLite30005

4. Execute SQL

4.1. CREATE

CREATE TABLE "Music" (
	"Artist"	TEXT NOT NULL,
	"SongTitle"	TEXT NOT NULL,
	"AlbumTitle"	TEXT NOT NULL,
	"Awards"	TEXT NOT NULL,
	"Price"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("Artist","SongTitle")
);

4.2. SELECT

SQLite30006

A. All

SELECT * FROM Music;

# WHERE [] LIKE '%Some%'
# WHERE [] LIKE '_omewha_%'
SELECT * FROM Music WHERE AlbumTitle LIKE '%Some%';
SELECT * FROM Music WHERE AlbumTitle LIKE '_omewha_%';

# LIMIT and OFFSET
SELECT * FROM Music LIMIT 3;

# OFFSET 起始資料，0~N
SELECT * FROM Music LIMIT 3 OFFSET 2;

B. ORDER BY ASC / DESC

SELECT * FROM Music ORDER BY Awards ASC;
SELECT * FROM Music ORDER BY Awards DESC;

C. GROUP BY

# SUM/MAX/MIN
SELECT Artist,SUM(Price) FROM Music GROUP BY Artist;

SELECT Artist,MIN(Price) FROM Music GROUP BY Artist HAVING Price>=110;

4.3. INSERT

SQLite30007

INSERT INTO Music (Artist,SongTitle,AlbumTitle,Awards,Price)
VALUES('Lanka', 'Lanka', 'Album123', '3', 99);

4.4. UPDATE

SQLite30009

UPDATE Music SET Awards='4' WHERE Artist='Lanka' and SongTitle='Lanka';

4.5. DELETE

SQLite30008

DELETE FROM Music
WHERE Artist='Lanka' and SongTitle='Lanka';

4.6. DROP

DROP TABLE Music;

Appendix

I. Study

I.1. SQLite

I.2. Datatypes In SQLite

I.3. SQL 教學 - SQL 語法教學 Tutorial

I.4. sqlite3 --- SQLite 資料庫的 DB-API 2.0 介面

II. Debug

III. Glossary

IV. Tool Usage

IV.1. sqlite3 Usage

$ sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -deserialize         open the database using sqlite3_deserialize()
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -maxsize N           maximum size for a --deserialize database
   -memtrace            trace all memory allocations and deallocations
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nofollow            refuse to open symbolic links to database files
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -quote               set output mode to 'quote'
   -readonly            open the database read-only
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
   -zip                 open the file as a ZIP Archive

IV.2. sqlitebrowser Usage

$ sqlitebrowser  --help
Usage: sqlitebrowser [options] [db]

Possible command line arguments:
  -h, --help            Show command line options
  -q, --quit            Exit application after running scripts
  -s, --sql [file]      Execute this SQL file after opening the DB
  -t, --table [table]   Browse this table after opening the DB
  -R, --read-only       Open database in read-only mode
  -o, --option [group/setting=value]    Run application with this setting temporarily set to value
  -v, --version         Display the current version
  [file]                Open this SQLite database

Author

Created and designed by Lanka Hsu.

License

HelperX is available under the BSD-3-Clause license. See the LICENSE file for more info.