Fork me on GitHub

Project Notes

MySQL Cheat Sheet

Basic administration and data management command cheats…

:arrow_forward: return to the Catalog

Create User and Database

mysql -u root
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE DATABASE mydb;
GRANT ALL ON mydb.* TO 'username'@'localhost';

Test it:

mysql -u username -p mydb

Import and Export

How To Dump a Database

Doc: mysqldump

mysqldump -u username -p mydb > dump.sql

Database Information

Describe

mysqlshow

See also:

Use mysqlshow to Display Database, Table, and Column Information

mysqlshow -u username -p

Poking around the information_schema

SELECT VERSION(), CURRENT_DATE;
SHOW DATABASES;
use information_schema;
SHOW GRANTS;
SHOW FULL TABLES;
desc COLUMNS;
desc TABLES;

-- listing databases
select schema_name as 'database'
  from information_schema.schemata;

-- listing tables in a schema
select table_name,table_rows,table_type,engine
  from information_schema.tables
 where table_schema = 'information_schema';


-- listing tables sizes in a schema
select table_name,table_rows,table_type,engine,
  ROUND((data_length+index_length)/1024/1024,2) as 'total_size_mb',
  ROUND((data_length+index_length-data_free)/1024/1024,2) as 'data_used_mb',
  ROUND(data_free/1024/1024,2) as 'data_free_mb'
  from information_schema.tables
 where table_schema = 'mydb';

-- listing a tables columns
select column_name, column_key, data_type, numeric_precision, numeric_scale, is_nullable, ordinal_position, column_default
  from information_schema.columns
 where table_schema = 'mydb'
   and table_name = 'mytable';

-- listing constraints on a table
select a.table_name, a.constraint_name, b.column_name, a.constraint_type
  from information_schema.table_constraints a,
       information_schema.key_column_usage b
 where a.table_name = 'mytable'
   and a.table_schema = 'mydb'
   and a.table_name = b.table_name
   and a.table_schema = b.table_schema
   and a.constraint_name = b.constraint_name;

select a.table_name, a.constraint_name, b.column_name, a.constraint_type
  from information_schema.table_constraints a
       left join information_schema.key_column_usage b
              on a.table_name = b.table_name
             and a.table_schema = b.table_schema
             and a.constraint_name = b.constraint_name
 where a.table_name = 'mytable'
   and a.table_schema = 'mydb';

Query Analysis

See: using EXPLAIN

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

e.g. (with output in JSON format);

EXPLAIN FORMAT = json SELECT .... ;

Indexes

See also:

How to see indexes for a database or table?

For a specific table:

SHOW INDEX FROM yourtable;

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';
SHOW {INDEX INDEXES KEYS}
{FROM IN} tbl_name  
[{FROM IN} db_name]  

Create Index

CREATE INDEX index_name ON table (column1, column2);

General guidelines for a multi-column index:

  • columns with highest cardinality first
  • similar cardinality, put the smaller one first
  • selecting with ranges: put last
About LCK#64 DatabaseMySQL
Project Source on GitHub Return to the Project Catalog

This page is a web-friendly rendering of my project notes shared in the LittleCodingKata GitHub repository.

LittleCodingKata is my collection of programming exercises, research and code toys broadly spanning things that relate to programming and software development (languages, frameworks and tools).

These range from the trivial to the complex and serious. Many are inspired by existing work and I'll note credits and references where applicable. The focus is quite scattered, as I variously work on things new and important in the moment, or go back to revisit things from the past.

This is primarily a personal collection for my own edification and learning, but anyone who stumbles by is welcome to borrow, steal or reference the work here. And if you spot errors or issues I'd really appreciate some feedback - create an issue, send me an email or even send a pull-request.