7. Database: SQL, MySQL

請尊重智慧財產權,若有抄襲等違反之事例,請"轉載人"盡速改善

一、 Data Base Management System (DBMS)

SQL全名為結構化的搜尋語言 (Structured Query Language),用於資料庫中的標準數據搜尋語言,SQL是一個專門用來處理關聯式資料庫的標準程式語言,也就是說它只能用來處理資料庫。

SQL被稱為”非程序語言” (單獨的撰寫一個命令,一個命令便擁有一個完整的意義,可以用來處理一項工作),它允許使用者在高層資料結構上工作,且不限制使用者對數據存放的方法,也不要求使用者了解其具體存放數據的方式;SQL雖然是功能強大的資料庫語言,但它不是一般的程式語言,所以無法僅利用SQL就能實現其他各種程式語言所能執行的架構。   

在實際使用時,一般處理還是用傳統程式語言來處理,而SQL之中會有內建可讓其他程式語言將它叫出來的函數介面。當碰上需要執行資料庫方面的處理時,一般程式語言便可利用這函數介面來呼叫SQL執行處理。  

1.  建立關係資料庫: 實體關係圖 (ER Diagram)
(1)實體完整性:每一行 (column)的主鍵 (primary key)必須要有一個值,且該值必須是唯一的。

(2)參照完整性:每一個外鍵 (foreign key)的值必須作為另一個表的主鍵 (primary key)的值。

2.  透過表格 (table)的型式來呈現資料
(1)所存取的東西就是資料,所以row又可稱作record。

(2)下圖中,有三個欄位 (column),每一個欄位都有一個名稱 (name),分別為身分證字號、性別、以及姓名。

(3)每一個相關欄位 (column)又稱作attribute,每一列 (row)的相關資料又稱作tuple,每一列相關資料的總數又稱作資料總數 (cardinality)。
 

3.  用一些數學運算來存取這些資料
Join:於兩個relations中選取符合”條件”的來做組合
Ex:C ← JOIN A and B where A.W = B.X

Union:table資料之間取聯集
Intersection:table資料之間取交集
Difference:table資料之間取差集

Product:table資料之間相乘作組合
Divide
1. lossless decomposition
2. nonloss decomposition

Dividing a relation into smaller relations and does not loss of information

Project:選取出relation中的columns作組合 

4.  資料庫的三種狀態
(1)Restrict:有時候要執行一些管理性的操作,而這些操作運行的時候,不能有其他用戶同時訪問資料庫。對於這種情況,可以設置系統進入RESTRICTED SESSION狀態,禁止普通用戶登入資料庫。

(2)Quiesce:當資料庫處於QUIESCE狀態時,只有DBA (Database Administrator, 資料庫管理員)會話可以進行操作,而普通會話會處於等待狀態,只有資料庫退出QUIESCE狀態時,普通會話才能繼續操作。

QUIESCE 似乎和RESTRICT很相似,都是修改資料庫的狀態,使得DBA用戶可以進行管理操作,避免非DBA用戶同時訪問,但是兩者還是有明顯的區別。首先,RESTRICT是禁止普通用戶登入,而對已經登入的用戶無能為力,如果要徹底禁止普通用戶的訪問,就必須通過重啟或者手工判斷已經連接的普通會話,並執行KILL SESSION的操作。然而,QUIESCE是通過設置系統的QUIESCE RESTRICTED,使得所有非DBA用戶處於等待狀態,不管是新登入的還是已經存在的普通用戶會話,都無法執行新的操作,直到系統退出QUIESCE狀態。

因此,QUIESCE狀態對於7*24環境 (全天候使用)是十分有幫助的,對於其他用戶而言,指示操作的等待時間變得很長,而不會報錯。當然QUIESCE有RESTRICT所沒有的優點,也必然有一些額外的要求,那就是資料庫必須配置資源管理 (Resource Management)。

 (3)Suspend:RESTRIC所限制的是沒有RESTRICTED SESSION權限的用戶,使得這些用戶無法登入資料庫。而QUIESCE針對所有非SYS (DOS命令)、SYSTEM用戶,禁止任何新的操作,包括登入、查詢、DML (Data Manipulation Language, 資料處理語言)等等。和RESTRICT、QUIESCE不同的是,SUSPEND主要是限制資料庫IO (Input/Output)的操作,而且SUSPEND限制的不僅僅是普通用戶,而是資料庫中任何的用戶。

 

