TIL#76 Django ORM & MySQL

SELECT name,price FROM products LIMIT 5 OFFSET 5;
-> 5개 건너뛰고 6번째부터 출력됨

Product.objects.all()[5:10]

select name,price from products where id=10;

Product.objects.filter(id=10)

select name,price from products where price between 10000 and 13000;

Product.objects.filter(price__range=(10000, 13000))

文字列の検索
binaryで大文字と小文字を区別します.likeの後ろに検索文字列を書くと、%は他の文字とみなされやすくなります.%new newで終わる文字列、%new% newを含む文字列、new% newで始まる文字列を検索します.
iと入力した場合、大文字と小文字は区別されません.
条件キーリファレンスリンク

select id, price, name from products where id in (10, 20);

Product.objects.filter(id__in=[10,20])

select name, price from products where id<100 and price <10000;

Product.objects.filter(id__lt=100, price__lt=10000)
Product.objects.filter(Q(id__lt=100) & Q(price__lt=10000))

select name,price from products where id>110 or price<5000;

Product.objects.filter(Q(id__gt=110) | Q(price__lt=5000))

select name, price from products where not name like '%다노%';

Product.objects.exclude(name__contains='다노')

select name,price from products order by price;

Product.objects.order_by('price')

select name,price from products order by price desc;

Product.objects.order_by('-price')

select min(price) from products;

Product.objects.all().aggregate(Min('price'))

select max(price) from products;

Product.objects.all().aggregate(Max('price'))

select count(*) from products;

Product.objects.count()

select discount_rate, count(*) as count from products group by discount_rate;

Product.objects.values('discount_rate').annotate(count=Count('discount_rate'))

select discount_rate, count('discount_rate') as count from products group by discount_rate having count > 1;

Product.objects.values('discount_rate').annotate(count=Count('discount_rate')).filter(count__gt=1)