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
- Open MySQL:
mysql -u username -p
- Select the database you want to load the file into:
USE database_name;
- 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:
UseSHOW FULL PROCESSLIST;
to view running queries, especially helpful for debugging long-running queries. - Check MySQL version:
SELECT VERSION();
- Enable SQL logging:
UseSET 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!
Leave a Reply