MYSQL Tutorial Part 4

-

MYSQL CONDITIONS 

    In this part, will learn about MySQL conditions. Using some operators will filter records from the database table.


AND, OR, NOT OPERATORS:

    The WHERE clause can be combined with AND, OR, and NOT operators.
    The AND and OR operators are used to filter records based on more than one condition:
• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.

    AND Syntax
        SELECT * FROM table_name WHERE condition1 AND condition2;

    Example:
        SELECT * FROM Students WHERE department='ECE' AND district='Thanjavur';

    OR Syntax
        SELECT * FROM table_name WHERE condition1 OR condition2;

    Example:
        SELECT * FROM Students WHERE department='ECE' OR district='Thanjavur';

    NOT Syntax
        SELECT * FROM table_name WHERE NOT condition;

    Example:
        SELECT * FROM Students WHERE NOT department='ECE';

LIKE:

  • The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
  • It performs pattern matching.
    Two wildcards
  1.  The percent sign (%) represents zero, one, or multiple characters
  2.  The underscore sign (_) represents a single character

    Syntax

        SELECT * FROM table_name WHERE column_name LIKE pattern;

    Example
        SELECT * FROM Students WHERE district LIKE 'Ch%';  - It finds the district starts with the letter Ch

        SELECT * FROM Students WHERE district LIKE '%ai';  - It finds the district ends with the letter ai

        SELECT * FROM Students WHERE district LIKE '%an%';  - It finds the district with the letter an in any position

        SELECT * FROM Students WHERE district LIKE 'C%i';  - It finds the district starts with the letter C and ends with the letter i

        SELECT * FROM Students WHERE district LIKE 'a_';  - It finds the district starts with the letter a and it should be at least 2 characters in length

        SELECT * FROM Students WHERE district LIKE '_h';  - It finds the district with the letter h in the second position

IN:

  • The IN operator allows you to specify multiple values in a WHERE clause.
  • It is like the OR condition.
  • It is used to reduce the use of multiple OR.

    Syntax

        SELECT column_names FROM table_name WHERE column_name IN (value1, value2, ...);

        or:

        SELECT column_names FROM table_name WHERE column_name IN (SELECT STATEMENT);

    Example
        SELECT * FROM Students WHERE district IN ('Trichy', 'Thanjavur', 'Chennai');

        SELECT * FROM Students WHERE district IN (SELECT dist_name FROM District);

BETWEEN:

  • It finds the value within a given range. 
  • The values can be numbers, text, or dates.
  • Begin and end values are included.

    Syntax

        SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

    Example
        SELECT * FROM Students WHERE fees BETWEEN 30000 AND 60000;

ALIAS:

  • It is used to create a temporary name for a particular table or column.
  • It makes the column name or table name more readable.
  • An alias only exists for the duration of that query.
  • It is created with the AS keyword.

    Syntax

        SELECT column_name AS alias_name FROM table_name;

    Example
        SELECT name AS student_name FROM Students;

NULL VALUE:
  • A field with a NULL value is a field with no value.
  • If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
  • It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
  • We will have to use the IS NULL and IS NOT NULL operators instead.
      IS NULL Syntax
            SELECT column_names FROM table_name WHERE column_name IS NULL;
      
       Example:
            SELECT * FROM Students WHERE district IS NULL;

      IS NOT NULL Syntax
            SELECT column_names FROM table_name WHERE column_name IS NOT NULL;

      Example:
           SELECT * FROM Students WHERE district IS NOT NULL;

EXISTS:

  • The EXISTS operator is used to test for the existence of any record in a subquery.
  • The EXISTS operator returns TRUE if the subquery returns one or more records.

    Syntax

        SELECT column_names FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

    Example
        SELECT name FROM Departments WHERE EXISTS (SELECT department FROM Students WHERE Students.department = Department.name AND fees < 50000);

