Mysql on,in,as,where的區(qū)別

答：Where查詢(xún)條件，on內(nèi)外連接時(shí)候用,as作為別名,in查詢(xún)某值是否在某條件里

創(chuàng)建2個(gè)表：student，score

student：

score：

where

SELECT * FROM student WHERE s_sex='男'

例如：on

SELECT * FROM student LEFT JOIN score?on student.s_id=score.s_id;

on和where組合：

SELECT * FROM student LEFT JOIN score on student.s_id=score.s_id WHERE s_name='趙雷'?

例如：in

SELECT * FROM score WHERE s_id in (SELECT s_id FROM student WHERE s_name='趙雷')

as

select * from score as a LEFT JOIN student as b on a.s_id=b.s_id where s_name='趙雷'

Mysql語(yǔ)句問(wèn)題解決

1、left join數(shù)據(jù)篩選問(wèn)題

on后面的條件只能對(duì)left join右邊的表進(jìn)行篩選，左表匹配不到右表數(shù)據(jù)會(huì)在原右表位置處顯示null，left join左邊的表數(shù)據(jù)不受約束，將on后的條件加到where后會(huì)對(duì)所有數(shù)據(jù)進(jìn)行篩選。

2、相同數(shù)據(jù)重復(fù)篩選使用問(wèn)題

with <name> as()

mysql內(nèi)可以使用with as生成臨時(shí)表，<name>為臨時(shí)表的名字，使用如下：

with arc as(?
? ? select id,arc.title,update_time,is_top,cId,pid,name_id from article arc where is_del = 0?
)?
select * from arc

with...as的作用范圍只有一次sql執(zhí)行的時(shí)間，執(zhí)行過(guò)后就不再存在，根據(jù)例子我們本要處理article表，但表里的數(shù)據(jù)并非都是我們需要的，所系先篩選建立了一個(gè)臨時(shí)表arc，我們會(huì)對(duì)arc進(jìn)行操作。

如果只是上述例子的簡(jiǎn)單操作是沒(méi)必要使用with...as的，但是當(dāng)我們需要將article表與其他表進(jìn)行聯(lián)查甚至嵌套時(shí)，會(huì)出現(xiàn)要多次進(jìn)行is_del = 0的判斷，最終出來(lái)的sql語(yǔ)句可能個(gè)十分復(fù)雜，并且極易出錯(cuò)，但使用arc就不需要在對(duì)數(shù)據(jù)進(jìn)行重復(fù)篩選了。

with...as里的sql可以更復(fù)雜些，比如article表里有name_id，但更多時(shí)候我們希望使用name，我們可以預(yù)先在with...as內(nèi)查找好，再使用臨時(shí)表去做其他操作。

3、根據(jù)某個(gè)字段排序取每個(gè)類(lèi)別最后三條數(shù)據(jù)或前三條數(shù)據(jù)

這算是個(gè)比較經(jīng)典的一個(gè)問(wèn)題了，我初學(xué)，只會(huì)一種解題方法，但會(huì)盡力講的簡(jiǎn)單通俗點(diǎn)。

示例：

select * from (?
select cId,title,content(?
? ? select count(*)+1 from arc a1 where (a1.cId = a2.cId) and a1.updateTime > a2.updateTime?
)updateTimeSort from arc a2?
) a3?
where updateTimeSort <= 3 order by cId,updateTime desc

示例中cId是類(lèi)別id，updateTime 是更新時(shí)間，解決問(wèn)題是選取arc內(nèi)每個(gè)類(lèi)別最晚更新的的三條數(shù)據(jù)，就像新聞的首頁(yè)需要為每個(gè)分類(lèi)選出最新的三條新聞，按照數(shù)據(jù)庫(kù)里的數(shù)據(jù)我們可以使用排序 order by cId,updateTime desc 對(duì)數(shù)據(jù)按類(lèi)別和更新時(shí)間進(jìn)行排序，但去取每個(gè)類(lèi)別的特定幾條數(shù)據(jù)，現(xiàn)有數(shù)據(jù)庫(kù)是做不到的，因此我們可以添加一個(gè)臨時(shí)字段。

