函数isnull(check_expression , replacement_value )
例:查询没有邮箱且年龄大于20岁的教师信息:
select * from teachers
where email is null and age>20;
select id,name, email,age,country
from teachers
where isnull(email) and age > 20
limit函数
例:第二高的球员的身高
select ifnull((select distinct height from players
order by height DESC
limit 1,1),null) as second_height
#limit 1,1表示从第二行开始检索,共检索一行数据
from子句中的子查询:查询结果作为临时表,与原表单连接或删除
例:删除重复的姓名
delete
from contacts
where id not in (select * from(select min(id) from contacts group by name)t)
# t是临时表的命名
函数DATEDIFF(date1,date2):返回date1 - date2之间相隔的天数
例:查找与前一天的日期相比美国的新增病例数更高的所有日期的 id
select n2.id
from new_cases n1 join new_cases n2 #join相当于inner join ,就是普通的连接,3x4表join后有12行
where datediff(n2.date,n1.date)=1 and n2.increased_count>n1.increased_count