MySQL is a powerful open-source database management system, widely used for storing and managing data. This detailed guide walks you through essential MySQL commands to help you get started.
1. Create a Database
Databases store tables and data. To create one:
CREATE DATABASE my_database;
CREATE DATABASEis the command to create a new database.- Replace
my_databasewith the name you want for your database.
Example:
CREATE DATABASE school_management;
This command creates a database named school_management.
2. Use a Database
After creating a database, you must select it to work with it.
USE my_database;
USEtells MySQL which database to operate on.
Example:
USE school_management;
This command sets the school_management database as active for further operations.
3. Create a Table
Tables organize data within a database. To create one:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2)
);
CREATE TABLEdefines a new table namedemployees.- Columns include:
id: Integer type, automatically increments, and serves as the primary key.name: Text type with a maximum of 50 characters.position: Text type with a maximum of 50 characters.salary: Decimal type with 10 digits in total, 2 of which are after the decimal point.
Example:
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
grade VARCHAR(10)
);
This creates a table students to store information about students.
4. Describe a Table
To see the structure of a table:
DESC table_name;
DESC(short for DESCRIBE) lists the table’s columns, data types, and constraints.
Example:
DESC employees;
This command displays the structure of the employees table.
5. Insert Data into a Table
To add records to a table:
INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);
- Specify the table name and columns to populate.
- Use
VALUESto provide the corresponding data.
Example:
INSERT INTO employees (name, position, salary)
VALUES ('Alice', 'Manager', 75000.00),
('Bob', 'Developer', 60000.00);
This adds two rows to the employees table.
Explanation:
- The first row contains
Alice, her positionManager, and her salary75000.00. - The second row contains
Bob, his positionDeveloper, and his salary60000.00.
6. Select Data from a Table
To retrieve data:
SELECT column1, column2 FROM table_name;
SELECTspecifies the columns to fetch.*fetches all columns.
Example 1: Fetch All Data
SELECT * FROM employees;
This retrieves all rows and columns from the employees table.
Example 2: Fetch Specific Data
SELECT name, position FROM employees;
This retrieves only the name and position columns.
7. Update Data in a Table
To modify existing data:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
UPDATEspecifies the table to update.SETdefines new values for the columns.WHERElimits the update to matching rows.
Example:
UPDATE employees
SET salary = 80000.00
WHERE name = 'Alice';
This updates Alice’s salary to 80000.00 in the employees table.
8. Delete Data from a Table
To remove records:
DELETE FROM table_name
WHERE condition;
DELETE FROMspecifies the table.WHEREdefines which rows to delete.
Example 1: Delete Specific Row
DELETE FROM employees
WHERE name = 'Bob';
This deletes Bob’s record from the employees table.
Example 2: Delete All Rows
DELETE FROM employees;
(Use this carefully—it removes all data but keeps the table structure.)
9. Best Practices
- Always back up your database before running
DELETEorUPDATEcommands. - Use
WHEREwithUPDATEandDELETEto avoid unintended changes. - Regularly check table structures with
DESCto ensure your design meets requirements.
Quick Recap:
- Create a Database:
CREATE DATABASE database_name; - Use a Database:
USE database_name; - Create a Table:
CREATE TABLE table_name (...); - Describe a Table:
DESC table_name; - Insert Data:
INSERT INTO table_name (...) VALUES (...); - Select Data:
SELECT ... FROM table_name; - Update Data:
UPDATE table_name SET ... WHERE ...; - Delete Data:
DELETE FROM table_name WHERE ...;
This detailed guide equips you with foundational MySQL commands. As you practice, you’ll gain confidence in managing databases efficiently. Happy coding!
