MySQL examples/common operations

common_operations.sql

------------------------------------------------------------------
-- alter column name
ALTER TABLE `xyz` CHANGE `manufacurerid` `manufacturerid` INT;

------------------------------------------------------------------
-- export database 
------------------------------------------------------------------
mysqldump db table > filename.out
------------------------------------------------------------------
-- import database 
------------------------------------------------------------------

mysql db < filename.out
-------------------------------------------------------------------
-- Get # of chars in a text field:
char_length(<string>)
-------------------------------------------------------------------

-------------------------------------------------------------------
-- Delete Row of table
delete from orders 
where id_users = 1 and id_product = 2
-- http://stackoverflow.com/questions/18378190/how-to-delete-a-certain-row-from-mysql-table-with-same-column-values#18378264
-------------------------------------------------------------------

-------------------------------------------------------------------
-- Get in depth info about innodb table issues for debugging
-- http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html
-------------------------------------------------------------------

set GLOBAL innodb_status_output=ON;
-------------------------------------------------------------------
-- List directories used by mysql (data directories)
mysql -u USER -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'
-------------------------------------------------------------------

----------------------------------------------------
-- ANTI JOIN METHODS
----------------------------------------------------

-- Anti Join - All rows of table 1 that don't join to some value in table 2
SELECT <fieldname1>, Name
FROM Table1
WHERE <fieldname> NOT IN (
    SELECT <fieldname2> 
    FROM Table2;
);

-- Anti Join: Method 2 source: https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
SELECT  l.*
FROM    t_left l
LEFT JOIN
        t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

-------------------------------------------------------
-- BOOLEAN TEXT SEARCH
-------------------------------------------------------
-- ********** IMPORTANT NOTE: Any word less than 4 characters should not be searched using 
-- match/IN boolean mode, use LIKE *******************

-- double quotes indicate match both words in the order they appear
-- no symbol between the terms indicates OR
-- query returns all row where the terms 'acide reflux' 
-- or 'attention deficit appears'
SELECT * FROM table_abc WHERE MATCH(message_field) 
against('"attention deficit" "acid reflux"' IN boolean mode
)

------------------------------------------------------------
-- APPENDING COLUMNS
-- SET VALUE OF COLUMN BASED ON A PRE-EXISTING COLUMN VALUE
------------------------------------------------------------

-- After creating the table:
-- Append columns 
ALTER TABLE chris.dummy_test ADD COLUMN lotsafriends INT DEFAULT 0;
-- Set new values in column based on another column 
update chris.dummy_test
  set lotsafriends = 1
  where friend_count > 500; 

-- NOTE: If the primary key is not used in the 'where' clause, 
-- safe update feature has to be turned off . 

------------------------------------------------------------
-- JOIN A PRE-EXISTING TABLE WITH THE RESULT OF A 
-- SELECT STATEMENT (see: http://stackoverflow.com/questions/2576770/how-can-join-a-query-result-set-with-an-existing-table)
------------------------------------------------------------
SELECT message_id, value FROM table_name1 x
INNER JOIN (SELECT message_id FROM table_name2  where id > 5 limit 10) y
ON x.message_id = y.message_id

------------------------------------------------------------
------------------------------------------------------------