一、查詢數(shù)值型數(shù)據(jù):
SELECT?*?FROM?tb_name?WHERE?sum?>?100;
查詢謂詞:>,=,<,<>,!=,!>,!<,=>,=<
二、查詢字符串
SELECT?*?FROM?tb_stu??WHERE?sname??=??‘小劉’
SELECT?*?FROM?tb_stu??WHERE?sname?like?‘劉%’
SELECT?*?FROM?tb_stu??WHERE?sname?like?‘%程序員’
SELECT?*?FROM?tb_stu??WHERE?sname?like?‘%PHP%’
三、查詢?nèi)掌谛蛿?shù)據(jù)
SELECT?*?FROM?tb_stu?WHERE?date?=?‘2011-04-08’
注:不同數(shù)據(jù)庫對(duì)日期型數(shù)據(jù)存在差異:?：
(1)MySQL:SELECT?*?from?tb_name?WHERE?birthday?=?‘2011-04-08’
(2)SQL?Server:SELECT?*?from?tb_name?WHERE?birthday?=?‘2011-04-08’
(3)Access:SELECT?*?from?tb_name?WHERE?birthday?=?#2011-04-08#
四媒咳、查詢邏輯型數(shù)據(jù)
SELECT?*?FROM?tb_name?WHERE?type?=?‘T’
SELECT?*?FROM?tb_name?WHERE?type?=?‘F’
邏輯運(yùn)算符:and?or?not
五、查詢非空數(shù)據(jù)
SELECT?*?FROM?tb_name?WHERE?address?<>”?order?by?addtime?desc
注:<>相當(dāng)于PHP中的!=
六、利用變量查詢數(shù)值型數(shù)據(jù)
SELECT?*?FROM?tb_name?WHERE?id?=?‘$_POST[text]’
注:利用變量查詢數(shù)據(jù)時(shí)草丧，傳入SQL的變量不必用引號(hào)括起來，因?yàn)镻HP中的字符串與數(shù)值型數(shù)據(jù)進(jìn)行連接時(shí)莹桅，程序會(huì)自動(dòng)將數(shù)值型數(shù)據(jù)轉(zhuǎn)變成字符串昌执，然后與要連接的字符串進(jìn)行連接
七、利用變量查詢字符串?dāng)?shù)據(jù)
SELECT?*?FROM?tb_name?WHERE?name?LIKE?‘%$_POST[name]%’
完全匹配的方法”%%”表示可以出現(xiàn)在任何位置
八诈泼、查詢前n條記錄
SELECT?*?FROM?tb_name?LIMIT?0,$N;
limit語句與其他語句懂拾，如order?by等語句聯(lián)合使用，會(huì)使用SQL語句千變?nèi)f化，使程序非常靈活
九、查詢后n條記錄
SELECT?*?FROM?tb_stu?ORDER?BY?id?ASC?LIMIT?$n
十赖阻、查詢從指定位置開始的n條記錄
SELECT?*?FROM?tb_stu?ORDER?BY?id?ASC?LIMIT?$_POST[begin],$n
注意:數(shù)據(jù)的id是從0開始的
十一、查詢統(tǒng)計(jì)結(jié)果中的前n條記錄
SELECT?*?,(yw+sx+wy)?AS?total?FROM?tb_score?ORDER?BY?(yw+sx+wy)?DESC?LIMIT?0,$num
十二贾节、查詢指定時(shí)間段的數(shù)據(jù)
SELECT??要查找的字段?FROM?表名?WHERE?字段名?BETWEEN?初始值?AND?終止值
SELECT?*?FROM?tb_stu?WHERE?age?BETWEEN?0?AND?18
十三、按月查詢統(tǒng)計(jì)數(shù)據(jù)
SELECT?*?FROM?tb_stu?WHERE?month(date)?=?‘$_POST[date]’?ORDER?BY?date?;
注：SQL語言中提供了如下函數(shù)衷畦，利用這些函數(shù)可以很方便地實(shí)現(xiàn)按年栗涂、月、日進(jìn)行查詢
year(data):返回data表達(dá)式中的公元年分所對(duì)應(yīng)的數(shù)值
month(data):返回data表達(dá)式中的月分所對(duì)應(yīng)的數(shù)值
day(data):返回data表達(dá)式中的日期所對(duì)應(yīng)的數(shù)值
十四祈争、查詢大于指定條件的記錄
SELECT?*?FROM?tb_stu?WHERE?age>$_POST[age]?ORDER?BY?age;
十五斤程、查詢結(jié)果不顯示重復(fù)記錄
SELECT?DISTINCT?字段名?FROM?表名?WHERE?查詢條件
注:SQL語句中的DISTINCT必須與WHERE子句聯(lián)合使用，否則輸出的信息不會(huì)有變化?,且字段不能用*代替
十六菩混、NOT與謂詞進(jìn)行組合條件的查詢
(1)NOT?BERWEEN?…?AND?…?對(duì)介于起始值和終止值間的數(shù)據(jù)時(shí)行查詢?可改成?<起始值?AND?>終止值
(2)IS?NOT?NULL?對(duì)非空值進(jìn)行查詢
(3)IS?NULL?對(duì)空值進(jìn)行查詢
(4)NOT?IN?該式根據(jù)使用的關(guān)鍵字是包含在列表內(nèi)還是排除在列表外忿墅，指定表達(dá)式的搜索，搜索表達(dá)式可以是常量或列名沮峡，而列名可以是一組常量疚脐，但更多情況下是子查詢
十七、顯示數(shù)據(jù)表中重復(fù)的記錄和記錄條數(shù)
SELECT??name,age,count(*)?,age?FROM?tb_stu?WHERE?age?=?’19’?group?by?date
十八邢疙、對(duì)數(shù)據(jù)進(jìn)行降序/升序查詢
SELECT?字段名?FROM?tb_stu?WHERE?條件?ORDER?BY?字段?DESC?降序
SELECT?字段名?FROM?tb_stu?WHERE?條件?ORDER?BY?字段?ASC??升序
注:對(duì)字段進(jìn)行排序時(shí)若不指定排序方式棍弄，則默認(rèn)為ASC升序
十九望薄、對(duì)數(shù)據(jù)進(jìn)行多條件查詢
SELECT?字段名?FROM?tb_stu?WHERE?條件?ORDER?BY?字段1?ASC?字段2?DESC??…
注意:對(duì)查詢信息進(jìn)行多條件排序是為了共同限制記錄的輸出，一般情況下呼畸，由于不是單一條件限制痕支，所以在輸出效果上有一些差別。
二十蛮原、對(duì)統(tǒng)計(jì)結(jié)果進(jìn)行排序
函數(shù)SUM([ALL]字段名)?或?SUM([DISTINCT]字段名),可實(shí)現(xiàn)對(duì)字段的求和卧须，函數(shù)中為ALL時(shí)為所有該字段所有記錄求和,若為DISTINCT則為該字段所有不重復(fù)記錄的字段求和
如：SELECT?name,SUM(price)?AS?sumprice??FROM?tb_price?GROUP?BY?name
SELECT?*?FROM?tb_name?ORDER?BY?mount?DESC,price?ASC
二十一、單列數(shù)據(jù)分組統(tǒng)計(jì)
SELECT?id,name,SUM(price)?AS?title,date?FROM?tb_price?GROUP?BY?pid?ORDER?BY?title?DESC
注:當(dāng)分組語句group?by排序語句order?by同時(shí)出現(xiàn)在SQL語句中時(shí)儒陨，要將分組語句書寫在排序語句的前面花嘶，否則會(huì)出現(xiàn)錯(cuò)誤
二十二、多列數(shù)據(jù)分組統(tǒng)計(jì)
多列數(shù)據(jù)分組統(tǒng)計(jì)與單列數(shù)據(jù)分組統(tǒng)計(jì)類似
SELECT?*框全，SUM(字段1*字段2)?AS?(新字段1)?FROM?表名?GROUP?BY?字段?ORDER?BY?新字段1?DESC
SELECT?id,name,SUM(price*num)?AS?sumprice??FROM?tb_price?GROUP?BY?pid?ORDER?BY?sumprice?DESC
注：group?by語句后面一般為不是聚合函數(shù)的數(shù)列察绷，即不是要分組的列
二十三、多表分組統(tǒng)計(jì)
SELECT?a.name,AVG(a.price),b.name,AVG(b.price)?FROM?tb_demo058?AS?a,tb_demo058_1?AS?b?WHERE?a.id=b.id?GROUP?BY?b.type;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
