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>
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 statement is used to create new database in MySQL.
create database dbname;
Create database Employee;
conclusion: A new Employee database will be created inside MySQL.
Show databases statement is used to list down all the databases in the current installed MySQL.
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;
drop database is used to delete the database from the MySQL.
drop database test;
Show Tables command list all the tables inside the selected database.
Create Table statement is used to create new table inside MySQL database.
create table tablename (col1 datatype, col2 datatype...);
create table EmpDetails( empid int(10), name varchar(25),designation varchar (25), location varchar(15));
Insert statement is used to insert the data into table.
INSERT INTO TABLENAME(col1, col2....) VALUES(val1,val2,...);
INSERT INTO EMPDETAILS(EMPID, NAME, DESIGNATION, LOCATION) VALUES(100,"Allen","Software Engineer","India");
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 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>>;
Delete from empdetails where empid=101;
It will delete the record where empid=101
Describe statement is used check the schema details of table. With describe statement, table columns name, datatype, constraints, keys can be seen.
Truncate statement is used to empty the table. all data will be deleted from table by using truncate command.