DEV Community: Yuhao Chen

MySQL Basics: Understanding Isolation Level and Transaction Anomalies

Yuhao Chen — Sat, 28 Sep 2024 02:58:31 +0000

Isolation Level & Transaction Anomalies

In relational databases, the isolation level of a transaction controls the visibility of data changes between transactions. Isolation levels define how one transaction is isolated from others in terms of read and write operations. By managing this isolation, you can prevent or allow certain types of transaction anomalies such as dirty reads, non-repeatable reads, and phantom reads.

Transaction Anomalies

Before we dive into isolation levels, let’s first understand some common transaction anomalies:

Dirty Read: This happens when a transaction reads data that has been modified by another transaction but not yet committed. If the other transaction rolls back, the read data becomes invalid.
Non-Repeatable Read: Occurs when a transaction reads the same row multiple times and gets different data each time because another transaction has modified the row in between the reads.
Phantom Read: Happens when a transaction reads a set of rows that match a condition, but when it reads again, new rows are added or deleted by another transaction, causing the result set to change.

SQL Isolation Levels

SQL defines four standard isolation levels that control how transactions interact with each other and the types of anomalies they allow. These are:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Each level offers a trade-off between performance (concurrency) and consistency. The stricter the isolation level, the less concurrency, but the greater the consistency.

1. READ UNCOMMITTED

Description: The lowest isolation level, where transactions can read data that has been modified but not yet committed by other transactions. This allows dirty reads.
Anomalies
- Dirty reads are possible.
- Non-repeatable reads are possible.
- Phantom reads are possible.

Example:

Transaction 1 (T1) updates the balance of an account but hasn't committed:

UPDATE accounts SET balance = 5000 WHERE account_id = 1;

Transaction 2 (T2) reads the balance before T1 commits:

SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 5000 (dirty read)

If T1 rolls back, T2 has read an invalid balance.

2. READ COMMITTED

Description: A transaction can only read data that has been committed by other transactions. This prevents dirty reads, but non-repeatable reads and phantom reads can still occur.
Anomalies
- Dirty reads are not possible.
- Non-repeatable reads are possible.
- Phantom reads are possible.

Example:

Transaction 1 (T1) reads a row:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 3000

Meanwhile, Transaction 2 (T2) updates and commits a new balance for the same account:

UPDATE accounts SET balance = 5000 WHERE account_id = 1;
COMMIT;

Now, T1 reads the balance again:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 5000 (non-repeatable read)

The data changed between T1’s reads, leading to a non-repeatable read.

3. REPEATABLE READ

Description: This isolation level ensures that if a transaction reads a row, it will always read the same data throughout the transaction. Non-repeatable reads are prevented, but phantom reads can still occur.
Anomalies
- Dirty reads are not possible.
- Non-repeatable reads are not possible.
- Phantom reads are possible.

Example:

Transaction 1 (T1) reads a row:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 3000

Transaction 2 (T2) tries to update the same row, but T1 has locked it for repeatable reading:

UPDATE accounts SET balance = 5000 WHERE account_id = 1;
-- T2 waits for T1 to finish.

T1 can read the balance again and will still see the original value:

SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 3000 (repeatable read)

However, phantom reads can still occur. If T1 runs a query that selects multiple rows based on a condition (e.g., WHERE balance > 1000), and T2 inserts a new row that matches the condition, T1 will see this new row in subsequent queries within the same transaction.

4. SERIALIZABLE

Description: The strictest isolation level. Transactions are executed as if they were running serially (one after another). This level prevents all anomalies—dirty reads, non-repeatable reads, and phantom reads—but it severely limits concurrency.
Anomalies
- Dirty reads are not possible.
- Non-repeatable reads are not possible.
- Phantom reads are not possible.

Example:

Transaction 1 (T1) reads a row:

SELECT balance FROM accounts WHERE account_id = 1; 
-- Returns 3000

If Transaction 2 (T2) tries to update or insert new rows, it will be blocked until T1 completes:

INSERT INTO accounts (account_id, balance) VALUES (2, 2000);
-- T2 is blocked until T1 finishes

T1 will always see the same data, and no other transactions can modify or insert new data that could affect T1’s query results.

Summary of Isolation Levels and Anomalies

