SQL常用语法

Order by 应位于where子句后；Having类似于where，不同的是where过滤的对象是行，而having过滤的对象是分组，所以having 在group by后使用

Select region,name from world.country;	返回Region列和name列
Select * from world.country;	返回所有列
select distinct region from world.country;	返回不同的值
select region from world.country limit 5 offset 10;	限制返回第10行起的后面5行数据 （第一个被检索的是第0行）
select  region from world.country order by region;	排序（升序）
select  region,name from world.country order by region,name;	首先按region排序，在相同的region中再按name进行排序（升序）
select  region from world.country order by region desc;	排序（降序）
select  region from world.country where region="western europe";	过滤
select * from world.country where indepyear between 1918 and 1962;	1918<=Indepyear<=1962
where indepyear is null	不包含值的
select * from world.country where indepyear between 1918 and 1962 and population>14786000;	两个过滤条件
select  region from world.country where region="western europe" or region="south america";	满足其中一个过滤条件 （and优先级比or更高，组合使用需要加括号）
select  region from world.country where region in("western europe","south america");	X = In(a,b,c,…)等价于X=a or X=b or X=c…
where region not in("western europe","south america");	相当于取反
where region like 'Ca%';	Like是通配符，%表示出现任意次数 找出所有由Ca开头的region，不需要准确给出匹配的值 %Ca%匹配任意位置上包含Ca的值，不论前后出现了什么字符
select  concat(region,' | ',name) from world.country	Concat(a,b,c,…)拼接a,b,c
select  concat(region,' | ',name) as new_column from world.country	拼接后按照new_column（别名）这一列来返回，任何客户端都可以使用这一列
select  Avg(Population*IndepYear) as avg_column from world.country	求出Population*IndepYear的平均值，别名为avg_column
select  count(Population) as count_column from world.country	对有population值的行计数
select  max(Population) as max_column from world.country	返回population的最大值
select sum(Population>=0) as sum_column from world.country;	返回Population>=0的行数之和
select sum(Population) as sum_column from world.country;	返回所有的population之和
select sum(distinct Population) as sum_column from world.country;	返回所有不同的poulation的和（默认为all）
select region, count(*) as count_column from world.country group by region;	按照region分组，然后计算每个组内包含的行数
select region,count(*) as count_column from world.country group by region having count_column>5;	Having类似于where，不同的是where过滤的对象是行，而having过滤的对象是分组，只显示count_column大于5的分组
alter table world.country add sex int;	增加一列，列名为add，数据的类型为int
alter table world.country drop sex;	删除sex这一列
alter table world.country modify population  int ;	更改population列的属性
alter table world.country change column population abc int;	修改population列名为abc，int为属性
update world.country set population=0 where code="aia";	更新where找出的行的population值为0