1. Toy模板网首页
  2. > Toy博客

SQL进阶（一）：SQL基础速览，以SQLite为例

10月前 作者：大地之灯 分类：Toy博客 阅读(27) 违法举报

这篇具有很好参考价值的文章主要介绍了SQL进阶（一）：SQL基础速览，以SQLite为例。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

SQL基础速览，以SQLite为例

本文是在原本sql闯关的基础上总结得来，加入了自己的理解以及疑问解答（by GPT4）

原活动链接

1. 开始之前的准备

在我们正式开始前，我们需要为此次的教学准备一个案例数据库，因此请完成以下内容的检查

用到的数据：链接
提取码：l03e

2. 创建你的数据库！

这一系列的教程中，我们将主要使用 SQLite 数据库进行学习，你可以在 SQLite 的官方网站 上找到关于这个数据库的更多信息，也可以访问 sqlite3 了解这个数据库在 Python 当中的应用。

为什么选择 SQLite ？

传统数据库（MySQL, Postgres） SQLite
配置复杂度 服务器，数据库进程，数据库用户账号密码 完全零配置，开箱即用
服务器学习成本 需要面对服务器级别的问题和知识，并非我们关注的重点 SQLite 是 Serverless 的，只需要关注数据库本身
分享与可复用 数据库由一系列文件构成，分享只能做到账号权限级别 SQLite 数据库本身是一个文件，可以像任何一个文件一样分享和储存
动态数据类型 静态类型，习惯使用 Python 的话容易在类型上踩坑 数据表的列不强制要求类型，上手容易

2.1 导入 Python 库和用到的数据集

# 导入库
from IPython.display import Markdown as md  # 用于以Markdown格式显示文本。
import sqlite3 # 一个用于与SQLite数据库交互的库。
import pandas as pd

# 读取泰坦尼克号数据集
df = pd.read_csv('./data/train.csv')

2.2 快速搭建数据库

# 只需要下面的 2 行代码，就可以完成：
# 1. 创建一个数据库
# 2. 在数据库当中创建一张表，如果这张表有同名的表格，则覆盖它
# 3. 将 pandas 的数据表中的数据写入上一步中创建好的数据表
# 4. 保存对应的数据表
# 5. 保存对应的数据库，并且将数据库存入 project 目录当中
with sqlite3.connect('example.db') as conn:
    df.to_sql('example_table',con = conn, if_exists='replace',index=False)

2.3 读取数据库

# 下面的语句可以帮助我们链接到刚刚创建好的数据库
connection = sqlite3.connect('example.db')

# 执行下面的语句就也可以从数据库当中获取数据了
# execute(): 在数据库下执行该 SQLite 查询语句
# fetchone()：获取一个查询结果，如果想要获取所有的查询结果，可以使用 fetchall()
connection.execute('''SELECT * FROM example_table;''').fetchone()

(1,
 0,
 3,
 'Braund, Mr. Owen Harris',
 'male',
 22.0,
 1,
 0,
 'A/5 21171',
 7.25,
 None,
 'S')

3. SQL 语句基础

3.1 SELECT, FROM

  • SELECT: 根据 SELECT 后的字段名，选择当前查询当中所要返回的字段，多个字段名之间通过英文 , 隔开

  • FROM: 选择需要查询的数据表

    例1
    SQL语句 SELECT user_id FROM users
    释义 从名为 users 的数据表当中，选出字段名/列名为 user_id 的那一列，并且返回那一列的所有数据
    例2
    SQL语句 SELECT user_id,user_name FROM users
    释义 从名为 users 的数据表当中，选出字段名/列名为 user_id 和 user_name 的列，并且返回这些列的所有数据

下面的代码将会从我们已经准备好的数据库当中选取一部分的数据，以下是这张表中的所有字段的名称：

'PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp',  'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked'  

# 从 example_table 中选取 PassengerId （乘客的id）和 Survived（乘客是否幸存）
query = '''
SELECT PassengerId,Survived
FROM example_table
'''
result = connection.execute(query).fetchone()
result
# 那么如果想要查看的既不是一条数据也不是全部的数据呢？

(1, 0)
🔖小贴士
你可能注意到了，在我们的代码中所有的 SQL 关键字都是大写的 (SELECT, FROM) ，这并不是 SQLite 强制我们做的，而是一种能够帮助我们更好的梳理 SQL 查询语句的结构的技巧
​

3.2 LIMIT

  • LIMIT： 仅从查询结果当中，选取一定数量的数据，选择的数据条数等于 LIMIT 关键字后的数字
例1
SQL语句 SELECT user_id FROM users LIMIT 20
释义 1. 从名为 users 的数据表当中

2. 选出字段名/列名为 user_id 的那一列

