5.limit語句：
語法：select 字段名 from 標明 limit 起始行,查詢幾行;
mysql>?select?*?from?4a?limit?0,3; +--------+------+--------+------+--------+------+------+-------+ |?sname??|?sage?|?tname??|?t????|?cname??|?s????|?c????|?score?| +--------+------+--------+------+--------+------+------+-------+ |?劉一???|???18?|?葉平???|????1?|?語文???|????1?|????1?|????56?| |?劉一???|???18?|?賀高???|????2?|?數學???|????1?|????2?|????78?| |?劉一???|???18?|?楊艷???|????3?|?英語???|????1?|????3?|????67?| +--------+------+--------+------+--------+------+------+-------+ 3?rows?in?set?(0.00?sec)
注意：起始行的第一行為0而不是1。
6. like語句：
語法：select 字段名 from 表名 where 字段名 like 條件；
mysql>?select?*?from?4a?where?sage?like?"%9"?or?sage?like?"%8"; +--------+------+--------+------+--------+------+------+-------+ |?sname??|?sage?|?tname??|?t????|?cname??|?s????|?c????|?score?| +--------+------+--------+------+--------+------+------+-------+ |?劉一???|???18?|?葉平???|????1?|?語文???|????1?|????1?|????56?| |?劉一???|???18?|?賀高???|????2?|?數學???|????1?|????2?|????78?| |?劉一???|???18?|?楊艷???|????3?|?英語???|????1?|????3?|????67?| |?劉一???|???18?|?周磊???|????4?|?物理???|????1?|????4?|????58?| |?錢二???|???19?|?葉平???|????1?|?語文???|????2?|????1?|????79?| |?錢二???|???19?|?賀高???|????2?|?數學???|????2?|????2?|????81?| |?錢二???|???19?|?楊艷???|????3?|?英語???|????2?|????3?|????92?| |?錢二???|???19?|?周磊???|????4?|?物理???|????2?|????4?|????68?| |?李四???|???18?|?賀高???|????2?|?數學???|????4?|????2?|????88?| |?李四???|???18?|?楊艷???|????3?|?英語???|????4?|????3?|????90?| |?李四???|???18?|?周磊???|????4?|?物理???|????4?|????4?|????93?| |?趙六???|???19?|?葉平???|????1?|?語文???|????6?|????1?|????35?| |?趙六???|???19?|?賀高???|????2?|?數學???|????6?|????2?|????68?| |?趙六???|???19?|?周磊???|????4?|?物理???|????6?|????4?|????71?| +--------+------+--------+------+--------+------+------+-------+ 14?rows?in?set?(0.00?sec)
mysql>?select?*?from?4a?where?sage?like?"%9"?or?sage?like?"%8"?or?sage?like?"%%"; +--------+------+--------+------+--------+------+------+-------+ |?sname??|?sage?|?tname??|?t????|?cname??|?s????|?c????|?score?| +--------+------+--------+------+--------+------+------+-------+ |?劉一???|???18?|?葉平???|????1?|?語文???|????1?|????1?|????56?| |?劉一???|???18?|?賀高???|????2?|?數學???|????1?|????2?|????78?| |?劉一???|???18?|?楊艷???|????3?|?英語???|????1?|????3?|????67?| |?劉一???|???18?|?周磊???|????4?|?物理???|????1?|????4?|????58?| |?錢二???|???19?|?葉平???|????1?|?語文???|????2?|????1?|????79?| |?錢二???|???19?|?賀高???|????2?|?數學???|????2?|????2?|????81?| |?錢二???|???19?|?楊艷???|????3?|?英語???|????2?|????3?|????92?| |?錢二???|???19?|?周磊???|????4?|?物理???|????2?|????4?|????68?| |?張三???|???17?|?葉平???|????1?|?語文???|????3?|????1?|????91?| |?張三???|???17?|?賀高???|????2?|?數學???|????3?|????2?|????47?| |?張三???|???17?|?楊艷???|????3?|?英語???|????3?|????3?|????88?| |?張三???|???17?|?周磊???|????4?|?物理???|????3?|????4?|????56?| |?李四???|???18?|?賀高???|????2?|?數學???|????4?|????2?|????88?| |?李四???|???18?|?楊艷???|????3?|?英語???|????4?|????3?|????90?| |?李四???|???18?|?周磊???|????4?|?物理???|????4?|????4?|????93?| |?王五???|???17?|?葉平???|????1?|?語文???|????5?|????1?|????46?| |?王五???|???17?|?楊艷???|????3?|?英語???|????5?|????3?|????78?| |?王五???|???17?|?周磊???|????4?|?物理???|????5?|????4?|????53?| |?趙六???|???19?|?葉平???|????1?|?語文???|????6?|????1?|????35?| |?趙六???|???19?|?賀高???|????2?|?數學???|????6?|????2?|????68?| |?趙六???|???19?|?周磊???|????4?|?物理???|????6?|????4?|????71?| +--------+------+--------+------+--------+------+------+-------+ 21?rows?in?set?(0.00?sec)
可以看出雙百分號%%是全匹配，可以匹配表中的所有記錄。那么，即便是NULL值也能夠匹配嗎？
答案是否定的：
mysql>?select?*?from?4a; +--------+------+--------+------+--------+------+------+-------+ |?sname??|?sage?|?tname??|?t????|?cname??|?s????|?c????|?score?| +--------+------+--------+------+--------+------+------+-------+ |?劉一???|???18?|?葉平???|????1?|?語文???|????1?|????1?|????56?| |?劉一???|???18?|?賀高???|????2?|?數學???|????1?|????2?|????78?| |?劉一???|???18?|?楊艷???|????3?|?英語???|????1?|????3?|????67?| |?劉一???|???18?|?周磊???|????4?|?物理???|????1?|????4?|????58?| |?錢二???|???19?|?葉平???|????1?|?語文???|????2?|????1?|????79?| |?錢二???|???19?|?賀高???|????2?|?數學???|????2?|????2?|????81?| |?錢二???|???19?|?楊艷???|????3?|?英語???|????2?|????3?|????92?| |?錢二???|???19?|?周磊???|????4?|?物理???|????2?|????4?|????68?| |?張三???|???17?|?葉平???|????1?|?語文???|????3?|????1?|????91?| |?張三???|???17?|?賀高???|????2?|?數學???|????3?|????2?|????47?| |?張三???|???17?|?楊艷???|????3?|?英語???|????3?|????3?|????88?| |?張三???|???17?|?周磊???|????4?|?物理???|????3?|????4?|????56?| |?李四???|???18?|?賀高???|????2?|?數學???|????4?|????2?|????88?| |?李四???|???18?|?楊艷???|????3?|?英語???|????4?|????3?|????90?| |?李四???|???18?|?周磊???|????4?|?物理???|????4?|????4?|????93?| |?王五???|???17?|?葉平???|????1?|?語文???|????5?|????1?|????46?| |?王五???|???17?|?楊艷???|????3?|?英語???|????5?|????3?|????78?| |?王五???|???17?|?周磊???|????4?|?物理???|????5?|????4?|????53?| |?趙六???|???19?|?葉平???|????1?|?語文???|????6?|????1?|????35?| |?趙六???|???19?|?賀高???|????2?|?數學???|????6?|????2?|????68?| |?趙六???|???19?|?周磊???|????4?|?物理???|????6?|????4?|????71?| |?NULL???|?NULL?|?NULL???|?NULL?|?NULL???|?NULL?|?NULL?|????93?| +--------+------+--------+------+--------+------+------+-------+ 22?rows?in?set?(0.05?sec)
7.distinct語句：
語法：select distinct 字段名 from 表名;
mysql>?select?distinct?sname?from?4a;??????????? +--------+ |?sname??| +--------+ |?劉一???| |?錢二???| |?張三???| |?李四???| |?王五???| |?趙六???| |?NULL???| +--------+
mysql>?select?distinct?sname,?sage?from?4a; +--------+------+ |?sname??|?sage?| +--------+------+ |?劉一???|???18?| |?錢二???|???19?| |?張三???|???17?| |?李四???|???18?| |?王五???|???17?| |?趙六???|???19?| |?NULL???|?NULL?| +--------+------+ 7?rows?in?set?(0.00?sec)
mysql>?select?distinct?sname,?sage,tname?from?4a; +--------+------+--------+ |?sname??|?sage?|?tname??| +--------+------+--------+ |?劉一???|???18?|?葉平???| |?劉一???|???18?|?賀高???| |?劉一???|???18?|?楊艷???| |?劉一???|???18?|?周磊???| |?錢二???|???19?|?葉平???| |?錢二???|???19?|?賀高???| |?錢二???|???19?|?楊艷???| |?錢二???|???19?|?周磊???| |?張三???|???17?|?葉平???| |?張三???|???17?|?賀高???| |?張三???|???17?|?楊艷???| |?張三???|???17?|?周磊???| |?李四???|???18?|?賀高???| |?李四???|???18?|?楊艷???| |?李四???|???18?|?周磊???| |?王五???|???17?|?葉平???| |?王五???|???17?|?楊艷???| |?王五???|???17?|?周磊???| |?趙六???|???19?|?葉平???| |?趙六???|???19?|?賀高???| |?趙六???|???19?|?周磊???| |?NULL???|?NULL?|?NULL???| +--------+------+--------+ 22?rows?in?set?(0.00?sec)
可以看出當作用于多列的時候distinct就不再返回唯一記錄了。
另外distinct還可以和一些數學運算的函數一起使用：
mysql>?select?count(distinct?sname)?from?4a; +-----------------------+ |?count(distinct?sname)?| +-----------------------+ |?????????????????????6?| +-----------------------+ 1?row?in?set?(0.08?sec)
mysql>?select?sum(distinct?score)?from?4a; +---------------------+ |?sum(distinct?score)?| +---------------------+ |????????????????1193?| +---------------------+ 1?row?in?set?(0.06?sec)
mysql>?select?max(distinct?score)?from?4a; +---------------------+ |?max(distinct?score)?| +---------------------+ |??????????????????93?| +---------------------+ 1?row?in?set?(0.06?sec)
mysql>?select?min(distinct?score)?from?4a; +---------------------+ |?min(distinct?score)?| +---------------------+ |??????????????????35?| +---------------------+ 1?row?in?set?(0.00?sec)
mysql>?select?min(distinct?score-1)?from?4a; +-----------------------+ |?min(distinct?score-1)?| +-----------------------+ |????????????????????34?| +-----------------------+ 1?row?in?set?(0.08?sec)
例子太多就不一一列舉了。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
