Home >>MySQL Tutorial >MySQL Common Queries

MySQL Common Queries

Basic SQL Queries with syntax

SQL Statement	What It Does
CREATE DATABASE database-name	Creates a new database
DROP DATABASE database-name	Deletes a database
CREATE TABLE table-name(field1, field2, . . .)	Creates a new table
DROP TABLE table-name	Deletes a table
RENAME Table Old-table-name TO New-table-name	Renames a table
ALTER TABLE table-name ADD(field1, field2, . . .)	Add fields in existing table
ALTER TABLE table-name DROP(field1)	Deletes fields from table
INSERT INTO table-name (field1, field2, . . . ) VALUES(value1, value2, . . .)	Inserts a new record into a table with specified values
UPDATE table-name SET field1=value1, field2=value2,... [WHERE condition]	Updates records in a table with new values
DELETE FROM table-name [WHERE condition]	Deletes records from a table
SELECT field1, field2, . . . FROM table-name [WHERE condition]	Retrieves matching records from a table
SELECT * FROM table-name	Retrieves all records from a table

MySQL Common Queries examples

What does auto_increment value mean in MySQL ?

Auto_increment: The word auto increment means ‘increment by itself’. When we insert any new record to the table and if any field of that table is set to auto_increment then it generates a unique number for that field automatically.

1) How to rearrange auto_increment value

When you deletes a auto_incremented value then it doesn't automatically rearrange the value. if you want to rearrange the auto_incremented value then use the queries

Alter table reg drop id;
// here table name is "reg" and auto_increment filed is "id"

Alter table reg auto_increment=1;
//assign auto_increment=1(if you want to start from 100  then auto_increment=100)

Alter table reg ADD id bigint unsigned not null auto_increment primary key first;
//Now  add a new filed "id", (define auto_increment)

2) What is the use of count in MySQL

if you want to count total number of rows from a table then use count() Eg

// here "employee" is  table name
 select count(*)  from employee

3) What is the use of max in MySQL ?

max is used to select the maximum value of the particular field. Example to find out the max salary from employee table :

// here "employee" is  table name  
select max(salary) from employee;

4) Find out the second maximum salary from employee table using nested query

select max(salary) from employee where salary < ( select max(salary) from employee);

5) What is the use of min in MySQL ?

Min : min is used to select the minimum value of the particular field. Example to find out the minimum salary from employee table:

// here "employee" is  table name 
select min(salary) from employee;

6) Example to find out the second minimum salary from employee table ?

 select min(salary) from employee where salary > ( select min(salary) from employee);
 // here "employee" is  table name

7) Example to find out all the records of the employees whose salary is maximum in employee table:

// here "employee" is  table name
select * from employee where salary = (select max(salary) from employee)

8) What is the use of order by in MySQL?

Order by : This is used to set the record in a order(descending order or ascending order). Example for descending order

// here "employee" is  table name
select * from employee ORDER BY salary desc;

Example for ascending order:

select * from employee ORDER BY salary asc;

//OR

select * from employee;
// here "employee" is  table name

9) What is the use of limit in mySql ?

Limit : It is used to specify the number of records to return. It applies the constraint on the number of rows in result set. Means if we have used ‘LIMIT 5,10 ‘ then it means it will select 10 record from starting from 6th. Example to select 10 records starting from 6 in the given offset

select * from employee limit 5,10;

// OR

Select * from employee limit 10 OFFSET  5;	
// both queries will give the same result.

Example to select the last records from employee table

select * from employee ORDER BY id Desc limit 1 ;
// here "employee" is  table name and it returns the last entry

Example to select the 1st records from employee table

select * from employee ORDER BY id ASC limit 1 ;
// here "employee" is  table name and it returns the first entry

Example to select 1st and last rerocds from emp table

(select id,name,salary from employee ORDER BY id Asc limit 1)
				UNION
(select  id, name, salary from employee ORDER BY id Desc limit 1);

// here "employee" is  table name and it returns the id,name, and salary of first user and last user

10) What is the use of between ?

Between is used to select the records from given lower and upper limit. Example to select student whose age between 25 to 40

select * from student where age between 25 and 40; 
// here "student" is  table name

Example to select records between two dates

select * from employee where date between “2012-04-01”  and “2012-04-10”;
// here "employee" is  table name.
//Year,month and date separated by '-' so pass date as a string “2012-04-01”.

11) What is the use of DISTINCT keyword ?

Distinct : It only returns distinct values . it rectifies the data from repeated(duplicate) values. Example to select unique student name from student table.

SELECT DISTINCT  name    FROM student; 
// here "student" is  table name while  "name" is field name

12)What is the use of IN CLAUSE operator ?

We can use “IN” clause to replace many “OR” conditions. It allows you to specify multiple values in a where clause. Example using multiple OR clause

select * from employee where daily_page=100 OR daily_page=150 OR daily_page=200;
// here "employee" is  table name.it return the details of  employee  whose daily_page=100 or 150 or 200

Example using IN Clause

Select * from employee where daily_page IN(100,150,200);
// here "employee" is  table name.it return the details of  employee  whose daily_page=100 or 150 or 200

13)What is the use of foreign key in mysql ? How to define foreign key

The key which is pointing to the primary key of another table is called as foreign key. Foreign keys concept doesn’t work in MYISAM storage engine change your storage engine as innodb. First create a person table

CREATE TABLE Person

(

P_id int auto_increment primary key, 

name char(30) not null,

email varchar(50) unique key

)

Create an ORDERS tables with a foreign key(P_id)

CREATE TABLE Orders

(

O_Id int  auto_increment PRIMARY KEY ,

OrderNo int NOT NULL,

P_id int,

FOREIGN KEY (P_id) REFERENCES Person(P_id)

);
// Here P_id field is foreign key(the primary key of Person table)

13)what is the use of LIKE ?

The like operator is used to search for specified pattern. Example 1: To select the std_info whose gender starts from m :

 SELECT * FROM stu_info  WHERE gender LIKE 'm%';

Example 2: Example to select the stuinfo whose username starts from n.

 SELECT * FROM stuinfo  WHERE userName LIKE 'n%’

Example 3: Example to select the stdinfo whose username contains n .

 SELECT * FROM stuinfo  WHERE userName LIKE '%n%';

« Previous Next »

MySQL Tutorial SQL Introduction MySQL Constraints MySQL Common Queries PHP MySQL Connectivity PHP Mysqli Database Connectivity PHP MySQL Create Database PHP Create MySQLi Database PHP MySQL Insert PHP MySQL Update PHP MySQL Delete PHP MySQL Select Mysqli Fetch Array Mysqli Fetch Assoc Mysqli fetch row Mysqli fetch object MySQL Where Clause MySQL Order By Select Insert Update Delete in Php How to create foreign key MySQL auto increment PHP MySQL Secure Registration Page PHP MySQL Secure Login Page MySQL Predefined Function MySQL Joins How to Export MySQL to CSV How to import csv into mysql How to edit radio button in PHP MySQL PHP MySQL Pagination Upload and Display Video in PHP Building a bar chart How to update Radio button, Checkbox and dropdown Create Registration form PHP MySQL How to insert data from textarea to database PHP PHP PDO

No Sidebar ads