一些mysql的操作语句

1.模糊查询(instr比like效率高)
select cust_id from tf_f_sale where instr(title,'供应托盘')>0 <=>select cust_id from

tf_f_sale where instr(title,'供应托盘')>0 AND 1

select cust_id from tf_f_sale where instr(title,'供应托盘')>0 AND 0 IS NULL

2.日期操作

select TO_DAYS('2009-10-10') -TO_DAYS('2009-10-1') --9 <=>select date('2009-10-10')-date

('2009-11-1')

to_days(publish_date) is a value: to_days(2009-06-27 03:11:32)=733950
date(now()) is a date: 2009-11-20

------------取近30天记录
select * from tf_f_sale where TO_DAYS(NOW()) -TO_DAYS(publish_date)<30

select * from tf_f_sale where datediff(now(),publish_date)<30

----注意下面是不能计算的，本月可以，但 select date('2009-11-01')-date('2009-10-26') ：75
select * from tf_f_sale　WHERE date(now())-date(publish_date)<30

3.聚合函数

select count(*) ct from (select *from tf_f_bank) tt --必须有tt

SELECT COUNT(DISTINCT per_id) ct FROM tf_f_leave_info

4.limit

SELECT *FROM tf_f_news LIMIT 7 -- 错误：SELECT *FROM tf_f_news where LIMIT 7

SELECT *FROM td_b_channel WHERE up_ch_id='0000000000' LIMIT 2--正确

5.按规则组串

SELECT CONCAT(REPEAT('0',10-LENGTH(shop_price)+3),LEFT(shop_price,LENGTH(shop_price)-3)) FROM zl_goods
SELECT LPAD(LEFT(shop_price,LENGTH(shop_price)-3),10,'0') FROM zl_goods

6.清空表的所有记录

TRUNCATE td_b_productattr 效率比 DELETE FROM td_b_productattr 高

7.复制表

INSERT INTO zl_product (product_id,cust_id,title,content,publish_date)

SELECT product_id,'L5NpmXbvofh4PDn',product_name, product_desc,NOW() FROM tf_f_product