[Django Models 뜯어보기 #2] Making Queries

앞으로 예시들에 사용할 모델.
글 하나에 블로그 여러 개 중 하나가 연결되어있고, author는 m2m.

class Blog(models.Model): # 블로그
name = models.CharField(max_length=100)
tagline = models.TextField()

def __str__(self):
return self.name

class Author(models.Model): # 작가
name = models.CharField(max_length=50)
email = models.EmailField()

def __str__(self):
return self.name

class Entry(models.Model): # 글
blog = models.ForeignKey(Blog)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateField()
mod_date = models.DateField()
authors = models.ManyToManyField(Author)
n_comments = models.IntegerField()
n_pingbacks = models.IntegerField()
rating = models.IntegerField()

def __str__(self):
return self.headline

오브젝트 만들기

b = Blog(name='Beatles Blog', tagline='All the latest Beatles news.') # 모델 인스턴스화
b.save() # 디비에 저장. SQL의 INSERT를 호출한다. 이거 하기 전까지는 디비를 건들지 않는다.

create()는 create하고 save까지 한다.

오브젝트 바뀐거 저장

>>> b5.name = 'New name'
>>> b5.save()

save()하면 UPDATE SQL문을 날린다.

foreignkey 필드 저장

entry = Entry.objects.get(pk=1)
yoolmoo_blog = Blog.objects.get(name='유르무르 블로그')
entry.blog = yoolmoo_blog
entry.save

ManyToManyField 저장

jay = Author.objects.create(name='Jay')
entry.authors.add(jay)

josh = Author.objects.create(name='Josh')
yoolmoo = Author.objects.create(name='Yoolmoo')
entry.authors.add(josh, yoolmoo)

add로 추가한다.

오브젝트 가져오기 (Retrieving objects)

오브젝트를 retrieve하면, Manager를 통해 Queryset을 만든다. Filters는 쿼리를 좁힌다. 모든 모델들은 하나 이상의 Manager를 가진다. Manager는 기본으로 objects라는 이름.
Queryset은 디비에서 나온 오브젝트들의 컬렉션이다.
QuerySet == SELECT
filter == WHERE, LIMIT

>>> Blog.objects
<django.db.models.manager.Manager object at ...>
>>> b = Blog(name='Foo', tagline='Bar')
>>> b.objects
Traceback:
...
AttributeError: "Manager isn't accessible via Blog instances."

Managers는 모델 클래스에서만 접근 가능한다.

모든 오브젝트 가져오기

all_entries = Entry.objects.all()

Manager의 all()메서드를 쓴다. 디비 모든 오브젝트의 쿼리셋을 반환한다.

Filter로 오브젝트 걸러서 가져오기

Entry.objects.filter(pub_date__year=2006) # all() 안써도 된다.
Entry.objects.all().filter(pub_date__year=2006)

>>> Entry.objects.filter(
... headline__startswith='What'
... ).exclude(
... pub_date__gte=datetime.date.today()
... ).filter(
... pub_date__gte=datetime(2005, 1, 30)
... )

Queryset이 evaluated되기 전까진 장고는 쿼리를 돌리지 않는다.

get()으로 싱글 오브젝트 가져오기

filter()는 싱글 오브젝트를 반환하더라도 쿼리셋으로 반환한다.
하나 오브젝트를 가져오려면 get()을써라.

one_entry = Engry.objects.get(pk=1)

get 안에 filter처럼 쓰면 된다.
쿼리 반환값이 없을 때 get()은 DoesNotExist exception을 뱉는다. 반환값이 1 이상일때는 MultipleObjectsReturned.

주석(annotate)

Queryset의 각 오브젝트마다 annotate하기.

>>> from django.db.models import Count
>>> q = Blog.objects.annotate(Count('entry'))
# 첫 번째 블로그 이름
>>> q[0].name
'율무네 블로그'
# 첫 번째 블로그의 엔트리 숫자
>>> q[0].entry__count
10

>>> q = Blog.objects.annotate(number_of_entries=Count('entry'))
# 첫 번째 블로그의 엔트리 숫자. 지정한 이름으로 가져오기
>>> q[0].number_of_entries
42

쿼리셋 제한하기

파이썬 Array-slicing 문법을 사용해서 쿼리셋의 서브셋 가져오기.
SQL의 LIMIT, OFFSET과 비슷하다.

Entry.objects.all()[:5] # 앞에서부터 5개 오브젝트
Entry.objects.all()[5:10] # 6번부터 10번까지
Entry.objects.all()[-1] # 에러난다
Entry.objects.all()[:10:2] # 10번째까지 모든 오브젝트들의 2번째 오브젝트
Entry.objects.order_by('headline')[0]

Field lookups

field이름__lookuptype=value이렇게 쓰면 된다.

Entry.objects.filter(pub_date__lte='2006-01-01')
Entry.objects.filter(blog_id=4) # foreignkey일땐 _id 서픽스를 붙여서 primary key를 찾는다.

Entry.objects.get(headline__exact="개발자 생활백서") # headline이 '개발자 생활백서'인 것을 찾는다.

Blog.objects.get(id__exact=14) # Explicit form
Blog.objects.get(id=14) # __exact is implied. 위랑 같다.

Blog.objects.get(name__iexact="beatles blog") # 대소문자 구분 X

Entry.objects.get(headline__contains='Lennon') # 포함

Span relationships의 Lookups

JOIN을 자동으로 해준다.

Entry.objects.filter(blog__name='율무 블로그') # 블로그모델의 name필드가 `율무 블로그`

Reverse relationship도 참조 가능. 모델 이름을 소문자로 쓴다.

Blog.objects.filter(entry__headline__contains='율무') # 거꾸로 참조

# 두 조건 모두 만족
Blog.objects.filter(entry__authors__isnull=False, entry__authors__name__isnull=True)

Spanning multi-valued relationships

M2M필드나 reverse foreignkey를 필터링할때, 두 가지 종류의 필터가 있다.
– Blog / Entry (one-to-many)
+ 엔트리 제목이 Lennon이면서 2008년에 발행된 블로그는 1개
+ 엔트리 제목이 Lennon이면서 2008년에 발행된 블로그를 찾고싶다.

Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008) # 엔트리 제목이 Lennon포함하면서 엔트리 발행일이 2008

Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008) # 엔트리 제목이 Lennon포함하는 블로그를 찾고, 거기서 엔트리 발행일이 2008인 블로그를 찾는다. 위의 쿼리셋과 다를 수 있다.

