List of MySQL Commands

 

MySQL is very popular and widely used database around the World. Here we will discuss the various MySQL commands with an example.

As MySQL is Structure Query language. In MySQL, we have database,table, view, functions, triggers, events, procedure.  We will see MySQL commands on all these

 

MySQL commands related to connection

Connecting to MySQL is the first thing to perform any operation in MySQL database. following are the commands related to MySQL connection.

How to connect with MySQL database in command line?

Following command is used to connect with MySQL in command line:

mysql -u <username> -p <password>

Example

mysql -u root -p test123

 

MySQL commands related to Database

Following are the MySQL commands related to Database:

• Create database
• show databases
• use
• drop
• show tables

 

MySQL commands related to Table

In MySQL Database, core thing is tables. Everything is saved in tables. so following are the MySQL commands related to tables:

• Create Statement
• Insert Statement
• Select Statement
• Delete Statement
• Describe Statement

 

 

Create database

create database statement is used to create new database in MySQL.

Syntax

create database dbname;

Example

Create database Employee;

conclusion: A new Employee database will be created inside MySQL.

 

show databases

Show databases statement is used to list down all the databases in the current installed MySQL.

show databases;

 

use

use database statement is used to select the database out of the installed databases in MySQL. If there are multiple databases installed in MySQL Database server, then we can choose the particular database to perform the operations by using use database;

use databasename;

drop database

drop database is used to delete the database from the MySQL.

drop database test;

 

Show Tables

Show Tables command list all the tables inside the selected database.

Show Tables;

 

Create Table

Create Table statement is used to create new table inside MySQL database.

Syntax

create table tablename (col1 datatype, col2 datatype...);

Example

create table EmpDetails( empid int(10), name varchar(25),designation varchar (25), location varchar(15));

 

Insert statement

Insert statement is used to insert the data into table.

Syntax

INSERT INTO TABLENAME(col1, col2....) VALUES(val1,val2,...);

Example

INSERT INTO EMPDETAILS(EMPID, NAME, DESIGNATION, LOCATION) VALUES(100,"Allen","Software Engineer","India");

 

Select Statement

Select Statement is used to fetch the data from the table.

Select * from <em>tablename</em> will fetch all the data from the table.

If there is requirement of fetching few column data to be displayed, then use select col1, col2.. from tablename;.

select empid, name from empdetails;

Its not recommended to use select *. It fetches unnecessarily all the data and also it slow down the processing.

 

Delete Statement

Delete statement is used to delete the data from the table.

Delete all the data from table

Delete from <em>tablename</em>; is used to delete all the data from the table.

If any specific rows need to be deleted, then it will come with where clause.

Delete from <em>tablename</em> where <<em>condition</em>>;

Example

Delete from empdetails where empid=101;

It will delete the record where empid=101

Describe Statement

Describe statement is used check the schema details of table. With describe statement, table columns name, datatype, constraints, keys can be seen.

Describe tablename;

 

Truncate

Truncate statement is used to empty the table. all data will be deleted from table by using truncate command.

Truncate tablename;