Creating Databases and Tables
Creating Databases and Tables
Building Your Data Structure with SQL Commands
🎯 What You'll Learn
- How to create and manage databases
- Creating tables with proper structure
- Understanding SQL syntax and commands
- Setting up primary keys and auto-increment
- Adding constraints and relationships
🗄️ Creating Your First Database
🏢 Think of Databases Like Office Buildings
- 🏢MySQL Server= The entire office building
- 🏗️Database= Each floor of the building
- 📁Tables= Individual offices on each floor
- 📄Records= Files in each office
-- Creating a new database is simple!
CREATE DATABASE online_store;
-- Use the database
USE online_store;
-- See all databases
SHOW DATABASES;
-- Delete a database (careful!)
DROP DATABASE IF EXISTS test_db;
CREATE DATABASE online_store;
-- Use the database
USE online_store;
-- See all databases
SHOW DATABASES;
-- Delete a database (careful!)
DROP DATABASE IF EXISTS test_db;
⚠️ Warning:
DROP DATABASEpermanently deletes everything! There's no undo button!📋 Creating Your First Table
Let's create auserstable for our online store:
CREATE TABLEusers (
idINT AUTO_INCREMENT PRIMARY KEY,
usernameVARCHAR(50) NOT NULL UNIQUE,
emailVARCHAR(100) NOT NULL UNIQUE,
passwordVARCHAR(255) NOT NULL,
first_nameVARCHAR(50),
last_nameVARCHAR(50),
created_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
idINT AUTO_INCREMENT PRIMARY KEY,
usernameVARCHAR(50) NOT NULL UNIQUE,
emailVARCHAR(100) NOT NULL UNIQUE,
passwordVARCHAR(255) NOT NULL,
first_nameVARCHAR(50),
last_nameVARCHAR(50),
created_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_atTIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
users Table Structure
Column
Data Type
Constraints
Description
id
INT
PK
NN
Unique identifier
username
VARCHAR(50)
NN
UQ
User's login name
email
VARCHAR(100)
NN
UQ
Email address
🔑 PRIMARY KEY
Unique identifier for each row. Like a social security number - no two are the same!
🔄 AUTO_INCREMENT
Automatically adds 1 to the previous value. Perfect for IDs!
❗ NOT NULL
This field must have a value. Can't be empty!
🎯 UNIQUE
No duplicates allowed. Each value must be different!
🏗️ Building Related Tables
Let's create a complete e-commerce database structure:
graph TB
Users[users table]
Products[products table]
Orders[orders table]
OrderItems[order_items table]
Categories[categories table]
Users -->|has many| Orders
Orders -->|has many| OrderItems
Products -->|belongs to| Categories
Products -->|appears in| OrderItems
style Users fill:#667eea,color:white
style Products fill:#f59e0b,color:white
style Orders fill:#10b981,color:white
style OrderItems fill:#ef4444,color:white
style Categories fill:#8b5cf6,color:white
-- Categories table
CREATE TABLEcategories (
idINT AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(50) NOT NULL,
descriptionTEXT
);
-- Products table with foreign key
CREATE TABLEproducts (
idINT AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(100) NOT NULL,
descriptionTEXT,
priceDECIMAL(10,2) NOT NULL,
stockINT DEFAULT0,
category_idINT,
FOREIGN KEY(category_id)REFERENCEScategories(id)
);
-- Orders table
CREATE TABLEorders (
idINT AUTO_INCREMENT PRIMARY KEY,
user_idINT NOT NULL,
order_dateDATETIME DEFAULT CURRENT_TIMESTAMP,
total_amountDECIMAL(10,2) NOT NULL,
statusENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
FOREIGN KEY(user_id)REFERENCESusers(id)
);
CREATE TABLEcategories (
idINT AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(50) NOT NULL,
descriptionTEXT
);
-- Products table with foreign key
CREATE TABLEproducts (
idINT AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(100) NOT NULL,
descriptionTEXT,
priceDECIMAL(10,2) NOT NULL,
stockINT DEFAULT0,
category_idINT,
FOREIGN KEY(category_id)REFERENCEScategories(id)
);
-- Orders table
CREATE TABLEorders (
idINT AUTO_INCREMENT PRIMARY KEY,
user_idINT NOT NULL,
order_dateDATETIME DEFAULT CURRENT_TIMESTAMP,
total_amountDECIMAL(10,2) NOT NULL,
statusENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
FOREIGN KEY(user_id)REFERENCESusers(id)
);
🔧 Modifying Tables
Sometimes you need to change a table after creating it. Here's how:
-- Add a new column
ALTER TABLEusersADD COLUMNphoneVARCHAR(20);
-- Modify an existing column
ALTER TABLEusersMODIFY COLUMNphoneVARCHAR(30) NOT NULL;
-- Rename a column
ALTER TABLEusersRENAME COLUMNphoneTOphone_number;
-- Drop a column (careful!)
ALTER TABLEusersDROP COLUMNphone_number;
-- Add an index for faster searches
CREATE INDEXidx_emailONusers(email);
-- Rename a table
ALTER TABLEusersRENAME TOcustomers;
ALTER TABLEusersADD COLUMNphoneVARCHAR(20);
-- Modify an existing column
ALTER TABLEusersMODIFY COLUMNphoneVARCHAR(30) NOT NULL;
-- Rename a column
ALTER TABLEusersRENAME COLUMNphoneTOphone_number;
-- Drop a column (careful!)
ALTER TABLEusersDROP COLUMNphone_number;
-- Add an index for faster searches
CREATE INDEXidx_emailONusers(email);
-- Rename a table
ALTER TABLEusersRENAME TOcustomers;
🎨 Interactive Table Builder
Build Your Own Table!
Try creating a table for a blog system:
📊 Viewing Table Information
-- See all tables in current database
SHOW TABLES;
-- Describe table structure
DESCRIBEusers;
-- or
SHOW COLUMNS FROMusers;
-- See the CREATE TABLE statement
SHOW CREATE TABLEusers;
-- Get table statistics
SHOW TABLE STATUS LIKE'users';
SHOW TABLES;
-- Describe table structure
DESCRIBEusers;
-- or
SHOW COLUMNS FROMusers;
-- See the CREATE TABLE statement
SHOW CREATE TABLEusers;
-- Get table statistics
SHOW TABLE STATUS LIKE'users';
🎮 Practice Exercise
Create a Library Database
Create a database for a library system with the following requirements:
- 📚 Abookstable with: ISBN, title, author, publication_year, available
- 👥 Amemberstable with: member_id, name, email, join_date
- 📋 Aloanstable tracking which member borrowed which book
💡 Click for Solution
CREATE DATABASElibrary;
USElibrary;
CREATE TABLEbooks (
isbnVARCHAR(13) PRIMARY KEY,
titleVARCHAR(200) NOT NULL,
authorVARCHAR(100) NOT NULL,
publication_yearYEAR,
availableBOOLEAN DEFAULTtrue
);
CREATE TABLEmembers (
member_idINT AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(100) NOT NULL,
emailVARCHAR(100) UNIQUE NOT NULL,
join_dateDATE DEFAULT(CURRENT_DATE)
);
CREATE TABLEloans (
loan_idINT AUTO_INCREMENT PRIMARY KEY,
member_idINT,
isbnVARCHAR(13),
loan_dateDATE DEFAULT(CURRENT_DATE),
return_dateDATE,
FOREIGN KEY(member_id)REFERENCESmembers(member_id),
FOREIGN KEY(isbn)REFERENCESbooks(isbn)
);
USElibrary;
CREATE TABLEbooks (
isbnVARCHAR(13) PRIMARY KEY,
titleVARCHAR(200) NOT NULL,
authorVARCHAR(100) NOT NULL,
publication_yearYEAR,
availableBOOLEAN DEFAULTtrue
);
CREATE TABLEmembers (
member_idINT AUTO_INCREMENT PRIMARY KEY,
nameVARCHAR(100) NOT NULL,
emailVARCHAR(100) UNIQUE NOT NULL,
join_dateDATE DEFAULT(CURRENT_DATE)
);
CREATE TABLEloans (
loan_idINT AUTO_INCREMENT PRIMARY KEY,
member_idINT,
isbnVARCHAR(13),
loan_dateDATE DEFAULT(CURRENT_DATE),
return_dateDATE,
FOREIGN KEY(member_id)REFERENCESmembers(member_id),
FOREIGN KEY(isbn)REFERENCESbooks(isbn)
);
🎯 Key Takeaways
-
CREATE DATABASEcreates a new database container -
CREATE TABLEdefines the structure for storing data - Primary keys uniquely identify each record
- Foreign keys create relationships between tables
- Constraints ensure data integrity
-
ALTER TABLElets you modify existing tables