Top 10 Most Common MySQL queries examples with Answers

Useful and common MySQL queries examples with answers for the developer and DBA’s

Table of Contents

How to create a MySQL database

mysql> create database techdb;
mysql> use techdb;

How to create table in mysql database?

Here is the example for table creation in the MySQL database
(1) We need to first get into the database

use techdb;

(2) Create a table using the below syntax

CREATE TABLE test (
test_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
test_desp 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 table

How to show the structure of the table after creation

Once the table is created successfully, the table structure can be shown using

Desc test

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>

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

(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 a value when rows insert into the table

(b) Select statement

select * from test where test_id=1;

delete from test where test_id=1;

MySQL queries examples (insert select delete statement)

How to disable autocommit for the session?

SET autocommit=0;

How to alter or drop the table in MySQL?

Alter table

alter table test add (test_description varchar(255) not null default '');

Drop table

drop table test2;

How to create a table from another table in MySQL?

create table test4 select * from test3;

mysql create table from another table without data

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

(a) create index

create index test3_idx on test3(table_name);

(b) show index

show index from test3;

drop index test3_idx on test3

How to get the execution plan for the mysql query?

explain <query>

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;
or
select test_name,student_name,test_marks from test join student_test on test.test_id=student_test.test_id

Left Join

select test_name,student_name,test_marks from test left join student_test on test.test_id=student_test.test_id;

Right Join

select test_name,student_name,test_marks from test right join student_test on test.test_id=student_test.test_id;

Auto Increment Column in MySQL database

We can have a auto increment attribute for the column in Mysql

CREATE TABLE test (
test_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
test_desp 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)
)

Hope you like these MySQL queries examples with answers

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
How to reset MySQL root password