updateTimeSort 它表示的是每個(gè)類(lèi)別中每個(gè)子項(xiàng)在這個(gè)類(lèi)別中的排序，在當(dāng)前問(wèn)題中這個(gè)臨時(shí)字段應(yīng)該是和字段 updateTime 相關(guān)的，根據(jù)更新時(shí)間為類(lèi)別中的每個(gè)子項(xiàng)排出順序。

如示例代碼，我們能找到a1和a2這兩個(gè)表，他們都是arc表的別稱(chēng)，通過(guò)子查詢(xún)的形式結(jié)合在一起，以a2為主，去a1表內(nèi)查找類(lèi)別和a2當(dāng)前數(shù)據(jù)相同的，并且更新時(shí)間晚于a2當(dāng)前數(shù)據(jù)的數(shù)據(jù)數(shù)量，能看到 count(*)+1 也就是數(shù)量加一了，不加一也可以，只是當(dāng)一條數(shù)據(jù)在它所處類(lèi)別更新時(shí)間最晚時(shí)count(*) 的值是0，若果使用count(*)+1 我們就可以將數(shù)據(jù)從1開(kāi)始排序。

最終我們只要選取 updateTimeSort <= 3 的數(shù)據(jù)即可，如果想要篩選最早發(fā)布的新聞也只需要將updateTimeSort 的篩選邏輯變更一下即可，在示例代碼中即將

a1.updateTime > a2.updateTime 更改為 a1.updateTime < a2.updateTime

可以看到示例代碼中還有一個(gè)表a3，它其實(shí)時(shí)一個(gè)臨時(shí)表，前面我們了解了with..as可以生成臨時(shí)表，也重這次代碼中可以看出，臨時(shí)表也可以以另一種形式存在，with...as我們只有當(dāng)sql復(fù)雜時(shí)才會(huì)使用，一般來(lái)說(shuō)現(xiàn)在這種方式能幫我們解決不少問(wèn)題了，各有優(yōu)劣，看情況使用。

4、業(yè)務(wù)邏輯書(shū)寫(xiě)位置問(wèn)題

接觸sql多了會(huì)發(fā)現(xiàn)，sql其實(shí)能幫我們解決一定的業(yè)務(wù)問(wèn)題，明顯的有sql的存儲(chǔ)過(guò)程和方法，對(duì)sql語(yǔ)句的批量處理其實(shí)在一定程度上幫我們解決一定的業(yè)務(wù)問(wèn)題，但缺點(diǎn)也很明顯，當(dāng)新手接觸這個(gè)項(xiàng)目時(shí)他很難搞清楚某個(gè)功能到底是如何實(shí)現(xiàn)的，不利于維護(hù)。

一般來(lái)說(shuō)我們解決業(yè)務(wù)是在server層，有時(shí)會(huì)使用sql解決一些問(wèn)題，但很少，在sever處理受制于計(jì)算機(jī)硬件，在數(shù)據(jù)庫(kù)處理受制于數(shù)據(jù)庫(kù)性能，相比之下，計(jì)算機(jī)硬件更易于擴(kuò)展，因此還是不推薦大量使用sql解決問(wèn)題的。

