MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）實(shí)訓(xùn)指導(dǎo)-4-5 高級(jí)查詢_第1頁(yè)
MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）實(shí)訓(xùn)指導(dǎo)-4-5 高級(jí)查詢_第2頁(yè)
MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）實(shí)訓(xùn)指導(dǎo)-4-5 高級(jí)查詢_第3頁(yè)
MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）實(shí)訓(xùn)指導(dǎo)-4-5 高級(jí)查詢_第4頁(yè)
MySQL數(shù)據(jù)庫(kù)應(yīng)用實(shí)戰(zhàn)教程（慕課版）（第2版）實(shí)訓(xùn)指導(dǎo)-4-5 高級(jí)查詢_第5頁(yè)
已閱讀5頁(yè)，還剩7頁(yè)未讀， 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說(shuō)明：本文檔由用戶提供并上傳，收益歸屬內(nèi)容提供方，若內(nèi)容存在侵權(quán)，請(qǐng)進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡(jiǎn)介

實(shí)訓(xùn)案例名稱：高級(jí)查詢一、任務(wù)介紹MySQL提供了幾種高級(jí)查詢語(yǔ)句，用于復(fù)雜場(chǎng)景下進(jìn)行多表一起查詢，這樣就會(huì)用到內(nèi)連接查詢、外連接查詢、自然連接查詢、交叉連接查詢和聯(lián)合查詢，以滿足日常業(yè)務(wù)查詢的需求，從而更能體現(xiàn)MySQL強(qiáng)大的功能。二、實(shí)現(xiàn)步驟1.內(nèi)連接查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name

|sex

|age

|password

|+----+-------+------+------+----------+|3 |david

|女

|28|111111

||4 |小紅

|女

|27|123456

||5 |小明

|男|10|123456

||6 |小剛

|男|12|123456

||7 |小王

|男|14|111111

||8 |小綠

|女|34|222222

||9 |曉峰

|男|15|333333

||10 |小影

|女|26|444444

||11 |大梅

|女|27|555555

|+----+-------+------+------+----------+9rowsinset(0.01sec)#創(chuàng)建用戶成績(jī)表scoremysql>CREATETABLEscore(idintnotnull,scorevarchar(255),gradevarchar(255),primarykey(id));QueryOK,0rowsaffected(1.83sec)#插入id為3的用戶的成績(jī)mysql>INSERTINTOscoreVALUES(3,'90','優(yōu)秀');QueryOK,1rowaffected(0.22sec)#插入id為4的用戶的成績(jī)mysql>INSERTINTOscoreVALUES(4,'87','中等');QueryOK,1rowaffected(0.00sec)#插入id為5的用戶的成績(jī)mysql>INSERTINTOscoreVALUES(5,'70','中等');QueryOK,1rowaffected(0.00sec)#插入id為11的用戶的成績(jī)mysql>INSERTINTOscoreVALUES(11,'40','不及格');QueryOK,1rowaffected(0.05sec)#查詢用戶成績(jī)表score的數(shù)據(jù)mysql>SELECT*FROMscore;+----+-------+--------+|id|score

|grade

|+----+-------+--------+|3|90|優(yōu)秀||4|87|中等||5|70|中等||11|40|不及格|+----+-------+--------+4rowsinset(0.01sec)#使用等值內(nèi)連接查詢mysql>SELECT*FROMuseruINNERJOINscoresONu.id=s.id;+----+-------+------+------+----------+----+-------+--------+|id |name

|sex

|age

|password|id

|score|grade

|+----+-------+------+------+----------+----+-------+--------+|3 |david

|女|28|111111|3|90|優(yōu)秀

||4 |小紅

|女|27|123456|4|87|中等

||5 |小明

|男|10|123456|5|70|中等

||11 |大梅

|女|27|555555|11|40|不及格

