简单上手Python SQLite 的10分钟 教程

简单上手Python SQLite 的10分钟 教程

前言
编程过程中数据库的重要性不言而喻 , 在Python中想要快速使用数据库读写少量数据sqlite是个很好的选择 , 相信看这篇文章的小伙伴们都知道 Python标准库中有一个 库叫做sqlite3 , 这篇文章作者君会尽量用最少的篇幅介绍Python SQLite3以及SQL的基础知识

阅读指导 , 本篇主要分为Python SQLite3 和 SQL基础俩部分,如果您只是想要学习sqlite3库中增删查改的用法,那么只需阅读本文中Python SQLite3的部分以及文章的附录即可 , 想要了解更加全面的SQL知识 , 请移步SQL基础

Python SQLite3

Python中sqlite3库的基本用法

下面的内容讲解了sqlite3这个Python库的用法 , 注意他不是sqlite本身的内容,仅仅是sqlite在Python内的"方言"对其他语言是不适用的

创建连接

在导入sqlite3后,正式使用之前我们需要先连接我们的数据库,在这里你需要sqlite3.connect(db_path:str)它将返回你需要的数据库对象

import sqlite3
# myDB作为我们的示例数据库文件位置,你可以更换
conn = sqlite3.connect("myDb")
# 如果传入的数据库文件不存在时,你无需自己手动或者编写额外的代码来创建myDB.db文件,sqlite3库会自动创建它

SQLite3 游标

现在我们已经获得了一个数据库对象 , 但你想要操作数据库来帮你完成一些任务 , 你还需要拿到它的游标cursor

cursor = conn.cursor()

execute()执行一条SQL语句

现在我们可以使用游标对象调用execute()方法来执行任何 SQL 查询。他需要的参数是一个字符串类型的SQL语句,当您的SQL语句中有占位符他还接受一个可选的参数让您向SQL语句传入必要的参数