二、 SQL語法

1.  資料庫
(1)建立 
mysql> create database 
資料庫名稱

(2)移除 
mysql> drop database 
資料庫名稱

(3)查詢有哪些資料庫
mysql> show databases;

(4)選取並連線
mysql> use 資料庫名稱

2.  資料表
(1)建立table(資料表)
mysql> create table  資料表名稱 (性質);

(2)移除table
mysql> drop table  資料表名稱;

(3)查詢table
mysql> describe  資料表名稱;
或者 mysql> show columns from 資料表名稱;

(4)增加欄位
mysql> alter table  資料表名稱
add  欄位1,
add  欄位2,
add .....;

(5)刪除欄位
mysql> alter table  資料表名稱
drop 欄位1,
drop 欄位2,
drop .....;

註:drop和add不可同時並存

(6)加上Index
mysql> alter table  資料表名稱
add index (欄位1),
add index (欄位2),
add index (.....);

(7)刪除Index
mysql> alter table  資料表名稱
drop index  欄位1,
drop index  欄位2,
drop index .....;

3.  Select語法 
(1)讀所有資料,且不設條件
mysql> select * from  資料表名稱;
Ex:
     

ID_information
 ID  Sex Name
A123456789 王小明
B123456789 王大明
C223456789 王小花

mysql> select * from ID_information;

結果
ID Sex Name
 A123456789 王小明
 B123456789 王大明
 C223456789 王小花

(2)讀部分資料,且指定條件
mysql> select 欄位1, 欄位2, ... from 資料表名稱 where 條件;
Ex:

ID_Information
ID Sex Name
 A123456789 王小明
 B123456789 王大明
 C223456789 王小花

 mysql> select ID, Name from ID_information; 

結果
 ID  Name
 A123456789 王小明
 B123456789 王大明
 C223456789 王小花

 mysql> select ID ,Name from ID_information   where Sex = '男';

結果 
 ID Name
 A123456789 王小明
 B123456789 王大明

 mysql> select ID, Name from ID_information   where Name like '%小%';

結果
 ID Name
 A123456789 王小明
 C223456789 王小花

註:
like '%S%' 代表字串中任有S即被抓出,同於模糊搜尋
like '_S%' 代表S前面必須要有一字元,S後方可有可無,不限制字元個數
like 'S' 代表恰好只能有S

(3)將資料以排序的方式取出 (order by)
Ex: 

ID_Information 
 ID Sex Name
 A123456789 王小明
C223456789 王小花
B123456789 王大明

#1 升冪排序
mysql> select  欄位1, 欄位2, ... from 資料表名稱 order by 欄位 ASC;

mysql> select ID, Sex, Name from ID_Information order by ID ASC;

結果 
ID Sex Name
 A123456789 王小明
 B123456789 王大明
 C223456789 王小花

 #2 降冪排序
mysql> select  欄位1, 欄位2, ... from 資料表名稱 order by 欄位 DESC;

mysql> select ID, Sex, Name from ID_Information order by ID DESC;

結果 
ID Sex Name
C223456789 王小花
 B123456789 王大明
 A123456789 王小明

 #3 兩欄排序 (前者優先)
mysql> select  欄位1, 欄位2, ... from 資料表名稱 order by 欄位 DESC, 欄位;

mysql> select ID, Sex, Name from ID_Information order by ID DESC, Name;

結果
 ID Sex Name
 C223456789 王小花
 B123456789 王大明
A123456789 王小明

(4)Group by: 用此來指定特定欄位,便將查詢區分成若干群組,然後對此群組進行運作。

(5)Having: 類似where,用來限制被讀取的條件,但需和Group by搭配,且不能取代Where,得先抓資料再篩選。

(6)Distinct: 如果某欄位中有值重覆率很高,可用此將它踢除。
Ex:

Score_Information
ID Score
 496510001  100
 496510002 80
 496510003 80

mysql> select distinct   Score from Score_Information ;

結果
Score
 100
80

(7)Limit: 在查詢中若只想要其中一部份,則可用此來限制資料被讀取的筆數。