|+----+-------+------+------+----------+----+-------+--------+4rowsinset(0.01sec)#省略關(guān)鍵字INNERmysql>SELECT*FROMuseruJOINscoresONu.id=s.id;+----+-------+------+------+----------+----+-------+--------+|id |name

|sex

|age

|password

|id|score|grade

|+----+-------+------+------+----------+----+-------+--------+|3 |david

|女|28|111111|3|90|優(yōu)秀||4 |小紅

|女|27|123456|4|87|中等||5 |小明

|男|10|123456|5|70|中等||11 |大梅

|女|27|555555|11|40|不及格|+----+-------+------+------+----------+----+-------+--------+4rowsinset(0.00sec)#使用別名查詢指定字段的數(shù)據(jù)mysql>SELECTu.iduid,,u.sex,u.age,s.score,s.gradeFROMuseruJOINscore

sONu.id=s.id;+-----+-------+------+------+-------+--------+|uid|name

|sex

|age

|score

|grade

|+-----+-------+------+------+-------+--------+|3|david|女|28|90|優(yōu)秀||4|小紅|女|27|87|中等||5|小明|男|10|70|中等

||11|大梅|女|27|40|不及格

|+-----+-------+------+------+-------+--------+4rowsinset(0.01sec)#省略O(shè)N關(guān)鍵字mysql>SELECT*FROMuseruJOINscores;+----+-------+------+------+----------+----+-------+--------+|id|name|sex

|age|password|id

|score

|grade|+----+-------+------+------+----------+----+-------+--------+|3|david|女|28|111111

|11

|40

|不及格

||3|david|女|28|111111

|5

|70

|中等

||3|david|女|28|111111

|4

|87

|中等

||3|david|女|28|111111

|3

|90

|優(yōu)秀

||4|小紅 |女|27|123456

|11

|40

|不及格

||4|小紅 |女|27|123456

|5

|70

|中等

||4|小紅 |女|27|123456

|4

|87

|中等

||4|小紅 |女|27|123456

|3

|90

|優(yōu)秀

||5|小明 |男|10|123456

|11

|40

|不及格

||5|小明 |男|10|123456

|5|70

|中等

||5|小明 |男|10|123456

|4|87

|中等

||5|小明 |男|10|123456

|3|90

|優(yōu)秀

||6|小剛 |男|12|123456

|11|40

|不及格

||6|小剛 |男|12|123456

|5|70

|中等

||6|小剛 |男|12|123456

|4|87

|中等

||6|小剛 |男|12|123456

|3|90

|優(yōu)秀

||7|小王 |男|14|111111

|11|40

|不及格

||7|小王 |男|14|111111

|5|70

|中等

||7|小王 |男|14|111111

|4|87

|中等

||7|小王 |男|14|111111

|3|90

|優(yōu)秀

||8|小綠 |女|34|222222

|11|40

|不及格

||8|小綠 |女|34|222222

|5|70

|中等

||8|小綠 |女|34|222222

|4|87

|中等

||8|小綠 |女|34|222222

|3|90

|優(yōu)秀

||9|曉峰 |男|15|333333

|11|40

|不及格

||9|曉峰 |男|15|333333

|5|70

|中等

||9|曉峰 |男|15|333333

|4|87

|中等

||9|曉峰 |男|15|333333

|3|90

|優(yōu)秀

||10|小影 |女|26|444444

|11|40

|不及格

||10|小影 |女|26|444444

|5|70

|中等

||10|小影 |女|26|444444

|4|87

|中等

||10|小影 |女|26|444444

|3|90

|優(yōu)秀

||11|大梅 |女|27|555555

|11|40

|不及格

||11|大梅 |女|27|555555

|5|70

|中等

||11|大梅 |女|27|555555

|4|87

|中等

||11|大梅 |女|27|555555

|3|90

|優(yōu)秀