모델의 필드를 참조할 수 있는 필터

특정 모델 필드랑 다른 필드 값 비교할 때도 쓸 수 있다.

>>> from django.db.models import F
>>> Entry.objects.filter(n_comments__gt=F('n_pingbacks')) # 커멘트가 핑백보다 수가 많은 Entry 가져오기
>>> Entry.objects.filter(rating__lt=F('n_comments') + F('n_pingbacks')) # 레이팅이 커멘트+핑백보다 적은 엔트리 가져오기
>>> Entry.objects.filter(authors__name=F('blog__name')) # 관계를 span하기 위해선 __를 쓴다.
>>> Entry.objects.filter(mod_date__gt=F('pub_date') + timedelta(days=3)) # 발행일보다 3일 후 날짜보다 큰 수정일을 가진 엔트리를 가져와라

pk lookup shortcut

primary key를 좀 더 편하게 참조할 수 있게 pk라고 shortcut을 만듦

>>> Blog.objects.get(id__exact=14) # Explicit form
>>> Blog.objects.get(id=14) # __exact is implied
>>> Blog.objects.get(pk=14) # pk implies id__exact

# pk가 1이나 4나 7인것
>>> Blog.objects.filter(pk__in=[1,4,7])

# pk가 14보다 큰 것
>>> Blog.objects.filter(pk__gt=14)

>>> Entry.objects.filter(blog__pk=3) # entry의 blog의 pk

쿼리셋 캐싱하기

>>> queryset = Entry.objects.all() # 재활용할거 할당해두기
>>> print([p.headline for p in queryset])
>>> print([p.pub_date for p in queryset])

>>> [entry for entry in queryset] # 데이터베이스 쿼리하기
>>> print queryset[5] # 캐시 쓰기
>>> print queryset[5] # 캐시 쓰기

Q 오브젝트로 복잡한 lookups 만들기

filter, exclude, get등에 쓸 수 있다.

from django.db.models import Q
Q(question__startswith='What')
Poll.objects.get(
Q(question__startswith='Who'),
Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)) # OR연산
)

오브젝트 삭제

e.delete()

다중 오브젝트 한번에 업데이트 하기

# pub_date가 2007인 모든 엔트리 헤드라인 업데이트하기
Entry.objects.filter(pub_date__year=2007).update(headline='Everything is the same')

# 모든 엔트리 업데이트
>>> Entry.objects.all().update(blog=b)

update 메서드는 SQL문으로 바뀌어 바로 적용된다.

Related objects

다대일(One-to-many)

Forward
Foreignkey로 연결했으면 그냥 .으로 호출 가능

>>> e = Entry.objects.get(id=2)
>>> e.blog = some_blog
>>> e.save() #이거 해야 저장됨

>>> e = Entry.objects.get(id=2)
>>> print(e.blog) # e에서 블로그를 가져올 때 DB를 건드린다.
>>> print(e.blog) # 캐시 써서 디비를 건드리지 않는다.

>>> e = Entry.objects.select_related().get(id=2) #select_related()
>>> print(e.blog) # 캐시 써서 디비를 건드리지 않는다.
>>> print(e.blog) # 캐시 써서 디비를 건드리지 않는다.

Backward
모델이 ForeignKey를 갖고 있으면,

>>> b = Blog.objects.get(id=1)
>>> b.entry_set.all() # 거꾸로 연결되어있던 Entry를 소문자로 바꾸고, 뒤에 _set을 붙여주면 블로그랑 연결된 모든 entry의 쿼리셋 가져온다.

>>> b.entry_set.filter(headline__contains='Lennon')
>>> b.entry_set.count()

related_name파라미터를 ForeignKey만들때 쓰면 FOO_set을 오버라이드 할 수 있다.

# related_name 지정해두기
blog = ForeignKey(Blog, on_delete=models.CASCADE, related_name='entries')

# related_name으로 불러오기
>>> b = Blog.objects.get(id=1)
>>> b.entries.all()

다대다(Many-to-many)

e = Entry.objects.get(id=3)
e.authors.all() # Returns all Author objects for this Entry.
e.authors.count()
e.authors.filter(name__contains='John')

a = Author.objects.get(id=5)
a.entry_set.all() # Returns all Entry objects for this Author.

Refer

https://docs.djangoproject.com/en/1.9/topics/db/queries/

Advertisements

