SQL基本文法&SQLite

標籤：

Databases

資料庫是一個資料存放區區用於儲存、查詢和處理資料。資料庫儲存我們需要的資料並且開放一個和資料互動的介面。大多數科技公司使用資料庫來組織數。資料庫系統包括資料庫管理軟體與管理控制、安全和存取控制，語言與資料庫介面這些內容。
首先，我們將關注SQL語言一個結構化查詢語言 (SQL)。它是用來查詢、更新和修改資料庫中的資料。

SQL

SQL是最常見的一種資料庫語言，在任何資料專業工具箱中都是一個重要的工具。雖然SQL是一種語言，但它完全不同於像Python或r 這樣的語言， SQL是專門為了與資料庫進行互動而建立的一種語言，不會像傳統程式設計語言一樣有很多的功能。由於SQL是一種聲明式語言,使用者需要關注的是表達他想要的東西而電腦則集中在如何執行計算。

Tables, Rows, & Columns

一個資料庫就是tables的集合，一個表格包含一行行的資料，以及列資訊。table和Pandas中的DataFrame很像。
本文的資料集是**recent-grads.csv**2010到2012的畢業生資訊，它的屬性如下：

• Rank - Rank by median earnings.
• Major_code - Major code.
• Major - Major description.
• Major_category - Category of major.
• Total - Total number of people with major.
• Sample_size - Sample size (unweighted) of full-time.
• Men - Male graduates.
• Women - Female graduates.
• ShareWomen - Women as share of total.
• Employed - Number employed.

Querying

• 寫一個查詢語言是與資料庫進行的基本互動，比如：

SELECT [columnA, columnB, ...]FROM tableName;SELECT Rank,MajorFROM recent_grads;

• 其中分號 ; 是不能省略的，它表明這個查詢語句的結尾。這可以允許我們在一行寫很多個查詢語句，只要不寫 ; 就沒有結束。

SQLite

SQLite是一個輕量層級的資料庫，用來探索和學習SQL是再好不過了。

