[Django] ORM과 SQL의 관계

SQL과 Django ORM의 관계를 알면 좋은점

• SQL문을 직접 비교하면서 학습하시게 되면 django와 같은 프레임워크를 사용하지 않아도 다른 웹 프레임워크를 사용해서도 주로 같이 사용되는 ORM을 사용하지 않고 직접 SQL을 사용하여 유연하게 구현할 수 있는 장점이 있습니다.
• django ORM으로 코드를 작성할때, SQL문을 한번 더 고려할 수 있기 때문에 추후에 성능 분석할 때 실제 django가 수행하는 SQL을 프로파일링 할 수 있는 장점이 있습니다.
  
  

Database MySQL

DDL (Data Define Language)

• 데이터베이스의 Schema(형태)를 정의할 수 있는 쿼리문을 의미합니다.
• CREATE, ALTER, DROP이 대표적인 쿼리문 입니다.
• 데이터를 구성하는 논리적인 구조(데이터베이스 또는 테이블)을 추가하고 수정하고 삭제할 수 있습니다.
• 메타데이터(데이터에 대한 데이터)를 다루는 쿼리문이라고 할 수 있습니다.
  
  

CREATE DATABASE

CREATE DATABASE my_favourite_artists; # Database 를 생성하는 명령어
SHOW DATABASES; # 현재 MySQL 에 저장되어있는 데이터베이스를 보는 명령어

USE my_favourite_artists; # 생성한 데이터베이스에 접근하는 명령어

SHOW tables; # 현재 데이터베이스 안에 저장되어있는 테이블들을 보는 명령어


# artists table 생성하기
CREATE TABLE artists 
(
  id INT NOT NULL AUTO_INCREMENT, # id 칼럼을 정수, NULL 을 허락하지 않으며 값을 자동증가하게 설정합니다.
  name VARCHAR(100) NOT NULL, # name 칼럼을 100의 크기를 가지는 문자열, NULL 을 허락하지 않습니다.
  PRIMARY KEY (id) # 이 테이블의 Primary Key 를 id 로 설정합니다.
); # 쿼리문은 ; 로 구분되기 때문에 엔터를 쳐서 쿼리문을 멀티라인으로 작성할 수 있습니다.

DESC artists; # Description 을 축약해 놓은 명령어로써, 테이블의 정보를 보여줍니다.


# songs table 생성하기
CREATE TABLE songs
(
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  lyrics VARCHAR(2000),
  artist_id INT,
  PRIMARY KEY (id),
  FOREIGN KEY (artist_id) REFERENCES artists (id) # 외래키를 걸어줄 칼럼과 참조할 테이블과 칼럼을 순서대로 적어줍니다.
);

DESC songs;

DML (Data Manipulation Language)

• 데이터를 조작하는 쿼리문들을 뜻합니다.
• INSERT, UPDATE, DELETE가 대표적인 쿼리문 입니다.

INSERT INTO table

INSERT INTO artists (name) VALUES ('Radio Head'); 
# INSERT INTO 뒤에는 테이블 명과 칼럼값을 소괄호로 감싸 줍니다.
# VALUES 뒤에는 실제로 넣을 값을 소괄호로 감싸주고 앞서 테이블의 칼럼을 지정한 순서대로 데이터를 넣으면 됩니다.
INSERT INTO artists (name) VALUES ('Pink Floid');
INSERT INTO artists (name) VALUES ('새소년');

SELECT * FROM artists; # * 는 와일드카드로써 테이블의 모든 칼럼을 조회하겠다는 의미와 같습니다.

UPDATE table SET column

UPDATE artists SET name='Pink Floyd' WHERE id=2;
# UPDATE 쿼리문은 데이터를 수정할 테이블을 기입합니다.
# 바꾸고자 하는 칼럼값에 새롭게 업데이트 할 데이터를 대입합니다.
# WHERE 문은 프로그래밍 언어의 if 문이라고 생각하면 됩니다. 조건을 주는 쿼리문 입니다.
# 이 예제에서는 id=2 즉, artists 테이블의 id가 2번인 데이터에 대해서 UPDATE를 하겠다는 의미입니다.

