Relational Database Management System
MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL). It is one of the world's most popular open-source databases, widely used in web applications, content management systems, and e-commerce platforms.
Originally developed by MySQL AB and now owned by Oracle Corporation, MySQL offers reliability, robustness, and ease of use. It follows the client-server architecture where a database server handles requests from multiple clients, and it supports various storage engines that provide different capabilities for different use cases.
-- Create a new database CREATE DATABASE bookstore; -- Use the database USE bookstore; -- Create a table with constraints CREATE TABLE books ( book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, author VARCHAR(100) NOT NULL, price DECIMAL(10, 2), publish_date DATE, category ENUM('Fiction', 'Non-Fiction', 'Science', 'History'), in_stock BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create a related table with a foreign key CREATE TABLE reviews ( review_id INT PRIMARY KEY AUTO_INCREMENT, book_id INT, reviewer_name VARCHAR(100), rating INT CHECK (rating BETWEEN 1 AND 5), review_text TEXT, review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (book_id) REFERENCES books(book_id) ON DELETE CASCADE );
-- Insert data into tables INSERT INTO books (title, author, price, publish_date, category) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 12.99, '1925-04-10', 'Fiction'), ('To Kill a Mockingbird', 'Harper Lee', 14.95, '1960-07-11', 'Fiction'), ('A Brief History of Time', 'Stephen Hawking', 18.50, '1988-01-01', 'Science'); -- Basic query with WHERE, ORDER BY, and LIMIT SELECT title, author, price FROM books WHERE category = 'Fiction' ORDER BY price DESC LIMIT 10; -- Joining tables SELECT b.title, b.author, r.rating, r.review_text FROM books b LEFT JOIN reviews r ON b.book_id = r.book_id WHERE b.category = 'Fiction' ORDER BY r.rating DESC;
-- Using MySQL stored procedure DELIMITER // CREATE PROCEDURE GetBooksByCategory(IN category_name VARCHAR(50)) BEGIN SELECT * FROM books WHERE category = category_name ORDER BY publish_date DESC; END // DELIMITER ; -- Call the stored procedure CALL GetBooksByCategory('Science'); -- Creating an index for performance CREATE INDEX idx_book_category ON books(category); -- Example of a MySQL trigger DELIMITER // CREATE TRIGGER before_book_update BEFORE UPDATE ON books FOR EACH ROW BEGIN SET NEW.created_at = NOW(); END // DELIMITER ;
MySQL powers many types of applications:
MySQL has gone through significant development since its inception:
MySQL's development continues under Oracle, while forks like MariaDB (created by MySQL's original developers) also maintain compatibility with added features.
Here are some excellent resources for learning MySQL:
Database technologies related to MySQL: