#查询
#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) 将之转换为日期类型. 需要注意:
长度超过一定位数后,需要截取前面的几位才可以.否则不能正确转换.
#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) 将之转换为日期类型. 需要注意:
长度超过一定位数后,需要截取前面的几位才可以.否则不能正确转换.