Chapter 3: Creating and Managing Databases & Tables (DDL)

Now that your database environment is ready, it’s time to build the foundation of your data storage: the database itself and the tables within it. This chapter introduces Data Definition Language (DDL) commands. DDL is a subset of SQL that deals with creating, modifying, and deleting database objects like databases, tables, indexes, and views. Understanding DDL is crucial because it’s how you define the structure, or “schema,” that holds all your important data. We’ll specifically focus on the CREATE, ALTER, and DROP commands for databases and tables.

What is DDL?

Data Definition Language (DDL) commands are used to define or modify the structure of your database. Unlike Data Manipulation Language (DML) which works with the actual data inside the tables, DDL works with the tables and databases themselves.

Think of it this way:

  • DDL is like building the empty house (creating tables, defining rooms).
  • DML is like furnishing the house (inserting data, updating data, deleting data).

The primary DDL commands we’ll cover are:

  • CREATE: To create new databases or tables.
  • ALTER: To modify the structure of existing databases or tables.
  • DROP: To delete existing databases or tables.

Creating a Database

The first step in any new project is to create a dedicated database to hold all your related tables.

Syntax for CREATE DATABASE

SQL
CREATE DATABASE database_name;

  • CREATE DATABASE: These are the keywords that tell SQL you want to create a new database.
  • database_name: This is the name you want to give to your database.

Example: Creating scriptbuzz_db

Let’s create the database we outlined in the previous chapter: scriptbuzz_db.

Open your phpMyAdmin or MySQL Workbench and navigate to the SQL query tab/editor. Then, type or paste the following:

SQL
CREATE DATABASE scriptbuzz_db;

Expected Outcome:

  • phpMyAdmin: You’ll see a success message, and scriptbuzz_db will appear in the left-hand navigation pane.
  • MySQL Workbench: You’ll see a green checkmark indicating success, and you can refresh the Schemas panel (usually on the left) to see scriptbuzz_db listed.

Best Practices for Database Naming

  • Descriptive: Choose a name that clearly indicates the purpose of the database (e.g., blog_app, ecommerce_site).
  • Lowercase and Underscores: Use lowercase letters and underscores (_) to separate words (e.g., my_website_db). This is a common convention and avoids issues with case sensitivity on different operating systems or database configurations.
  • Avoid Special Characters: Stick to letters, numbers, and underscores. Avoid hyphens, spaces, or other symbols.

Dropping a Database

Sometimes you need to completely remove a database, perhaps for starting fresh or cleaning up old projects.

Syntax for DROP DATABASE

SQL
DROP DATABASE database_name;

  • DROP DATABASE: Keywords to initiate database deletion.
  • database_name: The name of the database you want to delete.

Warning: Emphasize the Destructive Nature

BE EXTREMELY CAREFUL WITH DROP DATABASE! This command permanently deletes the database and all tables and data within it. There is no “undo” button. Always double-check the database name before executing this command, especially on a live server.

Example: DROP DATABASE scriptbuzz_db;

(Only run this if you want to delete the database you just created! We will be using scriptbuzz_db for future chapters.)

SQL
DROP DATABASE scriptbuzz_db;

Expected Outcome:

  • The scriptbuzz_db will be removed from your database list.

Creating a Table

Once you have a database, you can start creating tables within it. Tables are where your actual data will reside. When creating a table, you define its columns, their data types, and any constraints.

Before creating a table, make sure you’ve selected the correct database. In phpMyAdmin, click on scriptbuzz_db in the left panel. In MySQL Workbench, you can double-click scriptbuzz_db in the Schemas panel or use the USE statement:

SQL
USE scriptbuzz_db;

This command tells MySQL which database you want to work with for subsequent queries.

Syntax for CREATE TABLE

SQL
CREATE TABLE table_name (
    column1_name DATATYPE (size) CONSTRAINTS,
    column2_name DATATYPE (size) CONSTRAINTS,
    ...
    PRIMARY KEY (column_name)
);

  • CREATE TABLE: Keywords to create a new table.
  • table_name: The name you want to give your table.
  • column_name: The name for each column in your table.
  • DATATYPE: Specifies the type of data the column will hold (e.g., numbers, text, dates). We’ll cover common types in Chapter 4.
  • (size): For some data types (like VARCHAR), you specify a maximum length.
  • CONSTRAINTS: Rules that apply to the data in a column (e.g., NOT NULL, UNIQUE, PRIMARY KEY, AUTO_INCREMENT).