4.  Insert語法
新增資料表中欄位的值
mysql> insert into  資料表名稱 values ('值1', '值2', ... );
或者 mysql> insert into  資料表名稱 set 欄位1 = '值', 欄位2 = '值';
Ex

ID_Information 
 ID Sex Name
 A123456789 王小明
 B123456789 王大明
 C223456789 王小花

 mysql> insert into ID_Information values ('D223456789', '女', '王大花');
或者 mysql> insert into ID_Information set ID = 'D223456789', Sex = '女', Name = '王大花';

結果
 ID Sex Name
 A123456789 王小明
 B123456789 王大明
 C223456789 王小花
 D223456789 王大花

5.  Delete語法
刪除資料表中欄位的值
mysql> delete from  資料表名稱 where 條件;
Ex:

ID_Information 
 ID Sex Name
A123456789 王小明
B123456789 王大明
C223456789 王小花

 mysql> delete from ID_Information where ID = 'C223456789';

結果
 ID Sex Name
A123456789 王小明
B123456789 王大明

6.  Update語法
修改資料表中欄位的值
mysql> update  資料表名稱 set 欄位1 = '值', 欄位2 = '值' where 條件;

Score_Infromation
ID Score
496510001 100
496510002 80
496510003 80

 mysql> update Score_Information set  Score = '100' where ID = '496510003';

結果 
ID Score
496510001 100
496510002 80
496510003 100

7.  全文檢索語法
在創建資料表時或之後用alter table加入fulltext (欄位1, 欄位2, ...)

 

三、 MySQL

MySQL的官方發音為My Ess Que EII,最初是由瑞典顧問公司Tcx在1994年所建立的,是一個穩定且可高度存取的關聯式資料庫管理系統relational database management system,其縮寫為RDBMS。

MySQL 基於多用戶,所以採用多線程 RDBMS 伺服器來處理修改多筆SQL資料。多線程能力使得 MySQL database 可以同時執行多個任務,還能允許伺服器有效回應客戶端的要求。另外MySQL 能被應用在Windows,Mac OS X,Linux及 UNIX作業系統上。

MySQL由於可靠性高、低成本和良好的性能,已經成為最流行的開源資料庫,被廣泛地應用在Internet上的中小型網站中。

 

四、 SQL優缺點&指令簡介

1.  SQL優缺點       
優點:
1. 可同時處理多個紀錄record (tuple值組)

2. 不需要前置編譯器 (Precompiler)

3. 可以直接處理記錄內的屬性,而不必重新定義變數

缺點:
1. 不支援圖形化的查詢

2. 對物件導向的支援尚未標準化SQL指令包含:
  (1) 資料定義語言 (Data Definition Language; DDL)

  (2) 資料處理語言 (Data Manipulation Language; DML)

  (3) 資料查詢語言 (Data Query Language; DQL)

  (4) 資料控制語言 (Data Control Language; DCL)

  (5) 資料管理語言 (Data Administration Commands)

  (6) 交易控制指令 (Transactional control commands)

2.  SQL指令簡介
1. DDL:允許資料庫使用者建立與重建資料庫物件,如建立或更新刪除表格。
DDL指令 :
  (1) CREATE TABLE (建立表格,屬於概念層指令)

  (2) ALTER TABLE (修改表格,例如:增掩表格屬性)

  (3) DROP TABLE (刪除表格)

  (4) CREATE INDEX (建立索引表)

  (5) ALTER INDEX

  (6) DROP INDEX

  (7) CREATE VIEW (產生景觀,屬於外部綱目指令)

  (8) ALTER VIEW

  (9) DROP VIEW

2. DML:用來插入、更新及刪除關聯式資料庫的資料,DML有三個主要指令:insert、 delete、update。 

3. DQL:允許資料庫使用者查詢資料庫中相關的資料。DQL只有一個select指令,這裡的select指令與關聯式代數的「選擇」指令意義並不同。

4. DCL:DCL控制使用者對資料庫內容的存取權限。主要指令有:REVOKE刪除權限、ALTER PASSWORD、GRANT授與權限、SYNONYM。 

5. DAC:允許使用者對使用中的資料庫產生稽核與分析,共有兩種主要指令:START AUDIT、STOP AUDIT。

