SQL語句相關

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的表結構信息
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'微服;

image.png
  • 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的基本使用

?著作權歸作者所有,轉載或內容合作請聯系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市诽里,隨后出現的幾起案子袒餐,更是在濱河造成了極大的恐慌,老刑警劉巖谤狡,帶你破解...
    沈念sama閱讀 212,686評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件灸眼,死亡現場離奇詭異,居然都是意外死亡墓懂,警方通過查閱死者的電腦和手機幢炸,發(fā)現死者居然都...
    沈念sama閱讀 90,668評論 3 385
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來拒贱,“玉大人宛徊,你說我怎么就攤上這事÷甙模” “怎么了闸天?”我有些...
    開封第一講書人閱讀 158,160評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長斜做。 經常有香客問我苞氮,道長,這世上最難降的妖魔是什么瓤逼? 我笑而不...
    開封第一講書人閱讀 56,736評論 1 284
  • 正文 為了忘掉前任笼吟,我火速辦了婚禮,結果婚禮上霸旗,老公的妹妹穿的比我還像新娘贷帮。我一直安慰自己,他們只是感情好诱告,可當我...
    茶點故事閱讀 65,847評論 6 386
  • 文/花漫 我一把揭開白布撵枢。 她就那樣靜靜地躺著,像睡著了一般精居。 火紅的嫁衣襯著肌膚如雪锄禽。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 50,043評論 1 291
  • 那天靴姿,我揣著相機與錄音沃但,去河邊找鬼。 笑死佛吓,一個胖子當著我的面吹牛宵晚,可吹牛的內容都是我干的恨旱。 我是一名探鬼主播,決...
    沈念sama閱讀 39,129評論 3 410
  • 文/蒼蘭香墨 我猛地睜開眼坝疼,長吁一口氣:“原來是場噩夢啊……” “哼搜贤!你這毒婦竟也來了?” 一聲冷哼從身側響起钝凶,我...
    開封第一講書人閱讀 37,872評論 0 268
  • 序言:老撾萬榮一對情侶失蹤仪芒,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后耕陷,有當地人在樹林里發(fā)現了一具尸體掂名,經...
    沈念sama閱讀 44,318評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 36,645評論 2 327
  • 正文 我和宋清朗相戀三年哟沫,在試婚紗的時候發(fā)現自己被綠了饺蔑。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 38,777評論 1 341
  • 序言:一個原本活蹦亂跳的男人離奇死亡嗜诀,死狀恐怖猾警,靈堂內的尸體忽然破棺而出,到底是詐尸還是另有隱情隆敢,我是刑警寧澤发皿,帶...
    沈念sama閱讀 34,470評論 4 333
  • 正文 年R本政府宣布拂蝎,位于F島的核電站穴墅,受9級特大地震影響,放射性物質發(fā)生泄漏温自。R本人自食惡果不足惜玄货,卻給世界環(huán)境...
    茶點故事閱讀 40,126評論 3 317
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望悼泌。 院中可真熱鬧松捉,春花似錦、人聲如沸券躁。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,861評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽也拜。三九已至,卻和暖如春趾痘,著一層夾襖步出監(jiān)牢的瞬間慢哈,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 32,095評論 1 267
  • 我被黑心中介騙來泰國打工永票, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留卵贱,地道東北人滥沫。 一個月前我還...
    沈念sama閱讀 46,589評論 2 362
  • 正文 我出身青樓,卻偏偏與公主長得像键俱,于是被迫代替她去往敵國和親兰绣。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 43,687評論 2 351

推薦閱讀更多精彩內容