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
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
Select or Open a Database in MySQL
You can perform all database operations after opening the database,Example create table,store information etc.
Show Tables in a MySQL Database
The command which shows all the tables in the database is
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:
Start mysql in safe mode:
mysqld_safe –skip-grant-tables &
Login as root:
mysql -u root
Set the mysql root password:
update user set password=PASSWORD(“new-root-passwd”) where user=’root’;
Restart the mysql service and you can login with your new password:
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.
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”