寫在前面：本篇博客大部分內(nèi)容參考數(shù)據(jù)庫(kù)系統(tǒng)概念（本科教學(xué)版）第三章

SQL的組成

PS：其中DDL毕莱、DML绕德、integrity有一些補(bǔ)充怜浅，剩下的幾項(xiàng)都是課本內(nèi)容闷煤，應(yīng)該后面會(huì)講到。現(xiàn)在就先放在那邊吧

• 數(shù)據(jù)定義語(yǔ)言（Data-Definition Language涝滴， DDL）

  • 提供定義關(guān)系模式绣版，刪除關(guān)系以及修改關(guān)系模式的命令
  • 如create胶台、drop、alter等執(zhí)行以后會(huì)產(chǎn)生杂抽、銷毀表（定義诈唬、刪除關(guān)系模式）或改變表的結(jié)構(gòu)（修改關(guān)系模式）

• 數(shù)據(jù)操縱語(yǔ)言（Data-Manipulation Language，DML）

  • SQL DML提供從數(shù)據(jù)庫(kù)中查詢信息默怨，以及在數(shù)據(jù)庫(kù)中插入元組讯榕、刪除元組、修改元組的能力
  • 只是對(duì)表中數(shù)據(jù)的增刪改查匙睹，并沒(méi)有實(shí)際影響表的結(jié)構(gòu)（即關(guān)系并沒(méi)有改變愚屁，只是該表關(guān)系的實(shí)例）

• 完整性約束（integrity）

  • SQL DDL 會(huì)給每一個(gè)關(guān)系（每一張表）在定義或者修改的時(shí)候定義一定的完整性約束。之后所有的操作都應(yīng)該滿足這些約束痕檬，不滿足的操作將不被允許執(zhí)行

  復(fù)習(xí)注釋: 不過(guò)如果對(duì)違反約束的行為做了處理霎槐，有些違反約束的語(yǔ)句也是可以成功執(zhí)行的。比如在定義外鍵的時(shí)候指定了級(jí)聯(lián)刪除梦谜，那么一旦刪除語(yǔ)句違反了外鍵約束丘跌，數(shù)據(jù)庫(kù)不是拒絕執(zhí)行，而是級(jí)聯(lián)刪除相關(guān)數(shù)據(jù)

  • 比如主鍵約束表示一個(gè)或多個(gè)字段唯一標(biāo)識(shí)了一行數(shù)據(jù)唁桩，如果試圖插入主鍵值相同的數(shù)據(jù)闭树，就不會(huì)被允許
  • 具體的下面會(huì)單獨(dú)說(shuō)明并舉例

• 視圖定義（view definition）

  • SQL DDL 包括定義視圖的命令

• 事務(wù)控制（transaction control）

  • SQL包括定義事務(wù)的開(kāi)始和結(jié)束的命令

• 嵌入式SQL和動(dòng)態(tài)SQL（embedded SQL）

  • 嵌入式和動(dòng)態(tài)SQL定義SQL語(yǔ)句如何嵌入到通用編程語(yǔ)言，如C荒澡、C++报辱、Java

• 授權(quán)

  • SQL DDL包括定義對(duì)關(guān)系和視圖的訪問(wèn)權(quán)限的命令

基本類型

這些基本數(shù)據(jù)類型不依賴于某一個(gè)具體的數(shù)據(jù)庫(kù)，是SQL數(shù)據(jù)庫(kù)通用的類型

• char(n)——全稱character

  • 用戶指定長(zhǎng)度的字符串单山，所有取值的長(zhǎng)度都固定位n長(zhǎng)的char
  • 比如身份證或者電話號(hào)碼等長(zhǎng)度固定的數(shù)據(jù)可以使用這個(gè)
  • 一般實(shí)際用的較少

• varchar(n)——全稱character varying

  • 可變長(zhǎng)的字符串碍现，n為串的最大允許長(zhǎng)度
  • 實(shí)際存的時(shí)候數(shù)據(jù)多長(zhǎng)，就存多長(zhǎng)米奸，較省空間
  • 實(shí)際應(yīng)用中大多用varchar昼接，而不是char

• int——integer

  • 整數(shù)類型，沒(méi)啥好講的悴晰，Oj8k

• smallint

  • 小整數(shù)類型

