EDUCBA

Home » Data Science » Data Science Tutorials » MySQL Tutorial » Condition in MySQL

Condition in MySQL

Introduction to Condition in MySQL

Condition in MySQL can be defined as an open-source RDBMS (Relational Database Management System) that uses a standard language SQL – Structured Query Language for manipulating, storing and retrieving records in databases. In simple words, we can say that MYSQL is a Database server that is fast, secure and easy to use application for many small and big businesses and different purposes – Web Database, Data Warehousing, E-Commerce, Logging applications and more.

A database encompasses one or multiple tables with rows and columns. Each table is represented by a name and SQL statements are used to perform any action on a database.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Here, we will discuss Conditional statements in MYSQL which are based on some conditional expressions. An expression can be any arrangement of MySQL literals like variables, operators, and functions that on execution returns a logical value if the condition is satisfied.

Conditional Operators in MySQL

Let’s first view some of the important SQL statements with the syntax:

1. SELECT– extracts/select records from a database.  ‘*’ denotes ‘all ’.

SELECT column1, column2,......  FROM table_name;
SELECT * FROM table_name;

2. UPDATE– updates existing fields in a database

UPDATE tablename SET col1 = value1, col2 = value2, ... WHERE condition;

3. DELETE– deletes records from a database

DELETE FROM table_name WHERE condition;

4. DROP DATABASE – Deletes database

DROP DATABASE database_name;

5. INSERT INTO– adds new records into the database

INSERT INTO name_of_table (col1,col2,   ...) VALUES (val1, val2, ...);

6. CREATE DATABASE– generates a new database

CREATE DATABASE databasename;

7. CREATE TABLE– generates a new table

CREATE TABLE tablename ( col1 datatype, col2 datatype, col3 datatype, .... );

8. ALTER TABLE– make changes to a table

ALTER TABLE table_name ADD column_name datatype;

9. DROP TABLE– removes a table

DROP TABLE table_name;

10. CREATE INDEX– produces an index (search key)

CREATE INDEX index_name ON table_name (column1, column2, ...);

11. DROP INDEX– removes an index

DROP INDEX index_name ON table_name;

Note:  SQL keywords are case insensitive. For example, select can also be written as SELECT. The semicolon is used to distinguish each SQL statement in database systems.

Types of SQL Operators

The different types of SQL Operators are given below:

1. SQL Arithmetic Operators

Operators                                    Descriptions
+                                   Add
–                                   Subtract
*                               Multiply
/                                  Divide
%                                   Modulo

2. SQL Bitwise Operators

Operator                   Description
& Bitwise AND
| Bitwise OR
^ Bitwise exclusive OR

3. SQL Comparison Operators

Operator   Description
=                                              Equal to
>                                 Greater than
<                                  Less than
>=                                Greater than or equal to
<=                             Less than or equal to
<>                                 Not equal to

4. SQL Logical Operators

Operator     Description    
ALL     TRUE if all of the subquery values satisfy the condition.
AND     TRUE if all the conditional expressions separated by AND is TRUE.
ANY    TRUE if any of the subquery values satisfy the condition.
BETWEEN TRUE if the operand is inside the series of comparisons.
EXISTS   TRUE if the subquery results in one or more fields.
IN                                  TRUE if the operand matches one of a list of expressions.
LIKE                               TRUE if the operand equals a pattern.
NOT                              Shows a record if the condition(s) is FALSE.
OR                                 TRUE if any of the conditions separated by OR is valid.
SOME                            TRUE if any of the subquery values satisfy the condition.

5. Conditional Operators

You can learn and practice the SQL queries from educba.com a very easy and simple understanding way.

1. IF() Function

If the condition is the TRUE result is “YES” and if the condition is FALSE then “NO”.

Syntax:

IF(condition, value_if_true, value_if_false)

SQL Statement: 

SELECT IF(400<2000, "YES", "NO");

Output:

condition in mysql

2. COALESCE () Function

Results in the first non-null value in a list.

Syntax:

COALESCE (val1, val2, ...., val_n)

SQL Statement:   

SELECT COALESCE (NULL, 1, 5, 'example.com');

Output:

condition in mysql

3. CASE Function

Checks all the conditions and if the first condition is met, returns a value and will not read further. If no conditions are fulfilled, then the value will be returned in the ELSE clause. But again, it will return NULL if no ELSE portion and no conditional expressions are true.

Syntax:

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

Suppose a table Customer:

condition in mysql

SQL Statement:   

SELECT CustomerID, CustomerName, Address FROM Customers
ORDER BY (CASE
WHEN Address IS NULL THEN  City
ELSE Address
END);

Output:

<img class="

1 Shares
Share
Tweet
Share