Most Common MySQL queries

Useful and common MySQL queries for the developer and DBA’s

 1)How to create mysql database

mysql> create database techdb;
mysql> use techdb;

MySQL queries create database

2) How to create table in mysql database? .How to show the structure of the table after creation

Here is the example for table creation in MySQL database
1) We need to first get into database
use techdb;
2) Create table using below syntax
test_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
test_desctiption varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ”,
test_name varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ”,
test_updated datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
PRIMARY KEY (test_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Explanation of the various details in table creation syntax
test -> Table Name
test_id,test_description -> Table Column
bigint(20,varchar -> Datatype for column
Not null-> column cannot be null
utf8mb4_unicode_ci -> Collation
AUTO_INCREMENT -> Values get incremented automatically on insert
Engine -> Specify the engine for the tableOnce table is created successfully, table structure can be shown using
Desc test

MySQL queries create table

3) How to get the table creation statement for the table in mysql?

it can be found out using the command
mysql> show create table <table-name>

MySQL queries table creation statement

4) How to perform below common DML operation on the table?

a) Insert statement

b) Select statement

c) Delete statement

a) Insert statement
insert into test (test_desctiption,test_name,test_updated) values (‘this is mysql test’,’mysql-test-1′,current_date);
Here we did not specify test_id as that is auto increment and it will have value set when rows insertedb)

Select statement
select * from test where test_id=1;
c) Delete statement
delete from test where test_id=1;

MySQL queries insert select delete

5) How to disable autocommit for the session?

SET autocommit=0;

6) How to alter or drop the table in mysql?


Alter table
alter table test add (test_description2 varchar(255) not null default ”);2)
Drop table
drop table test2;

7) How to create table from another table in mysql

create table test4 select * from test3;

8) How to create an index and drop an index on table in MySQL?

a) create index
create index test3_idx on test3(table_name);
b) show index
show index from test3;
c) drop index
drop index test3_idx on test3

9) How to get the execution plan for the mysql query?

explain <query name>

10) How to select data from multiple tables using join in Mysql?

Mysql supports inner join,left join and Right join for fetching data from multiple table at the same time
Inner Join
select test_name,student_name,test_marks from test,student_test where test.test_id=student_test.test_id;

Hope you likes these mysql queries

Related Articles

Step-by-Step guide for Installing MySQL on Windows

Auto Increment Column – Sequence as Default Value in Oracle and mysql

Top 51 Frequently Asked MySQL Interview Questions And Answers

Step by step guide to build local Apache PHP MySQL development environment on windows


Leave a Reply