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);