Commonly Used MySQL Commands for Beginners

Understanding MySQL Basics

MySQL is a popular open-source relational database management system used to store and retrieve data. It uses SQL (Structured Query Language) for database operations. Here are commonly used MySQL commands that beginners should get familiar with is they want to master in MySQL.

1. Connecting to the MySQL Server

mysql -u username -p

This command connects you to the MySQL server using the specified username. After running it, you'll be prompted to enter your password.

2. Viewing Available Databases

SHOW DATABASES;

This displays a list of all the databases accessible to the current MySQL user.

3. Creating a New Database

CREATE DATABASE mydatabase;

This creates a new database named mydatabase.

4. Using a Database

USE mydatabase;

Selects the database you want to work with so that all subsequent queries will apply to it.

5. Creating a Table

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

Creates a table named users with three fields: id, name, and email.

6. Viewing Tables in a Database

SHOW TABLES;

Displays a list of all tables in the current database.

7. Describing a Table

DESCRIBE users;

Shows the structure of the users table, including field names, types, and keys.

8. Inserting Data into a Table

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

Adds a new row with specified values into the users table.

9. Selecting Data from a Table

SELECT * FROM users;

Retrieves all records from the users table.

10. Filtering Records

SELECT * FROM users WHERE name = 'John';

Fetches rows where the name is 'John'.

11. Updating Data

UPDATE users SET email = 'john_new@example.com' WHERE name = 'John';

Modifies the email of the user whose name is 'John'.

12. Deleting Data

DELETE FROM users WHERE name = 'John';

Removes records from the database with the name 'John' from the users table.

13. Deleting a Table

DROP TABLE users;

This command deletes the entire users table, including its structure and data. Before deleting, or dropping, a table, make sure you are sure about that as there's no undoing of that action.

14. Deleting a Database

DROP DATABASE mydatabase;

Completely removes the mydatabase from the MySQL server. Double check that you are deleting the right database and you are sure about the action, as you can't reverse it.

15. Exiting MySQL

exit;

This command ends your MySQL session and returns you to the terminal or command prompt.

Tips for Beginners

• Always backup important data before using commands like DROP or DELETE.
• Practice using a test database so you can explore commands without risking real data.
• Use semicolons at the end of each SQL statement to ensure it executes properly.
• Keep queries readable by using proper indentation and uppercase for commands.

These beginner-friendly MySQL commands cover the basics of managing databases, tables and data. Learning to use them fluently will form a strong foundation for advanced SQL operations and help streamline your web development workflow.

f t g+ in