def self_subquery_2_join() -> 'ModelSelect':
"""单表子查询转为 INNER JOIN.
SELECT c1.cust_contact
FROM customer AS c1
JOIN customer AS c2 ON c1.cust_name = c2.cust_name
WHERE c2.cust_contact = 'Jim Jones';
"""
Customer2 = Customer.alias()
return (Customer.select(Customer.cust_contact).join(
Customer2, on=Customer.cust_name == Customer2.cust_name).where(
Customer2.cust_contact == 'Jim Jones'))
def self_subquery():
"""单表子查询.
SELECT cust_contact
FROM customer
WHERE cust_name = (SELECT cust_name
FROM customer
WHERE cust_contact = 'Jim Jones');
"""
sub = (Customer.select(
Customer.cust_name).where(Customer.cust_contact == 'Jim Jones'))
return (Customer.select(
Customer.cust_contact).where(Customer.cust_name == sub))
def function_count() -> Tuple['ModelSelect', 'ModelSelect']:
"""行数.
SELECT COUNT(*) AS num_cust
FROM customer;
SELECT COUNT(cust_email) AS num_cust
FROM customer;
"""
q1 = (Customer.select(count().alias('num_cust')))
c2 = fn.COUNT(Customer.cust_email).alias('num_cust')
q2 = (Customer.select(c2))
return q1, q2
def simple_insert() -> 'ModelInsert':
"""指定列名插入.
INSERT INTO
customer (cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES ('1000000006',
'Toy Land',
'123 Any Street',
'NewYork',
'NY',
'11111',
'USA',
NULL,
NULL);
"""
return (Customer.insert({
Customer.cust_id: '1000000006',
Customer.cust_name: 'Toy Land',
Customer.cust_address: '123 Any Street',
Customer.cust_city: 'NewYork',
Customer.cust_state: 'NY',
Customer.cust_zip: '11111',
Customer.cust_country: 'USA',
Customer.cust_contact: None,
Customer.cust_email: None
}))
def all_union() -> 'ModelSelect':
"""包含重复的组合查询.
SELECT cust_name
FROM customer
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT cust_name
FROM customer
WHERE cust_name = 'Fun4All';
"""
q1 = (Customer.select(
Customer.cust_name).where(Customer.cust_state << ['IL', 'IN', 'MI']))
q2 = (Customer.select(
Customer.cust_name).where(Customer.cust_name == 'Fun4All'))
return q1.union_all(q2)
def order_union() -> 'ModelSelect':
"""包含重复的组合查询.
SELECT cust_name
FROM customer
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name
FROM customer
WHERE cust_name = 'Fun4All'
ORDER BY cust_name;
"""
q1 = (Customer.select(
Customer.cust_name).where(Customer.cust_state << ['IL', 'IN', 'MI']))
q2 = (Customer.select(
Customer.cust_name).where(Customer.cust_name == 'Fun4All'))
return q1.union(q2).order_by(Customer.cust_name)
def simple_delete() -> 'ModelDelete':
"""删除数据.
DELETE
FROM customer
WHERE cust_id = '1000000005'
"""
return (Customer.delete().where(Customer.cust_id == '1000000005'))
def left_outer_join() -> 'ModelSelect':
"""左外联结.
SELECT customer.cust_id, `order`.order_num
FROM customer
LEFT JOIN `order` ON customer.cust_id = `order`.cust_id;
"""
return (Customer.select(Customer.cust_id, Order.order_num).join(
Order, JOIN.LEFT_OUTER, on=Customer.cust_id == Order.cust_id))
def null() -> Tuple['ModelSelect', 'ModelSelect']:
"""空值检查.
SELECT cust_name, cust_email
FROM customer
WHERE cust_email IS NULL;
SELECT cust_name, cust_email
FROM customer
WHERE cust_email != '';
"""
q1 = (Customer.select(Customer.cust_name, Customer.cust_email).where(
Customer.cust_email.is_null(True)))
q2 = (Customer.select(
Customer.cust_name,
Customer.cust_email).where(Customer.cust_email != ''))
return q1, q2
def update_multi() -> 'ModelUpdate':
"""更新多列.
UPDATE customer
SET cust_contact = 'foo',
cust_email = '*****@*****.**'
WHERE cust_id = '1000000005';
"""
return (Customer.update({
Customer.cust_contact: 'foo',
Customer.cust_email: '*****@*****.**'
}).where(Customer.cust_id == '1000000005'))
def triple_select_2_join() -> 'ModelSelect':
"""三层 SELECT 转为 INNER JOIN.
SELECT cust_name
FROM customer
JOIN `order` ON customer.cust_id = `order`.cust_id
JOIN order_item ON `order`.order_num = order_item.order_num
WHERE order_item.prod_id = 'RGAN01';
"""
return (Customer.select(Customer.cust_name).join(
Order, on=Customer.cust_id == Order.cust_id).join(
OrderItem, on=Order.order_num == OrderItem.order_num).where(
OrderItem.prod_id == 'RGAN01'))
def calculation() -> 'ModelSelect':
"""子查询作为计算字段.
SELECT cust_id,
cust_name,
(SELECT COUNT(*)
FROM `order`
WHERE `order`.cust_id = customer.cust_id) AS orders
FROM customer;
"""
sub = (Order.select(count()).where(Order.cust_id == Customer.cust_id))
return (Customer.select(Customer.cust_id, Customer.cust_name,
sub.alias('orders')))
def part_insert() -> 'ModelInsert':
"""插入一部分列.
INSERT INTO
customer (cust_id,
cust_name)
VALUES ('1000000007',
'Toy Land');
"""
return (Customer.insert({
Customer.cust_id: '1000000007',
Customer.cust_name: 'Toy Land'
}))
def calculation_2_left_join() -> 'ModelSelect':
"""子查询作为计算字段转 LEFT JOIN.
SELECT customer.cust_id, customer.cust_name, COUNT(`order`.cust_id)
FROM customer
LEFT JOIN `order` ON customer.cust_id = `order`.cust_id
GROUP BY customer.cust_id;
"""
return (Customer.select(Customer.cust_id, Customer.cust_name,
fn.COUNT(Order.cust_id)).join(
Order,
JOIN.LEFT_OUTER,
on=Customer.cust_id == Order.cust_id).group_by(
Customer.cust_id))
def duplicate_insert() -> 'ModelInsert':
"""插入重复的数据.
INSERT INTO
customer (cust_id,
cust_name)
VALUES ('1000000007',
'Toy Land')
ON DUPLICATE KEY
UPDATE
cust_name = 'Duplicate';
"""
return (Customer.insert({
Customer.cust_id: '1000000007',
Customer.cust_name: 'Toy Land'
}).on_conflict(update={Customer.cust_name: 'Duplicate'}))
def triple_select() -> 'ModelSelect':
"""三层 SELECT.
SELECT cust_name
FROM customer
WHERE cust_id IN (SELECT cust_id
FROM `order`
WHERE order_num IN (SELECT order_num
FROM order_item
WHERE prod_id = 'RGAN01'));
"""
order_nums = (OrderItem.select(
OrderItem.order_num).where(OrderItem.prod_id == 'RGAN01'))
cust_ids = (Order.select(
Order.cust_id).where(Order.order_num << order_nums))
return (Customer.select(
Customer.cust_name).where(Customer.cust_id << cust_ids))