MySQL back to basics: Commands for newbie users

Here is the list of most basic and useful commands for MySQL newbie user. These commands are used time and again.

To create a database

mysql> create database [database_name];

To delete a database

mysql drop database [database_name];

To create a table

mysql> create table [table_name];

To delete a table

mysql> drop table [table_name];

To delete a row from table

mysql> DELETE FROM [table_name] where [field_name] = “any value”;

To show all the data

mysql> SELECT * FROM [table_name];

To show particular row

mysql> SELECT * FROM table [table_name] where [field_name] = “any value”;

To show unique records

mysql> SELECT DISTINCT [column_name] from [table_name];

To count rows

mysql SELECT COUNT(*) FROM [table_name];

To find sum of rows

mysql> SELECT SUM(*) FROM [table_name];

To add a column

mysql> ALTER table [table_name] add column [new column_name] varchar(10);

To delete a column

mysql> ALTER table [table_name] drop column [column_name];

To modify data in a table

mysql> UPDATE [table_name] SET column1 = value1, column 2 = value2 where some column = value;

To sort data in ascending or descending order

mysql> SELECT [column_name] from [table_name] ORDER BY [column_name] ASC| DESC;