SELECT * FROM artists;

DELETE FROM table

DELETE FROM artists WHERE name='새소년';
# DELETE 쿼리문은 테이블의 이름과 WHERE 문으로 조건을 걸어주면 됩니다. 

SELECT * FROM artists;

DQL (Data Query Language)

• 데이터를 쿼리하는데 사용되는 SQL문을 뜻합니다.
• 데이터베이스 서버에게 데이터를 달라고 요청합니다.
• SELECT가 대표적인 쿼리문입니다.

SELECT

SELECT * FROM artists;
SELECT * FROM songs;

SELECT songs.title FROM songs; 
# 노래제목들만 뽑아낼 수 있습니다.
SELECT songs.title, songs.lyrics FROM songs;
# 가사와 함께 뽑아낼 수도 있습니다.

WHERE 조건문

SELECT songs.lyrics FROM songs WHERE songs.title = '파도';
# 파도라는 노래의 가사를 뽑아낼 수 있습니다.

WHERE + LIKE 조건문

SELECT songs.title, songs.lyrics FROM songs WHERE songs.lyrics LIKE '%넘실넘실%';
# 위에서의 WHERE문은 = 연산자를 사용해서 정확한 값으로 비교를 했지만
# LIKE 문을 사용하면 이 처럼 넘실넘실이 포함된 데이터를 쿼리할 수 있습니다. 

SELECT songs.title, songs.lyrics FROM songs WHERE songs.title LIKE 'All%';
# songs.title의 값이 All 로 시작하면 쿼리문에 의해 데이터가 조회됩니다.

SELECT songs.title, songs.lyrics FROM songs WHERE songs.lyrics LIKE '%가져가도 돼요';
# songs.lyrics의 값이 가져가도 돼요로 끝나면 쿼리문에 의해 데이터가 조회됩니다.

JOIN

• 연관성이 있는 두 개의 테이블을 결합시킬 수 있습니다.
  

SELECT artists.name, songs.title, songs.lyrics # 필요한 칼럼을 나열 합니다.
FROM artists # artists 테이블에
JOIN songs # songs 테이블을 결합시킵니다.
ON artists.id = songs.artist_id; # ON 뒤에는 교집합, 즉 연결성이 있는 부분(칼럼)을 적어줍니다.

JOIN + WHERE + LIKE 조건문

SELECT artists.id, artists.name, songs.title
FROM artists
JOIN songs
ON artists.id = songs.artist_id
WHERE artists.name = '새소년'; # 새소년의 노래들만 조회할 수 있습니다.

SELECT artists.id, artists.name, songs.title
FROM artists
JOIN songs
ON artists.id = songs.artist_id
WHERE songs.lyrics LIKE '%you%'; # 가사에 you 가 들어간 데이터만 조회합니다.

SELECT artists.id, artists.name, songs.title
FROM artists
JOIN songs
ON artists.id = songs.artist_id
WHERE songs.lyrics LIKE '%you%' OR songs.lyrics LIKE '%i%'; 
# 가사에 you 또는 i가 들어간 데이터만 조회합니다.

DCL (Data Control Language)

• 데이터를 제어하는 쿼리문들을 뜻합니다.
• 데이터의 보안, 무결성, 회복, 병행 수행제어 등을 정의하는데 사용합니다.
• TCL을 포함한다.
• GRANT, REVOKE이 대표적인 쿼리문 입니다.
  
  

TCL (Transaction Control Language)

• 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위해서 사용합니다.
• COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

Django ORM과 SQL관계 Cheatsheet

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;

# l(less), g(greater), t(than), e(equal)
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;

# Q()을 사용하면 and, or, not 연산을 이용한 조건을 걸어서 데이터를 가져올 수 있다.
from django.db.models import Q
Person.objects.filter(Q(gender='male') | Q(age__gt=25))

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

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;

# F() 객체는 모델의 필드 혹은 어노테이트된 열의 값을 나타낸다.
# 실제로 데이터베이스에서 Python메모리로 가져오지 않고, 모델 필드 값을 참조하고 이를 데이터베이스에서 사용하여 작업할 수 있다.
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()