Mysql数据库之sql语句

1.查询命令

1.1查询并排序

#降序
select * from tablesName where clientNo = '123' order by salary desc;

#升序(默认升序)
select * from tablesName where clientNo = '123' order by salary ;
select * from tablesName where clientNo = '123' order by salary asc;

#对两个字段进行降序
select * from tablesName where clientNo = '123' order by salary desc，receiptNo desc;

#将某个字段由字符转为数值，再对两个字段进行降序
select * from tablesName where clientNo = '123' order by salary desc，(receiptNo+0) desc;

#先查找参考号，再根据参考号查找结算信息
select * cl_tran_hist where reference in (select reference from cl_receipt where receipt_type = ‘PO’) order by tran_date desc;

#not in 
select Name as Customers from Customers where Id not in (select CustomerId from Orders)

1.1查询并统计出现的个数

--查询某个字段出现的次数
select site,count(site) 次数 from a_cost 
group by site 
order by count(site) desc


--查询某个字段次数大于1的数据
select count(site),site 
from a_cost 
group by site 
having count(site) > 1


--查询某个字段出现次数最多的数据
select top 1 site,count(site) 次数 
from a_cost 
group by site
order by count(site) desc	

2.