Head first SQL (mySQL) 정리

  • DB
    • DB생성 : CREATE DATABASE test;
    • DB목록 조회 :  SHOW DATABASES;
    • DB삭제 : DROP DATABASE test;
  • 사용자 관리
    • DB를 사용할 사용자 계정을 추가
    • root 사용자만이 사용자를 관리
    • 사용자 추가 – id:tiger    pw:dbgood
      • create user ‘tiger’@’localhost’ IDENTIFIED BY ‘dbgood’;
      • SELECT host, user FROM user;    /*확인*/
    • 사용자 제거
      • DROP USER ‘tiger’@’localhost”;
  • 사용자 권한 관리
    • 사용자가 특정 DB를 사용할 수 있도록 권한 부여
    • 권한 추가 – id:tiger   대상DB : test
      • GRANT ALL ON test.* TO ‘tiger’@’localhost’ WITH GRANT OPTION;
    • 권한 제거
      • REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘tiger’@’localhost’;
      • FLUSH PRIVILEGES;    /*권한 변경 후 바로 적용*/
  • 테이블 만들기
    • 테이블 생성
      • 먼저 test데이터베이스 사용한다 명시해야함
      • use test;
      • CREATE TABLE user(
      • id CHAR(10) NOT NULL DEFAULT id,  //null값이 들어가면 에러남. 디폴트값에 id넣어줌
      • name CHAR(20),
      • passwd CHAR(10)
      • );
    • 테이블 조회
      • SHOW TABLES;
      • DESC user;      /*describe. */
      • SHOW CREATE TABLE user;
    •  테이블 삭제
      • DROP TABLE user;
    • 데이터 삽입
      • INSERT INTO user(id, name, passwd) VALUES(‘lee’, ‘leegood’, ‘dbgood’);
    • 데이터 확인
      • SELECT * FROM user;
      • SELECT drink_name, main FROM easy_drinks;
    • SHOW
      • SHOW COLUMNS FROM tablename;
        • 테이블의 모든 열 표시하고 데이터타입도.
      • SHOW CREATE DATABASE databasename;
        • SHOW CREATE TABLE처럼 데이터베이스를 생성하는 명령을 얻을 수 있다.
      • SHOW INDEX FROM tablename;
        • 인덱싱되어있는 열과 무슨 타입의 인덱스를 가지고 있는지를 표시.
      • SHOW WARNINGS;
        • 실제 경고메세지를 확인
  • 기타
    • 뒤에  N

 

<4. SQL-데이터 생성>

 

학습목표

  • 데이터 생성을 위한 CREATE TABLE관련 SQL을 알고 사용.
    • CREATE TABLE, 제약조건, KEY, UNIQUE
  • 데이터 생성을 위한 CREATE TABLE관련 SQL을 알고 사용
    • 단건, 대량건

CREATE TABLE

  • 테이블 만들기
    • CREATE TABLE user(
    •     id INT,
    •     passwd CHAR(10),
    •     registry_date DATE,
    •     access_hours DEC(8,1)    //정수부 4, 소수부 1자리를 갖는 5자리 숫자
    • );
  • 생성한 테이블 정보 조회하기
    • DESC user;
    • 아니면
    • SHOW CREATE TABLE tablename;

 

INSERT INTO

  • 데이터 삽입 명령
    • INSERT INTO book(id, title, publication, inventory)
    •     values(100, ‘head first SQL”, ‘한빛미디어’, 3);
    • 혹은
    • INSERT INTO book values(100, ‘head first SQL”, ‘한빛미디어’, 3);

테이블의 제약조건

  • NOT NULL : 속성값이 반드시 값이 존재해야함을 명시
    • NULL값의 의미는 정의불가(프로그래밍 언어에서 널과 다름)
  • PRIMARY KEY(id) : 레코드값 중복되지 않고 유일해야 하도록 제약
  • UNIQUE(title) : Primary key가 아닌데 유일해야 하는 속성값
  • DEFAULT 0 : 값이 입력되지 않았을 때 기본적으로 값을 0으로 함.

