What do you know about DML and DDL?

DML (Data Manipulation Language) and DDL (Data Definition Language) are subsets of SQL.

DDL (Data Definition Language) is used to define (create, modify, delete) the structure of a database and its objects (tables, indexes, views, etc.). DDL operations are non-transactional and cannot be rolled back.

Main DDL commands:

• CREATE: Creating database objects.

  -- Creating a table named users
  CREATE TABLE users (
      id INT PRIMARY KEY, // Primary key
      username VARCHAR(50) UNIQUE, // Unique username
      email VARCHAR(100)
  );
  

• ALTER: Modifying the structure of existing database objects.

  -- Adding a column last_login to the users table
  ALTER TABLE users
  ADD last_login TIMESTAMP;
  

• DROP: Deleting database objects.

  -- Dropping the users table
  DROP TABLE users;
  

• TRUNCATE: Removing all rows from a table. Faster than DELETE and not logged row-by-row.

  -- Removing all rows from the users table
  TRUNCATE TABLE users;
  

• RENAME: Renaming a database object.

  -- Renaming the users table to app_users
  RENAME TABLE users TO app_users;
  

DML (Data Manipulation Language) is used to manage (insert, update, delete, select) data stored in database objects. DML operations are transactional and can be rolled back.

Main DML commands:

• SELECT: Selecting data from one or multiple tables.

  -- Selecting all users
  SELECT * FROM users;
  
  -- Selecting a user by id
  SELECT username, email FROM users WHERE id = 1;
  

• INSERT: Inserting new rows into a table.

  -- Inserting a new user
  INSERT INTO users (id, username, email)
  VALUES (1, 'john_doe', 'john@example.com');
  

• UPDATE: Modifying existing data in a table.

  -- Updating email of user with id = 1
  UPDATE users
  SET email = 'john.doe@newdomain.com'
  WHERE id = 1;
  

• DELETE: Deleting rows from a table.

  -- Deleting user with id = 1
  DELETE FROM users WHERE id = 1;
  

Main differences:

Characteristic	DDL (Data Definition Language)	DML (Data Manipulation Language)
Purpose	Defining/changing database structure	Managing data within the database
Rollback	Cannot be rolled back	Can be rolled back
Impact	Changes database schema	Changes database content
Commands	CREATE, ALTER, DROP, TRUNCATE, RENAME	SELECT, INSERT, UPDATE, DELETE

Understanding the differences between DML and DDL is important for proper database design and interaction, especially in the context of transaction management and access rights.