6. TCC:用來管理資料的交易。
以下為主要指令: 
  (1) COMMIT:確認資料庫的交易。交易一旦確認就永久有效。

  (2) ROLLBACK:回復資料庫的交易。使交易回到未被確認狀態。

  (3) SAVEPOINT:設立群組內交易的指標。 

  (4) SET TRANSACTION:為每一次交易命名。
COMMIT、ROLLBACK、SAVEPOINT只能與DML的insert、delete、update指令一起使用。

 

五、 什麼是MYSQL

MySQL是一個真正的多用戶、多線程SQL資料庫服務器。SQL (結構化查詢語言)是世界上最流行的和標準化的資料庫語言。

MySQL是以一個客戶機/服務器結構 (Client/Server)的實現,它由一個服務器守護程序MYSQL與很多不同的客戶程序和庫所組成。

SQL是一種標準化的語言,它使得存儲、更新和存取信息更容易。例如,你能用SQL語言為一個網站檢索產品信息及存儲顧客信息,同時MySQL也足夠快和靈活以允許你存儲記錄文件和圖像。

MySQL 主要目標是快速、健壯和易用。最初是因為我們需要這樣一個SQL服務器,它能處理與任何可不昂貴硬件平台上提供資料庫的廠家在一個數量級上的大型資料庫,但速度更快,MySQL就開發出來。自1996年以來,我們一直都在使用MySQL,其環境有超 過 40 個資料庫,包含 10,000個表,其中500多個表超過7百萬行,這大約有100 GB的關鍵應用數據。

MySQL建立的基礎事業,已用在高要求的生產環境多年的一套實用歷程。儘管MySQL仍在開發中,但它已經提供一個豐富和極其有用的功能集。

 

六、 MySQL的小缺點

MySQL 並非是免費,若是你開發的網頁系統並未開放原始碼,你可能需要購買商業授權,請參閱原廠網頁說明。

另外,有些人不建議使用MySQL的其中最大原因主要在於罕用字的支援,基本上在Windows下使用MyODBC (Open Database Connectivity)連線,實際上是不支援Unicode,所以日、韓、簡、繁、罕用等遠東語系不能共存,但是你的應用程式若只有透過MyODBC連線,則造成的編碼差異並不會明顯看出來,但是資料庫並非是設計給單一系統使用,若是連線端有用jdbc (Java Database Connectivity)的,MySQL在jdbc下才真正支援Unicode,就會造成一邊是亂碼,因此,MyODBC/jdbc 只能有一邊是正常的。

DB & SQL
LAMP (Linux, Apache, MySQL, PHP)為資訊類組人都要懂得幾個系統、軟體。
資料庫是一種Data的集合,但一堆Data的集合不一定是資料庫。
DBMS (Database Management System)-資料庫管理系統
DBA (Database Administrant)-資料庫管理者
現代最受歡迎的DBMS為 relational database system
SQL為relational database system的一種標準語言。

資料庫儲存的方式是以table的方式存在。
primary key  (或稱prime key)為一個資料表獨一無二的,不能重複。
primary key為唯一的,如果出現多次,就會出現錯誤。
也可以用很多column集合為一個prime key。
foreign key是個column,在別的table為primary key,但是在此table為次要key。foreign key的好處為保持DB的完整性。

foreign key容許我們把不同table的Data集合起來,此動作稱為”join”。
foreign key 在其他表格一定要具有primary key,不然會出錯。

ER diagram (Entity-Relationship)
ER diagram 
是用來設計DB的工具。
表示一對多的Relationship是用一個EntitySet來表示。
表示多對多的Relationship是用兩個
EntitySet來表示。

 

七、 SQL的安全問題

其實MySQL本身相當安全,會造成資料外流或篡改主要來自於程式設計者設計不當的程式,因為程式的寫法可能導致MySQL資料庫被撈出一些已經限制讀取的資料。所以程式的寫法與寫程式的功力就相對的非常重要!!

1.  被攻擊:
當表格中的欄位過多時,許多設計人員都會習慣用字串的方式建立SQL指令,而且又使用系統管理員級的權限連到資料庫,因此讓有心人士有機會利用SQL的方式進行攻擊,像是在指令中加入部份刺探性 (例如:SELECT * FROM)或破壞性的指令 (例如:DROP 或是 DELETE * FROM),讓資料庫中的資料被破壞或竄改,導致資料庫癱瘓等後果,此種攻擊手法稱為SQL Injection。

