必发88手机客户端 7

mysql查询语句

mysql,A用户表,B留言表,查询留言数大于10条记下的用户名,留言数量

1、基本构成

:select u.name,count(*) as total
from user as u inner join message as m on u.user_id=m.user_id group by
u.name
having
total>10 order by total desc 

(壹)须求查询的表(单表,多表)

一.询问每一种用户最新的发言记录:

(2)须求查询的音信(字段音信,过滤处理)

select max(time) from 2017sxgf group by id order by time desc limit 10;

必发88手机客户端,(三)查询条件(字段关联,字段值范围,记录截取设置,排序格局,分组办法,去重,or
,and) 

必发88手机客户端 1

二、实例展示(以user表为例)

 

二.壹查询单表(user)

二.找到发言数最多的用户ID和次数

(一)查询单表全部字段

select userid,count(userid) from orders  where userid != ” group by
userid order by count(userid) desc  limit 1;

select * from
user;(select前边跟字段名,from前边跟表名,*表示享有字段,
where前面跟条件)

三.关于MySQL中种种用户取一条记下的三种写法

先是种是先排序,然后group,那样的话自然能够取到最适合的一条数据。 缺点很显眼:Using temporary; Using filesort

select * from (select * from 2017sxgf order by time desc)t group by
mobile limit 10;

 

其次种是手拉手查询 

select * from (select max(time) as btime  from 2017sxgf group by mobile
limit 10)t left join  2017sxgf as s on t.btime = s.time;

 

其二种是子查询

select * from 2017sxgf where exists(select mobile from (select
max(time) as btime from 2017sxgf  group by mobile limit 10)t where
t.btime = 2017sxgf.time);

 

5.

 

必发88手机客户端 2

 

 必发88手机客户端 3

必发88手机客户端 4

必发88手机客户端 5

 

必发88手机客户端 6

必发88手机客户端 7

 

(二)查询单表特定字段

select user_id,user_name from
user;(字段名之间用“,”隔开分离)

(三)查询单表记录总的数量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)中

 (陆)查询单表,条件为某些字段值范围

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;

(九)having关键字与group by,能够与协商函数一齐使用;

select count(*) from user group
by user_id desc having max(user_weight)<100;

(10)group_concat()与group by联合使用,列出分组字段的全数值

select
group_concat(user_weight order by user_weight desc) from user group
by user_id ;

(11)group_concat()与group
by联合再用substring_index(),列出分组字段的前多少个值

select
substring_index(group_concat(user_weight order by user_weight
desc),’,’,2) from user group by user_id ;

 

2.二询问多表(user,order)

(一)inner join(只回去相称值)

select * from user inner join
order on user.user_id=order.user_id;

(二)left join(重临相配值和左表剩余值)

select * from user u left join
order o on u.user_id=o.user_id;

注意:u和o是小名,方面利用

(三)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;