1、基本構成
(1)需要查詢的表(單表,多表)
(2)需要查詢的資訊(欄位信息,過濾處理)
(3)查詢條件(字段關聯,字段值範圍,記錄截取設置,排序方式,分組方式,去重,or ,and)
2、實例展示(以user表為例)
2.1查詢單表(user)
(1)查詢單表所有欄位
select * from user;(select後面跟著欄位名,from後面跟表名,*代表所有字段, where後面跟條件)
(2)查詢單表特定字段
select user_id,user_name from user;(字段名之間用“,”隔開)
(3)查詢單表記錄總數count(),sum(),max(),min()用法相同,後面三個函數參數不能為*。
select count(*) from user;
(4)查詢單表,按user_id分組統計每組記錄總數,並依照user_id倒序
select count(* ) from user group by user_id desc;
注意:分組欄位只有一個時,可直接在後面加desc進行倒序,預設是正序,也可加上asc
#(5)查詢單表,依user_id,user_name分組統計每組記錄總數,並依照user_id倒序
select count(*) from user group by user_id,user_name order by user_id desc;
注意:group by與order by同時使用,排序的欄位user_id要出現在分組欄位(user_id,user_name)中
(6)查詢單表,條件為某個欄位值範圍
#user_id> =1且<=2:select * from user where user_id>=1 and user_id<=2;
user_id在1和2之間 :select * from user where user_id between 1 and 2;
user_id包含於(1,2):select * from user where user_id in(1,2);
user_id是1或2 :select * from user where user_id=1 or user_id=2 ;
(7)查詢單表,截取資料limit index,length
截取第1條:select * from user limit 1;或select * from user limit 0,1;
截取第2條:select * from user limit 1,1;
(8)查詢單表,去重distinct
select distinct user_name from user;
#(9)having關鍵字,可以與合計函數一起使用;
select count(*) from user group by user_id desc having max(user_weight)<100;
#2.2查詢多表(user,order)
(1)inner join(只回傳符合值)
select * from user inner join order on user.user_id=order.user_id;
(2)left join(傳回符合值與左表剩餘值)
select * from user u left join order o on u.user_id=o.user_id;
注意:u和o是別名,方面使用
(3)right join(傳回符合值和右表剩餘值)
select * from user right join order on user.user_id=order.user_id;
(4)full join(傳回所有值)
select * from user full join order on user.user_id=order.user_id;
#
以上是mysql查詢語句的總結的詳細內容。更多資訊請關注PHP中文網其他相關文章!