關(guān)聯(lián)查詢(xún)

• 存儲(chǔ)關(guān)系: 1對(duì)1, 1對(duì)多, 多對(duì)多
• 建立關(guān)系表
  • 外鍵:

  create table scores(
  id int auto_increment primary key not null,
  score decimal(4, 1),
  stuid int,
  subid int,
  foreign key(stuid) references students(id),
  foreign key(subid) references subjects(id));
  

  • 外鍵的級(jí)聯(lián)操作
    • 在刪除students表的數(shù)據(jù)是, 如果這個(gè)id值在sources中已經(jīng)存在, 則會(huì)拋異常
    • 推薦使用邏輯刪除, 還可以解決這個(gè)問(wèn)題
    • 可以創(chuàng)建表時(shí)指定級(jí)聯(lián)操作, 也可以在創(chuàng)建表后再修改外鍵的級(jí)聯(lián)操作
    • 語(yǔ)法:

    alter table sources add constraint stu_sco foreign key(stuid) references students(id) on update CASCADE
    

    • 級(jí)聯(lián)操作的類(lèi)型包括 :
      • restrict(限制) : 默認(rèn)值, 拋出異常
      • cascade(級(jí)聯(lián)) : 如果主表的記錄刪除, 則從表中相關(guān)聯(lián)的記錄都將被刪除
      • set null : 將外鍵設(shè)置為空
      • no action : 什么都不做
• 連接查詢(xún)(多表查詢(xún))
  • 當(dāng)查詢(xún)結(jié)果來(lái)源于多張表的時(shí)候, 這個(gè)時(shí)候需要連接查詢(xún) (on后面主要寫(xiě)join左邊表和右邊表的關(guān)系)

  select student.name, subjects.title, scores.score
  form scores 
  inner join students on scores.stuid=students.id
  inner join subjects on scores.subid=subjects.id
  

  • 左連接(left join) : 以左表為主, 結(jié)果中顯示出來(lái)的數(shù)據(jù), 兩個(gè)表中滿(mǎn)足條件的 + 左表的數(shù)據(jù)
  • left join, inner join, right join : 主要的表現(xiàn)就是結(jié)果集的不同

視圖

• 對(duì)于復(fù)雜的查詢(xún), 在多次使用后, 維護(hù)是一件非常麻煩的事情, 定義視圖來(lái)解決該問(wèn)題
• 視圖本質(zhì)就是對(duì)查詢(xún)的一個(gè)封裝
• 定義視圖 : (create 換成alter 就是修改視圖)

  create view stuscore as 
  select students.*, scores.score from scores
  inner join students on scores.stuid=students.id;
  

• 視圖的用途就是查詢(xún):

  select * from stuscore;
  

事務(wù)

• 當(dāng)一個(gè)業(yè)務(wù)邏輯需要多個(gè)sql完成時(shí), 如果其中某條sql語(yǔ)句出錯(cuò), 則希望整個(gè)操作都退回
• 使用十五可以完成退回的功能, 保證業(yè)務(wù)邏輯的正確性
• 事務(wù)四大特性(ACID)
  • 原子性: 事務(wù)中的全部操作在數(shù)據(jù)庫(kù)中是不可分割的, 要么全部完成, 要么均不執(zhí)行
  • 一致性: 幾個(gè)并行執(zhí)行的事務(wù), 其執(zhí)行結(jié)果必須與按某一順序串執(zhí)行的結(jié)果相一致
  • 隔離性: 事務(wù)的執(zhí)行不受其他事務(wù)的干擾, 事務(wù)執(zhí)行的中間結(jié)果對(duì)其他事務(wù)必須是透明的
  • 持久性: 對(duì)于任意已提交事務(wù), 系統(tǒng)必須保證該事物對(duì)數(shù)據(jù)庫(kù)的改變不被丟失, 及時(shí)數(shù)據(jù)庫(kù)出現(xiàn)故障
• 要求: 表的類(lèi)型必須是innodb或bdb類(lèi)型, 才可以對(duì)此表使用事務(wù)
• 查看表的創(chuàng)建語(yǔ)句: show create table students -> 可以查看當(dāng)前引擎
• 修改表的類(lèi)型: alter table '表明' engine=innodb;
• 事務(wù)語(yǔ)句:
  • 開(kāi)啟: begin;
  • 回滾: rollback; (會(huì)解鎖)
  • 提交事務(wù): commit;(會(huì)解鎖)
  • 示例:

  select * from students;
  begin;
  update students set name = 'Jack' where id = 1; 
  commit; // 內(nèi)存級(jí)的臨時(shí)表更新到表中
  rollback; // begin之后所有的操作都將回滾
  

  • 使用情況: 在自己編寫(xiě)的代碼中, 鏈接數(shù)據(jù)庫(kù)的時(shí)候, 操作失敗的時(shí)候, 默認(rèn)會(huì)回滾

索引

MySQL中如何實(shí)現(xiàn)索引

• 選擇列的數(shù)據(jù)類(lèi)型

  • 越小的數(shù)據(jù)類(lèi)型通常更好: 越小的數(shù)據(jù)類(lèi)型通常在磁盤(pán), 內(nèi)存和CPU緩存中都需要更小的空間, 處理起來(lái)更快
  • 簡(jiǎn)單的數(shù)據(jù)類(lèi)型更好: 整形數(shù)據(jù)比起字符, 處理開(kāi)銷(xiāo)更小, 因?yàn)樽址谋容^更復(fù)雜
  • 盡量避免NULL: 應(yīng)該指定列為NOT NULL, 除非你想存儲(chǔ)NULL. 在MySQL中, 含有空值的列很難進(jìn)行查詢(xún)優(yōu)化, 因?yàn)樗鼈兪沟盟饕? 索引的統(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜. 你應(yīng)該用0, 一個(gè)特殊的值或者一個(gè)空串代替空值

• 操作:

  • 索引分單列索引和組合索引
    • 單列索引: 即一個(gè)索引只包含單個(gè)列, 一個(gè)表可以有多個(gè)單列索引, 但這不是組合索引
    • 組合索引: 即一個(gè)索引包含多個(gè)列
  • 查看索引: SHOW INDEX FROM table_name;
  • 創(chuàng)建索引: `` CREATE INDEX indexName ON table_name(username(length));
  • 刪除索引: DROP INDEX [indexName] ON table_name;

• 缺點(diǎn):

  • 雖然索引大大提高了查詢(xún)速度, 同時(shí)卻會(huì)降低更新表的速度, 如對(duì)表進(jìn)行INSERT, UPDATE, DELETE. 因?yàn)楦卤頃r(shí), MySQL不僅要保存數(shù)據(jù), 還要保存一下索引文件
  • 建立索引會(huì)占用磁盤(pán)空間的索引文件

• 示例:

  • 1. 開(kāi)啟運(yùn)行時(shí)間監(jiān)測(cè): set profiling=1;
  • 2. 執(zhí)行查詢(xún)語(yǔ)句: select * from areas where atitle='沈陽(yáng)市';
  • 3. 查看執(zhí)行時(shí)間: show profiles;
  • 4. 為表areas的atitle列創(chuàng)建索引: create index titleIndex on areas(atitle(20))
  • 5. 執(zhí)行查詢(xún)語(yǔ)句: select * from areas where atitle='沈陽(yáng);'
  • 6. 再次查看執(zhí)行的時(shí)間: show profiles;

• 數(shù)據(jù)庫(kù)優(yōu)化:

  • 優(yōu)化where后面的語(yǔ)句

自關(guān)聯(lián)

• 省市區(qū)表(查詢(xún)時(shí))