Suggest a feature
×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Dylan Lopez
Added: Jan 19, 2018 8:08 AM
Modified: Jan 19, 2018 8:10 AM
Views: 22
Tags: no tags
Very useful
  1. -- Empty a Column
  2. UPDATE TABLE_NAME SET prize = ''
  3.  
  4. -- Duplicate a Table
  5. CREATE TABLE newtable LIKE oldtable;
  6. INSERT newtable SELECT * FROM oldtable;
  7.  
  8. --  Convert Database Engine
  9. ALTER TABLE bf_lyrics ENGINE = MyISAM;
  10. ALTER TABLE bf_lyrics ENGINE = InnoDB;
  11.  
  12. --  Search FULLTEXT index
  13. SELECT `prod_id`
  14. FROM (`product_search`)
  15. WHERE MATCH (`prod_title`, `prod_desc`) AGAINST ('silk' IN BOOLEAN MODE)
  16. OR `prod_id` LIKE '%silk%' OR `prod_sku` LIKE '%silk%'
  17. OR `prod_title` LIKE '%silk%'cat
  18. OR `prod_desc` LIKE '%silk%';
  19.  
  20. -- Grant user permission on a Database
  21. ALL PRIVILEGES ON mydb.* TO USER@'localhost' IDENTIFIED BY 'password';GRANT
  22. GRANT remote access TO specific IP ALL DATABASE
  23. GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
  24.  
  25. -- Revoke permision:
  26. REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';
  27.  
  28. -- Check permission
  29. SELECT * FROM information_schema.user_privileges WHERE grantee LIKE "'USERNAME'%";
  30.  
  31. -- Copy table between databases ----
  32. IF you have shell access you may USE mysqldump TO dump the content OF database1.table1 AND pipe it TO mysql TO database2. The problem here IS that table1 IS still table1.
  33. mysqldump --user=user1 --password=password1 database1 table1 \
  34. | mysql --user=user2 --password=password2 database2
  35. Maybe you need TO RENAME table1 TO table2 WITH another query. ON the other way you might USE sed TO CHANGE table1 TO table2 BETWEEN the TO pipes.
  36. mysqldump --user=user1 --password=password1 database1 table1 \
  37. | sed -e 's/`table1`/`table2`/' \
  38. | mysql --user=user2 --password=password2 database2
  39. IF table2 already EXISTS, you might ADD the parameters TO the FIRST mysqldump which dont let CREATE the table-creates.
  40. --\ Copy table between databases ----
  41. mysqldump --no-create-info --no-create-db --user=user1 --password=password1 database1 table1 \
  42. | sed -e 's/`table1`/`table2`/' \
  43. | mysql --user=user2 --password=password2 database2
  44.  
  45. -- Import CVS
  46. "load data local infile 'ZALORA_SG-Product_Feed.txt' into table bf_datafeed_zalora
  47. fields terminated by ','
  48. enclosed by '""'
  49. lines terminated by '\n';"
  50. INSERT INTO a MySQL TABLE OR UPDATE IF EXISTS
  51. INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE  
  52. name="A", age=19
  53. SELECT duplicate ROWS
  54. SELECT `id` FROM `table_name` GROUP BY `simple_sku` HAVING COUNT(*) >= 2
  55. DELETE Dupplicate ROWS IN MySQL
  56. DELETE e1 FROM `bf_products_perfume` e1, `bf_products_perfume` e2 WHERE e1.`simple_sku` = e2.`simple_sku` AND e1.id > e2.id;
  57.  
  58.  
  59. -- Truncate a table with foreign key
  60. SET FOREIGN_KEY_CHECKS = 0;
  61. TRUNCATE table1;
  62. SET FOREIGN_KEY_CHECKS = 1;
  63. INSERT & FOREIGN KEY
  64. INSERT INTO tab_teacher
  65.   SET name_teacher = 'Dr. Smith';
  66. INSERT INTO tab_student
  67.   SET name_student = 'Bobby Tables',
  68.       id_teacher_fk = LAST_INSERT_ID()
  69.  
  70. -- Search & Replace
  71. UPDATE TABLE_NAME SET FIELD = REPLACE(FIELD, 'foo', 'bar') WHERE INSTR(FIELD, 'foo') > 0;
  72. Reset ID COLUMN
  73. SET @i=0;
  74. UPDATE bf_amz_urls_backup SET id=(@i:=@i+1) ORDER BY `cat`;
  75. CONVERT a String INTO a Slug
  76. LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM('My String'), ':', ''), ')', ''), '(', ''), ',', ''), '\\', ''), '\/', ''), '\"', ''), '?', ''), '\'', ''), '&', ''), '!', ''), '.', ''), ' ', '-'), '--', '-'), '--', '-')) AS `post_name`
  77.  
  78.  
  79. -- Select Random
  80. SELECT t.* FROM myTable AS t JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM myTable)) AS id) AS x WHERE t.id >= x.id LIMIT 1;
  81. READ more: https://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/
  82. Advanced text searching USING FULLTEXT indexes
  83. http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes
  84. GRANT ALL permission
  85. GRANT ALL PRIVILEGES ON TABLE.* TO USER@'%' WITH GRANT OPTION;
  86. INSERT / UPDATE ON exist
  87. INSERT INTO TABLE (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
  88. name="A", age=19
  89.  
  90.  
  91. -- Copy data (records) between two tables:
  92. INSERT destination_table SELECT * FROM source_table;
  93. SELECT DISTINCT ROWS bases ON a COLUMN’s VALUE
  94. SELECT a.*
  95. FROM bf_products_bags_shopee a
  96. INNER JOIN
  97.   (SELECT lazada_id,
  98.     MIN(id) AS id
  99.   FROM bf_products_bags_shopee
  100.   GROUP BY lazada_id
  101. ) AS b
  102.   ON a.lazada_id = b.lazada_id
  103.   AND a.id = b.id;
  104.  
  105. -- Change Auto-increasment Column’s Value
  106. UPDATE bf_products_powerbanks SET `id` = `id` + 200000
  107.  
  108.  
  109. -- Copy table
  110. CREATE TABLE `new_table_name` LIKE `old_table_name`;
  111. INSERT INTO `new_table_name` SELECT * FROM `old_table_name`;
  112.