---
disqus: hackmd
---

Databases and SQL for Data Science with Python <br> WEEK_1 - Getting Started with SQL
====

###### tags: `IBM Data Engineering Professional Certificate`,`Reading Note`,`Coursera`,`Databases and SQL for Data Science with Python`

### Overview

>* 講解 Database, Relational Database, DBMS, RDBMS 和 SQL 的功能及之間的差異。
>* 運用 SQL 的 SELECT, WHERE, COUNT, DISTINCT, LIMIT, INSERT, UPDATE, DELETE 指令操作 database 裡的資料。

<br>

## Basic SQL
### 1. Welcome to SQL for Data Science
* 在Data的領域中，都會需要使用SQL來訪問資料庫裡的資料。
<br>

### 2. Introduction to Databases
* SQL (Structured Query Language)
* 查詢關聯資料庫中的語言

* Data
* Facts(words, numbers)
* 在商業裡其中一個有價值的資產
* 需要安全的環境

* Database
* 資料倉庫
* 提供新增，更新，查詢資料的功能
* 目前有多種資料庫提供儲存各種的資料

* Relational Database
* 提供儲存表格資料-行列格式
* 每列會有一個屬性
* Table包含物件
* Relational是Table之間的關係

* DBMS
* 資料庫=資料倉庫
* DBMS(Database Management System)
* 資料管理的軟體
* Database = Database Server = Database System = Data Server = Database Management Systems

* RDBMS
* RDBMS(Relational Database Management System)
* 包含各種操作資料的軟體
* access
* organization
* storage
* 目前的產品有：
* MySQL
* Oracle Database
* IBM Db2

* Basic SQL Command
* 在Table操作資料的基本指令
* Create a table
* Insert
* Select
* Update
* Delete
<br>

### 3. SELECT Statement
* Retrieving rows from a table
* SELECT statement
* SELECT 是個 DML(Data Manipulation Language)語言，是用作讀取及更新資料

* Using the SELECT statement
* 使用 * 號提取所有在table裡的所有資料

    SELECT * FROM <tablename>

* Retrieving a subset of the columns
* 提取想要的column裡的資料

    SELECT <column 1>, <column 2> FROM <tablename>

* Restricting the result set : WHERE clause
* 使用關鍵字搜尋資料庫裡的資料

    SELECT * FROM <tablename>
    WHERE <column> = 'keyword'

* WHERE clause comparision operators
* SQL的比較運算符
<br>
<br>

### 4. COUNT, DISTINCT, LIMIT
* COUNT
* 計算rows在table裡的數量

    SELECT COUNT(*) FROM <tablename>

* 與WHERE一併使用

    SELECT COUNT(*) FROM <tablename>
    WHERE <column> = 'keyword'

* DISTINCT
* 與COUNT的差別在於，DISTINCT只會回傳唯一的資料

    SELECT DISTINCT <column> FROM <tablename>

* LIMIT
* Rows會被顯示的數量

    SELECT * FROM <tablename> LIMIT 10

* 與WHERE一併使用

    SELECT * FROM <tablename>
    WHERE <column> = 'keyword' LIMIT 10

<br>

### 5. INSERT Statement
* Adding rows to a table
* INSERT statement
* SELECT 是個 DML(Data Manipulation Language)語言，是用作讀取及更新資料

* Using the INSERT statement
* 把資料存入到table

      INSERT INTO <tablename>
          (column 1, column 2, column 3)
      VaLUES
          ('value 1', 'value 2', 'value 3')

* Inserting multiple rows
* 存入一筆以上的資料

      INSERT INTO <tablename>
          (column 1, column 2, column 3)
      VaLUES
          ('value 1', 'value 2', 'value 3')
          ('value a', 'value b', 'value c')

### 6. UPDATE and DELETE Statements
* Altering rows of a table - UPDATE statement
* UPDATE statement
* UPDATE 是個 DML(Data Manipulation Language)語言，是用作讀取及更新資料

* Using the UPDATE statement
* 修改table裡的資料

      UPDATE <tablename>
      SET <column 1> = 'new information 1', <column 2> = 'new information 2'
      WHERE <column> = 'specific information'

* Deleting rows from a table
* DELETE statement
* DELETE 是個 DML(Data Manipulation Language)語言，是用作讀取及更新資料

* Using the DELETE statement
* 刪除table裡的row

      DELETE FROM <tablename>
      WHERE column IN ('specific information 1', 'specific information 2')

## Summary & Highlights
* 課程完整整理的內容，所以把它記錄下來

* You can use Data Manipulation Language (DML) statements to read and modify data.
* The search condition of the WHERE clause uses a predicate to refine the search.
* COUNT, DISTINCT, and LIMIT are expressions that are used with SELECT statements.
* INSERT, UPDATE, and DELETE are DML statements for populating and changing tables.
<br>