Mysql - 查詢之關聯查詢

查詢這塊是重中之重, 關係到系統反應時間. 項目做到後期, 都是要做性能測試和性能優化的, 優化的時候, 資料庫這塊是一個大頭.

sql格式: select 列名/* from 表名 where 條件 group by 列 having 條件 order by 列 asc/desc;

這裡牽涉到一個查詢執行順序的問題. 

單表查詢執行順序: 

select sex, count(Sex) as count from tch_teacher where id > 15 group by Sex having count > 5 order by Sex asc limit 1;

1-> from 表 : 首先拿到表tch_teacher

2-> where 條件 : 根據where後面的條件篩選一遍數據集合A

3-> group by 分組 : 對篩選出的數據A, 根據group by後面的列進行分組, 得到數據集B

4-> having 篩選 : 對數據集B進行進一步篩選, 得到數據集C

5-> select 數據 : 這裡有四步

                         第一步 : 根據select後面的列名, 去數據集C中取數據. 得到數據集D

                         第二步 : 對數據集D中的數據進行去重操作(這一步是建立在 sql中有distinct 情況下), 得到數據集E

                         第三步 : 對數據集E進行排序操作, 得到數據集F

                         第四步 : 對數據集F進行截取數據操作, 得到最終的數據集(執行 limit 10 操作)

 在多表的時候, 只是第1步有了變化, 多了一個連表操作, 別的沒啥變化.

一、連表查詢

1. 交叉連接 -- 笛卡爾乘積  cross join

select * from tch_teacher cross join tch_contact

這種連接方式, 沒見人用過.  如果tch_teacher,tch_contact表各有10條數據, 那麼連接的結果, 就是 10 x 10 = 100 條數據. 

在mysql 中, cross join 後面是可以跟 on 和 where 的, 加上之後, 其實跟 inner join 是一樣的

2. 內連接 -- inner join 

內連接在不加on的情況下, 也是去求笛卡爾乘積. 不加on的用法並不推薦使用, 容易造成記憶體溢出的情況. 加on的時候, 在連表的時候, 就會對數據進行篩選, 以此來縮減有效數據範圍

select * from tch_teacher inner join tch_contact 

從上面的sql和圖片來看, inner join 的時候, 可以不加on, 也能得到一個結果, 而且這個結果和交叉連接的結果是一樣的. 

 這裡還有另外兩種寫法:

select * from tch_teacher,tch_contact

select * from tch_teacher join tch_contact

得到的結果是一樣的. 

3. 外連接 -- left/right join on 

這裡我特意加了一個on在上面, 因為不加on是會報錯的.

left join 稱之為左連接, 連接以左側表數據為準, 當右表沒有數據與之匹配的時候, 則會用null填補

right join 稱之為右連接, 與 left join 相反, 這個是以右表為準

先看下效果吧

select * from tch_teacher left join tch_contact on tch_teacher.Id = tch_contact.TId;

select * from tch_teacher right join tch_contact on tch_teacher.Id = tch_contact.TId;

其實這裡還有一個full join , 不過mysql裡面的full join, 著實有些讓人無語, 反正我沒這麼用過, 略過不表了.

這裡我做了一個小測試, 這裡的數據, tch_contact的tid值, 我取的是0~100000之間的隨機數

select * from tch_teacher inner join tch_contact on tch_teacher.Id = tch_contact.TId ;

select * from tch_teacher LEFT join tch_contact on tch_teacher.Id = tch_contact.TId ;

這裡面的值, 是我反覆運行之後, 在一些離散值裡面, 取得比較靠近中心點的值.

4. 自己連自己

mysql裡面, 有一個比較好用的功能, 就是自己連自己. 我再tch_teacher表裡面加入一列, CreateBy,  存放的是這個表的Id值

select a.*, b.Name as CreateByName from tch_teacher a
left join tch_teacher b on a.createby = b.id

能得到以下結果:

 二、union查詢

除了把幾個表通過內部關係拼成一個表結果, 還可以, 把多個表的查詢表結果拼成一個表結果. 所使用的方法就是union.

這裡要註意的是, 列的順序. 如果害怕列的順序不一致不好排查, 可以把表結果的列名都重命名為相同的.

select Id, Sex, BId, `No`, Name, CreateDate from tch_teacher

union

select 0 as Id, Sex, BId, `No`, Name, CreateDate from tch_teacher_temp

union是會對最後的表結果進行去重操作的, 如果我不想去重, 只想快速得到拼接的結果, 可以使用 union all 來拼接. 

參考:

Mysql架構總覽