데이터 타입

  • MySQL 데이터 타입
    • CHAR(m) : 고정길이 문자열, m=1~255
    • VARCHAR(m) : 가변길이 문자열. m=1~255. CHAR보다 검색속도가 느리지만 공간 효율적
    • 수: TINYINT, SMALLINT, INT, UNSIGNED INT, FLOAT, DOUBLE, DECIMAL(=NUMERIC, DEC)
    • 시간 : DATE, TIME, YEAR, TIMESTAMP, DATETIME
      • TIMESTAMP: 값 미입력시 자동으로 현재시간 입력됨
    • 대용량
      • tinyBLOB/tinyText : 최대길이 255개의 문자 저장
      • BLOB/ Text : 최대길이 65535
      • midiumBLOB/mediumText : 16777215
      • LongBLOB/ LongText : 4294967295(4GB)
    • 이미지 데이터 저장, 조회
      • DROP TABLE IF EXISTS blob_table;
      • CREATE TABLE blog_table)
      •   id INT PRIMARY KEY AUTO_INCREMENT,
      •   file VARCHAR(64),
      •   img_blob MEDIUMBLOB
      • );
      • INSERT INTO blob_table(img_blob, file) values(load_file(“C://test.jpg”), ‘test.jpg’);
      • SELECT * FROM blob_table;    //BLOB데이터는 이렇게 보면 안대용!
      • SELECT img_blob INTO DUMPFILE ‘c://dump.jpg’ FROM blob_table WHERE file=’test.jpg’;
    • 대량 데이터 생성
      • 생성, 입력해야할 데이터가 수천건이 넘어도 모두 INSERT INTO 로 생성해야하나요?
        • 가능은 하지만 그렇게하진 않아요.
      • 다른 db나 텍스트 파일 형태로 있는 대량 데이터를 MySQL로 대량입력할 수 없나요?
        • 파일로 저장된 대량 데이터 입력가능, DB간 데이터 입력도 가능.
    • 형식 맞아야 한다.
      • 작은따옴표 사용: CHAR, VARCHAR, DATE, DATETIME, TIME, TIMESTAMP, BLOB
      • 안사용 : DEC, INT
      • 작은따옴표 붙이려면 백슬래시 사용.
        • INSERT INTO hi VALUES(‘Jay Jin\’s Blog);

SELECT

  • 많은 앤 중에 앤찾기!
    • 전체 테이블을 힘들게 찾지 말고 셀렉트문으로 찾아라.
    • SELECT * FROM my_contacts WHERE first_name=’Anne’;
    • SELECT doughnut, rating FROM my_contacts WHERE first_name=’Anne’;
  • 쿼리들의 결합
    • SELECT location FROM doughnut_ratings WHERE type=’plain glazed’ AND rating>5;
    • OR도 사용가능.
  • NULL찾기
    • SELECT drink_name FROM drink_info WHERE calories IS NULL;
  • 문자 일부로 검색
    • SELECT * FROM my_contacts WHERE location LIKE ‘%CA’;
      • 퍼센트 기호를 작은따옴표 안에 : location열이 CA로 끝나는 모든 값을 찾기
      • ‘_im’ : _은 단 하나의 불특정 문자를 대신하는 문자
  • BETWEEN
    • SELECT drink_name FROM drink_info WHERE calories BETWEEN 30 AND 60;
  • IN
    • SELECT date_name FROM note WHERE rating=’good’ OR rating=’fabulous’ OR…;
    • 이걸 IN이란 키워드를 사용. 열의 값이 괄호안의 값들 중 하나와 일치하면 그 행 또는 지정된 열들이 반환.
    • …FROM note WHERE raiting IN (‘good’, ‘fabulous’, delightful’);
    • NOT IN : IN이 표시하는 집합과 일치하지 않는 행을 반환.
  • NOT
    • BETWEEN이나 LIKE와 함께도 사용가능.
      • 단, NOT이 WHERE바로 다음에 와야한다.
    • NOT IN, WHERE NOT, AND NOT…

 

DELETE

  • DELETE FROM clown_info WHERE activities = ‘dancing’;
    • WHERE조건에 맞는 모든 레코드가 테이블에서 지워짐
    • DELETE FROM your_table
      • 테이블의 모든 행을 지운다!
  • INSERT-DELETE
    • SELECT * FROM clown_info WHERE activities=’dancing’;
    • INSERT INTO clown_info VALUES(‘Zippo’,ddddd);
    • DELETE FROM clown_info WHERE activities = ‘dancing’;

UPDATE

  • WHERE조건을 만족하는 투플의 특정 속성값을 수정
    • *주의: WHERE생략시 테이블의 모든 투플의 속성값 수정
  • UPDATE doughnut SET type=’glaze’ WHERE type=’plain glazed’;
  • UPDATE doughnut SET first_col=’new’, second-col=’new2′;
  • UPDATE drink_info SET cost=cost+1 WHERE drink_name=’blue moon’ OF drink_name=’oh’;
  • UPDATE my_contacts SET state = RIGHT(location, 2);
    • state라는 새로운 열에 location열에서 두 문자를 추출해서 집어넣음

CASE

  • 기존 열의 값과 조건을 비교하여 UPDATE문을 합치긔
    • UPDATE my_table SET new_column =
    • CASE
    •   WHEN column1 = somevalue1
    •     THEN newvalue1
    •   WHEN column2 = somevalue2
    •     THEN newvalue2
    •   ELSE newvalue3
    • END;

문자 함수

  • SUBSTRING_INDEX(interests, ‘,’, 2) = ‘animals’;
    • 두 번째 콤마를 찾아서 그 앞의 모든 걸 반환
  • SUBSTR(interests, LENGTH(interest1)+2);
    • 문자열을 가져다 괄호안에 명시한 일부를 자르고 나머지를 반환
    • interest1에 있는 문자+2(콤마랑 공백) 해서 잘라서 버려버림.
  • 마지막 두 문자 추출
    • RIGHT()와 LEFT()를 사용해 열로부터 정해진 수의 문자들 선택
    • SELECT RIGHT(location, 2) FROM my_contacts;
  • 콤마 앞의 모든 문자 추출
    • 특정 문자나 문자열 앞의 모든 문자열을 반환
    • SELECT SUBSTRING_INDEX(location, ‘,’) FROM my_contacts;
  • SELECT SUBSTRING(your_string, start_pos, length)
    • sart_post에 있는 문자에서부터 시작해서 your_string의 일부를 반환. length는 반환되는 문자열의 길이.
  • SELECT UPPER(‘uSa’);
    • 문자열 uSa를 모두 대문자로 변환(<–>LOWER)
  • SELECT REVERSE(‘love’);
    • 문자열 순서 역순으로
  • SELECT LTRIM(‘    dogfood’);
    • 문자열의 앞에 있는 공백문자들을 제거한 문자열반환(<–>RTRIM)
  • SELECT LENGTH(‘san Antonio’);
    • 문자수 반환

ALTER

  • ex)
    • ALTER TABLE my_contacts
    • ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST,
    • DROP COLUMN start_date,
    • ADD PRIMARY KEY(id);
  • 키워드
    • FIRST: 모든 열 앞에 ㅇㅇ를 추가
    • LAST: 모든 열 뒤에 ㅇㅇ를 추가
    • FIFTH: 위치지정가능.
    • BEFORE last_name : last_name열 전에 추가.
  • 테이블 이름 바꾸기
    • ALTER TABLE projekts RENAME TO project;
  • CHANGE COLUMN
    • 이름과 데이터타입 모두 변경
    • ALTER TABLE project CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
    • ADD PRIMARY KEY(proj_id);
    • number열을 새 이름 proj_id로 변경하고 AUTO_INCREMENT, 그리고 기본키로 설정
  • 하나의 SQL문으로 두개열 변경
    • ALTER TABLE project_list
    • CHANGE COLUMN dee proj_desc VARCHAR(100),
    • CHANGE COLUMN coo con_name VARCHAR(30);
  • MODIFY
    • ALTER TABLE project_list MODIFY COLUMN prom_desc VARCHAR(120);
    • 열의 데이터 타입만 바꾸고 이름은 그대로 사용.
  • 열 제거
    • ALTER TABLE project DROP COLUMN start_date;
  • 기본키 없애기
    • ALTER TABLE table DROP PRIMARY KEY;

ORDER BY

  • 알파벳순 정렬
    • SELECT title, category, purchased
    • FROM movie_table
    • WHERE category=’family’
    • ORDER BY title, purchased DESC //DESC: 역순으로 정렬
    • LIMIT 2; //결과를 2개로 제한

연산

  • SUM
    • SELECT SUM(sales) FROM cookie_sales
    • WHERE name=’Nicole’;
  • 그룹합
    • SELECT name, SUM(sales) FROM cookie_sales
    • GROUP BY name ORDER BY SUM(sales) DESC;    //304쪽 참고
  • 기타
    • MAX(sales)   MIN(sales)
    • COUNT(sale_date)    //sale date열의 행의 수를 반환
    • AVG(salary)    //평균연봉

DISTINCT

  • ex)
    • SELECT DISTINCT sale_date
    • FROM cookie_sales
    • ORDER BY sale_date;
  • ex)
    • SELECT COUNT (DISTINCT sale_date)
    • FROM cookie_sales;

