SQL 新人入门 （从基础框架走起）

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