整理摘录自:
1.MySQL,从入门到熟练
2.SQL,从熟练到掌握
更多内容可参考 :
1.leetcode 相关练习
2.更多其它SQL语句
1.基础查询
语句 | 功能 |
select column from table | 最小化查询结构 |
select * from table | 选择所有列 |
select * from table where column=”X” or column =”Y” | 基础条件判断 |
select * from table where column in (“X”,”Y”,”Z”) | 基础多条件判断 |
> 、>=、 、、!= | 数值逻辑判断 |
select * from table where column between n1 and n2 | 数值区间判断[n1,n2] |
select * from table where column like “%ABC%” | 模糊查询 |
not in not like not null | 否定关键字 |
select * from table group by column1,column2 | 按列1,列2进行分组(合并同类),顺序为从做导游 |
select * from table order by column1 | 按列1进行排序(合并同类) |
select column1 count(column1) as num from table order by num | 对列1进行计数,并取别称和排序 |
select column1 count(distinct column1) as num from table order by num | 对列1进行非重复计数 |
select column1 count(distinct column1) as num from table group by name having num>=X | 用having对聚合(group by )进行过滤,无聚合则无需having,用where即可 |
select * from(select column1 count(distinct column1) as num from table ) as t1 where num >=X | 嵌套子查询,以子查询的列作为条件 |
2.函数相关查询
函数相关 | 功能 |
select now() | 返回当前时间,以datetime格式 |
select date(now()) | 返回当前时间,以date格式 |
select week(now(),0) | 返回当前星期数,0表示起始为周一 |
select date_add(date(now()) ,interval 1 day) | 返回日期加上时间间隔乘以单位的日期 |
数据清洗类 | |
select locate(“k”,column1),salary from table | 定位列1中k的位置 |
select left(column1,locate(“k”,ccolumn1)-1) from table | 取下标k-1及其左边的内容 |
select substr(column1,locate(“-“,column1)+1,length(column1)- locate(“-“,column1)-1) from table | 取‘-’和‘-’开始的‘k’之间的内容 |