1.普通查詢 2.排序查詢 order by asc | desc 3.區間查詢 between | and 4.範圍查詢 in | not in 5.模糊查詢 like % | _ (下劃線一次只能匹配任意一個字元) 6.為空查詢 is null | is not null 7.多條件查詢 and ...
1.普通查詢
1 SELECT 待顯示列欄位 FROM 表名 [WHERE 條件];
2 例:SELECT userid ,username FROM userinfo WHERE userage<30;
3 #查詢所有欄位使用:SELECT * FROM 表名;
2.排序查詢 order by asc | desc
1 SELECT * FROM userinfo ORDER BY userage ASC; //升序
2 SELECT * FROM userinfo ORDER BY userage,userid DESC; //降序
3.區間查詢 between | and
1 SELECT * FROM userinfo WHERE userage>=18 AND userage<=40;
2 SELECT * FROM userinfo WHERE userage BETWEEN 18 AND 40;
4.範圍查詢 in | not in
1 SELECT * FROM userinfo WHERE useraddress IN ('武漢','羅馬');
2 SELECT * FROM userinfo WHERE useraddress NOT IN ('武漢','羅馬');
5.模糊查詢 like % | _ (下劃線一次只能匹配任意一個字元)
1 SELECT * FROM userinfo WHERE username LIKE '張%'; ##以張開頭的數據
2 SELECT * FROM userinfo WHERE username LIKE '%亮'; ##以亮結尾的數據
3 SELECT * FROM userinfo WHERE username LIKE '%亮%'; ##包含亮的數據
4 SELECT * FROM userinfo WHERE username LIKE '__民'; ##第三個字是民的數據
6.為空查詢 is null | is not null
1 SELECT * FROM userinfo WHERE useraddress IS NOT NULL;
7.多條件查詢 and | or
1 SELECT * FROM userinfo WHERE usersex='男' AND userage>20;
2 SELECT * FROM userinfo WHERE usersex='男' OR userage>20;
8.唯一查詢 distinct
1 SELECT DISTINCT useraddress FROM userinfo;
9.分組查詢 group by 欄位 having 多條件表達式
1 #求出每個城市有多少人,並顯示姓名,統計總人數
2 SELECT useraddress,COUNT(*) ,GROUP_CONCAT(username) FROM userinfo GROUP BY useraddress WITH ROLLUP;
3 #求出每個城市有多少男的,有多少女的
4 SELECT useraddress,usersex,COUNT(*) FROM userinfo GROUP BY useraddress,usersex;
5 #求出有兩個年齡大於40歲以上人的城市
6 SELECT * FROM userinfo WHERE userage>40 GROUP BY useraddress HAVING COUNT(*)=2;
7 ##where,order by,limit 的先後順序
8 ##查詢前10個男生,按照年齡排序 SELECT * FROM userinfo WHERE usersex='男' ORDER BY userage LIMIT 0,10;
9 ##如何在查詢中篩選行:where;如何在查詢中篩選列:select 列 from
10.分頁查詢 limit ?,? (第一個?:index從即開始(0),第二個?:總共顯示幾個)
1 SELECT * FROM userinfo LIMIT 3,7;
2 ##每頁顯示10條,如何顯示第2頁;頁碼:page 每頁顯示:num
3 SELECT * FROM userinfo LIMIT (page-1)*num,num;
11.別名查詢 as
1 SELECT userid AS 編號 ,username AS 姓名 ,usersex AS 性別 FROM userinfo u;
12.函數查詢
1 #數學函數
2 SELECT ABS(-4),ABS(5); ##絕對值
3 SELECT BIN(5),OCT(13),HEX(21);
4 SELECT CEILING(5.4),FLOOR(5.7),ROUND(5.7);
5 SELECT GREATEST(6,5,4,6,7,3),LEAST(6,5,34,7,8,2);
6 SELECT MOD(7,5);
7 SELECT PI();
8 SELECT RAND(); ##隨機數
9 SELECT ROUND(); ##四捨五入
10 #聚合函數
11 SELECT COUNT(*) FROM userinfo; ##表中總行數
12 SELECT MAX(欄位),MIN(欄位) FROM userinfo;
13 SELECT SUM(欄位),AVG(欄位) FROM userinfo; ##總和及平均數
14 #字元函數
15 SELECT ASCII('a');
16 SELECT LENGTH('a比cD'),CHAR_LENGTH('a比cD'),BIT_LENGTH('a比cD'); ##計算字元串個數
17 SELECT CONCAT('a','b','c','d'),CONCAT_WS(',','a','b','c','d'); ##連接字元串
18 SELECT LOWER('aBcd'),UPPER('aBcd'); ##轉換大小寫
19 SELECT INSERT('abcdefg',2,3,'888'),REPLACE('abcdefg','cd','888'); ##替換字元串
20 SELECT INSTR('abcdefg','c'),POSITION('c' IN 'abcdefg'),FIND_IN_SET('21',userage) FROM userinfo;
21 SELECT LEFT('abcdefg',3),RIGHT('abcdefg',3); ##返回左邊或右邊指定個數的字元
22 SELECT LPAD('hello',10,'???'),RPAD('hello',10,'???'); ##在左邊或右邊填充字元串
23 SELECT LTRIM(' a b c '),RTRIM(' a b c '),TRIM(' a b c '); ##刪除空格
24 SELECT TRIM('xy' FROM 'asdasdxyadsfdas'); ##刪除指定字元串
25 SELECT REVERSE('abcd'); ##反轉字元串
26 SELECT SUBSTR('abcdefg',2,3);
27 ##時間日期函數
28 SELECT NOW(); ##獲取當前系統日期和時間
29 SELECT CURDATE(),CURRENT_DATE(); ##獲得系統當前日期
30 SELECT CURTIME(),CURRENT_TIME(); ##獲取系統當前時間
31 SELECT YEAR(NOW()),MONTH(NOW());
32 SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()); ##獲取當前時間的時分秒
33 SELECT DAYOFWEEK(NOW()),DAYOFYEAR(NOW()),DAYOFMONTH(NOW()); ##返回今天是一周/年/月的第幾天
34 SELECT DATE_ADD(NOW(),INTERVAL -3 MONTH);
35 SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()); ##返回nuix格式的時間戳
36 SELECT FROM_UNIXTIME('1566396366'); ##將unix格式的時間戳轉換為普通格式時間
37 ##系統函數(類型轉換) cast(值 as 新類型)
38 SELECT CAST('-029' AS SIGNED INTEGER);
39 SELECT CAST(NOW() AS CHAR);
40 SELECT CAST(NOW() AS SIGNED INTEGER);
41 ##加密函數
42 SELECT PASSWORD('12346');
43 SELECT MD5('123456');
44 SELECT ENCODE ('123456','123'); ##加密
45 SELECT DECODE(ENCODE('123456','123'),'123'); ##解密
46 ##其他函數
47 SELECT FORMAT (12313.1313313,3); ##格式化數字,保留小數點後指定位數
48 SELECT INET_ATON('192.168.0.3'),INET_NTOA('3232235523'); ##字元串網路點地址與數值網路地址相互轉換
13.表連接查詢 join
1 #左外連接(重點)
2 SELECT 待顯示列欄位 FROM 左表 LEFT [OUTER] JOIN 右表 ON 連接條件 [WHERE 篩選條件];
3 SELECT * FROM classinfo c LEFT OUTER JOIN stuinfo s ON c.classid=s.classid;
4 #右外連接
5 SELECT 待顯示列欄位 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 連接條件 [WHERE 篩選條件];
6 SELECT * FROM classinfo c RIGHT OUTER JOIN stuinfo s ON c.classid=s.classid;
7 #內連接(重點)
8 SELECT 待顯示列欄位 FROM 表A INNER JOIN 表B ON 連接條件 [WHERE 篩選條件];
9 SELECT 待顯示列欄位 FROM 表A,...表N WHERE 連接條件 [AND 篩選條件];
10 例:SELECT province ,city ,AREA FROM provinces p,cities c ,areas a WHERE p.provinceid=c.provinceid AND c.cityid=a.cityid;
12 #獲取浙江省的信息
13 SELECT province ,city ,AREA FROM provinces p,cities c ,areas a WHERE p.provinceid=c.provinceid AND c.cityid=a.cityid AND province='浙江省';
15 #顯示 省名稱,市名稱,區縣名稱
16 SELECT province,city,AREA FROM cities c LEFT JOIN provinces p ON p.provinceid=c.provinceid LEFT JOIN areas a ON c.cityid=a.cityid;
14.子查詢:將一個查詢的結果當成是一個查詢的條件,那麼這個查詢就是子查詢,外部查詢就是父查詢。
1)帶any、some關鍵字的子查詢:表示滿足其中任一個條件,就返回一個結果作為外層查詢的條件。
1 例:SELECT * FROM userinfo WHERE userage>ANY(SELECT userage FROM userinfo WHERE useraddress='蘇州');
2)帶all關鍵字的子查詢:使用all時需要同時滿足所有內層查詢的條件。
3)帶exists關鍵字的子查詢:exists關鍵字後面的參數是一個任意的子查詢,如果至少返回一行,則exists結果為true併進行外查詢;如果沒有返回,則exists結果為false,不進行外部查詢。
1 例:SELECT * FROM userinfo WHERE userage<30 AND EXISTS (SELECT username FROM userinfo WHERE username='白蓮女');
4)帶in關鍵字的子查詢:內層查詢僅僅返回一個數據列,這個數據列里的值將提供給外部查詢語句進行比較查詢。
1 例:查詢與'王璐'或者'郭二旦'地址一樣的人
2 SELECT * FROM userinfo WHERE useraddress IN ( SELECT useraddress FROM userinfo WHERE username='王璐'OR username = '郭二旦' );
15.合併查詢結果-->並集(union,union all),交集(intersect),差集(minus)
1 例:SELECT userid FROM userinfo WHERE userid<10
2 UNION
3 SELECT userid FROM userinfo WHERE userid BETWEEN 5 AND 14;
16.使用正則表達式查詢 regexp
1 字元“ ^ ” 匹配以特定字元或字元串開頭的文本
2 例:SELECT * FROM userinfo WHERE username REGEXP '^折';
3 字元“ $ ” 匹配以特定字元或字元串結尾的文本
4 例:SELECT * FROM userinfo WHERE username REGEXP '女$';
5 字元“ . ” 匹配任意一個字元
6 字元“ * ”匹配前面的字元任意多次,包括0次;“ + ”匹配前面的字元至少一次
7 匹配多個字元串,使用分隔符“ | ”隔開
8 例:SELECT * FROM userinfo WHERE username REGEXP '折|女';
9 方括弧“ [] ”匹配指定字元中的任意一個
10 例:SELECT * FROM userinfo WHERE userage REGEXP '[03]';
11 “ [^字元集合] ” 匹配不在指定集合中的任意字元
12 例:SELECT * FROM userinfo WHERE userage REGEXP '[^50-60]';
13 字元串{n, } 表示至少匹配n次前面的字元串;字元串{n,m}表示匹配前面的字元串不少於n次,不多於m次
14 例:SELECT * FROM userinfo WHERE userage REGEXP '2{1,}';