SQLite表operationtutorial

SQLite表operationoverview

表 is SQLitedatalibraryinstoredata basicstructure, class似于电子表格, 由行 and 列组成. in SQLitein, 表operation is datalibrarymanagement core部分, includingcreation表, modify表structure and delete表etc.operation.

SQLite表operation basiccommands

CREATE TABLE: creation new 表
ALTER TABLE: modify现 has 表structure
DROP TABLE: delete表
CREATE TABLE AS: 基于query结果creation表

creation表 (CREATE TABLE)

creation表 is usingSQLite Step 1, 需要指定表名, 列名, dataclass型 and 约束条件.

basic语法

                    CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    columnN datatype [constraints]
);

-- example: creationuser表
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
                

常用约束条件

约束	describes	example
PRIMARY KEY	主键约束, 唯一标识表in 行	id INTEGER PRIMARY KEY
UNIQUE	唯一约束, 确保列值唯一	email TEXT UNIQUE
NOT NULL	非空约束, 确保列值不 for 空	name TEXT NOT NULL
DEFAULT	默认值约束, for 列设置默认值	created_at DATETIME DEFAULT CURRENT_TIMESTAMP
CHECK	check约束, 确保列值满足条件	age INTEGER CHECK (age >= 0)

creation表 best practices

usingIF NOT EXISTS避免表已存 in error
for 每个表设置主键
using has 意义列名
for 列选择合适 dataclass型
根据需要添加适当约束条件

modify表structure (ALTER TABLE)

in SQLitein, ALTER TABLEcommands用于modify现 has 表 structure, includingrename表, 添加列, rename列etc.operation.

SQLitesupport ALTER TABLEoperation

rename表
添加 new 列
rename列 (SQLite 3.25.0+)
添加/delete表约束

rename表

                    -- 语法
ALTER TABLE old_table_name RENAME TO new_table_name;

-- example
ALTER TABLE users RENAME TO customers;
                

添加 new 列

                    -- 语法
ALTER TABLE table_name ADD COLUMN column_name datatype [constraints];

-- example
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD COLUMN address TEXT DEFAULT '';
                

rename列

                    -- 语法 (SQLite 3.25.0+) 
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

-- example
ALTER TABLE users RENAME COLUMN email TO email_address;
                

添加表约束

                    -- 添加UNIQUE约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加CHECK约束
ALTER TABLE users ADD CHECK (age >= 18);
                

modify表structure 限制

SQLite ALTER TABLEcommands has 一些限制:

不能直接modify现 has 列 dataclass型
不能直接delete列
不能直接modify列约束条件
不能直接modify列默认值

绕过限制 method

for 于SQLite不support 表structuremodifyoperation, 可以using以 under method:

                    -- 1. creation new 表
