mysql：最實用的sql語句 1.把catalog_product_entity_text表字段裡面的<a href="mailto:替換為<a rel="nofollow" href="mailto: www.2cto.com UPDATE `catalog_product_entity_text` SET `value` = REPLACE(`value`, '<a href="mailto:', '<a rel="nofollow" href="mailto:') WHERE INSTR(`value`,'<a href="mailto:') > 0 2.給表添加一個列 ALTER TABLE `isc_product_tags` ADD `bigtags` VARCHAR(20) （1）刪除多余的行數 www.2cto.com delete FROM `isc_product_tags` WHERE `tagname`='' 3.刪除表的數據 TRUNCATE TABLE `directory_country` 4.關聯查找tags表裡的tags的相關的其他tags： (1)查找tags表裡的tags的相關的其他tags： SELECT * FROM `isc_product_tags` WHERE `bigtags`=(SELECT `bigtags` FROM `isc_product_tags` WHERE `tagfriendlyname`='720p-car-dvr') and `tagfriendlyname` != '720p-car-dvr' ORDER BY 'tagname' DESC; (2)查找某個產品的所有關聯tags： SELECT * FROM `isc_product_tags` WHERE `tagid`in(SELECT `tagid` FROM `isc_product_tagassociations` WHERE `productid`=(SELECT `productid` FROM `isc_products` WHERE `prodname`='Mega Pixel 720p HD IR Array Waterproof Network TF Storage Camera')) SELECT * FROM `isc_product_tags` WHERE `tagid` in(SELECT `tagid` FROM `isc_product_tagassociations` WHERE `productid`=(SELECT `productid` FROM `isc_products` WHERE `prodname`='5 Megapixel Sensor Full HD 1080P Outdoor IP Camera 120m IR Night View ')) ORDER BY tagname ASC 5.更改產品價格： UPDATE`isc_products` SET `prodretailprice` = '92.0000', `prodsaleprice`='69.0000' WHERE `productid` =1605; 6.導入導出數據庫命令：（bin目錄下） 導出：mysqldump -u root -p bokele >c:/mysql.sql --default-character-set=utf8 導入：C:\mysql\bin\> mysql -u root -p <C:\helloapp\schema\sampledb.sql --default-character-set=utf8 說明：C:\mysql\bin\表示進入mysql程序根目錄 C:\helloapp\schema\sampledb.sql是要導入數據庫的文件的位置 c:/mysql.sql是導出的sql文件 --default-character-set=utf8 指編碼方式 7.建新表 CREATE TABLE IF NOT EXISTS `isc_pluginproduct_association` ( `id` int(11) NOT NULL AUTO_INCREMENT, `main_cat_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=66 ; INSERT INTO `isc_pluginproduct_association` (`id`, `main_cat_id`, `product_id`) VALUES (65, 31, 24), (64, 31, 18), (63, 31, 23), (62, 32, 24), (61, 32, 23), (60, 32, 28), (51, 38, 26), (16, 36, 14), (15, 36, 15), (50, 38, 23), (49, 38, 22), (48, 38, 21), (47, 38, 20), (46, 38, 18), (45, 38, 16), (44, 38, 12), (43, 38, 10), (42, 38, 15), (41, 38, 17), (40, 38, 13), (57, 39, 16), (56, 39, 15), (55, 39, 14); 8.多個and條件的查詢語句 （1）myphp的sql語句： SELECT * FROM `isc_products` WHERE `prodcode` = 'IP-Z0144' AND `prodname` LIKE '%Tilt WiFi %' AND `prodprice` = 144.00 LIMIT 0 , 30 9.把產品名字中的特殊符號和空格替換為-號，查找產品 SELECT * FROM `isc_products` WHERE `prodname` like '%5%' and `prodname` like '%Megapixels%' and `prodname` like '%1%' and `prodname` like '%2.5%' and `prodname` like '%Sensor%' and `prodname` like '%720P%' and `prodname` like '%Outdoor%' and `prodname` like '%IP%' and `prodname` like '%Camera%' and `prodname` like '%40m%' and `prodname` like '%IR%' and `prodname` like '%Night%' and `prodname` like '%View%' 10.寫一個數據庫的語句：將A表裡，B字段中第一個C字符替換成D (1) 查詢`isc_search_corrections`表的`correction`字段的首字母 SELECT SUBSTRING(`correction`,1,2) FROM `isc_search_corrections` (2) 更新：`isc_search_corrections`表，`correction`字段含有首字母C的替換首字母為D UPDATE `isc_search_corrections` SET `correction` = REPLACE(`correction`, 'C', 'D') WHERE SUBSTRING(`correction`,1,2)='C'