SELECT Rank,Major FROM recent_grads;‘‘‘[["Rank", "Major"], [1, "PETROLEUM ENGINEERING"], [2, "MINING AND MINERAL ENGINEERING"], [3, "METALLURGICAL ENGINEERING"], [4, "NAVAL ARCHITECTURE AND MARINE ENGINEERING"], . . .‘‘‘

Specifying Column Order

• SQL可以指定select傳回值中列的順序，下面將Major放在前面:

SELECT Major,Rank FROM recent_grads;‘‘‘[["Major", "Rank"], ["PETROLEUM ENGINEERING", 1], ["MINING AND MINERAL ENGINEERING", 2], ["METALLURGICAL ENGINEERING", 3], . . .‘‘‘

Practice: Select

SELECT Rank,Major_code,Major,Major_category,Total FROM recent_grads;‘‘‘[["Rank", "Major_code", "Major", "Major_category", "Total"], [1, 2419, "PETROLEUM ENGINEERING", "Engineering", 2339], [2, 2416, "MINING AND MINERAL ENGINEERING", "Engineering", 756], [3, 2415, "METALLURGICAL ENGINEERING", "Engineering", 856], [4, 2417, "NAVAL ARCHITECTURE AND MARINE ENGINEERING", "Engineering", 1258],. . .‘‘‘

Where

我們用select返回的是整個列的資料，倘若我們想要返回某列滿足一些條件的資料，則需要用到where語句，where語句是用來過濾的。

比如我們想擷取哪些Major的女生要大於男生，使用where需要三個東西：

• The column we want the database to filter on: ShareWomen
• A comparison operator to specify how we want a value in a column to be compared: >
• The comparison value we want the database to compare each value to: 0.5

SELECT Major,ShareWomenFROM recent_gradsWHERE ShareWomen > 0.5;‘‘‘[["Major", "ShareWomen"], ["ACTUARIAL SCIENCE", 0.535714286], ["COMPUTER SCIENCE", 0.578766338],["ENVIRONMENTAL ENGINEERING", 0.558548009],["NURSING", 0.896018988], . . .‘‘‘

where語句中可以使用的比較符：< <= > >= = !=

Practice: Where

SELECT Major,Employed FROM recent_grads WHERE Employed > 10000;‘‘‘[["Major", "Employed"], ["CHEMICAL ENGINEERING", 25694], ["MECHANICAL ENGINEERING", 76442], ["ELECTRICAL ENGINEERING", 61928], . . .‘‘‘

Limit

前面查詢語句返回的資料都很多，有的時候這很麻煩，我們需要做一個限制。SQL中有一個LIMIT 語句可以實現這個功能，LIMIT 語句放在查詢的最後。

• 下面這條語句將返回結果的前五條：

SELECT Major FROM recent_grads LIMIT 5;‘‘‘[["PETROLEUM ENGINEERING"], ["MINING AND MINERAL ENGINEERING"], ["METALLURGICAL ENGINEERING"], ["NAVAL ARCHITECTURE AND MARINE ENGINEERING"], ["CHEMICAL ENGINEERING"]]‘‘‘

• 這個例子將返回Employed>1000的前10條：

SELECT Major FROM recent_grads WHERE Employed>10000 LIMIT 10;‘‘‘[["Major"], ["CHEMICAL ENGINEERING"], ["MECHANICAL ENGINEERING"], ["ELECTRICAL ENGINEERING"], ["COMPUTER ENGINEERING"], ["AEROSPACE ENGINEERING"], ["BIOMEDICAL ENGINEERING"], ["INDUSTRIAL AND MANUFACTURING ENGINEERING"], ["GENERAL ENGINEERING"], ["COMPUTER SCIENCE"], ["MANAGEMENT INFORMATION SYSTEMS AND STATISTICS"]]‘‘‘

Logical Operators

where語句中有6個比較操作，邏輯操作（Logical operators）最常用的是OR 以及ＡＮＤ，使用邏輯操作可以串連多個比較操作，來進行更細緻的過濾。

And Operator

• AND的文法如下：

SELECT [column1, column2,...] FROM [table1]WHERE [condition1] AND [condition2]

• 下面這個語句表達的是：從recent_grads 中挑選出ShareWomen>0.5**並且**Employed>10000的資料（Major,ShareWomen,Employed）的前10條

SELECT Major,ShareWomen,Employed FROM recent_grads WHERE ShareWomen>0.5 AND Employed>10000 LIMIT 10;

Or Operator

• OR操作的文法如下：

SELECT [column1, column2,...] FROM [table1]WHERE [condition1] OR [condition2]

• 下面這段代碼錶示的是：從recent_grads 中挑選出edian >= 10000 或者Unemployed <= 1000的資料（Major,Median,Unemployed）的前20條

SELECT Major,Median,Unemployed FROM recent_grads WHERE Median >= 10000 OR Unemployed <= 1000 LIMIT 20;

Grouping Operators

• 如果我們想獲得majors 是Engineering 且大部分是women或者unemployment rate小於5.1%,資料，查詢語句如下，可以發現SQL語言是不群分大小寫：

select Major, Major_category, ShareWomen, Unemployment_ratefrom recent_gradswhere (Major_category = ‘Engineering‘) and (ShareWomen > 0.5 or Unemployment_rate < 0.051);‘‘‘[["Major", "Major_category", "ShareWomen", "Unemployment_rate"], ["PETROLEUM ENGINEERING", "Engineering", 0.120564344, 0.018380527], ["METALLURGICAL ENGINEERING", "Engineering", 0.153037383, 0.024096386], ["NAVAL ARCHITECTURE AND MARINE ENGINEERING", "Engineering", 0.107313196, 0.050125313],‘‘‘

• 另一個需要注意的是使用括弧的問題，如果我們對於Major_category = ‘Engineering’這個句子不加括弧，系統會認為我們想表達的意思是下面這樣，那麼結果會出錯：

where (Major_category = ‘Engineering‘ and ShareWomen > 0.5) or (Unemployment_rate < 0.051) 

Practice Grouping Operators

select Major, Major_category, Employed, Unemployment_ratefrom recent_gradswhere (Major_category = ‘Business‘ or Major_category = ‘Arts‘ or Major_category = ‘Health‘) and (Employed > 20000 or Unemployment_rate < 0.051);

Order By

• select語句返回的結果從上往下的順序是按照資料庫中資料原有的相對位置，比如上面的資料都是按照Rank排列的。但是有時候我們想要擷取的結果按照我們的意願排列，這個時候我們可以使用Order By語句，Order By語句的文法如下：

SELECT [column1, column2,...] FROM [table1]WHERE [conditions]..ORDER BY column1 [ASC or DESC]

• SQL使用標準的按字母順序排序，預設是ESC，從小到大。

select Majorfrom recent_gradsorder by Major desclimit 10;‘‘‘[["Major"], ["ZOOLOGY"], ["VISUAL AND PERFORMING ARTS"], ["UNITED STATES HISTORY"], ["TREATMENT THERAPY PROFESSIONS"], ["TRANSPORTATION SCIENCES AND TECHNOLOGIES"], ["THEOLOGY AND RELIGIOUS VOCATIONS"], ["TEACHER EDUCATION: MULTIPLE LEVELS"], ["STUDIO ARTS"], ["STATISTICS AND DECISION SCIENCE"], ["SPECIAL NEEDS EDUCATION"]]‘‘‘

Order Using Multiple Columns

• SQL也可以使用多列進行排序，通常在進行人名排序是，先按Last Name排序，再按First Name排序，因為Last Name有很多相同的。

select [column1, column2..]from table_nameorder by column1 (asc or desc), column2 (asc or desc)

select Major_category, Median, Majorfrom recent_gradsorder by Major asc, Median desclimit 20;

SQL基本文法&SQLite