Isolation Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads
READ UNCOMMITTED	Yes	Yes	Yes
READ COMMITTED	No	Yes	Yes
REPEATABLE READ	No	No	Yes
SERIALIZABLE	No	No	No

Choosing the Right Isolation Level

READ UNCOMMITTED: Useful when performance is more important than consistency. You’re okay with reading uncommitted data (e.g., for logging or monitoring purposes).
READ COMMITTED: The most commonly used isolation level. It prevents dirty reads, and is often the default in many databases like MySQL and PostgreSQL. It offers a good balance between consistency and performance.
REPEATABLE READ: Ensures consistency for repeated reads of the same data, but allows for phantom reads. This is the default isolation level in MySQL.
SERIALIZABLE: The safest but slowest isolation level. It guarantees that no anomalies will occur but can reduce concurrency significantly. Use this for critical transactions where consistency is paramount (e.g., in banking or financial systems).

Example in MySQL: Changing Isolation Levels

You can set the isolation level for a transaction in MySQL using:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Or, you can set it globally for the entire session:

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Then, you can begin your transaction:

START TRANSACTION;
-- Your SQL operations
COMMIT;

This is how you can control the consistency and performance trade-offs of your transactions.

MySQL Basics: Understanding Join and Index with Examples

Yuhao Chen — Sat, 28 Sep 2024 01:48:39 +0000

Join

Types of Joins:

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
CROSS JOIN

1. INNER JOIN

An INNER JOIN retrieves records that have matching values in both tables. If there is no match, the row will not be included in the result set.

Example:

Let’s say you have two tables: employees and departments.

Table: employees

emp_id	first_name	last_name	department_id
1	John	Doe	101
2	Jane	Smith	102
3	Alice	Johnson	103
4	Bob	Williams	NULL

Table: departments

department_id	department_name
101	HR
102	Engineering
103	Marketing
104	Sales

Now, let's use an INNER JOIN to retrieve all employees with their corresponding department names:

SELECT employees.emp_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

Result:

emp_id	first_name	last_name	department_name
1	John	Doe	HR
2	Jane	Smith	Engineering
3	Alice	Johnson	Marketing

Explanation: Only the rows where there is a matching department_id in both tables (employees and departments) are included in the result set. Bob, who has a NULL value for department_id, is excluded.

2. LEFT JOIN (or LEFT OUTER JOIN)

A LEFT JOIN returns all records from the left table (in this case, employees), and the matched records from the right table (in this case, departments). If there is no match, the result is NULL for the right table's columns.

Example:

SELECT employees.emp_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

Result:

emp_id	first_name	last_name	department_name
1	John	Doe	HR
2	Jane	Smith	Engineering
3	Alice	Johnson	Marketing
4	Bob	Williams	NULL

Explanation: This query includes all rows from the employees table. If a corresponding department_id is not found in the departments table, the department_name column will contain NULL (as in Bob’s case).

3. RIGHT JOIN (or RIGHT OUTER JOIN)

A RIGHT JOIN returns all records from the right table (in this case, departments), and the matched records from the left table (in this case, employees). If there is no match, the result is NULL for the left table's columns.

Example:

SELECT employees.emp_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

Result:

emp_id	first_name	last_name	department_name
1	John	Doe	HR
2	Jane	Smith	Engineering
3	Alice	Johnson	Marketing
NULL	NULL	NULL	Sales

Explanation: This query returns all rows from the departments table. If there is no matching department_id in the employees table (as is the case with the "Sales" department), the employee fields will contain NULL.

4. FULL JOIN (or FULL OUTER JOIN)

A FULL JOIN combines the results of both LEFT JOIN and RIGHT JOIN. It returns all records when there is a match in either the left or right table. If there is no match, the result is NULL for the missing side.

Example (if MySQL supported FULL JOIN natively):

SELECT employees.emp_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.department_id;

Result (Hypothetical):

emp_id	first_name	last_name	department_name
1	John	Doe	HR
2	Jane	Smith	Engineering
3	Alice	Johnson	Marketing
4	Bob	Williams	NULL
NULL	NULL	NULL	Sales

Explanation: This query includes all rows from both tables. If there is no matching department_id in either table, the columns for the non-matching table will show NULL. In MySQL, you can simulate a FULL JOIN by combining a LEFT JOIN and a RIGHT JOIN using UNION.

SELECT employees.emp_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION
SELECT employees.emp_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

5. CROSS JOIN

