TIL 16 | SQL과 Django ORM

1. 테이블 안에 특정한 row를 가져오기

SELECT price, description FROM products;

products.objects.values('price', 'description').distinct()

2. 테이블에서 해당 인덱스까지 슬라이싱

SELECT * FROM products LIMIT 10;

products.objects.all()[:10]

3. Limit, Offset

SELECT * FROM products LIMIT 2 OFFSET 3;

4번째 row부터 2개의 row를 출력

products.objects.all()[4:6]

4. Filter

SELECT * FROM products WHERE id = 1;

products.objects.filter(id=1)

5. Filter by comparison operators

SELECT * FROM products WHERE price > 5000;
SELECT * FROM products WHERE price >= 5000;
SELECT * FROM products WHERE price < 5000;
SELECT * FROM products WHERE price <= 5000;
SELECT * FROM products WHERE price != 5000;

products.objects.filter(price__gt=18)
products.objects.filter(price__gte=18)
products.objects.filter(price__lt=18)
products.objects.filter(price__lte=18)
products.objects.exclude(price=18)

6. Between

SELECT * FROM products WHERE price BETWEEN 3000 AND 5000;

products.objects.filter(price__range=(3000, 5000))

7. LIKE operator
   해당 문자열이 있는지 확인

SELECT * FROM products WHERE korean_name like '%A%';
SELECT * FROM products WHERE korean_name like binary '%A%';
SELECT * FROM products WHERE korean_name like 'A%';
SELECT * FROM products WHERE korean_name like binary 'A%';
SELECT * FROM products WHERE korean_name like '%A';
SELECT * FROM products WHERE korean_name like binary '%A';

products.objects.filter(korean_name__icontains='A')
products.objects.filter(korean_name__contains='A')
products.objects.filter(korean_name__istartswith='A')
products.objects.filter(korean_name__startswith='A')
products.objects.filter(korean_name__iendswith='A')
products.objects.filter(korean_name__endswith='A')

i-swith : 대소문자 구별하지 않음

8. IN operator

SELECT * FROM products WHERE id in (1, 2);

products.objects.filter(id__in=[1, 2])

9. AND

SELECT * FROM products WHERE id = 1 AND price > 3000;

products.objects.filter(id = 1, age__gt=3000)

10. OR

SELECT * FROM products WHERE id = 1 OR price > 3000;

from django.db.models import Q
products.objects.filter(Q(id = 1) | Q(age__gt=3000))

11. NOT

SELECT * FROM products WHERE NOT id = 1;

products.objects.exclude(id=1)

12. NULL

SELECT * FROM products WHERE price is NULL;
SELECT * FROM products WHERE price is NOT NULL;

products.objects.filter(price__isnull=True)
products.objects.filter(price__isnull=False)

# Alternate approach
products.objects.filter(price=None)
products.objects.exclude(price=None)

13. ORDER BY Keyword

SELECT * FROM products ORDER by price;
# price를 오름차순
SELECT * FROM products ORDER bY price DESC;

# price를 내림차순

products.objects.order_by('price')
products.objects.order_by('-price')

14. Insert

INSERT INTO products VALUES ('커피', 'coffee', 5000);

products.objects.create(korean_name='커피', english_name='coffee', price=5000)

15. Update

UPDATE products SET price = price * 1.5;

from django.db.models import F

products.objects.update(price=F('price')*1.5)

16. Delete all rows

DELETE FROM products;

products.objects.all().delete()

17. Delete specific rows

DELETE FROM products WHERE price < 6000;

products.objects.filter(price__lt=10).delete()

18. Aggregation

• MAX

SELECT MIN(price) FROM products;

from django.db.models import Min

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

• MIN

SELECT MAX(price) FROM products;

from django.db.models import Max

producsts.objects.all().aggregate(Max('age'))

• AVG

SELECT AVG(price) FROM products;

from django.db.models import Avg
products.objects.all().aggregate(Avg('price'))

• SUM

SELECT SUM(price) FROM products;

from django.db.models import Sum
products.objects.all().aggregate(Sum('price'))

• COUNT

SELECT COUNT(*) FROM products;

products.objects.count()

18. GROUP

select price, count('price') as count from products group by price

products.objects.values('price').annotate(count=Count('price'))

• HAVING

SELECT price, count('price') as count FROM products group by price having count >  0;

products.objects.annotate(count=Count('price'))
.values('price', 'count')
.filter(count__gt=0)

                          👇 예시

18. JOIN

models.py
class Products(models.Model):
    name = models.CharField(max_length=100)

class Categories(models.Model):
    products = models.ForeignKey(Products, on_delete=models.CASCADE)

• 가져오기

SELECT name
FROM Categories
LEFT JOIN Products
ON Categories.products_id = Products.id
WHERE Products.id=1;

categories = Categories.objects.select_related('products').get(id=1)
categories.products.name

• 특정 products id를 가진 카테고리 가져오기

SELECT *
FROM Categories
WHERE Categories.products_id = 1;

products = Products.objects.prefetch_related('categories_set').get(id=1)
categories = products.categories_set.all()