MySQL Commands A Developer should know

To login (from Linux shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

To create a database

mysql> create database [databasename];

List all databases

mysql> show databases;

To select a database

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see table columns and their details.

mysql> describe [table name];
mysql> desc [table name];

To delete a database.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all the rows in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Select rows where column a has value of ‘x’.

mysql> SELECT * FROM [table name] WHERE a = 'x';

Show all records where name is “abc” AND the phone number ‘1234’.

mysql> SELECT * FROM [table name] WHERE name = "abc" AND phone_number = '1234';

 

How to use ‘like’ wildcards

mysql> SELECT * FROM [table name] WHERE name like 'Mathew%';
mysql> SELECT * FROM [table name] WHERE name like '%smith';
mysql> SELECT * FROM [table name] WHERE name like '%sm%';
mysql> SELECT * FROM [table name] WHERE name like '_athew';
mysql> SELECT * FROM [table name] WHERE name like '_athew%';

Show top 5 records according to id

mysql> SELECT * FROM [table name] WHERE order by id asc limit 5;

Show 6th records from top according to id

mysql> SELECT * FROM [table name] WHERE order by id asc limit 5,1;

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];