SELECT 查詢
1.整表查詢
SELECT [查詢內(nèi)容] FROM [查詢的表名] ;
2.條件查詢
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [查詢的條件] ;
3.多條件查詢
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [查詢的條件] AND [查詢條件];
4.關(guān)鍵字BETWEEN AND 在什么之間
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [列名] BETWEEN [條件] AND [條件];
例：查詢TabName表里“id” 50 到 100 的列
SELECT * FROM TabName WHERE id BETWEEN 50 AND 100;
5.關(guān)鍵字IN集合查詢
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [列名] IN (x,x,x)
查詢TabName表里年齡Age 可能是18 ， 20 ，22的人的信息
SELECT * FROM TabName WHERE Age IN (18,20,22);
6.關(guān)鍵字IS NULL
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [列名] IS NULL
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [列名] IS NOT NULL
例： 查詢年齡沒(méi)有填寫(xiě)的和已填寫(xiě)的 表TanName 年齡Age
SELECT * FROM TabName WHERE Age IS NULL; Age 為空
SELECT * FROM TabName WHERE Age IS NOT NULL; Age 不為空
7.關(guān)鍵字LIKE模糊查詢
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [列名] LIKE ‘大概的值’
“%”匹配所有 “_”匹配一個(gè)
8.查詢排序order by
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [查詢的條件] ORDER BY;
9.限制查詢的記錄數(shù)LIMIT
SELECT [查詢內(nèi)容] FROM [查詢的表名] WHERE [查詢的條件] LIMIT (行數(shù)int)/(m,n)
10.使用集合函數(shù)查詢MAX()
SELECT COUNT() FROM [查詢的表名]； 計(jì)數(shù)
SELECT MAX() FROM [查詢的表名]； 最大值
SELECT MIN() FROM [查詢的表名]； 最小值
SELECT AVG() FROM [查詢的表名]； 平均值
SELECT SUM(*) FROM [查詢的表名]； 求和
SELECT GROUP_CONCAT(name) FROM [查詢的表名]； 一格顯示所有列
SELECT sex FROM employee5 GROUP BY sex; 去除重復(fù)
11.使用正則表達(dá)式查詢REGEXP
SELECT * FROM [查詢的表名] WHERE [列名] REGEXP ‘正則表達(dá)式’ ;
12.MySQL多表查詢
a.交叉連接 不需要條件
SELECT [查詢內(nèi)容] FROM [查詢的表名1],[查詢的表名2] ;
b.內(nèi)連接
SELECT [查詢內(nèi)容] FROM [查詢的表名1],[查詢的表名2] WHERE 表1.字段 = 表2.字段;
c.外連接 A表 left/right join B表 on 條件是
SELECT [查詢內(nèi)容] FROM [查詢的表名1] LEFT JOIN [查詢的表名2] ON 表1.字段 = 表2.字段 ;