LIMIT

 

  • 오잉

AS

 

  • 새로 만드는 테이블을 SELECT의 결과로 채운다.
    • CREATE TABLE profession
    • (
    •   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    •   profession VARCHAR(20)
    • ) AS                 //SELECT의 결과를 모아 새 테이블에 넣는다.
    •   SELECT profession FROM my_contacts
    •   GROUP BY profession
    •   ORDER BY profession;
  • 별명 만들기
    • CREATE TABLE profession
    • (
    •   id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    •   profession VARCHAR(20)
    • ) AS                 //SELECT의 결과를 모아 새 테이블에 넣는다.
    •   SELECT profession AS mc_prof FROM my_contacts AS ac       //AS생략가능.
    •   GROUP BY mc_prof
    •   ORDER BY mc_prof;

카티젼 조인

  • 한번에 두 테이블에 쿼리 수행. toys테이블에서 toy열과, boys테이블에서 boy열 가져오는 경우 결과값을 카티젼 결과값이라 한다.
    • SELECT t.toy, b.boy
    • FROM toys AS t CROSS JOIN boys AS b;

내부 조인

  • 조건을 사용하여 두 테이블의 레코드를 결합
  • 결합된 행들이 조건을 만족할 경우에만 열들이 반환.
    • SELECT somecolumns FROM table1
    •   INNER JOIN table 2                     //INNER안써도 됨.
    • ON somecondition;

내부조인-동등 조인

  • 같은지를 테스트하는 내부 조인
    • SELECT boys.boy, toys.toy
    • FROM boys INNER JOIN toys
    • ON boys.toy_id = toys.toy_id
    • ORDER BY boys.boy;

내부조인-비동등조인

  • 같지 않은 모든 행들 반환.
    • SELECT boys.boy, toys.toy
    • FROM boys INNER JOIN toys
    • ON boys.toy_id <> toys.toy_id
    • ORDER BY boys.boy;

내부조인-자연조인

  • 두 테이블에 같은 이름의 열이 있을때만 동작
    • SELECT boys.boy, toys.toy
    • FROM boys NATURAL JOIN toys;

서브 쿼리(내부쿼리)

  • 다른 쿼리에 쌓여진 쿼리
  • 비상관 서브쿼리
    • 내부쿼리 수행하고 그 값을 사용해서 외부쿼리 결과 알아냄
    • 서브쿼리가 외부쿼리의 어떤 것도 참조하지 않을때.
    • IN이나 NOT IN을 사용하여 값이 서브 쿼리에서 반환된 집합의 원소인지(아닌지)를 확인
  • 상관 서브쿼리
    • 내부 쿼리의 값이 결정되는데 외부 쿼리에 의존
  • 응답속도
    • 크로스조인, 상관서브쿼리는 느린 편
    • 조인이 서브쿼리보단 빠름

 

<10. 외부 조인, 셀프 조인, 그리고 유니온>

 

외부 조인

  • 조인되는 테이블 중 하나의 모든 행을 다른 테이블에서 일치하는 정보와 함께 반환.
  • 두 테이블 사이의 관계와 좀더 관련.
  • 왼쪽 외부조인
    • 왼쪽 테이블의 모든 행을 가져다 오른쪽테이블의 행과 비교.
    • 왼쪽 테이블과 오른쪽 테이블이 일대다 관계에 있을때 유용.
    • ex
      • SELECT g.girl, t.toy
      • FROM girls g
      • LEFT OUTER JOIN toys t
      • ON g.toy_id = t.toy_id;
    • 다른 테이블과 일치하는 것이 있는가에 상관없이 행을 반환.
      • 일치하는게 없으면 NULL
      • 왼쪽 외부조인의 결과가 NULL값이면 오른쪽 테이블에 왼쪽테이블에 상응하는 값이 없다는 의미.

Database basic with mySQL 강의정리

  • SW에서 데이터 중요해?
    • 객체지향 프로그래밍의 중심은 객체(데이터)
    • SW성능의 핵심은 데이터의 관리 성능
    • SW품질은 데이터 관리에 큰 영향 받음
  • 용어
    • 데이터 : 현실세계에서 관찰하거나 측정한 사실 또는 값
    • 정보 : 의사결정에 활용하기 위해 데이터를 처리한 결과물
    • 데이터베이스 : 데이터의 모임
      • 특정 조직의 다수 사용자가 공유하여 사용하도록 통합해서 저장한 운영 데이터의 집합
      • 특징
        • 자기 기술성 : DB 시스템이 데이터베이스 자체와 구조 및 제약조건 정의(메타데이터)를 가지고 있음
        • 프로그램과 데이터의 분리 : 프로그램-데이터 독립성
        • 데이터 추상화 : 데이터 모델을 사용하여 자세한 내용은 은닉하고 개념적 뷰만을 제공
        • 데이터에 대한 다중 뷰의 제공 : 관심있는 일부 데이터를 뷰로 정리 가능
        • 데이터의 공유와 다수 사용자 트랜잭션 처리
          • 다수 사용자용 DBMS
          • 트랜잭션 : DB작업의 수행 단위, ACID성질(시험이나 면접에 많이 나와)
    • DBMS : DataBase Management System
      • DB의 생성과 관리를 담당하는 소프트웨어 패키지
      • DBMS는 운영체제와 함께 중요한 시스템 소프트웨어 패키지로 분류
      • Oracle, IBM DB2, Sybase, MS-SQL, MySQL
      • 등장배경 – 파일 시스템의 문제점
        • 데이터 중복성 : 저장공간 낭비 및 데이터의 일관성과 무결성 유지 어려움
        • 데이터 종속성 : 사용하는 파일 구조를 변경하면 프로그램도 같이 변경 필요
    • 데이터베이스 시스템 : 데이터베이스 자체 + 이를 관리하는 SW(DBMS+응용프로그램)통칭
      • DBMS와 혼용하여 많이 사용
    • 메타데이터 : 데이터에 대한 데이터

 

 

