MySQL Query Example

In This section of tutorial, We will learn about mysql query like reate table, insert record, update record, delete record, select record, truncate table and drop table with example.

Get all student details from the student table

Select * from student

Get student_Name,class_Name from student table

Select student_Name,class_Name from student

Get student_Name,class_Name from student table

Select student_Name,class_Name from student

Get student_Name from student table in upper case

Select upper(student_Name) from student

Get student_Name from student table in lower case

Select lower (student_Name) from student

Get unique class_Name from student table

select distinct class_Name from student

Select first 2 characters of student_Name from student

select substr(student_Name,1,2) from student

Fetch position of 'a' in student_Name 'Max' from student table

Select LOCATE('a',student_Name) from student where student_Name='Max'

Get student_Name from student table after removing white spaces

select TRIM(student_Name) from student

Get student_Name from student table after removing white spaces

select TRIM(student_Name) from student

Get length of student_Name from student table

SELECT LENGTH(`student_Name`) as col_len FROM student ORDER BY col_len DESC

Get student_Name from student table after replacing 'max' with 'max joe'

select REPLACE(student_Name,'max','max joe') from student

Get student_Name and class_Name as single column from student table separated by a ' '

Select concat(student_Name,' ',class_Name) from student

Get all student details from the student table order by student_Name Ascending

Select * from student order by student_Name asc

Get all student details from the student table order by student_Name descending

Select * from student order by student_Name desc

Get all student details from the student table order by student_Name Ascending and age descending

Select * from student order by student_Name asc,age desc

Get student details from student table whose student name are "Manish" and "Max"

Select * from student where student_Name in ('Manish','Max')

Get student details from student table whose student name are not "Manish" and "Max"

Select * from student where student_Name not in ('Manish','Max')

Get student details from student table whose student_Name starts with 'M'

Select * from student where student_Name like 'M%'

Get student details from student table whose student_Name contains 'M'

Select * from student where student_Name like '%M%'

Get student details from student table whose student_Name ends with 'M'

Select * from student where student_Name like '%M'

Get student details from student table whose student_Name ends with 'M'

Select * from student where student_Name like '%M'

Get student details from student table whose student_Name ends with 'm' and student_Name contains 3 letters

Select * from student where student_Name like '__m'

Get student details from student table whose student_Name starts with 'm' and student_Name contains 3 letters

Select * from student where student_Name like 'm__'

Get student details from student table whose age greater than 18

Select * from student where age > 18

Get student details from student table whose age less than 18

Select * from student where age < 18

Get student details from student table whose age between 18 and 20

Select * from student where age between 18 and 20

Get student details from student table whose joining year is "2015"

Select * from student where year(joining_Date)='2015'

Get database date

select now()

Get student details from student table whose joining month is "January"

Select * from student where month(joining_Date)='01'

Get student details from student table who joined before January 1st 2015

Select * from student where joining_Date <'2013-01-01'

Get student details from student table who joined before January 1st 2015

Select * from student where joining_Date <'2013-01-01'

Get student_Name from student table who has '%' in student_Name.

Select student_Name from student where student_Name like '%\%%'

Get student_Name from student table after replacing % with white space.

Select REPLACE(student_Name,'%',' ') from student

Get class_Name,no of student in a Class with respect to a class_Name from student table.

Select class_Name,count(student_Name) from student group by class_Name

Select TOP 2 older student from student table

select * from student order by age desc limit 2

Get All INDEX from student table

SHOW INDEX from student;

Write syntax to delete table student

DROP table student

Write syntax to empty table student

Truncate student

Write syntax to set student_Id as primary key in student table

ALTER TABLE student add CONSTRAINT student_PK PRIMARY KEY(student_Id)