def test_sum_func(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT SUM(quantity) AS items_ordered
FROM OrderItems;
""")
"""
说明: NULL值
SUM()函数忽略列值为NULL的行.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'items_ordered': Decimal('1430')}
"""
print("=" * 60)
cursor.execute("""
SELECT SUM(quantity * item_price) AS total_price
FROM OrderItems;
""")
"""
说明: NULL值
SUM()函数忽略列值为NULL的行.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_count_func(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT COUNT(*) AS num_cust
FROM Customers;
""")
"""
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值.
- 使用COUNT(column)对特定列中具有值的行进行计数, 忽略NULL值.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'num_cust': 5}
"""
print("=" * 60)
cursor.execute("""
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
""")
"""
- 使用COUNT(column)对特定列中具有值的行进行计数, 忽略NULL值.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
def test_optimize_or(self):
"""
优化OR条件查询
对于含有OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引;
如果没有索引,则应该考虑增加索引.
"""
# 例子1: 当在建有复合索引的列store_id和film_id上面做OR操作,
# 是不能用到索引idx_store_id_film_id
with connection.cursor() as cursor:
cursor.execute("""
explain
select *
from inventory
where store_id<10 or film_id<10;
""")
for result in dictfetchall(cursor):
print(result)
_ = {
"id": 1,
"select_type": "SIMPLE",
"table": "inventory",
"partitions": None,
"type": "ALL",
"possible_keys":
"idx_store_id_film_id,inventory_film_id_609e926a_fk_film_film_id",
"key": None,
"key_len": None,
"ref": None,
"rows": 4581,
"filtered": 55.55,
"Extra": "Using where",
}
# 例子2: 当在建有独立索引的列last_name和customer_id上面做OR操作,
# 是可以正确地用到索引,MySQL处理含有OR的查询实际是对OR的各个
# 字段分别查询后的结果进行了UNION操作.
with connection.cursor() as cursor:
cursor.execute("""
explain
select *
from customer
where last_name='MORGAN' or customer_id<10;
""")
for result in dictfetchall(cursor):
print(result)
_ = {
"id": 1,
"select_type": "SIMPLE",
"table": "customer",
"partitions": None,
"type": "index_merge",
"possible_keys": "PRIMARY,idx_last_name",
"key": "idx_last_name,PRIMARY",
"key_len": "182,2",
"ref": None,
"rows": 10,
"filtered": 100.0,
"Extra": "Using union(idx_last_name,PRIMARY); Using where",
}
def test_mrr(self):
"""
利用MRR优化JOIN操作
"""
with connection.cursor() as cursor:
cursor.execute(
f"desc select * from payment where customer_id between 1 and 200;"
)
for result in dictfetchall(cursor):
print(result)
_ = {
"id": 1,
"select_type": "SIMPLE",
"table": "payment",
"partitions": None,
"type": "ALL",
"possible_keys":
"payment_customer_id_cfa68abe_fk_customer_customer_id",
"key": None,
"key_len": None,
"ref": None,
"rows": 16049,
"filtered": 33.92,
"Extra": "Using where", # 没有使用MRR(using MRR)
}
# 通过设置mrr和mrr_cost_based这两个优化器参数使用MRR
cursor.execute("set optimizer_switch='mrr=on,mrr_cost_based=off';")
# mrr参数控制MRR特性是否打开(默认on);
# mrr_cost_based控制是否根据优化器的计算成本来觉得使用MRR特性(默认on);
# 如果希望尽可能使用MRR,可以将此参数设置为off.
cursor.execute(
f"desc select * from payment where customer_id between 1 and 200;"
)
for result in dictfetchall(cursor):
print(result)
_ = {
"id": 1,
"select_type": "SIMPLE",
"table": "payment",
"partitions": None,
"type": "range",
"possible_keys":
"payment_customer_id_cfa68abe_fk_customer_customer_id",
"key": "payment_customer_id_cfa68abe_fk_customer_customer_id",
"key_len": "2",
"ref": None,
"rows": 5444,
"filtered": 100.0,
"Extra": "Using index condition; Using MRR", # 成功使用MRR
}
def test_count_func(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT cust_name, cust_state, (
SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id
) AS orders
FROM Customers
ORDER BY cust_name;
""")
"""
分析 ▼
这条SELECT语句对Customers表中每个顾客返回三列：cust_name、cust_state和orders。
orders是一个计算字段，它是由圆括号中的子査询建立的。该子查询对检索出的每个顾客执行一
次。在此例中，该子查询执行了5次，因为检索出了5个顾客。
子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名，而不只是
列名(cust_id)。它指定表名和列名(Orders.cust_id和Customers.cust_id)。下面的
WHERE子句告诉SQL，比较Orders表中的cust_id和当前正从Customers表中检索的cust_id:
WHERE Orders.cust_id = Customers.cust_id
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'cust_name': 'Fun4All', 'cust_state': 'IN', 'orders': 1}
{'cust_name': 'Fun4All', 'cust_state': 'AZ', 'orders': 1}
{'cust_name': 'Kids Place', 'cust_state': 'OH', 'orders': 0}
{'cust_name': 'The Toy Store', 'cust_state': 'IL', 'orders': 1}
{'cust_name': 'Village Toys', 'cust_state': 'MI', 'orders': 2}
"""
"""
def test_trim_func(self):
with connection.cursor() as cursor:
"""
如果想去掉返回的数据不需要的空格.可用使用TRIM函数
大多数DBMS都支持:
TRIM() :去掉字符串左右两边的空格.
LTRIM():去掉字符串左两边的空格.
RTRIM():去掉字符串右两边的空格.
"""
# 使用MySQL或MariaDB时需要使用的语句:
cursor.execute("""
SELECT CONCAT(vend_name, TRIM(' ( '), vend_country, RTRIM(') '))
AS 'vend title'
FROM Vendors
ORDER BY vend_name;
""")
"""
注意: 别名
别名的名字既可以是一个单词,也可用是一个字符串.如果是后者(譬如vend title),
字符串应该括在引号中.虽然这种做法是合法的,但不建议这么去做.多单词的名字可读
性高,不过会给客户端应用带来各种问题.因此,别名最常见的使用是将多个单词的列名
重命名为一个单词的名字.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_exercise1(self):
"""
1. Using INSERT and columns specified, add yourself to the Customers table.
"""
with connection.cursor() as cursor:
cursor.execute("""
INSERT INTO Customers(
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES (
1000000007,
'Me Gustas',
'广东',
'广州',
'GD',
'529999',
'CHA'
);
""")
cursor.execute("""
SELECT *
FROM Customers
WHERE cust_id = 1000000007;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_value_order_by(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
vend_id, prod_price
FROM
Products
WHERE
vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'vend_id': 'BRS01', 'prod_price': Decimal('11.99')}
{'vend_id': 'DLL01', 'prod_price': Decimal('3.49')}
{'vend_id': 'DLL01', 'prod_price': Decimal('3.49')}
{'vend_id': 'DLL01', 'prod_price': Decimal('3.49')}
{'vend_id': 'DLL01', 'prod_price': Decimal('4.99')}
"""
"""
结果:
返回的行中有4行价格小于10美元, 显然返回的行未按预期的进行过滤.
错误示范的结果解释:
原因在于求值的顺序. SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符.
当SQL看到上述WHERE子句时,它理解为:由供应商BRS01制造的价格为10美元以上的所
有产品,以及由供应商DLL01制造的所有产品,而不管其价格如何.
"""
print("=" * 60)
cursor.execute("""
SELECT
vend_id, prod_price
FROM
Products
WHERE
(vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
""")
"""
解决方法: 使用圆括号对操作符进行明确分组. 因为圆括号具有比AND或OR操作符更高的优先级.
提示:
任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号
明确地分组操作符.不要过分依赖默认求值顺序,即使它确实如你希
望的那样.使用圆括号没有任何坏处,它能消除歧义.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def dorp_partition(self, cursor, partition):
min_partition: str = min(partition, key=lambda x: x.replace("p", ""))
cursor.execute(f"ALTER TABLE TestTable DROP PARTITION {min_partition};")
cursor.execute(self.check_partitions)
for result in dictfetchall(cursor):
print(result)
"""
def test_sort_data_desc(self):
"""
如果想再多个列上进行降序排序, 必须对每一列指定DESC关键字
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT
prod_price, prod_name, prod_id
FROM
Products
ORDER BY
prod_price
DESC;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'prod_price': Decimal('11.99'), 'prod_name': '18 inch teddy bear', 'prod_id': 'BR03'}
{'prod_price': Decimal('9.49'), 'prod_name': 'King doll', 'prod_id': 'RYL01'}
{'prod_price': Decimal('9.49'), 'prod_name': 'Queen doll', 'prod_id': 'RYL02'}
{'prod_price': Decimal('8.99'), 'prod_name': '12 inch teddy bear', 'prod_id': 'BR02'}
{'prod_price': Decimal('5.99'), 'prod_name': '8 inch teddy bear', 'prod_id': 'BR01'}
{'prod_price': Decimal('4.99'), 'prod_name': 'Raggedy Ann', 'prod_id': 'RGAN01'}
{'prod_price': Decimal('3.49'), 'prod_name': 'Fish bean bag toy', 'prod_id': 'BNBG01'}
{'prod_price': Decimal('3.49'), 'prod_name': 'Bird bean bag toy', 'prod_id': 'BNBG02'}
{'prod_price': Decimal('3.49'), 'prod_name': 'Rabbit bean bag toy', 'prod_id': 'BNBG03'}
"""
print("=" * 60)
# 多列排序, 某个列为顺序, 某个列为降序
cursor.execute("""
SELECT
prod_price, prod_name, prod_id
FROM
Products
ORDER BY
prod_price DESC,
prod_name;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_multiple_column_sort_data(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
prod_id, prod_price, prod_name
FROM
Products
ORDER BY
prod_price, prod_name;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_show_query_log(self):
"""
慢查询日志配置
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启
使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果
要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）。
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
"""
with connection.cursor() as cursor:
cursor.execute("show variables like '%slow_query_log%';")
for result in dictfetchall(cursor):
print(result)
"""
{'Variable_name': 'slow_query_log', 'Value': 'OFF'}
{'Variable_name': 'slow_query_log_file', 'Value': '/var/lib/mysql/9535a632d1ca-slow.log'}
"""
cursor.execute("set global slow_query_log=1")
cursor.execute("show variables like '%slow_query_log%';")
for result in dictfetchall(cursor):
print(result)
"""
def test_table_alias(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT cust_name, cust_contact
FROM Customers C, Orders O, OrderItems OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_combination(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
COUNT(*) AS num_items,
MAX(prod_price) AS max_price,
MIN(prod_price) AS min_price,
AVG(prod_price) AS avg_price
FROM Products;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_self_join(self):
"""
假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求
首先找出JimJones工作的公司，然后找出在该公司工作的顾客。下面是
解决此问题的一种方法：
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT C1.cust_name, C1.cust_contact, C2.cust_contact AS contact2
FROM Customers C1
INNER JOIN Customers C2 ON C1.cust_name = C2.cust_name
WHERE C2.cust_contact = 'Jim Jones'
ORDER BY C1.cust_name;
""")
"""
提示：用自联结而不用子查询
自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查
询语句. 虽然最终的结果是相同的，但许多DBMS处理联结远比处理
子查询快得多应该试一下两种方法，以确定哪一种的性能更好。
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'cust_id': '1000000003', 'cust_name': 'Fun4All', 'cust_contact': 'Jim Jones'}
{'cust_id': '1000000004', 'cust_name': 'Fun4All', 'cust_contact': 'Denise L. Stephens'}
"""
print("=" * 60)
cursor.execute("""
SELECT C1.cust_name, C1.cust_contact, C2.cust_contact AS contact2
FROM Customers C1
INNER JOIN Customers C2 ON C1.cust_name = C2.cust_name
# WHERE C2.cust_contact = 'Jim Jones'
ORDER BY C1.cust_name;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_group_by_connection(self):
"""
聚合函数也可以与联结一起使用.
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT Customers.cust_id, COUNT(O.order_num) AS num_ord
FROM Customers
INNER JOIN Orders O on Customers.cust_id = O.cust_id
GROUP BY Customers.cust_id;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
"""
"""
分析 ▼
这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。
GROUP BY子句按顾客分组数据，因此，函数调用COUNT(O.order_num)
对每个顾客的订单计数，将它作为num_ord返回。
"""
print("=" * 60)
# 聚集函数也可以方便地与其他联结一起使用。请看下面的例子
cursor.execute("""
SELECT Customers.cust_id, COUNT(O.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders O on Customers.cust_id = O.cust_id
GROUP BY Customers.cust_id;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
"""
"""
def test_show_processlist(self):
"""
show processlist 是显示用户正在运行的线程，实时地查看SQL语句的执行情况,
需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到
自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了
PROCESS 权限。
https://zhuanlan.zhihu.com/p/30743094
"""
with connection.cursor() as cursor:
cursor.execute("show processlist;")
for result in dictfetchall(cursor):
print(result)
"""
def test_avg_func(self):
"""
说明:
AVG()函数忽略列值为NULL的行.
注意: 只用于单个列
AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数
给出.为了获得多个列的平均值,必须使用多个AVG()函数.只有一个例
外是要从多个列计算出一个值. 下面会说.
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT AVG(prod_price) AS avg_price
FROM Products;
""")
"""
AVG(): 返回某列的平均值
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
{'avg_price': Decimal('6.823333')}
"""
print("=" * 60)
cursor.execute("""
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
""")
"""
返回某列过滤后的平均值
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def add_partition(self, cursor, partition):
max_partition: str = max(partition, key=lambda x: x.replace("p", ""))
next_range = int(max_partition.replace('p', '')) + 1
next_partition: str = f"p{90}"
range_fun = lambda x: 10 * x + 1995
cursor.execute(f"""
ALTER TABLE TestTable ADD PARTITION (
PARTITION {next_partition} VALUES LESS THAN ({range_fun(next_range)})
);
""")
cursor.execute(self.check_partitions)
for result in dictfetchall(cursor):
print(result)
"""
def test_and_operator(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
prod_id, prod_name, prod_price
FROM
Products
WHERE
vend_id = 'DLL01' AND prod_price <= 4;
""")
"""
AND: 用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_or_operator(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
prod_id, prod_name, prod_price
FROM
Products
WHERE
vend_id = 'DLL01' OR vend_id = 'BRS01';
""")
"""
OR: 与AND相反,OR操作符告诉DBMS匹配任一条件而不是同时匹配两个条件.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_case_when(self):
"""根据条件改变输入值"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT (quantity*item_price) AS total,
CASE
WHEN (quantity*item_price) >= 1000 THEN '大买入'
WHEN (quantity*item_price) >= 500 THEN '中买入'
WHEN (quantity*item_price) >= 100 THEN '小买入'
ELSE '忽略买入'
END AS '评价'
FROM OrderItems;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_not_operator(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT vend_id, prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
""")
"""
NOT: 该关键字在WHERE子句中用来否定其后条件
在复杂的子句中, NOT非常有用.
譬如, 在与IN操作符联合使用时,NOT可以非常简单地找出与条件列表不匹配得行
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_min_func(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT MIN(prod_price) AS min_price
FROM Products;
""")
"""
提示: 对非数值数据使用MIN()
虽然MIN()一般用来找出最小的数值或日期值,但许多(并非所有)DBMS允许将它用来
返回任意列中的最小值,包括返回文本列中的最小值.用于文本数据时,MIN()返回按
该列排序后的最前一行.
说明: NULL值
MIN()函数忽略列值为NULL的行.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def view_table_row(self, cursor) -> List[str]:
cursor.execute("""
INSERT INTO TestTable VALUES ("1994"), ("2017");
""")
cursor.execute(self.check_partitions)
partition: list = [] # 收集分区的名称
for result in dictfetchall(cursor):
print(result)
partition.append(result["PARTITION_NAME"])
"""
{'PARTITION_NAME': 'p0', 'descr': '1995', 'position': 1, 'TABLE_ROWS': 1}
{'PARTITION_NAME': 'p1', 'descr': '2005', 'position': 2, 'TABLE_ROWS': 0}
{'PARTITION_NAME': 'p2', 'descr': '2015', 'position': 3, 'TABLE_ROWS': 0}
{'PARTITION_NAME': 'p3', 'descr': '2025', 'position': 4, 'TABLE_ROWS': 1}
"""
return partition
def test_insert_part_raw(self):
"""
假如要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求
首先找出JimJones工作的公司，然后找出在该公司工作的顾客。下面是
解决此问题的一种方法：
"""
with connection.cursor() as cursor:
cursor.execute("""
INSERT INTO Customers(
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES (
1000000006,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA'
);
""")
"""
注意：省略列
如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列
必须满足以下某个条件.
- 该列定义为允许NULL值（无值或空值）。
- 在表定义中给出默认值。这表示如果不给出值，将使用默认值。
注意：省略所需的值
如果表中不允许有NULL值或者默认值，这时却省略了表中的值,
DBMS就会产生生错误消息，相应的行不能成功插入。
"""
cursor.execute("""
SELECT *
FROM Customers
WHERE cust_id = 1000000006;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_arithmetic_calculation(self):
"""
SQL算术操作符 +(加), -(减), *(乘), /(除), DIV(除后结果取整), %(除后结果取余)
SELECT语句为测试,检验函数和计算提供了很好的方法.虽然SELECT通常用于从表中检索数据,
但是省略了FROM子句后就是简单地访问和处理表达式,例如SELECT 3 * 2;将返回6,
SELECT Trim(' ABC ');将返回ABC;SELECT Curdate();使用Curdate()函数返回当
前日期和时间.
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT prod_id, quantity, item_price, quantity DIV item_price AS total
FROM OrderItems
WHERE order_num = 20008;
""")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_sort_data(self):
"""
子句:
SQL语句由子句构成,有些子句是必需的,有些则是可选的.一个子句通常由一个关键字加上所提供的数据组成.
子句的例子有SELECT语句的FROM子句.
注意: ORDER BY 子句的位置
在指定一条ORDER BY 子句时,应该保证它是SELECT 语句中最后一条子句.如果它不是最后的子句, 报错
提示: 通过非选择列进行排序
通常,ORDER BY 子句中使用的列将是为显示而选择的列.但是,实际上并不是一定要这样,用非见搜的列排序
数据是完全ok的.
"""
with connection.cursor() as cursor:
cursor.execute("SELECT prod_name FROM Products ORDER BY prod_name;")
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_natural_join(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
FROM Customers C
INNER JOIN Orders O on C.cust_id = O.cust_id
INNER JOIN OrderItems OI on O.order_num = OI.order_num
WHERE OI.prod_id = 'RGAN01'
""")
"""
分析 ▼
在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以
没有重复的列被检索出来。
事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都
不会用到不是自然联结的内联结。
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""
def test_in_operator(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
vend_id, prod_name
FROM
Products
WHERE
vend_id IN ('DLL01', 'BRS01');
""")
"""
IN操作符完成了与OR相同的功能, 为何要使用IN操作符?
1. IN操作付一般比一组OR操作符执行得更快.
2. 在有很多合法选项时,IN操作符的语法更清楚, 更直观.
3. 在与其他AND和OR操作符组合使用IN时,求值顺序更容易管理.
4. IN得最大优点是可以包含其他SELECT语句,能够更动态地建立WHERE子句. 第11课会介绍.
"""
for result in dictfetchall(cursor): # 读取所有
print(result)
"""