A CROSS JOIN produces the Cartesian product of the two tables. It combines each row of the first table with every row of the second table, without considering any condition. This is useful when you want to combine all possible combinations of rows from two tables.

Example:

SELECT employees.first_name, departments.department_name
FROM employees
CROSS JOIN departments;

Result:

first_name	department_name
John	HR
John	Engineering
John	Marketing
John	Sales
Jane	HR
Jane	Engineering
Jane	Marketing
Jane	Sales
Alice	HR
Alice	Engineering
Alice	Marketing
Alice	Sales
Bob	HR
Bob	Engineering
Bob	Marketing
Bob	Sales

Explanation: The CROSS JOIN combines every employee with every department, resulting in a Cartesian product. This is often not useful unless you specifically want to get all combinations.

Real-World Use Cases

INNER JOIN: Used when you only want records that have matches in both tables (e.g., finding all orders where there is a matching customer).
LEFT JOIN: Useful when you want all records from the left table, even if there’s no matching record in the right table (e.g., listing all customers and their orders, including customers who have not placed any orders).
RIGHT JOIN: Similar to LEFT JOIN, but focuses on all records from the right table (e.g., listing all departments and the employees in those departments, including departments without employees).
FULL JOIN: Used when you need to see all records from both tables, whether or not they match.
CROSS JOIN: Used to create combinations (e.g., pairing products with all possible promotional offers).

Index

Types of Indexes

Clustered index: Determines the physical order of data in the table (there can be only one clustered index per table).

Non-clustered index: Separate from the table data and contains a pointer to the data.

(Only when they are not part of the primary key or explicitly defined as the clustered index)

Single-column index: Indexes a single column in a table.
Composite index: Indexes multiple columns in a table (useful for queries that filter by more than one column).
Unique index: Ensures that the indexed column(s) have unique values.

Clustered Index

Example

Let’s say we have an employees table:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,  -- Clustered Index is automatically created on this column
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50)
);

In this case, the emp_id is the primary key and will be the clustered index by default. The data in the employees table is physically ordered by emp_id.

Query benefit

A query like the one below will be fast because the rows are stored in the physical order of emp_id:

SELECT * FROM employees WHERE emp_id BETWEEN 100 AND 200;

Single-Column Index

Example

Let’s create a single-column index on the first_name column in the employees table.

CREATE INDEX idx_firstname ON employees (first_name);

This index is non-clustered (since there can only be one clustered index) and will improve query performance when searching by first_name.

Query benefit

The following query will benefit from the single-column index:

SELECT * FROM employees WHERE first_name = 'John';

Composite Index

Example

You frequently query employees by both last_name and department, so you can create a composite index on these two columns.

CREATE INDEX idx_lastname_department ON employees (last_name, department);

This is a non-clustered composite index that will speed up queries involving both last_name and department. The order of the columns in the composite index matters, as queries that filter by last_name first and then department will be faster.

Query benefit

The following query will benefit from the composite index:

SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'Sales';

However, if you search only by department, the index will not be as effective as if you had indexed department separately.

Unique Index

Example

You want to ensure that the email field is unique for all employees. You can create a unique index on the email column.

CREATE UNIQUE INDEX idx_email_unique ON employees (email);

This unique index guarantees that no two rows in the employees table will have the same email.

Query benefit

The following query will benefit from the unique index:

SELECT * FROM employees WHERE email = 'john.doe@example.com';

Additionally, if someone tries to insert a duplicate email, they will get an error due to the unique constraint:

INSERT INTO employees (emp_id, first_name, last_name, department, email)
VALUES (101, 'John', 'Doe', 'HR', 'john.doe@example.com');  -- This will fail if 'john.doe@example.com' already exists

Git Basics: Key Commands and Tips for Version Control

Yuhao Chen — Fri, 27 Sep 2024 14:03:21 +0000

Common Commands

1. Initialization and Configuration

git init: Initialize a new Git repository.
git config --global user.name "Your Name": Set your Git username.
git config --global user.email "your.email@example.com": Set your Git email.

2. Basic Commands

git clone <repository_url>: Clone a repository from a remote server.
git status: Check the status of your working directory.
git add <file>: Stage changes for the next commit.
git commit -m "Commit message": Commit staged changes.
git push: Push committed changes to a remote repository.
git pull: Fetch and merge changes from the remote repository.
git fetch: Download changes from the remote repository without merging them. This allows you to review before merging.

