查询单列
select xxx from table;
查询多列
select xx,xx,xx,xxfrom table;
查询所有列
select * from table_name
distinct单列
select distinct xx from table;
排序(默认升序)
select xxx from table order by xx;
降序排序
select xx order by xx DESC;
where等值
select * from table where xxx = xxx;
where between x and x;
where xx != xxx;
NULL值判断
无值 no value
select * from table where xx is null;
分页查询
取出前几个数据
select xx from table limit x;
取出范围数据(从n+1起,m个数据)
select xxx from table limit n,m;
组合where
where and where
where or where;
in操作符
select xx,xx,xx from table where xx in (....);
not操作符
not in,not like,not exists;
like模糊查询;
% 代表通配符,表示任何字符出现任意次数
拼接字段
concat()
别名as
聚合函数
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
count(column)
返回特定列中具有值的额的数量
分组过滤Having
select xxx as xxx from table group by xxx having count(*) >= 3 order by xx des,xx;
子查询,嵌套在其他查询中的查询
select xx from table1 where xx in (select xx from xx where xx = xx );
关联查询
内联inner join
select xxx,xxx,xxx from table1 inner join table2 on xxx.id = xxx.id;
左外联 left outer join
左联会输出左表所有的行,即使没有与右表匹配的,会用null输出替代右表的列
select xxx from table1 left join table2 where xx.id= xx.id;
右外联与左外联相反
全外联 相当于左外联+有右外联(mysql不支持)
Union组合查询
多数sql查询只包含从一个或者多个表中返回单条的select语句,组合查询雨荨执行多个查询,作为一个查询结果集返回。
xxx, xxx,xxx from table where xxx =xx union select xxx,xxx,xxx from table where xx =xxx;
union all
union all不会取消重复的行。