MySQL CLI Quick Reference

Connecting to MySQL

• Login to MySQL:

  mysql -u username -p

Prompts for a password.

• Specify Database on Login:

  mysql -u username -p -D database_name

Basic Commands

• Show all databases:

  SHOW DATABASES;

• Select a database:

  USE database_name;

• Show all tables in the current database:

  SHOW TABLES;

• Describe the structure of a table:

  DESCRIBE table_name;

• Show the current MySQL user:

  SELECT USER();

• Show current database:

  SELECT DATABASE();

• Show process list:

  SHOW PROCESSLIST;

• Show full process list (to see full queries):

  SHOW FULL PROCESSLIST;

• Exit MySQL:

  EXIT;

CRUD Operations

• Create a new database:

  CREATE DATABASE database_name;

• Create a new table:

  CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
  );

• Insert data into a table:

  INSERT INTO table_name (name, age) VALUES ('John', 30);

• Select data from a table:

  SELECT * FROM table_name;

• Update data in a table:

  UPDATE table_name SET age = 31 WHERE name = 'John';

• Delete data from a table:

  DELETE FROM table_name WHERE name = 'John';

• Drop a table:

  DROP TABLE table_name;

Loading .sql Files

To load and execute an SQL file that contains queries or database structure (e.g., schema.sql or data.sql):

Method 1: From Inside the MySQL Prompt

1. Open MySQL:

   mysql -u username -p

2. Select the database you want to load the file into:

   USE database_name;

3. Load the SQL file:

   SOURCE /path/to/your/file.sql;

Method 2: Directly from the Command Line

You can execute the SQL file directly from the command line without entering the MySQL prompt:

mysql -u username -p database_name < /path/to/your/file.sql

This method runs the .sql file directly into the specified database.

Common Tips

• View running MySQL queries:
  Use SHOW FULL PROCESSLIST; to view running queries, especially helpful for debugging long-running queries.
• Check MySQL version:

  SELECT VERSION();

• Enable SQL logging:
  Use SET GLOBAL general_log = 'ON'; to start logging all queries. To view the log, check the path from the following:

  SHOW VARIABLES LIKE 'general_log_file';

These commands and tips should help you navigate MySQL efficiently!