SQLite视graphtutorial

SQLite视graphoverview

视graph is datalibraryin 虚拟表, 它 is 基于SQLquery结果构建 , 不storepracticaldata. 视graph可以简化 complex queryoperation, improvingdata访问 security性, 以及providingdata 逻辑独立性. in SQLitein, 视graph is a非常 has 用 datalibraryobject.

视graph basicconcepts

视graph is 基于SQLquery结果虚拟表
视graph不storepracticaldata, 只storequery定义
视graph可以简化 complex SQLquery
视graph可以限制data访问, improvingsecurity性
视graph可以providingdata 逻辑独立性

creation视graph (CREATE VIEW)

in SQLitein, 可以usingCREATE VIEW语句creation视graph.

basic语法

                    -- creation视graph
CREATE [TEMP|TEMPORARY] VIEW [IF NOT EXISTS] view_name
AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column];

-- example
-- creation simple 视graph
CREATE VIEW IF NOT EXISTS view_active_users
AS
SELECT id, name, email, created_at
FROM users
WHERE status = 'active';

-- creation带别名 视graph
CREATE VIEW IF NOT EXISTS view_user_summary (user_id, user_name, order_count)
AS
SELECT users.id, users.name, COUNT(orders.id)
FROM users
LEFT JOIN orders ON users.id = orders.customer_id
GROUP BY users.id;

-- creation临时视graph
CREATE TEMP VIEW IF NOT EXISTS view_temp_products
AS
SELECT * FROM products WHERE price > 1000;
                

视graph命名约定

using has 意义视graph名称, such asview_table_purpose
for 于 complex 视graph, 可以 in 名称inpackage含其functionsdescribes
临时视graph可以usingtemp_view_ before 缀

using视graph

视graph usingmethod and 普通表class似, 可以 in SELECT语句inusing视graph, 也可以 in JOINoperationinusing视graph.

basicusing

                    -- query视graph
SELECT * FROM view_active_users;

-- 带条件query视graph
SELECT * FROM view_active_users WHERE created_at > '2023-01-01';

-- sortquery视graph
SELECT * FROM view_user_summary ORDER BY order_count DESC;

--  in JOINinusing视graph
SELECT * FROM view_active_users
JOIN view_user_summary ON view_active_users.id = view_user_summary.user_id
WHERE view_user_summary.order_count > 5;
                

视graph 优势

简化query: 将 complex query逻辑encapsulation in 视graphin, using时只需query视graph
improvingsecurity性: 可以限制user只能访问视graphin specific列
dataconsistency: many 个application可以using同一个视graph, 确保data访问 consistency
逻辑独立性: 即使底层表structure发生变化, 只要视graph定义保持不变, application程序就不需要modify

可update视graph

in SQLitein, 某些视graph is 可update , 这意味着可以through视graphmodify底层表 data.

可update视graph 条件

视graph必须基于单个表 or 可update 视graph
视graph定义in不能package含DISTINCT, GROUP BY, HAVING, UNION or LIMIT子句
视graph定义in必须package含底层表所 has NOT NULL列 (除非 has 默认值)
视graphin 列必须直接map to 底层表列, 不能 is 表达式 or aggregatefunction

update视graphexample

                    -- creation可update视graph
CREATE VIEW IF NOT EXISTS view_users_update
AS
SELECT id, name, email, status
FROM users;

-- through视graphupdatedata
UPDATE view_users_update
SET status = 'inactive'
WHERE id = 1;

-- through视graph插入data
INSERT INTO view_users_update (name, email, status)
VALUES (' new user', 'newuser@example.com', 'active');

-- through视graphdeletedata
DELETE FROM view_users_update WHERE id = 5;
                

不可update视graph

such as果视graphpackage含以 under in 容, 则 is 不可update :

many 个表 JOINoperation
DISTINCT关键字
GROUP BY子句
HAVING子句
UNION or UNION ALLoperation
aggregatefunction (SUM, COUNT, AVGetc.)
LIMIT子句

modify视graph

in SQLitein, 可以usingCREATE OR REPLACE VIEW语句modify现 has 视graph 定义.

modify视graph语法

                    -- modify视graph
CREATE OR REPLACE VIEW view_name
AS
SELECT column1, column2, ...
FROM table_name
[WHERE condition];

-- example
-- modify现 has 视graph
CREATE OR REPLACE VIEW view_active_users
AS
SELECT id, name, email, age, created_at
FROM users
WHERE status = 'active' AND age >= 18;
                

delete视graph

in SQLitein, 可以usingDROP VIEW语句delete视graph.

delete视graph语法

                    -- delete视graph
DROP VIEW [IF EXISTS] view_name;

-- example
-- delete单个视graph
DROP VIEW IF EXISTS view_active_users;

-- delete many 个视graph
DROP VIEW IF EXISTS view_user_summary;
DROP VIEW IF EXISTS view_temp_products;
                

视graphmanagement

SQLiteproviding了一些commands来management视graph, including查看视graph定义 and 列出所 has 视graph.

查看视graph定义

                    -- 查看单个视graph 定义
SELECT sql FROM sqlite_master WHERE type='view' AND name='view_active_users';

-- 查看所 has 视graph 定义
SELECT name, sql FROM sqlite_master WHERE type='view';
                

列出所 has 视graph

                    -- 列出所 has 视graph
.tables

--  or usingSQLquery
SELECT name FROM sqlite_master WHERE type='view';
                

视graphperformance考虑

虽然视graph可以简化query, 但也需要考虑其 for performance 影响.

视graph performance特点

