Learn About 10 Basic Commands of MySQL for Beginners

Reading Time: 13 minutes

MySQL is a database management tool which manages databases and it’s relations. MySQL commands are very easy to learn in comparison of other available database management software.

It is used to store, organize data and in data retrieval processes. It is handled by both – via MySQL Shell + MySQL commands.

Inside this article we will start very basic sessions of MySQL with PhpMyAdmin Interface. Supposed to be PhpMyAdmin already Installed in working system. Inside this we will cover from each basics for beginners i.e Learn About 10 Basic Commands of MySQL for Beginners.

Learn More –

• Create and Call MySQL Stored Procedure
• How to Add Comments on Columns of MySQL Table
• Import and Export Data To MySQL Using Command Line
• Which DataType Stores IP Address In MySQL ?

Let’s get started.

---

MySQL Database Setup

To work with MySQL, we need to create a database and tables. We have 2 options to create database. MySQL Database can be created –

• By PhpMyAdmin Interface Manual Tool
• MySQL Command

We will see both options to create database. Open up phpmyadmin by following URL hostname/phpmyadmin. In our case it is localhost/phpmyadmin

By PhpMyAdmin Interface Manual Tool

Manual Tool means an interface where we need to provide details, no need to execute any command. So PhpMyAdmin manual tool provides an interface where we need to give Database Name and click on Create Button.

When we provide database name and click on button, it will create and list the database into the list. Also we can see databases are already listing into the same image.

MySQL Command

We have very simple command to run and execute to create database. We need to open command panel i.e SQL Tab type command into it and execute.

Command –

CREATE DATABASE web_blogs; 

Press Go Button OR Press Ctrl + Enter

Successfully, we have now a database. We can create our own Tables and perform database operations.

---

Create MySQL Database Tables

Tables are the entities of any database. These entities have columns called as table columns. Columns can be 2 or more than 2 inside any table. These tables are the containers inside MySQL database management system where we store data.

Data stored in different different types. Types can be of any type like – Integer value, String Value, Char, Float, DateTime, Timestamp, Enum etc. These types are called as Data Types.

Tables also can be created by 2 ways in MySQL Database –

• By PhpMyAdmin Interface
• By MySQL Create Table Command

By PhpMyAdmin Interface

PhpMyAdmin provides a tool or say an interface which is used to create a table.

Inside Create Table Form, pass table name and number of columns we need to create table. After passing these values press on Right handed side Go Button.

In the next screen of Create table inside this manual process we will see the form structure to create it.

When we click on Right handed side Save button, it will create a table with the name of tbl_books.

MySQL Create Table Command

We need to run a set of command to create table inside MySQL Database.

Create Table Command –

CREATE TABLE `tbl_books` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(120) NOT NULL,
 `email` varchar(120) NOT NULL,
 `phone` varchar(30) NOT NULL,
 `status` int(11) NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Paste into SQL tab of Database and Press Go button to create it.

---

Insert Data into Table

Inside this command we will store data. Data will be stored inside in any MySQL table using

• Manual Insert Interface
• By MySQL Insert Command

Manual Insert Interface

Insert interface panel will take values. When we press Go button after passing values it will insert data into table. We can insert one row or more than one row.

To Insert data we have Insert Tab if you closely look into the image. Data Insert form panel you will get when you click on Insert Tab.

MySQL Insert Command

This is very basic command of MySQL to store data. Insert command as –

Single Row Insert Command

INSERT INTO tbl_books (name, email, phone, status) VALUES ('Learn Blogging', 'blog@gmail.com', '7418529630', 1);

More than 1 Rows to Insert

INSERT INTO tbl_books (name, email, phone, status) VALUES ('Learn Blogging', 'blog@gmail.com', '7418529630', 1), ('SEO Tutorials', 'seo@gmail.com', '8956231470', 1), ('C++ Programming', 'programming@gmail.com', '9632587410', 1);

---

Update Table Data

Update query is used to update existing data of table. While row update we need to pass WHERE condition, condition specifies on what basis we are going to update data. If we don’t specify any condition then it will update whole table.

Have a look, update query to update data –

UPDATE tbl_books SET name = 'Blog Update', email = 'blog_update@gmail.com', phone = '8514729630' WHERE id = 1;

In update command we use SET keyword to set updated values and as you can see we have used WHERE condition as id to set the condition to update.

---

Delete Data From Table

Delete command is basically clear from it’s name. It is for delete operation. It also takes condition when we delete any data else it will delete all table rows at once.

While using this Delete query we need to take care about syntax and it’s condition. Deleted data can’t be revert back to table.

Here is the MySQL Delete Query –

DELETE FROM tbl_books WHERE id = 2;

By the help of above query we will delete row from table whose id = 2 value.

Both Update & Delete command we do also via Manual List of MySQL database. See into this image, we have edit button for update and delete button into the data list.

---

Selecting/Finding Data

We use SELECT command to select data. Also we can pass conditional statements to find selected data.

For selecting data we can use manual interface as well – Search Tab

MySQL Select Commands

To Select All rows from table –

SELECT * FROM table_name;

Find a selected row on the basis of ID –

SELECT * FROM table_name WHERE id = 5 ;

Find selected columns from table on ID basis –

SELECT name, email FROM table_name WHERE id = 5 ;

Find all books where name starts with – “Ba“

SELECT * FROM table_name WHERE name LIKE 'Ba%' ;

---

Truncate Database Table

If we want to remove all data from database table, we have 2 options. Following are the options –

• By using MySQL Delete Command
• Using Truncate Command

MySQL Delete Command

To remove all data from a database table, delete command will remove that. In that delete command we don’t need to pass anything as a conditional statement.

Command –

DELETE FROM table_name ;

It will delete all rows but one thing here we can notice that. What is it?

When we delete all data row, then after inserting any new row it’s ID will be auto incremented from the last existing data row what we had deleted. But if we want new row should start from ID = 1, truncate command comes into play.

Truncate Command

Command –

TRUNCATE table_name ;

---

MySQL Drop Command

Drop commands used to drop or delete. Means if we want to delete Table, Database then we will use Drop command.

Drop command syntax is pretty simple in comparison of other commands.

To Drop Table

Command –

DROP TABLE table_name ;

To Drop Database

Command –

DROP DATABASE database_name ;

Drop & Truncate operations, also we can do from PhpMyAdmin Tool, have a look into this image –

---

Show Table Structure

To display the table structure what we have created and what are the columns and it’s data types, again we have 2 options as same as above.

By Manual Interface –

By MySQL Command –

Command –

SHOW CREATE TABLE table_name ;

Run this command at SQL tab. We will see complete structure details of the specified table.

---

Import/Export Database & Tables

Import and Export are two important features of MySQL Database. It’s PhpMyAdmin Tool for Database administrator.

The names of import and export is clear – one is for import data other is for export data. Basically these are used there, where we want to take database backup or import data into existing database.

Buttons of import and export available at Tool links as you can see here.

After these commands now you have some bit idea about playing with data at MySQL Database.

We hope this article helped you to learn about 10 Basic Commands of MySQL in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.