SQLitequerytutorial

SQLitequeryoverview

query is SQLitein最常用 operation之一, 用于 from datalibraryinretrievedata. SQLitesupport标准 SQLquery语句, includingSELECT语句, 条件query, sort, group and aggregatefunctionetc.. MasterSQLitequerytechniques for 于dataanalysis and applicationDevelopment至关 important .

SQLitequery basic组成部分

SELECT: 指定要retrieve 列
FROM: 指定要query 表
WHERE: 指定query条件
ORDER BY: 指定sort方式
GROUP BY: 指定group方式
HAVING: 指定group after 筛选条件
LIMIT: 限制返回行数

basicSELECT语句

basic SELECT语句用于 from 一个 or many 个表inretrievedata.

语法

                    -- 选择所 has 列
SELECT * FROM table_name;

-- 选择specific列
SELECT column1, column2, ... FROM table_name;

-- 给列起别名
SELECT column1 AS alias1, column2 AS alias2 FROM table_name;

-- example
SELECT * FROM users;
SELECT id, name, email FROM users;
SELECT name AS username, email FROM users;
                

usingDISTINCT去除重复值

                    -- 去除重复值
SELECT DISTINCT column1 FROM table_name;

-- example
SELECT DISTINCT department FROM employees;
                

条件query (WHERE子句)

WHERE子句用于指定query条件, 只返回满足条件行.

basic语法

                    SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- example
SELECT * FROM users WHERE age > 18;
SELECT * FROM products WHERE price < 100;
SELECT * FROM orders WHERE status = 'completed';
                

常用比较运算符

运算符	describes	example
=	etc.于	age = 25
!=, <>	不etc.于	status != 'active'
>	big 于	price > 100
<	small 于	quantity < 10
>=	big 于etc.于	score >= 90
<=	small 于etc.于	age <= 65

逻辑运算符

                    -- AND运算符: 同时满足 many 个条件
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- OR运算符: 满足任一条件
SELECT * FROM products WHERE category = 'electronics' OR price < 50;

-- NOT运算符: 取反条件
SELECT * FROM users WHERE NOT status = 'active';

-- 组合using
SELECT * FROM employees WHERE (department = 'sales' OR department = 'marketing') AND salary > 5000;
                

特殊条件

                    -- 范围query (BETWEEN) 
SELECT * FROM products WHERE price BETWEEN 100 AND 200;

-- listquery (IN) 
SELECT * FROM users WHERE country IN ('China', 'USA', 'Japan');

-- nullquery (IS NULL) 
SELECT * FROM users WHERE email IS NULL;

-- 非空query (IS NOT NULL) 
SELECT * FROM users WHERE email IS NOT NULL;

-- 模糊query (LIKE) 
SELECT * FROM users WHERE name LIKE '张%';  -- 以"张"开头
SELECT * FROM users WHERE email LIKE '%@example.com';  -- 以"@example.com"结尾
SELECT * FROM users WHERE name LIKE '%三%';  -- package含"三"
                

sort (ORDER BY子句)

ORDER BY子句用于 for query结果forsort.

basic语法

                    SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

-- example
SELECT * FROM users ORDER BY name;  -- 默认升序
SELECT * FROM products ORDER BY price DESC;  -- 降序
SELECT * FROM employees ORDER BY department ASC, salary DESC;  --  many 列sort
                

group (GROUP BY子句)

GROUP BY子句用于将datagroup, 通常 and aggregatefunction一起using.

basic语法

                    SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

-- example
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

SELECT category, AVG(price) AS average_price
FROM products
GROUP BY category;

SELECT country, MAX(salary) AS highest_salary
FROM employees
GROUP BY country;
                

常用aggregatefunction

function	describes	example
COUNT()	计数	COUNT(*)
SUM()	求 and	SUM(salary)
AVG()	平均值	AVG(score)
MAX()	最 big 值	MAX(price)
MIN()	最 small 值	MIN(age)

usingHAVING筛选group

                    -- HAVING用于筛选group after  结果
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING employee_count > 5;

-- example: find平均工资 big 于5000 部门
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING average_salary > 5000
ORDER BY average_salary DESC;
                

限制结果集 (LIMIT and OFFSET)

LIMIT and OFFSET用于限制返回行数 and 指定起始位置, 常用于分页query.

basic语法

                    -- 限制返回 行数
SELECT column1, column2, ...
FROM table_name
LIMIT number;

-- 限制返回 行数并指定起始位置
SELECT column1, column2, ...
FROM table_name
LIMIT number OFFSET offset;

-- example
SELECT * FROM users LIMIT 10;  -- 返回 before 10行
SELECT * FROM users LIMIT 10 OFFSET 20;  --  from 第21行开始, 返回10行

-- 分页queryexample (每页10条) 
-- 第1页: LIMIT 10 OFFSET 0
-- 第2页: LIMIT 10 OFFSET 10
-- 第3页: LIMIT 10 OFFSET 20
                

表连接 (JOIN)

表连接用于 from many 个表inretrievedata, 根据表之间 relationships将它们连接起来.

INNER JOIN ( in 连接)

                    --  in 连接: 只返回两个表in匹配 行
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- example: query员工及其部门information
SELECT employees.name, employees.position, departments.name AS department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
                

