sqlite常用的命令-增刪改查

標籤：add   between   asc   匯入   fill   指令   開啟   視窗   src   

一、查看版本資訊：

#sqlite3 -version

 

二、sqlite3常用命令

1、目前的目錄下建立或開啟test.db資料庫檔案，並進入sqlite命令終端，以sqlite>首碼標識：

 

2、輸出協助資訊：

sqlite>.help

 

3、查看資料庫檔案資訊命令(注意命令前帶字元‘.‘)：

sqlite>.database

 

4、退出sqlite終端命令：

sqlite>.quit

或

sqlite>.exit

 

列出當前顯示格式的配置：
sqlite>.show

 

6、顯示資料庫結構：.schema

顯示表的結構：.schema    表名

其實就是一些 SQL 語句，他們描述了資料庫的結構，

 

7、匯出某個表的資料： .dump    表名

 

8、設定匯出目標： 

.output     檔案名稱  

或者

.output   stdout

先運行 .output cars.sql ，然後再運行 .dump 命令試試看？如果要回複成匯出到終端（標準輸出），則運行 .output stdout

 

10、設定分隔字元：.separator    分隔字元

我們可以首先運行 SELECT * FROM Cars； ，可以看到預設的分隔字元是 | 

運行.separator : 以後，再 SELECT * FROM Cars；，可以看到分隔字元已經變成 : 了

 

11、顯示標題列：.headers   on

 

12、設定顯示模式：.mode    模式

有好幾種顯示模式，預設的是 list 顯示模式，一般我們使用 column 顯示模式，還有其他幾種顯示模式可以 .help 看 mode 相關內容。看看下面的圖，和上面是不是顯示的不一樣了？

 

 

13、設定 NULL 值顯示成什麼樣子： .nullvalue     你想要的NULL值格式

預設情況下NULL值什麼也不顯示，你可以設定成你自己想要的樣子

 

14、設定檔 .sqliterc

如果我們每次進入命令列都要重新設定顯示格式，很麻煩，其實 .show 命令列出的所有設定項都可以儲存到一個 .sqliterc 檔案中，這樣每次進入命令列就自動化佈建好了。.sqlterc 檔案在 Linux 下儲存在使用者的 Home 目錄下，在 Windows 下可以儲存到任何目錄下，但是需要設定環境變數讓資料庫引擎能找到它，感興趣的可以看看協助。 

 

 

三、資料庫和表的相關命令

 

1、建立一個新的資料庫：sqlite3     檔案名稱

建立一個 test.db 資料庫檔案，開啟控制台視窗，命令如下：

sqlite>sqlite3 test.db

 

2、開啟一個已經存在的資料庫：sqlite3 已經存在的檔案名稱

建立一個新資料庫和開啟一個已經存在的資料庫命令是一模一樣的，如果檔案在目前的目錄下不存在，則建立；如果存在，則開啟。

 

3、建立資料表

create table table_name(field1 type1, field2 type1, ...);

table_name是要建立資料表名稱，fieldx是資料表內欄位名稱，typex則是欄位類型。
如：該語句建立一個記錄學生資訊的資料表。

sql的指令格式：所有sql指令都是以分號(;)結尾，兩個減號(--)則表示注釋。

 

4、添加資料記錄

insert into table_name(列field1, field2, ...) values(值val1, val2, ...);

valx為需要存入欄位的值。
例，往老師資訊表添加資料：

很簡單，建立了一個 Teachers 表並向其中添加了四條資料，設定了一些約束，其中有自動增加的主鍵、預設值等等。

 

5、修改資料

UPDATE 表 SET 列 = ‘新值‘ 【WHERE 條件陳述式】

UPDATE 語句用來更新表中的某個列，如果不設定條件，則所有記錄的這一列都被更新； 如果設定了條件，則合格記錄的這一列被更新， WHERE 子句被用來設定條件，如下例：

 

6、刪除資料 DELETE FROM 表 【WHERE 條件陳述式】

如果設定 WHERE 條件子句，則刪除合格資料記錄；如果沒有設定條件陳述式，則刪除所有記錄

 

 

7、匯入資料：.read     資料檔案

開啟記事本，並將下列 SQL 語句複製到記事本中，儲存為 test.sql ，在命令列環境中輸入

.read   test.sql

即將所有的資料匯入到 test.db 資料庫中。

 

BEGIN TRANSACTION;
CREATE TABLE Cars(Id integer PRIMARY KEY, Name text, Cost integer);
INSERT INTO Cars VALUES(1,‘Audi‘,52642);
INSERT INTO Cars VALUES(2,‘Mercedes‘,57127);
INSERT INTO Cars VALUES(3,‘Skoda‘,9000);
INSERT INTO Cars VALUES(4,‘Volvo‘,29000);
INSERT INTO Cars VALUES(5,‘Bentley‘,350000);
INSERT INTO Cars VALUES(6,‘Citroen‘,21000);
INSERT INTO Cars VALUES(7,‘Hummer‘,41400);
INSERT INTO Cars VALUES(8,‘Volkswagen‘,21600);
COMMIT;