ANY:

    The ANY operator:

  • returns a boolean value as a result
  • returns TRUE if ANY of the subquery values meet the condition

    ANY means that the condition will be true if the operation is true for any of the values in the range.

    Syntax

        SELECT column_names FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition);

    Example
        SELECT name, department FROM Students WHERE department = ANY (SELECT name FROM Department WHERE location = 'Block1');

ALL:

    The ALL operator:

  • returns a boolean value as a result
  • returns TRUE if ALL of the subquery values meet the condition
  • is used with SELECT, WHERE, and HAVING statements

    ALL means that the condition will be true only if the operation is true for all values in the range.

    Syntax With SELECT

        SELECT ALL column_names FROM table_name WHERE condition;

    Syntax With WHERE or HAVING

        SELECT column_names FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);

    Example
        SELECT name, department FROM Students WHERE department = ALL (SELECT name FROM Department WHERE location = 'Block1');



                                                Thanks for choosing our blog 😊. 
                                    Will discuss constraints in the next part. -  Part-5

Tutorial Topics:
Introduction  -  Part-1
Database Creation  -  Part-2
Aggregate Functions  - Part-3
Conditions  - Part-4
Constraints  - Part-5
Clauses  -  Part-6
Joins  -  Part-7


Tutorial Topics for examples:
Database Creation -  Part-1
Table Modifications  -  Part-2
Clauses   - Part-3
Operators  - Part-4
Joins  - Part-5

Comments

Post a Comment

Popular posts from this blog

MySQL Tutorial Part 1

-
Image
MYSQL: MySQL is a very popular open-source relational database management system. It is very easy to use. MySQL is one of the most straightforward database technology to learn and use. To add, access, and process data stored in a database. SQL: SQL stands for Structured Query Language SQL is used to communicate with the database. SQL lets you access and manipulate databases SQL keywords are NOT case sensitive: select is the same as SELECT SQL VS MYSQL: SQL MYSQL SQL is a query programming language that manages RDBMS. MySQL is a relational database management system that uses SQL. SQL is primarily used to query and operate database systems. MySQL allows you to handle, store, modify and delete data and store data in an organized way. RDBMS: RDBMS stands for Relational Database Management System. RDBMS is a program used to maintain a relational ...
Read more

Pandas DataFrame empty

-
Image
HOW TO CHECK IF THE DATAFRAME IS EMPTY OR NOT? Check if the DataFrame is empty or not by using the  DataFrame.empty .  It returns the Boolean value. If the DataFrame is empty, then it returns True . If the DataFrame is not empty, then it returns False . CREATE A EMPTY DATAFRAME: Create an empty dataframe and will check if it is empty or not by using the property DataFrame.empty If it is empty, then it returns True. import  pandas as pd  empty_dataframe = pd.DataFrame() if empty_dataframe.empty:       print ('True') CREATE A NON-EMPTY DATAFRAME: Create a non-empty dataframe and will check if the dataframe is empty or not.  If it is not empty, then it returns False. import  pandas as pd  pd_dataframe= pd.DataFrame( [['Vetri',20],['Vishal',21],['Jack',18]],   columns=['Name', 'Age'] ) if  pd_dataframe .empty is False:      print ('False') Thanks for choosing our blog 😊. 
Read more

Introduction to Java

-
Image
JAVA      James Gosling Invented Java in 1991 and it is developed by Sun Microsystems. But the first version was released in 1995. The current version of Java is Java 17.  Java is an Object Oriented Programming language and it is platform-independent ( Write once and run anywhere ).  Java is easy to compile, and debug and it provides reusability of code that makes development faster.  The following four are important to run, compile and debug the Java application   JDK - Java Development Kit   JRE - Java Runtime Environment  JVM - Java Virtual Machine  Compiler - Java Compiler.    JDK           It is used to provide tools to develop Java applications it comes with Compiler, JRE, and JVM.  COMPILER  Java is the compiler used for compilation.  The compiler will check the program syntax and if it is wrong then throw the error at compile time.   If we don't hav...
Read more