3. 并且返回那一列的前 20 条数据		 
# 有了 LIMIT 我们就能用 fetchall() 了
query = '''
SELECT PassengerId,Survived,Ticket,Fare,Age
FROM example_table
LIMIT 20
'''
result = connection.execute(query).fetchall()
result
# 现在我们能够按照需求查看我们想要的数据数量了，下一步就是根据某些特定的规则筛选我们想看的数据

[(1, 0, 'A/5 21171', 7.25, 22.0),
 (2, 1, 'PC 17599', 71.2833, 38.0),
 (3, 1, 'STON/O2. 3101282', 7.925, 26.0),
 (4, 1, '113803', 53.1, 35.0),
 (5, 0, '373450', 8.05, 35.0),
 (6, 0, '330877', 8.4583, None),
 (7, 0, '17463', 51.8625, 54.0),
 (8, 0, '349909', 21.075, 2.0),
 (9, 1, '347742', 11.1333, 27.0),
 (10, 1, '237736', 30.0708, 14.0),
 (11, 1, 'PP 9549', 16.7, 4.0),
 (12, 1, '113783', 26.55, 58.0),
 (13, 0, 'A/5. 2151', 8.05, 20.0),
 (14, 0, '347082', 31.275, 39.0),
 (15, 0, '350406', 7.8542, 14.0),
 (16, 1, '248706', 16.0, 55.0),
 (17, 0, '382652', 29.125, 2.0),
 (18, 1, '244373', 13.0, None),
 (19, 0, '345763', 18.0, 31.0),
 (20, 1, '2649', 7.225, None)]

提问：多次执行上述语句的结果相同，如果是mysql或者postgresql结果应该是随机的吧，sqlLite为什么返回的结果相同呢

回答：您提到的问题是关于从数据库中查询数据时结果的一致性和随机性。

在大多数数据库系统中（包括 MySQL 和 PostgreSQL），如果您没有指定排序条件（如使用 ORDER BY 语句），那么返回的结果集的顺序并不是保证的。这意味着，尽管在某些情况下结果看起来可能是随机的，但这并不是数据库系统的一个特性或保证。在多次查询中看到不同的顺序通常是由于数据库内部管理数据的方式，以及在查询执行过程中访问数据的顺序造成的。

SQLite 在这方面可能表现得更一致。没有指定 ORDER BY 的情况下，SQLite 通常会按照数据插入的顺序返回结果。如果您在每次查询时都看到相同的结果，这可能是因为数据是按照相同的顺序插入的，且没有进行任何修改或重新排列。

如果您希望查询结果有随机性，可以在查询中添加一个 ORDER BY RANDOM() 子句，这将使得结果以随机的顺序返回。例如：

SELECT PassengerId, Survived, Ticket, Fare, Age
FROM example_table
ORDER BY RANDOM()
LIMIT 20

这将返回随机排序的 20 条记录。如果您想看看这个查询的效果，我可以帮您执行它。

✍️ 小练习 1：获取数据集中第一个乘客的性别
# 有了 LIMIT 我们就能用 fetchall() 了
# 性别字段名为 Sex
# 用 LIMIT 1 可以获取数据集前1 行的数据
query = '''
SELECT PassengerId,Sex
FROM example_table
LIMIT 1
'''
result = connection.execute(query).fetchall()
result

[(1, 'male')]

3.3 WHERE

WHERE 关键字可以帮助我们从数据库当中根据一些特定的条件筛选出数据，其基本的结构为：

WHERE 一个 或 一组 查询条件

对于单个的查询条件，我们使用的是简单的比较：

例1
SQL语句 SELECT user_id FROM users WHERE user_id = 1
释义 1. 从名为 users 的数据表当中

2. 选出 user_id 字段的值为 1 的数据

3. 选出该条数据的字段名/列名为 user_id 的那一列

4. 并且返回那一列的所有数据
例2
SQL语句 SELECT user_id,user_name FROM users WHERE age>30
释义 1. 从名为 users 的数据表当中

2.选出 age 字段大于 30 的数据

3. 选出字段名/列名为 user_id 和 user_name 的列

4. 并且返回这些列的所有数据

如果有多个查询条件，我们可以将这些查询条件通过 and 和 or 两种逻辑链接起来：

例3
SQL语句 SELECT user_id FROM users WHERE age>30 or user_id>20
释义 1. 从名为 users 的数据表当中

2.选出 age 字段大于 30 或 user_id 大于20 的数据

3. 选出该条数据的字段名/列名为 user_id 的那一列

4. 并且返回那一列的所有数据
例4
SQL语句 SELECT user_id,user_name FROM users WHERE user_id >20 and age>30
释义 1. 从名为 users 的数据表当中

2.选出 age 字段大于 30 且 user_id 大于20 的数据

3. 选出字段名/列名为 user_id 和 user_name 的列