LEFT JOIN ( left 连接)

                    --  left 连接: 返回 left 表in 所 has 行, 以及 right 表in匹配 行
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- example: query所 has 员工及其部门information (including没 has 部门 员工) 
SELECT employees.name, employees.position, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
                

RIGHT JOIN ( right 连接)

                    --  right 连接: 返回 right 表in 所 has 行, 以及 left 表in匹配 行
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- example: query所 has 部门及其员工information (including没 has 员工 部门) 
SELECT employees.name, employees.position, departments.name AS department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
                

子query

子query is 嵌套 in otherqueryin query, 用于 complex dataretrieve.

serving as条件子query

                    -- 子queryserving as条件
SELECT column1, column2, ...
FROM table_name
WHERE column IN (SELECT column FROM another_table WHERE condition);

-- example: query工资 high 于平均工资 员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
                

serving as表子query

                    -- 子queryserving as表
SELECT column1, column2, ...
FROM (SELECT column1, column2, ... FROM table_name WHERE condition) AS alias;

-- example: query每个部门 平均工资, 并按平均工资sort
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;
                

实践case: 销售dataanalysis

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

caserequirements

fake设 has 以 under 表structure:

products: 产品表 (id, name, category, price)
orders: 订单表 (id, customer_id, order_date, total_amount)
order_items: 订单详情表 (id, order_id, product_id, quantity, unit_price)
customers: 客户表 (id, name, email, country)

query练习

                    -- 1. query所 has 产品及其class别
SELECT * FROM products;

-- 2. query价格 big 于100 电子产品
SELECT * FROM products WHERE category = 'electronics' AND price > 100;

-- 3. query每个class别 产品数量 and 平均价格
SELECT category, COUNT(*) AS product_count, AVG(price) AS average_price
FROM products
GROUP BY category;

-- 4. query2023年 所 has 订单
SELECT * FROM orders WHERE order_date LIKE '2023%';

-- 5. query每个客户 订单总数 and 总consume金额
SELECT customers.name, COUNT(orders.id) AS order_count, SUM(orders.total_amount) AS total_spent
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent DESC;

-- 6. query销量最 high   before 5个产品
SELECT products.name, SUM(order_items.quantity) AS total_quantity
FROM products
INNER JOIN order_items ON products.id = order_items.product_id
GROUP BY products.id
ORDER BY total_quantity DESC
LIMIT 5;

-- 7. query每个国家 客户数量 and 平均订单金额
SELECT customers.country, COUNT(DISTINCT customers.id) AS customer_count, AVG(orders.total_amount) AS average_order_amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.country
HAVING customer_count > 1
ORDER BY customer_count DESC;
                

互动练习

请completion以 under query练习:

query所 has 年龄 in 20-30之间 user
query姓"王" user数量
query每个部门最 high 工资 and 最 low 工资
query销量 big 于100 产品及其class别
query2023年第二季度 (4-6月) 订单总数
query没 has under 过订单客户
query每个月销售总额, 并按月份sort

提示: using合适 query语句 and 条件.

本课时summarized

in 本课时in, 我们Learning了SQLite querytechniques:

usingSELECT语句retrievedata, including选择specific列 and using别名
usingWHERE子句设置query条件, including比较运算符, 逻辑运算符 and 特殊条件
usingORDER BY子句 for 结果forsort
usingGROUP BY子句 for dataforgroup, 并 and aggregatefunction一起using
usingLIMIT and OFFSET限制结果集, 用于分页query
usingJOIN from many 个表inretrievedata, including in 连接, left 连接 and right 连接
using子queryprocessing complex queryrequirements
throughpracticalcase练习了各种querytechniques

SQLitequeryoverview

SQLitequery basic组成部分

basicSELECT语句

语法

usingDISTINCT去除重复值

条件query (WHERE子句)

basic语法

常用比较运算符

逻辑运算符

特殊条件

sort (ORDER BY子句)

basic语法

group (GROUP BY子句)

basic语法

常用aggregatefunction

usingHAVING筛选group

限制结果集 (LIMIT and OFFSET)

basic语法

表连接 (JOIN)

INNER JOIN ( in 连接)

LEFT JOIN ( left 连接)

RIGHT JOIN ( right 连接)

子query

serving as条件子query

serving as表子query

实践case: 销售dataanalysis

caserequirements

query练习

互动练习

本课时summarized

推荐tutorial

tutorial导航

coreknowledge点

SQLitequeryoverview

SQLitequery basic组成部分

basicSELECT语句

语法

usingDISTINCT去除重复值

条件query (WHERE子句)

basic语法

常用比较运算符

逻辑运算符

特殊条件

sort (ORDER BY子句)

basic语法

group (GROUP BY子句)

basic语法

常用aggregatefunction

usingHAVING筛选group

限制结果集 (LIMIT and OFFSET)

basic语法

表连接 (JOIN)

INNER JOIN ( in 连接)

LEFT JOIN ( left 连接)

RIGHT JOIN ( right 连接)

子query

serving as条件 子query

serving as表 子query

实践case: 销售dataanalysis

caserequirements

query练习

互动练习

本课时summarized

推荐tutorial

tutorial导航

coreknowledge点

serving as条件子query

serving as表子query