A Guide to Basic Mysql Commands From Novice to Advanced

database management mysql

translations: [ de/Deutsch ] · [ fr/Français ] · [ es/Español ]

Table of contents

MySQL, a widely-used open-source relational database management system, is an essential tool for many IT professionals, including systems engineers. In this guide, we’ll explore some of the most common MySQL commands, ranging from basic to advanced. We’ll start with simple commands for beginners and gradually move to more complex ones.

Novice Commands

Connecting to MySQL

To start interacting with MySQL, you first need to connect to the MySQL server.

mysql -u username -p

Creating a Database

Creating a new database is a fundamental task.

CREATE DATABASE example_db;

Selecting a Database

Once you have created a database, you can select it for use.

USE example_db;

Creating a Table

Creating tables is a basic operation in database management.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Inserting Data

Insert data into your table with the INSERT command.

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

Retrieving Data

Use the SELECT command to retrieve data from a table.

SELECT * FROM users;

Intermediate Commands

Updating Data

Modify existing records using the UPDATE command.

UPDATE users SET email = 'alice@newdomain.com' WHERE name = 'Alice';

Deleting Data

Remove records from a table using DELETE.

DELETE FROM users WHERE name = 'Alice';

Checking if a Table Exists

Check for the existence of a table within the current database.

SELECT * FROM information_schema.tables WHERE table_schema = 'example_db' AND table_name = 'users' LIMIT 1;

Listing Tables

List all tables in the current database.

SHOW TABLES;

Advanced Commands

Joining Tables

Combine rows from two or more tables based on a related column.

SELECT users.name, orders.product FROM users
INNER JOIN orders ON users.id = orders.user_id;

Using Transactions

Transactions ensure that a series of SQL queries either complete successfully together or fail together.

START TRANSACTION;
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
UPDATE orders SET user_id = LAST_INSERT_ID() WHERE order_id = 123;
COMMIT;

Creating Indexes

Indexes improve the speed of data retrieval.

CREATE INDEX idx_name ON users (name);

Advanced Selection Queries

Utilize advanced SQL queries to filter and aggregate data.

SELECT COUNT(*), country FROM users GROUP BY country HAVING COUNT(*) > 5;

This guide covers a range of MySQL commands, from the very basic to more advanced ones. As you become more familiar with these commands, you’ll be able to manage and manipulate your databases more efficiently and effectively.

Created on: Jan 12, 2024