BEGIN TRANSACTION;
CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0), 
                    Customer text);
INSERT INTO Orders(OrderPrice, Customer) VALUES(1200, "Williamson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(200, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(40, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(1640, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(100, "Robertson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(50, "Williamson");
INSERT INTO Orders(OrderPrice, Customer) VALUES(150, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(250, "Smith");
INSERT INTO Orders(OrderPrice, Customer) VALUES(840, "Brown");
INSERT INTO Orders(OrderPrice, Customer) VALUES(440, "Black");
INSERT INTO Orders(OrderPrice, Customer) VALUES(20, "Brown");
COMMIT;

8、查詢資料記錄

a查詢輸出所有資料記錄
select * from table_name;

 

b限制輸出資料記錄數量

若資料庫中的資料太多，全部返回可不行，可以限制返回的數量，還可以設定返回的起始位置
select * from table_name limit val;

 

c升序輸出資料記錄
select * from table_name order by field asc;

 

d降序輸出資料記錄
select * from table_name order by field desc;

 

e條件查詢
select * from table_name where expression;

 

IN （集合）
select * from table_name where field in (‘val1‘, ‘val2‘, ‘val3‘);

 

BETWEEN 值1 AND 值2
select * from table_name where field between val1 and val2;

f、查詢記錄數目

select count (*) from table_name;

 

g、區分列資料
select distinct field from table_name;
有一些欄位的值可能會重複出現，distinct去掉重複項，將列中各欄位值單個列出。

h、別名 SELECT 列 AS 別名，列 AS 別名 FROM

可以給返回資料集中的某些列起一個比較直觀的名字，比如把 Cost 改為"Price Of Car"

 

I、條件查詢 SELECT 列 FROM 表 【WHERE 條件陳述式】

一般的條件陳述式都是大於、小於、等於之類的，這裡有幾個特別的條件陳述式

LIKE

------------------------------

LIKE 用萬用字元匹配字串

底線 _ 匹配一個字串

百分比符號 % 匹配多個字串

LIKE 匹配字串時不區分大小寫

 

GLOB

 

J 區分 DISTINCT 列

有一些欄位的值可能會出現重複，比如訂單表中，一個客戶可能會有好幾份訂單，因此客戶的名字會重複出現。

到底有哪些客戶下了訂單呢？下面的語句將客戶名字區分出來。

 

k分組 GROUP BY 列

分組和前面的區分有一點類似。區分僅僅是為了去掉重複項，而分組是為了對各類不同項進行統計計算。

比如上面的例子，我們區分出 5 個客戶，這 5 個客戶一共下了 11 個訂單，說明很多客戶都下了不止一個訂單。

下面的語句統計每個客戶在訂單上總共花費了多少錢。

這裡 Sum 是 SQLite 內建的統計函數，在這個例子中用來求每個顧客的訂單價格的和。

統計結果也可以設定返回條件，但是不能用 WHERE 子句，而是用 HAVING 子句，如下例，返回訂單總額大於 1000 的顧客。

 

9、建立索引
當說資料表存在大量記錄，索引有助於加快尋找資料錶速度。
create index index_name on table_name(field);
例，針對學生表stu_no欄位，建立一個索引：
create index student_index on student_table(stu_no);
建立完成後，sqlite3在對該欄位查詢時，會自動使用該索引。
 
10、刪除資料表或索引
drop table table_name;
drop index index_name;

 

四、sqlite3儲存資料的類型
NULL：標識一個NULL值
INTERGER：整數類型
REAL：浮點數
TEXT：字串
BLOB：位元

 

五、sqlite3儲存資料的約束條件
Sqlite常用約束條件如下：
PRIMARY KEY - 主鍵：
1）主鍵的值必須唯一，用於標識每一條記錄，如學生的學號
2）主鍵同時也是一個索引，通過主鍵尋找記錄速度較快
3）主鍵如果是整數類型，該列的值可以自動成長
NOT NULL - 非空：
約束列記錄不可為空，否則報錯
UNIQUE - 唯一：
除主鍵外，約束其他列的資料的值唯一
CHECK - 條件檢查：
約束該列的值必須符合條件才可存入
DEFAULT - 預設值：
列資料中的值基本都是一樣的，這樣的欄位列可設為預設值

 

CREATE TABLE Orders(Id integer PRIMARY KEY, OrderPrice integer CHECK(OrderPrice>0), 
                    Customer text);

CREATE TABLE Friends(Id integer PRIMARY KEY, Name text UNIQUE NOT NULL, 
                     Sex text CHECK(Sex IN (‘M‘, ‘F‘)));

 

CREATE TABLE IF NOT EXISTS Reservations(Id integer PRIMARY KEY, 
                                        CustomerId integer, Day text);
INSERT INTO Reservations(CustomerId, Day) VALUES(1, ‘2009-22-11‘);

CREATE TABLE Books(Id integer PRIMARY KEY, Title text, Author text, 
                   Isbn text default ‘not available‘);

 

 

 

怎麼在已經建立的表中插入一列

 

 

ALTER  TABLE   table-name  ADD COLUMN  column-name column-type

sqlite常用的命令-增刪改查