<3. Database system 개념과 아키텍쳐>

 

학습목표

  • 데이터베이스 시스템의 구조와 용어들 공부하끠

데이터 모델

  • 데이터 모델
    • 데이터 추상화를 위한 도구
    • DB의 구조(데이터의 타입, 관계, 제약조건..)를 명시하기 위해 사용하는 개념들의 집합
    • 데이터의 생성, 검색, 수정, 삭제를 수행하는 연산들을 포함
  • 분류
    • 고수준 데이터 모델
      • 일반 사용자들이 이해하는 데이터 표현
      • HW독립적 ER모델 (컴 사양이 어떤지는 상관없음)
    • 표현(구현)데이터 모델
      • 일반사용자도 이해하고 컴퓨터상에 구현도 가능
      • DBMS에서 많이 사용(관계 데.모, 계층 데.모, 네트워크 데.모 모델)
    • 저수준(물리적)데이터 모델
      • 데이터가 컴에 저장되는 방식을 표현
      • HW의존적
      • 레코드 형식, 순서, 인덱스 등 (이것들 일부를 이해하는게 이 강의의 목표)
  • 데이터베이스 스키마
    • Schema : 대략적 계획, 도식…
    • DB자체에 대한 기술.
    • 자료를 저장하는 구조와 표현법을 정의. 우리가 사용하는 DB가 어떤 모습인지
  • 데이터베이스 상태
    • 특정 시점의 DB의 데이터들의 집합.
    • instance들의 집합이라고도 함.

3단계 스키마 아키텍쳐와 데이터 독립성

  • 구조
    • 내부단계–내부 스키마–물리적 저장구조 기술
    • –(물리적 데이터 독립성)–
    • 개념단계–개념 스키마–데이터베이스 구조 기술
    • –(논리적 데이터 독립성)–
    • 외부단계–외부 뷰1, 외부 뷰2…–사용자에게 데이터베이스가 보여지는 구조 기술
  • 논리적 데이터 독립성
    • 외부 스키마나 응용프로그램을 변경하지 않으면서도 개념 스키마를 변경할 수 있는 능력(성질)
  • 물리적 데이터 독립성
    • 개념 스키마를 변경하지 않으면서 내부 스키마를 변경할 수 있는 능력(성질)
    • 인덱스 구조 등의 변경에도 개념 스키마(ex-테이블구조)는 영향을 받지 않는 능력

데이터베이스 언어

  • 데이터 정의어(DDL: data definition lang)
    • 개념 스키마와 내부 스키마를 정의
    • CREATE TABLE, DROP INDEX
  • 데이터 조작어(DML: data manipulation lang)
    • 데이터를 검색, 삽입, 삭제, 수정하기 위한 조작 언어
    • 대표적 언어: SQL
  • 데이터 관리어(DCL: data control lang)
    • DB관리를 위해 사용하는 언어
    • backup, grant

 

 

 

<5. 관계형 데이터베이스 개념>

학습목표

  • 관계형 데이터베이스의 기본 요소와 관계형 데이터 모델을 이해, 데이터베이스 시스템 사용시 관련 개념 활용가능
  • 관계형 데이터 모델의 제약조건을 이해하고 데이터베이스 시스템 사용시 관련 개넘을 활용가능

관계 데이터 모델의 개념

  • 1970년에 Ted Codd.가 처음 소개
  • 오라클의 Oracle, MySQL, IBM의 DB2, MS의 SQL Server, Access
  • 용어
    • 테이블->릴레이션      행->투플        열->애트리뷰트
    • 도메인 : (애트리뷰트가 가질 수 있는) 원자값들의 집합
      • ex) 전화번호->11자리 번호들의 집합, Names : 개인 이름들의 집합.
    • 데이터 타입 : 도메인은 실제 데이터 타입으로 명시함
      • ex) string, integer, real
  • 릴레이션 스키마
    • 릴레이션 이름 R과 애트리뷰트 A들의 집합으로 R(A1, A2…An)로 표기
    • 릴레이션 스키마 R(A1, A2…An)의 투플 t
      • 값들의 (순서화된) 집합 t=<V1, V2…Vn>
    • 릴레이션 스키마 R에 대한 릴레이션(또는 릴레이션 인스턴스) r 또는 r(R)
      • 투플의 집합; r=r(R)={t1, t2…tm)
      • r(R)은 실세계의 특정 상태를 반영

관계 데이터 모델의 제약조건

  • 제약 조건 : 모든 릴레이션 인스턴스들이 만족해야하는 조건
    • a.k.a 데이터 무결성 제약조건
  • 데이터 모델 스키마에서 DDL을 통해 직접 표현 가능한 제약조건
    • 도메인 제약조건
      • 각 애트리뷰트 A의 값은 반드시 A의 도메인 dom(A)에 속하는 원자값이어야 함
    • 키 제약조건
      • 릴레이션은 투플의 집합으로 정의되므로, 모든 원소는 중복이 안됨
      • 어떤 두 투플도 릴레이션의 모든 애트리뷰트에 대해 같은 값들의 조합을 가질 수 없음
        • 슈퍼키 : 릴레이션 r의 투플들을 고유 식별 가능한 애트리뷰트의 집합
        • 키: 최소성을 만족하는 슈퍼키
        • 기본키 : 릴레이션의 여러 키(후보키)중에서 선택한 키
    • 널에 대한 제약조건
      • 애트리뷰트 값으로 널을 허용안하면 널 못가짐
    • 엔티티 무결성 제약조건(==개체 무결성 제약조건)
      • 기본키가 각 투플들을 식별하는 데 이용되기 때문에 어떠한 기본키도 널 값 못가짐
    • 참조 무결성 제약조건
      • 한 릴레이션에 있는 투플이 다른 릴레이션에 있는 투플을 참조하려면 반드시 참조되는 투플이 그 릴레이션 내에 존재해야 함
      • 하나의 릴레이션 R에서 속성 F의 값으로 다른 릴레이션 S의 기본키 P값을 참조하는 경우에 R과 S는 참조 무결성 제약조건을 가진다고 함(F는 null을 가질 수 있음)
        • ->무슨말이지???

 

