1 DELETE from Person Where Id not in ( Select MIN(Id) as Id From Person Group by Email ) //执行结果错误
原因:mysql 不能先将select出表中的某些值,再这更新个表! 类似于java中的并发修改异常
正确:DELETE from Person
Where Id not in (
Select Id
From(
Select MIN(Id) as id
From Person
Group by Email
) t
)
2查询某一字段不重复的值
SELECT DISTINCT(testtext) from dome;
或者:SELECT testtext from dome GROUP BY testtext
distinct 和group by
3某一字段不为某值(可以为int,varchar…) ‘<>’ ,‘not’
SELECT * FROM dome where not testtext =‘张三’
SELECT * FROM dome where testtext <>‘张三’
SELECT * from dome where testtext not in (‘张三’)
not in 可以适用于多个,也可以用在一个上 not效率比<>高5ms
4(inner(可以省略))join 内连接 做笛卡尔级 两张表分别为m,n行结果就是m*n 行,inner join on 后面加上条件表示对这个结果进行筛选
dateDiff(a.RecordDate,b.RecordDate) = 1 日期间隔函数