第二章 Senior進(jìn)階 數(shù)據(jù)庫

一、數(shù)據(jù)庫管理系統(tǒng)

1.SQL：SQL是Structured Query Language(結(jié)構(gòu)化查詢語言)的縮寫。SQL是專為數(shù)據(jù)庫而建立的操作命令集，是一種功能齊全的數(shù)據(jù)庫語言。
2.常見的數(shù)據(jù)庫“
    1.My SQL:MySQL是一個(gè)精巧的SQL數(shù)據(jù)庫管理系統(tǒng)，而且是開源的數(shù)據(jù)管理系統(tǒng)。MySQL主要目標(biāo)是快速、健壯和易用。由于它的強(qiáng)大功能、靈活性、豐富的應(yīng)用編程接口（API）以及精巧的系統(tǒng)結(jié)構(gòu)，受到了廣大自由軟件愛好者甚至是商業(yè)軟件用戶的青睞
    2.Oracle：Oracle Database,又名Oracle RDBMS，或簡稱Oracle。是甲骨文公司的一款關(guān)系數(shù)據(jù)庫管理系統(tǒng)。系統(tǒng)可移植性好、使用方便、功能強(qiáng)。
3.數(shù)據(jù)庫的好處(特征):
    1.以一定方式存儲在一起
    2.能為多個(gè)用戶共享
    3.具有盡可能少的冗余代碼
    4.與程序彼此獨(dú)立的數(shù)據(jù)集合
4.數(shù)據(jù)庫
    1.定義：數(shù)據(jù)庫(Database)是按照數(shù)據(jù)結(jié)構(gòu)來組織、存儲和管理數(shù)據(jù)的倉庫
    2.分類：關(guān)系型數(shù)據(jù)庫(主流)、對象型數(shù)據(jù)庫、層次式數(shù)據(jù)庫、網(wǎng)格式數(shù)據(jù)庫
    3.常用的關(guān)系型數(shù)據(jù)庫
        PC端：Oracle、MySQL、SQL Server、Access、DB2、Sybase
        嵌入式\移動客戶端：SQLite
