SQLite 思维导图模板_ProcessOn思维导图、流程图

子查询

即嵌套在其他查询中的查询。只能是单列

SELECT

cust_id

FROM

orders

WHERE

order_num IN (

SELECT

order_num

FROM

orderitems

WHERE

prod_id = 'RGAN01')

联结表

等值联结（内联结）

SELECT

vend_name,

prod_name

FROM

vendors,

products

WHERE

vendors.vend_id = products.vend_id

联结表可以通过起别名的方式缩短SQL 语句

INNER JOIN 语法 ON

SELECT

vend_name,

prod_name

FROM

vendors

INNER JOIN

products

ON

vendors.vend_id = products.vend_id

联结多个表

SELECT

prod_name,

vend_name,

prod_price,

quantity

FROM

orderitems

INNER JOIN products

INNER JOIN vendors ON products.vend_id = vendors.vend_id

AND orderitems.prod_id = products.prod_id

AND order_num = '20007'

创建高级联结

自连接

SELECT

c1.cust_id,

c1.cust_name,

c1.cust_contact

FROM

customers AS c1,

customers AS c2

WHERE

c1.cust_name = c2.cust_name

AND c2.cust_contact = 'Jim Jones'

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句

自然联结

SELECT

c.*,

o.order_num,

o.order_date,

oi.prod_id,

oi.quantity,

item_price

FROM

customers AS c,

orders AS o,

orderitems AS oi

WHERE

c.cust_id = o.cust_id

AND oi.order_num = o.order_num

AND prod_id = 'RGAN01'

外联结

结包含了那些在相关表中没有关联行的行。
这种联结称为外联结

外联结 OUTER JOIN
在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表
（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）

SELECT

c.cust_id,

o.order_num

FROM

customers AS c

LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id

组合查询

插入数据

INSERT INTO ‘表名’VALUE （'xxx','xxx'）

复制到一个新表

CREATE TABLE ‘新表名’AS SELECT * FROM '源表'

更新和删除数据

更新

UPDATE ‘表名’SET name='数据' , password='数据' WHERE id = 'xxx'

删除某个列的值

UPDATE ‘表名’SET name=NULL WHERE id = 'xxx'

子主题

DELETE FROM ‘表’ WHERE id = 'xxx'

动态类型

NULL

INTERGER：整型数字

REAL：实数、浮点型

TEXT：文本类型

BLOB：二进制类型

游标 : cursor = conn.cursor() (执行SQL语句)

方法: execute("SQL语句",[参数])

fetchall(): 获取所有结果到列表

fetchone(): 获取一条结果到列表

fetchmany(记录数): 获取指定几条结果到列表

参数化查询: 避免SQL注入

: 参数名, 参数传递字典表

语句

插入内容

INSERT INTO '表名' VALUES ( '类别名称' )
INSERT INTO '表名'('列名') VALUES ( '类别名称' )

INSERT INTO '表名' VALUES ( ?,?,?,? ) ?参数占位符

删除内容

DELETE FROM '表名' WHERE '字段' = ( '类别' )

更新操作

UPDATE '表' SET '值'=?,'值'= ? WHERE

外键

FOREIGN KEY ( '字段' ) REFERENCES '表' ( '字段' )

内联查询

select xxx from xxx INNER JOIN xxx ON xxx 吧查询到的字段放到最后面

排序

select ROWID , * from xxx ORDER BY ROWID DESC
( ORDER BY )根据 ROWID排序 (DESC) 倒序

使用注释

行内注释

SELECT prod_name -- 这是一条注释

FROM products LIMIT 5,5;

代码注释

/*SELECT prod_name FROM products LIMIT 5,5;*/

执行

conn = sqlite3.connect(r'数据库路径') 链接数据库

c = conn.cursor( ) 准备游标

sql = INSERT...... 插入sql语句,语句内需要值的时候用 ? 代替

c.execute ( sql , (xxx , ) ) 游标执行sql 传参

conn.commit( ) 链接提交

conn.close( ) 关闭链接

创建表

CREATE TABLE '表名' ( '字段' TEXT )

查询数据库

SELECT ROWID,* FROM '表名'

SELECT 子句查询顺序

限制结果 LIMIT、OFFSET

LIMIT指定返回的行数。带OFFSET的LIMIT指定从哪儿开始

SELECT prod_name FROM products LIMIT 5;

SELECT prod_name FROM products LIMIT 5 OFFSET 5;

检索不同 DISTINCT

'DISTINCT' 检索出不同的值，放在列名前，作用所有列

SELECT DISTINCT vend_id FROM products;

排序数据 ORDER_BY、DESC

SELECT prod_name FROM products ORDER BY prod_id;

多列排序，首先按第一个，再按第二个

可以按列相对位置排序 ORDER_BY 2，3

SELECT prod_id,prod_price FROM products ORDER BY prod_price,prod_name

SELECT prod_id FROM products ORDER BY prod_price DESC;

倒序排列

SELECT prod_id FROM products ORDER BY prod_price DESC, prod_name; 仅指定一个列倒序

过滤数据

使用WHERE 子句

SELECT prod_name,prod_price FROM products WHERE prod_price=3.49

不匹配检查

操作符说明

= 等于

< > 不等于

!= 不等于

< 小于

<= 小于等于

>= 不小于

> 大于

>= 大于等于

<= 不大于

BETWEEN 在指定的两个值之间

IS NULL 为NULL值

SELECT vend_id,prod_name FROM products WHERE vend_id != 'DLL01'

范围检查 BETWEEN

SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10

空值检查 IS NULL

SELECT prod_name FROM products WHERE prod_id IS NULL

