Examples Of Common Queries – MySQL - MySQL Constraints

MySQL Common Queries

/ins>

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


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


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 :


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


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:


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


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


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

Example for ascending order:


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

Example to select the last records from employee table

Example to select the 1st records from employee table

Example to select 1st and last rerocds from emp table


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

Example to select records between two dates


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.


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

Example using IN Clause


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 an ORDERS tables with a foreign key(P_id)

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 :

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

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