MySQL Cheat Sheet

Some notes on administering MySQL database

Here are some notes and commands on MySQL administration

• Create a database:

  CREATE DATABASE mydatabase;
  

• Creating a user for the database:

  CREATE USER 'testuser'@localhost IDENTIFIED BY 'password';
  

• Granting testuser all privileges on a table:

  GRANT ALL PRIVILEGES ON mydatabase.* TO 'testuser'@localhost;
  

• Show a list of databases:

  SHOW DATABASES;
  

• Pick a database to use:

  USE mydatabase;
  

• Show a list of tables after selecting a database:

  SHOW TABLES;
  

• Updating a column with a constant value:

  UPDATE table1 SET column_a='value_a', column_b='value_b' WHERE query_id='1';
  

• Deleting rows that satisfy a criterion:

  DELETE FROM table1 WHERE query_id='1';
  

• Resetting the primary key value after any deletes that were performed:

  ALTER TABLE table1 AUTO_INCREMENT=1;
  

• Disabling safe update mode, and turning back on:

  SET SQL_SAFE_UPDATES = 0;
  DELETE FROM table1 WHERE query_id='1';
  SET SQL_SAFE_UPDATES = 1;
  

  Use the above with caution.

• Counting duplicates in a column:

  SELECT query_id, COUNT(*) c FROM table1 GROUP BY query_id HAVING c > 1;
  

  This would count all duplicates in query_id and list the count for each. You can drop the HAVING c > 1 part if you just want to get a count on a certain column.

• Getting size occupied by a database named mydatabase listed for each table:

  SELECT table_name AS "Tables",
  round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
  FROM information_schema.TABLES
  WHERE table_schema = "mydatabase"
  ORDER BY (data_length + index_length) DESC;
  

   programming   linux   database   mysql  

---

Related Post

• Setting Up Linode For The Impatient
• Sending MSMQ Messages Using Python
• Building Task Pipelines Using Luigi
• Multi-Processing With Pandas
• Asynchronous Python Logging Using MSMQ

---