SQLite簡易入門

標籤：

本文內容來源：https://www.dataquest.io/mission/129/introduction-to-sql

本文所用資料來源：https://github.com/fivethirtyeight/data/tree/master/college-names

摘要：主要簡介了SQLite的一些簡易操作（增刪改查）

未經處理資料展示（資料庫facts儲存了如下的這張表，主要欄位描述：

code - 國家代碼，name - 國家名稱， area - 國土面積， created_at - 建立這個表的時間）

  操作資料   查詢

#資料展示SELECT [columnA, columnB, ...]FROM tableName;#如： 查詢資料庫中code和name兩列的資料SELECT code, nameFROM facts;#條件查詢，使用where來界定查詢條件，如，查詢人口大於1億的國家代碼和名稱SELECT code, nameFROM factsWHERE population> 100000000;

在where語句中可以使用的比較符號：

• 小於: <
• 小於或等於: <=
• 大於: >
• 大於或等於: >=
• 等於: =
• 不等於: !=

sqlite中可以直接對日期資料進行比較，譬如，查詢在2015-11-01 14:00之前建立的資料

SELECT * FROM facts WHERE created_at < "2015-11-01 14:00"

注意：在雙引號中就是日期格式的資料，必須按照yyyy-mm-dd HH:MM:SS的格式

 

限定返回的資料數量

# 有時候只想查看返回資料的前幾條，使用limit， 如：查詢前五條資料

select * from facts limit 5;

 

邏輯運算子

可以使用邏輯運算子and和or來組合多重查詢條件

SELECT [column1, column2,...] FROM [table1]WHERE [condition1] AND [condition2]#如，人口大於1億且國土面積大於100000的國家名稱select name from facts where population> 100000000 and area_land> 100000 limit 10;

 

組合多個查詢條件

#如：查詢在人口過億的國家中，出生率大於20或者死亡率小於10的資料select namefrom factswhere (population >100000000) and (birth_rate  > 20 or death_rate < 10);

 

排序

通過order by語句來指定排序的列，asc是升序，desc是降序

SELECT [column1, column2,...] FROM [table1]WHERE [conditions]..ORDER BY column1 [ASC or DESC]# 按國家名稱降序排序select name from facts order by name desc limit 10;#多列排序，譬如，在一個儲存人名的表中，首先對姓排序，然後在同一個姓中再對名排序select [column1, column2..]from table_nameorder by column1 (asc or desc), column2 (asc or desc)

 

查詢一張表的資料類型，sqlite中主要有以下的資料類型：

• INTEGER - 類似於python中的整數型別
• REAL - 類似於python中的浮點數類型
• FLOAT - 類似於python中的浮點數類型
• TEXT - 類似於python中的字串類型
• VARCHAR(255) - 類似於python中的字串類型

之所以同一個類型有不同的名字，是因為sqlite用來相容其他資料庫所用的，查詢一張表的資料類型，使用pragma語句

PRAGMA table_info(tableName);#結果會返回一個列表（截取）：[[0, "id", "INTEGER", 1, null, 1], [1, "code", "varchar(255)", 1, null, 0], [2, "name", "varchar(255)", 1, null, 0]]

該列表的內容內容如下，pk = 1表明該欄位是一個主鍵：

 插入

往資料庫中插入資料，使用insert語句，注意日期資料必須符合格式：yyyy-mm-dd HH:MM:SS

INSERT INTO tableNameVALUES (value1, value2, ...);#例子：INSERT INTO factsVALUES (262, "dq", "DataquestLand", 60000, 40000, 20000, 500000, 100, 50, 10, 20, "2016-02-25 12:00:00", "2016-02-25 12:00:00");

如果要插入的資料中有空值，就用NULL代替即可

 更新

UPDATE tableNameSET column1=value1, column2=value2, ...WHERE column1=value3, column2=value4, ...# 例如，將United States改為DataquestLandupdate factsset name=‘DataquestLand‘where name=‘United States‘;

 

刪除

DELETE FROM tableNameWHERE column1=value1, column2=value2, ...;# 例如，將名為Canada的資料全部刪除delete from factswhere name=‘Canada‘;

 

