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

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>

comments powered by Disqus