MySQL Basic Queries

Basic MySQL Queries with syntax

MySQL 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 the existing table
ALTER TABLE table-name DROP(field1)	Deletes fields from the 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 Basic Queries examples

1 ) 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.

How to rearrange auto_increment value

When you delete an 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 users drop id;
// here table name is "users" and auto_increment filed is "id"

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

Alter table users 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 the total number of rows from a table then use count() Eg

// here "users" is  table name

select count(*)  from users

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 "users" is  table name  
select max(salary) from users;

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

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

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 "users" is  table name 
select min(salary) from users;

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

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

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

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

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

// here "employee" is the table name and it returns the last entry

select * from employee ORDER BY id Desc limit 1 ;

Example to select the 1st records from employee table

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

Example to select 1st and last records 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 limits.

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 “2019-04-01”  and “2019-04-10”;
// here "employee" is  table name.
//Year,month and date separated by '-' so pass date as a string “2019-04-01”.

11 ) What is the use of the DISTINCT keyword?

Distinct: It only returns distinct values. it rectifies the data from repeated(duplicate) values.

Example to select a unique student name from the student table.

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

12 ) What is the use of an 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=150 OR daily_page=250 OR daily_page=300;
// here "employee" is  table name.it return the details of  employee  whose daily_page=150 or 250 or 300

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(
	id int auto_increment primary key, 
	name char(30) not null,
	email varchar(50) unique key
)

Create ORDERS tables with a foreign key(P_id)

CREATE TABLE Orders(
	id int  auto_increment PRIMARY KEY ,
	OrderNo int NOT NULL,
	P_id int,
	FOREIGN KEY (id) REFERENCES Person(id)
);
// Here P_id field is foreign key(the primary key of Person table)

14 ) 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%';

Series: MySql Tutorials Hub

All Topics