def operator_and() -> Tuple['ModelSelect', 'ModelSelect']:
"""AND.
SELECT vend_id, prod_price
FROM product
WHERE vend_id = 'DLL01' AND prod_price <= 4;
"""
q1 = (Product.select(
Product.vend_id,
Product.prod_price).where((Product.vend_id == 'DLL01')
& (Product.prod_price <= 4)))
q2 = (Product.select(Product.vend_id,
Product.prod_price).where(Product.vend_id == 'DLL01',
Product.prod_price <= 4))
return q1, q2
def not_select() -> 'ModelSelect':
"""按非选择列排序.
SELECT prod_name FROM product
ORDER BY prod_price;
"""
return (Product.select(Product.prod_name).order_by(Product.prod_price))
def single_order() -> 'ModelSelect':
"""单字段排序.
SELECT prod_name FROM product
ORDER BY prod_name;
"""
return (Product.select(Product.prod_name).order_by(Product.prod_name))
def desc() -> 'ModelSelect':
"""降序排列.
SELECT prod_name, prod_price FROM product
ORDER BY prod_price DESC, prod_name;
"""
return (Product.select(Product.prod_name, Product.prod_price).order_by(
Product.prod_price.desc(), Product.prod_name))
def avg() -> 'ModelSelect':
"""平均数.
SELECT AVG(prod_price) AS avg_price
FROM product;
"""
avg_price = fn.AVG(Product.prod_price).alias('avg_price')
return (Product.select(avg_price))
def number_order() -> 'ModelSelect':
"""按列位置排序.
SELECT prod_name, prod_price FROM product
ORDER BY 2, 1;
"""
return (Product.select(Product.prod_name,
Product.prod_price).order_by(SQL('2'), SQL('1')))
def multi_order() -> 'ModelSelect':
"""多列排序.
SELECT prod_name, prod_price FROM product
ORDER BY prod_price, prod_name;
"""
return (Product.select(Product.prod_name, Product.prod_price).order_by(
Product.prod_price, Product.prod_name))
def distinct_avg() -> 'ModelSelect':
"""聚集不同值.
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM product
WHERE vend_id = 'DLL01';
"""
avg_price = fn.AVG(Product.prod_price.distinct()).alias('avg_price')
return (Product.select(avg_price).where(Product.vend_id == 'DLL01'))
def between() -> 'ModelSelect':
"""范围值检查.
SELECT prod_name, prod_price
FROM product
WHERE prod_price BETWEEN 5.99 AND 9.49;
"""
return (Product.select(Product.prod_name, Product.prod_price).where(
Product.prod_price.between(5.99, 9.49)))
def equal() -> 'ModelSelect':
"""相等检查.
SELECT prod_name, prod_price FROM product
WHERE prod_price = 3.49;
"""
return (Product.select(
Product.prod_name,
Product.prod_price).where(Product.prod_price == 3.49))
def single_match() -> 'ModelSelect':
"""匹配单个字符.
SELECT prod_name
FROM product
WHERE prod_name LIKE '_ inch teddy bear';
"""
return (Product.select(Product.prod_name).where(
Product.prod_name**'_ inch teddy bear'))
def any_match() -> 'ModelSelect':
"""匹配任意个字符 (包括 0 个).
SELECT prod_name
FROM product
WHERE prod_name LIKE '%bean bag%';
"""
return (Product.select(Product.prod_name).where(
Product.prod_name**'%bean bag%'))
def operator_not() -> 'ModelSelect':
"""NOT.
SELECT prod_name
FROM product
WHERE NOT vend_id = 'DLL01';
"""
return (Product.select(
Product.prod_name).where(~(Product.vend_id == 'DLL01')))
def operator_in() -> 'ModelSelect':
"""IN.
SELECT prod_name, vend_id
FROM product
WHERE vend_id IN ('DLL01', 'BRS01');
"""
return (Product.select(
Product.prod_name,
Product.vend_id).where(Product.vend_id << ['DLL01', 'BRS01']))
def group_with_count() -> 'ModelSelect':
"""分组计算行数.
SELECT vend_id, COUNT(*) AS num_prods
FROM product
GROUP BY vend_id;
"""
return (Product.select(Product.vend_id,
count().alias('num_prods')).group_by(
Product.vend_id))
def operator_or() -> Tuple['ModelSelect', 'ModelSelect']:
"""OR.
SELECT vend_id, prod_price
FROM product
WHERE vend_id = 'DLL01' OR prod_price <= 4;
"""
return (Product.select(
Product.vend_id,
Product.prod_price).where((Product.vend_id == 'DLL01')
| (Product.prod_price <= 4)))
def filter_group() -> 'ModelSelect':
"""过滤分组.
SELECT vend_id, COUNT(*) AS num_prods
FROM product
GROUP BY vend_id
HAVING COUNT(*) > 2;
"""
return (Product.select(Product.vend_id,
count().alias('num_prods')).group_by(
Product.vend_id).having(count() > 2))
def parenthese_logical() -> 'ModelSelect':
"""使用括号控制逻辑操作符顺序.
SELECT prod_name, prod_price
FROM product
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
AND prod_price >= 10;
"""
return (Product.select(
Product.prod_name,
Product.prod_price).where((
(Product.vend_id == 'DLL01') | (Product.vend_id == 'BRS01'))
& (Product.prod_price >= 10)))
def filter_row_and_group() -> 'ModelSelect':
"""过滤行和分组.
SELECT vend_id, COUNT(*) AS num_prods
FROM product
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
"""
return (Product.select(
Product.vend_id,
count().alias('num_prods')).where(Product.prod_price >= 4).group_by(
Product.vend_id).having(count() >= 2))
def logical_order() -> 'ModelSelect':
"""逻辑操作符顺序.
SELECT prod_name, prod_price
FROM product
WHERE vend_id = 'DLL01'
OR vend_id = 'BRS01'
AND prod_price >= 10;
"""
return (Product.select(
Product.prod_name,
Product.prod_price).where((Product.vend_id == 'DLL01')
| (Product.vend_id == 'BRS01')
& (Product.prod_price >= 10)))
def multi_reduce() -> 'ModelSelect':
"""多个聚集函数.
SELECT COUNT(*) AS count,
MIN(prod_price) AS min,
MAX(prod_price) AS max,
AVG(prod_price) AS avg,
SUM(prod_price) AS sum
FROM product;
"""
return (Product.select(count().alias('count'),
fn.MIN(Product.prod_price).alias('min'),
fn.MAX(Product.prod_price).alias('max'),
fn.AVG(Product.prod_price).alias('avg'),
fn.SUM(Product.prod_price).alias('sum')))
def distinct_multi() -> 'ModelSelect':
"""检索不同的多列.
SELECT DISTINCT vend_id, prod_price FROM product;
"""
return (Product.select(Product.vend_id, Product.prod_price).distinct())
def single() -> 'ModelSelect':
"""检索单列数据.
SELECT prod_name FROM product;
"""
return (Product.select(Product.prod_name))
def multi() -> 'ModelSelect':
"""检索多列数据.
SELECT prod_name, prod_price FROM product;
"""
return (Product.select(Product.prod_name, Product.prod_price))
def all_select() -> 'ModelSelect':
"""检索所有列数据.
SELECT * FROM product;
"""
return (Product.select())
def distinct_single() -> 'ModelSelect':
"""检索不同的单列.
SELECT DISTINCT vend_id FROM product;
"""
return (Product.select(Product.vend_id).distinct())
def limit() -> 'ModelSelect':
"""limit.
SELECT prod_name FROM product LIMIT 5;
"""
return (Product.select(Product.prod_name).limit(5))
def offset() -> 'ModelSelect':
"""offset.
SELECT prod_name FROM product LIMIT 5 OFFSET 5;
"""
return (Product.select(Product.prod_name).limit(5).offset(5))