iOS系統(tǒng)集成了一個輕量級數據庫:SQLite,SQLite支持絕大部分SQL語法斗埂,也可以使用SQL語句操作數據庫中的數據符糊。SQLite內部支持NULL,INTEGER呛凶,REAL(浮點數)男娄,TEXT(文本)和BLOB(大二進制對象)這5種數據類型鞠柄。下面以SQL語法為例介紹操作數據庫的語法,在實際中可能需要相應改動以適應SQLite萄金。
以下內容主要來自“Learning SQL忘嫉,Second Edition”。
建表
新建一種存放個人信息的表描述個人的信息類型:
姓名围橡,性別暖混,出生日期,地址翁授,最喜愛的食物
分析一般來說讓一個人有多個喜歡的食物拣播,因此這個食物需要單獨建議一張表存儲person的foods。這里包含一對多的關系收擦。在表的關系中還有多對多的關系贮配,不過在實際中就應用程序而言內置的存儲多對多這種負責的關系比較少見。
列 | 類型 | 允許值 |
---|---|---|
person_id | smallint(unsigned) | |
f_name | varchar(20) | |
l_name | varchar(20) | |
gender | char(1) | M塞赂,F |
birth_date | date | |
street | varchat(30) | |
city | varchat(20) | |
state | varchat(20) | |
country | varchat(20) | |
postal_code | varchat(20) |
列 | 類型 | 允許值 |
---|---|---|
person_id | smallint(unsigned) | |
food |
CREATE TABLE person (person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M','F'),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);
CONSTRAINT 建立主鍵約束
gender ENUM('M','F') 表示gender只接受M和F兩種值泪勒。
當然還可以設置默認值,例如
`fname` varchar(20) NOT NULL DEFAULT '' COMMENT '名字'
表示fname字段不能為NULL 宴猾,默認是為空字符串圆存,注釋這個表示名字。
CREATE TABLE favorite_food
(person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id,food),
CONSTRAINT fk_fav_food_person_id FOREIGN KEY(person_id)
REFERENCES person (person_id) ON DELETE CASCADE ON UPDATE CASCADE) ;
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id,food), 表示person_id和food是聯合主鍵
表示這個表中同一個person_id仇哆,不能有多個相同的food
CONSTRAINT fk_fav_food_person_id FOREIGN KEY(person_id)
REFERENCES person (person_id)) ON DELETE CASCADE ON UPDATE CASCADE;
表示給favorite_food建一個外鍵 與person表中的person_id相關聯沦辙。同時當父表中(person)中相關記錄刪除時,先檢查該記錄是否有對應外鍵讹剔,如果有則也刪除外鍵在子表(即包含外鍵的表)中的記錄油讯。
插入
在插入數據之間,需要改動一下person表延欠,一般情況下陌兑,插入一條記錄的時候,大多數情況下由捎,不會所有字段都賦值兔综,當主鍵相同時插入數據會出錯,所以一般在插入記錄的時候不會對主鍵賦值狞玛,而是設置主鍵自增.
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
插入語法
INSERT INTO 表明 (字段名1软驰,字段名2,...) VALUES (值1为居,值2碌宴,...);
//插入一條person信息
INSERT INTO person (person_id,fname,lname,gender,birth_date)
VALUES (null,'Willian','Turner','M','1972-05-27');
//為剛剛插入的person添加一條記錄 person最喜愛的food
INSERT INTO favorite_food (person_id,food) VALUES
(1,'pizza');
注意如果 在插入food記錄之前沒有相應的person記錄,會報錯
INSERT INTO favorite_food (person_id,food) VALUES
(3,'pizza');此語句會報錯蒙畴。
刪除
刪除語法
delete from where 條件
DELETE FROM person WHERE person_id = 1;
刪除person_id = 1贰镣;同時相應的food表中的記錄也會被刪除呜象。如果不是N DELETE CASCADE ON UPDATE CASCADE 而是其他選項則可能不會刪除,甚至可能報錯碑隆,表示刪除不了父表中的信息恭陡。
更新
更新語法
update 表明 set 字段名1 = '值1',字段名2 = '值2' where 條件
UPDATE person SET street = '1225 Tremont St.',
city = 'Boston',
state = 'USA',
postal_code = '02138'
WHERE person_id =1;
表示給person_id=1的記錄更新street上煤,city休玩,state,postal_code字段的值劫狠。
查詢
為了更好的演示數據庫操作拴疤,需要復雜一點的數據庫多表來完成。下面以書中Bank數據模型為例演示独泞。
show tables 顯示數據中的表
表名 | 定義 |
---|---|
account | 為特定顧客開放的特定產品 |
branch | 開展銀行交易業(yè)務的場所 |
busniess | 公司顧客(customer)的子類型 |
customer | 與銀行有業(yè)務來往的個人或公司 |
department | 執(zhí)行特定銀行職能的雇員分組 |
employee | 銀行的工作人員 |
individual | 個人顧客 |
officer | 允許為公司顧客發(fā)起商務交易的人 |
product | 向顧客提供的銀行服務 |
product_type | 具備相似功能的產品的分組 |
transaction | 改變賬戶余額的操作 |
DESC customer 顯示customer的表結構信息
子句 | 使用目的 |
---|---|
select | 確定結果集中應該包含哪些列 |
from | 指明索要提取數據的表呐矾,以及這些表示如何連接的 |
where | 過濾不需要的數據 |
group by | 用于對具有相同列值得進行分組 |
having | 過濾掉不需要的組 |
order by | 按一個或多個列,對最后結果集中的行進行排序 |
- select字句
select * from department;
顯示所有department表中的列(通過*表示)懦砂。
在此表中相當于下列語句
select dept_id,name from departmant;
select 子句中可以跟內建函數蜒犯,對列名、字符等運算荞膘。
SELECT emp_id,'ACTIVE',emp_id * 3.14159,UPPER(lname)
FROM employee
去重在查詢select關鍵字之后加上distinct關鍵字
select distinct cust_id from account;
- from子句
from子句定義了查詢中所使用的表罚随,以及連接這些表的方式。
這里的表包括:
1羽资、永久表(使用create table 創(chuàng)建的表)
2淘菩、臨時表(子查詢所返回的表)
3、虛擬表(使用create view 子句創(chuàng)建的視圖)
子查詢產生的表
SELECT
e.emp_id,
e.fname,
e.lname
FROM
(
SELECT
emp_id,
fname,
lname,
start_date,
title
FROM
employee
) e;
第一個from 后面就是子查詢創(chuàng)建的表
虛擬視圖 視圖是存儲在數據字典中的查詢削罩,表現得像一個表瞄勾,但實際上并不擁有任何數據费奸。
//創(chuàng)建視圖
CREATE VIEW employee_vw AS
SELECT emp_id,fname,lname,
YEAR(start_date) start_year
FROM employee;
//從視圖中查詢
SELECT emp_id,start_year FROM employee_vw;
- where子句
where子句用于在結果集中過濾掉不需要的行弥激。
SELECT emp_id,fname,lname,start_date,title
FROM employee
WHERE title = 'Head Teller';
最終的查詢結構滿足是 title = 'Head Teller'
//同時滿足兩個條件
SELECT emp_id,fname,lname,start_date,title
FROM employee
WHERE title = 'Head Teller'
AND start_date > '2016-01-01'愿阐;
//只需滿足一個條件
SELECT emp_id,fname,lname,start_date,title
FROM employee
WHERE title = 'Head Teller'
OR start_date > '2016-01-01'微服;
- group by 和 having子句
group by子句可以根據列值對數據進行分組,同時一般用having子句對分組后的數據過濾
SELECT d.name,COUNT(e.emp_id) num_employees
FROM department d INNER JOIN employee e ON
d.dept_id = e.dept_id
GROUP BY d.name
HAVING count(e.emp_id) > 2;
//根據d.name的中進行分組缨历,同時過濾count(e.emp_id) > 2的選項
- order by子句
order by用于對結果集中的原始列數據或是根據列數據計算的表達式結果進行排序以蕴。
SELECT emp_id,lname,fname,start_date FROM
employee
ORDER BY start_date DESC;
//DESC表示降序
- 多表查詢
盡管我們已經學習了單表查詢,但是有時候需要獲得的結果集需要查詢兩個或以上的表才能得到辛孵,特別是有外鍵的時候丛肮。join連接能夠幫我們解決這個問題。
SELECT e.fname,e.lname,d.name FROM
employee e (INNER)JOIN department d ON
e.dept_id = d.dept_id;
//JOIN ON表示內連接魄缚,employee中dept_id在department有相同的dept_id相匹配時才是我們需要的選項宝与。如果在employee中dept_id存在某個值而在department中不存在焚廊,這就會連接失敗。在沒有指定連接類型(INNER)時一般默認為內連接习劫。
如果兩個表中的列名相同可是使用
SELECT e.fname,e.lname,d.name FROM
employee e INNER JOIN department d USING(dept_id);
//多表連接 并伴有where過濾
SELECT a.account_id,a.cust_id,a.open_date,a.product_cd
FROM account a
INNER JOIN employee e
ON a.open_emp_id = e.emp_id
INNER JOIN branch b
ON e.assigned_branch_id = b.branch_id
WHERE e.start_date < '2007-01-01'
AND (e.title = 'Teller' OR e.title = 'Head Teller')
AND b.name = 'Woburn Branch';
更過關于SQL語句的知識請查看專業(yè)數據咆瘟。
下一篇介紹FMDB的基本使用