|+----+-------+------+------+----------+----+-------+--------+36rowsinset(0.01sec)#使用非等值內(nèi)連接查詢，查詢u.id>s.id的數(shù)據(jù)mysql>SELECT*FROMuseruINNERJOINscoresONu.id>s.id;+----+------+------+------+----------+----+-------+-------+|id |name|sex|age|password|id|score|grade|+----+------+------+------+----------+----+-------+-------+|4 |小紅|女|27|123456|3|90|優(yōu)秀||5 |小明|男|10|123456|3|90|優(yōu)秀||6 |小剛|男|12|123456|3|90|優(yōu)秀||7 |小王|男|14|111111|3|90|優(yōu)秀||8 |小綠|女|34|222222|3|90|優(yōu)秀||9 |曉峰|男|15|333333|3|90|優(yōu)秀||10 |小影|女|26|444444|3|90|優(yōu)秀||11 |大梅|女|27|555555|3|90|優(yōu)秀||5 |小明|男|10|123456|4|87|中等||6 |小剛|男|12|123456|4|87|中等||7 |小王|男|14|111111|4|87|中等||8 |小綠|女|34|222222|4|87|中等||9 |曉峰|男|15|333333|4|87|中等||10 |小影|女|26|444444|4|87|中等||11 |大梅|女|27|555555|4|87|中等||6 |小剛|男|12|123456|5|70|中等||7 |小王|男|14|111111|5|70|中等||8 |小綠|女|34|222222|5|70|中等||9 |曉峰|男|15|333333|5|70|中等||10 |小影|女|26|444444|5|70|中等||11 |大梅|女|27|555555|5|70|中等|+----+------+------+------+----------+----+-------+-------+21rowsinset(0.01sec)mysql>2.外連接查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#查詢用戶成績(jī)表scoremysql>SELECT*FROMscore;+----+-------+--------+|id|score

|grade

|+----+-------+--------+|3 |90|優(yōu)秀||4 |87|中等||5 |70|中等||11 |40|不及格|+----+-------+--------+4rowsinset(0.00sec)#左外連接，省略O(shè)UTERmysql>SELECT*FROMuseruLEFTJOINscoresONu.id=s.id;+----+-------+------+------+----------+------+-------+--------+|id |name|sex|age|password|id|score|grade|+----+-------+------+------+----------+------+-------+--------+|3 |david

|女

|28|111111|3|90|優(yōu)秀||4 |小紅

|女

|27|123456|4|87|中等||5 |小明

|男

|10|123456|5|70|中等||6 |小剛

|男

|12|123456|NULL|NULL|NULL||7 |小王

|男

|14|111111|NULL|NULL|NULL||8 |小綠

|女

|34|222222|NULL|NULL|NULL||9 |曉峰

|男

|15|333333|NULL|NULL|NULL||10 |小影

|女

|26|444444|NULL|NULL|NULL||11 |大梅

|女

|27|555555|11|40|不及格|+----+-------+------+------+----------+------+-------+--------+9rowsinset(0.01sec)#右外連接，省略O(shè)UTERmysql>SELECT*FROMuseruRIGHTJOINscoresONu.id=s.id;+------+-------+------+------+----------+----+-------+--------+|id

|name

|sex

|age

|password

|id

|score|grade

|+------+-------+------+------+----------+----+-------+--------+|3|david|女|28|111111|3|90|優(yōu)秀

||4|小紅|女|27|123456|4|87|中等

||5|小明|男|10|123456|5|70|中等

||11|大梅|女|27|555555|11|40|不及格|+------+-------+------+------+----------+----+-------+--------+4rowsinset(0.00sec)#左外連接，不省略O(shè)UTERmysql>SELECT*FROMuseruLEFTOUTERJOINscoresONu.id=s.id;+----+-------+------+------+----------+------+-------+--------+|id |name

|sex|age

|password

|id

|score

|grade

|+----+-------+------+------+----------+------+-------+--------+|3 |david

