When you want to do some operations on the data in the database, then you must have to write the query in the predefined syntax of SQL. The syntax of the structured query language is a unique set of rules and guidelines, which is not case-sensitive. Its Syntax is defined and maintained by the ISO and ANSI standards. Following are some most important points about the SQL syntax which are to remember: You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL keywords in uppercase improves the readability of the SQL query. SQL statements or syntax are dependent on text lines. We can place a single SQL statement on one or multiple text lines. You can perform most of the action in a database with SQL statements. SQL syntax depends on relational algebra and tuple relational calculus.
Most Important SQL Commands and Statements
The following is an example SQL query using the syntax mentioned in the prompt:
SELECT * FROM employees
WHERE department = ‘Marketing’
ORDER BY hire_date DESC;
Here are some examples of the most important SQL commands and statements:
- SELECT statement: The SELECT statement is used to retrieve data from one or more tables in a database. For example:
SELECT * FROM customers;This retrieves all columns from the “customers” table.
- UPDATE statement: The UPDATE statement is used to modify existing data in a table. For example:
UPDATE employees SET salary = 50000 WHERE employee_id = 123;This sets the salary of the employee with ID 123 to 50000.
- DELETE statement: The DELETE statement is used to remove data from a table. For example:
DELETE FROM customers WHERE customer_id = 456;This removes the customer with ID 456 from the “customers” table.
- CREATE TABLE statement: The CREATE TABLE statement is used to create a new table in a database. For example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_price DECIMAL(10,2)
);This creates a new “orders” table with columns for the order ID, customer ID, order date, and total price.
- ALTER TABLE statement: The ALTER TABLE statement is used to modify the structure of an existing table. For example:
ALTER TABLE customers ADD COLUMN phone_number VARCHAR(20);This adds a new “phone_number” column to the “customers” table.
- DROP TABLE statement: The DROP TABLE statement is used to remove an entire table from a database. For example:
DROP TABLE orders;- CREATE DATABASE statement: The CREATE DATABASE statement is used to create a new database. For example:
CREATE DATABASE mydatabase;This creates a new database called “mydatabase”.
- DROP DATABASE statement: The DROP DATABASE statement is used to remove an entire database from the server. For example:
DROP DATABASE mydatabase;This removes the “mydatabase” database from the server.
- INSERT INTO statement: The INSERT INTO statement is used to add new data to a table. For example:
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (123, 'John', 'Doe', '2022-01-01', 40000);This adds a new employee with ID 123, first name “John”, last name “Doe”, hire date “2022-01-01”, and salary 40000 to the “employees” table.
- TRUNCATE TABLE statement: The TRUNCATE TABLE statement is used to remove all data from a table. For example:
TRUNCATE TABLE orders;This removes all data from the “orders” table.
- DESCRIBE statement: The DESCRIBE statement is used to retrieve information about the structure of a table. For example:
DESCRIBE customers;This retrieves information about the columns in the “customers” table.
- DISTINCT clause: The DISTINCT clause is used to retrieve only unique values from a column. For example:
SELECT DISTINCT department FROM employees;
This retrieves all unique department names from the “employees” table.
- COMMIT statement: The COMMIT statement is used to save changes made to the database. For example:
COMMIT;
This saves all changes made since the last COMMIT statement.
- ROLLBACK statement: The ROLLBACK statement is used to undo changes made to the database. For example:
ROLLBACK;This undoes all changes made since the last COMMIT statement.
CREATE INDEX idx_customers_last_name ON customers (last_name);
This creates an index on the “last_name” column of the “customers” table.
- DROP INDEX statement: The DROP INDEX statement is used to remove an index from a table. For example:
DROP INDEX idx_customers_last_name ON customers;This removes the “idx_customers_last_name” index from the “customers” table.
- USE statement: The USE statement is used to switch to a different database. For example:
USE mydatabase;
This switches to the “mydatabase” database.
- GROUP BY clause: The GROUP BY clause is used to group data by one or more columns. For example:
SELECT department, COUNT(*) FROM employees GROUP BY department;
This retrieves the number of employees in each department from the “employees” table.
- HAVING clause: The HAVING clause is used to filter groups created by the GROUP BY clause. For example:
SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;
This retrieves the average salary for each department from the “employees” table, but only shows departments where the average salary is greater than 50000.
- JOIN clause: The JOIN clause is used to combine data from two or more tables based on a related column. For example:
SELECT customers.customer_id, orders.order_date, orders.total_price
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
This retrieves the customer ID, order date, and total price for all orders from the “orders” table, along with the corresponding customer ID from the “customers” table.
- LIKE operator: The LIKE operator is used to match text values based on a pattern. For example:
SELECT * FROM customers WHERE last_name LIKE 'Sm%';
This retrieves all customers with a last name that starts with “Sm” from the “customers” table.
- IN operator: The IN operator is used to match a value against a list of possible values. For example:
SELECT * FROM products WHERE category IN ('Electronics', 'Appliances');
This retrieves all products from the “products” table that have a category of “Electronics” or “Appliances”.
- BETWEEN operator: The BETWEEN operator is used to match a value within a range of values. For example:
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-01-31';
This retrieves all orders from the “orders” table that were placed in January 2022.
- NULL operator: The NULL operator is used to match null values. For example:
SELECT * FROM customers WHERE phone_number IS NULL;
This retrieves all customers from the “customers” table that do not have a phone number.
- EXISTS operator: The EXISTS operator is used to check if a subquery returns any rows. For example:
SELECT * FROM customers WHERE EXISTS (SELECT * FROM orders WHERE customers.customer_id = orders.customer_id);
This retrieves all customers from the “customers” table that have placed an order.
- UNION operator: The UNION operator is used to combine the results of two or more SELECT statements. For example:
SELECT customer_id, last_name FROM customers
UNION
SELECT customer_id, last_name FROM employees;
This retrieves the customer ID and last name from both the “customers” and “employees” tables, removing duplicates.
- ORDER BY clause: The ORDER BY clause is used to sort the results of a query by one or more columns. For example:
SELECT * FROM products ORDER BY price DESC;
This retrieves all products from the “products” table, sorted by price in descending order.
- LIMIT clause: The LIMIT clause is used to limit the number of rows returned by a query. For example:
SELECT * FROM customers LIMIT 10;
This retrieves the first 10 rows from the “customers” table.
- OFFSET clause: The OFFSET clause is used to skip a specified number of rows before returning results. For example:
SELECT * FROM customers LIMIT 10 OFFSET 10;
This retrieves 10 rows from the “customers” table, starting from the 11th row.
- Stored procedures: Stored procedures are a way to group SQL statements into a reusable, named block of code. For example:
CREATE PROCEDURE calculate_total_price (IN order_id INT, OUT total_price DECIMAL(10,2))
BEGIN
SELECT SUM(quantity * unit_price) INTO total_price FROM order_items WHERE order_id = order_id;
END
This creates a stored procedure called “calculate_total_price” that takes an order ID as input and returns the total price of the order as output.
- Triggers: Triggers are a way to execute SQL statements automatically in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. For example:
CREATE TRIGGER update_employee_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF NEW.salary > OLD.salary THEN
INSERT INTO salary_changes (employee_id, change_date, old_salary, new_salary)
VALUES (NEW.employee_id, NOW(), OLD.salary, NEW.salary);
END IF;
END
This creates a trigger called “update_employee_salary” that inserts a row into the “salary_changes” table whenever an employee’s salary is updated to a higher value.
- Joins: Joins are used to combine data from two or more tables based on a related column or columns. There are several types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. For example:
SELECT orders.order_id, customers.last_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
This retrieves the order ID and last name of each customer who has placed an order.
- Subqueries: Subqueries are queries that are nested within another query, and are used to retrieve data based on the results of another query. For example:
SELECT last_name, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
This retrieves the last name and first name of all employees who work in the “Sales” department.
- Views: Views are virtual tables that are created by a query, and can be used like a regular table in other queries. For example:
CREATE VIEW customer_orders AS
SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
This creates a view called “customer_orders” that retrieves the customer ID, order ID, and order date of each customer who has placed an order.
- Functions: Functions are built-in or user-defined procedures that can be called within a query to perform specific operations. Some common SQL functions include COUNT(), SUM(), AVG(), MAX(), and MIN(). For example:
SELECT COUNT(*) FROM customers;
This retrieves the total number of rows in the “customers” table.
- GROUP BY clause: The GROUP BY clause is used to group rows in a query based on one or more columns, and is often used with aggregate functions. For example:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
This retrieves the average salary for each department in the “employees” table.
- HAVING clause: The HAVING clause is used to filter the results of a query based on the results of an aggregate function. For example:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
This retrieves the average salary for each department in the “employees” table, but only includes departments where the average salary is greater than $50,000.
- CASE statement: The CASE statement is used to perform conditional logic within a query. For example:
SELECT order_id, CASE WHEN total_price > 1000 THEN 'High' ELSE 'Low' END AS order_category
FROM orders;
This retrieves the order ID and a category (“High” or “Low”) based on whether the total price of the order is greater than $1,000.
- UNION operator: The UNION operator is used to combine the results of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns, and the data types of the columns must be compatible. For example:
SELECT product_name, price FROM products
UNION
SELECT product_name, price FROM discontinued_products;
This retrieves the product name and price of all products, including both active and discontinued products.
- EXISTS operator: The EXISTS operator is used to test whether a subquery returns any rows. It returns true if the subquery returns at least one row, and false otherwise. For example:
SELECT customer_id, first_name, last_name
FROM customers
WHERE EXISTS (SELECT * FROM orders WHERE orders.customer_id = customers.customer_id);
This retrieves the customer ID, first name, and last name of all customers who have placed at least one order.
- NOT operator: The NOT operator is used to negate a condition in a WHERE clause or a subquery. For example:
SELECT * FROM products WHERE NOT price > 50;
This retrieves all products where the price is not greater than $50.
- LIKE operator: The LIKE operator is used to match a pattern in a string. The pattern can include wildcards, such as % (matches any string of zero or more characters) and _ (matches any single character). For example:
SELECT product_name FROM products WHERE product_name LIKE '%apple%';
This retrieves all products where the product name contains the word “apple”.
- IN operator: The IN operator is used to test whether a value is equal to any value in a list. For example:
SELECT * FROM products WHERE category_id IN (1, 2, 3);
This retrieves all products where the category ID is 1, 2, or 3.
- BETWEEN operator: The BETWEEN operator is used to test whether a value is between two other values. For example:
SELECT * FROM products WHERE price BETWEEN 10 AND 20;
This retrieves all products where the price is between $10 and $20.
- ORDER BY clause: The ORDER BY clause is used to sort the results of a query based on one or more columns. For example:
SELECT product_name, price FROM products ORDER BY price DESC;
This retrieves the product name and price of all products, sorted in descending order by price.
- GROUP BY clause: The GROUP BY clause is used to group the results of a query based on one or more columns. It is often used with aggregate functions (such as COUNT, SUM, AVG, MIN, and MAX) to compute summary information for each group. For example:
SELECT category_id, COUNT(*) as num_products
FROM products
GROUP BY category_id;
This retrieves the number of products in each category.
- HAVING clause: The HAVING clause is used to filter the results of a GROUP BY query based on a condition that applies to the groups. It is similar to the WHERE clause, but it operates on the groups rather than on individual rows. For example:
SELECT category_id, COUNT(*) as num_products
FROM products
GROUP BY category_id
HAVING COUNT(*) > 10;
This retrieves the number of products in each category where the number of products is greater than 10.
- JOIN operator: The JOIN operator is used to combine rows from two or more tables based on a related column between them. There are several types of joins, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. For example:
SELECT orders.order_id, customers.first_name, customers.last_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
This retrieves the order ID and customer name for all orders, including only those customers who have placed an order.
- Subquery: A subquery is a query that is embedded within another query. It is often used to retrieve information that is based on the results of another query. For example:
SELECT product_name, price
FROM products
WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');
This retrieves the product name and price of all products in the Electronics category.
- Views: A view is a virtual table that is based on the result of a SELECT statement. It is similar to a table, but it does not contain any data of its own. Instead, it retrieves the data from the underlying tables each time it is queried. For example:
CREATE VIEW product_details AS
SELECT products.product_id, products.product_name, categories.category_name, products.price
FROM products
INNER JOIN categories ON products.category_id = categories.category_id;
This creates a view that retrieves the product ID, product name, category name, and price of all products.
These are just a few of the most important SQL commands and statements. There are many others that can be used to perform various operations on a database.
- EXISTS operator: The EXISTS operator is used to test for the existence of rows returned by a subquery. It returns true if the subquery returns one or more rows, and false otherwise. For example:
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM orders
WHERE orders.customer_id = customers.customer_id);
This retrieves all customers who have placed at least one order.
- NOT operator: The NOT operator is used to negate a condition in a WHERE clause. For example:
SELECT *
FROM products
WHERE NOT category_id = 1;
This retrieves all products that do not belong to category 1.
- IN operator: The IN operator is used to test whether a value is contained in a list of values. For example:
SELECT *
FROM orders
WHERE customer_id IN (SELECT customer_id
FROM customers
WHERE city = 'New York');
This retrieves all orders placed by customers who live in New York.
- LIKE operator: The LIKE operator is used to perform pattern matching on a string value. It is often used with the wildcard characters % (matches any sequence of characters) and _ (matches any single character). For example:
SELECT *
FROM products
WHERE product_name LIKE '%DVD%';
This retrieves all products that have the word “DVD” in their name.
- UNION operator: The UNION operator is used to combine the results of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns and compatible data types. For example:
SELECT product_name, price
FROM products
WHERE category_id = 1
UNION
SELECT product_name, price
FROM products
WHERE category_id = 2;
This retrieves the product name and price of all products in category 1 and category 2.
- LIMIT clause: The LIMIT clause is used to limit the number of rows returned by a SELECT statement. It is often used with an ORDER BY clause to retrieve the top N rows based on a certain criterion. For example:
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;
This retrieves the 10 most recent orders.
- Stored procedures: A stored procedure is a named set of SQL statements that are stored in a database and can be executed on demand. It is often used to encapsulate complex business logic that needs to be executed repeatedly. For example:
CREATE PROCEDURE get_orders_by_customer(IN customer_id INT)
BEGIN
SELECT *
FROM orders
WHERE customer_id = customer_id;
END;
This creates a stored procedure that retrieves all orders placed by a given customer.
- Triggers: A trigger is a special type of stored procedure that is automatically executed in response to certain events, such as INSERT, UPDATE, or DELETE operations on a table. It is often used to enforce business rules or maintain data integrity. For example:
CREATE TRIGGER update_inventory
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
UPDATE products
SET quantity_in_stock = quantity_in_stock - NEW.quantity_ordered
WHERE product_id = NEW.product_id;
END;
This creates a trigger that updates the quantity in stock of a product every time an order is placed.
- Views: A view is a virtual table that is derived from one or more tables in a database. It is often used to simplify complex queries or provide a simplified interface to a database. For example:
CREATE VIEW top_customers AS
SELECT customers.customer_id, customers.first_name, customers.last_name, COUNT(*) AS num_orders
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id
ORDER BY num_orders DESC
LIMIT 10;
This creates a view that retrieves the top 10 customers based on the number of orders they have placed.
- Joins: Joins are used to combine data from two or more tables based on a related column. There are several types of joins, including INNER JOIN (returns only matching rows from both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right table), and RIGHT JOIN (returns all rows from the right table and matching rows from the left table). For example:
SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
This retrieves all customers who have placed orders.
- Subqueries: A subquery is a SELECT statement that is nested within another SELECT statement. It is often used to retrieve data from one table based on the results of another table. For example:
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE order_date >= '2022-01-01');
This retrieves all customers who have placed orders since January 1, 2022.
- Aggregate functions: Aggregate functions are used to perform calculations on a set of values and return a single result. Common aggregate functions include SUM (returns the sum of a set of values), AVG (returns the average of a set of values), MAX (returns the maximum value in a set of values), and MIN (returns the minimum value in a set of values). For example:
SELECT SUM(price)
FROM products;
This retrieves the total price of all products.
- Group by clause: The GROUP BY clause is used to group rows based on one or more columns and perform aggregate functions on each group. For example:
SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id;
This retrieves the number of products in each category.
- Order by clause: The ORDER BY clause is used to sort the results of a SELECT statement based on one or more columns. It can be used with ASC (ascending) or DESC (descending) to control the order of the sorting. For example:
SELECT *
FROM products
ORDER BY price DESC;
This retrieves all products sorted by price in descending order.
- Primary key: A primary key is a column or set of columns that uniquely identifies each row in a table. It is often used to enforce data integrity and enable efficient querying of a table. For example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50)
);
This creates a customers table with a primary key on the customer_id column.
- Foreign key: A foreign key is a column or set of columns that references a primary key in another table. It is often used to enforce referential integrity between related tables. For example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
This creates an orders table with a foreign key on the customer_id column that references the customer_id primary key in the customers table.
- Indexes: Indexes are used to improve the performance of queries by allowing the database to quickly locate the rows that match a certain condition. An index is created on one or more columns in a table, and it stores a copy of the data from those columns along with a pointer to the original row. For example:
CREATE INDEX idx_last_name ON customers (last_name);
This creates an index on the last_name column in the customers table.
- Stored procedures: A stored procedure is a precompiled set of SQL statements that is stored in the database and can be executed as a single unit. Stored procedures can be used to perform complex operations, encapsulate business logic, and improve performance. For example:
CREATE PROCEDURE get_customer_orders (IN customer_id INT)
BEGIN
SELECT *
FROM orders
WHERE customer_id = customer_id;
END;
This creates a stored procedure that retrieves all orders for a given customer.
- Triggers: A trigger is a set of SQL statements that are automatically executed in response to a certain event, such as an INSERT, UPDATE, or DELETE statement. Triggers can be used to enforce data integrity, audit changes to a table, or perform complex data transformations. For example:
CREATE TRIGGER log_order_changes
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_logs (order_id, action, timestamp)
VALUES (NEW.order_id, 'inserted', NOW());
END;
This creates a trigger that logs all changes to the orders table in the order_logs table.
- Transactions: A transaction is a series of SQL statements that are executed as a single unit, and either all of them are executed successfully, or none of them are executed. Transactions can be used to ensure data consistency and integrity, and to prevent conflicts between concurrent transactions. For example:
START TRANSACTION;
INSERT INTO customers (first_name, last_name, email)
VALUES (‘John’, ‘Doe’, ‘john.doe@example.com’);
INSERT INTO orders (customer_id, order_date)
VALUES (LAST_INSERT_ID(), NOW());
COMMIT;
This creates a transaction that inserts a new customer and a new order for that customer. If any of the statements fail, the entire transaction is rolled back and none of the changes are committed to the database.
- Views: A view is a virtual table that is based on the result set of a SELECT statement. Views can be used to simplify complex queries, encapsulate business logic, and restrict access to sensitive data. For example:
CREATE VIEW customer_orders AS
SELECT customers.first_name, customers.last_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
This creates a view that displays the first name, last name, and order date of each customer who has placed an order.
- Case statements: A case statement allows you to perform conditional logic within a SQL query. This is useful when you need to apply different logic based on the value of a certain column. For example:
SELECT order_id,
order_date,
CASE
WHEN order_total > 1000 THEN 'High'
WHEN order_total > 500 THEN 'Medium'
ELSE 'Low'
END AS order_priority
FROM orders;
This query returns the order ID, order date, and a priority level (high, medium, or low) based on the value of the order_total column.
- Group by statement: The GROUP BY statement is used to group rows that have the same values in one or more columns, and to apply aggregate functions (such as COUNT, SUM, AVG, MAX, or MIN) to each group. For example:
SELECT customer_id, COUNT(*) as num_orders
FROM orders
GROUP BY customer_id;
This query groups orders by customer ID and returns the number of orders for each customer.
- Having statement: The HAVING statement is used to filter the results of a GROUP BY query based on a condition that applies to the aggregated values. For example:
SELECT customer_id, COUNT(*) as num_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
This query groups orders by customer ID and returns only those customers who have placed more than 5 orders.
- Union statement: The UNION statement is used to combine the results of two or more SELECT statements into a single result set. The columns in each SELECT statement must match in data type and order. For example:
SELECT first_name, last_name, email
FROM customers
WHERE last_name = 'Doe'
UNION
SELECT first_name, last_name, email
FROM customers
WHERE email = 'john.doe@example.com';
This query returns all customers whose last name is ‘Doe’ or whose email address is ‘john.doe@example.com‘.
- Subqueries: A subquery is a SELECT statement that is nested inside another SELECT statement, and that is used to provide the values for a condition or calculation in the outer query. Subqueries can be used in WHERE, HAVING, and SELECT clauses. For example:
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_total > 1000
);
This query returns all customers who have placed an order with a total value greater than 1000. The subquery retrieves the customer IDs for those orders.
- Joins: Joins are used to combine rows from two or more tables based on a related column between them. There are different types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. For example:
SELECT customers.first_name, customers.last_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
This query returns the first name, last name, and order date for each customer who has placed an order.
- Aliases: Aliases are used to assign a temporary name to a table or a column in a SQL query. This can be useful for readability and for avoiding naming conflicts. For example:
SELECT c.first_name, c.last_name, o.order_date
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id;
This query is equivalent to the previous example, but it uses aliases to make the query easier to read.
- Indexes: Indexes are used to improve the performance of database queries by creating a data structure that allows faster lookup of data based on a specific column or set of columns. For example:
CREATE INDEX idx_customers_last_name ON customers (last_name);
This creates an index on the last_name column of the customers table, which can speed up queries that filter or sort by last name.
- Triggers: Triggers are special stored procedures that are automatically executed in response to certain events, such as insertions, updates, or deletions of data. Triggers can be used to enforce business rules, validate data, or perform other automated tasks. For example:
CREATE TRIGGER trg_orders_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_logs (order_id, log_text)
VALUES (NEW.order_id, 'New order created');
END;
This creates a trigger that is executed after a new order is inserted into the orders table. The trigger inserts a new record into the order_logs table with a log message indicating that a new order has been created.
- Stored procedures: Stored procedures are precompiled SQL code that can be stored in a database and reused multiple times. Stored procedures can be used to encapsulate business logic, improve performance, and enforce security. For example:
CREATE PROCEDURE sp_get_customer_orders (IN customer_id INT)
BEGIN
SELECT *
FROM orders
WHERE customer_id = customer_id;
END;
This creates a stored procedure that retrieves all orders for a specific customer ID.
- Cursors: Cursors are used to iterate over the result set of a query, one row at a time. Cursors can be used to perform complex data manipulation or to update data in a row-by-row fashion. Cursors are typically used in stored procedures or other server-side code. For example:
DECLARE cur_orders CURSOR FOR
SELECT order_id, order_date, order_total
FROM orders
WHERE customer_id = @customer_id;OPEN cur_orders;FETCH NEXT FROM cur_orders INTO @order_id, @order_date, @order_total;WHILE @@FETCH_STATUS = 0BEGIN
— do something with the row
…FETCH NEXT FROM cur_orders INTO @order_id, @order_date, @order_total;
END;
CLOSE cur_orders;
DEALLOCATE cur_orders;
This code declares a cursor that retrieves all orders for a specific customer ID. The cursor is then opened, and each row is fetched and processed one at a time. Finally, the cursor is closed and deallocated.
