SQLitedataclass型详解

SQLitedataclass型overview

SQLite is a轻量级嵌入式datalibrary, 它 dataclass型system and 传统relationships型datalibrary has 所不同. SQLiteusing动态class型system, 允许 in 同一个列instore不同class型 data. understandingSQLite dataclass型 for 于正确usingSQLite至关 important .

SQLitedataclass型特点

动态class型system: 列不强制要求specificdataclass型
storeclass: 将data分 for 几种basicstoreclass型
class型亲 and 性: 列会倾向于usingspecific storeclass
class型转换: in 需要时自动forclass型转换

SQLitestoreclass

SQLiteusingstoreclass (Storage Classes) 来表示data class型. storeclass is 比dataclass型更common classification方式. SQLitesupport以 under 五种storeclass:

storeclass	describes	example值
NULL	表示null	NULL
INTEGER	has 符号整数, 可变 long 度	123, -456, 0
REAL	浮点数, store for 8字节IEEE浮点数	3.14, -2.5, 0.0
TEXT	文本string, usingUTF-8, UTF-16BE or UTF-16LE编码	'Hello', 'SQLite'
BLOB	二进制 big object, store for 原始字节	graph片, 音频etc.二进制data

SQLiteclass型亲 and 性

SQLite class型亲 and 性 (Type Affinity) is 指列倾向于storespecificclass型 data. 当creation表时, 指定列class型会被map to 相应亲 and 性class型.

class型亲 and 性规则

SQLite根据列声明class型, 按照以 under 规则确定亲 and 性:

                    1. such as果声明class型package含 "INT", 则亲 and 性 for  INTEGER

such as果声明class型package含 "CHAR", "CLOB"  or  "TEXT", 则亲 and 性 for  TEXT

such as果声明class型package含 "BLOB", 则亲 and 性 for  BLOB

such as果声明class型package含 "REAL", "FLOA"  or  "DOUB", 则亲 and 性 for  REAL

否则, 亲 and 性 for  NUMERIC

commonclass型map

声明class型	亲 and 性
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT	INTEGER
CHAR, VARCHAR, TEXT	TEXT
BLOB	BLOB
REAL, DOUBLE, FLOAT	REAL
NUMERIC, DECIMAL, BOOLEAN, DATE, DATETIME	NUMERIC

SQLitesupport commondataclass型

虽然SQLiteusingstoreclass and class型亲 and 性, 但 in creation表时, 你仍然可以usingcommon SQLdataclass型名称. 以 under is SQLitein常用 dataclass型:

数值class型

INTEGER: 整数class型, 可store不同 big small 整数
REAL: 浮点数class型
NUMERIC: 数值class型, 可store整数, 浮点数 and boolean值

stringclass型

TEXT: 文本string
CHAR(n): 固定 long 度string
VARCHAR(n): 可变 long 度string

二进制class型

BLOB: 二进制data

日期时间class型

DATE: 日期class型
DATETIME: 日期时间class型
TIME: 时间class型

SQLitedataclass型usingexample

以 under is 一些SQLitedataclass型 usingexample:

creation表时指定dataclass型

                    CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email VARCHAR(255),
    balance REAL,
    created_at DATETIME,
    profile_picture BLOB
);
                

插入不同class型 data

                    -- 插入正常data
INSERT INTO users (name, age, email, balance, created_at) 
VALUES ('张三', 25, 'zhangsan@example.com', 1000.50, '2023-01-01 10:00:00');

-- 插入不同class型 data (SQLite允许) 
INSERT INTO users (name, age, email, balance, created_at) 
VALUES ('李四', '30', 'lisi@example.com', '2000', CURRENT_TIMESTAMP);
                

query时 dataclass型processing

                    -- 数值比较 (自动class型转换) 
SELECT * FROM users WHERE age > 25;

-- stringoperation
SELECT name, LENGTH(name) AS name_length FROM users;

-- 日期时间operation
SELECT name, created_at FROM users WHERE created_at > '2023-01-01';
                

SQLitedataclass型best practices

虽然SQLite 动态class型systemproviding了flexible性, 但遵循一些best practices可以improvingcode readable 性 and performance:

命名约定

using has 意义列名
保持列class型 consistency
for 于ID列, usingINTEGER PRIMARY KEY

performance考虑

for 于整数, usingINTEGERclass型
for 于string, usingTEXTclass型
for 于二进制data, usingBLOBclass型
避免 in 同一列instore不同class型 data

日期时间processing

usingISO 8601格式store日期时间: 'YYYY-MM-DD HH:MM:SS'
可以usingTEXTclass型store日期时间string
也可以usingINTEGERclass型storeUnix时间戳

实践case: creation学生information表

现 in 让我们through一个practicalcase来练习usingSQLitedataclass型.

caserequirements

creation一个学生information表, package含以 under 字段:

学生ID (自增)
姓名
年龄
性别
出生日期
成绩
备注 (可选)

solution

                    -- creation学生表
CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    gender TEXT,
    birth_date DATE,
    score REAL,
    remarks TEXT
);

-- 插入testdata
INSERT INTO students (name, age, gender, birth_date, score, remarks) 
VALUES 
('张三', 18, '男', '2005-01-01', 95.5, '优秀学生'),
('李四', 17, '女', '2006-02-02', 88.0, '良 good '),
('王五', 19, '男', '2004-03-03', 92.5, '优秀');

-- query学生information
SELECT * FROM students;

-- query成绩 big 于90 学生
SELECT name, score FROM students WHERE score > 90;

-- update学生information
UPDATE students SET remarks = '三 good 学生' WHERE id = 1;

-- delete学生information
DELETE FROM students WHERE id = 3;
                

互动练习

请completion以 under 练习:

creation一个员工表, package含id, 姓名, 职位, 薪资, 入职日期etc.字段
插入5条testdata
query薪资 big 于5000 员工
update某个员工职位
delete入职日期早于2020年员工

提示: using合适 dataclass型来定义各个字段.

本课时summarized

in 本课时in, 我们Learning了SQLite dataclass型system:

SQLiteusing动态class型system, 允许 in 同一列instore不同class型 data
SQLite has 五种storeclass: NULL, INTEGER, REAL, TEXT and BLOB
class型亲 and 性决定了列倾向于using storeclass
虽然SQLiteflexible, 但应该遵循best practices来保持dataconsistency
using合适 dataclass型可以improvingqueryperformance and code readable 性