Databases and SQL for Data Science with Python WEEK_1 - Getting Started with SQL

--- disqus: hackmd --- Databases and SQL for Data Science with Python 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 裡的資料。 ## Basic SQL ### 1. Welcome to SQL for Data Science * 在Data的領域中，都會需要使用SQL來訪問資料庫裡的資料。 ### 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 ### 3. SELECT Statement * Retrieving rows from a table * SELECT statement * SELECT 是個 DML(Data Manipulation Language)語言，是用作讀取及更新資料 * Using the SELECT statement * 使用 * 號提取所有在table裡的所有資料 ```SQL= SELECT * FROM <tablename> ``` * Retrieving a subset of the columns * 提取想要的column裡的資料 ```SQL= SELECT <column 1>, <column 2> FROM <tablename> ``` * Restricting the result set : WHERE clause * 使用關鍵字搜尋資料庫裡的資料 ```SQL= SELECT * FROM <tablename> WHERE <column> = 'keyword' ``` * WHERE clause comparision operators * SQL的比較運算符 ![](https://i.imgur.com/fRteRlN.png =300x) ### 4. COUNT, DISTINCT, LIMIT * COUNT * 計算rows在table裡的數量 ```SQL= SELECT COUNT(*) FROM <tablename> ``` * 與WHERE一併使用 ```SQL= SELECT COUNT(*) FROM <tablename> WHERE <column> = 'keyword' ``` * DISTINCT * 與COUNT的差別在於，DISTINCT只會回傳唯一的資料 ```SQL= SELECT DISTINCT <column> FROM <tablename> ``` * LIMIT * Rows會被顯示的數量 ```SQL= SELECT * FROM <tablename> LIMIT 10 ``` * 與WHERE一併使用 ```SQL= SELECT * FROM <tablename> WHERE <column> = 'keyword' LIMIT 10 ``` ### 5. INSERT Statement * Adding rows to a table * INSERT statement * SELECT 是個 DML(Data Manipulation Language)語言，是用作讀取及更新資料 * Using the INSERT statement * 把資料存入到table ```SQL= INSERT INTO <tablename> (column 1, column 2, column 3) VaLUES ('value 1', 'value 2', 'value 3') ``` * Inserting multiple rows * 存入一筆以上的資料 ```SQL= 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裡的資料 ```SQL= 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 ```SQL= 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.