4. 并且返回这些列的所有数据		 
# 在我们的 query 当中加入 WHERE 语句
# 在 WHERE 当中加入 and 的逻辑
# 这里我们查询的是泰坦尼克数据集中，年龄大于 20 岁且幸存的人
query = '''
SELECT PassengerId,Survived,Ticket,Fare,Age
FROM example_table
WHERE Age>20 and Survived=1
LIMIT 20
'''
result = connection.execute(query).fetchall()
result

[(2, 1, 'PC 17599', 71.2833, 38.0),
 (3, 1, 'STON/O2. 3101282', 7.925, 26.0),
 (4, 1, '113803', 53.1, 35.0),
 (9, 1, '347742', 11.1333, 27.0),
 (12, 1, '113783', 26.55, 58.0),
 (16, 1, '248706', 16.0, 55.0),
 (22, 1, '248698', 13.0, 34.0),
 (24, 1, '113788', 35.5, 28.0),
 (26, 1, '347077', 31.3875, 38.0),
 (53, 1, 'PC 17572', 76.7292, 49.0),
 (54, 1, '2926', 26.0, 29.0),
 (57, 1, 'C.A. 31026', 10.5, 21.0),
 (62, 1, '113572', 80.0, 38.0),
 (67, 1, 'C.A. 29395', 10.5, 29.0),
 (75, 1, '1601', 56.4958, 32.0),
 (80, 1, '364516', 12.475, 30.0),
 (82, 1, '345779', 9.5, 29.0),
 (86, 1, '3101278', 15.85, 33.0),
 (89, 1, '19950', 263.0, 23.0),
 (98, 1, 'PC 17759', 63.3583, 23.0)]
🔖小贴士
如果你有一定的编程经验，你会发现我们在 WHERE 语句的部分使用的是 "=" 而非编程中常见的 "=="，你可以将上述查询语句的 “=" 换成 “==”运行一下~
​

测试发现使用 == 代替 = 在这种情况下查询结果相同。这表明 SQLite 对于这种特定的语法错误有容错处理，允许在 WHERE 子句中使用 == 而不是标准的 =。

然而，重要的是要记住，这不是 SQL 标准的一部分，且在其他 SQL 数据库系统中这种查询可能会导致错误。始终使用标准的 = 操作符来进行比较是一个好习惯。

# 查询泰坦尼克数据集中，年龄大于 20 岁且幸存的人
# 🐳：选中下方代码、按Ctrl+/取消注释后Shift+Enter即可运行

# query = '''
# SELECT PassengerId,Survived,Ticket,Fare,Age
# FROM example_table
# WHERE Age>20 and Survived==1
# LIMIT 20
# '''
# result = connection.execute(query).fetchall()
# result
✍️ 小练习 2：泰坦尼克数据集中性别不为女性的乘客中，有多少人幸存了？

🔖 TIPS: 代码返回的结果是一个 Python List，可以通过查看 List 的长度判断人数喔~

# 泰坦尼克数据集中性别不为女性的乘客中
# 性别不为女性的条件可以通过where筛选
# 在sql中 <>,!= 等价于 ‘不等于’
query = '''
SELECT PassengerId
FROM example_table
WHERE Sex<>'female' and Survived==1
'''
result = connection.execute(query).fetchall()
result
# TIPS
# result中存储的是 存活且性别不为女性的乘客的id（PassengerId）
# len函数判断长度
print(len(result))

109

​

✍️ 小练习 3：获取数据中年龄处于 20-45 岁的乘客的人数
# 泰坦尼克数据集中年龄处于 20-45 岁的乘客的人数
# 我们也可以通过sql中的COUNT()计算PassengerId的行数来计算人数
query = '''
SELECT COUNT(PassengerId)
FROM example_table
WHERE age >= 20 AND age <= 45
'''
result = connection.execute(query).fetchall()
result
# TIPS
# result中存储的是 存活且性别不为女性的乘客的id（PassengerId）

[(447,)]

提问：经测试age 和 Age 使用查询结果一致，这也是语法容错处理的一部分吗。

回答：在 SQL 中，列名（字段名）通常是区分大小写的，这意味着 age 和 Age 应该被视为不同的列名。然而，实际上，这取决于具体的数据库系统及其配置。

在某些数据库系统中（如 MySQL），默认情况下列名是不区分大小写的。但在其他系统中（如 PostgreSQL），列名是区分大小写的。对于 SQLite，它默认情况下是不区分大小写的，所以 age 和 Age 会被视为同一个列名。

这并不是一个语法容错的问题，而是数据库系统如何处理列名大小写的问题。为了编写更可移植和健壮的 SQL 代码，建议始终保持对列名的一致大小写，特别是在可能会在不同的数据库系统之间迁移或共享代码的情况下。

3.4 AS