使用python和sqlite互動

sqlite資料庫並不需要開啟一個單獨的伺服器處理序，並且把所有的資料都儲存在硬碟上的一個檔案中，從python2.5開始，sqlite就整合在了python語言中，所以不需要額外安裝庫來操作sqlite

 

串連資料庫

import sqlite3conn = sqlite3.connect(‘countries.db’) #使用connect()方法來串連資料庫，該參數指定資料庫的名稱

 

python是通過cursor對象來操作sql語句的，cursor對象可以執行如下功能：

• 查詢資料庫
• 解析從資料庫返回的結果
• 將資料庫的返回結果轉化為python對象
• 將cursor的值儲存為本地變數

注意：cursor對象把返回的結果儲存到一個元組的列表中，如下

import sqlite3conn = sqlite3.connect("countries.db")       # 串連資料庫cursor = conn.cursor()                # 返回一個cursor對象query = ‘select name from facts;‘  # 將查詢語句儲存為字串格式cursor.execute(query)                # 執行查詢語句names = cursor.fetchall()              # 儲存全部的返回結果print(names[0:2])                   # 顯示前兩個資料#結果：一個列表，列表中的每個元素是一個元組[(‘Afghanistan,), (‘Albania‘,)]

有時候可能需要逐條返回查詢結果，就是用fetchone(),需要一次返回n條結果，就是用fetchmany(n)。因為在cursor對象中會儲存一個內部的計數器，在每次得到返回結果時都會增加計數器

# 上面的查詢語句等價於這樣import sqlite3conn = sqlite3.connect("countries.db")       # 串連資料庫cursor = conn.cursor()                # 返回一個cursor對象query = ‘select name from facts;‘  # 將查詢語句儲存為字串格式cursor.execute(query)                # 執行查詢語句names = cursor.fetchmany(2)              # 儲存前2條結果print(names)

在某個進程串連著一個sqlite資料庫的時候，其他進程是不能訪問該資料庫的，所以在操作完資料庫之後需要關閉串連，注意，在關閉串連的時候，之前對資料庫的更改會自動儲存並生效。

conn = sqlite3.connect("countries.db")conn.close()

 

 

動作表結構

上面的內容都是對錶中的資料進行操作，現在要對錶的結構進行操作，譬如建立新表，增加一列等

增加列

# 往一張表中增加一個新列ALTER TABLE tableNAmeADD columnName dataType;# 例如，往該表中增加一列名為leader，且資料格式為textalter table facts add leader text;

 

建立新表

CREATE TABLE dbName.tableName(   column1 dataType1 PRIMARY KEY,   column2 dataType2,   column3 dataType3,   ...);# 例如，在資料庫factbook中建立一個新表用來儲存國家領導人的資訊CREATE TABLE factbook.leaders(   id integer PRIMARY KEY,  # 指定主鍵   name text,   country text);

建立具有外鍵的表

CREATE TABLE factbook.leaders(   id integer PRIMARY KEY,   name text,   country integer,   worth float,   FOREIGN KEY(country) REFERENCES facts(id)  # 該外鍵指定要串連那個表);

 多表聯集查詢

SELECT [column1, column2, ...] from tableName1INNER JOIN tableName2  # 指定另一張表ON tableName1.column3 == tableName2.column4;# 譬如，尋找兩張表中編號一致的資料SELECT * from landmarksINNER JOIN factsON landmarks.country == facts.id;# 除了INNER JOIN還有LEFT OUTER JOIN（左外串連）

在上面的例子中，landmarks是在左邊的表，facts是在右邊的表，在執行JOIN的時候，會變成這樣

紅線左邊的就是landmarks表的值，右邊就是facts表的值，因為id和name兩個欄位都重複了，所以右邊的表會加上尾碼1

• INNER JOIN - 只展示符合查詢條件的值，在中就是左邊的country值等於右邊id_1的值的那些行
• LEFT OUTER JOIN - 左表中有不匹配的資料時，在合并的表中的其他欄位就顯示為NULL

關於JOIN，更多資料參考：http://www.yiibai.com/sqlite/sqlite_using_joins.html

SQLite簡易入門