TIL 16 | SQL과 Django ORM

saneeeeeeee_Ya·2021년 4월 26일
0

🛸

목록 보기
16/25
post-thumbnail

  1. 테이블 안에 특정한 row를 가져오기
SELECT price, description FROM products;
products.objects.values('price', 'description').distinct()
  1. 테이블에서 해당 인덱스까지 슬라이싱
SELECT * FROM products LIMIT 10;
products.objects.all()[:10]
  1. Limit, Offset
SELECT * FROM products LIMIT 2 OFFSET 3;

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

products.objects.all()[4:6]
  1. Filter
SELECT * FROM products WHERE id = 1;
products.objects.filter(id=1)
  1. 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)
  1. Between
SELECT * FROM products WHERE price BETWEEN 3000 AND 5000;
products.objects.filter(price__range=(3000, 5000))
  1. 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 : 대소문자 구별하지 않음

  1. IN operator
SELECT * FROM products WHERE id in (1, 2);
products.objects.filter(id__in=[1, 2])
  1. AND
SELECT * FROM products WHERE id = 1 AND price > 3000;
products.objects.filter(id = 1, age__gt=3000)
  1. 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))
  1. NOT
SELECT * FROM products WHERE NOT id = 1;
products.objects.exclude(id=1)
  1. 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)
  1. 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')
  1. Insert
INSERT INTO products VALUES ('커피', 'coffee', 5000);
products.objects.create(korean_name='커피', english_name='coffee', price=5000)
  1. Update
UPDATE products SET price = price * 1.5;
from django.db.models import F

products.objects.update(price=F('price')*1.5)
  1. Delete all rows
DELETE FROM products;
products.objects.all().delete()
  1. Delete specific rows
DELETE FROM products WHERE price < 6000;
products.objects.filter(price__lt=10).delete()
  1. 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()
  1. 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)

                          👇 예시

  1. 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()
profile
🐜https://action2thefuture.github.io/🐜

0개의 댓글