AS 关键字可以帮助我们对于筛选出的字段进行重命名，对于一些理解起来比较费劲的字段名称， 可以通过重命名的方式提升查询语句的可读性

例1
SQL语句 SELECT user_id AS `用户的ID信息` FROM users WHERE user_id = 1
释义 1. 从名为 users 的数据表当中

2. 选出 user_id 字段的值为 1 的数据

3. 选出该条数据的字段名/列名为 user_id 的那一列

4. 将这一列重命名为 "用户的ID信息"

5. 并且返回那一列的所有数据
🔖小贴士
你或许注意到了，在重命名的时候我们使用了 ` ` 标点，这可以帮助我们将字段重命名为中文 （当然 SQLite 并不强制我们这么做）
​ 
query = '''
SELECT 
       PassengerId AS `乘客的ID`
       ,Survived  AS 是否幸存
       ,Age AS `乘客的年龄`
       ,Ticket AS ticket_info
FROM example_table
WHERE Age>20 and Survived=1
LIMIT 20
'''
data_holder = connection.execute(query)
# 通过下面的方法获取字段的名称
names = list(map(lambda x: x[0], data_holder.description))
print(names)
data_holder.fetchall()

['乘客的ID', '是否幸存', '乘客的年龄', 'ticket_info']

​

[(2, 1, 38.0, 'PC 17599'),
 (3, 1, 26.0, 'STON/O2. 3101282'),
 (4, 1, 35.0, '113803'),
 (9, 1, 27.0, '347742'),
 (12, 1, 58.0, '113783'),
 (16, 1, 55.0, '248706'),
 (22, 1, 34.0, '248698'),
 (24, 1, 28.0, '113788'),
 (26, 1, 38.0, '347077'),
 (53, 1, 49.0, 'PC 17572'),
 (54, 1, 29.0, '2926'),
 (57, 1, 21.0, 'C.A. 31026'),
 (62, 1, 38.0, '113572'),
 (67, 1, 29.0, 'C.A. 29395'),
 (75, 1, 32.0, '1601'),
 (80, 1, 30.0, '364516'),
 (82, 1, 29.0, '345779'),
 (86, 1, 33.0, '3101278'),
 (89, 1, 23.0, '19950'),
 (98, 1, 23.0, 'PC 17759')]

4. SQL 语句进阶

4.1 GROUP BY 和聚合函数

GROUP BY：关键字可以帮助我们将数据按照关键字后的字段进行分组，进而方便我们对分组内的数据进行聚合或统计

聚合函数：对于一组数据（通过 GROUP BY 得到）进行处理，例如对于组内数据计算一些统计值（求和，求平均值，计数等）, 你可以在 SQLite 的官网上查看支持的聚合函数

例1
SQL语句 SELECT age,COUNT(user_id)
FROM users
WHERE age>20
GROUP BY age
释义 1. 从名为 users 的数据表当中

2. 选出 age 字段的值大于 20 的数据

3. 根据 age 字段的值进行分组

4. 统计每个组内 user_id 字段的值的数量

5. 并且返回不同组队的 age 值和统计结果
📌注意!
在使用 GROUP BY 的语句当中，SELECT 关键字之后只允许两类字段：1. GROUP BY 关键字内的字段 2. 使用了聚合函数的字段
SELECT age, gender, COUNT(user_id)
FROM users
GROUP BY age, gender		 ✅ 对于 age 和 gender 字段分组，计算每个分组下 user_id 的数量，返回 age, gender 和统计结果
SELECT age, COUNT(user_id) , COUNT(user_name)
FROM users
GROUP BY age, gender		 ✅ 对于 age 和 gender 字段分组，计算每个分组下 user_id 的数量和 user_name 的数量，返回 age 和统计结果
SELECT age, user_id , COUNT(user_name)
FROM users
GROUP BY age, gender		 ❌ 错误的语句，user_id 字段既没有出现在 GROUP BY 当中，也没有使用聚合函数 
# 通过 Group by 统计幸存人数
query = '''
SELECT 
    Survived
    ,COUNT(PassengerId)
FROM example_table
GROUP BY Survived
'''
result = connection.execute(query).fetchall()
result

[(0, 549), (1, 342)]

4.2 HAVING

HAVING 关键字帮助我们在分组当中针对聚合函数的统计结果进行筛选

例1
SQL语句 SELECT age,COUNT(user_id)
FROM users
WHERE age>20
GROUP BY age
HAVING COUNT(user_id) >10
释义 1. 从名为 users 的数据表当中

2. 选出 age 字段的值大于 20 的数据

3. 根据 age 字段的值进行分组

4. 统计每个组内 user_id 字段的值的数量

5. 筛选出 user_id 字段的值的数量大于 10 的分组

