SQL 新人入门 (从基础框架走起)SQL语句最基础框架
select 字段 from 表名
以下语句皆在该基础上加内容,假设表名为table
①如果想搜索表内所有内容
select * from table
②搜索多个字段
select city,country from table
③同时搜索两个表的内容,假设两个表里id字段内容相同
select
a.city,
a.country,
b.number
from table1 a
left join table2 b
on a.id=b.id
④以city分组
select
a.city,
a.country,
b.number
from table a
from table b
group by city
⑤以city升序排序
select
a.city,
a.country,
b.number
from table a
from table b
group by city
order by city
降序
select
a.city,
a.country,
b.number
from table a
from table b
group by city
order by city desc
⑥类似数据透视表功能,计算城市数量
select
a.city,
a.country,
b.number,count(1) //括号里面的1代表以第一列为计数标准。
from table a
from table b
group by city
order by city
⑦计算数量的基础上去重
select
a.city,
a.country,
b.number,count(distinct positionId) //distinct 后面跟具有唯一标识,可用来做去重标准
from table a
from table b
group by city
order by city
⑧多维聚合
select
a.city,
a.country,
b.number,count(distinct positionId)
from table a
from table b
group by city,country //group by 添加多个字段,它将以多维的形式进行数据聚合
order by city
⑨限制条件,数量500以上的城市
select
a.city,
count(distinct positionId)
from table a
group by city having count(distinct positionId) >= 500
order by city
⑩嵌套子查询
select * from
(select city ,count (distinct positionId) as counts from table
group by city ) as t1 //as 可命名表名
where counts >=500