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

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

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:

2. From a Specific Table

FROM table_name

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

  SELECT * FROM employees;

• Output:

3. Filter Rows Using Conditions

WHERE condition

• Input: Query to select employees from the IT department.

  SELECT * FROM employees WHERE department = 'IT';

• Output:

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:

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:

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:

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:

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

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;