6. 并且返回不同组内的 age 值和统计结果		 
# 通过 Group by 统计不同的性别×年龄分组下的人数
# 我们只需要保留超过 10 人的组
query = '''
SELECT 
        Sex
        ,Age
        ,COUNT(PassengerId)
FROM example_table
WHERE Age is not Null
GROUP BY Sex,Age
HAVING COUNT(PassengerId)>10
'''
result = connection.execute(query).fetchall()
result

[('female', 18.0, 13),
 ('female', 22.0, 12),
 ('female', 24.0, 16),
 ('female', 30.0, 11),
 ('male', 16.0, 11),
 ('male', 18.0, 13),
 ('male', 19.0, 18),
 ('male', 20.0, 13),
 ('male', 21.0, 17),
 ('male', 22.0, 15),
 ('male', 24.0, 14),
 ('male', 25.0, 18),
 ('male', 26.0, 13),
 ('male', 27.0, 12),
 ('male', 28.0, 18),
 ('male', 29.0, 13),
 ('male', 30.0, 14),
 ('male', 32.0, 15),
 ('male', 34.0, 11),
 ('male', 36.0, 15)]
🔖小贴士
在检查的数据的时候，你或许发现 Age 字段并不总是有值的，因此我们在语句中加入了 WHERE Age is not NULL 来筛选出字段 Age 有值的部分
​ 
# 如果不对where加限制条件，输出的结果会是：

"""
[('female', None, 53),
 ('female', 18.0, 13),
 ('female', 22.0, 12),
 ('female', 24.0, 16),
 ('female', 30.0, 11),
 ('male', None, 124),
 ('male', 16.0, 11),
 ('male', 18.0, 13),
 ('male', 19.0, 18),
 ('male', 20.0, 13),
 ('male', 21.0, 17),
 ('male', 22.0, 15),
 ('male', 24.0, 14),
 ('male', 25.0, 18),
 ('male', 26.0, 13),
 ('male', 27.0, 12),
 ('male', 28.0, 18),
 ('male', 29.0, 13),
 ('male', 30.0, 14),
 ('male', 32.0, 15),
 ('male', 34.0, 11),
 ('male', 36.0, 15)]
"""

"\n[('female', None, 53),\n ('female', 18.0, 13),\n ('female', 22.0, 12),\n ('female', 24.0, 16),\n ('female', 30.0, 11),\n ('male', None, 124),\n ('male', 16.0, 11),\n ('male', 18.0, 13),\n ('male', 19.0, 18),\n ('male', 20.0, 13),\n ('male', 21.0, 17),\n ('male', 22.0, 15),\n ('male', 24.0, 14),\n ('male', 25.0, 18),\n ('male', 26.0, 13),\n ('male', 27.0, 12),\n ('male', 28.0, 18),\n ('male', 29.0, 13),\n ('male', 30.0, 14),\n ('male', 32.0, 15),\n ('male', 34.0, 11),\n ('male', 36.0, 15)]\n"
✍️ 小练习 4：性别不为女性且年龄在 20-45岁的乘客中，幸存和罹难的人数分别是多少
# 通过 Group by 统计幸存人数
query = '''
SELECT 
    Survived
    ,COUNT(PassengerId)
FROM example_table
where age >= 20 AND age <= 45 and Sex<>'female'
GROUP BY Survived
'''
result = connection.execute(query).fetchall()
result

[(0, 236), (1, 55)]

4.3 ORDER BY

ORDER BY 关键字可以将查询语句返回的结果按照关键字后的字段进行排序，可以使用 ASC（正序） 或 DESC（倒序） 来决定排序顺序

例1
SQL语句 SELECT age,COUNT(user_id)
FROM users
WHERE age>20
GROUP BY age
HAVING COUNT(user_id) >10
ORDER BY age DESC
释义 1. 从名为 users 的数据表当中

2. 选出 age 字段的值大于 20 的数据

3. 根据 age 字段的值进行分组

4. 统计每个组内 user_id 字段的值的数量

5. 筛选出 user_id 字段的值的数量大于 10 的分组

6. 并且返回不同组内的 age 值和统计结果，按照 age 字段的值倒排序

如果想要针对多列进行排序，可以在 ORDER BY 后加入多个字段，每个字段是正序还是倒序排列都可以自行定义，且排序的优先级和字段顺序一致

例2
SQL语句 SELECT age,gender,COUNT(user_id)
FROM users
WHERE age>20
GROUP BY age,gender
HAVING COUNT(user_id) >10
ORDER BY age DESC, gender ASC
释义 1. 从名为 users 的数据表当中

2. 选出 age 字段的值大于 20 的数据

3. 根据 age 字段的值进行分组

4. 统计每个组内 user_id 字段的值的数量

5. 筛选出 user_id 字段的值的数量大于 10 的分组

6. 并且返回不同组内的 age 值和统计结果，先按照 age 倒排序后按照 gender 正排序

