def test_select_limit(self):
with connection.cursor() as cursor:
cursor.execute("SELECT prod_name FROM Products LIMIT 5;")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
Result(prod_name='Fish bean bag toy')
Result(prod_name='Bird bean bag toy')
Result(prod_name='Rabbit bean bag toy')
Result(prod_name='8 inch teddy bear')
Result(prod_name='12 inch teddy bear')
"""
print("=" * 60)
cursor.execute("SELECT prod_name FROM Products LIMIT 5 OFFSET 5;")
"""
分析:
LIMIT 5 OFFSET 5 指示MySQL等数据库返回从第5行起的5行数据.
第一个数字是检索的行数, 第二个数字是指从哪一行开始.
注意:
第一个呗检索的行是第0行, 而不是第1行. 因此, LIMIT 1 OFFSET 1 会检索第2行, 而不是1行.
提示:
MySQL, MariaDB, SQLit 可以把LIMIT 4 OFFSET 3 语句简化为 LIMIT 3,4
使用这个语法, 逗号之前的值对应OFFSET 3, 逗号之后的值对应LIMIT 4 (相反的!! 要小心)
"""
for result in namedtuplefetchall(cursor): # 这个语句的输出是:
print(result)
"""
def test_json_contains(self):
"""JSON_CONTAINS(target, candidate[,path])"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT JSON_CONTAINS(list, '5') AS is5 # 查询内容必须使用字符串类型.
FROM JsonTable;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
Result(is5=0)
Result(is5=1)
"""
cursor.execute("""
SELECT JSON_CONTAINS(list, '[6,5]') AS is1and5 # 查询内容必须使用字符串类型.
FROM JsonTable;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
Result(is1and5=0)
Result(is1and5=1)
"""
cursor.execute("""
SELECT JSON_CONTAINS(dict, '20', '$.age') AS is20 # path:'$.age', path的使用看76页
FROM JsonTable;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_join_multiple_tables(self):
"""
分析 ▼
这个例子显示订单20007中的物品。订单物品存储在OrderItems表中
每个产品按其产品ID存储，它引用Products表中的产品。这些产品通
过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个
产品的记录中。这里的FROM子句列出三个表，WHERE子句定义这两个联
结条件，而第三个联结条件用来过滤出订单20007中的物品。
注意：性能考虑
DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常
耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性
能下降越厉害。
注意：联结中表的最大数目
虽然SQL本身不限制每个联结约束中表的数目，但实际上许多DBMS
都有限制。请参阅具体的DBMS文档以了解其限制。
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products, OrderItems
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
Result(vend_name='Bears R Us', prod_name='18 inch teddy bear', prod_price=Decimal('11.99'))
Result(vend_name='Doll House Inc.', prod_name='Fish bean bag toy', prod_price=Decimal('3.49'))
Result(vend_name='Doll House Inc.', prod_name='Bird bean bag toy', prod_price=Decimal('3.49'))
Result(vend_name='Doll House Inc.', prod_name='Rabbit bean bag toy', prod_price=Decimal('3.49'))
Result(vend_name='Doll House Inc.', prod_name='Raggedy Ann', prod_price=Decimal('4.99'))
"""
print("=" * 60)
# cursor.execute("""
# SELECT cust_name, cust_contact
# FROM Customers, Orders, OrderItems
# WHERE Customers.cust_id = Orders.cust_id
# AND Orders.order_num = OrderItems.order_num
# AND OrderItems.prod_id = 'RGAN01';
# """)
cursor.execute("""
SELECT cust_name, cust_contact
FROM Customers
INNER JOIN Orders O on Customers.cust_id = O.cust_id
INNER JOIN OrderItems OI on O.order_num = OI.order_num
WHERE OI.prod_id = 'RGAN01';
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_select_all_column(self):
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM Products;")
# 注意: 检索不需要的列通常会降低检索速度和应用程序的性能
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_where_is_important(self):
"""
使用WHERE子句建立联结关系似乎有点奇怪，但实际上是有个很充分的
理由的。要记住，在一条SELECT语句中联结几个表时，相应的关系是
在运行中构造的。在数据库表的定义中没有指示DBMS如何对表进行联
结的内容。你必须自己做这件事情。在联结两个表时，实际要做的是将
第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤
条件，只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE
子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们
逻辑上是否能配在一起。
笛卡儿积（cartesian product)
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目
将是第一个表中的行数乘以第二个表中的行数。
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_set_type(self):
"""
SET和ENUM类似, 都是一个字符串对象, 最主要的区别在于SET类型一次可以选取多个成员,
而ENUM则只能选一个.
"""
with connection.cursor() as cursor:
cursor.execute("""
CREATE TABLE TestTable
( # AUTO_INCREMENT: 自增(自动连续编号功能)
id INT ZEROFILL AUTO_INCREMENT PRIMARY KEY, # 主键默认NOT NULL并且UNIQUE(不许重复)
col SET ("a", "b", "c", "d") # 1~8成员的集合, 占1个byte, 更多成员所占的byte看书52页
)
CHARSET=utf8mb4; # 可以选择性增加指定字符编码, 但一般创建数据库设置了字符编码为utf8mb4的话就不用再设置了.
""")
print(cursor.fetchone())
cursor.execute("""
INSERT INTO TestTable(
col
)
VALUES (
"a,b"
), (
"c,d,a"
), (
"d,a"
);
""")
cursor.execute("""
SELECT * FROM TestTable;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_soundex_func(self):
"""
SOUNDEX是一个将任何文本字符串转化为描述其语音表示的字母数字模式的算法.
SOUNDEX考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母
比较.虽然SOUNDEX不是SQL概念,但多数DBMS都提供对SOUNDEX的支持.
说明: SOUNDEX支持
PostgreSQL不支持SOUNDEX(),另外,如果在创建SQLite时使用了
SQLITE_SOUNDEX编译时选项,那么SOUNDEX()在SQLite中就可用.
因为SQLITE_SOUNDEX不是默认的编译时选项, 所以多数SQLite实
现不支持SOUNDEX().
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michell Green');
""")
"""
WHERE子句使用SOUNDEX()函数把cust_contact列值和搜索字符串转换为它们的
SOUNDEX值.因为Michael Green和Michelle Green发音相似,所以它们的
SOUNDEX值匹配,因此WHERE子句正确地过滤出了所需的数据.
"""
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_square_brackets_wildcard(self):
"""
方括号([])通配符用来指定一个字符集,它必须匹配指定位置(通配符的位置)的一个字符.
"""
with connection.cursor() as cursor:
"""找出所有名字以J或M开头的联系人."""
cursor.execute("""
SELECT cust_contact
FROM Customers
WHERE cust_contact
LIKE '[JM]%';
""")
"""可以使用前缀字符^(脱字号)来否定, 譬如查询匹配以J和M之外的任意字符起头的任意联系人名"""
cursor.execute("""
SELECT cust_contact
FROM Customers
WHERE cust_contact
LIKE '^[JM]%';
""")
"""
说明: 并不总是支持集合
与前面描述的通配符不一样,并不是所有DBMS都支持用来创建集合的[].微软
SQL Server支持集合,但是MySQL,Oracle,DB2,SQLite都不支持.
"""
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
def test_inner_join(self):
"""
12.2使用的联结称为等值联结（equi join)，它基于两个表之间的相等测试。
下面这种联结称为内联结（inner join)。其实，可以对这种联结使删微不同
的语法，明确指定联结的类型。下面的SELECT语句返回与12.2例子完全相同的
数据：
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id
""")
"""
分析 ▼
此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这
里,两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用
这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递
给ON的实际条件与传递给WHERE的相同。至于选用哪种语法，请参阅具
体的DBMS文档。
说明：“正确的”语法
ANSI SQL规范首选INNER JOIN语法，之前使用的是简单的等值语法。
其实，SQL语言纯正论者是用鄙视的眼光看待简单语法的。这就是说，
DBMS的确支持简单格式和标准格式，我建议你要理解这两种格式，具体
使用就看你用哪个更顺手了
"""
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_select_multiple_column(self):
with connection.cursor() as cursor:
cursor.execute(
"SELECT prod_id, prod_name, prod_price FROM Products;")
for result in namedtuplefetchall(cursor): # 读取所有
print(result.prod_id, result.prod_name, result.prod_price)
"""
def test_check_single_value(self):
with connection.cursor() as cursor:
cursor.execute(
"SELECT prod_name, prod_price FROM Products WHERE prod_price < 10;"
)
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_select_json(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT * FROM JsonTable;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_json_type(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT JSON_TYPE(dict) AS Dict, JSON_TYPE(list) AS List
FROM JsonTable;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_z_create_select(self):
"""
有一种数据插入不使用INSERT语句。要将一个表的内容复制到一个全
新的表(运行中创建的表)，可以使用CREATE SELECT语句(或者在
SQLServer里也可用SELECT INTO语句) 。
说明：DB2不支持
DB2不支持这里描述的CREATE SELECT。
与INSERT SELECT将数据添加到一个已经存在的表不同，CREATE
SELECT将数据复制到一个新表(有的DBMS可以覆盖已经存在的表，
这依赖于所使用的具体DBMS) 。
下面的例子说明如何使用CREATE SELECT：
"""
with connection.cursor() as cursor:
cursor.execute("""
CREATE TABLE OrdersCopy AS SELECT * FROM Orders;
""")
# 这种复制方法不能复制AUTO_INCREMENT等属性. AUTO_INCREMENT等属性需要在复制后再次进行设置.
# 复制后VARCHAR(100)甚至可能会变成CHAR(100)
# 因此必须复制后用DESC确认表的结构.
cursor.execute("""
SELECT * FROM OrdersCopy;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
print("=" * 60)
# 排序后复制
cursor.execute("""
CREATE TABLE OrdersCopy2 AS
SELECT *
FROM Orders
ORDER BY order_num DESC
LIMIT 2
OFFSET 1;
""")
cursor.execute("""
SELECT * FROM OrdersCopy2;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_exercise1(self):
"""
1. Write a SQL statement to retrieve all customer names (cust_name) from the Customers table,
and display the results sorted from Z to A.
"""
with connection.cursor() as cursor:
cursor.execute("SELECT cust_name FROM Customers ORDER BY 1 DESC ;")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_create_grouping(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT vend_name, prod_name, prod_price
FROM Products, Vendors
WHERE Vendors.vend_id = Products.vend_id;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_select_different_value(self):
with connection.cursor() as cursor:
cursor.execute("SELECT DISTINCT vend_id FROM Products;")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
Result(vend_id='BRS01')
Result(vend_id='DLL01')
Result(vend_id='FNG01')
"""
"""
def test_exercise3(self):
"""
3. Our fictitious store obviously prefers to sell more expensive items, and lots of them.
Write a SQL statement to display the quantity and price (item_price) from the OrderItems
table, sorted with the highest quantity and highest price first.
"""
with connection.cursor() as cursor:
cursor.execute("SELECT quantity, item_price FROM OrderItems ORDER BY 1 DESC, 2 DESC;")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_exercise2(self):
"""
2. Write a SQL statement to retrieve customer id (cust_id) and order number (order_num) from
the Orders table, and sort the results first by customer id, and then by order date in
reverse chronological order.
"""
with connection.cursor() as cursor:
cursor.execute("SELECT cust_id, order_num FROM Orders ORDER BY 1, order_date DESC;")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_exercise2(self):
"""
2. Let’s make the previous challenge more useful. In addition to returning the
customer name and order number, add a third column named OrderTotal containing
the total price of each order. There are two ways to do this, you can create
the OrderTotal column using a subquery on the OrderItems table, or you can
join the OrderItems table to the existing tables and use an aggregate function.
Here’s a hint, watch out for where you need to use fully qualified column names.
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT cust_name, O.order_num, SUM(OI.item_price*OI.quantity) AS OrderTotal
FROM Customers
INNER JOIN Orders O on Customers.cust_id = O.cust_id
INNER JOIN OrderItems OI on O.order_num = OI.order_num
GROUP BY O.order_num
Order By 3;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
Result(cust_name='The Toy Store', order_num=20008, OrderTotal=Decimal('189.60'))
Result(cust_name='Fun4All', order_num=20006, OrderTotal=Decimal('329.60'))
Result(cust_name='Village Toys', order_num=20005, OrderTotal=Decimal('1648.00'))
Result(cust_name='Fun4All', order_num=20007, OrderTotal=Decimal('1696.00'))
Result(cust_name='Village Toys', order_num=20009, OrderTotal=Decimal('1867.50'))
"""
print("=" * 60)
cursor.execute("""
SELECT cust_name, O.order_num, (
SELECT SUM(OI.quantity * OI.item_price)
FROM OrderItems OI
WHERE OI.order_num = O.order_num
) AS OrderTotal
FROM Customers
INNER JOIN Orders O on Customers.cust_id = O.cust_id
Order By 3;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_json_valid(self):
with connection.cursor() as cursor:
cursor.execute("""
SELECT
JSON_VALID('null') AS n1,
JSON_VALID('NULL') AS n2, # json数据类型对大小写敏感
JSON_VALID('false') AS n3,
JSON_VALID('FALSE') AS n4;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
def test_substring_func(self):
"""
从第X个字符开始截取Y个字符
"""
with connection.cursor() as cursor:
cursor.execute("""
SELECT cust_contact, SUBSTRING(cust_contact, 2, 4) AS sub
FROM Customers;
""")
for result in namedtuplefetchall(cursor): # 读取所有
print(result)
"""