• number(p, d)

  • 定點(diǎn)數(shù)慢睡，這個(gè)數(shù)有p位數(shù)字，其中小數(shù)點(diǎn)右邊有d位
  • 例如铡溪，numberic(3, 1)可表示-99.9 ~ 99.9
  • 下面在MySQL數(shù)據(jù)庫(kù)上面的測(cè)試一睁，舉個(gè)栗子：(測(cè)試結(jié)果表明，如果定義numberic(3, 1), 則小數(shù)點(diǎn)前面有三位數(shù)時(shí)是無(wú)法插入的佃却，如果小數(shù)點(diǎn)后面有多位小數(shù)，則通過(guò)四舍五入的方法只保留一位小數(shù))
    • 先簡(jiǎn)單定義一張Student表（沒(méi)有定義主鍵窘俺，允許插入相同的數(shù)據(jù)）

      -- 下面定義了一張學(xué)生表饲帅，其中g(shù)rade的取值范圍為-99.9 ~ 99.9（不合理的取值复凳，只是為了測(cè)試）
      CREATE TABLE Student (
        study_id VARCHAR(20) NOT NULL,
        age INTEGER DEFAULT 0,
        grade NUMERIC(3, 1)
      );
      

    • 執(zhí)行正常的插入操作

      
      -- 下面兩條插入語(yǔ)句都能正常執(zhí)行，并且結(jié)果正確
      INSERT INTO Student(study_id, age, grade) VALUES (
        '201590001', 18, 92.5
      )   
      
      INSERT INTO Student(study_id, age, grade) VALUES (
        '201590002', 18, -92.5
      )  
      
      -- 執(zhí)行下面一條插入操作灶泵，最后保存的記錄是 -92.6育八，
      INSERT INTO Student(study_id, age, grade) VALUES (
        '201590003', 18, -92.56
      )  
      
      -- 執(zhí)行下面一條插入操作，最后保存的記錄是 -92.5赦邻，
      INSERT INTO Student(study_id, age, grade) VALUES (
        '201590004', 18, -92.54
      ) 
      
      -- 下面的這條插入操作將會(huì)報(bào)錯(cuò)
      --  [22001][1264] Data truncation: Out of range value for column 'grade' at row 1
      INSERT INTO Student(study_id, age, grade) VALUES (
        '201590005', 18, 192.5
      )  
      

    • 執(zhí)行上面操作后的結(jié)果

• real,double,precision

  • 浮點(diǎn)數(shù)與雙精度浮點(diǎn)數(shù)

• float(n)

  • 精度至少為n位的浮點(diǎn)數(shù)

• Date, time, timestamp, interval

  • 日期類型

SQL中的完整性約束

下文中列舉的只是部分簡(jiǎn)單約束髓棋，具體的會(huì)在后面的內(nèi)容中討論

       先來(lái)簡(jiǎn)單聊一聊我對(duì)完整性約束的理解吧，其實(shí)數(shù)據(jù)庫(kù)中的完整性約束講起來(lái)就是數(shù)據(jù)庫(kù)在定義一張表的時(shí)候惶洲，對(duì)其作出了一系列的約束
  （這些個(gè)約束可能影響一個(gè)或多個(gè)字段按声，甚至影響整個(gè)表結(jié)構(gòu)）。如果后續(xù)的操作中（不管是DDL還是很DML）恬吕，如果違背了之前定義的
約束签则，（***如果沒(méi)有對(duì)違反約束行為做特殊的處理***）這個(gè)操作就不會(huì)成功

• primary key（A1, A2, ..., An）——主鍵約束

  • 該約束主要指定了以屬性A1~An構(gòu)成關(guān)系的主鍵，該主鍵值必須非空且唯一
  • 即只要一個(gè)表的主鍵確定铐料，那這個(gè)表中就不允許存在一行數(shù)據(jù)其主鍵的值為null渐裂， 也不允許存在兩行數(shù)據(jù)其主鍵的值一樣。一旦后續(xù)操作違反了這個(gè)原則（約束）钠惩，則該操作就不會(huì)被執(zhí)行
  • 舉個(gè)栗子：
    • 新定義一張department表

      -- 下面的操作創(chuàng)建了一張department表柒凉，并指定dept_name為主鍵
      CREATE TABLE department (
        dept_name VARCHAR(20),
        building  VARCHAR(15),
        budget    NUMERIC(12, 2),
        PRIMARY KEY (dept_name)
      );
      

    • 下面順序執(zhí)行一系列插入操作

      -- 下面的操作將成功插入一條記錄
      INSERT INTO department (dept_name, building, budget) 
      VALUES ('Software', 'A', 100000);
      
      -- 下面再執(zhí)行一次與上面一模一樣的操作，會(huì)發(fā)現(xiàn)報(bào)錯(cuò)篓跛，因?yàn)橹耙呀?jīng)有一條
      -- 記錄的主鍵值為'Software'膝捞，顧無(wú)法再插入主鍵值相同的數(shù)據(jù)
      -- [23000][1062] Duplicate entry 'Software' for key 'PRIMARY'
      INSERT INTO department (dept_name, building, budget) 
      VALUES ('Software', 'A', 100000);
      
      -- 下面的語(yǔ)句試圖插入一條dept_name為空的數(shù)據(jù)，顯然也是報(bào)錯(cuò)的
      -- 因?yàn)樽鳛橹麈I的字段是不允許為空的
      -- [23000][1048] Column 'dept_name' cannot be null
      INSERT INTO department (dept_name, building, budget) VALUES (NULL , 'A', 100000);
      

    • 執(zhí)行上面操作后的結(jié)果