排序是在所有的查询完成后的，因此我们还可以对于查询中产生的统计值进行排序。

例3
SQL语句 SELECT age,gender,COUNT(user_id)
FROM users
WHERE age>20
GROUP BY age,gender
HAVING COUNT(user_id) >10
ORDER BY COUNT(user_id)
释义 1. 从名为 users 的数据表当中

2. 选出 age 字段的值大于 20 的数据

3. 根据 age 字段的值进行分组

4. 统计每个组内 user_id 字段的值的数量

5. 筛选出 user_id 字段的值的数量大于 10 的分组

6. 并且返回不同组内的 age 值和统计结果，按照统计结果（user_id 的数量）正排序		 
# 通过 Group by 统计不同的性别×年龄分组下的人数
# 我们只需要保留超过 10 人的组
# 按照 人数倒序，Age 倒序，Sex 正序
query = '''
SELECT 
        Sex
        ,Age
        ,COUNT(PassengerId)
FROM example_table
WHERE Age is not Null
GROUP BY Sex,Age
HAVING COUNT(PassengerId)>10
ORDER BY COUNT(PassengerId) DESC,Age DESC, Sex ASC
'''
result = connection.execute(query).fetchall()
result

[('male', 28.0, 18),
 ('male', 25.0, 18),
 ('male', 19.0, 18),
 ('male', 21.0, 17),
 ('female', 24.0, 16),
 ('male', 36.0, 15),
 ('male', 32.0, 15),
 ('male', 22.0, 15),
 ('male', 30.0, 14),
 ('male', 24.0, 14),
 ('male', 29.0, 13),
 ('male', 26.0, 13),
 ('male', 20.0, 13),
 ('female', 18.0, 13),
 ('male', 18.0, 13),
 ('male', 27.0, 12),
 ('female', 22.0, 12),
 ('male', 34.0, 11),
 ('female', 30.0, 11),
 ('male', 16.0, 11)]
✍️ 小练习 5： 计算乘客中按照性别×年龄分组下，人均的船费（Fare）最高的组的人均船费是多少（只取整数部分，不需要四舍五入）
# 通过 Group by 统计不同的性别×年龄分组下的人数
# CAST(num AS INTEGER)用于对num取整,以下用于对AVG(Fare)向下取整（只取整数部分，不需要四舍五入）
# 按照 人均的船费（Fare）降序利用LIMIT 1取第一行，即最大值
query = '''
SELECT 
        Sex
        ,Age
        ,CAST(AVG(Fare) AS INTEGER)
FROM example_table
WHERE Age is not Null
GROUP BY Sex,Age
ORDER BY AVG(Fare) DESC
LIMIT 1
'''
result = connection.execute(query).fetchall()
result
# [('male', 0.92, 151)]
print(result[0][2])

151

​

4.4 CASE

CASE 是一个使用场景相当灵活，且功能相当强大的关键字，其基本的用法为：

CASE  
	WHEN ... THEN ...  
	WHEN ... THEN ...  
	...  
	ELSE  
END

其效果与编程语言中的 SWITCH… CASE 类似，都是 IF-ELSE 结构的拓展版，能够表达根据某些特定条件执行不同的分支，但相较于 IF-ELSE， CASE 语句能够更好地适应和表达 SQL 查询中的复杂条件，同时能够提升查询语句整体的可读性。下面的例子只提供了 CASE 语句的一些基本用法，更多高级用法

📌注意!
所有的 CASE 语句都必须要以一个 END 关键词作为结尾

CASE 语句在 SELECT 中使用可以将查询结果按照特定的条件返回不同的结果

例1
SQL语句 SELECT user_id
,(CASE WHEN gender = 1 THEN 'male' WHEN gender = 0 THEN 'female' END)
FROM users
释义 1. 从名为 users 的数据表当中

2. 选出该条数据的字段名/列名为 user_id 和 gender 的列

3. 如果 gender 字段的值为 1 则返回 "male"，如果是 0，则返回 "female"

4. 返回数据结果

CASE 语句可以用在 GROUP BY 当中，这样就可以自定义一个数据分组的条件了

例2
SQL语句 SELECT
(CASE WHEN age > 60 THEN 'old' ELSE 'young' END)
,COUNT(user_id)
FROM users
WHERE age>20
GROUP BY (CASE WHEN age > 60 THEN 'old' ELSE 'young' END)
HAVING COUNT(user_id) >10
释义 1. 从名为 users 的数据表当中

2. 选出 age 字段的值大于 20 的数据

3. 根据 "age 字段的值是否大于 60" 进行分组

4. 统计每个组内 user_id 字段的值的数量

5. 筛选出 user_id 字段的值的数量大于 10 的分组