例如上個(gè)問(wèn)題：根據(jù)某個(gè)字段排序取每個(gè)類(lèi)別最后三條數(shù)據(jù)或前三條數(shù)據(jù)問(wèn)題，雖然問(wèn)題基本解決但讓存在一些 ‘bug’，例如排序時(shí)會(huì)產(chǎn)生1、2、3、3、4這種排序，這是因?yàn)橥瑐€(gè)類(lèi)別內(nèi)有兩條數(shù)據(jù)更新時(shí)間重復(fù)了，那我們直觀想法(還是要看個(gè)人經(jīng)驗(yàn)值)應(yīng)該是，既然問(wèn)題出在數(shù)據(jù)庫(kù)，那應(yīng)該在數(shù)據(jù)庫(kù)查詢(xún)的時(shí)候就解決這個(gè)問(wèn)題，但事實(shí)上，讓數(shù)據(jù)庫(kù)去解決并不好解決，數(shù)據(jù)庫(kù)的強(qiáng)項(xiàng)在于各種搜索算法，不在于邏輯處理，因此我們就要轉(zhuǎn)移到server層處理，會(huì)有不少人陷于這個(gè)坑，花費(fèi)大量時(shí)間去找辦法讓數(shù)據(jù)庫(kù)去處理這類(lèi)問(wèn)題，但其實(shí)就算數(shù)據(jù)庫(kù)處理得了，它也不一定有server層處理的效率高，當(dāng)然如果是為了學(xué)習(xí)更多東西，這些時(shí)間也是值得花的，但是這種解題思路還是要改變下的。將1、2、3、3、4問(wèn)題交給server處理也就是利用java等高級(jí)語(yǔ)言處理這種問(wèn)題，相信熟用這些語(yǔ)言的開(kāi)發(fā)者解決這些問(wèn)題都是小case了。

5、查找另一表內(nèi)和本表相關(guān)字段的數(shù)量

先復(fù)習(xí)下知識(shí)：用過(guò)count函數(shù)的人都清楚一旦使用count這類(lèi)聚合函數(shù)，不做其他處理數(shù)據(jù)就會(huì)歸為一行數(shù)據(jù)，但很多時(shí)候我們并不期望這樣的結(jié)果，以此就要想些辦法能用聚合函數(shù)，也能獲取很多數(shù)據(jù)，我常用的是利用group by分組。

回歸問(wèn)題，現(xiàn)有(現(xiàn)不討論表是否合理)文章表(id,title,content)有文章id，標(biāo)題，文章內(nèi)容三個(gè)字段，點(diǎn)贊收藏表(id,arc_id,fav,like)有表id，文章id，收藏字段(0未收藏，1收藏)，點(diǎn)贊字段(0未點(diǎn)贊，1點(diǎn)贊)，現(xiàn)要查詢(xún)文章表內(nèi)每篇文章的點(diǎn)贊收藏?cái)?shù)，sql語(yǔ)句：

select art.title,art.content,?
count(case afl.fav when 1 then 1 end) as collectNum,?
count(case afl.like when 1 then 1 end) as likeNum?
from article art?
left join article_favor_like afl on afl.arc_id = art.id?
group by afl.arc_id //這是關(guān)鍵

如果沒(méi)有g(shù)roup by afl.arc_id 后果就是，查出來(lái)一行數(shù)據(jù)，數(shù)據(jù)還牛頭不對(duì)馬嘴，但通過(guò)對(duì)文章收藏表中的文章id進(jìn)行分組就可以針對(duì)每個(gè)文章id查詢(xún)數(shù)據(jù)，這樣left join時(shí)右表就有每個(gè)文章id對(duì)相應(yīng)的收藏?cái)?shù)與點(diǎn)贊數(shù)，而不是表內(nèi)所有點(diǎn)贊數(shù)和收藏?cái)?shù)，最終數(shù)據(jù)也是我們所需的。

6、關(guān)于union的使用

例子：

select id,title,content,1 isArc from arc?
union?
select id,name,content,0 isArc from news

• 使用union進(jìn)行的是上下整合
• 被聯(lián)合的數(shù)據(jù)列數(shù)要求一致
• 列數(shù)相同，數(shù)據(jù)類(lèi)型不同會(huì)自動(dòng)進(jìn)行數(shù)據(jù)類(lèi)型轉(zhuǎn)換
• 聯(lián)合后的列的名字由聯(lián)合中第一次出現(xiàn)的列名為依據(jù)，即使后續(xù)被聯(lián)合數(shù)據(jù)有自己的列名也不會(huì)使用，在例子中最終列名為：id,title,content,name等列名不會(huì)使用，因此使用union一般配合別名使用統(tǒng)一結(jié)果。
• 有時(shí)候會(huì)區(qū)分?jǐn)?shù)據(jù)是哪個(gè)表的，可以通過(guò)附加額外的字段來(lái)區(qū)別，就像例子中的isArc字段，news表中的isArc可以不寫(xiě)，原因也就是第4條，最終列名由第一次出現(xiàn)的列名決定，后續(xù)數(shù)據(jù)列名有沒(méi)有都可以。