高级数据过滤

操作符 AND

SELECT prod_name,prod_price FROM products WHERE vend_id = 'DLL01' AND prod_price <= 4;

操作符 OR

SELECT prod_name , prod_price FROM products WHERE vend_id = 'Dll01' OR vend_id = 'BRS01'

在第一个条件满足时，不管第二个条件是否满足，相应的行都将被检索出来

求值顺序（）> AND > OR

SELECT

prod_name,

prod_price

FROM

products

WHERE

(

vend_id = 'DLL01'

OR vend_id = 'BRS01'

)

AND prod_price >= 10

圆括号具有比AND或OR操作符更高的求值顺序（）> AND > OR

操作符 IN

SELECT

prod_name,

prod_price

FROM

products

WHERE

vend_id IN ('DLL01', 'BRS01')

ORDER BY

prod_name

为什么要使用IN操作符？其优点为：

在有很多合法选项时，IN操作符的语法更清楚，更直观。
在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
IN操作符一般比一组OR操作符执行得更快。
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。

操作符 NOT

SELECT

prod_name,

prod_price

FROM

products

WHERE

NOT vend_id = 'DLL01'

ORDER BY

prod_name

NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件

用通配符过滤

通配符：

用来匹配值的一部分的特殊字符

通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索

操作符 LIKE

% 通配符

在搜索串中，% 表示任何字符出现任意次数，也能表示 0 个字符，但不匹配 NULL

检索 Fish 开头的
SELECT

prod_id,

prod_name

FROM

products

WHERE

prod_name LIKE 'Fish%'

SELECT

prod_id,

prod_name

FROM

products

WHERE

prod_name LIKE '%bean bag%'

SELECT

prod_id,

prod_name

FROM

products

WHERE

prod_name LIKE 'F%y'

_ 通配符

下划线的用途与 % 一样，但它只匹配单个字符

SELECT

prod_id,

prod_name

FROM

products

WHERE

prod_name LIKE '__ inch teddy%'

技巧

通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

创建计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式

需要直接从数据库中检索出转换、计算或格式化过的数据，
而不是检索出数据，然后再在客户端应用程序中重新格式化

在SQL语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。
但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。

拼接字段

SELECT

CONCAT(

vend_name,

'(',

vend_country,

')'

) AS vend_title

FROM

vendors

ORDER BY

vend_name

使用 CONCAT函数
拼接两个字段 AS 别名（导出列） vend_title

输出：

Bear Emporium(USA)

Bears R Us(USA)

Doll House Inc.(USA)

执行算数计算

SELECT

prod_id,

quantity,

item_price,

quantity * item_price AS expanded_price

FROM

orderitems

WHERE

order_num = 20008

输出中显示的expanded_price列是一个计算字段，此计算为quantity * item_price。
客户端应用现在可以使用这个新计算列，就像使用其他列一样。

操作符说明

+ 加

- 减

* 乘

/ 除

使用数据处理函数

3个常用函数

语　　法

函　　数

提取字符串的组成部分

MySQL和SQL Server使用SUBSTRING()

数据类型转换

MariaDB、MySQL和SQL Server使用CONVERT()

取当前日期

MariaDB和MySQL使用CURDATE()

NOW()

使用函数

文本处理函数

UPPER 将文本转换为大写
SELECT

vend_name,

UPPER(vend_name) AS vend_namecase

FROM

vendors

ORDER BY

vend_name

日期和时间处理函数

YEAR( )的函数从日期中提取年份

SELECT

order_num

FROM

orders

WHERE

-- YEAR (order_date) = 2012

-- MONTH(order_date) = 1

DAY(order_date) = 8

数值处理函数

汇总数据

聚集函数

AVG 平均值

SELECT AVG(prod_price) AS avg_price FROM products

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 'DLL01'

COUNT 计总数

COUNT(*) 统计行数包括NULL

COUNT(column) 对特定列有值的统计忽略NULL

SELECT COUNT(cust_email) AS num_cust FROM customers

大小值忽略NULL行

MAX 最大值

SELECT MAX(prod_price) FROM products;

MIN 最小值

SELECT MIN(prod_price) FROM products;

SUM 计和

SELECT SUM(prod_price) FROM products;

SELECT

SUM(item_price * quantity) AS total_price

FROM

orderitems

WHERE

order_num = 20005;

聚集不同值

DLL01供应商的平均值排除相同的值
SELECT

AVG(DISTINCT prod_price) AS avg_price

FROM

products

WHERE

vend_id = 'DLL01'

组合聚集函数

SELECT

COUNT(*) AS num_items,

MIN(prod_price) AS price_min,

MAX(prod_price) AS price_max,

AVG(prod_price) AS price_avg

FROM

products

分组数据

创建分组 GROUP BY

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
GROUP BY子句可以包含任意数目的列
GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总

SELECT

vend_id,

COUNT(*) AS num_prods

FROM

products

GROUP BY

vend_id

过滤分组 HAVING

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

SELECT

cust_id,

count(*) AS orders

FROM

orders

GROUP BY

cust_id

HAVING

count(*) >= 2

包含 WHERE 和 HAVING

WHERE子句过滤所有prod_price至少为4的行，
然后按vend_id分组数据，
HAVING子句过滤计数为2或2以上的分组

SELECT

vend_id,

count(*) AS num_prods

FROM

products

WHERE

prod_price >= 4

GROUP BY

vend_id

HAVING

count(*) >= 2

分组和排序

SELECT

order_num,

count(*) AS items

FROM

orderitems

GROUP BY

order_num

HAVING

count(*) >= 3

ORDER BY

items,order_num