1. SQLiteIntroduction
SQLite is 一款轻量级 嵌入式relationships型datalibrary, 它不需要单独 serverprocess, 而 is 直接读取 and 写入普通 diskfile. SQLite由D. Richard Hipp于2000年creation, 现 in 已经成 for 世界 on using最广泛 datalibrary引擎之一.
1.1 SQLite 特点
- 轻量级: corelibrary big small 不 to 1MB, 适合嵌入式设备
- 零configuration: 不需要installation, configuration or managementserver
- 单一file: 整个datalibrarystore in 单个filein, 便于backup and 移植
- 跨平台: supportWindows, macOS, Linuxetc. many 种operationsystem
- transactionsupport: 完全supportACIDfeatures transaction
- SQL标准: support big 部分SQL92标准
- high performance: for 于in small 型application, performance优异
- reliability: adopts原子submitting and rollbackmechanism, 确保datasecurity
1.2 SQLite application场景
- moveapplication: Android and iOSapplication in 置datalibrary
- 桌面软件: 本地datastore and configurationmanagement
- 嵌入式设备: IoT设备, 智能家电etc.
- Webapplication: small 型Webapplication datalibrarysolution
- dataanalysis: 临时dataprocessing and analysis
- testenvironment: Development and testin datalibrary替代品
2. installationconfiguration
SQLite installation非常 simple , 因 for 它 is a 嵌入式datalibrary, 不需要installation独立 server软件.
2.1 Windowssysteminstallation
- 访问SQLite官方网站: https://www.sqlite.org/download.html
- under 载预编译 二进制file:
- for 于32位system: sqlite-tools-win32-x86-*.zip
- for 于64位system: sqlite-tools-win64-x64-*.zip
- 解压 under 载 zipfile to 任意Table of Contents, 例such as: C:\sqlite
- 将该Table of Contents添加 to systemenvironmentvariablePATHin, 以便 in commands行in直接usingsqlite3commands
- verificationinstallation: 打开commands提示符, 输入
sqlite3, such as果看 to SQLiteversioninformation, 则installation成功
2.2 macOSsysteminstallation
macOSsystem默认已经installation了SQLite. 您可以through以 under commandsverification:
$ sqlite3 --version
such as果需要update to 最 new version, 可以usingHomebrewinstallation:
$ brew install sqlite
2.3 Linuxsysteminstallation
big many 数Linux发行版默认已经installation了SQLite. 您可以through以 under commandsverification:
$ sqlite3 --version
such as果需要installation, 可以usingpackagemanagement器:
# Ubuntu/Debian $ sudo apt-get install sqlite3 # CentOS/RHEL $ sudo yum install sqlite3
2.4 programminglanguage集成
SQLite可以easily集成 to 各种programminglanguagein:
// Pythonexample
import sqlite3
# 连接 to datalibrary (such as果不存 in 则creation)
conn = sqlite3.connect('example.db')
# creation游标
c = conn.cursor()
# 执行SQL语句
c.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')
# submitting更改
conn.submitting()
# 关闭连接
conn.close()
3. basicconcepts
UnderstandSQLite basicconcepts, for 于 after 续 Learning非常 important .
3.1 datalibraryfile
SQLitedatalibrarystore in 单个filein, file名通常以.db, .sqlite or .sqlite3 for scale名. 这个filepackage含了所 has 表, index, 视graph and 触发器etc.datalibraryobject.
3.2 dataclass型
SQLitesupport以 under basicdataclass型:
- NULL: null
- INTEGER: 整数, store for 1, 2, 3, 4, 6 or 8字节 has 符号整数
- REAL: 浮点数, store for 8字节 IEEE浮点数
- TEXT: 文本string, usingUTF-8, UTF-16BE or UTF-16LE编码
- BLOB: 二进制 big object, store for 原始字节
提示: SQLiteusing动态class型system, 称 for "class型亲 and 性". 这意味着列可以store任何class型 data, 而不仅仅 is 声明 class型.
3.3 SQL语句
SQLitesupport以 under class型 SQL语句:
- data定义language(DDL): CREATE, ALTER, DROPetc.
- data操纵language(DML): INSERT, UPDATE, DELETEetc.
- dataquerylanguage(DQL): SELECTetc.
- transaction控制language(TCL): BEGIN, COMMIT, ROLLBACKetc.
4. basicoperation
现 in 我们来LearningSQLite basicoperation, includingcreationdatalibrary, creation表, 插入data, querydataetc..
4.1 commands行operation
usingsqlite3commands行tooloperationSQLitedatalibrary:
// 1. creation or 打开datalibrary
$ sqlite3 example.db
// 2. creation表
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
// 3. 插入data
sqlite> INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');
sqlite> INSERT INTO users (name, email) VALUES ('李四', 'lisi@example.com');
// 4. querydata
sqlite> SELECT * FROM users;
1|张三|zhangsan@example.com
2|李四|lisi@example.com
// 5. updatedata
sqlite> UPDATE users SET email='zhangsan_new@example.com' WHERE id=1;
// 6. deletedata
sqlite> DELETE FROM users WHERE id=2;
// 7. 退出
sqlite> .quit
4.2 常用commands
SQLitecommands行tool 常用点commands:
.help: 显示helpinginformation.tables: 显示所 has 表.schema: 显示表structure.dump: exportdatalibrary for SQLfile.read: 执行SQLfile.exitor.quit: 退出
5. 实践case: creation个人通讯录
现 in 我们来creation一个 simple 个人通讯录application, usingSQLitestore联系人information.
5.1 requirementsanalysis
- store联系人 姓名, 电话, 邮箱 and 地址
- support添加, query, update and delete联系人
- support按姓名搜索联系人
5.2 implementation步骤
// 1. creationdatalibrary and 表
$ sqlite3 contacts.db
// 2. creationcontacts表
sqlite> CREATE TABLE contacts (
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> name TEXT NOT NULL,
...> phone TEXT,
...> email TEXT,
...> address TEXT
...> );
// 3. 添加联系人
sqlite> INSERT INTO contacts (name, phone, email, address) VALUES ('张三', '13800138001', 'zhangsan@example.com', '北京市朝阳区');
sqlite> INSERT INTO contacts (name, phone, email, address) VALUES ('李四', '13900139001', 'lisi@example.com', ' on 海市浦东 new 区');
sqlite> INSERT INTO contacts (name, phone, email, address) VALUES ('王五', '13700137001', 'wangwu@example.com', '广州市天河区');
// 4. query所 has 联系人
sqlite> SELECT * FROM contacts;
// 5. 按姓名搜索
sqlite> SELECT * FROM contacts WHERE name LIKE '%张%';
// 6. update联系人information
sqlite> UPDATE contacts SET phone='13800138002' WHERE id=1;
// 7. delete联系人
sqlite> DELETE FROM contacts WHERE id=3;
// 8. exportdatalibrary
sqlite> .dump > contacts_backup.sql
// 9. 退出
sqlite> .quit
6. 互动练习
issues1: SQLite core特点 is what？
A. 需要独立 serverprocess
B. 轻量级, 零configuration
C. store in many 个filein
D. 只supportWindows平台
issues2: SQLitesupport哪些dataclass型？
A. INTEGER, FLOAT, STRING, BOOL
B. NULL, INTEGER, REAL, TEXT, BLOB
C. INT, FLOAT, VARCHAR, BOOLEAN
D. NUMBER, TEXT, DATE, TIME
issues3: in SQLitecommands行in, such as何查看所 has 表？
A. SHOW TABLES;
B. LIST TABLES;
C. .tables
D. SELECT TABLES;
issues4: SQLitedatalibrarystore in what地方？
A. memoryin
B. 单个filein
C. many 个filein
D. systemregister表in