Dialect note: These notes mainly use MySQL. In MySQL,
DATABASEandSCHEMAare effectively synonyms; other database systems organise them differently. UseDECIMALrather thanFLOATfor exact money values.
What is a Database?
A database is a structured collection of data, usually stored in tables.
Example (E-commerce):
- Signup → store customer info
- Signin → validate stored info
- Profile → fetch customer details
What is a Table?
A table is an entity that holds data in rows and columns.
- Rows → records
- Columns → fields
Types of Databases
1. Relational Databases (SQL)
Examples:
- MySQL
- SQL Server
- PostgreSQL
- SQLite
- MariaDB
2. NoSQL Databases
Examples:
- HBase
- Cassandra
- MongoDB
- DynamoDB
MySQL vs SQL
| Term | Meaning |
|---|---|
| MySQL | Database engine (stores the data) |
| SQL | Language used to query/operate on the data |
Example Database Structure
Database: retail_db
Tables: customers, orders, order_itemsSQL Concepts and Commands
Creating a Database
CREATE DATABASE retail_db;Creating a Table
Example: orders table
CREATE TABLE orders (
order_id INT,
order_date DATETIME,
customer_id INT,
order_status VARCHAR(30)
);SQL Comment Styles
. comment
- – comment
- /* comment */
Common SQL Data Types
- INT
- VARCHAR(30)
- DATE
- DATETIME
- FLOAT
Schema Structure
database → schema → tablesExamples:
retail_db → tables
public / dbo / sys → schemasDropping Objects
DROP TABLE table_name;
DROP DATABASE database_name;Tables and Sample Data
Customers Table
Create Table
CREATE TABLE customers (
customer_id INT,
customer_fname VARCHAR(45),
customer_lname VARCHAR(45),
customer_email VARCHAR(45),
customer_password VARCHAR(45),
customer_street VARCHAR(255),
customer_city VARCHAR(45),
customer_state VARCHAR(45),
customer_zipcode VARCHAR(45)
);Insert Records
INSERT INTO customers VALUES
(1,'Richard','Hernandez','XXXXXXXXX','XXXXXXXXX','6303 Heather Plaza','Brownsville','TX','78521');
INSERT INTO customers VALUES
(2,'Mary','Barrett','XXXXXXXXX','XXXXXXXXX','9526 Noble Embers Ridge','Littleton','CO','80126');Select Records
SELECT * FROM customers;Orders Table
Create Table
CREATE TABLE orders (
order_id INT,
order_date DATETIME,
order_customer_id INT,
order_status VARCHAR(45)
);Insert Records
INSERT INTO orders VALUES (1,'2013-07-25 00:00:00',11599,'CLOSED');
INSERT INTO orders VALUES (2,'2013-07-25',256,'PENDING_PAYMENT');Select Records
SELECT * FROM orders;Order Items Table
Create Table
CREATE TABLE order_items (
order_item_id INT,
order_item_order_id INT,
order_item_product_id INT,
order_item_quantity INT,
order_item_subtotal FLOAT,
order_item_product_price FLOAT
);Insert Records
INSERT INTO order_items VALUES (1,1,957,1,299.98,299.98);
INSERT INTO order_items VALUES (2,2,1073,1,199.99,199.99);
INSERT INTO order_items VALUES (3,2,502,5,250,50);Select Records
SELECT * FROM order_items;