資料庫之--- SQLite 語句

標籤：

一. 基礎創表操作:

• 1. 建立表

CREATE TABLE IF NOT EXISTS t_dog(name text, age bolb, weight real);

• 2. 插入記錄

INSERT INTO t_shop (name, left_count) VALUES (‘扇子‘, 100);

 

• 3.修改記錄

UPDATE t_shop SET price = 6288, left_count = 0;

• 4. 刪除表中資料

DELETE FROM t_shop;

• 5. 刪除表

DROP TABLE t_dog;

二. 條件陳述式

 

• 更改記錄

UPDATE t_shop SET left_count = 0 WHERE price < 1000;

• 刪除記錄

DELETE FROM t_shop WHERE left_count < 70 AND price < 500;

DELETE FROM t_shop WHERE left_count < 70 OR price < 500;

• 查詢資料

SELECT name, price, left_count FROM t_shop;

SELECT * FROM t_shop WHERE left_count > 80;

1. 別名: SELECT name shop_name, price AS shop_price, left_count 庫存 FROM t_shop s;
2. count: SELECT count(*) 剩餘數量 FROM t_shop WHERE left_count > 50;
3. 降序查詢:SELECT *FROM t_shop ORDER BY left_count DESC;
4. 預設ASC:SELECT *FROM t_shop ORDER BY left_count DESC, price ASC;
5. limit:SELECT *FROM t_shop LIMIT 2, 4;
   SELECT *FROM t_shop ORDER BY price DESC LIMIT 1, 10; ---- 取價格最高的第2頁, 10條記錄;
   
6. 約束: CREATE TABLE IF NOT EXISTS t_student (name text NOTNULL, age integer);
7. CREATE TABLE IF NOT EXISTS t_student (name text NOT NULL UNIQUE, age integer NOT NULL);
8. CREATE TABLE IF NOT EXISTS t_student (name text NOT NULL UNIQUE, age integer DEFAULT 1 NOT NULL);
9. 主鍵約束: primary key

CREATE TABLE IF NOT EXISTS t_class (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL UNIQUE);
INSERT INTO t_class(name) VALUES (‘ios‘);
INSERT INTO t_class(name) VALUES (‘andriod‘);

 

資料庫之--- SQLite 語句