|女|28|111111|3|90|優(yōu)秀||4 |小紅|女|27|123456|4|87|中等||5 |小明|男|10|123456|5|70|中等||6 |小剛|男|12|123456|NULL|NULL|NULL||7 |小王|男|14|111111|NULL|NULL|NULL||8 |小綠|女|34|222222|NULL|NULL|NULL||9 |曉峰|男|15|333333|NULL|NULL|NULL||10 |小影|女|26|444444|NULL|NULL|NULL||11 |大梅|女|27|555555|11|40|不及格|+----+-------+------+------+----------+------+-------+--------+9rowsinset(0.00sec)#右外連接，不省略O(shè)UTERmysql>SELECT*FROMuseruRIGHTOUTERJOINscoresONu.id=s.id;+------+-------+------+------+----------+----+-------+--------+|id

|name

|sex

|age

|password|id|score|grade

|+------+-------+------+------+----------+----+-------+--------+|3

|david|女|28|111111|3|90|優(yōu)秀

||4

|小紅|女|27|123456|4|87|中等

||5

|小明|男|10|123456|5|70|中等

||11

|大梅|女|27|555555|11|40|不及格

|+------+-------+------+------+----------+----+-------+--------+4rowsinset(0.00sec)mysql>3.自然連接查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged #查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id |name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#查詢用戶成績(jī)表scoremysql>SELECT*FROMscore;+----+-------+--------+|id |score|grade|+----+-------+--------+|3 |90|優(yōu)秀||4 |87|中等||5 |70|中等||11 |40|不及格|+----+-------+--------+4rowsinset(0.00sec)#自然內(nèi)連接，查詢出兩張表同時(shí)滿足的數(shù)據(jù)mysql>SELECT*FROMuserNATURALJOINscore;+----+-------+------+------+----------+-------+--------+|id |name|sex|age|password|score|grade

|+----+-------+------+------+----------+-------+--------+|3 |david|女|28|111111|90|優(yōu)秀

||4 |小紅|女|27|123456|87|中等

||5 |小明|男|10|123456|70|中等

||11 |大梅|女|27|555555|40|不及格

|+----+-------+------+------+----------+-------+--------+4rowsinset(0.01sec)#自然左外連接，以左表user為主查詢數(shù)據(jù)，不滿足的數(shù)據(jù)使用NULLmysql>SELECT*FROMuserNATURALLEFTJOINscore;+----+-------+------+------+----------+-------+--------+|id |name|sex

|age

|password

|score

|grade

|+----+-------+------+------+----------+-------+--------+|3 |david|女|28|111111

|90|優(yōu)秀

||4 |小紅|女|27|123456

|87|中等

||5 |小明|男|10|123456

|70|中等

||6 |小剛|男|12|123456

|NULL|NULL

||7 |小王|男|14|111111

|NULL|NULL

||8 |小綠|女|34|222222

|NULL|NULL

||9 |曉峰|男|15|333333

|NULL|NULL

||10 |小影|女|26|444444

|NULL|NULL

||11 |大梅|女|27|555555

|40|不及格

|+----+-------+------+------+----------+-------+--------+9rowsinset(0.00sec)#自然右外連接，以右表score為主，查詢滿足的數(shù)據(jù)mysql>SELECT*FROMuserNATURALRIGHTJOINscore;+----+-------+--------+-------+------+------+----------+|id|score|grade|name|sex|age|password|+----+-------+--------+-------+------+------+----------+|3 |90|優(yōu)秀|david|女|28|111111

||4 |87|中等|小紅|女|27|123456

||5 |70|中等|小明|男|10|123456

||11 |40|不及格|大梅|女|27|555555