视graph不storedata, 每次query视graph时都会执行底层 SELECT语句
complex 视graph可能会导致performance under 降, 特别 is package含 many 个表JOIN 视graph
频繁using complex 视graph可以考虑using物化视graph (SQLite不直接support, 需要手动implementation)
视graph可以 and index结合using, improvingqueryperformance

performanceoptimization建议

只 in 视graphinpackage含必要列
避免 in 视graphinusing complex JOINoperation
for 于频繁using 视graph, 可以考虑creation相应 index
定期check and optimization视graph定义
for 于非常 complex query, 考虑usingstore过程 or application程序code来processing

视graph using场景

视graph in 以 under 场景in特别 has 用:

1. 简化 complex query

for 于经常using complex query, 可以creation视graph来简化code.

                    --  complex query
SELECT
    orders.id,
    customers.name AS customer_name,
    products.name AS product_name,
    order_items.quantity,
    order_items.unit_price,
    order_items.quantity * order_items.unit_price AS total_price
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id
WHERE orders.status = 'completed' AND orders.order_date > '2023-01-01';

-- creation视graph简化query
CREATE VIEW IF NOT EXISTS view_order_details
AS
SELECT
    orders.id,
    customers.name AS customer_name,
    products.name AS product_name,
    order_items.quantity,
    order_items.unit_price,
    order_items.quantity * order_items.unit_price AS total_price
FROM orders
JOIN customers ON orders.customer_id = customers.id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

-- using视graph
SELECT * FROM view_order_details WHERE status = 'completed' AND order_date > '2023-01-01';
                

2. improvingsecurity性

through视graph可以限制user只能访问specific 列 or 行.

                    -- creationsecurity视graph (只显示非敏感information) 
CREATE VIEW IF NOT EXISTS view_customer_public
AS
SELECT id, name, email, created_at
FROM customers;

-- creationmanagement员视graph (显示所 has information) 
CREATE VIEW IF NOT EXISTS view_customer_admin
AS
SELECT * FROM customers;
                

3. dataabstraction

视graph可以providingdata 逻辑abstraction, 隐藏底层表structure 变化.

                    -- fake设底层表structure发生变化
--  old 表: users (id, name, email)
--  new 表: users (id, first_name, last_name, email)

-- creation兼容视graph
CREATE VIEW IF NOT EXISTS view_users_compat
AS
SELECT id, first_name || ' ' || last_name AS name, email
FROM users;

-- application程序继续using old  视graphstructure
SELECT * FROM view_users_compat;
                

实践case: 销售dataanalysis视graph

现 in 让我们through一个practicalcase来练习SQLite视graph using.

caserequirements

creation一个销售dataanalysissystem, package含以 under 视graph:

销售订单概览视graph
产品销售statistics视graph
客户购买history视graph
月度销售趋势视graph

solution

                    -- 1. 销售订单概览视graph
CREATE VIEW IF NOT EXISTS view_order_overview
AS
SELECT
    orders.id AS order_id,
    customers.name AS customer_name,
    orders.order_date,
    orders.total_amount,
    orders.status
FROM orders
JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.order_date DESC;

-- 2. 产品销售statistics视graph
CREATE VIEW IF NOT EXISTS view_product_sales
AS
SELECT
    products.id AS product_id,
    products.name AS product_name,
    products.category,
    COUNT(order_items.id) AS order_count,
    SUM(order_items.quantity) AS total_quantity,
    SUM(order_items.quantity * order_items.unit_price) AS total_sales
FROM products
LEFT JOIN order_items ON products.id = order_items.product_id
GROUP BY products.id
ORDER BY total_sales DESC;

-- 3. 客户购买history视graph
CREATE VIEW IF NOT EXISTS view_customer_history
AS
SELECT
    customers.id AS customer_id,
    customers.name AS customer_name,
    COUNT(orders.id) AS order_count,
    SUM(orders.total_amount) AS total_spent,
    MIN(orders.order_date) AS first_purchase,
    MAX(orders.order_date) AS last_purchase
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent DESC;

-- 4. 月度销售趋势视graph
CREATE VIEW IF NOT EXISTS view_monthly_sales
AS
SELECT
    strftime('%Y-%m', order_date) AS month,
    COUNT(id) AS order_count,
    SUM(total_amount) AS total_sales,
    AVG(total_amount) AS average_order
FROM orders
GROUP BY month
ORDER BY month;

-- using视graphforanalysis
-- 查看销售订单概览
SELECT * FROM view_order_overview LIMIT 10;

-- 查看销售最 good  产品
SELECT * FROM view_product_sales LIMIT 5;

-- 查看购买最 many  客户
SELECT * FROM view_customer_history LIMIT 5;

-- 查看月度销售趋势
SELECT * FROM view_monthly_sales;
                

互动练习

请completion以 under 视graph练习:

creation一个学生表 (students) and 课程表 (courses)
creation一个学生课程关联表 (student_courses)
creation视graph显示学生课程registercircumstances
creation视graph显示每门课程学生人数
creation视graph显示每个学生平均成绩
test视graph updateoperation

提示: using合适表structure and 视graph定义.

本课时summarized

in 本课时in, 我们Learning了SQLite 视graphtechniques:

understanding了视graph basicconcepts and important 性
Learning了creation视graph 语法 and techniques
Master了视graph usingmethod, includingquery and updateoperation
Understand了可update视graph 条件 and 限制
Learning了modify and delete视graph operation
Master了视graphmanagement basiccommands
Understand了视graph performance考虑 and optimization建议
through销售dataanalysis case练习了视graph practicalapplication