7、limit的巧用

limit一般用于分頁(yè)，功能是獲取指定區(qū)間內(nèi)的數(shù)據(jù)，因此我們也可以用它來(lái)減少數(shù)據(jù)庫(kù)的查詢(xún)，例子：

select * from arc where id = 12 limit 1

數(shù)據(jù)庫(kù)查詢(xún)由索引還好，沒(méi)有索引是要遍歷數(shù)據(jù)庫(kù)的，有些數(shù)據(jù)經(jīng)由條件篩選在邏輯上應(yīng)該是唯一的，使用limit 1可以使數(shù)據(jù)庫(kù)查詢(xún)到該數(shù)據(jù)時(shí)不再搜索，減少數(shù)據(jù)庫(kù)搜索次數(shù)，但這種方法僅是一種技巧，想大幅度優(yōu)化sql還要另想辦法。

8、update ignore和insert ignore的使用

//標(biāo)題是唯一索引，'新標(biāo)題'存在則更新操作不執(zhí)行?
update ignore arc set title = '新標(biāo)題'?
?
//標(biāo)題是唯一索引，'標(biāo)題1號(hào)'存在則插入操作不執(zhí)行?
insert ignore into arc values(null,'標(biāo)題1號(hào)','文章內(nèi)容')

有這種需求，數(shù)據(jù)存在時(shí)不執(zhí)行任何操作，不存在則更新或插入，一個(gè)辦法是使用ingore，它會(huì)忽略數(shù)據(jù)庫(kù)報(bào)錯(cuò)，而數(shù)據(jù)庫(kù)執(zhí)行原子操作時(shí)報(bào)錯(cuò)是會(huì)回滾的，因此只要我們給數(shù)據(jù)加上主鍵或唯一索引，當(dāng)被更新字段或插入字段與原有數(shù)據(jù)沖突時(shí)會(huì)報(bào)錯(cuò)，但因?yàn)閕ngore會(huì)忽視這種報(bào)錯(cuò)，后端也就不會(huì)報(bào)錯(cuò)，sql也未執(zhí)行，達(dá)到了目的，有人會(huì)對(duì)報(bào)錯(cuò)敏感，其實(shí)也沒(méi)什么，報(bào)錯(cuò)也是在檢查數(shù)據(jù)是發(fā)現(xiàn)不合理之處給的一個(gè)提醒或警告，對(duì)數(shù)據(jù)庫(kù)無(wú)害的。

9、mysql存在更新，不存在則插入

區(qū)別于上面那個(gè)需求，這個(gè)是當(dāng)插入的數(shù)據(jù)存在時(shí)更新數(shù)據(jù)，不再是不做任何操作，例子：

//本例子中title不是唯一索引，id是主鍵?
insert into arc values(1,'標(biāo)題1號(hào)','文章內(nèi)容')?
on duplicate key update title='標(biāo)題1號(hào)'
//若要更新多個(gè)字段使用','隔開(kāi)，例：title='標(biāo)題1號(hào)',content='文章內(nèi)容'

在例子中，當(dāng)id為1的數(shù)據(jù)存在時(shí)，更新標(biāo)題和內(nèi)容，不存在則插入，如果執(zhí)行更新操作，未設(shè)置新值的字段保持原來(lái)的值。

還有一個(gè)REPLACE INTO也可以達(dá)到這種效果，區(qū)別在于，REPLACE INTO更新時(shí)是先刪除后插入會(huì)破壞原有索引，id為3的數(shù)據(jù)更新時(shí)會(huì)刪除插入id為4的數(shù)據(jù)，未更新新值的字段設(shè)置為默認(rèn)值或null。

無(wú)論是兩個(gè)中的哪種方式判斷數(shù)據(jù)是否存在的依據(jù)都是主鍵和唯一索引。

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論