MySQL Commands Documentation

This guide provides commonly used MySQL commands used daily by database administrators and system administrators. Commands range from simple queries to advanced operations such as joins, views, and full-text searches.

Introduction

MySQL is an open-source relational database management system used for storing and managing structured data.

Connect to MySQL

# Login to MySQL server
mysql -u root -p

# Login to remote MySQL server
mysql -u username -p -h 192.168.1.10

# Show MySQL version
SELECT VERSION();

Database Operations

# Show all databases
SHOW DATABASES;

# Create new database
CREATE DATABASE school;

# Select database
USE school;

# Delete database
DROP DATABASE school;

Table Operations

# Show tables in database
SHOW TABLES;

# Show table structure
DESCRIBE users;

# Create table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(150),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# Delete table
DROP TABLE users;

Insert & Update Data

# Insert record
INSERT INTO users(name,email)
VALUES('John Doe','john@email.com');

# Insert multiple rows
INSERT INTO users(name,email)
VALUES
('Alice','alice@mail.com'),
('Bob','bob@mail.com');

# Update data
UPDATE users SET email='new@mail.com'
WHERE id=1;

# Delete row
DELETE FROM users WHERE id=5;

Selecting Data

# Select all records
SELECT * FROM users;

# Select specific columns
SELECT name,email FROM users;

# Filter results
SELECT * FROM users WHERE id=1;

# Order results
SELECT * FROM users ORDER BY id DESC;

# Limit number of results
SELECT * FROM users LIMIT 10;

Join Queries

# INNER JOIN example
SELECT users.name,orders.total
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

# LEFT JOIN (show all users even if no orders)
SELECT users.name,orders.total
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

Self Join (Table to Itself)

Used when records in the same table relate to each other, such as employees and managers.

SELECT e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

Common MySQL Functions

# Count rows
SELECT COUNT(*) FROM users;

# Sum column values
SELECT SUM(amount) FROM payments;

# Average value
SELECT AVG(score) FROM exams;

# Get current date
SELECT NOW();

# String search
SELECT * FROM users WHERE name LIKE '%john%';

Views

# Create view
CREATE VIEW user_orders AS
SELECT users.name,orders.total
FROM users
JOIN orders ON users.id = orders.user_id;

# Query view
SELECT * FROM user_orders;

# Delete view
DROP VIEW user_orders;

Copying Tables

# Copy table structure
CREATE TABLE users_backup LIKE users;

# Copy table with data
CREATE TABLE users_backup AS SELECT * FROM users;

Renaming Tables

# Rename table
RENAME TABLE users TO customers;

Full Text Search

# Add full text index
ALTER TABLE articles ADD FULLTEXT(title,content);

# Search text
SELECT * FROM articles
WHERE MATCH(title,content)
AGAINST('mysql tutorial');

Backup & Restore

# Backup database
mysqldump -u root -p dbname > backup.sql

# Restore database
mysql -u root -p dbname < backup.sql

MySQL Documentation