Django - SQL과 ORM

1. SQL(Structured Query Language)

• 관계형 데이터베이스 관리시스템의 데이터 관리를 위해 설계된 특수 목적으로 프로그래밍 언어
• 데이터베이스 스키마 생성 및 수정
• 자료의 검색 및 관리
• 데이터베이스 객체 접근 조정 관리
  
  

1.1 분류

DDL(Data Definition Language)

• CREATE
• DROP
• ALTER

DML(Data Manipulation Language)

• INSERT
• SELECT
• UPDATE
• DELETE

DCL(Data Control Language)

• GRANT
• REVOKE
• COMMIT
• ROLLBACK

2. 기본적인 CRUD

2.1 테이블 생성, 제거

분류	SQL	ORM
테이블 생성	CREATE TABLE [테이블명] ( [컬럼1][자료형], [컬럼2][자료형], [컬럼3][자료형], );	model에 테이블 정의 후 migratin
테이블 제거	DROP TABLE [테이블명];	model에 테이블 제거 후 migratin

2.2 데이터 생성(삽입)

SQL

INSERT INTO [테이블 이름] ([컬럼1], [컬럼2] ...) VALUES ([값2], [값2] ... );

• 테이블에 있는 모든 칼럼에 값을 지정할 땐, 테이블 이름 뒤에 컬럼을 명시하지 않아도 된다.

ORM

변수 = 클래스(테이블).objects.create(컬럼1 = 값1, 컬럼2 = 값2 ...)
ex) User.objects.create(id=1, name="아무개")

아래부터는 Django의 model에 User테이블이 정의되어 있다고 가정하고 작성했다.

2.3 데이터 조회

SQL

SELECT [컬럼1], [컬럼2] ... FROM [테이블명]

• 테이블에서 데이터를 조회
• 테이블 이름 대신 *을 넣으면 모든 정보를 조회한다.

clause

• LIMIT
  - 반환되는 행의 수를 제한, OFFSET과 자주 함께 사용
• OFFSET
  - 반환된 결과에서 offset만큼 건너뛰고 조회
• WHERE
  - 데이터 조회 시 특정 조건을 설정
• DISTINCT
  - 조회 결과의 중복 제거, SELECT 바로 뒤에 작성

분류	SQL	ORM
LIMIT OFFSET	SELECT [컬럼1], [컬럼2] ... FORM [테이블명] LIMIT [숫자] SELECT [컬럼1], [컬럼2] ... FORM [테이블명] LIMIT [숫자1] OFFSET [숫자2]	User.objects.all().values([컬럼1]=[값1])[:숫자] User.objects.all().values([컬럼1]=[값1])[숫자1:숫자2]
WHERE	SELECT [컬럼1], [컬럼2] ... FORM [테이블명] WHERE [조건]	User.objects.filter([조건]).values([컬럼1]=[값1])
DISTINCT	SELECT DISTINCT [컬럼1], [컬럼2] ... FORM [테이블명]	User.objects.all().values([컬럼1]=[값1]).distinct()

2.4 데이터 수정

UPDATE [테이블명] SET [컬럼1]=[값1], [컬럼2]=[값2] WHERE [조건];

• PK를 기준으로 탐색하여 삭제하는 것이 좋다.

분류	SQL	ORM
데이터 수정	UPDATE [테이블명] SET [컬럼1]=[값1], [컬럼2]=[값2] WHERE [조건];	user = User.objects.get([조건]) user.[컬럼1] = [값1] user.[컬럼1] = [값1] user.save()

2.5 데이터 제거

DELETE FROM [테이블명] WHERE [조건];

• PK를 기준으로 탐색하여 삭제하는 것이 좋다.

분류	SQL	ORM
데이터 제거	DELETE FROM [테이블명] WHERE [조건];	User.objects.get([조건]).delete()

2.6 예제

CREATE TABLE users_user (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  age INT NOT NULL,
  location TEXT NOT NULL,
  tel TEXT NOT NULL,
  balance INT NOT NULL,
);

예제에서 사용할 테이블은 위와 같다고 가정한다.

age가 30이상인 모든 컬럼 조회

SQL	ORM
SELECT * FROM users_user WHERE age >= 30;	User.objects.filter(age__gte=30)

age가 30이상인 이름 조회

SQL	ORM
SELECT last_name, first_name FROM users_user WHERE age >= 30;	User.objects.filter(age__gte=30).values('last_name', 'first_name')

age가 30이상, first_name이 '김'인 사람의 이름 조회

SQL	ORM
SELECT last_name, first_name FROM users_user WHERE age >= 30 AND last_name='김';	User.objects.filter(age__gte=30, last_name='김').values('last_name', 'first_name')

3. Sqlite Aggregate Functions

• COUNT : 레코드의 개수 조회
• AVG : 레코드의 개수 조회
• SUM : 레코드의 개수 조회
• MIN : 레코드의 개수 조회
• MAX : 레코드의 개수 조회