3. Branching and Merging

git branch: List all branches.
git branch <branch_name>: Create a new branch.
git checkout <branch_name>: Switch to a different branch.
git merge <branch_name>: Merge a branch into the current branch.

4. History and Diff

git log: Show commit history.
git diff: Show changes between commits, branches, etc.

5. Undoing Changes

git checkout -- <file>: Discard changes in a working directory.
git reset <commit>: Undo commits while preserving changes in the working directory.
git stash: Temporarily save (stash) changes in a working directory without committing, so you can switch branches or pull updates safely. Use git stash pop to apply stashed changes later.

Full Workflow

Starting a project from scratch and implementing a new feature

1. Set Up a New Git Repository

Initialize the Git Repository: Create a new project folder and initialize a Git repository.

  mkdir my-new-project
  cd my-new-project
  git init

Set Global Git Configuration (if not done previously):

  git config --global user.name "Your Name"
  git config --global user.email "your.email@example.com"

2. Set Up a Remote Repository

Create a remote repository (e.g., on GitHub, GitLab, Bitbucket) and link your local repo to it.
Connect to Remote

  git remote add origin https://github.com/your-username/your-repository.git

3. Create an Initial Commit

Add basic project setup files like README.md and .gitignore

  touch README.md
  echo "node_modules/" >> .gitignore

  git add .
  git commit -m "Initial project setup with README and .gitignore"
  git push -u origin main

4. Create Initial Branches

Create a developbranch (recommended) for ongoing development, keeping main for production-ready code.

  git checkout -b develop
  git push -u origin develop

5. Set Up Project Dependencies

Initialize your project with any required dependencies. For example, for Node.js:

  npm init -y
  npm install express  # Example dependency

  git add .
  git commit -m "Initialize project with basic dependencies"
  git push

6. Create a Feature Branch

Create a new branch for your feature to keep the develop branch clean.

  git checkout -b feature/new-feature

7. Work on the Feature

Implement the feature by writing the necessary code. Make sure to commit frequently with clear, descriptive commit messages.

  git add .
  git commit -m "Implement login functionality"

8. Pull Latest Changes from the Target Branch

Before pushing, ensure your feature branch is up-to-date with the latest code from develop (or main):
- Switch to the Target Branch:
```
git checkout develop
```
- Pull Latest Changes:
```
git pull origin develop
```
- Switch Back to Your Feature Branch:
```
git checkout feature/new-feature
```
- Merge the Latest Changes:
```
git merge develop
```
- Resolve Merge Conflicts (if any): If conflicts arise, Git will flag them, and you’ll need to manually resolve conflicts in the affected files. Once resolved:
```
git add <file>
git commit -m "Resolve merge conflicts"
```

9. Test Your Feature

Ensure the feature works by testing locally:
- Run Unit Tests.
- Manual Testing: Run the application and verify that the feature works as expected.

10. Push the Feature Branch to the Remote Repository

After testing and merging the latest changes from develop, push your feature branch to the remote repository.

  git push origin feature/new-feature

11. Create a Pull Request (PR)

On your Git hosting platform (GitHub, GitLab, etc.), create a Pull Request (PR) to merge your feature branch into develop (or main).
- Provide a Description: Explain what the feature does and highlight any important points for the reviewer.
- Link to Issues (if applicable): If the feature resolves any issues, link to them in the PR.

12. Code Review and Address Feedback

Collaborate with other developers for code review.
Make Changes Based on Feedback: If requested changes are necessary, implement them and push new commits to the feature branch:

  git add .
  git commit -m "Address code review feedback"
  git push

13. Merge the Feature Branch

Once the PR is approved, merge the feature branch into develop (or main).
If using the Git hosting platform’s UI, complete the merge there. Alternatively, merge locally:

  git checkout develop
  git merge feature/new-feature
  git push origin develop

14. Delete the Feature Branch

After merging, delete the feature branch both locally and on the remote repository to clean up.

  git branch -d feature/new-feature
  git push origin --delete feature/new-feature

15. Deploy and Verify (Optional)

If your project has a CI/CD pipeline, the feature might be automatically deployed. After deployment, verify that the feature works as expected in the staging or production environment.

MySQL Basics: Understanding DDL, DML, DQL, DCL, and TPL with Examples

