Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
My simply MySQL Command Line Cheatsheet

MySQL

Getting started:

Related tutorials:

Tools:

Commands

Access monitor: mysql -u [username] -p; (will prompt for password)

Show all databases: show databases;

Access database: mysql -u [username] -p [database] (will prompt for password)

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records: SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';

Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: truncate table [table];

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: DROP TABLE [table];

Deleting databases: DROP DATABASE [database];

Custom column output names: SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql

Use --lock-tables=false option for locked tables (more info here).

Import a database dump (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout: exit;

Aggregate functions

Select but without duplicates: SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate total number of records: SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]: SELECT MAX([column]) FROM [table];

Get smallest value: SELECT MIN([column]) FROM [table];

Get average value: SELECT AVG([column]) FROM [table];

Get rounded average value and group by [category-column]: SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple tables

Select from multiple tables: SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine rows from different tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)

Rename column or table using an alias: SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

List all users: SELECT User,Host FROM mysql.user;

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'; (source)

alias mysql=/Applications/MAMP/Library/bin/mysql
[mysqld]
max_allowed_packet=64M

Delete all records from a table (without dropping the table itself): delete from [tablename]. This also resets the incrementing counter for auto generated columns like an id column.

Owner

hofmannsven commented Nov 8, 2014

Thanks! I've added it above and also shared it on WPSE :)

anjanb commented Jul 24, 2015

Grunt ALL
Should that not be "GRANT ALL" ?

Owner

hofmannsven commented Aug 11, 2015

@anjanb Thx for the hint! Fixed it.

For scripting:
Comments: /* This is a comment */

Gets me every time between all the languages haha

mhenes commented Jan 12, 2016

For importing or loading data from local csv file into MySQL db (remove LOCAL if not local):
LOAD DATA LOCAL INFILE '/file/path/inputdata.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES;

For exporting MySQL table from MySQL prompt to csv file (will require root permission most likely) :
SELECT column_name FROM table_name INTO OUTFILE '/tmp/outfile.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

For exporting MySQL table from Terminal or Apple Command line to csv file:
mysql --host=localhost --user=user_name -p table_name -e "select column_name from table_name INTO OUTFILE '/tmp/outfile.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"

junrillg commented Feb 2, 2016

Find out the IP Address of the Mysql Host

SHOW VARIABLES WHERE Variable_name = 'hostname'

resource

Owner

hofmannsven commented Feb 5, 2016

👍

maxwsd commented May 12, 2016

when I create that alias you suggested for MAMP and "show databases" all of my databases are not visible, only information_schema database. Any idea? Thank you!

Owner

hofmannsven commented May 23, 2016

@maxwsd Did you source your config file after the change like source .bash_profile?

Great! Thanks so much!

nweak38 commented Jun 11, 2016

Thanks bro! very helpful for me as a beginner.

👍

asinode commented Jun 13, 2016

Thank you. Very helpful.

Soufraz commented Jul 18, 2016 edited

select database();
To determine what database is in use

truncate table [table-name];
To delete all records in a table

tomc12n commented Jul 26, 2016

To list indexes on a table, SHOW INDEX FROM tbl_name.

Owner

hofmannsven commented Jul 28, 2016

Thank you very much for your addition 👍

Might be helpful to add -h note at top on initial connection (for those connecting to remote DB like AWS RDS):

mysql -h endpoint.amazonaws.com -P 3306 -u username -p

Soufraz commented Aug 24, 2016 edited

mysqldump -u [user] -p[password] --all-databases > all_databases.sql
Backup all databases

sirfrank commented Sep 7, 2016

TRUNCATE TABLE tablename; is the one to reset the auto-increment value,
delete
not the DELETE FROM tablename;

sirfrank commented Sep 7, 2016

add unique key to column:
ALTER IGNORE TABLE mytbl ADD UNIQUE (columnName);

sirfrank commented Sep 13, 2016 edited

rename table

RENAME TABLE oldTablename TO nemTablename;

I can't get to mysql monitor

Acrunam commented Nov 1, 2016

This is REALLY useful. Thanks!

denohk commented Nov 8, 2016

All of these stuff are the skin of knowledge of DB. But they are helpful. could you provide the DB background resource also? thx.

create a table from some other table

  1. with data
    create tabe <targettable> as select * from <sourcedb>.<sourcetable>
  2. without data
    create table <targettable> like <sourcedb>.<sourcetable>

pascalandy commented Nov 14, 2016 edited

Hi folks!

Trying to create a a new DB and a new USER with variables (via a bash script). These classic commands are working:

CREATE DATABASE usermama;
CREATE USER 'usermama'@'%' IDENTIFIED BY 'fancypass0ZGViYgn7rRHh6ny';
GRANT ALL PRIVILEGES ON usermama.* TO 'usermama'@'%';
FLUSH PRIVILEGES;
SHOW DATABASES;

All good! Now same concept with variables:

SET @ENV_ID := 'usermama';
SET @ENV_PASS := 'fancypass0ZGViYgn7rRHh6ny';
CREATE DATABASE '@ENV_ID';
CREATE USER '@ENV_ID'@'%' IDENTIFIED BY '@ENV_PASS';
GRANT ALL PRIVILEGES ON '@ENV_ID.*' TO '@ENV_ID'@'%';
FLUSH PRIVILEGES;
SHOW DATABASES;

I getting this error :(

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@ENV_ID' at line 1

Will really appreciate your help!
twitter.com/_pascalandy

pleycpl commented Mar 17, 2017 edited

Thanks, this is nice document. Maybe we can show better (https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet).
Like this.(```sql)

CREATE TABLE [table] (
   [column] VARCHAR(120),
   [another-column] DATETIME
);

I saw this gist now.

MatthewHartstonge commented Mar 29, 2017 edited

One line to Create user and Grant ALL access to user for * tables: GRANT ALL PRIVILEGES ON database_name.* To 'user'@'localhost' IDENTIFIED BY 'password';

KNOWV commented Apr 6, 2017

mysql> select * from CALLOG limit 3;
+-----+--------+-------+-------+------------------+---------------+------------+---------------------+----------+----------+--------+-------------+
oid vendor startdate duration agent status disposition
+-----+--------+-------+-------+------------------+---------------+------------+---------------------+----------+----------+--------+-------------+
1 TCR 2017-01-04 09:22:39 0 NOTANDOG 00-NAN
2 TCR 2017-01-18 14:06:44 1 NOXOLOD1 01-NPC
3 TCR 2017-01-04 09:22:32 5 NOTANDOG 01-NPC
+-----+--------+-------+-------+------------------+---------------+------------+---------------------+----------+----------+--------+-------------+

Hi guy,

Need your help here

I want to do distinct count on "disposition" where start date is >='2017-01-01 00:00:00' , <'2017-02-01 00:00:00';

Thank you in advance

Regards,

saurabh-rao commented May 27, 2017 edited

Another command that we can add is the show table status command. Gives more information about the table ( ex : create_time , rows etc ) .
Format :
SHOW TABLE STATUS;
-- this shows the status for all the tables in the database
SHOW TABLE STATUS LIKE '[TABLE_NAME}';
-- this shows the status for a particular table in the database. `

`Hope this is useful !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment