A practical, beginner-to-intermediate reference for MySQL commands, including what each command does, why it matters, and real-world usage patterns.

These are the default locations where the MySQL client executable is installed. You need this path to run mysql from the terminal.

• macOS: /usr/local/mysql/bin
• Windows: C:\Program Files\MySQL\MySQL <version>\bin
• XAMPP: /xampp/mysql/bin

Definition: The MySQL binary is the actual program (mysql) that lets you interact with databases from the command line.

# Current session only
export PATH=${PATH}:/usr/local/mysql/bin

Definition: Temporarily adds MySQL to your system path so the mysql command works in the current terminal session.

# Permanently (macOS / Linux)
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bash_profile

Definition: Permanently adds MySQL to your PATH so it’s available every time you open a terminal.

📌 Why this matters: Without this, you must type the full path to mysql every time.

mysql -u root -p

Definition: Logs into the MySQL server as the root user and prompts for a password.

• -u root → username
• -p → ask for password

📌 Real-world tip: Avoid using root for applications—create dedicated users instead.

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'password';

Definition: Creates a new MySQL user who can connect only from localhost.

📌 Use case: Create separate users for applications, admins, and reporting tools.

GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Definition: Gives the user full access to all databases and reloads permission tables.

⚠️ Best practice: In production, grant only the permissions the user actually needs.

SHOW GRANTS FOR 'appuser'@'localhost';

Definition: Displays all privileges assigned to a specific user.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'appuser'@'localhost';

Definition: Removes all permissions and the ability to grant permissions to others.

DROP USER 'appuser'@'localhost';

Definition: Completely removes the user account from MySQL.

exit;

Definition: Closes the MySQL interactive shell.

SHOW DATABASES;

Definition: Lists all databases the current user has access to.

CREATE DATABASE acme;

Definition: Creates a new database named acme.

📌 Best practice: Use one database per application or service.

USE acme;

Definition: Sets acme as the active database for all subsequent queries.

DROP DATABASE acme;

Definition: Permanently deletes the database and all its tables.

⚠️ Danger: This action cannot be undone.

CREATE TABLE users (
  id INT AUTO_INCREMENT,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(50),
  password VARCHAR(20),
  location VARCHAR(100),
  dept VARCHAR(100),
  is_admin TINYINT(1),
  register_date DATETIME,
  PRIMARY KEY (id)
);

Definition: Creates a users table with typed columns and a primary key.

• AUTO_INCREMENT → automatically increases ID
• PRIMARY KEY → uniquely identifies each row

📌 Real-world note: Passwords should be hashed, not stored as plain text.

SHOW TABLES;

Definition: Lists all tables in the currently selected database.

DROP TABLE users;

Definition: Deletes the table and all stored data.

INSERT INTO users
(first_name, last_name, email, password, location, dept, is_admin, register_date)
VALUES
('Brad', 'Traversy', 'brad@gmail.com', '123456', 'Massachusetts', 'development', 1, NOW());

Definition: Adds a single record (row) into the users table.

📌 Best practice: Always specify column names to prevent schema-change errors.

INSERT INTO users (...) VALUES (...), (...), (...);

Definition: Inserts multiple records in a single query for better performance.

SELECT * FROM users;

Definition: Retrieves all columns and all rows from the table.

⚠️ Production tip: Avoid SELECT *; request only needed columns.

SELECT first_name, last_name FROM users;

Definition: Returns only the selected columns.

SELECT * FROM users WHERE location = 'Massachusetts';

Definition: Filters rows based on a condition.

SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';

Definition: Applies multiple filtering conditions.

UPDATE users
SET email = 'freddy@gmail.com'
WHERE id = 2;

Definition: Modifies existing data in a table.

⚠️ Critical rule: Always run a SELECT with the same WHERE clause first.

DELETE FROM users WHERE id = 6;

Definition: Removes specific rows from a table.

❌ DELETE FROM users; deletes everything.

ALTER TABLE users ADD age VARCHAR(3);

Definition: Adds a new column to an existing table.

ALTER TABLE users MODIFY COLUMN age INT(3);

Definition: Changes the data type or constraints of a column.

SELECT * FROM users ORDER BY last_name ASC;

Definition: Sorts results alphabetically (ascending).

SELECT CONCAT(first_name, ' ', last_name) AS Name FROM users;

Definition: Combines multiple columns into one output field.

SELECT * FROM users WHERE dept LIKE 'dev%';

Definition: Searches using pattern matching (% = wildcard).

SELECT * FROM users WHERE age BETWEEN 20 AND 25;

Definition: Filters values within a numeric range.

SELECT * FROM users WHERE dept IN ('design', 'sales');

Definition: Matches any value in a provided list.

CREATE INDEX idx_location ON users(location);

Definition: Improves query performance on frequently searched columns.

📌 Use indexes for: WHERE, JOIN, ORDER BY

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  title VARCHAR(100),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Definition: Creates a relationship between posts and users.

SELECT users.first_name, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;

Definition: Returns rows where data exists in both tables.

SELECT users.first_name, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

Definition: Returns all users, even those without posts.

SELECT COUNT(id) FROM users;

Definition: Counts total rows.

SELECT MAX(age), MIN(age) FROM users;

Definition: Finds highest and lowest values.

SELECT dept, COUNT(*)
FROM users
GROUP BY dept;

Definition: Groups rows and applies aggregate functions per group.

HAVING COUNT(*) >= 2;

Definition: Filters grouped results (used after GROUP BY).

erDiagram
    USERS ||--o{ POSTS : writes
    POSTS ||--o{ COMMENTS : has
    USERS ||--o{ COMMENTS : makes

Definition: Visual representation of table relationships in a real-world system.