把主表中在子表中没有的记录完全删除

#查询
#select * from customers where company_name like '%topower%'


/*

select a.* from (select co.`assigned_user`,c.company_name,u.dept,c.create_by, c.id id

from customers c , users u  , `contactors` co

where c.`create_by`=u.id and co.`company_id` = c.id and c.company_name like '%topower%') a

order by a.company_name desc limit 0,200

#select * from contactors where company_id in (20,22,23,28)

#select count(*) from customers_080514_1
#select * from users where id = 5

select count(*)

from customers c

where (select count(*) from contactors where company_id = c.id)=0

delete from customers c
where (select count(*) from contactors co where co.company_id = c.id)=0
*/

delete from customers where id not in(select company_id from conactors);

#select * from contactors where company_id in (20, 72, 79)

delete from customers

#从一个表向另一个同样结构的表插入批量的数据
insert into customers (select * from customers_new )

COMMIT

#mysql 取得下一个自动增长的id .取得这个数据是连接独立的.也就是mysql会自动维护没#一个链接应该拿到的最大id.
#也就是说,有两个链接同时插入进去这个表各一条记录,则mysql会自动返回相同的最后id.

SELECT LAST_INSERT_ID() from createuserid limit 0,1

#返回第一个非空的字符串.如果字段columnname字段中的数据为null,则会返回aaa值.

COALESCE(columnname,'aaa')

#此方法相当与sqlserver中的 isnull(columnname,'default') 返回第一个非空的字符串.

#修复表.如果mysql的表出现灰,不能被读取,并且表的linux用户组也都正确的情况下.使用如下命令修复表.

repair table tablename ;

#如果字段中的时间为long类型的毫秒数. 使用select FROM_UNIXTIME(875996580) 将之转换为日期类型. 需要注意:
长度超过一定位数后,需要截取前面的几位才可以.否则不能正确转换.