6. 并且返回不同组内的统计结果		 
# 通过 Group by 按照年龄区分 old 和 young
# 注意这里我们也筛选 Age 不为 NULL 值
query = '''
SELECT 
        (CASE WHEN Age > 60 THEN 'old' ELSE 'young' END)
        ,COUNT(PassengerId)
FROM example_table
WHERE Age is not Null
GROUP BY (CASE WHEN Age > 60 THEN 'old' ELSE 'young' END)
'''
result = connection.execute(query).fetchall()
result

[('old', 22), ('young', 692)]
🔖小贴士
在使用 CASE 语句的时候，一般建议在最后加上 ELSE 关键词，这样就不会出现某条数据无法满足任何一个分支条件而形成报错
​

闯关题

###根据要求完成题目：

继续使用上面教程中使用的泰坦尼克数据集，按照 10 岁为一个年龄段（0-9,10-19,20-29,30-39,40-49,50-59）且 60 岁以上（包括 60 岁）统一为一个年龄段(60+)， 计算每个年龄段的用户的幸存率（幸存人数/总人数，使用 round 保留 2 位小数），将查询结果按照幸存率从高到低排序，你将回答如下几个问题：

对题目进行分析

  • 1.按照 10 岁为一个年龄段（0-9,10-19,20-29,30-39,40-49,50-59）且 60 岁以上（包括 60 岁）统一为一个年龄段(60+)
    使用(CASE … END)对年龄段进行分类，以及需要对GROUP BY进行分组计算幸存率
  • 2.根据4.2例子可得知Age可能存在缺失值，需要用到where排除
  • 3.使用OREDER BY 可实现幸存率的降序/升序排列
query = '''
SELECT
    CASE
        WHEN Age < 10 THEN '00-09'
        WHEN Age < 20 THEN '10-19'
        WHEN Age < 30 THEN '20-29'
        WHEN Age < 40 THEN '30-39'
        WHEN Age < 50 THEN '40-49'
        WHEN Age < 60 THEN '50-59'
        ELSE '60+'
    END AS AgeGroup,
    ROUND(SUM(Survived) * 1.0 / COUNT(*),2) AS SurvivalRate
FROM example_table
WHERE Age is not Null
GROUP BY AgeGroup
ORDER BY SurvivalRate DESC
'''
result = connection.execute(query).fetchall()
result

[('00-09', 0.61),
 ('30-39', 0.44),
 ('50-59', 0.42),
 ('10-19', 0.4),
 ('40-49', 0.38),
 ('20-29', 0.35),
 ('60+', 0.27)]

query = '''
SELECT 
    ROUND(SUM(Survived) * 1.0 / COUNT(*),2) AS SurvivalRate2
FROM example_table
WHERE Age is Null
'''
result = connection.execute(query).fetchall()
result

[(0.29,)]
Q1：最高的幸存率是多少？
# ...your code...
a1 =  0.61 # 在 = 后面填入你的结果，如 a1 = 0.51
Q2：最低的幸存率发生在哪一个组？
# ...your code...

a2 = '60+'  # 在 '' 中填入你的结果，如 a2 = '0-9'
Q3：20-29 岁和 40-49 岁的乘客，哪组的幸存率更高？
# ...your code..

a3 = '40-49'  # 在 '' 中填入你的结果，如 a3 = '20-29'
Q4：没有提供年龄信息的乘客们的幸存率是多少？
# ...your code...

a4 = '0.29'  # 在 = 后面中填入你的结果，如 a4 = 0.51

将结果保存为 csv 文件
csv 需要有两列，列名：id、answer。其中，id 列为题号，如 q1、q2；answer 列为 STEP1 中各题你计算出来的结果。💡 这一步的代码你无需修改，直接运行即可。文章来源地址https://www.toymoban.com/news/detail-810850.html

# 生成 csv 作业答案文件
def save_csv(answers):
    import pandas as pd
    
    if len(answers)!=4:
        raise Exception("请检查你的答案数列中是否包含了 4 道题的答案。\n 参考检查方式: 检查 len(a1) == 4 是否为 True")
    df = pd.DataFrame({"id": ["q1","q2","q3","q4"], "answer": answers})
    df.to_csv("answer_workflow_1_1.csv", index=None)
save_csv([a1,a2,a3,a4])  # 该csv文件在左侧文件树project工作区下，你可以自行右击下载或者读取查看

