Functions and Operators

  Functions and Operators

https://dev.mysql.com/doc/refman/8.4/en/functions.html

Data type

https://dev.mysql.com/doc/refman/8.4/en/data-types.html

books

 https://github.com/manjunath5496/MySQL-Books/blob/master/README.md

join

 CREATE TABLE members (

    member_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (member_id)
);

CREATE TABLE committees (
    committee_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (committee_id)
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the tables members and committees :

INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');

INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');
mysql> SELECT * FROM members;
	+-----------+--------+
	| member_id | name   |
	+-----------+--------+
	|         1 | John   |
	|         2 | Jane   |
	|         3 | Mary   |
	|         4 | David  |
	|         5 | Amelia |
	+-----------+--------+
	5 rows in set (0.00 sec)

	mysql> SELECT * FROM committees;
	+--------------+--------+
	| committee_id | name   |
	+--------------+--------+
	|            1 | John   |
	|            2 | Mary   |
	|            3 | Amelia |
	|            4 | Joe    |
	+--------------+--------+
	4 rows in set (0.00 sec)



	mysql> select m.member_id,c.committee_id,m.name,c.name from members m  inner join committees as c on c.name=m.name;
	+-----------+--------------+--------+--------+
	| member_id | committee_id | name   | name   |
	+-----------+--------------+--------+--------+
	|         1 |            1 | John   | John   |
	|         3 |            2 | Mary   | Mary   |
	|         5 |            3 | Amelia | Amelia |
	+-----------+--------------+--------+--------+
	3 rows in set (0.01 sec)

	mysql> select m.member_id,c.committee_id,m.name,c.name from members m left  join committees as c on c.name=m.name;
	+-----------+--------------+--------+--------+
	| member_id | committee_id | name   | name   |
	+-----------+--------------+--------+--------+
	|         1 |            1 | John   | John   |
	|         2 |         NULL | Jane   | NULL   |
	|         3 |            2 | Mary   | Mary   |
	|         4 |         NULL | David  | NULL   |
	|         5 |            3 | Amelia | Amelia |
	+-----------+--------------+--------+--------+
	5 rows in set (0.00 sec)

	mysql> select m.member_id,c.committee_id,m.name,c.name from members m right  join committees as c on c.name=m.name;
	+-----------+--------------+--------+--------+
	| member_id | committee_id | name   | name   |
	+-----------+--------------+--------+--------+
	|         1 |            1 | John   | John   |
	|         3 |            2 | Mary   | Mary   |
	|         5 |            3 | Amelia | Amelia |
	|      NULL |            4 | NULL   | Joe    |
	+-----------+--------------+--------+--------+
	4 rows in set (0.00 sec)


	mysql> SELECT * FROM members where name not in(SELECT name FROM committees);
	+-----------+-------+
	| member_id | name  |
	+-----------+-------+
	|         2 | Jane  |
	|         4 | David |
	+-----------+-------+
	2 rows in set (0.01 sec)


	mysql> select * from committees where name not in ( select name from members);
	+--------------+------+
	| committee_id | name |
	+--------------+------+
	|            4 | Joe  |
	+--------------+------+
	1 row in set (0.00 sec)



Code language: SQL (Structured Query Language) (sql)

CASE

 mysql> SELECT 

    ->     CASE 

    ->         WHEN 4 > 5 THEN 'Condition 1: 4 is greater than 5'

    ->         WHEN 4 = 4 THEN 'Condition 2: 4 is equal to 4'

    ->         WHEN 4 < 3 THEN 'Condition 3: 4 is less than 3'

    ->         ELSE 'Default case: None of the conditions were true'

    ->     END AS result;

+------------------------------+

| result                       |

+------------------------------+

| Condition 2: 4 is equal to 4 |

+------------------------------+

1 row in set (0.01 sec)

------------------

SELECT CASE YEAR(CURDATE())

    WHEN 2025 THEN 'Estamos en el 2025, un año de grandes oportunidades.'

    WHEN 2024 THEN 'Estamos en el 2024, ¡es hora de prepararnos para el futuro!'

    WHEN 2023 THEN 'Es el 2023, un año para reflexionar sobre lo que hemos logrado.'

    WHEN 2022 THEN 'Ya es 2022, un año para reconstruir y avanzar.'

    WHEN 2021 THEN 'Estamos en el 2021, la esperanza y la resiliencia continúan.'

    WHEN 2020 THEN 'El 2020 fue un año desafiante, pero aprendimos mucho.'

    ELSE 'Año desconocido, parece que estamos en el futuro.'

END AS tp;

testing null

 mysql> SELECT product_id,if(isnull(price)=0,'A','B') as p from products;

---------

mysql> SELECT product_id,isnull(price) as p from products;

+------------+---+

| product_id | p |

+------------+---+

|          1 | 0 |

|          2 | 0 |

|          3 | 0 |

+------------+---+

3 rows in set (0.00 sec)

mysql> SELECT IF(NULL = NULL, 1, 3);

+-----------------------+

| IF(NULL = NULL, 1, 3) |

+-----------------------+

|                     3 |

+-----------------------+

1 row in set (0.00 sec)

mysql> SELECT IF(NULL is NULL, 1, 3);

+------------------------+

| IF(NULL is NULL, 1, 3) |

+------------------------+

|                      1 |

+------------------------+

Prepare statement

 -- Step 1: Prepare an INSERT statement to insert a user into the users table

PREPARE insert_stmt FROM 'INSERT INTO users (name, email) VALUES (?, ?)';

-- Step 2: Set values for placeholders

SET @name = 'John Doe', @email = 'john.doe@example.com';

-- Step 3: Execute the prepared INSERT statement with the provided values

EXECUTE insert_stmt USING @name, @email;

-- Step 4: Deallocate the prepared INSERT statement

DEALLOCATE PREPARE insert_stmt;

-- Step 5: Prepare a SELECT statement to retrieve user by ID

PREPARE select_stmt FROM 'SELECT id, name, email FROM users WHERE id = ?';

-- Step 6: Set the value for the ID of the user to retrieve

SET @id = 1;

-- Step 7: Execute the SELECT statement with the ID

EXECUTE select_stmt USING @id;

-- Step 8: Deallocate the prepared SELECT statement

DEALLOCATE PREPARE select_stmt;

MySQL Transactions

 In MySQL, transactions allow you to group multiple SQL statements into a single unit of work. This ensures that the changes made by those statements are executed together, and if something goes wrong, the transaction can be rolled back to maintain data integrity. Transactions are especially useful when you need to perform a series of operations that should either all succeed or all fail.

Basic Commands for MySQL Transactions

1. Start a Transaction
   To start a transaction, you use the START TRANSACTION or BEGIN command.

START TRANSACTION;

2 Commit a Transaction
To save all the changes made during the transaction, you use the COMMIT command. Once a transaction is committed, the changes become permanent.

COMMIT;

3 Rollback a Transaction
If something goes wrong during the transaction (e.g., a constraint violation), you can use the ROLLBACK command to undo all the changes made since the transaction started.

ROLLBACK;

4 Savepoints
A savepoint allows you to create a point within a transaction to which you can roll back without affecting the entire transaction.

SAVEPOINT savepoint_name;

If you want to roll back to a specific savepoint:

ROLLBACK TO SAVEPOINT savepoint_name;

5 Autocommit Mode
By default, MySQL runs in autocommit mode, meaning every SQL statement is treated as a transaction. If you want to disable autocommit for your session (so you can manually manage transactions), use the following command:

SET autocommit = 0;

To re-enable autocommit, use:

SET autocommit = 1;

Example of Using Transactions in MySQL

START TRANSACTION;

-- Insert data into a table

INSERT INTO accounts (account_id, balance) VALUES (1, 500);

INSERT INTO accounts (account_id, balance) VALUES (2, 300);

-- Simulate an error (e.g., a constraint violation)

-- For example, inserting an invalid value

-- INSERT INTO accounts (account_id, balance) VALUES (NULL, 'invalid_balance');

-- If no error occurs, commit the transaction

COMMIT;

In the above example:

1. We start a transaction with START TRANSACTION.
2. We perform some INSERT operations.
3. If everything is successful, we use COMMIT to save the changes to the database.
4. If an error occurs during the transaction (e.g., inserting invalid data), we could roll back the transaction with ROLLBACK to ensure the database stays in a consistent state.

Isolation Levels in MySQL

MySQL supports different transaction isolation levels, which control the visibility of changes made within a transaction to other transactions. The isolation level can be set as follows:

Here are the available isolation levels:

1. READ UNCOMMITTED: Transactions can see uncommitted changes made by other transactions.
2. READ COMMITTED: Transactions can only see committed changes from other transactions.
3. REPEATABLE READ: Ensures that if a transaction reads a value, it will see the same value if it reads it again later. This is the default isolation level in MySQL.
4. SERIALIZABLE: The highest isolation level. It ensures no other transactions can read, write, or even lock the same rows.

Example:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Key Points to Remember

• Atomicity: A transaction ensures that all the changes are applied or none at all. If a transaction is rolled back, none of the operations will take effect.
• Consistency: Transactions bring the database from one valid state to another, ensuring integrity.
• Isolation: Transactions are isolated from each other. Depending on the isolation level, the changes in one transaction may or may not be visible to others.
• Durability: Once a transaction is committed, the changes are permanent, even if the system crashes.

Conclusion

Transactions in MySQL are essential for maintaining data integrity and consistency, especially in systems where multiple operations need to be executed together. By using START TRANSACTION, COMMIT, and ROLLBACK, you can ensure that your database operations are reliable and robust.