Table of Contents
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 10 Basic Commands of MySQL.
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.
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’, ‘firstname.lastname@example.org’, ‘7418529630’, 1);
More than 1 Rows to Insert
INSERT INTO tbl_books (name, email, phone, status) VALUES (‘Learn Blogging’, ‘email@example.com’, ‘7418529630’, 1), (‘SEO Tutorials’, ‘firstname.lastname@example.org’, ‘8956231470’, 1), (‘C++ Programming’, ‘email@example.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 = ‘firstname.lastname@example.org’, 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.
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.
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.