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:
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;