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

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

select 字段 from 表名

---

以下语句皆在该基础上加内容，假设表名为table

---

①如果想搜索表内所有内容

select * from table

②搜索多个字段

select city,country from table

③同时搜索两个表的内容，假设两个表里id字段内容相同

select a.city,a.country, b.numberfrom table1 aleft join table2 bon a.id=b.id

④以city分组

select a.city,a.country, b.numberfrom table afrom table bgroup by city

⑤以city升序排序

select a.city,a.country, b.numberfrom table afrom table bgroup by cityorder by city

降序

select a.city,a.country, b.numberfrom table afrom table bgroup by cityorder by city desc

⑥类似数据透视表功能,计算城市数量

select a.city,a.country, b.number,count(1) //括号里面的1代表以第一列为计数标准。from table afrom table bgroup by cityorder by city

⑦计算数量的基础上去重

select a.city,a.country, b.number,count(distinct positionId) //distinct 后面跟具有唯一标识，可用来做去重标准from table afrom table bgroup by cityorder by city

⑧多维聚合

select a.city,a.country, b.number,count(distinct positionId) from table afrom table bgroup by city,country //group by 添加多个字段，它将以多维的形式进行数据聚合order by city

⑨限制条件，数量500以上的城市

select a.city,count(distinct positionId) from table agroup by city having count(distinct positionId) >= 500 order by city

⑩嵌套子查询

select * from(select city ,count (distinct positionId) as counts from tablegroup by city ) as t1 //as 可命名表名where counts >=500