Yuhao Chen — Fri, 27 Sep 2024 07:54:13 +0000

Language Type

DDL (Data Definition Language)

Data Definition Language is used to define database objects such as databases, tables, columns, etc.

DML (Data Manipulation Language)

Data Manipulation Language is used to manipulate database records.

DCL (Data Control Language)

Data Control Language is used to define access permissions and security levels.

DQL (Data Query Language)

Data Query Language is used to query data from the database.

TPL (Transaction Processing Language)

Transaction Processing Language is used to manage transactions in the database.

DDL (Data Definition Language)

DB

Explanation: Used to create and delete databases.
Example:

  CREATE DATABASE my_database;
  DROP DATABASE my_database;

Table

Explanation: Used to create, modify, or delete tables.
Example:

  CREATE TABLE employees (
      id INT PRIMARY KEY,
      name VARCHAR(100),
      department VARCHAR(50)
  );

  ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

  DROP TABLE employees;

Data Type

Explanation: Defines the type of data that can be stored in a table’s column.
int: Integer type.

double: Floating-point type. For example, double(5,2) means a maximum of 5 digits, with 2 of those digits after the decimal point. The maximum value would be 999.99.

decimal: Precision type, commonly used in financial or form data because it avoids precision loss.

char: Fixed-length string type. (If the input string is shorter than the defined length, spaces are added to fill the remaining space.)

varchar: Variable-length string type.

text: String type for larger amounts of text.

blob: Binary large object type, used to store binary data (such as files or images).

date: Date type, formatted as yyyy-MM-dd.

time: Time type, formatted as hh:mm:ss.

timestamp: Timestamp type, used to store both date and time.
Example:

  CREATE TABLE products (
      product_id INT,
      product_name VARCHAR(100),
      price DECIMAL(10, 2),
      created_at TIMESTAMP
  );

DML (Data Manipulation Language)

Insert

Explanation: Used to add records to a table.
Example:

  INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'HR');

Update

Explanation: Used to update existing records in a table.
Example:

  UPDATE employees SET department = 'Finance' WHERE id = 1;

Delete

Explanation: Used to delete records from a table.
Example:

  DELETE FROM employees WHERE id = 1;

DQL (Data Query Language)

Select

Explanation: Used to query data from a database.
Example:

  SELECT column_name(s)  -- Columns you want to query
  FROM table_name        -- Table from which you want to query data
  WHERE condition        -- Conditions to filter rows
  GROUP BY group_column  -- Columns used to group the results
  HAVING group_condition -- Conditions for grouped results
  ORDER BY sort_column   -- Columns used to sort the results
  LIMIT start_row, row_count -- Limit the result set with a starting row and row count

Key words

Explanation:
- SELECT column_name(s): Specifies the columns to retrieve.
- FROM table_name: Specifies the table from which to retrieve the data.
- WHERE condition: Filters rows based on the specified condition.
- GROUP BY group_column: Groups rows that have the same values in specified columns.
- HAVING group_condition: Filters groups based on a condition applied to the grouped data.
- ORDER BY sort_column: Sorts the result set by specified columns.
- LIMIT start_row, row_count: Limits the number of rows returned by the query starting from start_row.

Detailed Example

Example Table

`employees`

id	name	department	salary	hire_date	age
1	John Doe	HR	60000	2015-03-25	34
2	Jane Smith	IT	75000	2018-07-12	29
3	Bob Johnson	Finance	80000	2017-09-30	41
4	Alice Brown	IT	70000	2019-11-01	31
5	Mary Davis	HR	62000	2020-02-15	28
6	James White	Finance	85000	2016-12-10	38

1. Select Specific Columns

`SELECT column_name(s)`

Input: Query to select the employee name and salary from the employees table.

  SELECT name, salary FROM employees;

Output:

name	salary
John Doe	60000
Jane Smith	75000
Bob Johnson	80000
Alice Brown	70000
Mary Davis	62000
James White	85000

2. From a Specific Table

`FROM table_name`

Input: Query to select all columns from the employees table.

  SELECT * FROM employees;

Output:

id	name	department	salary	hire_date	age
1	John Doe	HR	60000	2015-03-25	34
2	Jane Smith	IT	75000	2018-07-12	29
3	Bob Johnson	Finance	80000	2017-09-30	41
4	Alice Brown	IT	70000	2019-11-01	31
5	Mary Davis	HR	62000	2020-02-15	28
6	James White	Finance	85000	2016-12-10	38

