子查询
即嵌套在其他查询中的查询。只能是单列
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>cust_id</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orders</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order_num IN (</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order_num</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orderitems</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_id = 'RGAN01')</div>
联结表
等值联结（内联结）
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vendors,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vendors.vend_id = products.vend_id</div>
联结表可以通过起别名的方式 缩短SQL 语句
INNER JOIN 语法 ON
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vendors</div><div>INNER JOIN </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products </div><div>ON </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vendors.vend_id = products.vend_id</div>
联结多个表
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_price,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>quantity</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orderitems</div><div>INNER JOIN products</div><div>INNER JOIN vendors ON products.vend_id = vendors.vend_id</div><div>AND orderitems.prod_id = products.prod_id</div><div>AND order_num = '20007'</div>
创建高级联结
自连接
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c1.cust_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c1.cust_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c1.cust_contact</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>customers AS c1,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>customers AS c2</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c1.cust_name = c2.cust_name</div><div>AND c2.cust_contact = 'Jim Jones'</div>
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句
自然联结
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c.*, </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>o.order_num,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>o.order_date,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>oi.prod_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>oi.quantity,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>item_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>customers AS c,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orders AS o,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orderitems AS oi</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c.cust_id = o.cust_id</div><div>AND oi.order_num = o.order_num</div><div>AND prod_id = 'RGAN01'</div>
外联结<br><br>结包含了那些在相关表中没有关联行的行。<br>这种联结称为外联结<br>
外联结 OUTER JOIN<br>在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表<br>（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）<br>
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>c.cust_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>o.order_num</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>customers AS c</div><div>LEFT OUTER JOIN orders AS o ON c.cust_id = o.cust_id</div>
插入数据
INSERT INTO ‘表名’VALUE （'xxx','xxx'）
复制到一个新表
CREATE TABLE ‘新表名’AS SELECT * FROM '源表'
更新和删除数据
更新
UPDATE ‘表名’SET name='数据' , password='数据' WHERE id = 'xxx'
删除某个列的值<br><br>UPDATE ‘表名’SET name=NULL WHERE id = 'xxx'<br>
子主题
DELETE FROM ‘表’ WHERE id = 'xxx'
<span style="font-size: 14px;">动态类型</span><br>
NULL
INTERGER：整型数字
REAL：实数、浮点型
TEXT：文本类型
BLOB：二进制类型
<span style="font-size: 14px;">游标 : cursor = conn.cursor() (执行SQL语句)</span>
方法: execute("SQL语句",[参数])
fetchall(): 获取所有结果到列表
fetchone(): 获取一条结果到列表
fetchmany(记录数): 获取指定几条结果到列表
<span style="font-size: 14px;">参数化查询: 避免SQL注入</span>
参数化查询: 避免SQL注入
: 参数名, 参数传递字典表
语句
插入内容
INSERT INTO '表名' VALUES ( '类别名称' )<br>INSERT INTO '表名'('列名') VALUES ( '类别名称' )<br>
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 <br>( ORDER BY )根据 ROWID排序 (DESC) 倒序
使用注释
行内注释
<div>SELECT prod_name -- 这是一条注释</div><div>FROM products LIMIT 5,5;</div>
代码注释
/*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
不匹配检查
<div>操作符<span class="Apple-tab-span" style="white-space:pre"> </span>说 明</div><div>=<span class="Apple-tab-span" style="white-space:pre"> </span>等于</div><div>< ><span class="Apple-tab-span" style="white-space:pre"> </span>不等于</div><div>!=<span class="Apple-tab-span" style="white-space:pre"> </span>不等于</div><div><<span class="Apple-tab-span" style="white-space:pre"> </span>小于</div><div><=<span class="Apple-tab-span" style="white-space:pre"> </span>小于等于</div><div>>=<span class="Apple-tab-span" style="white-space:pre"> </span>不小于</div><div>><span class="Apple-tab-span" style="white-space:pre"> </span>大于</div><div>>=<span class="Apple-tab-span" style="white-space:pre"> </span>大于等于</div><div><=<span class="Apple-tab-span" style="white-space:pre"> </span>不大于</div><div>BETWEEN 在指定的两个值之间</div><div>IS NULL<span class="Apple-tab-span" style="white-space:pre"> </span>为NULL值</div>
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
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id = 'DLL01'</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>OR vend_id = 'BRS01'</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div><div>AND prod_price >= 10</div>
圆括号具有比AND或OR操作符更高的求值顺序 （）> AND > OR
操作符 IN
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id IN ('DLL01', 'BRS01')</div><div>ORDER BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div>
为什么要使用IN操作符？其优点为：<br><br>在有很多合法选项时，IN操作符的语法更清楚，更直观。<br>在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。<br>IN操作符一般比一组OR操作符执行得更快。<br>IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。
操作符 NOT
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NOT vend_id = 'DLL01'</div><div>ORDER BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div>
NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件
用通配符过滤
通配符：
用来匹配值的一部分的特殊字符
通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索
操作符 LIKE
% 通配符
在搜索串中，% 表示任何字符出现任意次数，也能表示 0 个字符，但不匹配 NULL
<div>检索 Fish 开头的<br>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name LIKE 'Fish%'<br></div>
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name LIKE '%bean bag%'</div>
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name LIKE 'F%y'</div>
_ 通配符
下划线的用途与 % 一样，但它只匹配单个字符
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_name LIKE '__ inch teddy%'</div>
技巧
通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间<br><br>不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。<br>在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
创建计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式<br><br>需要直接从数据库中检索出转换、计算或格式化过的数据，<br>而不是检索出数据，然后再在客户端应用程序中重新格式化<br>
在SQL语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成。<br>但一般来说，在数据库服务器上完成这些操作比在客户端中完成要快得多。
拼接字段
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>CONCAT(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_name,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>'(',</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_country,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>')'</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>) AS vend_title</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vendors</div><div>ORDER BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_name</div>
使用 CONCAT函数 <br>拼接两个字段 AS 别名（导出列） vend_title
输出 ：<br><br>Bear Emporium(USA)<br><br>Bears R Us(USA)<br><br>Doll House Inc.(USA)<br>
执行算数计算
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>quantity,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>item_price,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>quantity * item_price AS expanded_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orderitems</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order_num = 20008</div>
输出中显示的expanded_price列是一个计算字段，此计算为quantity * item_price。<br>客户端应用现在可以使用这个新计算列，就像使用其他列一样。
<div>操作符 说明</div><div>+<span class="Apple-tab-span" style="white-space:pre"> </span>加</div><div>-<span class="Apple-tab-span" style="white-space:pre"> </span>减</div><div>*<span class="Apple-tab-span" style="white-space:pre"> </span>乘</div><div>/<span class="Apple-tab-span" style="white-space:pre"> </span>除</div><div><br></div>
汇总数据
聚集函数
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;
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>SUM(item_price * quantity) AS total_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orderitems</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order_num = 20005;</div>
聚集不同值
<div>DLL01供应商的平均值 排除相同的值<br>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>AVG(DISTINCT prod_price) AS avg_price</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id = 'DLL01'</div>
组合聚集函数
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>COUNT(*) AS num_items,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>MIN(prod_price) AS price_min,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>MAX(prod_price) AS price_max,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>AVG(prod_price) AS price_avg</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div>
分组数据
创建分组 GROUP BY
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前<br>GROUP BY子句可以包含任意数目的列<br>GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总<br>
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>COUNT(*) AS num_prods</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>GROUP BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id</div>
过滤分组 HAVING
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>cust_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) AS orders</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orders</div><div>GROUP BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>cust_id</div><div>HAVING</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) >= 2</div>
包含 WHERE 和 HAVING<br><br>WHERE子句过滤所有prod_price至少为4的行，<br>然后按vend_id分组数据，<br>HAVING子句过滤计数为2或2以上的分组<br>
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) AS num_prods</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>products</div><div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>prod_price >= 4</div><div>GROUP BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>vend_id</div><div>HAVING</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) >= 2</div>
分组和排序
<div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order_num,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) AS items</div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>orderitems</div><div>GROUP BY</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>order_num</div><div>HAVING</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>count(*) >= 3</div><div>ORDER BY </div><div><span class="Apple-tab-span" style="white-space:pre"> </span>items,order_num</div>
