stanford CS 193A 16sp Lecture 15: Databases and SQL

강의링크 : https://www.youtube.com/watch?v=6p_P6zMVsno&index=15&list=PLYKXDWkoIMUH088iBEr_B2WPfbPwtaG-V

1. 데이터베이스란?

관계형 데이터베이스 $Relational Database$ : 관련된 표$table$로 구조화 된 데이터. But it's not only data but also operations.

 - 엑셀 시트의 집합같은 것

 - CRUD$create, read, update, delete$ + 빠른 검색 등 유용한 연산을 지원함 

 - class 와 database 

class called student & database called student

columns : fields of class, rows : object of class.

2. SQL$Structured Query Language$ : 관계형데이터베이스에서는 데이터를 정의, 관리, 검색 할 때 주로 SQL을 사용함

 - 어떻게 작동하는지는 몰라도 됨, 원하는 것을 말하기만 하면 됨. $not really programming language$

3. 다양한 데이터베이스 소프트웨어

 - Oracle

 - Microsoft : SQL server$powerful$, Access$simple$

 - PostgreSQL : powerful/ complex free open-source database system

 - SQLite : transportable, lightweight free open-source database system

  * 메모리 사용이 효율적. 스마트폰 등 작은 기기에 올릴 때 유용함

 - MySQL : simple  free open-source database system, many server runs "LAMP"$Linux, Apache, MySQL and PHP$

 4. Example database

- SQL을 list 에 비효율적임 : 리스트를 표현하려면 여러 줄이 필요함$need several rows to represent list$

- 왜 teacher table을 따로 가지고 있을까? 선생님 이름이 바뀐다면 일일이 바꿀 필요 없도록.

- 왜 language를 country table에 넣지 않았을까? 

1. language에 대한 다른 information도 포함하고 있으므로 $ex, if it is official$

2. 한 나라가 여러 language를 사용할 수도 있음

- roles table : 한 영화에 한 배우가 여러 역을 연기한 것을 표현할 수 있음

5. The SELECT statement

SELECT column$s$ FROM table WHERE condition;

- 어떤 column을 원하는지, 어떤 table에서 원하는지, 어떤 조건을 만족하는 row를 원하는지

- table and column names are case sensitive

- SELECT DISTINCT : remove duplicates 

6. Android SQLite Database

- 기본 명령어

SQLiteDatabase db = openOrCreateDatabase$"name", MODE_PRIVATE, null$; 

// 해당 이름의 데이터베이스가 없었으면 새로 만들어 열고, 있었으면 있던걸 연다.

db.execSQL$"query"$; 

// 이렇게 쿼리 실행. Invalid query 일 경우 throws an SqliteExeption

// 데이터는 /data/data/packageName/ databases/에

저장됨. $파일을 직접 조작할 일은 없음. 백업 목적이 아닌 이상..$

- 추가 명령어들

- cursor  : object that allows you to iterate through the results of SELECT query. 쿼리의 결과를 순회할 수 있는 객체

  * 앞/뒤 row 로 움직힐 수 있음. & 현재 행의 특정 칼럼들의 값을 반환할 수 있음

Cursor cr = db.rawQuery$"SELECT id, email FROM students", null$; if$cr.moveToFirst($) { do { int id = cr.getInt$cr.getColumnIndex("id"$); String email = cr.getString$cr.getColumnIndex("email"$); ... }while$cr.moveToNext($); cr.close; }

* 라이브러리에 있는 메소드 예시

Stanford android library simple code

Cursor cr = db.rawQuery$"SELECT id, email FROM students", null$; for(SimpleRow row :SimpleDatabase.rows$cr$){ int id = row.get$"id"$; String email = row.get$"email"$; ... } cr.close;

 

7. Importing a .sql file

어떻게 데이터를 처음 build할까? =>  a.sql file를 임포트

- res/raw 폴더에 .sql파일을 넣음

- scanner로 open

- ;까지 readline

- 읽은 행을 execSQL로 실행

$반복$

// example.sql파일을 "example"이라는 데이터베이스로 불러올 것 SQLiteDatabase db = context.openOrCreateDatabase$"example"$; // res/raw/example.sql 파을 읽을 것 Scanner scan = new Scanner$getResources($.openRawResource$R.raw.example$); String query = ""; // 파일의 마지막 줄일 때 까지 while$scan.hasNextLine($){ // 한줄 더 읽고 query += scan.nextLine+"\n"; // ;로 끝나면 실행, 안하면 다음줄도 읽어 붙임 if$query.trim($.endsWith$";"$){ db.execSQL$query$; query = ""; } }

 

Stanford android library simple code

SimpleDatabase.with$this$ .executeSqlFile$db, R.raw.example$;

8. WHERE부분$조건 부분$ 추가

▶ =$==아님!$, <>$!= 아님!$, > >=, <, <=

▶ BETWEEN min AND max

▶ LIKE pattern $와일드카드는 %. ex. where first_name like "MAR%" and last_name like "C&"$

▶ IN(value, value, ... value)

▶ condition1 AND condition2; condition1 OR condition2

9. ORDER BY, LIMIT

▶ SELECT code, name, population FROM countries WHERE name LIKE 'United%' ORDER BY population;

▶ SELECT * FROM countries ORDER BY population DESC, gnp;

// 중요도가 높은 순으로 여러개를 명시할 수도 있음

▶ SELECT name FROM cities WHERE na me LIKE 'K%' LIMIT 5;

// 처음 N개의 결과를 반환

10. INSERT and REPLACE

▶ INSERT INTO table $columnName, ... ,columnName$ VALUES $value, value, ... , value$;

// 명시해서 넣을 칼럼 이름과 값을 넣어줌

▶ REPLACE INTO table $columnName, ... ,columnName$ VALUES $value, value, ... , value$ WHERE condition;

// 강의에서 안먹혀서 넘어감

11. UPDATE

여기서 갑자기 그래프 그리는걸 추가하더니 그래프를 그린다..$55분 쯤$

사용자 인풋으로 쿼리 넣는 부분에서 " 처리하는법

String query = "SELECT * FROM ranks WHERE name = '"+name+ "' AND sex = '"+sex+"'; // 또는

String query = "SELECT * FROM ranks WHERE name = \""+name+"\" AND sex = \""+sex+"\";

String query = "SELECT * FROM ranks WHERE name = \""+name+"\" AND sex = \""+sex+"\";

SQLiteDatabase db = openOrCreateDatabase$"babynames"$;

for$SimpleRow row : SimpleDatabase.rows(db.rawQuery(query,null$){

int year = row.get$"year"$;

int rank = row.get$"rank"$;

DataPoint point = new DataPoint$year, rank$;

line.appenData$point. false, 13$;

}

GraphView graph = $GraphView$ findViewById$R.id.graph$;

graph.addSeries$line$;