MySQL小知识

1、MySQl的特殊字符：%，_，\；如果想要在sql中使用字符本身，请使用字符“\”进行转义，如“%”应该“\%”

 

2、select into：

UPDATE `tbl_gp` INNER JOIN 
(SELECT gpdm,MAX(createTime) createTime FROM `tbl_gp_remark` GROUP BY gpdm ) b
 ON tbl_gp.`gpdm`=b.gpdm SET remarkTime=b.createTime

 

 

 

3、为mysql数据库中的时间列添加默认当前时间值：

timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

 

 

4、查看执行计划：

EXPLAIN
SELECT DISTINCT db FROM smi WHERE lb IN (3,5,18,44) AND rk BETWEEN '2014-08-14 06:00' AND '2014-08-14 07:59' 
AND DD=266089 AND XJ=570000 AND DB>0 LIMIT 0,20;

 

 

5、获得mysql优化器优化后的查询语句：

EXPLAIN EXTENDED
SELECT DISTINCT db FROM smi WHERE lb IN (3,5,18,44) AND rk BETWEEN '2014-08-14 06:00' AND '2014-08-14 07:59' 
AND DD=266089 AND XJ=570000 AND DB>0 LIMIT 0,20;
SHOW WARNINGS;

 

 

 

4、通过SELECT Sql生成批量update语句：

/*1、对应车线数据更新*/
SELECT CONCAT('UPDATE BD_CXXX SET CORR_CAR_LN=',t2.id,' WHERE ID=',t.id) FROM cxxx t
LEFT JOIN (SELECT id,cxmc FROM cxxx WHERE yxx !=0) t2 ON(t.dycx=t2.cxmc) WHERE t2.cxmc IS NOT NULL;

 

 

/*2、承担主体、承担费用和承担比率更新*/

SELECT CONCAT('UPDATE BD_TJD SET BEA_SUB=\'YD.',cdzt,'\',BEA_EXP=',cdfy,',BEA_RAT=',cdbl,' WHERE CXXX_ID=',id,' AND PATH_PLC=\'YD.',cdzt,'\'')
FROM cxxx WHERE yxx !=0

 

 

 5、按照表名模糊查找对于的字段名

SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME LIKE '%OfflineActivity%';

 

 

6、更改自增字段起始值

ALTER TABLE test AUTO_INCREMENT=30000;