django ORM - SQL Cheat Sheet

SELECT

전체 출력

특정 column 출력

SELECT name, age FROM Person;

Person.objects.only('name','age')

django only()

distinct (중복제거)

SELECT DISTINCT name, age FROM Person;

Person.objects.values('name', 'age').distinct()

offset / limit (pagination)

SELECT * FROM Person OFFSET 5 LIMIT 5;

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

django = list slicing

WHERE

filter/get 조건에 맞는 데이터 출력

연산자를 이용한 출력

WHERE age > 18;
WHERE age >= 18;
WHERE age < 18;
WHERE age <= 18;
WHERE age != 18;

Person.objects.filter(age__gt=18)
Person.objects.filter(age__gte=18)
Person.objects.filter(age__lt=18)
Person.objects.filter(age__lte=18)
Person.objects.exclude(age=18)

django gt,gte,lt,lte
greater than, grater than equal, lesser than, lesser than equal의 약자
__의 기능인 Field Lookup 이용

Between (연산자 2개)

SELECT * FROM Person WHERE age BETWEEN 10 AND 20;

Person.objects.filter(age__range=(10, 20))

django range
__의 기능인 Field Lookup 이용

Like (검색)

WHERE name like '%A%';
WHERE name like binary '%A%';
WHERE name like 'A%';
WHERE name like binary 'A%';
WHERE name like '%A';
WHERE name like binary '%A';

Person.objects.filter(name__icontains='A')
Person.objects.filter(name__contains='A')
Person.objects.filter(name__istartswith='A')
Person.objects.filter(name__startswith='A')
Person.objects.filter(name__iendswith='A')
Person.objects.filter(name__endswith='A')

SQL like binary : 대소문자 구별하겠다는 뜻
django i : 대소문자 구별하지 않겠다는 뜻

IN (검색)

WHERE id in (1, 2);

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

AND ,OR , NOT

WHERE gender='male' AND age > 25;

WHERE gender='male' OR age > 25;

WHERE NOT gender='male';

Person.objects.filter(gender='male', age__gt=25)

from django.db.models import Q
Person.objects.filter(Q(gender='male') | Q(age__gt=25))

Person.objects.exclude(gender='male')

django
AND는 filter 안에 쉼표로 조건 구분
OR는 Q objects 사용
NOT은 exclude()

NULL

WHERE age is NULL;
WHERE age is NOT NULL;

Person.objects.filter(age__isnull=True)
Person.objects.filter(age__isnull=False)

Person.objects.filter(age=None)
Person.objects.exclude(age=None)

ORDER BY

오름차순(ASC) 내림차순(DESC)

SELECT * FROM Person order by age;

SELECT * FROM Person ORDER BY age DESC;

Person.objects.order_by('age')

Person.objects.order_by('-age')

FIRST/LAST

SELECT FIRST(age) FROM Person;

SELECT LAST(age) FROM Person;

Person.objects.order_by('age').first()
Person.objects.order_by('age').last()

첫번째, 마지막 값 출력

INSERT INTO(CREATE)

UPDATE

열 하나 수정

UPDATE Person SET age = 20 WHERE id = 1;

person = Person.objects.get(id=1)
person.age = 20
person.save()

Person.objects.filter(id=1).update(age=20)

다중 열 수정

UPDATE Person SET age = age * 1.5;

from django.db.models import F

Person.objects.update(age=F('age')*1.5)

django F object 사용

DELETE

모든 열 삭제

특정 열 삭제

DELETE FROM Person WHERE age < 10;

Person.objects.filter(age__lt=10).delete()

AGGREGATION(집계)

MIN, MAX, AVG, SUM, COUNT

SELECT MIN(age) FROM Person;
SELECT MAX(age) FROM Person;
SELECT AVG(age) FROM Person;
SELECT SUM(age) FROM Person;
SELECT COUNT(*) FROM Person;

from django.db.models import Min, Max, Avg, Sum

Person.objects.all().aggregate(Min('age'))
{'age__min': 0}

Person.objects.all().aggregate(Max('age'))
{'age__max': 100}

Person.objects.all().aggregate(Avg('age'))
{'age__avg': 50}

Person.objects.all().aggregate(Sum('age'))
{'age__sum': 5050}

Person.objects.count()

최소,최대,평균,합계 데이터 집계 가능
django aggregate() 메서드를 사용하면 쿼리문 형태로 집계한 데이터 반환

count() 사용시 해당 테이블의 데이터의 총 갯수를 집계함

GROUP BY(Annotate)

COUNT

SELECT gender, COUNT(*) as count FROM Person GROUP BY gender;

Person.objects.values('gender').annotate(count=Count('gender'))

성별(gender)에 따른 인원수 계산(count)

HAVING (COUNT IF)

SELECT gender, COUNT('gender') as count FROM Person GROUP BY gender HAVING count > 1;

Person.objects.annotate(count=Count('gender'))
.values('gender', 'count')
.filter(count__gt=1)

인원수가 1보다 큰 데이터를 성별에 따라 그룹화하여 출력

(참고) Annotate

annotate는 영어로 '주석을 달다' 라는 뜻이다.
엑셀로 치면 계산용 column을 하나 추가하는 것이랑 비슷하다.
1. Field 이름 바꾸기

Products = Order.objects.annotate(name =F('product__name'),price=F('product__price'))
 .values('created','name','price')

F object와 annotate를 이용해서 외래 키 쿼리문 출력 시 product__name을 name으로 변경해서 출력할 수 있게 해줌

2. 특정 기준으로 값을 묶음

Products = Order.values('created_at').annotate(total = Sum('product__price'))

날짜별(created_at)으로 값을 그룹화

3. 모델의 칼럼 갯수 세기(COUNT)
COUNT, HAVING

4. 조건적 Annotate(Case, When)

order_list_2 = order_qs.annotate(
    sales_price=Case(
        When(
            is_cancel=False,  # 결제 취소가 아닌 경우
            then=F('price')
        ),
        default=0
    ),
    cancel_price=Case(
        When(
            is_cancel=True,   # 결제 취소인 경우
            then=F('price')
        ),
        default=0
    )
)

객체에 annotate 변수를 저장하고 Case, When으로 조건걸어서 사용

JOIN

FOREIGN KEY JOIN

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

class Book(models.Model):
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

SELECT_RELATED

SELECT name
FROM Book
LEFT JOIN Publisher
ON Book.publisher_id = Publisher.id
WHERE Book.id=1;

book = Book.objects.select_related('publisher').get(id=1)
book.publisher.name

PREFETCHED_RELATED

SELECT *
FROM Book
WHERE Book.publisher_id = 1;

publisher = Publisher.objects.prefetch_related('book_set').get(id=1)
books = publisher.book_set.all()