• 實體與實體之間有3種對應關係，這些關係也需要儲存下來
• 在開發中需要對儲存的資料進行一些處理，用到內建的一些函式
• 檢視用於完成查詢語句的封裝
• 事務可以保證複雜的增刪改操作有效

 

 

建立表的語句如下：

Create table scores(

Id int primary key auto_increment,

stuid int,

subid int,

score decimal(5,2)

);

 

此時插入或者修改資料時，如果stuid的值在students表中不存在則會報錯

在建立表示可以直接建立約束

create table scrores(

Id int primary key auto_increment,

Stuid int,

Subid int,

Score decimal(5,2),

Foreign key(stuid) references students(id),

Foreign key(subid) references subjects(id)

);

 

先看個問題

• 問：查詢每個學生每個科目的分數
• 分析：學生姓名來源於students表，科目名稱來源於subjects，分數來源於scores表，怎麼將3個表放到一起查詢，並將結果顯示在同一個結果集中呢？
• 答：當查詢結果來源於多張表時，需要使用連線查詢
• 關鍵：找到表間的關係，當前的關係是
  • students表的id---scores表的stuid
  • subjects表的id---scores表的subid
• 則上面問題的答案是：

select students.sname,subjects.stitle,scores.score

from scores

inner join students on scores.stuid=students.id

inner join subjects on scores.subid=subjects.id;

• 結論：當需要對有關係的多張表進行查詢時，需要使用連線join

連線查詢

• 連線查詢分類如下：
  • 表A inner join 表B：表A與表B匹配的行會出現在結果中，合併後的表的總條數是兩個表的乘積，這個操作只是將兩個錶連結，沒有任何實際的意義，所以要進過篩選
  • 表A left join 表B：表A與表B匹配的行會出現在結果中，外加表A中獨有的資料，未對應的資料使用null填充
  • 表A right join 表B：表A與表B匹配的行會出現在結果中，外加表B中獨有的資料，未對應的資料使用null填充
• 在查詢或條件中推薦使用“表名.列名”的語法
• 如果多個表中列名不重複可以省略“表名.”部分
• 如果表的名稱太長，可以在表名後面使用' as 簡寫名'或' 簡寫名'，為表起個臨時的簡寫名稱

練習

• 查詢學生的姓名、平均分

select students.sname,avg(scores.score)

from scores

inner join students on scores.stuid=students.id

group by students.sname;

• 查詢男生的姓名、總分

select students.sname,avg(scores.score)

from scores

inner join students on scores.stuid=students.id

where students.gender=1

group by students.sname;

• 查詢科目的名稱、平均分

select subjects.stitle,avg(scores.score)

from scores

inner join subjects on scores.subid=subjects.id

group by subjects.stitle;

• 查詢未刪除科目的名稱、最高分、平均分

select subjects.stitle,avg(scores.score),max(scores.score)

from scores

inner join subjects on scores.subid=subjects.id

where subjects.isdelete=0

group by subjects.stitle;

------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

 

 

 

總結：

Select distinct *

From 表1 inner|left|right join 表2 on 表1與表二的關係

Where …

Group by ….. having…..

Order by …..  asc|desc

Limit start,count

 

 

 

檢視

• 對於複雜的查詢，在多次使用後，維護是一件非常麻煩的事情
• 解決：定義檢視
• 檢視本質就是對查詢的一個封裝
• 定義檢視

create view stuscore as

select students.*,scores.score from scores

inner join students on scores.stuid=students.id;

• 檢視的用途就是查詢

select * from stuscore;

 

 

 

 

根據學生id分組，我們可以得到學生的姓名（如上圖，分組之後不存在奇異），而不能更具學生的id確定學生的科目的成績

 

自關聯

 

一張表可以存大量的資訊，如果只存幾萬條，是一種浪費。可以將三張不同的表建立咋一張表中，然後用自關聯實現

 

 

從sql檔案中匯入資料

source areas.sql

查詢一共有多少個省

    Select count(*) from areas where pid is null

查詢省得名稱為“山西省”的所有城市

 

事務

• 當一個業務邏輯需要多個sql完成時，如果其中某條sql語句出錯，則希望整個操作都退回
• 使用事務可以完成退回的功能，保證業務邏輯的正確性
• 事務四大特性(簡稱ACID)
  • 原子性(Atomicity)：事務中的全部操作在資料庫中是不可分割的，要麼全部完成，要麼均不執行
  • 一致性(Consistency)：幾個並行執行的事務，其執行結果必須與按某一順序序列執行的結果相一致
  • 隔離性(Isolation)：事務的執行不受其他事務的干擾，事務執行的中間結果對其他事務必須是透明的
  • 永續性(Durability)：對於任意已提交事務，系統必須保證該事務對資料庫的改變不被丟失，即使資料庫出現故障
• 要求：表的型別必須是innodb或bdb型別，才可以對此表使用事務
• 檢視錶的建立語句

show create table students;

• 修改表的型別

alter table '表名' engine=innodb;

• 事務語句

開啟begin;

提交commit;

回滾rollback;

示例1

• 步驟1：開啟兩個終端，連線mysql，使用同一個資料庫，操作同一張表

終端1：

select * from students;

------------------------

終端2：

begin;

insert into students(sname) values('張飛');

• 步驟2

終端1：

select * from students;

• 步驟3

終端2：

commit;

------------------------

終端1：

select * from students;

示例2

• 步驟1：開啟兩個終端，連線mysql，使用同一個資料庫，操作同一張表

終端1：

select * from students;

------------------------

終端2：

begin;

insert into students(sname) values('張飛');

• 步驟2

終端1：

select * from students;

• 步驟3

終端2：

rollback;

------------------------

終端1：

select * from students;

---------------------------------------------------------------------------------------------------------

索引

優化SQL語句時，先寫定值，然後寫定範圍；少用or

 

 

檢視索引

show index from 表名；

建立索引

create index indexName on mytable(欄位（長度）)

刪除索引

drop index [indexName] on mytable;