到了这里，关于SQL进阶（一）：SQL基础速览，以SQLite为例的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • SQLite导出数据库至sql文件

    SQLite导出数据库至sql文件

    SQLite是一款实现了自包含、无服务器、零配置、事务性SQL数据库引擎的软件库。SQLite是世界上部署最广泛的SQL数据库引擎。 SQLite 是非常小的，是轻量级的，完全配置时小于 400KiB，省略可选功能配置时小于250KiB。 SQLite 源代码不受版权限制。 Dbeaver等工具支持数据表导出为sq

    2024年04月23日
    浏览(36)

  • QT-sql操作(sqlite数据库文件)

        #include SqlDriver 如名称，数据库驱动。 hasFeature ： 检查驱动是否支持特定功能，如事务、批处理等。 isQuerySize ： 驱动是否支持  QSqlQuery::size  函数。 open 、 close ： 打开和关闭数据库连接。 isOpen ： 判断数据库连接是否处于打开状态。 commitTransaction 、 rollbackTransaction ：

    2024年02月04日
    浏览(55)
  • sqlite3 关系型数据库语言 SQL 语言

    sqlite3 关系型数据库语言 SQL 语言

            SQL(Structured Query Language)语言是一种结构化查询语言,是一个通用的,功能强大的关系型数据库操作语言. 包含 6 个部分: 1.数据查询语言(DQL:Data Query Language)         从数据库的二维表格中查询数据,保留字 SELECT 是 DQL 中用的最多的语句 2.数据操作语言(DML)       

    2024年02月07日
    浏览(57)
  • 使用SQL语句创建实验数据库（以学生表为例）

    使用SQL语句创建实验数据库（以学生表为例）

     (1) 创建数据库stuexpm create database stuexpm   (2) 创建studentinfo表,显示studentinfo表的基本结构 use stuexpm create table studentInfo （ StudentID varchar(6) not null primary key comment \\\'学号, Name varchar(8) not null comment\\\'姓名‘， Sex varchar(2) not null default \\\'男\\\' comment\\\'性别\\\', Birthday date not null comment\\\'出生日期

    2024年02月07日
    浏览(57)

  • 0基础下载并安装SQLite并新建数据库

    //本文章由chatgpt-3.5协助生成。// （此部分有参考） 访问 SQLite 官方Download Page: https://www.sqlite.org/download.html 选择适用的文件： Precompiled Binaries for 你的操作系统 例如Windows就在 Precompiled Binaries for Windows 中下载 sqlite-dll-win-x64-3440200.zip (1.24 MiB) 64-bit DLL (x64) for SQLite version 3.44.2. (S

    2024年02月05日
    浏览(43)

  • 实例讲解C++连接各种数据库，包含SQL Server、MySQL、Oracle、ACCESS、SQLite 和 PostgreSQL、MongoDB 数据库

      C++ 是一种通用的编程语言，可以使用不同的库和驱动程序来连接各种数据库。以下是一些示例代码，演示如何使用 C++ 连接 SQL Server、MySQL、Oracle、ACCESS、SQLite 和 PostgreSQL、MongoDB 数据库。 连接 SQL Server 数据库 要使用 C++ 连接 SQL Server 数据库，可以使用 Microsoft 的 ADODB 库。以

    2024年02月05日
    浏览(66)
  • 【数据库】MySQL 高级(进阶) SQL 语句

    【数据库】MySQL 高级(进阶) SQL 语句

    location表格创建 store_info表格创建 显示表格中一个或数个字段的所有数据记录 不显示重复的数据记录 按照条件进行查询 在已知的字段数据取值范围内取值 另外还有not in命令，用法一致，表示显示不在指定范围内的字段的值。 在两个字段数据值之间取值，包含两边字段的数据

    2024年02月09日
    浏览(137)
  • Unity3D 连接 SQLite 作为数据库基础功能【详细图文教程】

    Unity3D 连接 SQLite 作为数据库基础功能【详细图文教程】

    轻量级: SQLite是一个嵌入式数据库引擎，它的库文件非常小巧，没有独立的服务器进程，适用于嵌入到其他应用程序中，对于轻量级的项目或移动应用程序非常适用。 零配置: 使用SQLite不需要进行复杂的配置或管理，你只需要简单地创建一个文件即可开始使用，减少了额外的

    2024年02月10日
    浏览(39)

  • SQL进阶理论篇（一）：数据库的调优

    本节主要分为以下三方面内容： 数据库调优的目标是什么？ 如果要进行调优，都有哪些维度可以选择？ 如何思考和分析数据库调优? 只是简单介绍了一些基础理论，之后有时间的话再针对性扩展吧。 简单来说，就是让相应的时间更快，吞吐量更大。 那怎么断定我这次调优是

    2024年02月04日
    浏览(46)

  • 找工作所需数据库基础知识与实际操作（以MySQL为例）

    第一章、数据库原理概述 1.1.2 数据库、数据字典、数据库管理系统、数据库系统 1. 数据库（DB）--- （1）概念：按一定结构组织并长期存储在计算机内的、在逻辑上保持一致的、可共享的大量相关数据的集合---存储数据仓库 （2）属性：较小的冗余度、较高的数据独立性、易

    2024年02月05日
    浏览(63)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包