Search

📚

MySQL commands 

MySQL Commands: A Simple Guide

This guide will help you understand how to use MySQL commands to connect to a MySQL server, run queries, and manage your databases. Let’s break it down step by step.

1. Connecting to a MySQL Server

To connect to a MySQL server, you need the following details:

  • Server host: The IP address or URL of the server. If it’s on your computer, use localhost.
  • Port: The port number MySQL is running on. The default is 3306.
  • Username: Your MySQL username.
  • Password: The password for your MySQL account.
  • Database (optional): If you want to connect directly to a specific database.

Connect to a Local MySQL Server

If the MySQL server is on your computer (localhost), use this command:

bash

Copy

mysql -u username -p
  • Replace username with your MySQL username.
  • You’ll be asked to enter your password.

Connect to a Local MySQL Server with a Specific Database

If you want to connect to a specific database on your local server:

bash

Copy

mysql -u username -p db_name
  • Replace username with your MySQL username.
  • Replace db_name with the name of the database you want to use.
  • You’ll be prompted to enter your password.

Connect to a Remote MySQL Server

If the MySQL server is on a different computer (remote server), use this command:

bash

Copy

mysql -h remote_host -u username -p
  • Replace remote_host with the IP address or hostname of the remote server.
  • Replace username with your MySQL username.
  • You’ll be asked to enter your password.

Connect to a Remote MySQL Server with a Specific Database

To connect to a specific database on a remote server:

bash

Copy

mysql -h remote_host -u username -p db_name
  • Replace remote_host with the IP address or hostname of the remote server.
  • Replace username with your MySQL username.
  • Replace db_name with the name of the database you want to use.
  • You’ll be prompted to enter your password.

Specify a Custom Port

If the MySQL server is using a port other than the default (3306), specify it like this:

bash

Copy

mysql -h remote_host -P port -u username -p
  • Replace remote_host with the IP address or hostname of the server.
  • Replace port with the port number MySQL is using.
  • Replace username with your MySQL username.
  • You’ll be asked to enter your password.

Connect Using a Login Path

If you’ve set up a login path (a secure way to store your credentials), you can connect like this:

bash

Copy

mysql --login-path=mypath
  • Replace mypath with the name of your login path.
  • If you don’t specify a login path, MySQL will use the default one.

Connect to MySQL Server with SSL

If your MySQL server requires an SSL connection, use this command:

bash

Copy

mysql -h remote_host -u username -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
  • Replace remote_host with the IP address or hostname of the server.
  • Replace username with your MySQL username.
  • Replace the paths (ca.pem, client-cert.pem, client-key.pem) with the actual paths to your SSL certificates.
  • You’ll be prompted to enter your password.

2. Exiting the MySQL Client

To exit the MySQL command-line client, you can use any of these commands:

  • \q
  • quit
  • exit

Alternatively, you can use:

  • Ctrl+D on Unix-like systems (Linux, macOS).
  • Ctrl+Z on Windows.

3. Formatting Query Results

Horizontal Format (Default)

By default, MySQL displays query results in a horizontal table format. For example:

sql

Copy

SELECT firstName, lastName FROM employees ORDER BY firstName LIMIT 2;

Output:

Copy

+-----------+----------+
| firstName | lastName |
+-----------+----------+
| Andy      | Fixter   |
| Anthony   | Bow      |
+-----------+----------+
2 rows in set (0.00 sec)

Vertical Format

If the output is too wide (e.g., many columns), you can display it vertically using \G instead of ;:

sql

Copy

SELECT firstName, lastName FROM employees ORDER BY firstName LIMIT 2\G

Output:

Copy

*************************** 1. row ***************************
firstName: Andy
 lastName: Fixter
*************************** 2. row ***************************
firstName: Anthony
 lastName: Bow
2 rows in set (0.00 sec)

4. Running a Single Command and Exiting

If you want to run a single SQL command and exit immediately, use the -e option:

bash

Copy

mysql -u username -p -e "SELECT * FROM table_name;" db_name
  • Replace username with your MySQL username.
  • Replace table_name with the name of the table you want to query.
  • Replace db_name with the name of the database.
  • You’ll be prompted to enter your password.

Example:

bash

Copy

mysql -u root -p -e "SELECT firstName, lastName FROM employees ORDER BY firstName LIMIT 2" classicmodels;

Output:

Copy

+-----------+----------+
| firstName | lastName |
+-----------+----------+
| Andy      | Fixter   |
| Anthony   | Bow      |
+-----------+----------+

5. Running Queries from a File

If you have a file with SQL commands (e.g., script.sql), you can run it like this:

bash

Copy

mysql -u username -p db_name < script.sql
  • Replace username with your MySQL username.
  • Replace db_name with the name of the database.
  • Replace script.sql with the path to your SQL file.
  • You’ll be prompted to enter your password.

Example:

bash

Copy

mysql -u root -p classicmodels < query.sql

The query.sql file might contain:

sql

Copy

SELECT firstName, lastName FROM employees ORDER BY firstName LIMIT 2;

6. Writing Query Results to a File

To save the results of a query to a file, use the > operator:

bash

Copy

mysql -u username -p db_name -e "SELECT * FROM table_name" > path/to/file
  • Replace username with your MySQL username.
  • Replace db_name with the name of the database.
  • Replace table_name with the name of the table.
  • Replace path/to/file with the path where you want to save the output.
  • You’ll be prompted to enter your password.

Example:

bash

Copy

mysql -u root -p classicmodels -e "SELECT * FROM employees" > employees.txt

This will save the output of the query to employees.txt.

7. Using the Output of Another Command as Input

On Unix-like systems (Linux, macOS), you can use the output of one command as input for MySQL. For example:

bash

Copy

cat query.sql | mysql -u root -p classicmodels
  • This reads the contents of query.sql and passes it to MySQL.

Summary

  • Use mysql -u username -p to connect to a MySQL server.
  • Use \q, quit, or exit to exit the MySQL client.
  • Use \G to format query results vertically.
  • Use e to run a single command and exit.
  • Use < to run queries from a file.
  • Use > to save query results to a file.

This guide should help you get started with MySQL commands! Let me know if you have any questions. 😊