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.
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.
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 Create Database
After login to MySQL server below command help you create MySQL database
Backup a MySQL Database using mysqldump
Backup a MySQL Database using mysqldump
You can also dump the database and compress while running 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 .
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.
Import a MySQL Database
Import or restore a database with a single command.
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
The above command displays all the tables in the database.
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
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 an INNODB Table in MySQL
If you want to create INNODB table ,The following will create an innodb table:
Convert Mysql Table Engine MyISAM to INNODB
This command help you to convert MySQL table engine to 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.
And then run
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.
Show Columns in a MySQL Table
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:
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:
Delete a Row from a MySQL Table
How to delete row from table.
Show total rows in a MySQL Table
MySQL SUM Column Example
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 .
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”