MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL WHERE: conditional query

The WHERE clause in MySQL is used to filter records. It's used to specify the criteria that the rows or records must meet to be selected or affected by a SELECT, UPDATE, DELETE, or INSERT statement.

Here's the basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The WHERE clause follows the FROM clause and precedes the GROUP BY, HAVING, ORDER BY, LIMIT, or OFFSET clauses if they are used.

Examples:

Let's say you have a table named employees with the following data:

idnameagesalary
1John2550000
2Jane2860000
3Alice3070000
4Bob3580000
5Carol4090000

You can use the WHERE clause to filter records. For example, if you want to select employees who earn more than 60000, you would use:

SELECT *
FROM employees
WHERE salary > 60000;

This will return:

idnameagesalary
3Alice3070000
4Bob3580000
5Carol4090000

You can also use the WHERE clause to combine more than one condition using the AND and OR operators. For example, to select employees who earn more than 60000 and are less than 35 years old:

SELECT *
FROM employees
WHERE salary > 60000 AND age < 35;

This will return:

idnameagesalary
3Alice3070000

The WHERE clause is also used in the UPDATE and DELETE statements to specify which records to update or delete. For example, to give a raise to employees who earn less than 60000:

UPDATE employees
SET salary = salary * 1.1
WHERE salary < 60000;

Or to delete employees who are older than 60:

DELETE FROM employees
WHERE age > 60;

Remember to use the WHERE clause carefully, especially with UPDATE and DELETE statements, as it could affect more records than you intend.

  1. MySQL WHERE Clause Example:

    • Description: The WHERE clause is used to filter records in a MySQL query based on specified conditions.
    • Example Code:
      SELECT column1, column2, ...
FROM table_name
WHERE condition;

  2. How to Use WHERE in MySQL:

    • Description: Use the WHERE clause to specify conditions for filtering records in a SELECT, UPDATE, or DELETE statement.
    • Example Code:
      SELECT product_name, price
FROM products
WHERE category_id = 1;

  3. Conditional Filtering with WHERE in MySQL:

    • Description: WHERE allows you to filter records based on specific conditions, such as equality or inequality.
    • Example Code:
      SELECT customer_name, order_date
FROM orders
WHERE total_amount > 1000;

  4. Using AND and OR in WHERE Conditions in MySQL:

    • Description: Combine multiple conditions using AND and OR operators in the WHERE clause.
    • Example Code:
      SELECT employee_name, salary
FROM employees
WHERE department = 'Sales' AND (position = 'Manager' OR position = 'Representative');

  5. Comparisons and Operators in MySQL WHERE Clause:

    • Description: Use various operators (e.g., =, <>, >, <, BETWEEN) for comparisons in WHERE conditions.
    • Example Code:
      SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 100;

  6. Negating Conditions with NOT in MySQL WHERE:

    • Description: Use the NOT operator to negate conditions in the WHERE clause.
    • Example Code:
      SELECT customer_name, total_purchases
FROM customers
WHERE NOT total_purchases < 500;

  7. Combining Multiple Conditions with Parentheses in MySQL WHERE:

    • Description: Use parentheses to control the logical grouping of conditions in complex WHERE clauses.
    • Example Code:
      SELECT order_id, order_date
FROM orders
WHERE (total_amount > 1000 OR status = 'Pending') AND customer_id = 102;

  8. Dynamic Filtering Using Variables in MySQL WHERE:

    • Description: Use variables to dynamically filter records in the WHERE clause.
    • Example Code:
      SET @min_price = 50;
SET @max_price = 100;
SELECT product_name, price
FROM products
WHERE price BETWEEN @min_price AND @max_price;

  9. Examples of Using WHERE in MySQL Queries:

    • Description: Various examples showcasing the application of WHERE clause in different scenarios.
    • Example Code:
      SELECT employee_name, hire_date
FROM employees
WHERE department_id = 3;

      UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 101;
Copyright © devanddep.com | All Rights Reserved