版權(quán)說明：本文檔由用戶提供并上傳，收益歸屬內(nèi)容提供方，若內(nèi)容存在侵權(quán)，請進(jìn)行舉報(bào)或認(rèn)領(lǐng)
文檔簡介
實(shí)訓(xùn)案例名稱：基本查詢語法一、任務(wù)介紹MySQL使用關(guān)鍵字SELECT來進(jìn)行查詢。實(shí)現(xiàn)步驟1.查詢數(shù)據(jù)#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user中的數(shù)據(jù)，顯示所有字段的數(shù)據(jù)mysql>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)#查詢用戶表user中的數(shù)據(jù)，只顯示name字段的數(shù)據(jù)mysql>SELECTnameFROMuser;+-------+|name
|+-------+|david||小紅||小明||小剛||小王||小綠||曉峰||小影||大梅|+-------+9rowsinset(0.00sec)#按條件進(jìn)行查詢，查詢性別為男的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex='男';+----+------+------+------+----------+|id|name|sex
|age|password
|+----+------+------+------+----------+|5|小明|男
|10|123456
||6|小剛|男
|12|123456
||7|小王|男
|14|111111
||9|曉峰|男
|15|333333
|+----+------+------+------+----------+4rowsinset(0.00sec)#查詢年齡大于20的數(shù)據(jù)mysql>SELECT*FROMuserWHEREage>20;+----+-------+------+------+----------+|id|name
|sex|age|password
|+----+-------+------+------+----------+|3
|david
|女|28|111111||4
|小紅
|女|27|123456||8
|小綠
|女|34|222222||10|小影
|女|26|444444||11
|大梅
|女|27|555555|+----+-------+------+------+----------+5rowsinset(0.00sec)#查詢總共有多少條記錄mysql>SELECTcount(*)FROMuser;+----------+|count(*)|+----------+|9|+----------+1rowinset(0.08sec)#查詢年齡總和mysql>SELECTsum(age)FROMuser;+----------+|sum(age)|+----------+|193|+----------+1rowinset(0.05sec)#查詢年齡最大的數(shù)據(jù)mysql>SELECTmax(age)FROMuser;+----------+|max(age)|+----------+|34
|+----------+1rowinset(0.04sec)#按性別進(jìn)行分組查詢mysql>SELECTsex,count(*)FROMuserGROUPBYsex;+------+----------+|sex
|count(*)
|+------+----------+|女|5
||男|4
|+------+----------+2rowsinset(0.01sec)#按年齡降序排列查詢結(jié)果mysql>SELECT*FROMuserORDERBYageDESC;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|8 |小綠 |女|34|222222||3 |david |女|28|111111||4 |小紅 |女|27|123456||11 |大梅 |女|27|555555||10 |小影 |女|26|444444||9 |曉峰 |男|15|333333||7 |小王 |男|14|111111||6 |小剛 |男|12|123456||5 |小明 |男|10|123456|+----+-------+------+------+----------+9rowsinset(0.00sec)#按年齡升序排列查詢結(jié)果mysql>SELECT*FROMuserORDERBYageASC;+----+-------+------+------+----------+|id|name
|sex
|age
|password|+----+-------+------+------+----------+|5 |小明
|男|10|123456||6 |小剛
|男|12|123456||7 |小王
|男|14|111111||9 |曉峰
|男|15|333333||10 |小影
|女|26|444444||4 |小紅
|女|27|123456||11 |大梅
|女|27|555555||3 |david
|女|28|111111||8 |小綠
|女|34|222222|+----+-------+------+------+----------+9rowsinset(0.00sec)#查詢前5條記錄mysql>SELECT*FROMuserLIMIT0,5;+----+-------+------+------+----------+|id|name|sex
|age|password
|+----+-------+------+------+----------+|3|david
|女|28|111111
||4|小紅|女|27|123456
||5|小明|男|10|123456
||6|小剛|男|12|123456
||7|小王|男|14|111111
|+----+-------+------+------+----------+5rowsinset(0.00sec)mysql>實(shí)訓(xùn)案例名稱：數(shù)據(jù)過濾一、任務(wù)介紹數(shù)據(jù)過濾用在WHERE表達(dá)式里，常用的有基本查詢過濾、條件查詢過濾、模糊查詢過濾、字段控制查詢過濾以及正則表達(dá)式查詢過濾。二、實(shí)現(xiàn)步驟1.基本查詢過濾#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢所有字段的數(shù)據(jù)mysql>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)#查詢指定多個字段的數(shù)據(jù)mysql>SELECTname,sex,ageFROMuser;+-------+------+------+|name
|sex|age
|+-------+------+------+|david
|女|28||小紅|女|27||小明|男|10||小剛|男|12||小王|男|14||小綠|女|34||曉峰|男|15||小影|女|26||大梅|女|27|+-------+------+------+9rowsinset(0.00sec)2.條件查詢過濾#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#無條件查詢所有數(shù)據(jù)mysql>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)#使用AND查詢性別為女且年齡大于27的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex='女'ANDage>27;+----+-------+------+------+----------+|id|name
|sex|age|password
|+----+-------+------+------+----------+|3
|david
|女|28|111111
||8
|小綠
|女|34|222222
|+----+-------+------+------+----------+2rowsinset(0.00sec)#使用OR查詢姓名為小影或者姓名為小明的數(shù)據(jù)mysql>SELECT*FROMuserWHEREname='小影'ORname='小明';+----+------+------+------+----------+|id|name|sex|age|password|+----+------+------+------+----------+|5 |小明|男|10|123456||10 |小影|女|26|444444|+----+------+------+------+----------+2rowsinset(0.00sec)#使用IN查詢id在（3,5,7）范圍內(nèi)的數(shù)據(jù)mysql>SELECT*FROMuserWHEREidIN(3,5,7);+----+-------+------+------+----------+|id|name|sex|age|password
|+----+-------+------+------+----------+|3 |david|女|28|111111
||5 |小明|男|10|123456
||7 |小王|男|14|111111
|+----+-------+------+------+----------+3rowsinset(0.00sec)#使用NOTIN查詢id不在（3,5,7）范圍內(nèi)的數(shù)據(jù)mysql>SELECT*FROMuserWHEREidNOTIN(3,5,7);+----+------+------+------+----------+|id|name|sex|age|password|+----+------+------+------+----------+|4 |小紅|女|27|123456||6 |小剛|男|12|123456||8 |小綠|女|34|222222||9 |曉峰|男|15|333333||10 |小影|女|26|444444||11 |大梅|女|27|555555|+----+------+------+------+----------+6rowsinset(0.00sec)#使用ISNULL查詢年齡為空的數(shù)據(jù)mysql>SELECT*FROMuserWHEREageISNULL;Emptyset(0.00sec)#使用ISNOTNULL查詢年齡不為空的數(shù)據(jù)mysql>SELECT*FROMuserWHEREageISNOTNULL;+----+-------+------+------+----------+|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)#使用AND查詢年齡大于20且小于30的數(shù)據(jù)mysql>SELECT*FROMuserWHEREage>20ANDage<30;+----+-------+------+------+----------+|id|name
|sex
|age
|password
|+----+-------+------+------+----------+|3 |david
|女|28|111111
||4 |小紅
|女|27|123456
||10 |小影
|女|26|444444
||11 |大梅
|女|27|555555
|+----+-------+------+------+----------+4rowsinset(0.00sec)#使用BETWEENAND查詢年齡大于20且小于30的數(shù)據(jù)mysql>SELECT*FROMuserWHEREageBETWEEN20AND30;+----+-------+------+------+----------+|id|name
|sex
|age
|password|+----+-------+------+------+----------+|3 |david
|女|28|111111||4 |小紅
|女|27|123456||10 |小影
|女|26|444444||11 |大梅
|女|27|555555|+----+-------+------+------+----------+4rowsinset(0.01sec)#查詢性別為男的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex='男';+----+------+------+------+----------+|id |name
|sex
|age
|password
|+----+------+------+------+----------+|5 |小明
|男|10|123456
||6 |小剛
|男|12|123456
||7 |小王
|男|14|111111
||9 |曉峰
|男|15|333333
|+----+------+------+------+----------+4rowsinset(0.00sec)#查詢性別不為男的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex!='男';+----+-------+------+------+----------+|id|name
|sex
|age
|password
|+----+-------+------+------+----------+|3 |david
|女|28|111111
||4 |小紅
|女|27|123456
||8 |小綠
|女|34|222222
||10 |小影
|女|26|444444
||11 |大梅
|女|27|555555
|+----+-------+------+------+----------+5rowsinset(0.00sec)#查詢性別不為男的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex<>'男';+----+-------+------+------+----------+|id
|name
|sex
|age
|password
|+----+-------+------+------+----------+|3 |david
|女|28|111111
||4 |小紅
|女|27|123456
||8 |小綠
|女|34|222222
||10 |小影
|女|26|444444
||11 |大梅
|女|27|555555
|+----+-------+------+------+----------+5rowsinset(0.00sec)mysql>3.模糊查詢過濾#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user中的所有數(shù)據(jù)mysql>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)#查詢姓名以“小”開頭的數(shù)據(jù)，百分號（%）用于匹配任意字符mysql>SELECT*FROMuserWHEREnamelike'小%';+----+------+------+------+----------+|id
|name|sex|age
|password
|+----+------+------+------+----------+|4 |小紅|女|27|123456
||5 |小明|男|10|123456
||6 |小剛|男|12|123456
||7 |小王|男|14|111111
||8 |小綠|女|34|222222
||10 |小影|女|26|444444
|+----+------+------+------+----------+6rowsinset(0.00sec)#查詢姓名以“明”結(jié)尾的數(shù)據(jù)mysql>SELECT*FROMuserWHEREnamelike'%明';+----+------+------+------+----------+|id|name|sex|age|password
|+----+------+------+------+----------+|5|小明|男|10|123456
|+----+------+------+------+----------+1rowinset(0.00sec)#查詢姓名包含“小”的數(shù)據(jù)mysql>SELECT*FROMuserWHEREnamelike'%小%';+----+------+------+------+----------+|id |name|sex
|age
|password
|+----+------+------+------+----------+|4 |小紅|女|27|123456
||5 |小明|男|10|123456
||6 |小剛|男|12|123456
||7 |小王|男|14|111111
||8 |小綠|女|34|222222
||10 |小影|女|26|444444
|+----+------+------+------+----------+6rowsinset(0.00sec)#查詢年齡以“1”開頭的數(shù)據(jù)，下畫線（_）用于匹配單個字符mysql>SELECT*FROMuserWHEREagelike'1_';+----+------+------+------+----------+|id|name|sex
|age
|password
|+----+------+------+------+----------+|5|小明|男|10|123456
||6|小剛|男|12|123456
||7|小王|男|14|111111
||9|曉峰|男|15|333333
|+----+------+------+------+----------+4rowsinset(0.01sec)#使用ESCAPE關(guān)鍵字指定\作為轉(zhuǎn)義字符。模式'%\_%'中的\_表示匹配下畫線字符mysql>SELECT*FROMuserWHEREnameLIKE'%\_%'ESCAPE'_';Emptyset(0.00sec)mysql>4.字段控制查詢過濾#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user中的所有數(shù)據(jù)mysql>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)#去重查詢性別mysql>SELECTDISTINCTsexFROMuser;+-----+|sex|+-----+|女||男|+-----+2rowsinset(0.00sec)#去重查詢年齡mysql>SELECTDISTINCTageFROMuser;+------+|age
|+------+|28
||27
||10
||12
||14
||34
||15
||26
|+------+8rowsinset(0.00sec)#查詢id與age之和mysql>SELECT*,id+ageFROMuser;+----+-------+------+------+----------+--------+|id|name
|sex
|age
|password|id+age|+----+-------+------+------+----------+--------+|3 |david
|女|28|111111|31||4 |小紅
|女|27|123456|31||5 |小明
|男|10|123456|15||6 |小剛
|男|12|123456|18||7 |小王
|男|14|111111|21||8 |小綠|女|34|222222|42||9 |曉峰|男|15|333333|24||10 |小影|女|26|444444|36||11 |大梅|女|27|555555|38|+----+-------+------+------+----------+--------+9rowsinset(0.01sec)#查詢id與age之和，使用AS關(guān)鍵字設(shè)置別名totalmysql>SELECT*,id+ageAStotalFROMuser;+----+-------+------+------+----------+-------+|id|name
|sex
|age
|password|total|+----+-------+------+------+----------+-------+|3 |david|女|28|111111|31||4 |小紅|女|27|123456|31||5 |小明|男|10|123456|15||6 |小剛|男|12|123456|18||7 |小王|男|14|111111|21||8 |小綠|女|34|222222|42||9 |曉峰|男|15|333333|24||10 |小影|女|26|444444|36||11 |大梅|女|27|555555|38|+----+-------+------+------+----------+-------+9rowsinset(0.00sec)#查詢id與age之和，不使用AS關(guān)鍵字設(shè)置別名totalmysql>SELECT*,id+agetotalFROMuser;+----+-------+------+------+----------+-------+|id|name
|sex
|age|password|total|+----+-------+------+------+----------+-------+|3 |david
|女|28|111111|31||4 |小紅
|女|27|123456|31||5 |小明
|男|10|123456|15||6 |小剛
|男|12|123456|18||7 |小王
|男|14|111111|21||8 |小綠
|女|34|222222|42||9 |曉峰
|男|15|333333|24||10 |小影
|女|26|444444|36||11 |大梅
|女|27|555555|38|+----+-------+------+------+----------+-------+9rowsinset(0.00sec)mysql>5.正則表達(dá)式查詢過濾#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user中的所有記錄mysql>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)#查詢name字段中以“小”開頭的數(shù)據(jù)mysql>SELECT*FROMuserWHEREnameREGEXP'^小';+----+------+------+------+----------+|id
|name
|sex|age
|password
|+----+------+------+------+----------+|4 |小紅|女|27|123456
||5 |小明|男|10|123456
||6 |小剛|男|12|123456
||7 |小王|男|14|111111
||8 |小綠|女|34|222222
||10 |小影|女|26|444444
|+----+------+------+------+----------+6rowsinset(0.02sec)#查詢password字段中以“456”結(jié)尾的數(shù)據(jù)mysql>SELECT*FROMuserWHEREpasswordREGEXP'456$';+----+------+------+------+----------+|id|name|sex
|age
|password
|+----+------+------+------+----------+|4|小紅|女|27|123456
||5|小明|男|10|123456
||6|小剛|男|12|123456
|+----+------+------+------+----------+3rowsinset(0.00sec)#用“.”來替代字符串中的任意一個字符mysql>SELECT*FROMuserWHEREpasswordREGEXP'123.5';+----+------+------+------+----------+|id|name|sex
|age
|password
|+----+------+------+------+----------+|4|小紅|女|27|123456
||5|小明|男|10|123456
||6|小剛|男|12|123456
|+----+------+------+------+----------+3rowsinset(0.00sec)#用“'*”匹配前面的字符任意次，包括0次mysql>SELECT*FROMuserWHEREpasswordREGEXP'^1234*';+----+------+------+------+----------+|id|name|sex
|age
|password
|+----+------+------+------+----------+|4|小紅|女|27|123456
||5|小明|男|10|123456
||6|小剛|男|12|123456
|+----+------+------+------+----------+3rowsinset(0.00sec)#用“+”匹配至少一個前面的字符mysql>SELECT*FROMuserWHEREpasswordREGEXP'^123+';+----+------+------+------+----------+|id|name
|sex
|age
|password
|+----+------+------+------+----------+|4|小紅|女|27|123456
||5|小明|男|10|123456
||6|小剛|男|12|123456
|+----+------+------+------+----------+3rowsinset(0.00sec)#匹配指定字符串mysql>SELECT*FROMuserWHEREpasswordREGEXP11;+----+-------+------+------+----------+|id|name
|sex
|age
|password
|+----+-------+------+------+----------+|3|david|女|28|111111
||7|小王|男|14|111111
|+----+-------+------+------+----------+2rowsinset(0.00sec)#匹配指定字符串中的任何一個，將多個字符串使用分隔符“|”隔開mysql>SELECT*FROMuserWHEREpasswordREGEXP'123|222';+----+------+------+------+----------+|id|name|sex
|age
|password
|+----+------+------+------+----------+|4|小紅|女|27|123456
||5|小明|男|10|123456
||6|小剛|男|12|123456
||8|小綠|女|34|222222
|+----+------+------+------+----------+4rowsinset(0.00sec)/*使用“[]”指定一個字符集合，只匹配其中任何一個字符，即對所查找的文本還可以指定數(shù)值集合“[a-z]”表示集合區(qū)間為a～z的字母，“[0-9]”表示集合區(qū)間為0～9的數(shù)字*/mysql>SELECT*FROMuserWHEREidREGEXP'[15]';+----+------+------+------+----------+|id|name
|sex
|age
|password
|+----+------+------+------+----------+|5 |小明
|男|10|123456
||10|小影
|女|26|444444
||11|大梅
|女|27|555555
|+----+------+------+------+----------+3rowsinset(0.00sec)#查詢不在指定集合范圍內(nèi)的數(shù)據(jù)mysql>SELECT*FROMuserWHEREidREGEXP'[^1-4]';+----+------+------+------+----------+|id|name|sex
|age
|password
|+----+------+------+------+----------+|5 |小明|男|10|123456
||6 |小剛|男|12|123456
||7 |小王|男|14|111111
||8 |小綠|女|34|222222
||9 |曉峰|男|15|333333
||10 |小影|女|26|444444
|+----+------+------+------+----------+6rowsinset(0.00sec)#字符串{n,}：匹配前面的字符串至少n次mysql>SELECT*FROMuserWHEREpasswordREGEXP'333{2,}';+----+------+------+------+----------+|id|name
|sex
|age
|password
|+----+------+------+------+----------+|9|曉峰
|男|15|333333
|+----+------+------+------+----------+1rowinset(0.00sec)#字符串{n,m}：匹配前面的字符串至少n次，至多m次。如果n為0，則此參數(shù)為可選參數(shù)mysql>SELECT*FROMuserWHEREpasswordREGEXP'33{1,3}';+----+------+------+------+----------+|id|name
|sex
|age
|password
|+----+------+------+------+----------+|9|曉峰
|男|15|333333
|+----+------+------+------+----------+1rowinset(0.00sec)mysql>實(shí)訓(xùn)案例名稱：子查詢一、任務(wù)介紹子查詢是一個父表達(dá)式調(diào)用另一個子表達(dá)式結(jié)果的查詢操作，子查詢可以按返回結(jié)果和按對返回結(jié)果的調(diào)用方法進(jìn)行分類。按返回結(jié)果分類的子查詢又可以分為表子查詢、行子查詢、列子查詢、標(biāo)量子查詢；按對返回結(jié)果的調(diào)用方法分類的子查詢可以分為WHERE子查詢、FROM子查詢、exists子查詢。二、實(shí)現(xiàn)步驟1.表子查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user中年齡大于20的用戶的姓名、性別、年齡mysql>SELECT*FROM(SELECTname,sex,ageFROMuserWHEREage>20)ASuser20;+-------+------+------+|name
|sex
|age
|+-------+------+------+|david
|女|28||小紅
|女|27||小綠|女|34||小影|女|26||大梅|女|27|+-------+------+------+5rowsinset(0.00sec)mysql>2.行子查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢和david性別相同的用戶mysql>SELECT*FROMuserWHEREsex=(SELECTsexFROMuserWHEREname='david');+----+-------+------+------+----------+|id |name
|sex
|age
|password
|+----+-------+------+------+----------+|3 |david
|女
|28|111111
||4 |小紅
|女
|27|123456
||8 |小綠
|女
|34|222222
||10 |小影
|女
|26|444444
||11 |大梅
|女
|27|555555
|+----+-------+------+------+----------+5rowsinset(0.01sec)mysql>3.列子查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫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)#查詢性別為女的id和姓名m+----+-------+|id |name|+----+-------+|3 |david||4 |小紅||8 |小綠||10 |小影||11 |大梅|+----+-------+5rowsinset(0.01sec)#查詢年齡大于david的用戶的id、姓名和年齡mysql>SELECTid,name,ageFROMuserWHEREage>ANY(SELECTageFROMuserWHERE
name='david');+----+------+------+|id|name|age|+----+------+------+|8|小綠|34|+----+------+------+1rowinset(0.00sec)#查詢年齡最小的用戶mysql>SELECT*FROMuserWHEREage<=ALL(SELECTageFROMuser);+----+------+------+------+----------+|id|name|sex
|age
|password|+----+------+------+------+----------+|5|小明|男|10|123456|+----+------+------+------+----------+1rowinset(0.00sec)mysql>4.標(biāo)量子查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫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)#查詢和id為4的用戶年齡相同的用戶m+------+|name|+------+|大梅|+------+1rowinset(0.00sec)mysql>5.WHERE型子查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫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)#查詢比小剛年齡大的用戶mysql>SELECT*FROMuserWHEREage>(SELECTageFROMuserWHEREname='小剛');+----+-------+------+------+----------+|id |name|sex
|age
|password
|+----+-------+------+------+----------+|3 |david|女|28|111111
||4 |小紅|女|27|123456
||7 |小王|男|14|111111
||8 |小綠|女|34|222222
||9 |曉峰|男|15|333333
||10 |小影|女|26|444444
||11 |大梅|女|27|555555
|+----+-------+------+------+----------+7rowsinset(0.00sec)mysql>6.FROM型子查詢#使用用戶名root和相應(yīng)密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫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)#查詢性別為男的用戶my
溫馨提示
- 1. 本站所有資源如無特殊說明，都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等，如果需要附件，請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙，網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽，若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間，僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理，對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯，并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容，請與我們聯(lián)系，我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 敦煌飛天形象與產(chǎn)品的融合設(shè)計(jì)研究分析 財(cái)務(wù)會計(jì)學(xué)專業(yè)
- 2025至2030年中國桑拿房行業(yè)發(fā)展?jié)摿Ψ治黾巴顿Y方向研究報(bào)告
- 2025至2030年中國貓砂行業(yè)發(fā)展監(jiān)測及投資戰(zhàn)略研究報(bào)告
- 2025至2030年中國蘋果樹種植行業(yè)市場供需格局及行業(yè)前景展望報(bào)告
- 2025至2030年中國建筑器材租賃行業(yè)市場前景預(yù)測及投資戰(zhàn)略研究報(bào)告
- 養(yǎng)老院附加協(xié)議合同范本
- 與租賃公司合作合同范本
- 農(nóng)村拆房安置協(xié)議書模板
- 吉利楓葉80v換電協(xié)議合同
- 出租車車主終止合同協(xié)議
- T/SHPTA 102-2024聚四氟乙烯內(nèi)襯儲罐技術(shù)要求
- 好朋友友誼協(xié)議書
- 重癥醫(yī)學(xué)科健康宣教手冊
- 靈活用工合同協(xié)議書
- 全球及中國PCB檢測設(shè)備行業(yè)市場發(fā)展現(xiàn)狀及發(fā)展前景研究報(bào)告2025-2028版
- 《移步換景 別有洞天─中國古典園林欣賞》教學(xué)課件-2024-2025學(xué)年人教版初中美術(shù)八年級下冊
- 2025年重慶物流集團(tuán)渝地綠能科技有限公司招聘筆試參考題庫含答案解析
- 浙江省自然資源領(lǐng)域生態(tài)產(chǎn)品價值實(shí)現(xiàn)典型案例（第一批）
- 彩票店管理制度
- 加油站股制合同標(biāo)準(zhǔn)文本
- 部編版四年級道德與法治下冊《當(dāng)沖突發(fā)生》課件
評論
0/150
提交評論