Top 10 Most Common MySQL queries examples

Last updated on August 22nd, 2018 at 09:47 am

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

 1)How to create mysql database

mysql> create database techdb;
mysql> use techdb;

create database in mysql

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
CREATE TABLE test (
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 table
Once table is created successfully, table structure can be shown using
Desc test

MySQL queries examples (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 insert into

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

MySQL queries examples (insert select delete statement)

5) How to disable autocommit for the session?

SET autocommit=0;

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

1)

Alter table
alter table test add (test_description 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;

mysql create table from another table without data

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

mysql create index examples

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

explain <query name>
Mysql explain query examples

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

Mysql joins queries examples

Hope you likes these mysql queries examples

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