MySQL Basic Commands

This document outlines fundamental SQL commands for interacting with a MySQL database from the command line. These commands are essential for basic administration, data exploration, and troubleshooting.

1. Connecting to MySQL

The mysql client allows you to connect to a MySQL server.

mysql -u <username> -p --host=<hostname_or_ip> --port=<port_number>

• -u <username>: Specifies the user to log in as (e.g., root).
• -p: Prompts for the password after you press Enter (do not put the password directly after -p in scripts for security reasons).
• --host=<hostname_or_ip>: Specifies the host where the MySQL server is running (e.g., localhost, 127.0.0.1, or a remote IP).
• --port=<port_number>: Specifies the port the MySQL server is listening on (default is 3306).

Example:

mysql -u root -p --host=my-database-server.com --port=3306

2. Database Management

List Databases

Once connected, you can list all available databases.

SHOW DATABASES;

Select a Database

To work with tables and data within a specific database, you must first select it.

USE <database_name>;
-- Example:
USE my_application_db;

3. Table Operations

Show Tables in Current Database

After selecting a database, you can list all tables within it.

SHOW TABLES;

Show Table Structure (Columns)

To see the schema of a table (column names, data types, constraints), use DESCRIBE or SHOW COLUMNS FROM.

SHOW COLUMNS FROM <table_name>;
-- Or:
DESCRIBE <table_name>;
 
-- Example:
SHOW COLUMNS FROM users;

4. Data Operations

Select All Data from a Table

To retrieve all rows and columns from a table:

SELECT * FROM <table_name>;
-- Example:
SELECT * FROM products;

Select Specific Columns

To retrieve only specific columns:

SELECT <column1>, <column2> FROM <table_name>;
-- Example:
SELECT name, email FROM customers;

Filtering Data

Use the WHERE clause to filter rows based on a condition:

SELECT * FROM <table_name> WHERE <condition>;
-- Example:
SELECT * FROM orders WHERE status = 'pending';