MySQL commands

MySQL cheat sheet.sql

# Some _user_ manipulating commands on _database_ (exchange for real data)
CREATE USER '_user_'@'localhost' IDENTIFIED BY '_password_';
GRANT ALL PRIVILEGES ON _database_.* TO '_user_'@'localhost';
SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) UserAccount FROM mysql.user;
SHOW GRANTS FOR '_user_'@'localhost';

# Finding duplicate values http://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql
SELECT myfield, COUNT(*) AS c FROM mytable GROUP BY myfield HAVING c > 1;
SELECT * FROM mytable WHERE myfield IN (SELECT myfield FROM mytable GROUP BY myfield HAVING COUNT(myfield)>1)

# Import these rows into a MySQL table
LOAD DATA LOCAL INFILE '/var/www/html/deansgrads_201280.csv' 
INTO TABLE ttu_nameslist
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(firstname, middlename, lastname, city, county, state, termcode, category)


# Selecting node data for export with domain module
SELECT node.nid, tnid, title, node.language, `body_value`, `body_format`, alias, gid, realm
FROM `node`
JOIN field_data_body ON `node`.`nid` = `field_data_body`.`entity_id`
JOIN `url_alias` ON source = concat('node/',`node`.`nid`)
JOIN `domain_access` ON node.nid = domain_access.nid
WHERE `node`.`type`='page' and `node`.`nid`>11000 and gid=8
ORDER BY `nid`


# Complex case of joining on node and term
SELECT `node`.`nid`, `title`, `taxonomy_index`.`tid`, `name`
FROM `node`
JOIN `taxonomy_index` ON `node`.`nid` = `taxonomy_index`.`nid`
JOIN `taxonomy_term_data` ON `taxonomy_index`.`tid` = `taxonomy_term_data`.`tid`
WHERE `node`.`type`='session'
ORDER BY `nid`

# Delete records on conditition
DELETE FROM `table` where `column` > 4

# An if you want to tack on a limiting clause to an aggregated SQL query, you ll have to use "HAVING x" instead of "WHERE x"
SELECT *, COUNT(desiredcolumn) AS occurances FROM table GROUP BY desiredcolumn HAVING occurances > 5 ORDER BY occurances DESC LIMIT 1

# Drupal sample on join (not safe, should be join on revision) JOIN table ON field=filed, table in prefix!
SELECT `nid`, `title`, FROM_UNIXTIME(`created`), `body_value` FROM `drupal_node` JOIN `drupal_field_data_body` ON `nid` = `entity_id`

# Presenting date
SELECT from_unixtime(timestamp) FROM your_table;

# Replacing content in column in table
UPDATE `table` SET `column` = REPLACE(`column`, 'search', 'replace');

# Copy one column to another on condition
UPDATE `table` SET `destination` = `source`  WHERE `destination`=1;

# Tables status
show table status like '%';

# Calculate each table size in database
SELECT 
  round(((data_length + index_length) / 1024 / 1024),2) AS 'Size in MB',
  TABLE_NAME, table_rows, data_length, index_length 
FROM information_schema.TABLES 
WHERE table_schema = 'database_name'
ORDER by `Size in MB`

# Calculate tables size in database, ignore table name if run without WHERE clause
SELECT table_name AS "Table", SUM(round(((data_length + index_length) / 1024 / 1024), 2) ) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "database_name"
 AND table_name like "%table%" # for individual table