WordPress的必备SQL语句

2009-12-29 by 嘉瑜,   Category: 网址及工具,   Tags: WordPress No Comments  68次浏览  

1. Post类型只有4种：private publish draft inherit

2. 分类/tag与post相关的四张主表：
wp_term_taxonomy 命名表 category <->  term_taxonomy_id)   
字段：term_taxonomy_id    term_id    taxonomy    description    parent    count

wp_term_relationships  存放目录/tag与日志的对应关系数据
字段：object_id    term_taxonomy_id    term_order

wp_post  存放日志  
字段：post_author post_date    post_date_gmt    post_content    post_title    post_excerpt    post_status    comment_status    ping_status

wp_terms 存放目录/Tag
字段：term_id    name    slug    term_group    term_order

3. 建几个有用的视图：

-- 列出全部日志 --
create view v_post as
SELECT p.*, tax.term_taxonomy_id,tax.term_id,category.name, tax.count
FROM
wp_term_relationships relation,
wp_terms category,
wp_term_taxonomy tax,
wp_posts p
WHERE
category.term_id = tax.term_id and
tax.term_taxonomy_id = relation.term_taxonomy_id and
relation.object_id =  p.id 

--- 看每个分类下实际有多少日志（包含private) ---
alter view v_postCountByTerm as
SELECT count(1), tax.taxonomy, category.term_id,category.name
FROM
wp_term_relationships relation,
wp_terms category,
wp_term_taxonomy tax,
wp_posts p
WHERE
category.term_id = tax.term_id and
tax.term_taxonomy_id = relation.term_taxonomy_id and
relation.object_id =  p.id
group by  tax.taxonomy, category.term_id,category.name
order by category.name

-- 看每个目录下有多少私有日志 --
create view v_adminPost as
SELECT count(1), tax.taxonomy, category.term_id,category.name
FROM
wp_term_relationships relation,
wp_terms category,
wp_term_taxonomy tax,
wp_posts p
WHERE
p.post_status = 'private' and
category.term_id = tax.term_id and
tax.term_taxonomy_id = relation.term_taxonomy_id and
relation.object_id =  p.id
group by  tax.taxonomy, category.term_id,category.name
order by category.name

-- 查看某个目录下是否有私有日志 --
SELECT id
FROM  `v_postCountByTerm` c, v_post p
WHERE c.term_id = p.term_id
AND p.post_status =  'private'
AND c.term_id =27

4. 几个有用的SQL

-- 删除未使用过的TAG --
DELETE a,b,c
FROM wp_terms AS a
LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id
LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE c.taxonomy = 'post_tag' AND c.count = 0

-- 删除所有文章修订版本(Revisions)以及它们的Meta数据 --
DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

-- 更改正文中的链接地址 --
UPDATE wp_posts
SET post_content = REPLACE (post_content, 'www.旧网址.com', 'www.新网址.com');
UPDATE wp_postmeta
SET meta_value = REPLACE (meta_value, 'www.旧网址.com', 'www.新网址.com';
UPDATE wp_comments
SET comment_author_url = REPLACE( comment_author_url, '旧网址.com', '新网址.com' )
UPDATE wp_posts
SET guid = REPLACE (guid, 'www.旧网址.com', 'www.新网址.com')

--- 导出所有评论中的邮件地址 --
SELECT DISTINCT comment_author_email
FROM wp_comments

-- 删除所有的垃圾评论 --
DELETE FROM wp_comments
WHERE comment_approved = 'spam';
DELETE from wp_comments WHERE comment_approved = '0';

-- 更新正文内所有的’target=”_blank”‘为’rel=”nofollow”‘ --
UPDATE wp_posts
SET post_content = REPLACE (post_content, 'target="_blank',  'rel="nofollow')

-- 重置密码 --
UPDATE `wp_users` SET `user_pass` = MD5('PASSWORD') WHERE `wp_users`.`user_login` =`admin` LIMIT 1;

Tags: WordPress