<4. 좋은 테이블 설계>

용어

  • 원자적이다
    • 데이터가 충분히 쪼개어졌다.
    • 쪼갤 수 없는 가장 작은 조각으로 쪼개졌다.
  • 테이블을 정규화한다
    • 테이블들이 표준 규칙을 따르게 한다.
    • 그러면 새로운 설계자가 테이블을 이해하기 더 쉽다.
      • 이점 : 중복 데이터가 없어서 데이터베이스의 크기 줄여준다, 찾아야 할 데이터가 적어 쿼리가 더 빨라짐.
    • 제 1정규형(1NF)
      • 각 행의 데이터들은 원자적 값을 가져야 한다.
      • 각 행은 유일무이한 식별자인 기본키(primary key)를 가지고 있어야 한다.
    • 기본키
      • 각 레코드를 다른 레코드와 구분하는 열
      • NULL이 될 수 없다
      • 레코드가 삽입될 때 값이 있어야 한다.
      • 간결해야 한다.
      • 변경불가.

 

 

<7. 테이블이 여러개인 데이터베이스 설계>

용어

  • 참조키
    • 테이블의 한 열로 다른 테이블의 기본키를 가리킨다.
    • 연결되는 기본키와 다른 이름일 수도 있다.
    • 한 테이블의 열들이 다른 테이블의 열과 연결되도록 할때 사용.
    • null일 수 있다.
    • 유일할 필요가 없다.
  • 부모키, 부모 테이블
    • 참조키에서 참조하는 기본키
    • 부모키가 있는 테이블
  • 참조 무결성
    • 참조키의 값으로 부모테이블에 존재하는 키의 값만을 넣을 수 있다.
  • 참조키 제약조건
    • 제약조건 추가 안하고 그냥 다른 테이블 참조하며 참조키라고 안 하는 이유
      • 제약조건을 사용하면 부모 테이블에 존재하는 값만을 넣을 수 있다.
      • 두 테이블간의 연결을 강제하는 것.
    • my_contacts의 기본키가 interests테이블의 참조키 값으로 나오면 그 행은?
      • 지울 수 있다. 하지만 먼저 참조키의 행을 지워야 한다.
      • 행이 interest테이블에 남아있으면 느려진다->고아->느리게 한다.
  • 합성키
    • 여러 개의 열들로 구성되어 유일무이한 키를 만드는 기본키

참조키가 있는 테이블 생성

  • CREATE TABLE interests(
  • int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  • interest VARCHAR(50) NOT NULL,
  • contact_id INT NOT NULL,    //참조키
  • CONSTRAINT my_contacts_contact_id_fk    //제약조건. 참조키가 어느 테이블을 참조하는지, 키의 이름, 제약조건이 참조키(fk). 선택적인 라인이다. 사용하면 좋음
  • FOREIGN KEY (contact_id)    //참조키
  • REFERENCES my_contacts(contact_id)    //참조키가 어느 테이블을 참조하는지 표시하고, 다른 테이블의 열 이름을 표시.
  • );

테이블간의 관계

  • 일대일
    • 스키마에서 실선으로 연결
    • 부모 테이블 하나의 행이 자식 테이블 하나의 행과 연결
  • 일대다
    • 연결선: 한쪽끝에 화살표
  • 다대다
    • 여러명의 여자와 여러켤레의 신발
    • 연결 테이블 사용. 연결 테이블은 각 테이블의 키를 가지고 있다.

종속

  • 함수 종속 관계
    • T.x ->; T.y     //T라는 테이블에서 열y는 열 x에 함수적으로 종속된다.
  • 관계
    • 종속되는 열 : 다른 열이 변하면 변경
    • 독립되는 열 : 다른 열에 종속X
  • 부분적 함수 종속
    • 키가 아닌 열이 합성키의 전부가 아닌 일부에 종속되는 경우
  • 이행적 종속 관계
    • 키가 아닌 열이 다른 열의 변경을 초래

제2정규형

 

 

 

<ER모델>

학습목표

  • 데이터 모델링을 이해하고 데이터베이스 설계과정을 설명가능
  • ER데이터 모델링을 이해하고 데이터베이스 설계에 활용가능
  • 개념적 데이터 모델 설계 결과를 이용하여 논리적 데이터모델 설계가능

데이터 모델링

  • 모델링 개념: 요소 + 연산 + 제약조건
    • 구성요소 : 어떤 것들을 대상으로?(relation, tuple, attribute, value)
    • 연산 : 무슨 일들을 어떻게?(CRUD – Create Read Update Delete, insert)
    • 제약조건 : 지켜져야 할 규칙(도메인-, 키-, 널-, 엔티티무결성-, 참조무결성-)
  • 모델링 단계: 개념 – 논리 – 물리 3단계

