mysql sql灵活运用-白红宇

mysql sql灵活运用

阅读量：5309 次

发布时间：2019-06-14

本文共 2911 字，大约阅读时间需要 9 分钟。

1.mysq获取表的字段及属性

SELECT * FROM information_schema.COLUMNS WHERE table_name = 'item_url';

2.正则表达式

SELECT i.*,c.brand FROM item_base AS i LEFT JOIN item_base AS c ON i.item_id = c.item_id WHERE c.brand IN ('HSTYLE/韩都衣舍','INMAN/茵曼','OSA','ONLY','裂帛') AND i.item_id NOT REGEXP "yihaodian_[0-9]+"

SELECT * FROM item_base WHERE item_id REGEXP 'yihaodian_[^_]+_[0-9]+'

SELECT i.item_id, i.item_name, i.cid, i.last_update_time, u.url, u.image_count, u.local_image_address_list, u.image_urls_list, u.original_class FROM item_base AS i LEFT JOIN item_url AS u ON IF(i.item_id LIKE 'yihaodian_%_%',u.item_id = CONCAT('yihaodian',SUBSTRING(i.item_id,POSITION('_' IN SUBSTRING(i.item_id,11))+10)),i.item_id = u.item_id) LIMIT 0,10

3.count(*)返回查询结果数目

SELECT COUNT(*) FROM item_base WHERE brand IN ('HSTYLE/韩都衣舍','INMAN/茵曼','OSA','ONLY','裂帛') AND item_id REGEXP 'yihaodian_[0-9]+'

SELECT COUNT(*) FROM item_attr WHERE item_id REGEXP 'yihaodian_[0-9]+'

SELECT COUNT(*) FROM item_url WHERE item_id REGEXP 'yihaodian_[0-9]+'

SELECT * FROM item_base WHERE valid = 1 AND item_id REGEXP 'yihaodian_[^_]+_[0-9]+'

SELECT * FROM item_attr WHERE item_id REGEXP 'yihaodian_[^_]+_[0-9]+'

SELECT * FROM item_url WHERE item_id REGEXP 'yihaodian_[^_]+_[0-9]+'

SELECT * FROM item_url WHERE item_id REGEXP 'yihaodian_[0-9]+'

DELETE FROM item_attr WHERE item_id REGEXP 'yihaodian_[^_]+_[0-9]+'

4.sql in java ：

try {

String field_list = Arrays.toString(fields);

String field_listc = Arrays.toString(commentsFields);

String field_listp = Arrays.toString(priceFields);

String field_listu = Arrays.toString(urlFields);

String field_lista = Arrays.toString(attrFields);

sql = " select " +

field_list.substring(1, field_list.length() - 1) +

" , " + field_listc.substring(1, field_listc.length() - 1) +

" , " + field_listp.substring(1, field_listp.length() - 1) +

" , " + field_listu.substring(1, field_listu.length() - 1) +

" , " + field_lista.substring(1, field_lista.length() - 1) +

" from " + tableName +

" as i left join " +

ITEM_COMMENTS_TABLE +

" as c on IF(i.item_id LIKE 'yihaodian_%_%',c.item_id = CONCAT('yihaodian',SUBSTRING(i.item_id,POSITION('_' IN SUBSTRING(i.item_id,11))+10)),i.item_id = c.item_id) left join " +

ITEM_PRICE_TABLE +

" as p on IF(i.item_id LIKE 'yihaodian_%_%',p.item_id = CONCAT('yihaodian',SUBSTRING(i.item_id,POSITION('_' IN SUBSTRING(i.item_id,11))+10)),i.item_id = p.item_id) left join " +

ITEM_ATTR_TABLE +

" as a on IF(i.item_id LIKE 'yihaodian_%_%',a.item_id = CONCAT('yihaodian',SUBSTRING(i.item_id,POSITION('_' IN SUBSTRING(i.item_id,11))+10)),i.item_id = a.item_id) left join " +

ITEM_URL_TABLE +

" as u on IF(i.item_id LIKE 'yihaodian_%_%',u.item_id = CONCAT('yihaodian',SUBSTRING(i.item_id,POSITION('_' IN SUBSTRING(i.item_id,11))+10)),i.item_id = u.item_id) where i.image_complete>=0" +

" and i.last_update_time between 'TIME_START' and 'TIME_END' ";

}

转载于:https://www.cnblogs.com/DjangoBlog/p/3545554.html

你可能感兴趣的文章