MySQL SQL
Structured Query Language fundamentals
💬 What is SQL?
SQL (Structured Query Language) is the standard language for interacting with databases. It allows you to create, read, update, and delete data. SQL commands are simple, readable, and powerful for database operations.
-- Basic SQL command
SELECT * FROM users WHERE age > 18;
SQL Command Categories
DDL
Data Definition Language defines database structure. CREATE makes new tables, ALTER modifies existing ones, and DROP removes them. These commands shape your database schema and define how data is organized and stored.
DML
Data Manipulation Language handles data operations. INSERT adds new records, UPDATE modifies existing data, and DELETE removes records. These are the most frequently used commands for managing your database content and information.
DQL
Data Query Language retrieves data from databases. SELECT is the primary command for fetching information. You can filter, sort, and combine data from multiple tables. DQL is essential for extracting meaningful insights from stored data.
DCL
Data Control Language manages permissions and access. GRANT gives users privileges while REVOKE removes them. DCL ensures database security by controlling who can view, modify, or delete data, protecting sensitive information from unauthorized access.
🔹 DDL - Creating Tables
DDL commands define your database structure. CREATE TABLE builds new tables with specified columns and data types. You define constraints like PRIMARY KEY and NOT NULL to ensure data quality and integrity from the start.
-- Create a new table
CREATE TABLE books (
book_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
author VARCHAR(100),
price DECIMAL(8, 2),
published_year INT
);
-- Show table structure
DESCRIBE books;Output:
| Field | Type | Null | Key |
|---|---|---|---|
| book_id | int | NO | PRI |
| title | varchar(200) | NO |
🔹 DML - Inserting Data
INSERT adds new records to tables. You specify the table name, columns, and values. You can insert single or multiple rows at once. This is how you populate your database with actual information.
-- Insert single record
INSERT INTO books (title, author, price, published_year)
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, 1925);
-- Insert multiple records
INSERT INTO books (title, author, price, published_year)
VALUES
('1984', 'George Orwell', 14.99, 1949),
('To Kill a Mockingbird', 'Harper Lee', 13.99, 1960);Output:
Query OK, 3 rows affected (0.01 sec)
🔹 DML - Updating Data
UPDATE modifies existing records in your table. Always use a WHERE clause to specify which rows to update, otherwise all rows will be changed. This command is essential for keeping your data current and accurate.
-- Update specific record
UPDATE books
SET price = 15.99
WHERE book_id = 1;
-- Update multiple columns
UPDATE books
SET price = 16.99, published_year = 1950
WHERE author = 'George Orwell';Output:
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
🔹 DML - Deleting Data
DELETE removes records from tables. Use WHERE clause carefully to specify which rows to delete. Without WHERE, all rows will be deleted. This is a permanent operation, so always double-check your conditions before executing.
-- Delete specific record
DELETE FROM books
WHERE book_id = 3;
-- Delete with condition
DELETE FROM books
WHERE published_year < 1950;Output:
Query OK, 2 rows affected (0.01 sec)
🔹 Common SQL Data Types
MySQL supports various data types for different kinds of information. Choosing the right data type ensures efficient storage and accurate data representation. Each type has specific uses and storage requirements for optimal database performance.
Popular Data Types:
- INT: Whole numbers (e.g., 42, -100)
- VARCHAR(n): Variable-length text up to n characters
- TEXT: Long text content
- DECIMAL(m,d): Precise decimal numbers
- DATE: Date values (YYYY-MM-DD)
- DATETIME: Date and time combined
- BOOLEAN: True/False values