Mysql: the most practical SQL statement

Mysql: the most practical SQL statement 1. replace <a href = "mailto:" <a rel = "nofollow" href = "mailto:" 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. ADD an alter table 'isc _ product_tags' ADD 'bigtags' VARCHAR (20) (1) to the TABLE to delete excessive rows www.2cto.com delete FROM 'isc _ product_ta Gs 'where' tagname' = ''3. delete TABLE data truncate table 'Directory _ country '4. other tags associated with the tags in the tags table: (1) Other tags related to the tags in the tags table: SELECT * FROM 'isc _ product_tags' WHERE 'bigtags' = (SELECT 'bigtags' FROM 'isc _ product_tags' WHERE 'tagfriendlyname' = '720p-car-dvr ') and 'tagfriendlyname '! = '20p-car-dvr 'order BY 'tagname' DESC; (2) Search for all associated tags of a product: SELECT * FROM 'isc _ product_tags' WHERE 'tagid' in (SELECT 'tagid' FROM 'isc _ product_tagassociations 'WHERE 'produd d' = (SELECT 'produd d' FROM 'isc _ products' WHERE 'nginx' = 'Mega Pixel 720 p hd ir Array Waterproof Network TF Storage cama ')) SELECT * FROM 'isc _ product_tags' WHERE 'tagid' in (SELECT 'tagid' FROM 'isc _ product_tagassociations 'WHERE 'produd d' = (SELECT 'produd d' FROM 'isc _ products' WHERE 'nginx' = '5 Megapixel Sensor Full HD 1080 P Outdoor IP Camera 120 m IR Night view ')) order by tagname ASC 5. change product price: UPDATE 'isc _ products' SET 'prodretailprice' = '92. 0000 ', 'maid' = '69. 0000 'where' productid' = 1605; 6. command for importing and exporting Database: (in the bin directory) Export: mysqldump-u root-p bokele> c:/mysql. SQL -- default-character-set = utf8 import: C: \ mysql \ bin \> mysql-u root-p <C: \ helloapp \ schema \ sampledb. SQL -- default-character-set = utf8 Description: C: \ mysql \ bin \ indicates entering the mysql program root directory C: \ helloapp \ schema \ sampledb. SQL is the location of the file to be imported into the database c:/mysql. SQL is the exported SQL file -- default-character-set = utf8 indicates the encoding method 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. query statement for multiple and conditions (1) myphp SQL statement: SELECT * FROM 'isc _ products' WHERE 'prodcode' = 'IP-Z0144 'AND 'prodname' LIKE '% Tilt WiFi %' AND 'prodprice' = 144.00 LIMIT 0, 30 9. replace the special symbols and spaces in the product name, find the product SELECT * FROM 'isc _ products' WHERE 'prodname 'like' % 100' and 'prodname 'like' % Megapixels % 'and 'prodname' like '% 100' and' prodname 'like' % 100' and 'prodname 'like' % Sensor % 'and 'prodname' like '% 2.5% P %' and 'prodname 'like' % Outdoor % 'and' prodname 'like' % IP % 'and 'prodname' like '% Camera %' and 'prodname 'like' % 40 m % 'and 'prodname' like '% IR %' and 'pts' like '% Night %' and 'pts' like '% View %' 10. write A database statement: Replace the first C character in Table A and field B with D (1) query the select substring ('correction ', 1, 2) FROM 'isc _ search_corrections' (2) of the first letter of the 'correction 'field in the 'isc _ search_corrections' table. Update: in the 'isc _ search_corrections 'table, REPLACE the 'correction' field with the first letter C with d update 'isc _ search_corrections 'set 'correction '= REPLACE ('correction', 'C ', 'D ') where substring ('correction', 1, 2) = 'C'