• foregin key(A1, A2, ..., An) references —— 外鍵約束

  • 簡(jiǎn)單來(lái)講如果一個(gè)關(guān)系（表）A中包含一個(gè)外鍵C举塔，則作為外鍵的一個(gè)或多個(gè)字段必然是另一張表B的主鍵绑警。則由于外鍵約束這一層關(guān)系，對(duì)A表和B表都會(huì)收一定程度的限制（現(xiàn)在解釋的還是比較抽象央渣，看下面的例子會(huì)比較直觀一點(diǎn)）
    • 如果想向A表中插入一條數(shù)據(jù)计盒，且數(shù)據(jù)中C的值在B表中找不到對(duì)應(yīng)表項(xiàng)，則這個(gè)操作不會(huì)成功（就像你如果向運(yùn)動(dòng)員表中插入一條數(shù)據(jù)芽丹，國(guó)家屬性對(duì)應(yīng)的標(biāo)識(shí)在國(guó)家表中不存在北启，則這次插入操作就會(huì)失敗）
    • 如果試圖刪除一個(gè)表B的記錄拔第，而這條記錄的主鍵只正好是A表中某條數(shù)據(jù)的外鍵咕村，則這次刪除操作不會(huì)成功
  • 舉個(gè)栗子：

    • 先創(chuàng)建兩張表

      -- 創(chuàng)建一張表department，并制定dept_name為主鍵
      CREATE TABLE department (
        dept_name VARCHAR(20),
        building  VARCHAR(15),
        budget    NUMERIC(12, 2),
        PRIMARY KEY (dept_name)
      );
      
      -- 再創(chuàng)建一張couese表蚊俺，并指定dept_name為本表關(guān)于department表的外鍵
      CREATE TABLE course (
        course_id VARCHAR(7) NOT NULL,
        title     VARCHAR(50),
        dept_name VARCHAR(20),
        credits   NUMERIC(2, 0),
        PRIMARY KEY (course_id),
        FOREIGN KEY (dept_name) REFERENCES department (dept_name)
      );
      

    • 我們先試圖插入一條course記錄

      -- 下面試圖往course表中插入一條記錄懈涛，但是department表中并沒(méi)有
      -- dept_name = 'DUT'的記錄，故該操作會(huì)失敗
      INSERT INTO course (course_id, title, dept_name, credits)
      VALUES ('08', 'English', 'DUT', 3) 
      

    • 我們先往department表中插入記錄泳猬，再往course表中插入記錄批钠，下面的操作就能成功插入

      
      INSERT INTO department (dept_name, building, budget) VALUES ('DUT' , 'A', 100000);
      
      INSERT INTO course (course_id, title, dept_name, credits)
      VALUES ('08', 'English', 'DUT', 3) 
      

      • 執(zhí)行上面操作后的結(jié)果

      dept_name	building	budget
      DUT	A	100000

      course_id	title	dept_name	credits
      08	Endlish	DUT	3

    • 此時(shí)我們?nèi)绻噲D刪除department表中dept_name = DUT的那條數(shù)據(jù)

      -- 執(zhí)行下面的操作就會(huì)報(bào)錯(cuò)宇植，因?yàn)閏ourse表中還有一項(xiàng)數(shù)據(jù)的dept_name的值為DUT，如果刪除掉department中的這條數(shù)據(jù)埋心，就會(huì)破壞其完整性指郁，顧執(zhí)行失敗
      -- 23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`db_study`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`))
      DELETE FROM department WHERE dept_name = 'DUT';
      

    • 同樣的如果，我們?cè)噲D刪除department表拷呆，則只要course表中還有數(shù)據(jù)闲坎，這個(gè)操作就會(huì)失敗

• not null——非空約束

  • 理解上面兩個(gè)約束以后這個(gè)就很好理解了，這個(gè)約束就是指定某個(gè)字段不能為空茬斧，試圖傳入null值的操作都不會(huì)成功
  • 作為主鍵的字段隱式擁有非空約束