3. Filter Rows Using Conditions

`WHERE condition`

Input: Query to select employees from the IT department.

  SELECT * FROM employees WHERE department = 'IT';

Output:

id	name	department	salary	hire_date	age
2	Jane Smith	IT	75000	2018-07-12	29
4	Alice Brown	IT	70000	2019-11-01	31

4. Group Results

`GROUP BY group_column`

Input: Query to group employees by department and count the number of employees in each department.

  SELECT department, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department;

Output:

department	employee_count
HR	2
IT	2
Finance	2

5. Filter Grouped Results

`HAVING group_condition`

Input: Query to group employees by department and show departments with more than 1 employee.

  SELECT department, COUNT(*) AS employee_count
  FROM employees
  GROUP BY department
  HAVING COUNT(*) > 1;

Output:

department	employee_count
HR	2
IT	2
Finance	2

6. Sort the Results

`ORDER BY sort_column`

Input: Query to select all employees and sort them by salary in descending order.

  SELECT * FROM employees
  ORDER BY salary DESC;

Output:

id	name	department	salary	hire_date	age
6	James White	Finance	85000	2016-12-10	38
3	Bob Johnson	Finance	80000	2017-09-30	41
2	Jane Smith	IT	75000	2018-07-12	29
4	Alice Brown	IT	70000	2019-11-01	31
5	Mary Davis	HR	62000	2020-02-15	28
1	John Doe	HR	60000	2015-03-25	34

7. Limit the Results

`LIMIT start_row, row_count`

Input: Query to select the first 3 employees from the result.

  SELECT * FROM employees
  LIMIT 0, 3;

Output:

id	name	department	salary	hire_date	age
1	John Doe	HR	60000	2015-03-25	34
2	Jane Smith	IT	75000	2018-07-12	29
3	Bob Johnson	Finance	80000	2017-09-30	41

DCL (Data Control Language)

Create User

Explanation: Used to create a new database user.
Example:

  CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

Delete User

Explanation: Used to delete a database user.
Example:

  DROP USER 'new_user'@'localhost';

Grant Authorisation to User

Explanation: Used to give a user specific permissions.
Example:

  GRANT SELECT, INSERT ON my_database.* TO 'new_user'@'localhost';

Cancel Authorisation

Explanation: Used to revoke a user's permissions.
Example:

  REVOKE INSERT ON my_database.* FROM 'new_user'@'localhost';

Show Authorisation

Explanation: Used to display the permissions granted to a user.
Example:

  SHOW GRANTS FOR 'new_user'@'localhost';

TPL (Transaction Processing Language)

ACID

Explanation: ACID stands for Atomicity, Consistency, Isolation, and Durability, which are the properties that guarantee database transactions are processed reliably.
Example:
- Atomicity: Ensures that all operations within a transaction are completed; otherwise, the transaction is aborted.
- Consistency: Ensures that the database remains in a valid state before and after the transaction.
- Isolation: Ensures that transactions do not affect each other.
- Durability: Ensures that the result of a transaction is permanent, even in the case of a failure.

Start Transaction

Explanation: Begins a new transaction.
Example:

  START TRANSACTION;

Commit

Explanation: Saves the changes made by the transaction.
Example:

  COMMIT;

Autocommit

Explanation: Enables or disables the automatic commit of SQL statements.
Example:

  SET autocommit = 0;  -- Disable autocommit

Rollback

Explanation: Undoes the changes made by the transaction.
Example:

  ROLLBACK;

Full Transaction Example

Example Table

accounts

id	username	balance
1	Alice	1000.00
2	Bob	500.00
3	Charlie	750.00

START TRANSACTION;

-- Attempt to transfer $200 from Alice to Bob
UPDATE accounts SET balance = balance - 200 WHERE username = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE username = 'Bob';

-- Check if Bob's balance goes negative (for illustration)
SELECT balance FROM accounts WHERE username = 'Bob';  -- Assume this returns 700.00, no issue.

-- If all looks good, commit
COMMIT;

If an error occurs (e.g., if an unexpected condition arises), you might instead issue a ROLLBACK to ensure the accounts remain unchanged:

ROLLBACK;