MySQL commands with examples

Organizational success is based on  effective decision making which requires timely, relevant and accurate information. That is why  information plays a critical role in today’s competitive environment. Database Management Software (DBMS) makes more comprehensible  tasks of managing the data and extract useful information out of it. In this article , we shall learn about the basic concepts of databases and also learn how to use RDBMS for some applications

Basic Concepts and Definitions

Data is a collection of raw details which have not been processed to reveal useful information. Information is produced by processing data as shown in below:

Data – > Processing ->  Information

For example, given the data of the test marks of all the students in a class (data), the average, maximum and minimum marks in the class can be used as indicators of the performance of the class (information). In other words, we can say that we have extracted the information about average, maximum and minimum marks for given student data given below

Marks obtained by students in an examination ->Compute: Average marks, Maximum marks, Minimum marks   ->Performance of the class

Databases are being used mainly  in our day-to-day life. Be it business, engineering, medicine, education, library, to name a few. For example, consider the name, class, roll number, marks in every subject of every student in a school. To record this information about every student in a school, the school might have maintained a register, or stored it on a hard drive using a computer system and software such as a spreadsheet or DBMS package. Such collection of related data that has been recorded, organized and made available for searching is called a Database. A database has the following properties: 

1) A database is a representation of some aspect of the real world also called mini world. Whenever there are changes in this mini world they are also reflected in the database.

 2) It is designed, built and populated with data for specific purposes. 

3) It can be of any size and complexity. 

4) It can be maintained manually or it may be computerized.

In this article we will learn MySQL CUI Commands. Not phpmyadmin GUI interface.

MySQL Set Root Password

If you want to set Mysql root login password,There are number of command but below is simple command ,which help you to set root password.

myadmin -uroot -p 'new password'

How To Connect to MySQL

How to connect to MySQL ? From the command line if you want to login to MySQL server ,below is the command.

#mysql -u root -p

After this command screen ask for mysql server password. Give the password then # or $ prompt replace with 

mysql>

This shows that you’re connected to the MySQL server.

Connect to Mysql Remote Server

mysql> -u root -h HOSTNAME  -u

MySQL Create Database

After login to MySQL server below command help you create MySQL database

mysql> Create database DATABASENAME

Backup a MySQL Database using mysqldump

Backup a MySQL Database using mysqldump

 

#mysqldump -u root -p database-name > /home/user/backupdata.sql

You can also dump the database and compress while running the command by piping it through gzip:

mysqldump -u root -p database-name | gzip -v > database-backup.sql.gz

Dump all MySQL Databases on a server

If you want to take backup of all your databases  ,you can use one single command to take backup of all databases . 

#mysqldump -u root -p PASSWORD  --opt >/var/www/html/databases.sql

Dump a specific table from a mysql database

If some time you want to take a back for a specific table ,this command will help you.

mysqldump -c -u username -p ROOTPASSWORD  database-name table-name > /tmp/db-name.table-name.sql

Import a MySQL Database

Import or restore a database with a single command.

mysql -u username -p -h localhost database-name < database-backup.sql

Display All Database in MySQL Server

Login to mysql server ,then use

mysql>Show Databases;

Select or Open a Database in MySQL

You can perform all database operations after opening the database,Example create table,store information etc. 

use databasename;

Show Tables in a MySQL Database

The command which shows all the tables in the database is

Show tables;

Drop A MySQL Database (deletes a db)

If you want to delete a database from mysql , Delete database will delete everything from the database like table,procedure,trigger etc. all objects will be deleted .Be careful while deleting the database.You can delete database while you close the database 

Drop database DATABASENAME

How To Reset the MySQL root password

How to reset the Mysql root login password ? .Below I have expalin process will allow you to reset the mysql root password:

Stop mysql:

/etc/init.d/mysqld stop

Start mysql in safe mode:

mysqld_safe –skip-grant-tables &

Login as root:

mysql -u root

Set the mysql root password:

1

2

3

4

use mysql;

update user set password=PASSWORD(“new-root-passwd”) where user=’root’;

flush privileges;

quit

Restart the mysql service and you can login with your new password:

/etc/init.d/mysql restart

Create a MySQL table

To store information in database ,create tables in mysql database ,

 

Create table t1
(
Sno int,
Name varchar(20),
);

Create an INNODB Table in MySQL

If you want to create InnoDB table ,The following will create an InnoDB table:

CREATE TABLE t1(
Sno int,
Name varchar(20),
)type=innodb;

Convert Mysql Table Engine MyISAM to INNODB

This command help you to convert MySQL table engine to INNODB.

ALTER TABLE example  ENGINE = InnoDB

Repair Broken Table(s) in MySQL

If you have a corrupt / broken table (pretty common with MyiSAM) then take a dump (see the skip broken table with mysqldump instructions above) and then run:First run check table error with  given command. 

CHECK table tablename

REPAIR table tablename;

Show MySQL Database Fields & Field Formats

To check the fields or Columns in the table,the command you can run. 

DESCRIBE table-name;

MySQL Show Table Data (Displays the contents of a table)

This command shows all rows and columns from the table. 

Select * from table-name;

Show Columns in a MySQL Table


Select column-name from table-name;

Add a new column in MySQL

Add a new column in mysql table .The following is an example of how to add a new column in mysql:

ALTER table table-name add column new-column datatype(20);

Delete a Column in MySQL

Some time we want to delete column from table.The following is an example of how to delete (drop) a column in mysql:

 

ALTER table table-name drop column column-name;

Delete a Row from a MySQL Table

How to delete row from table.

 

Delete from table-name where column-name ='some’;

Show total rows  in a MySQL Table

mysql> SELECT count(*) from table-name;

MySQL SUM Column Example

Select sum(columnname) from table-name;

MySQL Join two and more Tables

How to join tables in MySQL:

SELECT column_names FROM table-1, table-2 WHERE (table-1.column = table-2.column)

Display Table data in Descending Order (DESC)

Show records in descending records.

mysql> Select * from table-name ORDER by column-name 

Default is ascending order but for descending order you have to give DESC at the end.

Select * from table-name ORDER by column-name DESC.

 

 

How to display MySQL table  Unique Records

Shows all unique records from a mysql table:

Select distinct column name from table-name.

How to display rows Containing a Value

This example will show all rows containing “America”:

Select * from table-name where column-name=“America”

MySQL Search for a Record Matching (Various Examples)

Search for records with the name “Johan” born in “1996”:

Select * from table where column-name=”Johan” and dob=”1996”