MySQL is one of the most popular relational database management systems (RDBMS) used for web applications and other data-driven applications. Whether you’re a beginner or someone looking to brush up on your MySQL skills, understanding the basic queries is essential. This blog will walk you through some fundamental MySQL queries that you can use for database operations, table manipulations, and data management.
To start with, you need a database where you will store your tables and data. Creating a database is straightforward:
CREATE DATABASE my_database;
Once you have created the database, use the following query to select it:
USE my_database;
If you need to delete a database, use the following command:
DROP DATABASE my_database;
Tables are where your data is stored. You can create a table with specific columns as follows:
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), age INT );
To see all the tables in your selected database:
SHOW TABLES;
If you want to know the structure of a table, you can describe it:
DESCRIBE users;
If you need to modify a table by adding or changing columns:
ALTER TABLE users ADD phone VARCHAR(15);
ALTER TABLE users MODIFY age TINYINT;
To delete a table:
DROP TABLE users;
To add data to a table:
INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 25);
Retrieve data from a table:
SELECT name, email FROM users WHERE age > 20;
To retrieve all data from a table:
SELECT * FROM users;
To update data in a table:
UPDATE users SET age = 26 WHERE name = 'John Doe';
To remove data from a table:
DELETE FROM users WHERE name = 'John Doe';
Use the WHERE clause to filter records based on specific conditions:
SELECT * FROM users WHERE age > 20;
Combine multiple conditions using AND or OR:
SELECT * FROM users WHERE age > 20 AND name = 'John Doe';
Select data based on a list of values:
SELECT * FROM users WHERE age IN (20, 25, 30);
Filter data within a range:
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
Search for patterns using the LIKE clause:
SELECT * FROM users WHERE name LIKE 'J%';
Filter records with NULL or NOT NULL values:
SELECT * FROM users WHERE email IS NULL;
Count the number of rows:
SELECT COUNT(*) FROM users;
Calculate the sum of a column:
SELECT SUM(age) FROM users;
Find the average value of a column:
SELECT AVG(age) FROM users;
Find the maximum or minimum value of a column:
SELECT MAX(age) FROM users;
SELECT MIN(age) FROM users;
Group data based on one or more columns:
SELECT age, COUNT(*) FROM users GROUP BY age;
Filter grouped data:
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
Sort data in ascending or descending order:
SELECT * FROM users ORDER BY age DESC;
Fetch data from multiple tables where the condition is met in both:
SELECT users.name, orders.order_date FROM users INNER JOIN orders ON users.id = orders.user_id;
Fetch data from the left table and matching rows from the right table:
SELECT users.name, orders.order_date FROM users LEFT JOIN orders ON users.id = orders.user_id;
Fetch data from the right table and matching rows from the left table:
SELECT users.name, orders.order_date FROM users RIGHT JOIN orders ON users.id = orders.user_id;
Use a subquery to filter results:
SELECT name FROM users WHERE id = (SELECT user_id FROM orders WHERE order_id = 1);
Use a subquery to calculate values:
SELECT name, (SELECT COUNT(*) FROM orders WHERE users.id = orders.user_id) AS order_count FROM users;
Create a virtual table based on a query:
CREATE VIEW user_orders AS SELECT users.name, orders.order_date FROM users INNER JOIN orders ON users.id = orders.user_id;
Delete a view:
DROP VIEW user_orders;
Improve query performance by creating an index:
CREATE INDEX idx_name ON users (name);
Remove an index:
DROP INDEX idx_name ON users;
Understanding these basic MySQL queries is essential for anyone working with relational databases. Whether you are managing data, optimizing queries, or ensuring data integrity, these commands form the foundation of your MySQL skills. By mastering them, you'll be well-equipped to handle most database-related tasks with ease.
The above is the detailed content of Basic MySQL Queries: A Comprehensive Guide. For more information, please follow other related articles on the PHP Chinese website!