MySQL数据库查询 学习笔记

WP网站数据库导入操作

CREATE DATABASE `yhrshop-com` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;   #创建数据库
use database; # 切换数据库
source /web/html/sql/shenhe1/ucovshop-210126.sql;
UPDATE `wp_options` SET option_value = REPLACE (option_value, 'ucovshop.com', 'yhrshop.com' ); 

WP网站数据库导出操作（在数据库命令行外面执行）

mysqldump -uroot -p database > database.sql 

stockxstoress.com多表联合查询sql语句

SELECT
    w.product_id,
    w.price,
    w.image,
    w.weight,
    a. NAME,
    a.description,
    a.tag,
    a.meta_description,
    a.meta_keyword,
    a.meta_title,
    GROUP_CONCAT(DISTINCT c.image) AS 'image2',
    GROUP_CONCAT(DISTINCT e. NAME) AS 'category_name',
    GROUP_CONCAT(DISTINCT g. NAME) AS 'sku_name'
FROM
    oc_product w
LEFT JOIN oc_product_description a ON w.product_id = a.product_id
LEFT JOIN oc_product_image c ON w.product_id = c.product_id
LEFT JOIN oc_product_to_category d ON w.product_id = d.product_id
LEFT JOIN oc_category_description e ON d.category_id = e.category_id
LEFT JOIN oc_product_option_value f ON w.product_id = f.product_id
LEFT JOIN oc_option_value_description g ON f.option_value_id = g.option_value_id
GROUP BY
    w.product_id;

kissgoddesss 爬虫 子查询 查找t_info表中 主页链接跟图片同时不为空的所有人物

SELECT * FROM t_info WHERE id in (SELECT DISTINCT(info_id) FROM t_photos WHERE info_id in  (SELECT id FROM t_info where links != '' ))

雅文小说爬虫 本地查询

-- SELECT * FROM rf_novel_chapter WHERE novel_id = 395 ORDER BY chapter_no LIMIT 10;
-- SELECT * FROM rf_novel_chapter WHERE novel_id = 396 ORDER BY chapter_no LIMIT 10;
-- SELECT id FROM rf_novel WHERE title = '一指成仙';  -- 211
-- SELECT title from rf_novel WHERE isSync = 0;

-- SELECT id,novel_id, title,content,chapter_no,isSync FROM rf_novel_chapter WHERE novel_id = 337 ORDER BY chapter_no ASC;

-- SELECT * from rf_novel_chapter where isSync = 0 and novel_id = 185; -- 查看chapter章节表中isSync为1的数据
-- UPDATE rf_novel_chapter set isSync = 0 WHERE novel_id = 262 AND chapter_no > 113; -- 更新chapter章节表中选中的章节isSync为0
-- UPDATE rf_novel set ifNeedSync = 0 WHERE id = 185; -- 更新novel表中 是否需要更新 ifNeedSync 为1 ：需要

-- SELECT * from rf_novel_chapter where isSync = 1; -- 查看chapter章节表中isSync为1的数据
-- SELECT COUNT(novel_id) as num  FROM rf_novel_chapter WHERE novel_id BETWEEN 1 and 395 and isSync = 0 ORDER BY novel_id; -- 统计chapter章节表中选中的小说总章节数
-- SELECT b.title,count(a.novel_id) as num FROM rf_novel_chapter as a LEFT JOIN rf_novel as b ON a.novel_id = b.id  GROUP BY b.id ORDER BY b.title asc; -- 关联表查询 小说各个章节的总数
-- UPDATE rf_novel_chapter set isSync = 0 WHERE novel_id not in (1,211); -- 更新chapter章节表中选中的章节isSync为0
-- UPDATE rf_novel set ifNeedSync = 0 WHERE id BETWEEN 271 and 395; -- 更新novel表中 是否需要更新 ifNeedSync 为0 ：不需要
-- SELECT DISTINCT title FROM rf_novel_chapter WHERE novel_id = 8 ; -- 过滤title中重复的数据
-- 查询小说章节中重复的title小说章节名数据
-- SELECT id, title FROM rf_novel_chapter WHERE id IN (SELECT MIN(id) FROM rf_novel_chapter  WHERE novel_id = 8 GROUP BY title HAVING COUNT(*) > 1)
-- 查询小说章节中所有重复的title小说章节名数据
-- SELECT * FROM rf_novel_chapter WHERE (title) IN (SELECT title FROM rf_novel_chapter WHERE novel_id = 337 GROUP BY title HAVING COUNT(*) > 1) ORDER BY chapter_no
-- UPDATE rf_novel_chapter set isSync = 0 WHERE novel_id = 20; -- 更新chapter章节表中选中的章节isSync为0

雅文小说爬虫 正式库查询

-- SELECT  b.n_name,count(a.n_book_id) as num FROM t_book_chapter as a LEFT JOIN t_book_data as b ON a.n_book_id = b.n_id  WHERE b.n_name in ('444号婚介所') AND b.n_deleted = 0 GROUP BY b.n_id ORDER BY b.n_name asc; -- 关联表查询 

-- SELECT * from t_book_data ORDER BY n_id DESC LIMIT 10;
-- SELECT * from t_book_i18n ORDER BY n_id DESC LIMIT 10;
-- SELECT * from t_book_chapter ORDER BY n_id DESC LIMIT 10;
-- SELECT * from t_book_chapter_i18n ORDER BY n_id DESC LIMIT 10;

-- SELECT * from t_book_chapter WHERE n_book_id = 644 LIMIT 10;

-- SELECT n_id FROM t_book_data WHERE n_name = '极品穿越之斗战胜佛';
SELECT n_id,n_name,n_content,n_chapter_no,n_size,n_trans_status FROM t_book_chapter WHERE n_book_id = (SELECT n_id FROM t_book_data WHERE n_name = '轮盘世界' and n_deleted = 0) ORDER BY n_chapter_no asc;

-- SELECT n_id,n_title,n_content,n_chapter_no FROM t_book_chapter_i18n WHERE n_book_chapter_id in ('48801','48812','48823','48575','48586','48597','48608','48619','48630','48641','48652','48663','48674','48686','48697','48708','48719','48730')
-- 
-- DELETE  FROM t_book_chapter WHERE n_book_id = (SELECT n_id FROM t_book_data WHERE n_name = '白鹿原') AND  n_chapter_no > 113;   -- 删除指定小说的章节