[TIL]Django ORM과 SQL 관계

Django ORM과 SQL 관계

---

1-1. SELECT

• Select all rows

  SELECT * FROM Person;

  persons = Person.objects.all()
  for person in persons:
      print(person.name)
      print(person.gender)
      print(person.age)

• Select 특정 column들

  SELECT name, age FROM Person;

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

• Fetch distinct rows

  SELECT DISTINCT name, age FROM Person;

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

• Fetch specific number of rows

  SELECT * FROM Person LIMIT 10;

  Person.objects.all()[:10]

• LIMIT AND OFFSET keywords

  SELECT * FROM Person OFFSET 5 LIMIT 5;

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

1-2. WHERE Clause

• Filter by single column

  SELECT * FROM Person WHERE id = 1;

  Person.objects.filter(id=1)

• Filter by comparison operators

  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)

• BETWEEN Clause

  SELECT * FROM Person WHERE age BETWEEN 10 AND 20;

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

• LIKE operator

  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')

• IN operator

  WHERE id in (1, 2);

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

• AND, OR and NOT Operators

  WHERE gender='male' AND age > 25;

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

  WHERE gender='male' OR age > 25;

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

  WHERE NOT gender='male';

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

• NULL Values

  WHERE age is NULL;
  WHERE age is NOT NULL;

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

  # Alternate approach
  Person.objects.filter(age=None)
  Person.objects.exclude(age=None)

1-3. ORDER BY Keyword

• Ascending Order

  SELECT * FROM Person order by age;

  Person.objects.order_by('age')

• Descending Order

  SELECT * FROM Person ORDER BY age DESC;

  Person.objects.order_by('-age')

• INSERT INTO Statement

  INSERT INTO Person VALUES ('Jack', '23', 'male');

  Person.objects.create(name='jack', age=23, gender='male)

1-4. UPDATE Statement

• Update single row

  UPDATE Person SET age = 20 WHERE id = 1;

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

• Update multiple rows

  UPDATE Person SET age = age * 1.5;

  from django.db.models import F
  
  Person.objects.update(age=F('age')*1.5)

1-5. DELETE Statement

• Delete all rows

  DELETE FROM Person;

  Person.objects.all().delete()

• Delete specific rows

  DELETE FROM Person WHERE age < 10;

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

1-6. Aggregation

• MIN Function

  SELECT MIN(age) FROM Person;

  from django.db.models import Min
  Person.objects.all().aggregate(Min('age'))
  {'age__min': 0}

• MAX Function

  SELECT MAX(age) FROM Person;

  >>> from django.db.models import Max
  >>> Person.objects.all().aggregate(Max('age'))
  {'age__max': 100}

• AVG Function

  SELECT AVG(age) FROM Person;

  >>> from django.db.models import Avg
  >>> Person.objects.all().aggregate(Avg('age'))
  {'age__avg': 50}

• SUM Function

  SELECT SUM(age) FROM Person;

  >>> from django.db.models import Sum
  >>> Person.objects.all().aggregate(Sum('age'))
  {'age__sum': 5050}

• COUNT Function

  SELECT COUNT(*) FROM Person;

  Person.objects.count()

1-7. GROUP BY Statement

• Count of Person by gender

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

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

1-8. HAVING Clause

• Count of Person by gender if number of person is greater than 1

  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-9. JOINS

• Consider a foreign key relationship between books and publisher.

  class Publisher(models.Model):
      name = models.CharField(max_length=100)
  
  class Book(models.Model):
      publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

• Fetch publisher name for a book

  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

• Fetch books which have specific publisher

  SELECT *
  FROM Book
  WHERE Book.publisher_id = 1;

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