Introduce Basic Data Types Relevant to Web (MySQL/MariaDB)

Here are some common data types you’ll use frequently in web development:

  • INT: Whole numbers (integers).
  • VARCHAR(size): Variable-length string, for text up to a specified size (e.g., VARCHAR(255) for names, short descriptions). Essential for textual data.
  • TEXT: For longer strings of text (e.g., blog post content). No size needed, but has a larger limit than VARCHAR.
  • DATE: Stores a date in YYYY-MM-DD format.
  • DATETIME: Stores both date and time in YYYY-MM-DD HH:MM:SS format.
  • DECIMAL(P, S): For precise numeric values, like prices. P is the total number of digits, S is the number of digits after the decimal point (e.g., DECIMAL(10,2) allows numbers up to 99,999,999.99).
  • BOOLEAN or TINYINT(1): MySQL doesn’t have a direct BOOLEAN type. TINYINT(1) is commonly used, where 1 represents TRUE and 0 represents FALSE.

Introduce PRIMARY KEY and AUTO_INCREMENT Constraints

  • PRIMARY KEY: A column (or set of columns) that uniquely identifies each row in a table.
    • Rules: Must contain unique values, cannot contain NULL values.
    • A table can have only one primary key.
    • It’s often an INT column named id or table_name_id.
  • AUTO_INCREMENT: Used with integer primary keys. Automatically generates a unique, sequential number for each new record inserted into the table. This is incredibly useful for generating unique IDs without manual effort.

Example: Create users and products Tables

Let’s create the users and products tables based on our scriptbuzz_db schema.

First, ensure you are using the correct database:

SQL
USE scriptbuzz_db;

Now, create the users table:

SQL
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Explanation of users table columns:

  • user_id INT AUTO_INCREMENT PRIMARY KEY: An integer that automatically increments for each new user, and serves as the unique identifier for each user.
  • username VARCHAR(50) NOT NULL UNIQUE: A text field up to 50 characters, must have a value (NOT NULL), and each username must be unique (UNIQUE).
  • email VARCHAR(100) NOT NULL UNIQUE: Similar to username, but for email addresses.
  • password VARCHAR(255) NOT NULL: A text field for storing hashed passwords. VARCHAR(255) is a common size for many hashing algorithms. It must not be empty.
  • created_at DATETIME DEFAULT CURRENT_TIMESTAMP: A timestamp column that automatically records the date and time when a new user record is created.

Next, create the products table:

SQL
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Explanation of products table columns:

  • product_id INT AUTO_INCREMENT PRIMARY KEY: Unique identifier for each product.
  • name VARCHAR(100) NOT NULL: Product name, required.
  • description TEXT: Longer text for product details, optional.
  • price DECIMAL(10,2) NOT NULL: Price, up to 10 digits total, 2 after the decimal point, required.
  • stock_quantity INT NOT NULL DEFAULT 0: Integer for current stock, required, defaults to 0 if not specified.
  • created_at DATETIME DEFAULT CURRENT_TIMESTAMP: Timestamp when the product record was created.

Expected Outcome:

  • In phpMyAdmin, after running each CREATE TABLE query, you’ll see success messages and the tables (users, products) will appear under scriptbuzz_db in the left panel. You can click on them to view their structure.
  • In MySQL Workbench, after running each query, you’ll see a green checkmark. You might need to refresh the Schemas panel to see the new tables listed under scriptbuzz_db.

Altering a Table

Once a table is created, you might need to modify its structure later – add a new column, remove an old one, or change a column’s data type. The ALTER TABLE command is used for these modifications.

Syntax for ALTER TABLE (Common Uses)

Add a Column:

SQL
ALTER TABLE table_name
ADD COLUMN new_column_name DATATYPE (size) CONSTRAINTS;

Drop a Column:

SQL
ALTER TABLE table_name
DROP COLUMN column_name_to_drop;

Modify a Column (Change Data Type/Constraints):

SQL
ALTER TABLE table_name
MODIFY COLUMN column_name_to_modify NEW_DATATYPE (size) NEW_CONSTRAINTS;

(Note: CHANGE COLUMN is also used for renaming columns and modifying simultaneously, but MODIFY is simpler for just changing type/constraints.)