5.SQLite(http://www.sqlite.org/doc.html)是一個(gè)輕量級的關(guān)系數(shù)據(jù)庫。SQLite最初的設(shè)計(jì)目標(biāo)是用于嵌入式系統(tǒng)，它占用資源非常少，在嵌入式設(shè)備中，只需要幾百k的內(nèi)容就夠了，目前應(yīng)用于Android、iOS等智能手機(jī)。iOS使用SQLite時(shí)，只需加入libsqlite3.0.tbd以及引入sqlite3.h頭文件即可。
6.表：是數(shù)據(jù)庫中一個(gè)非常重要的對象，是其他對象的基礎(chǔ)。根據(jù)信息的分類情況，一個(gè)數(shù)據(jù)庫中可能包含若干個(gè)數(shù)據(jù)表
7.字段：標(biāo)的“列”稱為“字段”，每一個(gè)字段包含某一個(gè)專題的信息。用來標(biāo)識本列數(shù)據(jù)類型
8.記錄：是指對應(yīng)于數(shù)據(jù)表中一行信息的一組完整的相關(guān)信息。用來表示表中一行的信息

二、SQL語句

1.SQLite是無類型的數(shù)據(jù)庫，可以保存任何類型的數(shù)據(jù)，對于SQLite來說對字段不指定類型是完全有效的(==注：良好的編程習(xí)慣應(yīng)該要為字段標(biāo)注類型==)
2.SQLite近似類型規(guī)則
    為了使SQLite和其他數(shù)據(jù)庫間的兼容性最大化，sqlite支持“類型近似”的觀點(diǎn)，列的類型近似指的是存儲在列上的數(shù)據(jù)的推薦類型
    1.如果類型字符串中包含“INT”，那么該字段的親緣類型是INTEGER。
    2.如果類型字符串中包含“CHAR”、“CLOB”或者“TEXT”，那么該字段的親緣類型是TEXT，如VARCHAR。
    3.如果類型字符串中包含“BLOB”。那么該字段的親緣類型是NONE。
    4.如果類型字符串中包含“REAL”、“FLOA”或“DOUB”，那么該字段的親緣類型是REAL
    5.其余情況下、字段的親緣類型為NUMERIC。
3.SQLite的字段約束條件
    NOT NULL -- 非空       該字段數(shù)據(jù)不能為空
    UNIQUE ----唯一        該字段數(shù)據(jù)不能重復(fù)
    PRIMARY KEY ---主鍵    用于唯一標(biāo)識一條數(shù)據(jù)
    FOREIGN KEY ---外鍵    該字段作為另一個(gè)表的主鍵，聯(lián)表查詢
    CHECK --- 條件檢查，確保一列中的所有值滿足一定條件   該字段數(shù)據(jù)必須滿足一定條件
    DWDAULT ---- 默認(rèn)      該字段數(shù)據(jù)可以給定默認(rèn)值
    AUTOINCREATEMENT --- 自增型變量   該字段數(shù)據(jù)如果為整型可以自動加1
4.SQLite字段約束條件：PRIMARY KEY -- 主鍵
    首先，數(shù)據(jù)表中每一條記錄都有一個(gè)主鍵，這就像我們每個(gè)人的身份證號碼、員工號、銀行賬號；反過來說，每一個(gè)主鍵對應(yīng)著一條數(shù)據(jù)記錄，所以，主鍵必須是唯一的
    其次，一般情況下注鍵同時(shí)也是也是一個(gè)索引，所以通過逐漸查找記錄速度比較快
    第三，在關(guān)系型數(shù)據(jù)庫中，一個(gè)表的主鍵可以作為另一個(gè)標(biāo)的外鍵，這樣，這兩個(gè)表之間就通過這個(gè)鍵建立了關(guān)系
    最后，逐漸一般是整數(shù)或者字符串，只要保證唯一就行。在SQLite中，主鍵如果是整數(shù)類型，該列的值可以自動增長
5.SQL語句：
    1.建表命令（Create table）
        create table if not exists 表名 (字段1 約束1 約束2····,字段2 約束1 約束2，····);
    2.數(shù)據(jù)插入命令（Insert）
        insert into 表名 (字段1，字段2，···) values (字段1值，字段2值，···);
        注意：要插入的數(shù)據(jù)要和字段順序相同
    3.數(shù)據(jù)庫更新命令（Update）
        update 表名 set 字段名1 = 修改值1，字段名2 = 修改值2，···· where 條件;
        注意：關(guān)鍵字 update set
              條件可以有一個(gè)或多個(gè)。多個(gè)條件使用and(與)，or(或)連接
    4.數(shù)據(jù)庫刪除命令（Delete
        delete from 表名 where 條件;
    5.數(shù)據(jù)庫檢索命令（Select）
        select 要查找的字段 form 表名 where 條件
        注意：要查找的字段，如果查找所有字段可以使用通配符 * 

三、iOS的數(shù)據(jù)庫技術(shù)實(shí)現(xiàn)

1.Linux系統(tǒng)級的SQLite技術(shù)實(shí)現(xiàn)框架
    Xcode 6 中l(wèi)ibsqlite3.0.dylib
    Xcode 7 中l(wèi)ibsqlite3.0.tbd
2.使用SQLite數(shù)據(jù)庫的流程
    1.引入<sqlite3.h>頭文件
        //包含了sqlite數(shù)據(jù)庫操作所需要的所有函數(shù)
        #import <sqlite3.h> 
    2.在.m文件中聲明一個(gè)sqlite3的屬性
        //數(shù)據(jù)庫句柄 這個(gè)屬性相當(dāng)于數(shù)據(jù)庫的對象
        sqlite3* sqliteHandle;
    3.創(chuàng)建數(shù)據(jù)庫文件將要存儲的文件路徑
        - (NSString*)dbPath{
            NSString* documentsPathString = [[SandBoxPaths documentsPath] stringByAppendingPathComponent:@"textDB.sqlite"];
            return documentsPathString;
        }
    4.打開數(shù)據(jù)庫
        - (void)createDataBase{
           //創(chuàng)建數(shù)據(jù)庫文件的函數(shù)，如果數(shù)據(jù)庫文件存在，那么這個(gè)方法就是打開數(shù)據(jù)庫文件，如果數(shù)據(jù)庫不文件存在，這個(gè)方法就是創(chuàng)建數(shù)據(jù)庫文件，再打開
           //第一個(gè)參數(shù)：數(shù)據(jù)庫文件的路徑,并且需要將其轉(zhuǎn)換為char * 類型（是C語言的字符串類型）
           //第二個(gè)參數(shù)：數(shù)據(jù)庫文件的句柄，取地址  &（and） 符號就是取地址
           //將數(shù)據(jù)庫句柄指向數(shù)據(jù)庫內(nèi)存，我們對數(shù)據(jù)庫的操作，都是通過句柄來完成的
           int result =  sqlite3_open([self dbPath].UTF8String, & sqliteHandle);
           if(result == SQLITE_OK){
               NSLog(@"數(shù)據(jù)庫打開成功");
           }else{
               NSLog(@"數(shù)據(jù)庫打開失敗 --- %d",result);
            }
        }
    3.執(zhí)行SQL命令(建表，增刪改查)
        1.非查詢操作（無返回結(jié)果集的操作） 插入 更新 刪除等
        //參數(shù)為需要執(zhí)行的sql語句
        - (void)noQueryOpertion:(NSString*)sql{
            //用來執(zhí)行無返回結(jié)果的操作
            //第一個(gè)參數(shù)：數(shù)據(jù)庫的句柄，所有對數(shù)據(jù)庫的操作都是通過句柄來完成的，所以這里需要句柄
            //第二個(gè)參數(shù)：要執(zhí)行的sql語句
            //第三個(gè)參數(shù)：回調(diào)函數(shù)，exec函數(shù)執(zhí)行完畢之后會執(zhí)行的函數(shù)。如果需要在該操作執(zhí)行完畢的時(shí)候做一些其他操作，就需要實(shí)現(xiàn)該回調(diào)函數(shù)，一般我們直接NULl
            //第四個(gè)參數(shù)：回調(diào)方法的第一個(gè)參數(shù)
            //第五個(gè)參數(shù)：錯(cuò)誤日志
            int result =  sqlite3_exec(sqliteHandle, sql.UTF8String, NULL, NULL, NULL);
            if (result == SQLITE_OK) {
                NSLog(@"操作執(zhí)行成功");
            }else{
                NSLog(@"操作執(zhí)行失敗");
            }
        }
        
        2.查詢操作
        - (void)queryWithSql:(NSString*)sql{
           //伴隨指針 當(dāng)我們進(jìn)行查詢操作的時(shí)候，該指針可以理解為存儲一條一條的記錄。我們獲取一條記錄的所有信息都是通過伴隨指針。
           sqlite3_stmt * stmt = NULL;
           //預(yù)執(zhí)行sql語句，如果預(yù)執(zhí)行成功說明，那么我們就可以獲取一條一條的記錄；如果預(yù)執(zhí)行失敗,說明我們的sql語句有問題 不能獲取表中的記錄信息
           //第一個(gè)參數(shù)：數(shù)據(jù)庫句柄
           //第二個(gè)參數(shù)：要執(zhí)行的SQ語句
           //第三個(gè)參數(shù)：要執(zhí)行的長度，如果全部執(zhí)行，這里就賦值為-1
           //第四個(gè)參數(shù)：伴隨指針
           //第五個(gè)參數(shù)：如果sql語句不全部執(zhí)行，該參數(shù)保存未執(zhí)行的sql語句
           int result = sqlite3_prepare(sqliteHandle, sql.UTF8String, -1, &stmt, NULL);
           //初始化一個(gè)可變數(shù)組,用來存放所有的記錄，每一條記錄就相當(dāng)于一個(gè)字典
           NSMutableArray* allDataMArray = [[NSMutableArray alloc] init];
           //判斷是否預(yù)執(zhí)行成功
           if(result == SQLITE_OK){
                //說明預(yù)執(zhí)行成功，可以取表中的數(shù)據(jù)了
                //通過while循環(huán)將表中的每一條記錄取出
                //step方法沒執(zhí)行一次，伴隨指針就會執(zhí)行下一條記錄；當(dāng)所有的記錄都取完之后，step函數(shù)的返回值就不等于SQLITE_ROW了，循環(huán)也就停止了
                while (sqlite3_step(stmt) == SQLITE_ROW) {
                    //每次執(zhí)行while循環(huán)，都是一條新的紀(jì)錄，一條新記錄要對應(yīng)一個(gè)新字典。
                    NSMutableDictionary* mDictionary = [[NSMutableDictionary alloc] init];
        
                    //進(jìn)入循環(huán)體內(nèi)，伴隨指針就相當(dāng)于當(dāng)前取出的一條記錄
                    //從記錄中取值，是一個(gè)字段一個(gè)字段取出
                    //取出第0列的值
                    //
                    const unsigned char* name = sqlite3_column_text(stmt, 0);
                    //將char*類型轉(zhuǎn)換為我們OC中可用的字符串類型
                    NSString* nameString = [[NSString alloc] initWithCString:(const char *)name encoding:NSUTF8StringEncoding];
                    //將數(shù)據(jù)存入到字典中
                    [mDictionary setObject:nameString forKey:@"name"];
                    //取出第一列的值，存入字典中
                    int age = sqlite3_column_int(stmt,1);
                    //將int數(shù)據(jù)轉(zhuǎn)換為NSNumber放入字典
                    [mDictionary setObject:@(age) forKey:@"age"];
                    //取出第二列的值
                    const unsigned char* gender = sqlite3_column_text(stmt, 2);
                    //將char*類型轉(zhuǎn)換為我們OC中可用的字符串類型
                    NSString* genderString = [NSString stringWithCString:(const char*)gender encoding:NSUTF8StringEncoding];
                    [mDictionary setObject:genderString forKey:@"gender"];
                    //將該字典添加到可變數(shù)組中
                    [allDataMArray addObject:mDictionary];
                }
            }else{
                NSLog(@"預(yù)執(zhí)行失敗 --- %d",result);
        }
              //將伴隨指針?biāo)谐钟械馁Y源釋放
              sqlite3_finalize(stmt);
              //打印結(jié)果
              NSLog(@"查詢結(jié)果 ---- %@",allDataMArray);
        }
        3.SQL語句操作
            1.建表操作
            NSString* sql = @"create table if not exists stu (name text,age integer,gender text)";
            [self noQueryOpertion:sql];
            2.插入一條數(shù)據(jù)
            NSString* insertSql = @"insert into stu values ('kk',16,'男')";
            [self noQueryOpertion:insertSql];
            3.更新操作
            NSString* updateSql = @"update stu set age = 18 where age = 16";
            [self noQueryOpertion:updateSql];
            4.刪除一條數(shù)據(jù)
            NSString* deleteSql = @"delete from stu where name = 'kk'";
            [self noQueryOpertion:deleteSql];
            5.查詢方法
            [self queryWithSql:@"select * from stu"];
    4.關(guān)閉數(shù)據(jù)庫
        sqlite3_close(sqliteHandle);
3.SQLite常用系列函數(shù)
    1.sqlite3_exec()可以執(zhí)行任何SQL語句，比如創(chuàng)表、更新、插入和刪除操作。但是一般不用它執(zhí)行查詢語句，因?yàn)樗粫祷夭樵兊降臄?shù)據(jù)
    2.SQLite語法：預(yù)執(zhí)行
        sqlite3_prepare()
            這個(gè)函數(shù)將sql文本轉(zhuǎn)換為一個(gè)準(zhǔn)備語句(prepared statement)對象，同時(shí)返回這個(gè)對象的指針。它實(shí)際上并不執(zhí)行(evaluate)這個(gè)SQL語句，他僅僅為執(zhí)行準(zhǔn)備這個(gè)sql語句
        sqlite3_bind_*()
            給宿主參數(shù)(host parameters)綁定值(*代表不同類型)
        sqlite3_step ()
            一次或多次來執(zhí)行有前面sqlite3_prepare創(chuàng)建的準(zhǔn)備語句
        sqlite3_column()
            這個(gè)過程從執(zhí)行sqlite3_step()執(zhí)行一個(gè)準(zhǔn)備語句得到的結(jié)果集的當(dāng)前行中返回一個(gè)列。每次sqlite3_step得到一個(gè)結(jié)果集的列停下后，這個(gè)過程就可以被多次調(diào)用去查詢這個(gè)行的各列的值。對列操作是有多個(gè)函數(shù)，均以sqlite3_column為前綴
        sqlite3_finalize()
            這個(gè)過程銷毀前面被sqlite3_prepare創(chuàng)建的準(zhǔn)備語句，每個(gè)準(zhǔn)備語句都必須使用這個(gè)函數(shù)去銷毀以防止內(nèi)存泄露