Mohan pd.
Mohan pd. Author of The Coders Blog. Also a professional fullstack javascript developer working with various tech company and client around world.

Quick Cheat Sheet For MySQL

Feb 01, 2019 · 3 minutes
Quick Cheat Sheet For MySQL

If you are learning MySQL and frequently need to view a guide as a refrerence for the syntax then these quick command will help you.

I have collected cheat sheets for all the code.

Login to access manager in my-sql.
mysql -u [username] -p; (will prompt for password)

Command to show all database in the my-sql
show databases;

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

Command to create new database:
create database [database];

Command to select a certain database:
use [database];

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

Show all tables in the database:
show tables;

Show table structure:
describe [table];

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

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

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

Adding auto incremental primary key:
ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record in the table:
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

Import a database dump:
mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout:
exit;

You can get more information in official guide from mysql here</p>