분류	SQL	ORM
COUNT	SELECT COUNT([컬럼]) FROM [테이블명]	User.objects.[쿼리구문].count()
AVG	SELECT AVG([컬럼]) FROM [테이블명]	User.objects.aggregate(Avg([컬럼]))
SUM	SELECT SUM([컬럼]) FROM [테이블명]	User.objects.[쿼리구문].aggregate(Sum([컬럼]))
MIN	SELECT MIN([컬럼]) FROM [테이블명]	User.objects.[쿼리구문].aggregate(Min([컬럼]))
MAX	SELECT MAX([컬럼]) FROM [테이블명]	User.objects.[쿼리구문].aggregate(Max([컬럼]))

4. LIKE Operator

• 패턴을 기반으로 데이터를 조회하는 방법
• 패턴에는 두가지 와일드카드를 제공

% (percent sign)	_(underscore)
이 자리에 문자열이 있어도,없어도 된다.	이 자리에 한 개의 문자가 반드시 존재한다.

4.1 사용 예시

패턴	의미
[문자열]%	[문자열]로 시작
%[문자열]	[문자열]로 끝남
%[문자열]%	[문자열] 포함
_[문자열]%	한 개의 문자 뒤에 [문자열]로 시작
[문자열]___	[문자열]로 시작하고 총 4자리인 값
2__%	2로 시작하고 적어도 3자리인 값

4.2 예제

CREATE TABLE users_user (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  age INT NOT NULL,
  location TEXT NOT NULL,
  tel TEXT NOT NULL,
  balance INT NOT NULL,
);

예제에서 사용할 테이블은 위와 같다고 가정한다.

20대만 조회

SQL	ORM
SELECT * FROM users_user WHERE age LIKE '2_';	User.objects.filter(age__startswith='2')

지역번호 02만 조회

SQL	ORM
SELECT * FROM users_user WHERE phone LIKE '02-%';	User.objects.filter(phone__startswith='02-')

이름이 '준'으로 끝나는 사람 조회

SQL	ORM
SELECT * FROM users_user WHERE first_name LIKE '%준';	User.objects.filter(first_name__endswith='준')

5. ORDER BY

• 내림차순 혹은 오름차순으로 조회 결과를 정렬
• LIMIT와 조합하여 상위 혹은 하위 몇 개만 조회가능
• 내림차순이 기본값이다.

분류	SQL	ORM
오름차순	SELECT [컬럼] FROM [테이블명] ORDER BY [컬럼] ASC;	User.objects.[쿼리구문].orderby([컬럼])
내림차순	SELECT [컬럼] FROM [테이블명] ORDER BY [컬럼] DESC;	User.objects.[쿼리구문].orderby(-[컬럼])

• 컬럼을 여러 개를 지정할 수도 있으며, 앞에서부터 적용된다. 예를 들어 [컬럼1]이 같은 경우 [컬럼2]를 기준으로 [컬럼1]에 속하는 데이터에 한해서 정렬한다.

6. GROUP BY

• 여러 개의 레코드를 선택하여 몇 개의 컬럼을 기준으로 요약 레코드(하나)로 만듦
• 문장에 WHERE 문이 있으면 반드시 뒤에 작성

SELECT [컬럼1], [aggregate] FROM [테이블] GROUP BY [컬럼1], [컬럼2]...;

7. SQL & ORM 예제

CREATE TABLE users_user (
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  age INT NOT NULL,
  location TEXT NOT NULL,
  tel TEXT NOT NULL,
  balance INT NOT NULL,
);

예제에서 사용할 테이블은 위와 같다고 가정한다.

전체 수 조회

SQL	ORM
SELECT COUNT(*) FROM users_user;	User.objects.count()

age >= 30인 사람 이름 조회

SQL	ORM
SELECT first_name FROM users_user WHERE age >= 30;	User.objects.filter(age__gte=30)

age >= 30인 사람의 수

SQL	ORM
SELECT COUNT(*) FROM users_user WHERE age >= 30;	ser.objects.filter(age__gte=30).count()

age >= 30 이고 last_name='김'인 사람 수

SQL	ORM
SELECT COUNT(*) FROM users_user WHERE age >= 30 AND last_name='김';	User.objects.filter(age__gte=30, last_name='김').count()

age >= 30 이거나 last_name='김'인 사람 수

SQL	ORM
SELECT COUNT(*) FROM users_user WHERE age >= 30 OR last_name='김';	``User.objects.filter(Q(age__gte=30)

잔액이 적고, 나이가 많은 순으로 10명

SQL	ORM
SELECT * FROM users_user ORDER BY balance, age DESC LIMIT 10;	User.objects.order_by('balance', '-age')[:10]

전체의 평균 나이

SQL	ORM
SELECT AVG(age) FROM users_user;	User.objects.aggregate(Avg('age'))

last_name='김' 평균 나이

SQL	ORM
SELECT AVG(age) FROM users_user WHERE last_name='김';	User.objects.filter(last_name='김').aggregate(Avg('age'))

phone이 '123'포함. age < 30 조회

SQL	ORM
SELECT * FROM users_user WHERE phone LIKE '%123%' AND age < 30;	User.objects.filter(phone__contains='123', age__lt='30')

phone이 '010'시작, 행정 구역 중복 없이 조회

SQL	ORM
SELECT DISTINCT country FROM users_user WHERE phone LIKE '010-%';	User.objects.filter(phone__startswith='010-').values('country').distinct()