A database (DB) serves as a repository for storing and managing data. A Database Management System (DBMS) is software designed to manipulate and administer databases. Relational databases (RDBMS) are built on the relational model, organizing data into interconnected two-dimensional tables. This structure ensures uniform data storage, simplifying maintenance, and employs SQL (Structured Query Language) for operations, providing a standardized approach suitable for complex queries.

SQL statements can be written in single or multiple lines, terminated with a semicolon. Spaces and indentation enhance readability. In MySQL, SQL keywords are case-insensitive. Comments include single-line (-- comment or # comment) and multi-line (/* comment */).

Database Opeartions with DDL

Managing Databases

• List all databases:

  SHOW DATABASES;
  

• Identify the current database:

  SELECT DATABASE();
  

• Switch to a specific database:

  USE db_name;
  

• Create a new database (optional existence check):

  CREATE DATABASE IF NOT EXISTS db_name;
  

• Remove a database:

  DROP DATABASE IF EXISTS db_name;
  

Table Operations

• Create a table with columns, constraints, and comments:

  CREATE TABLE table_name (
      col1 INT NOT NULL COMMENT 'ID field',
      col2 VARCHAR(50) DEFAULT 'N/A' COMMENT 'Description',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  ) COMMENT 'Sample table';
  

• List tables in the current database:

  SHOW TABLES;
  

• View table structure:

  DESCRIBE table_name;
  

• Retrieve the creation statement:

  SHOW CREATE TABLE table_name;
  

• Modify table schema:

  ALTER TABLE table_name ADD new_col DECIMAL(10,2);
  ALTER TABLE table_name MODIFY col1 BIGINT;
  ALTER TABLE table_name CHANGE old_col new_col CHAR(20);
  ALTER TABLE table_name DROP COLUMN col2;
  RENAME TABLE table_name TO new_table_name;
  

• Delete a table:

  DROP TABLE IF EXISTS table_name;
  

Constraints

Constraints enforce rules on table fields to ensure data accuracy and integrity.

Data Types

MySQL supports numeric, string, and date/time types. String and date values must be enclosed in quotes.