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中文网其他相关文章!