개체관계(Entity relationship) 데이터 모델

  • 개념적 데이터모델
  • 데이터를 엔티티(개체), 관계, 애트리뷰트(속성)로 모델링함.
    • Entity
      • 실세계에서 독립적으로 존재하는 실체
      • 저장할 가치가 있는 중요 데이터를 가지고 있는 사람/사물/개념/사건
      • 사각형
      • 엔티티 타입 : 같은 애트리뷰트를 갖는 엔티티들의 집합
    • Relationship
      • 엔티티들간의 연관관계
      • 개체와 개체 사이의 의미있는 연관성
      • 관계도 애트리뷰트를 가질 수 있음
      • 마름모
      • 보통 동사형 쓴다.
      • 유형(관계차수-관계에참여하는 개체타입의 수- 기준)
        • 이항관계, 삼항관계, 순환관계
      • 유형(매핑 카디널리티 기준)
        • 매핑 카디널리티: 관계를 맺는 두 개체 집합에서, 각 개체 인스턴스가 연관성을 맺고 있는 상대 개체 집합의 인스턴스 개수
        • 일대일, 일대다, 다대다
    • 엔티티 타입
      • 같은 애트리뷰트를 갖는 엔티티들의 집합
    • Attribute
      • 엔티티를 설명하는 속성
      • 기본키 애트리뷰트: 각 개체 인스턴스를 식별하는데 사용. 밑줄.
      • 분류(값)
        • 단일값 애트리뷰트
        • 다중값 애트리뷰트(동그라미2개)
      • 분류(의미분해 가능성)
        • 단순 애트리뷰트
        • 복합 애트리뷰트(성-이름)
      • 유도 애트리뷰트
        • 기존의 다른 애트리뷰트 값으로부터 유도되어 결정되는 속성(NumOfEmp)
        • 점선으로 표시.
  • 개체-관계 다이어그램(E-R diagram)
    • 개체-관계 모델을 이용해 현실세계를 개념적으로 모델링한 결과물을 그림으로 표현
  • 관계의 참여 특성
    • 전체(필수)참여
      • 모든 개체 인스턴스가 관계에 반드시 참여해야함
      • 모든 고객은 반드시 책을 구매해야 함
      •  ERD에서 이중선으로 표시
    • 부분(선택)참여
      • 개체의 일부만 관계에 참여해도 됨
      • 책 개체의 일부만 고객 개체와의 ‘구매’관계에 참여
  • 약한 엔티티 타입
    • 자신의 키 애트리뷰트가 없는 엔티티 타입
    • 애트리뷰트 중 하나가 다른 엔티티타입(식별 엔티티타입)과 연계됨으로써 식별가능
    • 강한- : 키를 가지는 일반 엔티티타입
    • 이중사각형으로 표시. 약한 엔티티가 식별엔티티와 맺는 관계는 이중마름모로 표현

 

<DB모델링 : ER매핑에 의한 논리 설계>

학습목표

  • 개념적 데이터 모델 설계 결과를 이용하여 논리적 데이터모델을 설계가능

논리적 설계

  • 개념
    • ER다이어그램으로 표현된 개념적 구조를 DB에 저장할 형태로 표현한 논리적 구조
    • 관계 데이터 모델, 계층 데이터 모델, 네트워크 데이터모델 등이 있다.
    • 개념 데이터 모델로부터 논리 데이터 모델로 변환하는 규칙에 따라 논리데이터 모델을 생성
  • 매핑1단계: 정규엔티티->릴레이션
    • 엔티티 타입은 릴레이션으로 매핑
    • 모든 단순 애트리뷰트 포함
    • 엔티티타입의 키 중 하나를 기본키로 지정
  • 매핑2단계: 약한엔티티->릴레이션
    • 약한 엔티티 타입을 릴레이션으로 매핑
    • 모든 단순 애트리뷰트 포함
    • 소유 릴레이션의 키 속성을 포함
    • 생성된 릴레이션의 기본 키는 소유 릴레이션의 키와 약한 엔티티 타입의 부분키를 합쳐서 만든다.
  • 매핑3단계: 1:1관계->외래키
    • 외래키 접근방식
      • 한 릴레이션(S)를 선택하여 T의 기본키를 S의 외래키로 표현. S는 완전참여 릴레이션을 선택하는 것이 좋음
      • 관계타입의 모든 단순 애트리뷰트를 S에 포함시킴
      • 이 방식이 가장 유용. 다른 방식은 참조만 할것
    • 합병 릴레이션 접근방식
      • 두 릴레이션을 하나의 릴레이션으로 통합. 두 릴레이션이 모두 완전참여일때 좋음
    • 교차 참조/관계 릴레이션 접근방식
      • S와 T를 교차참조하는 제 3의 릴레이션 R생성
  • 매핑 4단계: 1:N관계->외래키
    • 외래키 접근 방식(권장)
      • N측의 릴레이션(S)를 선택하여 1측의 릴레이션 T의 기본키를 S의 외래키로 포함
      • 관계타입의 모든 단순 애트리뷰트를 S에 포함시킴
    • 교차 참조/관계 릴레이션 접근방식(권장 X)
      • S와 T를 교차 참조하는 제 3의 릴레이션 R생성
  • 매핑 5단계: M:N관계->릴레이션
    • 별도의 릴레이션(관계 릴레이션이라고 부름)으로 생성하고, 관계에 참여하는 두 릴레이션의 기본 키를 각각 참조하는 외래키로 애트리뷰트를 고성
    • 이 때 두 외래키가 관계 릴레이션의 기본키를 형성
  • 매핑 6단계: 다중키 애트리뷰트->릴레이션
    • 릴레이션 R의 다중키 애트리뷰트는 R의 기본키를 포함하는 새로운 릴레이션으로 매핑된다.
    • 새로운 릴레이션의 키는 R의 기본키와 다중키 애트리뷰트의 조합이다.
  • 매핑 7단계: N차 관계->릴레이션
    • 관계에 참여하는 n개 릴레이션들의 키들로 구성되는 관계 릴레이션으로 매핑된다.
    • 관계 릴레이션의 애트리뷰트들은 참여 릴레이션의 기본키를 참조하는 외래키들과 관계속성(들)으로 구성된다.