CREATE TABLE new_table (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER CHECK (age >= 0),
    phone TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. copydata
INSERT INTO new_table (id, name, email, age, created_at) 
SELECT id, name, email, age, created_at FROM old_table;

-- 3. delete old 表
DROP TABLE old_table;

-- 4. rename new 表
ALTER TABLE new_table RENAME TO old_table;
                

delete表 (DROP TABLE)

delete表 is a dangerous operation, 会永久delete表structure and 所 has data.

basic语法

                    -- 语法
DROP TABLE [IF EXISTS] table_name;

-- example
DROP TABLE IF EXISTS users;
                

delete表 Notes

delete表会永久delete所 has data, 无法restore
usingIF EXISTS避免表不存 in error
delete表会同时delete and 该表相关 index, 触发器 and 视graph
in delete表之 before , 应该backup important data

基于querycreation表 (CREATE TABLE AS)

CREATE TABLE AScommands用于基于query结果creation new 表, 这 is a fast 速copy表structure and data method.

basic语法

                    -- 语法
CREATE TABLE new_table AS
SELECT column1, column2, ...
FROM existing_table
[WHERE condition];

-- example1: copy整个表
CREATE TABLE users_backup AS
SELECT * FROM users;

-- example2: 基于条件copydata
CREATE TABLE active_users AS
SELECT * FROM users
WHERE status = 'active';

-- example3: 只copy表structure, 不copydata
CREATE TABLE users_empty AS
SELECT * FROM users
WHERE 0;
                

CREATE TABLE AS 特点

会copyquery结果列structure and dataclass型
不会copy原始表约束条件 (such as主键, 唯一约束etc.)
不会copy原始表 index
is a fast 速backup表data method

查看表information

in SQLitein, 可以using以 under method查看表 structureinformation:

using.schemacommands

                    -- 查看所 has 表 creation语句
.schema

-- 查看指定表 creation语句
.schema table_name

-- example
.schema users
                

usingPRAGMAcommands

                    -- 查看表 列information
PRAGMA table_info(table_name);

-- example
PRAGMA table_info(users);
                

querySQLitesystem表

                    -- 查看所 has 表名
SELECT name FROM sqlite_master WHERE type='table';

-- 查看表 creation语句
SELECT sql FROM sqlite_master WHERE type='table' AND name='users';
                

实践case: creation and management员工表

现 in 让我们through一个practicalcase来练习SQLite表operation.

caserequirements

creation一个员工managementsystem datalibrary, package含以 under 表:

员工表 (employees) : store员工basicinformation
部门表 (departments) : store部门information
职位表 (positions) : store职位information

solution

                    -- 1. creation部门表
CREATE TABLE IF NOT EXISTS departments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- 2. creation职位表
CREATE TABLE IF NOT EXISTS positions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL UNIQUE,
    salary_min REAL,
    salary_max REAL
);

-- 3. creation员工表
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    department_id INTEGER,
    position_id INTEGER,
    hire_date DATE,
    salary REAL,
    FOREIGN KEY (department_id) REFERENCES departments(id),
    FOREIGN KEY (position_id) REFERENCES positions(id)
);

-- 4. modify员工表, 添加status列
ALTER TABLE employees ADD COLUMN status TEXT DEFAULT 'active';

-- 5. rename职位表 for job_positions
ALTER TABLE positions RENAME TO job_positions;

-- 6. creation员工backup表
CREATE TABLE employees_backup AS
SELECT * FROM employees;

-- 7. 查看表structure
.schema departments
.schema job_positions
.schema employees

-- 8. 查看列information
PRAGMA table_info(employees);
                

互动练习

请completion以 under 练习:

creation一个学生表 (students) , package含id, 姓名, 年龄, 性别, 班级etc.字段
creation一个课程表 (courses) , package含id, 课程名称, 学分, 教师etc.字段
creation一个选课表 (enrollments) , package含学生id, 课程id, 成绩etc.字段
modify学生表, 添加邮箱字段
rename课程表 for classes
creation一个优秀学生表, package含成绩 big 于85 学生information
delete临时表 (such as果 has )

提示: using合适 dataclass型 and 约束条件.

本课时summarized

in 本课时in, 我们Learning了SQLite 表operation:

usingCREATE TABLEcreation new 表, including指定列名, dataclass型 and 约束条件
usingALTER TABLEmodify表structure, includingrename表, 添加列, rename列etc.operation
usingDROP TABLEdelete表, 注意这 is a dangerous operation
usingCREATE TABLE AS基于query结果creation new 表
using.schema, PRAGMA and system表查看表information
Understand了SQLite表operation 限制 and 绕过method

SQLite表operationtutorial

SQLite表operationoverview

SQLite表operation basiccommands

creation表 (CREATE TABLE)

basic语法

常用约束条件

creation表 best practices

modify表structure (ALTER TABLE)

SQLitesupport ALTER TABLEoperation

rename表

添加 new 列

rename列

添加表约束

modify表structure 限制

绕过限制 method

delete表 (DROP TABLE)

basic语法

delete表 Notes

基于querycreation表 (CREATE TABLE AS)

basic语法

CREATE TABLE AS 特点

查看表information

using.schemacommands

usingPRAGMAcommands

querySQLitesystem表

实践case: creation and management员工表

caserequirements

solution

互动练习

本课时summarized

推荐tutorial

tutorial导航

coreknowledge点