MYsql 函数收集(重命名)

DELETE FROM `a2en_term_relationships` WHERE `object_id`>=3624 and`object_id`<=8619


select CONCAT('ALTER TABLE ', table_name ,' RENAME TO ', table_name,';')
FROM information_schema.tables
Where table_name LIKE 'a_%' and TABLE_SCHEMA='lzz_ecms'




update wp_posts set post_name=substring(post_name,2) where substring(post_name,1,1)='-'
update wp_posts set post_name=substring(post_name,2) where substring(post_name,1,1)='-'


select * from wp_posts where substring(post_name,1,1)='-'
select substring(post_name,2) from wp_posts where id=2



去最后一个-号
select substring(post_name,1,length(post_name)-1),length(post_name),post_name from wpa_posts where substring(post_name,-1,1)='-'


update wp_posts set post_name=substring(post_name,1,length(post_name)-1) where substring(post_name,-1,1)='-'

eg：update phome_ecms_product set productpic=substring(productpic,7) where substring(productpic,1,7)="/ecms2/"

修改news表中title长度为1000
ALTER TABLE  phome_ecms_news CHANGE  title  title VARCHAR( 2000 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT  ''

默认值：
ALTER TABLE  `phome_ecms_news` CHANGE  `midpicend`  `midpicend` VARCHAR( 15 ) CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL DEFAULT  '</div></div>'

update phome_ecms_product set titlepic=concat('/ecms6',titlepic)




DELETE FROM `test2.com`.`phome_ecms_news` WHERE classid = 63;
DELETE FROM `test2.com`.`phome_ecms_news_data_1` WHERE classid = 63;




UPDATE  phome_ecms_news SET  title = REPLACE(  title,  'metso',  'Kefid' ) WHERE  title LIKE  '%metso%'
UPDATE  phome_ecms_news_data_1 SET  newstext = REPLACE(  newstext,  'Metso',  'Kefid' ) WHERE  newstext LIKE  '%metso%'

UPDATE  phome_ecms_news SET  title = REPLACE(  title,  'Sandvik',  'Kefid' ) WHERE  title LIKE  '%Sandvik%'

UPDATE  aaen_posts SET  post_content = REPLACE(  post_content,  'KEFID',  'Liming' ) WHERE  post_content LIKE  '%KEFID%'
update b1ru_posts set post_content=replace(post_content,'http://www.drobilka-kz.com/','/') where post_content like '%http://www.drobilka-kz.com/%'

去掉里面的--
select post_name from  wp_posts  WHERE  post_name LIKE  '%--%'
UPDATE  wp_posts SET  post_name = REPLACE(  post_name,  '--',  '-' ) WHERE  post_name LIKE  '%--%'
select REPLACE(  newstext,  'metso',  'Kefid' ) from   phome_ecms_news_data_1  WHERE  newstext LIKE  '%metso%'
select * from phome_ecms_news where title like "%metso%"
select * from phome_ecms_news_data_1 where newstext like '%metso%'

随机查询：
SELECT * FROM table_name ORDER BY rand() LIMIT 5;


MySQL截取字符串函數方法
今天建視圖時，用到了MySQL中的字符串截取，很是方便。
感覺上MySQL的字符串函數截取字符，比用程序截取（如PHP或JAVA）來得強大，所以在這裏做壹個記錄，希望對大家有用。
函數：
1、從左開始截取字符串
left（str, length）
說明：left（被截取字段，截取長度）
例：select left（content,200） as abstract from my_content_t
2、從右開始截取字符串
right（str, length）
說明：right（被截取字段，截取長度）
例：select right（content,200） as abstract from my_content_t
3、截取字符串
substring（str, pos）
substring（str, pos, length）
說明：substring（被截取字段，從第幾位開始截取）
substring（被截取字段，從第幾位開始截取，截取長度）
例：select substring（content,5） as abstract from my_content_t
select substring（content,5,200） as abstract from my_content_t
（註：如果位數是負數 如-5 則是從後倒數位數，到字符串結束或截取的長度）
4、按關鍵字截取字符串
substring_index（str,delim,count）
說明：substring_index（被截取字段，關鍵字，關鍵字出現的次數）
例：select substring_index（”blog.chinabyte.com”，”。”，2） as abstract from my_content_t
結果：blog.chinabyte
（註：如果關鍵字出現的次數是負數 如-2 則是從後倒數，到字符串結束）
4、SUBSTRING_INDEX(str,delim,count) 返回字符串 str 中在第 count 个出现的分隔符 delim 之前的子串。如果 count 是一个正数，返回从最后的(从左边开始计数)分隔符到左边所有字符。如果 count 是负数，返回从最后的(从右边开始计数)分隔符到右边所有字符。 mysql>SELECT SUBSTRING_INDEX('www.baidu.com', '.', 2); -> 'www.baidu' mysql> SELECT SUBSTRING_INDEX('www.baidu.com', '.', -2); -> 'baidu.com' 5、将表里面的 field字段的str替换为str1 原表里的
replace(field,str,str1)
name字段的值是你好abc; UPDATE table SET name=REPLACE(name, 'abc', '123'); 替换完以后的值 为你好123


select substring(post_name,1,length(post_name)) from wp_posts where substring(post_name,-1)='-'

updata wp_posts set post_name=substring(post_name,1,length(post_name)-1) where substring(post_name,-1)='-'










select CONCAT('ALTER TABLE ', table_name ,' RENAME TO ', table_name,';')
FROM information_schema.tables
Where table_name LIKE 'a_%' and TABLE_SCHEMA='lzz_ecms'