# sql_creatTable是一条示例的SQL语句,它的作用是创建一个表
sql_creatTable = \
'''CREATE TABLE employees(
							id integer PRIMARY KEY,
							name text, salary real, 
							department text, 
							position text, 
							hireDate text)```
cursor.execute(sql_creatTable)

用fetchall()获取所有结果

当您用execute()执行了一条查询语句后,使用fetchall来获取它的查询结果
您可以这样使用

# sql_query是一条示例的SQL语句,它的作用是查询employees表的所有内容
sql_query = "select * from employees"
result = cursor.execute(sql_query).fetchall()
# 这里获取到的result类似于[(第一行第一列数据,第一行第二列数据),(第二行第一列数据,第二行第二列数据),(......)]

提交更改

更改数据库内容后您需要提交,否则您的更改将不会被保存

conn.commit()

关闭连接

conn.close()

SQLite的增删查改

这部分主要是SQL语句,执行时使用execute()方法

增

SQLite的增填数据

删

SQLite的删除数据

查

SQLite的查找数据

改

SQLite的修改数据

SQL基础

SQL简介

creat table创建表

creat table 表名()

常用数据类型

在 SQLite 中，值的数据类型与值本身是相关的，而不是与它的容器相关。

每个存储在 SQLite 数据库中的值都具有以下存储类之一：

数据类型	说明
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
TEXT	值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
BLOB	值是一个 blob 数据，完全根据它的输入存储。

SQLite 亲和(Affinity)类型

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型：

亲和类型	描述
TEXT	数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。
NUMERIC	当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。
INTEGER	对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。
REAL	其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE	不做任何的转换，直接以该数据所属的数据类型进行存储。

SQLite 亲和类型(Affinity)及类型名称

下表列出了当创建 SQLite3 表时可使用的各种数据类型名称，同时也显示了相应的亲和类型：

数据类型	亲和类型
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB no datatype specified	NONE
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

Boolean 数据类型

SQLite 没有单独的 Boolean 存储类。相反，布尔值被存储为整数 0（false）和 1（true）。

Date 与 Time 数据类型

SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

存储类	日期格式
TEXT	格式为 "YYYY-MM-DD HH:MM:SS.SSS" 的日期。
REAL	从公元前 4714 年 11 月 24 日格林尼治时间的正午开始算起的天数。
INTEGER	从 1970-01-01 00:00:00 UTC 算起的秒数。

您可以以任何上述格式来存储日期和时间，并且可以使用内置的日期和时间函数来自由转换不同格式。

insert插入

insert 语句用于向表中插入新记录

它有两种写法：

1. 第一种写法：为表中的所有字段添加值时不需要字段名称，但要确保值的顺序与表中 的字段顺序相同，语法如下：
   insert into 表名values (字段1的值，字段2的值，字段3的值，)
2. 第二种写法：为指定的字段插入数据时，要把字段名和字段值都列出，并一一对应 语法如下：
   insert into 表名（字段1，字段2，字段3，...) values (字段1的值字段2的值，字段3的值，...)

primary key表的主键

主键的全称是主键约束，表的主键由表中的一个字段或多字段组成，主键唯一代表表中 一条记录，关系数据库中通常每个表都有一个主键，没有主键的表通常是不严谨的设计的产 物。

主键有以下特点：

1. 每个表只能定义一个主键。
2. 主键值必须唯一标识表中的每一记录，且不能为nul,即表中不可能存在有相同主键 值的两条记录。
3. 一个字段名只能在联合主键字段中出现一次
4. 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，剩下的字 段不能唯一代表一条记录。

select查询

select 有 俩种写法

1. select * from 表名;
2. select 字段1,字段2,... from 表名;

distinct去重复值

同一字段中可能会出现重复值 ,使用关键字distinct可以去掉重复值 , 用法如下:
select distinct 字段名 from 表名;

where条件过滤

如果需要从表中选取指定的数据，可将where 子句添加到selecti 语句，语法如下：
select 字段名 from 表名名 where 字段 运算符 值;

where语句中的运算符

运算符	说明
=	等于
<>或!=	不等于

|大于
<|小于
=|大于等于
<=|小于等于
BETWEEN|在某个范围内
LIKE|搜索匹配的字符串模式

and和or运算符

and和or可以用在where 子句中把两个或多个条件结合起来。
and运算符要求两个条件 都成立；or运算符要求二个条件中只要有一个成立即可。语法如下：
select 字段名 from 表名 where 字段n 运算符值 n and|or 字段m 运算符 值m;

like搜索匹配的字符串

在WHERE 子句中使用LIKE运算符来搜索匹配字符串中的指定模式，百分号(%) 匹配零个、一个或多个字符。语法如下
select 字段名 from 表名 where 字段 like 字符串;

in匹配多个值

in运算符是在where 子句中指定多个搜索条件可以匹配的值。in运算符实际是多个or条 件的合并语法如下
select 字段名 from 表名 where 字段名 in (值1，值2);

between指定范围

betweeni 运算符用于选取介于两个值之间的数据范围内的值，这些值可以是数字，字符 串或日期between 运算符包括开始和结束值，相当于>=and<= 语法如下
select 字段名 from 表名 where 字段名 between 值1 and 值2;

order by排序

order 用于对结果集进行排序，默认按升序(asc)进行排序，也可以指定desc按降 序对结果集进行排序。语法如下：
select 字段名 from 表名 order by 字段1,字段2,ascldesc

update更新数据

update 语句用于更新表中的记录。语法如下：
update 表名 set 字段1 = 值1,字段2 = 值2 where子句;
注意：where 子句指定哪些记录需要更新。如果省略where 子句，表中的所有记录都将更 新

delete删除记录

delete 语句用于删除表中的记录语法如下
delete from 表名 where子句
注意：where 子句指定哪些记录需要删除。如果省略where 子句，表中的所有记录都将删 除

index索引

索引可以提高访问数据的速度。创建索引的语法如下：
create index 索引名 on 表名(字段1，字段)
使用索引时SQL语句的语法和不使用索引并没有任何不同，SQL语句会自动使用索引提 高访问数据的速度。

view视图

视图是基于SQL语句的结果集的可视化表，视图中的字段就是来自一个或多个数据库中 的真实的表中的字段。
视图总是显示当前的数据。每当用户查询视图时，数据库引擎通过使用SQL语句来重建 数据。
创建视图的语法：
create view 视图名 as select 语句;

null值

Nl值代表遗漏的未知数据，它的作用是未知的或不适用的值的占位符。
如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更 新已有的记录。这意味着该字段将以NULL值保存
字段值是否是NULL的判断不能用=或者<>进行，要用is null或者is not null进行

字段和表的别名

在SQL语句中可以为表名或字段名指定临时使用的别名(Alias ),别名只在当前的SQL语 句中生效，它通常比字段名更具有可读性。
别名有以下应用场景：

1. 字段名长或可读性差时
2. 涉及多个表，而且多个表中有相同的字段名时
3. 代表查询函数的结果
   别名的语法：
   select 字段名 as 别名 from 表名 as别名 as可以省略。

join连接

jin连接是基于多个表之间的共同字段把它们结合起来进行查询的一种方法。连接分为以下 几种：

方式	说明
内连接((inner join )	列出两个表中都存在的记录
左连接(left join )	即使没有匹配也列出左表中的所有记录
右连接((right join )	即使没有匹配也列出右表中的所有记录
语法：
select 字段名 as from 表1 join 表2 where子句

SubQuery子查询

子查询也称嵌套查询，是一种嵌套在其他SQL语句的WHERE 子句中的查询。子查询 用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。语法如下：
select 字段1,字段 from 表名 where 字段名 操作符(子查询)

常用函数

count

count 函数统计符合条件的记录数，count (*)统计表中的记录总数，count (字段名)统 计指定字段不为null的记录数

max

返回函数所选字段的最大值,用法是max(字段名)

min

返回函数所选字段的最小值,用法是min(字段名)

avg

avg函数范湖所选字段的平均值.用法为avg(字段名)

sum函数

sum函数返回所选字段的合计值,用法是sum(字段名)

group by分组

group byi 语句用于结合统计函数，根据一个或多个列对结果集进行分组。语法如下：
select 字段名 统计函数 from 表名 where子句 group by 字段名;

having过滤分组

having 子句和wher子句类似，都是对查询的结果集进行过滤。它们的过滤对象不同， where-子句对被选择的列进行过滤，而having-子句则对group by 子句所产生的组进行过滤。 语法如下：
select 字段名 统计函数 from 表名 where子句 group by 字段名 having 统计函数 运算符 值;