직접 해보고 비교해 본 결과들 기록해두기📝
<n번째 줄까지 출력하기>
SELECT name,price FROM Products LIMIT 5;
Product.objects.all()[:5]
<n번째부터 n번째까지 출력하기>
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
가 들어가면 대소문자 구분을 하지 않는다.
조건키워드 참고링크
id 가 10,20 인 상품 출력하기
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'))
-> 같은 방식으로 평균(AVG), 합(SUM) 도 가능하다.
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)