|+----+-------+--------+-------+------+------+----------+4rowsinset(0.00sec)#外連接模擬自然左外連接mysql>SELECT*FROMuserLEFTJOINscoreusing(id);+----+-------+------+------+----------+-------+--------+|id|name|sex|age|password|score|grade|+----+-------+------+------+----------+-------+--------+|3 |david|女|28|111111|90|優(yōu)秀||4 |小紅|女|27|123456|87|中等||5 |小明|男|10|123456|70|中等||6 |小剛|男|12|123456|NULL|NULL||7 |小王|男|14|111111|NULL|NULL||8 |小綠|女|34|222222|NULL|NULL||9 |曉峰|男|15|333333|NULL|NULL||10 |小影|女|26|444444|NULL|NULL||11 |大梅|女|27|555555|40|不及格|+----+-------+------+------+----------+-------+--------+9rowsinset(0.00sec)mysql>4.交叉連接查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommand

lineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|3 |david|女|28|111111||4 |小紅|女|27|123456||5 |小明|男|10|123456||6 |小剛|男|12|123456||7 |小王|男|14|111111||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#查詢用戶成績(jī)表scoremysql>SELECT*FROMscore;+----+-------+--------+|id |score

|grade

|+----+-------+--------+|3 |90|優(yōu)秀

||4 |87|中等

||5 |70|中等

||11 |40|不及格

|+----+-------+--------+4rowsinset(0.00sec)#交叉連接查詢，使用關(guān)鍵字CROSSJOINmysql>SELECT*FROMuserCROSSJOINscore;+----+-------+------+------+----------+----+-------+--------+|id

|name

|sex

|age

|password|id|score|grade

|+----+-------+------+------+----------+----+-------+--------+|3|david|女|28

|111111|11|40|不及格||3|david|女|28

|111111|5|70|中等||3|david|女|28

|111111|4|87|中等||3|david|女|28

|111111|3|90|優(yōu)秀||4|小紅|女|27

|123456|11|40|不及格||4|小紅|女|27

|123456|5|70|中等||4|小紅|女|27

|123456|4|87|中等||4|小紅|女|27

|123456|3|90|優(yōu)秀|+----+-------+------+------+----------+----+-------+--------+36rowsinset(0.00sec)#交叉連接查詢，F(xiàn)ROM兩張表mysql>SELECT*FROMuser,score;+----+-------+------+------+----------+----+-------+--------+|id

|name

|sex

|age

|password

|id|score|grade

|+----+-------+------+------+----------+----+-------+--------+|3|david|女|28|111111|11|40|不及格||3|david|女|28|111111|5|70|中等||3|david|女|28|111111|4|87|中等||3|david|女|28|111111|3|90|優(yōu)秀||4|小紅|女|27|123456|11|40|不及格||4|小紅|女|27|123456|5|70|中等||4|小紅|女|27|123456|4|87|中等||4|小紅|女|27|123456|3|90|優(yōu)秀|+----+-------+------+------+----------+----+-------+--------+36rowsinset(0.00sec)mysql>5.聯(lián)合查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫(kù)mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+-------+------------+--------+|id |name

|sex

|age

|password

|phone

|loginName

|remark

|+----+-------+------+------+----------+-------+------------+--------+|3 |david

|女|28|111111

|NULL|david

|baann

||4 |小紅

|女|27|123456

|NULL|xiaohong

|black

||5 |小明

|男|10

|123456

|NULL|xiaoming |berry||6 |小剛|男|12

|123456

|NULL|xiaogang |banner||7 |小王|男|14

|111111

|NULL|xiaowang |banana||8 |小綠|女|34

|222222

|NULL|xiaolv |car

||9 |曉峰|男|

溫馨提示

  • 1. 本站所有資源如無(wú)特殊說(shuō)明，都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請(qǐng)下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等，如果需要附件，請(qǐng)聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙，網(wǎng)頁(yè)內(nèi)容里面會(huì)有圖紙預(yù)覽，若沒(méi)有圖紙預(yù)覽就沒(méi)有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫(kù)網(wǎng)僅提供信息存儲(chǔ)空間，僅對(duì)用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理，對(duì)用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯，并不能對(duì)任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容，請(qǐng)與我們聯(lián)系，我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對(duì)自己和他人造成任何形式的傷害或損失。

評(píng)論

0/150

提交評(píng)論