2.  發生原因:
1.在應用程式中使用字串聯結方式組合SQL指令。

2. 在應用程式連結資料庫時使用權限過大的帳戶 (例如:內建最高權限的系統管理員帳戶連接Microsoft SQL Server資料庫)。

3. 在資料庫中開放了不必要但權限過大的功能。

4. 過於信任使用者所輸入的資料,未限制輸入的字元數和未對使用者輸入的資料做指令的檢查。

因此,如果在組合SQL的命令字串時,未針對單引號字元作取代處理的話,將導致該字元變數在填入命令字串時,被惡意竄改原本的SQL語法的作用。目前實務上較有效的防禦方法,就是全面改用參數化查詢,或是檢查輸入資料,過濾掉可能的危險指令或資料來防範。

 

 

 

---f20 web fundamental --

編輯者 : 林采昕 408262143 / 張字青 408262349 / 陳嬿婷 408262416 / 許瀚丰 408262208/ 龔子昀 408261060

iThome

 

雲端大數據/資料(Big data)

知識金字塔(資訊金字塔)

  • 智慧(intelligence)->知識(knowledge)->資訊(information)->數據/資料(data)->訊號(signal)
    • 資料庫,資料結構都在此之下
    • 儲存(EX. 資料庫), 處理(CPU處理)
    • 4V of Big Data
    • 資料預處理 (Preprocessing)

資料儲存

  • 硬體
    • Memory(1st Storage)
    • Disk (2nd Storage)
    • 隨身碟、CD (3rd Storage)
  • 儲存模式
    • 檔案 (OS)
    • 資料庫

NoSQL

  • 關聯型資料庫(Relational Database Menagement System, RDBMS)
    主要用於規模小而讀寫頻繁,或者批次很大、比較少存取的應用,使用 SQL 語法去抓資料、新增、刪除、查詢、修改,來做表格式的處理

    • SQL(Structured Query Language 結構化查詢語言)
    • NoSQL 強調 Key/Name-Value Stores 和文件資料庫
      (類似cookie或是JSON裡面的儲存形式)
      • 巨量文件建立索引
      • 高流量網站的網頁服務
      • 傳送串流媒體
      • 對處理網路上面大量的網站網頁,變較有效率,效能比較好
  • 資料庫管理系統 (DBMS) 在寫入或更新資料的過程中為保證交易(transaction)正確可靠信必須具備 ACID 四個特性

    1. 原子/不可分割性(Atomicity)最困難!
    2. 一致性(Consistency)
    3. 格離/獨立性(Isolation)
    4. 持久性 (Durability)
    • 一般NoSQL的結構通常提供弱一致性(weak consistency)的保證,因為資料量太大了,有些NoSQL資料庫,利用中間層(middleware)
      → 在某些情況下保證ACID
    • Goodle基於過濾器系統的 BigTable和滑鐵盧大學開發的 Hbase
    • 分散式儲存

Firebase(最有名的整合後端服務平台)

  • FCM(Firebase Cloud Messaging)
    針對Android、iOS及網路
    應用程式的訊息與通知的跨平台解決方案
  • 免費的數據分析工具、雲端訊息推播、
    通知系統、當機報告、遠端配置及動態連結
  • 後端服務平台(Backend as a Services, BaaS)

資料類別

  • 關聯資料
    • Tables/Transaction/Legacy Data
  • 文字資料
    • Text on Web
  • 半結構化資料(Semi-structured Data)
    有時候是儲存 有時候是傳輸時候用
    • XML
  • 圖形結構資料 Graph Data
    • Social Network, Semantic Web(RDF)
  • 多媒體串流資料 Streaming Data
    • can only scan the data once

如何才算"大"數據?

  • 隨著時間越來越大(e.g. Bill Gates : 640K是足夠所有人用)
  • 樹量只是分析參數之一?
  • 不同時空環境,和不同應用的"大"不一樣
  • Domain Data + Open Data
  • 90% Of Today’s Data Created In Two Years

