[DB] SQL with django ORM

1. 기본 CRUD 로직

1. 모든 user 레코드 조회

   # orm
   
   User.objects.all()

   -- sql
   
   SELECT *
   FROM users_user

2. user 레코드 생성

   # orm
   
   User.objects.create(
       first_name='길동',
       last_name='홍',
       age=100,
       country='제주도',
       phone='010-1234-5678',
       balance=10000
   )

   -- sql
   
   INSERT INTO users_user VALUES (102, '길동', '김', 100,'경상북도', '010-1100);234-1234', 100);
   SELECT * FROM users_user LIMIT 2 OFFSET 100;

   • 하나의 레코드를 빼고 작성 후 NOT NULL constraint 오류를 orm과 sql에서 모두 확인 해보세요.

3. 해당 user 레코드 조회

   • 102 번 id의 전체 레코드 조회

   # orm
   
   User.objects.get(pk=102)

   -- sql
   
   SELECT *
   FROM users_user
   WHERE id=102

4. 해당 user 레코드 수정

   • ORM: 102 번 글의 last_name 을 '김' 으로 수정
   • SQL: 102 번 글의 first_name 을 '철수' 로 수정

   # orm
   
   user = User.objects.get(pk=102)
   user.last_name = '김'
   user.save()

   -- sql
   
   UPDATE users_user SET first_name='철수' WHERE id=102;

5. 해당 user 레코드 삭제

   • ORM: 102 번 글 삭제
   • SQL: 101 번 글 삭제

   # orm
   
   User.objects.get(pk=102).delete()

   -- sql
   
   DELETE FROM users_user WHERE id=101;

---

2. 조건에 따른 쿼리문

1. 전체 인원 수

   • User 의 전체 인원수

   # orm
   
    User.objects.count()

   -- sql
   
   SELECT COUNT(*)
   FROM users_user

2. 나이가 30인 사람의 이름

   • ORM : .values 활용
     • 예시: User.objects.filter(조건).values(컬럼이름)

   # orm
   
   User.objects.filter(age=30).values('first_name')

   -- sql
   
   SELECT first_name
   FROM user_user
   WHERE age = 30;

3. 나이가 30살 이상인 사람의 인원 수

   • ORM: __gte , __lte , __gt, __lt -> 대소관계 활용

   # orm
   
   User.objects.filter(age__gte=30).count()

   -- sql
   
   SELECT COUNT(*) FROM users_user
   WHERE age >= 30;

4. 나이가 20살 이하인 사람의 인원 수

   # orm
   
   User.objects.filter(age__lte=20).count()

   -- sql
   
   SELECT COUNT(*) FROM users_user
   WHERE age <= 20;

5. 나이가 30이면서 성이 김씨인 사람의 인원 수

   # orm
   
   User.objects.filter(age=30, last_name='김').count()

   -- sql
   
   SELECT COUNT(*) FROM users_user
   WHERE age=30 AND last_name='김';

6. 나이가 30이거나 성이 김씨인 사람

   # orm
   
   from django.db.models import Q
   User.objects.filter(Q(age=30)| Q(last_name='김'))

   -- sql
   
   SELECT * FROM users_user
   WHERE age=30 OR last_name='김';

7. 지역번호가 02인 사람의 인원 수

   • ORM: __startswith

   # orm
   
   User.objects.filter(phone__startswith='02-').count()

   -- sql
   
   SELECT COUNT(*) FROM users.user
   WHERE phone LIKE '02-%'

8. 거주 지역이 강원도이면서 성이 황씨인 사람의 이름

   # orm
   
   User.objects.filter(country='강원도',last_name='황').values('first_name')

   -- sql
   
   SELECT first_name FROM users_user
   WHERE counrty='강원도' AND last_name='황';

---

3. 정렬 및 LIMIT, OFFSET

1. 나이가 많은 사람순으로 10명

   # orm
   
   Users.objects.order_by('-age')[:10]

   -- sql
   
   SELECT * FROM users_user
   ORDER BY age DESC LIMIT 10;

2. 잔액이 적은 사람순으로 10명

   # orm
   
   Users.objects.order_by('balance')[:10]

   -- sql
   
   SELECT * FROM users_user
   ORDER BY balance LIMIT 10;

3. 잔고는 오름차순, 나이는 내림차순으로 10명

   # orm
   
   Users.objects.order_by('balance','-age')[:10]

   -- sql
   
   SELECT * FROM users_user
   ORDER BY balance, age DESC LIMIT 10;

4. 성, 이름 내림차순 순으로 5번째 있는 사람

   # orm
   
   Users.objects.order_by('-last_name','-first_name')[4]

   -- sql
   
   SELECT * FROM users_user
   ORDER BY last_name DESC, first_name DESC LIMIT 1 OFFSET 4;

---

4. 표현식

ORM: aggregate 사용

https://docs.djangoproject.com/en/3.2/topics/db/aggregation/#aggregation

• '종합', '합계' 등의 사전적 의미
• 특정 필드 전체의 합, 평균 등을 계산할 때 사용
• 전체에 대해 구할 때는 all() 생략 가능

1. 전체 평균 나이

   # orm
   
   from django.db.models import Avg
   User.objects.all().aggregate(Avg('age'))

   -- sql
   
   SELECT AVG(age) FROM users_user

2. 김씨의 평균 나이

   # orm
   
   User.objects.filter(last_name='김').aggregate(Avg('age'))

   -- sql
   
   SELECT AVG(age)
   FROM users_user
   WHERE last_name='김'

3. 강원도에 사는 사람의 평균 계좌 잔고

   # orm
   
   User.objects.filter(country='강원도').aggregate(Avg('balance'))

   -- sql
   
   SELECT AVG(balance)
   FROM users_user
   WHERE country='강원도'

4. 계좌 잔액 중 가장 높은 값

   # orm
   
   from django.db.models import Max
   User.objects.aggregate(Max('balance'))

   -- sql
   
   SELECT MAX(balance)
   FROM users_user

5. 계좌 잔액 총액

   # orm
   
   from django.db.models import Sum
   User.objects.aggregate(Sum('balance'))

   -- sql
   
   SELECT SUM(balance)
   FROM users_user