Examples

Let’s add a last_login column to our users table:

SQL
USE scriptbuzz_db;

ALTER TABLE users
ADD COLUMN last_login DATETIME;

What it does: Adds a new column named last_login of type DATETIME to the users table. By default, this new column will allow NULL values.

Now, let’s say we realize products needs a category column:

SQL
USE scriptbuzz_db;

ALTER TABLE products
ADD COLUMN category VARCHAR(50) AFTER name;

What it does: Adds a category column (VARCHAR, 50 chars) to the products table, placing it immediately after the name column. AFTER name is optional, without it, the column is added at the end.

Let’s modify the description column in products to ensure it’s NOT NULL (if we decide all products must have a description):

SQL
USE scriptbuzz_db;

ALTER TABLE products
MODIFY COLUMN description TEXT NOT NULL;

What it does: Changes the description column to be NOT NULL. If there are existing NULL values, this query might fail or require a DEFAULT value to be set. (We’ll cover DEFAULT in more detail in the next chapter).

Finally, let’s imagine we no longer need the last_login column in users:

SQL
USE scriptbuzz_db;

ALTER TABLE users
DROP COLUMN last_login;

What it does: Removes the last_login column and all its data from the users table.

Dropping a Table

Similar to dropping a database, you can also drop individual tables.

Syntax for DROP TABLE

SQL
DROP TABLE table_name;

  • DROP TABLE: Keywords to initiate table deletion.
  • table_name: The name of the table you want to delete.

Warning: Emphasize the Destructive Nature

Again, be extremely cautious with DROP TABLE! This command permanently deletes the table and all data within it. There is no “undo.”

Example: DROP TABLE Users;

(Only run this if you want to delete the users table! We will need it later.)

SQL
USE scriptbuzz_db;

DROP TABLE users;

Expected Outcome:

  • The users table will be removed from scriptbuzz_db.

Comments in SQL

Good practice dictates adding comments to your SQL queries, especially for complex ones, to explain their purpose or tricky parts.

Single-Line Comments

Starts with two hyphens -- followed by a space.

SQL
-- This is a single-line comment
SELECT * FROM users; -- Get all users

Multi-Line Comments

Starts with /* and ends with */.

SQL
/*
This is a multi-line comment.
It can span multiple lines.
Useful for explaining a block of SQL.
*/
CREATE TABLE my_temp_table (
    id INT PRIMARY KEY
);

Imp Notes:

  • DDL (Data Definition Language) commands are used to define and modify database structures.
  • CREATE DATABASE database_name; creates a new database.
  • DROP DATABASE database_name; permanently deletes a database and all its contents. Use with extreme caution!
  • USE database_name; selects the database to work within.
  • CREATE TABLE table_name (column DATATYPE CONSTRAINTS, ...); defines a new table with specified columns, data types, and constraints (PRIMARY KEY, AUTO_INCREMENT, NOT NULL, UNIQUE, DEFAULT).
  • ALTER TABLE table_name ADD COLUMN ...;, ALTER TABLE table_name DROP COLUMN ...;, and ALTER TABLE table_name MODIFY COLUMN ...; are used to change table structure.
  • DROP TABLE table_name; permanently deletes a table and all its data. Use with extreme caution!
  • Use -- for single-line comments and /* ... */ for multi-line comments to explain your SQL code.

Small Exercise

Using the scriptbuzz_db and the SQL editor in phpMyAdmin or MySQL Workbench:

  1. Create a new table called orders with the following columns:
    • order_id: Integer, Primary Key, Auto-increment.
    • user_id: Integer (This will eventually link to the users table, but don’t worry about Foreign Keys yet).
    • order_date: DATETIME, with a default value of the current timestamp.
    • total_amount: DECIMAL(10,2), cannot be NULL.
    • status: VARCHAR(20), with a default value of ‘pending’.
  2. After creating the orders table, add a new column shipping_address to it, which should be a VARCHAR(255).
  3. Finally, modify the status column in the orders table to be VARCHAR(30) instead of VARCHAR(20).

📘 Continue to the Next Chapter

Understanding SQL Data Types

Explore different SQL data types used in MySQL such as INT, VARCHAR, DATE, and learn how to choose the right one for your database.

➡️

🚀 Explore Popular Learning Tracks