A MySQL database (DB) is a structured collection of data organized into tables within the MySQL relational database management system. It uses a SQL-based interface to manage data, making it ideal for applications requiring high performance, reliability, and scalability. Here's a brief overview of how a MySQL database functions:
Structure of a MySQL Database
Tables: Each database consists of one or more tables. Tables are the primary structure where data is stored. Each table is defined by a schema, which includes columns and data types.
Columns: Each table consists of columns, which define the structure of the data (e.g., id INT, name VARCHAR(100), email VARCHAR(100)).
Rows: Tables contain rows, which are individual records of data.
Primary Key: A unique identifier for each row in a table.
Indexes: Enhance query performance by allowing faster searches.
Relationships:
Foreign Keys: Create links between tables, ensuring referential integrity.
Joins: Used to combine rows from two or more tables based on a related column.
Data Types:
Numeric (INT, FLOAT, DOUBLE),
String (VARCHAR, CHAR, TEXT),
Date and Time (DATE, DATETIME, TIMESTAMP),
Binary (BLOB).
Managing MySQL Databases
Creating a Database:
sql
CREATE DATABASE my_database_name;
Selecting a Database:
sql
USE my_database_name;
Creating a Table:
sql
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Inserting Data:
sql
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
Selecting Data:
sql
SELECT * FROM users WHERE id = 1;
Updating Data:
sql
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
Deleting Data:
sql
DELETE FROM users WHERE id = 1;
Advanced Features
Indexes:
CREATE INDEX idx_email ON users (email);
Joins:
SELECT u.name, o.order_id FROM users u INNER JOIN orders o ON u.id = o.user_id;
Transactions:
BEGIN;, COMMIT;, ROLLBACK; for ensuring data consistency.
Stored Procedures:
Encapsulate complex operations.
sql
DELIMITER //
CREATE PROCEDURE get_user_info(IN user_id INT)
BEGIN
SELECT * FROM users WHERE id = user_id;
END//
DELIMITER ;
Views:
Virtual tables representing a SELECT query.
sql
CREATE VIEW user_orders AS
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.id = o.user_id;