大數據特徵 – 4V

  • 處理大量資料 – 大量(Volume)
    • 會越來越大,隔一陣子就有新的單位產生(zetta)
    • 44x increase from 2009 2020
    • From 0.8 zettabytes to 35 zb
    • 指數成長
    • 在儲存跟處理能力、在硬體跟網路部分要一直配合成長
  • 快速反應 – 快速(Velocity)
    • 處理大數據要快
    • 資料產生何處理
      • 需不需要經過學習的過程
    • Online Data Analytice 線上即時處理
    • Late decisitions → missing opportunities
  • 收集與分析更多元的資料 – 多元(Variety)
    • 資料來源、形式很多 -> Data warehousing
    • Data volume, various formats, types, and structures
    • Text, numberical, images, audio, video, sequences, social media data, multi-dim arrays, time series
    • A single application can be generating / collecting many types of data
  • 可信性 – 真實性(Veracity)
    • 資料必須要是真的

資料處理與分析(演算法)

  • 傳統
    • CRUD (Create/Read/Update/Delete)
  • 統計
  • 多資料庫、外部資料、不同來源的整合: 資料倉儲 (Data Warehousing)
  • 分析之前要先預處理

資料預處理 (Data Preprocessing)

  • Data in the real world is dirty 髒資料
    • incomplete(不完整) : missing attribute values, lack of certain sttributes of interest, or containing only aggregate data 例:必填欄位沒寫
    • inconsistent(不一致): ocntaining discrepancies in codes or names 例: 年齡跟出生年月日不相符
    • noisy(有雜質) :containing errors or outliers 資料有誤或超過合理範圍的資料 例: 年齡等於300、日期13月0號

資料預處理的主要工作

  • Data cleaning 清理
    • missing values
    • smooth noisy data
    • identify or remove outliers and noisy data
    • resolve inconsistencies
  • Data integration 整合
    • integration of multiple databases, or files
  • Data transformation 轉換
    • Normalization(正規化)
      • 將參數調整
        把全部參數轉換到0~1的範圍
    • Aggregation(聚合)
  • Data reduction 減量
    • Obtains reduced representation in volume but produces the same or similar analytical results
      把資料切割
  • Data discretization 數位化
    • for numerical data-from continuous to discrete(離散)
      把連續的資料、非數值資料,變成數位化的離散資料

Web Services

  • 針對後端的複雜狀況解決方案

    • 不同程式語言,不同平台,不同作業系統
  • WEB技術適用於後端和後端間的溝通

    • 使用HTTP協定,可穿越防火牆
    • 通訊內容為XML
  • WEB services tech

    • 若鴻海富士康接到 Apple 的訂單
    • 代工廠必須提供客戶訂單進度
    • 透過程式系統聯絡
    • Apple → Service Requestor
    • 鴻海 → Service Provider
    • Apple 程式可能和鴻海內部系統不相同
      → 透過 HTTP 溝通
    • 鴻海僅提供訂單相關資訊給Apple

    用此標準描述資料的格式提供給Service Requestor(標準格式)

Relation database

  • store in tables(row & column)
  • rule of entity integrity
  • primary key(主鍵) must be unique
  • rule of referential integrity
    • foreign key
  • joint data
  •  

資料庫的正規化分析

  • 關聯式資料庫不管設計好壞,都可以儲存資料,但是存取效率上可能會有很大的差別
  • 想提升關聯繫資料庫的效率,可以利用正規化(Normalization)的方式來協助我的修改資料表的結構
  • 正規化就是要讓資料庫中重複的資料減到最少

何謂正規化

  • 資料庫的正規化共可分為第一階正規化 (1st Normal Form, 1NF), 第二階正規化 (2NF)、第三階正規化 (3NF), BCNF (Boyce-Codd Normal Form), 第四階正規化 (4NF), 第五階正規化(5NF) 等多個階段
  • 對於一般資料庫設計來說, 通常只要執行到第三正規化即可, 其他更高階的正規化只有在特殊的情況下才用得到

結構化查詢語言 SQL

  • SQL (Structured Query Language, 唸法是 “S-Q-L”, 結構化查詢語言)
  • 它是目前關聯式資料庫管理系統所使用的查詢語言, 使用者可以利用 SQL 語法直接對關聯式資料庫進行存取與管理的操作。
  • SQL 的基本語法是由一些簡單的句子構成, 簡單易學
  • See the documentation for your database system to determine whether SQL is case sensitive on your system and to determine the syntax for SQL keywords (i.e., should they be all uppercase letters, all lowercase letters or some combination of the two?).